Table Data/Table Rows?

[lougreenwood]

Hi,

I'm currently doing migrations in a bad way (storing whole .sql files in git) and I'm looking to move to a proper migration system.

I'm trying to use this tool (alongside Phinx, obviously :D) to replace this. However, I'm confused how I can manage actual DB data?

It seems that the seeds in Phinx is the way to go, but also it seems that migrations could also handle 'inserts' of new data/rows?

Any advice on how I can auto-generate a migration/seed using quto-extracted data from the DB?

[odan]

I think database seeding is a simple way to populate your database with test data (for integration tests). This is often an automated process that is executed upon the initial setup of an application.

Database fixtures are part of the migration and could therefore be placed there as well.

There is currently no official feature to deploy "Fixtures" in Phinx. But that shouldn't stop us from writing fixtures ourselves.

I would do it like this:

1. Step: Create a blank migration with an empty change() and up() method.

phinx create MyNewFixtureMigration

2. Step: Implement the up() method which adds or updates your fixtures.

Example:

public function up()
{
    $rows = [];
    $rows[] = [
        'id' => 1,
        'title' => 'Yes',
    ];
    $rows[] = [
        'id' => 2,
        'title' => 'No',
    ];

    // Insert fixture
    $this->insert('my_table_name', $rows);

    // Update or delete fixtures
    $this->execute('UPDATE ....');
}

3. Step: Execute the migration

phinx migrate

When deploying on the server, you only need to perform all migrations.

[lougreenwood]

Hey @odan - thanks for the reply.

The problem I face is that my existing DB is 10Mb and contains 10s of thousands of insert statements.

This is because our system is a very complex WordPress setup, and the base configuration requires a LOT of DB rows.

I know this is just plain bad - but it's a side effect of the WP system....

Also, adding any new data to this system usually results in a large number of new inserts - probably more than could reliably be tracked and written as PHP migrations.

So you see my problem, there's no way I could manually copy all of those inserts to PHP...

Any suggestions? Perhaps there is some other Phinx tool you might be aware of, or maybe some other strategy I could consider?

It seems that maybe I just need to use a different migration tool - I've taken a look at Doctrine & Laravel migrations tools, but neither seem as good or de-coupled from their parent frameworks as Phinx is - so I'm loathed to move to them :(

[odan]

Well, 10 MB isn't that big for a database. Sounds like you're trying to create a database just once. My first suggestion was to distribute smaller amounts of data (after the database has been initialized).

To initialize large amounts of data, you need a different strategy.

For example: The migration script could read the data rows from an external file (CSV, XML or whatever) and execute the insert.

Why should Laravel or Doctrine have solved this better and how?

[lougreenwood]

10MB is just the base install/local dev, no production records are included...

For example: The migration script could read the data rows from an external file (CSV, XML or whatever) and execute the insert.

This could work for the initial DB, but manually adding (or removing) records using migrations would be painful because of the volume of changes when a change does occur (because of WP architecture...).

From my perspective, the ideal solution would look something like:

• when auto-generating a migration where DB rows have changed (not necessarily schema changes)
• migration generation detects changed rows & generates insert records for those rows

The use case might be adding some new feature. We may or may not make schema changes, but we would add new records for some new feature, when releasing to prod these records would need to be migrated alongside the schema...

[odan]

The phinx-migration-generator is actually only designed for schema migrations and not for data synchronization between databases.

You might try to start a data migration after the schema migration.

You could try phinx in combination with db-sync (not tested): https://github.com/mrjgreen/db-sync