Merge pull request #29504 from kirs/fixtures-arel-bulk

Use bulk INSERT to insert fixtures

4 files changed, 74 insertions, 9 deletions

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)
diff --git a/activerecord/lib/active_record/fixtures.rb b/activerecord/lib/active_record/fixtures.rb
index a6b66c91e3..e9acb8acae 100644
--- a/activerecord/lib/active_record/fixtures.rb
+++ b/activerecord/lib/active_record/fixtures.rb
@@ -567,9 +567,7 @@ module ActiveRecord
               end
 
               table_rows.each do |fixture_set_name, rows|
-                rows.each do |row|
-                  conn.insert_fixture(row, fixture_set_name)
-                end
+                conn.insert_fixtures(rows, fixture_set_name)
               end
 
               # Cap primary key sequences to max(pk).