Module: ArJdbc::Oracle

Defined in:
lib/arjdbc/oracle/adapter.rb

Defined Under Namespace

Modules: Column

Constant Summary collapse

ADAPTER_NAME = 
'Oracle'
NATIVE_DATABASE_TYPES = 
{
  :primary_key => "NUMBER(38) NOT NULL PRIMARY KEY",
  :string => { :name => "VARCHAR2", :limit => 255 },
  :text => { :name => "CLOB" },
  :integer => { :name => "NUMBER", :limit => 38 },
  :float => { :name => "NUMBER" },
  :decimal => { :name => "DECIMAL" },
  :datetime => { :name => "DATE" },
  :timestamp => { :name => "TIMESTAMP" },
  :time => { :name => "DATE" },
  :date => { :name => "DATE" },
  :binary => { :name => "BLOB" },
  :boolean => { :name => "NUMBER", :limit => 1 },
  :raw => { :name => "RAW", :limit => 2000 },
}
IDENTIFIER_LENGTH =

:nodoc:

30
@@_lob_callback_added = 
nil

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.arel2_visitors(config) ⇒ Object 



139
140
141
 
# File 'lib/arjdbc/oracle/adapter.rb', line 139

def self.arel2_visitors(config)
  { 'oracle' => Arel::Visitors::Oracle }
end

.column_selector ⇒ Object 



37
38
39
 
# File 'lib/arjdbc/oracle/adapter.rb', line 37

def self.column_selector
  [ /oracle/i, lambda { |cfg, column| column.extend(::ArJdbc::Oracle::Column) } ]
end

.extended(mod) ⇒ Object 



8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
# File 'lib/arjdbc/oracle/adapter.rb', line 8

def self.extended(mod)
  unless @@_lob_callback_added
    ActiveRecord::Base.class_eval do
      def after_save_with_oracle_lob
        self.class.columns.select { |c| c.sql_type =~ /LOB\(|LOB$/i }.each do |column|
          value = ::ArJdbc::SerializedAttributesHelper.dump_column_value(self, column)
          next if value.nil? || (value == '')

          connection.write_large_object(
            column.type == :binary, column.name, 
            self.class.table_name, self.class.primary_key, 
            quote_value(id), value
          )
        end
      end
    end

    ActiveRecord::Base.after_save :after_save_with_oracle_lob
    
    @@_lob_callback_added = true
  end

  unless ActiveRecord::ConnectionAdapters::AbstractAdapter.
      instance_methods(false).detect { |m| m.to_s == "prefetch_primary_key?" }
    require 'arjdbc/jdbc/quoted_primary_key'
    ActiveRecord::Base.extend ArJdbc::QuotedPrimaryKeyExtension
  end
end

.jdbc_connection_class ⇒ Object 



41
42
43
 
# File 'lib/arjdbc/oracle/adapter.rb', line 41

def self.jdbc_connection_class
  ::ActiveRecord::ConnectionAdapters::OracleJdbcConnection
end

Instance Method Details

#adapter_name ⇒ Object 



145
146
147
 
# File 'lib/arjdbc/oracle/adapter.rb', line 145

def adapter_name
  ADAPTER_NAME
end

#add_column_options!(sql, options) ⇒ Object

:nodoc:



330
331
332
333
334
335
336
 
# File 'lib/arjdbc/oracle/adapter.rb', line 330

def add_column_options!(sql, options) #:nodoc:
  # handle case  of defaults for CLOB columns, which would otherwise get "quoted" incorrectly
  if options_include_default?(options) && (column = options[:column]) && column.type == :text
    sql << " DEFAULT #{quote(options.delete(:default))}"
  end
  super
end

#add_limit_offset!(sql, options) ⇒ Object

:nodoc:



283
284
285
286
287
288
289
290
291
 
# File 'lib/arjdbc/oracle/adapter.rb', line 283

def add_limit_offset!(sql, options) #:nodoc:
  offset = options[:offset] || 0

  if limit = options[:limit]
    sql.replace "select * from (select raw_sql_.*, rownum raw_rnum_ from (#{sql}) raw_sql_ where rownum <= #{offset+limit}) where raw_rnum_ > #{offset}"
  elsif offset > 0
    sql.replace "select * from (select raw_sql_.*, rownum raw_rnum_ from (#{sql}) raw_sql_) where raw_rnum_ > #{offset}"
  end
end

#add_order_by_for_association_limiting!(sql, options) ⇒ Object

ORDER BY clause for the passed order option.

Uses column aliases as defined by #distinct.



423
424
425
426
427
428
429
430
431
432
 
# File 'lib/arjdbc/oracle/adapter.rb', line 423

def add_order_by_for_association_limiting!(sql, options)
  return sql if options[:order].blank?

  order_columns = extract_order_columns(options[:order]) do |columns|
    columns.map! { |s| $1 if s =~ / (.*)/ }; columns
  end
  order = order_columns.map { |s, i| "alias_#{i}__ #{s}" } # @see {#distinct}

  sql << "ORDER BY #{order.join(', ')}"
end

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

:nodoc:



338
339
340
341
342
343
 
# File 'lib/arjdbc/oracle/adapter.rb', line 338

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  change_column_sql = "ALTER TABLE #{quote_table_name(table_name)} " <<
    "MODIFY #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit])}"
  add_column_options!(change_column_sql, options)
  execute(change_column_sql)
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:



325
326
327
328
 
# File 'lib/arjdbc/oracle/adapter.rb', line 325

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)}"
end

#column_name_length ⇒ Object

:nodoc:



193
 
# File 'lib/arjdbc/oracle/adapter.rb', line 193

def column_name_length; IDENTIFIER_LENGTH; end

#columns(table_name, name = nil) ⇒ Object

NOTE: better to use current_schema instead of the configured one ?!



448
449
450
 
# File 'lib/arjdbc/oracle/adapter.rb', line 448

def columns(table_name, name = nil) # :nodoc:
  @connection.columns_internal(table_name.to_s, name, oracle_schema)
end

#create_savepoint ⇒ Object

:nodoc:



309
310
311
 
# File 'lib/arjdbc/oracle/adapter.rb', line 309

def create_savepoint # :nodoc:
  execute("SAVEPOINT #{current_savepoint_name}")
end

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

:nodoc:

Raises:

  • (ActiveRecord::StatementInvalid) 


199
200
201
202
203
204
205
 
# File 'lib/arjdbc/oracle/adapter.rb', line 199

def create_table(name, options = {}) #:nodoc:
  super(name, options)
  seq_name = options[:sequence_name] || default_sequence_name(name)
  start_value = options[:sequence_start_value] || 10000
  raise ActiveRecord::StatementInvalid.new("name #{seq_name} too long") if seq_name.length > table_alias_length
  execute "CREATE SEQUENCE #{seq_name} START WITH #{start_value}" unless options[:id] == false
end

#current_database ⇒ Object

:nodoc:



297
298
299
 
# File 'lib/arjdbc/oracle/adapter.rb', line 297

def current_database # :nodoc:
  @current_database ||= execute("SELECT sys_context('userenv', 'db_name') db FROM dual").first['db']
end

#current_schema ⇒ Object

:nodoc:



301
302
303
 
# File 'lib/arjdbc/oracle/adapter.rb', line 301

def current_schema # :nodoc:
  execute("SELECT sys_context('userenv', 'current_schema') schema FROM dual").first['schema']
end

#current_schema=(schema_owner) ⇒ Object 



305
306
307
 
# File 'lib/arjdbc/oracle/adapter.rb', line 305

def current_schema=(schema_owner)
  execute("ALTER SESSION SET current_schema=#{schema_owner}")
end

#current_user ⇒ Object

:nodoc:



293
294
295
 
# File 'lib/arjdbc/oracle/adapter.rb', line 293

def current_user # :nodoc:
  @current_user ||= execute("SELECT sys_context('userenv', 'session_user') su FROM dual").first['su']
end

#default_sequence_name(table_name, column = nil) ⇒ Object

:nodoc:



195
196
197
 
# File 'lib/arjdbc/oracle/adapter.rb', line 195

def default_sequence_name(table_name, column = nil) # :nodoc:
  "#{table_name.to_s[0, IDENTIFIER_LENGTH - 4]}_seq"
end

#distinct(columns, order_by) ⇒ Object

SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.

Oracle requires the ORDER BY columns to be in the SELECT list for DISTINCT queries. However, with those columns included in the SELECT DISTINCT list, you won’t actually get a distinct list of the column you want (presuming the column has duplicates with multiple values for the ordered-by columns. So we use the FIRST_VALUE function to get a single (first) value for each column, effectively making every row the same.

distinct("posts.id", "posts.created_at desc")



408
409
410
411
412
413
414
415
416
417
418
 
# File 'lib/arjdbc/oracle/adapter.rb', line 408

def distinct(columns, order_by)
  return "DISTINCT #{columns}" if order_by.blank?

  # construct a valid DISTINCT clause, ie. one that includes the ORDER BY columns, using
  # FIRST_VALUE such that the inclusion of these columns doesn't invalidate the DISTINCT
  order_columns = extract_order_columns(order_by).map do |c, i|
    "FIRST_VALUE(#{c.split.first}) OVER (PARTITION BY #{columns} ORDER BY #{c}) AS alias_#{i}__"
  end
  sql = "DISTINCT #{columns}, "
  sql << order_columns * ", "
end

#drop_database(name) ⇒ Object 



222
223
224
 
# File 'lib/arjdbc/oracle/adapter.rb', line 222

def drop_database(name)
  recreate_database(name)
end

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

:nodoc:



212
213
214
215
216
 
# File 'lib/arjdbc/oracle/adapter.rb', line 212

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

#explain(arel, binds = []) ⇒ Object 



510
511
512
513
514
515
 
# File 'lib/arjdbc/oracle/adapter.rb', line 510

def explain(arel, binds = [])
  sql = "EXPLAIN PLAN FOR #{to_sql(arel)}"
  return if sql =~ /FROM all_/
  exec_query(sql, 'EXPLAIN', binds)
  select_values("SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)", 'EXPLAIN').join("\n")
end

#in_clause_length ⇒ Object Also known as: ids_in_list_limit

Prevent ORA-01795 for in clauses with more than 1000



182
183
184
 
# File 'lib/arjdbc/oracle/adapter.rb', line 182

def in_clause_length # :nodoc:
  1000
end

#index_name_length ⇒ Object

:nodoc:



192
 
# File 'lib/arjdbc/oracle/adapter.rb', line 192

def index_name_length;  IDENTIFIER_LENGTH; end

#indexes(table, name = nil) ⇒ Object 



279
280
281
 
# File 'lib/arjdbc/oracle/adapter.rb', line 279

def indexes(table, name = nil)
  @connection.indexes(table, name, @connection.connection.meta_data.user_name)
end

#insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = []) ⇒ Object

:nodoc:



259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
 
# File 'lib/arjdbc/oracle/adapter.rb', line 259

def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = []) # :nodoc:
  if (id_value && ! sql_literal?(id_value)) || pk.nil?
    # Pre-assigned id or table without a primary key
    # Presence of #to_sql means an Arel literal bind variable
    # that should use #execute_id_insert below
    value = exec_insert(to_sql(sql, binds), name, binds)
    id_value || last_inserted_id(value) # super
  else
    # Assume the sql contains a bind-variable for the id
    # Extract the table from the insert sql. Yuck.
    sequence_name ||= begin
      table = extract_table_ref_from_insert_sql(sql)
      default_sequence_name(table)
    end
    id_value = next_sequence_value(sequence_name)
    log(sql, name) { @connection.execute_id_insert(sql, id_value) }
    id_value
  end
end

#jdbc_column_class ⇒ Object 



45
46
47
 
# File 'lib/arjdbc/oracle/adapter.rb', line 45

def jdbc_column_class
  ::ActiveRecord::ConnectionAdapters::OracleColumn
end

#modify_types(types) ⇒ Object 



169
170
171
172
173
174
175
 
# File 'lib/arjdbc/oracle/adapter.rb', line 169

def modify_types(types)
  super(types)
  NATIVE_DATABASE_TYPES.each do |key, value|
    types[key] = value.dup
  end
  types
end

#native_database_types ⇒ Object 



165
166
167
 
# File 'lib/arjdbc/oracle/adapter.rb', line 165

def native_database_types
  super.merge(NATIVE_DATABASE_TYPES)
end

#next_sequence_value(sequence_name) ⇒ Object 



250
251
252
253
 
# File 'lib/arjdbc/oracle/adapter.rb', line 250

def next_sequence_value(sequence_name)
  # avoid #select or #select_one so that the sequence values aren't cached
  execute("SELECT #{quote_table_name(sequence_name)}.nextval id FROM dual").first['id'].to_i
end

#prefetch_primary_key?(table_name = nil) ⇒ Boolean

Returns:

  • (Boolean) 


177
178
179
 
# File 'lib/arjdbc/oracle/adapter.rb', line 177

def prefetch_primary_key?(table_name = nil)
  columns(table_name).detect {|c| c.primary } if table_name
end

#quote(value, column = nil) ⇒ Object

:nodoc:



470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
 
# File 'lib/arjdbc/oracle/adapter.rb', line 470

def quote(value, column = nil) # :nodoc:
  # Arel 2 passes SqlLiterals through
  return value if sql_literal?(value)

  if column && [:text, :binary].include?(column.type)
    if /(.*?)\([0-9]+\)/ =~ column.sql_type
      %Q{empty_#{ $1.downcase }()}
    else
      %Q{empty_#{ column.sql_type.downcase rescue 'blob' }()}
    end
  else
    if column.respond_to?(:primary) && column.primary && column.klass != String
      return value.to_i.to_s
    end
    quoted = super
    if value.acts_like?(:date)
      quoted = %Q{DATE'#{quoted_date(value)}'}
    elsif value.acts_like?(:time)
      quoted = %Q{TIMESTAMP'#{quoted_date(value)}'}
    end
    quoted
  end
end

#quote_column_name(name) ⇒ Object

Camelcase column names need to be quoted. Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and “at” signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers. Source: download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements008.htm



466
467
468
 
# File 'lib/arjdbc/oracle/adapter.rb', line 466

def quote_column_name(name) #:nodoc:
  name.to_s =~ /^[a-z0-9_$#]+$/ ? name.to_s : "\"#{name}\""
end

#quote_table_name(name) ⇒ Object

See ACTIVERECORD_JDBC-33 for details – better to not quote table names, esp. if they have schemas.



456
457
458
 
# File 'lib/arjdbc/oracle/adapter.rb', line 456

def quote_table_name(name) # :nodoc:
  name.to_s
end

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



218
219
220
 
# File 'lib/arjdbc/oracle/adapter.rb', line 218

def recreate_database(name, options = {})
  tables.each{ |table| drop_table(table) }
end

#release_savepoint ⇒ Object

:nodoc:



317
318
319
 
# File 'lib/arjdbc/oracle/adapter.rb', line 317

def release_savepoint # :nodoc:
  # no RELEASE SAVEPOINT statement in Oracle
end

#remove_column(table_name, *column_names) ⇒ Object

:nodoc:



350
351
352
353
354
 
# File 'lib/arjdbc/oracle/adapter.rb', line 350

def remove_column(table_name, *column_names) #:nodoc:
  for column_name in column_names.flatten
    execute "ALTER TABLE #{quote_table_name(table_name)} DROP COLUMN #{quote_column_name(column_name)}"
  end
end

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

:nodoc:



321
322
323
 
# File 'lib/arjdbc/oracle/adapter.rb', line 321

def remove_index(table_name, options = {}) #:nodoc:
  execute "DROP INDEX #{index_name(table_name, options)}"
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

:nodoc:



345
346
347
348
 
# File 'lib/arjdbc/oracle/adapter.rb', line 345

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)}"
end

#rename_table(name, new_name) ⇒ Object

:nodoc:



207
208
209
210
 
# File 'lib/arjdbc/oracle/adapter.rb', line 207

def rename_table(name, new_name) #:nodoc:
  execute "RENAME #{name} TO #{new_name}"
  execute "RENAME #{name}_seq TO #{new_name}_seq" rescue nil
end

#rollback_to_savepoint ⇒ Object

:nodoc:



313
314
315
 
# File 'lib/arjdbc/oracle/adapter.rb', line 313

def rollback_to_savepoint # :nodoc:
  execute("ROLLBACK TO #{current_savepoint_name}")
end

#select(sql, name = nil, binds = []) ⇒ Object 



517
518
519
520
521
522
523
 
# File 'lib/arjdbc/oracle/adapter.rb', line 517

def select(sql, name = nil, binds = [])
  records = execute(sql, name, binds)
  for column in records
    column.delete('raw_rnum_')
  end
  records
end

#sql_literal?(value) ⇒ Boolean

Returns:

  • (Boolean) 


255
256
257
 
# File 'lib/arjdbc/oracle/adapter.rb', line 255

def sql_literal?(value)
  defined?(::Arel::SqlLiteral) && ::Arel::SqlLiteral === value
end

#structure_drop ⇒ Object

:nodoc:



388
389
390
391
392
393
394
395
396
 
# File 'lib/arjdbc/oracle/adapter.rb', line 388

def structure_drop #:nodoc:
  s = select_all("select sequence_name from user_sequences").inject("") do |drop, seq|
    drop << "drop sequence #{seq.to_a.first.last};\n\n"
  end

  select_all("select table_name from user_tables").inject(s) do |drop, table|
    drop << "drop table #{table.to_a.first.last} cascade constraints;\n\n"
  end
end

#structure_dump ⇒ Object

:nodoc:



356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
 
# File 'lib/arjdbc/oracle/adapter.rb', line 356

def structure_dump #:nodoc:
  s = select_all("select sequence_name from user_sequences").inject("") do |structure, seq|
    structure << "create sequence #{seq.to_a.first.last};\n\n"
  end

  select_all("select table_name from user_tables").inject(s) do |structure, table|
    ddl = "create table #{table.to_a.first.last} (\n "
    cols = select_all(%Q{
      select column_name, data_type, data_length, data_precision, data_scale, data_default, nullable
      from user_tab_columns
      where table_name = '#{table.to_a.first.last}'
      order by column_id
    }).map do |row|
      row = row.inject({}) { |h, args| h[ args[0].downcase ] = args[1]; h }
      col = "#{row['column_name'].downcase} #{row['data_type'].downcase}"
      if row['data_type'] == 'NUMBER' and ! row['data_precision'].nil?
        col << "(#{row['data_precision'].to_i}"
        col << ",#{row['data_scale'].to_i}" if ! row['data_scale'].nil?
        col << ')'
      elsif row['data_type'].include?('CHAR')
        col << "(#{row['data_length'].to_i})"
      end
      col << " default #{row['data_default']}" if !row['data_default'].nil?
      col << ' not null' if row['nullable'] == 'N'
      col
    end
    ddl << cols.join(",\n ")
    ddl << ");\n\n"
    structure << ddl
  end
end

#supports_explain? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


506
507
508
 
# File 'lib/arjdbc/oracle/adapter.rb', line 506

def supports_explain? # :nodoc:
  true
end

#supports_migrations? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


494
495
496
 
# File 'lib/arjdbc/oracle/adapter.rb', line 494

def supports_migrations? # :nodoc:
  true
end

#supports_primary_key? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


498
499
500
 
# File 'lib/arjdbc/oracle/adapter.rb', line 498

def supports_primary_key? # :nodoc:
  true
end

#supports_savepoints? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


502
503
504
 
# File 'lib/arjdbc/oracle/adapter.rb', line 502

def supports_savepoints? # :nodoc:
  true
end

#table_alias_length ⇒ Object

maximum length of Oracle identifiers is 30



190
 
# File 'lib/arjdbc/oracle/adapter.rb', line 190

def table_alias_length; IDENTIFIER_LENGTH; end

#table_name_length ⇒ Object

:nodoc:



191
 
# File 'lib/arjdbc/oracle/adapter.rb', line 191

def table_name_length;  IDENTIFIER_LENGTH; end

#tables ⇒ Object 



442
443
444
 
# File 'lib/arjdbc/oracle/adapter.rb', line 442

def tables
  @connection.tables(nil, oracle_schema)
end

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

:nodoc:



226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
 
# File 'lib/arjdbc/oracle/adapter.rb', line 226

def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc:
  case type.to_sym
  when :binary
    # { BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]
    # although Oracle does not like limit (length) with BLOB (or CLOB) :
    # 
    # CREATE TABLE binaries (data BLOB, short_data BLOB(1024));
    # ORA-00907: missing right parenthesis             *
    #
    # TODO do we need to worry about NORMAL vs. non IN-TABLE BLOBs ?!
    # http://dba.stackexchange.com/questions/8770/improve-blob-writing-performance-in-oracle-11g
    # - if the LOB is smaller than 3900 bytes it can be stored inside the 
    #   table row; by default this is enabled, 
    #   unless you specify DISABLE STORAGE IN ROW
    # - normal LOB - stored in a separate segment, outside of table, 
    #   you may even put it in another tablespace;
    super(type, nil, nil, nil)
  when :text
    super(type, nil, nil, nil)
  else
    super
  end
end