![[tokkee]](http://tokkee.org/images/avatar.png){kind=avatar}
index b5572e33ac556eca221dba2c2d3d1bdb539c3e89..002f5a721fceaac5f8901343d8bca066fa6873e6 100644 (file)
-# $Id: rdbms_common.py,v 1.13 2002-09-23 08:17:05 richard Exp $
+# $Id: rdbms_common.py,v 1.30 2003-02-06 05:43:47 richard Exp $
+''' Relational database (SQL) backend common code.
+
+Basics:
+
+- map roundup classes to relational tables
+- automatically detect schema changes and modify the table schemas
+ appropriately (we store the "database version" of the schema in the
+ database itself as the only row of the "schema" table)
+- multilinks (which represent a many-to-many relationship) are handled through
+ intermediate tables
+- journals are stored adjunct to the per-class tables
+- table names and columns have "_" prepended so the names can't clash with
+ restricted names (like "order")
+- retirement is determined by the __retired__ column being true
+
+Database-specific changes may generally be pushed out to the overridable
+sql_* methods, since everything else should be fairly generic. There's
+probably a bit of work to be done if a database is used that actually
+honors column typing, since the initial databases don't (sqlite stores
+everything as a string, and gadfly stores anything that's marsallable).
+'''
# standard python modules
import sys, os, time, re, errno, weakref, copy
from roundup import hyperdb, date, password, roundupdb, security
from roundup.hyperdb import String, Password, Date, Interval, Link, \
Multilink, DatabaseError, Boolean, Number
+from roundup.backends import locking
# support
from blobfiles import FileStorage
self.cache = {}
self.cache_lru = []
+ # database lock
+ self.lockfile = None
+
# open a connection to the database, creating the "conn" attribute
self.open_connection()
cl = self.classes[classname]
cols, mls = self.determine_columns(cl.properties.items())
- # add the special props
- node = node.copy()
- node['creation'] = node['activity'] = date.Date()
- node['creator'] = self.curuserid
+ # we'll be supplied these props if we're doing an import
+ if not node.has_key('creator'):
+ # add in the "calculated" properties (dupe so we don't affect
+ # calling code's node assumptions)
+ node = node.copy()
+ node['creation'] = node['activity'] = date.Date()
+ node['creator'] = self.curuserid
# default the non-multilink columns
for col, prop in cl.properties.items():
# get the property spec
prop = properties[k]
- if isinstance(prop, Password):
+ if isinstance(prop, Password) and v is not None:
d[k] = str(v)
elif isinstance(prop, Date) and v is not None:
d[k] = v.serialise()
d[k] = date.Date(v)
elif isinstance(prop, Interval) and v is not None:
d[k] = date.Interval(v)
- elif isinstance(prop, Password):
+ elif isinstance(prop, Password) and v is not None:
p = password.Password()
p.unpack(v)
d[k] = p
''' Close off the connection.
'''
self.conn.close()
+ if self.lockfile is not None:
+ locking.release_lock(self.lockfile)
+ if self.lockfile is not None:
+ self.lockfile.close()
+ self.lockfile = None
#
# The base Class class
(self.classname, newid, key))
elif isinstance(prop, String):
- if type(value) != type(''):
+ if type(value) != type('') and type(value) != type(u''):
raise TypeError, 'new property "%s" not a string'%key
elif isinstance(prop, Password):
# done
self.db.addnode(self.classname, newid, propvalues)
if self.do_journal:
- self.db.addjournal(self.classname, newid, 'create', propvalues)
+ self.db.addjournal(self.classname, newid, 'create', {})
self.fireReactors('create', newid, None)
creation = None
if d.has_key('activity'):
del d['activity']
- self.db.addjournal(self.classname, newid, 'create', d, creator,
+ self.db.addjournal(self.classname, newid, 'create', {}, creator,
creation)
return newid
# if the value's the same as the existing value, no sense in
# doing anything
- if node.has_key(propname) and value == node[propname]:
+ current = node.get(propname, None)
+ if value == current:
del propvalues[propname]
continue
+ journalvalues[propname] = current
# do stuff based on the prop type
if isinstance(prop, Link):
journalvalues[propname] = tuple(l)
elif isinstance(prop, String):
- if value is not None and type(value) != type(''):
+ if value is not None and type(value) != type('') and type(value) != type(u''):
raise TypeError, 'new property "%s" not a string'%propname
elif isinstance(prop, Password):
self.db.setnode(self.classname, nodeid, propvalues, multilink_changes)
if self.do_journal:
- propvalues.update(journalvalues)
- self.db.addjournal(self.classname, nodeid, 'set', propvalues)
+ self.db.addjournal(self.classname, nodeid, 'set', journalvalues)
self.fireReactors('set', nodeid, oldvalues)
if self.db.journaltag is None:
raise DatabaseError, 'Database open read-only'
- sql = 'update _%s set __retired__=1 where id=%s'%(self.classname,
- self.db.arg)
+ self.fireAuditors('retire', nodeid, None)
+
+ # use the arg for __retired__ to cope with any odd database type
+ # conversion (hello, sqlite)
+ sql = 'update _%s set __retired__=%s where id=%s'%(self.classname,
+ self.db.arg, self.db.arg)
if __debug__:
print >>hyperdb.DEBUG, 'retire', (self, sql, nodeid)
- self.db.cursor.execute(sql, (nodeid,))
+ self.db.cursor.execute(sql, (1, nodeid))
+
+ self.fireReactors('retire', nodeid, None)
def is_retired(self, nodeid):
'''Return true if the node is rerired
The returned list contains tuples of the form
- (date, tag, action, params)
+ (nodeid, date, tag, action, params)
'date' is a Timestamp object specifying the time of the change and
'tag' is the journaltag specified when the database was opened.
if not self.key:
raise TypeError, 'No key property set for class %s'%self.classname
- sql = 'select id,__retired__ from _%s where _%s=%s'%(self.classname,
- self.key, self.db.arg)
- self.db.sql(sql, (keyvalue,))
+ # use the arg to handle any odd database type conversion (hello,
+ # sqlite)
+ sql = "select id from _%s where _%s=%s and __retired__ <> %s"%(
+ self.classname, self.key, self.db.arg, self.db.arg)
+ self.db.sql(sql, (keyvalue, 1))
# see if there was a result that's not retired
- l = self.db.cursor.fetchall()
- if not l or int(l[0][1]):
+ row = self.db.sql_fetchone()
+ if not row:
raise KeyError, 'No key (%s) value "%s" for "%s"'%(self.key,
keyvalue, self.classname)
# return the id
- return l[0][0]
+ return row[0]
def find(self, **propspec):
'''Get the ids of nodes in this class which link to the given nodes.
- 'propspec' consists of keyword args propname={nodeid:1,}
+ 'propspec' consists of keyword args propname=nodeid or
+ propname={nodeid:1, }
'propname' must be the name of a property in this class, or a
KeyError is raised. That property must be a Link or Multilink
property, or a TypeError is raised.
'''
if __debug__:
print >>hyperdb.DEBUG, 'find', (self, propspec)
+
+ # shortcut
if not propspec:
return []
- queries = []
- tables = []
+
+ # validate the args
+ props = self.getprops()
+ propspec = propspec.items()
+ for propname, nodeids in propspec:
+ # check the prop is OK
+ prop = props[propname]
+ if not isinstance(prop, Link) and not isinstance(prop, Multilink):
+ raise TypeError, "'%s' not a Link/Multilink property"%propname
+
+ # first, links
+ where = []
allvalues = ()
- for prop, values in propspec.items():
- allvalues += tuple(values.keys())
- a = self.db.arg
+ a = self.db.arg
+ for prop, values in propspec:
+ if not isinstance(props[prop], hyperdb.Link):
+ continue
+ if type(values) is type(''):
+ allvalues += (values,)
+ where.append('_%s = %s'%(prop, a))
+ else:
+ allvalues += tuple(values.keys())
+ where.append('_%s in (%s)'%(prop, ','.join([a]*len(values))))
+ tables = []
+ if where:
+ tables.append('select id as nodeid from _%s where %s'%(
+ self.classname, ' and '.join(where)))
+
+ # now multilinks
+ for prop, values in propspec:
+ if not isinstance(props[prop], hyperdb.Multilink):
+ continue
+ if type(values) is type(''):
+ allvalues += (values,)
+ s = a
+ else:
+ allvalues += tuple(values.keys())
+ s = ','.join([a]*len(values))
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)
+ self.classname, prop, s))
+ sql = '\nunion\n'.join(tables)
self.db.sql(sql, allvalues)
- try:
- l = [x[0] for x in self.db.cursor.fetchall()]
- except gadfly.database.error, message:
- if message == 'no more results':
- l = []
- raise
+ l = [x[0] for x in self.db.sql_fetchall()]
+ if __debug__:
+ print >>hyperdb.DEBUG, 'find ... ', l
+ return l
+
+ def stringFind(self, **requirements):
+ '''Locate a particular node by matching a set of its String
+ properties in a caseless search.
+
+ If the property is not a String property, a TypeError is raised.
+
+ The return is a list of the id of all nodes that match.
+ '''
+ where = []
+ args = []
+ for propname in requirements.keys():
+ prop = self.properties[propname]
+ if isinstance(not prop, String):
+ raise TypeError, "'%s' not a String property"%propname
+ where.append(propname)
+ args.append(requirements[propname].lower())
+
+ # generate the where clause
+ s = ' and '.join(['_%s=%s'%(col, self.db.arg) for col in where])
+ sql = 'select id from _%s where %s'%(self.classname, s)
+ self.db.sql(sql, tuple(args))
+ l = [x[0] for x in self.db.sql_fetchall()]
if __debug__:
print >>hyperdb.DEBUG, 'find ... ', l
return l
'''
return self.db.getnodeids(self.classname, retired=0)
- def filter(self, search_matches, filterspec, sort, group):
+ def filter(self, search_matches, filterspec, sort=(None,None),
+ group=(None,None)):
''' Return a list of the ids of the active nodes in this class that
match the 'filter' spec, sorted by the group spec and then the
sort spec
property value to match is a list, any one of the values in the
list may match for that property to match.
'''
+ # just don't bother if the full-text search matched diddly
+ if search_matches == {}:
+ return []
+
cn = self.classname
# figure the WHERE clause from the filterspec
else:
where.append('id=%s.nodeid and %s.linkid = %s'%(tn, tn, a))
args.append(v)
+ elif k == 'id':
+ if isinstance(v, type([])):
+ s = ','.join([a for x in v])
+ where.append('%s in (%s)'%(k, s))
+ args = args + v
+ else:
+ where.append('%s=%s'%(k, a))
+ args.append(v)
elif isinstance(propclass, String):
if not isinstance(v, type([])):
v = [v]
xtra = ' or _%s is NULL'%k
else:
xtra = ''
- s = ','.join([a for x in v])
- where.append('(_%s in (%s)%s)'%(k, s, xtra))
- args = args + v
+ if v:
+ s = ','.join([a for x in v])
+ where.append('(_%s in (%s)%s)'%(k, s, xtra))
+ args = args + v
+ else:
+ where.append('_%s is NULL'%k)
else:
if v == '-1':
v = None
else:
where.append('_%s=%s'%(k, a))
args.append(v)
+ elif isinstance(propclass, Date):
+ if isinstance(v, type([])):
+ s = ','.join([a for x in v])
+ where.append('_%s in (%s)'%(k, s))
+ args = args + [date.Date(x).serialise() for x in v]
+ else:
+ where.append('_%s=%s'%(k, a))
+ args.append(date.Date(v).serialise())
+ elif isinstance(propclass, Interval):
+ if isinstance(v, type([])):
+ s = ','.join([a for x in v])
+ where.append('_%s in (%s)'%(k, s))
+ args = args + [date.Interval(x).serialise() for x in v]
+ else:
+ where.append('_%s=%s'%(k, a))
+ args.append(date.Interval(v).serialise())
else:
if isinstance(v, type([])):
s = ','.join([a for x in v])