From 1feab2105ac464747b231232f53f4ac335f4467f Mon Sep 17 00:00:00 2001
From: EuAndreh <eu@euandre.org>
Date: Mon, 4 Nov 2024 09:13:33 -0300
Subject: src/papod.go: Integrate db layer with network, create command
 handlers, simplify network loop

---
 tests/queries.sql | 220 +++++++++++++++++++++++++++++-------------------------
 1 file changed, 117 insertions(+), 103 deletions(-)

(limited to 'tests/queries.sql')

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:
-- 
cgit v1.2.3

