Module: ActiveRecord::ConnectionAdapters::OracleEnhanced::SchemaStatements

Included in:
ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter
Defined in:
lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb

Instance Method Summary collapse

Instance Method Details

#add_column(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:


277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 277

def add_column(table_name, column_name, type, options = {}) #:nodoc:
  if type.to_sym == :virtual
    type = options[:type]
  end
  type = aliased_types(type.to_s, type)
  add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD #{quote_column_name(column_name)} "
  add_column_sql << type_to_sql(type, options[:limit], options[:precision], options[:scale]) if type

  add_column_options!(add_column_sql, options.merge(:type=>type, :column_name=>column_name, :table_name=>table_name))

  add_column_sql << tablespace_for((type_to_sql(type).downcase.to_sym), nil, table_name, column_name) if type

  execute(add_column_sql)

  create_sequence_and_trigger(table_name, options) if type && type.to_sym == :primary_key
ensure
  clear_table_columns_cache(table_name)
end

#add_comment(table_name, column_name, comment) ⇒ Object

:nodoc:


354
355
356
357
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 354

def add_comment(table_name, column_name, comment) #:nodoc:
  return if comment.blank?
  execute "COMMENT ON COLUMN #{quote_table_name(table_name)}.#{column_name} IS '#{comment}'"
end

#add_foreign_key(from_table, to_table, options = {}) ⇒ Object 


399
400
401
402
403
404
405
406
407
408
409
410
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 399

def add_foreign_key(from_table, to_table, options = {})
  if options[:dependent]
    ActiveSupport::Deprecation.warn "`:dependent` option will be deprecated. Please use `:on_delete` option"
  end
  case options[:dependent]  
  when :delete then options[:on_delete] = :cascade
  when :nullify then options[:on_delete] = :nullify
  else
  end

  super
end

#add_index(table_name, column_name, options = {}) ⇒ Object

:nodoc:


161
162
163
164
165
166
167
168
169
170
171
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 161

def add_index(table_name, column_name, options = {}) #:nodoc:
  index_name, index_type, quoted_column_names, tablespace, index_options = add_index_options(table_name, column_name, options)
  execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{quoted_column_names})#{tablespace} #{index_options}"
  if index_type == 'UNIQUE'
    unless quoted_column_names =~ /\(.*\)/
      execute "ALTER TABLE #{quote_table_name(table_name)} ADD CONSTRAINT #{quote_column_name(index_name)} #{index_type} (#{quoted_column_names})"
    end
  end
ensure
  self.all_schema_indexes = nil
end

#add_index_options(table_name, column_name, options = {}) ⇒ Object

:nodoc:


173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 173

def add_index_options(table_name, column_name, options = {}) #:nodoc:
  column_names = Array(column_name)
  index_name   = index_name(table_name, column: column_names)

  options.assert_valid_keys(:unique, :order, :name, :where, :length, :internal, :tablespace, :options, :using)

  index_type = options[:unique] ? "UNIQUE" : ""
  index_name = options[:name].to_s if options.key?(:name)
  tablespace = tablespace_for(:index, options[:tablespace])
  max_index_length = options.fetch(:internal, false) ? index_name_length : allowed_index_name_length
  #TODO: This option is used for NOLOGGING, needs better argumetn name
  index_options = options[:options]

  if index_name.to_s.length > max_index_length
    raise ArgumentError, "Index name '#{index_name}' on table '#{table_name}' is too long; the limit is #{max_index_length} characters"
  end
  if index_name_exists?(table_name, index_name, false)
    raise ArgumentError, "Index name '#{index_name}' on table '#{table_name}' already exists"
  end

  quoted_column_names = column_names.map { |e| quote_column_name_or_expression(e) }.join(", ")
   [index_name, index_type, quoted_column_names, tablespace, index_options]
end

#add_table_comment(table_name, comment) ⇒ Object

:nodoc:


359
360
361
362
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 359

def add_table_comment(table_name, comment) #:nodoc:
  return if comment.blank?
  execute "COMMENT ON TABLE #{quote_table_name(table_name)} IS '#{comment}'"
end

#aliased_types(name, fallback) ⇒ Object 


296
297
298
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 296

def aliased_types(name, fallback)
  fallback
end

#change_column(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:


316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 316

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  column = column_for(table_name, column_name)

  # remove :null option if its value is the same as current column definition
  # otherwise Oracle will raise error
  if options.has_key?(:null) && options[:null] == column.null
    options[:null] = nil
  end
  if type.to_sym == :virtual
    type = options[:type]
  end
  change_column_sql = "ALTER TABLE #{quote_table_name(table_name)} MODIFY #{quote_column_name(column_name)} "
  change_column_sql << "#{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" if type

  add_column_options!(change_column_sql, options.merge(:type=>type, :column_name=>column_name, :table_name=>table_name))

  change_column_sql << tablespace_for((type_to_sql(type).downcase.to_sym), nil, options[:table_name], options[:column_name]) if type

  execute(change_column_sql)
ensure
  clear_table_columns_cache(table_name)
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:


300
301
302
303
304
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 300

def change_column_default(table_name, column_name, default) #:nodoc:
  execute "ALTER TABLE #{quote_table_name(table_name)} MODIFY #{quote_column_name(column_name)} DEFAULT #{quote(default)}"
ensure
  clear_table_columns_cache(table_name)
end

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

:nodoc:


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

def change_column_null(table_name, column_name, null, default = nil) #:nodoc:
  column = column_for(table_name, column_name)

  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

  change_column table_name, column_name, column.sql_type, :null => null
end

#column_comment(table_name, column_name) ⇒ Object

:nodoc:


373
374
375
376
377
378
379
380
381
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 373

def column_comment(table_name, column_name) #:nodoc:
  (owner, table_name, db_link) = @connection.describe(table_name)
  select_value "SELECT comments FROM all_col_comments\#{db_link}\nWHERE owner = '\#{owner}'\nAND table_name = '\#{table_name}'\nAND column_name = '\#{column_name.upcase}'\n"
end

#create_table(table_name, options = {}) {|td| ... } ⇒ Object

Additional options for create_table method in migration files.

You can specify individual starting value in table creation migration file, e.g.:

create_table :users, :sequence_start_value => 100 do |t|
  # ...
end

You can also specify other sequence definition additional parameters, e.g.:

create_table :users, :sequence_start_value =>

Create primary key trigger (so that you can skip primary key value in INSERT statement). By default trigger name will be “table_name_pkt”, you can override the name with :trigger_name option (but it is not recommended to override it as then this trigger will not be detected by ActiveRecord model and it will still do prefetching of sequence value). Example:

create_table :users, :primary_key_trigger => true do |t|
  # ...
end

It is possible to add table and column comments in table creation migration files:

create_table :employees, :comment =>

Yields:

  • (td) 

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

def create_table(table_name, options = {})
  create_sequence = options[:id] != false
  column_comments = {}
  temporary = options.delete(:temporary)
  additional_options = options
  td = create_table_definition table_name, temporary, additional_options
  td.primary_key(options[:primary_key] || Base.get_primary_key(table_name.to_s.singularize)) unless options[:id] == false

  # store that primary key was defined in create_table block
  unless create_sequence
    class << td
      attr_accessor :create_sequence
      def primary_key(*args)
        self.create_sequence = true
        super(*args)
      end
    end
  end

  # store column comments
  class << td
    attr_accessor :column_comments
    def column(name, type, options = {})
      if options[:comment]
        self.column_comments ||= {}
        self.column_comments[name] = options[:comment]
      end
      super(name, type, options)
    end
  end

  yield td if block_given?
  create_sequence = create_sequence || td.create_sequence
  column_comments = td.column_comments if td.column_comments

  if options[:force] && table_exists?(table_name)
    drop_table(table_name, options)
  end

  execute schema_creation.accept td

  create_sequence_and_trigger(table_name, options) if create_sequence

  add_table_comment table_name, options[:comment]
  column_comments.each do |column_name, comment|
    add_comment table_name, column_name, comment
  end
  td.indexes.each_pair { |c,o| add_index table_name, c, o }

  td.foreign_keys.each_pair do |other_table_name, foreign_key_options|
    add_foreign_key(table_name, other_table_name, foreign_key_options)
  end
end

#create_table_definition(name, temporary, options) ⇒ Object 


97
98
99
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 97

def create_table_definition(name, temporary, options)
  ActiveRecord::ConnectionAdapters::OracleEnhanced::TableDefinition.new native_database_types, name, temporary, options
end

#disable_referential_integrity(&block) ⇒ Object

REFERENTIAL INTEGRITY ====================================


461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 461

def disable_referential_integrity(&block) #:nodoc:
  sql_constraints = "SELECT constraint_name, owner, table_name\nFROM user_constraints\nWHERE constraint_type = 'R'\nAND status = 'ENABLED'\n"
  old_constraints = select_all(sql_constraints)
  begin
    old_constraints.each do |constraint|
      execute "ALTER TABLE #{constraint["table_name"]} DISABLE CONSTRAINT #{constraint["constraint_name"]}"
    end
    yield
  ensure
    old_constraints.each do |constraint|
      execute "ALTER TABLE #{constraint["table_name"]} ENABLE CONSTRAINT #{constraint["constraint_name"]}"
    end
  end
end

#drop_table(table_name, options = {}) ⇒ Object

:nodoc:


111
112
113
114
115
116
117
118
119
120
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 111

def drop_table(table_name, options = {}) #:nodoc:
  execute "DROP TABLE #{quote_table_name(table_name)}#{' CASCADE CONSTRAINTS' if options[:force] == :cascade}"
  seq_name = options[:sequence_name] || default_sequence_name(table_name)
  execute "DROP SEQUENCE #{quote_table_name(seq_name)}" rescue nil
rescue ActiveRecord::StatementInvalid => e
  raise e unless options[:if_exists]
ensure
  clear_table_columns_cache(table_name)
  self.all_schema_indexes = nil
end

#dump_schema_information ⇒ Object

:nodoc:


122
123
124
125
126
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 122

def dump_schema_information #:nodoc:
  sm_table = ActiveRecord::Migrator.schema_migrations_table_name
  migrated = select_values("SELECT version FROM #{sm_table} ORDER BY version")
  join_with_statement_token(migrated.map{|v| "INSERT INTO #{sm_table} (version) VALUES ('#{v}')" })
end

#extract_foreign_key_action(specifier) ⇒ Object

:nodoc:


452
453
454
455
456
457
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 452

def extract_foreign_key_action(specifier) # :nodoc:
  case specifier
  when 'CASCADE'; :cascade
  when 'SET NULL'; :nullify
  end
end

#foreign_keys(table_name) ⇒ Object

get table foreign keys for schema dump


417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 417

def foreign_keys(table_name) #:nodoc:
  (owner, desc_table_name, db_link) = @connection.describe(table_name)

  fk_info = select_all("SELECT r.table_name to_table\n,rc.column_name references_column\n,cc.column_name\n,c.constraint_name name\n,c.delete_rule\nFROM user_constraints\#{db_link} c, user_cons_columns\#{db_link} cc,\nuser_constraints\#{db_link} r, user_cons_columns\#{db_link} rc\nWHERE c.owner = '\#{owner}'\nAND c.table_name = '\#{desc_table_name}'\nAND c.constraint_type = 'R'\nAND cc.owner = c.owner\nAND cc.constraint_name = c.constraint_name\nAND r.constraint_name = c.r_constraint_name\nAND r.owner = c.owner\nAND rc.owner = r.owner\nAND rc.constraint_name = r.constraint_name\nAND rc.position = cc.position\nORDER BY name, to_table, column_name, references_column\n", 'Foreign Keys')

  fk_info.map do |row|
    options = {
      column: oracle_downcase(row['column_name']),
      name: oracle_downcase(row['name']),
      primary_key: oracle_downcase(row['references_column'])
    }
    options[:on_delete] = extract_foreign_key_action(row['delete_rule'])
    OracleEnhanced::ForeignKeyDefinition.new(oracle_downcase(table_name), oracle_downcase(row['to_table']), options)
  end
end

#index_name(table_name, options) ⇒ Object

returned shortened index name if default is too large


225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 225

def index_name(table_name, options) #:nodoc:
  default_name = super(table_name, options).to_s
  # sometimes options can be String or Array with column names
  options = {} unless options.is_a?(Hash)
  identifier_max_length = options[:identifier_max_length] || index_name_length
  return default_name if default_name.length <= identifier_max_length

  # remove 'index', 'on' and 'and' keywords
  shortened_name = "i_#{table_name}_#{Array(options[:column]) * '_'}"

  # leave just first three letters from each word
  if shortened_name.length > identifier_max_length
    shortened_name = shortened_name.split('_').map{|w| w[0,3]}.join('_')
  end
  # generate unique name using hash function
  if shortened_name.length > identifier_max_length
    shortened_name = 'i'+Digest::SHA1.hexdigest(default_name)[0,identifier_max_length-1]
  end
  @logger.warn "#{adapter_name} shortened default index name #{default_name} to #{shortened_name}" if @logger
  shortened_name
end

#index_name_exists?(table_name, index_name, default) ⇒ Boolean

Verify the existence of an index with a given name.

The default argument is returned if the underlying implementation does not define the indexes method, as there's no way to determine the correct answer in that case.

Will always query database and not index cache.


253
254
255
256
257
258
259
260
261
262
263
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 253

def index_name_exists?(table_name, index_name, default)
  (owner, table_name, db_link) = @connection.describe(table_name)
  result = select_value("SELECT 1 FROM all_indexes\#{db_link} i\nWHERE i.owner = '\#{owner}'\nAND i.table_owner = '\#{owner}'\nAND i.table_name = '\#{table_name}'\nAND i.index_name = '\#{index_name.to_s.upcase}'\n")
  result == 1
end

#initialize_schema_migrations_table ⇒ Object 


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
154
155
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 128

def initialize_schema_migrations_table
  sm_table = ActiveRecord::Migrator.schema_migrations_table_name

  unless table_exists?(sm_table)
    index_name = "#{Base.table_name_prefix}unique_schema_migrations#{Base.table_name_suffix}"
    if index_name.length > index_name_length
      truncate_to    = index_name_length - index_name.to_s.length - 1
      truncated_name = "unique_schema_migrations"[0..truncate_to]
      index_name     = "#{Base.table_name_prefix}#{truncated_name}#{Base.table_name_suffix}"
    end

    create_table(sm_table, :id => false) do |schema_migrations_table|
      schema_migrations_table.column :version, :string, :null => false
    end
    add_index sm_table, :version, :unique => true, :name => index_name

    # Backwards-compatibility: if we find schema_info, assume we've
    # migrated up to that point:
    si_table = Base.table_name_prefix + 'schema_info' + Base.table_name_suffix
    if table_exists?(si_table)
      ActiveSupport::Deprecation.warn "Usage of the schema table `#{si_table}` is deprecated. Please switch to using `schema_migrations` table"

      old_version = select_value("SELECT version FROM #{quote_table_name(si_table)}").to_i
      assume_migrated_upto_version(old_version)
      drop_table(si_table)
    end
  end
end

#remove_column(table_name, column_name, type = nil, options = {}) ⇒ Object

:nodoc:


347
348
349
350
351
352
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 347

def remove_column(table_name, column_name, type = nil, options = {}) #:nodoc:
  execute "ALTER TABLE #{quote_table_name(table_name)} DROP COLUMN #{quote_column_name(column_name)} CASCADE CONSTRAINTS"
ensure
  clear_table_columns_cache(table_name)
  self.all_schema_indexes = nil
end

#remove_foreign_key(from_table, options_or_to_table = {}) ⇒ Object 


412
413
414
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 412

def remove_foreign_key(from_table, options_or_to_table = {})
  super
end

#remove_index(table_name, options = {}) ⇒ Object

Remove the given index from the table. Gives warning if index does not exist


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

def remove_index(table_name, options = {}) #:nodoc:
  index_name = index_name(table_name, options)
  unless index_name_exists?(table_name, index_name, true)
    # sometimes options can be String or Array with column names
    options = {} unless options.is_a?(Hash)
    if options.has_key? :name
      options_without_column = options.dup
      options_without_column.delete :column
      index_name_without_column = index_name(table_name, options_without_column)
      return index_name_without_column if index_name_exists?(table_name, index_name_without_column, false)
    end
    raise ArgumentError, "Index name '#{index_name}' on table '#{table_name}' does not exist"
  end
  remove_index!(table_name, index_name)
end

#remove_index!(table_name, index_name) ⇒ Object

clear cached indexes when removing index


216
217
218
219
220
221
222
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 216

def remove_index!(table_name, index_name) #:nodoc:
  #TODO: It should execute only when index_type == "UNIQUE"
  execute "ALTER TABLE #{quote_table_name(table_name)} DROP CONSTRAINT #{quote_column_name(index_name)}" rescue nil
  execute "DROP INDEX #{quote_column_name(index_name)}"
ensure
  self.all_schema_indexes = nil
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

:nodoc:


339
340
341
342
343
344
345
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 339

def rename_column(table_name, column_name, new_column_name) #:nodoc:
  execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} to #{quote_column_name(new_column_name)}"
  self.all_schema_indexes = nil
  rename_column_indexes(table_name, column_name, new_column_name)
ensure
  clear_table_columns_cache(table_name)
end

#rename_index(table_name, old_name, new_name) ⇒ Object

:nodoc:


265
266
267
268
269
270
271
272
273
274
275
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 265

def rename_index(table_name, old_name, new_name) #:nodoc:
  unless index_name_exists?(table_name, old_name, true)
    raise ArgumentError, "Index name '#{old_name}' on table '#{table_name}' does not exist"
  end
  if new_name.length > allowed_index_name_length
    raise ArgumentError, "Index name '#{new_name}' on table '#{table_name}' is too long; the limit is #{allowed_index_name_length} characters"
  end
  execute "ALTER INDEX #{quote_column_name(old_name)} rename to #{quote_column_name(new_name)}"
ensure
  self.all_schema_indexes = nil
end

#rename_table(table_name, new_name) ⇒ Object

:nodoc:


101
102
103
104
105
106
107
108
109
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 101

def rename_table(table_name, new_name) #:nodoc:
  if new_name.to_s.length > table_name_length
    raise ArgumentError, "New table name '#{new_name}' is too long; the limit is #{table_name_length} characters"
  end
  execute "RENAME #{quote_table_name(table_name)} TO #{quote_table_name(new_name)}"
  execute "RENAME #{quote_table_name("#{table_name}_seq")} TO #{default_sequence_name(new_name)}" rescue nil

  rename_table_indexes(table_name, new_name)
end

#table_comment(table_name) ⇒ Object

:nodoc:


364
365
366
367
368
369
370
371
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 364

def table_comment(table_name) #:nodoc:
  (owner, table_name, db_link) = @connection.describe(table_name)
  select_value "SELECT comments FROM all_tab_comments\#{db_link}\nWHERE owner = '\#{owner}'\nAND table_name = '\#{table_name}'\n"
end

#tablespace(table_name) ⇒ Object 


391
392
393
394
395
396
397
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 391

def tablespace(table_name)
  select_value "SELECT tablespace_name\nFROM user_tables\nWHERE table_name='\#{table_name.to_s.upcase}'\n"
end

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

Maps logical Rails types to Oracle-specific data types.


384
385
386
387
388
389
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 384

def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc:
  # Ignore options for :text and :binary columns
  return super(type, nil, nil, nil) if ['text', 'binary'].include?(type.to_s)

  super
end

#update_table_definition(table_name, base) ⇒ Object

:nodoc:


157
158
159
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/schema_statements.rb', line 157

def update_table_definition(table_name, base) #:nodoc:
  OracleEnhanced::Table.new(table_name, base)
end