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)

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



197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 197

def column_definitions(table_name)
  local = 'AND a.attislocal' if @_dump_mode

  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_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 #{local}
      ORDER BY a.attnum
  SQL
end

#configure_connection ⇒ Object

Configure the interval format



63
64
65
66
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 63

def configure_connection
  super
  execute("SET SESSION IntervalStyle TO 'iso_8601'", 'SCHEMA')
end

#create_enum(name) ⇒ Object

Since enums create new types, type map needs to be rebooted to include the new ones, both normal and array one



70
71
72
73
74
75
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 70

def create_enum(name, *)
  super

  oid = query_value("SELECT #{quote(name)}::regtype::oid", "SCHEMA").to_i
  load_additional_types([oid])
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

#inherited_tables ⇒ Object

Get the list of inherited tables associated with their parent tables



156
157
158
159
160
161
162
163
164
165
166
167
168
169
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 156

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



78
79
80
81
82
83
84
85
86
87
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 78

def initialize_type_map(m = type_map)
  super
  m.register_type 'box',      OID::Box.new
  m.register_type 'circle',   OID::Circle.new
  m.register_type 'interval', OID::Interval.new
  m.register_type 'line',     OID::Line.new
  m.register_type 'segment',  OID::Segment.new

  m.alias_type 'regclass', 'varchar'
end

#load_additional_types(oids = nil) ⇒ Object

:nodoc:



90
91
92
93
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 90

def load_additional_types(oids = nil)
  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) == 1
    SELECT 1 FROM pg_catalog.pg_namespace WHERE nspname = '#{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.



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
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 96

def torque_load_additional_types(oids = nil)
  filter = ("AND     a.typelem::integer IN (%s)" % oids.join(', ')) if oids

  query = <<-SQL
    SELECT      a.typelem AS oid, t.typname, t.typelem,
                t.typdelim, t.typbasetype, t.typtype,
                t.typarray
    FROM        pg_type t
    INNER JOIN  pg_type a ON (a.oid = t.typarray)
    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 ( 'e' )
    #{filter}
    AND     NOT EXISTS(
              SELECT 1 FROM pg_catalog.pg_type el
                WHERE el.oid = t.typelem AND el.typarray = t.oid
              )
    AND     (t.typrelid = 0 OR (
              SELECT c.relkind = 'c' FROM pg_catalog.pg_class c
                WHERE c.oid = t.typrelid
              ))
  SQL

  execute_and_clear(query, 'SCHEMA', []) do |records|
    records.each { |row| OID::Enum.create(row, type_map) }
  end
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



172
173
174
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 172

def user_defined_schemas
  query_values(user_defined_schemas_sql, 'SCHEMA')
end

#user_defined_schemas_sql ⇒ Object

Build the query for allowed schemas



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

def user_defined_schemas_sql
  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

  <<-SQL.squish
    SELECT nspname
    FROM pg_catalog.pg_namespace
    WHERE 1=1 AND #{conditions.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



126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/torque/postgresql/adapter/database_statements.rb', line 126

def user_defined_types(*categories)
  category_condition = categories.present? \
    ? "AND t.typtype IN ('#{categories.join("', '")}')" \
    : "AND t.typtype NOT IN ('b', 'd')"

  select_all(<<-SQL, 'SCHEMA').rows.to_h
    SELECT t.typname AS name,
           CASE t.typtype
             WHEN 'e' THEN 'enum'
             END     AS type
    FROM pg_type t
           LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
      #{category_condition}
      AND NOT EXISTS(
        SELECT 1
        FROM pg_catalog.pg_type el
        WHERE el.oid = t.typelem
          AND el.typarray = t.oid
      )
      AND (t.typrelid = 0 OR (
      SELECT c.relkind = 'c'
      FROM pg_catalog.pg_class c
      WHERE c.oid = t.typrelid
    ))
    ORDER BY t.typtype DESC
  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