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.
---
 .../pkg/db/migrations/1_initialize.down.sql        | 36 +++++++++++++++++-----
 1 file changed, 29 insertions(+), 7 deletions(-)

(limited to 'syz-cluster/pkg/db/migrations/1_initialize.down.sql')

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;
-- 
cgit mrf-deployment