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(" CREATE MATERIALIZED VIEW \#{Postgres.cache_schema_name}.dynamic_migrations_keys_and_unique_constraints_cache as\n SELECT\n c.conname AS constraint_name,\n pg_get_constraintdef(c.oid, true) as constraint_definition,\n CASE c.contype\n WHEN 'f'::\"char\" THEN 'foreign_key'::text\n WHEN 'p'::\"char\" THEN 'primary_key'::text\n WHEN 'u'::\"char\" THEN 'unique'::text\n END AS constraint_type,\n sch.nspname AS schema_name,\n tbl.relname AS table_name,\n ARRAY_AGG(col.attname ORDER BY u.attposition) AS column_names,\n f_sch.nspname AS foreign_schema_name,\n f_tbl.relname AS foreign_table_name,\n -- null if is required to prevent indexes and unique constraints from being included\n NULLIF(ARRAY_AGG(f_col.attname ORDER BY f_u.attposition), ARRAY[null]::name[]) AS foreign_column_names,\n c.condeferrable as deferrable,\n c.condeferred as initially_deferred,\n CASE c.confupdtype\n WHEN 'a'::\"char\" THEN 'no_action'::text\n WHEN 'r'::\"char\" THEN 'restrict'::text\n WHEN 'c'::\"char\" THEN 'cascade'::text\n WHEN 'n'::\"char\" THEN 'set_null'::text\n WHEN 'd'::\"char\" THEN 'set_default'::text\n END AS on_update,\n CASE c.confdeltype\n WHEN 'a'::\"char\" THEN 'no_action'::text\n WHEN 'r'::\"char\" THEN 'restrict'::text\n WHEN 'c'::\"char\" THEN 'cascade'::text\n WHEN 'n'::\"char\" THEN 'set_null'::text\n WHEN 'd'::\"char\" THEN 'set_default'::text\n END AS on_delete,\n am.amname as index_type,\n obj_description(c.oid, 'pg_constraint') as description,\n -- in case we need to update this query in a later version of DynamicMigrations\n 1 as table_version\n FROM pg_constraint c\n LEFT JOIN LATERAL UNNEST(c.conkey)\n WITH ORDINALITY AS u(attnum, attposition)\n ON TRUE\n LEFT JOIN LATERAL UNNEST(c.confkey)\n WITH ORDINALITY AS f_u(attnum, attposition)\n ON f_u.attposition = u.attposition\n JOIN pg_class tbl\n ON\n tbl.oid = c.conrelid\n AND left(tbl.relname, 3) != 'pg_'\n JOIN pg_namespace sch\n ON\n sch.oid = tbl.relnamespace\n AND sch.nspname != 'information_schema'\n AND sch.nspname != 'postgis'\n AND left(sch.nspname, 3) != 'pg_'\n LEFT JOIN pg_attribute col\n ON\n (col.attrelid = tbl.oid\n AND col.attnum = u.attnum)\n LEFT JOIN pg_class f_tbl\n ON\n f_tbl.oid = c.confrelid\n AND left(f_tbl.relname, 3) != 'pg_'\n LEFT JOIN pg_namespace f_sch\n ON\n f_sch.oid = f_tbl.relnamespace\n AND f_sch.nspname != 'information_schema'\n AND f_sch.nspname != 'postgis'\n AND left(f_sch.nspname, 3) != 'pg_'\n LEFT JOIN pg_attribute f_col\n ON\n f_col.attrelid = f_tbl.oid\n AND f_col.attnum = f_u.attnum\n\n -- joins below to get the index type\n LEFT JOIN pg_class index_cls ON index_cls.relname = c.conname AND index_cls.relnamespace = sch.oid\n LEFT JOIN pg_index on index_cls.oid = pg_index.indexrelid AND tbl.oid = pg_index.indrelid\n LEFT JOIN pg_am am ON am.oid=index_cls.relam\n\n WHERE\n -- only foreign_key, unique or primary_key\n c.contype in ('f', 'u', 'p')\n\n GROUP BY c.oid, constraint_name, constraint_type, condeferrable, condeferred, schema_name, table_name, foreign_schema_name, foreign_table_name, am.amname\n ORDER BY schema_name, table_name;\n SQL\n connection.exec(<<~SQL)\n 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);\n SQL\n connection.exec(<<~SQL)\n 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.';\n SQL\nend\n")
|