At 01:00 PM 2/6/01 -0000, ian@... 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.

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.

Assuming that I have gotten smarter (or maybe just a little
more mellow), when I designed Netfrastructure I made
the core system tables reflect the structure (but not the
names) of the ODBC/JDBC metadata result sets. On the
other hand, system tables are read only to everyone but
the engine itself. Well, actually the guy who created
the database can tweak them, but there is not magic
sitting behind them.

I have made my peace with database standards by adopting
the JDBC interface as the native interface for the
Netfrastructure content store. In the long run, I
think Firebird would be well adviced to do likewise.
Trying to nudge the industry is essentially impossible
at this point, and given the mindset of most potential
users, pointless.

Jim Starkey