Re: dblink_ora - a first shot on Oracle ... - Mailing list pgsql-hackers

One reason I am excited about an Oracle-enabled dblink is that it gives
us a seamless way for PostgreSQL to operate in an evironment with
multiple database products, which I think is important.

As far as the Oracle libraries, once you have an Oracle-enabled patch in
CVS, I will put a some value in Makefile.global to indicate whether the
Oracle libraries were found by configure.  You can use any name you
wish.  I also see you include postgres.h, so I can add a C-level define
if that helps you too, though the easier way would be for the dblink
Makfile to add a -DWITH_ORACLE to the dblink compile line.

I will wait for you guys to complete your work.

---------------------------------------------------------------------------

Joe Conway wrote:
> Hans-J?rgen Sch?nig wrote:
> > Thanks a lot. I will integrate named connections as proposed by the most 
> > recent version of dblink as soon as possible.
> > Thanks for doing the configure stuff. What we need is Oracle's OCI 
> > interface and libsqlora (http://www.poitschke.de/libsqlora8/).
> 
> I was thinking that we should be merging dblink_ora (and eventually 
> jdbclink) into dblink. If you wanted to start down that road, here are 
> my thoughts on how that should be done.
> 
> dblink functions can be roughly divided into three groups. I'll address 
> each one separately:
> 
> 1) connection related
> 
>     SQL interface:
>     --------------
>     1.a dblink_connect (text <conn_str>)
>     1.b dblink_connect (text <conn_name>, text <conn_str>)
>     1.c dblink_disconnect ()
>     1.d dblink_disconnect (text <conn_name>)
> 
>     To these I would add:
>     1.e  dblink_connect (text <name>, text <conn_str>, text <conn_type>)
> 
>     Acceptable values for <conn_type> would be 'postgres', 'oracle', and
>     eventually 'jdbc', non-case-sensitive. New types could be added to
>     this list later if/when other connection types are developed.
> 
>     1.a and 1.b would default to type 'postgres'. Hence the only way to
>     get a non-postgres connection would be to use a named persistent
>     connection, but I think that makes sense anyway.
> 
>     Implementation:
>     ---------------
>     There are actually only two underlying functions, dblink_connect()
>     and dblink_disconnect(). Both of these should be modified so that
>     the mode (i.e. unnamed_default, named_default, named_with_type) is
>     discovered based on the arguments, then the real work farmed out to
>     connection type specific functions. I'm not entirely sure how it
>     should be handled when, for instance, the oracle library is not found
>     by configure. I suppose in that case you can use #ifdef's to provide
>     stubs with appropriate error messages.
> 
> 2) connection consumers
> 
>     SQL interface:
>     --------------
>     2.a dblink_open (text <cursor_name>, text <sql>)
>     2.b dblink_fetch (text <cursor_name>, int <num>)
>     2.c dblink_close (text <cursor_name>)
>     2.d dblink (text <sql>)
>     2.e dblink_exec (text <sql>)
>     2.f dblink_open (text <conn_name>, text <cursor_name>, text <sql>)
>     2.g dblink_fetch (text <conn_name>, text <cursor_name>, int <num>)
>     2.h dblink_close (text <conn_name>, text <cursor_name>)
>     2.i dblink (text <conn_name_or_str>, text <sql>)
>     2.j dblink_exec (text <conn_name_or_str>, text <sql>)
> 
>     2.a - 2.e all use the unnamed connection and therefore only apply
>     to connections of type 'postgres'
> 
>     2.f - 2.h all use named connections
> 
>     2.i and 2.j can use named connections or connection strings
> 
>     The existing named connections functions can be made to work with
>     <conn_type> == 'oracle', etc. It probably makes sense to add two
>     functions:
>     2.k dblink (text <conn_str>, text <conn_type>, text <sql>)
>     2.l dblink_exec (text <conn_str>, text <conn_type>, text <sql>)
>     This would allow dynamic specification of connections to
>     connection types other than 'postgres'.
> 
>     Implementation:
>     ---------------
>     Similar to the above, there is only one underlying function for each
>     SQL function name. These should be modified in a similar manner as
>     the connection functions.
> 
> 3) miscellaneous utility functions
> 
>     SQL interface:
>     --------------
>     3.a dblink_get_pkey
>     3.b dblink_build_sql_insert
>     3.c dblink_build_sql_delete
>     3.d dblink_build_sql_update
>     3.e dblink_current_query
> 
>     These functions don't use libpq, they use SPI or other internals.
>     I wouldn't try to support other types of databases for them.
> 
> 
> > Joe, you have told me some time ago that you are planning some sort of 
> > connection table which tells dblink to establish some connections 
> > automatically. Did you work on that?
> No, I wouldn't worry too much about that right now.
> 
> Comments?
> 
> Joe
> 
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073