From e2ef25710682d884b2e6f5e99d47f18eb7083c68 Mon Sep 17 00:00:00 2001 From: Yves Senn Date: Fri, 6 Jun 2014 17:24:46 +0200 Subject: fk: `add_foreign_key` and `remove_foreign_key` for PostgreSQL adapter. --- .../postgresql/schema_statements.rb | 27 ++++++++++++++++++++++ 1 file changed, 27 insertions(+) (limited to 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb') 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 b2aeb3a058..f09ce113d6 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -448,6 +448,33 @@ module ActiveRecord execute "ALTER INDEX #{quote_column_name(old_name)} RENAME TO #{quote_table_name(new_name)}" end + def add_foreign_key(from_table, to_table, options = {}) + foreign_key_column = options.fetch(:column) + referenced_column = "id" + foreign_key_name = foreign_key_name(from_table, options) + execute <<-SQL +ALTER TABLE #{quote_table_name(from_table)} +ADD CONSTRAINT #{foreign_key_name} + FOREIGN KEY (#{quote_column_name(foreign_key_column)}) + REFERENCES #{quote_table_name(to_table)} (#{quote_column_name(referenced_column)}) + SQL + end + + def remove_foreign_key(from_table, options = {}) + foreign_key_name = foreign_key_name(from_table, options) + execute <<-SQL +ALTER TABLE #{quote_table_name(from_table)} +DROP CONSTRAINT #{foreign_key_name} + SQL + end + + def foreign_key_name(table_name, options) + options.fetch(:name) do + column_name = options.fetch(:column) + "#{table_name}_#{column_name}_fk" + end + end + def index_name_length 63 end -- cgit v1.2.3 From 09b3a2847ca51d0e5dcebcb636d8770b19c397a7 Mon Sep 17 00:00:00 2001 From: Yves Senn Date: Tue, 10 Jun 2014 10:59:18 +0200 Subject: fk: add `foreign_keys` for PostgreSQL adapter. --- .../postgresql/schema_statements.rb | 24 ++++++++++++++++++++++ 1 file changed, 24 insertions(+) (limited to 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb') 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 f09ce113d6..b87fb85ae2 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -448,6 +448,30 @@ module ActiveRecord execute "ALTER INDEX #{quote_column_name(old_name)} RENAME TO #{quote_table_name(new_name)}" end + def foreign_keys(table_name) + fk_info = select_all <<-SQL +SELECT t2.relname AS to_table, a1.attname AS column, a2.attname AS primary_key, c.conname AS name, c.confdeltype AS dependency +FROM pg_constraint c +JOIN pg_class t1 ON c.conrelid = t1.oid +JOIN pg_class t2 ON c.confrelid = t2.oid +JOIN pg_attribute a1 ON a1.attnum = c.conkey[1] AND a1.attrelid = t1.oid +JOIN pg_attribute a2 ON a2.attnum = c.confkey[1] AND a2.attrelid = t2.oid +JOIN pg_namespace t3 ON c.connamespace = t3.oid +WHERE c.contype = 'f' + AND t1.relname = #{quote(table_name)} + AND t3.nspname = ANY (current_schemas(false)) +ORDER BY c.conname + SQL + + fk_info.map do |row| + options = { + column: row['column'], + name: row['name'], + primary_key: row['primary_key'] } + ForeignKeyDefinition.new(table_name, row["to_table"], options) + end + end + def add_foreign_key(from_table, to_table, options = {}) foreign_key_column = options.fetch(:column) referenced_column = "id" -- cgit v1.2.3 From 74b2fe4c0febe051cb48c7c25a565333ddf22bce Mon Sep 17 00:00:00 2001 From: Yves Senn Date: Tue, 10 Jun 2014 11:10:54 +0200 Subject: fk: `foreign_keys`, `add_foreign_key` and `remove_foreign_key` for MySQL --- .../connection_adapters/postgresql/schema_statements.rb | 7 ------- 1 file changed, 7 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb') 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 b87fb85ae2..7a93d9cde7 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -492,13 +492,6 @@ DROP CONSTRAINT #{foreign_key_name} SQL end - def foreign_key_name(table_name, options) - options.fetch(:name) do - column_name = options.fetch(:column) - "#{table_name}_#{column_name}_fk" - end - end - def index_name_length 63 end -- cgit v1.2.3 From 1c170fdea2be04691c7daa8266084766fe963fff Mon Sep 17 00:00:00 2001 From: Yves Senn Date: Tue, 10 Jun 2014 11:50:37 +0200 Subject: fk: generalize using `AlterTable` and `SchemaCreation`. --- .../postgresql/schema_statements.rb | 20 -------------------- 1 file changed, 20 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb') 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 7a93d9cde7..c061337e71 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -472,26 +472,6 @@ ORDER BY c.conname end end - def add_foreign_key(from_table, to_table, options = {}) - foreign_key_column = options.fetch(:column) - referenced_column = "id" - foreign_key_name = foreign_key_name(from_table, options) - execute <<-SQL -ALTER TABLE #{quote_table_name(from_table)} -ADD CONSTRAINT #{foreign_key_name} - FOREIGN KEY (#{quote_column_name(foreign_key_column)}) - REFERENCES #{quote_table_name(to_table)} (#{quote_column_name(referenced_column)}) - SQL - end - - def remove_foreign_key(from_table, options = {}) - foreign_key_name = foreign_key_name(from_table, options) - execute <<-SQL -ALTER TABLE #{quote_table_name(from_table)} -DROP CONSTRAINT #{foreign_key_name} - SQL - end - def index_name_length 63 end -- cgit v1.2.3 From 402f303f1d938cf2c7781d7734c4ff8e6b874f35 Mon Sep 17 00:00:00 2001 From: Yves Senn Date: Tue, 10 Jun 2014 14:26:50 +0200 Subject: fk: support dependent option (:delete, :nullify and :restrict). --- .../connection_adapters/postgresql/schema_statements.rb | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) (limited to 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb') 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 c061337e71..7b61ff81ba 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -467,7 +467,14 @@ ORDER BY c.conname options = { column: row['column'], name: row['name'], - primary_key: row['primary_key'] } + primary_key: row['primary_key'] + } + + options[:dependent] = case row['dependency'] + when 'c'; :delete + when 'n'; :nullify + when 'r'; :restrict + end ForeignKeyDefinition.new(table_name, row["to_table"], options) end end -- cgit v1.2.3 From 6955d864ceb0ba994ef4fb4c5e866463f247944b Mon Sep 17 00:00:00 2001 From: Yves Senn Date: Wed, 11 Jun 2014 08:23:17 +0200 Subject: fk: rename `dependent` to `on_delete` --- .../active_record/connection_adapters/postgresql/schema_statements.rb | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb') 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 7b61ff81ba..bf87395ef1 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -470,8 +470,8 @@ ORDER BY c.conname primary_key: row['primary_key'] } - options[:dependent] = case row['dependency'] - when 'c'; :delete + options[:on_delete] = case row['dependency'] + when 'c'; :cascade when 'n'; :nullify when 'r'; :restrict end -- cgit v1.2.3 From acd0287dc18a3fbba6fa4301cb31a7aecd22922b Mon Sep 17 00:00:00 2001 From: Yves Senn Date: Wed, 11 Jun 2014 08:47:53 +0200 Subject: fk: support for on_update --- .../postgresql/schema_statements.rb | 19 ++++++++++++------- 1 file changed, 12 insertions(+), 7 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb') 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 bf87395ef1..7ffbe4434f 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -450,7 +450,7 @@ module ActiveRecord def foreign_keys(table_name) fk_info = select_all <<-SQL -SELECT t2.relname AS to_table, a1.attname AS column, a2.attname AS primary_key, c.conname AS name, c.confdeltype AS dependency +SELECT t2.relname 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 FROM pg_constraint c JOIN pg_class t1 ON c.conrelid = t1.oid JOIN pg_class t2 ON c.confrelid = t2.oid @@ -470,12 +470,17 @@ ORDER BY c.conname primary_key: row['primary_key'] } - options[:on_delete] = case row['dependency'] - when 'c'; :cascade - when 'n'; :nullify - when 'r'; :restrict - end - ForeignKeyDefinition.new(table_name, row["to_table"], options) + options[:on_delete] = extract_foreign_key_action(row['on_delete']) + options[:on_update] = extract_foreign_key_action(row['on_update']) + ForeignKeyDefinition.new(table_name, row['to_table'], options) + end + end + + def extract_foreign_key_action(specifier) # :nodoc: + case specifier + when 'c'; :cascade + when 'n'; :nullify + when 'r'; :restrict end end -- cgit v1.2.3 From 24e1aefb4b2d7b2b4babfd4bae1e9e613283b003 Mon Sep 17 00:00:00 2001 From: Yves Senn Date: Thu, 26 Jun 2014 13:09:45 +0200 Subject: fk: review corrections: indent, visibility, syntax, wording. --- .../postgresql/schema_statements.rb | 24 +++++++++++----------- 1 file changed, 12 insertions(+), 12 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb') 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 7ffbe4434f..30df98be1b 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -449,18 +449,18 @@ module ActiveRecord end def foreign_keys(table_name) - fk_info = select_all <<-SQL -SELECT t2.relname 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 -FROM pg_constraint c -JOIN pg_class t1 ON c.conrelid = t1.oid -JOIN pg_class t2 ON c.confrelid = t2.oid -JOIN pg_attribute a1 ON a1.attnum = c.conkey[1] AND a1.attrelid = t1.oid -JOIN pg_attribute a2 ON a2.attnum = c.confkey[1] AND a2.attrelid = t2.oid -JOIN pg_namespace t3 ON c.connamespace = t3.oid -WHERE c.contype = 'f' - AND t1.relname = #{quote(table_name)} - AND t3.nspname = ANY (current_schemas(false)) -ORDER BY c.conname + fk_info = select_all <<-SQL.strip_heredoc + SELECT t2.relname 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 + FROM pg_constraint c + JOIN pg_class t1 ON c.conrelid = t1.oid + JOIN pg_class t2 ON c.confrelid = t2.oid + JOIN pg_attribute a1 ON a1.attnum = c.conkey[1] AND a1.attrelid = t1.oid + JOIN pg_attribute a2 ON a2.attnum = c.confkey[1] AND a2.attrelid = t2.oid + JOIN pg_namespace t3 ON c.connamespace = t3.oid + WHERE c.contype = 'f' + AND t1.relname = #{quote(table_name)} + AND t3.nspname = ANY (current_schemas(false)) + ORDER BY c.conname SQL fk_info.map do |row| -- cgit v1.2.3