From 29b0707f07f148d98515125dab44b73cfdc0a3d4 Mon Sep 17 00:00:00 2001 From: Michael Koziarski Date: Thu, 16 Aug 2007 06:26:30 +0000 Subject: 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 --- activerecord/CHANGELOG | 4 + .../connection_adapters/abstract/quoting.rb | 10 +- .../connection_adapters/postgresql_adapter.rb | 591 +++++++++++++++------ activerecord/test/datatype_test_postgresql.rb | 205 ++++++- activerecord/test/finder_test.rb | 16 +- .../fixtures/db_definitions/postgresql.drop.sql | 7 + .../test/fixtures/db_definitions/postgresql.sql | 40 ++ railties/configs/databases/postgresql.yml | 25 +- 8 files changed, 673 insertions(+), 225 deletions(-) diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG index 1b99306c0e..401dc6d53f 100644 --- a/activerecord/CHANGELOG +++ b/activerecord/CHANGELOG @@ -1,5 +1,9 @@ *SVN* +* Improve performance and functionality of the postgresql adapter. Closes #8049 [roderickvd] + + For more information see: http://dev.rubyonrails.org/ticket/8049 + * Don't clobber includes passed to has_many.count [danger] * Make sure has_many uses :include when counting [danger] diff --git a/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb b/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb index aa405eb47c..b3b3d70359 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb @@ -11,12 +11,12 @@ module ActiveRecord when String, ActiveSupport::Multibyte::Chars value = value.to_s if column && column.type == :binary && column.class.respond_to?(:string_to_binary) - "'#{quote_string(column.class.string_to_binary(value))}'" # ' (for ruby-mode) + "#{quoted_string_prefix}'#{quote_string(column.class.string_to_binary(value))}'" # ' (for ruby-mode) elsif column && [:integer, :float].include?(column.type) value = column.type == :integer ? value.to_i : value.to_f value.to_s else - "'#{quote_string(value)}'" # ' (for ruby-mode) + "#{quoted_string_prefix}'#{quote_string(value)}'" # ' (for ruby-mode) end when NilClass then "NULL" when TrueClass then (column && column.type == :integer ? '1' : quoted_true) @@ -28,7 +28,7 @@ module ActiveRecord if value.acts_like?(:date) || value.acts_like?(:time) "'#{quoted_date(value)}'" else - "'#{quote_string(value.to_yaml)}'" + "#{quoted_string_prefix}'#{quote_string(value.to_yaml)}'" end end end @@ -56,6 +56,10 @@ module ActiveRecord def quoted_date(value) value.to_s(:db) end + + def quoted_string_prefix + '' + end end end end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 97c537132b..4a48bf3a28 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -12,29 +12,202 @@ module ActiveRecord username = config[:username].to_s password = config[:password].to_s - min_messages = config[:min_messages] - if config.has_key?(:database) database = config[:database] else raise ArgumentError, "No database specified. Missing argument: database." end - pga = ConnectionAdapters::PostgreSQLAdapter.new( - PGconn.connect(host, port, "", "", database, username, password), logger, config - ) + # The postgres drivers don't allow to create an unconnected PGconn object, + # so just pass a nil connection object for the time being. + ConnectionAdapters::PostgreSQLAdapter.new(nil, logger, [host, port, nil, nil, database, username, password], config) + end + end - PGconn.translate_results = false if PGconn.respond_to? :translate_results= + module ConnectionAdapters + # PostgreSQL-specific extensions to column definitions in a table. + class PostgreSQLColumn < Column #:nodoc: + # Instantiates a new PostgreSQL column definition in a table. + def initialize(name, default, sql_type = nil, null = true) + super(name, self.class.extract_value_from_default(default), sql_type, null) + end - pga.schema_search_path = config[:schema_search_path] || config[:schema_order] + private + # Extracts the scale from PostgreSQL-specific data types. + def extract_scale(sql_type) + # Money type has a fixed scale of 2. + sql_type =~ /^money/ ? 2 : super + end - pga + # Extracts the precision from PostgreSQL-specific data types. + def extract_precision(sql_type) + # Actual code is defined dynamically in PostgreSQLAdapter.connect + # depending on the server specifics + super + end + + # Escapes binary strings for bytea input to the database. + def self.string_to_binary(value) + if PGconn.respond_to?(:escape_bytea) + self.class.module_eval do + define_method(:string_to_binary) do |value| + PGconn.escape_bytea(value) if value + end + end + else + self.class.module_eval do + define_method(:string_to_binary) do |value| + if value + result = '' + value.each_byte { |c| result << sprintf('\\\\%03o', c) } + result + end + end + end + end + self.class.string_to_binary(value) + end + + # Unescapes bytea output from a database to the binary string it represents. + def self.binary_to_string(value) + # In each case, check if the value actually is escaped PostgresSQL bytea output + # or an unescaped Active Record attribute that was just written. + if PGconn.respond_to?(:unescape_bytea) + self.class.module_eval do + define_method(:binary_to_string) do |value| + if value =~ /\\\\\d{3}/ + PGconn.unescape_bytea(value) + else + value + end + end + end + else + self.class.module_eval do + define_method(:binary_to_string) do |value| + if value =~ /\\\\\d{3}/ + result = '' + i, max = 0, value.size + while i < max + char = value[i] + if char == ?\\ + if value[i+1] == ?\\ + char = ?\\ + i += 1 + else + char = value[i+1..i+3].oct + i += 3 + end + end + result << char + i += 1 + end + result + else + value + end + end + end + end + self.class.binary_to_string(value) + end + + # Maps PostgreSQL-specific data types to logical Rails types. + def simplified_type(field_type) + case field_type + # Numeric and monetary types + when /^(?:real|double precision)$/ + :float + # Monetary types + when /^money$/ + :decimal + # Character types + when /^(?:character varying|bpchar)(?:\(\d+\))?$/ + :string + # Binary data types + when /^bytea$/ + :binary + # Date/time types + when /^timestamp with(?:out)? time zone$/ + :datetime + when /^interval$/ + :string + # Geometric types + when /^(?:point|line|lseg|box|"?path"?|polygon|circle)$/ + :string + # Network address types + when /^(?:cidr|inet|macaddr)$/ + :string + # Bit strings + when /^bit(?: varying)?(?:\(\d+\))?$/ + :string + # XML type + when /^xml$/ + :string + # Arrays + when /^\D+\[\]$/ + :string + # Object identifier types + when /^oid$/ + :integer + # Pass through all types that are not specific to PostgreSQL. + else + super + end + end + + # Extracts the value from a PostgreSQL column default definition. + def self.extract_value_from_default(default) + case default + # Numeric types + when /^-?\d+(\.\d*)?$/ + default + # Character types + when /^'(.*)'::(?:character varying|bpchar|text)$/ + $1 + # Binary data types + when /^'(.*)'::bytea$/ + $1 + # Date/time types + when /^'(.+)'::(?:time(?:stamp)? with(?:out)? time zone|date)$/ + $1 + when /^'(.*)'::interval$/ + $1 + # Boolean type + when /^true$/ + true + when /^false$/ + false + # Geometric types + when /^'(.*)'::(?:point|line|lseg|box|"?path"?|polygon|circle)$/ + $1 + # Network address types + when /^'(.*)'::(?:cidr|inet|macaddr)$/ + $1 + # Bit string types + when /^B'(.*)'::"?bit(?: varying)?"?$/ + $1 + # XML type + when /^'(.*)'::xml$/ + $1 + # Arrays + when /^'(.*)'::"?\D+"?\[\]$/ + $1 + # Object identifier types + when /^-?\d+$/ + $1 + else + # Anything else is blank, some user type, or some function + # and we can't know the value of that, so return nil. + nil + end + end end end module ConnectionAdapters - # The PostgreSQL adapter works both with the C-based (http://www.postgresql.jp/interfaces/ruby/) and the Ruby-base - # (available both as gem and from http://rubyforge.org/frs/?group_id=234&release_id=1145) drivers. + # The PostgreSQL adapter works both with the native C (http://ruby.scripting.ca/postgres/) and the pure + # Ruby (available both as gem and from http://rubyforge.org/frs/?group_id=234&release_id=1944) drivers. # # Options: # @@ -48,18 +221,17 @@ module ActiveRecord # * :min_messages -- An optional client min messages that is using in a SET client_min_messages TO call on connection. # * :allow_concurrency -- If true, use async query methods so Ruby threads don't deadlock; otherwise, use blocking query methods. class PostgreSQLAdapter < AbstractAdapter + # Returns 'PostgreSQL' as adapter name for identification purposes. def adapter_name 'PostgreSQL' end - def initialize(connection, logger, config = {}) + # Initializes and connects a PostgreSQL adapter. + def initialize(connection, logger, connection_parameters, config) super(connection, logger) - @config = config + @connection_parameters, @config = connection_parameters, config - # Ignore async_exec and async_query with the postgres-pr client lib. - @async = config[:allow_concurrency] && @connection.respond_to?(:async_exec) - - configure_connection + connect end # Is this connection alive and ready for queries? @@ -67,29 +239,32 @@ module ActiveRecord if @connection.respond_to?(:status) @connection.status == PGconn::CONNECTION_OK else + # We're asking the driver, not ActiveRecord, so use @connection.query instead of #query @connection.query 'SELECT 1' true end - # postgres-pr raises a NoMethodError when querying if no conn is available + # postgres-pr raises a NoMethodError when querying if no connection is available. rescue PGError, NoMethodError false end # Close then reopen the connection. def reconnect! - # TODO: postgres-pr doesn't have PGconn#reset. if @connection.respond_to?(:reset) @connection.reset configure_connection + else + disconnect! + connect end end + # Close the connection. def disconnect! - # Both postgres and postgres-pr respond to :close @connection.close rescue nil end - def native_database_types + def native_database_types #:nodoc: { :primary_key => "serial primary key", :string => { :name => "character varying", :limit => 255 }, @@ -106,30 +281,82 @@ module ActiveRecord } end + # Does PostgreSQL support migrations? def supports_migrations? true end + # Does PostgreSQL support standard conforming strings? + def supports_standard_conforming_strings? + # Temporarily set the client message level above error to prevent unintentional + # error messages in the logs when working on a PostgreSQL database server that + # does not support standard conforming strings. + client_min_messages_old = client_min_messages + self.client_min_messages = 'panic' + + # 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 + self.client_min_messages = client_min_messages_old + has_support + end + + # Returns the configured supported identifier length supported by PostgreSQL, + # or report the default of 63 on PostgreSQL 7.x. def table_alias_length - 63 + @table_alias_length ||= (postgresql_version >= 80000 ? query('SHOW max_identifier_length')[0][0].to_i : 63) end # QUOTING ================================================== - def quote(value, column = nil) + # Quotes PostgreSQL-specific data types for SQL input. + def quote(value, column = nil) #:nodoc: if value.kind_of?(String) && column && column.type == :binary - "'#{escape_bytea(value)}'" + "#{quoted_string_prefix}'#{column.class.string_to_binary(value)}'" + elsif value.kind_of?(String) && column && column.sql_type =~ /^xml$/ + "xml '#{quote_string(value)}'" + elsif value.kind_of?(Numeric) && column && column.sql_type =~ /^money$/ + # Not truly string input, so doesn't require (or allow) escape string syntax. + "'#{value.to_s}'" + elsif value.kind_of?(String) && column && column.sql_type =~ /^bit/ + case value + when /^[01]*$/ + "B'#{value}'" # Bit-string notation + when /^[0-9A-F]*$/i + "X'#{value}'" # Hexadecimal notation + end else super end end - def quote_column_name(name) + # Quotes strings for use in SQL input in the postgres driver for better performance. + def quote_string(s) #:nodoc: + if PGconn.respond_to?(:escape) + self.class.instance_eval do + define_method(:quote_string) do |s| + PGconn.escape(s) + end + end + else + # There are some incorrectly compiled postgres drivers out there + # that don't define PGconn.escape. + self.class.instance_eval do + undef_method(:quote_string) + end + end + quote_string(s) + end + + # Quotes column names for use in SQL queries. + def quote_column_name(name) #:nodoc: %("#{name}") end - # Include microseconds if the value is a Time responding to usec. - def quoted_date(value) + # Quote date/time values for use in SQL input. Includes microseconds + # if the value is a Time responding to usec. + def quoted_date(value) #:nodoc: if value.acts_like?(:time) && value.respond_to?(:usec) "#{super}.#{sprintf("%06d", value.usec)}" else @@ -140,12 +367,14 @@ module ActiveRecord # DATABASE STATEMENTS ====================================== - def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc: + # Executes an INSERT query and returns the new record's ID + def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) execute(sql, name) table = sql.split(" ", 4)[2] id_value || 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. def query(sql, name = nil) #:nodoc: log(sql, name) do if @async @@ -156,7 +385,9 @@ module ActiveRecord end end - def execute(sql, name = nil) #:nodoc: + # Executes a SQL statement, returning a PGresult object on success + # or raising a PGError exception otherwise. + def execute(sql, name = nil) log(sql, name) do if @async @connection.async_exec(sql) @@ -166,26 +397,30 @@ module ActiveRecord end end - def update(sql, name = nil) #:nodoc: + # Executes an UPDATE query and returns the number of affected tuples. + def update(sql, name = nil) execute(sql, name).cmdtuples end - def begin_db_transaction #:nodoc: + # Begins a transaction. + def begin_db_transaction execute "BEGIN" end - def commit_db_transaction #:nodoc: + # Commits a transaction. + def commit_db_transaction execute "COMMIT" end - def rollback_db_transaction #:nodoc: + # Aborts a transaction. + def rollback_db_transaction execute "ROLLBACK" end # SCHEMA STATEMENTS ======================================== - # Return the list of all tables in the schema search path. - def tables(name = nil) #:nodoc: + # Returns the list of all tables in the schema search path or a specified schema. + def tables(name = nil) schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',') query(<<-SQL, name).map { |row| row[0] } SELECT tablename @@ -194,7 +429,8 @@ module ActiveRecord SQL end - def indexes(table_name, name = nil) #:nodoc: + # Returns the list of all indexes for a table. + def indexes(table_name, name = nil) result = query(<<-SQL, name) SELECT i.relname, d.indisunique, a.attname FROM pg_class t, pg_class i, pg_index d, pg_attribute a @@ -227,34 +463,49 @@ module ActiveRecord indexes end - def columns(table_name, name = nil) #:nodoc: - column_definitions(table_name).collect do |name, type, default, notnull, typmod| - # typmod now unused as limit, precision, scale all handled by superclass - Column.new(name, default_value(default), translate_field_type(type), notnull == "f") + # Returns the list of all column definitions for a table. + def columns(table_name, name = nil) + # Limit, precision, and scale are all handled by superclass. + column_definitions(table_name).collect do |name, type, default, notnull| + PostgreSQLColumn.new(name, default, type, notnull == 'f') end end - # Set the schema search path to a string of comma-separated schema names. - # Names beginning with $ are quoted (e.g. $user => '$user') - # See http://www.postgresql.org/docs/8.0/interactive/ddl-schemas.html - def schema_search_path=(schema_csv) #:nodoc: + # Sets the schema search path to a string of comma-separated schema names. + # Names beginning with $ have to be quoted (e.g. $user => '$user'). + # See: http://www.postgresql.org/docs/current/static/ddl-schemas.html + # + # This should be not be called manually but set in database.yml. + def schema_search_path=(schema_csv) if schema_csv execute "SET search_path TO #{schema_csv}" - @schema_search_path = nil + @schema_search_path = schema_csv end end - def schema_search_path #:nodoc: + # Returns the active schema search path. + def schema_search_path @schema_search_path ||= query('SHOW search_path')[0][0] end - def default_sequence_name(table_name, pk = nil) + # Returns the current client message level. + def client_min_messages + query('SHOW client_min_messages')[0][0] + end + + # Set the client message level. + def client_min_messages=(level) + execute("SET client_min_messages TO '#{level}'") + end + + # Returns the sequence name for a table's primary key or some other specified key. + def default_sequence_name(table_name, pk = nil) #:nodoc: default_pk, default_seq = pk_and_sequence_for(table_name) default_seq || "#{table_name}_#{pk || default_pk || 'id'}_seq" end - # Resets sequence to the max value of the table's pk if present. - def reset_pk_sequence!(table, pk = nil, sequence = nil) + # Resets the sequence of a table's primary key to the maximum value. + def reset_pk_sequence!(table, pk = nil, sequence = nil) #:nodoc: unless pk and sequence default_pk, default_sequence = pk_and_sequence_for(table) pk ||= default_pk @@ -271,19 +522,18 @@ module ActiveRecord end end - # Find a table's primary key and sequence. - def pk_and_sequence_for(table) + # Returns a table's primary key and belonging sequence. + def pk_and_sequence_for(table) #:nodoc: # First try looking for a sequence with a dependency on the # given table's primary key. result = query(<<-end_sql, 'PK and serial sequence')[0] - SELECT attr.attname, name.nspname, seq.relname + SELECT attr.attname, seq.relname FROM pg_class seq, pg_attribute attr, pg_depend dep, pg_namespace name, pg_constraint cons WHERE seq.oid = dep.objid - AND seq.relnamespace = name.oid AND seq.relkind = 'S' AND attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid @@ -297,11 +547,9 @@ module ActiveRecord # If that fails, try parsing the primary key's default value. # Support the 7.x and 8.0 nextval('foo'::text) as well as # the 8.1+ nextval('foo'::regclass). - # TODO: assumes sequence is in same schema as table. result = query(<<-end_sql, 'PK and custom sequence')[0] - SELECT attr.attname, name.nspname, split_part(def.adsrc, '''', 2) + SELECT attr.attname, split_part(def.adsrc, '''', 2) FROM pg_class t - JOIN pg_namespace name ON (t.relnamespace = name.oid) JOIN pg_attribute attr ON (t.oid = attrelid) JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum) JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1]) @@ -310,17 +558,18 @@ module ActiveRecord AND def.adsrc ~* 'nextval' end_sql end - # check for existence of . in sequence name as in public.foo_sequence. if it does not exist, return unqualified sequence - # We cannot qualify unqualified sequences, as rails doesn't qualify any table access, using the search path + # [primary_key, sequence] [result.first, result.last] rescue nil end + # Renames a table. def rename_table(name, new_name) execute "ALTER TABLE #{name} RENAME TO #{new_name}" end + # Adds a column to a table. def add_column(table_name, column_name, type, options = {}) default = options[:default] notnull = options[:null] == false @@ -343,11 +592,12 @@ module ActiveRecord end end - def change_column(table_name, column_name, type, options = {}) #:nodoc: + # Changes the column of a table. + def change_column(table_name, column_name, type, options = {}) begin execute "ALTER TABLE #{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 PG7, so we use a more arcane way of doing it. + # 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) @@ -362,19 +612,23 @@ module ActiveRecord end end - def change_column_default(table_name, column_name, default) #:nodoc: + # Changes the default value of a table column. + def change_column_default(table_name, column_name, default) execute "ALTER TABLE #{table_name} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}" end - def rename_column(table_name, column_name, new_column_name) #:nodoc: + # Renames a column in a table. + def rename_column(table_name, column_name, new_column_name) execute "ALTER TABLE #{table_name} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}" end - def remove_index(table_name, options) #:nodoc: + # Drops an index from a table. + def remove_index(table_name, options = {}) execute "DROP INDEX #{index_name(table_name, options)}" end - def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc: + # Maps logical Rails types to PostgreSQL-specific data types. + def type_to_sql(type, limit = nil, precision = nil, scale = nil) return super unless type.to_s == 'integer' if limit.nil? || limit == 4 @@ -386,32 +640,32 @@ module ActiveRecord end end - # SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause. + # Returns a SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause. # # PostgreSQL requires the ORDER BY columns in the select list for distinct queries, and # requires that the ORDER BY include the distinct column. # # distinct("posts.id", "posts.created_at desc") - def distinct(columns, order_by) + def distinct(columns, order_by) #:nodoc: return "DISTINCT #{columns}" if order_by.blank? - # construct a clean list of column names from the ORDER BY clause, removing - # any asc/desc modifiers + # Construct a clean list of column names from the ORDER BY clause, removing + # any ASC/DESC modifiers order_columns = order_by.split(',').collect { |s| s.split.first } order_columns.delete_if &:blank? order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" } - # return a DISTINCT ON() clause that's distinct on the columns we want but includes - # all the required columns for the ORDER BY to work properly + # Return a DISTINCT ON() clause that's distinct on the columns we want but includes + # all the required columns for the ORDER BY to work properly. sql = "DISTINCT ON (#{columns}) #{columns}, " sql << order_columns * ', ' end - # ORDER BY clause for the passed order option. + # Returns a ORDER BY clause for the passed order option. # # PostgreSQL does not allow arbitrary ordering when using DISTINCT ON, so we work around this # by wrapping the sql as a sub-select and ordering in that query. - def add_order_by_for_association_limiting!(sql, options) + def add_order_by_for_association_limiting!(sql, options) #:nodoc: return sql if options[:order].blank? order = options[:order].split(',').collect { |s| s.strip }.reject(&:blank?) @@ -421,25 +675,85 @@ module ActiveRecord sql.replace "SELECT * FROM (#{sql}) AS id_list ORDER BY #{order}" end + protected + # Returns the version of the connected PostgreSQL version. + def postgresql_version + @postgresql_version ||= + if @connection.respond_to?(:server_version) + @connection.server_version + else + # Mimic PGconn.server_version behavior + begin + query('SELECT version()')[0][0] =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/ + ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i + rescue + 0 + end + end + end + private - BYTEA_COLUMN_TYPE_OID = 17 - NUMERIC_COLUMN_TYPE_OID = 1700 - TIMESTAMPOID = 1114 - TIMESTAMPTZOID = 1184 + # The internal PostgreSQL identifer of the money data type. + MONEY_COLUMN_TYPE_OID = 790 #:nodoc: + + # Connects to a PostgreSQL server and sets up the adapter depending on the + # connected server's characteristics. + def connect + @connection = PGconn.connect(*@connection_parameters) + PGconn.translate_results = false if PGconn.respond_to?(:translate_results=) + + # Ignore async_exec and async_query when using postgres-pr. + @async = @config[:allow_concurrency] && @connection.respond_to?(:async_exec) + + # Use escape string syntax if available. We cannot do this lazily when encountering + # the first string, because that could then break any transactions in progress. + # See: http://www.postgresql.org/docs/current/static/runtime-config-compatible.html + # If PostgreSQL doesn't know the standard_conforming_strings parameter then it doesn't + # support escape string syntax. Don't override the inherited quoted_string_prefix. + if supports_standard_conforming_strings? + self.class.instance_eval do + define_method(:quoted_string_prefix) { 'E' } + end + end + # Money type has a fixed precision of 10 in PostgreSQL 8.2 and below, and as of + # PostgreSQL 8.3 it has a fixed precision of 19. PostgreSQLColumn.extract_precision + # should know about this but can't detect it there, so deal with it here. + money_precision = (postgresql_version >= 80300) ? 19 : 10 + PostgreSQLColumn.module_eval(<<-end_eval) + def extract_precision(sql_type) + if sql_type =~ /^money$/ + #{money_precision} + else + super + end + end + end_eval + + configure_connection + end + + # Configures the encoding, verbosity, and schema search path of the connection. + # This is called by #connect and should not be called manually. def configure_connection if @config[:encoding] - execute("SET client_encoding TO '#{@config[:encoding]}'") - end - if @config[:min_messages] - execute("SET client_min_messages TO '#{@config[:min_messages]}'") + if @connection.respond_to?(:set_client_encoding) + @connection.set_client_encoding(@config[:encoding]) + else + execute("SET client_encoding TO '#{@config[:encoding]}'") + end end + self.client_min_messages = @config[:min_messages] if @config[:min_messages] + self.schema_search_path = @config[:schema_search_path] || @config[:schema_order] end - def last_insert_id(table, sequence_name) + # Returns the current ID of a table's sequence. + def last_insert_id(table, sequence_name) #:nodoc: Integer(select_value("SELECT currval('#{sequence_name}')")) end + # Executes a SELECT query and returns the results, performing any data type + # conversions that require to be performed here instead of in PostgreSQLColumn. def select(sql, name = nil) res = execute(sql, name) results = res.result @@ -448,17 +762,27 @@ module ActiveRecord fields = res.fields results.each do |row| hashed_row = {} - row.each_index do |cel_index| - column = row[cel_index] - - case res.type(cel_index) - when BYTEA_COLUMN_TYPE_OID - column = unescape_bytea(column) - when NUMERIC_COLUMN_TYPE_OID - column = column.to_d if column.respond_to?(:to_d) + row.each_index do |cell_index| + column = row[cell_index] + + # If this is a money type column and there are any currency symbols, + # then strip them off. Indeed it would be prettier to do this in + # PostgresSQLColumn.string_to_decimal but would break form input + # fields that call value_before_type_cast. + if res.type(cell_index) == MONEY_COLUMN_TYPE_OID + # Because money output is formatted according to the locale, there are two + # cases to consider (note the decimal seperators): + # (1) $12,345,678.12 + # (2) $12.345.678,12 + case column + when /^-?\D+[\d,]+\.\d{2}$/ # (1) + column = column.gsub(/[^-\d\.]/, '') + when /^-?\D+[\d\.]+,\d{2}$/ # (2) + column = column.gsub(/[^-\d,]/, '').sub(/,/, '.') + end end - hashed_row[fields[cel_index]] = column + hashed_row[fields[cell_index]] = column end rows << hashed_row end @@ -467,55 +791,7 @@ module ActiveRecord return rows end - def escape_bytea(s) - if PGconn.respond_to? :escape_bytea - self.class.send(:define_method, :escape_bytea) do |s| - PGconn.escape_bytea(s) if s - end - else - self.class.send(:define_method, :escape_bytea) do |s| - if s - result = '' - s.each_byte { |c| result << sprintf('\\\\%03o', c) } - result - end - end - end - escape_bytea(s) - end - - def unescape_bytea(s) - if PGconn.respond_to? :unescape_bytea - self.class.send(:define_method, :unescape_bytea) do |s| - PGconn.unescape_bytea(s) if s - end - else - self.class.send(:define_method, :unescape_bytea) do |s| - if s - result = '' - i, max = 0, s.size - while i < max - char = s[i] - if char == ?\\ - if s[i+1] == ?\\ - char = ?\\ - i += 1 - else - char = s[i+1..i+3].oct - i += 3 - end - end - result << char - i += 1 - end - result - end - end - end - unescape_bytea(s) - end - - # Query a table's column names, default values, and types. + # Returns the list of a table's column names, data types, and default values. # # The underlying query is roughly: # SELECT column.name, column.type, default.value @@ -533,7 +809,7 @@ module ActiveRecord # Query implementation notes: # - format_type includes the column size constraint, e.g. varchar(50) # - ::regclass is a function that gives the id for a table name - def column_definitions(table_name) + def column_definitions(table_name) #:nodoc: query <<-end_sql SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull FROM pg_attribute a LEFT JOIN pg_attrdef d @@ -543,43 +819,6 @@ module ActiveRecord ORDER BY a.attnum end_sql end - - # Translate PostgreSQL-specific types into simplified SQL types. - # These are special cases; standard types are handled by - # ConnectionAdapters::Column#simplified_type. - def translate_field_type(field_type) - # Match the beginning of field_type since it may have a size constraint on the end. - case field_type - # PostgreSQL array data types. - when /\[\]$/i then 'string' - when /^timestamp/i then 'datetime' - when /^real|^money/i then 'float' - when /^interval/i then 'string' - # geometric types (the line type is currently not implemented in postgresql) - when /^(?:point|lseg|box|"?path"?|polygon|circle)/i then 'string' - when /^bytea/i then 'binary' - else field_type # Pass through standard types. - end - end - - def default_value(value) - # Boolean types - return "t" if value =~ /true/i - return "f" if value =~ /false/i - - # Char/String/Bytea type values - return $1 if value =~ /^'(.*)'::(bpchar|text|character varying|bytea)$/ - - # Numeric values - return value if value =~ /^-?[0-9]+(\.[0-9]*)?/ - - # Fixed dates / times - return $1 if value =~ /^'(.+)'::(date|timestamp)/ - - # Anything else is blank, some user type, or some function - # and we can't know the value of that, so return nil. - return nil - end end end end 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 +); diff --git a/railties/configs/databases/postgresql.yml b/railties/configs/databases/postgresql.yml index ee5a9d117a..0da9e98032 100644 --- a/railties/configs/databases/postgresql.yml +++ b/railties/configs/databases/postgresql.yml @@ -1,9 +1,13 @@ -# PostgreSQL versions 7.4 - 8.1 +# PostgreSQL. Versions 7.4 and 8.x are supported. # -# Get the C bindings: -# gem install postgres -# or use the pure-Ruby bindings on Windows: -# gem install postgres-pr +# Install the ruby-postgres driver: +# gem install ruby-postgres +# On MacOS X: +# gem install ruby-postgres -- --include=/usr/local/pgsql +# On Windows: +# gem install ruby-postgres +# Choose the win32 build. +# Install PostgreSQL and put its /bin directory on your path. development: adapter: postgresql encoding: unicode @@ -11,21 +15,18 @@ development: username: <%= app_name %> password: - # Connect on a TCP socket. Omitted by default since the client uses a - # domain socket that doesn't need configuration. Windows does not have + # Connect on a TCP socket. Omitted by default since the client uses a + # domain socket that doesn't need configuration. Windows does not have # domain sockets, so uncomment these lines. #host: localhost #port: 5432 - # Schema search path. The server defaults to $user,public + # Schema search path. The server defaults to $user,public #schema_search_path: myapp,sharedapp,public - # Character set encoding. The server defaults to sql_ascii. - #encoding: UTF8 - # Minimum log levels, in increasing order: # debug5, debug4, debug3, debug2, debug1, - # info, notice, warning, error, log, fatal, or panic + # log, notice, warning, error, fatal, and panic # The server defaults to notice. #min_messages: warning -- cgit v1.2.3