SQL Data

Pop\Db\Sql\Data turns rows into INSERT statements in the connected adapter's dialect. It is the tool for producing a .sql seed file out of data you already have, and it travels — the statements it writes load into any adapter that speaks the same dialect.

Exporting Rows#

The constructor takes the adapter and the table the statements should target. serialize() returns the SQL as a string, and streamToFile() writes as it goes rather than building the whole thing in memory, which is what makes it usable on a large set:

PHP

use Pop\Db\Record;
use Pop\Db\Sql\Data;

$db = Record::getDb();

$rows = $db->select('SELECT * FROM widgets');

$data = new Data($db, 'widgets');
$data->streamToFile($rows, __DIR__ . '/../database/seeds/default/widgets.sql');

streamToFile() appends, so give it a path of its own or clear the file first. Both it and serialize() take an $omit argument — a column name or an array of them — to leave columns out of the statements, which is how you drop an auto-increment key and let the target database assign its own.

writeToFile() is the counterpart to serialize(): serialize first, then write the accumulated SQL, with an optional header and footer around it.

Rows Per Statement#

The constructor's third argument is how many rows go into each INSERT. It is 1 by default — one statement per row, which is the readable form and the one to diff. 0 puts every row into a single statement, and any other number batches that many at a time:

PHP

use Pop\Db\Record;
use Pop\Db\Sql\Data;

$db = Record::getDb();

$rows = $db->select('SELECT * FROM widgets');

$data = new Data($db, 'widgets', 100);

echo $data->serialize($rows);

Upserts#

Two settings change what the statements do when the row is already there. onConflict($columns, $key) appends the upsert clause — ON DUPLICATE KEY UPDATE on MySQL, ON CONFLICT ... DO UPDATE SET on PostgreSQL and SQLite — naming the columns to overwrite:

PHP

use Pop\Db\Record;
use Pop\Db\Sql\Data;

$db = Record::getDb();

$rows = $db->select('SELECT * FROM widgets');

$data = new Data($db, 'widgets');
$data->onConflict(['name'], 'id');

echo $data->serialize($rows);

setForceUpdate(true) writes an UPDATE per row instead of an INSERT, keyed on id unless you name a different column as the second argument.

Reading a File Back#

Pop\Db\Db::executeSqlFile() strips comments, splits on ; and runs the statements in order — the same call db:seed makes for a .sql seed. Db::executeSql() does the same for a string:

PHP

use Pop\Db\Db;
use Pop\Db\Record;

Db::executeSqlFile(__DIR__ . '/../database/seeds/default/widgets.sql', Record::getDb());

Kettle Export and Import#

BASH

./kettle db:export
./kettle db:import database/widgets.sql