Subject R: [Firebird-Architect] Atomic DDL and Metadata Transactions.
Author Marco Lauria
> -----Messaggio originale-----
> Da: Martijn Tonies [mailto:m.tonies@...]
> Inviato: mercoledì 9 giugno 2004 19.31
> A:
> Oggetto: Re: [Firebird-Architect] Atomic DDL and Metadata Transactions.
> Hi Jim,
> > >Well, for example, Database Workbench uses this ability to alter
> > >several things at once for a table in the GUI.
> > >
> > >You can, for example, add a couple of columns, drop a few,
> > >modify and add some triggers. Next press "Save" and all actions
> > >get saved or backed out when something critical happens. This
> > >"critical" part can be a typo in a trigger, for example.
> > >
> > >How would this work with Vulcan? My guess is, that it doesn't -
> > >right?
> > >
> > >
> > I agree with the need for the capability. But there's a much better way
> > to provide it. An "upgrade table" verb that transforms an existing
> > table into new state is a great deal more powerful than a sequence of
> > uncommitted alters and is safer to boot. It also addresses the
> > restartability problem of upgrade scripts since an "upgrade" to the
> > table's current state is a no-op.
> >
> > The "upgrade table" statement isn't on my plate, but is straightforward
> > to implement. Syntactically, it's equivalent to "create table". The
> > rest of the implementation is looping through the syntax nodes checking
> > the new definition against the existing one. If a difference is found,
> > a change to the metadata tables is done then and there. To really
> > exploit it for development, however, you need a library function to
> > generate "upgrade table" statement directly from the database, then wrap
> > a GUI around it.
> For a table alone, this would work fine. But how about table
> "child-objects", like a Trigger?
> Because of Firebirds current capabilities, Database Workbench
> implemented an user interface where you could edit whatever
> you like and do a "save" for all objects (table columns, triggers,
> indices, constraints) in a single mouse click.
> I know I'm talking from my point of view - but hey, that's what
> I know ;-)

I think this is not only your point of view...
All of my upgrade scripts counts on this capability in order to roll back all the changes if there is an error as you wrote before modifying a trigger or a stored procedure.

I think this capability is needed in order to do upgrades of databases without having to do a full backup...

If every changes is automatically committed and you have an error on the 5th statement of 10 you will end up with a wrong database that has neither the old metadata or the new one...

My two cents...
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
> Server.
> Upscene Productions
> Yahoo! Groups Links