Add initial implementation of some of `queriesT` functions

2 files changed, 1817 insertions, 107 deletions

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
+			);
+