From a5708b4c1f137baba8510ca1e1985bd1f878604b Mon Sep 17 00:00:00 2001
From: Marcel Molina <marcel@vernix.org>
Date: Wed, 4 Jan 2006 04:17:23 +0000
Subject: Multiple enhancements and adjustments to DB2 adaptor. Closes #3377.
git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@3380 5ecf4fe2-1ee6-0310-87b1-e25e094e27de
---
activerecord/CHANGELOG | 2 +
.../connection_adapters/db2_adapter.rb | 152 +++++++++-------
activerecord/lib/active_record/vendor/db2.rb | 11 +-
activerecord/test/adapter_test.rb | 5 +-
activerecord/test/associations_join_model_test.rb | 2 +-
activerecord/test/fixtures/db_definitions/db2.sql | 200 ++++++++++-----------
activerecord/test/fixtures/db_definitions/db22.sql | 4 +-
7 files changed, 207 insertions(+), 169 deletions(-)
(limited to 'activerecord')
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
);
--
cgit v1.2.3