path: root/tests/queries.sql

-- createTables.sql:
--   write:
		-- 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,
			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')),
			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           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')),
			uuid         BLOB    NOT NULL UNIQUE,
			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 (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           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 (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             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 "papod_members"(id),
			role         TEXT    NOT NULL,
			UNIQUE (member_id, role)
		) 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 (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           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 (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			uuid         BLOB    NOT NULL UNIQUE,
			network_id   INTEGER -- FIXME NOT NULL
				REFERENCES "papod_networks"(id),
			public_name  TEXT             UNIQUE,
			label        TEXT    NOT NULL,
			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 (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			channel_id   INTEGER NOT NULL
				REFERENCES "papod_channels"(id),
			attribute    TEXT    NOT NULL,
			value        TEXT    NOT NULL,
			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 (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			uuid         BLOB    NOT NULL UNIQUE,
			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:

-- 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:
--   write:

--    read:
		-- FIXME
	%!(EXTRA string=papod)

-- topic.sql:
--   write:
	%!(EXTRA string=papod)

--    read:

-- endChannel.sql:
--   write:
	%!(EXTRA string=papod)

--    read:

-- join.sql:
--   write:
		-- FIXME
	%!(EXTRA string=papod)

--    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
			);