Module: Torque::PostgreSQL::Adapter::DatabaseStatements

Included in:
Torque::PostgreSQL::Adapter
Defined in:
lib/torque/postgresql/adapter/database_statements.rb

Constant Summary collapse

EXTENDED_DATABASE_TYPES = 
%i[enum enum_set interval composite ltree lquery]

Instance Method Summary collapse

Instance Method Details

#column_definitions(table_name) ⇒ Object

Get the list of columns, and their definition, but only from the actual table, does not include columns that comes from inherited table



209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 209

def column_definitions(table_name)
  query(<<~SQL, "SCHEMA")
      SELECT a.attname, format_type(a.atttypid, a.atttypmod),
             pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
             c.collname, col_description(a.attrelid, a.attnum) AS comment,
             #{supports_identity_columns? ? 'attidentity' : quote('')} AS identity,
             #{supports_virtual_columns? ? 'attgenerated' : quote('')} as attgenerated
        FROM pg_attribute a
        LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
        LEFT JOIN pg_type t ON a.atttypid = t.oid
        LEFT JOIN pg_collation c ON a.attcollation = c.oid AND a.attcollation <> t.typcollation
       WHERE a.attrelid = #{quote(quote_table_name(table_name))}::regclass
         AND a.attnum > 0 AND NOT a.attisdropped
         #{'AND a.attislocal' if @_dump_mode}
       ORDER BY a.attnum
  SQL
end

#composite_column_types(name) ⇒ Object

Get the ordered list of columns of a composite type, as a hash of column name and its proper cast type



135
136
137
138
139
140
141
142
143
144
145
146
147
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 135

def composite_column_types(name)
  rows = query(<<~SQL, 'SCHEMA')
    SELECT a.attname, a.atttypid, a.atttypmod
    FROM pg_attribute a
    WHERE a.attrelid = #{quote(quote_type_name(name).to_s)}::regclass
      AND a.attnum > 0 AND NOT a.attisdropped
    ORDER BY a.attnum
  SQL

  rows.each_with_object({}) do |(attr_name, oid, fmod), hash|
    hash[attr_name] = get_oid_type(oid.to_i, fmod.to_i, attr_name)
  end
end

#composite_types ⇒ Object

Get the list of user-defined composite types, sorted so that types that depend on other composite types come later



151
152
153
154
155
156
157
158
159
160
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 151

def composite_types
  dependencies = composite_type_dependencies
  result = []

  dependencies.each_key do |name|
    sort_composite_type(name, dependencies, result)
  end

  result
end

#dump_mode! ⇒ Object

Switch between dump mode or not



11
12
13
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 11

def dump_mode!
  @_dump_mode = !!!@_dump_mode
end

#extended_types ⇒ Object

Get the list of extended types



41
42
43
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 41

def extended_types
  EXTENDED_DATABASE_TYPES
end

#filter_by_schema ⇒ Object

Build the condition for filtering by schema



262
263
264
265
266
267
268
269
270
271
272
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 262

def filter_by_schema
  conditions = []
  conditions << <<-SQL.squish if schemas_blacklist.any?
    nspname NOT LIKE ALL (ARRAY['#{schemas_blacklist.join("', '")}'])
  SQL

  conditions << <<-SQL.squish if schemas_whitelist.any?
    nspname LIKE ANY (ARRAY['#{schemas_whitelist.join("', '")}'])
  SQL
  conditions
end

#inherited_tables ⇒ Object

Get the list of inherited tables associated with their parent tables



177
178
179
180
181
182
183
184
185
186
187
188
189
190
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 177

def inherited_tables
  tables = query(<<-SQL, 'SCHEMA')
    SELECT inhrelid::regclass  AS table_name,
           inhparent::regclass AS inheritances
    FROM pg_inherits
    JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
    ORDER BY inhrelid
  SQL

  tables.each_with_object({}) do |(child, parent), result|
    (result[child] ||= []) << parent
  end
end

#initialize_type_map(m = type_map) ⇒ Object

Change some of the types being mapped



63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 63

def initialize_type_map(m = type_map)
  super

  if PostgreSQL.config.geometry.enabled
    m.register_type 'box',      OID::Box.new
    m.register_type 'circle',   OID::Circle.new
    m.register_type 'line',     OID::Line.new
    m.register_type 'segment',  OID::Segment.new
  end

  if PostgreSQL.config.interval.enabled
    m.register_type 'interval', OID::Interval.new
  end

  if PostgreSQL.config.ltree.enabled
    m.register_type 'ltree',    OID::Ltree.new
    m.register_type 'lquery',   OID::Lquery.new
  end
end

#list_versioned_commands(type) ⇒ Object

Get all possible schema entries that can be created via versioned commands of the provided type. Mostly for covering removals and not dump them



230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 230

def list_versioned_commands(type)
  query =
    case type
    when :function
      <<-SQL.squish
        SELECT n.nspname AS schema, p.proname AS name
        FROM pg_catalog.pg_proc p
        INNER JOIN pg_namespace n ON n.oid = p.pronamespace
        WHERE 1=1 AND #{filter_by_schema.join(' AND ')};
      SQL
    when :type
      <<-SQL.squish
        SELECT n.nspname AS schema, t.typname AS name
        FROM pg_type t
        INNER JOIN pg_namespace n ON n.oid = t.typnamespace
        WHERE 1=1 AND t.typtype NOT IN ('e')
          AND #{filter_by_schema.join(' AND ')};
      SQL
    when :view
      <<-SQL.squish
        SELECT n.nspname AS schema, c.relname AS name
        FROM pg_class c
        INNER JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE 1=1 AND c.relkind IN ('v', 'm')
          AND #{filter_by_schema.join(' AND ')};
      SQL
    end

  select_rows(query, 'SCHEMA')
end

#load_additional_types(oids = nil) ⇒ Object

:nodoc:



84
85
86
87
88
89
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 84

def load_additional_types(oids = nil)
  type_map.alias_type 'regclass', 'varchar'
  type_map.alias_type 'regconfig', 'varchar'
  super
  torque_load_additional_types(oids)
end

#schema_exists?(name, filtered: true) ⇒ Boolean

Checks if a given schema exists in the database. If filtered is given as false, then it will check regardless of whitelist and blacklist

Returns:

  • (Boolean) 


48
49
50
51
52
53
54
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 48

def schema_exists?(name, filtered: true)
  return user_defined_schemas.include?(name.to_s) if filtered

  query_value(<<-SQL, "SCHEMA") == 1
    SELECT 1 FROM pg_catalog.pg_namespace WHERE nspname = #{quote(name)}
  SQL
end

#schemas_blacklist ⇒ Object

List of schemas blocked by the application in the current connection



16
17
18
19
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 16

def schemas_blacklist
  @schemas_blacklist ||= Torque::PostgreSQL.config.schemas.blacklist +
    (@config.dig(:schemas, 'blacklist') || [])
end

#schemas_search_path_sanitized ⇒ Object

A list of schemas on the search path sanitized



28
29
30
31
32
33
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 28

def schemas_search_path_sanitized
  @schemas_search_path_sanitized ||= begin
    db_user = @config[:username] || ENV['USER'] || ENV['USERNAME']
    schema_search_path.split(',').map { |item| item.strip.sub('"$user"', db_user) }
  end
end

#schemas_whitelist ⇒ Object

List of schemas used by the application in the current connection



22
23
24
25
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 22

def schemas_whitelist
  @schemas_whitelist ||= Torque::PostgreSQL.config.schemas.whitelist +
    (@config.dig(:schemas, 'whitelist') || [])
end

#torque_load_additional_types(oids = nil) ⇒ Object

Add the composite types to be loaded too.



92
93
94
95
96
97
98
99
100
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
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 92

def torque_load_additional_types(oids = nil)
  return unless torque_load_additional_types?

  # Types: (b)ase, (c)omposite, (d)omain, (e)num, (p)seudotype, (r)ange
  # (m)ultirange

  query = <<~SQL
    SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput,
           r.rngsubtype, t.typtype, t.typbasetype, t.typarray, c.relkind
    FROM pg_type as t
    LEFT JOIN pg_range as r ON t.oid = rngtypid
    LEFT JOIN pg_class as c ON c.oid = t.typrelid
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  SQL

  if oids
    query += " AND t.oid IN (%s)" % oids.join(", ")
  else
    conditions = []
    conditions << "t.typtype IN ('e')" if PostgreSQL.config.enum.enabled
    conditions << "(t.typtype = 'c' AND c.relkind = 'c')" \
      if PostgreSQL.config.composite.enabled

    query += " AND (#{conditions.join(' OR ')})"
  end

  options = { allow_retry: true, materialize_transactions: false }
  internal_execute(query, 'SCHEMA', **options).each do |row|
    if row['typtype'] == 'e'
      OID::Enum.create(row, type_map) if PostgreSQL.config.enum.enabled
    elsif row['typtype'] == 'c' && row['relkind'] == 'c'
      OID::Composite.create(row, type_map) if PostgreSQL.config.composite.enabled
    end
  end
end

#torque_load_additional_types? ⇒ Boolean

Returns:

  • (Boolean) 


129
130
131
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 129

def torque_load_additional_types?
  PostgreSQL.config.enum.enabled || PostgreSQL.config.composite.enabled
end

#type_exists?(name) ⇒ Boolean Also known as: data_type_exists?

Returns true if type exists.

Returns:

  • (Boolean) 


57
58
59
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 57

def type_exists?(name)
  user_defined_types.key? name.to_s
end

#user_defined_schemas ⇒ Object

Get the list of schemas that were created by the user



193
194
195
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 193

def user_defined_schemas
  query_values(user_defined_schemas_sql, 'SCHEMA')
end

#user_defined_schemas_sql ⇒ Object

Build the query for allowed schemas



198
199
200
201
202
203
204
205
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 198

def user_defined_schemas_sql
  <<-SQL.squish
    SELECT nspname
    FROM pg_catalog.pg_namespace
    WHERE 1=1 AND #{filter_by_schema.join(' AND ')}
    ORDER BY oid
  SQL
end

#user_defined_types(*categories) ⇒ Object

Gets a list of user defined types. You can even choose the category filter



164
165
166
167
168
169
170
171
172
173
174
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 164

def user_defined_types(*categories)
  categories = categories.compact.presence || %w[c e p r m]

  query(<<-SQL, 'SCHEMA').to_h
    SELECT t.typname, t.typtype
    FROM pg_type as t
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
    AND t.typtype IN ('#{categories.join("', '")}')
  SQL
end

#valid_type?(type) ⇒ Boolean

Check if a given type is valid.

Returns:

  • (Boolean) 


36
37
38
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 36

def valid_type?(type)
  super || extended_types.include?(type)
end