phinx icon indicating copy to clipboard operation
phinx copied to clipboard

bulkinsert should execute multiple queries if too many parameters

Open MasterOdin opened this issue 9 months ago • 1 comments

Some adapters have a maximum value of allowed parameters per query. For example, for sqlite, per their docs:

To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999 for SQLite versions prior to 3.32.0 (2020-05-22) or 32766 for SQLite versions after 3.32.0.

During bulkinsert, phinx should be able to detect if it's going to exceed the maximum value for a given adapter, and if so, split up the singular query into several smaller ones (where we execute up to the max number of parameters).

As a first pass to keep things efficient in terms of processing, we can just do a simple count of columns in the first row, and divide that from our max to get the number of rows we can do per bulk insert. It's possible that given usage of Literal values we might end up below the max value by some amount, that's imo fine for the sake of simplicity.

MasterOdin avatar May 04 '25 23:05 MasterOdin

This is an example migration that triggers an error for sqlite:

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Numbers extends AbstractMigration
{
    /**
     * Change Method.
     *
     * Write your reversible migrations using this method.
     *
     * More information on writing migrations is available here:
     * https://book.cakephp.org/phinx/0/en/migrations.html#the-change-method
     *
     * Remember to call "create()" or "update()" and NOT "save()" when working
     * with the Table class.
     */
    public function change(): void
    {
        $table = $this->table('numbers')
            ->addColumn('key', 'integer')
            ->addColumn('value', 'integer');
        $table->create();

        if ($this->isMigratingUp) {
            for ($i = 1; $i < 150000; $i++) {
                $table->insert([
                    'key' => $i,
                    'value' => $i
                ]);
            }
            $table->saveData();
        }

    }
}

MasterOdin avatar May 04 '25 23:05 MasterOdin