This is an automated email from the ASF dual-hosted git repository. github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-7386-307dfc4cbaa66302caa1a9b254d6d5743deed4d4 in repository https://gitbox.apache.org/repos/asf/texera.git
commit e30eb17518dec86b522a2715f6b73c431ed39097 Author: Meng Wang <[email protected]> AuthorDate: Sun Aug 9 07:56:35 2026 -0700 feat(storage): add the user_warehouse table schema (#7386) ### What changes were proposed in this PR? Adds the `user_warehouse` table (umbrella #6870): one row per warehouse a user registers. The DDL below is the full shape — base columns only; the assume-role (BYO-S3) columns come in a later change. ```sql CREATE TABLE IF NOT EXISTS user_warehouse ( whid SERIAL PRIMARY KEY, uid INT NOT NULL, name VARCHAR(128) NOT NULL, warehouse_name VARCHAR(255) NOT NULL UNIQUE, lakekeeper_warehouse_id UUID, flavor VARCHAR(32) NOT NULL, s3_bucket VARCHAR(255), s3_endpoint VARCHAR(255), s3_region VARCHAR(64), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (uid, name), FOREIGN KEY (uid) REFERENCES "user" (uid) ON DELETE CASCADE ); ``` Schema only — nothing reads or writes the table yet. The DDL, the incremental migration (`sql/updates/32.sql`), and the changelog registration ship together; jOOQ classes are generated from the live database at build time as usual. ### Any related issues, documentation, discussions? Closes #6931. Part of #6870 (design discussions #5293 and #6040). ### How was this PR tested? A new `UserWarehouseSpec` (MockTexeraDB, embedded Postgres) pins the DDL's structural properties against the generated jOOQ classes: insert/read-back of a registered warehouse, the per-user name uniqueness, and the ownership cascade. Verified locally with `sbt "DAO/testOnly *UserWarehouseSpec"` plus scalafmt/scalafix; the cascade case was deliberately broken once to confirm it fails red. ### Was this PR authored or co-authored using generative AI tooling? Generated-by: Claude Code (claude-fable-5) --- .../org/apache/texera/dao/UserWarehouseSpec.scala | 114 +++++++++++++++++++++ sql/changelog.xml | 5 + sql/texera_ddl.sql | 19 ++++ sql/updates/32.sql | 46 +++++++++ 4 files changed, 184 insertions(+) diff --git a/common/dao/src/test/scala/org/apache/texera/dao/UserWarehouseSpec.scala b/common/dao/src/test/scala/org/apache/texera/dao/UserWarehouseSpec.scala new file mode 100644 index 0000000000..23c549d059 --- /dev/null +++ b/common/dao/src/test/scala/org/apache/texera/dao/UserWarehouseSpec.scala @@ -0,0 +1,114 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.texera.dao + +import org.apache.texera.dao.jooq.generated.Tables.{USER, USER_WAREHOUSE} +import org.apache.texera.dao.jooq.generated.enums.UserWarehouseFlavorEnum +import org.jooq.exception.DataAccessException + +import java.util.UUID +import org.scalatest.BeforeAndAfterAll +import org.scalatest.flatspec.AnyFlatSpec +import org.scalatest.matchers.should.Matchers + +/** + * Spec for the `user_warehouse` table (#6931). Schema only — nothing reads or writes the + * table in production yet — so this pins the DDL's structural properties (columns, the + * per-user name uniqueness, and the ownership cascade) against the generated jOOQ classes. + */ +class UserWarehouseSpec extends AnyFlatSpec with Matchers with BeforeAndAfterAll with MockTexeraDB { + + override protected def beforeAll(): Unit = { + super.beforeAll() + initializeDBAndReplaceDSLContext() + } + + override protected def afterAll(): Unit = + try closeConnectionPool() + finally super.afterAll() + + private def insertUser(name: String): Integer = + getDSLContext + .insertInto(USER, USER.NAME, USER.PASSWORD) + .values(name, "password") + .returning(USER.UID) + .fetchOne() + .getUid + + private def insertWarehouse(uid: Integer, name: String, warehouseName: String): Integer = + getDSLContext + .insertInto( + USER_WAREHOUSE, + USER_WAREHOUSE.UID, + USER_WAREHOUSE.NAME, + USER_WAREHOUSE.WAREHOUSE_NAME, + USER_WAREHOUSE.LAKEKEEPER_WAREHOUSE_ID, + USER_WAREHOUSE.FLAVOR + ) + .values(uid, name, warehouseName, UUID.randomUUID(), UserWarehouseFlavorEnum.local) + .returning(USER_WAREHOUSE.WHID) + .fetchOne() + .getWhid + + "user_warehouse" should "store a registered warehouse and return it by owner" in { + val uid = insertUser("warehouse-owner") + insertWarehouse(uid, "mybucket", s"user-$uid-mybucket") + + val row = getDSLContext + .selectFrom(USER_WAREHOUSE) + .where(USER_WAREHOUSE.UID.eq(uid)) + .fetchOne() + row.getName shouldBe "mybucket" + row.getWarehouseName shouldBe s"user-$uid-mybucket" + row.getFlavor shouldBe UserWarehouseFlavorEnum.local + row.getLakekeeperWarehouseId should not be null + row.getCreatedAt should not be null + } + + it should "reject a duplicate display name for the same user" in { + val uid = insertUser("duplicate-name-owner") + insertWarehouse(uid, "dup", s"user-$uid-dup") + + a[DataAccessException] should be thrownBy + insertWarehouse(uid, "dup", s"user-$uid-dup-2") + } + + it should "reject a duplicate warehouse_name across users" in { + val first = insertUser("catalog-name-owner") + val second = insertUser("catalog-name-intruder") + insertWarehouse(first, "shared", "user-shared-catalog-name") + + a[DataAccessException] should be thrownBy + insertWarehouse(second, "unrelated", "user-shared-catalog-name") + } + + it should "cascade-delete a user's warehouses with the user" in { + val uid = insertUser("cascade-owner") + insertWarehouse(uid, "doomed", s"user-$uid-doomed") + + getDSLContext.deleteFrom(USER).where(USER.UID.eq(uid)).execute() + + getDSLContext + .selectFrom(USER_WAREHOUSE) + .where(USER_WAREHOUSE.UID.eq(uid)) + .fetch() + .size shouldBe 0 + } +} diff --git a/sql/changelog.xml b/sql/changelog.xml index bdba73339d..f6daf9dc33 100644 --- a/sql/changelog.xml +++ b/sql/changelog.xml @@ -68,6 +68,11 @@ <sqlFile path="sql/updates/31.sql"/> </changeSet> + <!-- Per-user warehouse registrations (#6870) --> + <changeSet id="32" author="mengw15"> + <sqlFile path="sql/updates/32.sql"/> + </changeSet> + <!-- example changeSet <changeSet id="1" author="author"> <sqlFile path="sql/updates/1.sql"/> diff --git a/sql/texera_ddl.sql b/sql/texera_ddl.sql index 5b62f45edd..e90b79226a 100644 --- a/sql/texera_ddl.sql +++ b/sql/texera_ddl.sql @@ -92,6 +92,7 @@ CREATE TYPE user_role_enum AS ENUM ('INACTIVE', 'RESTRICTED', 'REGULAR', 'ADMIN' CREATE TYPE action_enum AS ENUM ('like', 'unlike', 'view', 'clone'); CREATE TYPE privilege_enum AS ENUM ('NONE', 'READ', 'WRITE'); CREATE TYPE workflow_computing_unit_type_enum AS ENUM ('local', 'kubernetes'); +CREATE TYPE user_warehouse_flavor_enum AS ENUM ('local', 'aws'); -- ============================================ -- 5. Create tables @@ -239,6 +240,24 @@ CREATE TABLE IF NOT EXISTS workflow_computing_unit FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE ); +-- Per-user warehouse registrations (#6870): one row per warehouse a user registered. +-- Base columns only; the assume-role (BYO-S3) columns come in a later change. +CREATE TABLE IF NOT EXISTS user_warehouse +( + whid SERIAL PRIMARY KEY, + uid INT NOT NULL, + name VARCHAR(128) NOT NULL, + warehouse_name VARCHAR(255) NOT NULL UNIQUE, + lakekeeper_warehouse_id UUID NOT NULL, + flavor user_warehouse_flavor_enum NOT NULL, + s3_bucket VARCHAR(255), + s3_endpoint VARCHAR(255), + s3_region VARCHAR(64), + created_at TIMESTAMPTZ NOT NULL DEFAULT now(), + UNIQUE (uid, name), + FOREIGN KEY (uid) REFERENCES "user" (uid) ON DELETE CASCADE +); + -- virtual_environments table CREATE TABLE IF NOT EXISTS virtual_environments ( diff --git a/sql/updates/32.sql b/sql/updates/32.sql new file mode 100644 index 0000000000..e25c25b1fe --- /dev/null +++ b/sql/updates/32.sql @@ -0,0 +1,46 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +\c texera_db + +SET search_path TO texera_db; + +BEGIN; + +CREATE TYPE user_warehouse_flavor_enum AS ENUM ('local', 'aws'); + +-- Per-user warehouse registrations (#6870): one row per warehouse a user registered. +-- Base columns only; the assume-role (BYO-S3) columns come in a later change. +CREATE TABLE IF NOT EXISTS user_warehouse +( + whid SERIAL PRIMARY KEY, + uid INT NOT NULL, + name VARCHAR(128) NOT NULL, + warehouse_name VARCHAR(255) NOT NULL UNIQUE, + lakekeeper_warehouse_id UUID NOT NULL, + flavor user_warehouse_flavor_enum NOT NULL, + s3_bucket VARCHAR(255), + s3_endpoint VARCHAR(255), + s3_region VARCHAR(64), + created_at TIMESTAMPTZ NOT NULL DEFAULT now(), + UNIQUE (uid, name), + FOREIGN KEY (uid) REFERENCES "user" (uid) ON DELETE CASCADE +); + +COMMIT;
