Roman Rokytskyy Wrote:

>I'd suggest to extend the tables like this:

>CREATE TABLE table [EXTERNAL {[FILE] 'filespec' | DATA SOURCE <ds_name>}]

>CREATE VIEW name [(view_col [, view_col .])]
>{AS <select> [WITH CHECK OPTION] | DATA SOURCE <ds_name>}

>That means that external tables has to be defined in the database
>explicitly. As to the explicit queries - if they are used often - people
>can define them as views (finally - as selectable procedures, add syntax
>extension to CREATE PROCEDURE statement).

>I do not consider the cross-database queries as something that should be
>very flexible - at the end it is about integrating two databases, not
>about acting as router to another DB.

I agree with this. The purpose for which we need this feature is to be able to split our Point
of Sale system databases between the configuration information (and the most recent few days of
collected data), and the "historical" collected data. Our system has over 200 tables, only about
20 of which store sales, timeclock data, security logs, etc. The configuration information plus
the last few days of (not yet locked) sales data would rarely exceed 100MB, even in a 20GB
database containing 5 years of sales data for 30 locations. If we can divide this, we can do
much more frequent backups of the "dynamic" data, and still be able to query the historical data
for reporting. But we need to be able to join the collected data to the configuration in order
to do complex where clauses.

For us, defining the tables in the history database as "foreign tables" in the current database
would be easy to do. It also sounds like it would address some of the complexities of doing
cross database joins.

>> 5. Transactions

For our purposes, we would never do inserts and updates to the external database within the same
transaction as updates to the configuration data. The two phase commit and savepoint stuff
wouldn't be necessary for our application.

I know we don't want to limit this functionality to just the needs of DRB Systems, but at the
same time, it may help with the design work if we have a better idea of the kinds of things
people need to be able to do with it. I agree with Roman that we (meaning DRB Systems) don't
need this to be a generic gateway to any external databases with no limitations on the
functionality available (except for the inevitable inefficiency inherent in a fully flexible
solution.) Instead, we need to be able to do aggregate queries on large amounts of external
data that is joined to local data to determine what records to keep. Speed is much more
important than flexiblity.