Re: SQL/MED compatible connection manager

martin.pihlak@gmail.com (Martin Pihlak) writes:
> Tons of details have been omitted, but should be enough to start discussion.
> What do you think, does this sound usable? Suggestions, objections?

Slony-I does some vaguely similar stuff in its handling of "connection paths"; here's the schema:

create table @NAMESPACE@.sl_path (pa_server            int4,pa_client            int4,pa_conninfo            text NOT
NULL,pa_connretry       int4,
 
CONSTRAINT "sl_path-pkey"    PRIMARY KEY (pa_server, pa_client),CONSTRAINT "pa_server-no_id-ref"    FOREIGN KEY
(pa_server)   REFERENCES @NAMESPACE@.sl_node (no_id),CONSTRAINT "pa_client-no_id-ref"    FOREIGN KEY (pa_client)
REFERENCES@NAMESPACE@.sl_node (no_id)
 
) WITHOUT OIDS;
comment on table @NAMESPACE@.sl_path is 'Holds connection information for the paths between nodes, and the
synchronisationdelay';
 
comment on column @NAMESPACE@.sl_path.pa_server is 'The Node ID # (from sl_node.no_id) of the data source';
comment on column @NAMESPACE@.sl_path.pa_client is 'The Node ID # (from sl_node.no_id) of the data target';
comment on column @NAMESPACE@.sl_path.pa_conninfo is 'The PostgreSQL connection string used to connect to the source
node.';
comment on column @NAMESPACE@.sl_path.pa_connretry is 'The synchronisation delay, in seconds';

I wouldn't be surprised to find there being some value in using
something like SQL/MED.

One detail I'll point out, that I'm noticing from an application I'm
working on right now.  We might want to have something like a "db
connection" data type; here's a prototype I put together:

slonyregress1=# create type dbconn as (port integer, dbname text, username text, password text, ssl boolean);
CREATE TYPE
slonyregress1=# create table dbconns (id serial primary key, db dbconn);
NOTICE: CREATE TABLE will create implicit sequence "dbconns_id_seq" for serial column "dbconns.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "dbconns_pkey" for table "dbconns"
CREATE TABLE
slonyregress1=# insert into dbconns (db) values ((5432, 'slonyregress1', 'slony', 'secret!', 'true'));
INSERT 0 1
slonyregress1=# select * from dbconns;id | db 
----+-------------------------------------- 1 | (5432,slonyregress1,slony,secret!,t)(1 row)

I'm not certain that this is forcibly the right representation, but I
think it is possible that we'd want a finer-grained representation
than merely a connection string.
-- 
(reverse (concatenate 'string "ofni.sesabatadxunil" "@" "enworbbc"))
http://linuxdatabases.info/info/finances.html
"DTDs are  not common knowledge  because programming students  are not
taught markup.  A markup language is not a programming language."
-- Peter Flynn <silmaril@m-net.arbornet.org>