Toggle line numbers
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 # The time module lacks strptime on certain platforms. This is being fixed
41 # in Python 2.3 apparently by a script. You will need to have the script
42 # in your Python path for now. It is called "wdbtime.py"
43 #try:
44 # import wdbtime # this is a fix for the lack of strptime on win32
45 #except ImportError:
46 # raise "You need the wdbtime/strptime fix" # until Python 2.3 that is
47
48 # $(<var name>) : "<given text>"
49 re_dollar = re.compile('\$\(([\w:]+)\)')
50 # strip out column name
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 = [] # list of row data
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 # load the page
83 page = Page(self.request, wikipage)
84
85 # iterate over page lines and pull out the table information
86 intable = False
87 for line in page.get_raw_body().split('\n'):
88 if not intable:
89 if line.find('||')==0:
90 # first line contains column names
91 self._getColumnInfo(line)
92 intable = True
93 else:
94 if line.find('||')<0:
95 break # end of table
96 # convert the row syntax into a dictionary
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' # time we end the working day
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]] # date is read from a column entry
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]] # date is read from a column entry
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]] # date is read from a column entry
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 # we're going to look in the DbFunctions class namespace for macro functions
262 functions = vars(self.fns.__class__)
263
264 # this will be { '<key>':func(query,rowdict) ... }
265 substdict = {}
266
267 while 1:
268 srch = re_dollar.search(fmtstr)
269 if not srch:
270 break # all done
271
272 substkey = srch.group(1).lower() # get the key $(<key>)
273 colon = substkey.find(':') # is this a function?
274
275 if colon>0:
276 # we found a function ie. $(<function>:<arguments>,...)
277 func, args = substkey[:colon], substkey[colon+1:]
278 # see if the function exists and call it if it does
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 # column data is formatted as it changes per row
289 # ie. $(col) -> %(col)s
290 replstr = '%%(%s)s' % substkey
291 replfunc = functions['_substColumn']
292 replargs = substkey
293
294 elif substkey in ('1','2','3','4','5','6'):
295 # if we have arguments their values don't change
296 # ie. $(argn) -> <arg value>
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 # not found!
308 replstr, replfunc, replargs = 'BAD_ARGUMENT', None, None
309
310 # replace the text in the query formatted string
311 fmtstr = fmtstr.replace('$(%s)' % substkey, replstr)
312
313 # record the function that will return us a value to subst in,
314 # if we need one that is.
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 # work out what values are going to be substituted in and how
326 formattedtest, substdict = self._makeSubstDict(test, varargs)
327 self._dbg("(where %s)" % formattedtest)
328 self._dbg(str(substdict))
329
330 selected = [] # our selected values
331 glob, loc = {}, {}
332
333 # iterate over the table and find our chosen set
334 substvals = {}
335 for row in self.table.rows:
336 # we need to update the subst values per row
337 for k in substdict.keys():
338 substvals[k] = substdict[k][0](self.fns, row, substdict[k][1])
339
340 # swap this rows values into the test
341 rowtest = formattedtest % substvals
342 self._dbg(rowtest)
343
344 # execute the test and see if this row passes the criteria
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 # return list of rows
354
355 def format(self, format, rows, varargs):
356 # format some table rows with the given format ie. select
357 # get the substitution format functions
358 formrow, substdict = self._makeSubstDict(format, varargs)
359 self._dbg("(select %s)" % formrow)
360 self._dbg(str(substdict))
361
362 # iterate over each of the rows given and format it
363 substvals = {} # our substitute values
364 for row in rows:
365
366 # we need to update the subst values every row
367 for k in substdict.keys():
368 substvals[k] = substdict[k][0](self.fns, row, substdict[k][1])
369
370 # use python formatted strings to swap in our values
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 # blank line
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 # == <queryname> == {{{ <query> }}}
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 # query.debug = True # add this to see debug information
423 result = string.join(query.execute(info, varargs), '')
424 if query.debug:
425 result = query.debugMessages() + result
426 # format the output using a wiki parser
427 parser = wiki.Parser(result, macro.request)
428 parser.format(macro.formatter)
429 return '' # we've already output the result using the parser