Re: oracle-l Digest V5 #130

On the development projects I have worked on so far we have found it more useful to start with a baseline schema and apply changes to it incrementally. The key part to this is tracking the baseline schema and tracking changes that have been applied.

At my previous job we had a formal change request process as Thomas mentioned. Originally we kept track of these changes manually but I ended up writing a Perl program that accepted a list of SQL scripts as input. The Perl program would run SQLPLUS for each SQL script in the list, then record the result in a table in the database. The success or failure was determined using SQLPLUS's WHENEVER SQLERROR EXIT command. Then the next time the Perl script was run it would query the 'changes' table and only apply new changes or reapply scripts that did not run successfully the first time.

The big advantage is that each schema 'knew' what scripts had been successfully applied to it. This allowed us to keep different versions of the database available and not worry so much about if databases had DDL in sync. We could query the changes to determine what level a schema was. If there was a requirement to get everything in sync we could run the Perl script against each schema and it would 'catch up' changes.

The main thing that helped us adopt this scheme was that we already had tight controls on changes. Even when we did this manually all incremental scripts were in source control so they didn't get modified by mistake or accident. Only DBAs could make DDL changes to the database.

Late in the project we got a new Development Manager that demanded that we maintain a full DDL script in addition to incremental changes. I asked him if he was willing to dedicate QA resources to make sure that the results of the full DDL script matched the results of all of the incremental scripts. Considering we hadn't had a need for a full DDL script in 7 years his demands subsided.

If you have a business requirement to keep a full DDL script maintained in parallel with incremental changes scripts then I would probably suggest keeping a QA environment with 2 schemas. Use this environment for validating that running the full DDL script against an empty schema matches the running of all the incrementals against a baseline.

• Dave

On 5/15/08, FreeLists Mailing List Manager <ecartis_at_freelists.org> wrote:

> Date: Wed, 14 May 2008 10:08:29 -0400
> From: "Rick Ricky" <ricks12345_at_gmail.com>
> Subject: how do you handle your DDL builds?
>
> We have a production database. We want to change tables, structure, etc...
> We do this frequently. How do you script this? So far I have been using
> upgrade directories.
> upgrade
> version1
> version2
> However, this leaves me with a problem. I can do an add column to a table
> in
> version1, but then I have to change my base DDL of the table, but how do I
> know that table works? Where would I test that my new create table
> statement
> works? You can do all this manually, but its slow, time consuming, and
> prone
> to bugs.

--
Dave Mann
www.ba6.us

--
http://www.freelists.org/webpage/oracle-l