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/abstract_mysql_adapter.rb | 44 ++++++++++++++++++++++ 1 file changed, 44 insertions(+) (limited to 'activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb') diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb index def04dbed2..431db591e6 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb @@ -192,6 +192,10 @@ module ActiveRecord true end + def supports_foreign_keys? + true + end + def native_database_types NATIVE_DATABASE_TYPES end @@ -501,6 +505,46 @@ module ActiveRecord execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} #{index_using} ON #{quote_table_name(table_name)} (#{index_columns})#{index_options} #{index_algorithm}" end + def foreign_keys(table_name) + fk_info = select_all %{ + SELECT fk.referenced_table_name as 'to_table' + ,fk.referenced_column_name as 'primary_key' + ,fk.column_name as 'column' + ,fk.constraint_name as 'name' + FROM information_schema.key_column_usage fk + WHERE fk.referenced_column_name is not null + AND fk.table_schema = '#{@config[:database]}' + AND fk.table_name = '#{table_name}' + } + + create_table_info = select_one("SHOW CREATE TABLE #{quote_table_name(table_name)}")["Create Table"] + + 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" + 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 FOREIGN KEY #{foreign_key_name} + SQL + end + # Maps logical Rails types to MySQL-specific data types. def type_to_sql(type, limit = nil, precision = nil, scale = nil) case type.to_s -- 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`. --- .../connection_adapters/abstract_mysql_adapter.rb | 24 ++++------------------ 1 file changed, 4 insertions(+), 20 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb') diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb index 431db591e6..6ba226765c 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb @@ -10,6 +10,10 @@ module ActiveRecord add_column_position!(super, column_options(o)) end + def visit_DropForeignKey(name) + "DROP FOREIGN KEY #{name}" + end + private def visit_TableDefinition(o) @@ -525,26 +529,6 @@ module ActiveRecord 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 FOREIGN KEY #{foreign_key_name} - SQL - end - # Maps logical Rails types to MySQL-specific data types. def type_to_sql(type, limit = nil, precision = nil, scale = nil) case type.to_s -- 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/abstract_mysql_adapter.rb | 14 +++++++++++++- 1 file changed, 13 insertions(+), 1 deletion(-) (limited to 'activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb') diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb index 6ba226765c..9610296043 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb @@ -524,7 +524,19 @@ module ActiveRecord create_table_info = select_one("SHOW CREATE TABLE #{quote_table_name(table_name)}")["Create Table"] fk_info.map do |row| - options = {column: row['column'], name: row['name'], primary_key: row['primary_key']} + options = { + column: row['column'], + name: row['name'], + primary_key: row['primary_key'] + } + + if create_table_info =~ /CONSTRAINT #{quote_column_name(row['name'])} FOREIGN KEY .* REFERENCES .* ON DELETE (CASCADE|SET NULL|RESTRICT)/ + options[:dependent] = case $1 + when 'CASCADE' then :delete + when 'SET NULL' then :nullify + end + 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` --- .../lib/active_record/connection_adapters/abstract_mysql_adapter.rb | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb') diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb index 9610296043..f3b7fe172e 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb @@ -531,8 +531,8 @@ module ActiveRecord } if create_table_info =~ /CONSTRAINT #{quote_column_name(row['name'])} FOREIGN KEY .* REFERENCES .* ON DELETE (CASCADE|SET NULL|RESTRICT)/ - options[:dependent] = case $1 - when 'CASCADE' then :delete + options[:on_delete] = case $1 + when 'CASCADE' then :cascade when 'SET NULL' then :nullify end 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 --- .../connection_adapters/abstract_mysql_adapter.rb | 17 +++++++++++------ 1 file changed, 11 insertions(+), 6 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb') diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb index f3b7fe172e..868181e677 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb @@ -530,17 +530,22 @@ module ActiveRecord primary_key: row['primary_key'] } - if create_table_info =~ /CONSTRAINT #{quote_column_name(row['name'])} FOREIGN KEY .* REFERENCES .* ON DELETE (CASCADE|SET NULL|RESTRICT)/ - options[:on_delete] = case $1 - when 'CASCADE' then :cascade - when 'SET NULL' then :nullify - end - end + options[:on_update] = extract_foreign_key_action(create_table_info, row['name'], "UPDATE") + options[:on_delete] = extract_foreign_key_action(create_table_info, row['name'], "DELETE") ForeignKeyDefinition.new(table_name, row['to_table'], options) end end + def extract_foreign_key_action(structure, name, action) # :nodoc: + if structure =~ /CONSTRAINT #{quote_column_name(name)} FOREIGN KEY .* REFERENCES .* ON #{action} (CASCADE|SET NULL|RESTRICT)/ + case $1 + when 'CASCADE'; :cascade + when 'SET NULL'; :nullify + end + end + end + # Maps logical Rails types to MySQL-specific data types. def type_to_sql(type, limit = nil, precision = nil, scale = nil) case type.to_s -- 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. --- .../connection_adapters/abstract_mysql_adapter.rb | 22 +++++++++++----------- 1 file changed, 11 insertions(+), 11 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb') diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb index 868181e677..4924f345fc 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb @@ -510,7 +510,7 @@ module ActiveRecord end def foreign_keys(table_name) - fk_info = select_all %{ + fk_info = select_all <<-SQL.strip_heredoc SELECT fk.referenced_table_name as 'to_table' ,fk.referenced_column_name as 'primary_key' ,fk.column_name as 'column' @@ -519,7 +519,7 @@ module ActiveRecord WHERE fk.referenced_column_name is not null AND fk.table_schema = '#{@config[:database]}' AND fk.table_name = '#{table_name}' - } + SQL create_table_info = select_one("SHOW CREATE TABLE #{quote_table_name(table_name)}")["Create Table"] @@ -537,15 +537,6 @@ module ActiveRecord end end - def extract_foreign_key_action(structure, name, action) # :nodoc: - if structure =~ /CONSTRAINT #{quote_column_name(name)} FOREIGN KEY .* REFERENCES .* ON #{action} (CASCADE|SET NULL|RESTRICT)/ - case $1 - when 'CASCADE'; :cascade - when 'SET NULL'; :nullify - end - end - end - # Maps logical Rails types to MySQL-specific data types. def type_to_sql(type, limit = nil, precision = nil, scale = nil) case type.to_s @@ -824,6 +815,15 @@ module ActiveRecord # ...and send them all in one query @connection.query "SET #{encoding} #{variable_assignments}" end + + def extract_foreign_key_action(structure, name, action) # :nodoc: + if structure =~ /CONSTRAINT #{quote_column_name(name)} FOREIGN KEY .* REFERENCES .* ON #{action} (CASCADE|SET NULL|RESTRICT)/ + case $1 + when 'CASCADE'; :cascade + when 'SET NULL'; :nullify + end + end + end end end end -- cgit v1.2.3