Add roomserver tests (2/?) (#2445)

* Add invite table tests; move variable declarations

* Add Membership table tests

* Move variable declarations

* Add PrevEvents table tests

* Add Published table test

* Add Redactions tests
Fix bug in SQLite markRedactionValidatedSQL

* PR comments, better readability for invite tests

17 files changed, 517 insertions, 66 deletions

diff --git a/roomserver/storage/postgres/invite_table.go b/roomserver/storage/postgres/invite_table.go
index 176c16e4..4cddfe2e 100644
--- a/roomserver/storage/postgres/invite_table.go
+++ b/roomserver/storage/postgres/invite_table.go
@@ -81,12 +81,12 @@ type inviteStatements struct {
 	updateInviteRetiredStmt             *sql.Stmt
 }
 
-func createInvitesTable(db *sql.DB) error {
+func CreateInvitesTable(db *sql.DB) error {
 	_, err := db.Exec(inviteSchema)
 	return err
 }
 
-func prepareInvitesTable(db *sql.DB) (tables.Invites, error) {
+func PrepareInvitesTable(db *sql.DB) (tables.Invites, error) {
 	s := &inviteStatements{}
 
 	return s, sqlutil.StatementList{
@@ -127,8 +127,8 @@ func (s *inviteStatements) UpdateInviteRetired(
 	defer internal.CloseAndLogIfError(ctx, rows, "updateInviteRetired: rows.close() failed")
 
 	var eventIDs []string
+	var inviteEventID string
 	for rows.Next() {
-		var inviteEventID string
 		if err = rows.Scan(&inviteEventID); err != nil {
 			return nil, err
 		}
@@ -152,9 +152,9 @@ func (s *inviteStatements) SelectInviteActiveForUserInRoom(
 	defer internal.CloseAndLogIfError(ctx, rows, "selectInviteActiveForUserInRoom: rows.close() failed")
 	var result []types.EventStateKeyNID
 	var eventIDs []string
+	var inviteEventID string
+	var senderUserNID int64
 	for rows.Next() {
-		var inviteEventID string
-		var senderUserNID int64
 		if err := rows.Scan(&inviteEventID, &senderUserNID); err != nil {
 			return nil, nil, err
 		}
diff --git a/roomserver/storage/postgres/membership_table.go b/roomserver/storage/postgres/membership_table.go
index 6ed5293e..c01753c3 100644
--- a/roomserver/storage/postgres/membership_table.go
+++ b/roomserver/storage/postgres/membership_table.go
@@ -160,12 +160,12 @@ type membershipStatements struct {
 	selectServerInRoomStmt                          *sql.Stmt
 }
 
-func createMembershipTable(db *sql.DB) error {
+func CreateMembershipTable(db *sql.DB) error {
 	_, err := db.Exec(membershipSchema)
 	return err
 }
 
-func prepareMembershipTable(db *sql.DB) (tables.Membership, error) {
+func PrepareMembershipTable(db *sql.DB) (tables.Membership, error) {
 	s := &membershipStatements{}
 
 	return s, sqlutil.StatementList{
@@ -234,8 +234,8 @@ func (s *membershipStatements) SelectMembershipsFromRoom(
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "selectMembershipsFromRoom: rows.close() failed")
 
+	var eNID types.EventNID
 	for rows.Next() {
-		var eNID types.EventNID
 		if err = rows.Scan(&eNID); err != nil {
 			return
 		}
@@ -262,8 +262,8 @@ func (s *membershipStatements) SelectMembershipsFromRoomAndMembership(
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "selectMembershipsFromRoomAndMembership: rows.close() failed")
 
+	var eNID types.EventNID
 	for rows.Next() {
-		var eNID types.EventNID
 		if err = rows.Scan(&eNID); err != nil {
 			return
 		}
@@ -298,8 +298,8 @@ func (s *membershipStatements) SelectRoomsWithMembership(
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "SelectRoomsWithMembership: rows.close() failed")
 	var roomNIDs []types.RoomNID
+	var roomNID types.RoomNID
 	for rows.Next() {
-		var roomNID types.RoomNID
 		if err := rows.Scan(&roomNID); err != nil {
 			return nil, err
 		}
@@ -320,9 +320,9 @@ func (s *membershipStatements) SelectJoinedUsersSetForRooms(
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "selectJoinedUsersSetForRooms: rows.close() failed")
 	result := make(map[types.EventStateKeyNID]int)
+	var userID types.EventStateKeyNID
+	var count int
 	for rows.Next() {
-		var userID types.EventStateKeyNID
-		var count int
 		if err := rows.Scan(&userID, &count); err != nil {
 			return nil, err
 		}
@@ -342,12 +342,12 @@ func (s *membershipStatements) SelectKnownUsers(
 	}
 	result := []string{}
 	defer internal.CloseAndLogIfError(ctx, rows, "SelectKnownUsers: rows.close() failed")
+	var resUserID string
 	for rows.Next() {
-		var userID string
-		if err := rows.Scan(&userID); err != nil {
+		if err := rows.Scan(&resUserID); err != nil {
 			return nil, err
 		}
-		result = append(result, userID)
+		result = append(result, resUserID)
 	}
 	return result, rows.Err()
 }
diff --git a/roomserver/storage/postgres/previous_events_table.go b/roomserver/storage/postgres/previous_events_table.go
index bd4e853e..26999a29 100644
--- a/roomserver/storage/postgres/previous_events_table.go
+++ b/roomserver/storage/postgres/previous_events_table.go
@@ -64,12 +64,12 @@ type previousEventStatements struct {
 	selectPreviousEventExistsStmt *sql.Stmt
 }
 
-func createPrevEventsTable(db *sql.DB) error {
+func CreatePrevEventsTable(db *sql.DB) error {
 	_, err := db.Exec(previousEventSchema)
 	return err
 }
 
-func preparePrevEventsTable(db *sql.DB) (tables.PreviousEvents, error) {
+func PreparePrevEventsTable(db *sql.DB) (tables.PreviousEvents, error) {
 	s := &previousEventStatements{}
 
 	return s, sqlutil.StatementList{
diff --git a/roomserver/storage/postgres/published_table.go b/roomserver/storage/postgres/published_table.go
index 15985fcd..56fa02f7 100644
--- a/roomserver/storage/postgres/published_table.go
+++ b/roomserver/storage/postgres/published_table.go
@@ -49,12 +49,12 @@ type publishedStatements struct {
 	selectPublishedStmt    *sql.Stmt
 }
 
-func createPublishedTable(db *sql.DB) error {
+func CreatePublishedTable(db *sql.DB) error {
 	_, err := db.Exec(publishedSchema)
 	return err
 }
 
-func preparePublishedTable(db *sql.DB) (tables.Published, error) {
+func PreparePublishedTable(db *sql.DB) (tables.Published, error) {
 	s := &publishedStatements{}
 
 	return s, sqlutil.StatementList{
@@ -94,8 +94,8 @@ func (s *publishedStatements) SelectAllPublishedRooms(
 	defer internal.CloseAndLogIfError(ctx, rows, "selectAllPublishedStmt: rows.close() failed")
 
 	var roomIDs []string
+	var roomID string
 	for rows.Next() {
-		var roomID string
 		if err = rows.Scan(&roomID); err != nil {
 			return nil, err
 		}
diff --git a/roomserver/storage/postgres/redactions_table.go b/roomserver/storage/postgres/redactions_table.go
index 5614f2bd..6e2f6712 100644
--- a/roomserver/storage/postgres/redactions_table.go
+++ b/roomserver/storage/postgres/redactions_table.go
@@ -59,12 +59,12 @@ type redactionStatements struct {
 	markRedactionValidatedStmt                  *sql.Stmt
 }
 
-func createRedactionsTable(db *sql.DB) error {
+func CreateRedactionsTable(db *sql.DB) error {
 	_, err := db.Exec(redactionsSchema)
 	return err
 }
 
-func prepareRedactionsTable(db *sql.DB) (tables.Redactions, error) {
+func PrepareRedactionsTable(db *sql.DB) (tables.Redactions, error) {
 	s := &redactionStatements{}
 
 	return s, sqlutil.StatementList{
diff --git a/roomserver/storage/postgres/storage.go b/roomserver/storage/postgres/storage.go
index 34e89149..88df7200 100644
--- a/roomserver/storage/postgres/storage.go
+++ b/roomserver/storage/postgres/storage.go
@@ -89,22 +89,22 @@ func (d *Database) create(db *sql.DB) error {
 	if err := createStateSnapshotTable(db); err != nil {
 		return err
 	}
-	if err := createPrevEventsTable(db); err != nil {
+	if err := CreatePrevEventsTable(db); err != nil {
 		return err
 	}
 	if err := createRoomAliasesTable(db); err != nil {
 		return err
 	}
-	if err := createInvitesTable(db); err != nil {
+	if err := CreateInvitesTable(db); err != nil {
 		return err
 	}
-	if err := createMembershipTable(db); err != nil {
+	if err := CreateMembershipTable(db); err != nil {
 		return err
 	}
-	if err := createPublishedTable(db); err != nil {
+	if err := CreatePublishedTable(db); err != nil {
 		return err
 	}
-	if err := createRedactionsTable(db); err != nil {
+	if err := CreateRedactionsTable(db); err != nil {
 		return err
 	}
 
@@ -140,7 +140,7 @@ func (d *Database) prepare(db *sql.DB, writer sqlutil.Writer, cache caching.Room
 	if err != nil {
 		return err
 	}
-	prevEvents, err := preparePrevEventsTable(db)
+	prevEvents, err := PreparePrevEventsTable(db)
 	if err != nil {
 		return err
 	}
@@ -148,19 +148,19 @@ func (d *Database) prepare(db *sql.DB, writer sqlutil.Writer, cache caching.Room
 	if err != nil {
 		return err
 	}
-	invites, err := prepareInvitesTable(db)
+	invites, err := PrepareInvitesTable(db)
 	if err != nil {
 		return err
 	}
-	membership, err := prepareMembershipTable(db)
+	membership, err := PrepareMembershipTable(db)
 	if err != nil {
 		return err
 	}
-	published, err := preparePublishedTable(db)
+	published, err := PreparePublishedTable(db)
 	if err != nil {
 		return err
 	}
-	redactions, err := prepareRedactionsTable(db)
+	redactions, err := PrepareRedactionsTable(db)
 	if err != nil {
 		return err
 	}
diff --git a/roomserver/storage/sqlite3/invite_table.go b/roomserver/storage/sqlite3/invite_table.go
index d54d313a..e051d63a 100644
--- a/roomserver/storage/sqlite3/invite_table.go
+++ b/roomserver/storage/sqlite3/invite_table.go
@@ -69,12 +69,12 @@ type inviteStatements struct {
 	selectInvitesAboutToRetireStmt      *sql.Stmt
 }
 
-func createInvitesTable(db *sql.DB) error {
+func CreateInvitesTable(db *sql.DB) error {
 	_, err := db.Exec(inviteSchema)
 	return err
 }
 
-func prepareInvitesTable(db *sql.DB) (tables.Invites, error) {
+func PrepareInvitesTable(db *sql.DB) (tables.Invites, error) {
 	s := &inviteStatements{
 		db: db,
 	}
@@ -119,8 +119,8 @@ func (s *inviteStatements) UpdateInviteRetired(
 		return
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "UpdateInviteRetired: rows.close() failed")
+	var inviteEventID string
 	for rows.Next() {
-		var inviteEventID string
 		if err = rows.Scan(&inviteEventID); err != nil {
 			return
 		}
@@ -147,9 +147,9 @@ func (s *inviteStatements) SelectInviteActiveForUserInRoom(
 	defer internal.CloseAndLogIfError(ctx, rows, "selectInviteActiveForUserInRoom: rows.close() failed")
 	var result []types.EventStateKeyNID
 	var eventIDs []string
+	var eventID string
+	var senderUserNID int64
 	for rows.Next() {
-		var eventID string
-		var senderUserNID int64
 		if err := rows.Scan(&eventID, &senderUserNID); err != nil {
 			return nil, nil, err
 		}
diff --git a/roomserver/storage/sqlite3/membership_table.go b/roomserver/storage/sqlite3/membership_table.go
index 7ed86b61..6f0fe8b6 100644
--- a/roomserver/storage/sqlite3/membership_table.go
+++ b/roomserver/storage/sqlite3/membership_table.go
@@ -136,12 +136,12 @@ type membershipStatements struct {
 	selectServerInRoomStmt                          *sql.Stmt
 }
 
-func createMembershipTable(db *sql.DB) error {
+func CreateMembershipTable(db *sql.DB) error {
 	_, err := db.Exec(membershipSchema)
 	return err
 }
 
-func prepareMembershipTable(db *sql.DB) (tables.Membership, error) {
+func PrepareMembershipTable(db *sql.DB) (tables.Membership, error) {
 	s := &membershipStatements{
 		db: db,
 	}
@@ -212,8 +212,8 @@ func (s *membershipStatements) SelectMembershipsFromRoom(
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "selectMembershipsFromRoom: rows.close() failed")
 
+	var eNID types.EventNID
 	for rows.Next() {
-		var eNID types.EventNID
 		if err = rows.Scan(&eNID); err != nil {
 			return
 		}
@@ -239,8 +239,8 @@ func (s *membershipStatements) SelectMembershipsFromRoomAndMembership(
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "selectMembershipsFromRoomAndMembership: rows.close() failed")
 
+	var eNID types.EventNID
 	for rows.Next() {
-		var eNID types.EventNID
 		if err = rows.Scan(&eNID); err != nil {
 			return
 		}
@@ -275,8 +275,8 @@ func (s *membershipStatements) SelectRoomsWithMembership(
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "SelectRoomsWithMembership: rows.close() failed")
 	var roomNIDs []types.RoomNID
+	var roomNID types.RoomNID
 	for rows.Next() {
-		var roomNID types.RoomNID
 		if err := rows.Scan(&roomNID); err != nil {
 			return nil, err
 		}
@@ -307,9 +307,9 @@ func (s *membershipStatements) SelectJoinedUsersSetForRooms(ctx context.Context,
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "selectJoinedUsersSetForRooms: rows.close() failed")
 	result := make(map[types.EventStateKeyNID]int)
+	var userID types.EventStateKeyNID
+	var count int
 	for rows.Next() {
-		var userID types.EventStateKeyNID
-		var count int
 		if err := rows.Scan(&userID, &count); err != nil {
 			return nil, err
 		}
@@ -326,12 +326,12 @@ func (s *membershipStatements) SelectKnownUsers(ctx context.Context, txn *sql.Tx
 	}
 	result := []string{}
 	defer internal.CloseAndLogIfError(ctx, rows, "SelectKnownUsers: rows.close() failed")
+	var resUserID string
 	for rows.Next() {
-		var userID string
-		if err := rows.Scan(&userID); err != nil {
+		if err := rows.Scan(&resUserID); err != nil {
 			return nil, err
 		}
-		result = append(result, userID)
+		result = append(result, resUserID)
 	}
 	return result, rows.Err()
 }
diff --git a/roomserver/storage/sqlite3/previous_events_table.go b/roomserver/storage/sqlite3/previous_events_table.go
index 7304bf0d..2a146ef6 100644
--- a/roomserver/storage/sqlite3/previous_events_table.go
+++ b/roomserver/storage/sqlite3/previous_events_table.go
@@ -70,12 +70,12 @@ type previousEventStatements struct {
 	selectPreviousEventExistsStmt *sql.Stmt
 }
 
-func createPrevEventsTable(db *sql.DB) error {
+func CreatePrevEventsTable(db *sql.DB) error {
 	_, err := db.Exec(previousEventSchema)
 	return err
 }
 
-func preparePrevEventsTable(db *sql.DB) (tables.PreviousEvents, error) {
+func PreparePrevEventsTable(db *sql.DB) (tables.PreviousEvents, error) {
 	s := &previousEventStatements{
 		db: db,
 	}
diff --git a/roomserver/storage/sqlite3/published_table.go b/roomserver/storage/sqlite3/published_table.go
index 9e416ace..50dfa549 100644
--- a/roomserver/storage/sqlite3/published_table.go
+++ b/roomserver/storage/sqlite3/published_table.go
@@ -49,12 +49,12 @@ type publishedStatements struct {
 	selectPublishedStmt    *sql.Stmt
 }
 
-func createPublishedTable(db *sql.DB) error {
+func CreatePublishedTable(db *sql.DB) error {
 	_, err := db.Exec(publishedSchema)
 	return err
 }
 
-func preparePublishedTable(db *sql.DB) (tables.Published, error) {
+func PreparePublishedTable(db *sql.DB) (tables.Published, error) {
 	s := &publishedStatements{
 		db: db,
 	}
@@ -96,8 +96,8 @@ func (s *publishedStatements) SelectAllPublishedRooms(
 	defer internal.CloseAndLogIfError(ctx, rows, "selectAllPublishedStmt: rows.close() failed")
 
 	var roomIDs []string
+	var roomID string
 	for rows.Next() {
-		var roomID string
 		if err = rows.Scan(&roomID); err != nil {
 			return nil, err
 		}
diff --git a/roomserver/storage/sqlite3/redactions_table.go b/roomserver/storage/sqlite3/redactions_table.go
index aed190b1..db6f57a1 100644
--- a/roomserver/storage/sqlite3/redactions_table.go
+++ b/roomserver/storage/sqlite3/redactions_table.go
@@ -48,7 +48,7 @@ const selectRedactionInfoByEventBeingRedactedSQL = "" +
 	" WHERE redacts_event_id = $1"
 
 const markRedactionValidatedSQL = "" +
-	" UPDATE roomserver_redactions SET validated = $2 WHERE redaction_event_id = $1"
+	" UPDATE roomserver_redactions SET validated = $1 WHERE redaction_event_id = $2"
 
 type redactionStatements struct {
 	db                                          *sql.DB
@@ -58,12 +58,12 @@ type redactionStatements struct {
 	markRedactionValidatedStmt                  *sql.Stmt
 }
 
-func createRedactionsTable(db *sql.DB) error {
+func CreateRedactionsTable(db *sql.DB) error {
 	_, err := db.Exec(redactionsSchema)
 	return err
 }
 
-func prepareRedactionsTable(db *sql.DB) (tables.Redactions, error) {
+func PrepareRedactionsTable(db *sql.DB) (tables.Redactions, error) {
 	s := &redactionStatements{
 		db: db,
 	}
@@ -118,6 +118,6 @@ func (s *redactionStatements) MarkRedactionValidated(
 	ctx context.Context, txn *sql.Tx, redactionEventID string, validated bool,
 ) error {
 	stmt := sqlutil.TxStmt(txn, s.markRedactionValidatedStmt)
-	_, err := stmt.ExecContext(ctx, redactionEventID, validated)
+	_, err := stmt.ExecContext(ctx, validated, redactionEventID)
 	return err
 }
diff --git a/roomserver/storage/sqlite3/storage.go b/roomserver/storage/sqlite3/storage.go
index 9522d305..a4e32d52 100644
--- a/roomserver/storage/sqlite3/storage.go
+++ b/roomserver/storage/sqlite3/storage.go
@@ -98,22 +98,22 @@ func (d *Database) create(db *sql.DB) error {
 	if err := createStateSnapshotTable(db); err != nil {
 		return err
 	}
-	if err := createPrevEventsTable(db); err != nil {
+	if err := CreatePrevEventsTable(db); err != nil {
 		return err
 	}
 	if err := createRoomAliasesTable(db); err != nil {
 		return err
 	}
-	if err := createInvitesTable(db); err != nil {
+	if err := CreateInvitesTable(db); err != nil {
 		return err
 	}
-	if err := createMembershipTable(db); err != nil {
+	if err := CreateMembershipTable(db); err != nil {
 		return err
 	}
-	if err := createPublishedTable(db); err != nil {
+	if err := CreatePublishedTable(db); err != nil {
 		return err
 	}
-	if err := createRedactionsTable(db); err != nil {
+	if err := CreateRedactionsTable(db); err != nil {
 		return err
 	}
 
@@ -149,7 +149,7 @@ func (d *Database) prepare(db *sql.DB, writer sqlutil.Writer, cache caching.Room
 	if err != nil {
 		return err
 	}
-	prevEvents, err := preparePrevEventsTable(db)
+	prevEvents, err := PreparePrevEventsTable(db)
 	if err != nil {
 		return err
 	}
@@ -157,19 +157,19 @@ func (d *Database) prepare(db *sql.DB, writer sqlutil.Writer, cache caching.Room
 	if err != nil {
 		return err
 	}
-	invites, err := prepareInvitesTable(db)
+	invites, err := PrepareInvitesTable(db)
 	if err != nil {
 		return err
 	}
-	membership, err := prepareMembershipTable(db)
+	membership, err := PrepareMembershipTable(db)
 	if err != nil {
 		return err
 	}
-	published, err := preparePublishedTable(db)
+	published, err := PreparePublishedTable(db)
 	if err != nil {
 		return err
 	}
-	redactions, err := prepareRedactionsTable(db)
+	redactions, err := PrepareRedactionsTable(db)
 	if err != nil {
 		return err
 	}
diff --git a/roomserver/storage/tables/invite_table_test.go b/roomserver/storage/tables/invite_table_test.go
new file mode 100644
index 00000000..8df3faa2
--- /dev/null
+++ b/roomserver/storage/tables/invite_table_test.go
@@ -0,0 +1,92 @@
+package tables_test
+
+import (
+	"context"
+	"testing"
+
+	"github.com/matrix-org/dendrite/internal/sqlutil"
+	"github.com/matrix-org/dendrite/roomserver/storage/postgres"
+	"github.com/matrix-org/dendrite/roomserver/storage/sqlite3"
+	"github.com/matrix-org/dendrite/roomserver/storage/tables"
+	"github.com/matrix-org/dendrite/roomserver/types"
+	"github.com/matrix-org/dendrite/setup/config"
+	"github.com/matrix-org/dendrite/test"
+	"github.com/matrix-org/util"
+	"github.com/stretchr/testify/assert"
+)
+
+func mustCreateInviteTable(t *testing.T, dbType test.DBType) (tables.Invites, func()) {
+	t.Helper()
+	connStr, close := test.PrepareDBConnectionString(t, dbType)
+	db, err := sqlutil.Open(&config.DatabaseOptions{
+		ConnectionString: config.DataSource(connStr),
+	}, sqlutil.NewExclusiveWriter())
+	assert.NoError(t, err)
+	var tab tables.Invites
+	switch dbType {
+	case test.DBTypePostgres:
+		err = postgres.CreateInvitesTable(db)
+		assert.NoError(t, err)
+		tab, err = postgres.PrepareInvitesTable(db)
+	case test.DBTypeSQLite:
+		err = sqlite3.CreateInvitesTable(db)
+		assert.NoError(t, err)
+		tab, err = sqlite3.PrepareInvitesTable(db)
+	}
+	assert.NoError(t, err)
+
+	return tab, close
+}
+
+func TestInviteTable(t *testing.T) {
+	ctx := context.Background()
+	test.WithAllDatabases(t, func(t *testing.T, dbType test.DBType) {
+		tab, close := mustCreateInviteTable(t, dbType)
+		defer close()
+		eventID1 := util.RandomString(16)
+		roomNID := types.RoomNID(1)
+		targetUserNID, senderUserNID := types.EventStateKeyNID(1), types.EventStateKeyNID(2)
+		newInvite, err := tab.InsertInviteEvent(ctx, nil, eventID1, roomNID, targetUserNID, senderUserNID, []byte(""))
+		assert.NoError(t, err)
+		assert.True(t, newInvite)
+
+		// Try adding the same invite again
+		newInvite, err = tab.InsertInviteEvent(ctx, nil, eventID1, roomNID, targetUserNID, senderUserNID, []byte(""))
+		assert.NoError(t, err)
+		assert.False(t, newInvite)
+
+		// Add another invite for this room
+		eventID2 := util.RandomString(16)
+		newInvite, err = tab.InsertInviteEvent(ctx, nil, eventID2, roomNID, targetUserNID, senderUserNID, []byte(""))
+		assert.NoError(t, err)
+		assert.True(t, newInvite)
+
+		// Add another invite for a different user
+		eventID := util.RandomString(16)
+		newInvite, err = tab.InsertInviteEvent(ctx, nil, eventID, types.RoomNID(3), targetUserNID, senderUserNID, []byte(""))
+		assert.NoError(t, err)
+		assert.True(t, newInvite)
+
+		stateKeyNIDs, eventIDs, err := tab.SelectInviteActiveForUserInRoom(ctx, nil, targetUserNID, roomNID)
+		assert.NoError(t, err)
+		assert.Equal(t, []string{eventID1, eventID2}, eventIDs)
+		assert.Equal(t, []types.EventStateKeyNID{2, 2}, stateKeyNIDs)
+
+		// retire the invite
+		retiredEventIDs, err := tab.UpdateInviteRetired(ctx, nil, roomNID, targetUserNID)
+		assert.NoError(t, err)
+		assert.Equal(t, []string{eventID1, eventID2}, retiredEventIDs)
+
+		// This should now be empty
+		stateKeyNIDs, eventIDs, err = tab.SelectInviteActiveForUserInRoom(ctx, nil, targetUserNID, roomNID)
+		assert.NoError(t, err)
+		assert.Empty(t, eventIDs)
+		assert.Empty(t, stateKeyNIDs)
+
+		// Non-existent targetUserNID
+		stateKeyNIDs, eventIDs, err = tab.SelectInviteActiveForUserInRoom(ctx, nil, types.EventStateKeyNID(10), roomNID)
+		assert.NoError(t, err)
+		assert.Empty(t, stateKeyNIDs)
+		assert.Empty(t, eventIDs)
+	})
+}
diff --git a/roomserver/storage/tables/membership_table_test.go b/roomserver/storage/tables/membership_table_test.go
new file mode 100644
index 00000000..14e8ce50
--- /dev/null
+++ b/roomserver/storage/tables/membership_table_test.go
@@ -0,0 +1,130 @@
+package tables_test
+
+import (
+	"context"
+	"fmt"
+	"testing"
+
+	"github.com/matrix-org/dendrite/internal/sqlutil"
+	"github.com/matrix-org/dendrite/roomserver/storage/postgres"
+	"github.com/matrix-org/dendrite/roomserver/storage/sqlite3"
+	"github.com/matrix-org/dendrite/roomserver/storage/tables"
+	"github.com/matrix-org/dendrite/roomserver/types"
+	"github.com/matrix-org/dendrite/setup/config"
+	"github.com/matrix-org/dendrite/test"
+	"github.com/stretchr/testify/assert"
+)
+
+func mustCreateMembershipTable(t *testing.T, dbType test.DBType) (tab tables.Membership, stateKeyTab tables.EventStateKeys, close func()) {
+	t.Helper()
+	connStr, close := test.PrepareDBConnectionString(t, dbType)
+	db, err := sqlutil.Open(&config.DatabaseOptions{
+		ConnectionString: config.DataSource(connStr),
+	}, sqlutil.NewExclusiveWriter())
+	assert.NoError(t, err)
+	switch dbType {
+	case test.DBTypePostgres:
+		err = postgres.CreateEventStateKeysTable(db)
+		assert.NoError(t, err)
+		err = postgres.CreateMembershipTable(db)
+		assert.NoError(t, err)
+		tab, err = postgres.PrepareMembershipTable(db)
+		assert.NoError(t, err)
+		stateKeyTab, err = postgres.PrepareEventStateKeysTable(db)
+	case test.DBTypeSQLite:
+		err = sqlite3.CreateEventStateKeysTable(db)
+		assert.NoError(t, err)
+		err = sqlite3.CreateMembershipTable(db)
+		assert.NoError(t, err)
+		tab, err = sqlite3.PrepareMembershipTable(db)
+		assert.NoError(t, err)
+		stateKeyTab, err = sqlite3.PrepareEventStateKeysTable(db)
+	}
+	assert.NoError(t, err)
+
+	return tab, stateKeyTab, close
+}
+
+func TestMembershipTable(t *testing.T) {
+	ctx := context.Background()
+	test.WithAllDatabases(t, func(t *testing.T, dbType test.DBType) {
+		tab, stateKeyTab, close := mustCreateMembershipTable(t, dbType)
+		defer close()
+		_ = close
+
+		userNIDs := make([]types.EventStateKeyNID, 0, 10)
+		for i := 0; i < 10; i++ {
+			stateKeyNID, err := stateKeyTab.InsertEventStateKeyNID(ctx, nil, fmt.Sprintf("@dummy%d:localhost", i))
+			assert.NoError(t, err)
+			userNIDs = append(userNIDs, stateKeyNID)
+			// This inserts a left user to the room
+			err = tab.InsertMembership(ctx, nil, 1, stateKeyNID, true)
+			assert.NoError(t, err)
+		}
+
+		// ... so this should be false
+		inRoom, err := tab.SelectLocalServerInRoom(ctx, nil, 1)
+		assert.NoError(t, err)
+		assert.False(t, inRoom)
+
+		changed, err := tab.UpdateMembership(ctx, nil, 1, userNIDs[0], userNIDs[0], tables.MembershipStateJoin, 1, false)
+		assert.NoError(t, err)
+		assert.True(t, changed)
+
+		// ... should now be true
+		inRoom, err = tab.SelectLocalServerInRoom(ctx, nil, 1)
+		assert.NoError(t, err)
+		assert.True(t, inRoom)
+
+		userJoinedToRooms, err := tab.SelectJoinedUsersSetForRooms(ctx, nil, []types.RoomNID{1}, userNIDs)
+		assert.NoError(t, err)
+		assert.Equal(t, 1, len(userJoinedToRooms))
+
+		// Get all left/banned users
+		eventNIDs, err := tab.SelectMembershipsFromRoomAndMembership(ctx, nil, 1, tables.MembershipStateLeaveOrBan, true)
+		assert.NoError(t, err)
+		assert.Equal(t, 9, len(eventNIDs))
+
+		_, membershipState, forgotten, err := tab.SelectMembershipFromRoomAndTarget(ctx, nil, 1, userNIDs[5])
+		assert.NoError(t, err)
+		assert.False(t, forgotten)
+		assert.Equal(t, tables.MembershipStateLeaveOrBan, membershipState)
+
+		// Get all members, regardless of state
+		members, err := tab.SelectMembershipsFromRoom(ctx, nil, 1, true)
+		assert.NoError(t, err)
+		assert.Equal(t, 10, len(members))
+
+		// Get correct user
+		roomNIDs, err := tab.SelectRoomsWithMembership(ctx, nil, userNIDs[1], tables.MembershipStateLeaveOrBan)
+		assert.NoError(t, err)
+		assert.Equal(t, []types.RoomNID{1}, roomNIDs)
+
+		// User is not joined to room
+		roomNIDs, err = tab.SelectRoomsWithMembership(ctx, nil, userNIDs[5], tables.MembershipStateJoin)
+		assert.NoError(t, err)
+		assert.Equal(t, 0, len(roomNIDs))
+
+		// Forget room
+		err = tab.UpdateForgetMembership(ctx, nil, 1, userNIDs[0], true)
+		assert.NoError(t, err)
+
+		// should now return true
+		_, _, forgotten, err = tab.SelectMembershipFromRoomAndTarget(ctx, nil, 1, userNIDs[0])
+		assert.NoError(t, err)
+		assert.True(t, forgotten)
+
+		serverInRoom, err := tab.SelectServerInRoom(ctx, nil, 1, "localhost")
+		assert.NoError(t, err)
+		assert.True(t, serverInRoom)
+
+		serverInRoom, err = tab.SelectServerInRoom(ctx, nil, 1, "notJoined")
+		assert.NoError(t, err)
+		assert.False(t, serverInRoom)
+
+		// get all users we know about; should be only one user, since no other user joined the room
+		knownUsers, err := tab.SelectKnownUsers(ctx, nil, userNIDs[0], "localhost", 2)
+		assert.NoError(t, err)
+		assert.Equal(t, 1, len(knownUsers))
+	})
+}
diff --git a/roomserver/storage/tables/previous_events_table_test.go b/roomserver/storage/tables/previous_events_table_test.go
new file mode 100644
index 00000000..96d7bfed
--- /dev/null
+++ b/roomserver/storage/tables/previous_events_table_test.go
@@ -0,0 +1,61 @@
+package tables_test
+
+import (
+	"context"
+	"testing"
+
+	"github.com/matrix-org/dendrite/internal/sqlutil"
+	"github.com/matrix-org/dendrite/roomserver/storage/postgres"
+	"github.com/matrix-org/dendrite/roomserver/storage/sqlite3"
+	"github.com/matrix-org/dendrite/roomserver/storage/tables"
+	"github.com/matrix-org/dendrite/setup/config"
+	"github.com/matrix-org/dendrite/test"
+	"github.com/matrix-org/util"
+	"github.com/stretchr/testify/assert"
+)
+
+func mustCreatePreviousEventsTable(t *testing.T, dbType test.DBType) (tab tables.PreviousEvents, close func()) {
+	t.Helper()
+	connStr, close := test.PrepareDBConnectionString(t, dbType)
+	db, err := sqlutil.Open(&config.DatabaseOptions{
+		ConnectionString: config.DataSource(connStr),
+	}, sqlutil.NewExclusiveWriter())
+	assert.NoError(t, err)
+	switch dbType {
+	case test.DBTypePostgres:
+		err = postgres.CreatePrevEventsTable(db)
+		assert.NoError(t, err)
+		tab, err = postgres.PreparePrevEventsTable(db)
+	case test.DBTypeSQLite:
+		err = sqlite3.CreatePrevEventsTable(db)
+		assert.NoError(t, err)
+		tab, err = sqlite3.PreparePrevEventsTable(db)
+	}
+	assert.NoError(t, err)
+
+	return tab, close
+}
+
+func TestPreviousEventsTable(t *testing.T) {
+	ctx := context.Background()
+	alice := test.NewUser()
+	room := test.NewRoom(t, alice)
+	test.WithAllDatabases(t, func(t *testing.T, dbType test.DBType) {
+		tab, close := mustCreatePreviousEventsTable(t, dbType)
+		defer close()
+
+		for _, x := range room.Events() {
+			for _, prevEvent := range x.PrevEvents() {
+				err := tab.InsertPreviousEvent(ctx, nil, prevEvent.EventID, prevEvent.EventSHA256, 1)
+				assert.NoError(t, err)
+
+				err = tab.SelectPreviousEventExists(ctx, nil, prevEvent.EventID, prevEvent.EventSHA256)
+				assert.NoError(t, err)
+			}
+		}
+
+		// RandomString with a correct EventSHA256 should fail and return sql.ErrNoRows
+		err := tab.SelectPreviousEventExists(ctx, nil, util.RandomString(16), room.Events()[0].EventReference().EventSHA256)
+		assert.Error(t, err)
+	})
+}
diff --git a/roomserver/storage/tables/published_table_test.go b/roomserver/storage/tables/published_table_test.go
new file mode 100644
index 00000000..87662ed4
--- /dev/null
+++ b/roomserver/storage/tables/published_table_test.go
@@ -0,0 +1,79 @@
+package tables_test
+
+import (
+	"context"
+	"sort"
+	"testing"
+
+	"github.com/matrix-org/dendrite/internal/sqlutil"
+	"github.com/matrix-org/dendrite/roomserver/storage/postgres"
+	"github.com/matrix-org/dendrite/roomserver/storage/sqlite3"
+	"github.com/matrix-org/dendrite/roomserver/storage/tables"
+	"github.com/matrix-org/dendrite/setup/config"
+	"github.com/matrix-org/dendrite/test"
+	"github.com/stretchr/testify/assert"
+)
+
+func mustCreatePublishedTable(t *testing.T, dbType test.DBType) (tab tables.Published, close func()) {
+	t.Helper()
+	connStr, close := test.PrepareDBConnectionString(t, dbType)
+	db, err := sqlutil.Open(&config.DatabaseOptions{
+		ConnectionString: config.DataSource(connStr),
+	}, sqlutil.NewExclusiveWriter())
+	assert.NoError(t, err)
+	switch dbType {
+	case test.DBTypePostgres:
+		err = postgres.CreatePublishedTable(db)
+		assert.NoError(t, err)
+		tab, err = postgres.PreparePublishedTable(db)
+	case test.DBTypeSQLite:
+		err = sqlite3.CreatePublishedTable(db)
+		assert.NoError(t, err)
+		tab, err = sqlite3.PreparePublishedTable(db)
+	}
+	assert.NoError(t, err)
+
+	return tab, close
+}
+
+func TestPublishedTable(t *testing.T) {
+	ctx := context.Background()
+	alice := test.NewUser()
+
+	test.WithAllDatabases(t, func(t *testing.T, dbType test.DBType) {
+		tab, close := mustCreatePublishedTable(t, dbType)
+		defer close()
+
+		// Publish some rooms
+		publishedRooms := []string{}
+		for i := 0; i < 10; i++ {
+			room := test.NewRoom(t, alice)
+			published := i%2 == 0
+			err := tab.UpsertRoomPublished(ctx, nil, room.ID, published)
+			assert.NoError(t, err)
+			if published {
+				publishedRooms = append(publishedRooms, room.ID)
+			}
+			publishedRes, err := tab.SelectPublishedFromRoomID(ctx, nil, room.ID)
+			assert.NoError(t, err)
+			assert.Equal(t, published, publishedRes)
+		}
+		sort.Strings(publishedRooms)
+
+		// check that we get the expected published rooms
+		roomIDs, err := tab.SelectAllPublishedRooms(ctx, nil, true)
+		assert.NoError(t, err)
+		assert.Equal(t, publishedRooms, roomIDs)
+
+		// test an actual upsert
+		room := test.NewRoom(t, alice)
+		err = tab.UpsertRoomPublished(ctx, nil, room.ID, true)
+		assert.NoError(t, err)
+		err = tab.UpsertRoomPublished(ctx, nil, room.ID, false)
+		assert.NoError(t, err)
+		// should now be false, due to the upsert
+		publishedRes, err := tab.SelectPublishedFromRoomID(ctx, nil, room.ID)
+		assert.NoError(t, err)
+		assert.False(t, publishedRes)
+	})
+}
diff --git a/roomserver/storage/tables/redactions_table_test.go b/roomserver/storage/tables/redactions_table_test.go
new file mode 100644
index 00000000..ea48dc22
--- /dev/null
+++ b/roomserver/storage/tables/redactions_table_test.go
@@ -0,0 +1,89 @@
+package tables_test
+
+import (
+	"context"
+	"testing"
+
+	"github.com/matrix-org/dendrite/internal/sqlutil"
+	"github.com/matrix-org/dendrite/roomserver/storage/postgres"
+	"github.com/matrix-org/dendrite/roomserver/storage/sqlite3"
+	"github.com/matrix-org/dendrite/roomserver/storage/tables"
+	"github.com/matrix-org/dendrite/setup/config"
+	"github.com/matrix-org/dendrite/test"
+	"github.com/matrix-org/util"
+	"github.com/stretchr/testify/assert"
+)
+
+func mustCreateRedactionsTable(t *testing.T, dbType test.DBType) (tab tables.Redactions, close func()) {
+	t.Helper()
+	connStr, close := test.PrepareDBConnectionString(t, dbType)
+	db, err := sqlutil.Open(&config.DatabaseOptions{
+		ConnectionString: config.DataSource(connStr),
+	}, sqlutil.NewExclusiveWriter())
+	assert.NoError(t, err)
+	switch dbType {
+	case test.DBTypePostgres:
+		err = postgres.CreateRedactionsTable(db)
+		assert.NoError(t, err)
+		tab, err = postgres.PrepareRedactionsTable(db)
+	case test.DBTypeSQLite:
+		err = sqlite3.CreateRedactionsTable(db)
+		assert.NoError(t, err)
+		tab, err = sqlite3.PrepareRedactionsTable(db)
+	}
+	assert.NoError(t, err)
+
+	return tab, close
+}
+
+func TestRedactionsTable(t *testing.T) {
+	ctx := context.Background()
+
+	test.WithAllDatabases(t, func(t *testing.T, dbType test.DBType) {
+		tab, close := mustCreateRedactionsTable(t, dbType)
+		defer close()
+
+		// insert and verify some redactions
+		for i := 0; i < 10; i++ {
+			redactionEventID, redactsEventID := util.RandomString(16), util.RandomString(16)
+			wantRedactionInfo := tables.RedactionInfo{
+				Validated:        false,
+				RedactsEventID:   redactsEventID,
+				RedactionEventID: redactionEventID,
+			}
+			err := tab.InsertRedaction(ctx, nil, wantRedactionInfo)
+			assert.NoError(t, err)
+
+			// verify the redactions are inserted as expected
+			redactionInfo, err := tab.SelectRedactionInfoByRedactionEventID(ctx, nil, redactionEventID)
+			assert.NoError(t, err)
+			assert.Equal(t, &wantRedactionInfo, redactionInfo)
+
+			redactionInfo, err = tab.SelectRedactionInfoByEventBeingRedacted(ctx, nil, redactsEventID)
+			assert.NoError(t, err)
+			assert.Equal(t, &wantRedactionInfo, redactionInfo)
+
+			// redact event
+			err = tab.MarkRedactionValidated(ctx, nil, redactionEventID, true)
+			assert.NoError(t, err)
+
+			wantRedactionInfo.Validated = true
+			redactionInfo, err = tab.SelectRedactionInfoByRedactionEventID(ctx, nil, redactionEventID)
+			assert.NoError(t, err)
+			assert.Equal(t, &wantRedactionInfo, redactionInfo)
+		}
+
+		// Should not fail, it just updates 0 rows
+		err := tab.MarkRedactionValidated(ctx, nil, "iDontExist", true)
+		assert.NoError(t, err)
+
+		// Should also not fail, but return a nil redactionInfo
+		redactionInfo, err := tab.SelectRedactionInfoByRedactionEventID(ctx, nil, "iDontExist")
+		assert.NoError(t, err)
+		assert.Nil(t, redactionInfo)
+
+		redactionInfo, err = tab.SelectRedactionInfoByEventBeingRedacted(ctx, nil, "iDontExist")
+		assert.NoError(t, err)
+		assert.Nil(t, redactionInfo)
+	})
+}