diff options
| -rw-r--r-- | src/papod.go | 1698 | ||||
| -rw-r--r-- | tests/papod.go | 1437 | ||||
| -rw-r--r-- | tests/queries.sql | 487 |
3 files changed, 3370 insertions, 252 deletions
diff --git a/src/papod.go b/src/papod.go index aedfb5f..00086c3 100644 --- a/src/papod.go +++ b/src/papod.go @@ -3,7 +3,6 @@ package papod import ( "bufio" "bytes" - "context" "database/sql" "errors" "flag" @@ -40,43 +39,119 @@ type queryT struct{ } type queriesT struct{ - addNetwork func(guuid.UUID, newNetworkT) (networkT, error) + createUser func(newUserT) (userT, error) + userByUUID func(guuid.UUID) (userT, error) + updateUser func(userT) error + deleteUser func(guuid.UUID) error + addNetwork func(userT, newNetworkT) (networkT, error) + getNetwork func(userT, guuid.UUID) (networkT, error) + networks func(userT, func(networkT) error) error + setNetwork func(userT, networkT) error + nipNetwork func(userT, guuid.UUID) error + addMember func(userT, networkT, newMemberT) (memberT, error) + showMember func(userT, guuid.UUID) (memberT, error) + members func(userT, guuid.UUID, func(memberT) error) error + editMember func(userT, memberT) error + dropMember func(userT, guuid.UUID) error addChannel func(guuid.UUID, newChannelT) (channelT, error) channels func(guuid.UUID, func(channelT) error) error + topic func(channelT) error + endChannel func(guuid.UUID) error + join func(guuid.UUID, guuid.UUID) error + part func(guuid.UUID, guuid.UUID) error + names func(guuid.UUID, func(memberT) error) error + addEvent func(newEventT) (eventT, error) allAfter func(guuid.UUID, func(eventT) error) error - addEvent func(string, string) error close func() error } +type newUserT struct{ + uuid guuid.UUID + username string + displayName string +} + +type userT struct{ + id int64 + timestamp time.Time + uuid guuid.UUID + username string + displayName string + pictureID *guuid.UUID +} + +type NetworkType string +const ( + NetworkType_Public NetworkType = "public" + NetworkType_Private NetworkType = "private" + NetworkType_Unlisted NetworkType = "unlisted" +) + type newNetworkT struct{ - uuid guuid.UUID - name string + uuid guuid.UUID + name string + description string + type_ NetworkType } type networkT struct{ + id int64 + timestamp time.Time + uuid guuid.UUID + createdBy guuid.UUID + name string + description string + type_ NetworkType +} + +type newMemberT struct{ + userID guuid.UUID +} + +type memberT struct{ id int64 timestamp time.Time uuid guuid.UUID - name string - createdBy guuid.UUID } type newChannelT struct{ - name string - uuid guuid.UUID + id int64 + timestamp time.Time + uuid guuid.UUID + // networkID guuid.UUID FIXME + publicName string + label string + description string + virtual bool } type channelT struct { - id int64 - timestamp time.Time - uuid guuid.UUID - name string + id int64 + timestamp time.Time + uuid guuid.UUID + // networkID guuid.UUID FIXME + publicName string + label string + description string + virtual bool +} + +type newEventT struct{ + eventID guuid.UUID + channelID guuid.UUID + connectionID guuid.UUID + type_ string + payload string } type eventT struct{ - id int64 - timestamp time.Time - uuid guuid.UUID + id int64 + timestamp time.Time + uuid guuid.UUID + channelID guuid.UUID + connectionID guuid.UUID + type_ string + payload string } type papodT struct{ @@ -101,7 +176,7 @@ type connectionT struct { isAuthenticated bool } -type userT struct { +type userT2 struct { connections []connectionT } @@ -147,8 +222,55 @@ type IPapod interface{ -func tryRollback(db *sql.DB, ctx context.Context, err error) error { - _, rollbackErr := db.ExecContext(ctx, "ROLLBACK;") +func serialized[A any, B any](callback func(...A) B) (func(...A) B, func()) { + in := make(chan []A) + out := make(chan B) + + closed := false + var ( + closeWg sync.WaitGroup + closeMutex sync.Mutex + ) + closeWg.Add(1) + + go func() { + for input := range in { + out <- callback(input...) + } + close(out) + closeWg.Done() + }() + + fn := func(input ...A) B { + in <- input + return (<- out) + } + + closeFn := func() { + closeMutex.Lock() + defer closeMutex.Unlock() + if closed { + return + } + close(in) + closed = true + closeWg.Wait() + } + + return fn, closeFn +} + +func execSerialized(query string, db *sql.DB) (func(...any) error, func()) { + return serialized(func(args ...any) error { + return inTx(db, func(tx *sql.Tx) error { + _, err := tx.Exec(query, args...) + return err + }) + }) +} + +func tryRollback(tx *sql.Tx, err error) error { + rollbackErr := tx.Rollback() if rollbackErr != nil { return fmt.Errorf( rollbackErrorFmt, @@ -160,25 +282,20 @@ func tryRollback(db *sql.DB, ctx context.Context, err error) error { return err } -// FIXME -// See: -// https://sqlite.org/forum/forumpost/2507664507 -func inTx(db *sql.DB, fn func(context.Context) error) error { - ctx := context.Background() - - _, err := db.ExecContext(ctx, "BEGIN IMMEDIATE;") +func inTx(db *sql.DB, fn func(*sql.Tx) error) error { + tx, err := db.Begin() if err != nil { return err } - err = fn(ctx) + err = fn(tx) if err != nil { - return tryRollback(db, ctx, err) + return tryRollback(tx, err) } - _, err = db.ExecContext(ctx, "COMMIT;") + err = tx.Commit() if err != nil { - return tryRollback(db, ctx, err) + return tryRollback(tx, err) } return nil @@ -194,24 +311,13 @@ func inTx(db *sql.DB, fn func(context.Context) error) error { /// opaque IDs. func createTablesSQL(prefix string) queryT { const tmpl_write = ` - CREATE TABLE IF NOT EXISTS "%s_workspaces" ( - id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - timestamp TEXT NOT NULL DEFAULT (%s), - uuid BLOB NOT NULL UNIQUE, - name TEXT NOT NULL, - description TEXT NOT NULL, - -- "public", "private", "unlisted" - type TEXT NOT NULL - ); - CREATE TABLE IF NOT EXISTS "%s_workspace_changes ( - id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - timestamp TEXT NOT NULL DEFAULT (%s), - workspace_id INTEGER NOT NULL - REFERENCES "%s_workspaces"(id), - attribute TEXT NOT NULL, - value TEXT NOT NULL, - op BOOLEAN NOT NULL - ); + -- 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 "%s_users" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), @@ -219,60 +325,168 @@ func createTablesSQL(prefix string) queryT { uuid BLOB NOT NULL UNIQUE, username TEXT NOT NULL, display_name TEXT NOT NULL, - picture_uuid BLOB NOT NULL UNIQUE, - deleted BOOLEAN NOT NULL - ); + picture_uuid BLOB UNIQUE, + deleted INT NOT NULL + ) STRICT; CREATE TABLE IF NOT EXISTS "%s_user_changes" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), user_id INTEGER NOT NULL REFERENCES "%s_users"(id), - attribute TEXT NOT NULL, + attribute TEXT NOT NULL CHECK( + attribute IN ( + 'username', + 'display_name', + 'picture_uuid', + 'deleted' + ) + ), value TEXT NOT NULL, - op BOOLEAN NOT NULL - ); + op INT NOT NULL CHECK(op IN (0, 1)) + ) STRICT; + CREATE TRIGGER IF NOT EXISTS "%s_user_creation" + AFTER INSERT ON "%s_users" + BEGIN + INSERT INTO "%s_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 "%s_user_creation_picture_uuid" + AFTER INSERT ON "%s_users" + WHEN NEW.picture_uuid != NULL + BEGIN + INSERT INTO "%s_user_changes" ( + user_id, attribute, value, op + ) VALUES + (NEW.id, 'picture_uuid', NEW.picture_uuid, true) + ; + END; + CREATE TRIGGER IF NOT EXISTS "%s_user_update_username" + AFTER UPDATE ON "%s_users" + WHEN OLD.username != NEW.username + BEGIN + INSERT INTO "%s_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 "%s_user_update_display_name" + AFTER UPDATE ON "%s_users" + WHEN OLD.display_name != NEW.display_name + BEGIN + INSERT INTO "%s_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 "%s_user_update_picture_uuid" + AFTER UPDATE ON "%s_users" + WHEN OLD.picture_uuid != NEW.picture_uuid + BEGIN + INSERT INTO "%s_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 "%s_user_update_deleted" + AFTER UPDATE ON "%s_users" + WHEN OLD.deleted != NEW.deleted + BEGIN + INSERT INTO "%s_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 "%s_networks" ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + timestamp TEXT NOT NULL DEFAULT (%s), + uuid BLOB NOT NULL UNIQUE, + creator_id INTEGER NOT NULL REFERENCES "%s_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 "%s_network_changes" ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + timestamp TEXT NOT NULL DEFAULT (%s), + network_id INTEGER NOT NULL + REFERENCES "%s_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 "%s_members" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), - workspace_id INTEGER NOT NULL - REFERENCES "%s_workspaces"(id), + network_id INTEGER NOT NULL + REFERENCES "%s_networks"(id), user_id INTEGER NOT NULL, username TEXT NOT NULL, display_name TEXT NOT NULL, - picture_uuid BLOB NOT NULL UNIQUE, - -- "active", "inactive", "removed" - status TEXT NOT NULL, - -- "active", always - active_uniq TEXT, - UNIQUE (workspace_id, username, active_uniq), - UNIQUE (workspace_id, user_id) - ); + 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 "%s_member_roles" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, member_id INTEGER NOT NULL REFERENCES "%s_members"(id), role TEXT NOT NULL, UNIQUE (member_id, role) - ); + ) STRICT; + + -- FIXME: use a trigger CREATE TABLE IF NOT EXISTS "%s_member_changes" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), - member_id INTEGER NOT NULL + member_id INTEGER NOT NULL REFERENCES "%s_members"(id), attribute TEXT NOT NULL, value TEXT NOT NULL, - op BOOLEAN NOT NULL - ); + op INT NOT NULL CHECK(op IN (0, 1)) + ) STRICT; + CREATE TABLE IF NOT EXISTS "%s_channels" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), uuid BLOB NOT NULL UNIQUE, - workspace_id INTEGER NOT NULL - REFERENCES "%s_workspaces"(id), + network_id INTEGER -- FIXME NOT NULL + REFERENCES "%s_networks"(id), public_name TEXT UNIQUE, label TEXT NOT NULL, - description TEXT, - virtual BOOLEAN 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 "%s_channel_changes" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), @@ -280,23 +494,40 @@ func createTablesSQL(prefix string) queryT { REFERENCES "%s_channels"(id), attribute TEXT NOT NULL, value TEXT NOT NULL, - op BOOLEAN NOT NULL - ); + op INT NOT NULL CHECK(op IN (0, 1)) + ) STRICT; + CREATE TABLE IF NOT EXISTS "%s_participants" ( - member_id - ); + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + channel_id INTEGER NOT NULL + REFERENCES "%s_channels"(id), + member_id INTEGER NOT NULL + REFERENCES "%s_members"(id), + UNIQUE (channel_id, member_id) + ) STRICT; + + -- FIXME: create 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 "%s_channel_events" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), uuid BLOB NOT NULL UNIQUE, - channel_id INTEGER NOT NULL REFERENCES "%s"(id), - connection_id INTEGER NOT NULL, - -- payload FIXME: vary by type? - ); - -- FIXME: group conversations? - -- user: person - -- member: workspace user - -- participant: channel member + channel_id INTEGER NOT NULL + REFERENCES "%s_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; ` return queryT{ write: fmt.Sprintf( @@ -307,11 +538,49 @@ func createTablesSQL(prefix string) queryT { g.SQLiteNow, prefix, prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + g.SQLiteNow, + prefix, + prefix, + g.SQLiteNow, + prefix, + prefix, + g.SQLiteNow, + prefix, + prefix, + prefix, + prefix, g.SQLiteNow, prefix, prefix, g.SQLiteNow, prefix, + prefix, + g.SQLiteNow, + prefix, + prefix, + prefix, + prefix, + prefix, + g.SQLiteNow, + prefix, ), } } @@ -319,28 +588,246 @@ func createTablesSQL(prefix string) queryT { func createTables(db *sql.DB, prefix string) error { q := createTablesSQL(prefix) - return inTx(db, func(ctx context.Context) error { - _, err := db.ExecContext(ctx, q.write) + return inTx(db, func(tx *sql.Tx) error { + _, err := tx.Exec(q.write) return err }) } -// addServer -// addWorkspace -// addNetwork FIXME -func addNetworkSQL(prefix string) queryT { +func createUserSQL(prefix string) queryT { const tmpl_write = ` - -- FIXME + INSERT INTO "%s_users" ( + uuid, username, display_name, picture_uuid, deleted + ) VALUES ( + ?, ?, ?, NULL, false + ) RETURNING id, timestamp; + ` + return queryT{ + write: fmt.Sprintf(tmpl_write, prefix), + } +} + +func createUserStmt( + db *sql.DB, + prefix string, +) (func(newUserT) (userT, error), func() error, error) { + q := createUserSQL(prefix) + + writeStmt, err := db.Prepare(q.write) + if err != nil { + return nil, nil, err + } + + fn := func(newUser newUserT) (userT, error) { + user := userT{ + uuid: newUser.uuid, + username: newUser.username, + displayName: newUser.displayName, + } + + var timestr string + err := writeStmt.QueryRow( + newUser.uuid[:], + newUser.username, + newUser.displayName, + ).Scan(&user.id, ×tr) + if err != nil { + return userT{}, err + } + + user.timestamp, err = time.Parse(time.RFC3339Nano, timestr) + if err != nil { + return userT{}, err + } + + return user, nil + } + + return fn, writeStmt.Close, nil +} + +func userByUUIDSQL(prefix string) queryT { + const tmpl_read = ` + SELECT + id, + timestamp, + username, + display_name, + picture_uuid + FROM "%s_users" + WHERE + uuid = ? AND + deleted = false; + ` + return queryT{ + read: fmt.Sprintf(tmpl_read, prefix), + } +} + +func userByUUIDStmt( + db *sql.DB, + prefix string, +) (func(guuid.UUID) (userT, error), func() error, error) { + q := userByUUIDSQL(prefix) + + readStmt, err := db.Prepare(q.read) + if err != nil { + return nil, nil, err + } + + fn := func(userID guuid.UUID) (userT, error) { + user := userT{ + uuid: userID, + } + + var ( + timestr string + picture_id_bytes []byte + ) + err := readStmt.QueryRow(userID[:]).Scan( + &user.id, + ×tr, + &user.username, + &user.displayName, + &picture_id_bytes, + ) + if err != nil { + return userT{}, err + } + if picture_id_bytes != nil { + pictureID := guuid.UUID(picture_id_bytes) + user.pictureID = &pictureID + } + + user.timestamp, err = time.Parse(time.RFC3339Nano, timestr) + if err != nil { + return userT{}, err + } + + return user, err + } + + return fn, readStmt.Close, nil +} + +func updateUserSQL(prefix string) queryT { + const tmpl_write = ` + UPDATE "%s_users" + SET + username = ?, + display_name = ?, + picture_uuid = ? + WHERE + id = ? AND + deleted = false + RETURNING id; + ` + return queryT{ + write: fmt.Sprintf(tmpl_write, prefix), + } +} + +func updateUserStmt( + db *sql.DB, + prefix string, +) (func(userT) error, func() error, error) { + q := updateUserSQL(prefix) + + writeStmt, err := db.Prepare(q.write) + if err != nil { + return nil, nil, err + } + + fn := func(user userT) error { + var picture_id_bytes []byte = nil + if user.pictureID != nil { + picture_id_bytes = user.pictureID[:] + } + var _id int64 + return writeStmt.QueryRow( + user.username, + user.displayName, + picture_id_bytes, + user.id, + ).Scan(&_id) + } + + return fn, writeStmt.Close, nil +} + +func deleteUserSQL(prefix string) queryT { + const tmpl_write = ` + UPDATE "%s_users" + SET deleted = true + WHERE + uuid = ? AND + deleted = false + RETURNING id; ` return queryT{ write: fmt.Sprintf(tmpl_write, prefix), } } +func deleteUserStmt( + db *sql.DB, + prefix string, +) (func(guuid.UUID) error, func() error, error) { + q := deleteUserSQL(prefix) + + writeStmt, err := db.Prepare(q.write) + if err != nil { + return nil, nil, err + } + + fn := func(userID guuid.UUID) error { + var _id int64 + return writeStmt.QueryRow(userID[:]).Scan(&_id) + } + + return fn, writeStmt.Close, nil +} + +func addNetworkSQL(prefix string) queryT { + const tmpl_write = ` + INSERT INTO "%s_networks" ( + uuid, name, description, type, creator_id + ) + VALUES ( + ?, + ?, + ?, + ?, + ( + SELECT id FROM "%s_users" + WHERE id = ? AND deleted = false + ) + ) RETURNING id, timestamp; + + INSERT INTO "%s_members" ( + network_id, user_id, username, display_name, + picture_uuid, status, active_uniq + ) VALUES ( + last_insert_rowid(), + ?, + ( + SELECT username, display_name, picture_uuid + FROM "%s_users" + WHERE id = ? AND deleted = false + ), + 'active', + 'active' + ) RETURNING id, timestamp; + ` + return queryT{ + write: fmt.Sprintf(tmpl_write, prefix, prefix), + } +} + func addNetworkStmt( db *sql.DB, prefix string, -) (func(guuid.UUID, newNetworkT) (networkT, error), func() error, error) { +) (func(userT, newNetworkT) (networkT, error), func() error, error) { q := addNetworkSQL(prefix) writeStmt, err := db.Prepare(q.write) @@ -349,25 +836,172 @@ func addNetworkStmt( } fn := func( - userID guuid.UUID, + user userT, newNetwork newNetworkT, ) (networkT, error) { network := networkT{ - uuid: newNetwork.uuid, - name: newNetwork.name, + uuid: newNetwork.uuid, + createdBy: user.uuid, + name: newNetwork.name, + description: newNetwork.description, + type_: newNetwork.type_, + } + + member := memberT{ } var timestr string - network_id_bytes := newNetwork.uuid[:] - user_id_bytes := userID[:] - err := writeStmt.QueryRow( - network_id_bytes, - newNetwork.name, - user_id_bytes, - ).Scan(&network.id, ×tr) + { + rows, err := writeStmt.Query( + newNetwork.uuid[:], + newNetwork.name, + newNetwork.description, + newNetwork.type_, + user.id, + ) + if err != nil { + return networkT{}, err + } + defer rows.Close() + + { + if !rows.Next() { + return networkT{}, sql.ErrNoRows + } + + err := rows.Scan(&network.id, ×tr) + if err != nil { + return networkT{}, err + } + + network.timestamp, err = time.Parse( + time.RFC3339Nano, + timestr, + ) + if err != nil { + return networkT{}, err + } + } + + { + if !rows.Next() { + return networkT{}, sql.ErrNoRows + } + + err := rows.Scan(&member.id, ×tr) + if err != nil { + return networkT{}, err + } + + member.timestamp, err = time.Parse( + time.RFC3339Nano, + timestr, + ) + if err != nil { + return networkT{}, err + } + } + + { + if rows.Next() { + return networkT{}, errors.New("FIXME") + } + err := rows.Err() + + if err != nil { + return networkT{}, err + } + } + } + + return network, nil + } + + return fn, writeStmt.Close, nil +} + +func getNetworkSQL(prefix string) queryT { + const tmpl_read = ` + SELECT + "%s_networks".id, + "%s_networks".timestamp, + "%s_users".uuid, + "%s_networks".name, + "%s_networks".description, + "%s_networks".type + FROM "%s_networks" + JOIN "%s_users" ON + "%s_users".id = "%s_networks".creator_id + WHERE + "%s_networks".uuid = $networkUUID AND + $userID IN ( + SELECT id FROM "%s_users" + WHERE id = $userID AND deleted = false + ) AND + ( + "%s_networks".type IN ('public', 'unlisted') OR + $userID IN ( + SELECT user_id FROM "%s_members" + WHERE + user_id = $userID AND + network_id = "%s_networks".id + ) + ); + ` + return queryT{ + read: fmt.Sprintf( + tmpl_read, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + ), + } +} + +func getNetworkStmt( + db *sql.DB, + prefix string, +) (func(userT, guuid.UUID) (networkT, error), func() error, error) { + q := getNetworkSQL(prefix) + + readStmt, err := db.Prepare(q.read) + if err != nil { + return nil, nil, err + } + + fn := func(user userT, networkID guuid.UUID) (networkT, error) { + network := networkT{ + uuid: networkID, + } + + var ( + timestr string + creator_id_bytes []byte + ) + err := readStmt.QueryRow(networkID[:], user.id).Scan( + &network.id, + ×tr, + &creator_id_bytes, + &network.name, + &network.description, + &network.type_, + ) if err != nil { return networkT{}, err } + network.createdBy = guuid.UUID(creator_id_bytes) network.timestamp, err = time.Parse(time.RFC3339Nano, timestr) if err != nil { @@ -377,14 +1011,341 @@ func addNetworkStmt( return network, nil } + return fn, readStmt.Close, nil +} + +func networkEach(rows *sql.Rows, callback func(networkT) error) error { + if rows == nil { + return nil + } + + for rows.Next() { + var ( + network networkT + timestr string + network_id_bytes []byte + ) + err := rows.Scan( + &network.id, + ×tr, + &network_id_bytes, + ) + if err != nil { + return g.WrapErrors(rows.Close(), err) + } + network.uuid = guuid.UUID(network_id_bytes) + + network.timestamp, err = time.Parse(time.RFC3339Nano, timestr) + if err != nil { + return g.WrapErrors(rows.Close(), err) + } + + err = callback(network) + if err != nil { + return g.WrapErrors(rows.Close(), err) + } + } + + return g.WrapErrors(rows.Err(), rows.Close()) +} + +func networksSQL(prefix string) queryT { + const tmpl_read = ` + -- FIXME + ` + return queryT{ + read: fmt.Sprintf(tmpl_read, prefix), + } +} + +func networksStmt( + db *sql.DB, + prefix string, +) (func(userT) (*sql.Rows, error), func() error, error) { + q := networksSQL(prefix) + + readStmt, err := db.Prepare(q.read) + if err != nil { + return nil, nil, err + } + + fn := func(user userT) (*sql.Rows, error) { + return readStmt.Query(user.uuid[:]) + } + + return fn, readStmt.Close, nil +} + +func setNetworkSQL(prefix string) queryT { + const tmpl_write = ` + ` + return queryT{ + write: fmt.Sprintf(tmpl_write, prefix), + } +} + +func setNetworkStmt( + db *sql.DB, + prefix string, +) (func(userT, networkT) error, func() error, error) { + q := setNetworkSQL(prefix) + + writeStmt, err := db.Prepare(q.write) + if err != nil { + return nil, nil, err + } + + fn := func(user userT, network networkT) error { + _, err := writeStmt.Exec(network) + return err + } + return fn, writeStmt.Close, nil } -func addChannelSQL(prefix string) queryT { +func nipNetworkSQL(prefix string) queryT { + const tmpl_write = ` + ` + return queryT{ + write: fmt.Sprintf(tmpl_write, prefix), + } +} + +func nipNetworkStmt( + db *sql.DB, + prefix string, +) (func(userT, guuid.UUID) error, func() error, error) { + q := nipNetworkSQL(prefix) + + writeStmt, err := db.Prepare(q.write) + if err != nil { + return nil, nil, err + } + + fn := func(user userT, networkID guuid.UUID) error { + _, err := writeStmt.Exec(networkID[:]) + return err + } + + return fn, writeStmt.Close, nil +} + + +func addMemberSQL(prefix string) queryT { const tmpl_write = ` -- FIXME ` return queryT{ + write: fmt.Sprintf(tmpl_write), + } +} + +func addMemberStmt( + db *sql.DB, + prefix string, +) (func(userT, networkT, newMemberT) (memberT, error), func() error, error) { + q := addMemberSQL(prefix) + + writeStmt, err := db.Prepare(q.write) + if err != nil { + return nil, nil, err + } + + fn := func( + user userT, + network networkT, + newMember newMemberT, + ) (memberT, error) { + member := memberT{ + } + + var timestr string + err := writeStmt.QueryRow(network.uuid[:], newMember).Scan( + &member.id, + ×tr, + ) + if err != nil { + return memberT{}, err + } + + member.timestamp, err = time.Parse(time.RFC3339Nano, timestr) + if err != nil { + return memberT{}, err + } + + return member, nil + } + + return fn, writeStmt.Close, nil +} + +func showMemberSQL(prefix string) queryT { + const tmpl_read = ` + ` + return queryT{ + read: fmt.Sprintf(tmpl_read, prefix), + } +} + +func showMemberStmt( + db *sql.DB, + prefix string, +) (func(userT, guuid.UUID) (memberT, error), func() error, error) { + q := showMemberSQL(prefix) + + readStmt, err := db.Prepare(q.read) + if err != nil { + return nil, nil, err + } + + fn := func(user userT, memberID guuid.UUID) (memberT, error) { + member := memberT{ + uuid: memberID, + } + + var timestr string + err := readStmt.QueryRow(memberID[:]).Scan( + &member.id, + ×tr, + ) + if err != nil { + return memberT{}, err + } + + member.timestamp, err = time.Parse(time.RFC3339Nano, timestr) + if err != nil { + return memberT{}, err + } + + return member, err + } + + return fn, readStmt.Close, nil +} + +func memberEach(rows *sql.Rows, callback func(memberT) error) error { + if rows == nil { + return nil + } + + for rows.Next() { + var ( + member memberT + timestr string + member_id_bytes []byte + ) + err := rows.Scan( + &member.id, + ×tr, + &member_id_bytes, + ) + if err != nil { + return g.WrapErrors(rows.Close(), err) + } + // member.uuid = guuid.UUID(member_id_bytes) FIXME + + member.timestamp, err = time.Parse(time.RFC3339Nano, timestr) + if err != nil { + return g.WrapErrors(rows.Close(), err) + } + + err = callback(member) + if err != nil { + return g.WrapErrors(rows.Close(), err) + } + } + + return g.WrapErrors(rows.Err(), rows.Close()) +} + +func membersSQL(prefix string) queryT { + const tmpl_read = ` + -- FIXME + ` + return queryT{ + read: fmt.Sprintf(tmpl_read, prefix), + } +} + +func membersStmt( + db *sql.DB, + prefix string, +) (func(userT, guuid.UUID) (*sql.Rows, error), func() error, error) { + q := membersSQL(prefix) + + readStmt, err := db.Prepare(q.read) + if err != nil { + return nil, nil, err + } + + fn := func(user userT, networkID guuid.UUID) (*sql.Rows, error) { + return readStmt.Query(networkID[:]) + } + + return fn, readStmt.Close, nil +} + +func editMemberSQL(prefix string) queryT { + const tmpl_write = ` + ` + return queryT{ + write: fmt.Sprintf(tmpl_write, prefix), + } +} + +func editMemberStmt( + db *sql.DB, + prefix string, +) (func(userT, memberT) error, func() error, error) { + q := editMemberSQL(prefix) + + writeStmt, err := db.Prepare(q.write) + if err != nil { + return nil, nil, err + } + + fn := func(user userT, member memberT) error { + _, err := writeStmt.Exec(member) + return err + } + + return fn, writeStmt.Close, nil +} + +func dropMemberSQL(prefix string) queryT { + const tmpl_write = ` + ` + return queryT{ + write: fmt.Sprintf(tmpl_write), + } +} + +func dropMemberStmt( + db *sql.DB, + prefix string, +) (func(userT, guuid.UUID) error, func() error, error) { + q := dropMemberSQL(prefix) + + writeStmt, err := db.Prepare(q.write) + if err != nil { + return nil, nil, err + } + + fn := func(user userT, memberID guuid.UUID) error { + _, err := writeStmt.Exec(memberID[:]) + return err + } + + return fn, writeStmt.Close, nil +} + +func addChannelSQL(prefix string) queryT { + const tmpl_write = ` + INSERT INTO "%s_channels" ( + uuid, public_name, label, description, virtual + ) VALUES (?, ?, ?, ?, ?) RETURNING id, timestamp; + ` + return queryT{ write: fmt.Sprintf(tmpl_write, prefix), } } @@ -405,14 +1366,21 @@ func addChannelStmt( newChannel newChannelT, ) (channelT, error) { channel := channelT{ - name: newChannel.name, + uuid: newChannel.uuid, + // networkID[:], + publicName: newChannel.publicName, + label: newChannel.label, + description: newChannel.description, + virtual: newChannel.virtual, } var timestr string - network_id_bytes := networkID[:] err := writeStmt.QueryRow( - network_id_bytes, - newChannel.name, + newChannel.uuid[:], + newChannel.publicName, + newChannel.label, + newChannel.description, + newChannel.virtual, ).Scan(&channel.id, ×tr) if err != nil { return channelT{}, err @@ -426,7 +1394,7 @@ func addChannelStmt( return channel, nil } - return fn, nil, nil + return fn, writeStmt.Close, nil } func channelEach(rows *sql.Rows, callback func(channelT) error) error { @@ -446,17 +1414,18 @@ func channelEach(rows *sql.Rows, callback func(channelT) error) error { &channel_id_bytes, ) if err != nil { - g.WrapErrors(rows.Close(), err) + return g.WrapErrors(rows.Close(), err) } + channel.uuid = guuid.UUID(channel_id_bytes) channel.timestamp, err = time.Parse(time.RFC3339Nano, timestr) if err != nil { - g.WrapErrors(rows.Close(), err) + return g.WrapErrors(rows.Close(), err) } err = callback(channel) if err != nil { - g.WrapErrors(rows.Close(), err) + return g.WrapErrors(rows.Close(), err) } } @@ -483,39 +1452,150 @@ func channelsStmt( return nil, nil, err } - fn := func(workspaceID guuid.UUID) (*sql.Rows, error) { - return readStmt.Query(workspaceID) + fn := func(networkID guuid.UUID) (*sql.Rows, error) { + return readStmt.Query(networkID[:]) } return fn, readStmt.Close, nil } -func eventEach(rows *sql.Rows, callback func(eventT) error) error { +func topicSQL(prefix string) queryT { + const tmpl_write = ` + ` + return queryT{ + write: fmt.Sprintf(tmpl_write, prefix), + } +} + +func topicStmt( + db *sql.DB, + prefix string, +) (func(channelT) error, func() error, error) { + q := topicSQL(prefix) + + writeStmt, err := db.Prepare(q.write) + if err != nil { + return nil, nil, err + } + + fn := func(channel channelT) error { + _, err := writeStmt.Exec(channel) + return err + } + + return fn, writeStmt.Close, nil +} + +func endChannelSQL(prefix string) queryT { + const tmpl_write = ` + ` + return queryT{ + write: fmt.Sprintf(tmpl_write, prefix), + } +} + +func endChannelStmt( + db *sql.DB, + prefix string, +) (func(guuid.UUID) error, func() error, error) { + q := endChannelSQL(prefix) + + writeStmt, err := db.Prepare(q.write) + if err != nil { + return nil, nil, err + } + + fn := func(channelID guuid.UUID) error { + _, err := writeStmt.Exec(channelID[:]) + return err + } + + return fn, writeStmt.Close, nil +} + +func joinSQL(prefix string) queryT { + const tmpl_write = ` + -- FIXME + ` + return queryT{ + write: fmt.Sprintf(tmpl_write, prefix), + } +} + +func joinStmt( + db *sql.DB, + prefix string, +) (func(guuid.UUID, guuid.UUID) error, func() error, error) { + q := joinSQL(prefix) + + writeStmt, err := db.Prepare(q.write) + if err != nil { + return nil, nil, err + } + + fn := func(memberID guuid.UUID, channelID guuid.UUID) error { + _, err := writeStmt.Exec(memberID[:], channelID[:]) + return err + } + + return fn, writeStmt.Close, nil +} + +func partSQL(prefix string) queryT { + const tmpl_write = ` + -- FIXME + ` + return queryT{ + write: fmt.Sprintf(tmpl_write, prefix), + } +} + +func partStmt( + db *sql.DB, + prefix string, +) (func(guuid.UUID, guuid.UUID) error, func() error, error) { + q := partSQL(prefix) + + writeStmt, err := db.Prepare(q.write) + if err != nil { + return nil, nil, err + } + + fn := func(memberID guuid.UUID, channelID guuid.UUID) error { + _, err := writeStmt.Exec(memberID[:], channelID[:]) + return err + } + + return fn, writeStmt.Close, nil +} + +func nameEach(rows *sql.Rows, callback func(memberT) error) error { if rows == nil { return nil } for rows.Next() { var ( - event eventT - timestr string - event_id_bytes []byte + member memberT + timestr string + member_id_bytes []byte ) err := rows.Scan( - &event.id, + &member.id, ×tr, - &event_id_bytes, + &member_id_bytes, ) if err != nil { return g.WrapErrors(rows.Close(), err) } + member.uuid = guuid.UUID(member_id_bytes) - event.timestamp, err = time.Parse(time.RFC3339Nano, timestr) + member.timestamp, err = time.Parse(time.RFC3339Nano, timestr) if err != nil { return g.WrapErrors(rows.Close(), err) } - err = callback(event) + err = callback(member) if err != nil { return g.WrapErrors(rows.Close(), err) } @@ -524,7 +1604,7 @@ func eventEach(rows *sql.Rows, callback func(eventT) error) error { return g.WrapErrors(rows.Err(), rows.Close()) } -func allAfterSQL(prefix string) queryT { +func namesSQL(prefix string) queryT { const tmpl_read = ` -- FIXME ` @@ -533,19 +1613,19 @@ func allAfterSQL(prefix string) queryT { } } -func allAfterStmt( +func namesStmt( db *sql.DB, prefix string, -) (func (guuid.UUID) (*sql.Rows, error), func() error, error) { - q := allAfterSQL(prefix) +) (func(guuid.UUID) (*sql.Rows, error), func() error, error) { + q := namesSQL(prefix) readStmt, err := db.Prepare(q.read) if err != nil { return nil, nil, err } - fn := func(eventID guuid.UUID) (*sql.Rows, error) { - return readStmt.Query(eventID) + fn := func(channelID guuid.UUID) (*sql.Rows, error) { + return readStmt.Query(channelID[:]) } return fn, readStmt.Close, nil @@ -553,17 +1633,25 @@ func allAfterStmt( func addEventSQL(prefix string) queryT { const tmpl_write = ` - -- FIXME + INSERT INTO "%s_channel_events" ( + uuid, channel_id, connection_uuid, type, payload + ) VALUES ( + ?, + (SELECT id FROM "%s_channels" WHERE uuid = ?), + ?, + ?, + ? + ) RETURNING id, timestamp; ` return queryT{ - write: fmt.Sprintf(tmpl_write, prefix), + write: fmt.Sprintf(tmpl_write, prefix, prefix), } } func addEventStmt( db *sql.DB, prefix string, -) (func (string, string) error, func() error, error) { +) (func (newEventT) (eventT, error), func() error, error) { q := addEventSQL(prefix) writeStmt, err := db.Prepare(q.write) @@ -571,34 +1659,201 @@ func addEventStmt( return nil, nil, err } - fn := func(type_ string, payload string) error { - _, err := writeStmt.Exec(type_, payload) - return err + fn := func(newEvent newEventT) (eventT, error) { + event := eventT{ + uuid: newEvent.eventID, + channelID: newEvent.channelID, + connectionID: newEvent.connectionID, + type_: newEvent.type_, + payload: newEvent.payload, + } + + var timestr string + err := writeStmt.QueryRow( + newEvent.eventID[:], + newEvent.channelID[:], + newEvent.connectionID[:], + newEvent.type_, + newEvent.payload, + ).Scan(&event.id, ×tr) + if err != nil { + return eventT{}, err + } + + event.timestamp, err = time.Parse(time.RFC3339Nano, timestr) + if err != nil { + return eventT{}, err + } + + return event, nil } return fn, writeStmt.Close, nil } +func eventEach(rows *sql.Rows, callback func(eventT) error) error { + if rows == nil { + return nil + } + + for rows.Next() { + var ( + event eventT + timestr string + event_id_bytes []byte + channel_id_bytes []byte + connection_id_bytes []byte + ) + err := rows.Scan( + &event.id, + ×tr, + &event_id_bytes, + &channel_id_bytes, + &connection_id_bytes, + &event.type_, + &event.payload, + ) + if err != nil { + rows.Close() + return err + } + event.uuid = guuid.UUID(event_id_bytes) + event.channelID = guuid.UUID(channel_id_bytes) + event.connectionID = guuid.UUID(connection_id_bytes) + + event.timestamp, err = time.Parse(time.RFC3339Nano, timestr) + if err != nil { + rows.Close() + return err + } + + err = callback(event) + if err != nil { + rows.Close() + return err + } + } + + return g.WrapErrors(rows.Err(), rows.Close()) +} + +func allAfterSQL(prefix string) queryT { + const tmpl_read = ` + WITH landmark_event AS ( + SELECT id, channel_id + FROM "%s_channel_events" + WHERE uuid = ? + ) + SELECT + "%s_channel_events".id, + "%s_channel_events".timestamp, + "%s_channel_events".uuid, + "%s_channels".uuid, + "%s_channel_events".connection_uuid, + "%s_channel_events".type, + "%s_channel_events".payload + FROM "%s_channel_events" + JOIN "%s_channels" ON + "%s_channel_events".channel_id = "%s_channels".id + WHERE + "%s_channel_events".id > ( + SELECT id FROM landmark_event + ) AND channel_id = ( + SELECT channel_id FROM landmark_event + ); + ` + return queryT{ + read: fmt.Sprintf( + tmpl_read, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + prefix, + ), + } +} + +func allAfterStmt( + db *sql.DB, + prefix string, +) (func (guuid.UUID) (*sql.Rows, error), func() error, error) { + q := allAfterSQL(prefix) + + readStmt, err := db.Prepare(q.read) + if err != nil { + return nil, nil, err + } + + fn := func(eventID guuid.UUID) (*sql.Rows, error) { + return readStmt.Query(eventID[:]) + } + + return fn, readStmt.Close, nil +} + func initDB( db *sql.DB, prefix string, ) (queriesT, error) { createTablesErr := createTables(db, prefix) -// addWorkspace, addWorkspaceClose, addWorkspaceErr := addWorkspaceStmt(db, prefix) -// addWorkspace, addWorkspaceClose, addWorkspaceErr := addWorkspaceQ(db, p) + createUser, createUserClose, createUserErr := createUserStmt(db, prefix) + userByUUID, userByUUIDClose, userByUUIDErr := userByUUIDStmt(db, prefix) + updateUser, updateUserClose, updateUserErr := updateUserStmt(db, prefix) + deleteUser, deleteUserClose, deleteUserErr := deleteUserStmt(db, prefix) addNetwork, addNetworkClose, addNetworkErr := addNetworkStmt(db, prefix) + getNetwork, getNetworkClose, getNetworkErr := getNetworkStmt(db, prefix) + networks, networksClose, networksErr := networksStmt(db, prefix) + setNetwork, setNetworkClose, setNetworkErr := setNetworkStmt(db, prefix) + nipNetwork, nipNetworkClose, nipNetworkErr := nipNetworkStmt(db, prefix) + addMember, addMemberClose, addMemberErr := addMemberStmt(db, prefix) + showMember, showMemberClose, showMemberErr := showMemberStmt(db, prefix) + members, membersClose, membersErr := membersStmt(db, prefix) + editMember, editMemberClose, editMemberErr := editMemberStmt(db, prefix) + dropMember, dropMemberClose, dropMemberErr := dropMemberStmt(db, prefix) addChannel, addChannelClose, addChannelErr := addChannelStmt(db, prefix) channels, channelsClose, channelsErr := channelsStmt(db, prefix) - allAfter, allAfterClose, allAfterErr := allAfterStmt(db, prefix) + topic, topicClose, topicErr := topicStmt(db, prefix) + endChannel, endChannelClose, endChannelErr := endChannelStmt(db, prefix) + join, joinClose, joinErr := joinStmt(db, prefix) + part, partClose, partErr := partStmt(db, prefix) + names, namesClose, namesErr := namesStmt(db, prefix) addEvent, addEventClose, addEventErr := addEventStmt(db, prefix) + allAfter, allAfterClose, allAfterErr := allAfterStmt(db, prefix) err := g.SomeError( createTablesErr, + createUserErr, + userByUUIDErr, + updateUserErr, + deleteUserErr, addNetworkErr, + getNetworkErr, + networksErr, + setNetworkErr, + nipNetworkErr, + addMemberErr, + showMemberErr, + membersErr, + editMemberErr, + dropMemberErr, addChannelErr, channelsErr, - allAfterErr, + topicErr, + endChannelErr, + joinErr, + partErr, + namesErr, addEventErr, + allAfterErr, ) if err != nil { return queriesT{}, err @@ -606,21 +1861,137 @@ func initDB( close := func() error { return g.SomeFnError( + createUserClose, + userByUUIDClose, + updateUserClose, + deleteUserClose, addNetworkClose, + getNetworkClose, + networksClose, + setNetworkClose, + nipNetworkClose, + addMemberClose, + showMemberClose, + membersClose, + editMemberClose, + dropMemberClose, addChannelClose, channelsClose, - allAfterClose, + topicClose, + endChannelClose, + joinClose, + partClose, + namesClose, addEventClose, + allAfterClose, ) } var connMutex sync.RWMutex return queriesT{ - addNetwork: func(a guuid.UUID, b newNetworkT) (networkT, error) { + createUser: func(a newUserT) (userT, error) { + connMutex.RLock() + defer connMutex.RUnlock() + return createUser(a) + }, + userByUUID: func(a guuid.UUID) (userT, error) { + connMutex.RLock() + defer connMutex.RUnlock() + return userByUUID(a) + }, + updateUser: func(a userT) error { + connMutex.RLock() + defer connMutex.RUnlock() + return updateUser(a) + }, + deleteUser: func(a guuid.UUID) error { + connMutex.RLock() + defer connMutex.RUnlock() + return deleteUser(a) + }, + addNetwork: func(a userT, b newNetworkT) (networkT, error) { connMutex.RLock() defer connMutex.RUnlock() return addNetwork(a, b) }, + getNetwork: func(a userT, b guuid.UUID) (networkT, error) { + connMutex.RLock() + defer connMutex.RUnlock() + return getNetwork(a, b) + }, + networks: func( + a userT, + callback func(networkT) error, + ) error { + var ( + err error + rows *sql.Rows + ) + { + connMutex.RLock() + defer connMutex.RUnlock() + rows, err = networks(a) + } + if err != nil { + return err + } + + return networkEach(rows, callback) + }, + setNetwork: func(a userT, b networkT) error { + connMutex.RLock() + defer connMutex.RUnlock() + return setNetwork(a, b) + }, + nipNetwork: func(a userT, b guuid.UUID) error { + connMutex.RLock() + defer connMutex.RUnlock() + return nipNetwork(a, b) + }, + addMember: func( + a userT, + b networkT, + c newMemberT, + ) (memberT, error) { + connMutex.RLock() + defer connMutex.RUnlock() + return addMember(a, b, c) + }, + showMember: func(a userT, b guuid.UUID) (memberT, error) { + connMutex.RLock() + defer connMutex.RUnlock() + return showMember(a, b) + }, + members: func( + a userT, + b guuid.UUID, + callback func(memberT) error, + ) error { + var ( + err error + rows *sql.Rows + ) + { + connMutex.RLock() + defer connMutex.RUnlock() + rows, err = members(a, b) + } + if err != nil { + return err + } + + return memberEach(rows, callback) + }, + editMember: func(a userT, b memberT) error { + connMutex.RLock() + defer connMutex.RUnlock() + return editMember(a, b) + }, + dropMember: func(a userT, b guuid.UUID) error { + connMutex.RLock() + defer connMutex.RUnlock() + return dropMember(a, b) + }, addChannel: func( a guuid.UUID, b newChannelT, ) (channelT, error) { @@ -647,6 +2018,47 @@ func initDB( return channelEach(rows, callback) }, + topic: func(a channelT) error { + connMutex.RLock() + defer connMutex.RUnlock() + return topic(a) + }, + endChannel: func(a guuid.UUID) error { + connMutex.RLock() + defer connMutex.RUnlock() + return endChannel(a) + }, + join: func(a guuid.UUID, b guuid.UUID) error { + connMutex.RLock() + defer connMutex.RUnlock() + return join(a, b) + }, + part: func(a guuid.UUID, b guuid.UUID) error { + connMutex.RLock() + defer connMutex.RUnlock() + return part(a, b) + }, + names: func(a guuid.UUID, callback func(memberT) error) error { + var ( + err error + rows *sql.Rows + ) + { + connMutex.RLock() + defer connMutex.RUnlock() + rows, err = names(a) + } + if err != nil { + return err + } + + return nameEach(rows, callback) + }, + addEvent: func(a newEventT) (eventT, error) { + connMutex.RLock() + defer connMutex.RUnlock() + return addEvent(a) + }, allAfter: func( a guuid.UUID, callback func(eventT) error, @@ -666,11 +2078,6 @@ func initDB( return eventEach(rows, callback) }, - addEvent: func(a string, b string) error { - connMutex.RLock() - defer connMutex.RUnlock() - return addEvent(a, b) - }, close: func() error { connMutex.Lock() defer connMutex.Unlock() @@ -1163,3 +2570,4 @@ func Main() { } // FIXME: review usage of g.Fatal() +// https://gist.github.com/xero/2d6e4b061b4ecbeb9f99 diff --git a/tests/papod.go b/tests/papod.go index 5f07b43..aa16ded 100644 --- a/tests/papod.go +++ b/tests/papod.go @@ -2,11 +2,15 @@ package papod import ( "bufio" + "crypto/rand" "database/sql" "errors" "fmt" + "io" "os" + "reflect" "strings" + "time" "golite" "guuid" @@ -15,6 +19,18 @@ import ( +func mknstring(n int) string { + buffer := make([]byte, n) + _, err := io.ReadFull(rand.Reader, buffer) + g.TErrorIf(err) + return string(buffer) +} + +func mkstring() string { + return mknstring(32) +} + + func test_defaultPrefix() { g.TestStart("defaultPrefix") @@ -23,26 +39,80 @@ func test_defaultPrefix() { }) } -func test_tryRollback() { +func test_serialized() { // FIXME } -func test_inTx() { +func test_execSerialized() { // FIXME } +func test_tryRollback() { + g.TestStart("tryRollback()") + + myErr := errors.New("bottom error") + + db, err := sql.Open(golite.DriverName, golite.InMemory) + g.TErrorIf(err) + defer db.Close() + + + g.Testing("the error is propagated if rollback doesn't fail", func() { + tx, err := db.Begin() + g.TErrorIf(err) + + err = tryRollback(tx, myErr) + g.TAssertEqual(err, myErr) + }) + + g.Testing("a wrapped error when rollback fails", func() { + tx, err := db.Begin() + g.TErrorIf(err) + + err = tx.Commit() + g.TErrorIf(err) + + err = tryRollback(tx, myErr) + g.TAssertEqual(reflect.DeepEqual(err, myErr), false) + g.TAssertEqual(errors.Is(err, myErr), true) + }) +} + +func test_inTx() { + g.TestStart("inTx()") + + db, err := sql.Open(golite.DriverName, golite.InMemory) + g.TErrorIf(err) + defer db.Close() + + + g.Testing("when fn() errors, we propagate it", func() { + myErr := errors.New("to be propagated") + err := inTx(db, func(tx *sql.Tx) error { + return myErr + }) + g.TAssertEqual(err, myErr) + }) + + g.Testing("on nil error we get nil", func() { + err := inTx(db, func(tx *sql.Tx) error { + return nil + }) + g.TErrorIf(err) + }) +} + func test_createTables() { - return g.TestStart("createTables()") - db, err := sql.Open(golite.DriverName, ":memory:") + db, err := sql.Open(golite.DriverName, golite.InMemory) g.TErrorIf(err) defer db.Close() g.Testing("tables exist afterwards", func() { const tmpl_read = ` - SELECT id FROM "%s_events" LIMIT 1; + SELECT id FROM "%s_channel_events" LIMIT 1; ` qRead := fmt.Sprintf(tmpl_read, defaultPrefix) @@ -65,49 +135,1062 @@ func test_createTables() { }) } +func test_createUserStmt() { + g.TestStart("createUserStmt()") + + const ( + prefix = defaultPrefix + ) + + db, err := sql.Open(golite.DriverName, golite.InMemory) + g.TErrorIf(err) + g.TErrorIf(createTables(db, prefix)) + + createUser, createUserClose, createUserErr := createUserStmt(db, prefix) + g.TErrorIf(createUserErr) + defer g.SomeFnError( + createUserClose, + db.Close, + ) + + + g.Testing("userID's must be unique", func() { + newUser := newUserT{ + uuid: guuid.New(), + } + + _, err1 := createUser(newUser) + _, err2 := createUser(newUser) + g.TErrorIf(err1) + g.TAssertEqual( + err2.(golite.Error).ExtendedCode, + golite.ErrConstraintUnique, + ) + }) + + g.Testing("a new user starts without a pictureID", func() { + newUser := newUserT{ + uuid: guuid.New(), + } + + user, err := createUser(newUser) + g.TErrorIf(err) + g.TAssertEqual(user.pictureID == nil, true) + }) + + g.Testing("the database fills default and generated values", func() { + newUser := newUserT{ + uuid: guuid.New(), + username: "the username", + displayName: "the display name", + } + + user, err := createUser(newUser) + g.TErrorIf(err) + + g.TAssertEqual(user.id == 0, false) + g.TAssertEqual(user.timestamp == time.Time{}, false) + g.TAssertEqual(user.uuid, newUser.uuid) + g.TAssertEqual(user.username, "the username") + g.TAssertEqual(user.displayName, "the display name") + }) + + g.Testing("users can have duplicate names and usernames", func() { + username := mkstring() + displayName := mkstring() + + newUser1 := newUserT{ + uuid: guuid.New(), + username: username, + displayName: displayName, + } + + newUser2 := newUserT{ + uuid: guuid.New(), + username: username, + displayName: displayName, + } + + _, err1 := createUser(newUser1) + _, err2 := createUser(newUser2) + g.TErrorIf(err1) + g.TErrorIf(err2) + }) + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + createUserClose(), + createUserClose(), + createUserClose(), + )) + }) +} + +func test_userByUUIDStmt() { + g.TestStart("userByUUIDStmt()") + + const ( + prefix = defaultPrefix + ) + + db, err := sql.Open(golite.DriverName, golite.InMemory) + g.TErrorIf(err) + g.TErrorIf(createTables(db, prefix)) + + createUser, createUserClose, createUserErr := createUserStmt(db, prefix) + userByUUID, userByUUIDClose, userByUUIDErr := userByUUIDStmt(db, prefix) + deleteUser, deleteUserClose, deleteUserErr := deleteUserStmt(db, prefix) + g.TErrorIf(g.SomeError( + createUserErr, + deleteUserErr, + userByUUIDErr, + )) + defer g.SomeFnError( + createUserClose, + deleteUserClose, + userByUUIDClose, + db.Close, + ) + + + g.Testing("when a user doesn't exist, we get sql.ErrNoRows", func() { + _, err := userByUUID(guuid.New()) + g.TAssertEqual(err, sql.ErrNoRows) + }) + + g.Testing("after creating, we can retrieve the user", func() { + newUser := newUserT{ + uuid: guuid.New(), + username: mkstring(), + displayName: mkstring(), + } + + user1, err := createUser(newUser) + g.TErrorIf(err) + + user2, err := userByUUID(newUser.uuid) + g.TErrorIf(err) + + g.TAssertEqual(user2.uuid, newUser.uuid) + g.TAssertEqual(user2.username, newUser.username) + g.TAssertEqual(user2.displayName, newUser.displayName) + g.TAssertEqual(user2.pictureID == nil, true) + + g.TAssertEqual(user2, user1) + }) + + g.Testing("we can't retrieve a user once deleted",func() { + newUser := newUserT{ + uuid: guuid.New(), + } + + _, err := createUser(newUser) + g.TErrorIf(err) + + _, err = userByUUID(newUser.uuid) + g.TErrorIf(err) + + err = deleteUser(newUser.uuid) + g.TErrorIf(err) + + _, err = userByUUID(newUser.uuid) + g.TAssertEqual(err, sql.ErrNoRows) + }) + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + userByUUIDClose(), + userByUUIDClose(), + userByUUIDClose(), + )) + }) +} + +func test_updateUserStmt() { + g.TestStart("updateUserStmt()") + + const ( + prefix = defaultPrefix + ) + + db, err := sql.Open(golite.DriverName, golite.InMemory) + g.TErrorIf(err) + g.TErrorIf(createTables(db, prefix)) + + createUser, createUserClose, createUserErr := createUserStmt(db, prefix) + userByUUID, userByUUIDClose, userByUUIDErr := userByUUIDStmt(db, prefix) + updateUser, updateUserClose, updateUserErr := updateUserStmt(db, prefix) + deleteUser, deleteUserClose, deleteUserErr := deleteUserStmt(db, prefix) + g.TErrorIf(g.SomeError( + createUserErr, + userByUUIDErr, + updateUserErr, + deleteUserErr, + )) + defer g.SomeFnError( + createUserClose, + userByUUIDClose, + updateUserClose, + deleteUserClose, + db.Close, + ) + + create := func() userT { + newUser := newUserT{ + uuid: guuid.New(), + username: mkstring(), + displayName: mkstring(), + } + + user, err := createUser(newUser) + g.TErrorIf(err) + + return user + } + + + g.Testing("a user needs to exist to be updated", func() { + virtualUser := userT{ + id: 1234, + } + + g.TAssertEqual(updateUser(virtualUser), sql.ErrNoRows) + }) + + g.Testing("after updating, fetching gives us the newer data", func() { + newUser := newUserT{ + uuid: guuid.New(), + username: "first username", + displayName: "first display name", + } + + user1, err := createUser(newUser) + g.TErrorIf(err) + + g.TAssertEqual(user1.username, newUser.username) + g.TAssertEqual(user1.displayName, newUser.displayName) + + + user2 := user1 + user2.username = "second username" + user2.displayName = "second display name" + + err = updateUser(user2) + g.TErrorIf(err) + + g.TAssertEqual(user2.id, user1.id) + g.TAssertEqual(user2.timestamp, user1.timestamp) + g.TAssertEqual(user2.uuid, user1.uuid) + + + user3, err := userByUUID(user2.uuid) + g.TErrorIf(err) + + g.TAssertEqual(user3, user2) + g.TAssertEqual(user3.username, "second username") + g.TAssertEqual(user3.displayName, "second display name") + }) + + g.Testing("can't update a deleted user", func() { + user := create() + + err = deleteUser(user.uuid) + g.TErrorIf(err) + + err = updateUser(user) + g.TAssertEqual(err, sql.ErrNoRows) + }) + + g.Testing("we can add (and remove) a picture to the user", func() { + user1 := create() + g.TAssertEqual(user1.pictureID == nil, true) + + pictureID := guuid.New() + user2 := user1 + user2.pictureID = &pictureID + err = updateUser(user2) + g.TErrorIf(err) + g.TAssertEqual(user2.pictureID == nil, false) + + user3, err := userByUUID(user1.uuid) + g.TErrorIf(err) + g.TAssertEqual(user3.pictureID == nil, false) + g.TAssertEqual(user3, user2) + + user4 := user3 + user4.pictureID = nil + err = updateUser(user4) + g.TErrorIf(err) + + user5, err := userByUUID(user1.uuid) + g.TErrorIf(err) + g.TAssertEqual(user5.pictureID == nil, true) + g.TAssertEqual(user5, user4) + }) + + g.Testing("we can't update the timestamp or uuid", func() { + user1 := create() + + user2 := user1 + user2.timestamp = user2.timestamp.Add(time.Minute * 1) + user2.uuid = guuid.New() + err = updateUser(user2) + g.TErrorIf(err) + + user3, err := userByUUID(user1.uuid) + g.TErrorIf(err) + g.TAssertEqual(user3, user1) + }) + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + updateUserClose(), + updateUserClose(), + updateUserClose(), + )) + }) +} + +func test_deleteUserStmt() { + g.TestStart("deleteUserStmt()") + + const ( + prefix = defaultPrefix + ) + + db, err := sql.Open(golite.DriverName, golite.InMemory) + g.TErrorIf(err) + g.TErrorIf(createTables(db, prefix)) + + createUser, createUserClose, createUserErr := createUserStmt(db, prefix) + deleteUser, deleteUserClose, deleteUserErr := deleteUserStmt(db, prefix) + g.TErrorIf(g.SomeError( + createUserErr, + deleteUserErr, + )) + defer g.SomeFnError( + createUserClose, + deleteUserClose, + ) + + + g.Testing("a user needs to exist to be deleted", func() { + err := deleteUser(guuid.New()) + g.TAssertEqual(err, sql.ErrNoRows) + }) + + g.Testing("error if deleted more than once", func() { + newUser := newUserT{ + uuid: guuid.New(), + } + + _, err := createUser(newUser) + g.TErrorIf(err) + + err1 := deleteUser(newUser.uuid) + err2 := deleteUser(newUser.uuid) + g.TErrorIf(err1) + g.TAssertEqual(err2, sql.ErrNoRows) + }) + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + deleteUserClose(), + deleteUserClose(), + deleteUserClose(), + )) + }) +} + func test_addNetworkStmt() { - return + return // FIXME g.TestStart("addNetworkStmt()") const ( prefix = defaultPrefix ) - db, err := sql.Open(golite.DriverName, ":memory:") + db, err := sql.Open(golite.DriverName, golite.InMemory) g.TErrorIf(err) g.TErrorIf(createTables(db, prefix)) + createUser, createUserClose, createUserErr := createUserStmt(db, prefix) + deleteUser, deleteUserClose, deleteUserErr := deleteUserStmt(db, prefix) addNetwork, addNetworkClose, addNetworkErr := addNetworkStmt(db, prefix) - g.TErrorIf(addNetworkErr) + g.TErrorIf(g.SomeError( + createUserErr, + deleteUserErr, + addNetworkErr, + )) defer g.SomeFnError( + createUserClose, + deleteUserClose, addNetworkClose, ) + create := func() userT { + newUser := newUserT{ + uuid: guuid.New(), + } + + user, err := createUser(newUser) + g.TErrorIf(err) + + return user + } + + + g.Testing("a user can create a newtwork", func() { + creator := create() + + newNetwork := newNetworkT{ + uuid: guuid.New(), + name: "the network name", + description: "the network description", + type_: NetworkType_Unlisted, + } + + network, err := addNetwork(creator, newNetwork) + g.TErrorIf(err) + + g.TAssertEqual(network.id == 0, false) + g.TAssertEqual(network.timestamp == time.Time{}, false) + g.TAssertEqual(network.uuid, newNetwork.uuid) + g.TAssertEqual(network.createdBy, creator.uuid) + g.TAssertEqual(network.name, "the network name") + g.TAssertEqual(network.description, "the network description") + g.TAssertEqual(network.type_, NetworkType_Unlisted) + }) + + g.Testing("the creator needs to exist", func() { + newNetwork := newNetworkT{ + uuid: guuid.New(), + } + + virtualUser := userT{ + uuid: guuid.New(), + } + + _, err := addNetwork(virtualUser, newNetwork) + g.TAssertEqual( + err.(golite.Error).ExtendedCode, + golite.ErrConstraintNotNull, + ) + }) g.Testing("we can't add the same network twice", func() { - // FIXME + creator := create() + + newNetwork := newNetworkT{ + uuid: guuid.New(), + name: mkstring(), + } + + _, err1 := addNetwork(creator, newNetwork) + _, err2 := addNetwork(creator, newNetwork) + g.TErrorIf(err1) + g.TAssertEqual( + err2.(golite.Error).ExtendedCode, + golite.ErrConstraintUnique, + ) }) g.Testing("a user can create multiple networks", func() { - userID := guuid.New() - newNetwork := newNetworkT{} + creator := create() + + newNetwork1 := newNetworkT{ + uuid: guuid.New(), + } + newNetwork2 := newNetworkT{ + uuid: guuid.New(), + } - network, err := addNetwork(userID, newNetwork) + network1, err1 := addNetwork(creator, newNetwork1) + network2, err2 := addNetwork(creator, newNetwork2) + g.TErrorIf(err1) + g.TErrorIf(err2) + + g.TAssertEqual(network1.createdBy, creator.uuid) + g.TAssertEqual(network2.createdBy, creator.uuid) + }) + + g.Testing("a deleted user can't create a network", func() { + creator := create() + + newNetwork1 := newNetworkT{ + uuid: guuid.New(), + } + newNetwork2 := newNetworkT{ + uuid: guuid.New(), + } + + _, err := addNetwork(creator, newNetwork1) g.TErrorIf(err) - g.TAssertEqual(network.createdBy, userID) + err = deleteUser(creator.uuid) + g.TErrorIf(err) + _, err = addNetwork(creator, newNetwork2) + g.TAssertEqual( + err.(golite.Error).ExtendedCode, + golite.ErrConstraintNotNull, + ) + }) + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + addNetworkClose(), + addNetworkClose(), + addNetworkClose(), + )) }) } -func test_addChannelStmt() { +func test_getNetworkStmt() { + return // FIXME + g.TestStart("getNetworkStmt()") + + const ( + prefix = defaultPrefix + ) + + db, err := sql.Open(golite.DriverName, golite.InMemory) + g.TErrorIf(err) + g.TErrorIf(createTables(db, prefix)) + + createUser, createUserClose, createUserErr := createUserStmt(db, prefix) + deleteUser, deleteUserClose, deleteUserErr := deleteUserStmt(db, prefix) + addNetwork, addNetworkClose, addNetworkErr := addNetworkStmt(db, prefix) + getNetwork, getNetworkClose, getNetworkErr := getNetworkStmt(db, prefix) + addMember, addMemberClose, addMemberErr := addMemberStmt(db, prefix) + dropMember, dropMemberClose, dropMemberErr := dropMemberStmt(db, prefix) + g.TErrorIf(g.SomeError( + createUserErr, + deleteUserErr, + addNetworkErr, + getNetworkErr, + addMemberErr, + dropMemberErr, + )) + defer g.SomeFnError( + createUserClose, + deleteUserClose, + addNetworkClose, + getNetworkClose, + addMemberClose, + dropMemberClose, + ) + + create := func() userT { + newUser := newUserT{ + uuid: guuid.New(), + } + + user, err := createUser(newUser) + g.TErrorIf(err) + + return user + } + + add := func(user userT, type_ NetworkType) networkT { + newNetwork := newNetworkT{ + uuid: guuid.New(), + type_: type_, + } + + network, err := addNetwork(user, newNetwork) + g.TErrorIf(err) + + return network + } + + + g.Testing("what we get is the same that was created", func() { + creator := create() + network1 := add(creator, NetworkType_Public) + + network2, err := getNetwork(creator, network1.uuid) + g.TErrorIf(err) + g.TAssertEqual(network2, network1) + }) + + g.Testing("a network needs to exist", func() { + _, err := getNetwork(create(), guuid.New()) + g.TAssertEqual(err, sql.ErrNoRows) + }) + + g.Testing("the probing user needs to exist", func() { + creator := create() + network := add(creator, NetworkType_Public) + + virtualUser := userT{ + id: 1234, + } + + _, err := getNetwork(creator, network.uuid) + g.TErrorIf(err) + + _, err = getNetwork(virtualUser, network.uuid) + g.TAssertEqual(err, sql.ErrNoRows) + }) + + g.Testing("the probing user can see any public network", func() { + creator := create() + user := create() + network := add(creator, NetworkType_Public) + + network1, err1 := getNetwork(creator, network.uuid) + network2, err2 := getNetwork(user, network.uuid) + g.TErrorIf(err1) + g.TErrorIf(err2) + g.TAssertEqual(network1, network) + g.TAssertEqual(network2, network) + }) + + g.Testing("the probing user sees the given unlisted network", func() { + creator := create() + user := create() + network := add(creator, NetworkType_Unlisted) + + network1, err1 := getNetwork(creator, network.uuid) + network2, err2 := getNetwork(user, network.uuid) + g.TErrorIf(err1) + g.TErrorIf(err2) + g.TAssertEqual(network1, network) + g.TAssertEqual(network2, network) + }) + + g.Testing("the probing user can't see a private network", func() { + creator := create() + user := create() + network := add(creator, NetworkType_Private) + + _, err1 := getNetwork(creator, network.uuid) + _, err2 := getNetwork(user, network.uuid) + g.TErrorIf(err1) + g.TAssertEqual(err2, sql.ErrNoRows) + }) + + g.Testing("the probing user must be a member to see it", func() { + creator := create() + member := create() + network := add(creator, NetworkType_Private) + newMember := newMemberT{ + userID: member.uuid, + } + + _, err := addMember(creator, network, newMember) + g.TErrorIf(err) + + network1, err1 := getNetwork(creator, network.uuid) + network2, err2 := getNetwork(member, network.uuid) + g.TErrorIf(err1) + g.TErrorIf(err2) + g.TAssertEqual(network1, network) + g.TAssertEqual(network2, network) + }) + + g.Testing("we can get the network if the creator was deleted", func() { + creator := create() + member := create() + network := add(creator, NetworkType_Public) + newMember := newMemberT{ + userID: member.uuid, + } + + _, err := addMember(creator, network, newMember) + g.TErrorIf(err) + + network1, err := getNetwork(creator, network.uuid) + g.TErrorIf(err) + + err = deleteUser(creator.uuid) + g.TErrorIf(err) + + network2, err := getNetwork(member, network.uuid) + g.TErrorIf(err) + g.TAssertEqual(network2, network1) + }) + + g.Testing("a deleted creator can't get a network", func() { + creator := create() + network := add(creator, NetworkType_Public) + + _, err := getNetwork(creator, network.uuid) + g.TErrorIf(err) + + err = deleteUser(creator.uuid) + g.TErrorIf(err) + + _, err = getNetwork(creator, network.uuid) + g.TAssertEqual(err, sql.ErrNoRows) + }) + + g.Testing("a deleted user can't get a public network", func() { + user := create() + network := add(create(), NetworkType_Public) + + _, err := getNetwork(user, network.uuid) + g.TErrorIf(err) + + err = deleteUser(user.uuid) + g.TErrorIf(err) + + _, err = getNetwork(user, network.uuid) + g.TAssertEqual(err, sql.ErrNoRows) + }) + + g.Testing("a deleted member can't get a private network", func() { + creator := create() + member := create() + network := add(creator, NetworkType_Private) + + _, err := getNetwork(member, network.uuid) + g.TErrorIf(err) + + err = deleteUser(member.uuid) + g.TErrorIf(err) + + _, err = getNetwork(member, network.uuid) + g.TAssertEqual(err, sql.ErrNoRows) + }) + + g.Testing("a removed member can't get a private network", func() { + creator := create() + member := create() + network := add(creator, NetworkType_Private) + newMember := newMemberT{ + userID: member.uuid, + } + + _, err := getNetwork(member, network.uuid) + g.TAssertEqual(err, sql.ErrNoRows) + + _, err = addMember(creator, network, newMember) + g.TErrorIf(err) + + _, err = getNetwork(member, network.uuid) + g.TErrorIf(err) + + err = dropMember(creator, member.uuid) + g.TErrorIf(err) + + _, err = getNetwork(member, network.uuid) + g.TAssertEqual(err, sql.ErrNoRows) + }) + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + getNetworkClose(), + getNetworkClose(), + getNetworkClose(), + )) + }) +} + +func test_networkEach() { + // FIXME +} + +func test_networksStmt() { + /* + FIXME + g.TestStart("networksStmt()") + + const ( + prefix = defaultPrefix + ) + + db, err := sql.Open(golite.DriverName, golite.InMemory) + g.TErrorIf(err) + g.TErrorIf(createTables(db, prefix)) + + addNetwork, addNetworkClose, addNetworkErr := addNetworkStmt(db, prefix) + networks, networksClose, networksErr := networksStmt(db, prefix) + g.TErrorIf(g.SomeError( + addNetworkErr, + networksErr, + )) + defer g.SomeFnError( + addNetworkClose, + networksClose, + db.Close, + ) + + nets := func(user userT) []networkT { + rows, err := networks(user) + g.TErrorIf(err) + + networkList := []networkT{} + err = networkEach(rows, func(network networkT) error { + networkList = append(networkList, network) + return nil + }) + g.TErrorIf(err) + + return networkList + } + + + g.Testing("when there are no networks, we get none", func() { + // FIXME + }) + + g.Testing("if we have only private networks, we also get none", func() { + // FIXME + }) + + g.Testing("we can get a list of public networks", func() { + // FIXME + }) + + g.Testing("a member user can see their's private networks", func() { + // FIXME + }) + + g.Testing("unlisted networks aren't shown", func() { + // FIXME + }) + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + networksClose(), + networksClose(), + networksClose(), + )) + }) + */ +} + +func test_setNetworkStmt() { return // FIXME + g.TestStart("setNetworkStmt()") + + const ( + prefix = defaultPrefix + ) + + db, err := sql.Open(golite.DriverName, golite.InMemory) + g.TErrorIf(err) + g.TErrorIf(createTables(db, prefix)) + + createUser, createUserClose, createUserErr := createUserStmt(db, prefix) + addNetwork, addNetworkClose, addNetworkErr := addNetworkStmt(db, prefix) + getNetwork, getNetworkClose, getNetworkErr := getNetworkStmt(db, prefix) + setNetwork, setNetworkClose, setNetworkErr := setNetworkStmt(db, prefix) + g.TErrorIf(g.SomeError( + createUserErr, + addNetworkErr, + getNetworkErr, + setNetworkErr, + )) + defer g.SomeFnError( + createUserClose, + addNetworkClose, + getNetworkClose, + setNetworkClose, + db.Close, + ) + + create := func() userT { + newUser := newUserT{ + uuid: guuid.New(), + } + + user, err := createUser(newUser) + g.TErrorIf(err) + + return user + } + + add := func(user userT) networkT { + newNetwork := newNetworkT{ + uuid: guuid.New(), + } + + network, err := addNetwork(user, newNetwork) + g.TErrorIf(err) + + return network + } + + + g.Testing("a network needs to exist to be updated", func() { + creator := create() + virtualNetwork := networkT{ + id: 1234, + } + + err := setNetwork(creator, virtualNetwork) + g.TAssertEqual(err, sql.ErrNoRows) + }) + + g.Testing("creator can change the network", func() { + // FIXME + }) + + g.Testing(`"network-settings-admin" can change the network`, func() { + // FIXME + }) + + g.Testing("ex-admin creator looses ability to change it", func() { + // FIXME + }) + + g.Testing("ex-member creator looses ability to change it", func() { + // FIXME + }) + + g.Testing("unauthorized users can't change the network", func() { + creator := create() + member := create() + network := add(creator) + + network.name = "member can't set the name" + err := setNetwork(member, network) + g.TAssertEqual(err, "403") + }) + + g.Testing("after setting, getting gives us the newer data", func() { + creator := create() + network1 := add(creator) + + network2 := network1 + network2.name = "first network name" + network2.description = "first network description" + network2.type_ = NetworkType_Private + + err := setNetwork(creator, network2) + g.TErrorIf(err) + + network3, err := getNetwork(creator, network1.uuid) + g.TErrorIf(err) + g.TAssertEqual(network3, network2) + }) + + g.Testing("the uuid, timestamp or creator never changes", func() { + creator := create() + network1 := add(creator) + + network2 := network1 + network2.uuid = guuid.New() + network2.timestamp = time.Time{} + network2.createdBy = guuid.New() + + err := setNetwork(creator, network2) + g.TErrorIf(err) + + network3, err := getNetwork(creator, network1.uuid) + g.TErrorIf(err) + g.TAssertEqual(network3, network1) + }) + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + setNetworkClose(), + setNetworkClose(), + setNetworkClose(), + )) + }) +} + +func test_nipNetworkStmt() { + // FIXME + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + // FIXME + )) + }) +} + +func test_addMemberStmt() { + /* + FIXME + g.TestStart("addMember()") + + const ( + prefix = defaultPrefix + ) + + db, err := sql.Open(golite.DriverName, golite.InMemory) + g.TErrorIf(err) + g.TErrorIf(createTables(db, prefix)) + + addNetwork, addNetworkClose, addNetworkErr := addNetworkStmt(db, prefix) + addMember, addMemberClose, addMemberErr := addMemberStmt(db, prefix) + g.TErrorIf(g.SomeError( + addNetworkErr, + addMemberErr, + )) + defer g.SomeFnError( + addNetworkClose, + addMemberClose, + ) + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + addMemberClose(), + addMemberClose(), + addMemberClose(), + )) + }) + */ +} + +func test_showMemberStmt() { + // FIXME + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + // FIXME + )) + }) +} + +func test_memberEach() { + // FIXME +} + +func test_membersStmt() { + // FIXME + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + // FIXME + )) + }) +} + +func test_editMemberStmt() { + // FIXME + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + // FIXME + )) + }) +} + +func test_dropMemberStmt() { + // FIXME + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + // FIXME + )) + }) +} + +func test_addChannelStmt() { + // FIXME + return g.TestStart("addChannelStmt()") const ( prefix = defaultPrefix ) - db, err := sql.Open(golite.DriverName, ":memory:") + db, err := sql.Open(golite.DriverName, golite.InMemory) g.TErrorIf(err) g.TErrorIf(createTables(db, prefix)) @@ -142,33 +1225,272 @@ func test_addChannelStmt() { } // private channels one is not a part of doesn't show up // channels only from the same workspace + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + // FIXME + )) + }) } -func test_initDB() { - g.TestStart("initDB()") +func test_channelEach() { + // FIXME +} - /* - q := new(liteq.Queue) - sql.Register("sqliteq", liteq.MakeDriver(q)) +func test_channelsStmt() { + // FIXME + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + // FIXME + )) + }) +} + +func test_topicStmt() { + // FIXME + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + // FIXME + )) + }) +} + +func test_endChannelStmt() { + // FIXME + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + // FIXME + )) + }) +} + +func test_joinStmt() { + // FIXME + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + // FIXME + )) + }) +} + +func test_partStmt() { + // FIXME + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + // FIXME + )) + }) +} + +func test_nameEach() { + // FIXME +} + +func test_namesStmt() { + // FIXME + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + // FIXME + )) + }) +} + +func test_addEventStmt() { + return // FIXME + g.TestStart("addEventStmt()") + + const ( + prefix = defaultPrefix + ) + + db, err := sql.Open(golite.DriverName, golite.InMemory) + g.TErrorIf(err) + g.TErrorIf(createTables(db, prefix)) + + addEvent, addEventClose, addEventErr := addEventStmt(db, prefix) + g.TErrorIf(addEventErr) + defer g.SomeFnError( + addEventClose, + db.Close, + ) - db, err := sql.Open("sqliteq", "file:papod-test.db?mode=memory&cache=shared") - if err != nil { - panic(err) + + g.Testing("we can create new events", func() { + newEvent := newEventT{ + eventID: guuid.New(), + channelID: guuid.New(), + connectionID: guuid.New(), + type_: "user-message", + payload: "xablau", + } + + _, err := addEvent(newEvent) + g.TErrorIf(err) + }) + + g.Testing("eventID's must be unique", func() { + // FIXME + }) + + g.Testing("the database fills the generated values", func() { + const ( + type_ = "user-message" + payload = "the payload" + ) + eventID := guuid.New() + newEvent := newEventT{ + eventID: eventID, + channelID: guuid.New(), + connectionID: guuid.New(), + type_: type_, + payload: payload, + } + + event, err := addEvent(newEvent) + g.TErrorIf(err) + + g.TAssertEqual(event.id == 0, false) + g.TAssertEqual(event.timestamp == time.Time{}, false) + g.TAssertEqual(event.channelID == guuid.UUID{}, false) + g.TAssertEqual(event.connectionID == guuid.UUID{}, false) + g.TAssertEqual(event.uuid, eventID) + g.TAssertEqual(event.type_, type_) + g.TAssertEqual(event.payload, payload) + }) + + g.Testing("multiple messages can have the same connectionID", func() { + // FIXME + }) + + g.Testing("messages can be dupicated: same type and payload", func() { + // FIXME + }) + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + addEventClose(), + addEventClose(), + addEventClose(), + )) + }) +} + +func test_eventEach() { + // FIXME +} + +func test_allAfterStmt() { + g.TestStart("allAfter()") + + const ( + prefix = defaultPrefix + ) + + db, err := sql.Open(golite.DriverName, golite.InMemory) + g.TErrorIf(err) + g.TErrorIf(createTables(db, prefix)) + + addChannel, addChannelClose, addChannelErr := addChannelStmt(db, prefix) + addEvent, addEventClose, addEventErr := addEventStmt(db, prefix) + allAfter, allAfterClose, allAfterErr := allAfterStmt(db, prefix) + g.TErrorIf(g.SomeError( + addChannelErr, + addEventErr, + allAfterErr, + )) + defer g.SomeFnError( + addChannelClose, + addEventClose, + allAfterClose, + db.Close, + ) + + channel := func(publicName string) channelT { + networkID := guuid.New() + newChannel := newChannelT{ + uuid: guuid.New(), + publicName: publicName, + } + + channel, err := addChannel(networkID, newChannel) + g.TErrorIf(err) + + return channel } - // defer db.Close() - *q, err = liteq.New(db) - if err != nil { - panic(err) + add := func(channelID guuid.UUID, type_ string, payload string) eventT { + newEvent := newEventT{ + eventID: guuid.New(), + channelID: channelID, + connectionID: guuid.New(), + type_: type_, + payload: payload, + } + + event, err := addEvent(newEvent) + g.TErrorIf(err) + + return event } - // defer q.Close() - // _, err = New(db, *q) - // _, err = initDB(db, - if err != nil { - panic(err) + all := func(eventID guuid.UUID) []eventT { + rows, err := allAfter(eventID) + g.TErrorIf(err) + + events := []eventT{} + err = eventEach(rows, func(event eventT) error { + events = append(events, event) + return nil + }) + g.TErrorIf(err) + + return events } - */ + + + g.Testing("after joining the channel, there are no events", func() { + ch := channel("#ch") + join := add(ch.uuid, "user-join", "fulano") + + expected := []eventT{ + add(ch.uuid, "user-join", "ciclano"), + add(ch.uuid, "user-join", "beltrano"), + add(ch.uuid, "user-message", "hi there"), + } + + given := all(join.uuid) + + g.TAssertEqual(given, expected) + }) + + g.Testing("we don't get events from other channels", func() { + }) + + g.Testing("as we change the reference point, the list changes", func() { + }) + + g.Testing("no error if closed more than once", func() { + g.TErrorIf(g.SomeError( + allAfterClose(), + allAfterClose(), + allAfterClose(), + )) + }) + // FIXME +} + +func test_initDB() { + // FIXME +} + +func test_queriesTclose() { + // FIXME } func test_splitOnCRLF() { @@ -607,11 +1929,29 @@ func test_parseMessage() { func dumpQueries() { queries := []struct{name string; fn func(string) queryT}{ { "createTables", createTablesSQL }, + { "createUser", createUserSQL }, + { "userByUUID", userByUUIDSQL }, + { "updateUser", updateUserSQL }, + { "deleteUser", deleteUserSQL }, { "addNetwork", addNetworkSQL }, + { "getNetwork", getNetworkSQL }, + { "networks", networksSQL }, + { "setNetwork", setNetworkSQL }, + { "nipNetwork", nipNetworkSQL }, + { "addMember", addMemberSQL }, + { "showMember", showMemberSQL }, + { "members", membersSQL }, + { "editMember", editMemberSQL }, + { "dropMember", dropMemberSQL }, { "addChannel", addChannelSQL }, { "channels", channelsSQL }, - { "allAfter", allAfterSQL }, + { "topic", topicSQL }, + { "endChannel", endChannelSQL }, + { "join", joinSQL }, + { "part", partSQL }, + { "names", namesSQL }, { "addEvent", addEventSQL }, + { "allAfter", allAfterSQL }, } for _, query := range queries { q := query.fn(defaultPrefix) @@ -631,12 +1971,41 @@ func MainTest() { g.Init() test_defaultPrefix() + test_serialized() + test_execSerialized() test_tryRollback() test_inTx() test_createTables() + test_createUserStmt() + test_userByUUIDStmt() + test_updateUserStmt() + test_deleteUserStmt() test_addNetworkStmt() + test_getNetworkStmt() + test_networkEach() + test_networksStmt() + test_setNetworkStmt() + test_nipNetworkStmt() + test_addMemberStmt() + test_showMemberStmt() + test_memberEach() + test_membersStmt() + test_editMemberStmt() + test_dropMemberStmt() test_addChannelStmt() + test_channelEach() + test_channelsStmt() + test_topicStmt() + test_endChannelStmt() + test_joinStmt() + test_partStmt() + test_nameEach() + test_namesStmt() + test_addEventStmt() + test_eventEach() + test_allAfterStmt() test_initDB() + test_queriesTclose() test_splitOnCRLF() test_splitOnRawMessage() test_parseMessageParams() diff --git a/tests/queries.sql b/tests/queries.sql index fe67f60..3aa6586 100644 --- a/tests/queries.sql +++ b/tests/queries.sql @@ -1,125 +1,404 @@ -- createTables.sql: -- write: - CREATE TABLE IF NOT EXISTS "papod_workspaces" ( + -- 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, - name TEXT NOT NULL, - description TEXT NOT NULL, - -- "public", "private", "unlisted" - type TEXT NOT NULL - ); - CREATE TABLE IF NOT EXISTS "papod_workspace_changes ( + username TEXT NOT NULL, + display_name TEXT NOT NULL, + picture_uuid BLOB UNIQUE, + deleted INT NOT NULL + ) 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')), - workspace_id INTEGER NOT NULL - REFERENCES "papod_workspaces"(id), - attribute TEXT NOT NULL, + 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 BOOLEAN NOT NULL - ); - CREATE TABLE IF NOT EXISTS "papod_users" ( + 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')), - -- provided by cracha uuid BLOB NOT NULL UNIQUE, - username TEXT NOT NULL, - display_name TEXT NOT NULL, - picture_uuid BLOB NOT NULL UNIQUE, - deleted BOOLEAN NOT NULL - ); - CREATE TABLE IF NOT EXISTS "papod_user_changes" ( + 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 (papod), - user_id INTEGER NOT NULL REFERENCES "strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')_users"(id), - attribute TEXT NOT NULL, + 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 BOOLEAN 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 (%!s(MISSING)), - workspace_id INTEGER NOT NULL - REFERENCES "%!s(MISSING)_workspaces"(id), + 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 NOT NULL UNIQUE, - -- "active", "inactive", "removed" - status TEXT NOT NULL, - -- "active", always - active_uniq TEXT, - UNIQUE (workspace_id, username, active_uniq), - UNIQUE (workspace_id, user_id) - ); - CREATE TABLE IF NOT EXISTS "%!s(MISSING)_member_roles" ( + 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 "%!s(MISSING)_members"(id), + REFERENCES "papod_members"(id), role TEXT NOT NULL, UNIQUE (member_id, role) - ); - CREATE TABLE IF NOT EXISTS "%!s(MISSING)_member_changes" ( + ) 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 (%!s(MISSING)), - member_id INTEGER NOT NULL - REFERENCES "%!s(MISSING)_members"(id), + 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 BOOLEAN NOT NULL - ); - CREATE TABLE IF NOT EXISTS "%!s(MISSING)_channels" ( + 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 (%!s(MISSING)), + timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), uuid BLOB NOT NULL UNIQUE, - workspace_id INTEGER NOT NULL - REFERENCES "%!s(MISSING)_workspaces"(id), + network_id INTEGER -- FIXME NOT NULL + REFERENCES "papod_networks"(id), public_name TEXT UNIQUE, label TEXT NOT NULL, - description TEXT, - virtual BOOLEAN NOT NULL - ); - CREATE TABLE IF NOT EXISTS "%!s(MISSING)_channel_changes" ( + 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 (%!s(MISSING)), + timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), channel_id INTEGER NOT NULL - REFERENCES "%!s(MISSING)_channels"(id), + REFERENCES "papod_channels"(id), attribute TEXT NOT NULL, value TEXT NOT NULL, - op BOOLEAN NOT NULL - ); - CREATE TABLE IF NOT EXISTS "%!s(MISSING)_participants" ( - member_id - ); - CREATE TABLE IF NOT EXISTS "%!s(MISSING)_channel_events" ( + 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 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 (%!s(MISSING)), + timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), uuid BLOB NOT NULL UNIQUE, - channel_id INTEGER NOT NULL REFERENCES "%!s(MISSING)"(id), - connection_id INTEGER NOT NULL, - -- payload FIXME: vary by type? - ); - -- FIXME: group conversations? - -- user: person - -- member: workspace user - -- participant: channel member + 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 "%!s(MISSING)_members" ( + network_id, user_id, username, display_name, + picture_uuid, status, active_uniq + ) VALUES ( + last_insert_rowid(), + ?, + ( + SELECT username, display_name, picture_uuid + FROM "%!s(MISSING)_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 %!(EXTRA string=papod) +-- setNetwork.sql: +-- write: + %!(EXTRA string=papod) + -- read: --- addChannel.sql: +-- nipNetwork.sql: +-- write: + %!(EXTRA string=papod) + +-- read: + +-- addMember.sql: +-- write: + -- FIXME + + +-- read: + +-- showMember.sql: -- write: + +-- read: + %!(EXTRA string=papod) + +-- members.sql: +-- write: + +-- read: -- FIXME %!(EXTRA string=papod) +-- editMember.sql: +-- write: + %!(EXTRA string=papod) + +-- read: + +-- dropMember.sql: +-- write: + + +-- read: + +-- addChannel.sql: +-- write: + INSERT INTO "papod_channels" ( + uuid, public_name, label, description, virtual + ) VALUES (?, ?, ?, ?, ?) RETURNING id, timestamp; + + -- read: -- channels.sql: @@ -129,16 +408,78 @@ -- FIXME %!(EXTRA string=papod) --- allAfter.sql: +-- topic.sql: -- write: + %!(EXTRA string=papod) -- read: + +-- endChannel.sql: +-- write: + %!(EXTRA string=papod) + +-- read: + +-- join.sql: +-- write: -- FIXME %!(EXTRA string=papod) --- addEvent.sql: +-- read: + +-- part.sql: +-- write: + -- FIXME + %!(EXTRA string=papod) + +-- read: + +-- names.sql: -- write: + +-- read: -- FIXME %!(EXTRA string=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 + ); + |