This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/texera.git
The following commit(s) were added to refs/heads/main by this push:
new e30eb17518 feat(storage): add the user_warehouse table schema (#7386)
e30eb17518 is described below
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;
