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 



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

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
end

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



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

def check_constraints table_name, schema_name: ""
  sql = <<~SQL.squish
    SELECT tc.TABLE_NAME,
           tc.CONSTRAINT_NAME,
           cc.CHECK_CLAUSE
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
        ON tc.CONSTRAINT_CATALOG = cc.CONSTRAINT_CATALOG
       AND tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA
       AND tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
    WHERE tc.TABLE_NAME = %<table_name>s
      AND tc.CONSTRAINT_SCHEMA = %<schema_name>s
      AND tc.CONSTRAINT_TYPE = 'CHECK'
      AND NOT (tc.CONSTRAINT_NAME LIKE 'CK_IS_NOT_NULL_%%' AND cc.CHECK_CLAUSE LIKE '%%IS NOT NULL')
  SQL

  rows = execute_query sql, table_name: table_name, schema_name: schema_name

  rows.map do |row|
    ActiveRecord::ConnectionAdapters::CheckConstraintDefinition.new(
      table_name, row["CHECK_CLAUSE"], name: row["CONSTRAINT_NAME"]
    )
  end
end

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



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

def foreign_keys from_table_name, from_schema_name: ""
  sql = <<~SQL
    SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE,
           FK_CATALOG, FK_SCHEMA, FK_TABLE,
           PK_CATALOG, PK_SCHEMA, PK_TABLE,
           ARRAY_AGG(FK_COLUMN) AS FK_COLUMNS, ARRAY_AGG(PK_COLUMN) AS PK_COLUMNS
    FROM (SELECT CONSTRAINTS.CONSTRAINT_CATALOG,
                 CONSTRAINTS.CONSTRAINT_SCHEMA,
                 CONSTRAINTS.CONSTRAINT_NAME,
                 CONSTRAINTS.UPDATE_RULE,
                 CONSTRAINTS.DELETE_RULE,
                 CHILD.TABLE_CATALOG  AS FK_CATALOG,
                 CHILD.TABLE_SCHEMA   AS FK_SCHEMA,
                 CHILD.TABLE_NAME     AS FK_TABLE,
                 CHILD.COLUMN_NAME    AS FK_COLUMN,
                 PARENT.TABLE_CATALOG AS PK_CATALOG,
                 PARENT.TABLE_SCHEMA  AS PK_SCHEMA,
                 PARENT.TABLE_NAME    AS PK_TABLE,
                 PARENT.COLUMN_NAME   AS PK_COLUMN
          FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS CONSTRAINTS
                   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CHILD
                              ON CONSTRAINTS.CONSTRAINT_CATALOG = CHILD.CONSTRAINT_CATALOG
                                  AND CONSTRAINTS.CONSTRAINT_SCHEMA = CHILD.CONSTRAINT_SCHEMA
                                  AND CONSTRAINTS.CONSTRAINT_NAME = CHILD.CONSTRAINT_NAME
                   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PARENT
                              ON CONSTRAINTS.UNIQUE_CONSTRAINT_CATALOG = PARENT.CONSTRAINT_CATALOG
                                  AND CONSTRAINTS.UNIQUE_CONSTRAINT_SCHEMA = PARENT.CONSTRAINT_SCHEMA
                                  AND CONSTRAINTS.UNIQUE_CONSTRAINT_NAME = PARENT.CONSTRAINT_NAME
                                  AND PARENT.ORDINAL_POSITION = CHILD.POSITION_IN_UNIQUE_CONSTRAINT
          ORDER BY CHILD.TABLE_CATALOG, CHILD.TABLE_SCHEMA, CHILD.TABLE_NAME, CHILD.POSITION_IN_UNIQUE_CONSTRAINT
    ) FOREIGN_KEYS
    WHERE FK_TABLE = %<table_name>s
      AND FK_SCHEMA = %<constraint_schema>s
    GROUP BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE,
             FK_CATALOG, FK_SCHEMA, FK_TABLE,
             PK_CATALOG, PK_SCHEMA, PK_TABLE
  SQL

  rows = execute_query(
    sql, table_name: from_table_name, constraint_schema: from_schema_name
  )

  rows.map do |row|
    ForeignKey.new(
      from_table_name,
      row["CONSTRAINT_NAME"],
      row["FK_COLUMNS"],
      row["PK_TABLE"],
      row["PK_COLUMNS"],
      on_delete: row["DELETE_RULE"],
      on_update: row["UPDATE_RULE"],
      table_schema: from_schema_name,
      ref_schema: row["PK_SCHEMA"]
    )
  end
end

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



216
217
218
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 216

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 



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

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 



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/activerecord_spanner_adapter/information_schema.rb', line 168

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 



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

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 



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

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 



120
121
122
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 120

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
 
# 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 << " 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.



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
154
155
156
157
158
159
160
161
162
163
164
165
166
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 129

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 



343
344
345
346
 
# File 'lib/activerecord_spanner_adapter/information_schema.rb', line 343

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