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 --- .../connection_adapters/postgresql_adapter.rb | 90 +++++++++++++++++----- 1 file changed, 70 insertions(+), 20 deletions(-) (limited to 'activerecord/lib/active_record') 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 -- cgit v1.2.3