Module: DbSchema::Reader::Postgres

Defined in:

lib/db_schema/reader.rb

Constant Summary

DEFAULT_VALUE =

/\A(('(?<string>.*)')|(?<float>\d+\.\d+)|(?<integer>\d+)|(?<boolean>true|false)|((?<function>[A-Za-z_]+)\(\)))/

COLUMN_NAMES_QUERY =

<<-SQL.freeze
   SELECT c.column_name AS name,
    c.ordinal_position AS pos,
    c.column_default AS default,
    c.is_nullable AS null,
    c.data_type AS type,
    c.udt_name AS custom_type_name,
    c.character_maximum_length AS char_length,
    c.numeric_precision AS num_precision,
    c.numeric_scale AS num_scale,
    c.datetime_precision AS dt_precision,
    c.interval_type,
    e.data_type AS element_type
     FROM information_schema.columns AS c
LEFT JOIN information_schema.element_types AS e
 ON e.object_catalog = c.table_catalog
AND e.object_schema = c.table_schema
AND e.object_name = c.table_name
AND e.object_type = 'TABLE'
AND e.collection_type_identifier = c.dtd_identifier
    WHERE c.table_schema = 'public'
AND c.table_name = ?
SQL

CONSTRAINTS_QUERY =

<<-SQL.freeze
SELECT conname AS name,
 pg_get_expr(conbin, conrelid, true) AS condition
  FROM pg_constraint, pg_class
 WHERE conrelid = pg_class.oid
   AND relname = ?
   AND contype = 'c'
SQL

INDICES_QUERY =

<<-SQL.freeze
   SELECT relname AS name,
    indkey AS column_positions,
    indisunique AS unique,
    indoption AS index_options,
    pg_get_expr(indpred, indrelid, true) AS condition,
    amname AS index_type,
    indexrelid AS index_oid
     FROM pg_class, pg_index
LEFT JOIN pg_opclass
 ON pg_opclass.oid = ANY(pg_index.indclass::int[])
LEFT JOIN pg_am
 ON pg_am.oid = pg_opclass.opcmethod
    WHERE pg_class.oid = pg_index.indexrelid
AND pg_class.oid IN (
     SELECT indexrelid
 FROM pg_index, pg_class
WHERE pg_class.relname = ?
  AND pg_class.oid = pg_index.indrelid
  AND indisprimary != 't'
)
  GROUP BY name, column_positions, indisunique, index_options, condition, index_type, index_oid
SQL

EXPRESSION_INDICES_QUERY =

<<-SQL.freeze
    WITH index_ids AS (SELECT unnest(?) AS index_id),
   elements AS (SELECT unnest(?) AS element)
  SELECT index_id,
   array_agg(pg_get_indexdef(index_id, element, 't')) AS definitions
    FROM index_ids, elements
GROUP BY index_id;
SQL

ENUMS_QUERY =

<<-SQL.freeze
  SELECT t.typname AS name,
   array_agg(e.enumlabel ORDER BY e.enumsortorder) AS values
    FROM pg_enum AS e
    JOIN pg_type AS t
ON t.oid = e.enumtypid
GROUP BY name
SQL

EXTENSIONS_QUERY =

<<-SQL.freeze
SELECT extname
  FROM pg_extension
 WHERE extname != 'plpgsql'
SQL

Class Method Summary

• .indices_data_for(table_name) ⇒ Object
• .read_schema ⇒ Object

Class Method Details

.indices_data_for(table_name) ⇒ Object

# File 'lib/db_schema/reader.rb', line 149

def indices_data_for(table_name)
  column_names = DbSchema.connection[COLUMN_NAMES_QUERY, table_name.to_s].reduce({}) do |names, column|
    names.merge(column[:pos] => column[:name].to_sym)
  end

  indices_data     = DbSchema.connection[INDICES_QUERY, table_name.to_s].to_a
  expressions_data = index_expressions_data(indices_data)

  indices_data.map do |index|
    positions = index[:column_positions].split(' ').map(&:to_i)
    options   = index[:index_options].split(' ').map(&:to_i)

    columns = positions.zip(options).map do |column_position, column_order_options|
      options = case column_order_options
      when 0
        {}
      when 3
        { order: :desc }
      when 2
        { nulls: :first }
      when 1
        { order: :desc, nulls: :last }
      end

      if column_position.zero?
        expression = expressions_data.fetch(index[:index_oid]).shift
        DbSchema::Definitions::Index::Expression.new(expression, **options)
      else
        DbSchema::Definitions::Index::TableField.new(column_names.fetch(column_position), **options)
      end
    end

    {
      name:      index[:name].to_sym,
      columns:   columns,
      unique:    index[:unique],
      type:      index[:index_type].to_sym,
      condition: index[:condition]
    }
  end
end

.read_schema ⇒ Object

# File 'lib/db_schema/reader.rb', line 106

def read_schema
  enums = DbSchema.connection[ENUMS_QUERY].map do |enum_data|
    Definitions::Enum.new(enum_data[:name].to_sym, enum_data[:values].map(&:to_sym))
  end

  extensions = DbSchema.connection[EXTENSIONS_QUERY].map do |extension_data|
    Definitions::Extension.new(extension_data[:extname].to_sym)
  end

  tables = DbSchema.connection.tables.map do |table_name|
    primary_key_name = DbSchema.connection.primary_key(table_name)

    fields = DbSchema.connection[COLUMN_NAMES_QUERY, table_name.to_s].map do |column_data|
      build_field(column_data, primary_key: column_data[:name] == primary_key_name)
    end

    indices = indices_data_for(table_name).map do |index_data|
      Definitions::Index.new(index_data)
    end.sort_by(&:name)

    foreign_keys = DbSchema.connection.foreign_key_list(table_name).map do |foreign_key_data|
      build_foreign_key(foreign_key_data)
    end

    checks = DbSchema.connection[CONSTRAINTS_QUERY, table_name.to_s].map do |check_data|
      Definitions::CheckConstraint.new(
        name:      check_data[:name].to_sym,
        condition: check_data[:condition]
      )
    end

    Definitions::Table.new(
      table_name,
      fields:       fields,
      indices:      indices,
      checks:       checks,
      foreign_keys: foreign_keys
    )
  end

  enums + extensions + tables
end