Symphony.

@ahwayakchih

Added backslashes for \r\n and it works perfect. Thank you.

$queries = preg_split('/;[\\r\\n]+/', $data, -1, PREG_SPLIT_NO_EMPTY);

vladG, sorry, i didn't notice that backslashes were eaten after i posted my comment. I'm glad i could help :).

does this dump even all of the content tables? if so, wouldn't going from dev -> staging -> prod wipe out that data in the prod database?

Yes, it dumps all of the content tables. This extension is useful for pulling data from production to dev and staging, as long as you understand that you're overwriting the data. But I wouldn't go the other way around, unless you intend to obliterate data in the production database.

thanks stephen. so from the readme:

New function to restore database from dump. Be advised that this should never be used in a production environment as the procedure to extract the queries from the dump may be prone to errors.

this is just for restoring a db? So that is why he advises against setting this option to yes on a production server?

also, i am trying to set up a staging environment and i included this array of settings to my config.php file:

    ###### DUMP_DB ######
    'dump_db' => array(
        'restore' => 'yes',
    ),
    ########

But when i got Preferences and click "restore authors/data", i get an alert that just says "undefined" in it. what's going on there, do you know?

if so, wouldn't going from dev -> staging -> prod wipe out that data in the prod database?

Yes. I use it in conjunction with readonly mode to lock production, pull the data from there (set 'dump' => 'text' or 'download' enables the download of the data), then import and change it locally.

"restore authors/data"? The version you've got installed must be very old. :-)

"restore authors/data"? The version you've got installed must be very old. :-)

I don't see how that can be the case b/c i just added it as a submodule to my site today =). though, for some reason, all of my extensions as submodules have come up as *(no branch) for some reason and i'm not sure why...

it seems that the "undefined" alert box is just a missing translation. When I click "ok", and as long as i have the data/authors.sql file(s), it updates the DB correctly.

ok, so now that i know this is a complete data dump/restore, what happens when you're developing some new feature locally and want to push it to staging/prod? but then let's say stuff has changed on prod in that same time. how do you manage that workflow?

@phoque

i get an alert that just says "undefined" in it. what's going on there, do you know?

By the way, I get this thing to on the production and staging servers. On my local dev is fine. I wonder why ...

@wtdtan

ok, so now that i know this is a complete data dump/restore, what happens when you're developing some new feature locally and want to push it to staging/prod? but then let's say stuff has changed on prod in that same time. how do you manage that workflow?

Have a look at DB Sync. The readme is all you need to know.

First of all, I tell my client that DB changes take time and that they'd have to expect a few minutes or hours of not being able to edit anything.

I set

'dump_db' => array(
    'restore' => 'yes', 
    'dump' => 'text',
),

on the production site and

'dump_db' => array(
    'restore' => 'yes',
),

on my local install.

I use Readonly Mode to lock the production backend and events and click "Dump Data". The database dump will be displayed in my browser window (and not saved to disk). I copy it into the data.sql file on my local installation, commit the change (so that I'm at least able to go back to the last working state) and click "Restore Data" there. Now my local installation is in sync with the production installation.

I then do what needs to be done, click "Dump Data" and commit the new dump into the Repository. After deploying the latest commits, the production installation will display a message that one of the .sql files has recently been changed and that I should restore the changes.

After restoring the dump I disable readonly mode. Most of the time I only implement DB changes as quickly as possible. As soon as the production has a valid DB state I can take as much time as I want to implement the actual code. :-)

To be honest I use the restore feature on production sites myself. It's just that the way the queries are being read isn't 100% failsafe.

@phoque - so basically, you don't do any development work while content is being updated? you only do it when a request comes in and you freeze all content updates until you get your feature done? what if the feature takes a few weeks to implement?

what if the feature takes a few weeks to implement?

In my experience, DB changes don't take that long. But yeah, that could be a problem. :-)

In my experience, DB changes don't take that long.

Right, that doesn't seem to be the problem unless if you have millions of rows and what not. So the biggest issue is the time between a data dump from PROD and freezing content updates to when a feature is actually released, which like you said,

that could be a problem

I posted an issue related to a Symphony 2.2 compatibility error.

...and I've fixed that issue. Thanks for the heads up! :-D

Thanks for the extremely useful extension and the fix!

Dump and Restore Structural Data

There has been a long-standing issue with Symphony in regard to managing database changes in development, staging and production. Referring to Dale's question:

what happens when you're developing some new feature locally and want to push it to staging/prod? but then let's say stuff has changed on prod in that same time. how do you manage that workflow?

What if you could dump only the structural data from the development or staging database and be able to import that data into the production database? It seems to me that all you would need to do is exclude tables containing data from the SQL dump, then grab all the tables that remain, which are the structural data tables. These files then get dumped to a structure.sql file, which can be saved to a Git repository and restored on the production server.

A pull request has been sent. But please DO NOT use this in a production environment! This has not been fully tested. At the same time, I'd like to find out whether this is a workable solution. Please test this out, ONLY ON TEST DATA, and let me know if it works for you.

Doh! I missed a crucial part. I forgot that each entries table will have a different structure that has the potential to change. There would need to be some way to compare the existing structure and the structure that is being imported. Based on the results of the diff, the tables could be altered or deleted and new tables created.

Back to the drawing board.