Added a OCI8-based Oracle adapter that has been verified to work with Oracle 8 and 9 #629 [Graham Jenkins]. Removed Maiks Oracle adapter until it can be merged with the OCI one.

git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@761 5ecf4fe2-1ee6-0310-87b1-e25e094e27de

13 files changed, 502 insertions, 440 deletions

diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG
index 667ea44d3b..20f5efcb88 100644
--- a/activerecord/CHANGELOG
+++ b/activerecord/CHANGELOG
@@ -1,5 +1,23 @@
 *SVN*
 
+* Added a OCI8-based Oracle adapter that has been verified to work with Oracle 8 and 9 #629 [Graham Jenkins]. Usage notes:
+
+    1.  Key generation uses a sequence "rails_sequence" for all tables. (I couldn't find a simple
+        and safe way of passing table-specific sequence information to the adapter.)
+    2.  Oracle uses DATE or TIMESTAMP datatypes for both dates and times. Consequently I have had to
+        resort to some hacks to get data converted to Date or Time in Ruby.
+        If the column_name ends in _at (like created_at, updated_at) 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 nasty - but if you use Duck Typing you'll probably not care very much.
+        In 9i it's tempting to map DATE to Date and TIMESTAMP to Time but I don't think that is
+        valid - too many databases use DATE for both.
+        Timezones and sub-second precision on timestamps are not supported.
+    3.  Default values that are functions (such as "SYSDATE") are not supported. This is a
+        restriction of the way active record supports default values.
+    4.  Referential integrity constraints are not fully supported. Under at least
+        some circumstances, active record appears to delete parent and child records out of
+        sequence and out of transaction scope. (Or this may just be a problem of test setup.)
+
 * Added TimeZone as the first of a number of value objects that Active Record will start shipping to provide incentatives to use rich value objects using composed_of #688 [Jamis Buck]
 
 * Added option :schema_order to the PostgreSQL adapter to support the use of multiple schemas per database #697 [YuriSchimke]
@@ -53,21 +71,6 @@
       inheritance_column { original_inheritance_column + "_id" }
     end
 
-* Added an Oracle adapter that works with the Oracle bindings by Yoshida (http://raa.ruby-lang.org/project/oracle/) #564 [Maik Schmidt]
-  
-  A number of caveats:
-
-    1. You have to create a sequence for every table that need an auto-generated primary key. 
-       The sequence name is built by appending "_id" to the according table_name, 
-       i.e. if you have a table called "employees", you have to create a sequence called "employees_id". 
-    
-    2. The data type TIME is not supported, because Oracle does not support it. 
-    
-    3. Binary data (BLOBs etc.) are currently not supported. Will be fixed soon. 
-    
-    4. LIMIT clauses with OFFSET are not supported, because I could not find a way to simulate this behaviour using ROWNUM and "SELECT *".
-
-
 * Fixed Base#clone for use with PostgreSQL #565 [hanson@surgery.wisc.edu]
 
 
diff --git a/activerecord/Rakefile b/activerecord/Rakefile
index 9bce56a4a8..9017df277d 100755
--- a/activerecord/Rakefile
+++ b/activerecord/Rakefile
@@ -69,6 +69,12 @@ Rake::TestTask.new("test_oracle") { |t|
   t.verbose = true
 }
 
+Rake::TestTask.new("test_oci") { |t|
+  t.libs << "test" << "test/connections/native_oci"
+  t.pattern = 'test/*_test.rb'
+  t.verbose = true
+}
+
 # Generate the RDoc documentation
 
 Rake::RDocTask.new { |rdoc|
diff --git a/activerecord/lib/active_record.rb b/activerecord/lib/active_record.rb
index 682fec2b4a..71f574680b 100755
--- a/activerecord/lib/active_record.rb
+++ b/activerecord/lib/active_record.rb
@@ -67,4 +67,4 @@ require 'active_record/connection_adapters/postgresql_adapter'
 require 'active_record/connection_adapters/sqlite_adapter'
 require 'active_record/connection_adapters/sqlserver_adapter'
 require 'active_record/connection_adapters/db2_adapter'
-require 'active_record/connection_adapters/oracle_adapter'
+require 'active_record/connection_adapters/oci_adapter'
diff --git a/activerecord/lib/active_record/connection_adapters/oci_adapter.rb b/activerecord/lib/active_record/connection_adapters/oci_adapter.rb
new file mode 100644
index 0000000000..eb966fa9bb
--- /dev/null
+++ b/activerecord/lib/active_record/connection_adapters/oci_adapter.rb
@@ -0,0 +1,259 @@
+# This is an Oracle adapter for the ActiveRecord persistence framework. It relies upon the OCI8
+# driver, which works with Oracle 8i and above. It was developed on Windows 2000
+# against an 8i database, using ActiveRecord 1.6.0 and OCI8 0.1.9. It has also been tested against
+# a 9i database.
+#
+# Implementation notes:
+# 1.  I had to redefine a method in ActiveRecord to make it possible to implement an autonumbering
+#     solution for oracle. It's implemented in a way that is intended to not break other adapters.
+# 2.  Default value support needs a patch to the OCI8 driver, to enable it to read LONG columns.
+#     LONG is deprecated, and so may never be properly added to driver.
+#     A similar patch is needed for TIMESTAMP.
+#     This is dangerous because it may break with newer versions of the driver.
+# 3.  Large Object support works by an after_save callback added to the ActiveRecord. This is not
+#     a problem - you can add other (chained) after_save callbacks.
+# 4.  LIMIT and OFFSET work by scrolling through a cursor - no rownum select from select required.
+#     It does mean that large OFFSETs will have to scroll through the intervening records. To keep
+#     consistency with other adapters I've allowed the LIMIT and OFFSET clauses to be included in
+#     the sql string and later extracted them by parsing the string.
+#
+# Usage notes:
+# 1.  Key generation uses a sequence "rails_sequence" for all tables. (I couldn't find a simple
+#     and safe way of passing table-specific sequence information to the adapter.)
+# 2.  Oracle uses DATE or TIMESTAMP datatypes for both dates and times. Consequently I have had to
+#     resort to some hacks to get data converted 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 nasty - but if you use Duck Typing you'll probably not care very much.
+#     In 9i it's tempting to map DATE to Date and TIMESTAMP to Time but I don't think that is
+#     valid - too many databases use DATE for both.
+#     Timezones and sub-second precision on timestamps are not supported.
+# 3.  Default values that are functions (such as "SYSDATE") are not supported. This is a
+#     restriction of the way active record supports default values.
+# 4.  Referential integrity constraints are not fully supported. Under at least
+#     some circumstances, active record appears to delete parent and child records out of
+#     sequence and out of transaction scope. (Or this may just be a problem of test setup.)
+#
+# Do what you want with this code, at your own peril, but if any significant portion of my code
+# remains then please acknowledge my contribution.
+# Copyright 2005 Graham Jenkins
+# $Revision: 1.2 $
+require 'active_record/connection_adapters/abstract_adapter'
+
+begin
+  require_library_or_gem 'oci8' unless self.class.const_defined? :OCI8
+
+  module ActiveRecord
+    module ConnectionAdapters
+      class OCIColumn < Column
+        attr_reader :sql_type
+
+        def initialize(name, default, limit, sql_type, scale)
+          @name, @limit, @sql_type, @scale, @sequence = name, limit, sql_type, scale
+          @type = simplified_type sql_type
+          @default = type_cast default
+        end
+
+        def simplified_type(field_type)
+          case field_type
+          when /char/i                          : :string
+          when /num|float|double|dec|real|int/i : @scale == 0 ? :integer : :float
+          when /date|time/i                     : @name =~ /_at$/ ? :time : :datetime
+          when /lob/i                           : :binary
+          end
+        end
+
+        def type_cast(value)
+          return nil if value.nil? || value =~ /^\s*null\s*$/i
+          case type
+          when :string   then value
+          when :integer  then value.to_i
+          when :float    then value.to_f
+          when :datetime then cast_to_date_or_time(value)
+          when :time     then cast_to_time(value)
+          else value
+          end
+        end
+
+        def cast_to_date_or_time(value)
+          return value if value.is_a? Date
+          guess_date_or_time (value.is_a? Time) ?
+            value : Time.local(*value.split(/\D+/).map{ |x| x.to_i })
+        end
+
+        def cast_to_time(value)
+          return value if value.is_a? Time
+          Time.local(2000, 1, 1,*value.split(/\D+/).map{ |x| x.to_i })
+        end
+
+        def guess_date_or_time(value)
+          (value.hour == 0 and value.min == 0 and value.sec == 0) ?
+            Date.new(value.year, value.month, value.day) : value
+        end
+      end
+
+      class OCIAdapter < AbstractAdapter
+        def quote_string(s)
+          s.gsub /'/, "''"
+        end
+
+        def quote(value, column = nil)
+          if column and column.type == :binary then %Q{empty_#{ column.sql_type }()}
+          else case value
+            when String     then %Q{'#{quote_string(value)}'}
+            when NilClass     then 'null'
+            when TrueClass    then '1'
+            when FalseClass   then '0'
+            when Numeric    then value.to_s
+            when Date, Time   then %Q{'#{value.strftime("%Y-%m-%d %H:%M:%S")}'}
+            else           %Q{'#{quote_string(value.to_yaml)}'}
+            end
+          end
+        end
+
+        def select_all(sql, name = nil)
+          offset = sql =~ /OFFSET (\d+)$/ ? $1.to_i : -1
+          sql, limit = $1, $2.to_i if sql =~ /(.*)(?: LIMIT[= ](\d+))(\s*OFFSET \d+)?$/
+          cursor = log(sql, name, @connection) { @connection.exec sql }
+          cols = cursor.get_col_names.map { |x| x.downcase }
+          rows = []
+          while row = cursor.fetch
+            next if cursor.row_count <= offset
+            hash = Hash.new
+            cols.each_with_index { |col, i|
+              hash[col] = case row[i]
+                when OCI8::LOB
+                  name == 'Writable Large Object' ? row[i]: row[i].read
+                when OraDate
+                  (row[i].hour == 0 and row[i].minute == 0 and row[i].second == 0) ?
+                    row[i].to_date : row[i].to_time
+                else row[i]
+                end
+            }
+            rows << hash
+            break if rows.size == limit
+          end
+          rows
+        ensure
+          cursor.close if cursor
+        end
+
+        def select_one(sql, name = nil)
+          result = select_all sql, name
+          result.size > 0 ? result.first : nil
+        end
+
+        def columns(table_name, name = nil)
+          cols = select_all(%Q{
+              select column_name, data_type, data_default, data_length, data_scale
+              from user_tab_columns where table_name = '#{table_name.upcase}'}
+          ).map { |row|
+            OCIColumn.new row['column_name'].downcase, row['data_default'],
+              row['data_length'], row['data_type'], row['data_scale']
+          }
+          cols
+        end
+
+        def insert(sql, name = nil, pk = nil, id_value = nil)
+          if pk.nil? # Who called us? What does the sql look like? No idea!
+            execute sql, name
+          elsif id_value # Pre-assigned id
+            log(sql, name, @connection) { @connection.exec sql }
+          else # Assume the sql contains a bind-variable for the id
+            id_value = select_one("select rails_sequence.nextval id from dual")['id']
+            log(sql, name, @connection) { @connection.exec sql, id_value }
+          end
+          id_value
+        end
+
+        def execute(sql, name = nil)
+          log(sql, name, @connection) { @connection.exec sql }
+        end
+
+        alias :update :execute
+        alias :delete :execute
+
+        def add_limit!(sql, limit)
+          sql << "LIMIT=" << limit.to_s
+        end
+
+        def begin_db_transaction()
+          @connection.autocommit = false
+        end
+
+        def commit_db_transaction()
+          @connection.commit
+        ensure
+          @connection.autocommit = true
+        end
+
+        def rollback_db_transaction()
+          @connection.rollback
+        ensure
+          @connection.autocommit = true
+        end
+
+        def adapter_name()
+          'OCI'
+        end
+      end
+    end
+  end
+
+  module ActiveRecord
+    class Base
+      def self.oci_connection(config)
+        conn = OCI8.new config[:username], config[:password], config[:host]
+        conn.exec %q{alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'}
+        conn.exec %q{alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS'}
+        conn.autocommit = true
+        ConnectionAdapters::OCIAdapter.new conn, logger
+      end
+
+      alias :attributes_with_quotes_pre_oci :attributes_with_quotes
+      # Enable the id column to be bound into the sql later, by the adapter's insert method.
+      # This is preferable to inserting the hard-coded value here, because the insert method
+      # needs to know the id value explicitly.
+      def attributes_with_quotes(creating = true)
+        aq = attributes_with_quotes_pre_oci creating
+        if connection.class == ConnectionAdapters::OCIAdapter
+          aq[self.class.primary_key] = ":id" if creating && aq[self.class.primary_key].nil?
+        end
+        aq
+      end
+
+      after_save :write_lobs
+
+      # After setting large objects to empty, select the OCI8::LOB and write back the data
+      def write_lobs()
+        if connection.class == ConnectionAdapters::OCIAdapter
+          self.class.columns.select { |c| c.type == :binary }.each { |c|
+            break unless value = self[c.name]
+            lob = connection.select_one(
+              "select #{ c.name} from #{ self.class.table_name } WHERE #{ self.class.primary_key} = #{quote(id)}",
+              'Writable Large Object'
+              )[c.name]
+            lob.write value
+          }
+        end
+      end
+
+      private :write_lobs
+    end
+  end
+
+  class OCI8
+    class Cursor
+      alias :define_a_column_pre_ar :define_a_column
+      def define_a_column(i)
+        case do_ocicall(@ctx) { @parms[i - 1].attrGet(OCI_ATTR_DATA_TYPE) }
+        when 8    : @stmt.defineByPos(i, String, 65535) # Read LONG values
+        when 187  : @stmt.defineByPos(i, OraDate) # Read TIMESTAMP values
+        else define_a_column_pre_ar i
+        end
+    	end
+    end
+  end
+rescue LoadError
+  # OCI8 driver is unavailable.
+end
\ No newline at end of file
diff --git a/activerecord/lib/active_record/connection_adapters/oracle_adapter.rb b/activerecord/lib/active_record/connection_adapters/oracle_adapter.rb
deleted file mode 100644
index 2cdb7ec3f7..0000000000
--- a/activerecord/lib/active_record/connection_adapters/oracle_adapter.rb
+++ /dev/null
@@ -1,244 +0,0 @@
-# Author: Maik Schmidt <contact@maik-schmidt.de>
-require 'active_record/connection_adapters/abstract_adapter'
-require 'date'
-
-begin
-
-  module ActiveRecord
-    class Base
-      # Establishes a connection to the database that's used by
-      # all Active Record objects
-      def self.oracle_connection(config) # :nodoc:
-        require 'oracle' unless self.class.const_defined?(:ORAconn)
-        symbolize_strings_in_hash(config)
-        usr = config[:username] || ''
-        pwd = config[:password] || ''
-        db = config[:database] || ''
-
-        connection = ORAconn.logon(usr, pwd, db)
-        cursor = connection.open
-        cursor.parse("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'")
-        rows_affected = cursor.exec
-        cursor.close
-        ConnectionAdapters::OracleAdapter.new(connection)
-      end
-    end
-
-    module ConnectionAdapters
-      class OracleAdapter < AbstractAdapter
-        def select_all(sql, name = nil)
-          select(sql, name)
-        end
-
-        def select_one(sql, name = nil)
-          select(sql, name).first
-        end
-
-        # Oracle does not support auto-generated columns, so we have to use
-        # sequences. Every insert is followed by a select statement, which
-        # returns the current sequence value. The statements are encapsulated
-        # in an anonymous PL/SQL block (supported since Oracle 7.3) to prevent
-        # race conditions and to maximize performance.
-        def insert(sql, name = nil, pk = nil, id_value = nil)
-          new_id = nil
-          if !id_value.nil?
-            execute(sql, name)
-            new_id = id_value
-          else
-            pk_col = pk || ActiveRecord::Base::primary_key
-            if sql !~ Regexp.new('\b' + pk_col + '\b')
-              seq_name = sql.sub(/^\s*insert\s+?into\s+(\S+).*$/im, '\1') + "_id"
-              sql.sub!(/(into\s+.*?\()/im, '\1' + "#{pk_col}, ")
-              sql.sub!(/(values\s*\()/im, '\1' + "#{seq_name}.nextval, ")
-              new_id = ' ' * 40 # Enough space for String representation of ID?
-              log(sql, name, @connection) do |connection| 
-                cursor = connection.open
-                s = "begin #{sql}; select #{seq_name}.currval into :new_id from dual; end;"
-                cursor.parse(s)
-                cursor.bindrv(':new_id', new_id)
-                cursor.exec
-                cursor.close
-                new_id = new_id.to_i
-              end
-            else
-              execute(sql, name)
-            end
-          end
-          new_id
-        end
-
-        def execute(sql, name = nil)
-          rows_affected = 0
-          log(sql, name, @connection) do |connection| 
-            cursor = connection.open
-            cursor.parse(sql)
-            rows_affected = cursor.exec
-            cursor.close
-          end
-          rows_affected
-        end
-
-        alias_method :update, :execute
-        alias_method :delete, :execute
-
-        def begin_db_transaction
-          @connection.commitoff
-        end
-
-        def commit_db_transaction
-          @connection.commit
-          @connection.commiton
-        end
-
-        def rollback_db_transaction
-          @connection.rollback
-          @connection.commiton
-        end
-
-        def quote_column_name(name) name; end
-
-        def quote(value, column = nil)
-          if column && column.type == :timestamp && value.class.to_s == "String"
-            begin
-              value = DateTime.parse(value)
-            rescue => ex
-              # Value cannot be parsed.
-            end
-          end
-
-          case value
-          when String
-            if column && column.type == :binary
-              "'#{quote_string(column.string_to_binary(value))}'" # ' (for ruby-mode)
-            else
-              "'#{quote_string(value)}'" # ' (for ruby-mode)
-            end
-          when NilClass              then "NULL"
-          when TrueClass             then (column && column.type == :boolean ? "'t'" : "1")
-          when FalseClass            then (column && column.type == :boolean ? "'f'" : "0")
-          when Float, Fixnum, Bignum then value.to_s
-          when Date                  then "to_date('#{value.strftime("%Y-%m-%d")}', 'YYYY-MM-DD')" 
-          when Time, DateTime        then "to_date('#{value.strftime("%Y-%m-%d %H:%M:%S")}', 'YYYY-MM-DD HH24:MI:SS')"
-          else                            "'#{quote_string(value.to_yaml)}'"
-          end
-        end
-
-        def quote_string(s)
-          s.gsub(/'/, "''") # ' (for ruby-mode)
-        end
-
-        def add_limit!(sql, limit)
-          l, o = limit.to_s.scan(/\d+/)
-          if o.nil?
-            sql.sub!(/^.*$/im, "select * from (#{sql}) where rownum <= #{l}")
-          else
-            raise ArgumentError, "LIMIT clauses with OFFSET are not supported yet!"
-          end
-        end
-
-        def columns(table_name, name = nil)
-          sql = <<-SQL
-          select column_name,
-                 data_type,
-                 data_length,
-                 data_precision,
-                 data_default
-          from   user_tab_columns
-          where  table_name = upper('#{table_name}')
-          SQL
-          result = []
-          cols = select_all(sql, name)
-          cols.each do |c|
-            name = c['column_name'].downcase
-            default = c['data_default']
-            default = (default == 'NULL') ? nil : default
-            type = get_sql_type(c['data_type'], c['data_length'])
-            result << Column.new(name, default, type)
-          end
-          result
-        end
-
-        private
-
-        def get_sql_type(type_name, type_length)
-          case type_name
-          when /timestamp/i
-            return "TIMESTAMP"
-          when /number/i
-            return "INT"
-          when /date/i
-            return "DATE"
-          else
-            return "#{type_name}(#{type_length})"
-          end
-        end
-
-        def select(sql, name = nil)
-          col_names = []
-          cursor = nil
-          log(sql, name, @connection) do |connection|
-            cursor = connection.open
-            col_names = parse(cursor, sql)
-            cursor.exec
-          end
-
-          rows = []
-          while cursor.fetch do
-            row = {}
-            col_names.each_with_index do |name, i|
-              row[name] = cursor.getCol(i + 1)[0]
-            end
-            rows << row
-          end
-          cursor.close
-          rows
-        end
-
-        VARCHAR2 = 1
-        NUMBER = 2
-        INTEGER = 3 ## external
-        FLOAT = 4   ## external
-        LONG = 8
-        ROWID = 11
-        DATE = 12
-        RAW = 23
-        LONG_RAW = 24
-        UNSIGNED_INT = 68 ## external
-        CHAR = 96
-        MLSLABEL = 105
-
-        def parse(cursor, sql)
-          cursor.parse(sql)
-          colnr = 1
-          col_names = []
-          loop {
-            colinfo = cursor.describe(colnr)
-            break if colinfo.nil?
-
-            col_names << colinfo[2].downcase
-            collength, coltype = colinfo[3], colinfo[1]
-
-            collength, coltype = case coltype
-            when NUMBER
-              [40, VARCHAR2]
-            when VARCHAR2, CHAR
-              [(collength * 1.5).ceil, VARCHAR2]
-            when LONG
-              [65535, LONG]
-            when LONG_RAW
-              [65535, LONG_RAW]
-            else
-              [collength, VARCHAR2]
-            end
-
-            cursor.define(colnr, collength, coltype)
-            colnr += 1
-          }
-          col_names
-        end
-      end
-    end
-  end
-rescue LoadError
-  # Oracle driver is unavailable.
-end
diff --git a/activerecord/test/connections/native_oci/connection.rb b/activerecord/test/connections/native_oci/connection.rb
new file mode 100644
index 0000000000..2d9006c231
--- /dev/null
+++ b/activerecord/test/connections/native_oci/connection.rb
@@ -0,0 +1,25 @@
+print "Using OCI Oracle\n"
+require 'fixtures/course'
+require 'logger'
+
+ActiveRecord::Base.logger = Logger.new STDOUT
+ActiveRecord::Base.logger.level = Logger::WARN
+
+db1 = 'activerecord_unittest'
+db2 = 'activerecord_unittest2'
+
+ActiveRecord::Base.establish_connection(
+  :adapter  => 'oci',
+  :host     => '',          # can use an oracle SID
+  :username => 'arunit',
+  :password => 'arunit',
+  :database => db1
+)
+
+Course.establish_connection(
+  :adapter  => 'oci',
+  :host     => '',          # can use an oracle SID
+  :username => 'arunit2',
+  :password => 'arunit2',
+  :database => db2
+)
diff --git a/activerecord/test/connections/native_oracle/connection.rb b/activerecord/test/connections/native_oracle/connection.rb
deleted file mode 100644
index 7e4cc5b574..0000000000
--- a/activerecord/test/connections/native_oracle/connection.rb
+++ /dev/null
@@ -1,24 +0,0 @@
-print "Using native Oracle\n"
-require 'fixtures/course'
-require 'logger'
-
-ActiveRecord::Base.logger = Logger.new("debug.log")
-
-db1 = 'local'
-db2 = 'local'
-
-ActiveRecord::Base.establish_connection(
-  :adapter  => "oracle",
-  :host     => "localhost",
-  :username => "arunit",
-  :password => "arunit",
-  :database => db1
-)
-
-Course.establish_connection(
-  :adapter  => "oracle",
-  :host     => "localhost",
-  :username => "arunit2",
-  :password => "arunit2",
-  :database => db2
-)
diff --git a/activerecord/test/fixtures/db_definitions/oci.drop.sql b/activerecord/test/fixtures/db_definitions/oci.drop.sql
new file mode 100644
index 0000000000..255e024531
--- /dev/null
+++ b/activerecord/test/fixtures/db_definitions/oci.drop.sql
@@ -0,0 +1,18 @@
+drop table accounts;
+drop table companies;
+drop table topics;
+drop table developers;
+drop table projects;
+drop table developers_projects;
+drop table customers;
+drop table movies;
+drop table subscribers;
+drop table booleantests;
+drop table auto_id_tests;
+drop table entrants;
+drop table colnametests;
+drop table mixins;
+drop table people;
+drop table binaries;
+drop table computers;
+drop sequence rails_sequence;
diff --git a/activerecord/test/fixtures/db_definitions/oci.sql b/activerecord/test/fixtures/db_definitions/oci.sql
new file mode 100644
index 0000000000..86d7a03165
--- /dev/null
+++ b/activerecord/test/fixtures/db_definitions/oci.sql
@@ -0,0 +1,167 @@
+create sequence rails_sequence minvalue 10000;
+
+create table companies (
+    id integer not null,
+    type varchar(50) default null,
+    ruby_type varchar(50) default null,
+    firm_id integer default null references companies initially deferred disable,
+    name varchar(50) default null,
+    client_of integer default null references companies initially deferred disable,
+    companies_count integer default 0,
+    rating integer default 1,
+    primary key (id)
+);
+
+create table accounts (
+    id integer not null,
+    firm_id integer default null references companies initially deferred disable,
+    credit_limit integer default null,
+    primary key (id)
+);
+
+create table topics (
+    id integer not null,
+    title varchar(255) default null,
+    author_name varchar(255) default null,
+    author_email_address varchar(255) default null,
+    written_on timestamp default null,
+    bonus_time timestamp default null,
+    last_read timestamp default null,
+    content varchar(4000),
+    approved integer default 1,
+    replies_count integer default 0,
+    parent_id integer references topics initially deferred disable,
+    type varchar(50) default null,
+    primary key (id)
+);
+-- try again for 8i
+create table topics (
+    id integer not null,
+    title varchar(255) default null,
+    author_name varchar(255) default null,
+    author_email_address varchar(255) default null,
+    written_on date default null,
+    bonus_time date default null,
+    last_read date default null,
+    content varchar(4000),
+    approved integer default 1,
+    replies_count integer default 0,
+    parent_id integer references topics initially deferred disable,
+    type varchar(50) default null,
+    primary key (id)
+);
+
+create table developers (
+    id integer not null,
+    name varchar(100) default null,
+    salary integer default 70000,
+    primary key (id)
+);
+
+create table projects (
+    id integer not null,
+    name varchar(100) default null,
+    primary key (id)
+);
+
+create table developers_projects (
+    developer_id integer not null references developers initially deferred disable,
+    project_id integer not null references projects initially deferred disable,
+    joined_on timestamp default null
+);
+-- Try again for 8i
+create table developers_projects (
+    developer_id integer not null references developers initially deferred disable,
+    project_id integer not null references projects initially deferred disable,
+    joined_on date default null
+);
+
+create table customers (
+    id integer not null,
+    name varchar(100) default null,
+    balance integer default 0,
+    address_street varchar(100) default null,
+    address_city varchar(100) default null,
+    address_country varchar(100) default null,
+    primary key (id)
+);
+
+create table movies (
+    movieid integer not null,
+    name varchar(100) default null,
+    primary key (movieid)
+);
+
+create table subscribers (
+    nick varchar(100) not null,
+    name varchar(100) default null,
+    primary key (nick)
+);
+
+create table booleantests (
+    id integer not null,
+    value integer default null,
+    primary key (id)
+);
+
+create table auto_id_tests (
+    auto_id integer not null,
+    value integer default null,
+    primary key (auto_id)
+);
+
+create table entrants (
+    id integer not null primary key,
+    name varchar(255) not null,
+    course_id integer not null
+);
+
+create table colnametests (
+    id integer not null,
+    references integer not null,
+    primary key (id)
+);
+
+create table mixins (
+    id integer not null,
+    parent_id integer default null references mixins initially deferred disable,
+    type varchar(40) default null,
+    pos integer default null,
+    lft integer default null,
+    rgt integer default null,
+    root_id integer default null,
+    created_at timestamp default null,
+    updated_at timestamp default null,
+    primary key (id)
+);
+-- try again for 8i
+create table mixins (
+    id integer not null,
+    parent_id integer default null references mixins initially deferred disable,
+    type varchar(40) default null,
+    pos integer default null,
+    lft integer default null,
+    rgt integer default null,
+    root_id integer default null,
+    created_at date default null,
+    updated_at date default null,
+    primary key (id)
+);
+
+create table people (
+    id integer not null,
+    first_name varchar(40) null,
+    lock_version integer default 0,
+    primary key (id)
+);
+
+create table binaries (
+    id integer not null,
+    data blob null,
+    primary key (id)
+);
+
+create table computers (
+  id integer not null primary key,
+  developer integer not null references developers initially deferred disable
+);
diff --git a/activerecord/test/fixtures/db_definitions/oci2.drop.sql b/activerecord/test/fixtures/db_definitions/oci2.drop.sql
new file mode 100644
index 0000000000..7277a52552
--- /dev/null
+++ b/activerecord/test/fixtures/db_definitions/oci2.drop.sql
@@ -0,0 +1,2 @@
+drop table courses;
+drop sequence rails_sequence;
diff --git a/activerecord/test/fixtures/db_definitions/oci2.sql b/activerecord/test/fixtures/db_definitions/oci2.sql
new file mode 100644
index 0000000000..5ac62b0062
--- /dev/null
+++ b/activerecord/test/fixtures/db_definitions/oci2.sql
@@ -0,0 +1,6 @@
+create sequence rails_sequence minvalue 10000;
+
+create table courses (
+  id int not null primary key,
+  name varchar(255) not null
+);
diff --git a/activerecord/test/fixtures/db_definitions/oracle.sql b/activerecord/test/fixtures/db_definitions/oracle.sql
deleted file mode 100644
index 31e66e8532..0000000000
--- a/activerecord/test/fixtures/db_definitions/oracle.sql
+++ /dev/null
@@ -1,149 +0,0 @@
-CREATE TABLE accounts (
-  id int NOT NULL,
-  firm_id int default NULL,
-  credit_limit int default NULL,
-  PRIMARY KEY (id)
-);
-CREATE SEQUENCE accounts_id MINVALUE 10000;
-
-CREATE TABLE companies (
-  id int NOT NULL,
-  type varchar(50) default NULL,
-  ruby_type varchar(50) default NULL,
-  firm_id int default NULL,
-  name varchar(50) default NULL,
-  client_of int default NULL,
-  rating int default 1,
-  PRIMARY KEY (id)
-);
-CREATE SEQUENCE companies_id MINVALUE 10000;
-
-CREATE TABLE topics (
-  id int NOT NULL,
-  title varchar(255) default NULL,
-  author_name varchar(255) default NULL,
-  author_email_address varchar(255) default NULL,
-  written_on timestamp default NULL,
-  bonus_time timestamp default NULL,
-  last_read date default NULL,
-  content varchar(3000),
-  approved smallint default 1,
-  replies_count int default 0,
-  parent_id int default NULL,
-  type varchar(50) default NULL,
-  PRIMARY KEY (id)
-);
-CREATE SEQUENCE topics_id MINVALUE 10000;
-
-CREATE TABLE developers (
-  id int NOT NULL,
-  name varchar(100) default NULL,
-  salary int default 70000,
-  PRIMARY KEY (id)
-);
-CREATE SEQUENCE developers_id MINVALUE 10000;
-
-CREATE TABLE projects (
-  id int NOT NULL,
-  name varchar(100) default NULL,
-  PRIMARY KEY (id)
-);
-CREATE SEQUENCE projects_id MINVALUE 10000;
-
-CREATE TABLE developers_projects (
-  developer_id int NOT NULL,
-  project_id int NOT NULL,
-  joined_on date default NULL
-);
-CREATE SEQUENCE developers_projects_id MINVALUE 10000;
-
-CREATE TABLE customers (
-  id int NOT NULL,
-  name varchar(100) default NULL,
-  balance int default 0,
-  address_street varchar(100) default NULL,
-  address_city varchar(100) default NULL,
-  address_country varchar(100) default NULL,
-  PRIMARY KEY (id)
-);
-CREATE SEQUENCE customers_id MINVALUE 10000;
-
-CREATE TABLE movies (
-  movieid int NOT NULL,
-  name varchar(100) default NULL,
-  PRIMARY KEY (movieid)
-);
-CREATE SEQUENCE movies_id MINVALUE 10000;
-
-CREATE TABLE subscribers (
-  nick varchar(100) NOT NULL,
-  name varchar(100) default NULL,
-  PRIMARY KEY (nick)
-);
-CREATE SEQUENCE subscribers_id MINVALUE 10000;
-
-CREATE TABLE booleantests (
-  id int NOT NULL,
-  value int default NULL,
-  PRIMARY KEY (id)
-);
-CREATE SEQUENCE booleantests_id MINVALUE 10000;
-
-CREATE TABLE auto_id_tests (
-  auto_id int NOT NULL,
-  value int default NULL,
-  PRIMARY KEY (auto_id)
-);
-CREATE SEQUENCE auto_id_tests_id MINVALUE 10000;
-
-CREATE TABLE entrants (
-  id int NOT NULL PRIMARY KEY,
-  name varchar(255) NOT NULL,
-  course_id int NOT NULL
-);
-CREATE SEQUENCE entrants_id MINVALUE 10000;
-
-CREATE TABLE colnametests (
-  id int NOT NULL,
-  references int NOT NULL,
-  PRIMARY KEY (id)
-);
-CREATE SEQUENCE colnametests_id MINVALUE 10000;
-
-CREATE TABLE mixins (
-  id int NOT NULL,
-  parent_id int default NULL,
-  pos int default NULL,
-  created_at timestamp default NULL,
-  updated_at timestamp default NULL,
-  lft int default NULL,
-  rgt int default NULL,
-  root_id int default NULL,
-  type varchar(40) default NULL,
-  PRIMARY KEY (id)
-);
-CREATE SEQUENCE mixins_id MINVALUE 10000;
-
-CREATE TABLE people (
-  id int NOT NULL,
-  first_name varchar(40) NOT NULL,
-  lock_version int default 0,
-  PRIMARY KEY  (id)
-);
-CREATE SEQUENCE people_id MINVALUE 10000;
-
-CREATE TABLE binaries (
-  id int NOT NULL,
-  data blob,
-  PRIMARY KEY  (id)
-);
-CREATE SEQUENCE binaries_id MINVALUE 10000;
-
-CREATE TABLE computers (
-  id int,
-  developer int NOT NULL,
-  PRIMARY KEY (id)
-);
-CREATE SEQUENCE computers_id MINVALUE 10000;
-
-EXIT;
diff --git a/activerecord/test/fixtures/db_definitions/oracle2.sql b/activerecord/test/fixtures/db_definitions/oracle2.sql
deleted file mode 100644
index e0bfde022c..0000000000
--- a/activerecord/test/fixtures/db_definitions/oracle2.sql
+++ /dev/null
@@ -1,7 +0,0 @@
-CREATE TABLE courses (
-  id int NOT NULL PRIMARY KEY,
-  name varchar(255) NOT NULL
-);
-CREATE SEQUENCE courses_id MINVALUE 10000;
-
-EXIT;