From: Joe Conway <mail@joeconway.com>
To: Hans-Jürgen Schönig <hs@cybertec.at>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Hans-Jürgen Schönig <postgres@cybertec.at>
Cc: pgsql-hackers@postgresql.org, EG <EG@cybertec.at>
Subject: Re: dblink_ora - a first shot on Oracle ...
Date: Sat, 19 Jul 2003 11:36:52 -0700
Message-ID: <3F198FC4.4010400@joeconway.com> (raw)
In-Reply-To: <3F191DC7.3060009@cybertec.at>
References: <200307190119.h6J1JtA09515@candle.pha.pa.us>
<3F191DC7.3060009@cybertec.at>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: mail@joeconway.com, hs@cybertec.at, pgman@candle.pha.pa.us, postgres@cybertec.at, EG@cybertec.at
Subject: Re: dblink_ora - a first shot on Oracle ...
In-Reply-To: <3F198FC4.4010400@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox