Re: SQL/MED compatible connection manager

martin(dot)pihlak(at)gmail(dot)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(at)(dot)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(at)(dot)sl_node (no_id),
CONSTRAINT "pa_client-no_id-ref"
FOREIGN KEY (pa_client)
REFERENCES @NAMESPACE(at)(dot)sl_node (no_id)
) WITHOUT OIDS;
comment on table @NAMESPACE(at)(dot)sl_path is 'Holds connection information for the paths between nodes, and the synchronisation delay';
comment on column @NAMESPACE(at)(dot)sl_path(dot)pa_server is 'The Node ID # (from sl_node.no_id) of the data source';
comment on column @NAMESPACE(at)(dot)sl_path(dot)pa_client is 'The Node ID # (from sl_node.no_id) of the data target';
comment on column @NAMESPACE(at)(dot)sl_path(dot)pa_conninfo is 'The PostgreSQL connection string used to connect to the source node.';
comment on column @NAMESPACE(at)(dot)sl_path(dot)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(at)m-net(dot)arbornet(dot)org>