[Gnumed-devel] database abstraction layer

[Horst Herb]

Reconsidering after Hilmar's objections, I had another go at a high 
level database abstraction layer.
The first attempt just reads the table structures and builds python 
classes according to the table columns; it handles both read and write 
access via a dictionary like interface - easy enough.

Interface would be straightforward:
patient = pgobject("identity", **kwds)
if id is in kwds, we assume this is the primary key and retrieve the 
object via primary key. If not, a query is constructed assuming that all 
kwds are field name:field value entries and a select is performed using 
these values. It would return either a list of patient objects or a 
single patient object depending on the query result

print patient['id']
patient['dob']='01.02.1903'
would both work as one would guess.
Commission to database happens either at deletion of object / garbage 
collection time or when patient.commit() method is called

Problems arise with foreign keys. Such abstraction really only make 
sense if referenced tables can be accessed equally comfortable. It is 
easy enough to check a table for foreign keys and load the referenced 
tables into the object, but....

When we have "many-to-many" relationships (via pivot tables), there is 
no quick way to find out such relationships in an automatized way; we 
have to do it either programmatically (which defies the purpose of the 
abstraction) or via a in-memory lookup table holding the relationships 
of the entire database - unless we adhere to a strict syntax regarding 
those pivot tables so that I can identify them with a single quick query.

Any thoughts?

Horst