![[tokkee]](http://tokkee.org/images/avatar.png){kind=avatar}
index f0acaee844380fd52efd0e0b019a00adec0af9d2..a60c79d6646f1296bdcaddff6d7292e5826e3847 100644 (file)
-# $Id: rdbms_common.py,v 1.18 2002-09-25 05:27:29 richard Exp $
+# $Id: rdbms_common.py,v 1.22 2002-10-08 04:11:16 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
# 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
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)
+ # 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))
def is_retired(self, nodeid):
'''Return true if the node is rerired
if not self.key:
raise TypeError, 'No key property set for class %s'%self.classname
- sql = '''select id from _%s where _%s=%s
- and __retired__ != '1' '''%(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
row = self.db.sql_fetchone()
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)
l = [x[0] for x in self.db.sql_fetchall()]
if __debug__:
'''
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