Tizian 「ティツィアーン」
649499be23
Its server, client, app and curator, and the tools that write its assets (maps, Anki fixtures, icons), as they are; paths are fixed in the commits that follow. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com> Signed-off-by: Tizian 「ティツィアーン」 <raisondetredev@pm.me>
388 lines
10 KiB
PL/PgSQL
388 lines
10 KiB
PL/PgSQL
BEGIN;
|
|
|
|
--
|
|
-- Function: gen_random_uuid_v7()
|
|
-- Source: https://gist.github.com/kjmph/5bd772b2c2df145aa645b837da7eca74
|
|
-- License: MIT (copyright notice included on the generator source code).
|
|
--
|
|
create or replace function gen_random_uuid_v7()
|
|
returns uuid
|
|
as $$
|
|
begin
|
|
-- use random v4 uuid as starting point (which has the same variant we need)
|
|
-- then overlay timestamp
|
|
-- then set version 7 by flipping the 2 and 1 bit in the version 4 string
|
|
return encode(
|
|
set_bit(
|
|
set_bit(
|
|
overlay(uuid_send(gen_random_uuid())
|
|
placing substring(int8send(floor(extract(epoch from clock_timestamp()) * 1000)::bigint) from 3)
|
|
from 1 for 6
|
|
),
|
|
52, 1
|
|
),
|
|
53, 1
|
|
),
|
|
'hex')::uuid;
|
|
end
|
|
$$
|
|
language plpgsql
|
|
volatile;
|
|
|
|
--
|
|
-- ACTION CREATE TABLE
|
|
--
|
|
CREATE TABLE "studyset" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"spaceId" bigint,
|
|
"keyId" text,
|
|
"payload" bytea NOT NULL,
|
|
"nonce" bytea
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX "studyset_space_idx" ON "studyset" USING btree ("spaceId");
|
|
|
|
--
|
|
-- ACTION CREATE TABLE
|
|
--
|
|
CREATE TABLE "crdt_data_attempted_value" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"fieldId" bigint NOT NULL,
|
|
"value" jsonb NOT NULL,
|
|
"projectionReason" bigint NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "crdt_data_attempted_value__fieldId__unique_idx" ON "crdt_data_attempted_value" USING btree ("fieldId");
|
|
|
|
--
|
|
-- ACTION CREATE TABLE
|
|
--
|
|
CREATE TABLE "crdt_data_fields" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"hlcDatetime" timestamp without time zone NOT NULL,
|
|
"hlcCounter" bigint NOT NULL,
|
|
"rowId" bigint NOT NULL,
|
|
"columnId" bigint NOT NULL,
|
|
"nodeId" bigint NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "crdt_data_fields_row_column_idx" ON "crdt_data_fields" USING btree ("rowId", "columnId");
|
|
|
|
--
|
|
-- ACTION CREATE TABLE
|
|
--
|
|
CREATE TABLE "crdt_data_rows" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"hlcDatetime" timestamp without time zone NOT NULL,
|
|
"hlcCounter" bigint NOT NULL,
|
|
"spaceId" bigint NOT NULL,
|
|
"tblId" bigint NOT NULL,
|
|
"uuidRowId" uuid NOT NULL,
|
|
"nodeId" bigint NOT NULL,
|
|
"visibility" bigint NOT NULL DEFAULT 0
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "crdt_data_rows_space_tbl_row_idx" ON "crdt_data_rows" USING btree ("spaceId", "tblId", "uuidRowId");
|
|
|
|
--
|
|
-- ACTION CREATE TABLE
|
|
--
|
|
CREATE TABLE "crdt_data_tombstone" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"hlcDatetime" timestamp without time zone NOT NULL,
|
|
"hlcCounter" bigint NOT NULL,
|
|
"rowId" bigint NOT NULL,
|
|
"nodeId" bigint NOT NULL,
|
|
"clFlag" bigint NOT NULL,
|
|
"reason" bigint NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "crdt_data_tombstone_row_idx" ON "crdt_data_tombstone" USING btree ("rowId");
|
|
|
|
--
|
|
-- ACTION CREATE TABLE
|
|
--
|
|
CREATE TABLE "crdt_nodes" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"uuidNodeId" uuid NOT NULL DEFAULT gen_random_uuid_v7(),
|
|
"lastHlc" jsonb
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "crdt_nodes__uuidNodeId__unique_idx" ON "crdt_nodes" USING btree ("uuidNodeId");
|
|
|
|
--
|
|
-- ACTION CREATE TABLE
|
|
--
|
|
CREATE TABLE "crdt_schema_columns" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"tblId" bigint NOT NULL,
|
|
"name" text NOT NULL,
|
|
"columnType" text NOT NULL,
|
|
"dartType" text NOT NULL,
|
|
"isNullable" boolean NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "crdt_schema_columns_table_column_idx" ON "crdt_schema_columns" USING btree ("tblId", "name");
|
|
|
|
--
|
|
-- ACTION CREATE TABLE
|
|
--
|
|
CREATE TABLE "crdt_schema_tables" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"name" text NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "crdt_schema_tables__name__unique_idx" ON "crdt_schema_tables" USING btree ("name");
|
|
|
|
--
|
|
-- ACTION CREATE TABLE
|
|
--
|
|
CREATE TABLE "offline_sync_integrity_violations" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"type" text NOT NULL,
|
|
"domainTableName" text NOT NULL,
|
|
"uuidRowId" uuid NOT NULL,
|
|
"ownerSpaceUuid" uuid,
|
|
"incomingSpaceUuid" uuid NOT NULL,
|
|
"operation" text NOT NULL,
|
|
"uuidNodeId" uuid,
|
|
"crdtDataRowId" bigint,
|
|
"hlcDatetime" timestamp without time zone,
|
|
"hlcCounter" bigint,
|
|
"firstSeenAt" timestamp without time zone NOT NULL,
|
|
"lastSeenAt" timestamp without time zone NOT NULL,
|
|
"occurrences" bigint NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "offline_sync_integrity_violations_key_idx" ON "offline_sync_integrity_violations" USING btree ("type", "operation", "domainTableName", "uuidRowId", "ownerSpaceUuid", "incomingSpaceUuid");
|
|
|
|
--
|
|
-- ACTION CREATE TABLE
|
|
--
|
|
CREATE TABLE "offline_sync_space_members" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"spaceId" bigint NOT NULL,
|
|
"userUuid" uuid NOT NULL,
|
|
"role" text NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "offline_sync_space_member_unique_idx" ON "offline_sync_space_members" USING btree ("userUuid", "spaceId");
|
|
|
|
--
|
|
-- ACTION CREATE TABLE
|
|
--
|
|
CREATE TABLE "offline_sync_space_nodes" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"spaceId" bigint NOT NULL,
|
|
"nodeId" bigint NOT NULL,
|
|
"lastReceivedHlc" jsonb
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "offline_sync_space_node_unique_idx" ON "offline_sync_space_nodes" USING btree ("spaceId", "nodeId");
|
|
|
|
--
|
|
-- ACTION CREATE TABLE
|
|
--
|
|
CREATE TABLE "offline_sync_spaces" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"uuidSpaceId" uuid NOT NULL DEFAULT gen_random_uuid_v7(),
|
|
"currentNodeId" bigint
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "offline_sync_spaces__uuidSpaceId__unique_idx" ON "offline_sync_spaces" USING btree ("uuidSpaceId");
|
|
|
|
--
|
|
-- ACTION ALTER TABLE
|
|
--
|
|
ALTER TABLE "serverpod_auth_core_profile" DROP CONSTRAINT IF EXISTS "serverpod_auth_core_profile_fk_1";
|
|
--
|
|
-- ACTION CREATE FOREIGN KEY
|
|
--
|
|
ALTER TABLE ONLY "studyset"
|
|
ADD CONSTRAINT "studyset_fk_0"
|
|
FOREIGN KEY("spaceId")
|
|
REFERENCES "offline_sync_spaces"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- ACTION CREATE FOREIGN KEY
|
|
--
|
|
ALTER TABLE ONLY "crdt_data_attempted_value"
|
|
ADD CONSTRAINT "crdt_data_attempted_value_fk_0"
|
|
FOREIGN KEY("fieldId")
|
|
REFERENCES "crdt_data_fields"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- ACTION CREATE FOREIGN KEY
|
|
--
|
|
ALTER TABLE ONLY "crdt_data_fields"
|
|
ADD CONSTRAINT "crdt_data_fields_fk_0"
|
|
FOREIGN KEY("rowId")
|
|
REFERENCES "crdt_data_rows"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
ALTER TABLE ONLY "crdt_data_fields"
|
|
ADD CONSTRAINT "crdt_data_fields_fk_1"
|
|
FOREIGN KEY("columnId")
|
|
REFERENCES "crdt_schema_columns"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
ALTER TABLE ONLY "crdt_data_fields"
|
|
ADD CONSTRAINT "crdt_data_fields_fk_2"
|
|
FOREIGN KEY("nodeId")
|
|
REFERENCES "crdt_nodes"("id")
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- ACTION CREATE FOREIGN KEY
|
|
--
|
|
ALTER TABLE ONLY "crdt_data_rows"
|
|
ADD CONSTRAINT "crdt_data_rows_fk_0"
|
|
FOREIGN KEY("spaceId")
|
|
REFERENCES "offline_sync_spaces"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
ALTER TABLE ONLY "crdt_data_rows"
|
|
ADD CONSTRAINT "crdt_data_rows_fk_1"
|
|
FOREIGN KEY("tblId")
|
|
REFERENCES "crdt_schema_tables"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
ALTER TABLE ONLY "crdt_data_rows"
|
|
ADD CONSTRAINT "crdt_data_rows_fk_2"
|
|
FOREIGN KEY("nodeId")
|
|
REFERENCES "crdt_nodes"("id")
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- ACTION CREATE FOREIGN KEY
|
|
--
|
|
ALTER TABLE ONLY "crdt_data_tombstone"
|
|
ADD CONSTRAINT "crdt_data_tombstone_fk_0"
|
|
FOREIGN KEY("rowId")
|
|
REFERENCES "crdt_data_rows"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
ALTER TABLE ONLY "crdt_data_tombstone"
|
|
ADD CONSTRAINT "crdt_data_tombstone_fk_1"
|
|
FOREIGN KEY("nodeId")
|
|
REFERENCES "crdt_nodes"("id")
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- ACTION CREATE FOREIGN KEY
|
|
--
|
|
ALTER TABLE ONLY "crdt_schema_columns"
|
|
ADD CONSTRAINT "crdt_schema_columns_fk_0"
|
|
FOREIGN KEY("tblId")
|
|
REFERENCES "crdt_schema_tables"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- ACTION CREATE FOREIGN KEY
|
|
--
|
|
ALTER TABLE ONLY "offline_sync_space_members"
|
|
ADD CONSTRAINT "offline_sync_space_members_fk_0"
|
|
FOREIGN KEY("spaceId")
|
|
REFERENCES "offline_sync_spaces"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- ACTION CREATE FOREIGN KEY
|
|
--
|
|
ALTER TABLE ONLY "offline_sync_space_nodes"
|
|
ADD CONSTRAINT "offline_sync_space_nodes_fk_0"
|
|
FOREIGN KEY("spaceId")
|
|
REFERENCES "offline_sync_spaces"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
ALTER TABLE ONLY "offline_sync_space_nodes"
|
|
ADD CONSTRAINT "offline_sync_space_nodes_fk_1"
|
|
FOREIGN KEY("nodeId")
|
|
REFERENCES "crdt_nodes"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- ACTION CREATE FOREIGN KEY
|
|
--
|
|
ALTER TABLE ONLY "offline_sync_spaces"
|
|
ADD CONSTRAINT "offline_sync_spaces_fk_0"
|
|
FOREIGN KEY("currentNodeId")
|
|
REFERENCES "crdt_nodes"("id")
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- ACTION CREATE FOREIGN KEY
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_core_profile"
|
|
ADD CONSTRAINT "serverpod_auth_core_profile_fk_1"
|
|
FOREIGN KEY("imageId")
|
|
REFERENCES "serverpod_auth_core_profile_image"("id")
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION
|
|
DEFERRABLE INITIALLY DEFERRED;
|
|
|
|
--
|
|
-- MIGRATION VERSION FOR halkyon_learn
|
|
--
|
|
INSERT INTO "serverpod_migrations" ("module", "version", "timestamp")
|
|
VALUES ('halkyon_learn', '20260930094729362-studysets', now())
|
|
ON CONFLICT ("module")
|
|
DO UPDATE SET "version" = '20260930094729362-studysets', "timestamp" = now();
|
|
|
|
--
|
|
-- MIGRATION VERSION FOR serverpod
|
|
--
|
|
INSERT INTO "serverpod_migrations" ("module", "version", "timestamp")
|
|
VALUES ('serverpod', '20260824182259319', now())
|
|
ON CONFLICT ("module")
|
|
DO UPDATE SET "version" = '20260824182259319', "timestamp" = now();
|
|
|
|
--
|
|
-- MIGRATION VERSION FOR serverpod_auth_idp
|
|
--
|
|
INSERT INTO "serverpod_migrations" ("module", "version", "timestamp")
|
|
VALUES ('serverpod_auth_idp', '20260924105404509', now())
|
|
ON CONFLICT ("module")
|
|
DO UPDATE SET "version" = '20260924105404509', "timestamp" = now();
|
|
|
|
--
|
|
-- MIGRATION VERSION FOR serverpod_offline_sync
|
|
--
|
|
INSERT INTO "serverpod_migrations" ("module", "version", "timestamp")
|
|
VALUES ('serverpod_offline_sync', '20260914143806119', now())
|
|
ON CONFLICT ("module")
|
|
DO UPDATE SET "version" = '20260914143806119', "timestamp" = now();
|
|
|
|
--
|
|
-- MIGRATION VERSION FOR serverpod_auth_core
|
|
--
|
|
INSERT INTO "serverpod_migrations" ("module", "version", "timestamp")
|
|
VALUES ('serverpod_auth_core', '20260924105232991', now())
|
|
ON CONFLICT ("module")
|
|
DO UPDATE SET "version" = '20260924105232991', "timestamp" = now();
|
|
|
|
|
|
COMMIT;
|