Mercurial Repository: p/roundup/code: roundup/backends/rdbms

comparison roundup/backends/rdbms_common.py @ 1183:08a13a84ed43

Some speedups - both of the SQL backends can handle using only one cursor. Better date unserialisation too.

author	Richard Jones <richard@users.sourceforge.net>
date	Mon, 23 Sep 2002 06:48:35 +0000
parents	49aebf5a8691
children	3b0735ef8207

comparison

equal deleted inserted replaced

-:cb35cf3db0e0
+:08a13a84ed43
-# $Id: rdbms_common.py,v 1.10 2002-09-23 00:50:32 richard Exp $
+# $Id: rdbms_common.py,v 1.11 2002-09-23 06:48:35 richard Exp $
 # standard python modules
 import sys, os, time, re, errno, weakref, copy
 # roundup modules
 def open_connection(self):
 ''' Open a connection to the database, creating it if necessary
 '''
 raise NotImplemented
-def sql(self, cursor, sql, args=None):
+def sql(self, sql, args=None):
 ''' Execute the sql with the optional args.
 '''
 if __debug__:
 print >>hyperdb.DEBUG, (self, sql, args)
 if args:
-cursor.execute(sql, args)
+self.cursor.execute(sql, args)
 else:
-cursor.execute(sql)
+self.cursor.execute(sql)
-def sql_fetchone(self, cursor):
+def sql_fetchone(self):
 ''' Fetch a single row. If there's nothing to fetch, return None.
 '''
 raise NotImplemented
 def sql_stringquote(self, value):
 ''' Quote the string so it's safe to put in the 'sql quotes'
 '''
 return re.sub("'", "''", str(value))
-def save_dbschema(self, cursor, schema):
+def save_dbschema(self, schema):
 ''' Save the schema definition that the database currently implements
 '''
 raise NotImplemented
-def load_dbschema(self, cursor):
+def load_dbschema(self):
 ''' Load the schema definition that the database currently implements
 '''
 raise NotImplemented
 def post_init(self):
 if not self.classes.has_key(classname):
 self.drop_class(classname)
 # update the database version of the schema
 if save:
-cursor = self.conn.cursor()
+self.sql('delete from schema')
-self.sql(cursor, 'delete from schema')
+self.save_dbschema(self.database_schema)
-self.save_dbschema(cursor, self.database_schema)
 # reindex the db if necessary
 if self.indexer.should_reindex():
 self.reindex()
 dbspec_propnames,dbspec_props = [],{}
 for propname,prop in dbspec[1]:
 dbspec_propnames.append(propname)
 dbspec_props[propname] = prop
-# we're going to need one of these
-cursor = self.conn.cursor()
 # now compare
 for propname in spec_propnames:
 prop = spec_props[propname]
 if dbspec_props.has_key(propname) and prop==dbspec_props[propname]:
 continue
 if not dbspec_props.has_key(propname):
 # add the property
 if isinstance(prop, Multilink):
 # all we have to do here is create a new table, easy!
-self.create_multilink_table(cursor, spec, propname)
+self.create_multilink_table(spec, propname)
 continue
 # no ALTER TABLE, so we:
 # 1. pull out the data, including an extra None column
 oldcols, x = self.determine_columns(dbspec[1])
 oldcols.append('__retired__')
 cn = spec.classname
 sql = 'select %s,%s from _%s'%(','.join(oldcols), self.arg, cn)
 if __debug__:
 print >>hyperdb.DEBUG, 'update_class', (self, sql, None)
-cursor.execute(sql, (None,))
+self.cursor.execute(sql, (None,))
-olddata = cursor.fetchall()
+olddata = self.cursor.fetchall()
 # 2. drop the old table
-cursor.execute('drop table _%s'%cn)
+self.cursor.execute('drop table _%s'%cn)
 # 3. create the new table
-cols, mls = self.create_class_table(cursor, spec)
+cols, mls = self.create_class_table(spec)
 # ensure the new column is last
 cols.remove('_'+propname)
 assert oldcols == cols, "Column lists don't match!"
 cols.append('_'+propname)
 # XXX this isn't necessary in sqlite :(
 self.conn.commit()
 # do the insert
 for row in olddata:
-self.sql(cursor, sql, tuple(row))
+self.sql(sql, tuple(row))
 else:
 # modify the property
 if __debug__:
 print >>hyperdb.DEBUG, 'update_class NOOP'
 # delete the property
 if isinstance(prop, Multilink):
 sql = 'drop table %s_%s'%(spec.classname, prop)
 if __debug__:
 print >>hyperdb.DEBUG, 'update_class', (self, sql)
-cursor.execute(sql)
+self.cursor.execute(sql)
 else:
 # no ALTER TABLE, so we:
 # 1. pull out the data, excluding the removed column
 oldcols, x = self.determine_columns(spec.properties.items())
 oldcols.append('id')
 oldcols.append('__retired__')
 # remove the missing column
 oldcols.remove('_'+propname)
 cn = spec.classname
 sql = 'select %s from _%s'%(','.join(oldcols), cn)
-cursor.execute(sql, (None,))
+self.cursor.execute(sql, (None,))
 olddata = sql.fetchall()
 # 2. drop the old table
-cursor.execute('drop table _%s'%cn)
+self.cursor.execute('drop table _%s'%cn)
 # 3. create the new table
-cols, mls = self.create_class_table(self, cursor, spec)
+cols, mls = self.create_class_table(self, spec)
 assert oldcols != cols, "Column lists don't match!"
 # 4. populate with the data from step one
 qs = ','.join([self.arg for x in cols])
 sql = 'insert into _%s values (%s)'%(cn, s)
-cursor.execute(sql, olddata)
+self.cursor.execute(sql, olddata)
 return 1
-def create_class_table(self, cursor, spec):
+def create_class_table(self, spec):
 ''' create the class table for the given spec
 '''
 cols, mls = self.determine_columns(spec.properties.items())
 # add on our special columns
 # create the base table
 scols = ','.join(['%s varchar'%x for x in cols])
 sql = 'create table _%s (%s)'%(spec.classname, scols)
 if __debug__:
 print >>hyperdb.DEBUG, 'create_class', (self, sql)
-cursor.execute(sql)
+self.cursor.execute(sql)
 return cols, mls
-def create_journal_table(self, cursor, spec):
+def create_journal_table(self, spec):
 ''' create the journal table for a class given the spec and
 already-determined cols
 '''
 # journal table
 cols = ','.join(['%s varchar'%x
 for x in 'nodeid date tag action params'.split()])
 sql = 'create table %s__journal (%s)'%(spec.classname, cols)
 if __debug__:
 print >>hyperdb.DEBUG, 'create_class', (self, sql)
-cursor.execute(sql)
+self.cursor.execute(sql)
-def create_multilink_table(self, cursor, spec, ml):
+def create_multilink_table(self, spec, ml):
 ''' Create a multilink table for the "ml" property of the class
 given by the spec
 '''
 sql = 'create table %s_%s (linkid varchar, nodeid varchar)'%(
 spec.classname, ml)
 if __debug__:
 print >>hyperdb.DEBUG, 'create_class', (self, sql)
-cursor.execute(sql)
+self.cursor.execute(sql)
 def create_class(self, spec):
 ''' Create a database table according to the given spec.
 '''
-cursor = self.conn.cursor()
+cols, mls = self.create_class_table(spec)
-cols, mls = self.create_class_table(cursor, spec)
+self.create_journal_table(spec)
-self.create_journal_table(cursor, spec)
 # now create the multilink tables
 for ml in mls:
-self.create_multilink_table(cursor, spec, ml)
+self.create_multilink_table(spec, ml)
 # ID counter
 sql = 'insert into ids (name, num) values (%s,%s)'%(self.arg, self.arg)
 vals = (spec.classname, 1)
 if __debug__:
 print >>hyperdb.DEBUG, 'create_class', (self, sql, vals)
-cursor.execute(sql, vals)
+self.cursor.execute(sql, vals)
 def drop_class(self, spec):
 ''' Drop the given table from the database.
 Drop the journal and multilink tables too.
 # figure the multilinks
 mls = []
 for col, prop in spec.properties.items():
 if isinstance(prop, Multilink):
 mls.append(col)
-cursor = self.conn.cursor()
 sql = 'drop table _%s'%spec.classname
 if __debug__:
 print >>hyperdb.DEBUG, 'drop_class', (self, sql)
-cursor.execute(sql)
+self.cursor.execute(sql)
 sql = 'drop table %s__journal'%spec.classname
 if __debug__:
 print >>hyperdb.DEBUG, 'drop_class', (self, sql)
-cursor.execute(sql)
+self.cursor.execute(sql)
 for ml in mls:
 sql = 'drop table %s_%s'%(spec.classname, ml)
 if __debug__:
 print >>hyperdb.DEBUG, 'drop_class', (self, sql)
-cursor.execute(sql)
+self.cursor.execute(sql)
 #
 # Classes
 #
 def __getattr__(self, classname):
 Note: I don't commit here, which is different behaviour to the
 "nuke from orbit" behaviour in the *dbms.
 '''
 if __debug__:
 print >>hyperdb.DEBUG, 'clear', (self,)
-cursor = self.conn.cursor()
 for cn in self.classes.keys():
 sql = 'delete from _%s'%cn
 if __debug__:
 print >>hyperdb.DEBUG, 'clear', (self, sql)
-cursor.execute(sql)
+self.cursor.execute(sql)
 #
 # Node IDs
 #
 def newid(self, classname):
 ''' Generate a new id for the given class
 '''
 # get the next ID
-cursor = self.conn.cursor()
 sql = 'select num from ids where name=%s'%self.arg
 if __debug__:
 print >>hyperdb.DEBUG, 'newid', (self, sql, classname)
-cursor.execute(sql, (classname, ))
+self.cursor.execute(sql, (classname, ))
-newid = cursor.fetchone()[0]
+newid = self.cursor.fetchone()[0]
 # update the counter
 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
 vals = (int(newid)+1, classname)
 if __debug__:
 print >>hyperdb.DEBUG, 'newid', (self, sql, vals)
-cursor.execute(sql, vals)
+self.cursor.execute(sql, vals)
 # return as string
 return str(newid)
 def setid(self, classname, setid):
 ''' Set the id counter: used during import of database
 '''
-cursor = self.conn.cursor()
 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
 vals = (setid, classname)
 if __debug__:
 print >>hyperdb.DEBUG, 'setid', (self, sql, vals)
-cursor.execute(sql, vals)
+self.cursor.execute(sql, vals)
 #
 # Nodes
 #
 vals = tuple([node[col[1:]] for col in cols]) + (nodeid, 0)
 s = ','.join([self.arg for x in cols]) + ',%s,%s'%(self.arg, self.arg)
 cols = ','.join(cols) + ',id,__retired__'
 # perform the inserts
-cursor = self.conn.cursor()
 sql = 'insert into _%s (%s) values (%s)'%(classname, cols, s)
 if __debug__:
 print >>hyperdb.DEBUG, 'addnode', (self, sql, vals)
-cursor.execute(sql, vals)
+self.cursor.execute(sql, vals)
 # insert the multilink rows
 for col in mls:
 t = '%s_%s'%(classname, col)
 for entry in node[col]:
 sql = 'insert into %s (linkid, nodeid) values (%s,%s)'%(t,
 self.arg, self.arg)
-self.sql(cursor, sql, (entry, nodeid))
+self.sql(sql, (entry, nodeid))
 # make sure we do the commit-time extra stuff for this node
 self.transactions.append((self.doSaveNode, (classname, nodeid, node)))
 def setnode(self, classname, nodeid, values, multilink_changes):
 mls.append(col)
 else:
 cols.append('_'+col)
 cols.sort()
-cursor = self.conn.cursor()
 # if there's any updates to regular columns, do them
 if cols:
 # make sure the ordering is correct for column name -> column value
 sqlvals = tuple([values[col[1:]] for col in cols]) + (nodeid,)
 s = ','.join(['%s=%s'%(x, self.arg) for x in cols])
 # perform the update
 sql = 'update _%s set %s where id=%s'%(classname, s, self.arg)
 if __debug__:
 print >>hyperdb.DEBUG, 'setnode', (self, sql, sqlvals)
-cursor.execute(sql, sqlvals)
+self.cursor.execute(sql, sqlvals)
 # now the fun bit, updating the multilinks ;)
 for col, (add, remove) in multilink_changes.items():
 tn = '%s_%s'%(classname, col)
 if add:
 sql = 'insert into %s (nodeid, linkid) values (%s,%s)'%(tn,
 self.arg, self.arg)
 for addid in add:
-self.sql(cursor, sql, (nodeid, addid))
+self.sql(sql, (nodeid, addid))
 if remove:
 sql = 'delete from %s where nodeid=%s and linkid=%s'%(tn,
 self.arg, self.arg)
 for removeid in remove:
-self.sql(cursor, sql, (nodeid, removeid))
+self.sql(sql, (nodeid, removeid))
 # make sure we do the commit-time extra stuff for this node
 self.transactions.append((self.doSaveNode, (classname, nodeid, values)))
 def getnode(self, classname, nodeid):
 cl = self.classes[classname]
 cols, mls = self.determine_columns(cl.properties.items())
 scols = ','.join(cols)
 # perform the basic property fetch
-cursor = self.conn.cursor()
 sql = 'select %s from _%s where id=%s'%(scols, classname, self.arg)
-self.sql(cursor, sql, (nodeid,))
+self.sql(sql, (nodeid,))
-values = self.sql_fetchone(cursor)
+values = self.sql_fetchone()
 if values is None:
 raise IndexError, 'no such %s node %s'%(classname, nodeid)
 # make up the node
 node = {}
 # now the multilinks
 for col in mls:
 # get the link ids
 sql = 'select linkid from %s_%s where nodeid=%s'%(classname, col,
 self.arg)
-cursor.execute(sql, (nodeid,))
+self.cursor.execute(sql, (nodeid,))
 # extract the first column from the result
-node[col] = [x[0] for x in cursor.fetchall()]
+node[col] = [x[0] for x in self.cursor.fetchall()]
 # un-dbificate the node data
 node = self.unserialise(classname, node)
 # save off in the cache
 for entry in self.transactions[:]:
 if entry[1][:2] == (classname, nodeid):
 self.transactions.remove(entry)
 # now do the SQL
-cursor = self.conn.cursor()
 sql = 'delete from _%s where id=%s'%(classname, self.arg)
-self.sql(cursor, sql, (nodeid,))
+self.sql(sql, (nodeid,))
 # remove from multilnks
 cl = self.getclass(classname)
 x, mls = self.determine_columns(cl.properties.items())
 for col in mls:
 # get the link ids
 sql = 'delete from %s_%s where nodeid=%s'%(classname, col, self.arg)
-cursor.execute(sql, (nodeid,))
+self.cursor.execute(sql, (nodeid,))
 # remove journal entries
 sql = 'delete from %s__journal where nodeid=%s'%(classname, self.arg)
-self.sql(cursor, sql, (nodeid,))
+self.sql(sql, (nodeid,))
 def serialise(self, classname, node):
 '''Copy the node contents, converting non-marshallable data into
 marshallable data.
 '''
 return d
 def hasnode(self, classname, nodeid):
 ''' Determine if the database has a given node.
 '''
-cursor = self.conn.cursor()
 sql = 'select count(*) from _%s where id=%s'%(classname, self.arg)
 if __debug__:
 print >>hyperdb.DEBUG, 'hasnode', (self, sql, nodeid)
-cursor.execute(sql, (nodeid,))
+self.cursor.execute(sql, (nodeid,))
-return int(cursor.fetchone()[0])
+return int(self.cursor.fetchone()[0])
 def countnodes(self, classname):
 ''' Count the number of nodes that exist for a particular Class.
 '''
-cursor = self.conn.cursor()
 sql = 'select count(*) from _%s'%classname
 if __debug__:
 print >>hyperdb.DEBUG, 'countnodes', (self, sql)
-cursor.execute(sql)
+self.cursor.execute(sql)
-return cursor.fetchone()[0]
+return self.cursor.fetchone()[0]
 def getnodeids(self, classname, retired=0):
 ''' Retrieve all the ids of the nodes for a particular Class.
 Set retired=None to get all nodes. Otherwise it'll get all the
 retired or non-retired nodes, depending on the flag.
 '''
-cursor = self.conn.cursor()
 # flip the sense of the flag if we don't want all of them
 if retired is not None:
 retired = not retired
 sql = 'select id from _%s where __retired__ <> %s'%(classname, self.arg)
 if __debug__:
 print >>hyperdb.DEBUG, 'getnodeids', (self, sql, retired)
-cursor.execute(sql, (retired,))
+self.cursor.execute(sql, (retired,))
-return [x[0] for x in cursor.fetchall()]
+return [x[0] for x in self.cursor.fetchall()]
 def addjournal(self, classname, nodeid, action, params, creator=None,
 creation=None):
 ''' Journal the Action
 'action' may be:
 if __debug__:
 print >>hyperdb.DEBUG, 'addjournal', (nodeid, journaldate,
 journaltag, action, params)
-cursor = self.conn.cursor()
+self.save_journal(classname, cols, nodeid, journaldate,
-self.save_journal(cursor, classname, cols, nodeid, journaldate,
 journaltag, action, params)
-def save_journal(self, cursor, classname, cols, nodeid, journaldate,
+def save_journal(self, classname, cols, nodeid, journaldate,
 journaltag, action, params):
 ''' Save the journal entry to the database
 '''
 raise NotImplemented
 '''
 # make sure the node exists
 if not self.hasnode(classname, nodeid):
 raise IndexError, '%s has no node %s'%(classname, nodeid)
-cursor = self.conn.cursor()
 cols = ','.join('nodeid date tag action params'.split())
-return self.load_journal(cursor, classname, cols, nodeid)
+return self.load_journal(classname, cols, nodeid)
-def load_journal(self, cursor, classname, cols, nodeid):
+def load_journal(self, classname, cols, nodeid):
 ''' Load the journal from the database
 '''
 raise NotImplemented
 def pack(self, pack_before):
 '''
 # get a 'yyyymmddhhmmss' version of the date
 date_stamp = pack_before.serialise()
 # do the delete
-cursor = self.conn.cursor()
 for classname in self.classes.keys():
 sql = "delete from %s__journal where date<%s and "\
 "action<>'create'"%(classname, self.arg)
 if __debug__:
 print >>hyperdb.DEBUG, 'pack', (self, sql, date_stamp)
-cursor.execute(sql, (date_stamp,))
+self.cursor.execute(sql, (date_stamp,))
 def sql_commit(self):
 ''' Actually commit to the database.
 '''
 self.conn.commit()
 methods, and other nodes may reuse the values of their key properties.
 '''
 if self.db.journaltag is None:
 raise DatabaseError, 'Database open read-only'
-cursor = self.db.conn.cursor()
 sql = 'update _%s set __retired__=1 where id=%s'%(self.classname,
 self.db.arg)
 if __debug__:
 print >>hyperdb.DEBUG, 'retire', (self, sql, nodeid)
-cursor.execute(sql, (nodeid,))
+self.db.cursor.execute(sql, (nodeid,))
 def is_retired(self, nodeid):
 '''Return true if the node is rerired
 '''
-cursor = self.db.conn.cursor()
 sql = 'select __retired__ from _%s where id=%s'%(self.classname,
 self.db.arg)
 if __debug__:
 print >>hyperdb.DEBUG, 'is_retired', (self, sql, nodeid)
-cursor.execute(sql, (nodeid,))
+self.db.cursor.execute(sql, (nodeid,))
-return int(cursor.fetchone()[0])
+return int(self.db.sql_fetchone()[0])
 def destroy(self, nodeid):
 '''Destroy a node.
 WARNING: this method should never be used except in extremely rare
 otherwise a KeyError is raised.
 '''
 if not self.key:
 raise TypeError, 'No key property set for class %s'%self.classname
-cursor = self.db.conn.cursor()
 sql = 'select id,__retired__ from _%s where _%s=%s'%(self.classname,
 self.key, self.db.arg)
-self.db.sql(cursor, sql, (keyvalue,))
+self.db.sql(sql, (keyvalue,))
 # see if there was a result that's not retired
-l = cursor.fetchall()
+l = self.db.cursor.fetchall()
 if not l or int(l[0][1]):
 raise KeyError, 'No key (%s) value "%s" for "%s"'%(self.key,
 keyvalue, self.classname)
 # return the id
 allvalues += tuple(values.keys())
 a = self.db.arg
 tables.append('select nodeid from %s_%s where linkid in (%s)'%(
 self.classname, prop, ','.join([a for x in values.keys()])))
 sql = '\nintersect\n'.join(tables)
-if __debug__:
+self.db.sql(sql, allvalues)
-print >>hyperdb.DEBUG, 'find', (self, sql, allvalues)
-cursor = self.db.conn.cursor()
-cursor.execute(sql, allvalues)
 try:
-l = [x[0] for x in cursor.fetchall()]
+l = [x[0] for x in self.db.cursor.fetchall()]
 except gadfly.database.error, message:
 if message == 'no more results':
 l = []
 raise
 if __debug__:
 cols = ','.join(cols)
 sql = 'select %s from %s %s%s%s'%(cols, frum, where, group, order)
 args = tuple(args)
 if __debug__:
 print >>hyperdb.DEBUG, 'filter', (self, sql, args)
-cursor = self.db.conn.cursor()
+self.db.cursor.execute(sql, args)
-cursor.execute(sql, args)
+l = self.db.cursor.fetchall()
-l = cursor.fetchall()
 # return the IDs (the first column)
 # XXX The filter(None, l) bit is sqlite-specific... if there's _NO_
 # XXX matches to a fetch, it returns NULL instead of nothing!?!
 return filter(None, [row[0] for row in l])

Mercurial > p > roundup > code

comparison roundup/backends/rdbms_common.py @ 1183:08a13a84ed43