Hi,

> >>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.

Then there's quite some ground to cover, as that's currently
not the case at all.

> >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?

You cannot, in the same transaction, drop an existing FK
and re-create it with, for example, different "ON DELETE"
attributes, or other columns.

You will get the "object in use" error.

Same goes for dropping multiple FKs that point to the
same table.

Very annoying when doing ad-hoc development or changes
to an existing db.

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...

> >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.

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.

> >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.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com