Re: [Gnumed-devel] vote on postgres interface

[Horst Herb]

On Thursday 08 August 2002 11:53 pm, you wrote:

> However, we could regret leaving DB-API severely if we ever need to
> switch databases (I admit I can't think why, Postgres aren't exactly
> going to go bankrupt)
>
> Humble suggestion: gmPG implements its own wrapper, say:
>
>       query (service, string)
>       async_query (service, string, callback)

The problem is not sending the queries, which works pretty much the same with 
all adapters. The problem is the typing of the results: every adapter returns 
something different for date, time, timestamp etc.

A wrapper would need to parse all returned results and type-cast into 
whatever we agree to. Unless we patch the adapter, which is easy to do but 
will haunt us regarding upgrading and maintenance forever.

I personally preferred the PyGreSQL way of handling dates and times: just 
returning them as strings. In the majority of cases we just display this 
information, we don't perform date and time arithmetics that often. That 
means, that all the other libraries perform a lot of parsing and type casting 
for nothing.

>       def join (service1, table1, key1, where1, service2, table2,
> key2, where2)
>
>
>       On a single server this becomes
>       "SELECT * FROM table1, table2 WHERE key1 = key2 AND where1 AND
> where2"
>
> On distributed servers:
>       server1: "SELECT * FROM table1 WHERE where1"
>
> then for each line so returned, a query is sent to server2:
>       server2: "SELECT * FROM table2 WHERE where2 AND key2 = key1"

If we design our services properly, I hope this will never be neccessary. 
Information should be that cleanly separated, that we never should need more 
than one "foreign" ID. Than it is best handled via a two stage query on the 
client side
example:

client side:
create a cursor for service "person", run query:
select id from person where ....
create a cursor for service "clinical", run query:
select * from immunisations where id_person = (id)

>From the client side it will be irrelevant whether gmPG queries one, two or 
more databases on one, two or more servers. The code remains readable & easy 
to understand, and the queries won be too complicated for my simple brain. As 
that type of cross-database querying only returns single id's, we don create 
much superfluous network traffic either

Horst