1 # $Id: back_gadfly.py,v 1.26 2002-09-24 01:59:28 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 self.cursor = self.conn.cursor()
75 self.cursor.execute('create table schema (schema varchar)')
76 self.cursor.execute('create table ids (name varchar, num integer)')
77 else:
78 self.cursor = self.conn.cursor()
79 self.cursor.execute('select schema from schema')
80 self.database_schema = self.cursor.fetchone()[0]
81 else:
82 self.conn = gadfly.client.gfclient(*db)
83 self.database_schema = self.load_dbschema()
85 def __repr__(self):
86 return '<roundfly 0x%x>'%id(self)
88 def sql_fetchone(self):
89 ''' Fetch a single row. If there's nothing to fetch, return None.
90 '''
91 try:
92 return self.cursor.fetchone()
93 except gadfly.database.error, message:
94 if message == 'no more results':
95 return None
96 raise
98 def sql_fetchall(self):
99 ''' Fetch a single row. If there's nothing to fetch, return [].
100 '''
101 try:
102 return self.cursor.fetchall()
103 except gadfly.database.error, message:
104 if message == 'no more results':
105 return []
106 raise
108 def save_dbschema(self, schema):
109 ''' Save the schema definition that the database currently implements
110 '''
111 self.sql('insert into schema values (?)', (self.database_schema,))
113 def load_dbschema(self):
114 ''' Load the schema definition that the database currently implements
115 '''
116 self.cursor.execute('select schema from schema')
117 return self.cursor.fetchone()[0]
119 def save_journal(self, classname, cols, nodeid, journaldate,
120 journaltag, action, params):
121 ''' Save the journal entry to the database
122 '''
123 # nothing special to do
124 entry = (nodeid, journaldate, journaltag, action, params)
126 # do the insert
127 a = self.arg
128 sql = 'insert into %s__journal (%s) values (?,?,?,?,?)'%(classname,
129 cols)
130 if __debug__:
131 print >>hyperdb.DEBUG, 'addjournal', (self, sql, entry)
132 self.cursor.execute(sql, entry)
134 def load_journal(self, classname, cols, nodeid):
135 ''' Load the journal from the database
136 '''
137 # now get the journal entries
138 sql = 'select %s from %s__journal where nodeid=%s'%(cols, classname,
139 self.arg)
140 if __debug__:
141 print >>hyperdb.DEBUG, 'getjournal', (self, sql, nodeid)
142 self.cursor.execute(sql, (nodeid,))
143 res = []
144 for nodeid, date_stamp, user, action, params in self.cursor.fetchall():
145 res.append((nodeid, date.Date(date_stamp), user, action, params))
146 return res
148 class GadflyClass:
149 def filter(self, search_matches, filterspec, sort, group):
150 ''' Gadfly doesn't have a LIKE predicate :(
151 '''
152 cn = self.classname
154 # figure the WHERE clause from the filterspec
155 props = self.getprops()
156 frum = ['_'+cn]
157 where = []
158 args = []
159 a = self.db.arg
160 for k, v in filterspec.items():
161 propclass = props[k]
162 if isinstance(propclass, Multilink):
163 tn = '%s_%s'%(cn, k)
164 frum.append(tn)
165 if isinstance(v, type([])):
166 s = ','.join([a for x in v])
167 where.append('id=%s.nodeid and %s.linkid in (%s)'%(tn,tn,s))
168 args = args + v
169 else:
170 where.append('id=%s.nodeid and %s.linkid = %s'%(tn, tn, a))
171 args.append(v)
172 else:
173 if isinstance(v, type([])):
174 s = ','.join([a for x in v])
175 where.append('_%s in (%s)'%(k, s))
176 args = args + v
177 else:
178 where.append('_%s=%s'%(k, a))
179 args.append(v)
181 # add results of full text search
182 if search_matches is not None:
183 v = search_matches.keys()
184 s = ','.join([a for x in v])
185 where.append('id in (%s)'%s)
186 args = args + v
188 # "grouping" is just the first-order sorting in the SQL fetch
189 # can modify it...)
190 orderby = []
191 ordercols = []
192 if group[0] is not None and group[1] is not None:
193 if group[0] != '-':
194 orderby.append('_'+group[1])
195 ordercols.append('_'+group[1])
196 else:
197 orderby.append('_'+group[1]+' desc')
198 ordercols.append('_'+group[1])
200 # now add in the sorting
201 group = ''
202 if sort[0] is not None and sort[1] is not None:
203 direction, colname = sort
204 if direction != '-':
205 if colname == 'id':
206 orderby.append(colname)
207 else:
208 orderby.append('_'+colname)
209 ordercols.append('_'+colname)
210 else:
211 if colname == 'id':
212 orderby.append(colname+' desc')
213 ordercols.append(colname)
214 else:
215 orderby.append('_'+colname+' desc')
216 ordercols.append('_'+colname)
218 # construct the SQL
219 frum = ','.join(frum)
220 if where:
221 where = ' where ' + (' and '.join(where))
222 else:
223 where = ''
224 cols = ['id']
225 if orderby:
226 cols = cols + ordercols
227 order = ' order by %s'%(','.join(orderby))
228 else:
229 order = ''
230 cols = ','.join(cols)
231 sql = 'select %s from %s %s%s%s'%(cols, frum, where, group, order)
232 args = tuple(args)
233 if __debug__:
234 print >>hyperdb.DEBUG, 'filter', (self, sql, args)
235 self.db.cursor.execute(sql, args)
236 l = self.db.cursor.fetchall()
238 # return the IDs
239 return [row[0] for row in l]
241 class Class(GadflyClass, Class):
242 pass
243 class IssueClass(GadflyClass, IssueClass):
244 pass
245 class FileClass(GadflyClass, FileClass):
246 pass