Re: Withdraw PL/Proxy from commitfest

On Wed, Oct 22, 2008 at 11:02:34AM +0300, Martin Pihlak wrote:
> Hannu Krosing wrote:
> >> It is cleverly hidden. The CREATE SERVER and CREATE USER MAPPING take
> >> generic options (list of key/value pairs). These can be used for
> >> defining the actual connection to the remote server.
> >
> > Are you sure this is how it is intended to be done ?
> >
> Yes.
>
> > In pl/proxy context this would mean that in order to define connection
> > info we would at least need (foreign) SERVER and USER MAPPING objects
> >
> > defined so
> >
> > CREATE SERVER <foreign server name>
> [snip]
> > CREATE USER MAPPING
> [snip]
> > plus a possibility to GRANT USAGE on those and also the function
> > ConnectServer(<foreign server name>) to actually make the connection.
> >
>
> Yes, and with the generic options actually any connection can be described
> (dbi, odbc, ...). In some situations the client module would probably need
> to know the type of the connection - this is where we could use either
> TYPE or FDW from the server definition.
>
> > I guess we can skip the FOREIGN DATA WRAPPER stuff until we actually
> > need it.
> >
>
> Actually a minimal FDW would be useful for validation the generic options
> of SERVER and USER MAPPING. For instance this could be used to check that
> server options are valid libpq conninfo parameters and that no password
> is present in server options. The downside is that a wrapper would need to
> be provided for all driver types (pgsql, dbi, ...). But I guess we could
> start with just two - pgsql and "default". The latter would not validate
> anything, thus enabling arbitrary options to be specified.
>
> The FDW could also provide a function for obtaining the connection details
> for the server and user mapping pair. For libpq connections it could merge
> the server and user options into single connect string. Others probably
> need username and password separately. This would take some of the complexity
> off the client -- it needs not be concerned with what the actual option
> keywords mean (there could be some that are not part of connect string).
>
> For clients we could then provide a SQL accessible wrapper function that would
> lookup server and user mapping. Then call the FDW to obtain connection details.
> Something along the lines of:
>
> # select * from pg_get_remote_connection_info('remotedb');
>
> key value
> ----------- ----------------------------------------
> conninfo dbname=remotedb host=remotehost user=...
>
> Whereas dbi might also include a username and password:
>
> key value
> ----------- ------------------------------------------------------------
> conninfo dbi:mysql:database=sakila;host=localhost
> username root
> password salakala
>
> What do you think, usable?

Sure.

DBI's DBD::Excel <http://search.cpan.org/~kwitknr/DBD-Excel-0.06/Excel.pm>
is a more extreme example of this kind of thing. You can get connect
strings that look like this:

my $hDb = DBI->connect(
"DBI:Excel:file=dbdtest.xls",
undef,
undef,
{
xl_vtbl => {
TESTV => {
sheetName => 'TEST_V',
ttlRow => 5,
startCol => 1,
colCnt => 4,
datRow => 6,
datlmt => 4,
}
}
}
);

That last bit with the hash of hashes of hashes is probably where we'd
use CTEs to expand things out.

Speaking of CTEs, now that we'll have them, is there anything we could
be doing more easily in pg_catalog, etc.?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate