Symphony.

I’ve recently found something wonderful. I came across a PHP migration manager that nobody seems to have found while working at my new job, and I’ve found it insanely useful. Might be worth looking at for ideas nick :)

Good find! Does this compare two databases and build the queries to update between them, or is this more a managed way to executing manually-created batch scripts?

It’s very similar to what we had intended for a more advanced Database Synchroniser extension. The idea is:

1. wrap the migration into a transaction (to roll back on error)
2. provide a configuration file to enter destination MySQL database
3. use Alistair’s command-line Symphony tool to provide a command which:
   • exports the database changes SQL file
   • opens a connection to the destination database and executes
   • saves the SQL file to an archive for SVN control
   • flushes the database change log

This way, the process of migrating is a one-step deployment from the command line.

What’s your workflow when using PHP Migration Manager? What are the really good bits that could be implemented into this extension?

I believe it’s the latter. It’s a very simple implementation: we chucked it in the SVN and set up our config files with ./migrate.php init, and then every time we alter the database we generate a new migration from the command line, add our SQL, commit to our repository, and run ./migrate.php latest on the other machines. Now if you could take that and adapt it to Symphony so the migrations are automatically generated as sections/etc are created, and an admin prompt that notifies you that new migrations have been found with an option to update your database - that’d be freakin’ sweet.
I have a new-found love for command line utilities, but if it was possible to perform the updates in the admin as well then you would have an incredible new feature for development teams!

Up to now I have always been developing on “live” websites, using lots of logged-in checks in my XSL templates. But as projects become bigger things get more complicated, and I am looking for a solution to deploy a dev build into a live website.

@Nick: Your solution is probably the most promising approach. How far is version 1.0?

@Alistair: How do you do it? Are you still using Navicat to synchronize databases?

(I managed to (partly) synchronize the file systems using rsync on the server. But the DB part makes my head spin.)

Your solution is probably the most promising approach. How far is version 1.0?

As it stands the extension is stable. We’ve used it on three major projects at Airlock and it’s worked 95% of the time. Twice (in 6 months of development, with releases almost weekly) there’s been a bug with a query not logging properly which have been resolvable.

The additions of transactions and shell execution are nice, but not essential. Just be sure to always back up your database first just in case!

This morning I had a few ideas about how it could be improved, but I don’t see myself being able to find time in the next month or so.

Thank you, Nick! I will try your extension!

@Nick: How do you synchronize file systems? Are you using rsync or Git? If it’s the latter: Can Git preserve permissions?

Deploying straight from SVN. Once the files are up, we back up the production database and apply the Database Synchroniser script. When it works, we reward ourselves with a cup of tea.

I imagine a blog post about our release procedure would be useful at some point. Although it will differ for everyone.

Thanks a lot.

@Nick: It seems to me that the installation instructions do not really work. They point out to first install the extension, then hack class.mysql.php. When I tried this sequence, it did not start logging. It works the other way round, and all you need to do then is an ititial “flush” on the log file (t ged rid of the mysql for enabling the extension).

The order was intentional: you don’t want the installation of the extension to be in the logs, since this extension should only be enabled on a development server (and indeed this change to class.mysql.php should only be on dev too).

I have understood your intention, but it simply does not work this way round. :-)

I will try again and come back!

I see. Did you get an error message? I’m not sure what isn’t working.

It simply did not start logging. Perhaps I did something wrong. I will see when I try the next time, but at the moment I am stuck by a brand-new Symphony bug

Did you then create/edit a Page, Section or Extension? It will only log these changes, not content within sections.

Nick, I do not really know what I have done wrong. My test was to save a page, and there was nothing in the logs. So I thought that it had to do with the enabling sequence, but I was proven wrong: I just tried the same sequence a second time, and everything works fine. So please forget my complaints.

It would be nice to have the log page send a text/plain header! Safari will discard line breaks at the moment, because there is a text/html header.

Some thoughts regarding section content logging: I can imagine situations like re-building certain sections where database logging on those sections would be really great. I assume that you discard those changes because you could really break things if you don’t know exactly what you are doing (e.g. which content might change on production in the meantime)? But let’s say I want to take the risk – would it be sufficient then to comment out the following line in the database logger code (which is added to class.mysql.php):

!(preg_match('/^(insert|delete)/i', $query) && preg_match('/(sym_entries)/i', $query))

I guess this would do it.

?

[EDIT]: Ah, well, and remove the && in the preceding line, of course… :-) [/EDIT]

I see it doesn’t work like that, probably because you are filtering the queries.

So which route could I take to build some news sections (plus entries) on the dev version and deploy these changes to staging/live versions?

I have done some testing and noticed that breaking things would even be easier than I thought if content changes were tracked in the dev version of a website and deployed to a production server. Solving this content issue via software (e.g. Nick’s extension) seems a silly undertaking.

So I will have to find a stremalined, fool-proof process to do certain development tasks.

@Nick: I would really love to read about your release procedure.

I have done some testing and noticed that breaking things would even be easier than I thought if content changes were tracked in the dev version of a website and deployed to a production server.

I gave this some thought today and came to the same conclusion. I had considered adding a way of including specific sections so that their content updates were logged, but this opens a new can of worms and I don’t think it’s within the scope of this extension.

If you need a repeatable import of content on multiple servers then I suggest writing a script to insert the content on development, so that it can also be run on other servers too. I half a half-working XML Importer extension (thanks to Rowan’s fine groundwork) which should see a release someday.

A blog post about a release procedure has been added to my growing to-do list :-)