Module: DynamicMigrations::Postgres::Server::Database::StructureLoader

Included in:
DynamicMigrations::Postgres::Server::Database
Defined in:
lib/dynamic_migrations/postgres/server/database/structure_loader.rb

Instance Method Summary collapse

Instance Method Details

#create_database_structure_cache ⇒ Object 



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
 
# File 'lib/dynamic_migrations/postgres/server/database/structure_loader.rb', line 8

def create_database_structure_cache
  connection.exec("    CREATE MATERIALIZED VIEW \#{Postgres.cache_schema_name}.dynamic_migrations_structure_cache AS\n      SELECT\n        -- Name of the schema containing the table\n        schemata.schema_name,\n        -- Name of the table\n        tables.table_name,\n        -- is this a real table or a view\n        tables.table_type,\n        -- The comment which has been added to the table (if any)\n        table_description.description AS table_description,\n        -- Name of the column\n        columns.column_name,\n        -- The comment which has been added to the column (if any)\n        column_description.description AS column_description,\n        -- Default expression of the column\n        columns.column_default,\n        -- YES if the column is possibly nullable, NO if\n        -- it is known not nullable\n        columns.is_nullable,\n        -- Is this column an array\n        columns.data_type = 'ARRAY' AS is_array,\n        -- The formatted data type (such as integer, char(5) or numeric(12,2)[])\n        CASE\n        WHEN tables.table_name IS NOT NULL THEN\n        (\n          SELECT format_type(atttypid,atttypmod) FROM pg_attribute a\n            WHERE a.attrelid = concat('\"', schemata.schema_name, '\"', '.', '\"', tables.table_name, '\"')::regclass\n            AND attnum = columns.ordinal_position\n        )\n        END AS data_type,\n        -- is this an emum\n        CASE\n        WHEN columns.data_type = 'ARRAY' OR columns.data_type = 'USER-DEFINED' THEN\n        (\n          SELECT EXISTS (\n            SELECT 1\n            FROM pg_type\n            INNER JOIN pg_enum\n              ON pg_type.oid = pg_enum.enumtypid\n            INNER JOIN pg_namespace\n              ON pg_namespace.oid = pg_type.typnamespace\n            WHERE\n              -- when the column is an array, the udt_name is the name of the enum prefixed with an underscore\n              (columns.data_type = 'ARRAY' AND concat('_', pg_type.typname) = columns.udt_name AND pg_namespace.nspname = columns.udt_schema)\n              -- when the column is not an array, the udt_name is the same name as the enum\n              OR (columns.data_type = 'USER-DEFINED' AND pg_type.typname = columns.udt_name AND pg_namespace.nspname = columns.udt_schema)\n          )\n        )\n        ELSE FALSE\n        END\n        AS is_enum,\n        -- If data_type identifies an interval type, this column contains\n        -- the specification which fields the intervals include for this\n        -- column, e.g., YEAR TO MONTH, DAY TO SECOND, etc. If no field\n        -- restrictions were specified (that is, the interval accepts all\n        -- fields), and for all other data types, this field is null.\n        columns.interval_type\n      FROM information_schema.schemata\n      LEFT JOIN information_schema.tables ON schemata.schema_name = tables.table_schema AND left(tables.table_name, 3) != 'pg_'\n      LEFT JOIN information_schema.columns ON tables.table_name = columns.table_name AND schemata.schema_name = columns.table_schema\n      -- required for the column and table description/comment joins\n      LEFT JOIN pg_catalog.pg_statio_all_tables ON pg_statio_all_tables.schemaname = schemata.schema_name AND pg_statio_all_tables.relname = tables.table_name\n      -- required for the table description/comment\n      LEFT JOIN pg_catalog.pg_description table_description ON table_description.objoid = pg_statio_all_tables.relid AND table_description.objsubid = 0\n      -- required for the column description/comment\n      LEFT JOIN pg_catalog.pg_description column_description ON column_description.objoid = pg_statio_all_tables.relid AND column_description.objsubid = columns.ordinal_position\n      WHERE\n        -- skip internal postgres schemas\n        schemata.schema_name != 'information_schema'\n        AND schemata.schema_name != 'postgis'\n        AND left(schemata.schema_name, 3) != 'pg_'\n        -- only base tables (skip views), the null check is required for the left join as the schema might be empty\n        AND (tables IS NULL OR tables.table_type = 'BASE TABLE')\n      -- order by the schema and table names alphabetically, then by the column position in the table\n      ORDER BY schemata.schema_name, tables.table_schema, columns.ordinal_position\n  SQL\n  connection.exec(<<~SQL)\n    COMMENT ON MATERIALIZED VIEW \#{Postgres.cache_schema_name}.dynamic_migrations_structure_cache IS 'A cached representation of the database structure. This is used by the dynamic migrations library and is created automatically and updated automatically after migrations have run.';\n  SQL\nend\n")

#fetch_schema_names ⇒ Object

returns a list of the schema names in this database



145
146
147
148
149
150
151
152
153
154
155
 
# File 'lib/dynamic_migrations/postgres/server/database/structure_loader.rb', line 145

def fetch_schema_names
  rows = connection.exec("    SELECT schema_name\n      FROM information_schema.schemata;\n  SQL\n  schema_names = rows.map { |row| row[\"schema_name\"] }\n  schema_names.reject! { |schema_name| schema_name == \"information_schema\" }\n  schema_names.reject! { |schema_name| schema_name == \"public\" }\n  schema_names.reject! { |schema_name| schema_name.start_with? \"pg_\" }\n  schema_names.sort\nend\n")

#fetch_structure ⇒ Object

fetch all columns from the database and build and return a useful hash representing the structure of your database



101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
 
# File 'lib/dynamic_migrations/postgres/server/database/structure_loader.rb', line 101

def fetch_structure
  begin
    rows = connection.exec("      SELECT * FROM \#{Postgres.cache_schema_name}.dynamic_migrations_structure_cache\n    SQL\n  rescue PG::UndefinedTable\n    create_database_structure_cache\n    rows = connection.exec(<<~SQL)\n      SELECT * FROM \#{Postgres.cache_schema_name}.dynamic_migrations_structure_cache\n    SQL\n  end\n\n  schemas = {}\n  rows.each do |row|\n    schema_name = row[\"schema_name\"].to_sym\n    schema = schemas[schema_name] ||= {\n      tables: {}\n    }\n\n    unless row[\"table_name\"].nil?\n      table_name = row[\"table_name\"].to_sym\n      table = schema[:tables][table_name] ||= {\n        description: row[\"table_description\"],\n        columns: {}\n      }\n\n      unless row[\"column_name\"].nil?\n        column_name = row[\"column_name\"].to_sym\n        column = table[:columns][column_name] ||= {}\n\n        column[:data_type] = row[\"data_type\"].to_sym\n        column[:null] = row[\"is_nullable\"] == \"YES\"\n        column[:is_enum] = row[\"is_enum\"] == \"t\"\n        column[:is_array] = row[\"is_array\"] == \"t\"\n        column[:default] = row[\"column_default\"]\n        column[:description] = row[\"column_description\"]\n        column[:interval_type] = row[\"interval_type\"].nil? ? nil : row[\"interval_type\"].to_sym\n      end\n    end\n  end\n  schemas\nend\n")

#fetch_table_names(schema_name) ⇒ Object

returns a list of the table names in the provided schema



158
159
160
161
162
163
164
165
166
 
# File 'lib/dynamic_migrations/postgres/server/database/structure_loader.rb', line 158

def fetch_table_names schema_name
  rows = connection.exec_params("    SELECT table_name FROM information_schema.tables\n      WHERE table_schema = $1\n  SQL\n  table_names = rows.map { |row| row[\"table_name\"] }\n  table_names.reject! { |table_name| table_name.start_with? \"pg_\" }\n  table_names.sort\nend\n", [schema_name.to_s])

#refresh_database_structure_cache ⇒ Object 



91
92
93
94
95
96
97
 
# File 'lib/dynamic_migrations/postgres/server/database/structure_loader.rb', line 91

def refresh_database_structure_cache
  connection.exec("    REFRESH MATERIALIZED VIEW \#{Postgres.cache_schema_name}.dynamic_migrations_structure_cache\n  SQL\nrescue PG::UndefinedTable\n  create_database_structure_cache\nend\n")