[PHP] SQL Source Control
Hi all, I know a lot of you use various means for PHP source control (CVS, SVN, etc), which is all well and fine, but how do you manage source control on your databases? Say you've got an upgrade to a site, all of the new PHP files are controlled by SVN, so you can rollback at any time, but say the upgrade includes several key modifications to a MySQL table and perhaps the changing of some core data. How (if at all?!) do you handle the versioning of the database and data itself, so you can keep both PHP and SQL structure in sync? Cheers, Rich -- Zend Certified Engineer http://www.corephp.co.uk Never trust a computer you can't throw out of a window -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Source Control
Richard Davey wrote: Hi all, I know a lot of you use various means for PHP source control (CVS, SVN, etc), which is all well and fine, but how do you manage source control on your databases? Say you've got an upgrade to a site, all of the new PHP files are controlled by SVN, so you can rollback at any time, but say the upgrade includes several key modifications to a MySQL table and perhaps the changing of some core data. How (if at all?!) do you handle the versioning of the database and data itself, so you can keep both PHP and SQL structure in sync? lets forget that updating SQL schemas on massive DBs will likely take so much time that you will have to plan in downtime on the systems involved ... that's clearly out of the scope of this question. my strategy is also pretty weak in this regard but generally: I write my code in such a way that older code can run with newer db schemas, which basically means I add stuff but never remove it (tables, fields, etc) ... schema updates are always 'expansive'. If I'm feeling very tidy I'll create a seperate CVS module for the schema and updates/rollbacks. this involves writing sql files that update for each version of the project I have/will rollout ... and also sql files that perform the reverse/rollback actions between project versions. (when I'm being tidy I always do DB schema update when the major version number of a project changes) I end up with files named something like: v1-to-v2.sql v2-to-v3.sql v2-to-v1.sql v3-to-v2.sql then I include a script which I can call with the desired version number and it works out which sql files it needs to run and in which order (the current version is either stored in the DB or stored in a txt file outside of CVS) ... I have considered making this 'change version' script also automatically perform the required 'cvs up -r Foo' command on the actual project files but I have not got round to ever actually do it (time, money, inspiration, lackof) - maybe that brainfart inspires somewhat, then again maybe you'll pass out fom the smell :-) Cheers, Rich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Source Control
Jochem Maas wrote: lets forget that updating SQL schemas on massive DBs will likely take so much time that you will have to plan in downtime on the systems involved ... that's clearly out of the scope of this question. Yes, this was part of the problem (and reason for my original post). At the moment I'm dealing with a 2GB SQL database, with hundreds of modifications per minute. Rolling out new features always requires that we take the site down anyway, just so we can stablise the changes coming in and back-up the database. But this is more disaster recovery than version control, and doesn't get around a problem such as: running with a site upgrade (which expands an existing set of tables), taking new valid data from users into that new schema, plus into older un-touched tables, then needing to rollback for whatever reason - we're left with a horrendous 'merge' issue. I'm surprised (or rather, I'm unaware of) there is no native MySQL solution for this situation. Perhaps that's left to the bigger boys? Or maybe it's the main 'weak area' of most web developers :) - maybe that brainfart inspires somewhat, then again maybe you'll pass out fom the smell :-) It did actually. I'm thinking that perhaps I tag all data with the *version* of the site in which it was created. And tag schema updates in a similar way as you suggested. Still.. am amazed nothing more 'standard' exists. Cheers, Rich -- Zend Certified Engineer http://www.corephp.co.uk Never trust a computer you can't throw out of a window -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Source Control
Richard Davey wrote: Hi all, I know a lot of you use various means for PHP source control (CVS, SVN, etc), which is all well and fine, but how do you manage source control on your databases? Say you've got an upgrade to a site, all of the new PHP files are controlled by SVN, so you can rollback at any time, but say the upgrade includes several key modifications to a MySQL table and perhaps the changing of some core data. How (if at all?!) do you handle the versioning of the database and data itself, so you can keep both PHP and SQL structure in sync? Cheers, Rich Rich, This is a well-known problem that comes from the fact that the database is an infrastructure component rather than a source component. You would have the same issue if you were to upgrade the operating system software, for example. What most people do is use their tool of choice to create the DDL/DML update scripts and put those under source control. To version the data you need to make database backups at well-understood times and grab the data files if appropriate. Rolling back a change is a matter of recovering the database and files to a point in time along with the sources. I don't know of any management tools for this part of it. Most larger organizations have different people responsible for the database and web tiers, so a single tool won't do. Some folks are trying to use ClearCase automation to manage a lot of it, but it's still a work in progress... In a smaller environment I would be inclined to create shell/whatever scripts to do the actual implementation. If you parameterize the connection/server details you can test the implementation in a QA environment before going live - less need for rollbacks that way. The shell scripts greatly reduce the chance of finger trouble which is key if your implementation is being done at some uncivilized hour or by rookies. If you want to truly embrace the New World, you can do all of this using Ant, which has built-in tasks for CVS/SVN as well as file movement, etc. It can also run shell scripts for the database stuff. ...Lori -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Source Control
Richard Davey wrote: Hi all, I know a lot of you use various means for PHP source control (CVS, SVN, etc), which is all well and fine, but how do you manage source control on your databases? Say you've got an upgrade to a site, all of the new PHP files are controlled by SVN, so you can rollback at any time, but say the upgrade includes several key modifications to a MySQL table and perhaps the changing of some core data. How (if at all?!) do you handle the versioning of the database and data itself, so you can keep both PHP and SQL structure in sync? Cheers, Rich One thing we do is add a table called 'versions' to each application, this table just has one row and a column for the schema version (and sometimes other stuff less important.) When the app runs it checks to ensure that its defined constant DBVERSION matches that of the database it is running against. This has actually helped out more than once, though not a solution to the actual problem. Travis Doherty -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Source Control
I'm surprised (or rather, I'm unaware of) there is no native MySQL solution for this situation. Considering it's not a mysql specific problem I'd be surprised if there was. Postgres lets you do database changes inside a transaction, eg: begin; alter table x add column y int; ... commit; but two problems with that: 1) mysql doesn't support it (nor do a lot of databases) even when using innodb (and using myisam is out of the question for this because it doesn't support transactions) 2) it won't help in a web environment because you can't rollback once the transaction has finished you are testing the changes and then find the problem. No easily solution unfortunately apart from writing 'undo' scripts or having a backup handy.. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Source Control
On Wed, April 11, 2007 10:13 am, Richard Davey wrote: I know a lot of you use various means for PHP source control (CVS, SVN, etc), which is all well and fine, but how do you manage source control on your databases? Say you've got an upgrade to a site, all of the new PHP files are controlled by SVN, so you can rollback at any time, but say the upgrade includes several key modifications to a MySQL table and perhaps the changing of some core data. How (if at all?!) do you handle the versioning of the database and data itself, so you can keep both PHP and SQL structure in sync? Good thread! Though perhaps a MySQL list would have more insight... I wonder if perhaps some larger-scale sites might not just buy a whole 'nother DB server, and flip the connection line to the new one... Granted, you lose all new data if you have to revert, but at least you know you have a valid state to revert to... Kinda pricey, but there it is. I suppose the other thing I do that hasn't been mentioned is plan my DB schema a bit farther out than my PHP code, so that I've got new empty unused fields/tables sitting there, and not doing any harm, but that I can add features without worrying that they won't work. I've done this successfully a couple times for simple stuff, but it usually won't work for anything remotely complex, as I don't anticipate the DB needs correctly. Still, better to have an unused Future Tech #1 field if you know you'll need it than to try to add it at the last minute when you do need it, for simple stuff. I suppose one could do a mysql dump of at least the schema and svn that... -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
