# # $Id: odbc_adapter.rb,v 1.8 2008/04/23 15:17:44 source Exp $ # # OpenLink ODBC Adapter for Ruby on Rails # Copyright (C) 2006 OpenLink Software # # Permission is hereby granted, free of charge, to any person obtaining # a copy of this software and associated documentation files (the # "Software"), to deal in the Software without restriction, including # without limitation the rights to use, copy, modify, merge, publish, # distribute, sublicense, and/or sell copies of the Software, and to # permit persons to whom the Software is furnished to do so, subject # to the following conditions: # # The above copyright notice and this permission notice shall be # included in all copies or substantial portions of the Software. # # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, # EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF # MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. # IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR # ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF # CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION # WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # require 'active_record/connection_adapters/abstract_adapter' require 'arel/visitors/bind_visitor' begin require 'odbc' unless self.class.const_defined?(:ODBC) #------------------------------------------------------------------------- module ActiveRecord class Base # :nodoc: def self.odbc_connection(config) #:nodoc: config = config.symbolize_keys if config.has_key?(:dsn) dsn = config[:dsn] username = config[:username] ? config[:username].to_s : nil password = config[:password] ? config[:password].to_s : nil elsif config.has_key?(:conn_str) connstr = config[:conn_str] else raise ActiveRecordError, "No data source name (:dsn) or connection string (:conn_str) specified." end trace = config[:trace] || false conv_num_lits = config[:convert_numeric_literals] || false emulate_bools = config[:emulate_booleans] || false if config.has_key?(:dsn) # Connect using dsn, username, password conn = ODBC::connect(dsn, username, password) conn_opts = { :dsn => dsn, :username => username, :password => password, :trace => trace, :conv_num_lits => conv_num_lits, :emulate_booleans => emulate_bools } else # Connect using ODBC connection string # - supports DSN-based or DSN-less connections # e.g. "DSN=virt5;UID=rails;PWD=rails" # "DRIVER={OpenLink Virtuoso};HOST=carlmbp;UID=rails;PWD=rails" connstr_keyval_pairs = connstr.split(';') driver = ODBC::Driver.new driver.name = 'odbc' driver.attrs = {} connstr_keyval_pairs.each do |pair| attr = pair.split('=') driver.attrs[attr[0]] = attr[1] if attr.length.eql?(2) end conn = ODBC::Database.new.drvconnect(driver) conn_opts = { :conn_str => config[:conn_str], :driver => driver, :trace => trace, :conv_num_lits => conv_num_lits, :emulate_booleans => emulate_bools } end conn.autocommit = true ConnectionAdapters::ODBCAdapter.new(conn, conn_opts, logger) end end # class Base module ConnectionAdapters # :nodoc: # This is an ODBC adapter for the ActiveRecord framework. # # The ODBC adapter requires the Ruby ODBC module (version 0.9991 or # later), available from http://raa.ruby-lang.org/project/ruby-odbc # # == Status # # === 23-Apr-2008 # # Adapter updated to support Rails 2.0.2 / ActiveRecord 2.0.2. # Added support for DSN-less connections (thanks to Ralf Vitasek). # Added support for SQLAnywhere (thanks to Bryan Lahartinger). # # === 27-Feb-2007 # # Adapter updated to support Rails 1.2.x / ActiveRecord 1.15.x. # Support added for AR :decimal type and :emulate_booleans connection # option introduced. # # === 09-Jan-2007 # # The current adapter supports Ingres r3, Informix 9.3 or later, # Virtuoso (Open-Source Edition) 4.5, Oracle 10g, MySQL 5, # SQL Server 2000, Sybase ASE 15, DB2 v9, Progress 9/10 (SQL-92 engine), # Progress 8 (SQL-89 engine) and PostgreSQL 8.2 # # == Testing Environments # # The adapter has been tested in the following environments: # * Windows XP, Linux Fedora Core, Mac OS X # The iODBC Driver Manager was used on Linux and Mac OS X. # # Databases supported using OpenLink ODBC drivers: # * Informix, Ingres, Oracle, MySQL, SQL Server, Sybase, DB2, Progress, # PostgreSQL # Databases supported using the database's own native ODBC driver: # * Virtuoso, MySQL, Informix # # === Note # * OpenLink ODBC drivers work with v0.998 or later of the Ruby ODBC # bridge. # * The native MySQL driver requires v0.9991 of the Ruby ODBC bridge. # # == Information # # More information can be found at: # * http://rubyforge.org/projects/odbc-rails/ # * http://odbc-rails.openlinksw.com # * http://sourceforge.net/projects/virtuoso/ # # Maintainer: Carl Blakeley (mailto:cblakeley@openlinksw.co.uk) # # == Connection Options # # The following options are supported by the ODBC adapter. # # :dsn:: # Specifies the ODBC data source name. # :username:: # Specifies the database user. # :password:: # Specifies the database password. # :conn_str:: # Specifies an ODBC-style connection string. # e.g. # "DSN=virt5;UID=rails;PWD=rails" or # "DRIVER={OpenLink Virtuoso};HOST=carlmbp;UID=rails;PWD=rails" # Use either a) :dsn, :username and :password or b) :conn_str # The :conn_str option in combination with the DRIVER keyword # supports DSN-less connections. # :trace:: # If set to true, turns on simple call tracing to the log file # referenced by ActiveRecord::Base.logger. If omitted, :trace # defaults to false. (We also suggest setting # ActiveRecord::Base.colorize_logging = false). # :convert_numeric_literals:: # If set to true, suppresses quoting of numeric literals. # If omitted, :convert_numeric_literals defaults to # false. # :emulate_booleans:: # Instructs the adapter to interpret certain numeric column types as # holding boolean, rather than numeric, data. It is intended for use # with databases which do not have a native boolean data type. # If omitted, :emulate_booleans defaults to false. # # == Usage Notes # === Informix # In order to match the formats of Ruby's Date, Time and DateTime types, # the following settings for Informix were used: # * DBDATE=Y4MD- # * DBTIME=%Y-%m-%d %H:%M:%S # To support mixed-case/quoted table names: # * DELIMIDENT=y # To allow embedded newlines in quoted strings: # * set ALLOW_NEWLINE=1 in the ONCONFIG configuration file. # # The adapter relies on an ODBC extension to SQLGetStmtOption implemented # by some ODBC drivers (SQL_LASTSERIAL=1049) to retrieve the primary key # value auto-generated by an insert into a SERIAL column. # # === Ingres # To match the formats of Ruby's Time and DateTime types, # the following settings for Ingres were used: # * II_DATE_FORMAT=SWEDEN # # === Oracle # If using an OpenLink Oracle driver or agent, the 'jetfix' configuration # option must be enabled to obtain the correct type mappings. # # === Sybase # Set the connection option :convert_numeric_literals to true to # avoid errors similar to: # "Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed." # # :boolean columns use the BIT SQL type, which does not allow nulls or # indexes. If a DEFAULT is not specified for #create_table, the # column will be declared with DEFAULT 0. # # Migrations are supported, but for ALTER TABLE commands to # work, the database must have the database option 'select into' set to # 'true' with sp_dboption. # # === DB2 # Set the connection option :convert_numeric_literals to true to # avoid errors similar to: # "The data types of the operands for the operation "=" are not compatible." # # To obtain the correct type mappings, ensure LongDataCompat is set to 1 # in the file db2cli.ini included in the DB2 client. # # Migrations are supported but the following methods are not # implemented because of lack of support in DB2 SQL. # * change_column, remove_column, rename_column # # === Progress 9/10 with SQL-92 engine # Connections to Progress v9 and above are assumed to be to the SQL-92 # engine. Migrations are supported but the following methods are not # implemented because of lack of support in Progress SQL. # * rename_table, change_column, remove_column, rename_column # # === Progress 8 with SQL-89 engine # Set the connection option :convert_numeric_literals to true # to avoid errors similar to: # "Incompatible data types in expression or assignment. (223)" # # Migrations are supported but the following methods are not # implemented because of lack of support in Progress SQL. # * rename_table, change_column, remove_column, rename_column # class ODBCAdapter < AbstractAdapter #------------------------------------------------------------------- # DbmsInfo holds DBMS-dependent information which cannot be derived # satisfactorily through ODBC class DbmsInfo # :nodoc: all private_class_method :new @@dbmsInfo = nil @@dbms_lookup_tbl = { # Uses dbmsName as key and dbmsMajorVer as a subkey. :db2 => { :any_version => { :primary_key => "INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 10000) PRIMARY KEY", :has_autoincrement_col => true, :supports_migrations => true, :supports_schema_names => true, :supports_count_distinct => true, :boolean_col_surrogate => "DECIMAL(1)" } }, :informix => { :any_version => { :primary_key => "SERIAL PRIMARY KEY", :has_autoincrement_col => true, :supports_migrations => true, :supports_schema_names => true, :supports_count_distinct => true, # This data adapter automatically maps ODBC::SQL_BIT to # :boolean. So, the following is unnecessary if the ODBC # driver maps the native BOOLEAN type available in # Informix 9.x to ODBC::SQL_BIT in SQLGetTypeInfo. :boolean_col_surrogate => "SMALLINT" } }, :ingres => { :any_version => { :primary_key => "INTEGER PRIMARY KEY NOT NULL", :has_autoincrement_col => false, :supports_migrations => true, :supports_schema_names => true, :supports_count_distinct => true, :boolean_col_surrogate => "INTEGER1" } }, :microsoftsqlserver => { :any_version => { :primary_key => "INT NOT NULL IDENTITY(1, 1) PRIMARY KEY", :has_autoincrement_col => true, :supports_migrations => true, :supports_schema_names => true, :supports_count_distinct => true, # boolean_col_surrogate not necessary. # SQL Server's BIT data type is mapped to ODBC::SQL_BIT/:boolean. :boolean_col_surrogate => nil }, 8 => { :primary_key => "INT NOT NULL IDENTITY(1, 1) PRIMARY KEY", :has_autoincrement_col => true, :supports_migrations => true, :supports_schema_names => true, :supports_count_distinct => true, # boolean_col_surrogate not necessary. # SQL Server's BIT data type is mapped to ODBC::SQL_BIT/:boolean. :boolean_col_surrogate => nil } }, :mysql => { :any_version => { :primary_key => "INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY", :has_autoincrement_col => true, :supports_migrations => true, :supports_schema_names => false, :supports_count_distinct => true, :boolean_col_surrogate => "TINYINT" }, 5 => { :primary_key => "INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY", :has_autoincrement_col => true, :supports_migrations => true, :supports_schema_names => false, :supports_count_distinct => true, :boolean_col_surrogate => "TINYINT" } }, :oracle => { :any_version => { :primary_key => "NUMBER(10) PRIMARY KEY NOT NULL", :has_autoincrement_col => false, :supports_migrations => true, :supports_schema_names => true, :supports_count_distinct => true, :boolean_col_surrogate => "NUMBER(1)" } }, :postgresql => { :any_version => { :primary_key => "SERIAL PRIMARY KEY", :has_autoincrement_col => true, :supports_migrations => true, :supports_schema_names => false, :supports_count_distinct => true, # boolean_col_surrogate not necessary. # PostgreSQL's BOOL data type is mapped to ODBC::SQL_BIT/:boolean. :boolean_col_surrogate => nil } }, :progress => { :any_version => { :primary_key => "INTEGER NOT NULL PRIMARY KEY", :has_autoincrement_col => false, :supports_migrations => true, :supports_schema_names => true, :supports_count_distinct => true, # boolean_col_surrogate not necessary. # Progress SQL-92's BIT data type is mapped to ODBC::SQL_BIT/:boolean. :boolean_col_surrogate => nil } }, :progress89 => { :any_version => { :primary_key => "INTEGER NOT NULL UNIQUE", :has_autoincrement_col => false, :supports_migrations => true, :supports_schema_names => false, :supports_count_distinct => true, # boolean_col_surrogate not necessary. # Progress SQL-89's LOGICAL data type is mapped to ODBC::SQL_BIT/:boolean. :boolean_col_surrogate => nil } }, :sybase => { :any_version => { :primary_key => "INT IDENTITY PRIMARY KEY", :has_autoincrement_col => true, :supports_migrations => true, :supports_schema_names => true, :supports_count_distinct => true, # boolean_col_surrogate not necessary. # Sybase's BIT data type is mapped to ODBC::SQL_BIT/:boolean. :boolean_col_surrogate => nil } }, :sqlanywhere => { :any_version => { :primary_key => "INTEGER PRIMARY KEY DEFAULT AUTOINCREMENT", :has_autoincrement_col => true, :supports_migrations => true, :supports_schema_names => true, :supports_count_distinct => true, :boolean_col_surrogate => "TINYINT" } }, :virtuoso => { :any_version => { :primary_key => "INT NOT NULL IDENTITY PRIMARY KEY", :has_autoincrement_col => true, :supports_migrations => true, :supports_schema_names => true, :supports_count_distinct => true, :boolean_col_surrogate => "SMALLINT" } }, :advantage => { :any_version => { :primary_key => "INT NOT NULL IDENTITY PRIMARY KEY", :has_autoincrement_col => true, :supports_migrations => true, :supports_schema_names => false, :supports_count_distinct => true, :boolean_col_surrogate => "SMALLINT" } }, :pervasive => { :any_version => { :primary_key => "INT NOT NULL IDENTITY PRIMARY KEY", :has_autoincrement_col => true, :supports_migrations => true, :supports_schema_names => false, :supports_count_distinct => true, :boolean_col_surrogate => "SMALLINT" } } } def self.create @@dbmsInfo = new unless @@dbmsInfo @@dbmsInfo end def get_info(dbms_name, dbms_major_ver, info_type) if (val = @@dbms_lookup_tbl[dbms_name]) then if (val = val[dbms_major_ver] || val = val[:any_version]) then val = val[info_type] end end if val.nil? then raise ActiveRecordError, "Lookup for #{info_type} failed" end val end end # class DbmsInfo #--------------------------------------------------------------------- # DSInfo holds SQLGetInfo responses from the data source class DSInfo # :nodoc: all attr_reader :info # Specifies the miniminum information we need about the data source @@baseInfo = [ ODBC::SQL_DBMS_NAME, ODBC::SQL_DBMS_VER, ODBC::SQL_IDENTIFIER_CASE, ODBC::SQL_QUOTED_IDENTIFIER_CASE, ODBC::SQL_IDENTIFIER_QUOTE_CHAR, ODBC::SQL_MAX_IDENTIFIER_LEN, ODBC::SQL_MAX_TABLE_NAME_LEN, ODBC::SQL_USER_NAME, ODBC::SQL_DATABASE_NAME ] def initialize(connection) @connection = connection @info = Hash.new @@baseInfo.each { |i| @info[i] = nil } getBaseInfo(@info) # TODO: HACK! OpenLink's Progress ODBC driver reports # SQL_IDENTIFIER_CASE as SQL_IC_MIXED, but it should be # SQL_IC_UPPER. All the driver's ODBC catalog calls return # identifiers in uppercase. @info[ODBC::SQL_IDENTIFIER_CASE] = ODBC::SQL_IC_UPPER if @info[ODBC::SQL_DBMS_NAME] =~ /progress/i end private def getBaseInfo(infoTypes) infoTypes.each_key do |infoType| begin infoTypes[infoType] = @connection.get_info(infoType) rescue ODBC::Error end end end end # class DSInfo class BindSubstitution < Arel::Visitors::ToSql # :nodoc: include Arel::Visitors::BindVisitor end #--------------------------------------------------------------------- # ODBC constants missing from Christian Werner's Ruby ODBC driver SQL_NO_NULLS = 0 # :nodoc: SQL_NULLABLE = 1 # :nodoc: SQL_NULLABLE_UNKNOWN = 2 # :nodoc: # dbInfo: ref to DSInfo instance attr_reader :dsInfo # :nodoc: # The name of DBMS currently connected to. # # Different ODBC drivers might return different names for the same # DBMS; so similar names are mapped to the same symbol. # _dbmsName_ is the SQL_DBMS_NAME returned by ODBC, downcased with # whitespace removed. e.g. informix, ingres, # microsoftsqlserver etc. attr_reader :dbmsName # Emulate boolean columns if the database doesn't have a native BOOLEAN type. attr_reader :emulate_booleans # Supports lookups of DBMS-dependent information/settings which # cannot be derived satisfactorily through ODBC @@dbmsLookups = DbmsInfo.create @@trace = nil #-- def initialize(connection, connection_options, logger = nil) @@trace = connection_options[:trace] && logger if !@@trace # Mixins in odbcext_xxx.rb included using Object#extend can't access # @@trace. Why? # (Error message "NameError: uninitialized class variable @@trace".) # So create an equivalent instance variable @trace = @@trace super(connection, logger) @logger.unknown("ODBCAdapter#initialize>") if @@trace @connection, @connection_options = connection, connection_options @convert_numeric_literals = connection_options[:conv_num_lits] @emulate_booleans = connection_options[:emulate_booleans] # Caches SQLGetInfo output @dsInfo = DSInfo.new(connection) # Caches SQLGetTypeInfo output @typeInfo = nil # Caches mapping of Rails abstract data types to DBMS native types. @abstract2NativeTypeMap = nil @visitor = BindSubstitution.new self # Set @dbmsName and @dbmsMajorVer from SQLGetInfo output. # Each ODBCAdapter instance is associated with only one connection, # so using ODBCAdapter instance variables for DBMS name and version # is OK. @dbmsMajorVer = @dsInfo.info[ODBC::SQL_DBMS_VER].split('.')[0].to_i @dbmsName = @dsInfo.info[ODBC::SQL_DBMS_NAME].downcase.gsub(/\s/,'') # Different ODBC drivers might return different names for the same # DBMS. So map similar names to the same symbol. @dbmsName = dbmsNameToSym(@dbmsName, @dbmsMajorVer) # Now we know which DBMS we're connected to, extend this ODBCAdapter # instance with the appropriate DBMS specific extensions @odbcExtFile = "active_record/vendor/odbcext_#{@dbmsName}" begin require "#{@odbcExtFile}" self.extend ODBCExt rescue MissingSourceFile puts "ODBCAdapter#initialize> Couldn't find extension #{@odbcExtFile}.rb" end end #-- # ABSTRACT ADAPTER OVERRIDES ======================================= # # see abstract_adapter.rb # Returns the human-readable name of the adapter. def adapter_name @logger.unknown("ODBCAdapter#adapter_name>") if @@trace 'ODBC' end # Does this adapter support migrations? def supports_migrations? @logger.unknown("ODBCAdapter#supports_migrations?>") if @@trace @@dbmsLookups.get_info(@dbmsName, @dbmsMajorVer, :supports_migrations) end # Does the database support COUNT(DISTINCT) queries? # e.g. select COUNT(DISTINCT ArtistID) from CDs def supports_count_distinct? @logger.unknown("ODBCAdapter#supports_count_distinct?>") if @@trace @@dbmsLookups.get_info(@dbmsName, @dbmsMajorVer, :supports_count_distinct) end # Should primary key values be selected from their corresponding # sequence before the insert statement? If true, #next_sequence_value # is called before each insert to set the record's primary key. def prefetch_primary_key?(table_name = nil) @logger.unknown("ODBCAdapter#prefetch_primary_key?>") if @@trace # Return true for any DBMS which can't support #last_insert_id. # i.e. doesn't support an autoincrement column type. An # implementation of #next_sequence_value must be provided for any # such database. !@@dbmsLookups.get_info(@dbmsName, @dbmsMajorVer, :has_autoincrement_col) end # Returns true if this connection active. def active? @logger.unknown("ODBCAdapter#active?>") if @@trace @connection.connected? end # Reconnects to the database. def reconnect! @logger.unknown("ODBCAdapter#reconnect!>") if @@trace @connection.disconnect if @connection.connected? if @connection_options.has_key?(:dsn) @connection = ODBC::connect(@connection_options[:dsn], @connection_options[:username], @connection_options[:password]) else @connection = ODBC::Database.new.drvconnect(@connection_options[:driver]) end # There's no need to refresh the data source info in @dsInfo because # we're reconnecting to the same data source. rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Disconnects from the database. def disconnect! @logger.unknown("ODBCAdapter#disconnect!>") if @@trace @connection.disconnect if @connection.connected? rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end #-- # QUOTING OVERRIDES ================================================ # # see: abstract/quoting.rb # Quotes the column value #-- # to help prevent {SQL injection attacks}[http://en.wikipedia.org/wiki/SQL_injection]. #++ def quote(value, column = nil) @logger.unknown("ODBCAdapter#quote>") if @@trace @logger.unknown("args=[#{value}]") if @@trace case value when String, ActiveSupport::Multibyte::Chars value = value.to_s if column && column.type == :binary && self.respond_to?(:string_to_binary) "'#{string_to_binary(value)}'" elsif (column && [:integer, :float].include?(column.type)) value = column.type == :integer ? value.to_i : value.to_f value.to_s elsif (column.nil? && @convert_numeric_literals && (value =~ /^[-+]?[0-9]+[.]?[0-9]*([eE][-+]?[0-9]+)?$/)) value else "'#{quote_string(value)}'" # ' (for ruby-mode) end when NilClass then "NULL" when TrueClass then (column && column.type == :integer ? '1' : quoted_true) when FalseClass then (column && column.type == :integer ? '0' : quoted_false) when Float, Fixnum, Bignum then value.to_s else if value.acts_like?(:date) || value.acts_like?(:time) quoted_date(value) else super end end rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Quotes a string, escaping any ' (single quote) and \ (backslash) # characters. def quote_string(string) @logger.unknown("ODBCAdapter#quote_string>") if @@trace @logger.unknown("args=[#{string}]") if @@trace string.gsub(/\'/, "''") end # Returns a quoted form of the column name. def quote_column_name(name) @logger.unknown("ODBCAdapter#quote_column_name>") if @@trace @logger.unknown("args=[#{name}]") if @@trace name = name.to_s if name.class == Symbol idQuoteChar = @dsInfo.info[ODBC::SQL_IDENTIFIER_QUOTE_CHAR] return name if !idQuoteChar || ((idQuoteChar = idQuoteChar.strip).length == 0) idQuoteChar = idQuoteChar[0] # Avoid quoting any already quoted name return name if name[0] == idQuoteChar && name[-1] == idQuoteChar # If DBMS's SQL_IDENTIFIER_CASE = SQL_IC_UPPER, only quote mixed # case names. # See #dbmsIdentCase for the identifier case conventions used by this # adapter. if @dsInfo.info[ODBC::SQL_IDENTIFIER_CASE] == ODBC::SQL_IC_UPPER return name unless (name =~ /([A-Z]+[a-z])|([a-z]+[A-Z])/) end idQuoteChar.chr + name + idQuoteChar.chr end def quote_table_name(name) @logger.unknown("ODBCAdapter#quote_table_name>") if @trace @logger.unknown("args=[#{name}]") if @trace quote_column_name(name) end def quoted_true @logger.unknown("ODBCAdapter#quoted_true>") if @@trace '1' end def quoted_false @logger.unknown("ODBCAdapter#quoted_false>") if @@trace '0' end def quoted_date(value) @logger.unknown("ODBCAdapter#quoted_date>") if @@trace @logger.unknown("args=[#{value}]") if @@trace # abstract_adapter's #quoted_date uses value.to_s(:db), but this # doesn't differentiate between pure dates (Date) and date/time # composites (Time and DateTime). # :db format string defaults to '%Y-%m-%d %H:%M:%S' and is defined # in ActiveSupport::CoreExtensions::Time::Conversions::DATE_FORMATS # Ideally, we'd return an ODBC date or timestamp literal escape # sequence, but not all ODBC drivers support them. if value.acts_like?(:time) # Time, DateTime #%Q!{ts #{value.strftime("%Y-%m-%d %H:%M:%S")}}! %Q!'#{value.strftime("%Y-%m-%d %H:%M:%S")}'! else # Date #%Q!{d #{value.strftime("%Y-%m-%d")}}! %Q!'#{value.strftime("%Y-%m-%d")}'! end end #-- # DATABASE STATEMENTS OVERRIDES ==================================== # # see: abstract/database_statements.rb # Begins a transaction (and turns off auto-committing). def begin_db_transaction @logger.unknown("ODBCAdapter#begin_db_transaction>") if @@trace @connection.autocommit = false rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Commits the transaction (and turns on auto-committing). def commit_db_transaction @logger.unknown("ODBCAdapter#commit_db_transaction>") if @@trace @connection.commit # ODBC chains transactions. Turn autocommit on after commit to # allow explicit transaction initiation. @connection.autocommit = true rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Rolls back the transaction (and turns on auto-committing). def rollback_db_transaction @logger.unknown("ODBCAdapter#rollback_db_transaction>") if @@trace @connection.rollback # ODBC chains transactions. Turn autocommit on after rollback to # allow explicit transaction initiation. @connection.autocommit = true rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Appends +LIMIT+ and/or +OFFSET+ options to a SQL statement. # See DatabaseStatements#add_limit_offset! #-- # Base class accepts only +LIMIT+ *AND* +OFFSET+ def add_limit_offset!(sql, options) @logger.unknown("ODBCAdapter#add_limit_offset!>") if @@trace @logger.unknown("args=[#{sql}]") if @@trace if limit = options[:limit] then sql << " LIMIT #{limit}" end if offset = options[:offset] then sql << " OFFSET #{offset}" end end # Returns an array of record hashes with the column names as keys and # column values as values. def select_all(arel, name=nil, binds = nil) sql = to_sql(arel, binds) @logger.unknown("ODBCAdapter#select_all>") if @@trace @logger.unknown("args=[#{sql}|#{name}]") if @@trace retVal = [] hResult = select(sql, name) rRows = hResult[:rows] rColDescs = hResult[:column_descriptors] # Convert rows from arrays to hashes if rRows rRows.each do |row| h = Hash.new (0...row.length).each do |iCol| h[activeRecIdentCase(rColDescs[iCol].name)] = convertOdbcValToGenericVal(row[iCol]) end retVal << h end end retVal end # Returns a record hash with the column names as keys and column values # as values. def select_one(arel, name=nil, binds = nil) sql = to_sql(arel, binds) @logger.unknown("ODBCAdapter#select_one>") if @@trace @logger.unknown("args=[#{sql}|#{name}]") if @@trace retVal = nil scrollableCursor = false offset = 0 qry = sql.dup # Strip OFFSET and LIMIT from query if present, since ODBC doesn't # support them in a generic form. # # TODO: Translate any OFFSET/LIMIT option to native SQL if DBMS supports it. # This will perform much better than simulating them. if qry =~ /(\bLIMIT\s+)(\d+)/i then # Check for 'LIMIT 0' otherwise ignore LIMIT if $2.to_i == 0 then return retVal end end if qry =~ /(\bOFFSET\s+)(\d+)/i then offset = $2.to_i end qry.gsub!(/(\bLIMIT\s+\d+|\bOFFSET\s+\d+)/i, '') # It's been assumed that it's quicker to support an offset # restriction using a forward-only cursor. A static cursor will # presumably take a snapshot of the whole result set, whereas when # using a forward-only cursor we only fetch the first offset+1 # rows. =begin if offset > 0 then scrollableCursor = true begin # ODBCStatement::fetch_first requires a scrollable cursor @connection.cursortype = ODBC::SQL_CURSOR_STATIC rescue # Assume ODBC driver doesn't support scrollable cursors @connection.cursortype = ODBC::SQL_CURSOR_FORWARD_ONLY scrollableCursor = false end end =end # Execute the query begin stmt = @connection.run(qry) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace stmt.drop unless stmt.nil? raise StatementInvalid, e.message end # Get one row, handling any offset stipulated rColDescs = stmt.columns(true) if scrollableCursor then # scrollableCursor == true => offset > 0 stmt.fetch_scroll(ODBC::SQL_FETCH_ABSOLUTE, offset) row = stmt.fetch else row = nil rRows = stmt.fetch_many(offset + 1) if rRows && rRows.length > offset then row = rRows[offset] end end # Convert row from array to hash if row then retVal = h = Hash.new (0...row.length).each do |iCol| h[activeRecIdentCase(rColDescs[iCol].name)] = convertOdbcValToGenericVal(row[iCol]) end end stmt.drop retVal end # Executes the SQL statement in the context of this connection. # Returns the number of rows affected. def execute(sql, name = nil) @logger.unknown("ODBCAdapter#execute>") if @@trace @logger.unknown("args=[#{sql}|#{name}]") if @@trace if sql =~ /^\s*INSERT/i && [:microsoftsqlserver, :virtuoso, :sybase].include?(@dbmsName) # Guard against IDENTITY insert problems caused by explicit inserts # into autoincrementing id column. insert(sql, name) else begin @connection.do(sql) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise StatementInvalid, e.message end end end # Returns the ID of the last inserted row. def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) sql = sql.to_sql if sql.is_a?(Arel::InsertManager) @logger.unknown("ODBCAdapter#insert>") if @@trace @logger.unknown("args=[#{sql}|#{name}|#{pk}|#{id_value}|#{sequence_name}]") if @@trace insert_sql(sql, name, pk, id_value, sequence_name) end # Returns the default sequence name for a table. # Used for databases which don't support an autoincrementing column # type, but do support sequences. def default_sequence_name(table, column) @logger.unknown("ODBCAdapter#default_sequence_name>") if @@trace @logger.unknown("args=[#{table}|#{column}]") if @@trace "#{table}_seq" end # Set the sequence to the max value of the table�s column. def reset_sequence!(table, column, sequence = nil) @logger.unknown("ODBCAdapter#reset_sequence!>") if @@trace @logger.unknown("args=[#{table}|#{column}|#{sequence}]") if @@trace super(table, column, sequence) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end #-- # SCHEMA STATEMENTS OVERRIDES ====================================== # # see: abstract/schema_statements.rb def create_database(name) @logger.unknown("ODBCAdapter#create_database>") if @trace @logger.unknown("args=[#{name}]") if @trace # raise NotImplementedError, "create_database is not implemented" rescue Exception => e @logger.unknown("exception=#{e}") if @trace raise end def drop_database(name) @logger.unknown("ODBCAdapter#drop_database>") if @trace @logger.unknown("args=[#{name}]") if @trace # raise NotImplementedError, "drop_database is not implemented" rescue Exception => e @logger.unknown("exception=#{e}") if @trace raise end #-- # Required by db:test:purge Rake task (see databases.rake) def recreate_database(name, fail_quietly = false) @logger.unknown("ODBCAdapter#recreate_database>") if @@trace @logger.unknown("args=[#{name}|#{fail_quietly}]") if @@trace begin drop_database(name) create_database(name) rescue Exception => e raise unless fail_quietly end end def current_database @dsInfo.info[ODBC::SQL_DATABASE_NAME].strip end # The maximum length a table alias can be. def table_alias_length maxIdentLen = @dsInfo.info[ODBC::SQL_MAX_IDENTIFIER_LEN] maxTblNameLen = @dsInfo.info[ODBC::SQL_MAX_TABLE_NAME_LEN] maxTblNameLen < maxIdentLen ? maxTblNameLen : maxIdentLen end # Returns an array of table names, for database tables visible on the # current connection. def tables(name = nil) @logger.unknown("ODBCAdapter#tables>") if @@trace @logger.unknown("args=[#{name}]") if @@trace tblNames = [] # TODO: ODBC::Connection#tables cannot filter on schema name # Modify Werner's Ruby ODBC driver to allow this currentUser = @dsInfo.info[ODBC::SQL_USER_NAME] stmt = @connection.tables resultSet = stmt.fetch_all || [] resultSet.each do |row| schemaName = row[1] tblName = row[2] tblType = row[3] next if respond_to?("table_filter") && table_filter(schemaName, tblName, tblType) if @@dbmsLookups.get_info(@dbmsName, @dbmsMajorVer, :supports_schema_names) tblNames << activeRecIdentCase(tblName) if schemaName.casecmp(currentUser) == 0 else tblNames << activeRecIdentCase(tblName) end end stmt.drop tblNames rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Returns an array of Column objects for the table specified by +table_name+. def columns(table_name, name = nil) @logger.unknown("ODBCAdapter#columns>") if @@trace @logger.unknown("args=[#{table_name}|#{name}]") if @@trace table_name = table_name.to_s if table_name.class == Symbol getDbTypeInfo begin booleanColSurrogate = @emulate_booleans ? @@dbmsLookups.get_info(@dbmsName, @dbmsMajorVer, :boolean_col_surrogate) : nil rescue Exception # No boolean column surrogate defined for target database in lookup table booleanColSurrogate = nil @emulate_booleans = false end cols = [] stmt = @connection.columns(dbmsIdentCase(table_name)) resultSet = stmt.fetch_all || [] resultSet.each do |col| colName = col[3] # SQLColumns: COLUMN_NAME colDefault = col[12] # SQLColumns: COLUMN_DEF colSqlType = col[4] # SQLColumns: DATA_TYPE colNativeType = col[5] # SQLColumns: TYPE_NAME colLimit = col[6] # SQLColumns: COLUMN_SIZE colScale = col[8] # SQLColumns: DECIMAL_DIGITS odbcIsNullable = col[17] # SQLColumns: IS_NULLABLE odbcNullable = col[10] # SQLColumns: NULLABLE # isNotNullable == true => *definitely not* nullable # == false => *may* be nullable isNotNullable = (!odbcIsNullable || odbcIsNullable.match('NO') != nil) # Assume column is nullable if odbcNullable == SQL_NULLABLE_UNKNOWN colNullable = !(isNotNullable || odbcNullable == SQL_NO_NULLS) # HACK! # MySQL native ODBC driver doesn't report nullability accurately. # So force nullability of 'id' columns colNullable = false if colName == 'id' # SQL Server ODBC drivers may wrap default value in parentheses if colDefault =~ /^\('(.*)'\)$/ # SQL Server character default colDefault = $1 elsif colDefault =~ /^\((.*)\)$/ # SQL Server numeric default colDefault = $1 # ODBC drivers should return string column defaults in quotes # - strip off the quotes # - Oracle may include a trailing space. # - PostgreSQL may return '::character varying' elsif colDefault =~ /^'(.*)'([ :].*)*$/ colDefault = $1 #TODO: HACKS for Progress elsif @dbmsName == :progress || @dbmsName == :progress89 if colDefault =~ /^\?$/ colDefault = nil elsif colSqlType == ODBC::SQL_BIT if ["yes", "no"].include?(colDefault) colDefault = colDefault == "yes" ? 1 : 0 end end end cols << ODBCColumn.new(activeRecIdentCase(colName), table_name, colDefault, colSqlType, colNativeType, colNullable, colLimit, colScale, @odbcExtFile+"_col", booleanColSurrogate, native_database_types()) end stmt.drop cols rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Returns an array of indexes for the given table. def indexes(table_name, name = nil) @logger.unknown("ODBCAdapter#indexes>") if @@trace @logger.unknown("args=[#{table_name}|#{name}]") if @@trace indexes = [] indexCols = indexName = isUnique = nil stmt = @connection.indexes(dbmsIdentCase(table_name.to_s)) rs = stmt.fetch_all || [] rs.each_index do |iRow| row = rs[iRow] # Skip table statistics next if row[6] == 0 # SQLStatistics: TYPE if (row[7] == 1) # SQLStatistics: ORDINAL_POSITION # Start of column descriptor block for next index indexCols = Array.new isUnique = (row[3] == 0) # SQLStatistics: NON_UNIQUE indexName = String.new(row[5]) # SQLStatistics: INDEX_NAME end indexCols << activeRecIdentCase(row[8]) # SQLStatistics: COLUMN_NAME lastRow = (iRow == rs.length - 1) if lastRow lastColOfIndex = true else nextRow = rs[iRow + 1] lastColOfIndex = (nextRow[6] == 0 || nextRow[7] == 1) end if lastColOfIndex indexes << IndexDefinition.new(table_name, activeRecIdentCase(indexName), isUnique, indexCols) end end return indexes rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message ensure stmt.drop unless stmt.nil? end # Returns a Hash of mappings from Rails' abstract data types to the # native database types. # See TableDefinition#column for details of the abstract data types. def native_database_types @logger.unknown("ODBCAdapter#native_database_types>") if @@trace return {}.merge(@abstract2NativeTypeMap) unless @abstract2NativeTypeMap.nil? @abstract2NativeTypeMap = { :primary_key => nil, :string => nil, :text => nil, :integer => nil, :decimal => nil, :float => nil, :datetime => nil, :timestamp => nil, :time => nil, :date => nil, :binary => nil, :boolean => nil } getDbTypeInfo # hAbs2Sql = Hash of ActiveRecord abstract types to ODBC SQL types hAbs2Sql = genericTypeToOdbcSqlTypesMap # hSql2Native = Hash of ODBC native data type descriptors from # SQLGetTypeInfo keyed on ODBC SQL type. # The hash value is an array of all rows in the SQLGetTypeInfo result # set for which DATA_TYPE matches the key. hSql2Native = Hash.new @typeInfo.each do |row| sqlType = row[1] # SQLGetTypeInfo: DATA_TYPE if (rNativeTypeDescs = hSql2Native[sqlType]) == nil hSql2Native[sqlType] = rNativeTypeDescs = Array.new() end rNativeTypeDescs << row end # For a particular abstract type, check if the DBMS supports one of # the corresponding ODBC SQL types then, if so, find the native DBMS # types corresponding to this ODBC SQL type and select the most # suitable. (For each SQL type, SQLGetTypeInfo should return the # closest match first). @abstract2NativeTypeMap.each_key do |abstractType| rCandidateSqlTypes = hAbs2Sql[abstractType] isSupported = false rCandidateSqlTypes.each do |sqlType| if (rNativeTypeDescs = hSql2Native[sqlType]) @abstract2NativeTypeMap[abstractType] = nativeTypeMapping(abstractType, rNativeTypeDescs) isSupported = true break end end @logger.unknown("WARNING: No suitable DBMS type for abstract type #{abstractType.to_s}") if !isSupported && @@trace end begin booleanColSurrogate = @emulate_booleans ? @@dbmsLookups.get_info(@dbmsName, @dbmsMajorVer, :boolean_col_surrogate) : nil rescue Exception # No boolean column surrogate defined for target database in lookup table booleanColSurrogate = nil @emulate_booleans = false end @abstract2NativeTypeMap[:boolean] = {:name => booleanColSurrogate} if booleanColSurrogate {}.merge(@abstract2NativeTypeMap) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end def primary_key(table) # SELECT constraint_name, FROM primary_keys where table_name = #{table_name}; nil # for now because we only want to query these tables end # Creates a new table. See SchemaStatements#create_table. def create_table(name, options = {}) @logger.unknown("ODBCAdapter#create_table>") if @@trace @logger.unknown("args=[#{name}]") if @@trace super(name, options) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Renames a table. def rename_table(name, new_name) @logger.unknown("ODBCAdapter#rename_table>") if @@trace @logger.unknown("args=[#{name}|#{new_name}]") if @@trace # Base class raises NotImplementedError super(name, new_name) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Drops a table from the database. def drop_table(name, options = {}) @logger.unknown("ODBCAdapter#drop_table>") if @@trace @logger.unknown("args=[#{name}]") if @@trace super(name, options) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Adds a new column to the named table. # See TableDefinition#column for details of the options you can use. def add_column(table_name, column_name, type, options = {}) @logger.unknown("ODBCAdapter#add_column>") if @@trace @logger.unknown("args=[#{table_name}|#{column_name}|#{type}]") if @@trace super(table_name, column_name, type, options) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Removes the column from the table definition. def remove_column(table_name, column_name) @logger.unknown("ODBCAdapter#remove_column>") if @@trace @logger.unknown("args=[#{table_name}|#{column_name}]") if @@trace super(table_name, column_name) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Changes the column's definition according to the new options. # See TableDefinition#column for details of the options you can use. def change_column(table_name, column_name, type, options = {}) @logger.unknown("ODBCAdapter#change_column>") if @@trace @logger.unknown("args=[#{table_name}|#{column_name}|#{type}]") if @@trace # Base class raises NotImplementedError super(table_name, column_name, type, options) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Sets a new default value for a column. def change_column_default(table_name, column_name, default) @logger.unknown("ODBCAdapter#change_column_default>") if @@trace @logger.unknown("args=[#{table_name}|#{column_name}]") if @@trace super(table_name, column_name, default) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end def rename_column(table_name, column_name, new_column_name) @logger.unknown("ODBCAdapter#rename_column>") if @@trace @logger.unknown("args=[#{table_name}|#{column_name}|#{new_column_name}]") if @@trace # Base class raises NotImplementedError super(table_name, column_name, new_column_name) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end def remove_index(table_name, options = {}) @logger.unknown("ODBCAdapter#remove_index>") if @@trace @logger.unknown("args=[#{table_name}]") if @@trace super(table_name, options) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Not exercised by ActiveRecord test suite def structure_dump # :nodoc: @logger.unknown("ODBCAdapter#structure_dump>") if @@trace raise NotImplementedError, "structure_dump is not implemented" end #-- # WRAPPER METHODS FOR TRACING ====================================== #-- # ------------------------------------------------------------------ # see: abstract/database_statements.rb # Returns a single value from a record #-- # No need to implement beyond a tracing wrapper def select_value(sql, name = nil) @logger.unknown("ODBCAdapter#select_value>") if @@trace @logger.unknown("args=[#{sql}|#{name}]") if @@trace super(sql, name) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise StatementInvalid, e.message end # Returns an array of the values of the first column in a select. def select_values(sql, name = nil) @logger.unknown("ODBCAdapter#select_values>") if @@trace @logger.unknown("args=[#{sql}|#{name}]") if @@trace result = select_all(sql, name) result.map{ |v| v.values.first } rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise StatementInvalid, e.message end # Returns an array of arrays containing the field values. # Order is the same as that returned by #columns. def select_rows(sql, name = nil) @logger.unknown("ODBCAdapter#select_rows>") if @@trace @logger.unknown("args=[#{sql}|#{name}]") if @@trace hResult = select(sql, name) hResult[:rows] rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise StatementInvalid, e.message end # Wrap a block in a transaction. Returns result of block. #-- # No need to implement beyond a tracing wrapper def transaction(start_db_transaction = true) @logger.unknown("ODBCAdapter#transaction>") if @@trace super(start_db_transaction) rescue Exception => e @logger.unknown("#{e.class}: #{e}") if @@trace raise end # Alias for #add_limit_offset! #-- # No need to implement beyond a tracing wrapper def add_limit!(sql, options) @logger.unknown("ODBCAdapter#add_limit!>") if @@trace @logger.unknown("args=[#{sql}]") if @@trace super(sql, options) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # Returns the last auto-generated ID from the affected table. def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) # :nodoc: # id_value ::= pre-assigned id retry_count = 0 begin pre_insert(sql, name, pk, id_value, sequence_name) if respond_to?("pre_insert") stmt = @connection.run(sql) table = sql.split(" ", 4)[2] res = id_value || last_insert_id(table, sequence_name || default_sequence_name(table, pk), stmt) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace if @dbmsName == :virtuoso && id_value.nil? && e.message =~ /sr197/i # Error: Non unique primary key # If id column is an autoincrementing IDENTITY column and there # have been prior inserts using explicit id's, the sequence # associated with the id column could lag behind the id values # inserted explicitly. In the course of subsequent inserts, if # an explicit id isn't given, the autogenerated id may collide # with a previously explicitly inserted value. unless stmt.nil? stmt.drop; stmt = nil end table_name = e.message =~/Non unique primary key on (\w+\.\w+\.\w+)/i ? $1 : nil if table_name && retry_count == 0 retry_count += 1 # Set next sequence value to be greater than current max. pk value set_sequence(table_name, pk) retry end end raise StatementInvalid, e.message ensure post_insert(sql, name, pk, id_value, sequence_name) if respond_to?("post_insert") stmt.drop unless stmt.nil? end res end #-- # ------------------------------------------------------------------ # see: abstract/schema_statements.rb # Adds a new index to the table. # See SchemaStatements#add_index. #-- # No need to implement beyond a tracing wrapper def add_index(table_name, column_name, options = {}) @logger.unknown("ODBCAdapter#add_index>") if @@trace @logger.unknown("args=[#{table_name}|#{column_name}]") if @@trace super(table_name, column_name, options) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end #-- # If the index is not explicitly named using the :name option, # there's a risk the generated index name could exceed the maximum # length supported by the database. # i.e. dsInfo.info[ODBC::SQL_MAX_IDENTIFIER_LEN] def index_name(table_name, options) # :nodoc: @logger.unknown("ODBCAdapter#index_name>") if @@trace @logger.unknown("args=[#{table_name}]") if @@trace super rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end def type_to_sql(type, limit = nil, precision = nil, scale = nil) # :nodoc: @logger.unknown("ODBCAdapter#type_to_sql>") if @@trace @logger.unknown("args=[#{type}|#{limit}|#{precision}|#{scale}]") if @@trace if native = native_database_types[type] column_type_sql = String.new(native.is_a?(Hash) ? native[:name] : native) if type == :decimal # ignore limit, use precision and scale precision ||= native[:precision] scale ||= native[:scale] if precision if scale column_type_sql << "(#{precision},#{scale})" else column_type_sql << "(#{precision})" end else raise ArgumentError, "Error adding decimal column: precision cannot be empty if scale if specified" if scale end column_type_sql else # if there's no limit in the type definition, assume that the type # doesn't support a length qualifier column_type_sql << "(#{limit || native[:limit]})" if native[:limit] column_type_sql end else @logger.unknown("Warning! Type #{type} not present in native_database_types") if @@trace column_type_sql = type end rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # No need to implement beyond tracing wrapper def add_column_options!(sql, options) # :nodoc: @logger.unknown("ODBCAdapter#add_column_options!>") if @@trace @logger.unknown("args=[#{sql}]") if @@trace super(sql, options) rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise StatementInvalid, e.message end # No need to implement beyond tracing wrapper def dump_schema_information # :nodoc: @logger.unknown("ODBCAdapter#dump_schema_information>") if @@trace super rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message end # ================================================================== private #-- # Executes a SELECT statement, returning a hash containing the # result set rows (key :rows) and the result set column descriptors # (key :column_descriptors) as arrays. def select(sql, name) # :nodoc: scrollableCursor = false limit = 0 offset = 0 qry = sql.dup # Strip OFFSET and LIMIT from query if present, since ODBC doesn't # support them in a generic form. # # TODO: Translate any OFFSET/LIMIT option to native SQL if DBMS supports it. # This will perform much better than simulating them. if qry =~ /(\bLIMIT\s+)(\d+)/i then if (limit = $2.to_i) == 0 then return Array.new end end if qry =~ /(\bOFFSET\s+)(\d+)/i then offset = $2.to_i end qry.gsub!(/(\bLIMIT\s+\d+|\bOFFSET\s+\d+)/i, '') # It's been assumed that it's quicker to support an offset and/or # limit restriction using a forward-only cursor. A static cursor will # presumably take a snapshot of the whole result set, whereas when # using a forward-only cursor we only fetch the first offset+limit # rows. =begin if offset > 0 then scrollableCursor = true begin # ODBCStatement::fetch_first requires a scrollable cursor @connection.cursortype = ODBC::SQL_CURSOR_STATIC rescue # Assume ODBC driver doesn't support scrollable cursors @connection.cursortype = ODBC::SQL_CURSOR_FORWARD_ONLY scrollableCursor = false end end =end # Execute the query begin stmt = @connection.run(qry) rescue Exception => e stmt.drop unless stmt.nil? @logger.unknown("exception=#{e}") if @@trace && name != :force_error raise StatementInvalid, e.message end rColDescs = stmt.columns(true) # Get the rows, handling any offset and/or limit stipulated if scrollableCursor then rRows = nil # scrollableCursor == true => offset > 0 if stmt.fetch_scroll(ODBC::SQL_FETCH_ABSOLUTE, offset) rRows = limit > 0 ? stmt.fetch_many(limit) : stmt.fetch_all end else rRows = limit > 0 ? stmt.fetch_many(offset + limit) : stmt.fetch_all # Enforce OFFSET if offset > 0 then if rRows && rRows.length > offset then rRows.slice!(0, offset) else rRows = nil end end # Enforce LIMIT if limit > 0 && rRows && rRows.length > limit then rRows.slice!(limit..(rRows.length-1)) end end stmt.drop {:rows => rRows, :column_descriptors => rColDescs} end # Maps a DBMS name to a symbol. # # Different ODBC drivers might return different names for the same # DBMS. So #dbmsNameToSym maps similar names to the same symbol. # # If adding an odbcext_xxx extension module for a particular DBMS, # you should define a symbol here for the target DBMS. # # dbmsName is the SQL_DBMS_NAME returned by ODBC, downcased with # whitespace removed. def dbmsNameToSym(dbmsName, dbmsVer) if dbmsName =~ /db2/i symbl = :db2 elsif dbmsName =~ /informix/i symbl = :informix elsif dbmsName =~ /ingres/i symbl = :ingres elsif dbmsName =~ /my.*sql/i symbl = :mysql elsif dbmsName =~ /oracle/i symbl = :oracle elsif dbmsName =~ /postgres/i symbl = :postgresql elsif dbmsName =~ /progress/i or dbmsName =~ /openedge/i # ODBC connections to Progress >= v9 are assumed to be to # the SQL-92 engine. Connections to Progress <= v8 are # assumed to be to the SQL-89 engine. symbl = (dbmsVer <= 8 && dbmsVer != 1) ? :progress89 : :progress elsif dbmsName == "o\x00p\x00e\x00n\x00e\x00d\x00g\x00e\x00" symbl = :progress elsif dbmsName =~ /sql.*server/i symbl = :microsoftsqlserver elsif dbmsName =~ /sybase/i symbl = :sybase elsif dbmsName =~ /virtuoso/i symbl = :virtuoso elsif dbmsName =~ /SQLAnywhere/i or dbmsName =~ /adaptiveserveranywhere/i or dbmsName == "a\x00d\x00a\x00p\x00t\x00i\x00v\x00e\x00\x00s\x00e\x00r\x00v\x00e\x00r\x00\x00a\x00n\x00y\x00w\x00h\x00e\x00r\x00e\x00" symbl = :sqlanywhere elsif dbmsName =~ /visualfoxpro/i # Try to access Visual Fox Pro database as a PostgreSQL database, works for simple queries. symbl = :postgresql elsif dbmsName =~ /advantage/i or dbmsName == "a\x00d\x00v\x00a\x00n\x00t\x00a\x00g\x00e\x00" symbl = :advantage elsif dbmsName == "p\x00e\x00r\x00v\x00a\x00s\x00i\x00v\x00e\x00.\x00s\x00q\x00l\x00" symbl = :pervasive else raise ActiveRecord::ActiveRecordError, "ODBCAdapter: Unsupported database (#{dbmsName})" end symbl end # Returns a Hash of mappings for each ActiveRecord abstract data type to # one or more ODBC SQL types # # Where more than one ODBC SQL type is associated with an abstract type, # the SQL types in the value array are in order of preference. def genericTypeToOdbcSqlTypesMap map = { :primary_key => [ODBC::SQL_INTEGER, ODBC::SQL_SMALLINT], :string => [ODBC::SQL_VARCHAR], :text => [ODBC::SQL_LONGVARCHAR, ODBC::SQL_VARCHAR], :integer => [ODBC::SQL_INTEGER, ODBC::SQL_SMALLINT], :decimal => [ ODBC::SQL_NUMERIC, ODBC::SQL_DECIMAL], :float => [ODBC::SQL_DOUBLE, ODBC::SQL_REAL], :datetime => [ODBC::SQL_TYPE_TIMESTAMP, ODBC::SQL_TIMESTAMP], :timestamp => [ODBC::SQL_TYPE_TIMESTAMP, ODBC::SQL_TIMESTAMP], :time => [ODBC::SQL_TYPE_TIME, ODBC::SQL_TIME, ODBC::SQL_TYPE_TIMESTAMP, ODBC::SQL_TIMESTAMP], :date => [ODBC::SQL_TYPE_DATE, ODBC::SQL_DATE, ODBC::SQL_TYPE_TIMESTAMP, ODBC::SQL_TIMESTAMP], :binary => [ ODBC::SQL_LONGVARBINARY, ODBC::SQL_VARBINARY], :boolean => [ODBC::SQL_BIT, ODBC::SQL_TINYINT, ODBC::SQL_SMALLINT, ODBC::SQL_INTEGER] } # MySQL: # Mapping of :boolean to ODBC::SQL_BIT is removed because it does not # work with the BIT datatype in MySQL 5.0.3 or later. # - Prior to MySQL 5.0.3: BIT was a synonym for TINYINT(1). # - MySQL 5.0.3: BIT datatype is supported only for MyISAM tables, # not InnoDB tables (which ActiveRecord requires for transaction # support). # - Ruby ODBC Bridge attempts to fetch SQL_BIT column to SQL_C_LONG. # With MySQL ODBC driver (3.51.12) # - 'select b from ...' returns 0 for a bit value of 0x1 # - 'select hex(b) from ...' returns 1 for a bit value of 0x1 if @dbmsName == :mysql map[:boolean].delete(ODBC::SQL_BIT) { raise ActiveRecordError, "SQL_BIT not found" } end map end # Creates a Hash describing a mapping from an abstract type to a # DBMS native type for use by #native_database_types # # rNativeTypeDescs = array of rows from SQLGetTypeInfo result set # all mapping to the same ODBC SQL type def nativeTypeMapping (abstractType, rNativeTypeDescs) res = {} if abstractType == :primary_key # The appropriate SQL for :primary_key is hard to derive as # ODBC doesn't provide any info on a DBMS's native syntax for # autoincrement columns. So we use a lookup instead. val = @@dbmsLookups.get_info(@dbmsName, @dbmsMajorVer, :primary_key) res = val else nativeTypeDesc = rNativeTypeDescs[0] # If more than one native type corresponds to the SQL type we're # handling, the type in the first descriptor should be the # best match, because the ODBC specification states that # SQLGetTypeInfo returns the results ordered by SQL type and then by # how closely the native type maps to that SQL type. # But, for :text and :binary, select the native type with the # largest capacity. if [:text, :binary].include?(abstractType) rNativeTypeDescs.each do |ntd| # Compare SQLGetTypeInfo:COLUMN_SIZE values nativeTypeDesc = ntd if nativeTypeDesc[2] < ntd[2] end end res[:name] = nativeTypeDesc[0] # SQLGetTypeInfo: TYPE_NAME createParams = nativeTypeDesc[5] # Depending on the column type, the CREATE_PARAMS keywords can # include length, precision or scale. if (createParams && createParams.strip.length > 0 && ![:decimal].include?(abstractType)) unless @dbmsName == :db2 && ["BLOB", "CLOB"].include?(res[:name]) # HACK: # Omit the :limit option for DB2's CLOB and BLOB types, as the # :limit value set from SQLGetTypeInfo(COL_SIZE) is 2GB. # The max. length for these types defaults to 1MB if the # length specifier is omitted. res[:limit] = nativeTypeDesc[2] # SQLGetTypeInfo: COL_SIZE end # The max row length in Ingres is typically around 2008 bytes, # depending on the default page size. # Limit the reported max length of the native type which maps to # :string to 255, instead of the actual max length of 2000. # This is done to reduce the chances of add_column() exceeding # the maximum row length and Ingres returning an error. # # Similarly with DB2. The max row length is typically around 4005 # bytes. # # Similarly with Sybase, reduce the max. :string length from 2000 # to 255, to avoid add_index exceeding the max. allowed index size # of 1250 bytes when creating a composite index. res[:limit] = 255 if [:ingres, :sybase, :db2, :progress, :progress89].include?(@dbmsName) && abstractType == :string end end res end def last_insert_id(table, sequence_name, stmt = nil) # This method must be overridden in module ODBCExt. # Each DBMS supported by this ODBCAdapter supplies its own version in # file vendor/odbcext_#{dbmsName}.rb raise NotImplementedError, "last_insert_id is an abstract method" end # Converts a result set value from an ODBC type to an ActiveRecord # generic type. def convertOdbcValToGenericVal(value) # When fetching a result set, the Ruby ODBC driver converts all ODBC # SQL types to an equivalent Ruby type; with the exception of # SQL_TYPE_DATE, SQL_TYPE_TIME and SQL_TYPE_TIMESTAMP. # # The conversions below are consistent with the mappings in # ODBCColumn#mapSqlTypeToGenericType and Column#klass. case value when ODBC::TimeStamp Time.gm(value.year, value.month, value.day, value.hour, value.minute, value.second) when ODBC::Time now = DateTime.now Time.gm(now.year, now.month, now.day, value.hour, value.minute, value.second) when ODBC::Date Date.new(value.year, value.month, value.day) else value end rescue # Handle pre-epoch dates # TODO Write a test to show that this works beyond anecdotal evidence DateTime.new(value.year, value.month, value.day, value.hour, value.minute, value.second) end # In general, ActiveRecord uses lowercase attribute names. This may # conflict with the database's data dictionary case. # # The ODBCAdapter uses the following conventions for databases # which report SQL_IDENTIFIER_CASE = SQL_IC_UPPER: # * if a name is returned from the DBMS in all uppercase, convert it # to lowercase before returning it to ActiveRecord. # * if a name is returned from the DBMS in lowercase or mixed case, # assume the underlying schema object's name was quoted when # the schema object was created. Leave the name untouched before # returning it to ActiveRecord. # * before making an ODBC catalog call, if a supplied identifier is all # lowercase, convert it to uppercase. Leave mixed case or all # uppercase identifiers unchanged. # * columns created with quoted lowercase names are not supported. # Converts an identifier to the case conventions used by the DBMS. def dbmsIdentCase(identifier) # Assume received identifier is in ActiveRecord case. case @dsInfo.info[ODBC::SQL_IDENTIFIER_CASE] when ODBC::SQL_IC_UPPER identifier =~ /[A-Z]/ ? identifier : identifier.upcase else identifier end end # Converts an identifier to the case conventions used by ActiveRecord. def activeRecIdentCase(identifier) # Assume received identifier is in DBMS's data dictionary case. case @dsInfo.info[ODBC::SQL_IDENTIFIER_CASE] when ODBC::SQL_IC_UPPER identifier =~ /[a-z]/ ? identifier : identifier.downcase else identifier end end # Gets ODBCColumn descriptor for specified column def getODBCColumnDesc(table_name, column_name) col = nil columns(table_name, column_name).each do |colDesc| if colDesc.name == column_name col = colDesc break end end col end # Gets and caches SQLGetTypeInfo result set def getDbTypeInfo return @typeInfo if @typeInfo begin stmt = @connection.types @typeInfo = stmt.fetch_all rescue Exception => e @logger.unknown("exception=#{e}") if @@trace raise ActiveRecordError, e.message ensure stmt.drop unless stmt.nil? end @typeInfo end # Simulating sequences def create_sequence(name, start_val = 1) end def drop_sequence(name) end def next_sequence_value(name) end def ensure_sequences_table() end end # class ODBCAdapter #--------------------------------------------------------------------- class ODBCColumn < Column #:nodoc: def initialize (name, tableName, default, odbcSqlType, nativeType, null = true, limit = nil, scale = nil, dbExt = nil, booleanColSurrogate = nil, nativeTypes = nil) begin require "#{dbExt}" self.extend ODBCColumnExt rescue MissingSourceFile # Assume the current DBMS doesn't require extensions to ODBCColumn end @name, @null = name, null @precision = extract_precision(odbcSqlType, limit) @scale = extract_scale(odbcSqlType, scale) @limit = limit # nativeType is DBMS type used for column definition # sql_type assigned here excludes any length specification @sql_type = @nativeType = String.new(nativeType) @type = mapSqlTypeToGenericType(odbcSqlType, @nativeType, @scale, booleanColSurrogate, limit, nativeTypes) # type_cast uses #type so @type must be set first # The MS SQL Native Client ODBC driver wraps defaults in parentheses # (contrary to the ODBC spec). # e.g. '(1)' instead of '1', '(null)' instead of 'null' if default =~ /^\((.+)\)$/ then default = $1 end if self.respond_to?(:default_preprocess, true) default_preprocess(nativeType, default) end @default = type_cast(default) @table = tableName @primary = nil @autounique = self.respond_to?(:autoUnique?, true) ? autoUnique? : false end # Casts a value (which is a String) to the Ruby class # corresponding to the ActiveRecord abstract type associated # with the column. # # See Column#klass for the Ruby class corresponding to each # ActiveRecord abstract type. # # When casting a column's default value: # nil => no default value specified # "''" => string default value # "NULL" => default value of NULL # "TRUNCATED" => default value can't be represented without truncation # # Microsoft's SQL Native Client ODBC driver may return '(null)' # as a column default, instead of NULL, contrary to the ODBC spec' # It also wraps other default values in parentheses. def type_cast(value) return nil if value.nil? || value =~ /(^\s*[(]*\s*null\s*[)]*\s*$)|(^\s*truncated\s*$)/i super end private # Maps an ODBC SQL type to an ActiveRecord abstract data type # # c.f. Mappings in ConnectionAdapters::Column#simplified_type based on # native column type declaration # # See also: # Column#klass (schema_definitions.rb) for the Ruby class corresponding # to each abstract data type. def mapSqlTypeToGenericType (odbcSqlType, nativeType, scale, booleanColSurrogate, rawPrecision, nativeTypes) if booleanColSurrogate && booleanColSurrogate.upcase.index(nativeType.upcase) fullType = nativeType.dup if booleanColSurrogate =~ /\(\d+(,\d+)?\)/ && rawPrecision fullType << "(#{rawPrecision}" fullType << ",#{scale}" if $1 && scale fullType << ")" end return :boolean if fullType.casecmp(booleanColSurrogate) == 0 end case odbcSqlType when ODBC::SQL_BIT then :boolean when ODBC::SQL_CHAR, ODBC::SQL_VARCHAR then :string when ODBC::SQL_LONGVARCHAR then :text when ODBC::SQL_WCHAR, ODBC::SQL_WVARCHAR then :string when ODBC::SQL_WLONGVARCHAR then :text when ODBC::SQL_TINYINT, ODBC::SQL_SMALLINT, ODBC::SQL_INTEGER, ODBC::SQL_BIGINT then :integer when ODBC::SQL_REAL, ODBC::SQL_FLOAT, ODBC::SQL_DOUBLE then :float # If SQLGetTypeInfo output of ODBC driver doesn't include a mapping # to a native type from SQL_DECIMAL/SQL_NUMERIC, map to :float when ODBC::SQL_DECIMAL, ODBC::SQL_NUMERIC then scale.nil? || scale == 0 ? :integer : nativeTypes[:decimal].nil? ? :float : :decimal when ODBC::SQL_BINARY, ODBC::SQL_VARBINARY, ODBC::SQL_LONGVARBINARY then :binary # SQL_DATETIME is an alias for SQL_DATE in ODBC's sql.h & sqlext.h when ODBC::SQL_DATE, ODBC::SQL_TYPE_DATE, ODBC::SQL_DATETIME then :date when ODBC::SQL_TIME, ODBC::SQL_TYPE_TIME then :time when ODBC::SQL_TIMESTAMP, ODBC::SQL_TYPE_TIMESTAMP then :timestamp when ODBC::SQL_GUID then :string else # when SQL_UNKNOWN_TYPE # (ruby-odbc driver doesn't support following ODBC SQL types: # SQL_WCHAR, SQL_WVARCHAR, SQL_WLONGVARCHAR, SQL_INTERVAL_xxx) msg = "Unsupported ODBC SQL type [" << odbcSqlType.to_s << "]" raise ActiveRecordError, msg end end def extract_precision(odbcSqlType, odbcPrecision) # Ignore the ODBC precision of SQL types which don't take # an explicit precision when defining a column case odbcSqlType when ODBC::SQL_DECIMAL, ODBC::SQL_NUMERIC then odbcPrecision end end def extract_scale(odbcSqlType, odbcScale) # Ignore the ODBC scale of SQL types which don't take # an explicit scale when defining a column case odbcSqlType when ODBC::SQL_DECIMAL, ODBC::SQL_NUMERIC then odbcScale ? odbcScale : 0 end end end # class ODBCColumn end # module ConnectionAdapters end # module ActiveRecord #------------------------------------------------------------------------- rescue LoadError module ActiveRecord # :nodoc: class Base def self.odbc_connection(config) # :nodoc: raise LoadError, "The Ruby ODBC module could not be loaded." end end end end