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

#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(<<~SQL)
    SELECT schema_name
      FROM information_schema.schemata;
  SQL
  schema_names = rows.map { |row| row["schema_name"] }
  schema_names.reject! { |schema_name| schema_name == "information_schema" }
  schema_names.reject! { |schema_name| schema_name == "public" }
  schema_names.reject! { |schema_name| schema_name.start_with? "pg_" }
  schema_names.sort
end

#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(<<~SQL)
      SELECT * FROM #{Postgres.cache_schema_name}.dynamic_migrations_structure_cache
    SQL
  rescue PG::UndefinedTable
    create_database_structure_cache
    rows = connection.exec(<<~SQL)
      SELECT * FROM #{Postgres.cache_schema_name}.dynamic_migrations_structure_cache
    SQL
  end

  schemas = {}
  rows.each do |row|
    schema_name = row["schema_name"].to_sym
    schema = schemas[schema_name] ||= {
      tables: {}
    }

    unless row["table_name"].nil?
      table_name = row["table_name"].to_sym
      table = schema[:tables][table_name] ||= {
        description: row["table_description"],
        columns: {}
      }

      unless row["column_name"].nil?
        column_name = row["column_name"].to_sym
        column = table[:columns][column_name] ||= {}

        column[:data_type] = row["data_type"].to_sym
        column[:null] = row["is_nullable"] == "YES"
        column[:is_enum] = row["is_enum"] == "t"
        column[:is_array] = row["is_array"] == "t"
        column[:default] = row["column_default"]
        column[:description] = row["column_description"]
        column[:interval_type] = row["interval_type"].nil? ? nil : row["interval_type"].to_sym
      end
    end
  end
  schemas
end

#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(<<~SQL, [schema_name.to_s])
    SELECT table_name FROM information_schema.tables
      WHERE table_schema = $1
  SQL
  table_names = rows.map { |row| row["table_name"] }
  table_names.reject! { |table_name| table_name.start_with? "pg_" }
  table_names.sort
end

#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(<<~SQL)
    REFRESH MATERIALIZED VIEW #{Postgres.cache_schema_name}.dynamic_migrations_structure_cache
  SQL
rescue PG::UndefinedTable
  create_database_structure_cache
end