Improve performance and functionality of the postgresql adapter. Closes #8049 [roderickvd]

git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@7329 5ecf4fe2-1ee6-0310-87b1-e25e094e27de

4 files changed, 234 insertions, 34 deletions

diff --git a/activerecord/test/datatype_test_postgresql.rb b/activerecord/test/datatype_test_postgresql.rb
index c4c3318de4..53961135c3 100644
--- a/activerecord/test/datatype_test_postgresql.rb
+++ b/activerecord/test/datatype_test_postgresql.rb
@@ -1,52 +1,203 @@
 require 'abstract_unit'
 
-class PostgresqlDatatype < ActiveRecord::Base
+class PostgresqlArray < ActiveRecord::Base
 end
 
-class PGDataTypeTest < Test::Unit::TestCase
-  self.use_transactional_fixtures = false
+class PostgresqlMoney < ActiveRecord::Base
+end
+
+class PostgresqlNumber < ActiveRecord::Base
+end
+
+class PostgresqlTime < ActiveRecord::Base
+end
+
+class PostgresqlNetworkAddress < ActiveRecord::Base
+end
+
+class PostgresqlBitString < ActiveRecord::Base
+end
 
-  TABLE_NAME = 'postgresql_datatypes'
-  COLUMNS = [
-    'id SERIAL PRIMARY KEY',
-    'commission_by_quarter INTEGER[]',
-    'nicknames TEXT[]'
-  ]
+class PostgresqlOid < ActiveRecord::Base
+end
+
+class PostgresqlDataTypeTest < Test::Unit::TestCase
+  self.use_transactional_fixtures = false
 
   def setup
     @connection = ActiveRecord::Base.connection
-    @connection.execute "CREATE TABLE #{TABLE_NAME} (#{COLUMNS.join(',')})"
-    @connection.execute "INSERT INTO #{TABLE_NAME} (commission_by_quarter, nicknames) VALUES ( '{35000,21000,18000,17000}', '{foo,bar,baz}' )"
-    @first = PostgresqlDatatype.find( 1 )
-  end
 
-  def teardown
-    @connection.execute "DROP TABLE #{TABLE_NAME}"
+    @connection.execute("INSERT INTO postgresql_arrays (commission_by_quarter, nicknames) VALUES ( '{35000,21000,18000,17000}', '{foo,bar,baz}' )")
+    @first_array = PostgresqlArray.find(1)
+
+    @connection.execute("INSERT INTO postgresql_moneys (wealth) VALUES ('$567.89')")
+    @connection.execute("INSERT INTO postgresql_moneys (wealth) VALUES ('-$567.89')")
+    @first_money = PostgresqlMoney.find(1)
+    @second_money = PostgresqlMoney.find(2)
+
+    @connection.execute("INSERT INTO postgresql_numbers (single, double) VALUES (123.456, 123456.789)")
+    @first_number = PostgresqlNumber.find(1)
+
+    @connection.execute("INSERT INTO postgresql_times (time_interval) VALUES ('1 year 2 days ago')")
+    @first_time = PostgresqlTime.find(1)
+
+    @connection.execute("INSERT INTO postgresql_network_addresses (cidr_address, inet_address, mac_address) VALUES('192.168.0/24', '172.16.1.254/32', '01:23:45:67:89:0a')")
+    @first_network_address = PostgresqlNetworkAddress.find(1)
+    
+    @connection.execute("INSERT INTO postgresql_bit_strings (bit_string, bit_string_varying) VALUES (B'00010101', X'15')")
+    @first_bit_string = PostgresqlBitString.find(1)
+    
+    @connection.execute("INSERT INTO postgresql_oids (obj_id) VALUES (1234)")
+    @first_oid = PostgresqlOid.find(1)
   end
 
   def test_data_type_of_array_types
-    assert_equal :string, @first.column_for_attribute("commission_by_quarter").type
-    assert_equal :string, @first.column_for_attribute("nicknames").type
+    assert_equal :string, @first_array.column_for_attribute(:commission_by_quarter).type
+    assert_equal :string, @first_array.column_for_attribute(:nicknames).type
+  end
+
+  def test_data_type_of_money_types
+    assert_equal :decimal, @first_money.column_for_attribute(:wealth).type
+  end
+
+  def test_data_type_of_number_types
+    assert_equal :float, @first_number.column_for_attribute(:single).type
+    assert_equal :float, @first_number.column_for_attribute(:double).type
+  end
+
+  def test_data_type_of_time_types
+    assert_equal :string, @first_time.column_for_attribute(:time_interval).type
+  end
+
+  def test_data_type_of_network_address_types
+    assert_equal :string, @first_network_address.column_for_attribute(:cidr_address).type
+    assert_equal :string, @first_network_address.column_for_attribute(:inet_address).type
+    assert_equal :string, @first_network_address.column_for_attribute(:mac_address).type
+  end
+
+  def test_data_type_of_bit_string_types
+    assert_equal :string, @first_bit_string.column_for_attribute(:bit_string).type
+    assert_equal :string, @first_bit_string.column_for_attribute(:bit_string_varying).type
+  end
+
+  def test_data_type_of_oid_types
+    assert_equal :integer, @first_oid.column_for_attribute(:obj_id).type
   end
 
   def test_array_values
-    assert_equal '{35000,21000,18000,17000}', @first.commission_by_quarter
-    assert_equal '{foo,bar,baz}', @first.nicknames
+   assert_equal '{35000,21000,18000,17000}', @first_array.commission_by_quarter
+   assert_equal '{foo,bar,baz}', @first_array.nicknames
+  end
+
+  def test_money_values
+    assert_equal 567.89, @first_money.wealth
+    assert_equal -567.89, @second_money.wealth
+  end
+
+  def test_number_values
+    assert_equal 123.456, @first_number.single
+    assert_equal 123456.789, @first_number.double
+  end
+
+  def test_time_values
+    assert_equal '-1 years -2 days', @first_time.time_interval
+  end
+
+  def test_network_address_values
+    assert_equal '192.168.0.0/24', @first_network_address.cidr_address
+    assert_equal '172.16.1.254', @first_network_address.inet_address
+    assert_equal '01:23:45:67:89:0a', @first_network_address.mac_address
+  end
+
+  def test_bit_string_values
+    assert_equal '00010101', @first_bit_string.bit_string
+    assert_equal '00010101', @first_bit_string.bit_string_varying
+  end
+
+  def test_oid_values
+    assert_equal 1234, @first_oid.obj_id
   end
 
   def test_update_integer_array
     new_value = '{32800,95000,29350,17000}'
-    assert @first.commission_by_quarter = new_value
-    assert @first.save
-    assert @first.reload
-    assert_equal @first.commission_by_quarter, new_value
+    assert @first_array.commission_by_quarter = new_value
+    assert @first_array.save
+    assert @first_array.reload
+    assert_equal @first_array.commission_by_quarter, new_value
+    assert @first_array.commission_by_quarter = new_value
+    assert @first_array.save
+    assert @first_array.reload
+    assert_equal @first_array.commission_by_quarter, new_value
   end
 
   def test_update_text_array
     new_value = '{robby,robert,rob,robbie}'
-    assert @first.nicknames = new_value
-    assert @first.save
-    assert @first.reload
-    assert_equal @first.nicknames, new_value
+    assert @first_array.nicknames = new_value
+    assert @first_array.save
+    assert @first_array.reload
+    assert_equal @first_array.nicknames, new_value
+    assert @first_array.nicknames = new_value
+    assert @first_array.save
+    assert @first_array.reload
+    assert_equal @first_array.nicknames, new_value
+  end
+
+  def test_update_money
+    new_value = 123.45
+    assert @first_money.wealth = new_value
+    assert @first_money.save
+    assert @first_money.reload
+    assert_equal @first_money.wealth, new_value
+  end
+
+  def test_update_number
+    new_single = 789.012
+    new_double = 789012.345
+    assert @first_number.single = new_single
+    assert @first_number.double = new_double
+    assert @first_number.save
+    assert @first_number.reload
+    assert_equal @first_number.single, new_single
+    assert_equal @first_number.double, new_double
+  end
+
+  def test_update_time
+    assert @first_time.time_interval = '2 years 3 minutes'
+    assert @first_time.save
+    assert @first_time.reload
+    assert_equal @first_time.time_interval, '2 years 00:03:00'
+  end
+
+  def test_update_network_address
+    new_cidr_address = '10.1.2.3/32'
+    new_inet_address = '10.0.0.0/8'
+    new_mac_address = 'bc:de:f0:12:34:56'
+    assert @first_network_address.cidr_address = new_cidr_address
+    assert @first_network_address.inet_address = new_inet_address
+    assert @first_network_address.mac_address = new_mac_address
+    assert @first_network_address.save
+    assert @first_network_address.reload
+    assert_equal @first_network_address.cidr_address, new_cidr_address
+    assert_equal @first_network_address.inet_address, new_inet_address
+    assert_equal @first_network_address.mac_address, new_mac_address
+  end
+
+  def test_update_bit_string
+    new_bit_string = '11111111'
+    new_bit_string_varying = 'FF'
+    assert @first_bit_string.bit_string = new_bit_string
+    assert @first_bit_string.bit_string_varying = new_bit_string_varying
+    assert @first_bit_string.save
+    assert @first_bit_string.reload
+    assert_equal @first_bit_string.bit_string, new_bit_string
+    assert_equal @first_bit_string.bit_string, @first_bit_string.bit_string_varying
+  end
+
+  def test_update_oid
+    new_value = 567890
+    assert @first_oid.obj_id = new_value
+    assert @first_oid.save
+    assert @first_oid.reload
+    assert_equal @first_oid.obj_id, new_value
   end
 end
diff --git a/activerecord/test/finder_test.rb b/activerecord/test/finder_test.rb
index d9809d4b7e..ef995aeb3f 100644
--- a/activerecord/test/finder_test.rb
+++ b/activerecord/test/finder_test.rb
@@ -268,18 +268,20 @@ class FinderTest < Test::Unit::TestCase
   end
 
   def test_bind_enumerable
+    quoted_abc = %(#{ActiveRecord::Base.connection.quote('a')},#{ActiveRecord::Base.connection.quote('b')},#{ActiveRecord::Base.connection.quote('c')})
+
     assert_equal '1,2,3', bind('?', [1, 2, 3])
-    assert_equal %('a','b','c'), bind('?', %w(a b c))
+    assert_equal quoted_abc, bind('?', %w(a b c))
 
     assert_equal '1,2,3', bind(':a', :a => [1, 2, 3])
-    assert_equal %('a','b','c'), bind(':a', :a => %w(a b c)) # '
+    assert_equal quoted_abc, bind(':a', :a => %w(a b c)) # '
 
     require 'set'
     assert_equal '1,2,3', bind('?', Set.new([1, 2, 3]))
-    assert_equal %('a','b','c'), bind('?', Set.new(%w(a b c)))
+    assert_equal quoted_abc, bind('?', Set.new(%w(a b c)))
 
     assert_equal '1,2,3', bind(':a', :a => Set.new([1, 2, 3]))
-    assert_equal %('a','b','c'), bind(':a', :a => Set.new(%w(a b c))) # '
+    assert_equal quoted_abc, bind(':a', :a => Set.new(%w(a b c))) # '
   end
 
   def test_bind_empty_enumerable
@@ -290,7 +292,7 @@ class FinderTest < Test::Unit::TestCase
   end
 
   def test_bind_string
-    assert_equal "''", bind('?', '')
+    assert_equal ActiveRecord::Base.connection.quote(''), bind('?', '')
   end
 
   def test_bind_record
@@ -302,8 +304,8 @@ class FinderTest < Test::Unit::TestCase
   end
 
   def test_string_sanitation
-    assert_not_equal "'something ' 1=1'", ActiveRecord::Base.sanitize("something ' 1=1")
-    assert_equal "'something; select table'", ActiveRecord::Base.sanitize("something; select table")
+    assert_not_equal "#{ActiveRecord::Base.connection.quoted_string_prefix}'something ' 1=1'", ActiveRecord::Base.sanitize("something ' 1=1")
+    assert_equal "#{ActiveRecord::Base.connection.quoted_string_prefix}'something; select table'", ActiveRecord::Base.sanitize("something; select table")
   end
 
   def test_count
diff --git a/activerecord/test/fixtures/db_definitions/postgresql.drop.sql b/activerecord/test/fixtures/db_definitions/postgresql.drop.sql
index 3afe6d39a9..31e2d38743 100644
--- a/activerecord/test/fixtures/db_definitions/postgresql.drop.sql
+++ b/activerecord/test/fixtures/db_definitions/postgresql.drop.sql
@@ -35,3 +35,10 @@ DROP TABLE legacy_things;
 DROP TABLE numeric_data;
 DROP TABLE column_data;
 DROP TABLE mixed_case_monkeys;
+DROP TABLE postgresql_arrays;
+DROP TABLE postgresql_moneys;
+DROP TABLE postgresql_numbers;
+DROP TABLE postgresql_times;
+DROP TABLE postgresql_network_addresses;
+DROP TABLE postgresql_bit_strings;
+DROP TABLE postgresql_oids;
diff --git a/activerecord/test/fixtures/db_definitions/postgresql.sql b/activerecord/test/fixtures/db_definitions/postgresql.sql
index 15a61ea8c4..bbe54599d8 100644
--- a/activerecord/test/fixtures/db_definitions/postgresql.sql
+++ b/activerecord/test/fixtures/db_definitions/postgresql.sql
@@ -245,3 +245,43 @@ CREATE TABLE mixed_case_monkeys (
  "monkeyID" INTEGER PRIMARY KEY,
  "fleaCount" INTEGER
 );
+
+CREATE TABLE postgresql_arrays (
+  id SERIAL PRIMARY KEY,
+  commission_by_quarter INTEGER[],
+  nicknames TEXT[]
+);
+
+CREATE TABLE postgresql_moneys (
+  id SERIAL PRIMARY KEY,
+  wealth MONEY
+);
+
+CREATE TABLE postgresql_numbers (
+  id SERIAL PRIMARY KEY,
+  single REAL,
+  double DOUBLE PRECISION
+);
+
+CREATE TABLE postgresql_times (
+  id SERIAL PRIMARY KEY,
+  time_interval INTERVAL
+);
+
+CREATE TABLE postgresql_network_addresses (
+  id SERIAL PRIMARY KEY,
+  cidr_address CIDR,
+  inet_address INET,
+  mac_address MACADDR
+);
+
+CREATE TABLE postgresql_bit_strings (
+  id SERIAL PRIMARY KEY,
+  bit_string BIT(8),
+  bit_string_varying BIT VARYING(8)
+);
+
+CREATE TABLE postgresql_oids (
+  id SERIAL PRIMARY KEY,
+  obj_id OID
+);