summaryrefslogtreecommitdiff
path: root/tests/queries.sql
diff options
context:
space:
mode:
Diffstat (limited to 'tests/queries.sql')
-rw-r--r--tests/queries.sql487
1 files changed, 414 insertions, 73 deletions
diff --git a/tests/queries.sql b/tests/queries.sql
index fe67f60..3aa6586 100644
--- a/tests/queries.sql
+++ b/tests/queries.sql
@@ -1,125 +1,404 @@
-- createTables.sql:
-- write:
- CREATE TABLE IF NOT EXISTS "papod_workspaces" (
+ -- FIXME: unconfirmed premise: statements within a trigger are
+ -- part of the transaction that caused it, and so are
+ -- atomic.
+ -- See also:
+ -- https://stackoverflow.com/questions/77441888/
+ -- https://stackoverflow.com/questions/30511116/
+
+ CREATE TABLE IF NOT EXISTS "papod_users" (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
+ -- provided by cracha
uuid BLOB NOT NULL UNIQUE,
- name TEXT NOT NULL,
- description TEXT NOT NULL,
- -- "public", "private", "unlisted"
- type TEXT NOT NULL
- );
- CREATE TABLE IF NOT EXISTS "papod_workspace_changes (
+ username TEXT NOT NULL,
+ display_name TEXT NOT NULL,
+ picture_uuid BLOB UNIQUE,
+ deleted INT NOT NULL
+ ) STRICT;
+ CREATE TABLE IF NOT EXISTS "papod_user_changes" (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
- workspace_id INTEGER NOT NULL
- REFERENCES "papod_workspaces"(id),
- attribute TEXT NOT NULL,
+ user_id INTEGER NOT NULL REFERENCES "papod_users"(id),
+ attribute TEXT NOT NULL CHECK(
+ attribute IN (
+ 'username',
+ 'display_name',
+ 'picture_uuid',
+ 'deleted'
+ )
+ ),
value TEXT NOT NULL,
- op BOOLEAN NOT NULL
- );
- CREATE TABLE IF NOT EXISTS "papod_users" (
+ op INT NOT NULL CHECK(op IN (0, 1))
+ ) STRICT;
+ CREATE TRIGGER IF NOT EXISTS "papod_user_creation"
+ AFTER INSERT ON "papod_users"
+ BEGIN
+ INSERT INTO "papod_user_changes" (
+ user_id, attribute, value, op
+ ) VALUES
+ (NEW.id, 'username', NEW.username, true),
+ (NEW.id, 'display_name', NEW.display_name, true),
+ (NEW.id, 'deleted', NEW.deleted, true)
+ ;
+ END;
+ CREATE TRIGGER IF NOT EXISTS "papod_user_creation_picture_uuid"
+ AFTER INSERT ON "papod_users"
+ WHEN NEW.picture_uuid != NULL
+ BEGIN
+ INSERT INTO "papod_user_changes" (
+ user_id, attribute, value, op
+ ) VALUES
+ (NEW.id, 'picture_uuid', NEW.picture_uuid, true)
+ ;
+ END;
+ CREATE TRIGGER IF NOT EXISTS "papod_user_update_username"
+ AFTER UPDATE ON "papod_users"
+ WHEN OLD.username != NEW.username
+ BEGIN
+ INSERT INTO "papod_user_changes" (
+ user_id, attribute, value, op
+ ) VALUES
+ (NEW.id, 'username', OLD.username, false),
+ (NEW.id, 'username', NEW.username, true)
+ ;
+ END;
+ CREATE TRIGGER IF NOT EXISTS "papod_user_update_display_name"
+ AFTER UPDATE ON "papod_users"
+ WHEN OLD.display_name != NEW.display_name
+ BEGIN
+ INSERT INTO "papod_user_changes" (
+ user_id, attribute, value, op
+ ) VALUES
+ (NEW.id, 'display_name', OLD.display_name, false),
+ (NEW.id, 'display_name', NEW.display_name, true)
+ ;
+ END;
+ CREATE TRIGGER IF NOT EXISTS "papod_user_update_picture_uuid"
+ AFTER UPDATE ON "papod_users"
+ WHEN OLD.picture_uuid != NEW.picture_uuid
+ BEGIN
+ INSERT INTO "papod_user_changes" (
+ user_id, attribute, value, op
+ ) VALUES
+ (NEW.id, 'picture_uuid', OLD.picture_uuid, false),
+ (NEW.id, 'picture_uuid', NEW.picture_uuid, true)
+ ;
+ END;
+ CREATE TRIGGER IF NOT EXISTS "papod_user_update_deleted"
+ AFTER UPDATE ON "papod_users"
+ WHEN OLD.deleted != NEW.deleted
+ BEGIN
+ INSERT INTO "papod_user_changes" (
+ user_id, attribute, value, op
+ ) VALUES
+ (NEW.id, 'deleted', OLD.deleted, false),
+ (NEW.id, 'deleted', NEW.deleted, true)
+ ;
+ END;
+
+ CREATE TABLE IF NOT EXISTS "papod_networks" (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
- -- provided by cracha
uuid BLOB NOT NULL UNIQUE,
- username TEXT NOT NULL,
- display_name TEXT NOT NULL,
- picture_uuid BLOB NOT NULL UNIQUE,
- deleted BOOLEAN NOT NULL
- );
- CREATE TABLE IF NOT EXISTS "papod_user_changes" (
+ creator_id INTEGER NOT NULL REFERENCES "papod_users"(id),
+ name TEXT NOT NULL,
+ description TEXT NOT NULL,
+ type TEXT NOT NULL CHECK(
+ type IN ('public', 'private', 'unlisted')
+ )
+ ) STRICT;
+ CREATE TABLE IF NOT EXISTS "papod_network_changes" (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
- timestamp TEXT NOT NULL DEFAULT (papod),
- user_id INTEGER NOT NULL REFERENCES "strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')_users"(id),
- attribute TEXT NOT NULL,
+ timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
+ network_id INTEGER NOT NULL
+ REFERENCES "papod_networks"(id),
+ attribute TEXT NOT NULL CHECK(
+ attribute IN (
+ 'name',
+ 'description',
+ 'type'
+ )
+ ),
value TEXT NOT NULL,
- op BOOLEAN NOT NULL
- );
+ op INT NOT NULL CHECK(op IN (0, 1))
+ ) STRICT;
+
CREATE TABLE IF NOT EXISTS "papod_members" (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
- timestamp TEXT NOT NULL DEFAULT (%!s(MISSING)),
- workspace_id INTEGER NOT NULL
- REFERENCES "%!s(MISSING)_workspaces"(id),
+ timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
+ network_id INTEGER NOT NULL
+ REFERENCES "papod_networks"(id),
user_id INTEGER NOT NULL,
username TEXT NOT NULL,
display_name TEXT NOT NULL,
- picture_uuid BLOB NOT NULL UNIQUE,
- -- "active", "inactive", "removed"
- status TEXT NOT NULL,
- -- "active", always
- active_uniq TEXT,
- UNIQUE (workspace_id, username, active_uniq),
- UNIQUE (workspace_id, user_id)
- );
- CREATE TABLE IF NOT EXISTS "%!s(MISSING)_member_roles" (
+ picture_uuid BLOB UNIQUE,
+ status TEXT NOT NULL CHECK(
+ status IN ('active', 'inactive', 'removed')
+ ),
+ active_uniq TEXT CHECK(
+ active_uniq IN ('active', NULL)
+ ),
+ UNIQUE (network_id, username, active_uniq),
+ UNIQUE (network_id, user_id)
+ ) STRICT;
+
+ CREATE TABLE IF NOT EXISTS "papod_member_roles" (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
member_id INTEGER NOT NULL
- REFERENCES "%!s(MISSING)_members"(id),
+ REFERENCES "papod_members"(id),
role TEXT NOT NULL,
UNIQUE (member_id, role)
- );
- CREATE TABLE IF NOT EXISTS "%!s(MISSING)_member_changes" (
+ ) STRICT;
+
+ -- FIXME: use a trigger
+ CREATE TABLE IF NOT EXISTS "papod_member_changes" (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
- timestamp TEXT NOT NULL DEFAULT (%!s(MISSING)),
- member_id INTEGER NOT NULL
- REFERENCES "%!s(MISSING)_members"(id),
+ timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
+ member_id INTEGER NOT NULL
+ REFERENCES "papod_members"(id),
attribute TEXT NOT NULL,
value TEXT NOT NULL,
- op BOOLEAN NOT NULL
- );
- CREATE TABLE IF NOT EXISTS "%!s(MISSING)_channels" (
+ op INT NOT NULL CHECK(op IN (0, 1))
+ ) STRICT;
+
+ CREATE TABLE IF NOT EXISTS "papod_channels" (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
- timestamp TEXT NOT NULL DEFAULT (%!s(MISSING)),
+ timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
uuid BLOB NOT NULL UNIQUE,
- workspace_id INTEGER NOT NULL
- REFERENCES "%!s(MISSING)_workspaces"(id),
+ network_id INTEGER -- FIXME NOT NULL
+ REFERENCES "papod_networks"(id),
public_name TEXT UNIQUE,
label TEXT NOT NULL,
- description TEXT,
- virtual BOOLEAN NOT NULL
- );
- CREATE TABLE IF NOT EXISTS "%!s(MISSING)_channel_changes" (
+ description TEXT NOT NULL,
+ virtual INT NOT NULL CHECK(virtual IN (0, 1))
+ ) STRICT;
+
+ -- FIXME: use a trigger
+ CREATE TABLE IF NOT EXISTS "papod_channel_changes" (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
- timestamp TEXT NOT NULL DEFAULT (%!s(MISSING)),
+ timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
channel_id INTEGER NOT NULL
- REFERENCES "%!s(MISSING)_channels"(id),
+ REFERENCES "papod_channels"(id),
attribute TEXT NOT NULL,
value TEXT NOT NULL,
- op BOOLEAN NOT NULL
- );
- CREATE TABLE IF NOT EXISTS "%!s(MISSING)_participants" (
- member_id
- );
- CREATE TABLE IF NOT EXISTS "%!s(MISSING)_channel_events" (
+ op INT NOT NULL CHECK(op IN (0, 1))
+ ) STRICT;
+
+ CREATE TABLE IF NOT EXISTS "papod_participants" (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ channel_id INTEGER NOT NULL
+ REFERENCES "papod_channels"(id),
+ member_id INTEGER NOT NULL
+ REFERENCES "papod_members"(id),
+ UNIQUE (channel_id, member_id)
+ ) STRICT;
+
+ -- FIXME: create table for connections?
+ -- A user can have multiple sessions (different browsers,
+ -- mobile, etc.), and each session has multiple connections, as
+ -- the user connects and disconnections using the same session
+ -- id, all while it is valid.
+ -- FIXME: can a connection have multiple sessions? A long-lived
+ -- connection that spans multiple sessions would fit into this.
+ CREATE TABLE IF NOT EXISTS "papod_channel_events" (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
- timestamp TEXT NOT NULL DEFAULT (%!s(MISSING)),
+ timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
uuid BLOB NOT NULL UNIQUE,
- channel_id INTEGER NOT NULL REFERENCES "%!s(MISSING)"(id),
- connection_id INTEGER NOT NULL,
- -- payload FIXME: vary by type?
- );
- -- FIXME: group conversations?
- -- user: person
- -- member: workspace user
- -- participant: channel member
+ channel_id INTEGER NOT NULL
+ REFERENCES "papod_channels"(id),
+ connection_uuid BLOB NOT NULL, -- FIXME: join
+ type TEXT NOT NULL CHECK(
+ type IN (
+ 'user-join',
+ 'user-message'
+ )
+ ),
+ payload TEXT NOT NULL
+ ) STRICT;
+
+
+-- read:
+
+-- createUser.sql:
+-- write:
+ INSERT INTO "papod_users" (
+ uuid, username, display_name, picture_uuid, deleted
+ ) VALUES (
+ ?, ?, ?, NULL, false
+ ) RETURNING id, timestamp;
+
+
+-- read:
+
+-- userByUUID.sql:
+-- write:
+
+-- read:
+ SELECT
+ id,
+ timestamp,
+ username,
+ display_name,
+ picture_uuid
+ FROM "papod_users"
+ WHERE
+ uuid = ? AND
+ deleted = false;
+
+
+-- updateUser.sql:
+-- write:
+ UPDATE "papod_users"
+ SET
+ username = ?,
+ display_name = ?,
+ picture_uuid = ?
+ WHERE
+ id = ? AND
+ deleted = false
+ RETURNING id;
+
+
+-- read:
+
+-- deleteUser.sql:
+-- write:
+ UPDATE "papod_users"
+ SET deleted = true
+ WHERE
+ uuid = ? AND
+ deleted = false
+ RETURNING id;
-- read:
-- addNetwork.sql:
-- write:
+ INSERT INTO "papod_networks" (
+ uuid, name, description, type, creator_id
+ )
+ VALUES (
+ ?,
+ ?,
+ ?,
+ ?,
+ (
+ SELECT id FROM "papod_users"
+ WHERE id = ? AND deleted = false
+ )
+ ) RETURNING id, timestamp;
+
+ INSERT INTO "%!s(MISSING)_members" (
+ network_id, user_id, username, display_name,
+ picture_uuid, status, active_uniq
+ ) VALUES (
+ last_insert_rowid(),
+ ?,
+ (
+ SELECT username, display_name, picture_uuid
+ FROM "%!s(MISSING)_users"
+ WHERE id = ? AND deleted = false
+ ),
+ 'active',
+ 'active'
+ ) RETURNING id, timestamp;
+
+
+-- read:
+
+-- getNetwork.sql:
+-- write:
+
+-- read:
+ SELECT
+ "papod_networks".id,
+ "papod_networks".timestamp,
+ "papod_users".uuid,
+ "papod_networks".name,
+ "papod_networks".description,
+ "papod_networks".type
+ FROM "papod_networks"
+ JOIN "papod_users" ON
+ "papod_users".id = "papod_networks".creator_id
+ WHERE
+ "papod_networks".uuid = $networkUUID AND
+ $userID IN (
+ SELECT id FROM "papod_users"
+ WHERE id = $userID AND deleted = false
+ ) AND
+ (
+ "papod_networks".type IN ('public', 'unlisted') OR
+ $userID IN (
+ SELECT user_id FROM "papod_members"
+ WHERE
+ user_id = $userID AND
+ network_id = "papod_networks".id
+ )
+ );
+
+
+-- networks.sql:
+-- write:
+
+-- read:
-- FIXME
%!(EXTRA string=papod)
+-- setNetwork.sql:
+-- write:
+ %!(EXTRA string=papod)
+
-- read:
--- addChannel.sql:
+-- nipNetwork.sql:
+-- write:
+ %!(EXTRA string=papod)
+
+-- read:
+
+-- addMember.sql:
+-- write:
+ -- FIXME
+
+
+-- read:
+
+-- showMember.sql:
-- write:
+
+-- read:
+ %!(EXTRA string=papod)
+
+-- members.sql:
+-- write:
+
+-- read:
-- FIXME
%!(EXTRA string=papod)
+-- editMember.sql:
+-- write:
+ %!(EXTRA string=papod)
+
+-- read:
+
+-- dropMember.sql:
+-- write:
+
+
+-- read:
+
+-- addChannel.sql:
+-- write:
+ INSERT INTO "papod_channels" (
+ uuid, public_name, label, description, virtual
+ ) VALUES (?, ?, ?, ?, ?) RETURNING id, timestamp;
+
+
-- read:
-- channels.sql:
@@ -129,16 +408,78 @@
-- FIXME
%!(EXTRA string=papod)
--- allAfter.sql:
+-- topic.sql:
-- write:
+ %!(EXTRA string=papod)
-- read:
+
+-- endChannel.sql:
+-- write:
+ %!(EXTRA string=papod)
+
+-- read:
+
+-- join.sql:
+-- write:
-- FIXME
%!(EXTRA string=papod)
--- addEvent.sql:
+-- read:
+
+-- part.sql:
+-- write:
+ -- FIXME
+ %!(EXTRA string=papod)
+
+-- read:
+
+-- names.sql:
-- write:
+
+-- read:
-- FIXME
%!(EXTRA string=papod)
+-- addEvent.sql:
+-- write:
+ INSERT INTO "papod_channel_events" (
+ uuid, channel_id, connection_uuid, type, payload
+ ) VALUES (
+ ?,
+ (SELECT id FROM "papod_channels" WHERE uuid = ?),
+ ?,
+ ?,
+ ?
+ ) RETURNING id, timestamp;
+
+
+-- read:
+
+-- allAfter.sql:
+-- write:
+
-- read:
+ WITH landmark_event AS (
+ SELECT id, channel_id
+ FROM "papod_channel_events"
+ WHERE uuid = ?
+ )
+ SELECT
+ "papod_channel_events".id,
+ "papod_channel_events".timestamp,
+ "papod_channel_events".uuid,
+ "papod_channels".uuid,
+ "papod_channel_events".connection_uuid,
+ "papod_channel_events".type,
+ "papod_channel_events".payload
+ FROM "papod_channel_events"
+ JOIN "papod_channels" ON
+ "papod_channel_events".channel_id = "papod_channels".id
+ WHERE
+ "papod_channel_events".id > (
+ SELECT id FROM landmark_event
+ ) AND channel_id = (
+ SELECT channel_id FROM landmark_event
+ );
+
generated by cgit v1.2.3 (git 2.49.0) at 2025-07-06 10:48:24 +0000