diff options
12 files changed, 435 insertions, 53 deletions
diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md index 8c99a3929e..475c237009 100644 --- a/activerecord/CHANGELOG.md +++ b/activerecord/CHANGELOG.md @@ -23,6 +23,37 @@ *kennyj* +* PostgreSQL inet and cidr types are converted to `IPAddr` objects. + *Dan McClain* + +* PostgreSQL array type support. Any datatype can be used to create an + array column, with full migration and schema dumper support. + + To declare an array column, use the following syntax: + + create_table :table_with_arrays do |t| + t.integer :int_array, :array => true + # integer[] + t.integer :int_array, :array => true, :length => 2 + # smallint[] + t.string :string_array, :array => true, :length => 30 + # char varying(30)[] + end + + This respects any other migraion detail (limits, defaults, etc). + ActiveRecord will serialize and deserialize the array columns on + their way to and from the database. + + One thing to note: PostgreSQL does not enforce any limits on the + number of elements, and any array can be multi-dimensional. Any + array that is multi-dimensional must be rectangular (each sub array + must have the same number of elements as its siblings). + + If the `pg_array_parser` gem is available, it will be used when + parsing PostgreSQL's array representation + + *Dan McClain* + * Attribute predicate methods, such as `article.title?`, will now raise `ActiveModel::MissingAttributeError` if the attribute being queried for truthiness was not read from the database, instead of just returning false. diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb new file mode 100644 index 0000000000..9d6111b51e --- /dev/null +++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_dumper.rb @@ -0,0 +1,56 @@ +module ActiveRecord + module ConnectionAdapters # :nodoc: + # The goal of this module is to move Adapter specific column + # definitions to the Adapter instead of having it in the schema + # dumper itself. This code represents the normal case. + # We can then redefine how certain data types may be handled in the schema dumper on the + # Adapter level by over-writing this code inside the database spececific adapters + module ColumnDumper + def column_spec(column, types) + spec = prepare_column_options(column, types) + (spec.keys - [:name, :type]).each{ |k| spec[k].insert(0, "#{k.to_s}: ")} + spec + end + + # This can be overridden on a Adapter level basis to support other + # extended datatypes (Example: Adding an array option in the + # PostgreSQLAdapter) + def prepare_column_options(column, types) + spec = {} + spec[:name] = column.name.inspect + + # AR has an optimization which handles zero-scale decimals as integers. This + # code ensures that the dumper still dumps the column as a decimal. + spec[:type] = if column.type == :integer && /^(numeric|decimal)/ =~ column.sql_type + 'decimal' + else + column.type.to_s + end + spec[:limit] = column.limit.inspect if column.limit != types[column.type][:limit] && spec[:type] != 'decimal' + spec[:precision] = column.precision.inspect if column.precision + spec[:scale] = column.scale.inspect if column.scale + spec[:null] = 'false' unless column.null + spec[:default] = default_string(column.default) if column.has_default? + spec + end + + # Lists the valid migration options + def migration_keys + [:name, :limit, :precision, :scale, :default, :null] + end + + private + + def default_string(value) + case value + when BigDecimal + value.to_s + when Date, DateTime, Time + "'#{value.to_s(:db)}'" + else + value.inspect + end + end + end + end +end diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb index 27700e4fd2..d5c7caad2e 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb @@ -3,6 +3,7 @@ require 'bigdecimal' require 'bigdecimal/util' require 'active_support/core_ext/benchmark' require 'active_record/connection_adapters/schema_cache' +require 'active_record/connection_adapters/abstract/schema_dumper' require 'monitor' module ActiveRecord @@ -52,6 +53,7 @@ module ActiveRecord include QueryCache include ActiveSupport::Callbacks include MonitorMixin + include ColumnDumper define_callbacks :checkout, :checkin diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/array_parser.rb b/activerecord/lib/active_record/connection_adapters/postgresql/array_parser.rb new file mode 100644 index 0000000000..b7d24f2bb3 --- /dev/null +++ b/activerecord/lib/active_record/connection_adapters/postgresql/array_parser.rb @@ -0,0 +1,97 @@ +module ActiveRecord + module ConnectionAdapters + class PostgreSQLColumn < Column + module ArrayParser + private + # Loads pg_array_parser if available. String parsing can be + # performed quicker by a native extension, which will not create + # a large amount of Ruby objects that will need to be garbage + # collected. pg_array_parser has a C and Java extension + begin + require 'pg_array_parser' + include PgArrayParser + rescue LoadError + def parse_pg_array(string) + parse_data(string, 0) + end + end + + def parse_data(string, index) + local_index = index + array = [] + while(local_index < string.length) + case string[local_index] + when '{' + local_index,array = parse_array_contents(array, string, local_index + 1) + when '}' + return array + end + local_index += 1 + end + + array + end + + def parse_array_contents(array, string, index) + is_escaping = false + is_quoted = false + was_quoted = false + current_item = '' + + local_index = index + while local_index + token = string[local_index] + if is_escaping + current_item << token + is_escaping = false + else + if is_quoted + case token + when '"' + is_quoted = false + was_quoted = true + when "\\" + is_escaping = true + else + current_item << token + end + else + case token + when "\\" + is_escaping = true + when ',' + add_item_to_array(array, current_item, was_quoted) + current_item = '' + was_quoted = false + when '"' + is_quoted = true + when '{' + internal_items = [] + local_index,internal_items = parse_array_contents(internal_items, string, local_index + 1) + array.push(internal_items) + when '}' + add_item_to_array(array, current_item, was_quoted) + return local_index,array + else + current_item << token + end + end + end + + local_index += 1 + end + return local_index,array + end + + def add_item_to_array(array, current_item, quoted) + if current_item.length == 0 + elsif !quoted && current_item == 'NULL' + array.push nil + else + array.push current_item + end + end + end + end + end +end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/cast.rb b/activerecord/lib/active_record/connection_adapters/postgresql/cast.rb index b59195f98a..62d091357d 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/cast.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/cast.rb @@ -45,6 +45,21 @@ module ActiveRecord end end + def array_to_string(value, column, adapter, should_be_quoted = false) + casted_values = value.map do |val| + if String === val + if val == "NULL" + "\"#{val}\"" + else + quote_and_escape(adapter.type_cast(val, column, true)) + end + else + adapter.type_cast(val, column, true) + end + end + "{#{casted_values.join(',')}}" + end + def string_to_json(string) if String === string ActiveSupport::JSON.decode(string) @@ -71,6 +86,10 @@ module ActiveRecord end end + def string_to_array(string, oid) + parse_pg_array(string).map{|val| oid.type_cast val} + end + private HstorePair = begin @@ -90,6 +109,15 @@ module ActiveRecord end end end + + def quote_and_escape(value) + case value + when "NULL" + value + else + "\"#{value.gsub(/"/,"\\\"")}\"" + end + end end end end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb b/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb index b8e7687b21..52344f61c0 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb @@ -63,6 +63,21 @@ module ActiveRecord end end + class Array < Type + attr_reader :subtype + def initialize(subtype) + @subtype = subtype + end + + def type_cast(value) + if String === value + ConnectionAdapters::PostgreSQLColumn.string_to_array value, @subtype + else + value + end + end + end + class Integer < Type def type_cast(value) return if value.nil? diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/quoting.rb b/activerecord/lib/active_record/connection_adapters/postgresql/quoting.rb index 85721601a9..37d43d891d 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/quoting.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/quoting.rb @@ -19,6 +19,12 @@ module ActiveRecord return super unless column case value + when Array + if column.array + "'#{PostgreSQLColumn.array_to_string(value, column, self)}'" + else + super + end when Hash case column.sql_type when 'hstore' then super(PostgreSQLColumn.hstore_to_string(value), column) @@ -59,24 +65,35 @@ module ActiveRecord end end - def type_cast(value, column) - return super unless column + def type_cast(value, column, array_member = false) + return super(value, column) unless column case value + when NilClass + if column.array && array_member + 'NULL' + elsif column.array + value + else + super(value, column) + end + when Array + return super(value, column) unless column.array + PostgreSQLColumn.array_to_string(value, column, self) when String - return super unless 'bytea' == column.sql_type + return super(value, column) unless 'bytea' == column.sql_type { :value => value, :format => 1 } when Hash case column.sql_type when 'hstore' then PostgreSQLColumn.hstore_to_string(value) when 'json' then PostgreSQLColumn.json_to_string(value) - else super + else super(value, column) end when IPAddr - return super unless ['inet','cidr'].includes? column.sql_type + return super(value, column) unless ['inet','cidr'].includes? column.sql_type PostgreSQLColumn.cidr_to_string(value) else - super + super(value, column) 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 d1751d70c6..1a727f9aa6 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -2,6 +2,7 @@ require 'active_record/connection_adapters/abstract_adapter' require 'active_record/connection_adapters/statement_pool' require 'active_record/connection_adapters/postgresql/oid' require 'active_record/connection_adapters/postgresql/cast' +require 'active_record/connection_adapters/postgresql/array_parser' require 'active_record/connection_adapters/postgresql/quoting' require 'active_record/connection_adapters/postgresql/schema_statements' require 'active_record/connection_adapters/postgresql/database_statements' @@ -41,16 +42,23 @@ module ActiveRecord module ConnectionAdapters # PostgreSQL-specific extensions to column definitions in a table. class PostgreSQLColumn < Column #:nodoc: + attr_accessor :array # Instantiates a new PostgreSQL column definition in a table. 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) + if sql_type =~ /\[\]$/ + @array = true + super(name, self.class.extract_value_from_default(default), sql_type[0..sql_type.length - 3], null) + else + @array = false + super(name, self.class.extract_value_from_default(default), sql_type, null) + end end # :stopdoc: class << self include ConnectionAdapters::PostgreSQLColumn::Cast - + include ConnectionAdapters::PostgreSQLColumn::ArrayParser attr_accessor :money_precision end # :startdoc: @@ -243,6 +251,10 @@ module ActiveRecord # In addition, default connection parameters of libpq can be set per environment variables. # See http://www.postgresql.org/docs/9.1/static/libpq-envars.html . class PostgreSQLAdapter < AbstractAdapter + class ColumnDefinition < ActiveRecord::ConnectionAdapters::ColumnDefinition + attr_accessor :array + end + class TableDefinition < ActiveRecord::ConnectionAdapters::TableDefinition def xml(*args) options = args.extract_options! @@ -277,6 +289,23 @@ module ActiveRecord def json(name, options = {}) column(name, 'json', options) end + + def column(name, type = nil, options = {}) + super + column = self[name] + column.array = options[:array] + + self + end + + private + + def new_column_definition(base, name, type) + definition = ColumnDefinition.new base, name, type + @columns << definition + @columns_hash[name] = definition + definition + end end ADAPTER_NAME = 'PostgreSQL' @@ -314,6 +343,19 @@ module ActiveRecord ADAPTER_NAME end + # Adds `:array` option to the default set provided by the + # AbstractAdapter + def prepare_column_options(column, types) + spec = super + spec[:array] = 'true' if column.respond_to?(:array) && column.array + spec + end + + # Adds `:array` as a valid migration key + def migration_keys + super + [:array] + end + # Returns +true+, since this connection adapter supports prepared statement # caching. def supports_statement_cache? @@ -494,6 +536,13 @@ module ActiveRecord @table_alias_length ||= query('SHOW max_identifier_length', 'SCHEMA')[0][0].to_i end + def add_column_options!(sql, options) + if options[:array] || options[:column].try(:array) + sql << '[]' + end + super + end + # Set the authorized user for this session def session_auth=(user) clear_cache! @@ -548,7 +597,7 @@ module ActiveRecord private def initialize_type_map - result = execute('SELECT oid, typname, typelem, typdelim FROM pg_type', 'SCHEMA') + result = execute('SELECT oid, typname, typelem, typdelim, typinput FROM pg_type', 'SCHEMA') leaves, nodes = result.partition { |row| row['typelem'] == '0' } # populate the leaf nodes @@ -556,11 +605,19 @@ module ActiveRecord OID::TYPE_MAP[row['oid'].to_i] = OID::NAMES[row['typname']] end + arrays, nodes = nodes.partition { |row| row['typinput'] == 'array_in' } + # 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 + + # populate array types + arrays.find_all { |row| OID::TYPE_MAP.key? row['typelem'].to_i }.each do |row| + array = OID::Array.new OID::TYPE_MAP[row['typelem'].to_i] + OID::TYPE_MAP[row['oid'].to_i] = array + end end FEATURE_NOT_SUPPORTED = "0A000" # :nodoc: @@ -703,12 +760,12 @@ 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, 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 - AND a.attnum > 0 AND NOT a.attisdropped - ORDER BY a.attnum + 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 + AND a.attnum > 0 AND NOT a.attisdropped + ORDER BY a.attnum end_sql end diff --git a/activerecord/lib/active_record/schema_dumper.rb b/activerecord/lib/active_record/schema_dumper.rb index 310b4c1459..36bde44e7c 100644 --- a/activerecord/lib/active_record/schema_dumper.rb +++ b/activerecord/lib/active_record/schema_dumper.rb @@ -107,27 +107,11 @@ HEADER column_specs = columns.map do |column| raise StandardError, "Unknown type '#{column.sql_type}' for column '#{column.name}'" if @types[column.type].nil? next if column.name == pk - spec = {} - spec[:name] = column.name.inspect - - # AR has an optimization which handles zero-scale decimals as integers. This - # code ensures that the dumper still dumps the column as a decimal. - spec[:type] = if column.type == :integer && /^(numeric|decimal)/ =~ column.sql_type - 'decimal' - else - column.type.to_s - end - spec[:limit] = column.limit.inspect if column.limit != @types[column.type][:limit] && spec[:type] != 'decimal' - spec[:precision] = column.precision.inspect if column.precision - spec[:scale] = column.scale.inspect if column.scale - spec[:null] = 'false' unless column.null - spec[:default] = default_string(column.default) if column.has_default? - (spec.keys - [:name, :type]).each{ |k| spec[k].insert(0, "#{k.to_s}: ")} - spec + @connection.column_spec(column, @types) end.compact # find all migration keys used in this table - keys = [:name, :limit, :precision, :scale, :default, :null] + keys = @connection.migration_keys # figure out the lengths for each column based on above keys lengths = keys.map { |key| @@ -170,17 +154,6 @@ HEADER stream end - def default_string(value) - case value - when BigDecimal - value.to_s - when Date, DateTime, Time - "'#{value.to_s(:db)}'" - else - value.inspect - end - end - def indexes(table, stream) if (indexes = @connection.indexes(table)).any? add_index_statements = indexes.map do |index| diff --git a/activerecord/test/cases/adapters/postgresql/array_test.rb b/activerecord/test/cases/adapters/postgresql/array_test.rb new file mode 100644 index 0000000000..8774bf626f --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/array_test.rb @@ -0,0 +1,98 @@ +# encoding: utf-8 +require "cases/helper" +require 'active_record/base' +require 'active_record/connection_adapters/postgresql_adapter' + +class PostgresqlArrayTest < ActiveRecord::TestCase + class PgArray < ActiveRecord::Base + self.table_name = 'pg_arrays' + end + + def setup + @connection = ActiveRecord::Base.connection + @connection.transaction do + @connection.create_table('pg_arrays') do |t| + t.string 'tags', :array => true + end + end + @column = PgArray.columns.find { |c| c.name == 'tags' } + end + + def teardown + @connection.execute 'drop table if exists pg_arrays' + end + + def test_column + assert_equal :string, @column.type + assert @column.array + end + + def test_type_cast_array + assert @column + + data = '{1,2,3}' + oid_type = @column.instance_variable_get('@oid_type').subtype + # we are getting the instance variable in this test, but in the + # normal use of string_to_array, it's called from the OID::Array + # class and will have the OID instance that will provide the type + # casting + array = @column.class.string_to_array data, oid_type + assert_equal(['1', '2', '3'], array) + assert_equal(['1', '2', '3'], @column.type_cast(data)) + + assert_equal([], @column.type_cast('{}')) + assert_equal([nil], @column.type_cast('{NULL}')) + end + + def test_rewrite + @connection.execute "insert into pg_arrays (tags) VALUES ('{1,2,3}')" + x = PgArray.first + x.tags = ['1','2','3','4'] + assert x.save! + end + + def test_select + @connection.execute "insert into pg_arrays (tags) VALUES ('{1,2,3}')" + x = PgArray.first + assert_equal(['1','2','3'], x.tags) + end + + def test_multi_dimensional + assert_cycle([['1','2'],['2','3']]) + end + + def test_strings_with_quotes + assert_cycle(['this has','some "s that need to be escaped"']) + end + + def test_strings_with_commas + assert_cycle(['this,has','many,values']) + end + + def test_strings_with_array_delimiters + assert_cycle(['{','}']) + end + + def test_strings_with_null_strings + assert_cycle(['NULL','NULL']) + end + + def test_contains_nils + assert_cycle(['1',nil,nil]) + end + + private + def assert_cycle array + # test creation + x = PgArray.create!(:tags => array) + x.reload + assert_equal(array, x.tags) + + # test updating + x = PgArray.create!(:tags => []) + x.tags = array + x.save! + x.reload + assert_equal(array, x.tags) + end +end diff --git a/activerecord/test/cases/adapters/postgresql/datatype_test.rb b/activerecord/test/cases/adapters/postgresql/datatype_test.rb index a7f6d9c580..c7ce43d71e 100644 --- a/activerecord/test/cases/adapters/postgresql/datatype_test.rb +++ b/activerecord/test/cases/adapters/postgresql/datatype_test.rb @@ -70,8 +70,8 @@ class PostgresqlDataTypeTest < ActiveRecord::TestCase end def test_data_type_of_array_types - assert_equal :string, @first_array.column_for_attribute(:commission_by_quarter).type - assert_equal :string, @first_array.column_for_attribute(:nicknames).type + assert_equal :integer, @first_array.column_for_attribute(:commission_by_quarter).type + assert_equal :text, @first_array.column_for_attribute(:nicknames).type end def test_data_type_of_tsvector_types @@ -112,8 +112,8 @@ class PostgresqlDataTypeTest < ActiveRecord::TestCase end def test_array_values - assert_equal '{35000,21000,18000,17000}', @first_array.commission_by_quarter - assert_equal '{foo,bar,baz}', @first_array.nicknames + assert_equal [35000,21000,18000,17000], @first_array.commission_by_quarter + assert_equal ['foo','bar','baz'], @first_array.nicknames end def test_tsvector_values @@ -170,7 +170,7 @@ class PostgresqlDataTypeTest < ActiveRecord::TestCase end def test_update_integer_array - new_value = '{32800,95000,29350,17000}' + new_value = [32800,95000,29350,17000] assert @first_array.commission_by_quarter = new_value assert @first_array.save assert @first_array.reload @@ -182,7 +182,7 @@ class PostgresqlDataTypeTest < ActiveRecord::TestCase end def test_update_text_array - new_value = '{robby,robert,rob,robbie}' + new_value = ['robby','robert','rob','robbie'] assert @first_array.nicknames = new_value assert @first_array.save assert @first_array.reload diff --git a/activerecord/test/cases/schema_dumper_test.rb b/activerecord/test/cases/schema_dumper_test.rb index 80d2670f94..80f46c6b08 100644 --- a/activerecord/test/cases/schema_dumper_test.rb +++ b/activerecord/test/cases/schema_dumper_test.rb @@ -79,9 +79,9 @@ class SchemaDumperTest < ActiveRecord::TestCase def test_arguments_line_up column_definition_lines.each do |column_set| - assert_line_up(column_set, /:default => /) - assert_line_up(column_set, /:limit => /) - assert_line_up(column_set, /:null => /) + assert_line_up(column_set, /default: /) + assert_line_up(column_set, /limit: /) + assert_line_up(column_set, /null: /) end end @@ -278,6 +278,14 @@ class SchemaDumperTest < ActiveRecord::TestCase end end + def test_schema_dump_includes_arrays_shorthand_definition + output = standard_dump + if %r{create_table "postgresql_arrays"} =~ output + assert_match %r[t.text\s+"nicknames",\s+array: true], output + assert_match %r[t.integer\s+"commission_by_quarter",\s+array: true], output + end + end + def test_schema_dump_includes_tsvector_shorthand_definition output = standard_dump if %r{create_table "postgresql_tsvectors"} =~ output |