XML dataset fixtures

Propulsion ships two commands for moving rows — not structure — between databases: data:dump reads a live database and writes an XML dataset file, and data:sql converts a dataset file into a file of INSERT statements for a target platform. Together they are how you capture a known-good set of rows once and replay it into a scratch or test database as often as you like.

The pair is deliberately split. Dumping needs a live connection and knows nothing about the platform you will eventually load into; generating SQL needs no connection at all, just the dataset and a schema. So the dataset file is the durable artifact — you commit it, review it in diffs, and re-render it as MySQL or PostgreSQL SQL whenever you need to.

What a dataset file looks like

A dataset is flat XML: a single <dataset> root, one child element per row, no nesting. Elements are named after the table’s phpName and attributes after each column’s phpName — not the physical table and column names:

<?xml version="1.0" encoding="utf-8"?>
<!--Created by DataDumpManager.-->
<dataset name="all">
  <Publisher Id="1" Name="Scholastic"/>
  <Book Id="1" Title="Harry Potter" PublishedAt="1997-06-26 00:00:00" InPrint="t" PublisherId="1"/>
  <Book Id="4" Title="Don Juan" PublishedAt="1819-01-01 00:00:00" InPrint="f" PublisherId="1"/>
</dataset>

Using phpNames is what makes the file portable across physical naming: the schema is the only thing that maps Book/PublishedAt back to book/published_at, so the same dataset can be loaded into a table with a different physical name as long as the phpNames line up. (If your <table> and <column> elements have no explicit phpName, Propulsion derives one from the physical name — see the schema reference.)

Columns that are NULL are omitted from the row element entirely rather than written as empty attributes, which is why Book rows can carry different attribute sets. data:sql therefore emits an INSERT naming only the columns present on that row, and the database applies its own defaults to the rest.

Anything the dataset references must exist in the schema you hand to data:sql: an element whose name matches no table phpName, or an attribute matching no column phpName, is an error, not a value it silently drops.

Dumping a live database

data:dump takes a schema path as its argument — a single file, or a directory whose *schema.xml files are all loaded (default ./schema) — and a PDO DSN for the database to read:

php bin/propulsion data:dump schema.xml \
    --dsn="pgsql:host=localhost;dbname=bookstore" \
    --user=me --password=secret \
    -o fixtures/bookstore-dataset.xml

It runs one SELECT * per table described by the schema, in schema order, and writes every row it finds. Tables in the database that the schema does not describe are not touched; tables the schema describes that don’t exist in the database are an error. The command reports the total row count on success.

Option	Meaning
--dsn	PDO DSN of the database to read (required)
--user, -u / --password, -p	Credentials for that connection
--output, -o	Dataset file to write (default ./dataset.xml)
--database, -d	Dump only the <database name="..."> with this name; every database in the schema is dumped if omitted
--config, -c	Build properties file overriding generator/default.php, repeatable, later files win

-d here selects a schema database — the name attribute on a <database> element — which matters when your project spans several schema files or several <database> elements and one DSN only reaches one of them. It is deliberately not the platform, which is the one real difference from data:sql’s identically-named flag.

The platform is taken from the DSN instead. --dsn is the one thing this command is always given that says what the database actually is, so its driver prefix sets propulsion.database, which is what quotes identifiers in the generated SELECTs — mysql: gives you backticks, pgsql: double quotes, and dblib:/oci: map to mssql/oracle. No extra flag or properties file is needed for the ordinary case:

bin/propulsion data:dump schema.xml --dsn="mysql:host=localhost;dbname=bookstore" -u app -p secret

A driver prefix Propulsion doesn’t recognise sets no override, leaving propulsion.database at whatever your build properties say (pgsql by default) — pass -c with a properties file to name the platform yourself in that case.

Converting a dataset to INSERT SQL

data:sql takes the dataset as its first argument and a schema path as its second, and needs no database connection:

php bin/propulsion data:sql fixtures/bookstore-dataset.xml schema.xml \
    --database=pgsql -o fixtures/bookstore-dataset.sql

The result is a plain .sql file of INSERT statements grouped by table, in the order the rows appear in the dataset:

INSERT INTO "publisher" ("id","name") VALUES (1,'Scholastic');
SELECT pg_catalog.setval('publisher_id_seq', 1);
INSERT INTO "book" ("id","title","published_at","in_print","publisher_id") VALUES (1,'Harry Potter','1997-06-26 00:00:00','t',1);
INSERT INTO "book" ("id","title","published_at","in_print","publisher_id") VALUES (4,'Don Juan','1819-01-01 00:00:00','f',1);
SELECT pg_catalog.setval('book_id_seq', 4);

Values are rendered per column type as declared in the schema, not per attribute text: INTEGER columns are cast to int, DECIMAL/FLOAT/DOUBLE to float, strings are quoted and escaped by the platform, and DATE/TIME/TIMESTAMP values are parsed and re-rendered as Y-m-d, H:i:s, and Y-m-d H:i:s respectively. A date value the parser cannot make sense of fails the run rather than being coerced.

Option	Meaning
--output, -o	SQL file to write (default ./dataset.sql)
--database, -d	Target platform: mysql, pgsql, sqlite, mssql, sqlsrv, or oracle
--data-database	Use only the <database name="..."> with this name from the schema; the first database found is used if omitted
--config, -c	Build properties file overriding generator/default.php, repeatable, later files win

The two look confusingly similar and are not the same axis. --database is the platform whose SQL dialect to emit — it sets propulsion.database, which in turn selects the DataSQLBuilder subclass used. --data-database is the schema <database> element to resolve phpNames against, i.e. the same thing -d means on data:dump. You only need --data-database when the schema files you pass contain more than one <database> element and the first one isn’t the one your dataset came from; with a single-database schema it can be left off.

Loading fixtures into a test database

The motivating workflow is seeding a test or development database with real rows captured from somewhere else. Build the DDL, apply it, render the dataset, apply that:

# 1. Structure: generate and run the DDL for the empty test database.
php bin/propulsion sql:build schema.xml --database=pgsql -o generated-sql/
php bin/propulsion sql:exec generated-sql/bookstore.sql \
    --dsn="pgsql:host=localhost;dbname=bookstore_test" --user=me --password=secret

# 2. Rows: render the committed dataset for this platform and load it.
php bin/propulsion data:sql fixtures/bookstore-dataset.xml schema.xml \
    --database=pgsql -o fixtures/bookstore-dataset.sql
php bin/propulsion sql:exec fixtures/bookstore-dataset.sql \
    --dsn="pgsql:host=localhost;dbname=bookstore_test" --user=me --password=secret

Both sql:exec calls take an ordered list of files, so a single invocation can apply the DDL, the fixtures, and any hand-written extras over one connection, in order — each statement in its own transaction unless you pass --autocommit. See adding additional SQL files. Step 2 is the part worth wiring into a test bootstrap or make target: the dataset is committed to the repository, the .sql file is generated output, and re-seeding is a truncate plus a re-run of the same file.

Because the dataset resolves through phpNames, the schema you pass in step 2 doesn’t have to be the schema you dumped with. Point data:sql at a schema whose table keeps the same phpName but a different name, and the same rows land in a differently-named table — useful for loading a production snapshot into a side-by-side comparison table.

If the database you’re seeding is under migrations, run the migrations first and skip sql:build/the DDL step; only the second half of the workflow applies.

Per-platform SQL differences

data:sql renders values through a DataSQLBuilder chosen by --database, and the platform-specific subclasses differ in ways that matter for the file you get:

• PostgreSQL writes booleans as 't'/'f', escapes BLOB values with pg_escape_bytea(), and — most usefully — appends a SELECT pg_catalog.setval('<sequence>', <max id>) after each table whose primary key is auto-increment with the native ID method. Inserting explicit ids leaves the sequence behind, so without that call the first row your application inserted afterwards would collide with an imported id; the value used is the highest id in the dataset, not the row count.
• MySQL uses the base builder as-is: backtick-quoted identifiers, booleans as 1/0, and BLOBs as platform-quoted strings.
• SQL Server (mssql, and sqlsrv for the pdo_sqlsrv driver) writes BLOB values as unquoted 0x-prefixed hex literals rather than quoted strings.
• SQLite and Oracle add nothing to the base builder.

Booleans cross platforms cleanly. A dataset carries the source database’s own textual rendering of each value — pdo_pgsql never converts a boolean column to a PHP bool, so a PostgreSQL dump writes t/f, while a MySQL dump writes 1/0 — and the base builder recognises all of those spellings, treating f, false, 0 and the empty string as false and anything else as true. So a dataset dumped from PostgreSQL renders correctly as MySQL SQL, and vice versa.

For which platforms Propulsion supports more broadly, see database support; for capturing the structure of an existing database rather than its rows, see working with existing databases.