Re: dblink_ora - a first shot on Oracle ...

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073