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 --- .../connection_adapters/oci_adapter.rb | 259 +++++++++++++++++++++ .../connection_adapters/oracle_adapter.rb | 244 ------------------- 2 files changed, 259 insertions(+), 244 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 (limited to 'activerecord/lib/active_record/connection_adapters') 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 -- cgit v1.2.3