From 985430232e659e5d501a81a0ef74e563e5b9009a Mon Sep 17 00:00:00 2001
From: EuAndreh <eu@euandre.org>
Date: Thu, 31 Oct 2024 15:50:56 -0300
Subject: Add initial implementation of some of `queriesT` functions

---
 tests/queries.sql | 487 ++++++++++++++++++++++++++++++++++++++++++++++--------
 1 file changed, 414 insertions(+), 73 deletions(-)

(limited to 'tests/queries.sql')

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
+			);
+	
-- 
cgit v1.2.3

