search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.24k stars 212 forks source link

Theoretical question: Define a view based on an ODBC connection #6894

Open bert1973 opened 3 years ago

bert1973 commented 3 years ago

Firebird has support for:

  1. External tables (Fixed Length files), No Blobs, Nulls
  2. External Datasource (FB db's only)

Would it theoretical be possible to implement functionality to define a view based on an ODBC connection? Suggested syntax: CREATE VIEW (FIELD1, FIELD2) AS SELECT FIELD1, FIELD2 FROM TABLE1 EXTERNAL ODBC CONNECTION 'DSN=SQLite3 Datasource;Database=test.sqlite3'

Or is this a 'no go' on forehand because of technical limitations?

hvlad commented 3 years ago

Yes, it is possible, while not as easy as we could wish.

aafemt commented 3 years ago

Would it theoretical be possible to implement functionality to define a view based on an ODBC connection?

For the beginning you can try this: https://www.ibphoenix.com/products/software/magpie

bert1973 commented 3 years ago

I took a look at parse.y, DdlNodes.h and DdlNodes.epp, but that's also a possibility, I'll try that one first. Thanks!

aafemt commented 3 years ago

In general this improvement would require db-links. As soon as they are here, you can use them to get data from any database that is supported by installed engine providers. Currently you are limited to ES-on-EDS functionality.

asfernandes commented 3 years ago

There is an example in FB/Java here https://github.com/FirebirdSQL/fbjava/blob/f5cf151f819bd4517897baae100d9177395d1991/examples/fbjava-example/util/code.sql#L35

So instead of create a view, you create a procedure with its SQL, and then just call it as a view: select * from employee_pgsql.

Of course a WHERE clause you put in the caller 'SELECT` is processed in Firebird after reading all rows from the external select. One could create another form of filters (passed as parameter).

Similar thing could be done even with UDR.

aafemt commented 3 years ago

Of course a WHERE clause you put in the caller 'SELECT` is processed in Firebird after reading all rows from the external select.

Yes, and this is the primary problem with database links. To make them really useful Provider plugins must expose some kind of query builder interface to let optimizer to push filtering, ordering and grouping to the remote side.