From 10d458b0aadc9f44bfadda6e59d08be8ea661029 Mon Sep 17 00:00:00 2001 From: David Heinemeier Hansson Date: Wed, 23 Feb 2005 13:34:57 +0000 Subject: 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 --- activerecord/CHANGELOG | 33 +-- activerecord/Rakefile | 6 + activerecord/lib/active_record.rb | 2 +- .../connection_adapters/oci_adapter.rb | 259 +++++++++++++++++++++ .../connection_adapters/oracle_adapter.rb | 244 ------------------- .../test/connections/native_oci/connection.rb | 25 ++ .../test/connections/native_oracle/connection.rb | 24 -- .../test/fixtures/db_definitions/oci.drop.sql | 18 ++ activerecord/test/fixtures/db_definitions/oci.sql | 167 +++++++++++++ .../test/fixtures/db_definitions/oci2.drop.sql | 2 + activerecord/test/fixtures/db_definitions/oci2.sql | 6 + .../test/fixtures/db_definitions/oracle.sql | 149 ------------ .../test/fixtures/db_definitions/oracle2.sql | 7 - 13 files changed, 502 insertions(+), 440 deletions(-) create mode 100644 activerecord/lib/active_record/connection_adapters/oci_adapter.rb delete mode 100644 activerecord/lib/active_record/connection_adapters/oracle_adapter.rb create mode 100644 activerecord/test/connections/native_oci/connection.rb delete mode 100644 activerecord/test/connections/native_oracle/connection.rb create mode 100644 activerecord/test/fixtures/db_definitions/oci.drop.sql create mode 100644 activerecord/test/fixtures/db_definitions/oci.sql create mode 100644 activerecord/test/fixtures/db_definitions/oci2.drop.sql create mode 100644 activerecord/test/fixtures/db_definitions/oci2.sql delete mode 100644 activerecord/test/fixtures/db_definitions/oracle.sql delete mode 100644 activerecord/test/fixtures/db_definitions/oracle2.sql 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 -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; -- cgit v1.2.3