Class: ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter

Inherits:
AbstractAdapter
  • Object
show all
Defined in:
lib/active_record/connection_adapters/oracle_enhanced_adapter.rb

Overview

This is an Oracle/OCI adapter for the ActiveRecord persistence framework. It relies upon the OCI8 driver, which works with Oracle 8i and above. Most recent development has been on Debian Linux against a 10g database, ActiveRecord 1.12.1 and OCI8 0.1.13. See: rubyforge.org/projects/ruby-oci8/

Usage notes:

  • Key generation assumes a “$table_name_seq” sequence is available for all tables; the sequence name can be changed using ActiveRecord::Base.set_sequence_name. When using Migrations, these sequences are created automatically.

  • Oracle uses DATE or TIMESTAMP datatypes for both dates and times. Consequently some hacks are employed to map data back to Date or Time in Ruby. If the column_name ends in _time it’s created as a Ruby Time. Else if the hours/minutes/seconds are 0, I make it a Ruby Date. Else it’s a Ruby Time. This is a bit nasty - but if you use Duck Typing you’ll probably not care very much. In 9i and up it’s tempting to map DATE to Date and TIMESTAMP to Time, but too many databases use DATE for both. Timezones and sub-second precision on timestamps are not supported.

  • Default values that are functions (such as “SYSDATE”) are not supported. This is a restriction of the way ActiveRecord supports default values.

  • Support for Oracle8 is limited by Rails’ use of ANSI join syntax, which is supported in Oracle9i and later. You will need to use #finder_sql for has_and_belongs_to_many associations to run against Oracle8.

Required parameters:

  • :username

  • :password

  • :database

Direct Known Subclasses

OracleAdapter

Constant Summary collapse

@@emulate_booleans = 
true
@@emulate_dates = 
false
@@emulate_dates_by_column_name =

RSI: set to true if columns with DATE in their name should be emulated as date

false
@@emulate_integers_by_column_name =

RSI: set to true if NUMBER columns with ID at the end of their name should be emulated as integers

false
@@emulate_booleans_from_strings =

RSI: set to true if CHAR(1), VARCHAR2(1) columns or VARCHAR2 columns with FLAG or YN at the end of their name should be emulated as booleans

false
@@string_to_date_format =

RSI: use to set NLS specific date formats which will be used when assigning string to :date and :datetime columns

@@string_to_time_format = nil
@@default_sequence_start_value =

RSI: default sequence start with value

10000

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Instance Attribute Details

#auto_retry ⇒ Object

Returns the value of attribute auto_retry.



421
422
423
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 421

def auto_retry
  @auto_retry
end

Class Method Details

.boolean_to_string(bool) ⇒ Object 



287
288
289
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 287

def self.boolean_to_string(bool)
  bool ? "Y" : "N"
end

.is_boolean_column?(name, field_type, table_name = nil) ⇒ Boolean

Returns:

  • (Boolean) 


283
284
285
286
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 283

def self.is_boolean_column?(name, field_type, table_name = nil)
  return true if ["CHAR(1)","VARCHAR2(1)"].include?(field_type)
  field_type =~ /^VARCHAR2/ && (name =~ /_flag$/i || name =~ /_yn$/i)
end

.is_date_column?(name, table_name = nil) ⇒ Boolean

Returns:

  • (Boolean) 


257
258
259
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 257

def self.is_date_column?(name, table_name = nil)
  name =~ /(^|_)date(_|$)/i
end

.is_integer_column?(name, table_name = nil) ⇒ Boolean

Returns:

  • (Boolean) 


275
276
277
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 275

def self.is_integer_column?(name, table_name = nil)
  name =~ /(^|_)id$/i
end

.valid_table_name?(name) ⇒ Boolean

unescaped table name should start with letter and contain letters, digits, _, $ or # can be prefixed with schema name CamelCase table names should be quoted

Returns:

  • (Boolean) 


350
351
352
353
354
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 350

def self.valid_table_name?(name)
  name = name.to_s
  name =~ /^([A-Za-z_0-9]+\.)?[a-z][a-z_0-9\$#]*$/ ||
  name =~ /^([A-Za-z_0-9]+\.)?[A-Z][A-Z_0-9\$#]*$/ ? true : false
end

Instance Method Details

#active? ⇒ Boolean

Returns true if the connection is active.

Returns:

  • (Boolean) 


432
433
434
435
436
437
438
439
440
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 432

def active?
  # Pings the connection to check if it's still good. Note that an
  # #active? method is also available, but that simply returns the
  # last known state, which isn't good enough if the connection has
  # gone stale since the last use.
  @connection.ping
rescue OracleEnhancedConnectionException
  false
end

#adapter_name ⇒ Object

:nodoc:



295
296
297
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 295

def adapter_name #:nodoc:
  'OracleEnhanced'
end

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



761
762
763
764
765
766
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 761

def add_column(table_name, column_name, type, options = {})
  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])}"
  options[:type] = type
  add_column_options!(add_column_sql, options)
  execute(add_column_sql)
end

#add_column_options!(sql, options) ⇒ Object

:nodoc:



899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 899

def add_column_options!(sql, options) #:nodoc:
  type = options[:type] || ((column = options[:column]) && column.type)
  type = type && type.to_sym
  # handle case of defaults for CLOB columns, which would otherwise get "quoted" incorrectly
  if options_include_default?(options)
    if type == :text
      sql << " DEFAULT #{quote(options[:default])}"
    else
      # from abstract adapter
      sql << " DEFAULT #{quote(options[:default], options[:column])}"
    end
  end
  # must explicitly add NULL or NOT NULL to allow change_column to work on migrations
  if options[:null] == false
    sql << " NOT NULL"
  elsif options[:null] == true
    sql << " NULL" unless type == :primary_key
  end
end

#add_comment(table_name, column_name, comment) ⇒ Object

RSI: table and column comments



806
807
808
809
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 806

def add_comment(table_name, column_name, comment)
  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



750
751
752
753
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 750

def add_index(table_name, column_name, options = {})
  self.all_schema_indexes = nil
  super
end

#add_limit_offset!(sql, options) ⇒ Object

:nodoc:



486
487
488
489
490
491
492
493
494
495
496
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 486

def add_limit_offset!(sql, options) #:nodoc:
  # RSI: added to_i for limit and offset to protect from SQL injection
  offset = (options[:offset] || 0).to_i

  if limit = options[:limit]
    limit = limit.to_i
    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.



945
946
947
948
949
950
951
952
953
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 945

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

  order = options[:order].split(',').collect { |s| s.strip }.reject(&:blank?)
  order.map! {|s| $1 if s =~ / (.*)/}
  order = order.zip((0...order.size).to_a).map { |s,i| "alias_#{i}__ #{s}" }.join(', ')

  sql << " ORDER BY #{order}"
end

#add_table_comment(table_name, comment) ⇒ Object 



811
812
813
814
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 811

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

#begin_db_transaction ⇒ Object

:nodoc:



470
471
472
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 470

def begin_db_transaction #:nodoc:
  @connection.autocommit = false
end

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

:nodoc:



782
783
784
785
786
787
788
789
790
791
792
793
794
795
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 782

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])}"
  options[:type] = type
  add_column_options!(change_column_sql, options)
  execute(change_column_sql)
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:



768
769
770
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 768

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

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



772
773
774
775
776
777
778
779
780
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 772

def change_column_null(table_name, column_name, null, default = nil)
  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

#clear_types_for_columns ⇒ Object 



640
641
642
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 640

def clear_types_for_columns
  @table_column_type = nil
end

#column_comment(table_name, column_name) ⇒ Object 



825
826
827
828
829
830
831
832
833
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 825

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

#columns(table_name, name = nil) ⇒ Object

:nodoc:



644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 644

def columns(table_name, name = nil) #:nodoc:
  # RSI: get ignored_columns by original table name
  ignored_columns = ignored_table_columns(table_name)

  (owner, desc_table_name) = @connection.describe(table_name)

  table_cols = <<-SQL
    select column_name as name, data_type as sql_type, data_default, nullable,
           decode(data_type, 'NUMBER', data_precision,
                             'FLOAT', data_precision,
                             'VARCHAR2', data_length,
                             'CHAR', data_length,
                              null) as limit,
           decode(data_type, 'NUMBER', data_scale, null) as scale
      from all_tab_columns
     where owner      = '#{owner}'
       and table_name = '#{desc_table_name}'
     order by column_id
  SQL

  # RSI: added deletion of ignored columns
  select_all(table_cols, name).delete_if do |row|
    ignored_columns && ignored_columns.include?(row['name'].downcase)
  end.map do |row|
    limit, scale = row['limit'], row['scale']
    if limit || scale
      row['sql_type'] << "(#{(limit || 38).to_i}" + ((scale = scale.to_i) > 0 ? ",#{scale})" : ")")
    end

    # clean up odd default spacing from Oracle
    if row['data_default']
      row['data_default'].sub!(/^(.*?)\s*$/, '\1')
      row['data_default'].sub!(/^'(.*)'$/, '\1')
      row['data_default'] = nil if row['data_default'] =~ /^(null|empty_[bc]lob\(\))$/i
    end

    OracleEnhancedColumn.new(oracle_downcase(row['name']),
                     row['data_default'],
                     row['sql_type'],
                     row['nullable'] == 'Y',
                     # RSI: pass table name for table specific column definitions
                     table_name,
                     # RSI: pass column type if specified in class definition
                     get_type_for_column(table_name, oracle_downcase(row['name'])))
  end
end

#commit_db_transaction ⇒ Object

:nodoc:



474
475
476
477
478
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 474

def commit_db_transaction #:nodoc:
  @connection.commit
ensure
  @connection.autocommit = true
end

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

:nodoc:



695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 695

def create_table(name, options = {}, &block) #:nodoc:
  create_sequence = options[:id] != false
  column_comments = {}
  super(name, options) do |t|
    # store that primary key was defined in create_table block
    unless create_sequence
      class <<t
        attr_accessor :create_sequence
        def primary_key(*args)
          self.create_sequence = true
          super(*args)
        end
      end
    end

    # store column comments
    class <<t
      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(t)
    create_sequence = create_sequence || t.create_sequence
    column_comments = t.column_comments if t.column_comments
  end

  seq_name = options[:sequence_name] || quote_table_name("#{name}_seq")
  seq_start_value = options[:sequence_start_value] || default_sequence_start_value
  execute "CREATE SEQUENCE #{seq_name} START WITH #{seq_start_value}" if create_sequence
  
  add_table_comment name, options[:comment]
  column_comments.each do |column_name, comment|
    add_comment name, column_name, comment
  end
  
end

#current_database ⇒ Object

SCHEMA STATEMENTS ========================================

see: abstract/schema_statements.rb



567
568
569
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 567

def current_database #:nodoc:
  select_one("select sys_context('userenv','db_name') db from dual")["db"]
end

#default_sequence_name(table, column) ⇒ Object

:nodoc:



504
505
506
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 504

def default_sequence_name(table, column) #:nodoc:
  quote_table_name("#{table}_seq")
end

#disconnect! ⇒ Object

Disconnects from the database.



450
451
452
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 450

def disconnect!
  @connection.logoff rescue nil
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")



929
930
931
932
933
934
935
936
937
938
939
940
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 929

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 = order_by.split(',').map { |s| s.strip }.reject(&:blank?)
  order_columns = order_columns.zip((0...order_columns.size).to_a).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_table(name, options = {}) ⇒ Object

:nodoc:



743
744
745
746
747
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 743

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

#execute(sql, name = nil) ⇒ Object

DATABASE STATEMENTS ======================================

see: abstract/database_statements.rb



459
460
461
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 459

def execute(sql, name = nil) #:nodoc:
  log(sql, name) { @connection.exec sql }
end

#get_type_for_column(table_name, column_name) ⇒ Object 



636
637
638
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 636

def get_type_for_column(table_name, column_name)
  @table_column_type && @table_column_type[table_name] && @table_column_type[table_name][column_name.to_s.downcase]
end

#ignore_table_columns(table_name, *args) ⇒ Object

RSI: set ignored columns for table



615
616
617
618
619
620
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 615

def ignore_table_columns(table_name, *args)
  @ignore_table_columns ||= {}
  @ignore_table_columns[table_name] ||= []
  @ignore_table_columns[table_name] += args.map{|a| a.to_s.downcase}
  @ignore_table_columns[table_name].uniq!
end

#ignored_table_columns(table_name) ⇒ Object 



622
623
624
625
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 622

def ignored_table_columns(table_name)
  @ignore_table_columns ||= {}
  @ignore_table_columns[table_name]
end

#indexes(table_name, name = nil) ⇒ Object

This method selects all indexes at once, and caches them in a class variable. Subsequent index calls get them from the variable, without going to the DB.



580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 580

def indexes(table_name, name = nil)
  (owner, table_name) = @connection.describe(table_name)
  unless all_schema_indexes
    result = select_all(<<-SQL)
      SELECT lower(i.table_name) as table_name, lower(i.index_name) as index_name, i.uniqueness, lower(c.column_name) as column_name
        FROM all_indexes i, all_ind_columns c
       WHERE i.owner = '#{owner}'
         AND i.table_owner = '#{owner}'
         AND c.index_name = i.index_name
         AND c.index_owner = i.owner
         AND NOT EXISTS (SELECT uc.index_name FROM all_constraints uc WHERE uc.index_name = i.index_name AND uc.owner = i.owner AND uc.constraint_type = 'P')
        ORDER BY i.index_name, c.column_position
    SQL

    current_index = nil
    self.all_schema_indexes = []

    result.each do |row|
      # have to keep track of indexes because above query returns dups
      # there is probably a better query we could figure out
      if current_index != row['index_name']
        self.all_schema_indexes << ::ActiveRecord::ConnectionAdapters::IndexDefinition.new(row['table_name'], row['index_name'], row['uniqueness'] == "UNIQUE", [])
        current_index = row['index_name']
      end

      self.all_schema_indexes.last.columns << row['column_name']
    end
  end

  # Return the indexes just for the requested table, since AR is structured that way
  table_name = table_name.downcase
  all_schema_indexes.select{|i| i.table == table_name}
end

#insert_fixture(fixture, table_name) ⇒ Object

Inserts the given fixture into the table. Overridden to properly handle lobs.



510
511
512
513
514
515
516
517
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 510

def insert_fixture(fixture, table_name)
  super

  klass = fixture.class_name.constantize rescue nil
  if klass.respond_to?(:ancestors) && klass.ancestors.include?(ActiveRecord::Base)
    write_lobs(table_name, klass, fixture)
  end
end

#is_date_column?(name, table_name = nil) ⇒ Boolean

RSI: instance method uses at first check if column type defined at class level

Returns:

  • (Boolean) 


261
262
263
264
265
266
267
268
269
270
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 261

def is_date_column?(name, table_name = nil)
  case get_type_for_column(table_name, name)
  when nil
    self.class.is_date_column?(name, table_name)
  when :date
    true
  else
    false
  end
end

#lob_order_by_expression(klass, order) ⇒ Object

RSI: change LOB column for ORDER BY clause just first 100 characters are taken for ordering



548
549
550
551
552
553
554
555
556
557
558
559
560
561
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 548

def lob_order_by_expression(klass, order)
  return order if order.nil?
  changed = false
  new_order = order.to_s.strip.split(/, */).map do |order_by_col|
    column_name, asc_desc = order_by_col.split(/ +/)
    if column = klass.columns.detect { |col| col.name == column_name && col.sql_type =~ /LOB$/i}
      changed = true
      "DBMS_LOB.SUBSTR(#{column_name},100,1) #{asc_desc}"
    else
      order_by_col
    end
  end.join(', ')
  changed ? new_order : order
end

#native_database_types ⇒ Object

:nodoc:



303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 303

def native_database_types #:nodoc:
  {
    :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" },
    # RSI: changed to native TIMESTAMP type
    # :timestamp   => { :name => "DATE" },
    :timestamp   => { :name => "TIMESTAMP" },
    :time        => { :name => "DATE" },
    :date        => { :name => "DATE" },
    :binary      => { :name => "BLOB" },
    # RSI: if emulate_booleans_from_strings then store booleans in VARCHAR2
    :boolean     => emulate_booleans_from_strings ?
      { :name => "VARCHAR2", :limit => 1 } : { :name => "NUMBER", :limit => 1 }
  }
end

#next_sequence_value(sequence_name) ⇒ Object

Returns the next sequence value from a sequence generator. Not generally called directly; used by ActiveRecord to get the next primary key value when inserting a new database record (see #prefetch_primary_key?).



466
467
468
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 466

def next_sequence_value(sequence_name)
  select_one("select #{sequence_name}.nextval id from dual")['id']
end

#pk_and_sequence_for(table_name) ⇒ Object

Find a table’s primary key and sequence. Note: Only primary key is implemented - sequence will be nil.



837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 837

def pk_and_sequence_for(table_name)
  (owner, table_name) = @connection.describe(table_name)

  # RSI: changed select from all_constraints to user_constraints - much faster in large data dictionaries
  pks = select_values(<<-SQL, 'Primary Key')
    select cc.column_name
      from user_constraints c, user_cons_columns cc
     where c.owner = '#{owner}'
       and c.table_name = '#{table_name}'
       and c.constraint_type = 'P'
       and cc.owner = c.owner
       and cc.constraint_name = c.constraint_name
  SQL

  # only support single column keys
  pks.size == 1 ? [oracle_downcase(pks.first), nil] : nil
end

#prefetch_primary_key?(table_name = nil) ⇒ Boolean

Returns true for Oracle adapter (since Oracle requires primary key values to be pre-fetched before insert). See also #next_sequence_value.

Returns:

  • (Boolean) 


500
501
502
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 500

def prefetch_primary_key?(table_name = nil)
  true
end

#quote(value, column = nil) ⇒ Object

:nodoc:



369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 369

def quote(value, column = nil) #:nodoc:
  if value && column
    case column.type
    when :text, :binary
      %Q{empty_#{ column.sql_type.downcase rescue 'blob' }()}
    # RSI: TIMESTAMP support
    when :timestamp
      quote_timestamp_with_to_timestamp(value)
    # RSI: NLS_DATE_FORMAT independent DATE support
    when :date, :time, :datetime
      quote_date_with_to_date(value)
    else
      super
    end
  elsif value.acts_like?(:date)
    quote_date_with_to_date(value)
  elsif value.acts_like?(:time)
    value.to_i == value.to_f ? quote_date_with_to_date(value) : quote_timestamp_with_to_timestamp(value)
  else
    super
  end
end

#quote_column_name(name) ⇒ Object

camelCase column names need to be quoted; not that anyone using Oracle would really do this, but handling this case means we pass the test…



342
343
344
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 342

def quote_column_name(name) #:nodoc:
  name.to_s =~ /[A-Z]/ ? "\"#{name}\"" : quote_oracle_reserved_words(name)
end

#quote_date_with_to_date(value) ⇒ Object

RSI: should support that composite_primary_keys gem will pass date as string



403
404
405
406
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 403

def quote_date_with_to_date(value)
  value = value.to_s(:db) if value.acts_like?(:date) || value.acts_like?(:time)
  "TO_DATE('#{value}','YYYY-MM-DD HH24:MI:SS')"
end

#quote_string(s) ⇒ Object

:nodoc:



365
366
367
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 365

def quote_string(s) #:nodoc:
  s.gsub(/'/, "''")
end

#quote_table_name(name) ⇒ Object

abstract_adapter calls quote_column_name from quote_table_name, so prevent that



357
358
359
360
361
362
363
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 357

def quote_table_name(name)
  if self.class.valid_table_name?(name)
    name
  else
    "\"#{name}\""
  end
end

#quote_timestamp_with_to_timestamp(value) ⇒ Object 



408
409
410
411
412
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 408

def quote_timestamp_with_to_timestamp(value)
  # add up to 9 digits of fractional seconds to inserted time
  value = "#{value.to_s(:db)}.#{("%.6f"%value.to_f).split('.')[1]}" if value.acts_like?(:time)
  "TO_TIMESTAMP('#{value}','YYYY-MM-DD HH24:MI:SS.FF6')"
end

#quoted_false ⇒ Object 



397
398
399
400
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 397

def quoted_false
  return "'#{self.class.boolean_to_string(false)}'" if emulate_booleans_from_strings
  "0"
end

#quoted_true ⇒ Object 



392
393
394
395
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 392

def quoted_true
  return "'#{self.class.boolean_to_string(true)}'" if emulate_booleans_from_strings
  "1"
end

#raw_connection ⇒ Object 



427
428
429
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 427

def raw_connection
  @connection.raw_connection
end

#reconnect! ⇒ Object

Reconnects to the database.



443
444
445
446
447
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 443

def reconnect!
  @connection.reset!
rescue OracleEnhancedConnectionException => e
  @logger.warn "#{adapter_name} automatic reconnection failed: #{e.message}"
end

#remove_column(table_name, column_name) ⇒ Object

:nodoc:



801
802
803
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 801

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

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

clear cached indexes when removing index



756
757
758
759
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 756

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

#rename_column(table_name, column_name, new_column_name) ⇒ Object

:nodoc:



797
798
799
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 797

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:



738
739
740
741
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 738

def rename_table(name, new_name) #:nodoc:
  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")}" rescue nil
end

#rollback_db_transaction ⇒ Object

:nodoc:



480
481
482
483
484
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 480

def rollback_db_transaction #:nodoc:
  @connection.rollback
ensure
  @connection.autocommit = true
end

#select_rows(sql, name = nil) ⇒ Object

Returns an array of arrays containing the field values. Order is the same as that returned by #columns.



330
331
332
333
334
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 330

def select_rows(sql, name = nil)
  # last parameter indicates to return also column list
  result, columns = select(sql, name, true)
  result.map{ |v| columns.map{|c| v[c]} }
end

#set_type_for_columns(table_name, column_type, *args) ⇒ Object

RSI: set explicit type for specified table columns



628
629
630
631
632
633
634
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 628

def set_type_for_columns(table_name, column_type, *args)
  @table_column_type ||= {}
  @table_column_type[table_name] ||= {}
  args.each do |col|
    @table_column_type[table_name][col.to_s.downcase] = column_type
  end
end

#structure_drop ⇒ Object

:nodoc:



888
889
890
891
892
893
894
895
896
897
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 888

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

  # RSI: changed select from user_tables to all_tables - much faster in large data dictionaries
  select_all("select table_name from all_tables where owner = sys_context('userenv','session_user') order by 1").inject(s) do |drop, table|
    drop << "drop table #{table.to_a.first.last} cascade constraints;\n\n"
  end
end

#structure_dump ⇒ Object

:nodoc:



855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 855

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

  # RSI: changed select from user_tables to all_tables - much faster in large data dictionaries
  select_all("select table_name from all_tables where owner = sys_context('userenv','session_user') order by 1").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, char_used, char_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|
      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')
        length = row['char_used'] == 'C' ? row['char_length'].to_i : row['data_length'].to_i
        col <<  "(#{length})"
      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_migrations? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


299
300
301
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 299

def supports_migrations? #:nodoc:
  true
end

#table_alias_length ⇒ Object 



324
325
326
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 324

def table_alias_length
  30
end

#table_comment(table_name) ⇒ Object 



816
817
818
819
820
821
822
823
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 816

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

#tables(name = nil) ⇒ Object

RSI: changed select from user_tables to all_tables - much faster in large data dictionaries



572
573
574
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 572

def tables(name = nil) #:nodoc:
  select_all("select decode(table_name,upper(table_name),lower(table_name),table_name) name from all_tables where owner = sys_context('userenv','session_user')").map {|t| t['name']}
end

#write_lobs(table_name, klass, attributes) ⇒ Object

Writes LOB values from attributes, as indicated by the LOB columns of klass.



520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 520

def write_lobs(table_name, klass, attributes)
  # is class with composite primary key>
  is_with_cpk = klass.respond_to?(:composite?) && klass.composite?
  if is_with_cpk
    id = klass.primary_key.map {|pk| attributes[pk.to_s] }
  else
    id = quote(attributes[klass.primary_key])
  end
  klass.columns.select { |col| col.sql_type =~ /LOB$/i }.each do |col|
    value = attributes[col.name]
    # RSI: changed sequence of next two lines - should check if value is nil before converting to yaml
    next if value.nil?  || (value == '')
    value = value.to_yaml if col.text? && klass.serialized_attributes[col.name]
    uncached do
      if is_with_cpk
        lob = select_one("SELECT #{col.name} FROM #{table_name} WHERE #{klass.composite_where_clause(id)} FOR UPDATE",
                          'Writable Large Object')[col.name]
      else
        lob = select_one("SELECT #{col.name} FROM #{table_name} WHERE #{klass.primary_key} = #{id} FOR UPDATE",
                         'Writable Large Object')[col.name]
      end
      @connection.write_lob(lob, value, col.type == :binary)
    end
  end
end