Martijn Tonies wrote:

>>Absolutely. SQL DDL (extended) must be able to express any legal change.
>>
>>But yes, complete coverage of creation/modification/deletion of all
>>database objects is absolutely necessary.
>>
>>
>
>Then there's quite some ground to cover, as that's currently
>not the case at all.
>
>You cannot, in the same transaction, drop an existing FK
>and re-create it with, for example, different "ON DELETE"
>attributes, or other columns.
>

That may be, but it is an artifact of DDL operations being under
transaction control.

>And, as a tool developer, I always want to ensure that all
>DDL changes are made - that is, if I drop an FK to replace
>it with a different one, either commit both of the actions or
>none at all...
>

I understand the desire, but while a robust implementation is
theoretically possible, it isn't practical. Most consistency checks are
made during the first phase of a DDL (DYN) operation, but the real work
is necessary deferred to commit time. The existing design (so-called)
has an implicit assumption of reasonable well defined, well ordered,
mutually consistent transformations. An unexpected failure during a
latter phase is, in general, unrecoverable. Once a table has been
physically deleted, for example, nothing can get its pages back, so a
subsequent error can never rollback to the original state.

There's somewhat of a chicken and egg problem here. People want DDL
updates under transaction control because DDL operations tend to be
flaky. On the other hand, making DDL operation robust involves making
them atomic. So given the unfair chose of robust, non-transactional
DDL, flaky transaction-based DDL, or a very substantial investment in
time and complexity to make DDL operations transactional and completely
reversible, what would you choose?

At the dawn of time, there were no internal engine DDL operations, and
DYN was a convenience, not a mandatory interface. Those days are long
gone, and it's time we reaped the benefits of internal DDL.

>Running scripts - if one particular DDL operation fails, but a bunch
>of others are committed, how do you re-start the script? Or do you
>have to restore a backup to get the old (before you ran the script)
>state of the db back? And if you cannot rollback all changes, but
>there simply was a very very small mistake - you then have to run
>part of the script ... etc etc... It's much easier to be able to make
>a whole bunch of changes, or to cancel them all at once.
>

Scripted based on "upgrade" rather than "create" can be rerun ad nauseum
without effect since an upgrade operation that matches the status quo is
a no-op. It's a different but superior way of attacking the same problem.

--

Jim Starkey
Netfrastructure, Inc.
978 526-1376