diff options
Diffstat (limited to 'tests')
| l--------- | tests/benchmarks/join-leave/main.go | 1 | ||||
| -rw-r--r-- | tests/benchmarks/join-leave/papod.go | 23 | ||||
| l--------- | tests/functional/multiple-channels-and-users/main.go | 1 | ||||
| -rw-r--r-- | tests/functional/multiple-channels-and-users/papod.go | 12 | ||||
| l--------- | tests/fuzz/api-check/main.go | 1 | ||||
| -rw-r--r-- | tests/fuzz/api-check/papod.go | 34 | ||||
| -rw-r--r-- | tests/main.go | 7 | ||||
| -rw-r--r-- | tests/papod.go | 382 | ||||
| -rw-r--r-- | tests/queries.sql | 1230 |
9 files changed, 0 insertions, 1691 deletions
diff --git a/tests/benchmarks/join-leave/main.go b/tests/benchmarks/join-leave/main.go deleted file mode 120000 index f67563d..0000000 --- a/tests/benchmarks/join-leave/main.go +++ /dev/null @@ -1 +0,0 @@ -../../main.go
\ No newline at end of file diff --git a/tests/benchmarks/join-leave/papod.go b/tests/benchmarks/join-leave/papod.go deleted file mode 100644 index 7bb69cc..0000000 --- a/tests/benchmarks/join-leave/papod.go +++ /dev/null @@ -1,23 +0,0 @@ -package papod - -import ( - "flag" - "time" -) - - - -var nFlag = flag.Int( - "n", - 1_000, - "The number of iterations to execute", -) - -func MainTest() { - flag.Parse() - n := *nFlag - - for i := 0; i < n; i++ { - time.Sleep(time.Millisecond * 1) - } -} diff --git a/tests/functional/multiple-channels-and-users/main.go b/tests/functional/multiple-channels-and-users/main.go deleted file mode 120000 index f67563d..0000000 --- a/tests/functional/multiple-channels-and-users/main.go +++ /dev/null @@ -1 +0,0 @@ -../../main.go
\ No newline at end of file diff --git a/tests/functional/multiple-channels-and-users/papod.go b/tests/functional/multiple-channels-and-users/papod.go deleted file mode 100644 index 6f6a67b..0000000 --- a/tests/functional/multiple-channels-and-users/papod.go +++ /dev/null @@ -1,12 +0,0 @@ -package papod - -import ( - g "gobang" -) - - - -func MainTest() { - g.Testing("multiple users can join a channel", func() { - }) -} diff --git a/tests/fuzz/api-check/main.go b/tests/fuzz/api-check/main.go deleted file mode 120000 index f67563d..0000000 --- a/tests/fuzz/api-check/main.go +++ /dev/null @@ -1 +0,0 @@ -../../main.go
\ No newline at end of file diff --git a/tests/fuzz/api-check/papod.go b/tests/fuzz/api-check/papod.go deleted file mode 100644 index edab81c..0000000 --- a/tests/fuzz/api-check/papod.go +++ /dev/null @@ -1,34 +0,0 @@ -package papod - -import ( - "os" - "testing" - "testing/internal/testdeps" -) - - - -func api(f *testing.F) { - f.Fuzz(func(t *testing.T, n int) { - if n > 1 { - if n < 2 { - t.Errorf("Failed n: %v\n", n) - } - } - }) -} - - - -func MainTest() { - fuzzTargets := []testing.InternalFuzzTarget{ - { "api", api }, - } - - deps := testdeps.TestDeps{} - tests := []testing.InternalTest {} - benchmarks := []testing.InternalBenchmark{} - examples := []testing.InternalExample {} - m := testing.MainStart(deps, tests, benchmarks, fuzzTargets, examples) - os.Exit(m.Run()) -} diff --git a/tests/main.go b/tests/main.go deleted file mode 100644 index f32854e..0000000 --- a/tests/main.go +++ /dev/null @@ -1,7 +0,0 @@ -package main - -import "papod" - -func main() { - papod.MainTest() -} diff --git a/tests/papod.go b/tests/papod.go deleted file mode 100644 index 3f0e557..0000000 --- a/tests/papod.go +++ /dev/null @@ -1,382 +0,0 @@ -package papod - -import ( - "bufio" - "errors" - "strings" - - g "gobang" -) - - - -func test_splitOnCRLF() { - g.TestStart("splitOnCRLF()") - - g.Testing("we need an ending \\r\\n to get output", func() { - type tableT struct{ - input string - expected []string - } - table := []tableT{ - { - "", - nil, - }, - { - "\r\n", - []string{ "" }, - }, - { - "abc\r\n", - []string{ "abc" }, - }, - { - "abc\r\n ", - []string{ "abc" }, - }, - { - "abc\r\n This gets ignored.\n", - []string{ "abc" }, - }, - { - "abc\r\n \r\n", - []string{ "abc", " " }, - }, - { - " \r\n \r\n", - []string{ " ", " " }, - }, - { - "aaa\r\nbbb\r\nccc\r\n", - []string{ "aaa", "bbb", "ccc" }, - }, - { - "\r\nsplit \r \n CRLF\r\n\r\n", - []string{ "", "split \r \n CRLF", "" }, - }, - } - - for _, entry := range table { - var given []string - scanner := bufio.NewScanner( - strings.NewReader(entry.input), - ) - scanner.Split(splitOnCRLF) - for scanner.Scan() { - given = append(given, scanner.Text()) - } - - err := scanner.Err() - g.TErrorIf(err) - g.TAssertEqual(given, entry.expected) - } - }) -} - -func test_splitOnRawMessage() { - g.TestStart("splitOnRawMessage()") - - g.Testing("empty messages get dropped", func() { - type tableT struct{ - input string - expected []string - } - table := []tableT{ - /* - FIXME - { - "\r\nfirst\r\n\r\nsecond\r\n \r\n", - []string{ "first", "second", " " }, - }, - */ - { - "first message\r\nsecond message\r\n", - []string{ "first message", "second message" }, - }, - { - "message 1\r\n\r\nmessage 2\r\n\r\nignored", - []string{ "message 1", "message 2" }, - }, - } - - - for _, entry := range table { - var given []string - scanner := bufio.NewScanner(strings.NewReader( - entry.input, - )) - scanner.Split(splitOnRawMessage) - for scanner.Scan() { - given = append(given, scanner.Text()) - } - - err := scanner.Err() - g.TErrorIf(err) - g.TAssertEqual(given, entry.expected) - } - }) -} - -func test_parseMessageParams() { - g.TestStart("parseMessageParams()") - - g.Testing("we can parse the string params", func() { - type tableT struct{ - input string - expected []string - } - table := []tableT{ - { "", []string{} }, - { " ", []string{} }, - { " :", []string{ "" } }, - { " : ", []string{ " " } }, - { ": ", []string{ ":" } }, - { ": ", []string{ ":" } }, - { " : ", []string{ " " } }, - { " :", []string{ "" } }, - { " :", []string{ "" } }, - { "a", []string{ "a" } }, - { "ab", []string{ "ab" } }, - { "a b", []string{ "a", "b" } }, - { "a b c", []string{ "a", "b", "c" } }, - { "a b:c", []string{ "a", "b:c" } }, - { "a b:c:", []string{ "a", "b:c:" } }, - { "a b :c", []string{ "a", "b", "c" } }, - { "a b :c:", []string{ "a", "b", "c:" } }, - { "a b :c ", []string{ "a", "b", "c " } }, - { "a b : c", []string{ "a", "b", " c" } }, - { "a b : c ", []string{ "a", "b", " c " } }, - { "a b : c :", []string{ "a", "b", " c :" } }, - { "a b : c : ", []string{ "a", "b", " c : " } }, - { "a b : c :d", []string{ "a", "b", " c :d" } }, - { "a b : c :d ", []string{ "a", "b", " c :d " } }, - { "a b : c : d ", []string{ "a", "b", " c : d " } }, - } - - for _, entry := range table { - given := parseMessageParams(entry.input) - g.TAssertEqual(given, entry.expected) - } - }) -} - -func test_parseMessage() { - g.TestStart("parseMessage()") - - g.Testing("we can parse a full message", func() { - type tableTOK struct{ - input string - expected messageT - } - tableOK := []tableTOK {{ - "NICK joebloe ", - messageT{ - prefix: "", - command: "NICK", - params: []string{ "joebloe" }, - raw: "NICK joebloe ", - }, - }, { - "USER joebloe 0.0.0.0 joe :Joe Bloe", - messageT{ - prefix: "", - command: "USER", - params: []string{ - "joebloe", - "0.0.0.0", - "joe", - "Joe Bloe", - }, - raw: "USER joebloe 0.0.0.0 joe :Joe Bloe", - }, - }, { - ":pre USER joebloe 0.0.0.0 joe :Joe Bloe", - messageT{ - prefix: "pre", - command: "USER", - params: []string{ - "joebloe", - "0.0.0.0", - "joe", - "Joe Bloe", - }, - raw: ":pre USER joebloe 0.0.0.0 joe :Joe Bloe", - }, - }, { - ":pre USER joebloe 0.0.0.0 joe : Joe Bloe ", - messageT{ - prefix: "pre", - command: "USER", - params: []string{ - "joebloe", - "0.0.0.0", - "joe", - " Joe Bloe ", - }, - raw: ":pre USER joebloe 0.0.0.0 " + - "joe : Joe Bloe ", - }, - }, { - ":pre USER jbloe: 0:0:0:1 joe::a: : Joe Bloe ", - messageT{ - prefix: "pre", - command: "USER", - params: []string{ - "jbloe:", - "0:0:0:1", - "joe::a:", - " Joe Bloe ", - }, - raw: ":pre USER jbloe: 0:0:0:1 " + - "joe::a: : Joe Bloe ", - }, - }, { - ":pre USER :Joe Bloe", - messageT{ - prefix: "pre", - command: "USER", - params: []string{ "Joe Bloe" }, - raw: ":pre USER :Joe Bloe", - }, - }, { - ":pre USER : Joe Bloe", - messageT{ - prefix: "pre", - command: "USER", - params: []string{ " Joe Bloe" }, - raw: ":pre USER : Joe Bloe", - }, - }, { - ":pre USER : Joe Bloe", - messageT{ - prefix: "pre", - command: "USER", - params: []string{ " Joe Bloe" }, - raw: ":pre USER : Joe Bloe", - }, - }, { - ":pre USER : ", - messageT{ - prefix: "pre", - command: "USER", - params: []string{ " " }, - raw: ":pre USER : ", - }, - }, { - ":pre USER :", - messageT{ - prefix: "pre", - command: "USER", - params: []string{}, - raw: ":pre USER :", - }, - }} - - for _, entry := range tableOK { - given, err := parseMessage(entry.input) - g.TErrorIf(err) - g.TAssertEqual(given, entry.expected) - } - }) - - - g.Testing("bad messages are rejected", func() { - type tableErrorT struct{ - input string - expected error - } - parseErr := errors.New("Can't parse message") - tableError := []tableErrorT{ - { - ":pre", - parseErr, - }, - { - ": pre", - parseErr, - }, - { - ":pre N1CK", - parseErr, - }, - } - - for _, entry := range tableError { - _, given := parseMessage(entry.input) - g.TAssertEqual(given, entry.expected) - } - }) -} - -func test_addTrailingSeparator() { - g.TestStart("addTrailingSeparator()") - - g.Testing("noop on empty slice", func() { - input := []string{} - addTrailingSeparator(input) - g.TAssertEqual(input, []string{}) - }) - - g.Testing("noop if last doesn't have a space", func() { - type tableT struct{ - input []string - expected []string - } - - entries := []tableT{ - { []string{ "" }, []string{ "" } }, - { []string{ "", "" }, []string{ "", "" } }, - { []string{ "a", "b" }, []string{ "a", "b" } }, - { - []string{ "a", "b", "c-d" }, - []string{ "a", "b", "c-d" }, - }, - { - []string{ "a ", "b", "cd" }, - []string{ "a ", "b", "cd" }, - }, - } - - for i, entry := range entries { - addTrailingSeparator(entry.input) - g.TAssertEqual(entry.input, entries[i].expected) - } - }) - - g.Testing("add ':' to the last otherwise", func() { - type tableT struct{ - input []string - expected []string - } - - entries := []tableT{ - { []string{ " " }, []string{ ": " } }, - { []string{ "a " }, []string{ ":a " } }, - { []string{ " a" }, []string{ ": a" } }, - { []string{ "a ", " b" }, []string{ "a ", ": b" } }, - { []string{ "a", " b" }, []string{ "a", ": b" } }, - { - []string{ "a", "b", "c d" }, - []string{ "a", "b", ":c d" }, - }, - } - - for i, entry := range entries { - addTrailingSeparator(entry.input) - addTrailingSeparator(entry.input) - g.TAssertEqual(entry.input, entries[i].expected) - } - }) -} - - - -func MainTest() { - g.Init() - test_splitOnCRLF() - test_splitOnRawMessage() - test_parseMessageParams() - test_parseMessage() - test_addTrailingSeparator() -} diff --git a/tests/queries.sql b/tests/queries.sql deleted file mode 100644 index 992c8d2..0000000 --- a/tests/queries.sql +++ /dev/null @@ -1,1230 +0,0 @@ - --- createTables.sql: --- write: - -- 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') - ), - -- provided by cracha - 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, - 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') - ), - uuid BLOB NOT NULL UNIQUE, - 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, - attribute TEXT NOT NULL CHECK( - attribute IN ( - 'name', - 'description', - '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') - ), - uuid BLOB NOT NULL UNIQUE, - 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_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, - member_id INTEGER NOT NULL - REFERENCES "papod_members"(id), - role TEXT NOT NULL, - UNIQUE (member_id, role) - ) STRICT; - 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') - ), - 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') - ), - uuid BLOB NOT NULL UNIQUE, - network_id INTEGER NOT NULL - REFERENCES "papod_networks"(id), - public_name TEXT, - label TEXT NOT NULL, - description TEXT NOT NULL, - virtual INT NOT NULL CHECK(virtual IN (0, 1)), - UNIQUE (network_id, public_name) - ) STRICT; - 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, - 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, - channel_id INTEGER NOT NULL - REFERENCES "papod_channels"(id), - member_id INTEGER NOT NULL - 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; - - 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), - 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, - 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" ( - user_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 - user_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 - user_uuid = ? AND - deleted = false - RETURNING id; - - --- read: - --- addNetwork.sql: --- write: - INSERT INTO "papod_networks" ( - uuid, name, description, type, deleted - ) - VALUES ( - ?, - ?, - ?, - ?, - 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" ( - uuid, network_id, user_id, username, display_name, - picture_uuid, status, active_uniq - ) VALUES ( - ?, - (SELECT id FROM new_network), - ?, - (SELECT username FROM creator), - (SELECT display_name FROM creator), - (SELECT picture_uuid FROM creator), - 'active', - 'active' - ) 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 - id, - timestamp, - name, - description, - type - FROM "papod_networks" - WHERE - uuid = ? AND - deleted = false AND - ? IN probing_user AND - ( - type IN ('public', 'unlisted') OR - ? IN ( - SELECT user_id FROM "papod_members" - WHERE - 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: - 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: - 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: - 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: - 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: - --- showMember.sql: --- write: - --- read: - 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: - 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: - UPDATE "papod_members" - SET - status = ? - WHERE id = ? - RETURNING id; - - --- read: - --- dropMember.sql: --- write: - 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, - 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: - 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; - - --- setChannel.sql: --- write: - 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: - -- FIXME papod - - --- read: - --- join.sql: --- write: - 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: - 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: - --- names.sql: --- write: - --- read: - -- FIXME papod - - --- addEvent.sql: --- write: - INSERT INTO "papod_channel_events" ( - uuid, channel_id, source_uuid, source_type, - source_metadata, type, payload, metadata - ) 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: |