Link existing database record via repository ID

Praefect generates a repository ID to uniquely identify a repository.
While new records that get created are already being linked via the
repository ID, historical records in the database are still not linked
via the repository ID. In order to update the queries to join the records
via the repository ID, all of the relevant records need to be linked via
the repository ID. This commit adds a migration that links the records
in 'storage_repositories' and 'repository_assignments' to the 'repositories'
table's records via the ID.

Replication jobs are not included in the migration as they are not long lived
and they can be rescheduled.

Changelog: other

1 files changed, 28 insertions, 0 deletions

diff --git a/internal/praefect/datastore/migrations/20210906145021_link_repository_id.go b/internal/praefect/datastore/migrations/20210906145021_link_repository_id.go
new file mode 100644
index 000000000..08c9663bd
--- /dev/null
+++ b/internal/praefect/datastore/migrations/20210906145021_link_repository_id.go
@@ -0,0 +1,28 @@
+package migrations
+
+import migrate "github.com/rubenv/sql-migrate"
+
+func init() {
+	m := &migrate.Migration{
+		Id: "20210906145021_link_repository_id",
+		Up: []string{
+			`
+UPDATE storage_repositories
+SET repository_id = repositories.repository_id
+FROM repositories
+WHERE storage_repositories.virtual_storage = repositories.virtual_storage
+AND   storage_repositories.relative_path   = repositories.relative_path
+			`,
+			`
+UPDATE repository_assignments
+SET repository_id = repositories.repository_id
+FROM repositories
+WHERE repository_assignments.virtual_storage = repositories.virtual_storage
+AND   repository_assignments.relative_path   = repositories.relative_path
+			`,
+		},
+		Down: []string{},
+	}
+
+	allMigrations = append(allMigrations, m)
+}