From afa148a4f0ac5e2a446b5fe87881a130e8a24f3d Mon Sep 17 00:00:00 2001 From: Cody Cutrer Date: Tue, 21 Jan 2014 16:56:53 -0700 Subject: 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 Conflicts: activerecord/CHANGELOG.md --- .../abstract/schema_statements.rb | 21 ++++++++++----------- .../connection_adapters/abstract_adapter.rb | 6 ++++++ .../connection_adapters/abstract_mysql_adapter.rb | 20 ++++++++++++++++++++ .../test/cases/adapters/mysql/active_schema_test.rb | 11 +++++++++++ .../cases/adapters/mysql2/active_schema_test.rb | 11 +++++++++++ 5 files changed, 58 insertions(+), 11 deletions(-) (limited to 'activerecord') 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..59210c345f 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 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..96c7d5d7eb 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(', ')}) " + 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..072aa65000 100644 --- a/activerecord/test/cases/adapters/mysql/active_schema_test.rb +++ b/activerecord/test/cases/adapters/mysql/active_schema_test.rb @@ -116,6 +116,17 @@ class ActiveSchemaTest < ActiveRecord::TestCase end end + def test_indexes_in_create + begin + ActiveRecord::Base.connection.stubs(:index_name_exists?).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..91d880e5ae 100644 --- a/activerecord/test/cases/adapters/mysql2/active_schema_test.rb +++ b/activerecord/test/cases/adapters/mysql2/active_schema_test.rb @@ -116,6 +116,17 @@ class ActiveSchemaTest < ActiveRecord::TestCase end end + def test_indexes_in_create + begin + ActiveRecord::Base.connection.stubs(:index_name_exists?).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 -- cgit v1.2.3