From 4ee42379cc30a07a7d47b7be8c710dba0efa407a Mon Sep 17 00:00:00 2001
From: Kir Shatrov <shatrov@me.com>
Date: Sun, 18 Jun 2017 11:14:52 -0400
Subject: Use bulk INSERT to insert fixtures

Improves the performance from O(n) to O(1).
Previously it would require 50 queries to
insert 50 fixtures. Now it takes only one query.

Disabled on sqlite which doesn't support multiple inserts.
---
 .../abstract/database_statements.rb                | 56 +++++++++++++++++++---
 .../connection_adapters/abstract_mysql_adapter.rb  | 17 +++++++
 .../connection_adapters/sqlite3_adapter.rb         |  6 +++
 3 files changed, 73 insertions(+), 6 deletions(-)

(limited to 'activerecord/lib/active_record/connection_adapters')

diff --git a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
index 56f0d6d1f4..dc766ce4fc 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
@@ -296,6 +296,9 @@ module ActiveRecord
 
       # Inserts the given fixture into the table. Overridden in adapters that require
       # something beyond a simple insert (eg. Oracle).
+      # Most of adapters should implement `insert_fixtures` that leverages bulk SQL insert.
+      # We keep this method to provide fallback
+      # for databases like sqlite that do not support bulk inserts.
       def insert_fixture(fixture, table_name)
         fixture = fixture.stringify_keys
 
@@ -312,12 +315,7 @@ module ActiveRecord
         table = Arel::Table.new(table_name)
 
         values = binds.map do |bind|
-          value = bind.value_for_database
-          begin
-            quote(value)
-          rescue TypeError
-            value = YAML.dump(value)
-          end
+          value = with_yaml_fallback(bind.value_for_database)
           [table[bind.name], value]
         end
 
@@ -327,6 +325,40 @@ module ActiveRecord
         execute manager.to_sql, "Fixture Insert"
       end
 
+      # Inserts a set of fixtures into the table. Overridden in adapters that require
+      # something beyond a simple insert (eg. Oracle).
+      def insert_fixtures(fixtures, table_name)
+        return if fixtures.empty?
+
+        columns = schema_cache.columns_hash(table_name)
+
+        values = fixtures.map do |fixture|
+          fixture = fixture.stringify_keys
+
+          unknown_columns = fixture.keys - columns.keys
+          if unknown_columns.any?
+            raise Fixture::FixtureError, %(table "#{table_name}" has no columns named #{unknown_columns.map(&:inspect).join(', ')}.)
+          end
+
+          columns.map do |name, column|
+            if fixture.key?(name)
+              type = lookup_cast_type_from_column(column)
+              bind = Relation::QueryAttribute.new(name, fixture[name], type)
+              with_yaml_fallback(bind.value_for_database)
+            else
+              Arel.sql("DEFAULT")
+            end
+          end
+        end
+
+        table = Arel::Table.new(table_name)
+        manager = Arel::InsertManager.new
+        manager.into(table)
+        columns.each_key { |column| manager.columns << table[column] }
+        manager.values = manager.create_values_list(values)
+        execute manager.to_sql, "Fixtures Insert"
+      end
+
       def empty_insert_statement_value
         "DEFAULT VALUES"
       end
@@ -388,6 +420,18 @@ module ActiveRecord
           end
           [relation, binds]
         end
+
+        # Fixture value is quoted by Arel, however scalar values
+        # are not quotable. In this case we want to convert
+        # the column value to YAML.
+        def with_yaml_fallback(value)
+          begin
+            quote(value)
+          rescue TypeError
+            value = YAML.dump(value)
+          end
+          value
+        end
     end
   end
 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 648c869915..c42e80ea2c 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -526,8 +526,25 @@ module ActiveRecord
         index.using == :btree || super
       end
 
+      def insert_fixtures(*)
+        without_sql_mode("NO_AUTO_VALUE_ON_ZERO") { super }
+      end
+
       private
 
+        def without_sql_mode(mode)
+          result = execute("SELECT @@SESSION.sql_mode")
+          current_mode = result.first[0]
+          return yield unless current_mode.include?(mode)
+
+          sql_mode = "REPLACE(@@sql_mode, '#{mode}', '')"
+          execute("SET @@SESSION.sql_mode = #{sql_mode}")
+          yield
+        ensure
+          sql_mode = "CONCAT(@@sql_mode, ',#{mode}')"
+          execute("SET @@SESSION.sql_mode = #{sql_mode}")
+        end
+
         def initialize_type_map(m)
           super
 
diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
index 7233325d5a..ee2faf43b5 100644
--- a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
@@ -349,6 +349,12 @@ module ActiveRecord
         end
       end
 
+      def insert_fixtures(rows, table_name)
+        rows.each do |row|
+          insert_fixture(row, table_name)
+        end
+      end
+
       private
 
         def table_structure(table_name)
-- 
cgit v1.2.3