| Subject | Re: [Firebird-Architect] Cross database queries: Requirements |
|---|---|
| Author | Jim Starkey |
| Post date | 2007-08-09T20:09:31Z |
Vlad Horsun wrote:
schema have any meaning other than as name qualifiers. Database vendors
tend to consider catalogs as independently maintained groups of tables,
meaning specifically that they may have inadvertent, non-semantic,
overlapping object names. Two independent Firebird databases could
legitimately be considered as separate catalogs except for the fact that
only QLI and to a lessor degree GPRE among the Firebird tools handle
access to multiple databases, and neither uses the concept of catalog.
Schema is generally understood to be a qualifier, no more and no less.
I believe it would be a serious mistake to try to use catalog to
differentiate between two classes of tables within a single database as
this would preclude its use for the more important case, addressing
tables in disjoint databases.
I wouldn't use the term "remote" or "local" with respect to databases.
Two local databases have the same problems and potential solutions as
two remote databases or one local and one remote. The problem is just
as acute, some would say more acute, with two independent databases on
the same physical computer.
There is no reason that a declared external table could not have
metadata represented in the core OSRI system tables (RDB$RELATIONS, et
al). It was intended from the beginning that different types of tables
would be represented, which is why there is a relation type field.
Interbase/Firebird have also supported access to non-database external
tables and have always supported views. There are no reasons whatsoever
that anyone should shy away from adding more table types. When I said
there was a problem with global field names, all I meant was it was
something that needed to be handled, just as global field names have to
be handled in DDL that does not include global field names. Nothing
prevents you from renaming the global field to RDB$nnn and getting on
with life.
so support must not be precluded even if it isn't implemented in a first
version.
On the other hand, I don't see how you can avoid implementing them.
request compilation process, I don't see how you can both base the
implementation of the existing engine and not support BLR.
Frankly, I think it is time to phase out BLR. Let's face, SQL won. I
didn't even bother in Netfrastructure (aka Falcon) and never missed it.
The Firebird engine must continue to support BLR, but I think it would
be very smart to build a parallel SQL based execution engine. Maybe a
brand, shiny new mega-database manager would be the place to prototype a
pure SQL engine that eventually could be moved back to the Firebird
engine...
identical to a local table and can be used without changing the client
or tool. "Works with" means that the client or tool may have to be
modified to know which tables are external database tables and which are
real and to manipulate them differently.
In my mind, "transparent" is the gold standard. Why should a client or
interface layer have to care about where a table is or what hoops the
greater database system must jump through to access it. To the client,
a table is a table is table.
really happy giving a single access account for all remote users,
essentially delegating his responsibility to an engine outside of his
administrative control. I think he would be a lot happier if he could
set up a mapping of local to remote accounts so the final level of
access control is performed at the remote system.
that said that system tables can't be extended. We even went to great
length to support user extensions to these tables. It is absolutely
legitimate to add additional fields. That a nice thing about the
relational model, you know....
I think you will need at least:
1. A "create external database" statement or equivalent that
specifies a name and an access string.
2. A system table RDB$DATABASES (or something)
3. Some statements to define mappings between local accounts and
external database accounts.
4. A system table or two to handle #3.
Just in case, as the original OSRI architect and inventory of the
original system tables, I hereby give you full permission to extend them
(wisely!) to do anything that needs doing.
Just keep it transparent, please!
>> 2. Preserve standard SQL semantics for statements that reference moreFrom the perspective of the standard, I don't believe that catalog and
>> than one database.
>>
>
> SQL standard is not clear about such statements. There are terms "catalog"
> and "schema" but there are no clear statement that "catalog" may be remote
> database or outside of local\current database. Correct me if i'm wrong
>
schema have any meaning other than as name qualifiers. Database vendors
tend to consider catalogs as independently maintained groups of tables,
meaning specifically that they may have inadvertent, non-semantic,
overlapping object names. Two independent Firebird databases could
legitimately be considered as separate catalogs except for the fact that
only QLI and to a lessor degree GPRE among the Firebird tools handle
access to multiple databases, and neither uses the concept of catalog.
Schema is generally understood to be a qualifier, no more and no less.
I believe it would be a serious mistake to try to use catalog to
differentiate between two classes of tables within a single database as
this would preclude its use for the more important case, addressing
tables in disjoint databases.
I wouldn't use the term "remote" or "local" with respect to databases.
Two local databases have the same problems and potential solutions as
two remote databases or one local and one remote. The problem is just
as acute, some would say more acute, with two independent databases on
the same physical computer.
There is no reason that a declared external table could not have
metadata represented in the core OSRI system tables (RDB$RELATIONS, et
al). It was intended from the beginning that different types of tables
would be represented, which is why there is a relation type field.
Interbase/Firebird have also supported access to non-database external
tables and have always supported views. There are no reasons whatsoever
that anyone should shy away from adding more table types. When I said
there was a problem with global field names, all I meant was it was
something that needed to be handled, just as global field names have to
be handled in DDL that does not include global field names. Nothing
prevents you from renaming the global field to RDB$nnn and getting on
with life.
>Architecturally, it is a must have. Cursors are part of standard SQL,
>> 3. Preserve standard SQL cursors, including "current of cursor".
>>
>
> Good to have but depends on support from remote side. Not sure it is
> high priority feature
>
so support must not be precluded even if it isn't implemented in a first
version.
On the other hand, I don't see how you can avoid implementing them.
>I agree that it isn't necessary, but since SQL disappears early in the
>>
>
>> 8. Works with the existing BLR client interface (quibble nor or lose
>> the right later!)
>>
>
> Good to have but not necessary
>
request compilation process, I don't see how you can both base the
implementation of the existing engine and not support BLR.
Frankly, I think it is time to phase out BLR. Let's face, SQL won. I
didn't even bother in Netfrastructure (aka Falcon) and never missed it.
The Firebird engine must continue to support BLR, but I think it would
be very smart to build a parallel SQL based execution engine. Maybe a
brand, shiny new mega-database manager would be the place to prototype a
pure SQL engine that eventually could be moved back to the Firebird
engine...
>"Transparent" means that an external database table is semantically
>> 9. Works with the existing SQL client interface.
>>
>
>
>> 10. Works with the existing remote interface, server, and line protocols.
>>
>
>
>> 11. Works with the existing ODBC drivers.
>> 12. Works with the existing JDBC drivers.
>> 13. Transparent to clients.
>> 14. Transparent to ODBC drivers.
>> 15. Transparent to JDBC drivers.
>> 16. Transparent to tools that use native Firebird system tables.
>>
>
> It depends on what you mean by "works" and "transparent"
>
identical to a local table and can be used without changing the client
or tool. "Works with" means that the client or tool may have to be
modified to know which tables are external database tables and which are
real and to manipulate them differently.
In my mind, "transparent" is the gold standard. Why should a client or
interface layer have to care about where a table is or what hoops the
greater database system must jump through to access it. To the client,
a table is a table is table.
>I think you want to think about this some more. Is a DBA going to be
>
>> 18. Should all users of a external database table use the same account
>> and password?
>>
>
> No, because it conflicts with :
>
>
>> 19. Should access control to external database tables be defined at
>> the database level or (redundantly) at the external database table
>> level?
>>
>
> At external level only.
>
really happy giving a single access account for all remote users,
essentially delegating his responsibility to an engine outside of his
administrative control. I think he would be a lot happier if he could
set up a mapping of local to remote accounts so the final level of
access control is performed at the remote system.
>Duh. (Meaning, sure).
>> This a starting point. Other folks may want to add or remove from these.
>>
>
> 20. Not force users to put much effort to maintain this new facility
>
>No, there has never been anything in DSRI, OSRI, Interbase, or Firebird
>
>
>
>> For transparency, external database
>> tables should be described in these system tables as ordinary tables,
>> external file system tables, and views.
>>
>
> Why ? To introduce external entities in local database we need to at
> least add some fields into existing RDB$xxx tables. This means that existing
> drivers and applications can't correctly query new RDB$xxx and must be
> upgraded : imagine two tables T in two different catalogs : X.T and Y.T
> (or T@X and T@Y) - existing drivers not know about catalogs and will think
> database have two tables T.
>
> To preserve compatibility we must not extend RDB$xxx and put
> external entities into some new tables
>
that said that system tables can't be extended. We even went to great
length to support user extensions to these tables. It is absolutely
legitimate to add additional fields. That a nice thing about the
relational model, you know....
I think you will need at least:
1. A "create external database" statement or equivalent that
specifies a name and an access string.
2. A system table RDB$DATABASES (or something)
3. Some statements to define mappings between local accounts and
external database accounts.
4. A system table or two to handle #3.
Just in case, as the original OSRI architect and inventory of the
original system tables, I hereby give you full permission to extend them
(wisely!) to do anything that needs doing.
Just keep it transparent, please!