Module: ActiveRecord::ConnectionAdapters::OracleEnhancedSchemaStatements

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:



212
213
214
215
216
217
218
219
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 212

def add_column(table_name, column_name, type, options = {}) #:nodoc:
  add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
  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)
  execute(add_column_sql)
ensure
  clear_table_columns_cache(table_name)
end

#add_comment(table_name, column_name, comment) ⇒ Object

:nodoc:



266
267
268
269
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 266

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_index(table_name, column_name, options = {}) ⇒ Object

clear cached indexes when adding new index



120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 120

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

  if Hash === options # legacy support, since this param was a string
    index_type = options[:unique] ? "UNIQUE" : ""
    index_name = options[:name].to_s if options.key?(:name)
    tablespace = tablespace_for(:index, options[:tablespace])
  else
    index_type = options
  end

  if index_name.to_s.length > index_name_length
    raise ArgumentError, "Index name '#{index_name}' on table '#{table_name}' is too long; the limit is #{index_name_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(", ")

  execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{quoted_column_names})#{tablespace} #{options[:options]}"
ensure
  self.all_schema_indexes = nil
end

#add_table_comment(table_name, comment) ⇒ Object

:nodoc:



271
272
273
274
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 271

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

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

:nodoc:



237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 237

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

  change_column_sql = "ALTER TABLE #{quote_table_name(table_name)} MODIFY #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
  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])
  execute(change_column_sql)
ensure
  clear_table_columns_cache(table_name)
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:



221
222
223
224
225
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 221

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:



227
228
229
230
231
232
233
234
235
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 227

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:



285
286
287
288
289
290
291
292
293
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 285

def column_comment(table_name, column_name) #:nodoc:
  (owner, table_name, db_link) = @connection.describe(table_name)
  select_value <<-SQL
    SELECT comments FROM all_col_comments#{db_link}
    WHERE owner = '#{owner}'
      AND table_name = '#{table_name}'
      AND column_name = '#{column_name.upcase}'
  SQL
end

#create_table(name, options = {}, &block) ⇒ 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 => “100 NOCACHE INCREMENT BY 10” do |t|
  # ...
end

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 => “Employees and contractors” do |t|
  t.string      :first_name, :comment => “Given name”
  t.string      :last_name, :comment => “Surname”
end



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
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 41

def create_table(name, options = {}, &block)
  create_sequence = options[:id] != false
  column_comments = {}
  
  table_definition = TableDefinition.new(self)
  table_definition.primary_key(options[:primary_key] || Base.get_primary_key(name.to_s.singularize)) unless options[:id] == false

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

  # store column comments
  class << table_definition
    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

  result = block.call(table_definition) if block
  create_sequence = create_sequence || table_definition.create_sequence
  column_comments = table_definition.column_comments if table_definition.column_comments
  tablespace = tablespace_for(:table, options[:tablespace])

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

  create_sql = "CREATE#{' GLOBAL TEMPORARY' if options[:temporary]} TABLE "
  create_sql << quote_table_name(name)
  create_sql << " (#{table_definition.to_sql})"
  unless options[:temporary]
    create_sql << " ORGANIZATION #{options[:organization]}" if options[:organization]
    create_sql << tablespace
    table_definition.lob_columns.each{|cd| create_sql << tablespace_for(cd.sql_type.downcase.to_sym, nil, name, cd.name)}
  end
  create_sql << " #{options[:options]}"
  execute create_sql
  
  create_sequence_and_trigger(name, options) if create_sequence
  
  add_table_comment name, options[:comment]
  column_comments.each do |column_name, comment|
    add_comment name, column_name, comment
  end
  
end

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

:nodoc:



111
112
113
114
115
116
117
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 111

def drop_table(name, options = {}) #:nodoc:
  super(name)
  seq_name = options[:sequence_name] || default_sequence_name(name)
  execute "DROP SEQUENCE #{quote_table_name(seq_name)}" rescue nil
ensure
  clear_table_columns_cache(name)
end

#index_name(table_name, options) ⇒ Object

returned shortened index name if default is too large



163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 163

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.

Returns:

  • (Boolean) 


191
192
193
194
195
196
197
198
199
200
201
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 191

def index_name_exists?(table_name, index_name, default)
  (owner, table_name, db_link) = @connection.describe(table_name)
  result = select_value(<<-SQL)
    SELECT 1 FROM all_indexes#{db_link} i
    WHERE i.owner = '#{owner}'
       AND i.table_owner = '#{owner}'
       AND i.table_name = '#{table_name}'
       AND i.index_name = '#{index_name.to_s.upcase}'
  SQL
  result == 1
end

#remove_column(table_name, column_name) ⇒ Object

:nodoc:



260
261
262
263
264
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 260

def remove_column(table_name, column_name) #:nodoc:
  execute "ALTER TABLE #{quote_table_name(table_name)} DROP COLUMN #{quote_column_name(column_name)}"
ensure
  clear_table_columns_cache(table_name)
end

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

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



147
148
149
150
151
152
153
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 147

def remove_index(table_name, options = {}) #:nodoc:
  index_name = index_name(table_name, options)
  unless index_name_exists?(table_name, index_name, true)
    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



156
157
158
159
160
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 156

def remove_index!(table_name, index_name) #:nodoc:
  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:



254
255
256
257
258
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 254

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)}"
ensure
  clear_table_columns_cache(table_name)
end

#rename_index(table_name, index_name, new_index_name) ⇒ Object

:nodoc:



203
204
205
206
207
208
209
210
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 203

def rename_index(table_name, index_name, new_index_name) #:nodoc:
  unless index_name_exists?(table_name, index_name, true)
    raise ArgumentError, "Index name '#{index_name}' on table '#{table_name}' does not exist"
  end
  execute "ALTER INDEX #{quote_column_name(index_name)} rename to #{quote_column_name(new_index_name)}"
ensure
  self.all_schema_indexes = nil
end

#rename_table(name, new_name) ⇒ Object

:nodoc:



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

def rename_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
  if "#{new_name}_seq".to_s.length > sequence_name_length
    raise ArgumentError, "New sequence name '#{new_name}_seq' is too long; the limit is #{sequence_name_length} characters"
  end
  execute "RENAME #{quote_table_name(name)} TO #{quote_table_name(new_name)}"
  execute "RENAME #{quote_table_name("#{name}_seq")} TO #{quote_table_name("#{new_name}_seq")}"
end

#table_comment(table_name) ⇒ Object

:nodoc:



276
277
278
279
280
281
282
283
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 276

def table_comment(table_name) #:nodoc:
  (owner, table_name, db_link) = @connection.describe(table_name)
  select_value <<-SQL
    SELECT comments FROM all_tab_comments#{db_link}
    WHERE owner = '#{owner}'
      AND table_name = '#{table_name}'
  SQL
end

#tablespace(table_name) ⇒ Object 



303
304
305
306
307
308
309
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 303

def tablespace(table_name)
  select_value <<-SQL
    SELECT tablespace_name
    FROM user_tables
    WHERE table_name='#{table_name.to_s.upcase}'
  SQL
end

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

Maps logical Rails types to Oracle-specific data types.



296
297
298
299
300
301
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_schema_statements.rb', line 296

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