Re: How to CM an Oracle DB

In article <mso-2510972005560001_at_m188.doubled.com>, mso_at_doubled.com (Mark) wrote:
>added groups:comp.software-eng,comp.databases,comp.databases.oracle.misc,
> comp.databases.oracle.tools
>
>In article <345218AF.9C6A62A8_at_mindspring.com>, Ron Perrella
><perrella_at_mindspring.com> wrote:
>
>@
>@ You don't really CM the DB. You CM how to reconstruct the DB. In
>@ other words, you want reproducibility. So, CM the SQL scripts that build
>@ and populate the DB.
>@
>@ Gee did I use enough TLA? (Three-letter-acronyms)
>@
>@ -Ron
>
>Yes, I agree that is the goal. I think this is is possible to do based on
>the last project I was on (I came in to a established CM project
>environment where all DB changes where released with the software as
>scripts to be run). However, the DBA on our current project claims it is
>not possible and is "asinine" to think it can be done due to the
>complexities of the project. She also claims the DB scripts used in one
>environment (development) cannot be used in another environment (system
>test, production). I just want to be sure I am correct before I push the
>issue up the chain of command. In my original post, I was looking for
>published references that it can be done.
>
>Please see my reply to Ken MacLeod <ken_at_bitsko.slc.ut.us> "Re:How to CM
>Oracle DB (DBAs input requested)"
>

We have 2 sets of SQL scripts for generating the database:

1. For generating from scratch
2. For generating version x.y from a previous version.

Type 2 scripts add new columns, tables or whatever as necessary.

To make life easier, it is usually simpler to require people not to leap over intermediate upgrades, at least as far as the database upgrade scripts go, though you could manually produce a combination upgrade script(s).

It is certainly an overhead to maintain these scripts, but a bit of careful diffing between your scripts and the ones produced by database dump utilities (e.g. Ingres copydb) helps.

As to the different between development and production or whatever, it is certainly best to keep them the same if at all possible. I can imagine that certain things such as index structures, or new indexes might change in production, but these should be fed back to dev't.

You do need a reasonable SCM tool which provides support for branches to make it easier to keep track of scripts (e.g. upgrade.sql for release 1.2 and upgrade.sql for release 1.1). We use Perforce and it works well.

Robert Received on Mon Oct 27 1997 - 00:00:00 CST