![[tokkee]](http://tokkee.org/images/avatar.png){kind=avatar}
index a4e8a76127bfed5736578975753643dc8ec4ce7c..f81dd04826b4e38432da7062747c691dc057f654 100644 (file)
-# $Id: back_gadfly.py,v 1.23 2002-09-19 02:37:41 richard Exp $
-__doc__ = '''
+# $Id: back_gadfly.py,v 1.28 2002-10-03 06:56:29 richard Exp $
+''' Gadlfy relational database hypderb backend.
+
About Gadfly
============
SQL.
-Basic Structure
-===============
-
-We map roundup classes to relational tables. Automatically detect schema
-changes and modify the gadfly table schemas appropriately. 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.
-
-All columns are defined as VARCHAR, since it really doesn't matter what
-type they're defined as. We stuff all kinds of data in there ;) [as long as
-it's marshallable, gadfly doesn't care]
-
-
Additional Instance Requirements
================================
'''
-from roundup.backends.rdbms_common import *
+# standard python modules
+import sys, os, time, re, errno, weakref, copy
+
+# roundup modules
+from roundup import hyperdb, date, password, roundupdb, security
+from roundup.hyperdb import String, Password, Date, Interval, Link, \
+ Multilink, DatabaseError, Boolean, Number
+
+# basic RDBMS backen implementation
+from roundup.backends import rdbms_common
# the all-important gadfly :)
import gadfly
import gadfly.client
import gadfly.database
-class Database(Database):
+class Database(rdbms_common.Database):
# char to use for positional arguments
arg = '?'
self.database_schema = {}
self.conn = gadfly.gadfly()
self.conn.startup(*db)
- cursor = self.conn.cursor()
- cursor.execute('create table schema (schema varchar)')
- cursor.execute('create table ids (name varchar, num integer)')
+ self.cursor = self.conn.cursor()
+ self.cursor.execute('create table schema (schema varchar)')
+ self.cursor.execute('create table ids (name varchar, num integer)')
else:
- cursor = self.conn.cursor()
- cursor.execute('select schema from schema')
- self.database_schema = cursor.fetchone()[0]
+ self.cursor = self.conn.cursor()
+ self.cursor.execute('select schema from schema')
+ self.database_schema = self.cursor.fetchone()[0]
else:
self.conn = gadfly.client.gfclient(*db)
- self.database_schema = self.load_dbschema(cursor)
+ self.database_schema = self.load_dbschema()
def __repr__(self):
return '<roundfly 0x%x>'%id(self)
- def sql_fetchone(self, cursor):
+ def sql_fetchone(self):
''' Fetch a single row. If there's nothing to fetch, return None.
'''
try:
- return cursor.fetchone()
+ return self.cursor.fetchone()
except gadfly.database.error, message:
if message == 'no more results':
return None
raise
- def save_dbschema(self, cursor, schema):
+ def sql_fetchall(self):
+ ''' Fetch a single row. If there's nothing to fetch, return [].
+ '''
+ try:
+ return self.cursor.fetchall()
+ except gadfly.database.error, message:
+ if message == 'no more results':
+ return []
+ raise
+
+ def save_dbschema(self, schema):
''' Save the schema definition that the database currently implements
'''
- self.sql(cursor, 'insert into schema values (?)',
- (self.database_schema,))
+ self.sql('insert into schema values (?)', (self.database_schema,))
- def load_dbschema(self, cursor):
+ def load_dbschema(self):
''' Load the schema definition that the database currently implements
'''
- cursor.execute('select schema from schema')
- return cursor.fetchone()[0]
+ self.cursor.execute('select schema from schema')
+ return self.cursor.fetchone()[0]
- 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
'''
cols)
if __debug__:
print >>hyperdb.DEBUG, 'addjournal', (self, sql, entry)
- cursor.execute(sql, entry)
+ self.cursor.execute(sql, entry)
- def load_journal(self, cursor, classname, cols, nodeid):
+ def load_journal(self, classname, cols, nodeid):
''' Load the journal from the database
'''
# now get the journal entries
self.arg)
if __debug__:
print >>hyperdb.DEBUG, 'getjournal', (self, sql, nodeid)
- cursor.execute(sql, (nodeid,))
+ self.cursor.execute(sql, (nodeid,))
res = []
- for nodeid, date_stamp, user, action, params in cursor.fetchall():
+ for nodeid, date_stamp, user, action, params in self.cursor.fetchall():
res.append((nodeid, date.Date(date_stamp), user, action, params))
return res
tn = '%s_%s'%(cn, k)
frum.append(tn)
if isinstance(v, type([])):
- s = ','.join([self.arg for x in v])
+ s = ','.join([a for x in v])
where.append('id=%s.nodeid and %s.linkid in (%s)'%(tn,tn,s))
args = args + v
else:
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 != '-':
- 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':
+ if 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':
+ if 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 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 = ''
- 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)
+ 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()
- cursor.execute(sql, args)
- l = cursor.fetchall()
+ self.db.cursor.execute(sql, args)
+ l = self.db.cursor.fetchall()
# return the IDs
return [row[0] for row in l]
-class Class(GadflyClass, Class):
+ def find(self, **propspec):
+ ''' Overload to filter out duplicates in the result
+ '''
+ d = {}
+ for k in rdbms_common.Class.find(self, **propspec):
+ d[k] = 1
+ return d.keys()
+
+class Class(GadflyClass, rdbms_common.Class):
pass
-class IssueClass(GadflyClass, IssueClass):
+class IssueClass(GadflyClass, rdbms_common.IssueClass):
pass
-class FileClass(GadflyClass, FileClass):
+class FileClass(GadflyClass, rdbms_common.FileClass):
pass