Module: ActiveRecord::ConnectionAdapters::CockroachDB::SchemaStatements

Includes:
PostgreSQL::SchemaStatements
Included in:
ActiveRecord::ConnectionAdapters::CockroachDBAdapter
Defined in:
lib/active_record/connection_adapters/cockroachdb/schema_statements.rb

Instance Method Summary collapse

Instance Method Details

#create_schema_dumper(options) ⇒ Object 



405
406
407
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 405

def create_schema_dumper(options)
  CockroachDB::SchemaDumper.create(self, options)
end

#create_table_definition(*args, **kwargs) ⇒ Object

override



401
402
403
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 401

def create_table_definition(*args, **kwargs)
  CockroachDB::TableDefinition.new(self, *args, **kwargs)
end

#default_sequence_name(table_name, pk = "id") ⇒ Object

CockroachDB uses unique_rowid() for primary keys, not sequences. It's possible to force a table to use sequences, but since it's not the default behavior we'll always return nil for default_sequence_name.



289
290
291
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 289

def default_sequence_name(table_name, pk = "id")
  nil
end

#foreign_key_options(from_table, to_table, options) ⇒ Object

OVERRIDE: CockroachDB does not support deferrable constraints. See: https://go.crdb.dev/issue-v/31632/v23.1



150
151
152
153
154
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 150

def foreign_key_options(from_table, to_table, options)
  options = super
  options.delete(:deferrable) unless supports_deferrable_constraints?
  options
end

#foreign_keys(table_name) ⇒ Object

OVERRIDE(v8.1.1):

- Replaces t2.oid::regclass::text with t2.relname
since this is more efficient in CockroachDB.
- prepend schema name to relname (see `AS to_table`)
- handle hidden attributes.

NOTE: If you edit this method, you'll need to edit the #all_foreign_keys method as well.



224
225
226
227
228
229
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
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 224

def foreign_keys(table_name)
  scope = quoted_scope(table_name)
  fk_info = internal_exec_query(<<~SQL, "SCHEMA", allow_retry: true, materialize_transactions: false)
    SELECT CASE
      WHEN n2.nspname = current_schema()
      THEN ''
      ELSE n2.nspname || '.'
    END || t2.relname AS to_table,
    c.conname AS name, c.confupdtype AS on_update, c.confdeltype AS on_delete, c.convalidated AS valid, c.condeferrable AS deferrable, c.condeferred AS deferred, c.conrelid, c.confrelid,
      (
        SELECT array_agg(a.attname ORDER BY idx)
        FROM (
          SELECT idx, c.conkey[idx] AS conkey_elem
          FROM generate_subscripts(c.conkey, 1) AS idx
        ) indexed_conkeys
        JOIN pg_attribute a ON a.attrelid = t1.oid
        AND a.attnum = indexed_conkeys.conkey_elem
        AND NOT a.attishidden
      ) AS conkey_names,
      (
        SELECT array_agg(a.attname ORDER BY idx)
        FROM (
          SELECT idx, c.confkey[idx] AS confkey_elem
          FROM generate_subscripts(c.confkey, 1) AS idx
        ) indexed_confkeys
        JOIN pg_attribute a ON a.attrelid = t2.oid
        AND a.attnum = indexed_confkeys.confkey_elem
        AND NOT a.attishidden
      ) AS confkey_names
    FROM pg_constraint c
    JOIN pg_class t1 ON c.conrelid = t1.oid
    JOIN pg_class t2 ON c.confrelid = t2.oid
    JOIN pg_namespace n1 ON t1.relnamespace = n1.oid
    JOIN pg_namespace n2 ON t2.relnamespace = n2.oid
    WHERE c.contype = 'f'
      AND t1.relname = #{scope[:name]}
      AND n1.nspname = #{scope[:schema]}
    ORDER BY c.conname
  SQL

  fk_info.map do |row|
    to_table = PostgreSQL::Utils.unquote_identifier(row["to_table"])

    column = decode_string_array(row["conkey_names"])
    primary_key = decode_string_array(row["confkey_names"])

    options = {
      column: column.size == 1 ? column.first : column,
      name: row["name"],
      primary_key: primary_key.size == 1 ? primary_key.first : primary_key
    }

    options[:on_delete] = extract_foreign_key_action(row["on_delete"])
    options[:on_update] = extract_foreign_key_action(row["on_update"])
    options[:deferrable] = extract_constraint_deferrable(row["deferrable"], row["deferred"])

    options[:validate] = row["valid"]

    ForeignKeyDefinition.new(table_name, to_table, options)
  end
end

#indexes(table_name) ⇒ Object

OVERRIDE(v8.1.1):

- prepend Utils with PostgreSQL::
- handle hidden attributes

Returns an array of indexes for the given table.



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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 27

def indexes(table_name) # :nodoc:
  scope = quoted_scope(table_name)

  result = query(<<~SQL, "SCHEMA")
    SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid),
                    pg_catalog.obj_description(i.oid, 'pg_class') AS comment, d.indisvalid,
                    ARRAY(
                      SELECT pg_get_indexdef(d.indexrelid, k + 1, true)
                      FROM generate_subscripts(d.indkey, 1) AS k
                      ORDER BY k
                    ) AS columns,
                    ARRAY(
                      SELECT a.attname
                      FROM pg_attribute a
                      WHERE a.attrelid = d.indexrelid AND a.attishidden
                    ) AS hidden_columns
    FROM pg_class t
    INNER JOIN pg_index d ON t.oid = d.indrelid
    INNER JOIN pg_class i ON d.indexrelid = i.oid
    LEFT JOIN pg_namespace n ON n.oid = t.relnamespace
    WHERE i.relkind IN ('i', 'I')
      AND d.indisprimary = 'f'
      AND t.relname = #{scope[:name]}
      AND n.nspname = #{scope[:schema]}
    ORDER BY i.relname
  SQL

  unquote = -> (column) {
    PostgreSQL::Utils.unquote_identifier(column.strip.gsub('""', '"'))
  }
  result.map do |row|
    index_name = row[0]
    unique = row[1]
    indkey = row[2].split(" ").map(&:to_i)
    inddef = row[3]
    comment = row[4]
    valid = row[5]
    columns = decode_string_array(row[6]).map(&unquote)
    hidden_columns = decode_string_array(row[7]).map(&unquote)

    using, expressions, include, nulls_not_distinct, where = inddef.scan(/ USING (\w+?) \((.+?)\)(?: INCLUDE \((.+?)\))?( NULLS NOT DISTINCT)?(?: WHERE (.+))?\z/m).flatten

    orders = {}
    opclasses = {}
    include_columns = include ? include.split(",").map(&unquote) : []

    if indkey.include?(0)
      columns = expressions
    else
      # prevent INCLUDE and hidden columns from being matched
      columns.reject! { |c| include_columns.include?(c) || hidden_columns.include?(c) }

      # add info on sort order (only desc order is explicitly specified, asc is the default)
      # and non-default opclasses
      expressions.scan(/(?<column>\w+)"?\s?(?<opclass>\w+_ops(_\w+)?)?\s?(?<desc>DESC)?\s?(?<nulls>NULLS (?:FIRST|LAST))?/).each do |column, opclass, desc, nulls|
        opclasses[column] = opclass.to_sym if opclass
        if nulls
          orders[column] = [desc, nulls].compact.join(" ")
        else
          orders[column] = :desc if desc
        end
      end
    end

    IndexDefinition.new(
      table_name,
      index_name,
      unique,
      columns,
      orders: orders,
      opclasses: opclasses,
      where: where,
      using: using.to_sym,
      include: include_columns.presence,
      nulls_not_distinct: nulls_not_distinct.present?,
      comment: comment.presence,
      valid: valid
    )
  end
end

#new_column_from_field(table_name, field, _definition) ⇒ Object

OVERRIDE(v8.1.1):

- Add hidden information


338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 338

def new_column_from_field(table_name, field, _definition)
  column_name, type, default, notnull, oid, fmod, collation, comment, identity, attgenerated, hidden = field
  type_metadata = fetch_type_metadata(column_name, type, oid.to_i, fmod.to_i)
  default_value = extract_value_from_default(default)

  if attgenerated.present?
    default_function = default
  else
    default_function = extract_default_function(default_value, default)
  end

  if match = default_function&.match(/\Anextval\('"?(?<sequence_name>.+_(?<suffix>seq\d*))"?'::regclass\)\z/)
    serial = sequence_name_from_parts(table_name, column_name, match[:suffix]) == match[:sequence_name]
  end

  CockroachDB::Column.new(
    column_name,
    get_oid_type(oid.to_i, fmod.to_i, column_name, type),
    default_value,
    type_metadata,
    !notnull,
    default_function,
    collation: collation,
    comment: comment.presence,
    serial: serial,
    identity: identity.presence,
    generated: attgenerated,
    hidden: hidden
  )
end

#pk_and_sequence_for(table) ⇒ Object

OVERRIDE(v8.1.1): Added unique_rowid to the last line of the second query. This is a CockroachDB-specific function used for primary keys. Also make sure we don't consider NOT VISIBLE columns.

Returns a table's primary key and belonging sequence.



161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 161

def pk_and_sequence_for(table) # :nodoc:
  # First try looking for a sequence with a dependency on the
  # given table's primary key.
  result = query(<<~SQL, "SCHEMA")[0]
    SELECT attr.attname, nsp.nspname, seq.relname
    FROM pg_class      seq,
         pg_attribute  attr,
         pg_depend     dep,
         pg_constraint cons,
         pg_namespace  nsp
    WHERE seq.oid           = dep.objid
      AND seq.relkind       = 'S'
      AND attr.attrelid     = dep.refobjid
      AND attr.attnum       = dep.refobjsubid
      AND attr.attrelid     = cons.conrelid
      AND attr.attnum       = cons.conkey[1]
      AND seq.relnamespace  = nsp.oid
      AND cons.contype      = 'p'
      AND dep.classid       = 'pg_class'::regclass
      AND dep.refobjid      = #{quote(quote_table_name(table))}::regclass
      AND not attr.attishidden
  SQL

  if result.nil? || result.empty?
    result = query(<<~SQL, "SCHEMA")[0]
      SELECT attr.attname, nsp.nspname,
        CASE
          WHEN pg_get_expr(def.adbin, def.adrelid) !~* 'nextval' THEN NULL
          WHEN split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2) ~ '.' THEN
            substr(split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2),
                   strpos(split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2), '.')+1)
          ELSE split_part(pg_get_expr(def.adbin, def.adrelid), '''', 2)
        END
      FROM pg_class       t
      JOIN pg_attribute   attr ON (t.oid = attrelid)
      JOIN pg_attrdef     def  ON (adrelid = attrelid AND adnum = attnum)
      JOIN pg_constraint  cons ON (conrelid = adrelid AND adnum = conkey[1])
      JOIN pg_namespace   nsp  ON (t.relnamespace = nsp.oid)
      WHERE t.oid = #{quote(quote_table_name(table))}::regclass
        AND NOT attr.attishidden
        AND cons.contype = 'p'
        AND pg_get_expr(def.adbin, def.adrelid) ~* 'nextval|uuid_generate|gen_random_uuid|unique_rowid'
    SQL
  end

  pk = result.shift
  if result.last
    [pk, PostgreSQL::Name.new(*result)]
  else
    [pk, nil]
  end
rescue
  nil
end

#primary_key(table_name) ⇒ Object

ActiveRecord allows for tables to exist without primary keys. Databases like PostgreSQL support this behavior, but CockroachDB does not. If a table is created without a primary key, CockroachDB will add a rowid column to serve as its primary key. This breaks a lot of ActiveRecord's assumptions so we'll treat tables with rowid primary keys as if they didn't have primary keys at all. https://www.cockroachlabs.com/docs/v19.2/create-table.html#create-a-table https://api.rubyonrails.org/v5.2.4/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-create_table



123
124
125
126
127
128
129
130
131
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 123

def primary_key(table_name)
  pk = super

  if pk == CockroachDBAdapter::DEFAULT_PRIMARY_KEY
    nil
  else
    pk
  end
end

#primary_keys(table_name) ⇒ Object

OVERRIDE(v8.1.1): handle hidden attributes



134
135
136
137
138
139
140
141
142
143
144
145
146
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 134

def primary_keys(table_name)
  query_values(<<~SQL, "SCHEMA")
    SELECT a.attname
    FROM pg_index i
    JOIN pg_attribute a
      ON a.attrelid = i.indrelid
      AND a.attnum = ANY(i.indkey)
      AND NOT a.attishidden
    WHERE i.indrelid = #{quote(quote_table_name(table_name))}::regclass
      AND i.indisprimary
    ORDER BY array_position(i.indkey, a.attnum)
  SQL
end

#schema_creation ⇒ Object 



409
410
411
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 409

def schema_creation
  CockroachDB::SchemaCreation.new(self)
end

#schema_names ⇒ Object

OVERRIDE: We do not want to see the crdb_internal schema in the names.

Returns an array of schema names.



111
112
113
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 111

def schema_names
  super - ["crdb_internal"]
end

#type_to_sql(type, limit: nil, precision: nil, scale: nil, array: nil) ⇒ Object

CockroachDB will use INT8 if the SQL type is INTEGER, so we make it use INT4 explicitly when needed.

For spatial columns, include the limit to properly format the column name since type alone is not enough to format the column. Ex. type_to_sql(:geography, limit: "Point,4326") => "geography(Point,4326)"



377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 377

def type_to_sql(type, limit: nil, precision: nil, scale: nil, array: nil, **) # :nodoc:
  sql = \
    case type.to_s
    when "integer"
      case limit
      when nil; "int"
      when 1, 2; "int2"
      when 3, 4; "int4"
      when 5..8; "int8"
      else super
      end
    when "geometry", "geography"
      "#{type}(#{limit})"
    else
      super
    end
  # The call to super might have appeneded [] already.
  if array && type != :primary_key && !sql.end_with?("[]")
    sql = "#{sql}[]"
  end
  sql
end

#unique_constraints(table_name) ⇒ Object

OVERRIDE(v8.1.1): handle hidden attributes

Returns an array of unique constraints for the given table. The unique constraints are represented as UniqueConstraintDefinition objects.



297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
 
# File 'lib/active_record/connection_adapters/cockroachdb/schema_statements.rb', line 297

def unique_constraints(table_name)
  scope = quoted_scope(table_name)

  unique_info = internal_exec_query(<<~SQL, "SCHEMA", allow_retry: true, materialize_transactions: false)
    SELECT c.conname, c.conrelid, c.condeferrable, c.condeferred, pg_get_constraintdef(c.oid) AS constraintdef,
    (
      SELECT array_agg(a.attname ORDER BY idx)
      FROM (
        SELECT idx, c.conkey[idx] AS conkey_elem
        FROM generate_subscripts(c.conkey, 1) AS idx
      ) indexed_conkeys
      JOIN pg_attribute a ON a.attrelid = t.oid
      AND a.attnum = indexed_conkeys.conkey_elem
      AND NOT a.attishidden
    ) AS conkey_names
    FROM pg_constraint c
    JOIN pg_class t ON c.conrelid = t.oid
    JOIN pg_namespace n ON n.oid = c.connamespace
    WHERE c.contype = 'u'
      AND t.relname = #{scope[:name]}
      AND n.nspname = #{scope[:schema]}
  SQL

  unique_info.map do |row|
    columns = decode_string_array(row["conkey_names"])

    nulls_not_distinct = row["constraintdef"].start_with?("UNIQUE NULLS NOT DISTINCT")
    deferrable = extract_constraint_deferrable(row["condeferrable"], row["condeferred"])

    options = {
      name: row["conname"],
      nulls_not_distinct: nulls_not_distinct,
      deferrable: deferrable
    }

    UniqueConstraintDefinition.new(table_name, columns, options)
  end
end