Silverstripe DataObject bulk insert.

BulkInsert.php

<?php

/**
 * Handles bulk inserts to a MySQL database. This wil only work on base DataObject tables
 * as there is currently no way to relate to child tables in a bulk insert.
 * 
 * @author Michael Strong <[email protected]>
 **/
class BulkInsert
{
    /**
     * Holds the base table for this first class which extends DataObject.
     *
     * @var string
     **/
    protected $baseTable;

    /**
     * This holds the insert data for the base DataObject table (ie this first after DataObject).
     *
     * @var array
     **/
    protected $inserts = array();

    /**
     * A key to keep track of which insert query we're building.
     *
     * @var int
     **/
    protected $insertIterator = 0;

    /**
     * The maximum number of inserts per query.
     *
     * @var int
     **/
    protected $insertLimit = 500;

    /**
     * Database Columns.
     *
     * @var array
     **/
    protected $columns = array();

    /**
     * Takes a DataObject and sets up our bulk insert class with Database tables,
     * columns etc.
     *
     * @param string $dataObject
     * @param int    $insertLimit
     **/
    public function __construct($dataObject, $insertLimit = 500)
    {
        $this->insertLimit = (int) $insertLimit;
        $this->baseTable = ClassInfo::baseDataClass($dataObject);

        if ($this->baseTable != $dataObject) {
            throw new Exception("Bulk inserts can only work on base DataObject's. (ie the first to extend DataObject).");
        }

        // set the columns for a table, and re-order them for later use with inserts
        $this->columns = DataObject::database_fields($dataObject);
        ksort($this->columns);
    }

    /**
     * Queue a row to be inserted into the database.
     *
     * @param DataObject $dataObject
     * @param bool       $execute    Execute the insert if insertLimit has been reached
     *
     * @return bool
     **/
    public function queue(DataObject $dataObject, $execute = true)
    {
        // Ensure we're using the correct class
        if (get_class($dataObject) != $this->baseTable) {
            throw new Exception(sprintf(
                'Invalid class passed to queue. Must be of type %s.',
                $this->baseTable
            ));
        }

        // Ensure we're not surpassing our insert limit.
        if (isset($this->inserts[$this->insertIterator])) {
            if (count($this->inserts[$this->insertIterator]) >= $this->insertLimit) {
                // If we're executing, then execute the query and reset the current
                // insert array.
                if ($execute) {
                    $this->execute();
                    $this->inserts[$this->insertIterator] = [];
                } else {
                    ++$this->insertIterator;
                    $this->inserts[$this->insertIterator] = [];
                }
            }
        } else {
            $this->inserts[$this->insertIterator] = [];
        }

        // Ensure Created and LastEdited have a value.
        if (!$dataObject->Created) {
            $dataObject->setCastedField('Created', time());
        }
        if (!$dataObject->LastEdited) {
            $dataObject->setCastedField('LastEdited', $dataObject->Created);
        }

        $data = array();
        foreach ($this->columns as $col => $type) {
            $data[$col] = "'".Convert::raw2sql($dataObject->$col)."'";
        }

        $this->inserts[$this->insertIterator][] = '('.implode(', ', $data).')';
    }

    /**
     * Execute all queries that we've build up.
     **/
    public function execute()
    {
        $columns = array();
        foreach ($this->columns as $col => $type) {
            $columns[] = '`'.Convert::raw2sql($col).'`';
        }

        $baseQuery = 'INSERT INTO `'.Convert::raw2sql($this->baseTable).'` ('.implode(', ', $columns).') VALUES ';

        // No we need to separate the query by insert limit
        foreach ($this->inserts as $insert) {
            $query = $baseQuery.implode(', ', $insert);
            DB::query($query);
        }
    }
}