--- In IB-Architect@y..., Jim Starkey <jas@n...> wrote:

> At 01:00 PM 2/6/01 -0000, ian@w... wrote:
> >
> > I know that at the moment a number of interbase/third party tools
> >can extract the meta data for a database for example, but I have
> >noticed that all these work in slightly different ways. To me, it
> >would be much nicer if the database itself could be interigated via
> >SQL to obtain information required.
> >
> > I guess that I am indicating that all system information and

setup

> >be able to be set with SQL from any platform.
> >
> > Feel free to tell me that this is a bad idea!
> >
>
> It's a very good idea, but it hasn't been a successful idea.

To say the least.

For what it's worth [I heard that], SQL92 included specifications for
standard system tables (actually, views -- there are approximately 25
if you want to collect the whole set), collectively known as the
Information Schema. The Information Schema is intended to be used for
query only, not update.

The idea of including these in the SQL standard was that these
standard views could be layered over any vendor's proprietary metadata
mumbo-jumbo, and they'd provide a standard way of viewing the metadata
for any database. Laudable.

Support for the Information Schema was not required for Entry Level
conformance in SQL92, it was only required for anyone claiming
Intermediate Level conformance ... in SQL terms, that's well along the
road to never-never land.

(I believe there was an attempt made in the FIPS Transitional standard
(another winner) to require support for a subset of the SQL92
Information Schema, however they got into a bit of a muddle because
supporting the Information Schema requires support for names > 18
characters, but 18 character names is all that's required for Entry
Level, etc. etc., death spiral, etc.)

>
> The origins of Firebird go back to DSRI (DEC Standard Relational
> Interface) which specified a core set of system tables that
> described everything, including themselves. Rdb/VMS, Rdb/ELN,
> Interbase, a modest collection of gateways, and a database
> replicator where built to the standard with a very high
> degree of interoperability. Rdb/ELN and Interbase went
> further, and made the system tables the primary data definition
> interface.
>
> As far as I know, nobody else in the industry has tried to
> standardize system tables. ODBC (and its correspondant JDBC)
> use metadata calls that specific result sets, which can be
> treated by utilities as system tables. But you can't do
> SQL against them, you can do arbitrary subsetting, you
> can't join them, and you can't change the ordering. Sure
> would be nice to have a standard set of tables (or view)
> and do SQL on them, but nobody cares.
>
> Active system tables (created a table by storing into
> RDB$RELATIONS) have not proven to be a good idea. The
> only folks (outside of the system utilities) that use
> them generally regret the experience.

Oh, yes.

[Again, for what little it's worth ...] There was a proposal made many
years ago to the SQL committee, suggesting that life would be so much
nicer if they got rid of DDL entirely, since people could achieve the
same thing using direct updates to system table things. (Right? Uhhh,
right?) Nice, in theory, but the vendors in the room went into a
collective state of apoplexy ... possibly one of the few times they've
been in agreement on anything :-)

>
> Assuming that I have gotten smarter (or maybe just a little
> more mellow), <...>

Mellow? Say it isn't so!

> I have made my peace with database standards <...>

Double gasp! OK, who really wrote this message, anyway? :-)

db