Update database migrations, remove goose (#2264)

* Add new db migration

* Update migrations
Remove goose

* Add possibility to test direct upgrades

* Try to fix WASM test

* Add checks for specific migrations

* Remove AddMigration
Use WithTransaction
Add Dendrite version to table

* Fix linter issues

* Update tests

* Update comments, outdent if

* Namespace migrations

* Add direct upgrade tests, skipping over one version

* Split migrations

* Update go version in CI

* Fix copy&paste mistake

* Use contexts in migrations

Co-authored-by: kegsay <kegan@matrix.org>
Co-authored-by: Neil Alexander <neilalexander@users.noreply.github.com>

3 files changed, 6 insertions, 265 deletions

diff --git a/cmd/dendrite-upgrade-tests/main.go b/cmd/dendrite-upgrade-tests/main.go
index cabd07e7..39843dcc 100644
--- a/cmd/dendrite-upgrade-tests/main.go
+++ b/cmd/dendrite-upgrade-tests/main.go
@@ -37,6 +37,7 @@ var (
 	flagBuildConcurrency = flag.Int("build-concurrency", runtime.NumCPU(), "The amount of build concurrency when building images")
 	flagHead             = flag.String("head", "", "Location to a dendrite repository to treat as HEAD instead of Github")
 	flagDockerHost       = flag.String("docker-host", "localhost", "The hostname of the docker client. 'localhost' if running locally, 'host.docker.internal' if running in Docker.")
+	flagDirect           = flag.Bool("direct", false, "If a direct upgrade from the defined FROM version to TO should be done")
 	alphaNumerics        = regexp.MustCompile("[^a-zA-Z0-9]+")
 )
 
@@ -229,7 +230,7 @@ func getAndSortVersionsFromGithub(httpClient *http.Client) (semVers []*semver.Ve
 	return semVers, nil
 }
 
-func calculateVersions(cli *http.Client, from, to string) []string {
+func calculateVersions(cli *http.Client, from, to string, direct bool) []string {
 	semvers, err := getAndSortVersionsFromGithub(cli)
 	if err != nil {
 		log.Fatalf("failed to collect semvers from github: %s", err)
@@ -284,6 +285,9 @@ func calculateVersions(cli *http.Client, from, to string) []string {
 	if to == HEAD {
 		versions = append(versions, HEAD)
 	}
+	if direct {
+		versions = []string{versions[0], versions[len(versions)-1]}
+	}
 	return versions
 }
 
@@ -461,7 +465,7 @@ func main() {
 		os.Exit(1)
 	}
 	cleanup(dockerClient)
-	versions := calculateVersions(httpClient, *flagFrom, *flagTo)
+	versions := calculateVersions(httpClient, *flagFrom, *flagTo, *flagDirect)
 	log.Printf("Testing dendrite versions: %v\n", versions)
 
 	branchToImageID := buildDendriteImages(httpClient, dockerClient, *flagTempDir, *flagBuildConcurrency, versions)
diff --git a/cmd/goose/README.md b/cmd/goose/README.md
deleted file mode 100644
index 725c6a58..00000000
--- a/cmd/goose/README.md
+++ /dev/null
@@ -1,109 +0,0 @@
-## Database migrations
-
-We use [goose](https://github.com/pressly/goose) to handle database migrations. This allows us to execute
-both SQL deltas (e.g `ALTER TABLE ...`) as well as manipulate data in the database in Go using Go functions.
-
-To run a migration, the `goose` binary in this directory needs to be built:
-```
-$ go build ./cmd/goose
-```
-
-This binary allows Dendrite databases to be upgraded and downgraded. Sample usage for upgrading the roomserver database:
-
-```
-# for sqlite
-$ ./goose -dir roomserver/storage/sqlite3/deltas sqlite3 ./roomserver.db up
-
-# for postgres
-$ ./goose -dir roomserver/storage/postgres/deltas postgres "user=dendrite dbname=dendrite sslmode=disable" up
-```
-
-For a full list of options, including rollbacks, see https://github.com/pressly/goose or use `goose` with no args.
-
-
-### Rationale
-
-Dendrite creates tables on startup using `CREATE TABLE IF NOT EXISTS`, so you might think that we should also
-apply version upgrades on startup as well. This is convenient and doesn't involve an additional binary to run
-which complicates upgrades. However, combining the upgrade mechanism and the server binary makes it difficult
-to handle rollbacks. Firstly, how do you specify you wish to rollback? We would have to add additional flags
-to the main server binary to say "rollback to version X". Secondly, if you roll back the server binary from
-version 5 to version 4, the version 4 binary doesn't know how to rollback the database from version 5 to
-version 4! For these reasons, we prefer to have a separate "upgrade" binary which is run for database upgrades.
-Rather than roll-our-own migration tool, we decided to use [goose](https://github.com/pressly/goose) as it supports
-complex migrations in Go code in addition to just executing SQL deltas. Other alternatives like
-`github.com/golang-migrate/migrate` [do not support](https://github.com/golang-migrate/migrate/issues/15) these
-kinds of complex migrations.
-
-### Adding new deltas
-
-You can add `.sql` or `.go` files manually or you can use goose to create them for you.
-
-If you only want to add a SQL delta then run:
-
-```
-$ ./goose -dir serverkeyapi/storage/sqlite3/deltas sqlite3 ./foo.db create new_col sql
-2020/09/09 14:37:43 Created new file: serverkeyapi/storage/sqlite3/deltas/20200909143743_new_col.sql
-```
-
-In this case, the version number is `20200909143743`. The important thing is that it is always increasing.
-
-Then add up/downgrade SQL commands to the created file which looks like:
-```sql
--- +goose Up
--- +goose StatementBegin
-SELECT 'up SQL query';
--- +goose StatementEnd
-
--- +goose Down
--- +goose StatementBegin
-SELECT 'down SQL query';
--- +goose StatementEnd
-
-```
-You __must__ keep the `+goose` annotations. You'll need to repeat this process for Postgres.
-
-For complex Go migrations:
-
-```
-$ ./goose -dir serverkeyapi/storage/sqlite3/deltas sqlite3 ./foo.db create complex_update go
-2020/09/09 14:40:38 Created new file: serverkeyapi/storage/sqlite3/deltas/20200909144038_complex_update.go
-```
-
-Then modify the created `.go` file which looks like:
-
-```go
-package migrations
-
-import (
-	"database/sql"
-	"fmt"
-
-	"github.com/pressly/goose"
-)
-
-func init() {
-	goose.AddMigration(upComplexUpdate, downComplexUpdate)
-}
-
-func upComplexUpdate(tx *sql.Tx) error {
-	// This code is executed when the migration is applied.
-	return nil
-}
-
-func downComplexUpdate(tx *sql.Tx) error {
-	// This code is executed when the migration is rolled back.
-	return nil
-}
-
-```
-
-You __must__ import the package in `/cmd/goose/main.go` so `func init()` gets called.
-
-
-#### Database limitations
-
-- SQLite3 does NOT support `ALTER TABLE table_name DROP COLUMN` - you would have to rename the column or drop the table
-  entirely and recreate it. ([example](https://github.com/matrix-org/dendrite/blob/master/userapi/storage/accounts/sqlite3/deltas/20200929203058_is_active.sql))
-  
-  More information: [sqlite.org](https://www.sqlite.org/lang_altertable.html)
diff --git a/cmd/goose/main.go b/cmd/goose/main.go
deleted file mode 100644
index 31a5b005..00000000
--- a/cmd/goose/main.go
+++ /dev/null
@@ -1,154 +0,0 @@
-// This is custom goose binary
-
-package main
-
-import (
-	"flag"
-	"fmt"
-	"log"
-	"os"
-
-	"github.com/pressly/goose"
-
-	pgusers "github.com/matrix-org/dendrite/userapi/storage/postgres/deltas"
-	slusers "github.com/matrix-org/dendrite/userapi/storage/sqlite3/deltas"
-
-	_ "github.com/lib/pq"
-	_ "github.com/mattn/go-sqlite3"
-)
-
-const (
-	AppService       = "appservice"
-	FederationSender = "federationapi"
-	KeyServer        = "keyserver"
-	MediaAPI         = "mediaapi"
-	RoomServer       = "roomserver"
-	SigningKeyServer = "signingkeyserver"
-	SyncAPI          = "syncapi"
-	UserAPI          = "userapi"
-)
-
-var (
-	dir       = flags.String("dir", "", "directory with migration files")
-	flags     = flag.NewFlagSet("goose", flag.ExitOnError)
-	component = flags.String("component", "", "dendrite component name")
-	knownDBs  = []string{
-		AppService, FederationSender, KeyServer, MediaAPI, RoomServer, SigningKeyServer, SyncAPI, UserAPI,
-	}
-)
-
-// nolint: gocyclo
-func main() {
-	err := flags.Parse(os.Args[1:])
-	if err != nil {
-		panic(err.Error())
-	}
-	args := flags.Args()
-
-	if len(args) < 3 {
-		fmt.Println(
-			`Usage: goose [OPTIONS] DRIVER DBSTRING COMMAND
-
-Drivers:
-    postgres
-    sqlite3
-
-Examples:
-    goose -component roomserver sqlite3 ./roomserver.db status
-    goose -component roomserver sqlite3 ./roomserver.db up
-
-    goose -component roomserver postgres "user=dendrite dbname=dendrite sslmode=disable" status
-
-Options:
-  -component string
-    	Dendrite component name e.g roomserver, signingkeyserver, clientapi, syncapi
-  -table string
-    	migrations table name (default "goose_db_version")
-  -h	print help
-  -v	enable verbose mode
-  -dir string
-        directory with migration files, only relevant when creating new migrations.
-  -version
-    	print version
-
-Commands:
-    up                   Migrate the DB to the most recent version available
-    up-by-one            Migrate the DB up by 1
-    up-to VERSION        Migrate the DB to a specific VERSION
-    down                 Roll back the version by 1
-    down-to VERSION      Roll back to a specific VERSION
-    redo                 Re-run the latest migration
-    reset                Roll back all migrations
-    status               Dump the migration status for the current DB
-    version              Print the current version of the database
-    create NAME [sql|go] Creates new migration file with the current timestamp
-    fix                  Apply sequential ordering to migrations`,
-		)
-		return
-	}
-
-	engine := args[0]
-	if engine != "sqlite3" && engine != "postgres" {
-		fmt.Println("engine must be one of 'sqlite3' or 'postgres'")
-		return
-	}
-
-	knownComponent := false
-	for _, c := range knownDBs {
-		if c == *component {
-			knownComponent = true
-			break
-		}
-	}
-	if !knownComponent {
-		fmt.Printf("component must be one of %v\n", knownDBs)
-		return
-	}
-
-	if engine == "sqlite3" {
-		loadSQLiteDeltas(*component)
-	} else {
-		loadPostgresDeltas(*component)
-	}
-
-	dbstring, command := args[1], args[2]
-
-	db, err := goose.OpenDBWithDriver(engine, dbstring)
-	if err != nil {
-		log.Fatalf("goose: failed to open DB: %v\n", err)
-	}
-
-	defer func() {
-		if err := db.Close(); err != nil {
-			log.Fatalf("goose: failed to close DB: %v\n", err)
-		}
-	}()
-
-	arguments := []string{}
-	if len(args) > 3 {
-		arguments = append(arguments, args[3:]...)
-	}
-
-	// goose demands a directory even though we don't use it for upgrades
-	d := *dir
-	if d == "" {
-		d = os.TempDir()
-	}
-	if err := goose.Run(command, db, d, arguments...); err != nil {
-		log.Fatalf("goose %v: %v", command, err)
-	}
-}
-
-func loadSQLiteDeltas(component string) {
-	switch component {
-	case UserAPI:
-		slusers.LoadFromGoose()
-	}
-}
-
-func loadPostgresDeltas(component string) {
-	switch component {
-	case UserAPI:
-		pgusers.LoadFromGoose()
-	}
-}