On 10 June 2010 15:11, silky <[email protected]> wrote: > 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.
Probably not. I'd observe that a 'diff' tool for databases is useful for the same reasons as diff tools for text files. (what did we change between these 2 revisions to break functionality) As an aside, Beyond Compare does a much easier to read compare than windiff. -- Meski "Going to Starbucks for coffee is like going to prison for sex. Sure, you'll get it, but it's going to be rough" - Adam Hills
