From 3ad2f9921a10397090882de23f3e8fb971b8f027 Mon Sep 17 00:00:00 2001 From: fatkodima Date: Fri, 22 Dec 2017 01:12:13 +0200 Subject: Support for PostgreSQL foreign tables --- activerecord/CHANGELOG.md | 4 + .../postgresql/schema_statements.rb | 12 ++- .../connection_adapters/postgresql_adapter.rb | 4 + .../adapters/postgresql/foreign_table_test.rb | 109 +++++++++++++++++++++ 4 files changed, 128 insertions(+), 1 deletion(-) create mode 100644 activerecord/test/cases/adapters/postgresql/foreign_table_test.rb (limited to 'activerecord') diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md index efe555374a..e2dc8045e2 100644 --- a/activerecord/CHANGELOG.md +++ b/activerecord/CHANGELOG.md @@ -1,3 +1,7 @@ +* Support for PostgreSQL foreign tables. + + *fatkodima* + * Fix relation merger issue with `left_outer_joins`. *Mehmet Emin İNAÇ* diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb index c6e5122daf..b9d2f4da39 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -527,6 +527,14 @@ module ActiveRecord end end + def foreign_tables + query_values(data_source_sql(type: "FOREIGN TABLE"), "SCHEMA") + end + + def foreign_table_exists?(table_name) + query_values(data_source_sql(table_name, type: "FOREIGN TABLE"), "SCHEMA").any? if table_name.present? + end + # Maps logical Rails types to PostgreSQL-specific data types. def type_to_sql(type, limit: nil, precision: nil, scale: nil, array: nil, **) # :nodoc: sql = \ @@ -739,7 +747,7 @@ module ActiveRecord def data_source_sql(name = nil, type: nil) scope = quoted_scope(name, type: type) - scope[:type] ||= "'r','v','m'" # (r)elation/table, (v)iew, (m)aterialized view + scope[:type] ||= "'r','v','m','f'" # (r)elation/table, (v)iew, (m)aterialized view, (f)oreign table sql = "SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace".dup sql << " WHERE n.nspname = #{scope[:schema]}" @@ -756,6 +764,8 @@ module ActiveRecord "'r'" when "VIEW" "'v','m'" + when "FOREIGN TABLE" + "'f'" end scope = {} scope[:schema] = schema ? quote(schema) : "ANY (current_schemas(false))" diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 9ac5a8760e..ddc5a91286 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -318,6 +318,10 @@ module ActiveRecord postgresql_version >= 90300 end + def supports_foreign_tables? + postgresql_version >= 90300 + end + def supports_pgcrypto_uuid? postgresql_version >= 90400 end diff --git a/activerecord/test/cases/adapters/postgresql/foreign_table_test.rb b/activerecord/test/cases/adapters/postgresql/foreign_table_test.rb new file mode 100644 index 0000000000..4fa315ad23 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/foreign_table_test.rb @@ -0,0 +1,109 @@ +# frozen_string_literal: true + +require "cases/helper" +require "models/professor" + +if ActiveRecord::Base.connection.supports_foreign_tables? + class ForeignTableTest < ActiveRecord::TestCase + self.use_transactional_tests = false + + class ForeignProfessor < ActiveRecord::Base + self.table_name = "foreign_professors" + end + + class ForeignProfessorWithPk < ForeignProfessor + self.primary_key = "id" + end + + def setup + @professor = Professor.create(name: "Nicola") + + @connection = ActiveRecord::Base.connection + enable_extension!("postgres_fdw", @connection) + + foreign_db_config = ARTest.connection_config["arunit2"] + @connection.execute <<-SQL + CREATE SERVER foreign_server + FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname '#{foreign_db_config["database"]}') + SQL + + @connection.execute <<-SQL + CREATE USER MAPPING FOR CURRENT_USER + SERVER foreign_server + SQL + + @connection.execute <<-SQL + CREATE FOREIGN TABLE foreign_professors ( + id int, + name character varying NOT NULL + ) SERVER foreign_server OPTIONS ( + table_name 'professors' + ) + SQL + end + + def teardown + disable_extension!("postgres_fdw", @connection) + @connection.execute <<-SQL + DROP SERVER IF EXISTS foreign_server CASCADE + SQL + end + + def test_table_exists + table_name = ForeignProfessor.table_name + assert_not ActiveRecord::Base.connection.table_exists?(table_name) + end + + def test_foreign_tables_are_valid_data_sources + table_name = ForeignProfessor.table_name + assert @connection.data_source_exists?(table_name), "'#{table_name}' should be a data source" + end + + def test_foreign_tables + assert_equal ["foreign_professors"], @connection.foreign_tables + end + + def test_foreign_table_exists + assert @connection.foreign_table_exists?("foreign_professors") + assert @connection.foreign_table_exists?(:foreign_professors) + assert_not @connection.foreign_table_exists?("nonexistingtable") + assert_not @connection.foreign_table_exists?("'") + assert_not @connection.foreign_table_exists?(nil) + end + + def test_attribute_names + assert_equal ["id", "name"], ForeignProfessor.attribute_names + end + + def test_attributes + professor = ForeignProfessorWithPk.find(@professor.id) + assert_equal @professor.attributes, professor.attributes + end + + def test_does_not_have_a_primary_key + assert_nil ForeignProfessor.primary_key + end + + def test_insert_record + # Explicit `id` here to avoid complex configurations to implicitly work with remote table + ForeignProfessorWithPk.create!(id: 100, name: "Leonardo") + + professor = ForeignProfessorWithPk.last + assert_equal "Leonardo", professor.name + end + + def test_update_record + professor = ForeignProfessorWithPk.find(@professor.id) + professor.name = "Albert" + professor.save! + professor.reload + assert_equal "Albert", professor.name + end + + def test_delete_record + professor = ForeignProfessorWithPk.find(@professor.id) + assert_difference("ForeignProfessor.count", -1) { professor.destroy } + end + end +end -- cgit v1.2.3