diff options
Diffstat (limited to 'tests/queries.sql')
| -rw-r--r-- | tests/queries.sql | 1081 |
1 files changed, 906 insertions, 175 deletions
diff --git a/tests/queries.sql b/tests/queries.sql index c996f02..992c8d2 100644 --- a/tests/queries.sql +++ b/tests/queries.sql @@ -1,134 +1,271 @@ -- createTables.sql: -- write: - -- FIXME: unconfirmed premise: statements within a trigger are - -- part of the transaction that caused it, and so are - -- atomic. + -- TODO: 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')), + timestamp TEXT NOT NULL DEFAULT ( + strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now') + ), -- provided by cracha - uuid BLOB NOT NULL UNIQUE, + user_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_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, + attribute TEXT NOT NULL CHECK( + attribute IN ( + 'username', + 'display_name', + 'picture_uuid', + 'deleted' + ) + ), + value_text TEXT, + value_blob BLOB, + value_bool INT CHECK(value_bool IN (0, 1)), + op INT NOT NULL CHECK(op IN (0, 1)) + ) STRICT; + CREATE TRIGGER IF NOT EXISTS "papod_user_new" + AFTER INSERT ON "papod_users" + BEGIN + INSERT INTO "papod_user_changes" ( + user_id, attribute, value_text, op + ) VALUES + (NEW.id, 'username', NEW.username, true), + (NEW.id, 'display_name', NEW.display_name, true) + ; + INSERT INTO "papod_user_changes" ( + user_id, attribute, value_bool, op + ) VALUES + (NEW.id, 'deleted', NEW.deleted, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_user_new_picture_uuid" + AFTER INSERT ON "papod_users" + WHEN NEW.picture_uuid IS NOT NULL + BEGIN + INSERT INTO "papod_user_changes" ( + user_id, attribute, value_blob, 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_text, 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_text, 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_add_picture_uuid" + AFTER UPDATE ON "papod_users" + WHEN ( + OLD.picture_uuid IS NULL AND + NEW.picture_uuid IS NOT NULL + ) + BEGIN + INSERT INTO "papod_user_changes" ( + user_id, attribute, value_blob, op + ) VALUES + (NEW.id, 'picture_uuid', NEW.picture_uuid, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_user_remove_picture_uuid" + AFTER UPDATE ON "papod_users" + WHEN ( + OLD.picture_uuid IS NOT NULL AND + NEW.picture_uuid IS NULL + ) + BEGIN + INSERT INTO "papod_user_changes" ( + user_id, attribute, value_blob, op + ) VALUES + (NEW.id, 'picture_uuid', OLD.picture_uuid, false) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_user_update_picture_uuid" + AFTER UPDATE ON "papod_users" + WHEN ( + OLD.picture_uuid IS NOT NULL AND + NEW.picture_uuid IS NOT NULL AND + OLD.picture_uuid != NEW.picture_uuid + ) + BEGIN + INSERT INTO "papod_user_changes" ( + user_id, attribute, value_blob, 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_bool, op + ) VALUES + (NEW.id, 'deleted', OLD.deleted, false), + (NEW.id, 'deleted', NEW.deleted, true) + ; + END; + + CREATE TABLE IF NOT EXISTS "papod_sessions" ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + timestamp TEXT NOT NULL DEFAULT ( + strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now') + ), + -- provided by cracha + session_uuid BLOB NOT NULL UNIQUE, + user_id INTEGER NOT NULL + REFERENCES "papod_users"(id), + finished_at TEXT + ); + CREATE TABLE IF NOT EXISTS "papod_connections" ( + 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, + finished_at TEXT + ); + CREATE TABLE IF NOT EXISTS "papod_logons" ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + timestamp TEXT NOT NULL DEFAULT ( + strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now') + ), + session_id INTEGER NOT NULL + REFERENCES "papod_sessions"(id), + connection_id INTEGER NOT NULL + REFERENCES "papod_connections"(id), + UNIQUE (session_id, connection_id) + ) STRICT; 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')), + 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') - ) + ), + deleted INT NOT NULL CHECK(deleted IN (0, 1)) ) STRICT; + CREATE INDEX IF NOT EXISTS "papod_networks_type" + ON "papod_networks"(type); 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), + timestamp TEXT NOT NULL DEFAULT ( + strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now') + ), + network_id INTEGER NOT NULL, attribute TEXT NOT NULL CHECK( attribute IN ( 'name', 'description', - 'type' + 'type', + 'deleted', + 'logon_id' -- FIXME ) ), value TEXT NOT NULL, op INT NOT NULL CHECK(op IN (0, 1)) ) STRICT; + CREATE TRIGGER IF NOT EXISTS "papod_network_new" + AFTER INSERT ON "papod_networks" + BEGIN + INSERT INTO "papod_network_changes" ( + network_id, attribute, value, op + ) VALUES + (NEW.id, 'name', NEW.name, true), + (NEW.id, 'description', NEW.description, true), + (NEW.id, 'type', NEW.type, true), + (NEW.id, 'deleted', NEW.deleted, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_network_update_name" + AFTER UPDATE ON "papod_networks" + WHEN OLD.name != NEW.name + BEGIN + INSERT INTO "papod_network_changes" ( + network_id, attribute, value, op + ) VALUES + (NEW.id, 'name', OLD.name, false), + (NEW.id, 'name', NEW.name, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_network_update_description" + AFTER UPDATE ON "papod_networks" + WHEN OLD.description != NEW.description + BEGIN + INSERT INTO "papod_network_changes" ( + network_id, attribute, value, op + ) VALUES + (NEW.id, 'description', OLD.description, false), + (NEW.id, 'description', NEW.description, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_network_update_type" + AFTER UPDATE ON "papod_networks" + WHEN OLD.description != NEW.description + BEGIN + INSERT INTO "papod_network_changes" ( + network_id, attribute, value, op + ) VALUES + (NEW.id, 'type', OLD.type, false), + (NEW.id, 'type', NEW.type, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_network_update_deleted" + AFTER UPDATE ON "papod_networks" + WHEN OLD.deleted != NEW.deleted + BEGIN + INSERT INTO "papod_network_changes" ( + network_id, attribute, value, op + ) VALUES + (NEW.id, 'deleted', OLD.deleted, false), + (NEW.id, 'deleted', NEW.deleted, true) + ; + END; 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')), + timestamp TEXT NOT NULL DEFAULT ( + strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now') + ), + uuid BLOB NOT NULL UNIQUE, network_id INTEGER NOT NULL REFERENCES "papod_networks"(id), user_id INTEGER NOT NULL, @@ -144,6 +281,120 @@ UNIQUE (network_id, username, active_uniq), UNIQUE (network_id, user_id) ) STRICT; + 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, + attribute TEXT NOT NULL CHECK( + attribute IN ( + 'username', + 'display_name', + 'picture_uuid', + 'status', + 'logon_id' -- FIXME + ) + ), + value_text TEXT, + value_blob BLOB, + op INT NOT NULL CHECK(op IN (0, 1)) + ) STRICT; + CREATE TRIGGER IF NOT EXISTS "papod_member_new" + AFTER INSERT ON "papod_members" + BEGIN + INSERT INTO "papod_member_changes" ( + member_id, attribute, value_text, op + ) VALUES + (NEW.id, 'username', NEW.username, true), + (NEW.id, 'display_name', NEW.display_name, true), + (NEW.id, 'status', NEW.status, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_member_new_picture_uuid" + AFTER INSERT ON "papod_members" + WHEN NEW.picture_uuid IS NOT NULL + BEGIN + INSERT INTO "papod_member_changes" ( + member_id, attribute, value_blob, op + ) VALUES + (NEW.id, 'picture_uuid', NEW.picture_uuid, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_member_update_username" + AFTER UPDATE ON "papod_members" + WHEN OLD.username != NEW.username + BEGIN + INSERT INTO "papod_member_changes" ( + member_id, attribute, value_text, op + ) VALUES + (NEW.id, 'username', OLD.username, false), + (NEW.id, 'username', NEW.username, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_member_update_display_name" + AFTER UPDATE ON "papod_members" + WHEN OLD.display_name != NEW.display_name + BEGIN + INSERT INTO "papod_member_changes" ( + member_id, attribute, value_text, 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_member_update_status" + AFTER UPDATE ON "papod_members" + WHEN OLD.status != NEW.status + BEGIN + INSERT INTO "papod_member_changes" ( + member_id, attribute, value_text, op + ) VALUES + (NEW.id, 'status', OLD.status, false), + (NEW.id, 'status', NEW.status, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_member_add_picture_uuid" + AFTER UPDATE ON "papod_members" + WHEN ( + OLD.picture_uuid IS NULL AND + NEW.picture_uuid IS NOT NULL + ) + BEGIN + INSERT INTO "papod_member_changes" ( + member_id, attribute, value_blob, op + ) VALUES + (NEW.id, 'picture_uuid', NEW.picture_uuid, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_member_remove_picture_uuid" + AFTER UPDATE ON "papod_members" + WHEN ( + OLD.picture_uuid IS NOT NULL AND + NEW.picture_uuid IS NULL + ) + BEGIN + INSERT INTO "papod_member_changes" ( + member_id, attribute, value_blob, op + ) VALUES + (NEW.id, 'picture_uuid', OLD.picture_uuid, false) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_member_update_picture_uuid" + AFTER UPDATE ON "papod_members" + WHEN ( + OLD.picture_uuid IS NOT NULL AND + NEW.picture_uuid IS NOT NULL AND + OLD.picture_uuid != NEW.picture_uuid + ) + BEGIN + INSERT INTO "papod_member_changes" ( + member_id, attribute, value_blob, op + ) VALUES + (NEW.id, 'picture_uuid', OLD.picture_uuid, false), + (NEW.id, 'picture_uuid', NEW.picture_uuid, true) + ; + END; CREATE TABLE IF NOT EXISTS "papod_member_roles" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, @@ -152,40 +403,157 @@ role TEXT NOT NULL, UNIQUE (member_id, role) ) STRICT; - - -- FIXME: use a trigger - CREATE TABLE IF NOT EXISTS "papod_member_changes" ( + CREATE TABLE IF NOT EXISTS "papod_member_role_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, + timestamp TEXT NOT NULL DEFAULT ( + strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now') + ), + role_id INTEGER NOT NULL, + attribute TEXT NOT NULL CHECK( + attribute IN ( + 'role', + 'logon_id' -- FIXME + ) + ), value TEXT NOT NULL, op INT NOT NULL CHECK(op IN (0, 1)) ) STRICT; + CREATE TRIGGER IF NOT EXISTS "papod_member_role_add" + AFTER INSERT ON "papod_member_roles" + BEGIN + INSERT INTO "papod_member_role_changes" ( + role_id, attribute, value, op + ) VALUES + (NEW.id, 'role', NEW.role, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_member_role_remove" + AFTER DELETE ON "papod_member_roles" + BEGIN + INSERT INTO "papod_member_role_changes" ( + role_id, attribute, value, op + ) VALUES + (OLD.id, 'role', OLD.role, false) + ; + END; 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')), + timestamp TEXT NOT NULL DEFAULT ( + strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now') + ), uuid BLOB NOT NULL UNIQUE, - network_id INTEGER -- FIXME NOT NULL + network_id INTEGER NOT NULL REFERENCES "papod_networks"(id), - public_name TEXT UNIQUE, + public_name TEXT, label TEXT NOT NULL, description TEXT NOT NULL, - virtual INT NOT NULL CHECK(virtual IN (0, 1)) + virtual INT NOT NULL CHECK(virtual IN (0, 1)), + UNIQUE (network_id, public_name) ) 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, + timestamp TEXT NOT NULL DEFAULT ( + strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now') + ), + channel_id INTEGER NOT NULL, + attribute TEXT NOT NULL CHECK( + attribute IN ( + 'public_name', + 'label', + 'description', + 'virtual', + 'logon_id' -- FIXME + ) + ), + value_text TEXT, + value_bool INT CHECK(value_bool IN (0, 1)), op INT NOT NULL CHECK(op IN (0, 1)) ) STRICT; + CREATE TRIGGER IF NOT EXISTS "papod_channel_new" + AFTER INSERT ON "papod_channels" + BEGIN + INSERT INTO "papod_channel_changes" ( + channel_id, attribute, value_text, op + ) VALUES + (NEW.id, 'label', NEW.label, true), + (NEW.id, 'description', NEW.description, true) + ; + INSERT INTO "papod_channel_changes" ( + channel_id, attribute, value_bool, op + ) VALUES + (NEW.id, 'virtual', NEW.virtual, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_channel_new_public_name" + AFTER INSERT ON "papod_channels" + WHEN NEW.public_name IS NOT NULL + BEGIN + INSERT INTO "papod_channel_changes" ( + channel_id, attribute, value_text, op + ) VALUES + (NEW.id, 'public_name', NEW.public_name, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_channel_update_label" + AFTER UPDATE ON "papod_channels" + WHEN OLD.label != NEW.label + BEGIN + INSERT INTO "papod_channel_changes" ( + channel_id, attribute, value_text, op + ) VALUES + (NEW.id, 'label', OLD.label, false), + (NEW.id, 'label', NEW.label, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_channel_update_description" + AFTER UPDATE ON "papod_channels" + WHEN OLD.description != NEW.description + BEGIN + INSERT INTO "papod_channel_changes" ( + channel_id, attribute, value_text, op + ) VALUES + (NEW.id, 'description', OLD.description, false), + (NEW.id, 'description', NEW.description, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_channel_update_virtual" + AFTER UPDATE ON "papod_channels" + WHEN OLD.virtual != NEW.virtual + BEGIN + INSERT INTO "papod_channel_changes" ( + channel_id, attribute, value_bool, op + ) VALUES + (NEW.id, 'virtual', OLD.virtual, false), + (NEW.id, 'virtual', NEW.virtual, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_channel_add_public_name" + AFTER UPDATE ON "papod_channels" + WHEN ( + OLD.public_name IS NULL AND + NEW.public_name IS NOT NULL + ) + BEGIN + INSERT INTO "papod_channel_changes" ( + channel_id, attribute, value_text, op + ) VALUES + (NEW.id, 'public_name', NEW.public_name, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "papod_channel_remove_public_name" + AFTER UPDATE ON "papod_channels" + WHEN ( + OLD.public_name IS NOT NULL AND + NEW.public_name IS NULL + ) + BEGIN + INSERT INTO "papod_channel_changes" ( + channel_id, attribute, value_text, op + ) VALUES + (OLD.id, 'public_name', OLD.public_name, false) + ; + END; CREATE TABLE IF NOT EXISTS "papod_participants" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, @@ -195,37 +563,65 @@ REFERENCES "papod_members"(id), UNIQUE (channel_id, member_id) ) STRICT; + CREATE TABLE IF NOT EXISTS "papod_participant_changes" ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + timestamp TEXT NOT NULL DEFAULT ( + strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now') + ), + participant_id INTEGER NOT NULL, + attribute TEXT NOT NULL CHECK( + attribute IN ( + 'connection_id' + ) + ), + value TEXT NOT NULL, + op INT NOT NULL CHECK(op IN (0, 1)) + ) 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')), + 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 + source_uuid BLOB NOT NULL, + source_type TEXT NOT NULL CHECK( + source_type IN ( + 'logon' + ) + ), + source_metadata TEXT, type TEXT NOT NULL CHECK( type IN ( 'user-join', 'user-message' ) ), - payload TEXT NOT NULL + payload TEXT NOT NULL, + metadata TEXT ) STRICT; + -- read: +-- memberRoles.sql: +-- write: + +-- read: + SELECT role FROM "papod_member_roles" + JOIN "papod_members" ON + "papod_member_roles".member_id = "papod_members".id + WHERE "papod_members".uuid = ? + ORDER BY "papod_member_roles".id; + + -- createUser.sql: -- write: INSERT INTO "papod_users" ( - uuid, username, display_name, picture_uuid, deleted + user_uuid, username, display_name, picture_uuid, deleted ) VALUES ( ?, ?, ?, NULL, false ) RETURNING id, timestamp; @@ -245,8 +641,8 @@ picture_uuid FROM "papod_users" WHERE - uuid = ? AND - deleted = false; + user_uuid = ? AND + deleted = false; -- updateUser.sql: @@ -269,8 +665,8 @@ UPDATE "papod_users" SET deleted = true WHERE - uuid = ? AND - deleted = false + user_uuid = ? AND + deleted = false RETURNING id; @@ -279,92 +675,267 @@ -- addNetwork.sql: -- write: INSERT INTO "papod_networks" ( - uuid, name, description, type, creator_id + uuid, name, description, type, deleted ) VALUES ( ?, ?, ?, ?, - ( - SELECT id FROM "papod_users" - WHERE id = ? AND deleted = false - ) - ) RETURNING id, timestamp; + false + ) RETURNING id; + WITH creator AS ( + SELECT username, display_name, picture_uuid + FROM "papod_users" + WHERE id = ? AND deleted = false + ), new_network AS ( + SELECT id FROM "papod_networks" WHERE uuid = ? + ) INSERT INTO "papod_members" ( - network_id, user_id, username, display_name, + uuid, 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 - ), + (SELECT id FROM new_network), + ?, + (SELECT username FROM creator), + (SELECT display_name FROM creator), + (SELECT picture_uuid FROM creator), 'active', 'active' - ) RETURNING id, timestamp; + ) RETURNING id; + + WITH new_member AS ( + SELECT id FROM "papod_members" WHERE uuid = ? + ) + INSERT INTO "papod_member_roles" (member_id, role) + VALUES ( + (SELECT id FROM new_member), + 'admin' + ), + ( + (SELECT id FROM new_member), + 'creator' + ) + RETURNING id; -- read: + SELECT id, timestamp FROM "papod_networks" + WHERE uuid = ? AND deleted = false; + -- getNetwork.sql: -- write: -- read: + WITH probing_user AS ( + SELECT id FROM "papod_users" + WHERE id = ? AND deleted = false + ), target_network AS ( + SELECT id FROM "papod_networks" + WHERE uuid = ? AND deleted = false + ) SELECT - "papod_networks".id, - "papod_networks".timestamp, - "papod_users".uuid, - "papod_networks".name, - "papod_networks".description, - "papod_networks".type + id, + timestamp, + name, + description, + 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 + uuid = ? AND + deleted = false AND + ? IN probing_user AND ( - "papod_networks".type IN ('public', 'unlisted') OR - $userID IN ( + type IN ('public', 'unlisted') OR + ? IN ( SELECT user_id FROM "papod_members" WHERE - user_id = $userID AND - network_id = "papod_networks".id + user_id = ? AND + network_id IN target_network AND + status != 'removed' ) ); - + %!(EXTRA string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod) -- networks.sql: -- write: -- read: - -- FIXME papod + WITH current_user AS ( + SELECT id, deleted FROM "papod_users" WHERE id = ? + ) + SELECT + "papod_networks".id, + "papod_networks".timestamp, + "papod_networks".uuid, + "papod_networks".name, + "papod_networks".description, + "papod_networks".type, + (SELECT deleted FROM current_user) + FROM "papod_networks" + JOIN "papod_members" ON + "papod_networks".id = "papod_members".network_id + WHERE ( + "papod_networks".type = 'public' OR + "papod_networks".id IN ( + SELECT network_id FROM "papod_members" + WHERE user_id IN (SELECT id FROM current_user) + ) + ) AND "papod_networks".deleted = false + ORDER BY "papod_networks".id; -- setNetwork.sql: -- write: - -- FIXME papod + UPDATE "papod_networks" + SET + name = ?, + description = ?, + type = ? + WHERE id = ? AND deleted = false + RETURNING ( + SELECT CASE WHEN EXISTS ( + SELECT role from "papod_member_roles" + WHERE + member_id = ? AND + role IN ( + 'admin', + 'network-settings-update' + ) AND ? IN ( + SELECT network_id + FROM "papod_members" + WHERE + id = ? AND + status = 'active' + ) + ) THEN true ELSE RAISE( + ABORT, + 'member not allowed to update network data' + ) END + ); + -- read: -- nipNetwork.sql: -- write: - -- FIXME papod + WITH target_network AS ( + SELECT network_id AS id + FROM "papod_members" + WHERE + id = ? AND + status = 'active' + ) + UPDATE "papod_networks" + SET deleted = true + WHERE id IN target_network AND deleted = false + RETURNING ( + SELECT CASE WHEN EXISTS ( + SELECT role FROM "papod_member_roles" + WHERE + member_id = ? AND + role IN ( + 'admin' + ) + ) THEN true ELSE RAISE( + ABORT, + 'member not allowed to delete network' + ) END + ); -- read: +-- membership.sql: +-- write: + +-- read: + SELECT + "papod_members".id, + "papod_members".timestamp, + "papod_members".uuid, + "papod_members".username, + "papod_members".display_name, + "papod_members".picture_uuid, + "papod_members".status + FROM "papod_members" + JOIN "papod_users" ON + "papod_users".id = "papod_members".user_id + JOIN "papod_networks" ON + "papod_networks".id = "papod_members".network_id + WHERE + "papod_members".user_id = ? AND + "papod_members".network_id = ? AND + "papod_members".status = 'active' AND + "papod_users".deleted = false AND + "papod_networks".deleted = false; + + -- addMember.sql: -- write: - -- FIXME papod + WITH target_user AS ( + SELECT id, username, display_name, picture_uuid + FROM "papod_users" + WHERE user_uuid = ? AND deleted = false + ), target_network AS ( + SELECT "papod_members".network_id AS id + FROM "papod_members" + JOIN "papod_networks" ON + "papod_members".network_id = "papod_networks".id + WHERE + "papod_members".id = ? AND + "papod_members".status = 'active' AND + "papod_networks".deleted = false + ) + INSERT INTO "papod_members" ( + uuid, network_id, user_id, username, display_name, + picture_uuid, status, active_uniq + ) VALUES ( + ?, + (SELECT id FROM target_network), + (SELECT id FROM target_user), + ?, + (SELECT display_name FROM target_user), + (SELECT picture_uuid FROM target_user), + 'active', + 'active' + ) RETURNING id, timestamp, display_name, picture_uuid, status, ( + SELECT CASE WHEN EXISTS ( + SELECT role from "papod_member_roles" + WHERE + member_id = ? AND + role IN ( + 'admin', + 'add-member' + ) + ) THEN true ELSE RAISE( + ABORT, + 'member not allowed to add another member' + ) END + ); + + +-- read: + +-- addRole.sql: +-- write: + INSERT INTO "papod_member_roles" (member_id, role) + VALUES (?, ?); + + +-- read: + +-- dropRole.sql: +-- write: + DELETE FROM "papod_member_roles" + WHERE + member_id = ? AND + role = ? + RETURNING 1; -- read: @@ -373,52 +944,175 @@ -- write: -- read: - -- FIXME papod + WITH current_network AS ( + SELECT network_id + FROM "papod_members" + WHERE id = ? + ) + SELECT + id, + timestamp, + username, + display_name, + picture_uuid, + status + FROM "papod_members" + WHERE + uuid = ? AND + network_id IN current_network; -- members.sql: -- write: -- read: - -- FIXME papod + WITH target_network AS ( + SELECT "papod_members".network_id + FROM "papod_members" + JOIN "papod_networks" ON + "papod_members".network_id = "papod_networks".id + WHERE + "papod_members".id = ? AND + "papod_networks".deleted = false + ) + SELECT + id, + timestamp, + uuid, + username, + display_name, + picture_uuid, + status + FROM "papod_members" + WHERE + network_id IN target_network AND + status = 'active'; -- editMember.sql: -- write: - -- FIXME papod + UPDATE "papod_members" + SET + status = ? + WHERE id = ? + RETURNING id; -- read: -- dropMember.sql: -- write: - -- FIXME + UPDATE "papod_members" SET status = 'removed' + WHERE uuid = ? RETURNING id; + + DELETE FROM "papod_member_roles" + WHERE + role != 'creator' AND + member_id IN ( + SELECT id FROM "papod_members" + WHERE uuid = ? + ) -- read: -- addChannel.sql: -- write: + WITH target_network AS ( + SELECT network_id AS id + FROM "papod_members" + WHERE id = ? + ) INSERT INTO "papod_channels" ( - uuid, public_name, label, description, virtual - ) VALUES (?, ?, ?, ?, ?) RETURNING id, timestamp; + uuid, + network_id, + public_name, + label, + description, + virtual + ) VALUES ( + ?, + (SELECT id FROM target_network), + ?, + ?, + ?, + ? + ) RETURNING id, timestamp; + + WITH new_channel AS ( + SELECT id FROM "papod_channels" WHERE uuid = ? + ) + INSERT INTO "papod_participants" (channel_id, member_id) + VALUES ( + (SELECT id FROM new_channel), + ? + ); -- read: + SELECT id, timestamp FROM "papod_channels" + WHERE uuid = ?; + -- channels.sql: -- write: -- read: - -- FIXME papod + WITH current_network AS ( + SELECT network_id AS id + FROM "papod_members" + WHERE id = ? + ), member_private_channels AS ( + SELECT channel_id AS id + FROM "papod_participants" + WHERE member_id = ? + ) + SELECT + id, + timestamp, + uuid, + public_name, + label, + description, + virtual + FROM "papod_channels" + WHERE + network_id IN current_network AND + ( + public_name IS NOT NULL OR + id IN member_private_channels + ) + ORDER BY id; --- topic.sql: +-- setChannel.sql: -- write: - -- FIXME papod + WITH participant_channel AS ( + SELECT channel_id AS id + FROM "papod_participants" + WHERE + member_id = ? AND + channel_id = ? + ) + UPDATE "papod_channels" + SET + description = ?, + public_name = ? + WHERE id IN participant_channel + RETURNING id; -- read: + SELECT ( + SELECT network_id AS id + FROM "papod_channels" + WHERE id = ? + ) AS channel_network_id, ( + SELECT network_id AS id + FROM "papod_members" + WHERE id = ? + ) AS member_network_id; + -- endChannel.sql: -- write: @@ -429,14 +1123,47 @@ -- join.sql: -- write: - -- FIXME papod + WITH target_channel AS ( + SELECT id + FROM "papod_channels" + WHERE + uuid = ? AND + public_name IS NOT NULL + ) + INSERT INTO "papod_participants" (channel_id, member_id) + VALUES ( + (SELECT id FROM target_channel), + ? + ) RETURNING id; -- read: + SELECT ( + SELECT network_id AS id + FROM "papod_channels" + WHERE + uuid = ? AND + public_name IS NOT NULL + ) AS channel_network_id, ( + SELECT network_id AS id + FROM "papod_members" WHERE id = ? + ) AS member_network_id; + -- part.sql: -- write: - -- FIXME papod + WITH target_channel AS ( + SELECT id + FROM "papod_channels" + WHERE + id = ? AND + virtual = false + ) + DELETE FROM "papod_participants" + WHERE + member_id = ? AND + channel_id IN target_channel + RETURNING 1; -- read: @@ -451,12 +1178,16 @@ -- addEvent.sql: -- write: INSERT INTO "papod_channel_events" ( - uuid, channel_id, connection_uuid, type, payload + uuid, channel_id, source_uuid, source_type, + source_metadata, type, payload, metadata ) VALUES ( ?, (SELECT id FROM "papod_channels" WHERE uuid = ?), ?, ?, + ?, + ?, + ?, ? ) RETURNING id, timestamp; @@ -477,7 +1208,7 @@ "papod_channel_events".timestamp, "papod_channel_events".uuid, "papod_channels".uuid, - "papod_channel_events".connection_uuid, + -- "papod_channel_events".connection_uuid, "papod_channel_events".type, "papod_channel_events".payload FROM "papod_channel_events" |