From 8203482a9ef9bd60d5014745fd7af868d9954b1d Mon Sep 17 00:00:00 2001 From: Travis Hunter Date: Fri, 20 Jan 2017 16:18:46 -0500 Subject: Add support for invalid foreign keys in Postgres Add validate_constraint and update naming --- .../abstract/schema_definitions.rb | 5 ++ .../abstract/schema_statements.rb | 2 + .../connection_adapters/abstract_adapter.rb | 5 ++ .../postgresql/schema_creation.rb | 12 ++++ .../postgresql/schema_definitions.rb | 13 +++++ .../postgresql/schema_statements.rb | 44 +++++++++++++- .../connection_adapters/postgresql_adapter.rb | 4 ++ .../test/cases/migration/foreign_key_test.rb | 68 ++++++++++++++++++++++ 8 files changed, 152 insertions(+), 1 deletion(-) (limited to 'activerecord') diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb index b66009bdc6..88ef8e0819 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb @@ -87,6 +87,11 @@ module ActiveRecord options[:primary_key] != default_primary_key end + def validate? + options.fetch(:validate, true) + end + alias validated? validate? + def defined_for?(to_table_ord = nil, to_table: nil, **options) if to_table_ord self.to_table == to_table_ord.to_s diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb index 7ae32d0ced..09ccf0c193 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb @@ -964,6 +964,8 @@ module ActiveRecord # Action that happens ON DELETE. Valid values are +:nullify+, +:cascade+ and +:restrict+ # [:on_update] # Action that happens ON UPDATE. Valid values are +:nullify+, +:cascade+ and +:restrict+ + # [:validate] + # (Postgres only) Specify whether or not the constraint should be validated. Defaults to +true+. def add_foreign_key(from_table, to_table, options = {}) return unless supports_foreign_keys? diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb index 8993c517a6..fc80d332f9 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb @@ -272,6 +272,11 @@ module ActiveRecord false end + # Does this adapter support creating invalid constraints? + def supports_validate_constraints? + false + end + # Does this adapter support creating foreign key constraints # in the same statement as creating the table? def supports_foreign_keys_in_create? diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/schema_creation.rb b/activerecord/lib/active_record/connection_adapters/postgresql/schema_creation.rb index 59f661da25..8e381a92cf 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_creation.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_creation.rb @@ -5,6 +5,18 @@ module ActiveRecord module PostgreSQL class SchemaCreation < AbstractAdapter::SchemaCreation # :nodoc: private + def visit_AlterTable(o) + super << o.constraint_validations.map { |fk| visit_ValidateConstraint fk }.join(" ") + end + + def visit_AddForeignKey(o) + super.dup.tap { |sql| sql << " NOT VALID" unless o.validate? } + end + + def visit_ValidateConstraint(name) + "VALIDATE CONSTRAINT #{quote_column_name(name)}" + end + def add_column_options!(sql, options) if options[:collation] sql << " COLLATE \"#{options[:collation]}\"" diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/schema_definitions.rb b/activerecord/lib/active_record/connection_adapters/postgresql/schema_definitions.rb index 75622eb304..a3bb66bf3e 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_definitions.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_definitions.rb @@ -192,6 +192,19 @@ module ActiveRecord class Table < ActiveRecord::ConnectionAdapters::Table include ColumnMethods end + + class AlterTable < ActiveRecord::ConnectionAdapters::AlterTable + attr_reader :constraint_validations + + def initialize(td) + super + @constraint_validations = [] + end + + def validate_constraint(name) + @constraint_validations << name + end + end end end end 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 15a1dfd102..38102b2e7a 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -507,7 +507,7 @@ module ActiveRecord def foreign_keys(table_name) scope = quoted_scope(table_name) fk_info = exec_query(<<-SQL.strip_heredoc, "SCHEMA") - SELECT t2.oid::regclass::text AS to_table, a1.attname AS column, a2.attname AS primary_key, c.conname AS name, c.confupdtype AS on_update, c.confdeltype AS on_delete + SELECT t2.oid::regclass::text AS to_table, a1.attname AS column, a2.attname AS primary_key, c.conname AS name, c.confupdtype AS on_update, c.confdeltype AS on_delete, c.convalidated AS valid FROM pg_constraint c JOIN pg_class t1 ON c.conrelid = t1.oid JOIN pg_class t2 ON c.confrelid = t2.oid @@ -529,6 +529,7 @@ module ActiveRecord options[:on_delete] = extract_foreign_key_action(row["on_delete"]) options[:on_update] = extract_foreign_key_action(row["on_update"]) + options[:validate] = row["valid"] ForeignKeyDefinition.new(table_name, row["to_table"], options) end @@ -589,6 +590,43 @@ module ActiveRecord PostgreSQL::SchemaDumper.create(self, options) end + # Validates the given constraint. + # + # Validates the constraint named +constraint_name+ on +accounts+. + # + # validate_foreign_key :accounts, :constraint_name + def validate_constraint(table_name, constraint_name) + return unless supports_validate_constraints? + + at = create_alter_table table_name + at.validate_constraint constraint_name + + execute schema_creation.accept(at) + end + + # Validates the given foreign key. + # + # Validates the foreign key on +accounts.branch_id+. + # + # validate_foreign_key :accounts, :branches + # + # Validates the foreign key on +accounts.owner_id+. + # + # validate_foreign_key :accounts, column: :owner_id + # + # Validates the foreign key named +special_fk_name+ on the +accounts+ table. + # + # validate_foreign_key :accounts, name: :special_fk_name + # + # The +options+ hash accepts the same keys as SchemaStatements#add_foreign_key. + def validate_foreign_key(from_table, options_or_to_table = {}) + return unless supports_validate_constraints? + + fk_name_to_validate = foreign_key_for!(from_table, options_or_to_table).name + + validate_constraint from_table, fk_name_to_validate + end + private def schema_creation PostgreSQL::SchemaCreation.new(self) @@ -598,6 +636,10 @@ module ActiveRecord PostgreSQL::TableDefinition.new(*args) end + def create_alter_table(name) + PostgreSQL::AlterTable.new create_table_definition(name) + end + def new_column_from_field(table_name, field) column_name, type, default, notnull, oid, fmod, collation, comment = field type_metadata = fetch_type_metadata(column_name, type, oid.to_i, fmod.to_i) diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 9397481c4c..68b79f777e 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -142,6 +142,10 @@ module ActiveRecord true end + def supports_validate_constraints? + true + end + def supports_views? true end diff --git a/activerecord/test/cases/migration/foreign_key_test.rb b/activerecord/test/cases/migration/foreign_key_test.rb index 499d072de5..079be04946 100644 --- a/activerecord/test/cases/migration/foreign_key_test.rb +++ b/activerecord/test/cases/migration/foreign_key_test.rb @@ -227,6 +227,74 @@ if ActiveRecord::Base.connection.supports_foreign_keys? end end + if ActiveRecord::Base.connection.supports_validate_constraints? + def test_add_invalid_foreign_key + @connection.add_foreign_key :astronauts, :rockets, column: "rocket_id", validate: false + + foreign_keys = @connection.foreign_keys("astronauts") + assert_equal 1, foreign_keys.size + + fk = foreign_keys.first + refute fk.validated? + end + + def test_validate_foreign_key_infers_column + @connection.add_foreign_key :astronauts, :rockets, validate: false + refute @connection.foreign_keys("astronauts").first.validated? + + @connection.validate_foreign_key :astronauts, :rockets + assert @connection.foreign_keys("astronauts").first.validated? + end + + def test_validate_foreign_key_by_column + @connection.add_foreign_key :astronauts, :rockets, column: "rocket_id", validate: false + refute @connection.foreign_keys("astronauts").first.validated? + + @connection.validate_foreign_key :astronauts, column: "rocket_id" + assert @connection.foreign_keys("astronauts").first.validated? + end + + def test_validate_foreign_key_by_symbol_column + @connection.add_foreign_key :astronauts, :rockets, column: :rocket_id, validate: false + refute @connection.foreign_keys("astronauts").first.validated? + + @connection.validate_foreign_key :astronauts, column: :rocket_id + assert @connection.foreign_keys("astronauts").first.validated? + end + + def test_validate_foreign_key_by_name + @connection.add_foreign_key :astronauts, :rockets, column: "rocket_id", name: "fancy_named_fk", validate: false + refute @connection.foreign_keys("astronauts").first.validated? + + @connection.validate_foreign_key :astronauts, name: "fancy_named_fk" + assert @connection.foreign_keys("astronauts").first.validated? + end + + def test_validate_foreign_non_existing_foreign_key_raises + assert_raises ArgumentError do + @connection.validate_foreign_key :astronauts, :rockets + end + end + + def test_validate_constraint_by_name + @connection.add_foreign_key :astronauts, :rockets, column: "rocket_id", name: "fancy_named_fk", validate: false + + @connection.validate_constraint :astronauts, "fancy_named_fk" + assert @connection.foreign_keys("astronauts").first.validated? + end + else + # Foreign key should still be created, but should not be invalid + def test_add_invalid_foreign_key + @connection.add_foreign_key :astronauts, :rockets, column: "rocket_id", validate: false + + foreign_keys = @connection.foreign_keys("astronauts") + assert_equal 1, foreign_keys.size + + fk = foreign_keys.first + assert fk.validated? + end + end + def test_schema_dumping @connection.add_foreign_key :astronauts, :rockets output = dump_table_schema "astronauts" -- cgit v1.2.3