1package migrations
2
3import migrate "github.com/rubenv/sql-migrate"
4
5func init() {
6	m := &migrate.Migration{
7		Id: "20210525173505_valid_primaries_view",
8		Up: []string{
9			`
10CREATE VIEW repository_generations AS
11	SELECT virtual_storage, relative_path, MAX(generation) AS generation
12	FROM storage_repositories
13	GROUP BY virtual_storage, relative_path
14			`,
15			`
16CREATE VIEW valid_primaries AS
17	WITH candidates AS (
18		SELECT virtual_storage, relative_path, storage, repository_assignments.storage IS NOT NULL AS assigned
19		FROM storage_repositories
20		JOIN repository_generations USING (virtual_storage, relative_path, generation)
21		JOIN healthy_storages USING (virtual_storage, storage)
22		LEFT JOIN repository_assignments USING (virtual_storage, relative_path, storage)
23		WHERE NOT EXISTS (
24			-- This check exists to prevent us from electing a primary that is pending deletion. The primary
25			-- could accept a write and lose it when the deletion is carried out.
26			SELECT true
27			FROM replication_queue
28			WHERE state NOT IN ('completed', 'dead', 'cancelled')
29			AND job->>'change' = 'delete_replica'
30			AND job->>'virtual_storage' = virtual_storage
31			AND job->>'relative_path' = relative_path
32			AND job->>'target_node_storage' = storage
33		)
34	)
35
36	SELECT virtual_storage, relative_path, storage
37	FROM candidates
38	WHERE assigned OR (
39		SELECT NOT EXISTS (
40			SELECT FROM candidates AS assigned_candidates
41			WHERE assigned
42			AND assigned_candidates.virtual_storage = candidates.virtual_storage
43			AND assigned_candidates.relative_path   = candidates.relative_path
44		)
45	)
46						`,
47		},
48		Down: []string{
49			"DROP VIEW valid_primaries",
50			"DROP VIEW repository_generations",
51		},
52	}
53
54	allMigrations = append(allMigrations, m)
55}
56