Mercurial > hg > ZDBInterface
annotate ZDBInterfaceFolder.py @ 9:87087f4f4059
fixed bug with default sort order.
author | root@xserve09.mpiwg-berlin.mpg.de |
---|---|
date | Thu, 17 Feb 2011 20:18:18 +0100 |
parents | 17b19345d011 |
children | 283c01ebe96e |
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 | |
9
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
29 def __init__(self, id, title, connection_id=None, autocommit=False, default_search='bw'): |
3 | 30 """init""" |
31 self.id = id | |
32 self.title = title | |
33 # database connection id | |
34 self.connection_id = connection_id | |
9
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
35 # set db connection to autocommit |
8 | 36 self.autocommit = autocommit |
9
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
37 # default text search mode |
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
38 self.default_search = default_search |
3 | 39 |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
40 def getSQLQuery(self, table, argv): |
4 | 41 """returns dict with SQL query string and args""" |
42 logging.debug("getSQLquery table=%s argv=%s"%(table,argv)) | |
43 args = [] | |
44 select = "*" | |
45 order = None | |
46 wheres = [] | |
47 whereOp = "AND" | |
8 | 48 processed = {} |
49 unprocessed = {} | |
7 | 50 limit = None |
51 offset = None | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
52 |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
53 def doOp(op, param, val): |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
54 # handles comparison operations in WHERE clause |
7 | 55 logging.debug("doop op=%s param=%s val=%s"%(op,param,val)) |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
56 if isinstance(val, list): |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
57 # 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
|
58 val = " ".join(val) |
7 | 59 |
60 # string comparisons are case-insensitive | |
61 if isinstance(val, basestring): | |
62 param = "LOWER(%s)"%param | |
63 val = val.lower() | |
64 | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
65 if op == "eq": |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
66 wheres.append(param + " = %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 == "lt": |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
69 wheres.append(param + " < %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 == "gt": |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
72 wheres.append(param + " > %s") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
73 args.append(val) |
9
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
74 elif op == "ew": |
7 | 75 wheres.append(param + " ILIKE %s") |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
76 args.append("%" + val) |
9
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
77 elif op == "bw": |
7 | 78 wheres.append(param + " ILIKE %s") |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
79 args.append(val + "%") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
80 elif op == "ct": |
7 | 81 wheres.append(param + " ILIKE %s") |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
82 args.append("%" + val + "%") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
83 elif op == "all": |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
84 words = [] |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
85 for word in val.split(" "): |
7 | 86 words.append(param + " ILIKE %s") |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
87 args.append("%" + word + "%") |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
88 wheres.append("(" + " AND ".join(words) + ")") |
7 | 89 elif op == "one": |
90 words = [] | |
91 for word in val.split(" "): | |
92 words.append(param + " ILIKE %s") | |
93 args.append("%" + word + "%") | |
94 wheres.append("(" + " OR ".join(words) + ")") | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
95 else: |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
96 logging.error("getSQLquery: unknown op=%s!"%op) |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
97 |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
98 return |
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
99 |
4 | 100 # evaluate argv |
101 for (key, val) in argv.items(): | |
9
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
102 logging.debug("process key=%s val=%s"%(key,val)) |
8 | 103 if key in processed: |
4 | 104 # parameter has been processed |
9
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
105 logging.debug(" key=%s processed"%(key)) |
4 | 106 continue |
107 | |
108 # beginning of a command should always be "_" | |
109 if key[0] == "-": | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
110 key = "_" + key[1:] |
4 | 111 |
112 if key == "_select": | |
113 # SELECT expression | |
114 select = sqlName(val, more="*,") | |
115 elif key == "_sort": | |
116 # sort i.e. ORDER BY expression | |
117 order = sqlName(val, more=",") | |
118 elif key == "_lop": | |
119 # logical operation joining WHERE clauses | |
120 whereOp = sqlName(val) | |
7 | 121 elif key == "max": |
122 # number of results | |
123 limit = sqlName(val) | |
124 elif key == "skip": | |
125 # start at result number | |
126 offset = sqlName(val) | |
4 | 127 elif key[:3] == "_op": |
128 # operation parameters _op_param=option | |
129 param = sqlName(key[4:]) | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
130 logging.debug("param=%s key=%s val=%s"%(param,key,val)) |
4 | 131 if param in argv: |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
132 doOp(val, param, argv[param]) |
8 | 133 processed[param] = True |
4 | 134 else: |
135 # no corresponding parameter | |
136 logging.error("getSQLquery: param=%s for op not found!"%param) | |
8 | 137 else: |
138 # parameter=value pair | |
139 unprocessed[key] = val | |
4 | 140 |
141 # process remaining parameters (without _op) | |
8 | 142 for (key, val) in unprocessed.items(): |
9
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
143 if key not in processed: |
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
144 param = sqlName(key) |
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
145 # default operation |
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
146 doOp(self.default_search, param, val) |
4 | 147 |
148 # join it all | |
149 query = "SELECT %s FROM %s"%(select, table) | |
150 if wheres: | |
151 query += " WHERE " + (" " + whereOp + " ").join(wheres) | |
152 | |
153 if order: | |
154 query += " ORDER BY " + order | |
7 | 155 |
156 if limit: | |
157 query += " LIMIT " + limit | |
158 | |
159 if offset: | |
160 query += " OFFSET " + offset | |
4 | 161 |
162 return {'query' : query, 'args' : args} | |
163 | |
164 def ZDBInlineSearch(self, _table=None, **argv): | |
165 """returns result set from search""" | |
5
ca30cf0e810d
fix bugs in ZDBInlineSearch.
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
166 query = self.getSQLQuery(_table, argv) |
4 | 167 result = self.executeZSQL(query['query'], query['args']) |
168 return result | |
3 | 169 |
9
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
170 def manage_editZDBInterfaceFolder(self, title=None, connection_id=None, autocommit=None, default_search=None, |
3 | 171 REQUEST=None): |
172 """Change the object""" | |
9
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
173 logging.debug("editZDBInterfaceFolder title=%s, connection_id=%s, autocommit=%s default_search=%s"%(title,connection_id,autocommit,default_search)) |
3 | 174 if title is not None: |
175 self.title = title | |
176 | |
177 if connection_id is not None: | |
178 self.connection_id = connection_id | |
179 | |
9
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
180 if default_search is not None: |
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
181 self.default_search = default_search |
87087f4f4059
fixed bug with default sort order.
root@xserve09.mpiwg-berlin.mpg.de
parents:
8
diff
changeset
|
182 |
8 | 183 self.autocommit = (autocommit == "on") |
184 | |
3 | 185 #checkPermission=getSecurityManager().checkPermission |
186 REQUEST.RESPONSE.redirect('manage_main') | |
187 | |
188 | |
189 manage_addZDBInterfaceFolderForm=PageTemplateFile('zpt/addZDBInterfaceFolder',globals()) | |
190 | |
191 def manage_addZDBInterfaceFolder(self, id, title='', label='', description='', | |
192 createPublic=0, | |
193 createUserF=0, | |
194 REQUEST=None): | |
195 """Add a new object with id *id*.""" | |
196 | |
197 ob=ZDBInterfaceFolder(str(id),title) | |
198 self._setObject(id, ob) | |
199 | |
200 #checkPermission=getSecurityManager().checkPermission | |
201 REQUEST.RESPONSE.redirect('manage_main') | |
202 | |
203 |