Subject Re: [Firebird-Architect] RFC: Proposal for the implementation of Temporary Tables.
Author Martijn Tonies

> Syntax:
> CREATE TEMPORARY TABLE <table name> <table contents source>


for tables that are accessible over connection boundaries.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Upscene Productions

> Syntax:
> If the optional ON COMMIT clause is omitted, the contents of the temporary
table are discarded at the end of the transaction that
> created them.
> Semantics:
> An entry for relation <table name> is made in the RDB$RELATIONS system
table. Entries are made in RDB$RELATION_FIELDS and
> RDB$FIELDS relations describing the fields listed in the <table contents
source>, as they would be for a permanent table. The
provisional and unusable until the transaction which issued the
> CREATE statement commits.
> All connections can see the definition of temporary tables; temporary
tables are part of the global name space. No connection can
> see data stored in a temporary table by another connection.
> A temporary table can be either the referenced or referencing
table in a foreign key constraint, but the partner table must
> be a temporary table. If either table is ON DELETE COMMIT, both must be
> System table support:
> A new field will be added to RDB$RELATIONS, called RDB$RELATION_TYPE. If
that field value has the value RDB$PERMANENT, the table is
> permanent. If the field value is RDB$TEMPORARY_PRESERVE the table is
temporary and contents are retained until the connection which
> created them terminates. If the value is a RDB$TEMPORARY_DELETE, the
table is temporary and contents are discarded at the end of
> the transaction which created them.
> Notes:
> No changes are proposed to ALTER TABLE or DROP TABLE. Changing a table
from permanent to temporary or vice-versa is accomplished
> by dropping the table and recreating it.
> The syntax AS <query expression> has been suggested as an alternative to
the <table contents source> listed in the syntax above. If
> that syntax is part of the SQL-99 standard, I can't find it.
> Implementation:
> A temporary table is represented internally by a TemporaryRelation
class, which is a subclass of the Relation class. A
> TemporaryRelation instance is created either by dfw, CStatement, or during
the processing of blr. TemporaryRelation classes hang
> off the attachment object as well as the Database object. The
TemporaryRelation object provides the location of the index root
> page for that table instance and it's pointer page list.
> Changes to VIO, DPM, NAV, BTR, and IDX would be primarily cleanup and
applying object discipline to the driving structures. Record
> source blocks become a class hierarchy. References to the source
relation would be through either the TemporaryRelation object or
> the Relation.
> Data and indexes for a temporary table could be stored on pages in the
active database or in a temporary file created for the
> instance. Each alternative has advantages.
> Using the active database leverages the existing structures. Free space
can be located with existing mechanisms completely
> unchanged. Page number references in indexes and blobs can be used
directly. Cleaning up the pages used by a temporary table in
> the case of a system crash is problematic.
> Using a temporary file requires that space management and all page
references be redirected to the temporary file, increasing the
> number of layers that must be aware of this change. A temporary file is
automatically destroyed in case of system failure.
> Paul Beach
> Main Tel (UK):+44 (0) 1844 354357
> Mobile: (UK): +44 (0) 7837 898072
> Yahoo! Groups Links