diff options
author | Rafael Mendonça França <rafaelmfranca@gmail.com> | 2014-03-26 17:26:20 -0300 |
---|---|---|
committer | Rafael Mendonça França <rafaelmfranca@gmail.com> | 2014-03-26 17:26:20 -0300 |
commit | fb522a27731e04d149abe68809fa030382931bc3 (patch) | |
tree | c3f70cd34da8df0a6a515e6ce4f189f16ec36828 | |
parent | 9ed0cf51b467907810ef3959c8e9cdf77370370e (diff) | |
parent | 63c94efccb20c0b398dd90c0d209d6894eb22d92 (diff) | |
download | rails-fb522a27731e04d149abe68809fa030382931bc3.tar.gz rails-fb522a27731e04d149abe68809fa030382931bc3.tar.bz2 rails-fb522a27731e04d149abe68809fa030382931bc3.zip |
Merge pull request #14480 from steverice/mysql-indexes-in-create-table
Create indexes inline in CREATE TABLE for MySQL
6 files changed, 80 insertions, 14 deletions
diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md index 6e77493de9..ccd07ce248 100644 --- a/activerecord/CHANGELOG.md +++ b/activerecord/CHANGELOG.md @@ -1,3 +1,18 @@ +* Create indexes inline in CREATE TABLE for MySQL + + This is important, because adding an index on a temporary table after it has been created + would commit the transaction. + It also allows creating and dropping indexed tables with fewer queries and fewer permissions required. + + Example: + + create_table :temp, temporary: true, as: "SELECT id, name, zip FROM a_really_complicated_query" do |t| + t.index :zip + end + # => CREATE TEMPORARY TABLE temp (INDEX (zip)) AS SELECT id, name, zip FROM a_really_complicated_query + + *Cody Cutrer*, *Steve Rice* + * Save `has_one` association even if the record doesn't changed. Fixes #14407. 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 7f530ec5e4..23404a5c48 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb @@ -186,24 +186,23 @@ module ActiveRecord def create_table(table_name, options = {}) td = create_table_definition table_name, options[:temporary], options[:options], options[:as] - if !options[:as] - unless options[:id] == false - pk = options.fetch(:primary_key) { - Base.get_primary_key table_name.to_s.singularize - } - - td.primary_key pk, options.fetch(:id, :primary_key), options - end + unless options[:id] == false || options[:as] + pk = options.fetch(:primary_key) { + Base.get_primary_key table_name.to_s.singularize + } - yield td if block_given? + td.primary_key pk, options.fetch(:id, :primary_key), options end + yield td if block_given? + if options[:force] && table_exists?(table_name) drop_table(table_name, options) end - execute schema_creation.accept td - td.indexes.each_pair { |c,o| add_index table_name, c, o } + result = execute schema_creation.accept td + td.indexes.each_pair { |c,o| add_index table_name, c, o } unless supports_indexes_in_create? + result end # Creates a new join table with the name created using the lexical order of the first two @@ -796,7 +795,7 @@ module ActiveRecord if index_name.length > max_index_length raise ArgumentError, "Index name '#{index_name}' on table '#{table_name}' is too long; the limit is #{max_index_length} characters" end - if index_name_exists?(table_name, index_name, false) + if table_exists?(table_name) && index_name_exists?(table_name, index_name, false) raise ArgumentError, "Index name '#{index_name}' on table '#{table_name}' already exists" end index_columns = quoted_columns_for_index(column_names, options).join(", ") diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb index c7e5a18f02..543eeb5740 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb @@ -217,6 +217,12 @@ module ActiveRecord false end + # Does this adapter support creating indexes in the same statement as + # creating the table? As of this writing, only mysql does. + def supports_indexes_in_create? + false + end + # This is meant to be implemented by the adapters that support extensions def disable_extension(name) end 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 9a819720f7..a1748c9261 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb @@ -6,6 +6,17 @@ module ActiveRecord include Savepoints class SchemaCreation < AbstractAdapter::SchemaCreation + def visit_TableDefinition(o) + create_sql = "CREATE#{' TEMPORARY' if o.temporary} TABLE " + create_sql << "#{quote_table_name(o.name)} " + statements = [] + statements.concat(o.columns.map { |c| accept c }) + statements.concat(o.indexes.map { |(column_name, options)| index_in_create(o.name, column_name, options) }) + create_sql << "(#{statements.join(', ')}) " if statements.present? + create_sql << "#{o.options}" + create_sql << " AS #{@conn.to_sql(o.as)}" if o.as + create_sql + end def visit_AddColumn(o) add_column_position!(super, column_options(o)) @@ -29,6 +40,11 @@ module ActiveRecord end sql end + + def index_in_create(table_name, column_name, options) + index_name, index_type, index_columns, index_options, index_algorithm, index_using = @conn.send(:add_index_options, table_name, column_name, options) + "#{index_type} INDEX #{quote_column_name(index_name)} #{index_using} (#{index_columns})#{index_options} #{index_algorithm}".gsub(' ', ' ').strip + end end def schema_creation @@ -225,6 +241,10 @@ module ActiveRecord version[0] >= 5 end + def supports_indexes_in_create? + true + end + def native_database_types NATIVE_DATABASE_TYPES end diff --git a/activerecord/test/cases/adapters/mysql/active_schema_test.rb b/activerecord/test/cases/adapters/mysql/active_schema_test.rb index e77138eb1d..35681fb5ae 100644 --- a/activerecord/test/cases/adapters/mysql/active_schema_test.rb +++ b/activerecord/test/cases/adapters/mysql/active_schema_test.rb @@ -17,7 +17,8 @@ class ActiveSchemaTest < ActiveRecord::TestCase end def test_add_index - # add_index calls index_name_exists? which can't work since execute is stubbed + # add_index calls table_exists? and index_name_exists? which can't work since execute is stubbed + def (ActiveRecord::Base.connection).table_exists?(*); true; end def (ActiveRecord::Base.connection).index_name_exists?(*); false; end expected = "CREATE INDEX `index_people_on_last_name` ON `people` (`last_name`) " @@ -116,6 +117,18 @@ class ActiveSchemaTest < ActiveRecord::TestCase end end + def test_indexes_in_create + begin + ActiveRecord::Base.connection.stubs(:table_exists?).with(:temp).returns(false) + ActiveRecord::Base.connection.stubs(:index_name_exists?).with(:index_temp_on_zip).returns(false) + expected = "CREATE TEMPORARY TABLE `temp` (INDEX `index_temp_on_zip` (`zip`)) ENGINE=InnoDB AS SELECT id, name, zip FROM a_really_complicated_query" + actual = ActiveRecord::Base.connection.create_table(:temp, temporary: true, as: "SELECT id, name, zip FROM a_really_complicated_query") do |t| + t.index :zip + end + assert_equal expected, actual + end + end + private def with_real_execute ActiveRecord::Base.connection.singleton_class.class_eval do diff --git a/activerecord/test/cases/adapters/mysql2/active_schema_test.rb b/activerecord/test/cases/adapters/mysql2/active_schema_test.rb index 7905edaf83..925b9c1bd1 100644 --- a/activerecord/test/cases/adapters/mysql2/active_schema_test.rb +++ b/activerecord/test/cases/adapters/mysql2/active_schema_test.rb @@ -17,7 +17,8 @@ class ActiveSchemaTest < ActiveRecord::TestCase end def test_add_index - # add_index calls index_name_exists? which can't work since execute is stubbed + # add_index calls table_exists? and index_name_exists? which can't work since execute is stubbed + def (ActiveRecord::Base.connection).table_exists?(*); true; end def (ActiveRecord::Base.connection).index_name_exists?(*); false; end expected = "CREATE INDEX `index_people_on_last_name` ON `people` (`last_name`) " @@ -116,6 +117,18 @@ class ActiveSchemaTest < ActiveRecord::TestCase end end + def test_indexes_in_create + begin + ActiveRecord::Base.connection.stubs(:table_exists?).with(:temp).returns(false) + ActiveRecord::Base.connection.stubs(:index_name_exists?).with(:index_temp_on_zip).returns(false) + expected = "CREATE TEMPORARY TABLE `temp` (INDEX `index_temp_on_zip` (`zip`)) ENGINE=InnoDB AS SELECT id, name, zip FROM a_really_complicated_query" + actual = ActiveRecord::Base.connection.create_table(:temp, temporary: true, as: "SELECT id, name, zip FROM a_really_complicated_query") do |t| + t.index :zip + end + assert_equal expected, actual + end + end + private def with_real_execute ActiveRecord::Base.connection.singleton_class.class_eval do |