From 2326c3f96a02de8c1452de5364f3c14bf2ab85db Mon Sep 17 00:00:00 2001 From: richard Date: Thu, 19 Sep 2002 02:37:41 +0000 Subject: [PATCH] Added some words to the installation doc about choosing backends. Added hyperdb Class.filter unit tests - gadfly currently fails substring searching, but I knew it would :( Lots of fixes to the RDBMS backend - it works a treat now! A couple of other cleanups in CGI land... git-svn-id: http://svn.roundup-tracker.org/svnroot/roundup/trunk@1194 57a73879-2fb5-44c3-a270-3262357dd7e2 --- doc/installation.txt | 50 +++++++++---- roundup/backends/back_anydbm.py | 6 +- roundup/backends/back_gadfly.py | 119 ++++++++++++++++++++++++++++++- roundup/backends/back_sqlite.py | 79 +------------------- roundup/backends/rdbms_common.py | 97 ++++++++++++++++++------- roundup/cgi/client.py | 15 +--- roundup/cgi/templating.py | 2 +- test/test_db.py | 66 ++++++++++++----- 8 files changed, 283 insertions(+), 151 deletions(-) diff --git a/doc/installation.txt b/doc/installation.txt index a39595c..ab103e7 100644 --- a/doc/installation.txt +++ b/doc/installation.txt @@ -2,7 +2,7 @@ Installing Roundup ================== -:Version: $Revision: 1.23 $ +:Version: $Revision: 1.24 $ .. contents:: @@ -35,20 +35,40 @@ holds issues which have priorities and statuses. Each issue may also have a set of messages which are disseminated to the issue's list of nosy users. -Extended Template ------------------ - -The extended template adds additional information to issues: product, -platform, version, targetversion and supportcall. -There is an additional class for -handling support calls, which includes a time log, customername, rate and -source. - -The priorty class has different default entries too: "fatal-bug", "bug", -"usability" and "feature". - -Users of this template will want to change the contents of the product -class as soon as the tracker is created. +Backends +-------- + +The actual storage of Roundup tracker information is handled by backends. +There's several to choose from, each with benefits and limitations: + +**anydbm** + This backend is guaranteed to work on any system that Python runs on. It + will generally choose the best *dbm backend that is available on your system + (from the list dbhash, gdbm, dbm, dumbdbm). It is the least scaleable of all + backends, but performs well enough for a smallish tracker (a couple of + thousand issues, under fifty users, ...). +**bsddb** + This effectively the same as anydbm, but uses the bsddb backend. This allows + it to gain some performance and scaling benefits. +**bsddb3** + Again, this effectively the same as anydbm, but uses the bsddb3 backend. + This allows it to gain some performance and scaling benefits. +**sqlite** + This uses the SQLite embedded RDBMS to provide a fast, scaleable backend. + There are no limitations. +**gadfly** + This is a proof-of-concept relational database backend, not really intended + for actual production use, although it can be. It uses the Gadfly RDBMS + to store data. It is unable to perform string searches due to gadfly not + having a LIKE operation. It should scale well, assuming a client/server + setup is used. +**metakit** + This backend is implemented over the metakit storage system, using Mk4Py as + the interface. It scales much better than the *dbm backends, but has some + missing features: + + - you may not unset properties once they are set + - journal retrieval is not implemented Prerequisites diff --git a/roundup/backends/back_anydbm.py b/roundup/backends/back_anydbm.py index e50a960..ef24fd1 100644 --- a/roundup/backends/back_anydbm.py +++ b/roundup/backends/back_anydbm.py @@ -15,7 +15,7 @@ # BASIS, AND THERE IS NO OBLIGATION WHATSOEVER TO PROVIDE MAINTENANCE, # SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. # -#$Id: back_anydbm.py,v 1.80 2002-09-17 23:59:59 richard Exp $ +#$Id: back_anydbm.py,v 1.81 2002-09-19 02:37:41 richard Exp $ ''' This module defines a backend that saves the hyperdatabase in a database chosen by anydbm. It is guaranteed to always be available in python @@ -1499,6 +1499,10 @@ class Class(hyperdb.Class): "sort" and "group" are (dir, prop) where dir is '+', '-' or None and prop is a prop name or None "search_matches" is {nodeid: marker} + + The filter must match all properties specificed - but if the + property value to match is a list, any one of the values in the + list may match for that property to match. ''' cn = self.classname diff --git a/roundup/backends/back_gadfly.py b/roundup/backends/back_gadfly.py index 8bd2346..a4e8a76 100644 --- a/roundup/backends/back_gadfly.py +++ b/roundup/backends/back_gadfly.py @@ -1,4 +1,4 @@ -# $Id: back_gadfly.py,v 1.22 2002-09-18 05:07:47 richard Exp $ +# $Id: back_gadfly.py,v 1.23 2002-09-19 02:37:41 richard Exp $ __doc__ = ''' About Gadfly ============ @@ -136,3 +136,120 @@ class Database(Database): res.append((nodeid, date.Date(date_stamp), user, action, params)) return res +class GadflyClass: + def filter(self, search_matches, filterspec, sort, group): + ''' Gadfly doesn't have a LIKE predicate :( + ''' + cn = self.classname + + # figure the WHERE clause from the filterspec + props = self.getprops() + frum = ['_'+cn] + where = [] + args = [] + a = self.db.arg + for k, v in filterspec.items(): + propclass = props[k] + if isinstance(propclass, Multilink): + tn = '%s_%s'%(cn, k) + frum.append(tn) + if isinstance(v, type([])): + s = ','.join([self.arg for x in v]) + where.append('id=%s.nodeid and %s.linkid in (%s)'%(tn,tn,s)) + args = args + v + else: + where.append('id=%s.nodeid and %s.linkid = %s'%(tn, tn, a)) + args.append(v) + else: + 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) + + # add results of full text search + if search_matches is not None: + v = search_matches.keys() + s = ','.join([a for x in v]) + where.append('id in (%s)'%s) + args = args + v + + # figure the order by clause + orderby = [] + ordercols = [] + if sort[0] is not None and sort[1] is not None: + direction, colname = sort + if direction != '-': + if colname == 'activity': + orderby.append('activity') + ordercols.append('max(%s__journal.date) as activity'%cn) + frum.append('%s__journal'%cn) + where.append('%s__journal.nodeid = _%s.id'%(cn, cn)) + elif colname == 'id': + orderby.append(colname) + ordercols.append(colname) + else: + orderby.append('_'+colname) + ordercols.append('_'+colname) + else: + if colname == 'activity': + orderby.append('activity desc') + ordercols.append('max(%s__journal.date) as activity'%cn) + frum.append('%s__journal'%cn) + where.append('%s__journal.nodeid = _%s.id'%(cn, cn)) + elif colname == 'id': + orderby.append(colname+' desc') + ordercols.append(colname) + else: + orderby.append('_'+colname+' desc') + ordercols.append('_'+colname) + + # figure the group by clause + groupby = [] + groupcols = [] + if group[0] is not None and group[1] is not None: + if group[0] != '-': + groupby.append('_'+group[1]) + groupcols.append('_'+group[1]) + else: + groupby.append('_'+group[1]+' desc') + groupcols.append('_'+group[1]) + + # construct the SQL + frum = ','.join(frum) + where = ' and '.join(where) + cols = [] + if orderby: + cols = cols + ordercols + order = ' order by %s'%(','.join(orderby)) + else: + order = '' + if 0: #groupby: + cols = cols + groupcols + group = ' group by %s'%(','.join(groupby)) + else: + group = '' + if 'id' not in cols: + cols.append('id') + cols = ','.join(cols) + sql = 'select %s from %s where %s%s%s'%(cols, frum, where, order, + group) + args = tuple(args) + if __debug__: + print >>hyperdb.DEBUG, 'filter', (self, sql, args) + cursor = self.db.conn.cursor() + cursor.execute(sql, args) + l = cursor.fetchall() + + # return the IDs + return [row[0] for row in l] + +class Class(GadflyClass, Class): + pass +class IssueClass(GadflyClass, IssueClass): + pass +class FileClass(GadflyClass, FileClass): + pass + diff --git a/roundup/backends/back_sqlite.py b/roundup/backends/back_sqlite.py index 6f1f359..965245f 100644 --- a/roundup/backends/back_sqlite.py +++ b/roundup/backends/back_sqlite.py @@ -1,4 +1,4 @@ -# $Id: back_sqlite.py,v 1.2 2002-09-18 07:04:37 richard Exp $ +# $Id: back_sqlite.py,v 1.3 2002-09-19 02:37:41 richard Exp $ __doc__ = ''' See https://pysqlite.sourceforge.net/ for pysqlite info ''' @@ -128,80 +128,3 @@ class Database(Database): d[k] = v return d -class Class(Class): - _marker = [] - def get(self, nodeid, propname, default=_marker, cache=1): - '''Get the value of a property on an existing node of this class. - - 'nodeid' must be the id of an existing node of this class or an - IndexError is raised. 'propname' must be the name of a property - of this class or a KeyError is raised. - - 'cache' indicates whether the transaction cache should be queried - for the node. If the node has been modified and you need to - determine what its values prior to modification are, you need to - set cache=0. - ''' - if propname == 'id': - return nodeid - - if propname == 'creation': - if not self.do_journal: - raise ValueError, 'Journalling is disabled for this class' - journal = self.db.getjournal(self.classname, nodeid) - if journal: - return self.db.getjournal(self.classname, nodeid)[0][1] - else: - # on the strange chance that there's no journal - return date.Date() - if propname == 'activity': - if not self.do_journal: - raise ValueError, 'Journalling is disabled for this class' - journal = self.db.getjournal(self.classname, nodeid) - if journal: - return self.db.getjournal(self.classname, nodeid)[-1][1] - else: - # on the strange chance that there's no journal - return date.Date() - if propname == 'creator': - if not self.do_journal: - raise ValueError, 'Journalling is disabled for this class' - journal = self.db.getjournal(self.classname, nodeid) - if journal: - name = self.db.getjournal(self.classname, nodeid)[0][2] - else: - return None - try: - return self.db.user.lookup(name) - except KeyError: - # the journaltag user doesn't exist any more - return None - - # get the property (raises KeyErorr if invalid) - prop = self.properties[propname] - - # get the node's dict - d = self.db.getnode(self.classname, nodeid) #, cache=cache) - - if not d.has_key(propname): - if default is self._marker: - if isinstance(prop, Multilink): - return [] - else: - return None - else: - return default - - # special handling for some types - if isinstance(prop, Multilink): - # don't pass our list to other code - return d[propname][:] - elif d[propname] is None: - # always return None right now, no conversion - return None - elif isinstance(prop, Boolean) or isinstance(prop, Number): - # turn Booleans and Numbers into integers - return int(d[propname]) - - return d[propname] - diff --git a/roundup/backends/rdbms_common.py b/roundup/backends/rdbms_common.py index 1d1f8a3..42d832d 100644 --- a/roundup/backends/rdbms_common.py +++ b/roundup/backends/rdbms_common.py @@ -1,4 +1,4 @@ -# $Id: rdbms_common.py,v 1.2 2002-09-18 07:04:38 richard Exp $ +# $Id: rdbms_common.py,v 1.3 2002-09-19 02:37:41 richard Exp $ # standard python modules import sys, os, time, re, errno, weakref, copy @@ -53,6 +53,11 @@ class Database(FileStorage, hyperdb.Database, roundupdb.Database): ''' 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): ''' Save the schema definition that the database currently implements ''' @@ -1588,6 +1593,10 @@ class Class(hyperdb.Class): "sort" and "group" are (dir, prop) where dir is '+', '-' or None and prop is a prop name or None "search_matches" is {nodeid: marker} + + The filter must match all properties specificed - but if the + property value to match is a list, any one of the values in the + list may match for that property to match. ''' cn = self.classname @@ -1599,6 +1608,7 @@ class Class(hyperdb.Class): a = self.db.arg for k, v in filterspec.items(): propclass = props[k] + # now do other where clause stuff if isinstance(propclass, Multilink): tn = '%s_%s'%(cn, k) frum.append(tn) @@ -1607,9 +1617,35 @@ class Class(hyperdb.Class): where.append('id=%s.nodeid and %s.linkid in (%s)'%(tn,tn,s)) args = args + v else: - where.append('id=%s.nodeid and %s.linkid = %s'%(tn, tn, - self.arg)) + where.append('id=%s.nodeid and %s.linkid = %s'%(tn, tn, a)) args.append(v) + elif isinstance(propclass, String): + if not isinstance(v, type([])): + v = [v] + + # Quote the bits in the string that need it and then embed + # in a "substring" search. Note - need to quote the '%' so + # they make it through the python layer happily + v = ['%%'+self.db.sql_stringquote(s)+'%%' for s in v] + + # now add to the where clause + where.append(' or '.join(["_%s LIKE '%s'"%(k, s) for s in v])) + # note: args are embedded in the query string now + elif isinstance(propclass, Link): + if isinstance(v, type([])): + if '-1' in v: + v.remove('-1') + xtra = ' or _%s is NULL'%k + s = ','.join([a for x in v]) + where.append('(_%s in (%s)%s)'%(k, s, xtra)) + args = args + v + else: + if v == '-1': + v = None + where.append('_%s is NULL'%k) + else: + where.append('_%s=%s'%(k, a)) + args.append(v) else: if isinstance(v, type([])): s = ','.join([a for x in v]) @@ -1626,9 +1662,20 @@ class Class(hyperdb.Class): where.append('id in (%s)'%s) args = args + v - # figure the order by clause + # "grouping" is just the first-order sorting in the SQL fetch + # can modify it...) orderby = [] ordercols = [] + if group[0] is not None and group[1] is not None: + if group[0] != '-': + orderby.append('_'+group[1]) + ordercols.append('_'+group[1]) + else: + orderby.append('_'+group[1]+' desc') + ordercols.append('_'+group[1]) + + # now add in the sorting + group = '' if sort[0] is not None and sort[1] is not None: direction, colname = sort if direction != '-': @@ -1637,6 +1684,10 @@ class Class(hyperdb.Class): ordercols.append('max(%s__journal.date) as activity'%cn) frum.append('%s__journal'%cn) where.append('%s__journal.nodeid = _%s.id'%(cn, cn)) + # we need to group by id + group = ' group by id' + elif colname == 'id': + orderby.append(colname) else: orderby.append('_'+colname) ordercols.append('_'+colname) @@ -1646,46 +1697,42 @@ class Class(hyperdb.Class): ordercols.append('max(%s__journal.date) as activity'%cn) frum.append('%s__journal'%cn) where.append('%s__journal.nodeid = _%s.id'%(cn, cn)) + # we need to group by id + group = ' group by id' + elif colname == 'id': + orderby.append(colname+' desc') + ordercols.append(colname) else: orderby.append('_'+colname+' desc') ordercols.append('_'+colname) - # figure the group by clause - groupby = [] - groupcols = [] - if group[0] is not None and group[1] is not None: - if group[0] != '-': - groupby.append('_'+group[1]) - groupcols.append('_'+group[1]) - else: - groupby.append('_'+group[1]+' desc') - groupcols.append('_'+group[1]) - # construct the SQL frum = ','.join(frum) - where = ' and '.join(where) + if where: + where = ' where ' + (' and '.join(where)) + else: + where = '' cols = ['id'] if orderby: cols = cols + ordercols order = ' order by %s'%(','.join(orderby)) else: order = '' - if 0: #groupby: - cols = cols + groupcols - group = ' group by %s'%(','.join(groupby)) - else: - group = '' cols = ','.join(cols) - sql = 'select %s from %s where %s%s%s'%(cols, frum, where, order, - group) + 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() + print (sql, args) cursor.execute(sql, args) + l = cursor.fetchall() + print l - # return the IDs - return [row[0] for row in 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]) def count(self): '''Get the number of nodes in this class. diff --git a/roundup/cgi/client.py b/roundup/cgi/client.py index 8825350..2ab969e 100644 --- a/roundup/cgi/client.py +++ b/roundup/cgi/client.py @@ -1,4 +1,4 @@ -# $Id: client.py,v 1.39 2002-09-18 06:33:06 richard Exp $ +# $Id: client.py,v 1.40 2002-09-19 02:37:41 richard Exp $ __doc__ = """ WWW request handler (also used in the stand-alone server). @@ -474,19 +474,6 @@ class Client: self.userid = self.db.user.lookup('anonymous') self.user = 'anonymous' - def logout(self): - ''' Make us really anonymous - nuke the cookie too - ''' - self.make_user_anonymous() - - # construct the logout cookie - now = Cookie._getdate() - path = '/'.join((self.env['SCRIPT_NAME'], self.env['TRACKER_NAME'], - '')) - self.additional_headers['Set-Cookie'] = \ - 'roundup_user_2=deleted; Max-Age=0; expires=%s; Path=%s;'%(now, path) - self.login() - def opendb(self, user): ''' Open the database. ''' diff --git a/roundup/cgi/templating.py b/roundup/cgi/templating.py index f78313b..1de56e1 100644 --- a/roundup/cgi/templating.py +++ b/roundup/cgi/templating.py @@ -445,7 +445,7 @@ class HTMLItem(HTMLPermissions): def __getitem__(self, item): ''' return an HTMLProperty instance ''' - #print 'HTMLItem.getitem', (self, item) + #print 'HTMLItem.getitem', (self, item) if item == 'id': return self._nodeid diff --git a/test/test_db.py b/test/test_db.py index 6d5cabc..c9226d2 100644 --- a/test/test_db.py +++ b/test/test_db.py @@ -15,7 +15,7 @@ # BASIS, AND THERE IS NO OBLIGATION WHATSOEVER TO PROVIDE MAINTENANCE, # SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. # -# $Id: test_db.py,v 1.49 2002-09-18 07:04:39 richard Exp $ +# $Id: test_db.py,v 1.50 2002-09-19 02:37:41 richard Exp $ import unittest, os, shutil, time @@ -476,6 +476,40 @@ class anydbmDBTestCase(MyTestCase): self.assertEquals(self.db.indexer.search(['flebble'], self.db.issue), {'1': {}}) + def filteringSetup(self): + for user in ( + {'username': 'bleep'}, + {'username': 'blop'}, + {'username': 'blorp'}): + self.db.user.create(**user) + iss = self.db.issue + for issue in ( + {'title': 'issue one', 'status': '2'}, + {'title': 'issue two', 'status': '1'}, + {'title': 'issue three', 'status': '1', 'nosy': ['1','2']}): + self.db.issue.create(**issue) + self.db.commit() + return self.assertEqual, self.db.issue.filter + + def testFilteringString(self): + ae, filt = self.filteringSetup() + ae(filt(None, {'title': 'issue one'}, ('+','id'), (None,None)), ['1']) + ae(filt(None, {'title': 'issue'}, ('+','id'), (None,None)), + ['1','2','3']) + + def testFilteringLink(self): + ae, filt = self.filteringSetup() + ae(filt(None, {'status': '1'}, ('+','id'), (None,None)), ['2','3']) + + def testFilteringMultilink(self): + ae, filt = self.filteringSetup() + ae(filt(None, {'nosy': '2'}, ('+','id'), (None,None)), ['3']) + + def testFilteringMany(self): + ae, filt = self.filteringSetup() + ae(filt(None, {'nosy': '2', 'status': '1'}, ('+','id'), (None,None)), + ['3']) + class anydbmReadOnlyDBTestCase(MyTestCase): def setUp(self): from roundup.backends import anydbm @@ -678,7 +712,21 @@ def suite(): unittest.makeSuite(anydbmDBTestCase, 'test'), unittest.makeSuite(anydbmReadOnlyDBTestCase, 'test') ] -# return unittest.TestSuite(l) + #return unittest.TestSuite(l) + + try: + import sqlite + l.append(unittest.makeSuite(sqliteDBTestCase, 'test')) + l.append(unittest.makeSuite(sqliteReadOnlyDBTestCase, 'test')) + except: + print 'sqlite module not found, skipping gadfly DBTestCase' + + try: + import gadfly + l.append(unittest.makeSuite(gadflyDBTestCase, 'test')) + l.append(unittest.makeSuite(gadflyReadOnlyDBTestCase, 'test')) + except: + print 'gadfly module not found, skipping gadfly DBTestCase' try: import bsddb @@ -694,20 +742,6 @@ def suite(): except: print 'bsddb3 module not found, skipping bsddb3 DBTestCase' - try: - import gadfly - l.append(unittest.makeSuite(gadflyDBTestCase, 'test')) - l.append(unittest.makeSuite(gadflyReadOnlyDBTestCase, 'test')) - except: - print 'gadfly module not found, skipping gadfly DBTestCase' - - try: - import sqlite - l.append(unittest.makeSuite(sqliteDBTestCase, 'test')) - l.append(unittest.makeSuite(sqliteReadOnlyDBTestCase, 'test')) - except: - print 'sqlite module not found, skipping gadfly DBTestCase' - try: import metakit l.append(unittest.makeSuite(metakitDBTestCase, 'test')) -- 2.30.2