# +----------------------------------------------------------------------+ # | Licensed Materials - Property of IBM | # | | # | (C) Copyright IBM Corporation 2006, 2007. | # +----------------------------------------------------------------------+ # | Author: Antonio Cangiano | # +----------------------------------------------------------------------+ require 'active_record/connection_adapters/abstract_adapter' module ActiveRecord class Base # Method required to handle LOBs and XML fields. An after save callback checks if a marker # has been inserted by the calling insert or update method, and if so it proceeds to update that record # with the real large object through query binding. after_save :handle_lobs def handle_lobs() if connection.kind_of?(ConnectionAdapters::IBM_DBAdapter) # Checks that the insert or update had at least a BLOB, CLOB or XML field if connection.sql =~ /BLOB\('(.*)'\)/i || connection.sql =~ /@@@IBMTEXT@@@/i || connection.sql =~ /@@@IBMXML@@@/i update_query = "UPDATE #{self.class.table_name} SET (" counter = 0 values = [] params = [] # Selects only binary, text and xml columns self.class.columns.select{|col| col.type == :binary || col.type == :text || col.type == :xml}.each do |col| # Adds the selected columns to the query if counter == 0 update_query << "#{col.name}" else update_query << ",#{col.name}" end # Add a '?' for the parameter or a NULL if the value is nil or empty (except for a CLOB field where '' can be a value) if self[col.name].nil? || self[col.name] == {} || (self[col.name] == '' && col.type != :text) params << 'NULL' else values << self[col.name] params << '?' end counter += 1 end update_query << ") = " # IBM_DB accepts 'SET (column) = NULL' but not (NULL), # therefore the sql needs to be changed for a single NULL field. if params.size==1 && params[0] == 'NULL' update_query << "NULL" else update_query << "(" + params.join(',') + ")" end update_query << " WHERE #{self.class.primary_key} = #{id}" stmt = IBM_DB::prepare(connection.connection, update_query) connection.log_query(update_query,'update of LOB/XML field(s)in handle_lobs') # rollback any failed LOB/XML field updates (and remove associated marker) unless IBM_DB::execute(stmt, values) connection.execute("ROLLBACK") raise "Failed to insert/update LOB/XML field(s) due to: #{IBM_DB::stmt_errormsg(stmt)}" end end # if connection.sql end # if connection.kind_of? end # handle_lobs private :handle_lobs # Establishes a connection to a specified database using the credentials provided # with the +config+ argument. All the ActiveRecord objects will use this connection def self.ibm_db_connection(config) # Attempts to load the Ruby driver IBM databases # while not already loaded or raises LoadError in case of failure. begin require 'ibm_db' unless defined? IBM_DB rescue LoadError raise LoadError, "Failed to load IBM_DB Ruby driver." end # Converts all +config+ keys to symbols config = config.symbolize_keys # Retrieves the database alias (local catalog name) or remote name # (for remote TCP/IP connections) from the +config+ hash # or raises ArgumentError in case of failure. if config.has_key?(:database) database = config[:database].to_s else raise ArgumentError, "Missing argument: a database name needs to be specified." end # Retrieves database user credentials from the +config+ hash # or raises ArgumentError in case of failure. if !config.has_key?(:username) || !config.has_key?(:password) raise ArgumentError, "Missing argument(s): Database configuration #{config} requires credentials: username and password" else username = config[:username].to_s password = config[:password].to_s end # Providing default schema (username) when not specified config[:schema] = config.has_key?(:schema) ? config[:schema].to_s : config[:username].to_s # Extract connection options from the database configuration # (in support to formatting, audit and billing purposes): # Retrieve database objects fields in lowercase conn_options = {IBM_DB::ATTR_CASE => IBM_DB::CASE_LOWER} # Set connection's user info conn_options[:app_user] = config[:app_user].to_s if config.has_key?(:app_user) # Set connection's account info conn_options[:account] = config[:account].to_s if config.has_key?(:account) # Set connection's application info conn_options[:application] = config[:application].to_s if config.has_key?(:application) # Set connection's workstation info conn_options[:workstation] = config[:workstation].to_s if config.has_key?(:workstation) # Checks if a host name or address has been specified. If so, this implies a TCP/IP connection # Returns IBM_DB::Connection object upon succesful DB connection to the database # If otherwise the connection fails, +false+ is returned if config.has_key?(:host) # Retrieves the host address/name host = config[:host] # A net address connection requires a port. If no port has been specified, 50000 is used by default port = config[:port] || 50000 # Connects to the database using the database, host, port, username and password specified connection = IBM_DB::connect "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=#{database};HOSTNAME=#{host};\ PORT=#{port};PROTOCOL=TCPIP;UID=#{username};PWD=#{password};", '', '', conn_options else # No host implies a local catalog-based connection: +database+ represents catalog alias connection = IBM_DB::connect( database, username, password, conn_options ) end # Verifies that the connection was succesfull if connection # Creates an instance of *IBM_DBAdapter* based on the +connection+ # and credentials provided in +config+ ConnectionAdapters::IBM_DBAdapter.new(connection, logger, config, conn_options) else # If the connection failed, it raises a Runtime error raise "Failed to connect to the [#{database}] due to: #{IBM_DB::conn_errormsg}" end end # method self.ibm_db_connection end # class Base module ConnectionAdapters class IBM_DBColumn < Column # Casts value (which is a String) to an appropriate instance def type_cast(value) # Casts the database NULL value to nil return nil if value == 'NULL' # Invokes parent's method for default casts super end # Used to convert from BLOBs to Strings def self.binary_to_string(value) # Returns a string removing the eventual BLOB scalar function value.to_s.gsub(/"SYSIBM"."BLOB"\('(.*)'\)/i,'\1') end private # Mapping IBM data servers SQL datatypes to Ruby data types def simplified_type(field_type) case field_type # if +field_type+ contains 'for bit data' handle it as a binary when /for bit data/i :binary when /smallint/i :boolean when /int/i :integer when /float|double|real/i :float when /decimal|numeric/i :decimal when /timestamp/i :timestamp when /time/i :time when /date/i :date when /clob|text|graphic/i :text when /xml/i :xml when /blob|binary/i :binary when /char/i :string when /boolean/i :boolean when /rowid/i # rowid is a supported datatype on z/OS and i/5 :rowid end end # method simplified_type end #class IBM_DBColumn # The IBM_DB Adapter requires the native Ruby driver (ibm_db) # for IBM data servers (ibm_db.so). # +config+ the hash passed as an initializer argument content: # == mandatory parameters # adapter: 'ibm_db' // IBM_DB Adapter name # username: 'db2user' // data server (database) user # password: 'secret' // data server (database) password # database: 'ARUNIT' // remote database name (or catalog entry alias) # == optional (highly recommended for data server auditing and monitoring purposes) # schema: 'rails123' // name space qualifier # account: 'tester' // OS account (client workstation) # app_user: 'test11' // authenticated application user # application: 'rtests' // application name # workstation: 'plato' // client workstation name # == remote TCP/IP connection (required when no local database catalog entry available) # host: 'socrates' // fully qualified hostname or IP address # port: '50000' // data server TCP/IP port number # # When schema is not specified, the username value is used instead. # class IBM_DBAdapter < AbstractAdapter attr_reader :connection attr_accessor :sql attr_reader :schema, :app_user, :account, :application, :workstation # Name of the adapter def adapter_name 'IBM_DB' end def initialize(connection, logger, config, conn_options) # Caching database connection configuration (+connect+ or +reconnect+ support) @connection = connection @conn_options = conn_options @database = config[:database] @username = config[:username] @password = config[:password] if config.has_key?(:host) @host = config[:host] @port = config[:port] || 50000 # default port end @schema = config[:schema] # Caching database connection options (auditing and billing support) @app_user = conn_options[:app_user] if conn_options.has_key?(:app_user) @account = conn_options[:account] if conn_options.has_key?(:account) @application = conn_options[:application] if conn_options.has_key?(:application) @workstation = conn_options[:workstation] if conn_options.has_key?(:workstation) # Calls the parent class +ConnectionAdapters+' initializer # which sets @connection, @logger, @runtime and @last_verification super(@connection, logger) if @connection server_info = IBM_DB::server_info( @connection ) case server_info.DBMS_NAME # DB2 on Linux, Unix and Windows (LUW) when /DB2\//i @servertype = IBM_DB2_LUW.new(@connection, self) # DB2 on zOS # when /DB2/i case server_info.DBMS_VER when /09/ @servertype = IBM_DB2_ZOS.new(@connection, self) when /08/ @servertype = IBM_DB2_ZOS_8.new(@connection, self) else raise "Only DB2 z/OS version 8 and above are currently supported" end # DB2 on i5 when /AS/i @servertype = IBM_DB2_I5.new(@connection, self) end end # Executes the +set schema+ statement using the schema identifier provided @servertype.set_schema(@schema) if @schema && @schema != @username end # Optional connection attribute: database name space qualifier def schema=(name) unless name == @schema @schema = name @servertype.set_schema(@schema) end end # Optional connection attribute: authenticated application user def app_user=(name) unless name == @app_user option = {IBM_DB::SQL_ATTR_INFO_USERID => "#{name}"} if IBM_DB::set_option( @connection, option, 1 ) @app_user = IBM_DB::get_option( @connection, IBM_DB::SQL_ATTR_INFO_USERID, 1 ) end end end # Optional connection attribute: OS account (client workstation) def account=(name) unless name == @account option = {IBM_DB::SQL_ATTR_INFO_ACCTSTR => "#{name}"} if IBM_DB::set_option( @connection, option, 1 ) @account = IBM_DB::get_option( @connection, IBM_DB::SQL_ATTR_INFO_ACCTSTR, 1 ) end end end # Optional connection attribute: application name def application=(name) unless name == @application option = {IBM_DB::SQL_ATTR_INFO_APPLNAME => "#{name}"} if IBM_DB::set_option( @connection, option, 1 ) @application = IBM_DB::get_option( @connection, IBM_DB::SQL_ATTR_INFO_APPLNAME, 1 ) end end end # Optional connection attribute: client workstation name def workstation=(name) unless name == @workstation option = {IBM_DB::SQL_ATTR_INFO_WRKSTNNAME => "#{name}"} if IBM_DB::set_option( @connection, option, 1 ) @workstation = IBM_DB::get_option( @connection, IBM_DB::SQL_ATTR_INFO_WRKSTNNAME, 1 ) end end end # This adapter supports migrations. # Current limitations: # +rename_column+ is not currently supported by the IBM data servers # +remove_column+ is not currently supported by the DB2 for zOS data server # Tables containing columns of XML data type do not support +remove_column+ def supports_migrations? true end def log_query(sql, name) #:nodoc: # Used by handle_lobs log(sql,name){} end #============================================== # CONNECTION MANAGEMENT #============================================== # Tests the connection status def active? IBM_DB::active @connection rescue false end # Private method used by +reconnect!+. # It connects to the database with the initially provided credentials def connect # If the type of connection is net based if @host # Connects and assigns the resulting IBM_DB::Connection to the +@connection+ instance variable @connection = IBM_DB::connect "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=#{@database};HOSTNAME=#{@host};\ PORT=#{@port};PROTOCOL=TCPIP;UID=#{@username};PWD=#{@password};", '', '', @conn_options else # Connects to the database using the local alias (@database) # and assigns the connection object (IBM_DB::Connection) to @connection @connection = IBM_DB::connect @database, @username, @password, @conn_options end # Sets the schema if different from default (username) if @schema && @schema != @username @servertype.set_schema(@schema) end end private :connect # Closes the current connection and opens a new one def reconnect! disconnect! connect end # Closes the current connection def disconnect! # Attempts to close the connection. The methods will return: # * true if succesfull # * false if the connection is already closed # * nil if an error is raised IBM_DB::close(@connection) rescue nil end #============================================== # DATABASE STATEMENTS #============================================== def create_table(name, options = {}) @servertype.setup_for_lob_table super @servertype.create_index_after_table(name, self) end # Returns an array of hashes with the column names as keys and # column values as values. +sql+ is the select query, and +name+ is an optional description for logging def select_all(sql, name = nil) # Replaces {"= NULL" with " IS NULL"} OR {"IN (NULL)" with " IS NULL"} sql.gsub!( /(=\s*NULL|IN\s*\(NULL\))/i, " IS NULL" ) results = [] # Invokes the method +execute+ in order to log and execute the sql instructions # and get a IBM_DB::Statement from which is possible to fetch the results if stmt = execute(sql, name) begin @servertype.select_all(sql, name, stmt, results) ensure # Ensures to free the resources associated with the statement IBM_DB::free_result stmt end end # The array of record hashes is returned results end # Returns a record hash with the column names as keys and column values # as values. def select_one(sql, name = nil) # Gets the first hash from the array of hashes returned by # select_all select_all(sql,name).first end # Perform an insert and returns the last ID generated. # This can be the ID passed to the method or the one auto-generated by the database, # and retrieved by the +last_generated_id+ method. def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) if stmt = execute(sql, name) begin @sql = sql return id_value || @servertype.last_generated_id # Ensures to free the resources associated with the statement ensure IBM_DB::free_result(stmt) end end end # Executes and logs +sql+ commands and # returns a +IBM_DB::Statement+ object. def execute(sql, name = nil) sql.gsub!( /(\s*xml DEFAULT NULL)/i, " xml NOT NULL" ) # Logs and execute the sql instructions. # The +log+ method is defined in the parent class +AbstractAdapter+ log(sql, name) do @servertype.execute(sql, name) end end # Executes an "UPDATE" SQL statement def update(sql, name = nil) # Make sure the WHERE clause handles NULL's correctly sqlarray = sql.split(/\s*WHERE\s*/) if !sqlarray[1].nil? sqlarray[1].gsub!( /(=\s*NULL|IN\s*\(NULL\))/i, " IS NULL" ) sql = sqlarray[0] + " WHERE " + sqlarray[1] end # Logs and execute the given sql query. if stmt = execute(sql, name) begin @sql = sql # Retrieves the number of affected rows IBM_DB::num_rows(stmt) # Ensures to free the resources associated with the statement ensure IBM_DB::free_result(stmt) end end end # The delete method executes the delete # statement and returns the number of affected rows. # The method is an alias for +update+ alias_method :delete, :update # Begins the transaction (and turns off auto-committing) def begin_db_transaction # Turns off the auto-commit IBM_DB::autocommit(@connection, IBM_DB::SQL_AUTOCOMMIT_OFF) end # Commits the transaction and turns on auto-committing def commit_db_transaction # Commits the transaction IBM_DB::commit @connection rescue nil # Turns on auto-committing IBM_DB::autocommit @connection, IBM_DB::SQL_AUTOCOMMIT_ON end # Rolls back the transaction and turns on auto-committing. Must be # done if the transaction block raises an exception or returns false def rollback_db_transaction # ROLLBACK the transaction IBM_DB::rollback(@connection) rescue nil # Turns on auto-committing IBM_DB::autocommit @connection, IBM_DB::SQL_AUTOCOMMIT_ON end # Modifies a sql statement in order to implement a LIMIT and an OFFSET. # A LIMIT defines the number of rows that should be fetched, while # an OFFSET defines from what row the records must be fetched. # IBM data servers implement a LIMIT in SQL statements through: # FETCH FIRST n ROWS ONLY, where n is the number of rows required. # The implementation of OFFSET is more elaborate, and requires the usage of # subqueries and the ROW_NUMBER() command in order to add row numbering # as an additional column to a copy of the existing table. # ==== Examples # add_limit_offset!('SELECT * FROM staff', {:limit => 10}) # generates: "SELECT * FROM staff FETCH FIRST 10 ROWS ONLY" # # add_limit_offset!('SELECT * FROM staff', {:limit => 10, :offset => 30}) # generates "SELECT O.* FROM (SELECT I.*, ROW_NUMBER() OVER () sys_rownum # FROM (SELECT * FROM staff) AS I) AS O WHERE sys_row_num BETWEEN 31 AND 40" def add_limit_offset!(sql, options) # If there is a limit if limit = options[:limit] # if the limit is zero if limit == 0 # Returns a query that will always generate zero records # (e.g. WHERE sys_row_num BETWEEN 1 and 0) sql = query_offset_limit(sql, 0, limit) # If there is a non-zero limit else offset = options[:offset] # If an offset is specified builds the query with offset and limit, oterwise retrieves only the first +limit+ rows offset ? sql = query_offset_limit(sql, offset, limit) : sql << " FETCH FIRST #{limit} ROWS ONLY" end end # Returns the sql query in any case sql end # method add_limit_offset! # Private method used by +add_limit_offset!+ to create a # sql query given an offset and a limit def query_offset_limit(sql, offset, limit) @servertype.query_offset_limit(sql, offset, limit) end private :query_offset_limit def default_sequence_name(table, column) # :nodoc: "#{table}_#{column}_seq" end #============================================== # QUOTING #============================================== # Properly quotes the various data types. # +value+ contains the data, +column+ is optional and contains info on the field def quote(value, column = nil) case value # If it's a numeric value and the column type is not a string, it shouldn't be quoted # (IBM_DB doesn't accept quotes on numeric types) when Numeric # If the column type is text or string, return the quote value if column && column.type == :text || column && column.type == :string "'#{value}'" else # value is Numeric, column.type is not a string, # therefore it converts the number to string without quoting it value.to_s end when String, ActiveSupport::Multibyte::Chars if column && column.type == :binary # If quoting is required for the insert/update of a BLOB unless caller[0] =~ /add_column_options/i # Invokes a convertion from string to binary "BLOB('?')" else # Quoting required for the default value of a column @servertype.set_blob_default(value) end elsif column && column.type == :text unless caller[0] =~ /add_column_options/i "'@@@IBMTEXT@@@'" else "#{value}" end elsif column && column.type == :xml unless caller[0] =~ /add_column_options/i "'@@@IBMXML@@@'" else "#{value}" end else "'#{quote_string(value)}'" end when TrueClass then quoted_true # return '1' for true when FalseClass then quoted_false # return '0' for false when NilClass if column && column.instance_of?(IBM_DBColumn) && !column.primary && !column.null "''" # allow empty inserts if not nullable or identity else # in order to support default ActiveRecord constructors "NULL" end else super # rely on superclass handling end end # Quotes a given string, escaping single quote (') characters. def quote_string(string) string.gsub(/'/, "''") end # *true* is represented by a smallint 1, *false* # by 0, as no native boolean type exists in DB2. # Numerics are not quoted in DB2. def quoted_true "1" end def quoted_false "0" end def quote_column_name(name) @servertype.check_reserved_words(name) end #============================================== # SCHEMA STATEMENTS #============================================== # Returns a Hash of mappings from the abstract data types to the native # database types def native_database_types { :primary_key => @servertype.primary_key, :string => { :name => "varchar", :limit => 255 }, :text => { :name => "clob" }, :integer => { :name => "integer" }, :float => { :name => "float" }, :datetime => { :name => "timestamp" }, :timestamp => { :name => "timestamp" }, :time => { :name => "time" }, :date => { :name => "date" }, :binary => { :name => "blob" }, # IBM data servers don't have a native boolean type. # A boolean can be represented by a smallint, # adopting the convention that False is 0 and True is 1 :boolean => { :name => "smallint"}, :xml => { :name => "xml"}, :decimal => { :name => "decimal" }, :rowid => { :name => "rowid" } # rowid is a supported datatype on z/OS and i/5 } end # IBM data servers do not support limits on certain data types (unlike MySQL) # Limit is supported for the {float, decimal, numeric, varchar, clob, blob} data types. def type_to_sql(type, limit = nil, precision = nil, scale = nil) return super if limit.nil? # strip off limits on data types not supporting them if [:integer, :double, :date, :time, :timestamp, :xml].include? type return type.to_s else return super end end # Returns the maximum length a table alias identifier can be. # IBM data servers (cross-platform) table limit is 128 characters def table_alias_length 128 end # Returns an array of tables within the given shema def tables(name = nil) # Initializes the tables array tables = [] # Returns a IBM_DB::Statement used to retrieve the tables if stmt = IBM_DB::tables(@connection, nil, @schema.upcase) # Fetches all the records available while tab = IBM_DB::fetch_assoc(stmt) # Adds the lowercase table name to the array tables << tab["table_name"].downcase end end # Returns the tables array tables end # Returns an array of non-primary key indexes for the given table def indexes(table_name, name = nil) # to_s required because +table_name+ may be a symbol. table_name = table_name.to_s # Checks if a blank table name has been given. # If so it returns an empty array of columns. return [] if table_name.strip.empty? # +indexes+ will contain the resulting array indexes = [] # Query table statistics for all indexes on the table # "TABLE_NAME: #{index_stats[2]}" # "NON_UNIQUE: #{index_stats[3]}" # "INDEX_NAME: #{index_stats[5]}" # "COLUMN_NAME: #{index_stats[8]}" stmt = IBM_DB::statistics( @connection, nil, @schema.upcase, table_name.upcase, 1) while ( index_stats = IBM_DB::fetch_array(stmt) ) if index_stats[5] # INDEX_NAME index_name = index_stats[5].downcase # Non-unique index type (not the primary key) unless index_stats[3] == 0 # NON_UNIQUE index_unique = (index_stats[3] == 0) index_columns = index_stats[8].map{|c| c.downcase} # COLUMN_NAME # Create an IndexDefinition object and add to the indexes array indexes << IndexDefinition.new(table_name, index_name, index_unique, index_columns) end end end # Returns the indexes array return indexes # Ensures to free the resources associated with the statement ensure IBM_DB::free_result(stmt) if stmt end # Returns an array of Column objects for the table specified by +table_name+ def columns(table_name, name = nil) # to_s required because it may be a symbol. table_name = table_name.to_s.upcase # Checks if a blank table name has been given. # If so it returns an empty array return [] if table_name.strip.empty? # +columns+ will contain the resulting array columns = [] # Statement required to access all the columns information if stmt = IBM_DB::columns(@connection, nil, @schema.upcase, table_name.upcase) begin # Fetches all the columns and assigns them to col. # +col+ is an hash with keys/value pairs for a column while col = IBM_DB::fetch_assoc(stmt) column_name = col["column_name"].downcase # Assigns the column default value. column_default_value = col["column_def"] # If there is no default value, it assigns NIL column_default_value = nil if (column_default_value && column_default_value.upcase == 'NULL') # Removes single quotes from the default value column_default_value.gsub!(/^'(.*)'$/, '\1') unless column_default_value.nil? # Assigns the column type column_type = col["type_name"].downcase # Assigns the field length (size) for the column column_length = col["column_size"] column_scale = col["decimal_digits"] # The initializer of the class Column, requires the +column_length+ to be declared between brackets after # the datatype(e.g VARCHAR(50)) for :string and :text types. If it's a "for bit data" field it does a subsitution in place, if not # it appends the (column_length) string on the supported data types unless column_length.nil? || column_length == '' || column_type.sub!(/ \(\) for bit data/i,"(#{column_length}) FOR BIT DATA") || !column_type =~ /char|lob|graphic/i if column_type =~ /decimal/i column_type << "(#{column_length},#{column_scale})" else column_type << "(#{column_length})" end end # col["NULLABLE"] is 1 if the field is nullable, 0 if not. column_nullable = (col["nullable"] == 1) ? true : false # Make sure the hidden column (db2_generated_rowid_for_lobs) in DB2 z/OS isn't added to the list if !(column_name =~ /db2_generated_rowid_for_lobs/i) # Pushes into the array the *IBM_DBColumn* object, created by passing to the initializer # +column_name+, +default_value+, +column_type+ and +column_nullable+. columns << IBM_DBColumn.new(column_name, column_default_value, column_type, column_nullable) end end rescue StandardError error_msg = IBM_DB::conn_errormsg ? IBM_DB::conn_errormsg : IBM_DB::stmt_errormsg if error_msg && !error_msg.empty? raise "Failed to retrieve column metadata due to driver error: #{error_msg}" else raise end end end # Returns the columns array columns end # Renames a table. # ==== Example # rename_table('octopuses', 'octopi') # Overriden to satisfy IBM data servers syntax def rename_table(name, new_name) # SQL rename table statement rename_table_sql = "RENAME TABLE #{name} TO #{new_name}" stmt = execute(rename_table_sql) # Ensures to free the resources associated with the statement ensure IBM_DB::free_result stmt if stmt end # Renames a column. # ===== Example # rename_column(:suppliers, :description, :name) def rename_column(table_name, column_name, new_column_name) @servertype.rename_column(table_name, column_name, new_column_name) end # Removes the column from the table definition. # ===== Examples # remove_column(:suppliers, :qualification) def remove_column(table_name, column_name) @servertype.remove_column(table_name, column_name) end # Changes the column's definition according to the new options. # See TableDefinition#column for details of the options you can use. # ===== Examples # change_column(:suppliers, :name, :string, :limit => 80) # change_column(:accounts, :description, :text) def change_column(table_name, column_name, type, options = {}) @servertype.change_column(table_name, column_name, type, options) end # Sets a new default value for a column. This does not set the default # value to +NULL+, instead, it needs DatabaseStatements#execute which # can execute the appropriate SQL statement for setting the value. # ==== Examples # change_column_default(:suppliers, :qualification, 'new') # change_column_default(:accounts, :authorized, 1) # Method overriden to satisfy IBM data servers syntax. def change_column_default(table_name, column_name, default) # SQL statement which alters column's default value change_column_sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET WITH DEFAULT #{quote(default)}" stmt = execute(change_column_sql) # Ensures to free the resources associated with the statement ensure IBM_DB::free_result stmt if stmt end # Adds a new index to the table. +column_name+ can be a single Symbol, or # an Array of Symbols. # # The index will be named after the table and the first column names, # unless you pass +:name+ as an option. # # When creating an index on multiple columns, the first column is used as a name # for the index. For example, when you specify an index on two columns # [+:first+, +:last+], the DBMS creates an index for both columns as well as an # index for the first colum +:first+. Using just the first name for this index # makes sense, because you will never have to create a singular index with this # name. # # ===== Examples # ====== Creating a simple index # add_index(:suppliers, :name) # generates # CREATE INDEX suppliers_name_index ON suppliers(name) # ====== Creating a unique index # add_index(:accounts, [:branch_id, :party_id], :unique => true) # generates # CREATE UNIQUE INDEX accounts_branch_id_index ON accounts(branch_id, party_id) # ====== Creating a named index # add_index(:accounts, [:branch_id, :party_id], :unique => true, :name => 'by_branch_party') # generates # CREATE UNIQUE INDEX by_branch_party ON accounts(branch_id, party_id) # Overidden to comply to the 18 characters cross-platform limit on index identifiers. def add_index(table_name, column_name, options = {}) index_name = index_name(table_name, options) if Hash === options # legacy support, since this param was a string index_type = options[:unique] ? "UNIQUE" : "" else index_type = options end execute("CREATE #{index_type} INDEX #{index_name} ON #{table_name} (#{Array(column_name).join(", ")})") end # Remove the given index from the table. # # Remove the suppliers_name_index in the suppliers table (legacy support, use the second or third forms). # remove_index :suppliers, :name # Remove the index named accounts_branch_id in the accounts table. # remove_index :accounts, :column => :branch_id # Remove the index named by_branch_party in the accounts table. # remove_index :accounts, :name => :by_branch_party # # You can remove an index on multiple columns by specifying the first column. # add_index :accounts, [:username, :password] # remove_index :accounts, :username # Overriden to use the IBM data servers SQL syntax. def remove_index(table_name, options = {}) execute("DROP INDEX #{index_name(table_name, options)}") end # Builds an index name from a table_name and column. If an index name has been passed, # the method returns it. Overrides the default method to respect the IBM data servers (cross-platform) # limit on indexes (max of 18 characters) def index_name(table_name, options) if Hash === options and options[:name] # legacy support # If a name has been specified, this is returned options[:name] else # We reverse the table name to reduce the chance of hitting duplicate # index name errors. For e.g. indexes on table names like accounts, # accounts_favorites "ror_#{table_name.to_s.reverse[0,10]}_idx" end end private :index_name end # class IBM_DBAdapter # This class contains common code across DB's (DB2 LUW, zOS and i5) class IBM_DataServer def initialize(connection, caller) @connection = connection @caller = caller end def last_generated_id end def create_index_after_table (table_name, caller) end def setup_for_lob_table () end def reorg_table(table_name) end def check_reserved_words(col_name) col_name end # This is supported by the DB2 for Linux, UNIX, Windows data servers # and by the DB2 for i5 data servers def remove_column(table_name, column_name) begin @caller.execute "ALTER TABLE #{table_name} DROP #{column_name}" reorg_table(table_name) rescue StandardError => exec_err # Provide details on the current XML columns support if exec_err.message.include?('SQLCODE=-1242') && exec_err.message.include?('42997') raise StatementInvalid, "A column that is part of a table containing an XML column cannot be dropped. To remove the column, the table must be dropped and recreated without the #{column_name} column: #{exec_err}" else raise end end end def set_schema(schema) @caller.execute("SET SCHEMA #{schema}") end def select_all(sql, name, stmt, results) end def execute(sql, name) end def query_offset_limit(sql, offset, limit) end def set_blob_default(value) "BLOB('#{value}')" end end # class IBM_DataServer class IBM_DB2 < IBM_DataServer def rename_column(table_name, column_name, new_column_name) raise NotImplementedError, "rename_column is not implemented yet in the IBM_DB Adapter" end def primary_key return "INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 100) PRIMARY KEY" end # Method that returns the last automatically generated ID # on the given +@connection+. This method is required by the +insert+ # method def last_generated_id # Queries the db to obtain the last ID that was automatically generated sql = "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1" stmt = IBM_DB::exec(@connection, sql) # Fetches the only record available (containing the last id) IBM_DB::fetch_row(stmt) # Retrieves and returns the result of the query with the last id. IBM_DB::result(stmt,0) end def change_column(table_name, column_name, type, options) data_type = @caller.type_to_sql(type, options[:limit], options[:precision], options[:scale]) begin @caller.execute "ALTER TABLE #{table_name} ALTER #{column_name} SET DATA TYPE #{data_type}" rescue StandardError => exec_err if exec_err.message.include?('SQLCODE=-190') raise StatementInvalid, "Please consult documentation for compatible data types while changing column datatype. The column datatype change to [#{data_type}] is not supported by this data server: #{exec_err}" else raise end end reorg_table(table_name) if !options[:default].nil? @caller.change_column_default(table_name, column_name, options[:default]) end end # Fetches all the results available. IBM_DB::fetch_assoc(stmt) returns # an hash for each single record. # The loop stops when there aren't any more valid records to fetch def select_all(sql, name, stmt, results) if (!@offset.nil? && @offset >= 0) || (!@limit.nil? && @limit > 0) # We know at this point that there is an offset and/or a limit # Check if the cursor type is set correctly cursor_type = IBM_DB::get_option stmt, IBM_DB::SQL_ATTR_CURSOR_TYPE, 0 if (cursor_type == IBM_DB::SQL_CURSOR_STATIC) index = 0 # Get @limit rows starting at @offset while (index < @limit) # We increment the offset by 1 because for DB2 the offset of the initial row is 1 instead of 0 if single_hash = IBM_DB::fetch_assoc(stmt, @offset + index + 1) # Add the record to the +results+ array results << single_hash index = index + 1 else # break from the while loop break end end else # cursor != IBM_DB::SQL_CURSOR_STATIC # If the result set contains a LOB, the cursor type will never be SQL_CURSOR_STATIC # because DB2 does not allow this. We can't use the offset mechanism because the cursor # is not scrollable. In this case, ignore first @offset rows and return rows starting # at @offset to @offset + @limit index = 0 while (index < @offset + @limit) if single_hash = IBM_DB::fetch_assoc(stmt) # Add the record to the +results+ array only from row @offset to @offset + @limit if (index >= @offset) results << single_hash end index = index + 1 else # break from the while loop break end end end # This is the case where limit is set to zero # Simply return an empty +results+ elsif (!@limit.nil? && @limit == 0) results # No limits or offsets specified else while single_hash = IBM_DB::fetch_assoc(stmt) # Add the record to the +results+ array results << single_hash end end # Assign the instance variables to nil. We will not be using them again @offset = nil @limit = nil end def execute(sql, name) # Check if there is a limit and/or an offset # If so then make sure and use a static cursor type if (!@offset.nil? && @offset >= 0) || (!@limit.nil? && @limit > 0) begin # Set the cursor type to static so we can later utilize the offset and limit correctly if stmt = IBM_DB::exec(@connection, sql, {IBM_DB::SQL_ATTR_CURSOR_TYPE => IBM_DB::SQL_CURSOR_STATIC}) stmt # Return the statement object else raise StatementInvalid, IBM_DB::stmt_errormsg end rescue StandardError error_msg = IBM_DB::conn_errormsg ? IBM_DB::conn_errormsg : IBM_DB::stmt_errormsg if error_msg && !error_msg.empty? raise "Failed to execute statement due to error: #{error_msg}" else raise end end else begin if stmt = IBM_DB::exec(@connection, sql) stmt # Return the statement object else raise StatementInvalid, IBM_DB::stmt_errormsg end rescue StandardError error_msg = IBM_DB::conn_errormsg ? IBM_DB::conn_errormsg : IBM_DB::stmt_errormsg if error_msg && !error_msg.empty? raise "Failed to execute statement due to error: #{error_msg}" else raise end end end end def query_offset_limit(sql, offset, limit) @limit = limit @offset = offset end end # class IBM_DB2 class IBM_DB2_LUW < IBM_DB2 # Reorganizes the table for column changes def reorg_table(table_name) @caller.execute("CALL ADMIN_CMD('REORG TABLE #{table_name}')") end def select_all(sql, name, stmt, results) # Fetches all the results available. IBM_DB::fetch_assoc(stmt) returns # an hash for each single record. # The loop stops when there aren't any more valid records to fetch while single_hash = IBM_DB::fetch_assoc(stmt) # Add the record to the +results+ array results << single_hash end end def execute(sql, name) begin if stmt = IBM_DB::exec(@connection, sql) stmt # Return the statement object else raise StatementInvalid, IBM_DB::stmt_errormsg end rescue StandardError error_msg = IBM_DB::conn_errormsg ? IBM_DB::conn_errormsg : IBM_DB::stmt_errormsg if error_msg && !error_msg.empty? raise "Failed to execute statement due to error: #{error_msg}" else raise end end end def query_offset_limit(sql, offset, limit) # Defines what will be the last record last_record = offset + limit # Transforms the SELECT query in order to retrieve/fetch only # a number of records after the specified offset. # 'select' or 'SELECT' is replaced with the partial query below that adds the sys_row_num column # to select with the condition of this column being between offset+1 and the offset+limit sql.gsub!(/SELECT/i,"SELECT O.* FROM (SELECT I.*, ROW_NUMBER() OVER () sys_row_num FROM (SELECT") # The final part of the query is appended to include a WHERE...BETWEEN...AND condition, # and retrieve only a LIMIT number of records starting from the OFFSET+1 sql << ") AS I) AS O WHERE sys_row_num BETWEEN #{offset+1} AND #{last_record}" end end # class IBM_DB2_LUW module HostedDataServer require 'pathname' #find DB2-i5-zOS rezerved words file relative path rfile = Pathname.new(File.dirname(__FILE__)).parent + 'vendor' + 'db2-i5-zOS.yaml' if rfile RESERVED_WORDS = open(rfile.to_s) {|f| YAML.load(f) } def check_reserved_words(col_name) if RESERVED_WORDS[col_name] '"' + RESERVED_WORDS[col_name] + '"' else col_name end end else raise "Failed to locate IBM_DB Adapter dependency: #{rfile}" end end # module HostedDataServer class IBM_DB2_ZOS < IBM_DB2 # since v9 doesn't need, suggest putting it in HostedDataServer? def create_index_after_table(table_name, caller) caller.add_index(table_name, "id", :unique => true) end def remove_column(table_name, column_name) raise NotImplementedError, "remove_column is not supported by the DB2 for zOS data server" end # DB2 z/OS only allows a "null" or an empty binary string # as a default for a BLOB column # if value is not empty or equivalent to the string "null", # the server will complain def set_blob_default(value) "#{value}" end end # class IBM_DB2_ZOS class IBM_DB2_ZOS_8 < IBM_DB2_ZOS include HostedDataServer # Setting the SQLID on z/OS will also update the CURRENT SCHEMA # special register, but not vice versa def set_schema(schema) @caller.execute("SET CURRENT SQLID ='#{schema.upcase}'") end # This call is needed on DB2 z/OS v8 for the creation of tables # with LOBs. When issued, this call does the following: # DB2 creates LOB table spaces, auxiliary tables, and indexes on auxiliary # tables for LOB columns. def setup_for_lob_table() @caller.execute "SET CURRENT RULES = 'STD'" end end # class IBM_DB2_ZOS_8 class IBM_DB2_I5 < IBM_DB2 include HostedDataServer end # class IBM_DB2_I5 end # module ConnectionAdapters end # module ActiveRecord