Jim,

One thing though --

ONLY make the system tables a representation of the internal structures
if ALL metadata changes can be done via normal DDL.

For example, currently:

- changing a column from NULLable to required -> system tables change
- changing the default for a column -> system tables change
- trying to modify a view -> either a long set of DDL systems or a system
tables hack

There's probably more, but these I found out to be safe.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> For those of you not familiar with the innards of the engine's metadata
> handling, here is the life cycle of a SQL DDL statement:
>
> 1. The statement is parsed, validated, and analyzed by DSQL then
> transmogrified into a DYN string. DYN is a linear, binary, byte
> encoded structure for expressing low level metadata updates.
> 2. The DYN string is parsed and interpreted, the interpreter making
> direct updates to the system tables.
> 3. Internal triggers on the system tables catch the updates and
> create DFW (deferred work blocks) to perform magic at commit time.
> 4. At commit time, the DFW mechanism cycles through the DFW blocks N
> times. This allows interleaving of steps from different DFW blocks.
> 5. The various DFW steps tweak data structures, update other system
> tables, update primary system tables, and god knows what else.
>
> For contrast, here is how the same operations are done in Netfrastructure:
>
> 1. The statement is parsed, validated, and analyzed. The internal
> data structures are modified to reflect the change.
> 2. The object in question (for example, an instance of the Table
> class) is asked to save itself to the system tables.
>
> I have no stomach for even thinking about rewriting the Firebird steps 1
> through 5. I would rather replace them with an analog of the
> Netfrastructure steps 1 and 2.
>
> This is my recommendation on how to proceed post-Vulcan:
>
> 1. Build a reusable module that models metadata and can generate SQL

DDL.

> 2. Replace the GBak direct system table updates with this module.
> 3. Make system tables read only except for internal engine operations
> 4. Get rid of DYN, MET, and DFW. DDL statements operate against
> internal data structures that update system tables to reflect the
> current state.