Martijn Tonies wrote:

>>From a user and tools developer POV, there are a couple of
>things that matter (to me) when changing the current behaviour
>of DDL.
>
>1) make sure any operation possible via either current DDL or
> "modify the system tables DML" is possible in the new DDL.
>This includes adding/dropping NOT NULL constraints to colums,
>decent ALTER VIEW functionality (yes, please!!) and much more.
>

Absolutely. SQL DDL (extended) must be able to express any legal change.

I've probably mentioned it before, but I'm extremely partial to the
"upgrade" DDL verb, which creates the object, if necessary, or does
whatever modification is necessary to the target object. I do with in
Netfrastructure, but with the additional rules that "upgrade" doesn't
drop fields or shrink fields, essentially making it a DDL merge. The
various DDL tools generate upgrade statements from metadata, so adding
fields, etc., is no more than modifying the DDL statement and
reexecuting it. The net result is that DDL fields are completely
unnecessary (and also unused). Remove and shrinking fields must be done
with "alter", but are almost never done.

But yes, complete coverage of creation/modification/deletion of all
database objects is absolutely necessary.

>2) make it so that a DROP FK and CREATE FK don't block
>eachother - or, perhaps even better yet, make a ALTER
>CONSTRAINT statement?
>

This sounds right, but maybe I don't understand the problem?

>
>3) make it so, that multiple DDL operations can happen in the
>same transaction, despite what they are. I should be able to
>create 100 tables, add 250 FKs, create a dozen views etc etc
>with UNCOMMITTED metadata. This makes sense. Yes, it
>does. If one op fails, a user should be able to do a "rollback".
>

I don't understand why this is either necessary or desirable. Making
individual DDL operations atomic makes sense to me. Undoing a bunch of
DDL statements doesn't. It sounds like you have a specific problem in
mind. Could you describe a scenario where you might need to undo a
sequence of DDL operations? It sounds like there's something
interesting lurking there which may have serveral possible solutions.

>
>4) if DML and DDL cannot be mixed, don't accept it - raise
>an exception. Don't bother with warnings in release notes - raise
>an exception. Exceptions are cool. At least, they are when they
>are clear :-)
>

I suppose that transaction semantics could be redefined to disallow DML
in a DDL transaction (am I correct that access to objects pending
alternation are blocked from usage?), but absent a clear requirement for
DDL operations to be non-atomic, I tend to favor the more simple design.

--

Jim Starkey
Netfrastructure, Inc.
978 526-1376