Module: ActiveRecord::ConnectionAdapters::Peasys::SchemaStatements

Included in:
ActiveRecord::ConnectionAdapters::PeasysAdapter
Defined in:
lib/active_record/connection_adapters/peasys/schema_statements.rb

Instance Method Summary collapse

Instance Method Details

#add_index(table_name, column_name, **options) ⇒ Object 



238
239
240
241
242
243
244
245
246
247
248
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 238

def add_index(table_name, column_name, **options)
  index_name = options[:name] || index_name(table_name, column_name)
  unique = options[:unique] ? "UNIQUE " : ""

  columns = Array(column_name).map do |col|
    order = options[:order].is_a?(Hash) ? options[:order][col] : nil
    "#{quote_column_name(col)}#{" DESC" if order.to_s == "desc"}"
  end.join(", ")

  execute("CREATE #{unique}INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{columns})")
end

#change_column(table_name, column_name, type, **options) ⇒ Object 



200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 200

def change_column(table_name, column_name, type, **options)
  sql_type = type_to_sql(type, **options.slice(:limit, :precision, :scale))
  execute("ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DATA TYPE #{sql_type}")

  if options.key?(:null)
    change_column_null(table_name, column_name, options[:null])
  end

  if options.key?(:default)
    change_column_default(table_name, column_name, options[:default])
  end
end

#change_column_default(table_name, column_name, default_or_changes) ⇒ Object 



213
214
215
216
217
218
219
220
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 213

def change_column_default(table_name, column_name, default_or_changes)
  default = extract_new_default_value(default_or_changes)
  if default.nil?
    execute("ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} DROP DEFAULT")
  else
    execute("ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}")
  end
end

#change_column_null(table_name, column_name, null, default = nil) ⇒ Object 



222
223
224
225
226
227
228
229
230
231
232
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 222

def change_column_null(table_name, column_name, null, default = nil)
  unless null || default.nil?
    execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)} = #{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
  end

  if null
    execute("ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} DROP NOT NULL")
  else
    execute("ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET NOT NULL")
  end
end

#create_internal_metadata_table ⇒ Object

Ensures the ar_internal_metadata table exists with DB2-compatible types.



316
317
318
319
320
321
322
323
324
325
326
327
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 316

def create_internal_metadata_table
  unless table_exists?("ar_internal_metadata")
    execute(<<~SQL)
      CREATE TABLE #{quote_table_name("ar_internal_metadata")} (
        "KEY" VARCHAR(255) NOT NULL PRIMARY KEY,
        "VALUE" VARCHAR(255),
        "CREATED_AT" TIMESTAMP NOT NULL,
        "UPDATED_AT" TIMESTAMP NOT NULL
      )
    SQL
  end
end

#create_schema_migrations_table ⇒ Object

Ensures the schema_migrations table exists with DB2-compatible types.



305
306
307
308
309
310
311
312
313
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 305

def create_schema_migrations_table
  unless table_exists?("schema_migrations")
    execute(<<~SQL)
      CREATE TABLE #{quote_table_name("schema_migrations")} (
        "VERSION" VARCHAR(255) NOT NULL PRIMARY KEY
      )
    SQL
  end
end

#create_table_definition(name, **options) ⇒ Object 



293
294
295
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 293

def create_table_definition(name, **options)
  Peasys::TableDefinition.new(self, name, **options)
end

#drop_table(table_name, **options) ⇒ Object 



196
197
198
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 196

def drop_table(table_name, **options)
  execute("DROP TABLE #{quote_table_name(table_name)}")
end

#foreign_keys(table_name) ⇒ Object 



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
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 130

def foreign_keys(table_name)
  schema = @config[:schema]&.upcase
  tbl = table_name.to_s.upcase

  sql = <<~SQL
    SELECT
      RC.CONSTRAINT_NAME AS FK_NAME,
      KC.COLUMN_NAME,
      RC.UNIQUE_CONSTRAINT_SCHEMA AS REF_SCHEMA,
      UC.TABLE_NAME AS REFERENCED_TABLE_NAME,
      RKC.COLUMN_NAME AS REFERENCED_COLUMN_NAME,
      CST.DELETE_RULE,
      CST.UPDATE_RULE
    FROM QSYS2.SYSREFCST RC
    INNER JOIN QSYS2.SYSCST CST
      ON RC.CONSTRAINT_SCHEMA = CST.CONSTRAINT_SCHEMA
      AND RC.CONSTRAINT_NAME = CST.CONSTRAINT_NAME
    INNER JOIN QSYS2.SYSKEYCST KC
      ON RC.CONSTRAINT_SCHEMA = KC.CONSTRAINT_SCHEMA
      AND RC.CONSTRAINT_NAME = KC.CONSTRAINT_NAME
    INNER JOIN QSYS2.SYSCST UC
      ON RC.UNIQUE_CONSTRAINT_SCHEMA = UC.CONSTRAINT_SCHEMA
      AND RC.UNIQUE_CONSTRAINT_NAME = UC.CONSTRAINT_NAME
    INNER JOIN QSYS2.SYSKEYCST RKC
      ON RC.UNIQUE_CONSTRAINT_SCHEMA = RKC.CONSTRAINT_SCHEMA
      AND RC.UNIQUE_CONSTRAINT_NAME = RKC.CONSTRAINT_NAME
      AND KC.ORDINAL_POSITION = RKC.ORDINAL_POSITION
    WHERE CST.TABLE_SCHEMA = '#{schema}'
      AND CST.TABLE_NAME = '#{tbl}'
    ORDER BY RC.CONSTRAINT_NAME, KC.ORDINAL_POSITION
  SQL

  result = internal_exec_query(sql, "SCHEMA")

  fk_hash = {}
  result.each do |row|
    fk_name = row["fk_name"]&.strip
    fk_hash[fk_name] ||= {
      from_columns: [],
      to_table: row["referenced_table_name"]&.strip&.downcase,
      to_columns: [],
      on_delete: extract_fk_action(row["delete_rule"]),
      on_update: extract_fk_action(row["update_rule"]),
    }
    fk_hash[fk_name][:from_columns] << row["column_name"]&.strip&.downcase
    fk_hash[fk_name][:to_columns] << row["referenced_column_name"]&.strip&.downcase
  end

  fk_hash.map do |name, data|
    options = {
      name: name,
      column: data[:from_columns].size == 1 ? data[:from_columns].first : data[:from_columns],
      primary_key: data[:to_columns].size == 1 ? data[:to_columns].first : data[:to_columns],
      on_delete: data[:on_delete],
      on_update: data[:on_update],
    }
    ForeignKeyDefinition.new(table_name.to_s, data[:to_table], options)
  end
end

#indexes(table_name) ⇒ Object 



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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 76

def indexes(table_name)
  schema = @config[:schema]&.upcase
  tbl = table_name.to_s.upcase

  sql = <<~SQL
    SELECT
      IX.INDEX_NAME,
      IX.IS_UNIQUE,
      KC.COLUMN_NAME,
      KC.ORDINAL_POSITION,
      KC.ORDERING
    FROM QSYS2.SYSINDEXES IX
    INNER JOIN QSYS2.SYSKEYS KC
      ON IX.INDEX_SCHEMA = KC.INDEX_SCHEMA
      AND IX.INDEX_NAME = KC.INDEX_NAME
    WHERE IX.TABLE_SCHEMA = '#{schema}'
      AND IX.TABLE_NAME = '#{tbl}'
      AND IX.INDEX_NAME NOT IN (
        SELECT CONSTRAINT_NAME FROM QSYS2.SYSCST
        WHERE TABLE_SCHEMA = '#{schema}'
          AND TABLE_NAME = '#{tbl}'
          AND CONSTRAINT_TYPE = 'PRIMARY KEY'
      )
    ORDER BY IX.INDEX_NAME, KC.ORDINAL_POSITION
  SQL

  result = internal_exec_query(sql, "SCHEMA")

  indexes_hash = {}
  result.each do |row|
    idx_name = row["index_name"].strip.downcase
    indexes_hash[idx_name] ||= {
      unique: row["is_unique"]&.strip == "U",
      columns: [],
      orders: {}
    }
    col = row["column_name"].strip.downcase
    indexes_hash[idx_name][:columns] << col
    if row["ordering"]&.strip == "D"
      indexes_hash[idx_name][:orders][col] = :desc
    end
  end

  indexes_hash.map do |name, data|
    IndexDefinition.new(
      table_name.to_s,
      name,
      data[:unique],
      data[:columns],
      orders: data[:orders].presence || {}
    )
  end
end

#primary_keys(table_name) ⇒ Object 



57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 57

def primary_keys(table_name)
  schema = @config[:schema]&.upcase
  tbl = table_name.to_s.upcase

  sql = <<~SQL
    SELECT KC.COLUMN_NAME
    FROM QSYS2.SYSKEYCST KC
    INNER JOIN QSYS2.SYSCST CST
      ON KC.CONSTRAINT_SCHEMA = CST.CONSTRAINT_SCHEMA
      AND KC.CONSTRAINT_NAME = CST.CONSTRAINT_NAME
    WHERE CST.TABLE_SCHEMA = '#{schema}'
      AND CST.TABLE_NAME = '#{tbl}'
      AND CST.CONSTRAINT_TYPE = 'PRIMARY KEY'
    ORDER BY KC.ORDINAL_POSITION
  SQL

  query_values(sql, "SCHEMA").map { |name| name.strip.downcase }
end

#remove_index(table_name, column_name = nil, **options) ⇒ Object 



250
251
252
253
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 250

def remove_index(table_name, column_name = nil, **options)
  index_name = options[:name] || index_name(table_name, column_name)
  execute("DROP INDEX #{quote_column_name(index_name)}")
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object 



234
235
236
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 234

def rename_column(table_name, column_name, new_column_name)
  execute("ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}")
end

#rename_table(table_name, new_name) ⇒ Object

– DDL Methods –



192
193
194
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 192

def rename_table(table_name, new_name, **)
  execute("RENAME TABLE #{quote_table_name(table_name)} TO #{quote_table_name(new_name)}")
end

#schema_creation ⇒ Object 



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

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

#schema_dumper_class ⇒ Object

Tells Rails to use our custom SchemaDumper



298
299
300
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 298

def schema_dumper_class
  Peasys::SchemaDumper
end

#table_exists?(table_name) ⇒ Boolean

Returns:

  • (Boolean) 


31
32
33
34
35
36
37
38
39
40
41
42
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 31

def table_exists?(table_name)
  schema = @config[:schema]&.upcase
  tbl = table_name.to_s.upcase
  sql = <<~SQL
    SELECT COUNT(*)
    FROM QSYS2.SYSTABLES
    WHERE TABLE_SCHEMA = '#{schema}'
      AND TABLE_NAME = '#{tbl}'
      AND TABLE_TYPE IN ('T', 'P')
  SQL
  query_value(sql, "SCHEMA").to_i > 0
end

#tables ⇒ Object 



7
8
9
10
11
12
13
14
15
16
17
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 7

def tables
  schema = @config[:schema]&.upcase
  sql = <<~SQL
    SELECT TABLE_NAME
    FROM QSYS2.SYSTABLES
    WHERE TABLE_SCHEMA = '#{schema}'
      AND TABLE_TYPE = 'T'
    ORDER BY TABLE_NAME
  SQL
  query_values(sql, "SCHEMA").map { |name| name.strip.downcase }
end

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



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
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 255

def type_to_sql(type, limit: nil, precision: nil, scale: nil, **)
  native = native_database_types[type.to_sym]
  return type.to_s unless native

  sql_type = native.is_a?(Hash) ? native[:name] : native

  case type.to_sym
  when :string, :char
    limit ||= native[:limit] if native.is_a?(Hash)
    limit ? "#{sql_type}(#{limit})" : sql_type
  when :decimal
    if precision
      scale ? "#{sql_type}(#{precision},#{scale})" : "#{sql_type}(#{precision})"
    else
      sql_type
    end
  when :integer
    if limit
      case limit
      when 1, 2 then "SMALLINT"
      when 3, 4 then "INTEGER"
      when 5..8 then "BIGINT"
      else sql_type
      end
    else
      sql_type
    end
  when :text, :binary, :float, :date, :time, :datetime, :timestamp, :boolean, :bigint
    sql_type
  else
    sql_type
  end
end

#view_exists?(view_name) ⇒ Boolean

Returns:

  • (Boolean) 


44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 44

def view_exists?(view_name)
  schema = @config[:schema]&.upcase
  vw = view_name.to_s.upcase
  sql = <<~SQL
    SELECT COUNT(*)
    FROM QSYS2.SYSTABLES
    WHERE TABLE_SCHEMA = '#{schema}'
      AND TABLE_NAME = '#{vw}'
      AND TABLE_TYPE = 'V'
  SQL
  query_value(sql, "SCHEMA").to_i > 0
end

#views ⇒ Object 



19
20
21
22
23
24
25
26
27
28
29
 
# File 'lib/active_record/connection_adapters/peasys/schema_statements.rb', line 19

def views
  schema = @config[:schema]&.upcase
  sql = <<~SQL
    SELECT TABLE_NAME
    FROM QSYS2.SYSTABLES
    WHERE TABLE_SCHEMA = '#{schema}'
      AND TABLE_TYPE = 'V'
    ORDER BY TABLE_NAME
  SQL
  query_values(sql, "SCHEMA").map { |name| name.strip.downcase }
end