Class: ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter

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

Overview

Oracle enhanced adapter will work with both Ruby 1.8/1.9 ruby-oci8 gem (which provides interface to Oracle OCI client) or with JRuby and Oracle JDBC driver.

It should work with Oracle 9i, 10g and 11g databases. Limited set of functionality should work on Oracle 8i as well but several features rely on newer functionality in Oracle database.

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. 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.

Required parameters:

  • :username

  • :password

  • :database - either TNS alias or connection string for OCI client or database name in JDBC connection string

Optional parameters:

  • :host - host name for JDBC connection, defaults to “localhost”

  • :port - port number for JDBC connection, defaults to 1521

  • :privilege - set “SYSDBA” if you want to connect with this privilege

  • :allow_concurrency - set to “true” if non-blocking mode should be enabled (just for OCI client)

  • :prefetch_rows - how many rows should be fetched at one time to increase performance, defaults to 100

  • :cursor_sharing - cursor sharing mode to minimize amount of unique statements, defaults to “similar”

  • :nls_length_semantics - semantics of size of VARCHAR2 and CHAR columns, defaults to “CHAR” (meaning that size specifies number of characters and not bytes)

Direct Known Subclasses

OracleAdapter

Constant Summary collapse

IDENTIFIER_MAX_LENGTH =

maximum length of Oracle identifiers

30

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(connection, logger = nil) ⇒ OracleEnhancedAdapter

:nodoc:



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

def initialize(connection, logger = nil) #:nodoc:
  super
  @quoted_column_names, @quoted_table_names = {}, {}
end

Instance Attribute Details

#auto_retry ⇒ Object

If SQL statement fails due to lost connection then reconnect and retry SQL statement if autocommit mode is enabled. By default this functionality is disabled.



491
492
493
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 491

def auto_retry
  @auto_retry
end

Class Method Details

.boolean_to_string(bool) ⇒ Object

How boolean value should be quoted to String. Used if emulate_booleans_from_strings option is set to true.



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

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

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

Check column name to identify if it is boolean (and not String) column. Is used if emulate_booleans_from_strings option is set to true. Override this method definition in initializer file if different boolean column recognition is needed.

Returns:

  • (Boolean) 


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

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

Check column name to identify if it is Date (and not Time) column. Is used if emulate_dates_by_column_name option is set to true. Override this method definition in initializer file if different Date column recognition is needed.

Returns:

  • (Boolean) 


297
298
299
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 297

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

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

Check column name to identify if it is Integer (and not Float or BigDecimal) column. Is used if emulate_integers_by_column_name option is set to true. Override this method definition in initializer file if different Integer column recognition is needed.

Returns:

  • (Boolean) 


327
328
329
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 327

def self.is_integer_column?(name, table_name = nil)
  name =~ /((^|_)id|_(pk|fk))$/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) 


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

def self.valid_table_name?(name) #:nodoc:
  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) 


505
506
507
508
509
510
511
512
513
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 505

def active? #:nodoc:
  # 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:



371
372
373
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 371

def adapter_name #:nodoc:
  'OracleEnhanced'
end

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

:nodoc:



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

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

#add_column_options!(sql, options) ⇒ Object

:nodoc:



1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1067

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

:nodoc:



932
933
934
935
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 932

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



856
857
858
859
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 856

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

#add_limit_offset!(sql, options) ⇒ Object

:nodoc:



568
569
570
571
572
573
574
575
576
577
578
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 568

def add_limit_offset!(sql, options) #:nodoc:
  # 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.



1113
1114
1115
1116
1117
1118
1119
1120
1121
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1113

def add_order_by_for_association_limiting!(sql, options) #:nodoc:
  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

:nodoc:



937
938
939
940
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 937

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

#begin_db_transaction ⇒ Object

:nodoc:



552
553
554
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 552

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

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

:nodoc:



909
910
911
912
913
914
915
916
917
918
919
920
921
922
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 909

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:



895
896
897
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 895

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

:nodoc:



899
900
901
902
903
904
905
906
907
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 899

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

#clear_types_for_columns ⇒ Object

:nodoc:



722
723
724
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 722

def clear_types_for_columns #:nodoc:
  @table_column_type = nil
end

#column_comment(table_name, column_name) ⇒ Object

:nodoc:



951
952
953
954
955
956
957
958
959
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 951

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

#columns(table_name, name = nil) ⇒ Object

:nodoc:



726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 726

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

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

  table_cols = "select column_name as name, data_type as sql_type, data_default, nullable,\ndecode(data_type, 'NUMBER', data_precision,\n'FLOAT', data_precision,\n'VARCHAR2', decode(char_used, 'C', char_length, data_length),\n'CHAR', decode(char_used, 'C', char_length, data_length),\nnull) as limit,\ndecode(data_type, 'NUMBER', data_scale, null) as scale\nfrom all_tab_columns\nwhere owner      = '\#{owner}'\nand table_name = '\#{desc_table_name}'\norder by column_id\n"

  # 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',
                     # pass table name for table specific column definitions
                     table_name,
                     # 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:



556
557
558
559
560
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 556

def commit_db_transaction #:nodoc:
  @connection.commit
ensure
  @connection.autocommit = true
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 =>

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

create_table :employees, :comment => 


801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 801

def create_table(name, options = {}, &block)
  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) if block
    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



649
650
651
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 649

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

#default_sequence_name(table, column) ⇒ Object

:nodoc:



586
587
588
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 586

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

#default_sequence_start_value ⇒ Object

:singleton-method: Specify default sequence start with value (by default 10000 if not explicitly set), e.g.:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.default_sequence_start_value = 1



778
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 778

cattr_accessor :default_sequence_start_value

#disconnect! ⇒ Object

Disconnects from the database.



523
524
525
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 523

def disconnect! #:nodoc:
  @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")



1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1097

def distinct(columns, order_by) #:nodoc:
  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:



849
850
851
852
853
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 849

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

#emulate_booleans ⇒ Object

:singleton-method: By default, the OracleEnhancedAdapter will consider all columns of type NUMBER(1) as boolean. If you wish to disable this emulation you can add the following line to your initializer file:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_booleans = false



263
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 263

cattr_accessor :emulate_booleans

#emulate_booleans_from_strings ⇒ Object

:singleton-method: If you wish that CHAR(1), VARCHAR2(1) columns or VARCHAR2 columns with FLAG or YN at the end of their name are typecasted to booleans then you can add the following line to your initializer file:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_booleans_from_strings = true



337
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 337

cattr_accessor :emulate_booleans_from_strings

#emulate_dates ⇒ Object

:singleton-method: By default, the OracleEnhancedAdapter will typecast all columns of type DATE to Time or DateTime (if value is out of Time value range) value. If you wish that DATE values with hour, minutes and seconds equal to 0 are typecasted to Date then you can add the following line to your initializer file:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_dates = true

As this option can have side effects when unnecessary typecasting is done it is recommended that Date columns are explicily defined with set_date_columns method.



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

cattr_accessor :emulate_dates

#emulate_dates_by_column_name ⇒ Object

:singleton-method: By default, the OracleEnhancedAdapter will typecast all columns of type DATE to Time or DateTime (if value is out of Time value range) value. If you wish that DATE columns with “date” in their name (e.g. “creation_date”) are typecasted to Date then you can add the following line to your initializer file:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_dates_by_column_name = true

As this option can have side effects when unnecessary typecasting is done it is recommended that Date columns are explicily defined with set_date_columns method.



291
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 291

cattr_accessor :emulate_dates_by_column_name

#emulate_integers_by_column_name ⇒ Object

:singleton-method: By default, the OracleEnhancedAdapter will typecast all columns of type NUMBER (without precision or scale) to Float or BigDecimal value. If you wish that NUMBER columns with name “id” or that end with “_id” are typecasted to Integer then you can add the following line to your initializer file:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_integers_by_column_name = true



321
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 321

cattr_accessor :emulate_integers_by_column_name

#execute(sql, name = nil) ⇒ Object

Executes a SQL statement



533
534
535
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 533

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

#get_type_for_column(table_name, column_name) ⇒ Object

:nodoc:



718
719
720
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 718

def get_type_for_column(table_name, column_name) #:nodoc:
  @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

set ignored columns for table



697
698
699
700
701
702
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 697

def ignore_table_columns(table_name, *args) #:nodoc:
  @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

:nodoc:



704
705
706
707
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 704

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

#index_name(table_name, options) ⇒ Object

returned shortened index name if default is too large



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 868

def index_name(table_name, options) #:nodoc:
  default_name = super(table_name, options)
  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
  
  if shortened_name.length <= IDENTIFIER_MAX_LENGTH
    @logger.warn "#{adapter_name} shortened index name #{default_name} to #{shortened_name}" if @logger
    return shortened_name
  else
    raise ArgumentError, "#{adapter_name} cannot shorten index name #{default_name}, please use add_index with :name option"
  end
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.



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
690
691
692
693
694
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 662

def indexes(table_name, name = nil) #:nodoc:
  (owner, table_name) = @connection.describe(table_name)
  unless all_schema_indexes
    result = select_all("SELECT lower(i.table_name) as table_name, lower(i.index_name) as index_name, i.uniqueness, lower(c.column_name) as column_name\nFROM all_indexes i, all_ind_columns c\nWHERE i.owner = '\#{owner}'\nAND i.table_owner = '\#{owner}'\nAND c.index_name = i.index_name\nAND c.index_owner = i.owner\nAND 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')\nORDER BY i.index_name, c.column_position\n")

    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.



592
593
594
595
596
597
598
599
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 592

def insert_fixture(fixture, table_name) #:nodoc:
  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

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

Returns:

  • (Boolean) 


302
303
304
305
306
307
308
309
310
311
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 302

def is_date_column?(name, table_name = nil) #:nodoc:
  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

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



630
631
632
633
634
635
636
637
638
639
640
641
642
643
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 630

def lob_order_by_expression(klass, order) #:nodoc:
  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:



379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 379

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" },
    # changed to native TIMESTAMP type
    # :timestamp   => { :name => "DATE" },
    :timestamp   => { :name => "TIMESTAMP" },
    :time        => { :name => "DATE" },
    :date        => { :name => "DATE" },
    :binary      => { :name => "BLOB" },
    # 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?).



548
549
550
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 548

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.



963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 963

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

  # changed select from all_constraints to user_constraints - much faster in large data dictionaries
  pks = select_values("select cc.column_name\nfrom user_constraints c, user_cons_columns cc\nwhere c.owner = '\#{owner}'\nand c.table_name = '\#{table_name}'\nand c.constraint_type = 'P'\nand cc.owner = c.owner\nand cc.constraint_name = c.constraint_name\n", 'Primary Key')

  # 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) 


582
583
584
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 582

def prefetch_primary_key?(table_name = nil)
  true
end

#quote(value, column = nil) ⇒ Object

:nodoc:



440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 440

def quote(value, column = nil) #:nodoc:
  if value && column
    case column.type
    when :text, :binary
      %Q{empty_#{ column.sql_type.downcase rescue 'blob' }()}
    # NLS_DATE_FORMAT independent TIMESTAMP support
    when :timestamp
      quote_timestamp_with_to_timestamp(value)
    # 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

QUOTING ==================================================

see: abstract/quoting.rb



411
412
413
414
415
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 411

def quote_column_name(name) #:nodoc:
  # 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...
  @quoted_column_names[name] = name.to_s =~ /[A-Z]/ ? "\"#{name}\"" : quote_oracle_reserved_words(name)
end

#quote_date_with_to_date(value) ⇒ Object

:nodoc:



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

def quote_date_with_to_date(value) #:nodoc:
  # should support that composite_primary_keys gem will pass date as string
  value = quoted_date(value) if value.acts_like?(:date) || value.acts_like?(:time)
  "TO_DATE('#{value}','YYYY-MM-DD HH24:MI:SS')"
end

#quote_string(s) ⇒ Object

:nodoc:



436
437
438
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 436

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

#quote_table_name(name) ⇒ Object

:nodoc:



427
428
429
430
431
432
433
434
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 427

def quote_table_name(name) #:nodoc:
  # abstract_adapter calls quote_column_name from quote_table_name, so prevent that
  @quoted_table_names[name] ||= if self.class.valid_table_name?(name)
    name
  else
    "\"#{name}\""
  end
end

#quote_timestamp_with_to_timestamp(value) ⇒ Object

:nodoc:



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

def quote_timestamp_with_to_timestamp(value) #:nodoc:
  # add up to 9 digits of fractional seconds to inserted time
  value = "#{quoted_date(value)}.#{("%.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

:nodoc:



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

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

#quoted_true ⇒ Object

:nodoc:



463
464
465
466
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 463

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

#raw_connection ⇒ Object

return raw OCI8 or JDBC connection



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

def raw_connection
  @connection.raw_connection
end

#reconnect! ⇒ Object

Reconnects to the database.



516
517
518
519
520
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 516

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

#remove_column(table_name, column_name) ⇒ Object

:nodoc:



928
929
930
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 928

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



862
863
864
865
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 862

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:



924
925
926
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 924

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:



844
845
846
847
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 844

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:



562
563
564
565
566
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 562

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.



539
540
541
542
543
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 539

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

set explicit type for specified table columns



710
711
712
713
714
715
716
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 710

def set_type_for_columns(table_name, column_type, *args) #:nodoc:
  @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:



1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1055

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

  # 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:



981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 981

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

  # 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

#structure_dump_db_stored_code ⇒ Object

Extract all stored procedures, packages, synonyms and views.



1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1015

def structure_dump_db_stored_code #:nodoc:
  structure = "\n"
  select_all("select distinct name, type 
               from all_source 
              where type in ('PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION') 
                and  owner = sys_context('userenv','session_user')").inject("\n\n") do |structure, source|
      ddl = "create or replace   \n "
      lines = select_all(%Q{
              select text
                from all_source
               where name = '#{source['name']}'
                 and type = '#{source['type']}'
                 and owner = sys_context('userenv','session_user')
               order by line 
            }).map do |row|
        ddl << row['text'] if row['text'].size > 1
      end
      ddl << ";"
      structure << ddl << "\n"
  end

  # export views 
  select_all("select view_name, text from user_views").inject(structure) do |structure, view|
    ddl = "create or replace view #{view['view_name']} AS\n "
    # any views with empty lines will cause OCI to barf when loading. remove blank lines =/ 
    ddl << view['text'].gsub(/^\n/, '') 
    ddl << ";\n\n"
    structure << ddl
  end

  # export synonyms 
  select_all("select owner, synonym_name, table_name, table_owner 
                from all_synonyms  
               where table_owner = sys_context('userenv','session_user') ").inject(structure) do |structure, synonym|
    ddl = "create or replace #{synonym['owner'] == 'PUBLIC' ? 'PUBLIC' : '' } SYNONYM #{synonym['synonym_name']} for #{synonym['table_owner']}.#{synonym['table_name']};\n\n"
    structure << ddl;
  end
end

#supports_migrations? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


375
376
377
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 375

def supports_migrations? #:nodoc:
  true
end

#table_alias_length ⇒ Object

:nodoc:



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

def table_alias_length #:nodoc:
  IDENTIFIER_MAX_LENGTH
end

#table_comment(table_name) ⇒ Object

:nodoc:



942
943
944
945
946
947
948
949
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 942

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

#tables(name = nil) ⇒ Object

:nodoc:



653
654
655
656
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 653

def tables(name = nil) #:nodoc:
  # changed select from user_tables to all_tables - much faster in large data dictionaries
  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.



602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 602

def write_lobs(table_name, klass, attributes) #:nodoc:
  # 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]
    # 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.to_s, col.type == :binary)
    end
  end
end