qbit-sql-workbench/docs/ddl/postgres.sql

-- SQL Workbench QBit (com.kingsrook.qbits:qbit-sql-workbench) - PostgreSQL DDL
--
-- Table and column names are the snake_case forms of the QQQ names, which is
-- what a host gets when its tableMetaDataCustomizer applies
-- QInstanceEnricher.inferBackendName to the table name and
-- QInstanceEnricher.setInferredFieldBackendNames to the fields (see README,
-- "Storage"). With a tableNamePrefix, prefix the table names the same way.
--
-- Liquibase: paste into a formatted-SQL changeset, e.g.
--   -- liquibase formatted sql
--   -- changeset you:NNNN-1
-- Idempotent (IF NOT EXISTS), so it is safe on re-run.

CREATE TABLE IF NOT EXISTS sql_workbench_notebook (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   title VARCHAR(255) NOT NULL,
   description TEXT,
   owner_user_id VARCHAR(255) NOT NULL,
   owner_name VARCHAR(255),
   visibility VARCHAR(16) NOT NULL DEFAULT 'PERSONAL',
   default_backend_name VARCHAR(100),
   parameters_json TEXT,
   current_version INTEGER,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_sql_workbench_notebook PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS ix_sql_workbench_notebook_owner ON sql_workbench_notebook (owner_user_id);
CREATE INDEX IF NOT EXISTS ix_sql_workbench_notebook_visibility ON sql_workbench_notebook (visibility);

CREATE TABLE IF NOT EXISTS sql_workbench_cell (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   notebook_id INTEGER NOT NULL,
   cell_key VARCHAR(64) NOT NULL,
   sort_order INTEGER,
   cell_type VARCHAR(16) NOT NULL,
   title VARCHAR(255),
   content TEXT,
   backend_name VARCHAR(100),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_sql_workbench_cell PRIMARY KEY (id),
   CONSTRAINT uq_sql_workbench_cell_key UNIQUE (notebook_id, cell_key),
   CONSTRAINT fk_sql_workbench_cell_notebook FOREIGN KEY (notebook_id) REFERENCES sql_workbench_notebook (id)
);

CREATE TABLE IF NOT EXISTS sql_workbench_notebook_version (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   notebook_id INTEGER NOT NULL,
   version_number INTEGER NOT NULL,
   content_json TEXT,
   saved_by_user_id VARCHAR(255),
   saved_by_name VARCHAR(255),
   comment VARCHAR(255),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_sql_workbench_notebook_version PRIMARY KEY (id),
   CONSTRAINT uq_sql_workbench_notebook_version UNIQUE (notebook_id, version_number),
   CONSTRAINT fk_sql_workbench_notebook_version_notebook FOREIGN KEY (notebook_id) REFERENCES sql_workbench_notebook (id)
);

CREATE TABLE IF NOT EXISTS sql_workbench_result_snapshot (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   notebook_id INTEGER NOT NULL,
   cell_key VARCHAR(64),
   execution_uuid VARCHAR(36),
   label VARCHAR(255),
   backend_name VARCHAR(100),
   sql_text TEXT,
   result_json TEXT,
   row_count INTEGER,
   truncated BOOLEAN,
   created_by_user_id VARCHAR(255),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_sql_workbench_result_snapshot PRIMARY KEY (id),
   CONSTRAINT fk_sql_workbench_result_snapshot_notebook FOREIGN KEY (notebook_id) REFERENCES sql_workbench_notebook (id)
);
-- the notebook page lists its snapshots, and deleting a notebook deletes them, by notebook_id
CREATE INDEX IF NOT EXISTS ix_sql_workbench_result_snapshot_notebook ON sql_workbench_result_snapshot (notebook_id);

-- Shares (1.0.0-RC.2): who, besides the owner, may read a notebook.
-- audience_key is "user:<id>", "group:<name>" or "all" (the shared library);
-- the record security locks match it against the session's audience keys.
CREATE TABLE IF NOT EXISTS sql_workbench_notebook_share (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   notebook_id INTEGER NOT NULL,
   audience_type VARCHAR(16) NOT NULL,
   audience_id VARCHAR(255),
   audience_key VARCHAR(300) NOT NULL,
   created_by_user_id VARCHAR(255),
   created_by_name VARCHAR(255),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_sql_workbench_notebook_share PRIMARY KEY (id),
   CONSTRAINT uq_sql_workbench_notebook_share UNIQUE (notebook_id, audience_key),
   CONSTRAINT fk_sql_workbench_notebook_share_notebook FOREIGN KEY (notebook_id) REFERENCES sql_workbench_notebook (id)
);
CREATE INDEX IF NOT EXISTS ix_sql_workbench_notebook_share_audience ON sql_workbench_notebook_share (audience_key, notebook_id);

-- Upgrading from 1.0.0-RC.1: notebooks published to the library
-- (visibility SHARED) get their "all" share row. Idempotent.
INSERT INTO sql_workbench_notebook_share (notebook_id, audience_type, audience_id, audience_key, created_by_user_id, created_by_name, create_date, modify_date)
SELECT n.id, 'ALL', NULL, 'all', n.owner_user_id, n.owner_name, COALESCE(n.modify_date, CURRENT_TIMESTAMP), COALESCE(n.modify_date, CURRENT_TIMESTAMP)
FROM sql_workbench_notebook n
WHERE n.visibility = 'SHARED'
  AND NOT EXISTS (SELECT 1 FROM sql_workbench_notebook_share s WHERE s.notebook_id = n.id AND s.audience_key = 'all');

-- Audit: append-only. Not foreign-keyed to notebooks, so deleting a
-- notebook never deletes its audit trail.
CREATE TABLE IF NOT EXISTS sql_workbench_execution (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   execution_uuid VARCHAR(36) NOT NULL,
   user_id VARCHAR(255),
   user_name VARCHAR(255),
   backend_name VARCHAR(100),
   mode VARCHAR(24),
   status VARCHAR(16),
   sql_text TEXT,
   sql_hash VARCHAR(64),
   parameters_json TEXT,
   statement_count INTEGER,
   statement_kinds VARCHAR(255),
   notebook_id INTEGER,
   cell_key VARCHAR(64),
   start_time TIMESTAMP WITHOUT TIME ZONE,
   end_time TIMESTAMP WITHOUT TIME ZONE,
   duration_ms INTEGER,
   rows_returned INTEGER,
   rows_affected INTEGER,
   truncated BOOLEAN,
   error_message TEXT,
   client_ip VARCHAR(64),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_sql_workbench_execution PRIMARY KEY (id),
   CONSTRAINT uq_sql_workbench_execution_uuid UNIQUE (execution_uuid)
);
CREATE INDEX IF NOT EXISTS ix_sql_workbench_execution_user ON sql_workbench_execution (user_id, id);
CREATE INDEX IF NOT EXISTS ix_sql_workbench_execution_start ON sql_workbench_execution (start_time);
-- the notebook page's "Runs" list joins executions by notebook_id
CREATE INDEX IF NOT EXISTS ix_sql_workbench_execution_notebook ON sql_workbench_execution (notebook_id);