From 75a2e4a8dff8c01dad913048d3676b993a8e23a2 Mon Sep 17 00:00:00 2001 From: Cody Cutrer Date: Fri, 13 Dec 2013 15:20:03 -0700 Subject: support creating temporary tables from queries also override drop_table in AbstractMySQLAdapter to properly drop temporary tables without committing the transaction --- activerecord/CHANGELOG.md | 10 +++++++ .../abstract/schema_creation.rb | 7 +++-- .../abstract/schema_definitions.rb | 5 ++-- .../abstract/schema_statements.rb | 35 +++++++++++++++------- .../connection_adapters/abstract_mysql_adapter.rb | 4 +++ .../connection_adapters/postgresql_adapter.rb | 4 +-- .../test/cases/adapters/mysql2/schema_test.rb | 9 ++++++ activerecord/test/cases/migration_test.rb | 26 ++++++++++++++++ 8 files changed, 83 insertions(+), 17 deletions(-) diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md index 4845150a24..fc868ecaea 100644 --- a/activerecord/CHANGELOG.md +++ b/activerecord/CHANGELOG.md @@ -1,3 +1,13 @@ +* Respect temporary option when dropping tables with MySQL + + Normal DROP TABLE also works, but commits the transaction. + + *Cody Cutrer* + +* Add option to create tables from a query. + + *Cody Cutrer* + * Create a whitelist of delegable methods to `Array`. Currently `Relation` directly delegates methods to `Array`. With this change, diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb index 7c330a2f25..a51691bfa8 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb @@ -34,9 +34,10 @@ module ActiveRecord def visit_TableDefinition(o) create_sql = "CREATE#{' TEMPORARY' if o.temporary} TABLE " - create_sql << "#{quote_table_name(o.name)} (" - create_sql << o.columns.map { |c| accept c }.join(', ') - create_sql << ") #{o.options}" + create_sql << "#{quote_table_name(o.name)} " + create_sql << "(#{o.columns.map { |c| accept c }.join(', ')}) " unless o.as + create_sql << "#{o.options}" + create_sql << " AS #{@conn.to_sql(o.as)}" if o.as create_sql end 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 063b19871a..c39bf15e83 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb @@ -49,14 +49,15 @@ module ActiveRecord # An array of ColumnDefinition objects, representing the column changes # that have been defined. attr_accessor :indexes - attr_reader :name, :temporary, :options + attr_reader :name, :temporary, :options, :as - def initialize(types, name, temporary, options) + def initialize(types, name, temporary, options, as = nil) @columns_hash = {} @indexes = {} @native = types @temporary = temporary @options = options + @as = as @name = name end 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 51a6929dab..e8ea96c89f 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb @@ -131,6 +131,9 @@ module ActiveRecord # [:force] # Set to true to drop the table before creating it. # Defaults to false. + # [:as] + # SQL to use to generate the table. When this option is used, the block is + # ignored, as are the :id and :primary_key options. # # ====== Add a backend specific option to the generated SQL (MySQL) # @@ -169,19 +172,31 @@ module ActiveRecord # supplier_id int # ) # + # ====== Create a temporary table based on a query + # + # create_table(:long_query, temporary: true, + # as: "SELECT * FROM orders INNER JOIN line_items ON order_id=orders.id") + # + # generates: + # + # CREATE TEMPORARY TABLE long_query AS + # SELECT * FROM orders INNER JOIN line_items ON order_id=orders.id + # # See also TableDefinition#column for details on how to create columns. def create_table(table_name, options = {}) - td = create_table_definition table_name, options[:temporary], options[:options] + td = create_table_definition table_name, options[:temporary], options[:options], options[:as] - unless options[:id] == false - pk = options.fetch(:primary_key) { - Base.get_primary_key table_name.to_s.singularize - } + 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 + td.primary_key pk, options.fetch(:id, :primary_key), options + end - yield td if block_given? + yield td if block_given? + end if options[:force] && table_exists?(table_name) drop_table(table_name, options) @@ -826,8 +841,8 @@ module ActiveRecord end private - def create_table_definition(name, temporary, options) - TableDefinition.new native_database_types, name, temporary, options + def create_table_definition(name, temporary, options, as = nil) + TableDefinition.new native_database_types, name, temporary, options, as end def create_alter_table(name) 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 1b9f865666..59a788397e 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb @@ -492,6 +492,10 @@ module ActiveRecord rename_table_indexes(table_name, new_name) end + def drop_table(table_name, options = {}) + execute "DROP#{' TEMPORARY' if options[:temporary]} TABLE #{quote_table_name(table_name)}" + end + def rename_index(table_name, old_name, new_name) if (version[0] == 5 && version[1] >= 7) || version[0] >= 6 execute "ALTER TABLE #{quote_table_name(table_name)} RENAME INDEX #{quote_table_name(old_name)} TO #{quote_table_name(new_name)}" diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index d23a24589c..2912a208ee 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -973,8 +973,8 @@ module ActiveRecord $1.strip if $1 end - def create_table_definition(name, temporary, options) - TableDefinition.new native_database_types, name, temporary, options + def create_table_definition(name, temporary, options, as = nil) + TableDefinition.new native_database_types, name, temporary, options, as end def update_table_definition(table_name, base) diff --git a/activerecord/test/cases/adapters/mysql2/schema_test.rb b/activerecord/test/cases/adapters/mysql2/schema_test.rb index 5db60ff8a0..43c9116b5a 100644 --- a/activerecord/test/cases/adapters/mysql2/schema_test.rb +++ b/activerecord/test/cases/adapters/mysql2/schema_test.rb @@ -65,6 +65,15 @@ module ActiveRecord assert_nil index_c.using assert_equal :fulltext, index_c.type end + + def test_drop_temporary_table + @connection.transaction do + @connection.create_table(:temp_table, temporary: true) + # if it doesn't properly say DROP TEMPORARY TABLE, the transaction commit + # will complain that no transaction is active + @connection.drop_table(:temp_table, temporary: true) + end + end end end end diff --git a/activerecord/test/cases/migration_test.rb b/activerecord/test/cases/migration_test.rb index 519045095d..ebcc778b8b 100644 --- a/activerecord/test/cases/migration_test.rb +++ b/activerecord/test/cases/migration_test.rb @@ -443,6 +443,32 @@ class MigrationTest < ActiveRecord::TestCase Person.connection.drop_table :binary_testings rescue nil end + def test_create_table_with_query + Person.connection.drop_table :table_from_query_testings rescue nil + Person.connection.create_table(:person, force: true) + + Person.connection.create_table :table_from_query_testings, as: "SELECT id FROM person" + + columns = Person.connection.columns(:table_from_query_testings) + assert_equal 1, columns.length + assert_equal "id", columns.first.name + + Person.connection.drop_table :table_from_query_testings rescue nil + end + + def test_create_table_with_query_from_relation + Person.connection.drop_table :table_from_query_testings rescue nil + Person.connection.create_table(:person, force: true) + + Person.connection.create_table :table_from_query_testings, as: Person.select(:id) + + columns = Person.connection.columns(:table_from_query_testings) + assert_equal 1, columns.length + assert_equal "id", columns.first.name + + Person.connection.drop_table :table_from_query_testings rescue nil + end + if current_adapter? :OracleAdapter def test_create_table_with_custom_sequence_name # table name is 29 chars, the standard sequence name will -- cgit v1.2.3