Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Changeset 1912

Show
Ignore:
Timestamp:
07/24/05 14:01:35 (3 years ago)
Author:
david
Message:

Made Oracle a first-class connection adapter by adhering closer to idiomatic Oracle style #1798 [The Gang of Oracles]

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • trunk/activerecord/CHANGELOG

    r1897 r1912  
    11*SVN* 
     2 
     3* Default sequence names for Oracle changed to #{table_name}_seq, which is the most commonly used standard. In addition, a new method ActiveRecord::Base#set_sequence_name allows the developer to set the sequence name per model. This is a non-backwards-compatible change -- anyone using the old-style "rails_sequence" will need to either create new sequences, or set: ActiveRecord::Base.set_sequence_name = "rails_sequence" #1798 
     4 
     5* OCIAdapter now properly handles synonyms, which are commonly used to separate out the schema owner from the application user #1798 
     6 
     7* Fixed the handling of camelCase columns names in Oracle #1798 
     8 
     9* Implemented for OCI the Rakefile tasks of :clone_structure_to_test, :db_structure_dump, and :purge_test_database, which enable Oracle folks to enjoy all the agile goodness of Rails for testing. Note that the current implementation is fairly limited -- only tables and sequences are cloned, not constraints or indexes. A full clone in Oracle generally requires some manual effort, and is version-specific. Post 9i, Oracle recommends the use of the DBMS_METADATA package, though that approach requires editing of the physical characteristics generated #1798 
     10 
     11* Fixed the handling of multiple blob columns in Oracle if one or more of them are null #1798 
    212 
    313* Added support for calling constrained class methods on has_many and has_and_belongs_to_many collections #1764 [Tobias Luetke] 

  • trunk/activerecord/lib/active_record/base.rb

    r1894 r1912  
    570570      end 
    571571 
     572      # Defines the sequence_name (for Oracle) -- can be overridden in subclasses. 
     573      def sequence_name 
     574        "#{table_name}_seq" 
     575      end 
     576 
    572577      # Sets the table name to use to the given value, or (if the value 
    573578      # is nil or false) to the value returned by the given block. 
    …… 
    612617      end 
    613618      alias :inheritance_column= :set_inheritance_column 
     619 
     620      # Sets the name of the sequence to use when generating ids to the given 
     621      # value, or (if the value is nil or false) to the value returned by the 
     622      # given block. Currently useful only when using Oracle, which requires 
     623      # explicit sequences. 
     624      # 
     625      # Setting the sequence name when using other dbs will have no effect. 
     626      # If a sequence name is not explicitly set when using Oracle, it will 
     627      # default to the commonly used pattern of: #{table_name}_seq 
     628      # 
     629      # Example: 
     630      # 
     631      #   class Project < ActiveRecord::Base 
     632      #     set_sequence_name "projectseq"   # default would have been "project_seq" 
     633      #   end 
     634      def set_sequence_name( value=nil, &block ) 
     635        define_attr_method :sequence_name, value, &block 
     636      end 
     637      alias :sequence_name= :set_sequence_name 
    614638 
    615639      # Turns the +table_name+ back into a class name following the reverse rules of +table_name+. 
    …… 
    11941218          "VALUES(#{attributes_with_quotes.values.join(', ')})", 
    11951219          "#{self.class.name} Create", 
    1196           self.class.primary_key, self.id 
     1220          self.class.primary_key, self.id, self.class.sequence_name 
    11971221        ) 
    11981222 

  • trunk/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb

    r1893 r1912  
    280280 
    281281      # Returns the last auto-generated ID from the affected table. 
    282       def insert(sql, name = nil, pk = nil, id_value = nil) end 
     282      def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) end 
    283283 
    284284      # Executes the update statement and returns the number of rows affected. 

  • trunk/activerecord/lib/active_record/connection_adapters/db2_adapter.rb

    r1850 r1912  
    4545        end 
    4646 
    47         def insert(sql, name = nil, pk = nil, id_value = nil) 
     47        def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 
    4848          execute(sql, name = nil) 
    4949          id_value || last_insert_id 

  • trunk/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb

    r1774 r1912  
    110110      end 
    111111 
    112       def insert(sql, name = nil, pk = nil, id_value = nil) 
     112      def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 
    113113        execute(sql, name = nil) 
    114114        id_value || @connection.insert_id 

  • trunk/activerecord/lib/active_record/connection_adapters/oci_adapter.rb

    r1629 r1912  
    1616# remains then please acknowledge my contribution. 
    1717# Copyright 2005 Graham Jenkins 
    18 # $Revision: 1.2 $ 
     18 
    1919require 'active_record/connection_adapters/abstract_adapter' 
    2020 
    …… 
    7878      # 
    7979      # Usage notes: 
    80       # * Key generation uses a sequence "rails_sequence" for all tables. (I couldn't find a simple 
    81       #   and safe way of passing table-specific sequence information to the adapter.) 
     80      # * Key generation assumes a "${table_name}_seq" sequence is available for all tables; the 
     81      #   sequence name can be changed using ActiveRecord::Base.set_sequence_name 
    8282      # * Oracle uses DATE or TIMESTAMP datatypes for both dates and times. Consequently I have had to 
    8383      #   resort to some hacks to get data converted to Date or Time in Ruby. 
    …… 
    107107          if column and column.type == :binary then %Q{empty_#{ column.sql_type }()} 
    108108          else case value 
    109             when String     then %Q{'#{quote_string(value)}'} 
     109            when String       then %Q{'#{quote_string(value)}'} 
    110110            when NilClass     then 'null' 
    111111            when TrueClass    then '1' 
    112112            when FalseClass   then '0' 
    113             when Numeric    then value.to_s 
     113            when Numeric      then value.to_s 
    114114            when Date, Time   then %Q{'#{value.strftime("%Y-%m-%d %H:%M:%S")}'} 
    115             else           %Q{'#{quote_string(value.to_yaml)}'} 
     115            else                   %Q{'#{quote_string(value.to_yaml)}'} 
    116116            end 
     117          end 
     118        end 
     119 
     120        # camelCase column names need to be quoted; not that anyone using Oracle 
     121        # would really do this, but handling this case means we pass the test... 
     122        def quote_column_name(name) 
     123          name =~ /[A-Z]/ ? "\"#{name}\"" : name 
     124        end 
     125 
     126        def structure_dump 
     127          s = select_all("select sequence_name from user_sequences").inject("") do |structure, seq| 
     128            structure << "create sequence #{seq.to_a.first.last};\n\n" 
     129          end 
     130 
     131          select_all("select table_name from user_tables").inject(s) do |structure, table| 
     132            ddl = "create table #{table.to_a.first.last} (\n "   
     133            cols = select_all(%Q{ 
     134              select column_name, data_type, data_length, data_precision, data_scale, data_default, nullable 
     135              from user_tab_columns 
     136              where table_name = '#{table.to_a.first.last}' 
     137              order by column_id 
     138            }).map do |row|               
     139              col = "#{row['column_name'].downcase} #{row['data_type'].downcase}"       
     140              if row['data_type'] =='NUMBER' and !row['data_precision'].nil? 
     141                col << "(#{row['data_precision'].to_i}" 
     142                col << ",#{row['data_scale'].to_i}" if !row['data_scale'].nil? 
     143                col << ')' 
     144              elsif row['data_type'].include?('CHAR') 
     145                col << "(#{row['data_length'].to_i})"   
     146              end 
     147              col << " default #{row['data_default']}" if !row['data_default'].nil? 
     148              col << ' not null' if row['nullable'] == 'N' 
     149              col 
     150            end 
     151            ddl << cols.join(",\n ") 
     152            ddl << ");\n\n" 
     153            structure << ddl 
     154          end 
     155        end 
     156 
     157        def structure_drop 
     158          s = select_all("select sequence_name from user_sequences").inject("") do |drop, seq| 
     159            drop << "drop sequence #{seq.to_a.first.last};\n\n" 
     160          end 
     161 
     162          select_all("select table_name from user_tables").inject(s) do |drop, table| 
     163            drop << "drop table #{table.to_a.first.last} cascade constraints;\n\n" 
    117164          end 
    118165        end 
    …… 
    121168          offset = sql =~ /OFFSET (\d+)$/ ? $1.to_i : 0 
    122169          sql, limit = $1, $2.to_i if sql =~ /(.*)(?: LIMIT[= ](\d+))(\s*OFFSET \d+)?$/ 
     170           
    123171          if limit 
    124172            sql = "select * from (select raw_sql_.*, rownum raw_rnum_ from (#{sql}) raw_sql_ where rownum <= #{offset+limit}) where raw_rnum_ > #{offset}" 
    …… 
    126174            sql = "select * from (select raw_sql_.*, rownum raw_rnum_ from (#{sql}) raw_sql_) where raw_rnum_ > #{offset}" 
    127175          end 
     176           
    128177          cursor = log(sql, name) { @connection.exec sql } 
    129           cols = cursor.get_col_names.map { |x| x.downcase } 
     178          cols = cursor.get_col_names.map { |x| oci_downcase(x) } 
    130179          rows = [] 
     180           
    131181          while row = cursor.fetch 
    132182            hash = Hash.new 
    133             cols.each_with_index { |col, i| 
     183 
     184            cols.each_with_index do |col, i| 
    134185              hash[col] = case row[i] 
    135186                when OCI8::LOB 
    …… 
    140191                else row[i] 
    141192                end unless col == 'raw_rnum_' 
    142             } 
     193            end 
     194 
    143195            rows << hash 
    144196          end 
     197 
    145198          rows 
    146199        ensure 
    …… 
    154207 
    155208        def columns(table_name, name = nil) 
    156           cols = select_all(%Q{ 
     209          select_all(%Q{ 
    157210              select column_name, data_type, data_default, data_length, data_scale 
    158               from user_tab_columns where table_name = '#{table_name.upcase}'} 
    159           ).map { |row| 
    160             OCIColumn.new row['column_name'].downcase, row['data_default'], 
    161               row['data_length'], row['data_type'], row['data_scale'] 
    162           } 
    163           cols 
    164         end 
    165  
    166         def insert(sql, name = nil, pk = nil, id_value = nil) 
     211              from user_catalog cat, user_synonyms syn, all_tab_columns col 
     212              where cat.table_name = '#{table_name.upcase}' 
     213              and syn.synonym_name (+)= cat.table_name 
     214              and col.owner = nvl(syn.table_owner, user) 
     215              and col.table_name = nvl(syn.table_name, cat.table_name)} 
     216          ).map do |row| 
     217            OCIColumn.new( 
     218              oci_downcase(row['column_name']),  
     219              row['data_default'], 
     220              row['data_length'],  
     221              row['data_type'],  
     222              row['data_scale'] 
     223            ) 
     224          end 
     225        end 
     226 
     227        def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 
    167228          if pk.nil? # Who called us? What does the sql look like? No idea! 
    168229            execute sql, name 
    …… 
    170231            log(sql, name) { @connection.exec sql } 
    171232          else # Assume the sql contains a bind-variable for the id 
    172             id_value = select_one("select rails_sequence.nextval id from dual")['id'] 
     233            id_value = select_one("select #{sequence_name}.nextval id from dual")['id'] 
    173234            log(sql, name) { @connection.exec sql, id_value } 
    174235          end 
     236 
    175237          id_value 
    176238        end 
    …… 
    202264          'OCI' 
    203265        end 
     266         
     267        private 
     268          # Oracle column names by default are case-insensitive, but treated as upcase; 
     269          # for neatness, we'll downcase within Rails. EXCEPT that folks CAN quote 
     270          # their column names when creating Oracle tables, which makes then case-sensitive. 
     271          # I don't know anybody who does this, but we'll handle the theoretical case of a 
     272          # camelCase column name. I imagine other dbs handle this different, since there's a 
     273          # unit test that's currently failing test_oci. 
     274          def oci_downcase(column_name) 
     275            column_name =~ /[a-z]/ ? column_name : column_name.downcase 
     276          end 
    204277      end 
    205278    end 
    …… 
    208281  module ActiveRecord 
    209282    class Base 
    210       def self.oci_connection(config) #:nodoc: 
    211         conn = OCI8.new config[:username], config[:password], config[:host] 
    212         conn.exec %q{alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'} 
    213         conn.exec %q{alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS'} 
    214         conn.autocommit = true 
    215         ConnectionAdapters::OCIAdapter.new conn, logger 
     283      class << self 
     284        def oci_connection(config) #:nodoc: 
     285          conn = OCI8.new config[:username], config[:password], config[:host] 
     286          conn.exec %q{alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'} 
     287          conn.exec %q{alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS'} 
     288          conn.autocommit = true 
     289          ConnectionAdapters::OCIAdapter.new conn, logger 
     290        end 
    216291      end 
    217292 
    …… 
    232307      # After setting large objects to empty, select the OCI8::LOB and write back the data 
    233308      def write_lobs() #:nodoc: 
    234         if connection.class == ConnectionAdapters::OCIAdapter 
     309        if connection.is_a?(ConnectionAdapters::OCIAdapter) 
    235310          self.class.columns.select { |c| c.type == :binary }.each { |c| 
    236             break unless value = self[c.name] 
     311            value = self[c.name] 
     312            next if value.nil?  || (value == '') 
    237313            lob = connection.select_one( 
    238314              "select #{ c.name} from #{ self.class.table_name } WHERE #{ self.class.primary_key} = #{quote(id)}", 

  • trunk/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb

    r1703 r1912  
    9696      end 
    9797 
    98       def insert(sql, name = nil, pk = nil, id_value = nil) 
     98      def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 
    9999        execute(sql, name) 
    100100        table = sql.split(" ", 4)[2] 

  • trunk/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb

    r1893 r1912  
    124124      end 
    125125 
    126       def insert(sql, name = nil, pk = nil, id_value = nil) 
     126      def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 
    127127        execute(sql, name = nil) 
    128128        id_value || @connection.last_insert_row_id 

  • trunk/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb

    r1784 r1912  
    205205      end 
    206206 
    207       def insert(sql, name = nil, pk = nil, id_value = nil) 
     207      def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 
    208208        begin 
    209209          table_name = get_table_name(sql) 

  • trunk/activerecord/Rakefile

    r1814 r1912  
    2323 
    2424desc "Default Task" 
    25 task :default => [ :test_ruby_mysql, :test_mysql_ruby, :test_sqlite, :test_sqlite3, :test_postgresql ] 
     25task :default => [ :test_mysql, :test_sqlite, :test_postgresql ] 
    2626 
    2727# Run the unit tests 
    2828 
    29 Rake::TestTask.new("test_ruby_mysql") { |t| 
    30   t.libs << "test" << "test/connections/native_mysql" 
    31   t.pattern = 'test/*_test{,_mysql}.rb' 
    32   t.verbose = true 
    33 } 
    34  
    35 Rake::TestTask.new("test_mysql_ruby") { |t| 
    36   t.libs << "test" << "test/connections/native_mysql" 
    37   t.pattern = 'test/*_test{,_mysql}.rb' 
    38   t.verbose = true 
    39 } 
    40  
    41 for adapter in %w( postgresql sqlite sqlite3 sqlserver sqlserver_odbc db2 oci ) 
     29for adapter in %w( mysql postgresql sqlite sqlite3 sqlserver sqlserver_odbc db2 oci ) 
    4230  Rake::TestTask.new("test_#{adapter}") { |t| 
    4331    t.libs << "test" << "test/connections/native_#{adapter}" 

  • trunk/activerecord/test/base_test.rb

    r1850 r1912  
    572572  def test_multiparameter_mass_assignment_protector 
    573573    task = Task.new 
    574     time = Time.mktime(0) 
     574    time = Time.mktime(2000, 1, 1, 1) 
    575575    task.starting = time  
    576576    attributes = { "starting(1i)" => "2004", "starting(2i)" => "6", "starting(3i)" => "24" } 

  • trunk/activerecord/test/fixtures/company.rb

    r1398 r1912  
    11class Company < ActiveRecord::Base 
    22  attr_protected :rating 
     3  set_sequence_name :companies_nonstd_seq 
    34 
    45  validates_presence_of :name 

  • trunk/activerecord/test/fixtures/db_definitions/oci.drop.sql

    r1132 r1912  
    22drop table companies; 
    33drop table topics; 
     4drop synonym subjects; 
     5drop table developers_projects; 
     6drop table computers; 
    47drop table developers; 
    58drop table projects; 
    6 drop table developers_projects; 
    79drop table customers; 
    810drop table movies; 
    …… 
    1517drop table people; 
    1618drop table binaries; 
    17 drop table posts; 
    1819drop table comments; 
    1920drop table authors; 
    20 drop table computers; 
     21drop table tasks; 
     22drop table categories_posts; 
    2123drop table categories; 
    22 drop table categories_posts; 
    23 drop sequence rails_sequence; 
     24drop table posts; 
     25drop table fk_test_has_pk; 
     26drop table fk_test_has_fk; 
     27drop sequence accounts_seq; 
     28drop sequence companies_nonstd_seq; 
     29drop sequence topics_seq; 
     30drop sequence developers_seq; 
     31drop sequence projects_seq; 
     32drop sequence developers_projects_seq; 
     33drop sequence customers_seq; 
     34drop sequence movies_seq; 
     35drop sequence subscribers_seq; 
     36drop sequence booleantests_seq; 
     37drop sequence auto_id_tests_seq; 
     38drop sequence entrants_seq; 
     39drop sequence colnametests_seq; 
     40drop sequence mixins_seq; 
     41drop sequence people_seq; 
     42drop sequence binaries_seq; 
     43drop sequence posts_seq; 
     44drop sequence comments_seq; 
     45drop sequence authors_seq; 
     46drop sequence tasks_seq; 
     47drop sequence computers_seq; 
     48drop sequence categories_seq; 
     49drop sequence categories_posts_seq; 
     50drop sequence fk_test_has_pk_seq; 
     51drop sequence fk_test_has_fk_seq; 

  • trunk/activerecord/test/fixtures/db_definitions/oci.sql

    r1860 r1912  
    1 create sequence rails_sequence minvalue 10000; 
    2  
    31create table companies ( 
    42    id integer not null, 
    …… 
    1311); 
    1412 
     13-- non-standard sequence name used to test set_sequence_name 
     14-- 
     15create sequence companies_nonstd_seq minvalue 10000; 
     16 
    1517create table accounts ( 
    1618    id integer not null, 
    …… 
    1921    primary key (id) 
    2022); 
     23create sequence accounts_seq minvalue 10000; 
    2124 
    2225create table topics ( 
    …… 
    5154    primary key (id) 
    5255); 
     56create sequence topics_seq minvalue 10000; 
     57 
     58create synonym subjects for topics; 
    5359 
    5460create table developers ( 
    …… 
    6066    primary key (id) 
    6167); 
     68create sequence developers_seq minvalue 10000; 
    6269 
    6370create table projects ( 
    …… 
    6774    primary key (id) 
    6875); 
     76create sequence projects_seq minvalue 10000; 
    6977 
    7078create table developers_projects ( 
    …… 
    8088    joined_on date default null 
    8189); 
     90create sequence developers_projects_seq minvalue 10000; 
    8291 
    8392create table customers ( 
    …… 
    91100    primary key (id) 
    92101); 
     102create sequence customers_seq minvalue 10000; 
    93103 
    94104create table movies ( 
    …… 
    97107    primary key (movieid) 
    98108); 
     109create sequence movies_seq minvalue 10000; 
    99110 
    100111create table subscribers ( 
    …… 
    103114    primary key (nick) 
    104115); 
     116create sequence subscribers_seq minvalue 10000; 
    105117 
    106118create table booleantests ( 
    …… 
    109121    primary key (id) 
    110122); 
     123create sequence booleantests_seq minvalue 10000; 
    111124 
    112125create table auto_id_tests ( 
    …… 
    115128    primary key (auto_id) 
    116129); 
     130create sequence auto_id_tests_seq minvalue 10000; 
    117131 
    118132create table entrants ( 
    …… 
    121135    course_id integer not null 
    122136); 
     137create sequence entrants_seq minvalue 10000; 
    123138 
    124139create table colnametests ( 
    …… 
    127142    primary key (id) 
    128143); 
     144create sequence colnametests_seq minvalue 10000; 
    129145 
    130146create table mixins ( 
    …… 
    153169    primary key (id) 
    154170); 
     171create sequence mixins_seq minvalue 10000; 
    155172 
    156173create table people ( 
    …… 
    160177    primary key (id) 
    161178); 
     179create sequence people_seq minvalue 10000; 
    162180 
    163181create table binaries ( 
    …… 
    166184    primary key (id) 
    167185); 
     186create sequence binaries_seq minvalue 10000; 
    168187 
    169188create table computers ( 
    170189  id integer not null primary key, 
    171190  developer integer not null references developers initially deferred disable, 
    172   extendedWarranty integer not null 
    173 ); 
     191  "extendedWarranty" integer not null 
     192); 
     193create sequence computers_seq minvalue 10000; 
    174194 
    175195create table posts ( 
    …… 
    180200  body varchar(3000) default null 
    181201); 
     202create sequence posts_seq minvalue 10000; 
    182203 
    183204create table comments ( 
    …… 
    187208  body varchar(3000) default null 
    188209); 
     210create sequence comments_seq minvalue 10000; 
    189211 
    190212create table authors ( 
    …… 
    192214  name varchar(255) default null 
    193215); 
     216create sequence authors_seq minvalue 10000; 
    194217 
    195218create table tasks ( 
    …… 
    198221  ending date default null 
    199222); 
     223create sequence tasks_seq minvalue 10000; 
    200224 
    201225create table categories ( 
    …… 
    204228  type varchar(255) default null 
    205229); 
     230create sequence categories_seq minvalue 10000; 
    206231 
    207232create table categories_posts ( 
    …… 
    209234  post_id integer not null references posts initially deferred disable 
    210235); 
     236create sequence categories_posts_seq minvalue 10000; 
    211237 
    212238create table fk_test_has_pk ( 
    213239  id integer not null primary key 
    214240); 
     241create sequence fk_test_has_pk_seq minvalue 10000; 
    215242 
    216243create table fk_test_has_fk ( 
    …… 
    218245  fk_id integer not null references fk_test_has_fk initially deferred disable 
    219246); 
     247create sequence fk_test_has_fk_seq minvalue 10000; 

  • trunk/activerecord/test/fixtures/db_definitions/oci2.drop.sql

    r761 r1912  
    11drop table courses; 
    2 drop sequence rails_sequence; 
     2drop sequence courses_seq; 

  • trunk/activerecord/test/fixtures/db_definitions/oci2.sql

    r761 r1912  
    1 create sequence rails_sequence minvalue 10000; 
    2  
    31create table courses ( 
    42  id int not null primary key, 
    53  name varchar(255) not null 
    64); 
     5 
     6create sequence courses_seq minvalue 10000;