1 # $Id: back_gadfly.py,v 1.27 2002-09-26 03:04:24 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 # standard python modules
51 import sys, os, time, re, errno, weakref, copy
53 # roundup modules
54 from roundup import hyperdb, date, password, roundupdb, security
55 from roundup.hyperdb import String, Password, Date, Interval, Link, \
56 Multilink, DatabaseError, Boolean, Number
58 # basic RDBMS backen implementation
59 from roundup.backends import rdbms_common
61 # the all-important gadfly :)
62 import gadfly
63 import gadfly.client
64 import gadfly.database
66 class Database(rdbms_common.Database):
67 # char to use for positional arguments
68 arg = '?'
70 def open_connection(self):
71 db = getattr(self.config, 'GADFLY_DATABASE', ('database', self.dir))
72 if len(db) == 2:
73 # ensure files are group readable and writable
74 os.umask(0002)
75 try:
76 self.conn = gadfly.gadfly(*db)
77 except IOError, error:
78 if error.errno != errno.ENOENT:
79 raise
80 self.database_schema = {}
81 self.conn = gadfly.gadfly()
82 self.conn.startup(*db)
83 self.cursor = self.conn.cursor()
84 self.cursor.execute('create table schema (schema varchar)')
85 self.cursor.execute('create table ids (name varchar, num integer)')
86 else:
87 self.cursor = self.conn.cursor()
88 self.cursor.execute('select schema from schema')
89 self.database_schema = self.cursor.fetchone()[0]
90 else:
91 self.conn = gadfly.client.gfclient(*db)
92 self.database_schema = self.load_dbschema()
94 def __repr__(self):
95 return '<roundfly 0x%x>'%id(self)
97 def sql_fetchone(self):
98 ''' Fetch a single row. If there's nothing to fetch, return None.
99 '''
100 try:
101 return self.cursor.fetchone()
102 except gadfly.database.error, message:
103 if message == 'no more results':
104 return None
105 raise
107 def sql_fetchall(self):
108 ''' Fetch a single row. If there's nothing to fetch, return [].
109 '''
110 try:
111 return self.cursor.fetchall()
112 except gadfly.database.error, message:
113 if message == 'no more results':
114 return []
115 raise
117 def save_dbschema(self, schema):
118 ''' Save the schema definition that the database currently implements
119 '''
120 self.sql('insert into schema values (?)', (self.database_schema,))
122 def load_dbschema(self):
123 ''' Load the schema definition that the database currently implements
124 '''
125 self.cursor.execute('select schema from schema')
126 return self.cursor.fetchone()[0]
128 def save_journal(self, classname, cols, nodeid, journaldate,
129 journaltag, action, params):
130 ''' Save the journal entry to the database
131 '''
132 # nothing special to do
133 entry = (nodeid, journaldate, journaltag, action, params)
135 # do the insert
136 a = self.arg
137 sql = 'insert into %s__journal (%s) values (?,?,?,?,?)'%(classname,
138 cols)
139 if __debug__:
140 print >>hyperdb.DEBUG, 'addjournal', (self, sql, entry)
141 self.cursor.execute(sql, entry)
143 def load_journal(self, classname, cols, nodeid):
144 ''' Load the journal from the database
145 '''
146 # now get the journal entries
147 sql = 'select %s from %s__journal where nodeid=%s'%(cols, classname,
148 self.arg)
149 if __debug__:
150 print >>hyperdb.DEBUG, 'getjournal', (self, sql, nodeid)
151 self.cursor.execute(sql, (nodeid,))
152 res = []
153 for nodeid, date_stamp, user, action, params in self.cursor.fetchall():
154 res.append((nodeid, date.Date(date_stamp), user, action, params))
155 return res
157 class GadflyClass:
158 def filter(self, search_matches, filterspec, sort, group):
159 ''' Gadfly doesn't have a LIKE predicate :(
160 '''
161 cn = self.classname
163 # figure the WHERE clause from the filterspec
164 props = self.getprops()
165 frum = ['_'+cn]
166 where = []
167 args = []
168 a = self.db.arg
169 for k, v in filterspec.items():
170 propclass = props[k]
171 if isinstance(propclass, Multilink):
172 tn = '%s_%s'%(cn, k)
173 frum.append(tn)
174 if isinstance(v, type([])):
175 s = ','.join([a for x in v])
176 where.append('id=%s.nodeid and %s.linkid in (%s)'%(tn,tn,s))
177 args = args + v
178 else:
179 where.append('id=%s.nodeid and %s.linkid = %s'%(tn, tn, a))
180 args.append(v)
181 else:
182 if isinstance(v, type([])):
183 s = ','.join([a for x in v])
184 where.append('_%s in (%s)'%(k, s))
185 args = args + v
186 else:
187 where.append('_%s=%s'%(k, a))
188 args.append(v)
190 # add results of full text search
191 if search_matches is not None:
192 v = search_matches.keys()
193 s = ','.join([a for x in v])
194 where.append('id in (%s)'%s)
195 args = args + v
197 # "grouping" is just the first-order sorting in the SQL fetch
198 # can modify it...)
199 orderby = []
200 ordercols = []
201 if group[0] is not None and group[1] is not None:
202 if group[0] != '-':
203 orderby.append('_'+group[1])
204 ordercols.append('_'+group[1])
205 else:
206 orderby.append('_'+group[1]+' desc')
207 ordercols.append('_'+group[1])
209 # now add in the sorting
210 group = ''
211 if sort[0] is not None and sort[1] is not None:
212 direction, colname = sort
213 if direction != '-':
214 if colname == 'id':
215 orderby.append(colname)
216 else:
217 orderby.append('_'+colname)
218 ordercols.append('_'+colname)
219 else:
220 if colname == 'id':
221 orderby.append(colname+' desc')
222 ordercols.append(colname)
223 else:
224 orderby.append('_'+colname+' desc')
225 ordercols.append('_'+colname)
227 # construct the SQL
228 frum = ','.join(frum)
229 if where:
230 where = ' where ' + (' and '.join(where))
231 else:
232 where = ''
233 cols = ['id']
234 if orderby:
235 cols = cols + ordercols
236 order = ' order by %s'%(','.join(orderby))
237 else:
238 order = ''
239 cols = ','.join(cols)
240 sql = 'select %s from %s %s%s%s'%(cols, frum, where, group, order)
241 args = tuple(args)
242 if __debug__:
243 print >>hyperdb.DEBUG, 'filter', (self, sql, args)
244 self.db.cursor.execute(sql, args)
245 l = self.db.cursor.fetchall()
247 # return the IDs
248 return [row[0] for row in l]
250 def find(self, **propspec):
251 ''' Overload to filter out duplicates in the result
252 '''
253 d = {}
254 for k in rdbms_common.Class.find(self, **propspec):
255 d[k] = 1
256 return d.keys()
258 class Class(GadflyClass, rdbms_common.Class):
259 pass
260 class IssueClass(GadflyClass, rdbms_common.IssueClass):
261 pass
262 class FileClass(GadflyClass, rdbms_common.FileClass):
263 pass