Load Data Local Infile - Laravel Seeder

LoadData.php

<?php
/**
 * notes
 *
 * decent tutorial on load data...
 * https://tenerant.com/blog/using-load-data-local-infile-in-a-laravel-migration/
 *
 * simple example
 * https://gist.github.com/Xeoncross/2012182
 *
 * mysql-load-data-tool - limited functionality
 * https://github.com/itsmill3rtime/mysql-load-data-tool/blob/master/src/MySQLFileTool.php
 */

namespace BEN\Database;

use Illuminate\Database\Eloquent\Model;
use PDO;

/**
 * Class LoadData
 *
 * @package BEN\Database
 */
class LoadData
{
	/**
	 * @var string
	 */
	protected $unset = true;

	/**
	 * @var string
	 */
	protected $table = '';

	/**
	 * @var array
	 */
	protected $columns = [];

	/**
	 * @var array
	 */
	protected $files = [];

	/**
	 * @var null
	 */
	protected $active_file = null;

	/**
	 * @var int
	 */
	protected $current_index = 0;

	/**
	 * @var int
	 */
	protected $total_index = 0;

	/**
	 * @var null
	 */
	protected $active_file_reference = null;

	/**
	 * @var
	 */
	protected $chunk_size = 10000;

	/**
	 * @var string|null
	 */
	protected $mode = null;

	/**
	 * @var \PDO
	 */
	protected $pdo = null;

	/**
	 * @param Model|PDO $model
	 * @param int $chunk_size
	 * @param string|null $mode
	 * @throws
	 */
	public function __construct(Model $model, int $chunk_size = 10000, string $mode = null)
	{
		$this->chunk_size = $chunk_size;
		$this->mode = $mode;
		$this->table = $model->getTable();
		if ($model->getAttributes() ?? false) {
			$this->columns = array_keys($model->getAttributes());
		} else {
			if ($model->getFillable() ?? false) {
				$this->columns = $model->getFillable();
			} else {
				throw new \Exception('requires a valid Model');
			}
		}
		sort($this->columns);
		$this->pdo = $model->getConnection()->getPdo();
	}

	/**
	 * @return resource
	 */
	private function active_file()
	{
		//if we need a new file
		if (is_null($this->active_file)) {
			$this->active_file = storage_path('SQL-'.uniqid().'.csv');
			$this->active_file_reference = fopen($this->active_file, 'a');
			$this->files[] = $this->active_file;
		}
		//if we hit our chunk size
		if ($this->current_index >= $this->chunk_size) {
			fclose($this->active_file_reference);
			//set active to null so we can make a new one
			$this->active_file = null;
			$this->current_index = 0;

			//create a new one
			return $this->active_file();
		}

		return $this->active_file_reference;
	}

	/**
	 * @param bool $ignore_foreign_key
	 * @return int
	 */
	public function import_files($ignore_foreign_key=false)
	{
		try {
			fclose($this->active_file_reference);
		} catch (\Exception $e) {
		}
		if($ignore_foreign_key){
			$this->pdo->exec("SET FOREIGN_KEY_CHECKS=0");
		}
		foreach ($this->files as $file) {
			try {
				if ($this->mode === 'replace') {
					$method = 'REPLACE ';
				} elseif ($this->mode === 'ignore') {
					$method = 'IGNORE ';
				} else {
					if ($this->mode === 'ignore' || $this->mode === null) {
						$method = '';
					} else {
						throw new \Exception('invalid data mode');
					}
				}
				$this->pdo->exec("LOAD DATA LOCAL INFILE '".$file."' {$method}INTO TABLE ".$this->table." FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\\\"' LINES TERMINATED BY '\\n' (".implode(",",
						$this->columns).")");
				$this->remove_file($file);
			} catch (\Exception $exception) {
				//keep failed file for review
				dd('file: '.$file.' failed', $exception);
			}
		}
		if($ignore_foreign_key){
			$this->pdo->exec("SET FOREIGN_KEY_CHECKS=1");
		}
		$total = $this->total_index;
		$this->reset();

		return $total;
	}

	/**
	 * @param array $row_fields_data
	 * @param bool $debug
	 * @return array
	 */
	public function add_row(array $row_fields_data, $debug = false)
	{
		ksort($row_fields_data);
		fputcsv($this->active_file(), $row_fields_data);
		$this->current_index++;
		$this->total_index++;

		$current = ['file' => $this->active_file, 'count' => $this->current_index, 'total' => $this->total_index];
		if ($this->current_index == 1 && $debug) {
			dump($current);
		}

		return $current;
	}

	/**
	 * @param $rows
	 */
	public function add_rows($rows)
	{
		foreach ($rows as $row_fields_data) {
			$this->add_row($row_fields_data);
		}
		$complete_string = null;
	}

	/**
	 * @param $file
	 */
	private function remove_file($file)
	{
		if ($this->unset) {
			@unlink($file);
		}
	}

	/**
	 *
	 */
	public function reset()
	{
		$this->files = [];
		$this->active_file = null;
		$this->total_index = 0;
		$this->current_index = 0;
		$this->active_file_reference = null;
	}
}

SeederExample.php

<?php

use App\Database\Note;
use Illuminate\Database\Seeder;

class NotesSeeder extends Seeder
{
    /**
     * @throws \Exception
     */
    public function run()
    {
        $debug = true;
        $note_id = '';
        $supportDB = \DB::connection('old-server');
        /** @var \PDO $pdo */
        $pdo = $supportDB->getPdo();
        $pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
        $stmt = $pdo->prepare('SELECT  * from notes');
        $noteInstance = new Note::();
        /// init.
        $loadData = new \App\Database\LoadData($noteInstance);
        if ($stmt->execute()) {
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                // add rows
                $loadData->add_row($row, $debug);
            }
        }
        // load data from files into the model table.
        $total = $loadData->import_files();
        dump(['total' => $total]);
    }
}

updated, was not sorting the columns. oops.

now sorts column names and then sorts the rows by key, requires associative arrays to work properly

added flag to ignore foreign key restraints during import