Re: [Gnumed-devel] gmrecalls.sql

[Ian Haywood]

On Fri, 10 Jan 2003 01:56:19 +1100
Horst Herb <address@hidden> wrote:

> I have submitted gmrecalls.sql, the first draft of a generic recalls database.
> 
> Plenty to do, like server side functions to facilitate regularly recurring 
> recalls (like biannual Pap smears)  or recalls recurring following a given 
> schema (like vaccinations)
A Python rule database would be good for this (instead of ad hoc functions 
for each of these clinical scenarios)

This would be easy, except for the need for cross-database queries.
dblink () can do this, if we know the host. This means each database 
has to be hard-coded with the hostname of every other database.
(they can grab it from gmconfiguration.sql, but they still need to know where 
*that* is)
Of course, this depends of the user and profile....

Proposed solution:
Each user, on connection to each and every database other than gmconfiguration, 
uses "CREATE LOCAL TEMPORARY TABLE" to set the locations of all the other 
services
for that session. This overrides an empty ordinary table of the same name (say 
'remote_dbs')

The advantage of this is that dblink () can sit behind a view to emulate the 
remote table transparently. 

Example:

In schema:
create table remote_dbs (service text, conn text);

create view identity as select 
        int dblink_tok (t1.ptr, 0) as id,
        dblink_tok (t1.ptr, 1) as pupic,
        dblink_tok (t1.ptr, 2) as gender,
        date dblink_tok(t1.ptr, 3) as dob,
        dblink_tok (t1.ptr, 4) as cob
from (select dblink ((select conn from remote_dbs where 
service='demographica'), 'select id, pupic, gender, dob, cob from identity') as 
ptr) as t1;

At connection:
create local temporary table remote_dbs (service text, conn text);
insert into remote_dbs (service, conn) values ('demographica', 
'hostaddr=192.168.0.2 dbname=demographica user=ian password=mypass');


The big advantage, as I see it is that, those running GNUMed on a single server,
don't create the views, and leave remote_dbs empty, so avoiding an otherwise 
pointless
speed penalty.

> Still needs a function to check referential integrity for references outside 
> of the recall database (only one external reference so far: identity(id)).

This would allow a standard REFERENCES clause to work.

Ian

pgpzni7nw0WB4.pgp
Description: PGP signature