| -- 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. |
| |
| -- ============================================ |
| -- 0. Specify the database name |
| -- (defaults to texera_db) |
| -- Override the name with: |
| -- psql -v DB_NAME=<alternative_name> ... |
| -- ============================================ |
| \if :{?DB_NAME} |
| \else |
| \set DB_NAME 'texera_db' |
| \endif |
| |
| -- ============================================ |
| -- 1. Drop and recreate the database (psql only) |
| -- Remove if you already created texera_db |
| -- ============================================ |
| \c postgres |
| DROP DATABASE IF EXISTS :"DB_NAME"; |
| CREATE DATABASE :"DB_NAME"; |
| |
| -- ============================================ |
| -- 2. Connect to the new database (psql only) |
| -- ============================================ |
| \c :"DB_NAME" |
| |
| CREATE SCHEMA IF NOT EXISTS texera_db; |
| SET search_path TO texera_db, public; |
| |
| -- ============================================ |
| -- 3. Drop all tables if they exist |
| -- (CASCADE handles FK dependencies) |
| -- ============================================ |
| DROP TABLE IF EXISTS operator_executions CASCADE; |
| DROP TABLE IF EXISTS operator_port_executions CASCADE; |
| DROP TABLE IF EXISTS operator_port_cache CASCADE; |
| DROP TABLE IF EXISTS workflow_user_access CASCADE; |
| DROP TABLE IF EXISTS workflow_of_user CASCADE; |
| DROP TABLE IF EXISTS user_config CASCADE; |
| DROP TABLE IF EXISTS auth_provider CASCADE; |
| DROP TABLE IF EXISTS "user" CASCADE; |
| DROP TABLE IF EXISTS user_last_active_time CASCADE; |
| DROP TABLE IF EXISTS workflow CASCADE; |
| DROP TABLE IF EXISTS workflow_version CASCADE; |
| DROP TABLE IF EXISTS project CASCADE; |
| DROP TABLE IF EXISTS workflow_of_project CASCADE; |
| DROP TABLE IF EXISTS workflow_executions CASCADE; |
| DROP TABLE IF EXISTS dataset_upload_session CASCADE; |
| DROP TABLE IF EXISTS dataset_upload_session_part CASCADE; |
| DROP TABLE IF EXISTS dataset CASCADE; |
| DROP TABLE IF EXISTS dataset_user_access CASCADE; |
| DROP TABLE IF EXISTS dataset_version CASCADE; |
| DROP TABLE IF EXISTS model_upload_session CASCADE; |
| DROP TABLE IF EXISTS model_upload_session_part CASCADE; |
| DROP TABLE IF EXISTS model_user_access CASCADE; |
| DROP TABLE IF EXISTS model_version CASCADE; |
| DROP TABLE IF EXISTS model CASCADE; |
| DROP TABLE IF EXISTS dataset_contributor CASCADE; |
| DROP TABLE IF EXISTS public_project CASCADE; |
| DROP TABLE IF EXISTS project_user_access CASCADE; |
| DROP TABLE IF EXISTS workflow_user_likes CASCADE; |
| DROP TABLE IF EXISTS workflow_user_clones CASCADE; |
| DROP TABLE IF EXISTS workflow_view_count CASCADE; |
| DROP TABLE IF EXISTS user_action CASCADE; |
| DROP TABLE IF EXISTS dataset_user_likes CASCADE; |
| DROP TABLE IF EXISTS dataset_view_count CASCADE; |
| DROP TABLE IF EXISTS site_settings CASCADE; |
| DROP TABLE IF EXISTS computing_unit_user_access CASCADE; |
| DROP TABLE IF EXISTS notebook CASCADE; |
| DROP TABLE IF EXISTS workflow_notebook_mapping CASCADE; |
| DROP TABLE IF EXISTS virtual_environments CASCADE; |
| |
| -- ============================================ |
| -- 4. Create PostgreSQL enum types |
| -- to mimic MySQL ENUM fields |
| -- ============================================ |
| DROP TYPE IF EXISTS user_role_enum CASCADE; |
| DROP TYPE IF EXISTS privilege_enum CASCADE; |
| DROP TYPE IF EXISTS action_enum CASCADE; |
| DROP TYPE IF EXISTS provider_type_enum CASCADE; |
| DROP TYPE IF EXISTS default_view_enum CASCADE; |
| |
| 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 provider_type_enum AS ENUM ('LOCAL', 'GOOGLE'); |
| CREATE TYPE user_warehouse_flavor_enum AS ENUM ('local', 'aws'); |
| CREATE TYPE default_view_enum AS ENUM ('CANVAS', 'FORM'); |
| |
| -- ============================================ |
| -- 5. Create tables |
| -- ============================================ |
| |
| -- "user" table |
| CREATE TABLE IF NOT EXISTS "user" |
| ( |
| uid SERIAL PRIMARY KEY, |
| name VARCHAR(256) NOT NULL, |
| email VARCHAR(256) UNIQUE, |
| avatar VARCHAR(512), |
| role user_role_enum NOT NULL DEFAULT 'INACTIVE', |
| comment TEXT, |
| account_creation_time TIMESTAMPTZ NOT NULL DEFAULT now(), |
| affiliation VARCHAR(128), |
| joining_reason VARCHAR(500), |
| -- placeholder accounts are auto-created for dataset contributors and carry no credentials until claimed |
| is_placeholder BOOLEAN NOT NULL DEFAULT FALSE |
| ); |
| |
| CREATE TABLE IF NOT EXISTS auth_provider |
| ( |
| uid INT NOT NULL, |
| provider_type provider_type_enum NOT NULL, |
| provider_id VARCHAR(256) NOT NULL, |
| password VARCHAR(256), -- hashed credential; only for LOCAL |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now(), |
| PRIMARY KEY (uid, provider_type), |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| CONSTRAINT uq_provider_identity UNIQUE (provider_type, provider_id), |
| CONSTRAINT ck_provider_credential CHECK ((provider_type = 'LOCAL') = (password IS NOT NULL)) |
| ); |
| |
| -- Contributor emails are resolved with lower(email) lookups. |
| CREATE INDEX idx_user_email_lower ON "user" (lower(email)); |
| |
| -- user_config |
| CREATE TABLE IF NOT EXISTS user_config |
| ( |
| uid INT NOT NULL, |
| key VARCHAR(256) NOT NULL, |
| value TEXT NOT NULL, |
| PRIMARY KEY (uid, key), |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE |
| ); |
| |
| -- feedback |
| CREATE TABLE IF NOT EXISTS feedback |
| ( |
| fid SERIAL PRIMARY KEY, |
| uid INT NOT NULL, |
| message TEXT NOT NULL, |
| creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE |
| ); |
| |
| -- workflow |
| CREATE TABLE IF NOT EXISTS workflow |
| ( |
| wid SERIAL PRIMARY KEY, |
| name VARCHAR(128) NOT NULL, |
| description TEXT, |
| content TEXT NOT NULL, |
| creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| last_modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| is_public BOOLEAN NOT NULL DEFAULT false, |
| -- Which view the workflow opens in by default (CANVAS or FORM); the form's definition |
| -- lives in workflow.content (`formBinding`). |
| default_view default_view_enum NOT NULL DEFAULT 'CANVAS' |
| ); |
| |
| -- workflow_of_user |
| CREATE TABLE IF NOT EXISTS workflow_of_user |
| ( |
| uid INT NOT NULL, |
| wid INT NOT NULL, |
| PRIMARY KEY (uid, wid), |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| FOREIGN KEY (wid) REFERENCES workflow(wid) ON DELETE CASCADE |
| ); |
| |
| -- workflow_user_access |
| CREATE TABLE IF NOT EXISTS workflow_user_access |
| ( |
| uid INT NOT NULL, |
| wid INT NOT NULL, |
| privilege privilege_enum NOT NULL DEFAULT 'NONE', |
| PRIMARY KEY (uid, wid), |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| FOREIGN KEY (wid) REFERENCES workflow(wid) ON DELETE CASCADE |
| ); |
| |
| -- workflow_version |
| CREATE TABLE IF NOT EXISTS workflow_version |
| ( |
| vid SERIAL PRIMARY KEY, |
| wid INT NOT NULL, |
| content TEXT NOT NULL, |
| creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| FOREIGN KEY (wid) REFERENCES workflow(wid) ON DELETE CASCADE |
| ); |
| |
| -- workflow_cover_image (optional custom card cover image, stored as a downscaled data URL) |
| CREATE TABLE IF NOT EXISTS workflow_cover_image |
| ( |
| wid INT PRIMARY KEY, |
| image TEXT NOT NULL, |
| FOREIGN KEY (wid) REFERENCES workflow(wid) ON DELETE CASCADE |
| ); |
| |
| -- project |
| CREATE TABLE IF NOT EXISTS project |
| ( |
| pid SERIAL PRIMARY KEY, |
| name VARCHAR(128) NOT NULL, |
| description VARCHAR(10000), |
| owner_id INT NOT NULL, |
| creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| color VARCHAR(6), |
| UNIQUE (owner_id, name), |
| FOREIGN KEY (owner_id) REFERENCES "user"(uid) ON DELETE CASCADE |
| ); |
| |
| -- workflow_of_project |
| CREATE TABLE IF NOT EXISTS workflow_of_project |
| ( |
| wid INT NOT NULL, |
| pid INT NOT NULL, |
| PRIMARY KEY (wid, pid), |
| FOREIGN KEY (wid) REFERENCES workflow(wid) ON DELETE CASCADE, |
| FOREIGN KEY (pid) REFERENCES project(pid) ON DELETE CASCADE |
| ); |
| |
| -- project_user_access |
| CREATE TABLE IF NOT EXISTS project_user_access |
| ( |
| uid INT NOT NULL, |
| pid INT NOT NULL, |
| privilege privilege_enum NOT NULL DEFAULT 'NONE', |
| PRIMARY KEY (uid, pid), |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| FOREIGN KEY (pid) REFERENCES project(pid) ON DELETE CASCADE |
| ); |
| |
| -- workflow_computing_unit table |
| CREATE TABLE IF NOT EXISTS workflow_computing_unit |
| ( |
| uid INT NOT NULL, |
| name VARCHAR(128) NOT NULL, |
| cuid SERIAL PRIMARY KEY, |
| creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| terminate_time TIMESTAMP DEFAULT NULL, |
| type workflow_computing_unit_type_enum, |
| uri TEXT NOT NULL DEFAULT '', |
| resource TEXT DEFAULT '', |
| 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, |
| lakekeeper_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 |
| ( |
| veid SERIAL PRIMARY KEY, |
| uid INT NOT NULL, |
| name VARCHAR(128) NOT NULL, |
| packages JSONB NOT NULL DEFAULT '{}'::jsonb, |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| UNIQUE (uid, name) |
| ); |
| |
| -- workflow_executions |
| CREATE TABLE IF NOT EXISTS workflow_executions |
| ( |
| eid SERIAL PRIMARY KEY, |
| vid INT NOT NULL, |
| uid INT NOT NULL, |
| cuid INT, |
| status SMALLINT NOT NULL DEFAULT 1, |
| result TEXT, |
| starting_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| last_update_time TIMESTAMP, |
| bookmarked BOOLEAN DEFAULT FALSE, |
| name VARCHAR(128) NOT NULL DEFAULT 'Untitled Execution', |
| environment_version VARCHAR(128) NOT NULL, |
| log_location TEXT, |
| runtime_stats_uri TEXT, |
| runtime_stats_size BIGINT DEFAULT 0, |
| whid INT, |
| FOREIGN KEY (vid) REFERENCES workflow_version(vid) ON DELETE CASCADE, |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| FOREIGN KEY (cuid) REFERENCES workflow_computing_unit(cuid) ON DELETE CASCADE, |
| FOREIGN KEY (whid) REFERENCES user_warehouse(whid) ON DELETE SET NULL |
| ); |
| |
| -- public_project |
| CREATE TABLE IF NOT EXISTS public_project |
| ( |
| pid INT PRIMARY KEY, |
| uid INT, |
| FOREIGN KEY (pid) REFERENCES project(pid) ON DELETE CASCADE |
| -- Note: MySQL schema doesn't define a foreign key for uid |
| ); |
| |
| -- dataset |
| CREATE TABLE IF NOT EXISTS dataset |
| ( |
| did SERIAL PRIMARY KEY, |
| owner_uid INT NOT NULL, |
| name VARCHAR(128) NOT NULL, |
| repository_name VARCHAR(128), |
| is_public BOOLEAN NOT NULL DEFAULT TRUE, |
| is_downloadable BOOLEAN NOT NULL DEFAULT TRUE, |
| description TEXT NOT NULL, |
| creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| cover_image varchar(255), |
| FOREIGN KEY (owner_uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| UNIQUE (owner_uid, name) |
| ); |
| |
| -- dataset_user_access |
| CREATE TABLE IF NOT EXISTS dataset_user_access |
| ( |
| did INT NOT NULL, |
| uid INT NOT NULL, |
| privilege privilege_enum NOT NULL DEFAULT 'NONE', |
| PRIMARY KEY (did, uid), |
| FOREIGN KEY (did) REFERENCES dataset(did) ON DELETE CASCADE, |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE |
| ); |
| |
| -- dataset_version |
| CREATE TABLE IF NOT EXISTS dataset_version |
| ( |
| dvid SERIAL PRIMARY KEY, |
| did INT NOT NULL, |
| creator_uid INT NOT NULL, |
| name VARCHAR(128) NOT NULL, |
| version_hash VARCHAR(64) NOT NULL, |
| creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| FOREIGN KEY (did) REFERENCES dataset(did) ON DELETE CASCADE |
| ); |
| |
| -- dataset_contributor |
| CREATE TABLE IF NOT EXISTS dataset_contributor |
| ( |
| cid SERIAL PRIMARY KEY, |
| did INT NOT NULL, |
| name VARCHAR(256) NOT NULL, |
| creator BOOLEAN NOT NULL DEFAULT FALSE, |
| email VARCHAR(256), |
| affiliation VARCHAR(256), |
| comments TEXT, |
| uid INT, |
| FOREIGN KEY (did) REFERENCES dataset(did) ON DELETE CASCADE, |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE SET NULL |
| ); |
| |
| -- Per-dataset contributor emails are unique (blank emails exempt). |
| CREATE UNIQUE INDEX idx_dataset_contributor_did_email |
| ON dataset_contributor (did, lower(trim(email))) |
| WHERE email IS NOT NULL AND trim(email) <> ''; |
| |
| CREATE TABLE IF NOT EXISTS dataset_upload_session |
| ( |
| did INT NOT NULL, |
| uid INT NOT NULL, |
| file_path TEXT NOT NULL, |
| upload_id VARCHAR(256) NOT NULL UNIQUE, |
| physical_address TEXT, |
| num_parts_requested INT NOT NULL, |
| file_size_bytes BIGINT NOT NULL, |
| part_size_bytes BIGINT NOT NULL, |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now(), |
| |
| PRIMARY KEY (uid, did, file_path), |
| |
| FOREIGN KEY (did) REFERENCES dataset(did) ON DELETE CASCADE, |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| |
| CONSTRAINT chk_dataset_upload_session_num_parts_requested_positive |
| CHECK (num_parts_requested >= 1), |
| |
| CONSTRAINT chk_dataset_upload_session_file_size_bytes_positive |
| CHECK (file_size_bytes > 0), |
| |
| CONSTRAINT chk_dataset_upload_session_part_size_bytes_positive |
| CHECK (part_size_bytes > 0), |
| |
| CONSTRAINT chk_dataset_upload_session_part_size_bytes_s3_upper_bound |
| CHECK (part_size_bytes <= 5368709120) |
| ); |
| |
| CREATE TABLE IF NOT EXISTS dataset_upload_session_part |
| ( |
| upload_id VARCHAR(256) NOT NULL, |
| part_number INT NOT NULL, |
| etag TEXT NOT NULL DEFAULT '', |
| |
| PRIMARY KEY (upload_id, part_number), |
| |
| CONSTRAINT chk_part_number_positive CHECK (part_number > 0), |
| |
| FOREIGN KEY (upload_id) |
| REFERENCES dataset_upload_session(upload_id) |
| ON DELETE CASCADE |
| ); |
| |
| -- ML models |
| CREATE TABLE IF NOT EXISTS model |
| ( |
| mid SERIAL PRIMARY KEY, |
| owner_uid INT NOT NULL, |
| name VARCHAR(128) NOT NULL, |
| repository_name VARCHAR(128), |
| is_public BOOLEAN NOT NULL DEFAULT TRUE, |
| is_downloadable BOOLEAN NOT NULL DEFAULT TRUE, |
| description TEXT NOT NULL, |
| creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| cover_image varchar(255), |
| framework VARCHAR(32), |
| format VARCHAR(32), |
| FOREIGN KEY (owner_uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| UNIQUE (owner_uid, name) |
| ); |
| |
| -- model_version |
| CREATE TABLE IF NOT EXISTS model_version |
| ( |
| mvid SERIAL PRIMARY KEY, |
| mid INT NOT NULL, |
| creator_uid INT NOT NULL, |
| name VARCHAR(128) NOT NULL, |
| version_hash VARCHAR(64) NOT NULL, |
| creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| FOREIGN KEY (mid) REFERENCES model(mid) ON DELETE CASCADE, |
| -- FileResolver resolves a version by (mid, name) with fetchOneInto. |
| CONSTRAINT uq_model_version_mid_name UNIQUE (mid, name) |
| ); |
| |
| -- model_user_access |
| CREATE TABLE IF NOT EXISTS model_user_access |
| ( |
| mid INT NOT NULL, |
| uid INT NOT NULL, |
| privilege privilege_enum NOT NULL DEFAULT 'NONE', |
| PRIMARY KEY (mid, uid), |
| FOREIGN KEY (mid) REFERENCES model(mid) ON DELETE CASCADE, |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE |
| ); |
| |
| -- model_upload_session |
| CREATE TABLE IF NOT EXISTS model_upload_session |
| ( |
| mid INT NOT NULL, |
| uid INT NOT NULL, |
| file_path TEXT NOT NULL, |
| upload_id VARCHAR(256) NOT NULL UNIQUE, |
| physical_address TEXT, |
| num_parts_requested INT NOT NULL, |
| file_size_bytes BIGINT NOT NULL, |
| part_size_bytes BIGINT NOT NULL, |
| created_at TIMESTAMPTZ NOT NULL DEFAULT now(), |
| |
| PRIMARY KEY (uid, mid, file_path), |
| |
| FOREIGN KEY (mid) REFERENCES model(mid) ON DELETE CASCADE, |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| |
| CONSTRAINT chk_model_upload_session_num_parts_requested_positive |
| CHECK (num_parts_requested >= 1), |
| |
| CONSTRAINT chk_model_upload_session_file_size_bytes_positive |
| CHECK (file_size_bytes > 0), |
| |
| CONSTRAINT chk_model_upload_session_part_size_bytes_positive |
| CHECK (part_size_bytes > 0), |
| |
| CONSTRAINT chk_model_upload_session_part_size_bytes_s3_upper_bound |
| CHECK (part_size_bytes <= 5368709120) |
| ); |
| |
| -- model_upload_session_part |
| CREATE TABLE IF NOT EXISTS model_upload_session_part |
| ( |
| upload_id VARCHAR(256) NOT NULL, |
| part_number INT NOT NULL, |
| etag TEXT NOT NULL DEFAULT '', |
| |
| PRIMARY KEY (upload_id, part_number), |
| |
| CONSTRAINT chk_model_part_number_positive CHECK (part_number > 0), |
| |
| FOREIGN KEY (upload_id) |
| REFERENCES model_upload_session(upload_id) |
| ON DELETE CASCADE |
| ); |
| |
| -- operator_executions (modified to match MySQL: no separate primary key; added console_messages_uri) |
| CREATE TABLE IF NOT EXISTS operator_executions |
| ( |
| workflow_execution_id INT NOT NULL, |
| operator_id VARCHAR(100) NOT NULL, |
| console_messages_uri TEXT, |
| console_messages_size BIGINT DEFAULT 0, |
| PRIMARY KEY (workflow_execution_id, operator_id), |
| FOREIGN KEY (workflow_execution_id) REFERENCES workflow_executions(eid) ON DELETE CASCADE |
| ); |
| |
| -- operator_port_executions |
| CREATE TABLE operator_port_executions |
| ( |
| workflow_execution_id INT NOT NULL, |
| global_port_id VARCHAR(200) NOT NULL, |
| result_uri TEXT, |
| result_size BIGINT DEFAULT 0, |
| PRIMARY KEY (workflow_execution_id, global_port_id), |
| FOREIGN KEY (workflow_execution_id) REFERENCES workflow_executions(eid) ON DELETE CASCADE |
| ); |
| |
| -- operator_port_cache |
| -- Caches a materialized output port result so it can be reused across executions. |
| -- A row is identified by (workflow_id, global_port_id, cache_key_hash), where |
| -- cache_key_hash is a SHA-256 hash of the upstream sub-DAG that produces the port (its |
| -- operators, their parameters and exec info, schemas, and wiring). cache_key_hash is the |
| -- lookup key; cache_key_json is the JSON the hash was computed from, kept so a hash match |
| -- can be confirmed against the full content (collision safety). A different upstream |
| -- computation (for example an operator parameter or version change) produces a different |
| -- cache_key_hash and therefore a new row, so existing entries are never overwritten: each |
| -- row is the result of one specific computation of one port. tuple_count is the result's |
| -- row count, kept so the coordinator can report a reused region's output stats without a |
| -- second query to the Iceberg catalog. |
| CREATE TABLE operator_port_cache |
| ( |
| workflow_id INT NOT NULL, |
| global_port_id VARCHAR(200) NOT NULL, |
| cache_key_hash CHAR(64) NOT NULL, |
| cache_key_json TEXT NOT NULL, |
| storage_uri TEXT NOT NULL, |
| tuple_count BIGINT, |
| source_execution_id BIGINT, |
| updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), |
| PRIMARY KEY (workflow_id, global_port_id, cache_key_hash), |
| FOREIGN KEY (workflow_id) REFERENCES workflow(wid) ON DELETE CASCADE |
| ); |
| |
| -- workflow_user_likes |
| CREATE TABLE IF NOT EXISTS workflow_user_likes |
| ( |
| uid INT NOT NULL, |
| wid INT NOT NULL, |
| PRIMARY KEY (uid, wid), |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| FOREIGN KEY (wid) REFERENCES workflow(wid) ON DELETE CASCADE |
| ); |
| |
| -- workflow_user_clones |
| CREATE TABLE IF NOT EXISTS workflow_user_clones |
| ( |
| uid INT NOT NULL, |
| wid INT NOT NULL, |
| PRIMARY KEY (uid, wid), |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| FOREIGN KEY (wid) REFERENCES workflow(wid) ON DELETE CASCADE |
| ); |
| |
| -- workflow_view_count |
| CREATE TABLE IF NOT EXISTS workflow_view_count |
| ( |
| wid INT NOT NULL PRIMARY KEY, |
| view_count INT NOT NULL DEFAULT 0, |
| FOREIGN KEY (wid) REFERENCES workflow(wid) ON DELETE CASCADE |
| ); |
| |
| -- user_action table |
| CREATE TABLE IF NOT EXISTS user_action ( |
| user_action_id BIGSERIAL PRIMARY KEY, |
| uid INTEGER, |
| ip VARCHAR(15), |
| action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| resource_type VARCHAR(15) NOT NULL, |
| resource_id INTEGER NOT NULL, |
| action texera_db.action_enum NOT NULL, |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE SET NULL |
| ); |
| |
| -- dataset_user_likes table |
| CREATE TABLE IF NOT EXISTS dataset_user_likes |
| ( |
| uid INTEGER NOT NULL, |
| did INTEGER NOT NULL, |
| PRIMARY KEY (uid, did), |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE, |
| FOREIGN KEY (did) REFERENCES dataset(did) ON DELETE CASCADE |
| ); |
| |
| -- dataset_view_count table |
| CREATE TABLE IF NOT EXISTS dataset_view_count |
| ( |
| did INTEGER NOT NULL, |
| view_count INTEGER NOT NULL DEFAULT 0, |
| PRIMARY KEY (did), |
| FOREIGN KEY (did) REFERENCES dataset(did) ON DELETE CASCADE |
| ); |
| |
| -- site_settings table |
| CREATE TABLE IF NOT EXISTS site_settings |
| ( |
| key VARCHAR(255) PRIMARY KEY, |
| value TEXT NOT NULL, |
| updated_by VARCHAR(50), |
| updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
| ); |
| |
| -- user_last_active_time table |
| CREATE TABLE IF NOT EXISTS user_last_active_time |
| ( |
| uid INT NOT NULL |
| PRIMARY KEY |
| REFERENCES "user"(uid), |
| last_active_time TIMESTAMPTZ |
| ); |
| |
| -- computing_unit_user_access table |
| CREATE TABLE IF NOT EXISTS computing_unit_user_access |
| ( |
| cuid INT NOT NULL, |
| uid INT NOT NULL, |
| privilege privilege_enum NOT NULL DEFAULT 'NONE', |
| PRIMARY KEY (cuid, uid), |
| FOREIGN KEY (cuid) REFERENCES workflow_computing_unit(cuid) ON DELETE CASCADE, |
| FOREIGN KEY (uid) REFERENCES "user"(uid) ON DELETE CASCADE |
| ); |
| |
| -- notebook table |
| CREATE TABLE IF NOT EXISTS notebook |
| ( |
| nid SERIAL NOT NULL PRIMARY KEY, |
| wid INT NOT NULL UNIQUE, |
| notebook JSONB NOT NULL, |
| UNIQUE (wid, nid), |
| FOREIGN KEY (wid) REFERENCES workflow(wid) ON DELETE CASCADE |
| ); |
| |
| -- workflow_notebook_mapping table |
| CREATE TABLE IF NOT EXISTS workflow_notebook_mapping |
| ( |
| wid INT NOT NULL, |
| vid INT NOT NULL, |
| nid INT NOT NULL, |
| mapping JSONB NOT NULL, |
| PRIMARY KEY (wid, vid, nid), |
| FOREIGN KEY (vid) REFERENCES workflow_version(vid) ON DELETE CASCADE, |
| FOREIGN KEY (wid, nid) REFERENCES notebook(wid, nid) ON DELETE CASCADE |
| ); |
| |
| -- START Fulltext search index creation (DO NOT EDIT THIS LINE) |
| CREATE EXTENSION IF NOT EXISTS pgroonga; |
| |
| DO $$ |
| DECLARE |
| r RECORD; |
| stem_filter TEXT := ''; |
| plugin_status TEXT; |
| BEGIN |
| -- Drop all GIN and PGroonga indexes |
| FOR r IN |
| SELECT indexname FROM pg_indexes |
| WHERE (indexdef ILIKE '%USING gin%' OR indexdef ILIKE '%USING pgroonga%') |
| AND tablename IN ('workflow', 'user', 'project', 'dataset', 'dataset_version') |
| LOOP |
| EXECUTE format('DROP INDEX IF EXISTS %I;', r.indexname); |
| END LOOP; |
| |
| -- Check if TokenFilterStem plugin is registered |
| WITH plugin_registration AS ( |
| SELECT pgroonga_command('plugin_register token_filters/stem') AS result |
| ) |
| SELECT |
| CASE |
| WHEN result::jsonb @> '[true]' THEN 'Plugin registered successfully' |
| ELSE 'Plugin registration failed' |
| END INTO plugin_status |
| FROM plugin_registration; |
| |
| -- Set the stem_filter based on plugin status |
| IF plugin_status = 'Plugin registered successfully' THEN |
| stem_filter := ', plugins=''token_filters/stem'', token_filters=''TokenFilterStem'''; |
| RAISE NOTICE 'Using TokenMecab + TokenFilterStem'; |
| ELSE |
| RAISE NOTICE 'Using TokenMecab only'; |
| END IF; |
| |
| -- Create PGroonga indexes dynamically with correct TokenFilterStem usage |
| FOR r IN |
| SELECT tablename, |
| CASE |
| WHEN tablename = 'workflow' THEN |
| '(COALESCE(name, '''') || '' '' || COALESCE(description, '''') || '' '' || COALESCE(content, ''''))' |
| WHEN tablename IN ('project', 'dataset') THEN |
| '(COALESCE(name, '''') || '' '' || COALESCE(description, ''''))' |
| ELSE |
| 'COALESCE(name, '''')' |
| END AS index_column |
| FROM (VALUES ('workflow'), ('user'), ('project'), ('dataset'), ('dataset_version')) AS t(tablename) |
| LOOP |
| -- Create PGroonga index with proper TokenFilterStem usage |
| EXECUTE format( |
| 'CREATE INDEX idx_%s_pgroonga ON %I USING pgroonga (%s) WITH (tokenizer = ''TokenMecab''%s);', |
| r.tablename, r.tablename, r.index_column, stem_filter |
| ); |
| END LOOP; |
| END $$; |
| |
| -- END Fulltext search index creation (DO NOT EDIT THIS LINE) |
