From 7279d23560fc7b60efa107fbfa9e0086681d00d6 Mon Sep 17 00:00:00 2001
From: Aleksandr Nogikh <nogikh@google.com>
Date: Tue, 13 May 2025 14:44:58 +0200
Subject: syz-cluster/pkg/db: test migrations

Add a test that verifies that we have correct down migrations.
Fix the down migrations sql file.
---
 syz-cluster/db-mgmt/main.go                        |  2 +-
 .../pkg/db/migrations/1_initialize.down.sql        | 36 +++++++++++++++++-----
 syz-cluster/pkg/db/spanner.go                      | 25 +++++++++------
 syz-cluster/pkg/db/spanner_test.go                 | 21 +++++++++++++
 4 files changed, 66 insertions(+), 18 deletions(-)
 create mode 100644 syz-cluster/pkg/db/spanner_test.go

diff --git a/syz-cluster/db-mgmt/main.go b/syz-cluster/db-mgmt/main.go
index 17ad70f1d..7d362e929 100644
--- a/syz-cluster/db-mgmt/main.go
+++ b/syz-cluster/db-mgmt/main.go
@@ -66,7 +66,7 @@ func main() {
 		switch os.Args[1] {
 		case "migrate":
 			log.Printf("running schema migrations")
-			err = db.RunMigrations(ctx, uri.Full)
+			err = db.RunMigrations(uri.Full)
 		case "run":
 			if len(os.Args) < 3 {
 				app.Fatalf("second argument is the SQL query to run")
diff --git a/syz-cluster/pkg/db/migrations/1_initialize.down.sql b/syz-cluster/pkg/db/migrations/1_initialize.down.sql
index 050a78ac4..04bdb97e2 100644
--- a/syz-cluster/pkg/db/migrations/1_initialize.down.sql
+++ b/syz-cluster/pkg/db/migrations/1_initialize.down.sql
@@ -1,10 +1,32 @@
-BEGIN TRANSACTION;
+-- Delete constraints first as these may create ciruclar dependencies.
+ALTER TABLE Patches DROP CONSTRAINT FK_SeriesPatches;
+ALTER TABLE Builds DROP CONSTRAINT FK_Series;
+ALTER TABLE Sessions DROP CONSTRAINT FK_SeriesSessions;
+ALTER TABLE Series DROP CONSTRAINT FK_SeriesLatestSession;
+ALTER TABLE SessionTests DROP CONSTRAINT FK_SessionResults;
+ALTER TABLE SessionTests DROP CONSTRAINT FK_BaseBuild;
+ALTER TABLE SessionTests DROP CONSTRAINT FK_PatchedBuild;
+ALTER TABLE Findings DROP CONSTRAINT FK_SessionCrashes;
+ALTER TABLE Findings DROP CONSTRAINT FK_TestCrashes;
+ALTER TABLE SessionReports DROP CONSTRAINT FK_SessionReports;
+ALTER TABLE ReportReplies DROP CONSTRAINT FK_ReplyReportID;
 
-DROP TABLE Series;
-DROP TABLE Patches;
-DROP TABLE Sessions;
+-- Spanner does not let drop tables without first deleting the indices.
+DROP INDEX SeriesByPublishedAt;
+DROP INDEX SeriesByExtID;
+DROP INDEX PatchesBySeriesAndSeq;
+DROP INDEX LastSuccessfulBuild;
+DROP INDEX SessionsByFinishedAt;
+DROP INDEX NoDupFindings;
+DROP INDEX NoDupSessionReports;
+DROP INDEX SessionReportsByStatus;
+DROP INDEX SessionReportsByMessageID;
+
+DROP TABLE ReportReplies;
+DROP TABLE Findings;
 DROP TABLE SessionTests;
+DROP TABLE SessionReports;
+DROP TABLE Patches;
 DROP TABLE Builds;
-DROP TABLE Findings;
-
-COMMIT;
+DROP TABLE Sessions;
+DROP TABLE Series;
diff --git a/syz-cluster/pkg/db/spanner.go b/syz-cluster/pkg/db/spanner.go
index 8db081c14..3135038b3 100644
--- a/syz-cluster/pkg/db/spanner.go
+++ b/syz-cluster/pkg/db/spanner.go
@@ -104,24 +104,29 @@ func dropSpannerDB(ctx context.Context, uri ParsedURI) error {
 //go:embed migrations/*.sql
 var migrationsFs embed.FS
 
-func RunMigrations(ctx context.Context, uri string) error {
-	sourceDriver, err := iofs.New(migrationsFs, "migrations")
+func RunMigrations(uri string) error {
+	m, err := getMigrateInstance(uri)
 	if err != nil {
 		return err
 	}
+	return m.Up()
+}
+
+func getMigrateInstance(uri string) (*migrate.Migrate, error) {
+	sourceDriver, err := iofs.New(migrationsFs, "migrations")
+	if err != nil {
+		return nil, err
+	}
 	s := &migrate_spanner.Spanner{}
 	dbDriver, err := s.Open("spanner://" + uri + "?x-clean-statements=true")
 	if err != nil {
-		return err
+		return nil, err
 	}
 	m, err := migrate.NewWithInstance("iofs", sourceDriver, "spanner", dbDriver)
-	if err == migrate.ErrNoChange {
-		// This is not a problem.
-		return nil
-	} else if err != nil {
-		return err
+	if err != nil {
+		return nil, err
 	}
-	return m.Up()
+	return m, nil
 }
 
 func NewTransientDB(t *testing.T) (*spanner.Client, context.Context) {
@@ -162,7 +167,7 @@ func NewTransientDB(t *testing.T) (*spanner.Client, context.Context) {
 		t.Fatal(err)
 	}
 	t.Cleanup(client.Close)
-	err = RunMigrations(ctx, uri.Full)
+	err = RunMigrations(uri.Full)
 	if err != nil {
 		t.Fatal(err)
 	}
diff --git a/syz-cluster/pkg/db/spanner_test.go b/syz-cluster/pkg/db/spanner_test.go
new file mode 100644
index 000000000..ec181409e
--- /dev/null
+++ b/syz-cluster/pkg/db/spanner_test.go
@@ -0,0 +1,21 @@
+// Copyright 2025 syzkaller project authors. All rights reserved.
+// Use of this source code is governed by Apache 2 LICENSE that can be found in the LICENSE file.
+
+package db
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/require"
+)
+
+func TestMigrations(t *testing.T) {
+	// Run, rollback and then again apply all DB migrations.
+	client, _ := NewTransientDB(t)
+	m, err := getMigrateInstance(client.DatabaseName())
+	require.NoError(t, err)
+	err = m.Down()
+	require.NoError(t, err, "migrating down failed")
+	err = m.Up()
+	require.NoError(t, err, "migrating up again failed")
+}
-- 
cgit mrf-deployment