Do you know the best techniques for monitoring and/or automating DB schema changes? We uses Subversion for version control and we have had the opportunity to automate a lot of our tasks by doing this (pushing accumulates to some staging server, implementing examined code to some production server) but we are still doing database updates by hand. I must find or produce a solution that enables us to operate effectively across servers with various conditions while ongoing to make use of Subversion like a after sales by which code and DB updates are pressed around to numerous servers.
Many popular software programs include auto-update scripts which identify DB version and apply the required changes. Is the easiest method to do that even on the bigger scale (across multiple projects and often multiple conditions and languages)? If that's the case, can there be any existing code available that simplifies the procedure or is it more beneficial simply to roll our very own solution? Has anybody implemented such like before and integrated it into Subversion publish-commit hooks, or perhaps is this an awful idea?
While an answer that supports multiple platforms could be more suitable, we certainly have to offer the Linux/Apache/MySQL/PHP stack since most of our jobs are on that platform.
Within the Rails world, there's the idea of migrations, scripts by which changes towards the database come in Ruby as opposed to a database-specific flavour of SQL. Your Ruby migration code eventually ends up being changed into the DDL specific for your current database this will make switching database platforms super easy.
For each change you are making towards the database, you are writing a brand new migration. Migrations routinely have two techniques: an "up" method that the alterations are applied along with a "lower" method that the alterations are un-tied. Just one command brings the database current, and may also be used to create the database to some specific version from the schema. In Rails, migrations are stored in their own individual directory within the project directory and obtain checked out version control as with every other project code.
This Oracle help guide to Rails migrations covers migrations very well.
Designers using other languages have checked out migrations and also have implemented their very own language-specific versions. I understand of Ruckusing, a PHP migrations system that's modelled after Rails' migrations it may be what you are searching for.