Hi Ann,

At September 15, 2004, 18:11, Ann W. Harrison wrote:

> If you develop or are considering developing an interface
> tool that works with Firebird, I'd like your ideas about
> temporary tables. There's a discussion of creating temporary
> tables that are visible to a single attachment, persist
> across transactions, and are deleted with the attachment
> exits.

> How much infrastructure would your tools need to recognize
> and work with such tables?

I think the first place to look would be the SQL-2003 standard. The
issue of temporary tables is dealt with. There is 3 types of temporary
tables: Global table, Created local table and declared local table.

If the definition of the temporary tables found in the standard is not
enough, then I suppose that it can be expanded to fit FB's users
needs. Although, by reading the standard description, the contents of
a temporary table is either connection bound or transaction bound,
depending on how the table is declared.

Here's the definition in the SQL-2003 standard(4.14.2):
[Start quote...]

A global temporary table is a named table defined by a <table
definition> that specifies GLOBAL TEMPORARY. A created local temporary
table is a named table defined by a <table definition> that specifies
LOCAL TEMPORARY. Global and created local temporary tables are
effectively materialized only when referenced in an SQL-session. Every
SQL-client module in every SQL-session that references a created local
temporary table causes a distinct instance of that created local
temporary table to be materialized. That is, the contents of a global
temporary table or a created local temporary table cannot be shared
between SQL-sessions.

In addition, the contents of a created local temporary table cannot be
shared between SQL-client modules of a single SQL-session. The
definition of a global temporary table or a created local temporary
table appears in a schema. In SQL language, the name and the scope of
the name of a global temporary table or a created local temporary
table are indistinguishable from those of a persistent base table.
However, because global temporary table contents are distinct within
SQL-sessions, and created local temporary tables are distinct within
SQLclient modules within SQL-sessions, the effective <schema name> of
the schema in which the global temporary table or the created local
temporary table is instantiated is an implementation-dependent <schema
name> that may be thought of as having been effectively derived from
the <schema name> of the schema in which the global temporary table or
created local temporary table is defined and the
implementation-dependent SQLsession identifier associated with the
SQL-session.

In addition, the effective <schema name> of the schema in which the
created local temporary table is instantiated may be thought of as
being further qualified by a unique implementation-dependent name
associated with the SQL-client module in which the created local
temporary table is referenced.

A module local temporary table is a named table defined by a
<temporary table declaration> in an SQL-client module. A module local
temporary table is effectively materialized the first time it is
referenced in an SQL-session, and it persists for that SQL-session.

A declared local temporary table may be declared in an SQL-client module.

A declared local temporary table is a module local temporary table. A
declared local temporary table is accessible only by
externally-invoked procedures in the SQL-client module that contains
the <temporary table declaration> that declares the declared local
temporary table. The effective <schema name> of the <schema qualified
name> of the declared local temporary table may be thought of as the
implementation-dependent SQL-session identifier associated with the
SQL-session and a unique implementation-dependent name associated with
the <SQL-client module definition> that contains the <temporary table
declaration>.

All references to a declared local temporary table are prefixed by
“MODULE.”.

The materialization of a temporary table does not persist beyond the
end of the SQL-session in which the table was materialized. Temporary
tables are effectively empty at the start of an SQL-session.

[...End quote]

And, here's the syntax description:

[Start quote...]

14.13 <temporary table declaration>

Function

Declare a declared local temporary table.

Format

<temporary table declaration> ::=
DECLARE LOCAL TEMPORARY TABLE <table name> <table element list>
[ ON COMMIT <table commit action> ROWS ]

Syntax Rules

1) Let TN be the <table name> of a <temporary table declaration> TTD,
and let T be the <qualified identifier> of TN.
2) TTD shall be contained in an <SQL-client module definition>.
3) Case:
a) If TN contains a <local or schema qualifier> LSQ, then LSQ shall
be “MODULE”.
b) If TN does not contain a <local or schema qualifier>, then
“MODULE” is implicit.
4) If a <temporary table declaration> is contained in an <SQL-client
module definition>M, then the <qualified identifier> of TN shall not
be equivalent to the <qualified identifier> of the <table name> of any
other <temporary table declaration> that is contained in M.
5) The descriptor of the table defined by a <temporary table
declaration> includes TN and the column descriptor specified by each
<column definition>. The i-th column descriptor is given by the i-th
<column definition>.
6) A <temporary table declaration> shall contain at least one <column
definition>.
7) If ON COMMIT is not specified, then ON COMMIT DELETE ROWS is
implicit.

Access Rules

None.

General Rules

1) Let U be the implementation-dependent <schema name> that is
effectively derived from the implementationdependent SQL-session
identifier associated with the SQL-session and an
implementation-dependent name associated with the SQL-client module
that contains the <temporary table declaration>.
2) Let UI be the current user identifier and let R be the current role
name.
Case:
a) If UI is not the null value, then let A be UI.
b) Otherwise, let A be R.
3) The definition of T within an SQL-client module is effectively
equivalent to the definition of a persistent base table U.T. Within
the SQL-client module, any reference to MODULE.T is equivalent to a
reference to U.T.
4) A set of privilege descriptors is created that define the
privileges INSERT, SELECT, UPDATE, DELETE, and REFERENCES on this
table and INSERT, SELECT, UPDATE, and REFERENCES for every <column
definition> in the table definition to A. These privileges are not
grantable. The grantor for each of these privilege descriptors is set
to the special grantor value “_SYSTEM”. The grantee is “PUBLIC”.
5) The definition of a temporary table persists for the duration of
the SQL-session. The termination of the SQL-session is effectively
followed by the execution of the following <drop table statement> with
the current authorization identifier A and current <schema name> U
without further Access Rule checking: DROP TABLE T CASCADE
6) The definition of a declared local temporary table does not appear
in any view of the Information Schema.

NOTE 396 — The Information Schema is defined in ISO/IEC 9075-11.

Conformance Rules

1) Without Feature F531, “Temporary tables”, conforming SQL language
shall not contain a <temporary table declaration>.

[...End quote]


--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)