Skip to content

Instantly share code, notes, and snippets.

@Artistan Artistan/LoadData.php

Last active Jul 1, 2019
Embed
What would you like to do?
Load Data Local Infile - Laravel Seeder
<?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;
}
}
<?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]);
}
}
@Artistan

This comment has been minimized.

Copy link
Owner Author

Artistan commented Nov 1, 2018

updated, was not sorting the columns. oops.

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

@Artistan

This comment has been minimized.

Copy link
Owner Author

Artistan commented Jul 1, 2019

added flag to ignore foreign key restraints during import

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.