FuelPHP Batch insert Class

BatchQuery.php

<?

/**
* BatchQuery allows you to do mass mysql inserts easily and quickly
*/
class BatchQuery
{
	private $db;
	private $batchSize  = 0;
	private $batchLimit = 200;
	private $query      = '';
	private $insertSQL  = '';
	private $method     = '';
	private $ignore     = false;
	private $columns    = [];
	private $prepared   = false;
	private $colCount   = 0;
	private $table      = '';
	private $count_die = true;

	function __destruct()
	{
		if ($this->batchSize > 0) throw new Exception("Batch query for {$this->table} has unprocessed rows, call finish or reset");
	}

	public static function forge()
	{
		return new static();
	}

	public function skip_column_mismatch()
	{
		$this->count_die = false;
		return $this;
	}

	public function db($db)
	{
		$this->db = $db;
		return $this;
	}

	public function limit($limit = 200)
	{
		$this->batchLimit = $limit;
		return $this;
	}

	public function insert($table)
	{
		$this->method = 'INSERT';
		$this->table  = $table;
		return $this;
	}

	public function replace($table)
	{
		$this->method = 'REPLACE';
		$this->table  = $table;
		return $this;
	}

	public function ignore()
	{
		$this->ignore = true;
		return $this;
	}

	public function columns(Array $cols)
	{
		$this->columns = $cols;
		$this->colCount = count($this->columns);
		return $this;
	}

	public function prepare()
	{
		$this->insertSQL = $this->method . ($this->ignore ? ' IGNORE' : '') . " INTO `{$this->table}` ";
		$this->insertSQL .= $this->implode_quote($this->columns, '`');
		$this->insertSQL .= " VALUES ";
		$this->prepared = true;
		$this->reset();
	}

	 /**
	 * Add a row to the batch insert
	 * @param Mixed columns accepts a variable number of arguments. batch->add($col1, $col2, $col3);
	 */
	public function add(Array $values)
	{
		if ( ! $this->prepared) $this->prepare();

		if ($this->colCount != count($values))
		{
			if (\Fuel::$is_cli) \Cli::write('column count mismatch');
			if ($this->count_die) throw new Exception("Column count mismatch. {$this->colCount} expected, ".count($values)." set");
			else return false;
		}

		// add comma at the end of the previous batch
		if ($this->batchSize != 0) $this->query .= ',';

		// escape the values
		$values = array_map(function($in){ return trim(preg_replace('/[\x00-\x1F\x80-\xFF]/', '', $in));}, $values);
		$values = array_map(['DB', 'quote'], $values);

		// add this batch
		$this->query .= '('.implode(',', $values).')';
		$this->batchSize++;

		// if limit reach, run
		if ($this->batchSize >= $this->batchLimit) $this->finish();
		return true;
	}

	 /**
	 * Finish a batch query if it's not complete
	 */
	public function finish()
	{
		if ($this->batchSize > 0)
		{
			// run the query
			\DB::query($this->query)->execute($this->db);

			$this->reset();
		}
		return $this;
	}

	 /**
	 * Resets the batch counter and starts setting up the query
	 */
	protected function reset()
	{
		$this->batchSize = 0;
		$this->query = $this->insertSQL;
		return $this;
	}

 	/**
 	* Implode and quote an array of items
 	*/
	protected function implode_quote($array, $sep = "'")
	{
		return " ($sep" . implode("$sep,$sep", $array) . "$sep) ";
	}
}

Usage: this will prepare a batch insertion based on a tab separated csv. It'll load the csv in line by line and insert them in batches of 200 (the default batch size). This will both insert very quickly avoiding constant indexing in the db, and reduce memory requirements.

$batch = \BatchQuery::forge()
    ->db('default') // optional, uses fuelphp default db
    ->limit(200) // optional, defaults to 200
    ->insert('vehicles') // table name to insert into
    ->columns(['id', 'make', 'model', 'year']); // columns you'll provide values for using each $batch->add()

while ( ! feof($flatfile))
{
    $record = fgets($flatfile);
    if ( ! $record) continue;
    $csv = explode("\t", $record);
    $batch->add($csv);
}

$batch->finish(); // make sure to complete any remaining batched items