index e53b965cb90dda4a7fe4790877f5519029b6cf0c..002f5a721fceaac5f8901343d8bca066fa6873e6 100644 (file)
-# $Id: rdbms_common.py,v 1.10 2002-09-23 00:50:32 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()
'''
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
'''
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
# update the database version of the schema
if save:
- cursor = self.conn.cursor()
- self.sql(cursor, 'delete from schema')
- self.save_dbschema(cursor, self.database_schema)
+ self.sql('delete from schema')
+ self.save_dbschema(self.database_schema)
# reindex the db if necessary
if self.indexer.should_reindex():
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]
# 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:
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,))
- olddata = cursor.fetchall()
+ self.cursor.execute(sql, (None,))
+ 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!"
# do the insert
for row in olddata:
- self.sql(cursor, sql, tuple(row))
+ self.sql(sql, tuple(row))
else:
# modify the property
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.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())
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
'''
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
'''
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(cursor, spec)
- self.create_journal_table(cursor, spec)
+ cols, mls = self.create_class_table(spec)
+ self.create_journal_table(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.
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
'''
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
''' 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, ))
- newid = cursor.fetchone()[0]
+ self.cursor.execute(sql, (classname, ))
+ 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
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():
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:
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)))
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
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():
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)))
if self.cache.has_key(key):
# push us back to the top of the LRU
self.cache_lru.remove(key)
- self.cache_lry.insert(0, key)
+ self.cache_lru.insert(0, key)
# return the cached information
return self.cache[key]
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)
# 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)
self.cache[key] = node
# update the LRU
self.cache_lru.insert(0, key)
- del self.cache[self.cache_lru.pop()]
+ if len(self.cache_lru) > ROW_CACHE_SIZE:
+ del self.cache[self.cache_lru.pop()]
return node
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)
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
# 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
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,))
- return int(cursor.fetchone()[0])
+ self.cursor.execute(sql, (nodeid,))
+ 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)
- return cursor.fetchone()[0]
+ self.cursor.execute(sql)
+ 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,))
- return [x[0] for x in cursor.fetchall()]
+ self.cursor.execute(sql, (retired,))
+ return [x[0] for x in self.cursor.fetchall()]
def addjournal(self, classname, nodeid, action, params, creator=None,
creation=None):
print >>hyperdb.DEBUG, 'addjournal', (nodeid, journaldate,
journaltag, action, params)
- cursor = self.conn.cursor()
- self.save_journal(cursor, classname, cols, nodeid, journaldate,
+ self.save_journal(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
'''
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
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.
''' 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)
value = pwd
d[propname] = value
+ # add the node and journal
+ self.db.addnode(self.classname, newid, d)
+
# extract the extraneous journalling gumpf and nuke it
if d.has_key('creator'):
creator = d['creator']
del d['creator']
+ else:
+ creator = None
if d.has_key('creation'):
creation = d['creation']
del d['creation']
+ else:
+ creation = None
if d.has_key('activity'):
del d['activity']
-
- # add the node and journal
- self.db.addnode(self.classname, newid, d)
- 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'
- cursor = self.db.conn.cursor()
- 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)
- 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
'''
- 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,))
- return int(cursor.fetchone()[0])
+ self.db.cursor.execute(sql, (nodeid,))
+ return int(self.db.sql_fetchone()[0])
def destroy(self, nodeid):
'''Destroy a node.
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
- 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,))
+ # 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 = 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)
+ l = [x[0] for x in self.db.sql_fetchall()]
if __debug__:
- 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()]
- except gadfly.database.error, message:
- if message == 'no more results':
- l = []
- raise
+ 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])
args = tuple(args)
if __debug__:
print >>hyperdb.DEBUG, 'filter', (self, sql, args)
- cursor = self.db.conn.cursor()
- cursor.execute(sql, args)
- l = cursor.fetchall()
+ self.db.cursor.execute(sql, args)
+ l = self.db.cursor.fetchall()
# return the IDs (the first column)
# XXX The filter(None, l) bit is sqlite-specific... if there's _NO_