From 9a947af0e79cfb8692eb7e5ae94c1b8c40756f49 Mon Sep 17 00:00:00 2001 From: Jeremy Kemper Date: Fri, 22 Feb 2008 03:26:21 +0000 Subject: PostgreSQL: support server versions 7.4 through 8.0 and the ruby-pg driver. Closes #11127 git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@8922 5ecf4fe2-1ee6-0310-87b1-e25e094e27de --- activerecord/CHANGELOG | 2 + .../connection_adapters/postgresql_adapter.rb | 90 +++++++++++++++++----- activerecord/test/cases/defaults_test.rb | 4 +- .../cases/schema_authorization_test_postgresql.rb | 4 +- activerecord/test/schema/postgresql.sql | 4 +- 5 files changed, 80 insertions(+), 24 deletions(-) diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG index 65cf4385c3..a16f9117f4 100644 --- a/activerecord/CHANGELOG +++ b/activerecord/CHANGELOG @@ -1,5 +1,7 @@ *SVN* +* PostgreSQL: support server versions 7.4 through 8.0 and the ruby-pg driver. #11127 [jdavis] + * Ensure association preloading doesn't break when an association returns nil. ##11145 [GMFlash] * Make dynamic finders respect the :include on HasManyThrough associations. #10998. [cpytel] diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 6060c4ac1a..6b0204191c 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -1,11 +1,25 @@ require 'active_record/connection_adapters/abstract_adapter' +begin + require_library_or_gem 'pg' +rescue LoadError => e + begin + require_library_or_gem 'postgres' + class PGresult + alias_method :nfields, :num_fields unless self.method_defined?(:nfields) + alias_method :ntuples, :num_tuples unless self.method_defined?(:ntuples) + alias_method :ftype, :type unless self.method_defined?(:ftype) + alias_method :cmd_tuples, :cmdtuples unless self.method_defined?(:cmd_tuples) + end + rescue LoadError + raise e + end +end + module ActiveRecord class Base # Establishes a connection to the database that's used by all Active Record objects def self.postgresql_connection(config) # :nodoc: - require_library_or_gem 'postgres' unless self.class.const_defined?(:PGconn) - config = config.symbolize_keys host = config[:host] port = config[:port] || 5432 @@ -300,7 +314,7 @@ module ActiveRecord # postgres-pr does not raise an exception when client_min_messages is set higher # than error and "SHOW standard_conforming_strings" fails, but returns an empty # PGresult instead. - has_support = execute('SHOW standard_conforming_strings')[0][0] rescue false + has_support = query('SHOW standard_conforming_strings')[0][0] rescue false self.client_min_messages = client_min_messages_old has_support end @@ -369,11 +383,22 @@ module ActiveRecord # REFERENTIAL INTEGRITY ==================================== + def supports_disable_referential_integrity?() #:nodoc: + version = query("SHOW server_version")[0][0].split('.') + (version[0].to_i >= 8 && version[1].to_i >= 1) ? true : false + rescue + return false + end + def disable_referential_integrity(&block) #:nodoc: - execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";")) + if supports_disable_referential_integrity?() then + execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";")) + end yield ensure - execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";")) + if supports_disable_referential_integrity?() then + execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";")) + end end # DATABASE STATEMENTS ====================================== @@ -390,14 +415,28 @@ module ActiveRecord super || pk && last_insert_id(table, sequence_name || default_sequence_name(table, pk)) end - # Queries the database and returns the results in an Array or nil otherwise. + # create a 2D array representing the result set + def result_as_array(res) #:nodoc: + ary = [] + for i in 0...res.ntuples do + ary << [] + for j in 0...res.nfields do + ary[i] << res.getvalue(i,j) + end + end + return ary + end + + + # Queries the database and returns the results in an Array-like object def query(sql, name = nil) #:nodoc: log(sql, name) do if @async - @connection.async_query(sql) + res = @connection.async_exec(sql) else - @connection.query(sql) + res = @connection.exec(sql) end + return result_as_array(res) end end @@ -415,7 +454,7 @@ module ActiveRecord # Executes an UPDATE query and returns the number of affected tuples. def update_sql(sql, name = nil) - super.cmdtuples + super.cmd_tuples end # Begins a transaction. @@ -566,7 +605,13 @@ module ActiveRecord # Support the 7.x and 8.0 nextval('foo'::text) as well as # the 8.1+ nextval('foo'::regclass). result = query(<<-end_sql, 'PK and custom sequence')[0] - SELECT attr.attname, split_part(def.adsrc, '''', 2) + SELECT attr.attname, + CASE + WHEN split_part(def.adsrc, '''', 2) ~ '.' THEN + substr(split_part(def.adsrc, '''', 2), + strpos(split_part(def.adsrc, '''', 2), '.')+1) + ELSE split_part(def.adsrc, '''', 2) + END FROM pg_class t JOIN pg_attribute attr ON (t.oid = attrelid) JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum) @@ -576,6 +621,7 @@ module ActiveRecord AND def.adsrc ~* 'nextval' end_sql end + # [primary_key, sequence] [result.first, result.last] rescue @@ -608,13 +654,17 @@ module ActiveRecord execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" rescue ActiveRecord::StatementInvalid # This is PostgreSQL 7.x, so we have to use a more arcane way of doing it. - begin_db_transaction - tmp_column_name = "#{column_name}_ar_tmp" - add_column(table_name, tmp_column_name, type, options) - execute "UPDATE #{quoted_table_name} SET #{quote_column_name(tmp_column_name)} = CAST(#{quote_column_name(column_name)} AS #{type_to_sql(type, options[:limit], options[:precision], options[:scale])})" - remove_column(table_name, column_name) - rename_column(table_name, tmp_column_name, column_name) - commit_db_transaction + begin + begin_db_transaction + tmp_column_name = "#{column_name}_ar_tmp" + add_column(table_name, tmp_column_name, type, options) + execute "UPDATE #{quoted_table_name} SET #{quote_column_name(tmp_column_name)} = CAST(#{quote_column_name(column_name)} AS #{type_to_sql(type, options[:limit], options[:precision], options[:scale])})" + remove_column(table_name, column_name) + rename_column(table_name, tmp_column_name, column_name) + commit_db_transaction + rescue + rollback_db_transaction + end end change_column_default(table_name, column_name, options[:default]) if options_include_default?(options) @@ -785,10 +835,10 @@ module ActiveRecord def select_raw(sql, name = nil) res = execute(sql, name) - results = res.result + results = result_as_array(res) fields = [] rows = [] - if results.length > 0 + if res.ntuples > 0 fields = res.fields results.each do |row| hashed_row = {} @@ -797,7 +847,7 @@ module ActiveRecord # then strip them off. Indeed it would be prettier to do this in # PostgreSQLColumn.string_to_decimal but would break form input # fields that call value_before_type_cast. - if res.type(cell_index) == MONEY_COLUMN_TYPE_OID + if res.ftype(cell_index) == MONEY_COLUMN_TYPE_OID # Because money output is formatted according to the locale, there are two # cases to consider (note the decimal separators): # (1) $12,345,678.12 diff --git a/activerecord/test/cases/defaults_test.rb b/activerecord/test/cases/defaults_test.rb index ac133da47a..bd19ffcc29 100644 --- a/activerecord/test/cases/defaults_test.rb +++ b/activerecord/test/cases/defaults_test.rb @@ -61,7 +61,9 @@ class DefaultTest < ActiveRecord::TestCase if current_adapter?(:PostgreSQLAdapter) def test_multiline_default_text - assert_equal "--- []\n\n", Default.columns_hash['multiline_default'].default + # older postgres versions represent the default with escapes ("\\012" for a newline) + assert ( "--- []\n\n" == Default.columns_hash['multiline_default'].default || + "--- []\\012\\012" == Default.columns_hash['multiline_default'].default) end end end diff --git a/activerecord/test/cases/schema_authorization_test_postgresql.rb b/activerecord/test/cases/schema_authorization_test_postgresql.rb index 61838d4bf7..7a0796ef53 100644 --- a/activerecord/test/cases/schema_authorization_test_postgresql.rb +++ b/activerecord/test/cases/schema_authorization_test_postgresql.rb @@ -18,7 +18,7 @@ class SchemaAuthorizationTest < ActiveRecord::TestCase @connection.execute "SET search_path TO '$user',public" set_session_auth USERS.each do |u| - @connection.execute "CREATE ROLE #{u}" + @connection.execute "CREATE USER #{u}" @connection.execute "CREATE SCHEMA AUTHORIZATION #{u}" set_session_auth u @connection.execute "CREATE TABLE #{TABLE_NAME} (#{COLUMNS.join(',')})" @@ -32,7 +32,7 @@ class SchemaAuthorizationTest < ActiveRecord::TestCase @connection.execute "RESET search_path" USERS.each do |u| @connection.execute "DROP SCHEMA #{u} CASCADE" - @connection.execute "DROP ROLE #{u}" + @connection.execute "DROP USER #{u}" end end diff --git a/activerecord/test/schema/postgresql.sql b/activerecord/test/schema/postgresql.sql index f2198f24e9..885dab7a79 100644 --- a/activerecord/test/schema/postgresql.sql +++ b/activerecord/test/schema/postgresql.sql @@ -110,7 +110,9 @@ CREATE TABLE defaults ( positive_integer integer default 1, negative_integer integer default -1, decimal_number decimal(3,2) default 2.78, - multiline_default text DEFAULT E'--- []\n\n'::text + multiline_default text DEFAULT '--- [] + +'::text ); CREATE TABLE auto_id_tests ( -- cgit v1.2.3