-- 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 CHECK(deleted IN (0, 1)) ) 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 database 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 "papod_members" ( network_id, user_id, username, display_name, picture_uuid, status, active_uniq ) VALUES ( last_insert_rowid(), ?, ( SELECT username, display_name, picture_uuid FROM "papod_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 papod -- setNetwork.sql: -- write: -- FIXME papod -- read: -- nipNetwork.sql: -- write: -- FIXME papod -- read: -- addMember.sql: -- write: -- FIXME papod -- read: -- showMember.sql: -- write: -- read: -- FIXME papod -- members.sql: -- write: -- read: -- FIXME papod -- editMember.sql: -- write: -- FIXME papod -- read: -- dropMember.sql: -- write: -- FIXME -- read: -- addChannel.sql: -- write: INSERT INTO "papod_channels" ( uuid, public_name, label, description, virtual ) VALUES (?, ?, ?, ?, ?) RETURNING id, timestamp; -- read: -- channels.sql: -- write: -- read: -- FIXME papod -- topic.sql: -- write: -- FIXME papod -- read: -- endChannel.sql: -- write: -- FIXME papod -- read: -- join.sql: -- write: -- FIXME papod -- read: -- part.sql: -- write: -- FIXME papod -- read: -- names.sql: -- write: -- read: -- FIXME 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 ); -- logMessage.sql: -- write: -- FIXME papod -- read: