Re: Version controlling for Oracle

Syed

Obviously you can use any file-based version control system (RCS, SCCS, 
ClearCase, Synergy, PVCS, SourceSafe, CVS, etc) to control text version of your 
database objects.

The real fun part is deciding
1 - what is the "unit" of source control?
Each table, function, package can be a separate file
Make sure that your unit fits with your working practices and tools (eg 
SQL*Developer, TOAD etc)
Make sure that you can easily extract code from database to file
I recommend separating package bodies and specs
I recommend keeping NOTHING in the text file except for the object definition. 
Everything else (including SHOW ERRORS, WHEN SQLERROR THEN FAIL etc) should go 
into wrapper scripts.
Overall goal: avoid/minimise manual steps wherever possible
2 - how to cope with schema changes from version to version
Most apps get distributed to multiple sites (even if that is just test and prod)
Will you keep scripts for a "clean" or "fresh" build? Will you also keep 
"delta" scripts for upgrading the database?
The delta scripts can be generated from tools like Oracle Designer - but they 
almost always need to be modified and tailored for reality

3 - how to cope with reference (seed) data changes from version to version
Pretty much all applications have static or nearly static data (codes, valid 
values etc) which get modified from time to time.
Again, you have to think about deltas versus "clean" install

4 - What happens when you upgrade a populated database? (production, for 
example). 
Upgrade steps that may work in test may explode in production
Because of all these factors, planning how you are going to keep track of all 
the database objects, the install and upgrade scripts, and the deltas over 
time, is much more important than simply selecting a source control tool. Given 
a consistent approach, it is possible to write batch scripts to do things like:
- make a release
- test the release (eg in overnight build tests)
I've produced these in the past against version control tools like Clearcase 
and Synergy.

BTW there are tools other than Oracle Designer which claim to help with the 
diffs between database versions. I saw one blogged about quite recently on 
OraNA - I'll try to track it down.


Regards Nigel



----- Original Message ----
From: Syed Jaffar Hussain <sjaffarhussain@xxxxxxxxx>
Sent: Monday, January 28, 2008 11:53:34 AM
Subject: Version controlling for Oracle

Other related posts: