RE: [agileDatabases] Re: Database Refactorings and Packages
- On Tue, 20 Jun 2006, Garris, Nicole wrote:
> Thanks Dave and Malcolm! You have convinced me that DBGhost makes itIt's possible (though certainly not as nice) to do this without DBGhost.
> feasible to use source control for database evolution. Wow!
I've been doing it for years.
> And the price is very nice! (Too bad it only works with Microsoft'sIf you want to start in on this sort of thing with PostgreSQL, you
> SQL Server.)
might grab the tools and example app over at pgtools.sourceforge.net
to see how you can do automated loading and unit testing using this
sort of thing. I think I described update scripts somewhere in the docs
on the web site, though I don't recall off-hand. This stuff should be
reasonably easy to port for other database systems that have a Unix
command-line-based sql tool, as well.
Curt Sampson <cjs@...> +81 90 7737 2974
The power of accurate observation is commonly called cynicism
by those who have not got it. --George Bernard Shaw
This is a matter of isolating the change. The question should not be "How do I get the database refactorings to work with a global package?â This won't happen because it is global. The question should be "How do I make a package local to the environment I am manipulating?" Without isolating environments it is like doing parallel development against code in a flat file system.
I have not used Oracle in years so I will leave the specifics to those with domain knowledge. It may require completely separate database instances.
When it comes to promoting these refactorings up the code chain I prefer the accumulation method. Our project uses a "scorched earth" approach to our builds:
ant refreshDBwill drop all artifacts in the database, create all tables, views, and stored procedures then populate it with configuration and canned test data. Then it runs the delta SQL, which is the exact DDL and DML needed to change the database from the baseline to what the developer needs it to be. Each developer appends their changes to the end of the file; ClearCase takes care of concurrency issues. This exact script is used during the stabilization day at the end of the iteration to update the QA databases at which time the SQL in the delta is integrated into the creation scripts (also in source code control) and zeroed out. All versions of this delta since the last release are used at release time to update the production database.
There are several advantages to this, first is the delta is tested every build. Second, the DBA's have a well known place to look for database changes. Third, a developer has unlimited freedom to experiment, they have a fully sandboxed environment. Fourth, database changes are associated with code changes when everything is checked in. Fifth, changes are small and easily understood.
The down side: it is a manual step to integrate the changes into the creation scripts. I doubt a tool will be able to solve this problem for us because we have made some interesting implementation choices. I'd be happy to detail them if anyone in interested, but in QA and PROD the database used by the J2EE data source is just a set of views looking into tables on other databases.
We are very agile, we use Sybase. We have over 200 tables, 20 developers and a "one day a week" DBA. We have done 6 releases so far this year, some had significant schema changes. We still have some versions released over a year ago running against the same database. The first few times we released and changed the database, management was absolutely white knuckled; they have since gained trust in our process and embraced agility.