Symphony.

Hi,

I'm currently setting up my DTAP environments, continuous integration server and build scripts, but I'm a bit stuck on how to automate the database changes.

So I was wondering if anyone has any experience with working with Symphony using Continuous Integration / Continuous Deployment?

Cheers,

Remie

Assuming the database changes are mainly structural and not the actual entry data, you could use the Database Synchroniser extension.

Yes, I've looked into the Database Synchroniser, but i'm still pondering on how to integrate this in the build. I can automatically execute the db_sync.sql... but is it safe to execute it multiple times (for instance, on every commit)?

I just did a test and obviously it doesn't work. You cannot execute the DB_Sync.sql file twice because if there is an insert statement (for instance to create a new section) it will fail during subsequent runs due to the fact that the section was already created the first time the SQL file was executed.

So I need to clean the DB_Sync file after every build, which is a bit cumbersome. A workaround would be to adjust the Database Synchronizer to see if a specific line was already executed.

Does anyone have any experience with this?

Sorry, I haven't used it in a while. From memory there is an option to flush the DB_Sync file. Could that be utilized to clear the log after you commit for integration?

Could you do it in your build script so that when DB_Sync is committed and pushed, it then rm's the file and touch a new one?

Well... the point is you don't want your CI server to commit anything back to the repository. So it should always be able to run from trunk. The only solution would be to commit, run the CI and then commit an empty file. However, that is not really following best practices in regard to continuous integration and continuous deployment.

So I decided to create a new extension based on the Database Synchronizer made by Nick Dunn. I will be posting it to the forum as soon as it is done and committed to github.

The extension will be called CDI (Continuous Database Integration) and will create a single file for each query that is executed (selected by the RegExp filter made by Nick Dunn). The filename will consist of the timestamp, an order number and the MD5 hash generated based on the sql script.

Upon execution of the scripts the extension will log this in a database table. Based on the listing in this table it will know whether or not a script has already been executed. This will prevent any of the database scripts to be executed twice.

If the CDI extension has been enabled from the beginning, this will allow you to create an entire new installation of the structure simply by running the 'update' function of the extension (having all scripts available).

Any other environment (Test, Acceptance, Production) will automatically be updated to the latest structure changes. During the deployment phase of the build script, the 'update' function can be called automatically through REST interface. That way, both sources and database are updated by the build.

Nice solution, it becomes very much like Rails and rake in that regard then. I look forward to the extension :)

There you go: CDI extension version 0.1.0

I suggest that this discussion is marked 'closed' and future comments are placed here