On Thu, Jun 10, 2010 at 3:02 PM, Mark Ryall <[email protected]> wrote: > I meant to say this in one of the previous threads on this topic. > > The conventional approach seems to be to manage a set of sql files per > database (one file per table, stored proc, view, function, etc.) which when > run in the correct order (constraints after tables, views after tables, > etc.) will recreate the schema from scratch. The database projects in > VS2010 (and earlier) are an attempt to allow developers to make whatever > database changes they want using GUI tools and then generate an sql script > at the end to apply those changes. It is then the responsibility of the > developer to add conditions to detect whether the change has already been > applied so that all scripts can be rerun (they are idempotent). > > While this approach might be necessary in some circumstances (such as where > you're working as part of a large distributed team or there are many > projects contributing to the same database), it seems much uglier and more > complicated than necessary to me. The approach of having some tool work out > the changes you just made yourself just seems completely broken (you made > those changes after all - you were there). > > The reason it seems appealing is that it is consistant with the way we > typically manage code changes. The way code changes are applied is very > different to the way schema changes are made (ie. we rebuild everything > every time with code whereas database changes are always a delta between two > schema states with some possible data migration). > > Developers make schema changes for specific purposes so it seems better to > create a single sql file per change and include the purpose or nature of the > change in the name of the file.
It's significant overkill if you're making a trivial change (and a few of them, to different areas). > The most successful application of this practice was to have a folder > containing sql scripts with a "YYYY-MM-DD HH-MM-SS <PURPOSE OF CHANGE>" > naming convention. The automated build would start by restoring the base > schema version (ie. the schema that exists in production - this may just be > a blank database) and then applying these scripts in collation sequence > before running all integration/functional tests. This means the upgrade > process is always being tested and schema changes can be freely mixed with > data migrations. > > I'm clearly talking about something similar to rails migrations (rails > didn't invent the concept but it seems to have become work popular as a > result of rails) but without "down" scripts. Down scripts are only > necessary if it is impossible to back up a copy of production and restore in > the case of a problem (undoing lossy data migrations is a nontrivial > problem). > > Once you've done a release and applied these changes, you can change the > build to load a copy of the production database with that set of deltas > already applied and move the previous deltas to an archive folder. > > Automation with this approach is then completely trivial and everyone's > happy. > > I'm very curious to know why the "get the tool to work out what changes have > been made" approach is so popular. Really? I'm sure you can easily work out why. Anyway, I'm not going to be drawn into a useless discussion on the matter, and I suspect this isn't directed at me anyway. > Mark. -- silky http://www.programmingbranch.com/
