summaryrefslogtreecommitdiff
path: root/tests/queries.sql
diff options
context:
space:
mode:
Diffstat (limited to 'tests/queries.sql')
-rw-r--r--tests/queries.sql220
1 files changed, 117 insertions, 103 deletions
diff --git a/tests/queries.sql b/tests/queries.sql
index 3aa6586..c996f02 100644
--- a/tests/queries.sql
+++ b/tests/queries.sql
@@ -16,88 +16,88 @@
username TEXT NOT NULL,
display_name TEXT NOT NULL,
picture_uuid BLOB UNIQUE,
- deleted INT NOT NULL
+ 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 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,
@@ -196,7 +196,7 @@
UNIQUE (channel_id, member_id)
) STRICT;
- -- FIXME: create table for connections?
+ -- 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
@@ -292,7 +292,7 @@
)
) RETURNING id, timestamp;
- INSERT INTO "%!s(MISSING)_members" (
+ INSERT INTO "papod_members" (
network_id, user_id, username, display_name,
picture_uuid, status, active_uniq
) VALUES (
@@ -300,7 +300,7 @@
?,
(
SELECT username, display_name, picture_uuid
- FROM "%!s(MISSING)_users"
+ FROM "papod_users"
WHERE id = ? AND deleted = false
),
'active',
@@ -345,24 +345,26 @@
-- write:
-- read:
- -- FIXME
- %!(EXTRA string=papod)
+ -- FIXME papod
+
-- setNetwork.sql:
-- write:
- %!(EXTRA string=papod)
+ -- FIXME papod
+
-- read:
-- nipNetwork.sql:
-- write:
- %!(EXTRA string=papod)
+ -- FIXME papod
+
-- read:
-- addMember.sql:
-- write:
- -- FIXME
+ -- FIXME papod
-- read:
@@ -371,23 +373,26 @@
-- write:
-- read:
- %!(EXTRA string=papod)
+ -- FIXME papod
+
-- members.sql:
-- write:
-- read:
- -- FIXME
- %!(EXTRA string=papod)
+ -- FIXME papod
+
-- editMember.sql:
-- write:
- %!(EXTRA string=papod)
+ -- FIXME papod
+
-- read:
-- dropMember.sql:
-- write:
+ -- FIXME
-- read:
@@ -405,32 +410,34 @@
-- write:
-- read:
- -- FIXME
- %!(EXTRA string=papod)
+ -- FIXME papod
+
-- topic.sql:
-- write:
- %!(EXTRA string=papod)
+ -- FIXME papod
+
-- read:
-- endChannel.sql:
-- write:
- %!(EXTRA string=papod)
+ -- FIXME papod
+
-- read:
-- join.sql:
-- write:
- -- FIXME
- %!(EXTRA string=papod)
+ -- FIXME papod
+
-- read:
-- part.sql:
-- write:
- -- FIXME
- %!(EXTRA string=papod)
+ -- FIXME papod
+
-- read:
@@ -438,8 +445,8 @@
-- write:
-- read:
- -- FIXME
- %!(EXTRA string=papod)
+ -- FIXME papod
+
-- addEvent.sql:
-- write:
@@ -483,3 +490,10 @@
SELECT channel_id FROM landmark_event
);
+
+-- logMessage.sql:
+-- write:
+ -- FIXME papod
+
+
+-- read:
generated by cgit v1.2.3 (git 2.49.0) at 2025-07-06 14:03:53 +0000