diff options
Diffstat (limited to 'activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb')
-rw-r--r-- | activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb | 464 |
1 files changed, 283 insertions, 181 deletions
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index e6ddf8bf8f..d2126a3e19 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -1,6 +1,8 @@ require 'active_record/connection_adapters/abstract_adapter' require 'active_support/core_ext/object/blank' require 'active_record/connection_adapters/statement_pool' +require 'active_record/connection_adapters/postgresql/oid' +require 'arel/visitors/bind_visitor' # Make sure we're using pg high enough for PGResult#values gem 'pg', '~> 0.11' @@ -14,7 +16,7 @@ module ActiveRecord # Forward any unused config params to PGconn.connect. [:statement_limit, :encoding, :min_messages, :schema_search_path, - :schema_order, :adapter, :pool, :wait_timeout, + :schema_order, :adapter, :pool, :wait_timeout, :template, :reaping_frequency].each do |key| conn_params.delete key end @@ -34,7 +36,8 @@ module ActiveRecord # 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) + def initialize(name, default, oid_type, sql_type = nil, null = true) + @oid_type = oid_type super(name, self.class.extract_value_from_default(default), sql_type, null) end @@ -52,180 +55,192 @@ module ActiveRecord end end - def cast_hstore(object) + def hstore_to_string(object) if Hash === object object.map { |k,v| "#{escape_hstore(k)}=>#{escape_hstore(v)}" - }.join ', ' + }.join ',' else - kvs = object.scan(/(?<!\\)".*?(?<!\\)"/).map { |o| - unescape_hstore(o[1...-1]) - } - Hash[kvs.each_slice(2).to_a] + object end end - private - HSTORE_ESCAPE = { - ' ' => '\\ ', - '\\' => '\\\\', - '"' => '\\"', - '=' => '\\=', - } - HSTORE_ESCAPE_RE = Regexp.union(HSTORE_ESCAPE.keys) - HSTORE_UNESCAPE = HSTORE_ESCAPE.invert - HSTORE_UNESCAPE_RE = Regexp.union(HSTORE_UNESCAPE.keys) - - def unescape_hstore(value) - value.gsub(HSTORE_UNESCAPE_RE) do |match| - HSTORE_UNESCAPE[match] + def string_to_hstore(string) + if string.nil? + nil + elsif String === string + Hash[string.scan(HstorePair).map { |k,v| + v = v.upcase == 'NULL' ? nil : v.gsub(/^"(.*)"$/,'\1').gsub(/\\(.)/, '\1') + k = k.gsub(/^"(.*)"$/,'\1').gsub(/\\(.)/, '\1') + [k,v] + }] + else + string end end + private + HstorePair = begin + quoted_string = /"[^"\\]*(?:\\.[^"\\]*)*"/ + unquoted_string = /(?:\\.|[^\s,])[^\s=,\\]*(?:\\.[^\s=,\\]*|=[^,>])*/ + /(#{quoted_string}|#{unquoted_string})\s*=>\s*(#{quoted_string}|#{unquoted_string})/ + end + def escape_hstore(value) - value.gsub(HSTORE_ESCAPE_RE) do |match| - HSTORE_ESCAPE[match] - end + value.nil? ? 'NULL' + : value =~ /[=\s,>]/ ? '"%s"' % value.gsub(/(["\\])/, '\\\\\1') + : value == "" ? '""' + : value.to_s.gsub(/(["\\])/, '\\\\\1') end end # :startdoc: - private - def extract_limit(sql_type) - case sql_type - when /^bigint/i; 8 - when /^smallint/i; 2 - else super - end - end - - # 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 - - # Extracts the precision from PostgreSQL-specific data types. - def extract_precision(sql_type) - if sql_type == 'money' - self.class.money_precision - else - super - end - 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 - when 'hstore' - :hstore + # Extracts the value from a PostgreSQL column default definition. + def self.extract_value_from_default(default) + # This is a performance optimization for Ruby 1.9.2 in development. + # If the value is nil, we return nil straight away without checking + # the regular expressions. If we check each regular expression, + # Regexp#=== will call NilClass#to_str, which will trigger + # method_missing (defined by whiny nil in ActiveSupport) which + # makes this method very very slow. + return default unless default + + case default + # Numeric types + when /\A\(?(-?\d+(\.\d*)?\)?)\z/ + $1 # Character types - when /^(?:character varying|bpchar)(?:\(\d+\))?$/ - :string + when /\A'(.*)'::(?:character varying|bpchar|text)\z/m + $1 + # Character types (8.1 formatting) + when /\AE'(.*)'::(?:character varying|bpchar|text)\z/m + $1.gsub(/\\(\d\d\d)/) { $1.oct.chr } # Binary data types - when 'bytea' - :binary + when /\A'(.*)'::bytea\z/m + $1 # Date/time types - when /^timestamp with(?:out)? time zone$/ - :datetime - when 'interval' - :string + when /\A'(.+)'::(?:time(?:stamp)? with(?:out)? time zone|date)\z/ + $1 + when /\A'(.*)'::interval\z/ + $1 + # Boolean type + when 'true' + true + when 'false' + false # Geometric types - when /^(?:point|line|lseg|box|"?path"?|polygon|circle)$/ - :string + when /\A'(.*)'::(?:point|line|lseg|box|"?path"?|polygon|circle)\z/ + $1 # Network address types - when /^(?:cidr|inet|macaddr)$/ - :string - # Bit strings - when /^bit(?: varying)?(?:\(\d+\))?$/ - :string + when /\A'(.*)'::(?:cidr|inet|macaddr)\z/ + $1 + # Bit string types + when /\AB'(.*)'::"?bit(?: varying)?"?\z/ + $1 # XML type - when 'xml' - :xml - # tsvector type - when 'tsvector' - :tsvector + when /\A'(.*)'::xml\z/m + $1 # Arrays - when /^\D+\[\]$/ - :string + when /\A'(.*)'::"?\D+"?\[\]\z/ + $1 + # Hstore + when /\A'(.*)'::hstore\z/ + $1 # Object identifier types - when 'oid' - :integer - # UUID type - when 'uuid' - :string - # Small and big integer types - when /^(?:small|big)int$/ - :integer - # Pass through all types that are not specific to PostgreSQL. + when /\A-?\d+\z/ + $1 else - super - end + # 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 - # Extracts the value from a PostgreSQL column default definition. - def self.extract_value_from_default(default) - case default - # This is a performance optimization for Ruby 1.9.2 in development. - # If the value is nil, we return nil straight away without checking - # the regular expressions. If we check each regular expression, - # Regexp#=== will call NilClass#to_str, which will trigger - # method_missing (defined by whiny nil in ActiveSupport) which - # makes this method very very slow. - when NilClass - nil - # Numeric types - when /\A\(?(-?\d+(\.\d*)?\)?)\z/ - $1 - # Character types - when /\A'(.*)'::(?:character varying|bpchar|text)\z/m - $1 - # Character types (8.1 formatting) - when /\AE'(.*)'::(?:character varying|bpchar|text)\z/m - $1.gsub(/\\(\d\d\d)/) { $1.oct.chr } - # Binary data types - when /\A'(.*)'::bytea\z/m - $1 - # Date/time types - when /\A'(.+)'::(?:time(?:stamp)? with(?:out)? time zone|date)\z/ - $1 - when /\A'(.*)'::interval\z/ - $1 - # Boolean type - when 'true' - true - when 'false' - false - # Geometric types - when /\A'(.*)'::(?:point|line|lseg|box|"?path"?|polygon|circle)\z/ - $1 - # Network address types - when /\A'(.*)'::(?:cidr|inet|macaddr)\z/ - $1 - # Bit string types - when /\AB'(.*)'::"?bit(?: varying)?"?\z/ - $1 - # XML type - when /\A'(.*)'::xml\z/m - $1 - # Arrays - when /\A'(.*)'::"?\D+"?\[\]\z/ - $1 - # Object identifier types - when /\A-?\d+\z/ - $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 + def type_cast(value) + return if value.nil? + return super if encoded? + + @oid_type.type_cast value + end + + private + def extract_limit(sql_type) + case sql_type + when /^bigint/i; 8 + when /^smallint/i; 2 + else super + end + end + + # 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 + + # Extracts the precision from PostgreSQL-specific data types. + def extract_precision(sql_type) + if sql_type == 'money' + self.class.money_precision + else + super end + 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 + when 'hstore' + :hstore + # 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' + :xml + # tsvector type + when 'tsvector' + :tsvector + # Arrays + when /^\D+\[\]$/ + :string + # Object identifier types + when 'oid' + :integer + # UUID type + when 'uuid' + :string + # Small and big integer types + when /^(?:small|big)int$/ + :integer + # Pass through all types that are not specific to PostgreSQL. + else + super + end + end end # The PostgreSQL adapter works with the native C (https://bitbucket.org/ged/ruby-pg) driver. @@ -284,7 +299,8 @@ module ActiveRecord :binary => { :name => "bytea" }, :boolean => { :name => "boolean" }, :xml => { :name => "xml" }, - :tsvector => { :name => "tsvector" } + :tsvector => { :name => "tsvector" }, + :hstore => { :name => "hstore" } } # Returns 'PostgreSQL' as adapter name for identification purposes. @@ -302,6 +318,10 @@ module ActiveRecord true end + def supports_partial_index? + true + end + class StatementPool < ConnectionAdapters::StatementPool def initialize(connection, max) super @@ -340,7 +360,7 @@ module ActiveRecord private def cache - @cache[$$] + @cache[Process.pid] end def dealloc(key) @@ -354,11 +374,23 @@ module ActiveRecord end end + class BindSubstitution < Arel::Visitors::PostgreSQL # :nodoc: + include Arel::Visitors::BindVisitor + end + # Initializes and connects a PostgreSQL adapter. def initialize(connection, logger, connection_parameters, config) super(connection, logger) + + if config.fetch(:prepared_statements) { true } + @visitor = Arel::Visitors::PostgreSQL.new self + else + @visitor = BindSubstitution.new self + end + + connection_parameters.delete :prepared_statements + @connection_parameters, @config = connection_parameters, config - @visitor = Arel::Visitors::PostgreSQL.new self # @local_tz is initialized as nil to avoid warnings when connect tries to use it @local_tz = nil @@ -372,6 +404,7 @@ module ActiveRecord raise "Your version of PostgreSQL (#{postgresql_version}) is too old, please upgrade!" end + initialize_type_map @local_tz = execute('SHOW TIME ZONE', 'SCHEMA').first["TimeZone"] end @@ -455,14 +488,14 @@ module ActiveRecord # Escapes binary strings for bytea input to the database. def escape_bytea(value) - @connection.escape_bytea(value) if value + PGconn.escape_bytea(value) if value end # Unescapes bytea output from a database to the binary string it represents. # NOTE: This is NOT an inverse of escape_bytea! This is only to be used # on escaped binary output from database drive. def unescape_bytea(value) - @connection.unescape_bytea(value) if value + PGconn.unescape_bytea(value) if value end # Quotes PostgreSQL-specific data types for SQL input. @@ -470,6 +503,11 @@ module ActiveRecord return super unless column case value + when Hash + case column.sql_type + when 'hstore' then super(PostgreSQLColumn.hstore_to_string(value), column) + else super + end when Float return super unless value.infinite? && column.type == :datetime "'#{value.to_s.downcase}'" @@ -501,6 +539,9 @@ module ActiveRecord when String return super unless 'bytea' == column.sql_type { :value => value, :format => 1 } + when Hash + return super unless 'hstore' == column.sql_type + PostgreSQLColumn.hstore_to_string(value) else super end @@ -571,7 +612,7 @@ module ActiveRecord # DATABASE STATEMENTS ====================================== def explain(arel, binds = []) - sql = "EXPLAIN #{to_sql(arel)}" + sql = "EXPLAIN #{to_sql(arel, binds)}" ExplainPrettyPrinter.new.pp(exec_query(sql, 'EXPLAIN', binds)) end @@ -693,7 +734,14 @@ module ActiveRecord end def substitute_at(column, index) - Arel.sql("$#{index + 1}") + Arel::Nodes::BindParam.new "$#{index + 1}" + end + + class Result < ActiveRecord::Result + def initialize(columns, rows, column_types) + super(columns, rows) + @column_types = column_types + end end def exec_query(sql, name = 'SQL', binds = []) @@ -701,7 +749,17 @@ module ActiveRecord result = binds.empty? ? exec_no_cache(sql, binds) : exec_cache(sql, binds) - ret = ActiveRecord::Result.new(result.fields, result_as_array(result)) + types = {} + result.fields.each_with_index do |fname, i| + ftype = result.ftype i + fmod = result.fmod i + types[fname] = OID::TYPE_MAP.fetch(ftype, fmod) { |oid, mod| + warn "unknown OID: #{fname}(#{oid}) (#{sql})" + OID::Identity.new + } + end + + ret = Result.new(result.fields, result.values, types) result.clear return ret end @@ -885,16 +943,20 @@ module ActiveRecord # add info on sort order for columns (only desc order is explicitly specified, asc is the default) desc_order_columns = inddef.scan(/(\w+) DESC/).flatten orders = desc_order_columns.any? ? Hash[desc_order_columns.map {|order_column| [order_column, :desc]}] : {} + where = inddef.scan(/WHERE (.+)$/).flatten[0] - column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names, [], orders) + column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names, [], orders, where) end.compact end # Returns the list of all column definitions for a table. - def columns(table_name, name = nil) + def columns(table_name) # Limit, precision, and scale are all handled by the superclass. - column_definitions(table_name).collect do |column_name, type, default, notnull| - PostgreSQLColumn.new(column_name, default, type, notnull == 'f') + column_definitions(table_name).map do |column_name, type, default, notnull, oid, fmod| + oid = OID::TYPE_MAP.fetch(oid.to_i, fmod.to_i) { + OID::Identity.new + } + PostgreSQLColumn.new(column_name, default, oid, type, notnull == 'f') end end @@ -983,26 +1045,46 @@ module ActiveRecord def pk_and_sequence_for(table) #:nodoc: # First try looking for a sequence with a dependency on the # given table's primary key. - result = exec_query(<<-end_sql, 'SCHEMA').rows.first - SELECT attr.attname, ns.nspname, seq.relname - FROM pg_class seq - INNER JOIN pg_depend dep ON seq.oid = dep.objid - INNER JOIN pg_attribute attr ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid - INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] - INNER JOIN pg_namespace ns ON seq.relnamespace = ns.oid - WHERE seq.relkind = 'S' - AND cons.contype = 'p' - AND dep.refobjid = '#{quote_table_name(table)}'::regclass + result = query(<<-end_sql, 'PK and serial sequence')[0] + 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.relkind = 'S' + AND attr.attrelid = dep.refobjid + AND attr.attnum = dep.refobjsubid + AND attr.attrelid = cons.conrelid + AND attr.attnum = cons.conkey[1] + AND cons.contype = 'p' + AND dep.refobjid = '#{quote_table_name(table)}'::regclass end_sql - # [primary_key, sequence] - if result.second == 'public' then - sequence = result.last - else - sequence = result.second+'.'+result.last + if result.nil? or result.empty? + # 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). + result = query(<<-end_sql, 'PK and custom sequence')[0] + 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) + JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1]) + WHERE t.oid = '#{quote_table_name(table)}'::regclass + AND cons.contype = 'p' + AND def.adsrc ~* 'nextval' + end_sql end - [result.first, sequence] + [result.first, result.last] rescue nil end @@ -1107,7 +1189,7 @@ module ActiveRecord # Construct a clean list of column names from the ORDER BY clause, removing # any ASC/DESC modifiers - order_columns = orders.collect { |s| s.gsub(/\s+(ASC|DESC)\s*/i, '') } + order_columns = orders.collect { |s| s.gsub(/\s+(ASC|DESC)\s*(NULLS\s+(FIRST|LAST)\s*)?/i, '') } order_columns.delete_if { |c| c.blank? } order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" } @@ -1151,6 +1233,22 @@ module ActiveRecord end private + def initialize_type_map + result = execute('SELECT oid, typname, typelem, typdelim FROM pg_type', 'SCHEMA') + leaves, nodes = result.partition { |row| row['typelem'] == '0' } + + # populate the leaf nodes + leaves.find_all { |row| OID.registered_type? row['typname'] }.each do |row| + OID::TYPE_MAP[row['oid'].to_i] = OID::NAMES[row['typname']] + end + + # populate composite types + nodes.find_all { |row| OID::TYPE_MAP.key? row['typelem'].to_i }.each do |row| + vector = OID::Vector.new row['typdelim'], OID::TYPE_MAP[row['typelem'].to_i] + OID::TYPE_MAP[row['oid'].to_i] = vector + end + end + FEATURE_NOT_SUPPORTED = "0A000" # :nodoc: def exec_no_cache(sql, binds) @@ -1173,7 +1271,11 @@ module ActiveRecord # prepared statements whose return value may have changed is # FEATURE_NOT_SUPPORTED. Check here for more details: # http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/cache/plancache.c#l573 - code = e.result.result_error_field(PGresult::PG_DIAG_SQLSTATE) + begin + code = e.result.result_error_field(PGresult::PG_DIAG_SQLSTATE) + rescue + raise e + end if FEATURE_NOT_SUPPORTED == code @statements.delete sql_key(sql) retry @@ -1249,7 +1351,7 @@ module ActiveRecord # Executes a SELECT query and returns the results, performing any data type # conversions that are required to be performed here instead of in PostgreSQLColumn. def select(sql, name = nil, binds = []) - exec_query(sql, name, binds).to_a + exec_query(sql, name, binds) end def select_raw(sql, name = nil) @@ -1280,7 +1382,7 @@ module ActiveRecord # - ::regclass is a function that gives the id for a table name def column_definitions(table_name) #:nodoc: exec_query(<<-end_sql, 'SCHEMA').rows - SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull + SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull, a.atttypid, a.atttypmod FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attrelid = '#{quote_table_name(table_name)}'::regclass |