> Thoughts?

Sure...

> DFW and metadata operations subject to transaction control are artifacts
> of supporting direct update of system tables. There were a number of
> reasons that I wanted to support direct update of system tables in
> Rdb/ELN and Interbase, but based on reflection and experience, I'm
> willing to concede they were wrong.
>
> The original Interbase engine didn't not have support for DDL, nor was
> there a generally accepted DDL to support. This is not longer the case,
> however.
>
> The pre-Vulcan implementation of SQL DDL is layered rather than
> integral, which requires support for direction update of system tables.
> Vulcan, however, pushes "dsql" into the engine proper, and could
> dispense with direct update of system tables in entirety.
>
> I'd like people to think about the implications of dropping support for
> direct update of system tables in favor of internal engine support for
> proper SQL DDL. The implementation would certainly use an ad hoc
> special transation committed or rolled back on a per statement basis.
> The same semantics would probably makes sense to retrofit over DYN,
> which I wouldn't mind dropping, but probably isn't feasible to do so.
>
> Decommitting direct update of system tables wouldn't necessarily
> eliminate DFW immediately, but would turn it from a general mechaninsm
> into one restricted to support well defined, bounded, internal

requirements.

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.

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?

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

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 :-)


Guess that's it... For now.

With regards,

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