MySQL - Cumulative updates or tiered updates?

[NewsBot]

Hey folks, I'm working on a very dynamic system that I'm not sure how to implement in the best manner possible.* Basically, the idea is this:

Users have their own domain that is parked as an addon domain to mine, thus I am able to access their domain, and, more importantly, they are able to access certain centralized files in mine.* All of these domains aren't really accessible to the user themselves, that is the files aren't; I set the domain up for them, and I put all of the files needed for their site up for them.* Whenever I release an update, however, I can't deploy to all of the user's simultaneously, so I take a different approach.* Everything up to this point works fine and there are no problems.

Here's where things start to get tricky.* On my centralized area that all of these domains have access to, I put a zip file with updated files, and I extract the files via PHP and overwrite their site's files with the files in the zip file as appropriate.* There are no problems with this method and it seems to work fine.* The problem is that each of these domains ALSO has a MySQL database instance and the tables on all of them are ALSO the same across the board.* When I need to make updates to their database, obviously I need that to be done automatically also, or I'll spend the rest of my natural life updating databases.

The question I am trying laboriously to get to is as follows:

The update files they get and I extract behind the scenes are always the most up-to-date.* However, the database changes are incremental and I can't always tell what state their database is in for the update script.* Thus the database updates can be done in one of two ways:

1) For every new "version" I release, I save record of that in the database, and there is a corresponding update file for the database for each version.* If someone's updating their site and it's 3 or 4 versions old, it loops through all of the updates since their version until they're up to date.

2) There is one update file that gets updated each release.* Somehow or another, we put appropriate "IF NOT EXISTS"-type checks into the update scripts so any new tables are created, and alterations to the tables needed are performed, and in the end their database is always completely up to date with the latest changes.

Can method 2 work?* When you do an ALTER TABLE and specify all of the columns and the modifications you want, does it simply skip that "alteration" if it already exists?* Is there a way to check if an alteration exists without having to parse DESCRIBE TABLE output and determine if it's of the right data type and such?

Sorry for the long-windedness, and thanks for reading if you got this far ;)

 

 

 

More...

 

View All Our Microsoft Related Feeds