|
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
|
|
4
|
37 def getSQLQuery(self, table=None, argv):
|
|
|
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 = {}
|
|
|
46 # evaluate argv
|
|
|
47 for (key, val) in argv.items():
|
|
|
48 if key in done:
|
|
|
49 # parameter has been processed
|
|
|
50 continue
|
|
|
51
|
|
|
52 if isinstance(val, list):
|
|
|
53 # join multiple parameters with spaces (makes sense with checkbox and -op=all)
|
|
|
54 val = " ".join(val)
|
|
|
55
|
|
|
56 # beginning of a command should always be "_"
|
|
|
57 if key[0] == "-":
|
|
|
58 key[0] = "_"
|
|
|
59
|
|
|
60 if key == "_select":
|
|
|
61 # SELECT expression
|
|
|
62 select = sqlName(val, more="*,")
|
|
|
63 elif key == "_sort":
|
|
|
64 # sort i.e. ORDER BY expression
|
|
|
65 order = sqlName(val, more=",")
|
|
|
66 elif key == "_lop":
|
|
|
67 # logical operation joining WHERE clauses
|
|
|
68 whereOp = sqlName(val)
|
|
|
69 elif key[:3] == "_op":
|
|
|
70 # operation parameters _op_param=option
|
|
|
71 param = sqlName(key[4:])
|
|
|
72 if param in argv:
|
|
|
73 doOp(op, param)
|
|
|
74 done[param] = True
|
|
|
75 else:
|
|
|
76 # no corresponding parameter
|
|
|
77 logging.error("getSQLquery: param=%s for op not found!"%param)
|
|
|
78
|
|
|
79 # process remaining parameters (without _op)
|
|
|
80 for (key, val) in argv.items():
|
|
|
81 if key not in done:
|
|
|
82 param = sqlName(key)
|
|
|
83 # default is begins-with
|
|
|
84 doOp("bw", param)
|
|
|
85
|
|
|
86 # join it all
|
|
|
87 query = "SELECT %s FROM %s"%(select, table)
|
|
|
88 if wheres:
|
|
|
89 query += " WHERE " + (" " + whereOp + " ").join(wheres)
|
|
|
90
|
|
|
91 if order:
|
|
|
92 query += " ORDER BY " + order
|
|
|
93
|
|
|
94 return {'query' : query, 'args' : args}
|
|
|
95
|
|
|
96 def doOp(op, param):
|
|
|
97 # handles comparison operations in WHERE clause
|
|
|
98 if op == "eq":
|
|
|
99 wheres.append(param + " = %s")
|
|
|
100 args.append(argv[param])
|
|
|
101 elif op == "lt":
|
|
|
102 wheres.append(param + " < %s")
|
|
|
103 args.append(argv[param])
|
|
|
104 elif op == "gt":
|
|
|
105 wheres.append(param + " > %s")
|
|
|
106 args.append(argv[param])
|
|
|
107 elif op == "bw":
|
|
|
108 wheres.append(param + " LIKE %s")
|
|
|
109 args.append("%" + argv[param])
|
|
|
110 elif op == "ew":
|
|
|
111 wheres.append(param + " LIKE %s")
|
|
|
112 args.append(argv[param] + "%")
|
|
|
113 elif op == "ct":
|
|
|
114 wheres.append(param + " LIKE %s")
|
|
|
115 args.append("%" + argv[param] + "%")
|
|
|
116 else:
|
|
|
117 logging.error("getSQLquery: unknown op=%s!"%op)
|
|
|
118
|
|
|
119 return
|
|
|
120
|
|
|
121
|
|
|
122 def ZDBInlineSearch(self, _table=None, **argv):
|
|
|
123 """returns result set from search"""
|
|
|
124 query = self.getSQLQuery(_table=_table, argv)
|
|
|
125 result = self.executeZSQL(query['query'], query['args'])
|
|
|
126 return result
|
|
3
|
127
|
|
|
128 def manage_editZDBInterfaceFolder(self, title=None, connection_id=None,
|
|
|
129 REQUEST=None):
|
|
|
130 """Change the object"""
|
|
|
131 if title is not None:
|
|
|
132 self.title = title
|
|
|
133
|
|
|
134 if connection_id is not None:
|
|
|
135 self.connection_id = connection_id
|
|
|
136
|
|
|
137 #checkPermission=getSecurityManager().checkPermission
|
|
|
138 REQUEST.RESPONSE.redirect('manage_main')
|
|
|
139
|
|
|
140
|
|
|
141 manage_addZDBInterfaceFolderForm=PageTemplateFile('zpt/addZDBInterfaceFolder',globals())
|
|
|
142
|
|
|
143 def manage_addZDBInterfaceFolder(self, id, title='', label='', description='',
|
|
|
144 createPublic=0,
|
|
|
145 createUserF=0,
|
|
|
146 REQUEST=None):
|
|
|
147 """Add a new object with id *id*."""
|
|
|
148
|
|
|
149 ob=ZDBInterfaceFolder(str(id),title)
|
|
|
150 self._setObject(id, ob)
|
|
|
151
|
|
|
152 #checkPermission=getSecurityManager().checkPermission
|
|
|
153 REQUEST.RESPONSE.redirect('manage_main')
|
|
|
154
|
|
|
155
|
