Class: ActiveRecordSpannerAdapter::InformationSchema

Inherits:
Object
  • Object
show all
Includes:
ActiveRecord::ConnectionAdapters::Quoting
Defined in:
lib/activerecord_spanner_adapter/information_schema.rb

Constant Summary collapse

IsRails71OrLater = 
ActiveRecord.gem_version >= Gem::Version.create("7.1.0")

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(connection) ⇒ InformationSchema

Returns a new instance of InformationSchema.



22
23
24
25
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 22

def initialize connection
  @connection = connection
  @mutex = Mutex.new
end

Instance Attribute Details

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



20
21
22
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 20

def connection
  @connection
end

Instance Method Details

#_create_column(table_name, row, primary_keys, column_options, schema_name: "") ⇒ Object 



88
89
90
91
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
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 88

def _create_column table_name, row, primary_keys, column_options, schema_name: ""
  type, limit = parse_type_and_limit row["SPANNER_TYPE"]
  column_name = row["COLUMN_NAME"]
  options = column_options[column_name]
  primary_key = primary_keys.include? column_name

  default = row["COLUMN_DEFAULT"]
  default_function = row["GENERATION_EXPRESSION"]

  if default && default.length < 200 && /\w+\(.*\)/.match?(default)
    default_function ||= default
    default = nil
  end

  if default && type == "STRING"
    default = unquote_string default
  end

  Table::Column.new \
    table_name,
    column_name,
    type,
    schema_name: schema_name,
    limit: limit,
    allow_commit_timestamp: options["allow_commit_timestamp"],
    ordinal_position: row["ORDINAL_POSITION"],
    nullable: row["IS_NULLABLE"] == "YES",
    default: default,
    default_function: default_function,
    generated: row["GENERATION_EXPRESSION"].present?,
    primary_key: primary_key,
    is_identity: row["IS_IDENTITY"] == "YES"
end

#check_constraints(table_name, schema_name: "") ⇒ Object 



310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 310

def check_constraints table_name, schema_name: ""
  sql = "    SELECT tc.TABLE_NAME,\n           tc.CONSTRAINT_NAME,\n           cc.CHECK_CLAUSE\n    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc\n    INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc\n        ON tc.CONSTRAINT_CATALOG = cc.CONSTRAINT_CATALOG\n       AND tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA\n       AND tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME\n    WHERE tc.TABLE_NAME = %<table_name>s\n      AND tc.CONSTRAINT_SCHEMA = %<schema_name>s\n      AND tc.CONSTRAINT_TYPE = 'CHECK'\n      AND NOT (tc.CONSTRAINT_NAME LIKE 'CK_IS_NOT_NULL_%%' AND cc.CHECK_CLAUSE LIKE '%%IS NOT NULL')\n  SQL\n\n  rows = execute_query sql, table_name: table_name, schema_name: schema_name\n\n  rows.map do |row|\n    ActiveRecord::ConnectionAdapters::CheckConstraintDefinition.new(\n      table_name, row[\"CHECK_CLAUSE\"], name: row[\"CONSTRAINT_NAME\"]\n    )\n  end\nend\n".squish

#foreign_keys(from_table_name, from_schema_name: "") ⇒ Object 



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
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 253

def foreign_keys from_table_name, from_schema_name: ""
  sql = "    SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE,\n           FK_CATALOG, FK_SCHEMA, FK_TABLE,\n           PK_CATALOG, PK_SCHEMA, PK_TABLE,\n           ARRAY_AGG(FK_COLUMN) AS FK_COLUMNS, ARRAY_AGG(PK_COLUMN) AS PK_COLUMNS\n    FROM (SELECT CONSTRAINTS.CONSTRAINT_CATALOG,\n                 CONSTRAINTS.CONSTRAINT_SCHEMA,\n                 CONSTRAINTS.CONSTRAINT_NAME,\n                 CONSTRAINTS.UPDATE_RULE,\n                 CONSTRAINTS.DELETE_RULE,\n                 CHILD.TABLE_CATALOG  AS FK_CATALOG,\n                 CHILD.TABLE_SCHEMA   AS FK_SCHEMA,\n                 CHILD.TABLE_NAME     AS FK_TABLE,\n                 CHILD.COLUMN_NAME    AS FK_COLUMN,\n                 PARENT.TABLE_CATALOG AS PK_CATALOG,\n                 PARENT.TABLE_SCHEMA  AS PK_SCHEMA,\n                 PARENT.TABLE_NAME    AS PK_TABLE,\n                 PARENT.COLUMN_NAME   AS PK_COLUMN\n          FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS CONSTRAINTS\n                   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CHILD\n                              ON CONSTRAINTS.CONSTRAINT_CATALOG = CHILD.CONSTRAINT_CATALOG\n                                  AND CONSTRAINTS.CONSTRAINT_SCHEMA = CHILD.CONSTRAINT_SCHEMA\n                                  AND CONSTRAINTS.CONSTRAINT_NAME = CHILD.CONSTRAINT_NAME\n                   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PARENT\n                              ON CONSTRAINTS.UNIQUE_CONSTRAINT_CATALOG = PARENT.CONSTRAINT_CATALOG\n                                  AND CONSTRAINTS.UNIQUE_CONSTRAINT_SCHEMA = PARENT.CONSTRAINT_SCHEMA\n                                  AND CONSTRAINTS.UNIQUE_CONSTRAINT_NAME = PARENT.CONSTRAINT_NAME\n                                  AND PARENT.ORDINAL_POSITION = CHILD.POSITION_IN_UNIQUE_CONSTRAINT\n          ORDER BY CHILD.TABLE_CATALOG, CHILD.TABLE_SCHEMA, CHILD.TABLE_NAME, CHILD.POSITION_IN_UNIQUE_CONSTRAINT\n    ) FOREIGN_KEYS\n    WHERE FK_TABLE = %<table_name>s\n      AND FK_SCHEMA = %<constraint_schema>s\n    GROUP BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE,\n             FK_CATALOG, FK_SCHEMA, FK_TABLE,\n             PK_CATALOG, PK_SCHEMA, PK_TABLE\n  SQL\n\n  rows = execute_query(\n    sql, table_name: from_table_name, constraint_schema: from_schema_name\n  )\n\n  rows.map do |row|\n    ForeignKey.new(\n      from_table_name,\n      row[\"CONSTRAINT_NAME\"],\n      row[\"FK_COLUMNS\"],\n      row[\"PK_TABLE\"],\n      row[\"PK_COLUMNS\"],\n      on_delete: row[\"DELETE_RULE\"],\n      on_update: row[\"UPDATE_RULE\"],\n      table_schema: from_schema_name,\n      ref_schema: row[\"PK_SCHEMA\"]\n    )\n  end\nend\n"

#index(table_name, index_name, schema_name: "") ⇒ Object 



218
219
220
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 218

def index table_name, index_name, schema_name: ""
  indexes(table_name, index_name: index_name, schema_name: schema_name).first
end

#index_columns(table_name, schema_name: "", index_name: nil) ⇒ Object 



222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 222

def index_columns table_name, schema_name: "", index_name: nil
  sql = +"SELECT INDEX_NAME, COLUMN_NAME, COLUMN_ORDERING, ORDINAL_POSITION"
  sql << " FROM INFORMATION_SCHEMA.INDEX_COLUMNS"
  sql << " WHERE TABLE_NAME=%<table_name>s"
  sql << " AND TABLE_CATALOG = ''"
  sql << " AND TABLE_SCHEMA = %<schema_name>s"
  sql << " AND INDEX_NAME=%<index_name>s" if index_name
  sql << " ORDER BY ORDINAL_POSITION ASC"

  execute_query(
    sql,
    table_name: table_name, schema_name: schema_name, index_name: index_name
  ).map do |row|
    Index::Column.new \
      table_name,
      row["INDEX_NAME"],
      row["COLUMN_NAME"],
      schema_name: schema_name,
      order: row["COLUMN_ORDERING"],
      ordinal_position: row["ORDINAL_POSITION"]
  end
end

#indexes(table_name, schema_name: "", index_name: nil, index_type: nil) ⇒ Object 



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
215
216
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 170

def indexes table_name, schema_name: "", index_name: nil, index_type: nil
  table_indexes_columns = index_columns(
    table_name,
    schema_name: schema_name,
    index_name: index_name
  )

  sql = +"SELECT INDEX_NAME, INDEX_TYPE, IS_UNIQUE, IS_NULL_FILTERED, PARENT_TABLE_NAME, INDEX_STATE"
  sql << " FROM INFORMATION_SCHEMA.INDEXES"
  sql << " WHERE TABLE_NAME=%<table_name>s"
  sql << " AND TABLE_CATALOG = ''"
  sql << " AND TABLE_SCHEMA = %<schema_name>s"
  sql << " AND INDEX_NAME=%<index_name>s" if index_name
  sql << " AND INDEX_TYPE=%<index_type>s" if index_type
  sql << " AND SPANNER_IS_MANAGED=FALSE"

  execute_query(
    sql,
    table_name: table_name,
    schema_name: schema_name,
    index_name: index_name,
    index_type: index_type
  ).map do |row|
    columns = []
    storing = []
    table_indexes_columns.each do |c|
      next unless c.index_name == row["INDEX_NAME"]
      if c.ordinal_position
        columns << c
      else
        storing << c.name
      end
    end

    Index.new \
      table_name,
      row["INDEX_NAME"],
      columns,
      type: row["INDEX_TYPE"],
      unique: row["IS_UNIQUE"],
      null_filtered: row["IS_NULL_FILTERED"],
      interleave_in: row["PARENT_TABLE_NAME"],
      storing: storing,
      state: row["INDEX_STATE"],
      schema: schema_name
  end
end

#indexes_by_columns(table_name, column_names, schema_name: "") ⇒ Object 



245
246
247
248
249
250
251
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 245

def indexes_by_columns table_name, column_names, schema_name: ""
  column_names = Array(column_names).map(&:to_s)

  indexes(table_name, schema_name: schema_name).select do |index|
    index.columns.any? { |c| column_names.include? c.name }
  end
end

#parse_type_and_limit(value) ⇒ Object 



335
336
337
338
339
340
341
342
343
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 335

def parse_type_and_limit value
  matched = /^([A-Z]*)\((.*)\)/.match value
  return [value] unless matched

  limit = matched[2]
  limit = limit.to_i unless limit == "MAX"

  [matched[1], limit]
end

#table(table_name, schema_name: "", view: nil) ⇒ Object 



58
59
60
61
62
63
64
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 58

def table table_name, schema_name: "", view: nil
  tables(
    table_name: table_name,
    schema_name: schema_name,
    view: view
  ).first
end

#table_column(table_name, column_name, schema_name: "") ⇒ Object 



122
123
124
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 122

def table_column table_name, column_name, schema_name: ""
  table_columns(table_name, column_name: column_name, schema_name: schema_name).first
end

#table_columns(table_name, column_name: nil, schema_name: "") ⇒ Object 



66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 66

def table_columns table_name, column_name: nil, schema_name: ""
  primary_keys = table_primary_keys(table_name).map(&:name)
  sql = +"SELECT COLUMN_NAME, SPANNER_TYPE, IS_NULLABLE, GENERATION_EXPRESSION,"
  sql << " CAST(COLUMN_DEFAULT AS STRING) AS COLUMN_DEFAULT, ORDINAL_POSITION,"
  sql << " IS_IDENTITY"
  sql << " FROM INFORMATION_SCHEMA.COLUMNS"
  sql << " WHERE TABLE_NAME=%<table_name>s"
  sql << " AND TABLE_SCHEMA=%<schema_name>s"
  sql << " AND COLUMN_NAME=%<column_name>s" if column_name
  sql << " ORDER BY ORDINAL_POSITION ASC"

  column_options = column_options table_name, column_name, schema_name: schema_name
  execute_query(
    sql,
    table_name: table_name,
    column_name: column_name,
    schema_name: schema_name
  ).map do |row|
    _create_column table_name, row, primary_keys, column_options, schema_name: schema_name
  end
end

#table_primary_keys(table_name, include_parent_keys = IsRails71OrLater, schema_name: "") ⇒ Object

Returns the primary key columns of the given table. By default it will only return the columns that are not part of the primary key of the parent table (if any). These are the columns that are considered the primary key by ActiveRecord. The parent primary key columns are filtered out by default to allow interleaved tables to be considered as tables with a single-column primary key by ActiveRecord. The actual primary key of the table will include both the parent primary key columns and the 'own' primary key columns of a table.



131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 131

def table_primary_keys table_name, include_parent_keys = IsRails71OrLater, schema_name: ""
  sql = +"WITH TABLE_PK_COLS AS ( "
  sql << "SELECT C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME, "
  sql << "C.INDEX_NAME, C.COLUMN_ORDERING, C.ORDINAL_POSITION "
  sql << "FROM INFORMATION_SCHEMA.INDEX_COLUMNS C "
  sql << "WHERE C.INDEX_TYPE = 'PRIMARY_KEY' "
  sql << "AND TABLE_CATALOG = '' "
  sql << "AND TABLE_SCHEMA = '') "
  sql << "SELECT INDEX_NAME, COLUMN_NAME, COLUMN_ORDERING, ORDINAL_POSITION "
  sql << "FROM TABLE_PK_COLS "
  sql << "INNER JOIN INFORMATION_SCHEMA.TABLES T USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME) "
  sql << "WHERE TABLE_NAME = %<table_name>s "
  sql << "AND TABLE_CATALOG = '' "
  sql << "AND TABLE_SCHEMA = %<schema_name>s "
  unless include_parent_keys
    sql << "AND (T.PARENT_TABLE_NAME IS NULL OR COLUMN_NAME NOT IN ( "
    sql << "  SELECT COLUMN_NAME "
    sql << "  FROM TABLE_PK_COLS "
    sql << "  WHERE TABLE_CATALOG = T.TABLE_CATALOG "
    sql << "  AND TABLE_SCHEMA=T.TABLE_SCHEMA "
    sql << "  AND TABLE_NAME = T.PARENT_TABLE_NAME "
    sql << ")) "
  end
  sql << "ORDER BY ORDINAL_POSITION"
  execute_query(
    sql,
    table_name: table_name,
    schema_name: schema_name
  ).map do |row|
    Index::Column.new \
      table_name,
      row["INDEX_NAME"],
      row["COLUMN_NAME"],
      schema_name: schema_name,
      order: row["COLUMN_ORDERING"],
      ordinal_position: row["ORDINAL_POSITION"]
  end
end

#tables(table_name: nil, schema_name: "", view: nil) ⇒ Object 



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
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 27

def tables table_name: nil, schema_name: "", view: nil
  sql = +"SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, PARENT_TABLE_NAME, ON_DELETE_ACTION"
  sql << " FROM INFORMATION_SCHEMA.TABLES"
  sql << " WHERE TABLE_SCHEMA=%<schema_name>s"
  sql << " AND TABLE_NAME=%<table_name>s" if table_name

  rows = execute_query(
    sql,
    schema_name: schema_name || "", table_name: table_name
  )

  rows.map do |row|
    table = Table.new(
      row["TABLE_NAME"],
      parent_table: row["PARENT_TABLE_NAME"],
      on_delete: row["ON_DELETE_ACTION"],
      schema_name: row["TABLE_SCHEMA"],
      catalog: row["TABLE_CATALOG"]
    )

    if [:full, :columns].include? view
      table.columns = table_columns table.name, schema_name: schema_name
    end

    if [:full, :indexes].include? view
      table.indexes = indexes table.name, schema_name: table.schema_name
    end
    table
  end
end

#unquote_string(value) ⇒ Object 



345
346
347
348
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 345

def unquote_string value
  return unquote_raw_string value, 1 if value[0] == "r" || value[0] == "R"
  unescape_string unquote_raw_string value
end