> -----Original Message-----
> From: Jim Starkey [mailto:jas@...]
> Sent: Jueves 8 de Febrero de 2001 15:41
>
> At 01:00 PM 2/6/01 -0000, ian@... wrote:

[snip]

> > I guess that I am indicating that all system information and setup
> >be able to be set with SQL from any platform.
>
> 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.

Were all known system tables (with the exception of rdb$user_privileges,
rdb$roles and rdb$formats) defined in the DSRI documents? Or with "core" you
mean a few system tables?

> Rdb/ELN and Interbase went
> further, and made the system tables the primary data definition
> interface.

Just curious, how did the other pre-relational engines deal with data
definition?

> 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.

It's near to impossible. Every engine has its own set of capabilities.
Define an standard and it will be violated by every db vendor. The best that
could be asked is a minimal set of system tables... now talk about the
fields in those tables and we have 15 years of discussions. Some engines do
almost everything through manipulation of real or fake system tables. (Take
a peek at MsSql tables, for example.) However, much of those manipulation
occurs with proprietary commands that extend (or mangle) SQL's DDL.

> 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.

If I understand your idea, system tables should be informative, mantained
by the engine and R/O for everyone, but they shouldn't initiate the creation
of db objects, so DDL should go straight to the raw C/C++ code to create
those objects instead.
A couple of times I've asked myself is DYN is necessary in those days. I
know you created it initially due to a licensing restriction (gdef not given
to customers) and not due to a technical problem, so maybe in the future
things can be reshaped? The current behavior hurts my dreams, since it
should be something like:
- Send DDL through DSQL
- DDL is recognized and DYN is generated
- DYN is passed to the Y-valve
- BLR is generated to change system tables
- Db objects are created, modified or deleted.

> 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.

Well, I have to agree that the concept of active system tables attracts me,
but in practice is not easy to deal with system tables directly. We already
have two levels of security (rdb$security_classes v/s rdb$user_privileges
plus rdb$roles), so I can't imagine what we could get if we would add a set
of ODBC/JDBC metadata-compliant system tables on top of the current ones.

> 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.

Sorry for being a pain, but are you suggesting a change of known system
tables or an addition of a new layer?

> Trying to nudge the industry is essentially impossible
> at this point, and given the mindset of most potential
> users, pointless.

Everybody thinks that the own solution is the best. Do I need to remember
readers how many years did the SQL committee take to include procedures in
the standard, for example? Who's following such standard?

(I'm currently facing the idea of extracting metadata, but following the
many intricacies of IB, so metadata information -or lack of all items I
would need- has became a sore point for me.)

C.