diff options
Diffstat (limited to 'activerecord/test/cases/adapters/postgresql')
33 files changed, 4230 insertions, 0 deletions
diff --git a/activerecord/test/cases/adapters/postgresql/active_schema_test.rb b/activerecord/test/cases/adapters/postgresql/active_schema_test.rb new file mode 100644 index 0000000000..3808db5141 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/active_schema_test.rb @@ -0,0 +1,58 @@ +require 'cases/helper' + +class PostgresqlActiveSchemaTest < ActiveRecord::TestCase + def setup + ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval do + def execute(sql, name = nil) sql end + end + end + + teardown do + ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval do + remove_method :execute + end + end + + def test_create_database_with_encoding + assert_equal %(CREATE DATABASE "matt" ENCODING = 'utf8'), create_database(:matt) + assert_equal %(CREATE DATABASE "aimonetti" ENCODING = 'latin1'), create_database(:aimonetti, :encoding => :latin1) + assert_equal %(CREATE DATABASE "aimonetti" ENCODING = 'latin1'), create_database(:aimonetti, 'encoding' => :latin1) + end + + def test_create_database_with_collation_and_ctype + assert_equal %(CREATE DATABASE "aimonetti" ENCODING = 'UTF8' LC_COLLATE = 'ja_JP.UTF8' LC_CTYPE = 'ja_JP.UTF8'), create_database(:aimonetti, :encoding => :"UTF8", :collation => :"ja_JP.UTF8", :ctype => :"ja_JP.UTF8") + end + + def test_add_index + # add_index calls index_name_exists? which can't work since execute is stubbed + ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.stubs(:index_name_exists?).returns(false) + + expected = %(CREATE UNIQUE INDEX "index_people_on_last_name" ON "people" ("last_name") WHERE state = 'active') + assert_equal expected, add_index(:people, :last_name, :unique => true, :where => "state = 'active'") + + expected = %(CREATE INDEX CONCURRENTLY "index_people_on_last_name" ON "people" ("last_name")) + assert_equal expected, add_index(:people, :last_name, algorithm: :concurrently) + + %w(gin gist hash btree).each do |type| + expected = %(CREATE INDEX "index_people_on_last_name" ON "people" USING #{type} ("last_name")) + assert_equal expected, add_index(:people, :last_name, using: type) + + expected = %(CREATE INDEX CONCURRENTLY "index_people_on_last_name" ON "people" USING #{type} ("last_name")) + assert_equal expected, add_index(:people, :last_name, using: type, algorithm: :concurrently) + end + + assert_raise ArgumentError do + add_index(:people, :last_name, algorithm: :copy) + end + expected = %(CREATE UNIQUE INDEX "index_people_on_last_name" ON "people" USING gist ("last_name")) + assert_equal expected, add_index(:people, :last_name, :unique => true, :using => :gist) + + expected = %(CREATE UNIQUE INDEX "index_people_on_last_name" ON "people" USING gist ("last_name") WHERE state = 'active') + assert_equal expected, add_index(:people, :last_name, :unique => true, :where => "state = 'active'", :using => :gist) + end + + private + def method_missing(method_symbol, *arguments) + ActiveRecord::Base.connection.send(method_symbol, *arguments) + end +end 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..8df1b7d18c --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/array_test.rb @@ -0,0 +1,275 @@ +# encoding: utf-8 +require "cases/helper" + +class PostgresqlArrayTest < ActiveRecord::TestCase + include InTimeZone + OID = ActiveRecord::ConnectionAdapters::PostgreSQL::OID + + class PgArray < ActiveRecord::Base + self.table_name = 'pg_arrays' + end + + def setup + @connection = ActiveRecord::Base.connection + + unless @connection.extension_enabled?('hstore') + @connection.enable_extension 'hstore' + @connection.commit_db_transaction + end + + @connection.reconnect! + + @connection.transaction do + @connection.create_table('pg_arrays') do |t| + t.string 'tags', array: true + t.integer 'ratings', array: true + t.datetime :datetimes, array: true + t.hstore :hstores, array: true + end + end + @column = PgArray.columns_hash['tags'] + end + + teardown do + @connection.execute 'drop table if exists pg_arrays' + end + + def test_column + assert_equal :string, @column.type + assert_equal "character varying", @column.sql_type + assert @column.array + assert_not @column.number? + assert_not @column.binary? + + ratings_column = PgArray.columns_hash['ratings'] + assert_equal :integer, ratings_column.type + assert ratings_column.array + assert_not ratings_column.number? + end + + def test_default + @connection.add_column 'pg_arrays', 'score', :integer, array: true, default: [4, 4, 2] + PgArray.reset_column_information + + assert_equal([4, 4, 2], PgArray.column_defaults['score']) + assert_equal([4, 4, 2], PgArray.new.score) + ensure + PgArray.reset_column_information + end + + def test_default_strings + @connection.add_column 'pg_arrays', 'names', :string, array: true, default: ["foo", "bar"] + PgArray.reset_column_information + + assert_equal(["foo", "bar"], PgArray.column_defaults['names']) + assert_equal(["foo", "bar"], PgArray.new.names) + ensure + PgArray.reset_column_information + end + + def test_change_column_with_array + @connection.add_column :pg_arrays, :snippets, :string, array: true, default: [] + @connection.change_column :pg_arrays, :snippets, :text, array: true, default: [] + + PgArray.reset_column_information + column = PgArray.columns_hash['snippets'] + + assert_equal :text, column.type + assert_equal [], PgArray.column_defaults['snippets'] + assert column.array + end + + def test_change_column_cant_make_non_array_column_to_array + @connection.add_column :pg_arrays, :a_string, :string + assert_raises ActiveRecord::StatementInvalid do + @connection.transaction do + @connection.change_column :pg_arrays, :a_string, :string, array: true + end + end + end + + def test_change_column_default_with_array + @connection.change_column_default :pg_arrays, :tags, [] + + PgArray.reset_column_information + assert_equal [], PgArray.column_defaults['tags'] + end + + def test_type_cast_array + assert_equal(['1', '2', '3'], @column.type_cast_from_database('{1,2,3}')) + assert_equal([], @column.type_cast_from_database('{}')) + assert_equal([nil], @column.type_cast_from_database('{NULL}')) + end + + def test_type_cast_integers + x = PgArray.new(ratings: ['1', '2']) + + assert_equal([1, 2], x.ratings) + + x.save! + x.reload + + assert_equal([1, 2], x.ratings) + end + + def test_select_with_strings + @connection.execute "insert into pg_arrays (tags) VALUES ('{1,2,3}')" + x = PgArray.first + assert_equal(['1','2','3'], x.tags) + end + + def test_rewrite_with_strings + @connection.execute "insert into pg_arrays (tags) VALUES ('{1,2,3}')" + x = PgArray.first + x.tags = ['1','2','3','4'] + x.save! + assert_equal ['1','2','3','4'], x.reload.tags + end + + def test_select_with_integers + @connection.execute "insert into pg_arrays (ratings) VALUES ('{1,2,3}')" + x = PgArray.first + assert_equal([1, 2, 3], x.ratings) + end + + def test_rewrite_with_integers + @connection.execute "insert into pg_arrays (ratings) VALUES ('{1,2,3}')" + x = PgArray.first + x.ratings = [2, '3', 4] + x.save! + assert_equal [2, 3, 4], x.reload.ratings + end + + def test_multi_dimensional_with_strings + assert_cycle(:tags, [[['1'], ['2']], [['2'], ['3']]]) + end + + def test_with_empty_strings + assert_cycle(:tags, [ '1', '2', '', '4', '', '5' ]) + end + + def test_with_multi_dimensional_empty_strings + assert_cycle(:tags, [[['1', '2'], ['', '4'], ['', '5']]]) + end + + def test_with_arbitrary_whitespace + assert_cycle(:tags, [[['1', '2'], [' ', '4'], [' ', '5']]]) + end + + def test_multi_dimensional_with_integers + assert_cycle(:ratings, [[[1], [7]], [[8], [10]]]) + end + + def test_strings_with_quotes + assert_cycle(:tags, ['this has','some "s that need to be escaped"']) + end + + def test_strings_with_commas + assert_cycle(:tags, ['this,has','many,values']) + end + + def test_strings_with_array_delimiters + assert_cycle(:tags, ['{','}']) + end + + def test_strings_with_null_strings + assert_cycle(:tags, ['NULL','NULL']) + end + + def test_contains_nils + assert_cycle(:tags, ['1',nil,nil]) + end + + def test_insert_fixture + tag_values = ["val1", "val2", "val3_with_'_multiple_quote_'_chars"] + @connection.insert_fixture({"tags" => tag_values}, "pg_arrays" ) + assert_equal(PgArray.last.tags, tag_values) + end + + def test_attribute_for_inspect_for_array_field + record = PgArray.new { |a| a.ratings = (1..11).to_a } + assert_equal("[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...]", record.attribute_for_inspect(:ratings)) + end + + def test_escaping + unknown = 'foo\\",bar,baz,\\' + tags = ["hello_#{unknown}"] + ar = PgArray.create!(tags: tags) + ar.reload + assert_equal tags, ar.tags + end + + def test_string_quoting_rules_match_pg_behavior + tags = ["", "one{", "two}", %(three"), "four\\", "five ", "six\t", "seven\n", "eight,", "nine", "ten\r", "NULL"] + x = PgArray.create!(tags: tags) + x.reload + + assert_equal x.tags_before_type_cast, PgArray.columns_hash['tags'].type_cast_for_database(tags) + end + + def test_quoting_non_standard_delimiters + strings = ["hello,", "world;"] + comma_delim = OID::Array.new(ActiveRecord::Type::String.new, ',') + semicolon_delim = OID::Array.new(ActiveRecord::Type::String.new, ';') + + assert_equal %({"hello,",world;}), comma_delim.type_cast_for_database(strings) + assert_equal %({hello,;"world;"}), semicolon_delim.type_cast_for_database(strings) + end + + def test_mutate_array + x = PgArray.create!(tags: %w(one two)) + + x.tags << "three" + x.save! + x.reload + + assert_equal %w(one two three), x.tags + assert_not x.changed? + end + + def test_mutate_value_in_array + x = PgArray.create!(hstores: [{ a: 'a' }, { b: 'b' }]) + + x.hstores.first['a'] = 'c' + x.save! + x.reload + + assert_equal [{ 'a' => 'c' }, { 'b' => 'b' }], x.hstores + assert_not x.changed? + end + + def test_datetime_with_timezone_awareness + tz = "Pacific Time (US & Canada)" + + in_time_zone tz do + PgArray.reset_column_information + time_string = Time.current.to_s + time = Time.zone.parse(time_string) + + record = PgArray.new(datetimes: [time_string]) + assert_equal [time], record.datetimes + assert_equal ActiveSupport::TimeZone[tz], record.datetimes.first.time_zone + + record.save! + record.reload + + assert_equal [time], record.datetimes + assert_equal ActiveSupport::TimeZone[tz], record.datetimes.first.time_zone + end + end + + private + def assert_cycle field, array + # test creation + x = PgArray.create!(field => array) + x.reload + assert_equal(array, x.public_send(field)) + + # test updating + x = PgArray.create!(field => []) + x.public_send("#{field}=", array) + x.save! + x.reload + assert_equal(array, x.public_send(field)) + end +end diff --git a/activerecord/test/cases/adapters/postgresql/bit_string_test.rb b/activerecord/test/cases/adapters/postgresql/bit_string_test.rb new file mode 100644 index 0000000000..72222c01fd --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/bit_string_test.rb @@ -0,0 +1,76 @@ +# -*- coding: utf-8 -*- +require "cases/helper" +require 'support/connection_helper' +require 'support/schema_dumping_helper' + +class PostgresqlBitStringTest < ActiveRecord::TestCase + include ConnectionHelper + include SchemaDumpingHelper + + class PostgresqlBitString < ActiveRecord::Base; end + + def setup + @connection = ActiveRecord::Base.connection + @connection.create_table('postgresql_bit_strings', :force => true) do |t| + t.bit :a_bit, default: "00000011", limit: 8 + t.bit_varying :a_bit_varying, default: "0011", limit: 4 + end + end + + def teardown + return unless @connection + @connection.execute 'DROP TABLE IF EXISTS postgresql_bit_strings' + end + + def test_bit_string_column + column = PostgresqlBitString.columns_hash["a_bit"] + assert_equal :bit, column.type + assert_equal "bit(8)", column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_bit_string_varying_column + column = PostgresqlBitString.columns_hash["a_bit_varying"] + assert_equal :bit_varying, column.type + assert_equal "bit varying(4)", column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_default + assert_equal "00000011", PostgresqlBitString.column_defaults['a_bit'] + assert_equal "00000011", PostgresqlBitString.new.a_bit + + assert_equal "0011", PostgresqlBitString.column_defaults['a_bit_varying'] + assert_equal "0011", PostgresqlBitString.new.a_bit_varying + end + + def test_schema_dumping + output = dump_table_schema("postgresql_bit_strings") + assert_match %r{t\.bit\s+"a_bit",\s+limit: 8,\s+default: "00000011"$}, output + assert_match %r{t\.bit_varying\s+"a_bit_varying",\s+limit: 4,\s+default: "0011"$}, output + end + + def test_assigning_invalid_hex_string_raises_exception + assert_raises(ActiveRecord::StatementInvalid) { PostgresqlBitString.create! a_bit: "FF" } + assert_raises(ActiveRecord::StatementInvalid) { PostgresqlBitString.create! a_bit_varying: "FF" } + end + + def test_roundtrip + PostgresqlBitString.create! a_bit: "00001010", a_bit_varying: "0101" + record = PostgresqlBitString.first + assert_equal "00001010", record.a_bit + assert_equal "0101", record.a_bit_varying + + record.a_bit = "11111111" + record.a_bit_varying = "0xF" + record.save! + + assert record.reload + assert_equal "11111111", record.a_bit + assert_equal "1111", record.a_bit_varying + end +end diff --git a/activerecord/test/cases/adapters/postgresql/bytea_test.rb b/activerecord/test/cases/adapters/postgresql/bytea_test.rb new file mode 100644 index 0000000000..7872f91943 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/bytea_test.rb @@ -0,0 +1,118 @@ +# encoding: utf-8 +require "cases/helper" + +class PostgresqlByteaTest < ActiveRecord::TestCase + class ByteaDataType < ActiveRecord::Base + self.table_name = 'bytea_data_type' + end + + def setup + @connection = ActiveRecord::Base.connection + begin + @connection.transaction do + @connection.create_table('bytea_data_type') do |t| + t.binary 'payload' + t.binary 'serialized' + end + end + end + @column = ByteaDataType.columns_hash['payload'] + assert(@column.is_a?(ActiveRecord::ConnectionAdapters::PostgreSQLColumn)) + end + + teardown do + @connection.execute 'drop table if exists bytea_data_type' + end + + def test_column + assert_equal :binary, @column.type + end + + def test_type_cast_binary_converts_the_encoding + assert @column + + data = "\u001F\x8B" + assert_equal('UTF-8', data.encoding.name) + assert_equal('ASCII-8BIT', @column.type_cast_from_database(data).encoding.name) + end + + def test_type_cast_binary_value + data = "\u001F\x8B".force_encoding("BINARY") + assert_equal(data, @column.type_cast_from_database(data)) + end + + def test_type_case_nil + assert_equal(nil, @column.type_cast_from_database(nil)) + end + + def test_read_value + data = "\u001F" + @connection.execute "insert into bytea_data_type (payload) VALUES ('#{data}')" + record = ByteaDataType.first + assert_equal(data, record.payload) + record.delete + end + + def test_read_nil_value + @connection.execute "insert into bytea_data_type (payload) VALUES (null)" + record = ByteaDataType.first + assert_equal(nil, record.payload) + record.delete + end + + def test_write_value + data = "\u001F" + record = ByteaDataType.create(payload: data) + assert_not record.new_record? + assert_equal(data, record.payload) + end + + def test_via_to_sql + data = "'\u001F\\" + ByteaDataType.create(payload: data) + sql = ByteaDataType.where(payload: data).select(:payload).to_sql + result = @connection.query(sql) + assert_equal([[data]], result) + end + + def test_via_to_sql_with_complicating_connection + Thread.new do + other_conn = ActiveRecord::Base.connection + other_conn.execute('SET standard_conforming_strings = off') + end.join + + test_via_to_sql + end + + def test_write_binary + data = File.read(File.join(File.dirname(__FILE__), '..', '..', '..', 'assets', 'example.log')) + assert(data.size > 1) + record = ByteaDataType.create(payload: data) + assert_not record.new_record? + assert_equal(data, record.payload) + assert_equal(data, ByteaDataType.where(id: record.id).first.payload) + end + + def test_write_nil + record = ByteaDataType.create(payload: nil) + assert_not record.new_record? + assert_equal(nil, record.payload) + assert_equal(nil, ByteaDataType.where(id: record.id).first.payload) + end + + class Serializer + def load(str); str; end + def dump(str); str; end + end + + def test_serialize + klass = Class.new(ByteaDataType) { + serialize :serialized, Serializer.new + } + obj = klass.new + obj.serialized = "hello world" + obj.save! + obj.reload + assert_equal "hello world", obj.serialized + end +end diff --git a/activerecord/test/cases/adapters/postgresql/citext_test.rb b/activerecord/test/cases/adapters/postgresql/citext_test.rb new file mode 100644 index 0000000000..2acb64f81c --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/citext_test.rb @@ -0,0 +1,76 @@ +# encoding: utf-8 +require 'cases/helper' + +if ActiveRecord::Base.connection.supports_extensions? + class PostgresqlCitextTest < ActiveRecord::TestCase + class Citext < ActiveRecord::Base + self.table_name = 'citexts' + end + + def setup + @connection = ActiveRecord::Base.connection + + unless @connection.extension_enabled?('citext') + @connection.enable_extension 'citext' + @connection.commit_db_transaction + end + + @connection.reconnect! + + @connection.create_table('citexts') do |t| + t.citext 'cival' + end + end + + teardown do + @connection.execute 'DROP TABLE IF EXISTS citexts;' + @connection.execute 'DROP EXTENSION IF EXISTS citext CASCADE;' + end + + def test_citext_enabled + assert @connection.extension_enabled?('citext') + end + + def test_column + column = Citext.columns_hash['cival'] + assert_equal :citext, column.type + assert_equal 'citext', column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_change_table_supports_json + @connection.transaction do + @connection.change_table('citexts') do |t| + t.citext 'username' + end + Citext.reset_column_information + column = Citext.columns_hash['username'] + assert_equal :citext, column.type + + raise ActiveRecord::Rollback # reset the schema change + end + ensure + Citext.reset_column_information + end + + def test_write + x = Citext.new(cival: 'Some CI Text') + x.save! + citext = Citext.first + assert_equal "Some CI Text", citext.cival + + citext.cival = "Some NEW CI Text" + citext.save! + + assert_equal "Some NEW CI Text", citext.reload.cival + end + + def test_select_case_insensitive + @connection.execute "insert into citexts (cival) values('Cased Text')" + x = Citext.where(cival: 'cased text').first + assert_equal 'Cased Text', x.cival + end + end +end diff --git a/activerecord/test/cases/adapters/postgresql/composite_test.rb b/activerecord/test/cases/adapters/postgresql/composite_test.rb new file mode 100644 index 0000000000..cfab5ca902 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/composite_test.rb @@ -0,0 +1,131 @@ +# -*- coding: utf-8 -*- +require "cases/helper" +require 'support/connection_helper' + +module PostgresqlCompositeBehavior + include ConnectionHelper + + class PostgresqlComposite < ActiveRecord::Base + self.table_name = "postgresql_composites" + end + + def setup + super + + @connection = ActiveRecord::Base.connection + @connection.transaction do + @connection.execute <<-SQL + CREATE TYPE full_address AS + ( + city VARCHAR(90), + street VARCHAR(90) + ); + SQL + @connection.create_table('postgresql_composites') do |t| + t.column :address, :full_address + end + end + end + + def teardown + super + + @connection.execute 'DROP TABLE IF EXISTS postgresql_composites' + @connection.execute 'DROP TYPE IF EXISTS full_address' + reset_connection + PostgresqlComposite.reset_column_information + end +end + +# Composites are mapped to `OID::Identity` by default. The user is informed by a warning like: +# "unknown OID 5653508: failed to recognize type of 'address'. It will be treated as String." +# To take full advantage of composite types, we suggest you register your own +OID::Type+. +# See PostgresqlCompositeWithCustomOIDTest +class PostgresqlCompositeTest < ActiveRecord::TestCase + include PostgresqlCompositeBehavior + + def test_column + ensure_warning_is_issued + + column = PostgresqlComposite.columns_hash["address"] + assert_nil column.type + assert_equal "full_address", column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_composite_mapping + ensure_warning_is_issued + + @connection.execute "INSERT INTO postgresql_composites VALUES (1, ROW('Paris', 'Champs-Élysées'));" + composite = PostgresqlComposite.first + assert_equal "(Paris,Champs-Élysées)", composite.address + + composite.address = "(Paris,Rue Basse)" + composite.save! + + assert_equal '(Paris,"Rue Basse")', composite.reload.address + end + + private + def ensure_warning_is_issued + warning = capture(:stderr) do + PostgresqlComposite.columns_hash + end + assert_match(/unknown OID \d+: failed to recognize type of 'address'\. It will be treated as String\./, warning) + end +end + +class PostgresqlCompositeWithCustomOIDTest < ActiveRecord::TestCase + include PostgresqlCompositeBehavior + + class FullAddressType < ActiveRecord::Type::Value + def type; :full_address end + + def type_cast_from_database(value) + if value =~ /\("?([^",]*)"?,"?([^",]*)"?\)/ + FullAddress.new($1, $2) + end + end + + def type_cast_from_user(value) + value + end + + def type_cast_for_database(value) + return if value.nil? + "(#{value.city},#{value.street})" + end + end + + FullAddress = Struct.new(:city, :street) + + def setup + super + + @connection.type_map.register_type "full_address", FullAddressType.new + end + + def test_column + column = PostgresqlComposite.columns_hash["address"] + assert_equal :full_address, column.type + assert_equal "full_address", column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_composite_mapping + @connection.execute "INSERT INTO postgresql_composites VALUES (1, ROW('Paris', 'Champs-Élysées'));" + composite = PostgresqlComposite.first + assert_equal "Paris", composite.address.city + assert_equal "Champs-Élysées", composite.address.street + + composite.address = FullAddress.new("Paris", "Rue Basse") + composite.save! + + assert_equal 'Paris', composite.reload.address.city + assert_equal 'Rue Basse', composite.reload.address.street + end +end diff --git a/activerecord/test/cases/adapters/postgresql/connection_test.rb b/activerecord/test/cases/adapters/postgresql/connection_test.rb new file mode 100644 index 0000000000..d26cda46fa --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/connection_test.rb @@ -0,0 +1,205 @@ +require "cases/helper" +require 'support/connection_helper' + +module ActiveRecord + class PostgresqlConnectionTest < ActiveRecord::TestCase + include ConnectionHelper + + class NonExistentTable < ActiveRecord::Base + end + + def setup + super + @subscriber = SQLSubscriber.new + @subscription = ActiveSupport::Notifications.subscribe('sql.active_record', @subscriber) + @connection = ActiveRecord::Base.connection + end + + def teardown + ActiveSupport::Notifications.unsubscribe(@subscription) + super + end + + def test_encoding + assert_not_nil @connection.encoding + end + + def test_collation + assert_not_nil @connection.collation + end + + def test_ctype + assert_not_nil @connection.ctype + end + + def test_default_client_min_messages + assert_equal "warning", @connection.client_min_messages + end + + # Ensure, we can set connection params using the example of Generic + # Query Optimizer (geqo). It is 'on' per default. + def test_connection_options + params = ActiveRecord::Base.connection_config.dup + params[:options] = "-c geqo=off" + NonExistentTable.establish_connection(params) + + # Verify the connection param has been applied. + expect = NonExistentTable.connection.query('show geqo').first.first + assert_equal 'off', expect + end + + def test_reset + @connection.query('ROLLBACK') + @connection.query('SET geqo TO off') + + # Verify the setting has been applied. + expect = @connection.query('show geqo').first.first + assert_equal 'off', expect + + @connection.reset! + + # Verify the setting has been cleared. + expect = @connection.query('show geqo').first.first + assert_equal 'on', expect + end + + def test_reset_with_transaction + @connection.query('ROLLBACK') + @connection.query('SET geqo TO off') + + # Verify the setting has been applied. + expect = @connection.query('show geqo').first.first + assert_equal 'off', expect + + @connection.query('BEGIN') + @connection.reset! + + # Verify the setting has been cleared. + expect = @connection.query('show geqo').first.first + assert_equal 'on', expect + end + + def test_tables_logs_name + @connection.tables('hello') + assert_equal 'SCHEMA', @subscriber.logged[0][1] + end + + def test_indexes_logs_name + @connection.indexes('items', 'hello') + assert_equal 'SCHEMA', @subscriber.logged[0][1] + end + + def test_table_exists_logs_name + @connection.table_exists?('items') + assert_equal 'SCHEMA', @subscriber.logged[0][1] + end + + def test_table_alias_length_logs_name + @connection.instance_variable_set("@table_alias_length", nil) + @connection.table_alias_length + assert_equal 'SCHEMA', @subscriber.logged[0][1] + end + + def test_current_database_logs_name + @connection.current_database + assert_equal 'SCHEMA', @subscriber.logged[0][1] + end + + def test_encoding_logs_name + @connection.encoding + assert_equal 'SCHEMA', @subscriber.logged[0][1] + end + + def test_schema_names_logs_name + @connection.schema_names + assert_equal 'SCHEMA', @subscriber.logged[0][1] + end + + def test_statement_key_is_logged + bindval = 1 + @connection.exec_query('SELECT $1::integer', 'SQL', [[nil, bindval]]) + name = @subscriber.payloads.last[:statement_name] + assert name + res = @connection.exec_query("EXPLAIN (FORMAT JSON) EXECUTE #{name}(#{bindval})") + plan = res.column_types['QUERY PLAN'].type_cast_from_database res.rows.first.first + assert_operator plan.length, :>, 0 + end + + # Must have PostgreSQL >= 9.2, or with_manual_interventions set to + # true for this test to run. + # + # When prompted, restart the PostgreSQL server with the + # "-m fast" option or kill the individual connection assuming + # you know the incantation to do that. + # To restart PostgreSQL 9.1 on OS X, installed via MacPorts, ... + # sudo su postgres -c "pg_ctl restart -D /opt/local/var/db/postgresql91/defaultdb/ -m fast" + def test_reconnection_after_actual_disconnection_with_verify + original_connection_pid = @connection.query('select pg_backend_pid()') + + # Sanity check. + assert @connection.active? + + if @connection.send(:postgresql_version) >= 90200 + secondary_connection = ActiveRecord::Base.connection_pool.checkout + secondary_connection.query("select pg_terminate_backend(#{original_connection_pid.first.first})") + ActiveRecord::Base.connection_pool.checkin(secondary_connection) + elsif ARTest.config['with_manual_interventions'] + puts 'Kill the connection now (e.g. by restarting the PostgreSQL ' + + 'server with the "-m fast" option) and then press enter.' + $stdin.gets + else + # We're not capable of terminating the backend ourselves, and + # we're not allowed to seek assistance; bail out without + # actually testing anything. + return + end + + @connection.verify! + + assert @connection.active? + + # If we get no exception here, then either we re-connected successfully, or + # we never actually got disconnected. + new_connection_pid = @connection.query('select pg_backend_pid()') + + assert_not_equal original_connection_pid, new_connection_pid, + "umm -- looks like you didn't break the connection, because we're still " + + "successfully querying with the same connection pid." + + # Repair all fixture connections so other tests won't break. + @fixture_connections.each do |c| + c.verify! + end + end + + def test_set_session_variable_true + run_without_connection do |orig_connection| + ActiveRecord::Base.establish_connection(orig_connection.deep_merge({:variables => {:debug_print_plan => true}})) + set_true = ActiveRecord::Base.connection.exec_query "SHOW DEBUG_PRINT_PLAN" + assert_equal set_true.rows, [["on"]] + end + end + + def test_set_session_variable_false + run_without_connection do |orig_connection| + ActiveRecord::Base.establish_connection(orig_connection.deep_merge({:variables => {:debug_print_plan => false}})) + set_false = ActiveRecord::Base.connection.exec_query "SHOW DEBUG_PRINT_PLAN" + assert_equal set_false.rows, [["off"]] + end + end + + def test_set_session_variable_nil + run_without_connection do |orig_connection| + # This should be a no-op that does not raise an error + ActiveRecord::Base.establish_connection(orig_connection.deep_merge({:variables => {:debug_print_plan => nil}})) + end + end + + def test_set_session_variable_default + run_without_connection do |orig_connection| + # This should execute a query that does not raise an error + ActiveRecord::Base.establish_connection(orig_connection.deep_merge({:variables => {:debug_print_plan => :default}})) + end + end + end +end diff --git a/activerecord/test/cases/adapters/postgresql/datatype_test.rb b/activerecord/test/cases/adapters/postgresql/datatype_test.rb new file mode 100644 index 0000000000..a0a34e4b87 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/datatype_test.rb @@ -0,0 +1,119 @@ +require "cases/helper" +require 'support/ddl_helper' + + +class PostgresqlNumber < ActiveRecord::Base +end + +class PostgresqlTime < ActiveRecord::Base +end + +class PostgresqlOid < ActiveRecord::Base +end + +class PostgresqlLtree < ActiveRecord::Base +end + +class PostgresqlDataTypeTest < ActiveRecord::TestCase + self.use_transactional_fixtures = false + + def setup + @connection = ActiveRecord::Base.connection + + @connection.execute("INSERT INTO postgresql_numbers (id, single, double) VALUES (1, 123.456, 123456.789)") + @connection.execute("INSERT INTO postgresql_numbers (id, single, double) VALUES (2, '-Infinity', 'Infinity')") + @connection.execute("INSERT INTO postgresql_numbers (id, single, double) VALUES (3, 123.456, 'NaN')") + @first_number = PostgresqlNumber.find(1) + @second_number = PostgresqlNumber.find(2) + @third_number = PostgresqlNumber.find(3) + + @connection.execute("INSERT INTO postgresql_times (id, time_interval, scaled_time_interval) VALUES (1, '1 year 2 days ago', '3 weeks ago')") + @first_time = PostgresqlTime.find(1) + + @connection.execute("INSERT INTO postgresql_oids (id, obj_id) VALUES (1, 1234)") + @first_oid = PostgresqlOid.find(1) + end + + teardown do + [PostgresqlNumber, PostgresqlTime, PostgresqlOid].each(&:delete_all) + 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 + assert_equal :string, @first_time.column_for_attribute(:scaled_time_interval).type + end + + def test_data_type_of_oid_types + assert_equal :integer, @first_oid.column_for_attribute(:obj_id).type + end + + def test_number_values + assert_equal 123.456, @first_number.single + assert_equal 123456.789, @first_number.double + assert_equal(-::Float::INFINITY, @second_number.single) + assert_equal ::Float::INFINITY, @second_number.double + assert_same ::Float::NAN, @third_number.double + end + + def test_time_values + assert_equal '-1 years -2 days', @first_time.time_interval + assert_equal '-21 days', @first_time.scaled_time_interval + end + + def test_oid_values + assert_equal 1234, @first_oid.obj_id + end + + def test_update_number + new_single = 789.012 + new_double = 789012.345 + @first_number.single = new_single + @first_number.double = new_double + assert @first_number.save + assert @first_number.reload + assert_equal new_single, @first_number.single + assert_equal new_double, @first_number.double + end + + def test_update_time + @first_time.time_interval = '2 years 3 minutes' + assert @first_time.save + assert @first_time.reload + assert_equal '2 years 00:03:00', @first_time.time_interval + end + + def test_update_oid + new_value = 567890 + @first_oid.obj_id = new_value + assert @first_oid.save + assert @first_oid.reload + assert_equal new_value, @first_oid.obj_id + end +end + +class PostgresqlInternalDataTypeTest < ActiveRecord::TestCase + include DdlHelper + + setup do + @connection = ActiveRecord::Base.connection + end + + def test_name_column_type + with_example_table @connection, 'ex', 'data name' do + column = @connection.columns('ex').find { |col| col.name == 'data' } + assert_equal :string, column.type + end + end + + def test_char_column_type + with_example_table @connection, 'ex', 'data "char"' do + column = @connection.columns('ex').find { |col| col.name == 'data' } + assert_equal :string, column.type + end + end +end diff --git a/activerecord/test/cases/adapters/postgresql/domain_test.rb b/activerecord/test/cases/adapters/postgresql/domain_test.rb new file mode 100644 index 0000000000..1500adb42d --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/domain_test.rb @@ -0,0 +1,47 @@ +# -*- coding: utf-8 -*- +require "cases/helper" +require 'support/connection_helper' + +class PostgresqlDomainTest < ActiveRecord::TestCase + include ConnectionHelper + + class PostgresqlDomain < ActiveRecord::Base + self.table_name = "postgresql_domains" + end + + def setup + @connection = ActiveRecord::Base.connection + @connection.transaction do + @connection.execute "CREATE DOMAIN custom_money as numeric(8,2)" + @connection.create_table('postgresql_domains') do |t| + t.column :price, :custom_money + end + end + end + + teardown do + @connection.execute 'DROP TABLE IF EXISTS postgresql_domains' + @connection.execute 'DROP DOMAIN IF EXISTS custom_money' + reset_connection + end + + def test_column + column = PostgresqlDomain.columns_hash["price"] + assert_equal :decimal, column.type + assert_equal "custom_money", column.sql_type + assert column.number? + assert_not column.binary? + assert_not column.array + end + + def test_domain_acts_like_basetype + PostgresqlDomain.create price: "" + record = PostgresqlDomain.first + assert_nil record.price + + record.price = "34.15" + record.save! + + assert_equal BigDecimal.new("34.15"), record.reload.price + end +end diff --git a/activerecord/test/cases/adapters/postgresql/enum_test.rb b/activerecord/test/cases/adapters/postgresql/enum_test.rb new file mode 100644 index 0000000000..d99c4a292e --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/enum_test.rb @@ -0,0 +1,85 @@ +# -*- coding: utf-8 -*- +require "cases/helper" +require 'support/connection_helper' +require 'active_record/base' +require 'active_record/connection_adapters/postgresql_adapter' + +class PostgresqlEnumTest < ActiveRecord::TestCase + include ConnectionHelper + + class PostgresqlEnum < ActiveRecord::Base + self.table_name = "postgresql_enums" + end + + def setup + @connection = ActiveRecord::Base.connection + @connection.transaction do + @connection.execute <<-SQL + CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); + SQL + @connection.create_table('postgresql_enums') do |t| + t.column :current_mood, :mood + end + end + end + + teardown do + @connection.execute 'DROP TABLE IF EXISTS postgresql_enums' + @connection.execute 'DROP TYPE IF EXISTS mood' + reset_connection + end + + def test_column + column = PostgresqlEnum.columns_hash["current_mood"] + assert_equal :enum, column.type + assert_equal "mood", column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_enum_defaults + @connection.add_column 'postgresql_enums', 'good_mood', :mood, default: 'happy' + PostgresqlEnum.reset_column_information + + assert_equal "happy", PostgresqlEnum.column_defaults['good_mood'] + assert_equal "happy", PostgresqlEnum.new.good_mood + ensure + PostgresqlEnum.reset_column_information + end + + def test_enum_mapping + @connection.execute "INSERT INTO postgresql_enums VALUES (1, 'sad');" + enum = PostgresqlEnum.first + assert_equal "sad", enum.current_mood + + enum.current_mood = "happy" + enum.save! + + assert_equal "happy", enum.reload.current_mood + end + + def test_invalid_enum_update + @connection.execute "INSERT INTO postgresql_enums VALUES (1, 'sad');" + enum = PostgresqlEnum.first + enum.current_mood = "angry" + + assert_raise ActiveRecord::StatementInvalid do + enum.save + end + end + + def test_no_oid_warning + @connection.execute "INSERT INTO postgresql_enums VALUES (1, 'sad');" + stderr_output = capture(:stderr) { PostgresqlEnum.first } + + assert stderr_output.blank? + end + + def test_enum_type_cast + enum = PostgresqlEnum.new + enum.current_mood = :happy + + assert_equal "happy", enum.current_mood + end +end diff --git a/activerecord/test/cases/adapters/postgresql/explain_test.rb b/activerecord/test/cases/adapters/postgresql/explain_test.rb new file mode 100644 index 0000000000..416f84cb38 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/explain_test.rb @@ -0,0 +1,28 @@ +require "cases/helper" +require 'models/developer' + +module ActiveRecord + module ConnectionAdapters + class PostgreSQLAdapter + class ExplainTest < ActiveRecord::TestCase + fixtures :developers + + def test_explain_for_one_query + explain = Developer.where(:id => 1).explain + assert_match %(EXPLAIN for: SELECT "developers".* FROM "developers" WHERE "developers"."id" = $1), explain + assert_match %(QUERY PLAN), explain + assert_match %(Index Scan using developers_pkey on developers), explain + end + + def test_explain_with_eager_loading + explain = Developer.where(:id => 1).includes(:audit_logs).explain + assert_match %(QUERY PLAN), explain + assert_match %(EXPLAIN for: SELECT "developers".* FROM "developers" WHERE "developers"."id" = $1), explain + assert_match %(Index Scan using developers_pkey on developers), explain + assert_match %(EXPLAIN for: SELECT "audit_logs".* FROM "audit_logs" WHERE "audit_logs"."developer_id" IN (1)), explain + assert_match %(Seq Scan on audit_logs), explain + end + end + end + end +end diff --git a/activerecord/test/cases/adapters/postgresql/extension_migration_test.rb b/activerecord/test/cases/adapters/postgresql/extension_migration_test.rb new file mode 100644 index 0000000000..7b99fcdda0 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/extension_migration_test.rb @@ -0,0 +1,63 @@ +require "cases/helper" + +class PostgresqlExtensionMigrationTest < ActiveRecord::TestCase + self.use_transactional_fixtures = false + + class EnableHstore < ActiveRecord::Migration + def change + enable_extension "hstore" + end + end + + class DisableHstore < ActiveRecord::Migration + def change + disable_extension "hstore" + end + end + + def setup + super + + @connection = ActiveRecord::Base.connection + + unless @connection.supports_extensions? + return skip("no extension support") + end + + @old_schema_migration_tabel_name = ActiveRecord::SchemaMigration.table_name + @old_tabel_name_prefix = ActiveRecord::Base.table_name_prefix + @old_tabel_name_suffix = ActiveRecord::Base.table_name_suffix + + ActiveRecord::Base.table_name_prefix = "p_" + ActiveRecord::Base.table_name_suffix = "_s" + ActiveRecord::SchemaMigration.delete_all rescue nil + ActiveRecord::SchemaMigration.table_name = "p_schema_migrations_s" + ActiveRecord::Migration.verbose = false + end + + def teardown + ActiveRecord::Base.table_name_prefix = @old_tabel_name_prefix + ActiveRecord::Base.table_name_suffix = @old_tabel_name_suffix + ActiveRecord::SchemaMigration.delete_all rescue nil + ActiveRecord::Migration.verbose = true + ActiveRecord::SchemaMigration.table_name = @old_schema_migration_tabel_name + + super + end + + def test_enable_extension_migration_ignores_prefix_and_suffix + @connection.disable_extension("hstore") + + migrations = [EnableHstore.new(nil, 1)] + ActiveRecord::Migrator.new(:up, migrations).migrate + assert @connection.extension_enabled?("hstore"), "extension hstore should be enabled" + end + + def test_disable_extension_migration_ignores_prefix_and_suffix + @connection.enable_extension("hstore") + + migrations = [DisableHstore.new(nil, 1)] + ActiveRecord::Migrator.new(:up, migrations).migrate + assert_not @connection.extension_enabled?("hstore"), "extension hstore should not be enabled" + end +end diff --git a/activerecord/test/cases/adapters/postgresql/full_text_test.rb b/activerecord/test/cases/adapters/postgresql/full_text_test.rb new file mode 100644 index 0000000000..9dadb177ca --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/full_text_test.rb @@ -0,0 +1,26 @@ +# encoding: utf-8 +require "cases/helper" + +class PostgresqlFullTextTest < ActiveRecord::TestCase + class PostgresqlTsvector < ActiveRecord::Base; end + + def test_tsvector_column + column = PostgresqlTsvector.columns_hash["text_vector"] + assert_equal :tsvector, column.type + assert_equal "tsvector", column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_update_tsvector + PostgresqlTsvector.create text_vector: "'text' 'vector'" + tsvector = PostgresqlTsvector.first + assert_equal "'text' 'vector'", tsvector.text_vector + + tsvector.text_vector = "'new' 'text' 'vector'" + tsvector.save! + assert tsvector.reload + assert_equal "'new' 'text' 'vector'", tsvector.text_vector + end +end diff --git a/activerecord/test/cases/adapters/postgresql/geometric_test.rb b/activerecord/test/cases/adapters/postgresql/geometric_test.rb new file mode 100644 index 0000000000..6c0adbbeaa --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/geometric_test.rb @@ -0,0 +1,72 @@ +# -*- coding: utf-8 -*- +require "cases/helper" +require 'support/connection_helper' +require 'support/schema_dumping_helper' + +class PostgresqlPointTest < ActiveRecord::TestCase + include ConnectionHelper + include SchemaDumpingHelper + + class PostgresqlPoint < ActiveRecord::Base; end + + def setup + @connection = ActiveRecord::Base.connection + @connection.transaction do + @connection.create_table('postgresql_points') do |t| + t.point :x + t.point :y, default: [12.2, 13.3] + t.point :z, default: "(14.4,15.5)" + end + end + end + + teardown do + @connection.execute 'DROP TABLE IF EXISTS postgresql_points' + end + + def test_column + column = PostgresqlPoint.columns_hash["x"] + assert_equal :point, column.type + assert_equal "point", column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_default + assert_equal [12.2, 13.3], PostgresqlPoint.column_defaults['y'] + assert_equal [12.2, 13.3], PostgresqlPoint.new.y + + assert_equal [14.4, 15.5], PostgresqlPoint.column_defaults['z'] + assert_equal [14.4, 15.5], PostgresqlPoint.new.z + end + + def test_schema_dumping + output = dump_table_schema("postgresql_points") + assert_match %r{t\.point\s+"x"$}, output + assert_match %r{t\.point\s+"y",\s+default: \[12\.2, 13\.3\]$}, output + assert_match %r{t\.point\s+"z",\s+default: \[14\.4, 15\.5\]$}, output + end + + def test_roundtrip + PostgresqlPoint.create! x: [10, 25.2] + record = PostgresqlPoint.first + assert_equal [10, 25.2], record.x + + record.x = [1.1, 2.2] + record.save! + assert record.reload + assert_equal [1.1, 2.2], record.x + end + + def test_mutation + p = PostgresqlPoint.create! x: [10, 20] + + p.x[1] = 25 + p.save! + p.reload + + assert_equal [10.0, 25.0], p.x + assert_not p.changed? + end +end diff --git a/activerecord/test/cases/adapters/postgresql/hstore_test.rb b/activerecord/test/cases/adapters/postgresql/hstore_test.rb new file mode 100644 index 0000000000..1296eb72c0 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/hstore_test.rb @@ -0,0 +1,349 @@ +# encoding: utf-8 + +require "cases/helper" +require 'active_record/base' +require 'active_record/connection_adapters/postgresql_adapter' + +class PostgresqlHstoreTest < ActiveRecord::TestCase + class Hstore < ActiveRecord::Base + self.table_name = 'hstores' + + store_accessor :settings, :language, :timezone + end + + def setup + @connection = ActiveRecord::Base.connection + + unless @connection.extension_enabled?('hstore') + @connection.enable_extension 'hstore' + @connection.commit_db_transaction + end + + @connection.reconnect! + + @connection.transaction do + @connection.create_table('hstores') do |t| + t.hstore 'tags', :default => '' + t.hstore 'payload', array: true + t.hstore 'settings' + end + end + @column = Hstore.columns_hash['tags'] + end + + teardown do + @connection.execute 'drop table if exists hstores' + end + + if ActiveRecord::Base.connection.supports_extensions? + def test_hstore_included_in_extensions + assert @connection.respond_to?(:extensions), "connection should have a list of extensions" + assert @connection.extensions.include?('hstore'), "extension list should include hstore" + end + + def test_disable_enable_hstore + assert @connection.extension_enabled?('hstore') + @connection.disable_extension 'hstore' + assert_not @connection.extension_enabled?('hstore') + @connection.enable_extension 'hstore' + assert @connection.extension_enabled?('hstore') + ensure + # Restore column(s) dropped by `drop extension hstore cascade;` + load_schema + end + + def test_column + assert_equal :hstore, @column.type + assert_equal "hstore", @column.sql_type + assert_not @column.number? + assert_not @column.binary? + assert_not @column.array + end + + def test_default + @connection.add_column 'hstores', 'permissions', :hstore, default: '"users"=>"read", "articles"=>"write"' + Hstore.reset_column_information + + assert_equal({"users"=>"read", "articles"=>"write"}, Hstore.column_defaults['permissions']) + assert_equal({"users"=>"read", "articles"=>"write"}, Hstore.new.permissions) + ensure + Hstore.reset_column_information + end + + def test_change_table_supports_hstore + @connection.transaction do + @connection.change_table('hstores') do |t| + t.hstore 'users', default: '' + end + Hstore.reset_column_information + column = Hstore.columns_hash['users'] + assert_equal :hstore, column.type + + raise ActiveRecord::Rollback # reset the schema change + end + ensure + Hstore.reset_column_information + end + + def test_hstore_migration + hstore_migration = Class.new(ActiveRecord::Migration) do + def change + change_table("hstores") do |t| + t.hstore :keys + end + end + end + + hstore_migration.new.suppress_messages do + hstore_migration.migrate(:up) + assert_includes @connection.columns(:hstores).map(&:name), "keys" + hstore_migration.migrate(:down) + assert_not_includes @connection.columns(:hstores).map(&:name), "keys" + end + end + + def test_cast_value_on_write + x = Hstore.new tags: {"bool" => true, "number" => 5} + assert_equal({"bool" => true, "number" => 5}, x.tags_before_type_cast) + assert_equal({"bool" => "true", "number" => "5"}, x.tags) + x.save + assert_equal({"bool" => "true", "number" => "5"}, x.reload.tags) + end + + def test_type_cast_hstore + assert_equal({'1' => '2'}, @column.type_cast_from_database("\"1\"=>\"2\"")) + assert_equal({}, @column.type_cast_from_database("")) + assert_equal({'key'=>nil}, @column.type_cast_from_database('key => NULL')) + assert_equal({'c'=>'}','"a"'=>'b "a b'}, @column.type_cast_from_database(%q(c=>"}", "\"a\""=>"b \"a b"))) + end + + def test_with_store_accessors + x = Hstore.new(language: "fr", timezone: "GMT") + assert_equal "fr", x.language + assert_equal "GMT", x.timezone + + x.save! + x = Hstore.first + assert_equal "fr", x.language + assert_equal "GMT", x.timezone + + x.language = "de" + x.save! + + x = Hstore.first + assert_equal "de", x.language + assert_equal "GMT", x.timezone + end + + def test_duplication_with_store_accessors + x = Hstore.new(language: "fr", timezone: "GMT") + assert_equal "fr", x.language + assert_equal "GMT", x.timezone + + y = x.dup + assert_equal "fr", y.language + assert_equal "GMT", y.timezone + end + + def test_yaml_round_trip_with_store_accessors + x = Hstore.new(language: "fr", timezone: "GMT") + assert_equal "fr", x.language + assert_equal "GMT", x.timezone + + y = YAML.load(YAML.dump(x)) + assert_equal "fr", y.language + assert_equal "GMT", y.timezone + end + + def test_changes_in_place + hstore = Hstore.create!(settings: { 'one' => 'two' }) + hstore.settings['three'] = 'four' + hstore.save! + hstore.reload + + assert_equal 'four', hstore.settings['three'] + assert_not hstore.changed? + end + + def test_gen1 + assert_equal(%q(" "=>""), @column.cast_type.type_cast_for_database({' '=>''})) + end + + def test_gen2 + assert_equal(%q(","=>""), @column.cast_type.type_cast_for_database({','=>''})) + end + + def test_gen3 + assert_equal(%q("="=>""), @column.cast_type.type_cast_for_database({'='=>''})) + end + + def test_gen4 + assert_equal(%q(">"=>""), @column.cast_type.type_cast_for_database({'>'=>''})) + end + + def test_parse1 + assert_equal({'a'=>nil,'b'=>nil,'c'=>'NuLl','null'=>'c'}, @column.type_cast_from_database('a=>null,b=>NuLl,c=>"NuLl",null=>c')) + end + + def test_parse2 + assert_equal({" " => " "}, @column.type_cast_from_database("\\ =>\\ ")) + end + + def test_parse3 + assert_equal({"=" => ">"}, @column.type_cast_from_database("==>>")) + end + + def test_parse4 + assert_equal({"=a"=>"q=w"}, @column.type_cast_from_database('\=a=>q=w')) + end + + def test_parse5 + assert_equal({"=a"=>"q=w"}, @column.type_cast_from_database('"=a"=>q\=w')) + end + + def test_parse6 + assert_equal({"\"a"=>"q>w"}, @column.type_cast_from_database('"\"a"=>q>w')) + end + + def test_parse7 + assert_equal({"\"a"=>"q\"w"}, @column.type_cast_from_database('\"a=>q"w')) + end + + def test_rewrite + @connection.execute "insert into hstores (tags) VALUES ('1=>2')" + x = Hstore.first + x.tags = { '"a\'' => 'b' } + assert x.save! + end + + def test_select + @connection.execute "insert into hstores (tags) VALUES ('1=>2')" + x = Hstore.first + assert_equal({'1' => '2'}, x.tags) + end + + def test_array_cycle + assert_array_cycle([{"AA" => "BB", "CC" => "DD"}, {"AA" => nil}]) + end + + def test_array_strings_with_quotes + assert_array_cycle([{'this has' => 'some "s that need to be escaped"'}]) + end + + def test_array_strings_with_commas + assert_array_cycle([{'this,has' => 'many,values'}]) + end + + def test_array_strings_with_array_delimiters + assert_array_cycle(['{' => '}']) + end + + def test_array_strings_with_null_strings + assert_array_cycle([{'NULL' => 'NULL'}]) + end + + def test_contains_nils + assert_array_cycle([{'NULL' => nil}]) + end + + def test_select_multikey + @connection.execute "insert into hstores (tags) VALUES ('1=>2,2=>3')" + x = Hstore.first + assert_equal({'1' => '2', '2' => '3'}, x.tags) + end + + def test_create + assert_cycle('a' => 'b', '1' => '2') + end + + def test_nil + assert_cycle('a' => nil) + end + + def test_quotes + assert_cycle('a' => 'b"ar', '1"foo' => '2') + end + + def test_whitespace + assert_cycle('a b' => 'b ar', '1"foo' => '2') + end + + def test_backslash + assert_cycle('a\\b' => 'b\\ar', '1"foo' => '2') + end + + def test_comma + assert_cycle('a, b' => 'bar', '1"foo' => '2') + end + + def test_arrow + assert_cycle('a=>b' => 'bar', '1"foo' => '2') + end + + def test_quoting_special_characters + assert_cycle('ca' => 'cà', 'ac' => 'àc') + end + + def test_multiline + assert_cycle("a\nb" => "c\nd") + end + + class TagCollection + def initialize(hash); @hash = hash end + def to_hash; @hash end + def self.load(hash); new(hash) end + def self.dump(object); object.to_hash end + end + + class HstoreWithSerialize < Hstore + serialize :tags, TagCollection + end + + def test_hstore_with_serialized_attributes + HstoreWithSerialize.create! tags: TagCollection.new({"one" => "two"}) + record = HstoreWithSerialize.first + assert_instance_of TagCollection, record.tags + assert_equal({"one" => "two"}, record.tags.to_hash) + record.tags = TagCollection.new("three" => "four") + record.save! + assert_equal({"three" => "four"}, HstoreWithSerialize.first.tags.to_hash) + end + + def test_clone_hstore_with_serialized_attributes + HstoreWithSerialize.create! tags: TagCollection.new({"one" => "two"}) + record = HstoreWithSerialize.first + dupe = record.dup + assert_equal({"one" => "two"}, dupe.tags.to_hash) + end + end + + private + + def assert_array_cycle(array) + # test creation + x = Hstore.create!(payload: array) + x.reload + assert_equal(array, x.payload) + + # test updating + x = Hstore.create!(payload: []) + x.payload = array + x.save! + x.reload + assert_equal(array, x.payload) + end + + def assert_cycle(hash) + # test creation + x = Hstore.create!(:tags => hash) + x.reload + assert_equal(hash, x.tags) + + # test updating + x = Hstore.create!(:tags => {}) + x.tags = hash + x.save! + x.reload + assert_equal(hash, x.tags) + end +end diff --git a/activerecord/test/cases/adapters/postgresql/infinity_test.rb b/activerecord/test/cases/adapters/postgresql/infinity_test.rb new file mode 100644 index 0000000000..22e8873333 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/infinity_test.rb @@ -0,0 +1,44 @@ +require "cases/helper" + +class PostgresqlInfinityTest < ActiveRecord::TestCase + class PostgresqlInfinity < ActiveRecord::Base + end + + setup do + @connection = ActiveRecord::Base.connection + @connection.create_table(:postgresql_infinities) do |t| + t.float :float + t.datetime :datetime + end + end + + teardown do + @connection.execute("DROP TABLE IF EXISTS postgresql_infinities") + end + + test "type casting infinity on a float column" do + record = PostgresqlInfinity.create!(float: Float::INFINITY) + record.reload + assert_equal Float::INFINITY, record.float + end + + test "update_all with infinity on a float column" do + record = PostgresqlInfinity.create! + PostgresqlInfinity.update_all(float: Float::INFINITY) + record.reload + assert_equal Float::INFINITY, record.float + end + + test "type casting infinity on a datetime column" do + record = PostgresqlInfinity.create!(datetime: Float::INFINITY) + record.reload + assert_equal Float::INFINITY, record.datetime + end + + test "update_all with infinity on a datetime column" do + record = PostgresqlInfinity.create! + PostgresqlInfinity.update_all(datetime: Float::INFINITY) + record.reload + assert_equal Float::INFINITY, record.datetime + end +end diff --git a/activerecord/test/cases/adapters/postgresql/json_test.rb b/activerecord/test/cases/adapters/postgresql/json_test.rb new file mode 100644 index 0000000000..86ba849445 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/json_test.rb @@ -0,0 +1,193 @@ +# encoding: utf-8 + +require "cases/helper" +require 'active_record/base' +require 'active_record/connection_adapters/postgresql_adapter' + +module PostgresqlJSONSharedTestCases + class JsonDataType < ActiveRecord::Base + self.table_name = 'json_data_type' + + store_accessor :settings, :resolution + end + + def setup + @connection = ActiveRecord::Base.connection + begin + @connection.transaction do + @connection.create_table('json_data_type') do |t| + t.public_send column_type, 'payload', default: {} # t.json 'payload', default: {} + t.public_send column_type, 'settings' # t.json 'settings' + end + end + rescue ActiveRecord::StatementInvalid + skip "do not test on PG without json" + end + @column = JsonDataType.columns_hash['payload'] + end + + def teardown + @connection.execute 'drop table if exists json_data_type' + end + + def test_column + column = JsonDataType.columns_hash["payload"] + assert_equal column_type, column.type + assert_equal column_type.to_s, column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_default + @connection.add_column 'json_data_type', 'permissions', column_type, default: '{"users": "read", "posts": ["read", "write"]}' + JsonDataType.reset_column_information + + assert_equal({"users"=>"read", "posts"=>["read", "write"]}, JsonDataType.column_defaults['permissions']) + assert_equal({"users"=>"read", "posts"=>["read", "write"]}, JsonDataType.new.permissions) + ensure + JsonDataType.reset_column_information + end + + def test_change_table_supports_json + @connection.transaction do + @connection.change_table('json_data_type') do |t| + t.public_send column_type, 'users', default: '{}' # t.json 'users', default: '{}' + end + JsonDataType.reset_column_information + column = JsonDataType.columns_hash['users'] + assert_equal column_type, column.type + + raise ActiveRecord::Rollback # reset the schema change + end + ensure + JsonDataType.reset_column_information + end + + def test_cast_value_on_write + x = JsonDataType.new payload: {"string" => "foo", :symbol => :bar} + assert_equal({"string" => "foo", :symbol => :bar}, x.payload_before_type_cast) + assert_equal({"string" => "foo", "symbol" => "bar"}, x.payload) + x.save + assert_equal({"string" => "foo", "symbol" => "bar"}, x.reload.payload) + end + + def test_type_cast_json + column = JsonDataType.columns_hash["payload"] + + data = "{\"a_key\":\"a_value\"}" + hash = column.type_cast_from_database(data) + assert_equal({'a_key' => 'a_value'}, hash) + assert_equal({'a_key' => 'a_value'}, column.type_cast_from_database(data)) + + assert_equal({}, column.type_cast_from_database("{}")) + assert_equal({'key'=>nil}, column.type_cast_from_database('{"key": null}')) + assert_equal({'c'=>'}','"a"'=>'b "a b'}, column.type_cast_from_database(%q({"c":"}", "\"a\"":"b \"a b"}))) + end + + def test_rewrite + @connection.execute "insert into json_data_type (payload) VALUES ('{\"k\":\"v\"}')" + x = JsonDataType.first + x.payload = { '"a\'' => 'b' } + assert x.save! + end + + def test_select + @connection.execute "insert into json_data_type (payload) VALUES ('{\"k\":\"v\"}')" + x = JsonDataType.first + assert_equal({'k' => 'v'}, x.payload) + end + + def test_select_multikey + @connection.execute %q|insert into json_data_type (payload) VALUES ('{"k1":"v1", "k2":"v2", "k3":[1,2,3]}')| + x = JsonDataType.first + assert_equal({'k1' => 'v1', 'k2' => 'v2', 'k3' => [1,2,3]}, x.payload) + end + + def test_null_json + @connection.execute %q|insert into json_data_type (payload) VALUES(null)| + x = JsonDataType.first + assert_equal(nil, x.payload) + end + + def test_select_array_json_value + @connection.execute %q|insert into json_data_type (payload) VALUES ('["v0",{"k1":"v1"}]')| + x = JsonDataType.first + assert_equal(['v0', {'k1' => 'v1'}], x.payload) + end + + def test_rewrite_array_json_value + @connection.execute %q|insert into json_data_type (payload) VALUES ('["v0",{"k1":"v1"}]')| + x = JsonDataType.first + x.payload = ['v1', {'k2' => 'v2'}, 'v3'] + assert x.save! + end + + def test_with_store_accessors + x = JsonDataType.new(resolution: "320×480") + assert_equal "320×480", x.resolution + + x.save! + x = JsonDataType.first + assert_equal "320×480", x.resolution + + x.resolution = "640×1136" + x.save! + + x = JsonDataType.first + assert_equal "640×1136", x.resolution + end + + def test_duplication_with_store_accessors + x = JsonDataType.new(resolution: "320×480") + assert_equal "320×480", x.resolution + + y = x.dup + assert_equal "320×480", y.resolution + end + + def test_yaml_round_trip_with_store_accessors + x = JsonDataType.new(resolution: "320×480") + assert_equal "320×480", x.resolution + + y = YAML.load(YAML.dump(x)) + assert_equal "320×480", y.resolution + end + + def test_changes_in_place + json = JsonDataType.new + assert_not json.changed? + + json.payload = { 'one' => 'two' } + assert json.changed? + assert json.payload_changed? + + json.save! + assert_not json.changed? + + json.payload['three'] = 'four' + assert json.payload_changed? + + json.save! + json.reload + + assert_equal({ 'one' => 'two', 'three' => 'four' }, json.payload) + assert_not json.changed? + end +end + +class PostgresqlJSONTest < ActiveRecord::TestCase + include PostgresqlJSONSharedTestCases + + def column_type + :json + end +end + +class PostgresqlJSONBTest < ActiveRecord::TestCase + include PostgresqlJSONSharedTestCases + + def column_type + :jsonb + end +end diff --git a/activerecord/test/cases/adapters/postgresql/ltree_test.rb b/activerecord/test/cases/adapters/postgresql/ltree_test.rb new file mode 100644 index 0000000000..889e369bd6 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/ltree_test.rb @@ -0,0 +1,48 @@ +# encoding: utf-8 +require "cases/helper" + +class PostgresqlLtreeTest < ActiveRecord::TestCase + class Ltree < ActiveRecord::Base + self.table_name = 'ltrees' + end + + def setup + @connection = ActiveRecord::Base.connection + + unless @connection.extension_enabled?('ltree') + @connection.enable_extension 'ltree' + end + + @connection.transaction do + @connection.create_table('ltrees') do |t| + t.ltree 'path' + end + end + rescue ActiveRecord::StatementInvalid + skip "do not test on PG without ltree" + end + + teardown do + @connection.execute 'drop table if exists ltrees' + end + + def test_column + column = Ltree.columns_hash['path'] + assert_equal :ltree, column.type + assert_equal "ltree", column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_write + ltree = Ltree.new(path: '1.2.3.4') + assert ltree.save! + end + + def test_select + @connection.execute "insert into ltrees (path) VALUES ('1.2.3')" + ltree = Ltree.first + assert_equal '1.2.3', ltree.path + end +end diff --git a/activerecord/test/cases/adapters/postgresql/money_test.rb b/activerecord/test/cases/adapters/postgresql/money_test.rb new file mode 100644 index 0000000000..87183174f2 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/money_test.rb @@ -0,0 +1,96 @@ +# encoding: utf-8 +require "cases/helper" +require 'support/schema_dumping_helper' + +class PostgresqlMoneyTest < ActiveRecord::TestCase + include SchemaDumpingHelper + + class PostgresqlMoney < ActiveRecord::Base; end + + setup do + @connection = ActiveRecord::Base.connection + @connection.execute("set lc_monetary = 'C'") + @connection.create_table('postgresql_moneys') do |t| + t.column "wealth", "money" + t.column "depth", "money", default: "150.55" + end + end + + teardown do + @connection.execute 'DROP TABLE IF EXISTS postgresql_moneys' + end + + def test_column + column = PostgresqlMoney.columns_hash["wealth"] + assert_equal :money, column.type + assert_equal "money", column.sql_type + assert_equal 2, column.scale + assert column.number? + assert_not column.binary? + assert_not column.array + end + + def test_default + assert_equal BigDecimal.new("150.55"), PostgresqlMoney.column_defaults['depth'] + assert_equal BigDecimal.new("150.55"), PostgresqlMoney.new.depth + end + + def test_money_values + @connection.execute("INSERT INTO postgresql_moneys (id, wealth) VALUES (1, '567.89'::money)") + @connection.execute("INSERT INTO postgresql_moneys (id, wealth) VALUES (2, '-567.89'::money)") + + first_money = PostgresqlMoney.find(1) + second_money = PostgresqlMoney.find(2) + assert_equal 567.89, first_money.wealth + assert_equal(-567.89, second_money.wealth) + end + + def test_money_type_cast + column = PostgresqlMoney.columns_hash['wealth'] + assert_equal(12345678.12, column.type_cast_from_user("$12,345,678.12")) + assert_equal(12345678.12, column.type_cast_from_user("$12.345.678,12")) + assert_equal(-1.15, column.type_cast_from_user("-$1.15")) + assert_equal(-2.25, column.type_cast_from_user("($2.25)")) + end + + def test_schema_dumping + output = dump_table_schema("postgresql_moneys") + assert_match %r{t\.money\s+"wealth",\s+scale: 2$}, output + assert_match %r{t\.money\s+"depth",\s+scale: 2,\s+default: 150.55$}, output + end + + def test_create_and_update_money + money = PostgresqlMoney.create(wealth: "987.65") + assert_equal 987.65, money.wealth + + new_value = BigDecimal.new('123.45') + money.wealth = new_value + money.save! + money.reload + assert_equal new_value, money.wealth + end + + def test_update_all_with_money_string + money = PostgresqlMoney.create! + PostgresqlMoney.update_all(wealth: "987.65") + money.reload + + assert_equal 987.65, money.wealth + end + + def test_update_all_with_money_big_decimal + money = PostgresqlMoney.create! + PostgresqlMoney.update_all(wealth: '123.45'.to_d) + money.reload + + assert_equal 123.45, money.wealth + end + + def test_update_all_with_money_numeric + money = PostgresqlMoney.create! + PostgresqlMoney.update_all(wealth: 123.45) + money.reload + + assert_equal 123.45, money.wealth + end +end diff --git a/activerecord/test/cases/adapters/postgresql/network_test.rb b/activerecord/test/cases/adapters/postgresql/network_test.rb new file mode 100644 index 0000000000..4f4c1103fa --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/network_test.rb @@ -0,0 +1,71 @@ +# encoding: utf-8 +require "cases/helper" + +class PostgresqlNetworkTest < ActiveRecord::TestCase + class PostgresqlNetworkAddress < ActiveRecord::Base + end + + def test_cidr_column + column = PostgresqlNetworkAddress.columns_hash["cidr_address"] + assert_equal :cidr, column.type + assert_equal "cidr", column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_inet_column + column = PostgresqlNetworkAddress.columns_hash["inet_address"] + assert_equal :inet, column.type + assert_equal "inet", column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_macaddr_column + column = PostgresqlNetworkAddress.columns_hash["mac_address"] + assert_equal :macaddr, column.type + assert_equal "macaddr", column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_network_types + PostgresqlNetworkAddress.create(cidr_address: '192.168.0.0/24', + inet_address: '172.16.1.254/32', + mac_address: '01:23:45:67:89:0a') + + address = PostgresqlNetworkAddress.first + assert_equal IPAddr.new('192.168.0.0/24'), address.cidr_address + assert_equal IPAddr.new('172.16.1.254'), address.inet_address + assert_equal '01:23:45:67:89:0a', address.mac_address + + address.cidr_address = '10.1.2.3/32' + address.inet_address = '10.0.0.0/8' + address.mac_address = 'bc:de:f0:12:34:56' + + address.save! + assert address.reload + assert_equal IPAddr.new('10.1.2.3/32'), address.cidr_address + assert_equal IPAddr.new('10.0.0.0/8'), address.inet_address + assert_equal 'bc:de:f0:12:34:56', address.mac_address + end + + def test_invalid_network_address + invalid_address = PostgresqlNetworkAddress.new(cidr_address: 'invalid addr', + inet_address: 'invalid addr') + assert_nil invalid_address.cidr_address + assert_nil invalid_address.inet_address + assert_equal 'invalid addr', invalid_address.cidr_address_before_type_cast + assert_equal 'invalid addr', invalid_address.inet_address_before_type_cast + assert invalid_address.save + + invalid_address.reload + assert_nil invalid_address.cidr_address + assert_nil invalid_address.inet_address + assert_nil invalid_address.cidr_address_before_type_cast + assert_nil invalid_address.inet_address_before_type_cast + end +end diff --git a/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb b/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb new file mode 100644 index 0000000000..cfff1f980b --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb @@ -0,0 +1,451 @@ +# encoding: utf-8 +require "cases/helper" +require 'support/ddl_helper' +require 'support/connection_helper' + +module ActiveRecord + module ConnectionAdapters + class PostgreSQLAdapterTest < ActiveRecord::TestCase + include DdlHelper + include ConnectionHelper + + def setup + @connection = ActiveRecord::Base.connection + end + + def test_bad_connection + assert_raise ActiveRecord::NoDatabaseError do + configuration = ActiveRecord::Base.configurations['arunit'].merge(database: 'should_not_exist-cinco-dog-db') + connection = ActiveRecord::Base.postgresql_connection(configuration) + connection.exec_query('SELECT 1') + end + end + + def test_valid_column + with_example_table do + column = @connection.columns('ex').find { |col| col.name == 'id' } + assert @connection.valid_type?(column.type) + end + end + + def test_invalid_column + assert_not @connection.valid_type?(:foobar) + end + + def test_primary_key + with_example_table do + assert_equal 'id', @connection.primary_key('ex') + end + end + + def test_primary_key_works_tables_containing_capital_letters + assert_equal 'id', @connection.primary_key('CamelCase') + end + + def test_non_standard_primary_key + with_example_table 'data character varying(255) primary key' do + assert_equal 'data', @connection.primary_key('ex') + end + end + + def test_primary_key_returns_nil_for_no_pk + with_example_table 'id integer' do + assert_nil @connection.primary_key('ex') + end + end + + def test_primary_key_raises_error_if_table_not_found + assert_raises(ActiveRecord::StatementInvalid) do + @connection.primary_key('unobtainium') + end + end + + def test_insert_sql_with_proprietary_returning_clause + with_example_table do + id = @connection.insert_sql("insert into ex (number) values(5150)", nil, "number") + assert_equal "5150", id + end + end + + def test_insert_sql_with_quoted_schema_and_table_name + with_example_table do + id = @connection.insert_sql('insert into "public"."ex" (number) values(5150)') + expect = @connection.query('select max(id) from ex').first.first + assert_equal expect, id + end + end + + def test_insert_sql_with_no_space_after_table_name + with_example_table do + id = @connection.insert_sql("insert into ex(number) values(5150)") + expect = @connection.query('select max(id) from ex').first.first + assert_equal expect, id + end + end + + def test_multiline_insert_sql + with_example_table do + id = @connection.insert_sql(<<-SQL) + insert into ex( + number) + values( + 5152 + ) + SQL + expect = @connection.query('select max(id) from ex').first.first + assert_equal expect, id + end + end + + def test_insert_sql_with_returning_disabled + connection = connection_without_insert_returning + id = connection.insert_sql("insert into postgresql_partitioned_table_parent (number) VALUES (1)") + expect = connection.query('select max(id) from postgresql_partitioned_table_parent').first.first + assert_equal expect, id + end + + def test_exec_insert_with_returning_disabled + connection = connection_without_insert_returning + result = connection.exec_insert("insert into postgresql_partitioned_table_parent (number) VALUES (1)", nil, [], 'id', 'postgresql_partitioned_table_parent_id_seq') + expect = connection.query('select max(id) from postgresql_partitioned_table_parent').first.first + assert_equal expect, result.rows.first.first + end + + def test_exec_insert_with_returning_disabled_and_no_sequence_name_given + connection = connection_without_insert_returning + result = connection.exec_insert("insert into postgresql_partitioned_table_parent (number) VALUES (1)", nil, [], 'id') + expect = connection.query('select max(id) from postgresql_partitioned_table_parent').first.first + assert_equal expect, result.rows.first.first + end + + def test_sql_for_insert_with_returning_disabled + connection = connection_without_insert_returning + result = connection.sql_for_insert('sql', nil, nil, nil, 'binds') + assert_equal ['sql', 'binds'], result + end + + def test_serial_sequence + assert_equal 'public.accounts_id_seq', + @connection.serial_sequence('accounts', 'id') + + assert_raises(ActiveRecord::StatementInvalid) do + @connection.serial_sequence('zomg', 'id') + end + end + + def test_default_sequence_name + assert_equal PostgreSQL::Name.new('public', 'accounts_id_seq'), + @connection.default_sequence_name('accounts', 'id') + + assert_equal PostgreSQL::Name.new('public', 'accounts_id_seq'), + @connection.default_sequence_name('accounts') + end + + def test_default_sequence_name_bad_table + assert_equal PostgreSQL::Name.new(nil, 'zomg_id_seq'), + @connection.default_sequence_name('zomg', 'id') + + assert_equal PostgreSQL::Name.new(nil, 'zomg_id_seq'), + @connection.default_sequence_name('zomg') + end + + def test_pk_and_sequence_for + with_example_table do + pk, seq = @connection.pk_and_sequence_for('ex') + assert_equal 'id', pk + assert_equal @connection.default_sequence_name('ex', 'id'), seq + end + end + + def test_pk_and_sequence_for_with_non_standard_primary_key + with_example_table 'code serial primary key' do + pk, seq = @connection.pk_and_sequence_for('ex') + assert_equal 'code', pk + assert_equal @connection.default_sequence_name('ex', 'code'), seq + end + end + + def test_pk_and_sequence_for_returns_nil_if_no_seq + with_example_table 'id integer primary key' do + assert_nil @connection.pk_and_sequence_for('ex') + end + end + + def test_pk_and_sequence_for_returns_nil_if_no_pk + with_example_table 'id integer' do + assert_nil @connection.pk_and_sequence_for('ex') + end + end + + def test_pk_and_sequence_for_returns_nil_if_table_not_found + assert_nil @connection.pk_and_sequence_for('unobtainium') + end + + def test_pk_and_sequence_for_with_collision_pg_class_oid + @connection.exec_query('create table ex(id serial primary key)') + @connection.exec_query('create table ex2(id serial primary key)') + + correct_depend_record = [ + "'pg_class'::regclass", + "'ex_id_seq'::regclass", + '0', + "'pg_class'::regclass", + "'ex'::regclass", + '1', + "'a'" + ] + + collision_depend_record = [ + "'pg_attrdef'::regclass", + "'ex2_id_seq'::regclass", + '0', + "'pg_class'::regclass", + "'ex'::regclass", + '1', + "'a'" + ] + + @connection.exec_query( + "DELETE FROM pg_depend WHERE objid = 'ex_id_seq'::regclass AND refobjid = 'ex'::regclass AND deptype = 'a'" + ) + @connection.exec_query( + "INSERT INTO pg_depend VALUES(#{collision_depend_record.join(',')})" + ) + @connection.exec_query( + "INSERT INTO pg_depend VALUES(#{correct_depend_record.join(',')})" + ) + + seq = @connection.pk_and_sequence_for('ex').last + assert_equal PostgreSQL::Name.new("public", "ex_id_seq"), seq + + @connection.exec_query( + "DELETE FROM pg_depend WHERE objid = 'ex2_id_seq'::regclass AND refobjid = 'ex'::regclass AND deptype = 'a'" + ) + ensure + @connection.exec_query('DROP TABLE IF EXISTS ex') + @connection.exec_query('DROP TABLE IF EXISTS ex2') + end + + def test_exec_insert_number + with_example_table do + insert(@connection, 'number' => 10) + + result = @connection.exec_query('SELECT number FROM ex WHERE number = 10') + + assert_equal 1, result.rows.length + assert_equal "10", result.rows.last.last + end + end + + def test_exec_insert_string + with_example_table do + str = 'いただきます！' + insert(@connection, 'number' => 10, 'data' => str) + + result = @connection.exec_query('SELECT number, data FROM ex WHERE number = 10') + + value = result.rows.last.last + + assert_equal str, value + end + end + + def test_table_alias_length + assert_nothing_raised do + @connection.table_alias_length + end + end + + def test_exec_no_binds + with_example_table do + result = @connection.exec_query('SELECT id, data FROM ex') + assert_equal 0, result.rows.length + assert_equal 2, result.columns.length + assert_equal %w{ id data }, result.columns + + string = @connection.quote('foo') + @connection.exec_query("INSERT INTO ex (id, data) VALUES (1, #{string})") + result = @connection.exec_query('SELECT id, data FROM ex') + assert_equal 1, result.rows.length + assert_equal 2, result.columns.length + + assert_equal [['1', 'foo']], result.rows + end + end + + def test_exec_with_binds + with_example_table do + string = @connection.quote('foo') + @connection.exec_query("INSERT INTO ex (id, data) VALUES (1, #{string})") + result = @connection.exec_query( + 'SELECT id, data FROM ex WHERE id = $1', nil, [[nil, 1]]) + + assert_equal 1, result.rows.length + assert_equal 2, result.columns.length + + assert_equal [['1', 'foo']], result.rows + end + end + + def test_exec_typecasts_bind_vals + with_example_table do + string = @connection.quote('foo') + @connection.exec_query("INSERT INTO ex (id, data) VALUES (1, #{string})") + + column = @connection.columns('ex').find { |col| col.name == 'id' } + result = @connection.exec_query( + 'SELECT id, data FROM ex WHERE id = $1', nil, [[column, '1-fuu']]) + + assert_equal 1, result.rows.length + assert_equal 2, result.columns.length + + assert_equal [['1', 'foo']], result.rows + end + end + + def test_substitute_at + bind = @connection.substitute_at(nil, 0) + assert_equal Arel.sql('$1'), bind + + bind = @connection.substitute_at(nil, 1) + assert_equal Arel.sql('$2'), bind + end + + def test_partial_index + with_example_table do + @connection.add_index 'ex', %w{ id number }, :name => 'partial', :where => "number > 100" + index = @connection.indexes('ex').find { |idx| idx.name == 'partial' } + assert_equal "(number > 100)", index.where + end + end + + def test_columns_for_distinct_zero_orders + assert_equal "posts.id", + @connection.columns_for_distinct("posts.id", []) + end + + def test_columns_for_distinct_one_order + assert_equal "posts.id, posts.created_at AS alias_0", + @connection.columns_for_distinct("posts.id", ["posts.created_at desc"]) + end + + def test_columns_for_distinct_few_orders + assert_equal "posts.id, posts.created_at AS alias_0, posts.position AS alias_1", + @connection.columns_for_distinct("posts.id", ["posts.created_at desc", "posts.position asc"]) + end + + def test_columns_for_distinct_blank_not_nil_orders + assert_equal "posts.id, posts.created_at AS alias_0", + @connection.columns_for_distinct("posts.id", ["posts.created_at desc", "", " "]) + end + + def test_columns_for_distinct_with_arel_order + order = Object.new + def order.to_sql + "posts.created_at desc" + end + assert_equal "posts.id, posts.created_at AS alias_0", + @connection.columns_for_distinct("posts.id", [order]) + end + + def test_columns_for_distinct_with_nulls + assert_equal "posts.title, posts.updater_id AS alias_0", @connection.columns_for_distinct("posts.title", ["posts.updater_id desc nulls first"]) + assert_equal "posts.title, posts.updater_id AS alias_0", @connection.columns_for_distinct("posts.title", ["posts.updater_id desc nulls last"]) + end + + def test_columns_for_distinct_without_order_specifiers + assert_equal "posts.title, posts.updater_id AS alias_0", + @connection.columns_for_distinct("posts.title", ["posts.updater_id"]) + + assert_equal "posts.title, posts.updater_id AS alias_0", + @connection.columns_for_distinct("posts.title", ["posts.updater_id nulls last"]) + + assert_equal "posts.title, posts.updater_id AS alias_0", + @connection.columns_for_distinct("posts.title", ["posts.updater_id nulls first"]) + end + + def test_raise_error_when_cannot_translate_exception + assert_raise TypeError do + @connection.send(:log, nil) { @connection.execute(nil) } + end + end + + def test_reload_type_map_for_newly_defined_types + @connection.execute "CREATE TYPE feeling AS ENUM ('good', 'bad')" + result = @connection.select_all "SELECT 'good'::feeling" + assert_instance_of(PostgreSQLAdapter::OID::Enum, + result.column_types["feeling"]) + ensure + @connection.execute "DROP TYPE IF EXISTS feeling" + reset_connection + end + + def test_only_reload_type_map_once_for_every_unknown_type + silence_warnings do + assert_queries 2, ignore_none: true do + @connection.select_all "SELECT NULL::anyelement" + end + assert_queries 1, ignore_none: true do + @connection.select_all "SELECT NULL::anyelement" + end + assert_queries 2, ignore_none: true do + @connection.select_all "SELECT NULL::anyarray" + end + end + ensure + reset_connection + end + + def test_only_warn_on_first_encounter_of_unknown_oid + warning = capture(:stderr) { + @connection.select_all "SELECT NULL::anyelement" + @connection.select_all "SELECT NULL::anyelement" + @connection.select_all "SELECT NULL::anyelement" + } + assert_match(/\Aunknown OID \d+: failed to recognize type of 'anyelement'. It will be treated as String.\n\z/, warning) + ensure + reset_connection + end + + def test_unparsed_defaults_are_at_least_set_when_saving + with_example_table "id SERIAL PRIMARY KEY, number INTEGER NOT NULL DEFAULT (4 + 4) * 2 / 4" do + number_klass = Class.new(ActiveRecord::Base) do + self.table_name = 'ex' + end + column = number_klass.columns_hash["number"] + assert_nil column.default + assert_nil column.default_function + + first_number = number_klass.new + assert_nil first_number.number + + first_number.save! + assert_equal 4, first_number.reload.number + end + end + + private + def insert(ctx, data) + binds = data.map { |name, value| + [ctx.columns('ex').find { |x| x.name == name }, value] + } + columns = binds.map(&:first).map(&:name) + + bind_subs = columns.length.times.map { |x| "$#{x + 1}" } + + sql = "INSERT INTO ex (#{columns.join(", ")}) + VALUES (#{bind_subs.join(', ')})" + + ctx.exec_insert(sql, 'SQL', binds) + end + + def with_example_table(definition = 'id serial primary key, number integer, data character varying(255)', &block) + super(@connection, 'ex', definition, &block) + end + + def connection_without_insert_returning + ActiveRecord::Base.postgresql_connection(ActiveRecord::Base.configurations['arunit'].merge(:insert_returning => false)) + end + end + end +end diff --git a/activerecord/test/cases/adapters/postgresql/quoting_test.rb b/activerecord/test/cases/adapters/postgresql/quoting_test.rb new file mode 100644 index 0000000000..11d5173d37 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/quoting_test.rb @@ -0,0 +1,74 @@ +require "cases/helper" +require 'ipaddr' + +module ActiveRecord + module ConnectionAdapters + class PostgreSQLAdapter + class QuotingTest < ActiveRecord::TestCase + def setup + @conn = ActiveRecord::Base.connection + end + + def test_type_cast_true + c = PostgreSQLColumn.new(nil, 1, Type::Boolean.new, 'boolean') + assert_equal 't', @conn.type_cast(true, nil) + assert_equal 't', @conn.type_cast(true, c) + end + + def test_type_cast_false + c = PostgreSQLColumn.new(nil, 1, Type::Boolean.new, 'boolean') + assert_equal 'f', @conn.type_cast(false, nil) + assert_equal 'f', @conn.type_cast(false, c) + end + + def test_type_cast_cidr + ip = IPAddr.new('255.0.0.0/8') + c = PostgreSQLColumn.new(nil, ip, OID::Cidr.new, 'cidr') + assert_equal ip, @conn.type_cast(ip, c) + end + + def test_type_cast_inet + ip = IPAddr.new('255.1.0.0/8') + c = PostgreSQLColumn.new(nil, ip, OID::Cidr.new, 'inet') + assert_equal ip, @conn.type_cast(ip, c) + end + + def test_quote_float_nan + nan = 0.0/0 + c = PostgreSQLColumn.new(nil, 1, OID::Float.new, 'float') + assert_equal "'NaN'", @conn.quote(nan, c) + end + + def test_quote_float_infinity + infinity = 1.0/0 + c = PostgreSQLColumn.new(nil, 1, OID::Float.new, 'float') + assert_equal "'Infinity'", @conn.quote(infinity, c) + end + + def test_quote_cast_numeric + fixnum = 666 + c = PostgreSQLColumn.new(nil, nil, Type::String.new, 'varchar') + assert_equal "'666'", @conn.quote(fixnum, c) + c = PostgreSQLColumn.new(nil, nil, Type::Text.new, 'text') + assert_equal "'666'", @conn.quote(fixnum, c) + end + + def test_quote_time_usec + assert_equal "'1970-01-01 00:00:00.000000'", @conn.quote(Time.at(0)) + assert_equal "'1970-01-01 00:00:00.000000'", @conn.quote(Time.at(0).to_datetime) + end + + def test_quote_range + range = "1,2]'; SELECT * FROM users; --".."a" + c = PostgreSQLColumn.new(nil, nil, OID::Range.new(Type::Integer.new, :int8range)) + assert_equal "'[1,0]'", @conn.quote(range, c) + end + + def test_quote_bit_string + c = PostgreSQLColumn.new(nil, 1, OID::Bit.new) + assert_equal nil, @conn.quote("'); SELECT * FROM users; /*\n01\n*/--", c) + end + end + end + end +end diff --git a/activerecord/test/cases/adapters/postgresql/range_test.rb b/activerecord/test/cases/adapters/postgresql/range_test.rb new file mode 100644 index 0000000000..d812cd01c4 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/range_test.rb @@ -0,0 +1,323 @@ +require "cases/helper" +require 'support/connection_helper' + +if ActiveRecord::Base.connection.supports_ranges? + class PostgresqlRange < ActiveRecord::Base + self.table_name = "postgresql_ranges" + end + + class PostgresqlRangeTest < ActiveRecord::TestCase + self.use_transactional_fixtures = false + include ConnectionHelper + + def setup + @connection = PostgresqlRange.connection + begin + @connection.transaction do + @connection.execute <<_SQL + CREATE TYPE floatrange AS RANGE ( + subtype = float8, + subtype_diff = float8mi + ); +_SQL + + @connection.create_table('postgresql_ranges') do |t| + t.daterange :date_range + t.numrange :num_range + t.tsrange :ts_range + t.tstzrange :tstz_range + t.int4range :int4_range + t.int8range :int8_range + end + + @connection.add_column 'postgresql_ranges', 'float_range', 'floatrange' + end + PostgresqlRange.reset_column_information + rescue ActiveRecord::StatementInvalid + skip "do not test on PG without range" + end + + insert_range(id: 101, + date_range: "[''2012-01-02'', ''2012-01-04'']", + num_range: "[0.1, 0.2]", + ts_range: "[''2010-01-01 14:30'', ''2011-01-01 14:30'']", + tstz_range: "[''2010-01-01 14:30:00+05'', ''2011-01-01 14:30:00-03'']", + int4_range: "[1, 10]", + int8_range: "[10, 100]", + float_range: "[0.5, 0.7]") + + insert_range(id: 102, + date_range: "[''2012-01-02'', ''2012-01-04'')", + num_range: "[0.1, 0.2)", + ts_range: "[''2010-01-01 14:30'', ''2011-01-01 14:30'')", + tstz_range: "[''2010-01-01 14:30:00+05'', ''2011-01-01 14:30:00-03'')", + int4_range: "[1, 10)", + int8_range: "[10, 100)", + float_range: "[0.5, 0.7)") + + insert_range(id: 103, + date_range: "[''2012-01-02'',]", + num_range: "[0.1,]", + ts_range: "[''2010-01-01 14:30'',]", + tstz_range: "[''2010-01-01 14:30:00+05'',]", + int4_range: "[1,]", + int8_range: "[10,]", + float_range: "[0.5,]") + + insert_range(id: 104, + date_range: "[,]", + num_range: "[,]", + ts_range: "[,]", + tstz_range: "[,]", + int4_range: "[,]", + int8_range: "[,]", + float_range: "[,]") + + insert_range(id: 105, + date_range: "[''2012-01-02'', ''2012-01-02'')", + num_range: "[0.1, 0.1)", + ts_range: "[''2010-01-01 14:30'', ''2010-01-01 14:30'')", + tstz_range: "[''2010-01-01 14:30:00+05'', ''2010-01-01 06:30:00-03'')", + int4_range: "[1, 1)", + int8_range: "[10, 10)", + float_range: "[0.5, 0.5)") + + @new_range = PostgresqlRange.new + @first_range = PostgresqlRange.find(101) + @second_range = PostgresqlRange.find(102) + @third_range = PostgresqlRange.find(103) + @fourth_range = PostgresqlRange.find(104) + @empty_range = PostgresqlRange.find(105) + end + + teardown do + @connection.execute 'DROP TABLE IF EXISTS postgresql_ranges' + @connection.execute 'DROP TYPE IF EXISTS floatrange' + reset_connection + end + + def test_data_type_of_range_types + assert_equal :daterange, @first_range.column_for_attribute(:date_range).type + assert_equal :numrange, @first_range.column_for_attribute(:num_range).type + assert_equal :tsrange, @first_range.column_for_attribute(:ts_range).type + assert_equal :tstzrange, @first_range.column_for_attribute(:tstz_range).type + assert_equal :int4range, @first_range.column_for_attribute(:int4_range).type + assert_equal :int8range, @first_range.column_for_attribute(:int8_range).type + end + + def test_int4range_values + assert_equal 1...11, @first_range.int4_range + assert_equal 1...10, @second_range.int4_range + assert_equal 1...Float::INFINITY, @third_range.int4_range + assert_equal(-Float::INFINITY...Float::INFINITY, @fourth_range.int4_range) + assert_nil @empty_range.int4_range + end + + def test_int8range_values + assert_equal 10...101, @first_range.int8_range + assert_equal 10...100, @second_range.int8_range + assert_equal 10...Float::INFINITY, @third_range.int8_range + assert_equal(-Float::INFINITY...Float::INFINITY, @fourth_range.int8_range) + assert_nil @empty_range.int8_range + end + + def test_daterange_values + assert_equal Date.new(2012, 1, 2)...Date.new(2012, 1, 5), @first_range.date_range + assert_equal Date.new(2012, 1, 2)...Date.new(2012, 1, 4), @second_range.date_range + assert_equal Date.new(2012, 1, 2)...Float::INFINITY, @third_range.date_range + assert_equal(-Float::INFINITY...Float::INFINITY, @fourth_range.date_range) + assert_nil @empty_range.date_range + end + + def test_numrange_values + assert_equal BigDecimal.new('0.1')..BigDecimal.new('0.2'), @first_range.num_range + assert_equal BigDecimal.new('0.1')...BigDecimal.new('0.2'), @second_range.num_range + assert_equal BigDecimal.new('0.1')...BigDecimal.new('Infinity'), @third_range.num_range + assert_equal BigDecimal.new('-Infinity')...BigDecimal.new('Infinity'), @fourth_range.num_range + assert_nil @empty_range.num_range + end + + def test_tsrange_values + tz = ::ActiveRecord::Base.default_timezone + assert_equal Time.send(tz, 2010, 1, 1, 14, 30, 0)..Time.send(tz, 2011, 1, 1, 14, 30, 0), @first_range.ts_range + assert_equal Time.send(tz, 2010, 1, 1, 14, 30, 0)...Time.send(tz, 2011, 1, 1, 14, 30, 0), @second_range.ts_range + assert_equal(-Float::INFINITY...Float::INFINITY, @fourth_range.ts_range) + assert_nil @empty_range.ts_range + end + + def test_tstzrange_values + assert_equal Time.parse('2010-01-01 09:30:00 UTC')..Time.parse('2011-01-01 17:30:00 UTC'), @first_range.tstz_range + assert_equal Time.parse('2010-01-01 09:30:00 UTC')...Time.parse('2011-01-01 17:30:00 UTC'), @second_range.tstz_range + assert_equal(-Float::INFINITY...Float::INFINITY, @fourth_range.tstz_range) + assert_nil @empty_range.tstz_range + end + + def test_custom_range_values + assert_equal 0.5..0.7, @first_range.float_range + assert_equal 0.5...0.7, @second_range.float_range + assert_equal 0.5...Float::INFINITY, @third_range.float_range + assert_equal(-Float::INFINITY...Float::INFINITY, @fourth_range.float_range) + assert_nil @empty_range.float_range + end + + def test_create_tstzrange + tstzrange = Time.parse('2010-01-01 14:30:00 +0100')...Time.parse('2011-02-02 14:30:00 CDT') + round_trip(@new_range, :tstz_range, tstzrange) + assert_equal @new_range.tstz_range, tstzrange + assert_equal @new_range.tstz_range, Time.parse('2010-01-01 13:30:00 UTC')...Time.parse('2011-02-02 19:30:00 UTC') + end + + def test_update_tstzrange + assert_equal_round_trip(@first_range, :tstz_range, + Time.parse('2010-01-01 14:30:00 CDT')...Time.parse('2011-02-02 14:30:00 CET')) + assert_nil_round_trip(@first_range, :tstz_range, + Time.parse('2010-01-01 14:30:00 +0100')...Time.parse('2010-01-01 13:30:00 +0000')) + end + + def test_create_tsrange + tz = ::ActiveRecord::Base.default_timezone + assert_equal_round_trip(@new_range, :ts_range, + Time.send(tz, 2010, 1, 1, 14, 30, 0)...Time.send(tz, 2011, 2, 2, 14, 30, 0)) + end + + def test_update_tsrange + tz = ::ActiveRecord::Base.default_timezone + assert_equal_round_trip(@first_range, :ts_range, + Time.send(tz, 2010, 1, 1, 14, 30, 0)...Time.send(tz, 2011, 2, 2, 14, 30, 0)) + assert_nil_round_trip(@first_range, :ts_range, + Time.send(tz, 2010, 1, 1, 14, 30, 0)...Time.send(tz, 2010, 1, 1, 14, 30, 0)) + end + + def test_create_numrange + assert_equal_round_trip(@new_range, :num_range, + BigDecimal.new('0.5')...BigDecimal.new('1')) + end + + def test_update_numrange + assert_equal_round_trip(@first_range, :num_range, + BigDecimal.new('0.5')...BigDecimal.new('1')) + assert_nil_round_trip(@first_range, :num_range, + BigDecimal.new('0.5')...BigDecimal.new('0.5')) + end + + def test_create_daterange + assert_equal_round_trip(@new_range, :date_range, + Range.new(Date.new(2012, 1, 1), Date.new(2013, 1, 1), true)) + end + + def test_update_daterange + assert_equal_round_trip(@first_range, :date_range, + Date.new(2012, 2, 3)...Date.new(2012, 2, 10)) + assert_nil_round_trip(@first_range, :date_range, + Date.new(2012, 2, 3)...Date.new(2012, 2, 3)) + end + + def test_create_int4range + assert_equal_round_trip(@new_range, :int4_range, Range.new(3, 50, true)) + end + + def test_update_int4range + assert_equal_round_trip(@first_range, :int4_range, 6...10) + assert_nil_round_trip(@first_range, :int4_range, 3...3) + end + + def test_create_int8range + assert_equal_round_trip(@new_range, :int8_range, Range.new(30, 50, true)) + end + + def test_update_int8range + assert_equal_round_trip(@first_range, :int8_range, 60000...10000000) + assert_nil_round_trip(@first_range, :int8_range, 39999...39999) + end + + def test_exclude_beginning_for_subtypes_with_succ_method_is_deprecated + tz = ::ActiveRecord::Base.default_timezone + + silence_warnings { + assert_deprecated { + range = PostgresqlRange.create!(date_range: "(''2012-01-02'', ''2012-01-04'']") + assert_equal Date.new(2012, 1, 3)..Date.new(2012, 1, 4), range.date_range + } + assert_deprecated { + range = PostgresqlRange.create!(ts_range: "(''2010-01-01 14:30'', ''2011-01-01 14:30'']") + assert_equal Time.send(tz, 2010, 1, 1, 14, 30, 1)..Time.send(tz, 2011, 1, 1, 14, 30, 0), range.ts_range + } + assert_deprecated { + range = PostgresqlRange.create!(tstz_range: "(''2010-01-01 14:30:00+05'', ''2011-01-01 14:30:00-03'']") + assert_equal Time.parse('2010-01-01 09:30:01 UTC')..Time.parse('2011-01-01 17:30:00 UTC'), range.tstz_range + } + assert_deprecated { + range = PostgresqlRange.create!(int4_range: "(1, 10]") + assert_equal 2..10, range.int4_range + } + assert_deprecated { + range = PostgresqlRange.create!(int8_range: "(10, 100]") + assert_equal 11..100, range.int8_range + } + } + end + + def test_exclude_beginning_for_subtypes_without_succ_method_is_not_supported + assert_raises(ArgumentError) { PostgresqlRange.create!(num_range: "(0.1, 0.2]") } + assert_raises(ArgumentError) { PostgresqlRange.create!(float_range: "(0.5, 0.7]") } + end + + def test_update_all_with_ranges + PostgresqlRange.create! + + PostgresqlRange.update_all(int8_range: 1..100) + + assert_equal 1...101, PostgresqlRange.first.int8_range + end + + def test_ranges_correctly_escape_input + range = "-1,2]'; DROP TABLE postgresql_ranges; --".."a" + PostgresqlRange.update_all(int8_range: range) + + assert_nothing_raised do + PostgresqlRange.first + end + end + + private + def assert_equal_round_trip(range, attribute, value) + round_trip(range, attribute, value) + assert_equal value, range.public_send(attribute) + end + + def assert_nil_round_trip(range, attribute, value) + round_trip(range, attribute, value) + assert_nil range.public_send(attribute) + end + + def round_trip(range, attribute, value) + range.public_send "#{attribute}=", value + assert range.save + assert range.reload + end + + def insert_range(values) + @connection.execute <<-SQL + INSERT INTO postgresql_ranges ( + id, + date_range, + num_range, + ts_range, + tstz_range, + int4_range, + int8_range, + float_range + ) VALUES ( + #{values[:id]}, + '#{values[:date_range]}', + '#{values[:num_range]}', + '#{values[:ts_range]}', + '#{values[:tstz_range]}', + '#{values[:int4_range]}', + '#{values[:int8_range]}', + '#{values[:float_range]}' + ) + SQL + end + end +end diff --git a/activerecord/test/cases/adapters/postgresql/schema_authorization_test.rb b/activerecord/test/cases/adapters/postgresql/schema_authorization_test.rb new file mode 100644 index 0000000000..99c26c4bf7 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/schema_authorization_test.rb @@ -0,0 +1,114 @@ +require "cases/helper" + +class SchemaThing < ActiveRecord::Base +end + +class SchemaAuthorizationTest < ActiveRecord::TestCase + self.use_transactional_fixtures = false + + TABLE_NAME = 'schema_things' + COLUMNS = [ + 'id serial primary key', + 'name character varying(50)' + ] + USERS = ['rails_pg_schema_user1', 'rails_pg_schema_user2'] + + def setup + @connection = ActiveRecord::Base.connection + @connection.execute "SET search_path TO '$user',public" + set_session_auth + USERS.each do |u| + @connection.execute "CREATE USER #{u}" rescue nil + @connection.execute "CREATE SCHEMA AUTHORIZATION #{u}" rescue nil + set_session_auth u + @connection.execute "CREATE TABLE #{TABLE_NAME} (#{COLUMNS.join(',')})" + @connection.execute "INSERT INTO #{TABLE_NAME} (name) VALUES ('#{u}')" + set_session_auth + end + end + + teardown do + set_session_auth + @connection.execute "RESET search_path" + USERS.each do |u| + @connection.execute "DROP SCHEMA #{u} CASCADE" + @connection.execute "DROP USER #{u}" + end + end + + def test_schema_invisible + assert_raise(ActiveRecord::StatementInvalid) do + set_session_auth + @connection.execute "SELECT * FROM #{TABLE_NAME}" + end + end + + def test_session_auth= + assert_raise(ActiveRecord::StatementInvalid) do + @connection.session_auth = 'DEFAULT' + @connection.execute "SELECT * FROM #{TABLE_NAME}" + end + end + + def test_setting_auth_clears_stmt_cache + assert_nothing_raised do + set_session_auth + USERS.each do |u| + set_session_auth u + assert_equal u, @connection.exec_query("SELECT name FROM #{TABLE_NAME} WHERE id = $1", 'SQL', [[nil, 1]]).first['name'] + set_session_auth + end + end + end + + def test_auth_with_bind + assert_nothing_raised do + set_session_auth + USERS.each do |u| + @connection.clear_cache! + set_session_auth u + assert_equal u, @connection.exec_query("SELECT name FROM #{TABLE_NAME} WHERE id = $1", 'SQL', [[nil, 1]]).first['name'] + set_session_auth + end + end + end + + def test_schema_uniqueness + assert_nothing_raised do + set_session_auth + USERS.each do |u| + set_session_auth u + assert_equal u, @connection.select_value("SELECT name FROM #{TABLE_NAME} WHERE id = 1") + set_session_auth + end + end + end + + def test_sequence_schema_caching + assert_nothing_raised do + USERS.each do |u| + set_session_auth u + st = SchemaThing.new :name => 'TEST1' + st.save! + st = SchemaThing.new :id => 5, :name => 'TEST2' + st.save! + set_session_auth + end + end + end + + def test_tables_in_current_schemas + assert !@connection.tables.include?(TABLE_NAME) + USERS.each do |u| + set_session_auth u + assert @connection.tables.include?(TABLE_NAME) + set_session_auth + end + end + + private + def set_session_auth auth = nil + @connection.session_auth = auth || 'default' + end + +end diff --git a/activerecord/test/cases/adapters/postgresql/schema_test.rb b/activerecord/test/cases/adapters/postgresql/schema_test.rb new file mode 100644 index 0000000000..9e5fd17dc4 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/schema_test.rb @@ -0,0 +1,428 @@ +require "cases/helper" + +class SchemaTest < ActiveRecord::TestCase + self.use_transactional_fixtures = false + + SCHEMA_NAME = 'test_schema' + SCHEMA2_NAME = 'test_schema2' + TABLE_NAME = 'things' + CAPITALIZED_TABLE_NAME = 'Things' + INDEX_A_NAME = 'a_index_things_on_name' + INDEX_B_NAME = 'b_index_things_on_different_columns_in_each_schema' + INDEX_C_NAME = 'c_index_full_text_search' + INDEX_D_NAME = 'd_index_things_on_description_desc' + INDEX_E_NAME = 'e_index_things_on_name_vector' + INDEX_A_COLUMN = 'name' + INDEX_B_COLUMN_S1 = 'email' + INDEX_B_COLUMN_S2 = 'moment' + INDEX_C_COLUMN = %q{(to_tsvector('english', coalesce(things.name, '')))} + INDEX_D_COLUMN = 'description' + INDEX_E_COLUMN = 'name_vector' + COLUMNS = [ + 'id integer', + 'name character varying(50)', + 'email character varying(50)', + 'description character varying(100)', + 'name_vector tsvector', + 'moment timestamp without time zone default now()' + ] + PK_TABLE_NAME = 'table_with_pk' + UNMATCHED_SEQUENCE_NAME = 'unmatched_primary_key_default_value_seq' + UNMATCHED_PK_TABLE_NAME = 'table_with_unmatched_sequence_for_pk' + + class Thing1 < ActiveRecord::Base + self.table_name = "test_schema.things" + end + + class Thing2 < ActiveRecord::Base + self.table_name = "test_schema2.things" + end + + class Thing3 < ActiveRecord::Base + self.table_name = 'test_schema."things.table"' + end + + class Thing4 < ActiveRecord::Base + self.table_name = 'test_schema."Things"' + end + + class Thing5 < ActiveRecord::Base + self.table_name = 'things' + end + + class Song < ActiveRecord::Base + self.table_name = "music.songs" + has_and_belongs_to_many :albums + end + + class Album < ActiveRecord::Base + self.table_name = "music.albums" + has_and_belongs_to_many :songs + end + + def setup + @connection = ActiveRecord::Base.connection + @connection.execute "CREATE SCHEMA #{SCHEMA_NAME} CREATE TABLE #{TABLE_NAME} (#{COLUMNS.join(',')})" + @connection.execute "CREATE TABLE #{SCHEMA_NAME}.\"#{TABLE_NAME}.table\" (#{COLUMNS.join(',')})" + @connection.execute "CREATE TABLE #{SCHEMA_NAME}.\"#{CAPITALIZED_TABLE_NAME}\" (#{COLUMNS.join(',')})" + @connection.execute "CREATE SCHEMA #{SCHEMA2_NAME} CREATE TABLE #{TABLE_NAME} (#{COLUMNS.join(',')})" + @connection.execute "CREATE INDEX #{INDEX_A_NAME} ON #{SCHEMA_NAME}.#{TABLE_NAME} USING btree (#{INDEX_A_COLUMN});" + @connection.execute "CREATE INDEX #{INDEX_A_NAME} ON #{SCHEMA2_NAME}.#{TABLE_NAME} USING btree (#{INDEX_A_COLUMN});" + @connection.execute "CREATE INDEX #{INDEX_B_NAME} ON #{SCHEMA_NAME}.#{TABLE_NAME} USING btree (#{INDEX_B_COLUMN_S1});" + @connection.execute "CREATE INDEX #{INDEX_B_NAME} ON #{SCHEMA2_NAME}.#{TABLE_NAME} USING btree (#{INDEX_B_COLUMN_S2});" + @connection.execute "CREATE INDEX #{INDEX_C_NAME} ON #{SCHEMA_NAME}.#{TABLE_NAME} USING gin (#{INDEX_C_COLUMN});" + @connection.execute "CREATE INDEX #{INDEX_C_NAME} ON #{SCHEMA2_NAME}.#{TABLE_NAME} USING gin (#{INDEX_C_COLUMN});" + @connection.execute "CREATE INDEX #{INDEX_D_NAME} ON #{SCHEMA_NAME}.#{TABLE_NAME} USING btree (#{INDEX_D_COLUMN} DESC);" + @connection.execute "CREATE INDEX #{INDEX_D_NAME} ON #{SCHEMA2_NAME}.#{TABLE_NAME} USING btree (#{INDEX_D_COLUMN} DESC);" + @connection.execute "CREATE INDEX #{INDEX_E_NAME} ON #{SCHEMA_NAME}.#{TABLE_NAME} USING gin (#{INDEX_E_COLUMN});" + @connection.execute "CREATE INDEX #{INDEX_E_NAME} ON #{SCHEMA2_NAME}.#{TABLE_NAME} USING gin (#{INDEX_E_COLUMN});" + @connection.execute "CREATE TABLE #{SCHEMA_NAME}.#{PK_TABLE_NAME} (id serial primary key)" + @connection.execute "CREATE SEQUENCE #{SCHEMA_NAME}.#{UNMATCHED_SEQUENCE_NAME}" + @connection.execute "CREATE TABLE #{SCHEMA_NAME}.#{UNMATCHED_PK_TABLE_NAME} (id integer NOT NULL DEFAULT nextval('#{SCHEMA_NAME}.#{UNMATCHED_SEQUENCE_NAME}'::regclass), CONSTRAINT unmatched_pkey PRIMARY KEY (id))" + end + + teardown do + @connection.execute "DROP SCHEMA #{SCHEMA2_NAME} CASCADE" + @connection.execute "DROP SCHEMA #{SCHEMA_NAME} CASCADE" + end + + def test_schema_names + assert_equal ["public", "schema_1", "test_schema", "test_schema2"], @connection.schema_names + end + + def test_create_schema + begin + @connection.create_schema "test_schema3" + assert @connection.schema_names.include? "test_schema3" + ensure + @connection.drop_schema "test_schema3" + end + end + + def test_raise_create_schema_with_existing_schema + begin + @connection.create_schema "test_schema3" + assert_raises(ActiveRecord::StatementInvalid) do + @connection.create_schema "test_schema3" + end + ensure + @connection.drop_schema "test_schema3" + end + end + + def test_drop_schema + begin + @connection.create_schema "test_schema3" + ensure + @connection.drop_schema "test_schema3" + end + assert !@connection.schema_names.include?("test_schema3") + end + + def test_habtm_table_name_with_schema + ActiveRecord::Base.connection.execute <<-SQL + DROP SCHEMA IF EXISTS music CASCADE; + CREATE SCHEMA music; + CREATE TABLE music.albums (id serial primary key); + CREATE TABLE music.songs (id serial primary key); + CREATE TABLE music.albums_songs (album_id integer, song_id integer); + SQL + + song = Song.create + Album.create + assert_equal song, Song.includes(:albums).references(:albums).first + ensure + ActiveRecord::Base.connection.execute "DROP SCHEMA music CASCADE;" + end + + def test_raise_drop_schema_with_nonexisting_schema + assert_raises(ActiveRecord::StatementInvalid) do + @connection.drop_schema "test_schema3" + end + end + + def test_raise_wraped_exception_on_bad_prepare + assert_raises(ActiveRecord::StatementInvalid) do + @connection.exec_query "select * from developers where id = ?", 'sql', [[nil, 1]] + end + end + + def test_schema_change_with_prepared_stmt + altered = false + @connection.exec_query "select * from developers where id = $1", 'sql', [[nil, 1]] + @connection.exec_query "alter table developers add column zomg int", 'sql', [] + altered = true + @connection.exec_query "select * from developers where id = $1", 'sql', [[nil, 1]] + ensure + # We are not using DROP COLUMN IF EXISTS because that syntax is only + # supported by pg 9.X + @connection.exec_query("alter table developers drop column zomg", 'sql', []) if altered + end + + def test_table_exists? + [Thing1, Thing2, Thing3, Thing4].each do |klass| + name = klass.table_name + assert @connection.table_exists?(name), "'#{name}' table should exist" + end + end + + def test_table_exists_when_on_schema_search_path + with_schema_search_path(SCHEMA_NAME) do + assert(@connection.table_exists?(TABLE_NAME), "table should exist and be found") + end + end + + def test_table_exists_when_not_on_schema_search_path + with_schema_search_path('PUBLIC') do + assert(!@connection.table_exists?(TABLE_NAME), "table exists but should not be found") + end + end + + def test_table_exists_wrong_schema + assert(!@connection.table_exists?("foo.things"), "table should not exist") + end + + def test_table_exists_quoted_names + [ %("#{SCHEMA_NAME}"."#{TABLE_NAME}"), %(#{SCHEMA_NAME}."#{TABLE_NAME}"), %(#{SCHEMA_NAME}."#{TABLE_NAME}")].each do |given| + assert(@connection.table_exists?(given), "table should exist when specified as #{given}") + end + with_schema_search_path(SCHEMA_NAME) do + given = %("#{TABLE_NAME}") + assert(@connection.table_exists?(given), "table should exist when specified as #{given}") + end + end + + def test_table_exists_quoted_table + with_schema_search_path(SCHEMA_NAME) do + assert(@connection.table_exists?('"things.table"'), "table should exist") + end + end + + def test_with_schema_prefixed_table_name + assert_nothing_raised do + assert_equal COLUMNS, columns("#{SCHEMA_NAME}.#{TABLE_NAME}") + end + end + + def test_with_schema_prefixed_capitalized_table_name + assert_nothing_raised do + assert_equal COLUMNS, columns("#{SCHEMA_NAME}.#{CAPITALIZED_TABLE_NAME}") + end + end + + def test_with_schema_search_path + assert_nothing_raised do + with_schema_search_path(SCHEMA_NAME) do + assert_equal COLUMNS, columns(TABLE_NAME) + end + end + end + + def test_proper_encoding_of_table_name + assert_equal '"table_name"', @connection.quote_table_name('table_name') + assert_equal '"table.name"', @connection.quote_table_name('"table.name"') + assert_equal '"schema_name"."table_name"', @connection.quote_table_name('schema_name.table_name') + assert_equal '"schema_name"."table.name"', @connection.quote_table_name('schema_name."table.name"') + assert_equal '"schema.name"."table_name"', @connection.quote_table_name('"schema.name".table_name') + assert_equal '"schema.name"."table.name"', @connection.quote_table_name('"schema.name"."table.name"') + end + + def test_classes_with_qualified_schema_name + assert_equal 0, Thing1.count + assert_equal 0, Thing2.count + assert_equal 0, Thing3.count + assert_equal 0, Thing4.count + + Thing1.create(:id => 1, :name => "thing1", :email => "thing1@localhost", :moment => Time.now) + assert_equal 1, Thing1.count + assert_equal 0, Thing2.count + assert_equal 0, Thing3.count + assert_equal 0, Thing4.count + + Thing2.create(:id => 1, :name => "thing1", :email => "thing1@localhost", :moment => Time.now) + assert_equal 1, Thing1.count + assert_equal 1, Thing2.count + assert_equal 0, Thing3.count + assert_equal 0, Thing4.count + + Thing3.create(:id => 1, :name => "thing1", :email => "thing1@localhost", :moment => Time.now) + assert_equal 1, Thing1.count + assert_equal 1, Thing2.count + assert_equal 1, Thing3.count + assert_equal 0, Thing4.count + + Thing4.create(:id => 1, :name => "thing1", :email => "thing1@localhost", :moment => Time.now) + assert_equal 1, Thing1.count + assert_equal 1, Thing2.count + assert_equal 1, Thing3.count + assert_equal 1, Thing4.count + end + + def test_raise_on_unquoted_schema_name + assert_raises(ActiveRecord::StatementInvalid) do + with_schema_search_path '$user,public' + end + end + + def test_without_schema_search_path + assert_raises(ActiveRecord::StatementInvalid) { columns(TABLE_NAME) } + end + + def test_ignore_nil_schema_search_path + assert_nothing_raised { with_schema_search_path nil } + end + + def test_index_name_exists + with_schema_search_path(SCHEMA_NAME) do + assert @connection.index_name_exists?(TABLE_NAME, INDEX_A_NAME, true) + assert @connection.index_name_exists?(TABLE_NAME, INDEX_B_NAME, true) + assert @connection.index_name_exists?(TABLE_NAME, INDEX_C_NAME, true) + assert @connection.index_name_exists?(TABLE_NAME, INDEX_D_NAME, true) + assert @connection.index_name_exists?(TABLE_NAME, INDEX_E_NAME, true) + assert @connection.index_name_exists?(TABLE_NAME, INDEX_E_NAME, true) + assert_not @connection.index_name_exists?(TABLE_NAME, 'missing_index', true) + end + end + + def test_dump_indexes_for_schema_one + do_dump_index_tests_for_schema(SCHEMA_NAME, INDEX_A_COLUMN, INDEX_B_COLUMN_S1, INDEX_D_COLUMN, INDEX_E_COLUMN) + end + + def test_dump_indexes_for_schema_two + do_dump_index_tests_for_schema(SCHEMA2_NAME, INDEX_A_COLUMN, INDEX_B_COLUMN_S2, INDEX_D_COLUMN, INDEX_E_COLUMN) + end + + def test_dump_indexes_for_schema_multiple_schemas_in_search_path + do_dump_index_tests_for_schema("public, #{SCHEMA_NAME}", INDEX_A_COLUMN, INDEX_B_COLUMN_S1, INDEX_D_COLUMN, INDEX_E_COLUMN) + end + + def test_with_uppercase_index_name + @connection.execute "CREATE INDEX \"things_Index\" ON #{SCHEMA_NAME}.things (name)" + assert_nothing_raised { @connection.remove_index! "things", "#{SCHEMA_NAME}.things_Index"} + @connection.execute "CREATE INDEX \"things_Index\" ON #{SCHEMA_NAME}.things (name)" + + with_schema_search_path SCHEMA_NAME do + assert_nothing_raised { @connection.remove_index! "things", "things_Index"} + end + end + + def test_primary_key_with_schema_specified + [ + %("#{SCHEMA_NAME}"."#{PK_TABLE_NAME}"), + %(#{SCHEMA_NAME}."#{PK_TABLE_NAME}"), + %(#{SCHEMA_NAME}.#{PK_TABLE_NAME}) + ].each do |given| + assert_equal 'id', @connection.primary_key(given), "primary key should be found when table referenced as #{given}" + end + end + + def test_primary_key_assuming_schema_search_path + with_schema_search_path(SCHEMA_NAME) do + assert_equal 'id', @connection.primary_key(PK_TABLE_NAME), "primary key should be found" + end + end + + def test_primary_key_raises_error_if_table_not_found_on_schema_search_path + with_schema_search_path(SCHEMA2_NAME) do + assert_raises(ActiveRecord::StatementInvalid) do + @connection.primary_key(PK_TABLE_NAME) + end + end + end + + def test_pk_and_sequence_for_with_schema_specified + pg_name = ActiveRecord::ConnectionAdapters::PostgreSQL::Name + [ + %("#{SCHEMA_NAME}"."#{PK_TABLE_NAME}"), + %("#{SCHEMA_NAME}"."#{UNMATCHED_PK_TABLE_NAME}") + ].each do |given| + pk, seq = @connection.pk_and_sequence_for(given) + assert_equal 'id', pk, "primary key should be found when table referenced as #{given}" + assert_equal pg_name.new(SCHEMA_NAME, "#{PK_TABLE_NAME}_id_seq"), seq, "sequence name should be found when table referenced as #{given}" if given == %("#{SCHEMA_NAME}"."#{PK_TABLE_NAME}") + assert_equal pg_name.new(SCHEMA_NAME, UNMATCHED_SEQUENCE_NAME), seq, "sequence name should be found when table referenced as #{given}" if given == %("#{SCHEMA_NAME}"."#{UNMATCHED_PK_TABLE_NAME}") + end + end + + def test_current_schema + { + %('$user',public) => 'public', + SCHEMA_NAME => SCHEMA_NAME, + %(#{SCHEMA2_NAME},#{SCHEMA_NAME},public) => SCHEMA2_NAME, + %(public,#{SCHEMA2_NAME},#{SCHEMA_NAME}) => 'public' + }.each do |given,expect| + with_schema_search_path(given) { assert_equal expect, @connection.current_schema } + end + end + + def test_prepared_statements_with_multiple_schemas + [SCHEMA_NAME, SCHEMA2_NAME].each do |schema_name| + with_schema_search_path schema_name do + Thing5.create(:id => 1, :name => "thing inside #{SCHEMA_NAME}", :email => "thing1@localhost", :moment => Time.now) + end + end + + [SCHEMA_NAME, SCHEMA2_NAME].each do |schema_name| + with_schema_search_path schema_name do + assert_equal 1, Thing5.count + end + end + end + + def test_schema_exists? + { + 'public' => true, + SCHEMA_NAME => true, + SCHEMA2_NAME => true, + 'darkside' => false + }.each do |given,expect| + assert_equal expect, @connection.schema_exists?(given) + end + end + + def test_reset_pk_sequence + sequence_name = "#{SCHEMA_NAME}.#{UNMATCHED_SEQUENCE_NAME}" + @connection.execute "SELECT setval('#{sequence_name}', 123)" + assert_equal "124", @connection.select_value("SELECT nextval('#{sequence_name}')") + @connection.reset_pk_sequence!("#{SCHEMA_NAME}.#{UNMATCHED_PK_TABLE_NAME}") + assert_equal "1", @connection.select_value("SELECT nextval('#{sequence_name}')") + end + + private + def columns(table_name) + @connection.send(:column_definitions, table_name).map do |name, type, default| + "#{name} #{type}" + (default ? " default #{default}" : '') + end + end + + def with_schema_search_path(schema_search_path) + @connection.schema_search_path = schema_search_path + yield if block_given? + ensure + @connection.schema_search_path = "'$user', public" + end + + def do_dump_index_tests_for_schema(this_schema_name, first_index_column_name, second_index_column_name, third_index_column_name, fourth_index_column_name) + with_schema_search_path(this_schema_name) do + indexes = @connection.indexes(TABLE_NAME).sort_by {|i| i.name} + assert_equal 4,indexes.size + + do_dump_index_assertions_for_one_index(indexes[0], INDEX_A_NAME, first_index_column_name) + do_dump_index_assertions_for_one_index(indexes[1], INDEX_B_NAME, second_index_column_name) + do_dump_index_assertions_for_one_index(indexes[2], INDEX_D_NAME, third_index_column_name) + do_dump_index_assertions_for_one_index(indexes[3], INDEX_E_NAME, fourth_index_column_name) + + indexes.select{|i| i.name != INDEX_E_NAME}.each do |index| + assert_equal :btree, index.using + end + assert_equal :gin, indexes.select{|i| i.name == INDEX_E_NAME}[0].using + assert_equal :desc, indexes.select{|i| i.name == INDEX_D_NAME}[0].orders[INDEX_D_COLUMN] + end + end + + def do_dump_index_assertions_for_one_index(this_index, this_index_name, this_index_column) + assert_equal TABLE_NAME, this_index.table + assert_equal 1, this_index.columns.size + assert_equal this_index_column, this_index.columns[0] + assert_equal this_index_name, this_index.name + end +end diff --git a/activerecord/test/cases/adapters/postgresql/sql_types_test.rb b/activerecord/test/cases/adapters/postgresql/sql_types_test.rb new file mode 100644 index 0000000000..d7d40f6385 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/sql_types_test.rb @@ -0,0 +1,18 @@ +require "cases/helper" + +class SqlTypesTest < ActiveRecord::TestCase + def test_binary_types + assert_equal 'bytea', type_to_sql(:binary, 100_000) + assert_raise ActiveRecord::ActiveRecordError do + type_to_sql :binary, 4294967295 + end + assert_equal 'text', type_to_sql(:text, 100_000) + assert_raise ActiveRecord::ActiveRecordError do + type_to_sql :text, 4294967295 + end + end + + def type_to_sql(*args) + ActiveRecord::Base.connection.type_to_sql(*args) + end +end diff --git a/activerecord/test/cases/adapters/postgresql/statement_pool_test.rb b/activerecord/test/cases/adapters/postgresql/statement_pool_test.rb new file mode 100644 index 0000000000..1497b0abc7 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/statement_pool_test.rb @@ -0,0 +1,41 @@ +require 'cases/helper' + +module ActiveRecord + module ConnectionAdapters + class PostgreSQLAdapter < AbstractAdapter + class InactivePGconn + def query(*args) + raise PGError + end + + def status + PGconn::CONNECTION_BAD + end + end + + class StatementPoolTest < ActiveRecord::TestCase + if Process.respond_to?(:fork) + def test_cache_is_per_pid + cache = StatementPool.new nil, 10 + cache['foo'] = 'bar' + assert_equal 'bar', cache['foo'] + + pid = fork { + lookup = cache['foo']; + exit!(!lookup) + } + + Process.waitpid pid + assert $?.success?, 'process should exit successfully' + end + end + + def test_dealloc_does_not_raise_on_inactive_connection + cache = StatementPool.new InactivePGconn.new, 10 + cache['foo'] = 'bar' + assert_nothing_raised { cache.clear } + end + end + end + end +end diff --git a/activerecord/test/cases/adapters/postgresql/timestamp_test.rb b/activerecord/test/cases/adapters/postgresql/timestamp_test.rb new file mode 100644 index 0000000000..3614b29190 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/timestamp_test.rb @@ -0,0 +1,154 @@ +require 'cases/helper' +require 'models/developer' +require 'models/topic' + +class PostgresqlTimestampTest < ActiveRecord::TestCase + class PostgresqlTimestampWithZone < ActiveRecord::Base; end + + self.use_transactional_fixtures = false + + setup do + @connection = ActiveRecord::Base.connection + @connection.execute("INSERT INTO postgresql_timestamp_with_zones (id, time) VALUES (1, '2010-01-01 10:00:00-1')") + end + + teardown do + PostgresqlTimestampWithZone.delete_all + end + + def test_timestamp_with_zone_values_with_rails_time_zone_support + with_timezone_config default: :utc, aware_attributes: true do + @connection.reconnect! + + timestamp = PostgresqlTimestampWithZone.find(1) + assert_equal Time.utc(2010,1,1, 11,0,0), timestamp.time + assert_instance_of Time, timestamp.time + end + ensure + @connection.reconnect! + end + + def test_timestamp_with_zone_values_without_rails_time_zone_support + with_timezone_config default: :local, aware_attributes: false do + @connection.reconnect! + # make sure to use a non-UTC time zone + @connection.execute("SET time zone 'America/Jamaica'", 'SCHEMA') + + timestamp = PostgresqlTimestampWithZone.find(1) + assert_equal Time.utc(2010,1,1, 11,0,0), timestamp.time + assert_instance_of Time, timestamp.time + end + ensure + @connection.reconnect! + end +end + +class TimestampTest < ActiveRecord::TestCase + fixtures :topics + + def test_group_by_date + keys = Topic.group("date_trunc('month', created_at)").count.keys + assert_operator keys.length, :>, 0 + keys.each { |k| assert_kind_of Time, k } + end + + def test_load_infinity_and_beyond + d = Developer.find_by_sql("select 'infinity'::timestamp as updated_at") + assert d.first.updated_at.infinite?, 'timestamp should be infinite' + + d = Developer.find_by_sql("select '-infinity'::timestamp as updated_at") + time = d.first.updated_at + assert time.infinite?, 'timestamp should be infinite' + assert_operator time, :<, 0 + end + + def test_save_infinity_and_beyond + d = Developer.create!(:name => 'aaron', :updated_at => 1.0 / 0.0) + assert_equal(1.0 / 0.0, d.updated_at) + + d = Developer.create!(:name => 'aaron', :updated_at => -1.0 / 0.0) + assert_equal(-1.0 / 0.0, d.updated_at) + end + + def test_default_datetime_precision + ActiveRecord::Base.connection.create_table(:foos) + ActiveRecord::Base.connection.add_column :foos, :created_at, :datetime + ActiveRecord::Base.connection.add_column :foos, :updated_at, :datetime + assert_nil activerecord_column_option('foos', 'created_at', 'precision') + end + + def test_timestamp_data_type_with_precision + ActiveRecord::Base.connection.create_table(:foos) + ActiveRecord::Base.connection.add_column :foos, :created_at, :datetime, :precision => 0 + ActiveRecord::Base.connection.add_column :foos, :updated_at, :datetime, :precision => 5 + assert_equal 0, activerecord_column_option('foos', 'created_at', 'precision') + assert_equal 5, activerecord_column_option('foos', 'updated_at', 'precision') + end + + def test_timestamps_helper_with_custom_precision + ActiveRecord::Base.connection.create_table(:foos) do |t| + t.timestamps :precision => 4 + end + assert_equal 4, activerecord_column_option('foos', 'created_at', 'precision') + assert_equal 4, activerecord_column_option('foos', 'updated_at', 'precision') + end + + def test_passing_precision_to_timestamp_does_not_set_limit + ActiveRecord::Base.connection.create_table(:foos) do |t| + t.timestamps :precision => 4 + end + assert_nil activerecord_column_option("foos", "created_at", "limit") + assert_nil activerecord_column_option("foos", "updated_at", "limit") + end + + def test_invalid_timestamp_precision_raises_error + assert_raises ActiveRecord::ActiveRecordError do + ActiveRecord::Base.connection.create_table(:foos) do |t| + t.timestamps :precision => 7 + end + end + end + + def test_postgres_agrees_with_activerecord_about_precision + ActiveRecord::Base.connection.create_table(:foos) do |t| + t.timestamps :precision => 4 + end + assert_equal '4', pg_datetime_precision('foos', 'created_at') + assert_equal '4', pg_datetime_precision('foos', 'updated_at') + end + + def test_bc_timestamp + date = Date.new(0) - 1.week + Developer.create!(:name => "aaron", :updated_at => date) + assert_equal date, Developer.find_by_name("aaron").updated_at + end + + def test_bc_timestamp_leap_year + date = Time.utc(-4, 2, 29) + Developer.create!(:name => "taihou", :updated_at => date) + assert_equal date, Developer.find_by_name("taihou").updated_at + end + + def test_bc_timestamp_year_zero + date = Time.utc(0, 4, 7) + Developer.create!(:name => "yahagi", :updated_at => date) + assert_equal date, Developer.find_by_name("yahagi").updated_at + end + + private + + def pg_datetime_precision(table_name, column_name) + results = ActiveRecord::Base.connection.execute("SELECT column_name, datetime_precision FROM information_schema.columns WHERE table_name ='#{table_name}'") + result = results.find do |result_hash| + result_hash["column_name"] == column_name + end + result && result["datetime_precision"] + end + + def activerecord_column_option(tablename, column_name, option) + result = ActiveRecord::Base.connection.columns(tablename).find do |column| + column.name == column_name + end + result && result.send(option) + end +end diff --git a/activerecord/test/cases/adapters/postgresql/type_lookup_test.rb b/activerecord/test/cases/adapters/postgresql/type_lookup_test.rb new file mode 100644 index 0000000000..23817198b1 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/type_lookup_test.rb @@ -0,0 +1,15 @@ +require 'cases/helper' + +class PostgresqlTypeLookupTest < ActiveRecord::TestCase + setup do + @connection = ActiveRecord::Base.connection + end + + test "array delimiters are looked up correctly" do + box_array = @connection.type_map.lookup(1020) + int_array = @connection.type_map.lookup(1007) + + assert_equal ';', box_array.delimiter + assert_equal ',', int_array.delimiter + end +end diff --git a/activerecord/test/cases/adapters/postgresql/utils_test.rb b/activerecord/test/cases/adapters/postgresql/utils_test.rb new file mode 100644 index 0000000000..3fdb6888d9 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/utils_test.rb @@ -0,0 +1,61 @@ +require 'cases/helper' + +class PostgreSQLUtilsTest < ActiveSupport::TestCase + Name = ActiveRecord::ConnectionAdapters::PostgreSQL::Name + include ActiveRecord::ConnectionAdapters::PostgreSQL::Utils + + def test_extract_schema_qualified_name + { + %(table_name) => [nil,'table_name'], + %("table.name") => [nil,'table.name'], + %(schema.table_name) => %w{schema table_name}, + %("schema".table_name) => %w{schema table_name}, + %(schema."table_name") => %w{schema table_name}, + %("schema"."table_name") => %w{schema table_name}, + %("even spaces".table) => ['even spaces','table'], + %(schema."table.name") => ['schema', 'table.name'] + }.each do |given, expect| + assert_equal Name.new(*expect), extract_schema_qualified_name(given) + end + end +end + +class PostgreSQLNameTest < ActiveSupport::TestCase + Name = ActiveRecord::ConnectionAdapters::PostgreSQL::Name + + test "represents itself as schema.name" do + obj = Name.new("public", "articles") + assert_equal "public.articles", obj.to_s + end + + test "without schema, represents itself as name only" do + obj = Name.new(nil, "articles") + assert_equal "articles", obj.to_s + end + + test "quoted returns a string representation usable in a query" do + assert_equal %("articles"), Name.new(nil, "articles").quoted + assert_equal %("public"."articles"), Name.new("public", "articles").quoted + end + + test "prevents double quoting" do + name = Name.new('"quoted_schema"', '"quoted_table"') + assert_equal "quoted_schema.quoted_table", name.to_s + assert_equal %("quoted_schema"."quoted_table"), name.quoted + end + + test "equality based on state" do + assert_equal Name.new("access", "users"), Name.new("access", "users") + assert_equal Name.new(nil, "users"), Name.new(nil, "users") + assert_not_equal Name.new(nil, "users"), Name.new("access", "users") + assert_not_equal Name.new("access", "users"), Name.new("public", "users") + assert_not_equal Name.new("public", "users"), Name.new("public", "articles") + end + + test "can be used as hash key" do + hash = {Name.new("schema", "article_seq") => "success"} + assert_equal "success", hash[Name.new("schema", "article_seq")] + assert_equal nil, hash[Name.new("schema", "articles")] + assert_equal nil, hash[Name.new("public", "article_seq")] + end +end diff --git a/activerecord/test/cases/adapters/postgresql/uuid_test.rb b/activerecord/test/cases/adapters/postgresql/uuid_test.rb new file mode 100644 index 0000000000..66006d718f --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/uuid_test.rb @@ -0,0 +1,256 @@ +# encoding: utf-8 + +require "cases/helper" +require 'active_record/base' +require 'active_record/connection_adapters/postgresql_adapter' + +module PostgresqlUUIDHelper + def connection + @connection ||= ActiveRecord::Base.connection + end + + def drop_table(name) + connection.execute "drop table if exists #{name}" + end +end + +class PostgresqlUUIDTest < ActiveRecord::TestCase + include PostgresqlUUIDHelper + + class UUIDType < ActiveRecord::Base + self.table_name = "uuid_data_type" + end + + setup do + connection.create_table "uuid_data_type" do |t| + t.uuid 'guid' + end + end + + teardown do + drop_table "uuid_data_type" + end + + def test_change_column_default + @connection.add_column :uuid_data_type, :thingy, :uuid, null: false, default: "uuid_generate_v1()" + UUIDType.reset_column_information + column = UUIDType.columns_hash['thingy'] + assert_equal "uuid_generate_v1()", column.default_function + + @connection.change_column :uuid_data_type, :thingy, :uuid, null: false, default: "uuid_generate_v4()" + + UUIDType.reset_column_information + column = UUIDType.columns_hash['thingy'] + assert_equal "uuid_generate_v4()", column.default_function + ensure + UUIDType.reset_column_information + end + + def test_data_type_of_uuid_types + column = UUIDType.columns_hash["guid"] + assert_equal :uuid, column.type + assert_equal "uuid", column.sql_type + assert_not column.number? + assert_not column.binary? + assert_not column.array + end + + def test_treat_blank_uuid_as_nil + UUIDType.create! guid: '' + assert_equal(nil, UUIDType.last.guid) + end + + def test_treat_invalid_uuid_as_nil + uuid = UUIDType.create! guid: 'foobar' + assert_equal(nil, uuid.guid) + end + + def test_invalid_uuid_dont_modify_before_type_cast + uuid = UUIDType.new guid: 'foobar' + assert_equal 'foobar', uuid.guid_before_type_cast + end + + def test_rfc_4122_regex + # Valid uuids + ['A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11', + '{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}', + 'a0eebc999c0b4ef8bb6d6bb9bd380a11', + 'a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11', + '{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}'].each do |valid_uuid| + uuid = UUIDType.new guid: valid_uuid + assert_not_nil uuid.guid + end + + # Invalid uuids + [['A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'], + Hash.new, + 0, + 0.0, + true, + 'Z0000C99-9C0B-4EF8-BB6D-6BB9BD380A11', + '{a0eebc99-9c0b-4ef8-fb6d-6bb9bd380a11}', + 'a0eebc999r0b4ef8ab6d6bb9bd380a11', + 'a0ee-bc99------4ef8-bb6d-6bb9-bd38-0a11', + '{a0eebc99-bb6d6bb9-bd380a11}'].each do |invalid_uuid| + uuid = UUIDType.new guid: invalid_uuid + assert_nil uuid.guid + end + end + + def test_uuid_formats + ["A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11", + "{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}", + "a0eebc999c0b4ef8bb6d6bb9bd380a11", + "a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11", + "{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}"].each do |valid_uuid| + UUIDType.create(guid: valid_uuid) + uuid = UUIDType.last + assert_equal "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11", uuid.guid + end + end +end + +class PostgresqlUUIDGenerationTest < ActiveRecord::TestCase + include PostgresqlUUIDHelper + + class UUID < ActiveRecord::Base + self.table_name = 'pg_uuids' + end + + setup do + enable_uuid_ossp!(connection) + + connection.create_table('pg_uuids', id: :uuid, default: 'uuid_generate_v1()') do |t| + t.string 'name' + t.uuid 'other_uuid', default: 'uuid_generate_v4()' + end + + # Create custom PostgreSQL function to generate UUIDs + # to test dumping tables which columns have defaults with custom functions + connection.execute <<-SQL + CREATE OR REPLACE FUNCTION my_uuid_generator() RETURNS uuid + AS $$ SELECT * FROM uuid_generate_v4() $$ + LANGUAGE SQL VOLATILE; + SQL + + # Create such a table with custom function as default value generator + connection.create_table('pg_uuids_2', id: :uuid, default: 'my_uuid_generator()') do |t| + t.string 'name' + t.uuid 'other_uuid_2', default: 'my_uuid_generator()' + end + end + + teardown do + drop_table "pg_uuids" + drop_table 'pg_uuids_2' + connection.execute 'DROP FUNCTION IF EXISTS my_uuid_generator();' + end + + if ActiveRecord::Base.connection.supports_extensions? + def test_id_is_uuid + assert_equal :uuid, UUID.columns_hash['id'].type + assert UUID.primary_key + end + + def test_id_has_a_default + u = UUID.create + assert_not_nil u.id + end + + def test_auto_create_uuid + u = UUID.create + u.reload + assert_not_nil u.other_uuid + end + + def test_pk_and_sequence_for_uuid_primary_key + pk, seq = connection.pk_and_sequence_for('pg_uuids') + assert_equal 'id', pk + assert_equal nil, seq + end + + def test_schema_dumper_for_uuid_primary_key + schema = StringIO.new + ActiveRecord::SchemaDumper.dump(connection, schema) + assert_match(/\bcreate_table "pg_uuids", id: :uuid, default: "uuid_generate_v1\(\)"/, schema.string) + assert_match(/t\.uuid "other_uuid", default: "uuid_generate_v4\(\)"/, schema.string) + end + + def test_schema_dumper_for_uuid_primary_key_with_custom_default + schema = StringIO.new + ActiveRecord::SchemaDumper.dump(connection, schema) + assert_match(/\bcreate_table "pg_uuids_2", id: :uuid, default: "my_uuid_generator\(\)"/, schema.string) + assert_match(/t\.uuid "other_uuid_2", default: "my_uuid_generator\(\)"/, schema.string) + end + end +end + +class PostgresqlUUIDTestNilDefault < ActiveRecord::TestCase + include PostgresqlUUIDHelper + + setup do + enable_uuid_ossp!(connection) + + connection.create_table('pg_uuids', id: false) do |t| + t.primary_key :id, :uuid, default: nil + t.string 'name' + end + end + + teardown do + drop_table "pg_uuids" + end + + if ActiveRecord::Base.connection.supports_extensions? + def test_id_allows_default_override_via_nil + col_desc = connection.execute("SELECT pg_get_expr(d.adbin, d.adrelid) as default + FROM pg_attribute a + LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum + WHERE a.attname='id' AND a.attrelid = 'pg_uuids'::regclass").first + assert_nil col_desc["default"] + end + end +end + +class PostgresqlUUIDTestInverseOf < ActiveRecord::TestCase + include PostgresqlUUIDHelper + + class UuidPost < ActiveRecord::Base + self.table_name = 'pg_uuid_posts' + has_many :uuid_comments, inverse_of: :uuid_post + end + + class UuidComment < ActiveRecord::Base + self.table_name = 'pg_uuid_comments' + belongs_to :uuid_post + end + + setup do + enable_uuid_ossp!(connection) + + connection.transaction do + connection.create_table('pg_uuid_posts', id: :uuid) do |t| + t.string 'title' + end + connection.create_table('pg_uuid_comments', id: :uuid) do |t| + t.references :uuid_post, type: :uuid + t.string 'content' + end + end + end + + teardown do + connection.transaction do + drop_table "pg_uuid_comments" + drop_table "pg_uuid_posts" + end + end + + if ActiveRecord::Base.connection.supports_extensions? + def test_collection_association_with_uuid + post = UuidPost.create! + comment = post.uuid_comments.create! + assert post.uuid_comments.find(comment.id) + end + end +end diff --git a/activerecord/test/cases/adapters/postgresql/view_test.rb b/activerecord/test/cases/adapters/postgresql/view_test.rb new file mode 100644 index 0000000000..47b7d38eda --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/view_test.rb @@ -0,0 +1,67 @@ +require "cases/helper" + +module ViewTestConcern + extend ActiveSupport::Concern + + included do + self.use_transactional_fixtures = false + mattr_accessor :view_type + end + + SCHEMA_NAME = 'test_schema' + TABLE_NAME = 'things' + COLUMNS = [ + 'id integer', + 'name character varying(50)', + 'email character varying(50)', + 'moment timestamp without time zone' + ] + + class ThingView < ActiveRecord::Base + end + + def setup + super + ThingView.table_name = "#{SCHEMA_NAME}.#{view_type}_things" + + @connection = ActiveRecord::Base.connection + @connection.execute "CREATE SCHEMA #{SCHEMA_NAME} CREATE TABLE #{TABLE_NAME} (#{COLUMNS.join(',')})" + @connection.execute "CREATE #{view_type.humanize} #{ThingView.table_name} AS SELECT * FROM #{SCHEMA_NAME}.#{TABLE_NAME}" + end + + def teardown + super + @connection.execute "DROP SCHEMA #{SCHEMA_NAME} CASCADE" + end + + def test_table_exists + name = ThingView.table_name + assert @connection.table_exists?(name), "'#{name}' table should exist" + end + + def test_column_definitions + assert_nothing_raised do + assert_equal COLUMNS, columns(ThingView.table_name) + end + end + + private + def columns(table_name) + @connection.send(:column_definitions, table_name).map do |name, type, default| + "#{name} #{type}" + (default ? " default #{default}" : '') + end + end + +end + +class ViewTest < ActiveRecord::TestCase + include ViewTestConcern + self.view_type = 'view' +end + +if ActiveRecord::Base.connection.supports_materialized_views? + class MaterializedViewTest < ActiveRecord::TestCase + include ViewTestConcern + self.view_type = 'materialized_view' + end +end diff --git a/activerecord/test/cases/adapters/postgresql/xml_test.rb b/activerecord/test/cases/adapters/postgresql/xml_test.rb new file mode 100644 index 0000000000..4165dd5ac9 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/xml_test.rb @@ -0,0 +1,48 @@ +# encoding: utf-8 +require 'cases/helper' + +class PostgresqlXMLTest < ActiveRecord::TestCase + class XmlDataType < ActiveRecord::Base + self.table_name = 'xml_data_type' + end + + def setup + @connection = ActiveRecord::Base.connection + begin + @connection.transaction do + @connection.create_table('xml_data_type') do |t| + t.xml 'payload' + end + end + rescue ActiveRecord::StatementInvalid + skip "do not test on PG without xml" + end + @column = XmlDataType.columns_hash['payload'] + end + + teardown do + @connection.execute 'drop table if exists xml_data_type' + end + + def test_column + assert_equal :xml, @column.type + end + + def test_null_xml + @connection.execute %q|insert into xml_data_type (payload) VALUES(null)| + assert_nil XmlDataType.first.payload + end + + def test_round_trip + data = XmlDataType.new(payload: "<foo>bar</foo>") + assert_equal "<foo>bar</foo>", data.payload + data.save! + assert_equal "<foo>bar</foo>", data.reload.payload + end + + def test_update_all + data = XmlDataType.create! + XmlDataType.update_all(payload: "<bar>baz</bar>") + assert_equal "<bar>baz</bar>", data.reload.payload + end +end |