diff options
| -rw-r--r-- | activerecord/CHANGELOG | 2 | ||||
| -rw-r--r-- | activerecord/lib/active_record/connection_adapters/db2_adapter.rb | 152 | ||||
| -rw-r--r-- | activerecord/lib/active_record/vendor/db2.rb | 11 | ||||
| -rw-r--r-- | activerecord/test/adapter_test.rb | 5 | ||||
| -rw-r--r-- | activerecord/test/associations_join_model_test.rb | 2 | ||||
| -rw-r--r-- | activerecord/test/fixtures/db_definitions/db2.sql | 200 | ||||
| -rw-r--r-- | activerecord/test/fixtures/db_definitions/db22.sql | 4 |
7 files changed, 207 insertions, 169 deletions
diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG index 3b53432cc6..590f4d8300 100644 --- a/activerecord/CHANGELOG +++ b/activerecord/CHANGELOG @@ -1,5 +1,7 @@ *SVN* +* Multiple enhancements and adjustments to DB2 adaptor. #3377 [contact@maik-schmidt.de] + * Sanitize scoped conditions. [Marcel Molina Jr.] * Added option to Base.reflection_of_all_associations to specify a specific association to scope the call. For example Base.reflection_of_all_associations(:has_many) [DHH] diff --git a/activerecord/lib/active_record/connection_adapters/db2_adapter.rb b/activerecord/lib/active_record/connection_adapters/db2_adapter.rb index 232971e4ec..7e3627261a 100644 --- a/activerecord/lib/active_record/connection_adapters/db2_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/db2_adapter.rb @@ -14,16 +14,17 @@ begin config = config.symbolize_keys usr = config[:username] pwd = config[:password] + schema = config[:schema] if config.has_key?(:database) database = config[:database] else - raise ArgumentError, "No database specified. Missing argument: database." + raise ArgumentError, 'No database specified. Missing argument: database.' end connection = DB2::Connection.new(DB2::Environment.new) connection.connect(database, usr, pwd) - ConnectionAdapters::DB2Adapter.new(connection) + ConnectionAdapters::DB2Adapter.new(connection, logger, :schema => schema) end end @@ -35,7 +36,18 @@ begin # * <tt>:username</tt> -- Defaults to nothing # * <tt>:password</tt> -- Defaults to nothing # * <tt>:database</tt> -- The name of the database. No default, must be provided. + # * <tt>:schema</tt> -- Database schema to be set initially. class DB2Adapter < AbstractAdapter + def initialize(connection, logger, connection_options) + super(connection, logger) + @connection_options = connection_options + if schema = @connection_options[:schema] + with_statement do |stmt| + stmt.exec_direct("SET SCHEMA=#{schema}") + end + end + end + def select_all(sql, name = nil) select(sql, name) end @@ -51,14 +63,12 @@ begin def execute(sql, name = nil) rows_affected = 0 - - log(sql, name) do - stmt = DB2::Statement.new(@connection) - stmt.exec_direct(sql) - rows_affected = stmt.row_count - stmt.free + with_statement do |stmt| + log(sql, name) do + stmt.exec_direct(sql) + rows_affected = stmt.row_count + end end - rows_affected end @@ -92,64 +102,68 @@ begin end def add_limit_offset!(sql, options) - if options[:limit] and !options[:limit].nil? - # "FETCH FIRST 0 ROWS ONLY" is not allowed, so we have - # to use a cheap trick. - if options[:limit] == 0 - if sql =~ /WHERE/i - sql.sub!(/WHERE/i, 'WHERE 1 = 2 AND ') - elsif - sql =~ /ORDER\s+BY/i - sql.sub!(/ORDER\s+BY/i, 'WHERE 1 = 2 ORDER BY') - else - sql << 'WHERE 1 = 2' - end - else - sql << " FETCH FIRST #{options[:limit]} ROWS ONLY" - end - end - if options[:offset] and !options[:offset].nil? - raise ArgumentError, ':offset option is not yet supported!' + if limit = options[:limit] + offset = options[:offset] || 0 + # The following trick was added by andrea+rails@webcom.it. + sql.gsub!(/SELECT/i, 'SELECT B.* FROM (SELECT A.*, row_number() over () AS internal$rownum FROM (SELECT') + sql << ") A ) B WHERE B.internal$rownum > #{offset} AND B.internal$rownum <= #{limit + offset}" end end def tables(name = nil) - stmt = DB2::Statement.new(@connection) result = [] - stmt.tables.each { |t| result << t[2].downcase } - stmt.free + with_statement do |stmt| + stmt.tables.each { |t| result << t[2].downcase } + end result end + def indexes(table_name, name = nil) + tmp = {} + with_statement do |stmt| + stmt.indexes(table_name.upcase).each do |t| + next unless t[5] + next if t[4] == 'SYSIBM' # Skip system indexes. + idx_name = t[5].downcase + col_name = t[8].downcase + if tmp.has_key?(idx_name) + tmp[idx_name].columns << col_name + else + is_unique = t[3] == 0 + tmp[idx_name] = IndexDefinition.new(table_name, idx_name, is_unique, [col_name]) + end + end + end + tmp.values + end + def columns(table_name, name = nil) - stmt = DB2::Statement.new(@connection) result = [] - - stmt.columns(table_name.upcase).each do |c| - c_name = c[3].downcase - c_default = c[12] == 'NULL' ? nil : c[12] - c_type = c[5].downcase - c_type += "(#{c[6]})" if !c[6].nil? && c[6] != '' - result << Column.new(c_name, c_default, c_type) - end - - stmt.free + with_statement do |stmt| + stmt.columns(table_name.upcase).each do |c| + c_name = c[3].downcase + c_default = c[12] == 'NULL' ? nil : c[12] + c_type = c[5].downcase + c_type += "(#{c[6]})" if !c[6].nil? && c[6] != '' + result << Column.new(c_name, c_default, c_type) + end + end result end def native_database_types { - :primary_key => "int generated by default as identity primary key", - :string => { :name => "varchar", :limit => 255 }, - :text => { :name => "clob", :limit => 32768 }, - :integer => { :name => "int" }, - :float => { :name => "float" }, - :datetime => { :name => "timestamp" }, - :timestamp => { :name => "timestamp" }, - :time => { :name => "time" }, - :date => { :name => "date" }, - :binary => { :name => "blob", :limit => 32768 }, - :boolean => { :name => "decimal", :limit => 1 } + :primary_key => 'int generated by default as identity primary key', + :string => { :name => 'varchar', :limit => 255 }, + :text => { :name => 'clob', :limit => 32768 }, + :integer => { :name => 'int' }, + :float => { :name => 'float' }, + :datetime => { :name => 'timestamp' }, + :timestamp => { :name => 'timestamp' }, + :time => { :name => 'time' }, + :date => { :name => 'date' }, + :binary => { :name => 'blob', :limit => 32768 }, + :boolean => { :name => 'decimal', :limit => 1 } } end @@ -161,8 +175,24 @@ begin '0' end + def active? + @connection.select_one 'select 1 from ibm.sysdummy1' + true + rescue Exception + false + end + + def reconnect! + end + private + def with_statement + stmt = DB2::Statement.new(@connection) + yield stmt + stmt.free + end + def last_insert_id row = select_one(<<-GETID.strip) with temp(id) as (values (identity_val_local())) select * from temp @@ -171,17 +201,17 @@ begin end def select(sql, name = nil) - stmt = nil - log(sql, name) do - stmt = DB2::Statement.new(@connection) - stmt.exec_direct("#{sql.gsub(/=\s*null/i, 'IS NULL')} with ur") - end - rows = [] - while row = stmt.fetch_as_hash - rows << row + with_statement do |stmt| + log(sql, name) do + stmt.exec_direct("#{sql.gsub(/=\s*null/i, 'IS NULL')} with ur") + end + + while row = stmt.fetch_as_hash + row.delete('internal$rownum') + rows << row + end end - stmt.free rows end end diff --git a/activerecord/lib/active_record/vendor/db2.rb b/activerecord/lib/active_record/vendor/db2.rb index 42171beda8..5ebd348a0a 100644 --- a/activerecord/lib/active_record/vendor/db2.rb +++ b/activerecord/lib/active_record/vendor/db2.rb @@ -110,13 +110,18 @@ module DB2 check_rc(rc) end - def columns(table_name) - check_rc(SQLColumns(@handle, "", "%", table_name, "%")) + def columns(table_name, schema_name = '%') + check_rc(SQLColumns(@handle, '', schema_name, table_name, '%')) fetch_all end def tables - check_rc(SQLTables(@handle, "", "%", "%", "TABLE")) + check_rc(SQLTables(@handle, '', '%', '%', 'TABLE')) + fetch_all + end + + def indexes(table_name) + check_rc(SQLStatistics(@handle, '', '', table_name, SQL_INDEX_ALL, SQL_ENSURE)) fetch_all end diff --git a/activerecord/test/adapter_test.rb b/activerecord/test/adapter_test.rb index 7d427cda50..034e9ac45a 100644 --- a/activerecord/test/adapter_test.rb +++ b/activerecord/test/adapter_test.rb @@ -19,13 +19,14 @@ class AdapterTest < Test::Unit::TestCase def test_indexes if @connection.respond_to?(:indexes) + idx_name = "accounts_idx" indexes = @connection.indexes("accounts") assert indexes.empty? - @connection.add_index :accounts, :firm_id + @connection.add_index :accounts, :firm_id, :name => idx_name indexes = @connection.indexes("accounts") assert_equal "accounts", indexes.first.table - assert_equal "accounts_firm_id_index", indexes.first.name + assert_equal idx_name, indexes.first.name assert !indexes.first.unique assert_equal ["firm_id"], indexes.first.columns else diff --git a/activerecord/test/associations_join_model_test.rb b/activerecord/test/associations_join_model_test.rb index f385bd2175..f8d8b77813 100644 --- a/activerecord/test/associations_join_model_test.rb +++ b/activerecord/test/associations_join_model_test.rb @@ -40,7 +40,7 @@ class AssociationsJoinModelTest < Test::Unit::TestCase end def test_has_many_with_piggyback - assert_equal "2", categories(:sti_test).authors.first.post_id + assert_equal "2", categories(:sti_test).authors.first.post_id.to_s end def test_has_many_find_all diff --git a/activerecord/test/fixtures/db_definitions/db2.sql b/activerecord/test/fixtures/db_definitions/db2.sql index 49f31aba67..c50e7178f8 100644 --- a/activerecord/test/fixtures/db_definitions/db2.sql +++ b/activerecord/test/fixtures/db_definitions/db2.sql @@ -1,196 +1,196 @@ CREATE TABLE accounts ( - id int generated by default as identity (start with +10000), - firm_id int default NULL, - credit_limit int default NULL, + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + firm_id INT DEFAULT NULL, + credit_limit INT DEFAULT NULL, PRIMARY KEY (id) ); CREATE TABLE companies ( - id int generated by default as identity (start with +10000), - type varchar(50) default NULL, - ruby_type varchar(50) default NULL, - firm_id int default NULL, - name varchar(50) default NULL, - client_of int default NULL, - rating int default 1, + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + type VARCHAR(50) DEFAULT NULL, + ruby_type VARCHAR(50) DEFAULT NULL, + firm_id INT DEFAULT NULL, + name VARCHAR(50) DEFAULT NULL, + client_of INT DEFAULT NULL, + rating INT DEFAULT 1, PRIMARY KEY (id) ); CREATE TABLE topics ( - id int generated by default as identity (start with +10000), - title varchar(255) default NULL, - author_name varchar(255) default NULL, - author_email_address varchar(255) default NULL, - written_on timestamp default NULL, - bonus_time time default NULL, - last_read date default NULL, - content varchar(3000), - approved smallint default 1, - replies_count int default 0, - parent_id int default NULL, - type varchar(50) default NULL, + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + title VARCHAR(255) DEFAULT NULL, + author_name VARCHAR(255) DEFAULT NULL, + author_email_address VARCHAR(255) DEFAULT NULL, + written_on TIMESTAMP DEFAULT NULL, + bonus_time TIME DEFAULT NULL, + last_read DATE DEFAULT NULL, + content VARCHAR(3000), + approved SMALLINT DEFAULT 1, + replies_count INT DEFAULT 0, + parent_id INT DEFAULT NULL, + type VARCHAR(50) DEFAULT NULL, PRIMARY KEY (id) ); CREATE TABLE developers ( - id int generated by default as identity (start with +10000), - name varchar(100) default NULL, - salary int default 70000, - created_at timestamp default NULL, - updated_at timestamp default NULL, + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + name VARCHAR(100) DEFAULT NULL, + salary INT DEFAULT 70000, + created_at TIMESTAMP DEFAULT NULL, + updated_at TIMESTAMP DEFAULT NULL, PRIMARY KEY (id) ); CREATE TABLE projects ( - id int generated by default as identity (start with +10000), - name varchar(100) default NULL, - type varchar(255) default NULL, + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + name VARCHAR(100) DEFAULT NULL, + type VARCHAR(255) DEFAULT NULL, PRIMARY KEY (id) ); CREATE TABLE developers_projects ( - developer_id int NOT NULL, - project_id int NOT NULL, - joined_on date default NULL, - access_level smallint default 1 + developer_id INT NOT NULL, + project_id INT NOT NULL, + joined_on DATE DEFAULT NULL, + access_level SMALLINT DEFAULT 1 ); CREATE TABLE orders ( - id int generated by default as identity (start with +10000), - name varchar(100) default NULL, - billing_customer_id int default NULL, - shipping_customer_id int default NULL, + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + name VARCHAR(100) DEFAULT NULL, + billing_customer_id INT DEFAULT NULL, + shipping_customer_id INT DEFAULT NULL, PRIMARY KEY (id) ); CREATE TABLE customers ( - id int generated by default as identity (start with +10000), - name varchar(100) default NULL, - balance int default 0, - address_street varchar(100) default NULL, - address_city varchar(100) default NULL, - address_country varchar(100) default NULL, - gps_location varchar(100) default NULL, + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + name VARCHAR(100) DEFAULT NULL, + balance INT DEFAULT 0, + address_street VARCHAR(100) DEFAULT NULL, + address_city VARCHAR(100) DEFAULT NULL, + address_country VARCHAR(100) DEFAULT NULL, + gps_location VARCHAR(100) DEFAULT NULL, PRIMARY KEY (id) ); CREATE TABLE movies ( - movieid int generated by default as identity (start with +10000), - name varchar(100) default NULL, + movieid INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + name VARCHAR(100) DEFAULT NULL, PRIMARY KEY (movieid) ); CREATE TABLE subscribers ( - nick varchar(100) NOT NULL, - name varchar(100) default NULL, + nick VARCHAR(100) NOT NULL, + name VARCHAR(100) DEFAULT NULL, PRIMARY KEY (nick) ); CREATE TABLE booleantests ( - id int generated by default as identity (start with +10000), - value int default NULL, + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + value INT DEFAULT NULL, PRIMARY KEY (id) ); CREATE TABLE auto_id_tests ( - auto_id int generated by default as identity (start with +10000), - value int default NULL, + auto_id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + value INT DEFAULT NULL, PRIMARY KEY (auto_id) ); CREATE TABLE entrants ( - id int NOT NULL PRIMARY KEY, - name varchar(255) NOT NULL, - course_id int NOT NULL + id INT NOT NULL PRIMARY KEY, + name VARCHAR(255) NOT NULL, + course_id INT NOT NULL ); CREATE TABLE colnametests ( - id int generated by default as identity (start with +10000), - references int NOT NULL, + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + references INT NOT NULL, PRIMARY KEY (id) ); CREATE TABLE mixins ( - id int generated by default as identity (start with +10000), - parent_id int default NULL, - pos int default NULL, - created_at timestamp default NULL, - updated_at timestamp default NULL, - lft int default NULL, - rgt int default NULL, - root_id int default NULL, - type varchar(40) default NULL, + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + parent_id INT DEFAULT NULL, + pos INT DEFAULT NULL, + created_at TIMESTAMP DEFAULT NULL, + updated_at TIMESTAMP DEFAULT NULL, + lft INT DEFAULT NULL, + rgt INT DEFAULT NULL, + root_id INT DEFAULT NULL, + type VARCHAR(40) DEFAULT NULL, PRIMARY KEY (id) ); CREATE TABLE people ( - id int generated by default as identity (start with +10000), - first_name varchar(40) NOT NULL, - lock_version int default 0, - PRIMARY KEY (id) + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + first_name VARCHAR(40) NOT NULL, + lock_version INT DEFAULT 0, + PRIMARY KEY (id) ); CREATE TABLE binaries ( - id int generated by default as identity (start with +10000), - data blob(50000), - PRIMARY KEY (id) + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + data BLOB(50000), + PRIMARY KEY (id) ); CREATE TABLE computers ( - id int generated by default as identity (start with +10000), - developer int NOT NULL, - extendedWarranty int NOT NULL + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + developer INT NOT NULL, + extendedWarranty INT NOT NULL ); CREATE TABLE posts ( - id int generated by default as identity (start with +10000), - author_id int default NULL, - title varchar(255) default NULL, - type varchar(255) default NULL, - body varchar(3000) default NULL + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + author_id INT DEFAULT NULL, + title VARCHAR(255) DEFAULT NULL, + type VARCHAR(255) DEFAULT NULL, + body VARCHAR(3000) DEFAULT NULL ); CREATE TABLE comments ( - id int generated by default as identity (start with +10000), - post_id int default NULL, - type varchar(255) default NULL, - body varchar(3000) default NULL + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + post_id INT DEFAULT NULL, + type VARCHAR(255) DEFAULT NULL, + body VARCHAR(3000) DEFAULT NULL ); CREATE TABLE authors ( - id int generated by default as identity (start with +10000), - name varchar(255) default NULL + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + name VARCHAR(255) DEFAULT NULL ); CREATE TABLE tasks ( - id int generated by default as identity (start with +10000), - starting timestamp default NULL, - ending timestamp default NULL + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + starting TIMESTAMP DEFAULT NULL, + ending TIMESTAMP DEFAULT NULL ); CREATE TABLE categories ( - id int generated by default as identity (start with +10000), - name varchar(255) NOT NULL, - type varchar(40) default NULL + id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), + name VARCHAR(255) NOT NULL, + type VARCHAR(40) DEFAULT NULL ); CREATE TABLE categories_posts ( - category_id int NOT NULL, - post_id int NOT NULL + category_id INT NOT NULL, + post_id INT NOT NULL ); CREATE TABLE keyboards ( - key_number int generated by default as identity (start with +10000), + key_number INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10000), name VARCHAR(255) ); CREATE TABLE fk_test_has_pk ( - id INTEGER NOT NULL PRIMARY KEY + id INT NOT NULL PRIMARY KEY ); CREATE TABLE fk_test_has_fk ( - id INTEGER NOT NULL PRIMARY KEY, - fk_id INTEGER NOT NULL, + id INT NOT NULL PRIMARY KEY, + fk_id INT NOT NULL, FOREIGN KEY (fk_id) REFERENCES fk_test_has_pk(id) ); diff --git a/activerecord/test/fixtures/db_definitions/db22.sql b/activerecord/test/fixtures/db_definitions/db22.sql index 9198cf5f6e..853e2c73b1 100644 --- a/activerecord/test/fixtures/db_definitions/db22.sql +++ b/activerecord/test/fixtures/db_definitions/db22.sql @@ -1,5 +1,5 @@ CREATE TABLE courses ( - id int NOT NULL PRIMARY KEY, - name varchar(255) NOT NULL + id INT NOT NULL PRIMARY KEY, + name VARCHAR(255) NOT NULL ); |