1 """
2 MoinMoin - Wiki Database Macro.
3
4 Copyright (c) 2003 by Nick Trout <trout@users.sf.net>
5 Changes 2004-2005 by Jonas Smedegaard <dr@jones.dk>
6 All rights reserved, see COPYING for details.
7 (Try contacting me through the Moin user/develop mailing list about this macro)
8
9 Macro allowing us to perform simple database functionality. The syntax
10 and behaviour is loosely based on SQL. This macro is designed for a few
11 little tasks which suit my needs and I will expand its functionality
12 as I need. It should be pretty easy and straightforward for anyone to
13 add functions to DbFunctions.
14
15 To do:
16 * I18N & _()
17 * Better error checking and reporting.
18 * Data insertion - need to sort out forms.
19 * Sorting.
20 * Caching table reads for faster access.
21
22 Remark of ThomasWaldmann:
23 In general it is a good idea to use UTC (aka GMT) as internal time representation and
24 convert to localtime when doing user interface output. This of course needs knowledge
25 about the time zone of the current user. But that avoids a lot of trouble in an international
26 context and also off-by-1hour errors when dealing with daylight-saving time.
27
28 Revisions:
29 * 12-Mar-03 - First version released.
30 * 16-Oct-04 - Add newline at end of warnings.
31 Disable wdbtime import (needed for Windows only, it seems - enable there!).
32 * 26-Aug-05 - hack to (seemingly) work with MoinMoin 1.3.x (pass request to DbTable class).
33 """
34
35 import re, string, time
36 from MoinMoin.Page import Page
37 from MoinMoin import wikiutil
38 from MoinMoin.parser import wiki
39
40
41
42
43
44
45
46
47
48
49 re_dollar = re.compile('\$\(([\w:]+)\)')
50
51 re_colinfo = re.compile('(\w+)')
52
53
54 class DbTable:
55 """ This class represents a database table.
56 Tables are read from wiki pages
57 """
58 def __init__(self, request):
59 self.request = request
60 self.rows = []
61 self.colorder = None
62
63 def _getColumnInfo(self, line):
64 """ Given the first line of a wiki table, pull out the column
65 name information. Note: Column names are all one word with no
66 whitespace: eg. "name", "due_date", "day_of_week"
67 """
68 colraw = line[2:-2].split('||')
69 self.colorder = []
70 for col in colraw:
71 srch = re_colinfo.search(col)
72 if not srch:
73 name = 'BAD_NAME'
74 else:
75 name = srch.group(1)
76 self.colorder.append(name)
77
78 def readTable(self, wikipage):
79 """ Really simple wiki table reader.
80 Note: Tables should not be indented.
81 """
82
83 page = Page(self.request, wikipage)
84
85
86 intable = False
87 for line in page.get_raw_body().split('\n'):
88 if not intable:
89 if line.find('||')==0:
90
91 self._getColumnInfo(line)
92 intable = True
93 else:
94 if line.find('||')<0:
95 break
96
97 colvals = line[2:-2].split('||')
98 row = {}
99 for col in range(len(colvals)):
100 row[self.colorder[col]] = colvals[col].strip()
101 self.rows.append(row)
102
103
104 class DbTime:
105 """ This class represents an instant in time.
106 setTime and setAsCurrentTime can be used to set the time.
107
108 TODO: self.seconds == what? UTC?
109 Important: mktime() := inverse func of localtime()
110 NOT: inverse func of gmtime() !!!
111 """
112 WORKING_DAY_END = ' 18:00'
113
114 def __init__(self):
115 self.timestruct = None
116 self.seconds = None
117
118 def setTime(self, str, format='%d-%b-%y %H:%M'):
119 """ Set the time from a formatted string. The format is optional
120 and defaults to day-month-year hours:mins eg. 25-dec-04 18:00
121 """
122 self.timestruct = wdbtime.strptime(str, format)
123 self.seconds = time.mktime(self.timestruct)
124
125 def setAsCurrentTime(self):
126 """ Insert the current time! """
127 self.timestruct = time.gmtime()
128 self.seconds = time.mktime(self.timestruct)
129
130 def setAsWorkingDayEnd(self, str, format='%d-%b-%y'):
131 """ Quite often when doing date differences, the end of the working day
132 is a good point to draw the line in a day count.
133 """
134 self.setTime(str+self.WORKING_DAY_END, format+' %H:%M')
135
136 def getDayOfWeek(self):
137 """ Get day of week the time is on. """
138 return time.strftime('%a', self.timestruct)
139
140
141 class DbPeriod:
142 """ This class is used to represent and query a period of time.
143 """
144 def __init__(self, fromtime, totime):
145 self.fromtime, self.totime = fromtime, totime
146
147 def getDaysBetween(self):
148 """ Calculate the number of days between two given dates. """
149 diff = int(self.totime.seconds - self.fromtime.seconds)
150 return diff/(60*60*24)+1
151
152 def getWorkingDaysBetween(self):
153 """ Calculate the number of working days between two given dates. """
154 nowsecs = self.fromtime.seconds
155 days = 0
156 while nowsecs < self.totime.seconds:
157 timestruct = time.gmtime(nowsecs)
158 day = time.strftime('%a', timestruct)
159 if not day in ('Sat','Sun'):
160 days += 1
161 nowsecs += 60*60*24
162 return days
163
164
165 class DbFunctions:
166 """ These are functions called by the queries and formatter.
167
168 The fn_<name> are the functions called using $(<name>:<args)
169
170 Each function receives row which is a dictionary of the row values in
171 the form { <column name> : <value> ... } and args, which is a
172 tuple of the args passed (shown above).
173
174 We try and cache objects we create to help performance. We don't want
175 to create and release objects every time we call a function when we
176 can reuse them for the duration of the entire query. eg.:
177 if not 'today' in dir(self): self.today = DbTime()
178 eg. see fn_days_until() etc. for an example.
179 """
180 def __init__(self):
181 pass
182
183 def fn_day_of_week(self, row, args):
184 """ Return the day of the week that a columns date falls on. """
185 if not 'day' in dir(self):
186 self.day = DbTime()
187 duedate = row[args[0]]
188 self.day.setTime(duedate, format='%d-%b-%y')
189 return self.day.getDayOfWeek()
190
191 def fn_days_until(self, row, args):
192 """ Return the number of days until a given date supplied in a column. """
193 duedate = row[args[0]]
194 if not 'period' in dir(self):
195 self.period = DbPeriod(DbTime(), DbTime())
196 self.period.fromtime.setAsCurrentTime()
197 self.period.totime.setAsWorkingDayEnd(duedate, format='%d-%b-%y')
198 return self.period.getDaysBetween()
199
200 def fn_working_days_until(self, row, args):
201 """ Return the number of working days until a given date supplied in a column. """
202 duedate = row[args[0]]
203 if not 'period' in dir(self):
204 self.period = DbPeriod(DbTime(), DbTime())
205 self.period.fromtime.setAsCurrentTime()
206 self.period.totime.setAsWorkingDayEnd(duedate, format='%d-%b-%y')
207 return self.period.getWorkingDaysBetween()
208
209 def _substColumn(self, row, argkey):
210 """ just return a columns value in a given row
211 Note: this is a private function, not for user
212 """
213 return row[argkey]
214
215
216 class DbQuery:
217 """ This class takes care of data retrieval, searching and formatting.
218 We use the "execute" function to execute a query string. The following
219 is an example query which lists information filtered from the "ListOfJobs"
220 wiki page table.
221
222 select || $(name) || $(due_date) || $(day_of_week:due_date) || $(job_desc) ||
223 titles || '''Programmer''' || '''Due''' || '''Day''' || '''Job''' ||
224 from ListOfJobs
225 where "$(name)"=="$(1)"
226 """
227 def __init__(self, request):
228 self.request = request
229 self.table = None
230 self.result = []
231 self.debug = False
232 self.dbgmsgs = []
233 self.fns = DbFunctions()
234
235 def _dbg(self, msg):
236 """ If debugging is turned on remember the debug message passed. """
237 if self.debug:
238 self.dbgmsgs.append(msg + '\n')
239
240 def debugMessages(self):
241 """ Return a wiki formatted string of all the debug messages. """
242 return '{{{' + string.join(self.dbgmsgs, ' ') + '}}}\n'
243
244 def readTable(self, wikipage):
245 """ Read a database table from a given wikipage. """
246 self.table = DbTable(self.request)
247 self.table.readTable(wikipage)
248 return True
249
250 def _makeSubstDict(self, fmtstr, varargs):
251 """ Find all the values that will need substituting and make a dictionary
252 which maps a key name to a function that will supply the replacement
253 value.
254 * Where there are arguments, eg. $(1), these are substituted immediately
255 as they don't change per row.
256 * Where we have column values, eg. $(name), we can use a simple function
257 to retrieve a value for them. ie. _substColumn.
258 * For functions we record the arguments passed and map this to a local
259 function with prototype: fn_<function>(self, row, <args>)
260 """
261
262 functions = vars(self.fns.__class__)
263
264
265 substdict = {}
266
267 while 1:
268 srch = re_dollar.search(fmtstr)
269 if not srch:
270 break
271
272 substkey = srch.group(1).lower()
273 colon = substkey.find(':')
274
275 if colon>0:
276
277 func, args = substkey[:colon], substkey[colon+1:]
278
279 if functions.has_key('fn_' + func):
280 replstr = '%%(%s)s' % substkey
281 replfunc = functions['fn_' + func]
282 replargs = args.split(',')
283 else:
284 replstr= "'''Bad function: %s'''" % func
285 replfunc, replargs = None, None
286
287 elif substkey in self.table.colorder:
288
289
290 replstr = '%%(%s)s' % substkey
291 replfunc = functions['_substColumn']
292 replargs = substkey
293
294 elif substkey in ('1','2','3','4','5','6'):
295
296
297 if varargs:
298 argn = int(substkey)-1
299 if argn<0 or argn>=len(varargs):
300 replstr, replfunc, replargs = 'BAD_ARG', None, None
301 else:
302 replstr, replfunc, replargs = varargs[argn], None, None
303 else:
304 replstr, replfunc, replargs = 'NO_ARGS', None, None
305
306 else:
307
308 replstr, replfunc, replargs = 'BAD_ARGUMENT', None, None
309
310
311 fmtstr = fmtstr.replace('$(%s)' % substkey, replstr)
312
313
314
315 if replfunc:
316 substdict[substkey] = (replfunc, replargs)
317
318 return fmtstr, substdict
319
320 def where(self, test, varargs):
321 if not self.table:
322 self.result.append("'''No table loaded.'''[[BR]]")
323 return []
324
325
326 formattedtest, substdict = self._makeSubstDict(test, varargs)
327 self._dbg("(where %s)" % formattedtest)
328 self._dbg(str(substdict))
329
330 selected = []
331 glob, loc = {}, {}
332
333
334 substvals = {}
335 for row in self.table.rows:
336
337 for k in substdict.keys():
338 substvals[k] = substdict[k][0](self.fns, row, substdict[k][1])
339
340
341 rowtest = formattedtest % substvals
342 self._dbg(rowtest)
343
344
345 try:
346 rowresult = eval(rowtest, glob, loc)
347 except:
348 self.result.append("'''Bad Test: %s'''[[BR]]" % rowtest)
349 rowresult = False
350 if rowresult:
351 selected.append(row)
352
353 return selected
354
355 def format(self, format, rows, varargs):
356
357
358 formrow, substdict = self._makeSubstDict(format, varargs)
359 self._dbg("(select %s)" % formrow)
360 self._dbg(str(substdict))
361
362
363 substvals = {}
364 for row in rows:
365
366
367 for k in substdict.keys():
368 substvals[k] = substdict[k][0](self.fns, row, substdict[k][1])
369
370
371 self.result.append((formrow % substvals) + '\n')
372
373 def execute(self, querytext, varargs):
374 self._dbg("Query:\n%s" % querytext)
375 format = ''
376 titles = ''
377 selected = []
378 for line in querytext.split('\n'):
379 words = line.strip().split(' ')
380 if words[0]=='':
381 continue
382 if words[0]=='titles':
383 titles += string.join(words[1:], ' ')
384 elif words[0]=='select':
385 format += string.join(words[1:], ' ')
386 elif words[0]=='from':
387 self.readTable(words[1])
388 elif words[0]=='where':
389 selected += self.where(string.join(words[1:], ' '),varargs)
390 else:
391 self.result.append("'''Bad Command: %s'''[[BR]]" % words[0])
392 if len(selected)>0:
393 if titles:
394 self.result.append(titles + '\n')
395 if format:
396 self.format(format, selected, varargs)
397 else:
398 self.result.append("'''No results to this query.'''[[BR]]")
399 return self.result
400
401
402 def execute(macro, args):
403 request = macro.request
404
405 cmds = args.split(',')
406 if len(cmds)<2:
407 return "<b>No info</b>\n"
408 wikipage = cmds[0]
409 queryname = cmds[1]
410 if len(cmds)>2:
411 varargs = cmds[2:]
412 else:
413 varargs = None
414 page = Page(request, wikipage)
415
416 srch = re.compile('=+\s*%s\s+=+\s+{{{([^{]*)}}}' % queryname)
417 srch = srch.search(page.get_raw_body())
418 if not srch:
419 return "<b>Query not found</b>\n"
420 info = srch.group(1)
421 query = DbQuery(request)
422
423 result = string.join(query.execute(info, varargs), '')
424 if query.debug:
425 result = query.debugMessages() + result
426
427 parser = wiki.Parser(result, macro.request)
428 parser.format(macro.formatter)
429 return ''
