/[gnumed]/gnumed/gnumed/client/python-common/gmPgObject.py
ViewVC logotype

Contents of /gnumed/gnumed/client/python-common/gmPgObject.py

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.16 - (show annotations) (download) (as text)
Fri Feb 13 01:38:32 2004 UTC (20 years, 2 months ago) by ncq
Branch: MAIN
CVS Tags: HEAD
Changes since 1.15: +6 -2 lines
File MIME type: text/x-python
FILE REMOVED
- moved to test_area

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 #

savannah-hackers-public@gnu.org
ViewVC Help
Powered by ViewVC 1.1.26