1 |
############################################################################# |
2 |
# |
3 |
# gmPgObject - database row abstraction for gnumed |
4 |
# --------------------------------------------------------------------------- |
5 |
# |
6 |
# @author: Dr. Horst Herb |
7 |
# @copyright: author |
8 |
# @license: GPL (details at http://www.gnu.org) |
9 |
# @dependencies: DB-API 2.0 compliant PostgreSQL adapter |
10 |
# CAVE: fetch() has to return a list !!! |
11 |
# when using pyPgSQL, set pyPgSQL.PgSQL.fetchReturnsList=1 |
12 |
# |
13 |
# @TODO: Almost everything |
14 |
# - compound primary keys (primary keys spanning more than one column) |
15 |
# - write queries / write access |
16 |
# - automatic child object creation for foreign keys |
17 |
############################################################################ |
18 |
# $Source: /cvsroot/gnumed/gnumed/gnumed/client/python-common/gmPgObject.py,v $ |
19 |
__version__ = "$Revision: 1.15 $" |
20 |
__author__ = "Horst Herb <hherb@gnumed.net>" |
21 |
|
22 |
import string |
23 |
#============================================================== |
24 |
_cached_tables = [] |
25 |
_table_metadata = {} |
26 |
_column_indices = {} |
27 |
_primarykeys = {} |
28 |
_foreignkeys = {} |
29 |
#============================================================== |
30 |
def listPrimaryKey(con, table): |
31 |
"""return the column index of the primary key of the stated table |
32 |
con = open database connection (DBAPI 2.0)""" |
33 |
global gmPG.QTablePrimaryKeyIndex |
34 |
cursor = con.cursor() |
35 |
cursor.execute(gmPG.QTablePrimaryKeyIndex % table) |
36 |
pk = cursor.fetchone() |
37 |
return int(pk[0])-1 |
38 |
|
39 |
def cache_table_info(con, table, cursor=None): |
40 |
"""cache all relevant metatdata of the stated table |
41 |
if cursor is stated, use it otherwise a dummy query is executed |
42 |
con = open database connection (DBAPI 2.0)""" |
43 |
global _table_metadata |
44 |
global _column_indices |
45 |
global _primarykey |
46 |
global _foreignkeys |
47 |
global _cached_tables |
48 |
|
49 |
if table in _cached_tables: |
50 |
return |
51 |
|
52 |
if cursor is None: |
53 |
cursor = con.cursor() |
54 |
cursor.execute("select * from %s limit 1" % table) |
55 |
|
56 |
_table_metadata[table] = cursor.description |
57 |
_foreignkeys[table] = gmPG.get_fkey_defs(con, table) |
58 |
_primarykeys[table] = listPrimaryKey(con, table) |
59 |
index = {} |
60 |
for i in range(len (cursor.description)): |
61 |
index[cursor.description[i][0]] = i |
62 |
_column_indices[table] = index |
63 |
_cached_tables.append(table) |
64 |
|
65 |
#============================================================== |
66 |
class pgobject: |
67 |
|
68 |
def __init__(self, db, table, pkey = None): |
69 |
"""db = gmPG.ConnectionPool object |
70 |
table = service and table as string in the format 'service.table' |
71 |
pkey: if stated, the object will be initialized from the backend |
72 |
using this primary key value""" |
73 |
|
74 |
#index of columns by column names |
75 |
self._index = None |
76 |
#DBAPI cursor.description |
77 |
self._metadata = None |
78 |
#list of referenced foreign keys |
79 |
self._foreignkeys = None |
80 |
#cache for referenced objects |
81 |
self._referenced = {} |
82 |
#fetched row data |
83 |
self._row = None |
84 |
#name of the service and table this object is representing |
85 |
st = string.split(table, '.') |
86 |
if len(st) == 1: |
87 |
self._service = 'default' |
88 |
self._tablename = st[0] |
89 |
else: |
90 |
self._service = st[0] |
91 |
self._tablename = st[1] |
92 |
#database connection broker |
93 |
self._dbbroker = db |
94 |
#reuseable open read-only database connection |
95 |
self._db = db.GetConnection(self._service) |
96 |
#'dirty' flags: list of columns (colum names) that have been modified |
97 |
self._modified = [] |
98 |
#value of the primary key |
99 |
self._pkey = None |
100 |
#column(s) holding the primary key |
101 |
self._pkcolumn = None |
102 |
#the primary key of the last row that has been fetched |
103 |
self._fetched = None |
104 |
if pkey is not None: |
105 |
self.fetch(pkey) |
106 |
#--------------------------------------------- |
107 |
def new_primary_key(self): |
108 |
"""returns a new primary key safely created via the appropriate backend sequence""" |
109 |
cursor.execute("select nextval('%s_%s_seq')" % (self._tablename, self._pkcolumn)) |
110 |
pk, = cursor.fetchone() |
111 |
return pk |
112 |
#--------------------------------------------- |
113 |
def __getitem__(self, aCol = None): |
114 |
"""Any class[x] is turned into class.__getitem__(self, x). |
115 |
|
116 |
returns a column by name or index |
117 |
""" |
118 |
# sanity checks |
119 |
if self._primarykey is None: |
120 |
return None |
121 |
if aCol is None: |
122 |
return None |
123 |
# FIXME: or rather return all columns ? |
124 |
|
125 |
# lazy access: did we fetch the data yet ? |
126 |
if self._fetched is None: |
127 |
# actually retrieve the row now |
128 |
self._fetch() |
129 |
|
130 |
if self._row is None: |
131 |
return None |
132 |
|
133 |
#are we indexing the column by ordinal number or by column name? |
134 |
if type(aCol) == int: |
135 |
return self._row[aCol] |
136 |
else: |
137 |
# does the given column name reference another table |
138 |
# (i.e. is it a foreign key) ? |
139 |
if self._foreignkeys.has_key(aCol): |
140 |
# yep, but not retrieved yet |
141 |
if not self._referenced.has_key(aCol): |
142 |
# so get it |
143 |
ref = pgobject( |
144 |
db = self._dbbroker, |
145 |
table = "%s.%s" % (self._service, self._foreignkeys[aCol][0]), |
146 |
pkey = self._row[self._index[aCol]] |
147 |
) |
148 |
# and keep a reference |
149 |
self._referenced[aCol] = ref |
150 |
#print "-> reference:", self._foreignkeys[aCol][0], self._foreignkeys[aCol][1] |
151 |
return self._referenced[aCol] |
152 |
else: |
153 |
return self._row[self._index[aCol]] |
154 |
#--------------------------------------------- |
155 |
def __setitem__(self, key, value): |
156 |
"set the value of the column as determined by either column name or index" |
157 |
newflag = 0 |
158 |
#is table metadata already cached? |
159 |
if self._index is None: |
160 |
self._update_metadata() |
161 |
# are we dealing with a fetched row or with a new record? |
162 |
if self._row is None: |
163 |
#create an empty record if neccessary |
164 |
newflag=1 |
165 |
self._row = [] |
166 |
for idx in range(len(self._metadata)): |
167 |
self._row.append(None) |
168 |
#are we indexing the column by ordinal number or by column name? |
169 |
if type(key) == int: |
170 |
idx = key |
171 |
else: |
172 |
idx = self._index[key] |
173 |
#has the column value really changed? |
174 |
if value != self._row[idx]: |
175 |
#changed once is enoug to remember |
176 |
if key not in self._modified: |
177 |
self._modified.append(key) |
178 |
self._row[idx] = value |
179 |
if newflag: |
180 |
#if a new record is accessed, we must refresh it from the backend |
181 |
#in order to fill the columns with the default values |
182 |
self._save() |
183 |
|
184 |
|
185 |
|
186 |
|
187 |
def __del__(self): |
188 |
self._save() |
189 |
|
190 |
|
191 |
def _save(self): |
192 |
#only save if there is data |
193 |
is_new=0 |
194 |
if self._row is None: |
195 |
#nothing to save |
196 |
return |
197 |
#only save if something has been modified: |
198 |
if len(self._modified) > 0: |
199 |
if self._fetched > 0: |
200 |
#existing data has been modified |
201 |
colvals = "%s = %s" % (self._modified[0], self._quote(self._row[self._index[self._modified[0]]])) |
202 |
for column in self._modified[1:]: |
203 |
colvals = "%s , %s = %s" % (colvals, column, self._quote(self._row[self._index[column]])) |
204 |
query = "update %s set %s where %s = %s" % (self._tablename, colvals, self._pkcolumn, self._primarykey) |
205 |
else: |
206 |
#a new row has to be inserted |
207 |
is_new=1 |
208 |
columns = "" |
209 |
count = 0 |
210 |
#create a "safe" primary key (assumption: primary key is of type "serial") |
211 |
self._primarykey = self.new_primary_key() |
212 |
self._row[self._index[self._pkcolumn]] = self._primarykey |
213 |
if self._pkcolumn not in self._modified: |
214 |
self._modified.append(self._pkcolumn) |
215 |
for column in self._modified: |
216 |
value = self._row[self._index[column]] |
217 |
if value is not None: |
218 |
count += 1 |
219 |
#print "quoting" |
220 |
value = self._quote(value) |
221 |
if count == 1: |
222 |
columns = column |
223 |
values = value |
224 |
else: |
225 |
columns = "%s, %s" % (columns, column) |
226 |
values = "%s, %s" % (values, value) |
227 |
query = "insert into %s(%s) values(%s)" % (self._tablename, columns, values) |
228 |
db = self._dbbroker.GetConnection(self._service, 0) |
229 |
cursor = db.cursor() |
230 |
cursor.execute(query) |
231 |
db.commit() |
232 |
self._modified = [] |
233 |
if is_new: |
234 |
#reload row from backend, since columns may have changed |
235 |
#through default constraints |
236 |
print "refreshing table ..." |
237 |
self._fetch() |
238 |
#--------------------------------------------- |
239 |
def _quote(self, arg): |
240 |
"postgres specific quoting: strings in '', single ' escaped by another '" |
241 |
if type(arg) is str: |
242 |
q = "'%s'" % arg.replace("'", "''") |
243 |
else: |
244 |
q = str(arg) |
245 |
return q |
246 |
#--------------------------------------------- |
247 |
def _fetch(self, primarykey = None): |
248 |
"""Actually fetch the row from the table determined by the primary key. |
249 |
|
250 |
if primarykey is not stated, the current object is |
251 |
refreshed from the backend |
252 |
""" |
253 |
#self._save() |
254 |
if primarykey is not None: |
255 |
self._primarykey = primarykey |
256 |
if self._pkcolumn is None: |
257 |
self._update_metadata() |
258 |
cursor = self._db.cursor() |
259 |
query = "select * from %s where %s = %s" % (self._tablename, self._pkcolumn, self._primarykey) |
260 |
cursor.execute(query) |
261 |
self._row = cursor.fetchone() |
262 |
#did the query return a row? |
263 |
if (self._row is None) or (len(self._row) <=0): |
264 |
self._fetched = 0 |
265 |
else: |
266 |
self._fetched = self._primarykey |
267 |
#data that has been just fetched cannot be modified yet |
268 |
self._modified = [] |
269 |
cursor.close() |
270 |
#--------------------------------------------- |
271 |
def _update_metadata(self, cursor=None): |
272 |
"cache the table's meta data" |
273 |
global _column_indices |
274 |
global _pkcolumn |
275 |
global _foreignkeys |
276 |
global _table_descriptions |
277 |
cache_table_info(self._db, self._tablename, cursor) |
278 |
self._metadata = _table_metadata[self._tablename] |
279 |
self._index = _column_indices[self._tablename] |
280 |
self._pkcolumn = self._metadata[_primarykeys[self._tablename]][0] |
281 |
self._foreignkeys = _foreignkeys[self._tablename] |
282 |
#--------------------------------------------- |
283 |
def fetch(self, primarykey): |
284 |
"""Fetch the row determined by the primary key attribute. |
285 |
|
286 |
lazy data access: data will not really be fetched before it is accessed""" |
287 |
#if we have data in cache, save it first; 'save()' will check for modifications first |
288 |
if self._fetched: |
289 |
self._save() |
290 |
self._fetched = None |
291 |
self._primarykey = primarykey |
292 |
#--------------------------------------------- |
293 |
def save(self): |
294 |
"""force the current data to be written to the backend manually""" |
295 |
self._save() |
296 |
#--------------------------------------------- |
297 |
def undo(self): |
298 |
"""reset the state ofthe row to the state it has on the backend""" |
299 |
if self._fetched: |
300 |
self._fetch() |
301 |
else: |
302 |
print "Undo for new objects not impemented yet" |
303 |
|
304 |
#============================================================== |
305 |
if __name__ == "__main__": |
306 |
|
307 |
import sys, gmPG, gmLoginInfo |
308 |
|
309 |
login = gmLoginInfo.LoginInfo(user="hherb", passwd='') |
310 |
db = gmPG.ConnectionPool(login) |
311 |
db.SetFetchReturnsList(1) |
312 |
|
313 |
#request a writeable connection and create test tables |
314 |
con = db.GetConnection('default', readonly = 0) |
315 |
cursor = con.cursor() |
316 |
|
317 |
try: |
318 |
cursor.execute("drop sequence test_pgo_id_seq;") |
319 |
con.commit() |
320 |
cursor.execute("drop table test_pgo;"); |
321 |
con.commit() |
322 |
except: |
323 |
pass |
324 |
|
325 |
try: |
326 |
cursor.execute("drop sequence test_pgofk_id_seq") |
327 |
con.commit() |
328 |
cursor.execute("drop table test_pgofk"); |
329 |
con.commit() |
330 |
except: |
331 |
print "table test_pgofk or sequence test_pgofk_id_seq did not exist" |
332 |
|
333 |
try: |
334 |
cursor.execute("create table test_pgofk (id serial primary key, text text, ts timestamp default now())") |
335 |
cursor.execute("create table test_pgo (id serial primary key, id_fk integer references test_pgofk, text text, ts timestamp default now())") |
336 |
con.commit() |
337 |
except: |
338 |
print "Could not create test tables on backend. Test failed" |
339 |
sys.exit(-1) |
340 |
|
341 |
try: |
342 |
cursor.execute("insert into test_pgofk(text) values('this is the text in the referenced table')") |
343 |
cursor.execute("insert into test_pgo(id_fk, text) values(1, 'this is the text in the referencing table 1')") |
344 |
cursor.execute("insert into test_pgo(id_fk, text) values(1, 'this is the text in the referencing table 2')") |
345 |
con.commit() |
346 |
except: |
347 |
print "Cannot fill test tables with default values! - Test failed." |
348 |
sys.exit(-1) |
349 |
|
350 |
dbo = pgobject(db, 'test_pgo', 1) |
351 |
print dbo['text'], str(dbo['ts']) |
352 |
print "Now changing a value, should force a backend update" |
353 |
dbo['text'] = "it really works!!!" |
354 |
print dbo['text'] |
355 |
dbo.fetch(2) |
356 |
print dbo['text'], str(dbo['ts']) |
357 |
dbo.fetch(1) |
358 |
print dbo['text'], str(dbo['ts']) |
359 |
dbo = pgobject(db, 'test_pgo') |
360 |
dbo['text'] = "this wasn't there before" |
361 |
dbo = pgobject(db, 'test_pgo', 1) |
362 |
print dbo['id_fk']['text'] |
363 |
dbo['id_fk']['text'] = "this is a new foreign key for table 1!" |
364 |
print "\nText before change:", dbo['text'] |
365 |
dbo['text'] = "I just changed it!" |
366 |
print "Text after change (not committed):", dbo['text'] |
367 |
dbo.undo() |
368 |
print "Text after undo:", dbo['text'] |
369 |
|
370 |
#============================================================== |
371 |
# $Log: gmPgObject.py,v $ |
372 |
# Revision 1.15 2003/11/17 10:56:36 sjtan |
373 |
# |
374 |
# synced and commiting. |
375 |
# |
376 |
# Revision 1.1 2003/10/23 06:02:39 sjtan |
377 |
# |
378 |
# manual edit areas modelled after r.terry's specs. |
379 |
# |
380 |
# Revision 1.14 2003/09/30 19:09:30 ncq |
381 |
# - use a lot more of the gmPG infrastructure |
382 |
# |
383 |
# Revision 1.13 2003/02/07 14:26:28 ncq |
384 |
# - code commenting, basically |
385 |
# |
386 |
# Revision 1.12 2003/02/03 16:25:56 ncq |
387 |
# - coding style |
388 |
# |
389 |
# Revision 1.11 2003/01/16 14:45:04 ncq |
390 |
# - debianized |
391 |
# |
392 |
# Revision 1.10 2002/10/26 04:43:06 hherb |
393 |
# Undo implemented for fetched rows modified before committtment |
394 |
# Manually enforced "save" implemented |
395 |
# |
396 |
# Revision 1.9 2002/10/26 04:32:32 hherb |
397 |
# minor code cleanup, comments added |
398 |
# |
399 |
# Revision 1.8 2002/10/26 04:14:24 hherb |
400 |
# when a newly created object (row) is first modified, it is saved to the backend and refreshed from the backend in order to load default values |
401 |
# |
402 |
# Revision 1.7 2002/10/26 02:47:08 hherb |
403 |
# object changes now saved to backend. Foreign key references now transparently dereferenced (write access untested) |
404 |
# |
405 |
# Revision 1.6 2002/10/25 13:04:15 hherb |
406 |
# API change: pgobject constructor takes now gmPG.ConnectionPool as argument insted of an open connection |
407 |
# Write functionality now enabled, quoting works for strings, some datatypes still will crash |
408 |
# |
409 |
# Revision 1.5 2002/10/24 21:41:40 hherb |
410 |
# quoting of strings in write queries |
411 |
# |
412 |
# Revision 1.4 2002/10/23 22:08:49 hherb |
413 |
# saving changes to backend partially implemented (query string generation); still needs quoting fixed |
414 |
# |
415 |
# Revision 1.3 2002/10/23 15:05:47 hherb |
416 |
# "Lazy fetch" now working when primary key passed as parameter to class constructor |
417 |
# |
418 |
# Revision 1.2 2002/10/23 15:01:24 hherb |
419 |
# meta data caching now working |
420 |
# |
421 |
# Revision 1.1 2002/10/23 14:34:43 hherb |
422 |
# database row abstraction layer |
423 |
# |