Mercurial > p > roundup > code

comparison roundup/backends/rdbms_common.py @ 1836:94e430ad4fdb

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
make the RDBMS common backend and the SQLite and MYsql backend create... ...and drop indexes for the basic columns - index multilinks, index id and retired columns of all class tables.
author Anthony Baxter <anthonybaxter@users.sourceforge.net>
date Tue, 07 Oct 2003 07:17:54 +0000
parents a3b1b1dcf639
children 532f51a2c741
comparison
equal deleted inserted replaced
1835:461d8aa81376 1836:94e430ad4fdb
1 # $Id: rdbms_common.py,v 1.62 2003-09-08 20:39:18 jlgijsbers Exp $ 1 # $Id: rdbms_common.py,v 1.63 2003-10-07 07:17:54 anthonybaxter Exp $
2 ''' Relational database (SQL) backend common code. 2 ''' Relational database (SQL) backend common code.
3 3
4 Basics: 4 Basics:
5 5
6 - map roundup classes to relational tables 6 - map roundup classes to relational tables
197 old_has = {} 197 old_has = {}
198 for name,prop in old_spec[1]: 198 for name,prop in old_spec[1]:
199 old_has[name] = 1 199 old_has[name] = 1
200 if not new_has(name) and isinstance(prop, Multilink): 200 if not new_has(name) and isinstance(prop, Multilink):
201 # it's a multilink, and it's been removed - drop the old 201 # it's a multilink, and it's been removed - drop the old
202 # table 202 # table. First drop indexes.
203 index_sqls = [ 'drop index %s_%s_l_idx'%(spec.classname, ml),
204 'drop index %s_%s_n_idx'%(spec.classname, ml) ]
205 for index_sql in index_sqls:
206 if __debug__:
207 print >>hyperdb.DEBUG, 'drop_index', (self, index_sql)
208 try:
209 self.cursor.execute(index_sql)
210 except:
211 # The database may not actually have any indexes.
212 # assume the worst.
213 pass
203 sql = 'drop table %s_%s'%(spec.classname, prop) 214 sql = 'drop table %s_%s'%(spec.classname, prop)
204 if __debug__: 215 if __debug__:
205 print >>hyperdb.DEBUG, 'update_class', (self, sql) 216 print >>hyperdb.DEBUG, 'update_class', (self, sql)
206 self.cursor.execute(sql) 217 self.cursor.execute(sql)
207 continue 218 continue
229 if __debug__: 240 if __debug__:
230 print >>hyperdb.DEBUG, 'update_class', (self, sql) 241 print >>hyperdb.DEBUG, 'update_class', (self, sql)
231 self.cursor.execute(sql) 242 self.cursor.execute(sql)
232 olddata = self.cursor.fetchall() 243 olddata = self.cursor.fetchall()
233 244
234 # drop the old table 245 # drop the old table - indexes first
246 index_sqls = [ 'drop index _%s_id_idx'%cn,
247 'drop index _%s_retired_idx'%cn ]
248 for index_sql in index_sqls:
249 if __debug__:
250 print >>hyperdb.DEBUG, 'drop_index', (self, index_sql)
251 try:
252 self.cursor.execute(index_sql)
253 except:
254 # The database may not actually have any indexes.
255 # assume the worst.
256 pass
235 self.cursor.execute('drop table _%s'%cn) 257 self.cursor.execute('drop table _%s'%cn)
236
237 # create the new table 258 # create the new table
238 self.create_class_table(spec) 259 self.create_class_table(spec)
239 260
240 if olddata: 261 if olddata:
241 # do the insert 262 # do the insert
261 scols = ','.join(['%s varchar'%x for x in cols]) 282 scols = ','.join(['%s varchar'%x for x in cols])
262 sql = 'create table _%s (%s)'%(spec.classname, scols) 283 sql = 'create table _%s (%s)'%(spec.classname, scols)
263 if __debug__: 284 if __debug__:
264 print >>hyperdb.DEBUG, 'create_class', (self, sql) 285 print >>hyperdb.DEBUG, 'create_class', (self, sql)
265 self.cursor.execute(sql) 286 self.cursor.execute(sql)
287 index_sql1 = 'create index _%s_id_idx on _%s(id)'%(
288 spec.classname, spec.classname)
289 if __debug__:
290 print >>hyperdb.DEBUG, 'create_index', (self, index_sql1)
291 self.cursor.execute(index_sql1)
292 index_sql2 = 'create index _%s_retired_idx on _%s(__retired__)'%(
293 spec.classname, spec.classname)
294 if __debug__:
295 print >>hyperdb.DEBUG, 'create_index', (self, index_sql2)
296 self.cursor.execute(index_sql2)
266 297
267 return cols, mls 298 return cols, mls
268 299
269 def create_journal_table(self, spec): 300 def create_journal_table(self, spec):
270 ''' create the journal table for a class given the spec and 301 ''' create the journal table for a class given the spec and
275 for x in 'nodeid date tag action params'.split()]) 306 for x in 'nodeid date tag action params'.split()])
276 sql = 'create table %s__journal (%s)'%(spec.classname, cols) 307 sql = 'create table %s__journal (%s)'%(spec.classname, cols)
277 if __debug__: 308 if __debug__:
278 print >>hyperdb.DEBUG, 'create_class', (self, sql) 309 print >>hyperdb.DEBUG, 'create_class', (self, sql)
279 self.cursor.execute(sql) 310 self.cursor.execute(sql)
311 index_sql = 'create index %s_journ_idx on %s__journal(nodeid)'%(
312 spec.classname, spec.classname)
313 if __debug__:
314 print >>hyperdb.DEBUG, 'create_index', (self, index_sql)
315 self.cursor.execute(index_sql)
280 316
281 def create_multilink_table(self, spec, ml): 317 def create_multilink_table(self, spec, ml):
282 ''' Create a multilink table for the "ml" property of the class 318 ''' Create a multilink table for the "ml" property of the class
283 given by the spec 319 given by the spec
284 ''' 320 '''
285 sql = 'create table %s_%s (linkid varchar, nodeid varchar)'%( 321 sql = 'create table %s_%s (linkid varchar, nodeid varchar)'%(
286 spec.classname, ml) 322 spec.classname, ml)
287 if __debug__: 323 if __debug__:
288 print >>hyperdb.DEBUG, 'create_class', (self, sql) 324 print >>hyperdb.DEBUG, 'create_class', (self, sql)
289 self.cursor.execute(sql) 325 self.cursor.execute(sql)
326 index_sql = 'create index %s_%s_l_idx on %s_%s(linkid)'%(
327 spec.classname, ml, spec.classname, ml)
328 if __debug__:
329 print >>hyperdb.DEBUG, 'create_index', (self, index_sql)
330 self.cursor.execute(index_sql)
331 index_sql = 'create index %s_%s_n_idx on %s_%s(nodeid)'%(
332 spec.classname, ml, spec.classname, ml)
333 if __debug__:
334 print >>hyperdb.DEBUG, 'create_index', (self, index_sql)
335 self.cursor.execute(index_sql)
290 336
291 def create_class(self, spec): 337 def create_class(self, spec):
292 ''' Create a database table according to the given spec. 338 ''' Create a database table according to the given spec.
293 ''' 339 '''
294 cols, mls = self.create_class_table(spec) 340 cols, mls = self.create_class_table(spec)
314 mls = [] 360 mls = []
315 for col, prop in spec.properties.items(): 361 for col, prop in spec.properties.items():
316 if isinstance(prop, Multilink): 362 if isinstance(prop, Multilink):
317 mls.append(col) 363 mls.append(col)
318 364
365 index_sqls = [ 'drop index _%s_id_idx'%cn,
366 'drop index _%s_retired_idx'%cn,
367 'drop index %s_journ_idx'%cn ]
368 for index_sql in index_sqls:
369 if __debug__:
370 print >>hyperdb.DEBUG, 'drop_index', (self, index_sql)
371 try:
372 self.cursor.execute(index_sql)
373 except:
374 # The database may not actually have any indexes.
375 # assume the worst.
376 pass
377
319 sql = 'drop table _%s'%spec.classname 378 sql = 'drop table _%s'%spec.classname
320 if __debug__: 379 if __debug__:
321 print >>hyperdb.DEBUG, 'drop_class', (self, sql) 380 print >>hyperdb.DEBUG, 'drop_class', (self, sql)
322 self.cursor.execute(sql) 381 self.cursor.execute(sql)
323
324 sql = 'drop table %s__journal'%spec.classname 382 sql = 'drop table %s__journal'%spec.classname
325 if __debug__: 383 if __debug__:
326 print >>hyperdb.DEBUG, 'drop_class', (self, sql) 384 print >>hyperdb.DEBUG, 'drop_class', (self, sql)
327 self.cursor.execute(sql) 385 self.cursor.execute(sql)
328 386
329 for ml in mls: 387 for ml in mls:
388 index_sqls = [
389 'drop index %s_%s_n_idx'%(spec.classname, ml),
390 'drop index %s_%s_l_idx'%(spec.classname, ml),
391 ]
392 for index_sql in index_sqls:
393 if __debug__:
394 print >>hyperdb.DEBUG, 'drop_index', (self, index_sql)
395 try:
396 self.cursor.execute(index_sql)
397 except:
398 # The database may not actually have any indexes.
399 # assume the worst.
400 pass
330 sql = 'drop table %s_%s'%(spec.classname, ml) 401 sql = 'drop table %s_%s'%(spec.classname, ml)
331 if __debug__: 402 if __debug__:
332 print >>hyperdb.DEBUG, 'drop_class', (self, sql) 403 print >>hyperdb.DEBUG, 'drop_class', (self, sql)
333 self.cursor.execute(sql) 404 self.cursor.execute(sql)
334 405
Roundup Issue Tracker: http://roundup-tracker.org/