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