62dfee21a6551ce413413d7fd4e5be86bd8795f2
1 # $Id: back_gadfly.py,v 1.29 2002-10-07 00:52:51 richard Exp $
2 ''' Gadlfy relational database hypderb backend.
4 About Gadfly
5 ============
7 Gadfly is a collection of python modules that provides relational
8 database functionality entirely implemented in Python. It supports a
9 subset of the intergalactic standard RDBMS Structured Query Language
10 SQL.
13 Additional Instance Requirements
14 ================================
16 The instance configuration must specify where the database is. It does this
17 with GADFLY_DATABASE, which is used as the arguments to the gadfly.gadfly()
18 method:
20 Using an on-disk database directly (not a good idea):
21 GADFLY_DATABASE = (database name, directory)
23 Using a network database (much better idea):
24 GADFLY_DATABASE = (policy, password, address, port)
26 Because multiple accesses directly to a gadfly database aren't handled, but
27 multiple network accesses are, it's strongly advised that the latter setup be
28 used.
30 '''
32 # standard python modules
33 import sys, os, time, re, errno, weakref, copy
35 # roundup modules
36 from roundup import hyperdb, date, password, roundupdb, security
37 from roundup.hyperdb import String, Password, Date, Interval, Link, \
38 Multilink, DatabaseError, Boolean, Number
40 # basic RDBMS backen implementation
41 from roundup.backends import rdbms_common
43 # the all-important gadfly :)
44 import gadfly
45 import gadfly.client
46 import gadfly.database
48 class Database(rdbms_common.Database):
49 # char to use for positional arguments
50 arg = '?'
52 def open_connection(self):
53 db = getattr(self.config, 'GADFLY_DATABASE', ('database', self.dir))
54 if len(db) == 2:
55 # ensure files are group readable and writable
56 os.umask(0002)
57 try:
58 self.conn = gadfly.gadfly(*db)
59 except IOError, error:
60 if error.errno != errno.ENOENT:
61 raise
62 self.database_schema = {}
63 self.conn = gadfly.gadfly()
64 self.conn.startup(*db)
65 self.cursor = self.conn.cursor()
66 self.cursor.execute('create table schema (schema varchar)')
67 self.cursor.execute('create table ids (name varchar, num integer)')
68 else:
69 self.cursor = self.conn.cursor()
70 self.cursor.execute('select schema from schema')
71 self.database_schema = self.cursor.fetchone()[0]
72 else:
73 self.conn = gadfly.client.gfclient(*db)
74 self.database_schema = self.load_dbschema()
76 def __repr__(self):
77 return '<roundfly 0x%x>'%id(self)
79 def sql_fetchone(self):
80 ''' Fetch a single row. If there's nothing to fetch, return None.
81 '''
82 try:
83 return self.cursor.fetchone()
84 except gadfly.database.error, message:
85 if message == 'no more results':
86 return None
87 raise
89 def sql_fetchall(self):
90 ''' Fetch a single row. If there's nothing to fetch, return [].
91 '''
92 try:
93 return self.cursor.fetchall()
94 except gadfly.database.error, message:
95 if message == 'no more results':
96 return []
97 raise
99 def save_dbschema(self, schema):
100 ''' Save the schema definition that the database currently implements
101 '''
102 self.sql('insert into schema values (?)', (self.database_schema,))
104 def load_dbschema(self):
105 ''' Load the schema definition that the database currently implements
106 '''
107 self.cursor.execute('select schema from schema')
108 return self.cursor.fetchone()[0]
110 def save_journal(self, 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 self.cursor.execute(sql, entry)
125 def load_journal(self, 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 self.cursor.execute(sql, (nodeid,))
134 res = []
135 for nodeid, date_stamp, user, action, params in self.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=(None,None),
141 group=(None,None)):
142 ''' Gadfly doesn't have a LIKE predicate :(
143 '''
144 cn = self.classname
146 # figure the WHERE clause from the filterspec
147 props = self.getprops()
148 frum = ['_'+cn]
149 where = []
150 args = []
151 a = self.db.arg
152 for k, v in filterspec.items():
153 propclass = props[k]
154 if isinstance(propclass, Multilink):
155 tn = '%s_%s'%(cn, k)
156 frum.append(tn)
157 if isinstance(v, type([])):
158 s = ','.join([a for x in v])
159 where.append('id=%s.nodeid and %s.linkid in (%s)'%(tn,tn,s))
160 args = args + v
161 else:
162 where.append('id=%s.nodeid and %s.linkid = %s'%(tn, tn, a))
163 args.append(v)
164 else:
165 if isinstance(v, type([])):
166 s = ','.join([a for x in v])
167 where.append('_%s in (%s)'%(k, s))
168 args = args + v
169 else:
170 where.append('_%s=%s'%(k, a))
171 args.append(v)
173 # add results of full text search
174 if search_matches is not None:
175 v = search_matches.keys()
176 s = ','.join([a for x in v])
177 where.append('id in (%s)'%s)
178 args = args + v
180 # "grouping" is just the first-order sorting in the SQL fetch
181 # can modify it...)
182 orderby = []
183 ordercols = []
184 if group[0] is not None and group[1] is not None:
185 if group[0] != '-':
186 orderby.append('_'+group[1])
187 ordercols.append('_'+group[1])
188 else:
189 orderby.append('_'+group[1]+' desc')
190 ordercols.append('_'+group[1])
192 # now add in the sorting
193 group = ''
194 if sort[0] is not None and sort[1] is not None:
195 direction, colname = sort
196 if direction != '-':
197 if colname == 'id':
198 orderby.append(colname)
199 else:
200 orderby.append('_'+colname)
201 ordercols.append('_'+colname)
202 else:
203 if colname == 'id':
204 orderby.append(colname+' desc')
205 ordercols.append(colname)
206 else:
207 orderby.append('_'+colname+' desc')
208 ordercols.append('_'+colname)
210 # construct the SQL
211 frum = ','.join(frum)
212 if where:
213 where = ' where ' + (' and '.join(where))
214 else:
215 where = ''
216 cols = ['id']
217 if orderby:
218 cols = cols + ordercols
219 order = ' order by %s'%(','.join(orderby))
220 else:
221 order = ''
222 cols = ','.join(cols)
223 sql = 'select %s from %s %s%s%s'%(cols, frum, where, group, order)
224 args = tuple(args)
225 if __debug__:
226 print >>hyperdb.DEBUG, 'filter', (self, sql, args)
227 self.db.cursor.execute(sql, args)
228 l = self.db.cursor.fetchall()
230 # return the IDs
231 return [row[0] for row in l]
233 def find(self, **propspec):
234 ''' Overload to filter out duplicates in the result
235 '''
236 d = {}
237 for k in rdbms_common.Class.find(self, **propspec):
238 d[k] = 1
239 return d.keys()
241 class Class(GadflyClass, rdbms_common.Class):
242 pass
243 class IssueClass(GadflyClass, rdbms_common.IssueClass):
244 pass
245 class FileClass(GadflyClass, rdbms_common.FileClass):
246 pass