a4e8a76127bfed5736578975753643dc8ec4ce7c
1 # $Id: back_gadfly.py,v 1.23 2002-09-19 02:37:41 richard Exp $
2 __doc__ = '''
3 About Gadfly
4 ============
6 Gadfly is a collection of python modules that provides relational
7 database functionality entirely implemented in Python. It supports a
8 subset of the intergalactic standard RDBMS Structured Query Language
9 SQL.
12 Basic Structure
13 ===============
15 We map roundup classes to relational tables. Automatically detect schema
16 changes and modify the gadfly table schemas appropriately. Multilinks
17 (which represent a many-to-many relationship) are handled through
18 intermediate tables.
20 Journals are stored adjunct to the per-class tables.
22 Table names and columns have "_" prepended so the names can't
23 clash with restricted names (like "order"). Retirement is determined by the
24 __retired__ column being true.
26 All columns are defined as VARCHAR, since it really doesn't matter what
27 type they're defined as. We stuff all kinds of data in there ;) [as long as
28 it's marshallable, gadfly doesn't care]
31 Additional Instance Requirements
32 ================================
34 The instance configuration must specify where the database is. It does this
35 with GADFLY_DATABASE, which is used as the arguments to the gadfly.gadfly()
36 method:
38 Using an on-disk database directly (not a good idea):
39 GADFLY_DATABASE = (database name, directory)
41 Using a network database (much better idea):
42 GADFLY_DATABASE = (policy, password, address, port)
44 Because multiple accesses directly to a gadfly database aren't handled, but
45 multiple network accesses are, it's strongly advised that the latter setup be
46 used.
48 '''
50 from roundup.backends.rdbms_common import *
52 # the all-important gadfly :)
53 import gadfly
54 import gadfly.client
55 import gadfly.database
57 class Database(Database):
58 # char to use for positional arguments
59 arg = '?'
61 def open_connection(self):
62 db = getattr(self.config, 'GADFLY_DATABASE', ('database', self.dir))
63 if len(db) == 2:
64 # ensure files are group readable and writable
65 os.umask(0002)
66 try:
67 self.conn = gadfly.gadfly(*db)
68 except IOError, error:
69 if error.errno != errno.ENOENT:
70 raise
71 self.database_schema = {}
72 self.conn = gadfly.gadfly()
73 self.conn.startup(*db)
74 cursor = self.conn.cursor()
75 cursor.execute('create table schema (schema varchar)')
76 cursor.execute('create table ids (name varchar, num integer)')
77 else:
78 cursor = self.conn.cursor()
79 cursor.execute('select schema from schema')
80 self.database_schema = cursor.fetchone()[0]
81 else:
82 self.conn = gadfly.client.gfclient(*db)
83 self.database_schema = self.load_dbschema(cursor)
85 def __repr__(self):
86 return '<roundfly 0x%x>'%id(self)
88 def sql_fetchone(self, cursor):
89 ''' Fetch a single row. If there's nothing to fetch, return None.
90 '''
91 try:
92 return cursor.fetchone()
93 except gadfly.database.error, message:
94 if message == 'no more results':
95 return None
96 raise
98 def save_dbschema(self, cursor, schema):
99 ''' Save the schema definition that the database currently implements
100 '''
101 self.sql(cursor, 'insert into schema values (?)',
102 (self.database_schema,))
104 def load_dbschema(self, cursor):
105 ''' Load the schema definition that the database currently implements
106 '''
107 cursor.execute('select schema from schema')
108 return cursor.fetchone()[0]
110 def save_journal(self, cursor, classname, cols, nodeid, journaldate,
111 journaltag, action, params):
112 ''' Save the journal entry to the database
113 '''
114 # nothing special to do
115 entry = (nodeid, journaldate, journaltag, action, params)
117 # do the insert
118 a = self.arg
119 sql = 'insert into %s__journal (%s) values (?,?,?,?,?)'%(classname,
120 cols)
121 if __debug__:
122 print >>hyperdb.DEBUG, 'addjournal', (self, sql, entry)
123 cursor.execute(sql, entry)
125 def load_journal(self, cursor, classname, cols, nodeid):
126 ''' Load the journal from the database
127 '''
128 # now get the journal entries
129 sql = 'select %s from %s__journal where nodeid=%s'%(cols, classname,
130 self.arg)
131 if __debug__:
132 print >>hyperdb.DEBUG, 'getjournal', (self, sql, nodeid)
133 cursor.execute(sql, (nodeid,))
134 res = []
135 for nodeid, date_stamp, user, action, params in cursor.fetchall():
136 res.append((nodeid, date.Date(date_stamp), user, action, params))
137 return res
139 class GadflyClass:
140 def filter(self, search_matches, filterspec, sort, group):
141 ''' Gadfly doesn't have a LIKE predicate :(
142 '''
143 cn = self.classname
145 # figure the WHERE clause from the filterspec
146 props = self.getprops()
147 frum = ['_'+cn]
148 where = []
149 args = []
150 a = self.db.arg
151 for k, v in filterspec.items():
152 propclass = props[k]
153 if isinstance(propclass, Multilink):
154 tn = '%s_%s'%(cn, k)
155 frum.append(tn)
156 if isinstance(v, type([])):
157 s = ','.join([self.arg for x in v])
158 where.append('id=%s.nodeid and %s.linkid in (%s)'%(tn,tn,s))
159 args = args + v
160 else:
161 where.append('id=%s.nodeid and %s.linkid = %s'%(tn, tn, a))
162 args.append(v)
163 else:
164 if isinstance(v, type([])):
165 s = ','.join([a for x in v])
166 where.append('_%s in (%s)'%(k, s))
167 args = args + v
168 else:
169 where.append('_%s=%s'%(k, a))
170 args.append(v)
172 # add results of full text search
173 if search_matches is not None:
174 v = search_matches.keys()
175 s = ','.join([a for x in v])
176 where.append('id in (%s)'%s)
177 args = args + v
179 # figure the order by clause
180 orderby = []
181 ordercols = []
182 if sort[0] is not None and sort[1] is not None:
183 direction, colname = sort
184 if direction != '-':
185 if colname == 'activity':
186 orderby.append('activity')
187 ordercols.append('max(%s__journal.date) as activity'%cn)
188 frum.append('%s__journal'%cn)
189 where.append('%s__journal.nodeid = _%s.id'%(cn, cn))
190 elif colname == 'id':
191 orderby.append(colname)
192 ordercols.append(colname)
193 else:
194 orderby.append('_'+colname)
195 ordercols.append('_'+colname)
196 else:
197 if colname == 'activity':
198 orderby.append('activity desc')
199 ordercols.append('max(%s__journal.date) as activity'%cn)
200 frum.append('%s__journal'%cn)
201 where.append('%s__journal.nodeid = _%s.id'%(cn, cn))
202 elif colname == 'id':
203 orderby.append(colname+' desc')
204 ordercols.append(colname)
205 else:
206 orderby.append('_'+colname+' desc')
207 ordercols.append('_'+colname)
209 # figure the group by clause
210 groupby = []
211 groupcols = []
212 if group[0] is not None and group[1] is not None:
213 if group[0] != '-':
214 groupby.append('_'+group[1])
215 groupcols.append('_'+group[1])
216 else:
217 groupby.append('_'+group[1]+' desc')
218 groupcols.append('_'+group[1])
220 # construct the SQL
221 frum = ','.join(frum)
222 where = ' and '.join(where)
223 cols = []
224 if orderby:
225 cols = cols + ordercols
226 order = ' order by %s'%(','.join(orderby))
227 else:
228 order = ''
229 if 0: #groupby:
230 cols = cols + groupcols
231 group = ' group by %s'%(','.join(groupby))
232 else:
233 group = ''
234 if 'id' not in cols:
235 cols.append('id')
236 cols = ','.join(cols)
237 sql = 'select %s from %s where %s%s%s'%(cols, frum, where, order,
238 group)
239 args = tuple(args)
240 if __debug__:
241 print >>hyperdb.DEBUG, 'filter', (self, sql, args)
242 cursor = self.db.conn.cursor()
243 cursor.execute(sql, args)
244 l = cursor.fetchall()
246 # return the IDs
247 return [row[0] for row in l]
249 class Class(GadflyClass, Class):
250 pass
251 class IssueClass(GadflyClass, IssueClass):
252 pass
253 class FileClass(GadflyClass, FileClass):
254 pass