Mercurial > hg > ZDBInterface
annotate ZDBInterfaceFolder.py @ 5:ca30cf0e810d
fix bugs in ZDBInlineSearch.
author | root@xserve09.mpiwg-berlin.mpg.de |
---|---|
date | Tue, 15 Feb 2011 20:39:48 +0100 |
parents | 0ade331198de |
children | 917e28a08c58 |
rev | line source |
---|---|
3 | 1 ''' |
2 Created on 14.2.2011 | |
3 | |
4 @author: casties | |
5 ''' | |
6 | |
7 from OFS.Folder import Folder | |
8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile | |
9 from AccessControl import getSecurityManager, Unauthorized | |
10 import logging | |
11 import re | |
12 | |
13 from DBInterface import * | |
14 | |
15 | |
16 class ZDBInterfaceFolder(DBInterface, Folder): | |
17 """Folder for database queries | |
18 """ | |
19 | |
20 meta_type="ZDBInterfaceFolder" | |
21 manage_options=Folder.manage_options+( | |
22 {'label':'Config','action':'manage_editZDBInterfaceFolderForm'}, | |
23 ) | |
24 | |
25 # management templates | |
26 manage_editZDBInterfaceFolderForm=PageTemplateFile('zpt/editZDBInterfaceFolder',globals()) | |
27 | |
28 | |
29 def __init__(self, id, title, connection_id=None): | |
30 """init""" | |
31 self.id = id | |
32 self.title = title | |
33 # database connection id | |
34 self.connection_id = connection_id | |
35 | |
36 | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
37 def getSQLQuery(self, table, argv): |
4 | 38 """returns dict with SQL query string and args""" |
39 logging.debug("getSQLquery table=%s argv=%s"%(table,argv)) | |
40 args = [] | |
41 select = "*" | |
42 order = None | |
43 wheres = [] | |
44 whereOp = "AND" | |
45 done = {} | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
46 |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
47 def doOp(op, param, val): |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
48 # handles comparison operations in WHERE clause |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
49 if isinstance(val, list): |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
50 # join multiple parameters with spaces (makes sense with checkbox and -op=all) |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
51 val = " ".join(val) |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
52 logging.debug("doop op=%s param=%s val=%s"%(op,param,val)) |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
53 if op == "eq": |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
54 wheres.append(param + " = %s") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
55 args.append(val) |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
56 elif op == "lt": |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
57 wheres.append(param + " < %s") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
58 args.append(val) |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
59 elif op == "gt": |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
60 wheres.append(param + " > %s") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
61 args.append(val) |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
62 elif op == "bw": |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
63 wheres.append(param + " LIKE %s") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
64 args.append("%" + val) |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
65 elif op == "ew": |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
66 wheres.append(param + " LIKE %s") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
67 args.append(val + "%") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
68 elif op == "ct": |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
69 wheres.append(param + " LIKE %s") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
70 args.append("%" + val + "%") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
71 elif op == "all": |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
72 words = [] |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
73 for word in val.split(" "): |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
74 words.append(param + " LIKE %s") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
75 args.append("%" + word + "%") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
76 wheres.append("(" + " AND ".join(words) + ")") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
77 else: |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
78 logging.error("getSQLquery: unknown op=%s!"%op) |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
79 |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
80 return |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
81 |
4 | 82 # evaluate argv |
83 for (key, val) in argv.items(): | |
84 if key in done: | |
85 # parameter has been processed | |
86 continue | |
87 | |
88 # beginning of a command should always be "_" | |
89 if key[0] == "-": | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
90 key = "_" + key[1:] |
4 | 91 |
92 if key == "_select": | |
93 # SELECT expression | |
94 select = sqlName(val, more="*,") | |
95 elif key == "_sort": | |
96 # sort i.e. ORDER BY expression | |
97 order = sqlName(val, more=",") | |
98 elif key == "_lop": | |
99 # logical operation joining WHERE clauses | |
100 whereOp = sqlName(val) | |
101 elif key[:3] == "_op": | |
102 # operation parameters _op_param=option | |
103 param = sqlName(key[4:]) | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
104 logging.debug("param=%s key=%s val=%s"%(param,key,val)) |
4 | 105 if param in argv: |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
106 doOp(val, param, argv[param]) |
4 | 107 done[param] = True |
108 else: | |
109 # no corresponding parameter | |
110 logging.error("getSQLquery: param=%s for op not found!"%param) | |
111 | |
112 # process remaining parameters (without _op) | |
113 for (key, val) in argv.items(): | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
114 if key not in done and key[0] not in ("_", "-"): |
4 | 115 param = sqlName(key) |
116 # default is begins-with | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
117 doOp("bw", param, val) |
4 | 118 |
119 # join it all | |
120 query = "SELECT %s FROM %s"%(select, table) | |
121 if wheres: | |
122 query += " WHERE " + (" " + whereOp + " ").join(wheres) | |
123 | |
124 if order: | |
125 query += " ORDER BY " + order | |
126 | |
127 return {'query' : query, 'args' : args} | |
128 | |
129 def ZDBInlineSearch(self, _table=None, **argv): | |
130 """returns result set from search""" | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
131 query = self.getSQLQuery(_table, argv) |
4 | 132 result = self.executeZSQL(query['query'], query['args']) |
133 return result | |
3 | 134 |
135 def manage_editZDBInterfaceFolder(self, title=None, connection_id=None, | |
136 REQUEST=None): | |
137 """Change the object""" | |
138 if title is not None: | |
139 self.title = title | |
140 | |
141 if connection_id is not None: | |
142 self.connection_id = connection_id | |
143 | |
144 #checkPermission=getSecurityManager().checkPermission | |
145 REQUEST.RESPONSE.redirect('manage_main') | |
146 | |
147 | |
148 manage_addZDBInterfaceFolderForm=PageTemplateFile('zpt/addZDBInterfaceFolder',globals()) | |
149 | |
150 def manage_addZDBInterfaceFolder(self, id, title='', label='', description='', | |
151 createPublic=0, | |
152 createUserF=0, | |
153 REQUEST=None): | |
154 """Add a new object with id *id*.""" | |
155 | |
156 ob=ZDBInterfaceFolder(str(id),title) | |
157 self._setObject(id, ob) | |
158 | |
159 #checkPermission=getSecurityManager().checkPermission | |
160 REQUEST.RESPONSE.redirect('manage_main') | |
161 | |
162 |