From 5c7f8c929b228063b224eaa17360dcc105788296 Mon Sep 17 00:00:00 2001 From: Paul Gallagher Date: Thu, 9 Jun 2011 09:47:01 +0800 Subject: Improve PostgreSQL adapter schema-awareness * table_exists? scoped by schema search path unless schema is explicitly named. Added tests and doc to clarify the behaviour * extract_schema_and_table tests and implementation extended to cover all cases * primary_key does not ignore schema information * add current_schema and schema_exists? methods * more robust table referencing in insert_sql and sql_for_insert methods --- .../adapters/postgresql/postgresql_adapter_test.rb | 39 ++++++++++ .../test/cases/adapters/postgresql/schema_test.rb | 85 +++++++++++++++++++++- 2 files changed, 122 insertions(+), 2 deletions(-) (limited to 'activerecord/test/cases') diff --git a/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb b/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb index 7c49236854..b113267dca 100644 --- a/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb +++ b/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb @@ -10,6 +10,45 @@ module ActiveRecord @connection.exec_query('create table ex(id serial primary key, number integer, data character varying(255))') end + def test_primary_key + assert_equal 'id',@connection.primary_key('ex') + end + + def test_non_standard_primary_key + @connection.exec_query('drop table if exists ex') + @connection.exec_query('create table ex(data character varying(255) primary key)') + assert_equal 'data', @connection.primary_key('ex') + end + + def test_primary_key_returns_nil_for_no_pk + @connection.exec_query('drop table if exists ex') + @connection.exec_query('create table ex(id integer)') + assert_nil @connection.primary_key('ex') + 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 + id = @connection.insert_sql("insert into ex (number) values(5150)", nil, "number") + assert_equal "5150", id + end + + def test_insert_sql_with_quoted_schema_and_table_name + 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 + + def test_insert_sql_with_no_space_after_table_name + 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 + def test_serial_sequence assert_equal 'public.accounts_id_seq', @connection.serial_sequence('accounts', 'id') diff --git a/activerecord/test/cases/adapters/postgresql/schema_test.rb b/activerecord/test/cases/adapters/postgresql/schema_test.rb index a5c3e69af9..d4797e1680 100644 --- a/activerecord/test/cases/adapters/postgresql/schema_test.rb +++ b/activerecord/test/cases/adapters/postgresql/schema_test.rb @@ -20,6 +20,7 @@ class SchemaTest < ActiveRecord::TestCase 'email character varying(50)', 'moment timestamp without time zone default now()' ] + PK_TABLE_NAME = 'table_with_pk' class Thing1 < ActiveRecord::Base set_table_name "test_schema.things" @@ -37,6 +38,10 @@ class SchemaTest < ActiveRecord::TestCase set_table_name 'test_schema."Things"' end + class PrimaryKeyTestHarness < ActiveRecord::Base + set_table_name 'test_schema.pktest' + end + def setup @connection = ActiveRecord::Base.connection @connection.execute "CREATE SCHEMA #{SCHEMA_NAME} CREATE TABLE #{TABLE_NAME} (#{COLUMNS.join(',')})" @@ -49,6 +54,7 @@ class SchemaTest < ActiveRecord::TestCase @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 TABLE #{SCHEMA_NAME}.#{PK_TABLE_NAME} (id serial primary key)" end def teardown @@ -63,12 +69,30 @@ class SchemaTest < ActiveRecord::TestCase 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_table - assert(@connection.table_exists?('"things.table"'), "table should exist") + 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_with_schema_prefixed_table_name @@ -164,6 +188,63 @@ class SchemaTest < ActiveRecord::TestCase ActiveRecord::Base.connection.schema_search_path = "public" 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_extract_schema_and_table + { + %(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") => %w{schema "table.name"} + }.each do |given,expect| + assert_equal expect, @connection.extract_schema_and_table(given) + 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_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 + private def columns(table_name) @connection.send(:column_definitions, table_name).map do |name, type, default| -- cgit v1.2.3 From 019c263633242f4329f44f863705435f58e1d884 Mon Sep 17 00:00:00 2001 From: Paul Gallagher Date: Fri, 10 Jun 2011 22:48:58 +0800 Subject: apply private method indentation convention * tidy test code and fix my typo --- .../test/cases/adapters/postgresql/postgresql_adapter_test.rb | 2 +- activerecord/test/cases/adapters/postgresql/schema_test.rb | 4 ++-- 2 files changed, 3 insertions(+), 3 deletions(-) (limited to 'activerecord/test/cases') diff --git a/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb b/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb index b113267dca..9943cd18f6 100644 --- a/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb +++ b/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb @@ -11,7 +11,7 @@ module ActiveRecord end def test_primary_key - assert_equal 'id',@connection.primary_key('ex') + assert_equal 'id', @connection.primary_key('ex') end def test_non_standard_primary_key diff --git a/activerecord/test/cases/adapters/postgresql/schema_test.rb b/activerecord/test/cases/adapters/postgresql/schema_test.rb index d4797e1680..a12f689802 100644 --- a/activerecord/test/cases/adapters/postgresql/schema_test.rb +++ b/activerecord/test/cases/adapters/postgresql/schema_test.rb @@ -225,8 +225,8 @@ class SchemaTest < ActiveRecord::TestCase def test_current_schema { - %('$user',public) => 'public', - SCHEMA_NAME => SCHEMA_NAME, + %('$user',public) => 'public', + SCHEMA_NAME => SCHEMA_NAME, %(#{SCHEMA2_NAME},#{SCHEMA_NAME},public) => SCHEMA2_NAME, %(public,#{SCHEMA2_NAME},#{SCHEMA_NAME}) => 'public' }.each do |given,expect| -- cgit v1.2.3 From 1d7c751bf703c729887e2d8a9ae104a8e6aef010 Mon Sep 17 00:00:00 2001 From: Paul Gallagher Date: Sat, 11 Jun 2011 02:07:08 +0800 Subject: remove table quoting in primary_key method * add/cleanup tests --- .../adapters/postgresql/postgresql_adapter_test.rb | 30 ++++++++++++++++++++++ .../test/cases/adapters/postgresql/schema_test.rb | 22 ++++++++++++---- 2 files changed, 47 insertions(+), 5 deletions(-) (limited to 'activerecord/test/cases') diff --git a/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb b/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb index 9943cd18f6..d57794daf8 100644 --- a/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb +++ b/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb @@ -74,6 +74,36 @@ module ActiveRecord @connection.default_sequence_name('zomg') end + def test_pk_and_sequence_for + pk, seq = @connection.pk_and_sequence_for('ex') + assert_equal 'id', pk + assert_equal @connection.default_sequence_name('ex', 'id'), seq + end + + def test_pk_and_sequence_for_with_non_standard_primary_key + @connection.exec_query('drop table if exists ex') + @connection.exec_query('create table ex(code serial primary key)') + pk, seq = @connection.pk_and_sequence_for('ex') + assert_equal 'code', pk + assert_equal @connection.default_sequence_name('ex', 'code'), seq + end + + def test_pk_and_sequence_for_returns_nil_if_no_seq + @connection.exec_query('drop table if exists ex') + @connection.exec_query('create table ex(id integer primary key)') + assert_nil @connection.pk_and_sequence_for('ex') + end + + def test_pk_and_sequence_for_returns_nil_if_no_pk + @connection.exec_query('drop table if exists ex') + @connection.exec_query('create table ex(id integer)') + assert_nil @connection.pk_and_sequence_for('ex') + end + + def test_pk_and_sequence_for_returns_nil_if_table_not_found + assert_nil @connection.pk_and_sequence_for('unobtainium') + end + def test_exec_insert_number insert(@connection, 'number' => 10) diff --git a/activerecord/test/cases/adapters/postgresql/schema_test.rb b/activerecord/test/cases/adapters/postgresql/schema_test.rb index a12f689802..6d98bd4a39 100644 --- a/activerecord/test/cases/adapters/postgresql/schema_test.rb +++ b/activerecord/test/cases/adapters/postgresql/schema_test.rb @@ -38,10 +38,6 @@ class SchemaTest < ActiveRecord::TestCase set_table_name 'test_schema."Things"' end - class PrimaryKeyTestHarness < ActiveRecord::Base - set_table_name 'test_schema.pktest' - end - def setup @connection = ActiveRecord::Base.connection @connection.execute "CREATE SCHEMA #{SCHEMA_NAME} CREATE TABLE #{TABLE_NAME} (#{COLUMNS.join(',')})" @@ -189,7 +185,11 @@ class SchemaTest < ActiveRecord::TestCase 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| + [ + %("#{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 @@ -208,6 +208,18 @@ class SchemaTest < ActiveRecord::TestCase end end + def test_pk_and_sequence_for_with_schema_specified + [ + %("#{SCHEMA_NAME}"."#{PK_TABLE_NAME}"), + %(#{SCHEMA_NAME}."#{PK_TABLE_NAME}"), + %(#{SCHEMA_NAME}.#{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 "#{SCHEMA_NAME}.#{PK_TABLE_NAME}_id_seq", seq, "sequence name should be found when table referenced as #{given}" + end + end + def test_extract_schema_and_table { %(table_name) => [nil,'table_name'], -- cgit v1.2.3 From f8c4b374c831d6f37efb7b7685fe1d8fe101e096 Mon Sep 17 00:00:00 2001 From: Paul Gallagher Date: Sat, 11 Jun 2011 18:34:09 +0800 Subject: make extract_schema_and_table a private method --- activerecord/test/cases/adapters/postgresql/schema_test.rb | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) (limited to 'activerecord/test/cases') diff --git a/activerecord/test/cases/adapters/postgresql/schema_test.rb b/activerecord/test/cases/adapters/postgresql/schema_test.rb index 6d98bd4a39..8668957d5a 100644 --- a/activerecord/test/cases/adapters/postgresql/schema_test.rb +++ b/activerecord/test/cases/adapters/postgresql/schema_test.rb @@ -223,15 +223,15 @@ class SchemaTest < ActiveRecord::TestCase def test_extract_schema_and_table { %(table_name) => [nil,'table_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") => %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 expect, @connection.extract_schema_and_table(given) + assert_equal expect, @connection.send(:extract_schema_and_table, given) end end -- cgit v1.2.3 From b0d59907f733841280095b16c98a95574b3a0938 Mon Sep 17 00:00:00 2001 From: Paul Gallagher Date: Fri, 17 Jun 2011 21:57:47 +0800 Subject: Make PostgreSQL adapter view-compatible * amongst other things, allows meta_search to run against view-backed models --- .../test/cases/adapters/postgresql/schema_test.rb | 1 - .../test/cases/adapters/postgresql/view_test.rb | 49 ++++++++++++++++++++++ 2 files changed, 49 insertions(+), 1 deletion(-) create mode 100644 activerecord/test/cases/adapters/postgresql/view_test.rb (limited to 'activerecord/test/cases') diff --git a/activerecord/test/cases/adapters/postgresql/schema_test.rb b/activerecord/test/cases/adapters/postgresql/schema_test.rb index 8668957d5a..27e7b1a1c3 100644 --- a/activerecord/test/cases/adapters/postgresql/schema_test.rb +++ b/activerecord/test/cases/adapters/postgresql/schema_test.rb @@ -111,7 +111,6 @@ class SchemaTest < ActiveRecord::TestCase 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"') 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..303ba9245a --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/view_test.rb @@ -0,0 +1,49 @@ +require "cases/helper" + +class ViewTest < ActiveRecord::TestCase + self.use_transactional_fixtures = false + + SCHEMA_NAME = 'test_schema' + TABLE_NAME = 'things' + VIEW_NAME = 'view_things' + COLUMNS = [ + 'id integer', + 'name character varying(50)', + 'email character varying(50)', + 'moment timestamp without time zone' + ] + + class ThingView < ActiveRecord::Base + set_table_name 'test_schema.view_things' + 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 VIEW #{SCHEMA_NAME}.#{VIEW_NAME} AS SELECT id,name,email,moment FROM #{SCHEMA_NAME}.#{TABLE_NAME}" + end + + def teardown + @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("#{SCHEMA_NAME}.#{VIEW_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 -- cgit v1.2.3