8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
# File 'lib/dynamic_migrations/postgres/server/database/keys_and_unique_constraints_loader.rb', line 8
def create_database_keys_and_unique_constraints_cache
connection.exec(<<~SQL)
CREATE MATERIALIZED VIEW #{Postgres.cache_schema_name}.dynamic_migrations_keys_and_unique_constraints_cache as
SELECT
c.conname AS constraint_name,
pg_get_constraintdef(c.oid, true) as constraint_definition,
CASE c.contype
WHEN 'f'::"char" THEN 'foreign_key'::text
WHEN 'p'::"char" THEN 'primary_key'::text
WHEN 'u'::"char" THEN 'unique'::text
END AS constraint_type,
sch.nspname AS schema_name,
tbl.relname AS table_name,
ARRAY_AGG(col.attname ORDER BY u.attposition) AS column_names,
f_sch.nspname AS foreign_schema_name,
f_tbl.relname AS foreign_table_name,
-- null if is required to prevent indexes and unique constraints from being included
NULLIF(ARRAY_AGG(f_col.attname ORDER BY f_u.attposition), ARRAY[null]::name[]) AS foreign_column_names,
c.condeferrable as deferrable,
c.condeferred as initially_deferred,
CASE c.confupdtype
WHEN 'a'::"char" THEN 'no_action'::text
WHEN 'r'::"char" THEN 'restrict'::text
WHEN 'c'::"char" THEN 'cascade'::text
WHEN 'n'::"char" THEN 'set_null'::text
WHEN 'd'::"char" THEN 'set_default'::text
END AS on_update,
CASE c.confdeltype
WHEN 'a'::"char" THEN 'no_action'::text
WHEN 'r'::"char" THEN 'restrict'::text
WHEN 'c'::"char" THEN 'cascade'::text
WHEN 'n'::"char" THEN 'set_null'::text
WHEN 'd'::"char" THEN 'set_default'::text
END AS on_delete,
am.amname as index_type,
obj_description(c.oid, 'pg_constraint') as description,
-- in case we need to update this query in a later version of DynamicMigrations
1 as table_version
FROM pg_constraint c
LEFT JOIN LATERAL UNNEST(c.conkey)
WITH ORDINALITY AS u(attnum, attposition)
ON TRUE
LEFT JOIN LATERAL UNNEST(c.confkey)
WITH ORDINALITY AS f_u(attnum, attposition)
ON f_u.attposition = u.attposition
JOIN pg_class tbl
ON
tbl.oid = c.conrelid
AND left(tbl.relname, 3) != 'pg_'
JOIN pg_namespace sch
ON
sch.oid = tbl.relnamespace
AND sch.nspname != 'information_schema'
AND sch.nspname != 'postgis'
AND left(sch.nspname, 3) != 'pg_'
LEFT JOIN pg_attribute col
ON
(col.attrelid = tbl.oid
AND col.attnum = u.attnum)
LEFT JOIN pg_class f_tbl
ON
f_tbl.oid = c.confrelid
AND left(f_tbl.relname, 3) != 'pg_'
LEFT JOIN pg_namespace f_sch
ON
f_sch.oid = f_tbl.relnamespace
AND f_sch.nspname != 'information_schema'
AND f_sch.nspname != 'postgis'
AND left(f_sch.nspname, 3) != 'pg_'
LEFT JOIN pg_attribute f_col
ON
f_col.attrelid = f_tbl.oid
AND f_col.attnum = f_u.attnum
-- joins below to get the index type
LEFT JOIN pg_class index_cls ON index_cls.relname = c.conname AND index_cls.relnamespace = sch.oid
LEFT JOIN pg_index on index_cls.oid = pg_index.indexrelid AND tbl.oid = pg_index.indrelid
LEFT JOIN pg_am am ON am.oid=index_cls.relam
WHERE
-- only foreign_key, unique or primary_key
c.contype in ('f', 'u', 'p')
GROUP BY c.oid, constraint_name, constraint_type, condeferrable, condeferred, schema_name, table_name, foreign_schema_name, foreign_table_name, am.amname
ORDER BY schema_name, table_name;
SQL
connection.exec(<<~SQL)
CREATE UNIQUE INDEX dynamic_migrations_keys_and_unique_constraints_cache_index ON #{Postgres.cache_schema_name}.dynamic_migrations_keys_and_unique_constraints_cache (schema_name, table_name, constraint_name);
SQL
connection.exec(<<~SQL)
COMMENT ON MATERIALIZED VIEW #{Postgres.cache_schema_name}.dynamic_migrations_keys_and_unique_constraints_cache IS 'A cached representation of the database constraints. This is used by the dynamic migrations library and is created automatically and updated automatically after migrations have run.';
SQL
end
|