-
-
Save manuglopez/1fe3cdc61ce11cfb660ab6e222859c1f to your computer and use it in GitHub Desktop.
Load Data Local Infile - Laravel Seeder
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?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; | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?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]); | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment