Annotation of ZSQLExtend/ZSQLExtend.py, revision 1.7
1.1 dwinter 1: from OFS.Folder import Folder
2: from Globals import Persistent
3: from Acquisition import Implicit
4: from Globals import DTMLFile
5: import urllib
6: import re
7: import string
1.3 dwinter 8: from pyPgSQL import libpq
1.1 dwinter 9: from AccessControl import getSecurityManager
1.5 dwinter 10: import os.path
1.1 dwinter 11:
1.2 dwinter 12: def quoteString(name):
13: #return re.sub(r'([\(\)\?])',"\\\1",name)
14: #return "Euklid"
15: return name
16:
1.1 dwinter 17: class ZSQLExtendFolder(Persistent, Implicit, Folder):
18: """Folder"""
19: meta_type="ZSQLExtendFolder"
20:
1.5 dwinter 21:
22: def lowerEnd(self,path):
23: """oinly for demo"""
24: return os.path.splitext(path)[0]+".jpg"
25:
26: def ZSQLisEmpty(self,field):
27: """Teste ob Treffer leer"""
1.7 ! dwinter 28: #print "field",field
1.5 dwinter 29: if not field:
30: return 1
31: if field=="":
32: return 1
33: return 0
34:
1.6 dwinter 35: def ZSQLsearchOptions(self,fieldname=""):
1.5 dwinter 36: """return HTML Fragment with search options"""
1.6 dwinter 37:
38: ret="""<select name="-op_%s">
1.5 dwinter 39: <option value="bw">begins with</option> <!-- begins with / beginnt mit, "Wort*" -->
40: <option value="ew">ends with</option>
1.7 ! dwinter 41: <option value="ct" selected>contains</option> <!-- contains / enthaellt, "Wort" -->
1.5 dwinter 42: <option value="eq">equals</option> <!-- equals / ist, =Wort -->
1.6 dwinter 43: </select>"""%fieldname
1.5 dwinter 44: return ret
45:
46: def ZSQLInlineSearch(self,storename=None,**argv):
1.1 dwinter 47: """inlinesearch"""
48: qs=[]
1.5 dwinter 49: if storename:
50: """store"""
51: else:
52: storename="foundCount"
53:
1.2 dwinter 54:
55:
1.5 dwinter 56: #print "INLINE:",argv
1.1 dwinter 57: for a in argv.keys():
58: qs.append(a+"="+urllib.quote(str(argv[a])))
1.5 dwinter 59: #return []
60: return self.parseQueryString(string.join(qs,","),"_",storename=storename)
1.1 dwinter 61:
1.5 dwinter 62: def ZSQLInlineSearch2(self,query):
63: """inlinesearch"""
64: qs=[]
65:
66:
67:
68: #print "INLINE:",query
69:
70: return self.search(var=query)
1.1 dwinter 71:
72: def ZSQLAdd(self):
73: """Neuer Eintrag"""
74: qs=self.REQUEST['QUERY_STRING']
75: addList={}
76: for q in qs.split("&"):
77: name=re.sub("r'+'"," ",q.split("=")[0].lower())
78: value=q.split("=")[1]
79: value=re.sub(r'\+'," ",value)
80: value=urllib.unquote(value)
81: if name=="-table":
82: table=urllib.unquote(value)
83: elif name=="-format":
84: format=urllib.unquote(value)
85: elif (not name[0]=="-") and (not len(value)==0):
86: addList[urllib.unquote(name)]=urllib.unquote(value)
87:
88: keyList=[]
89: valueList=[]
90: for x in addList.keys():
91: keyList.append("\""+x+"\"")
1.3 dwinter 92: valueList.append(libpq.PgQuoteString(addList[x]))
1.1 dwinter 93:
94: keyString=string.join(keyList,",")
95: valueString=string.join(valueList,",")
96:
97: queryString="INSERT INTO %s (%s) VALUES (%s)"%(table,keyString,valueString)
98: self.search(var=queryString)
99: return self.REQUEST.RESPONSE.redirect(format)
100:
1.4 dwinter 101: def ZSQLChange(self,**argv):
102: """Ändern von Einträgen"""
103: #qs=self.REQUEST['QUERY_STRING']
104: # very bad hack
105: qs_temp=[]
106:
107: for a in self.REQUEST.form.keys():
108: qs_temp.append(a+"="+urllib.quote(str(self.REQUEST.form[a])))
109:
110: qs=string.join(qs_temp,"&")
111:
112:
113: #print "CHANGE QS",self.REQUEST
114: #return self.REQUEST
115: changeList=[]
116: for q in qs.split("&"):
117: name=urllib.unquote(re.sub("r'+'"," ",q.split("=")[0].lower()))
118: value=q.split("=")[1]
119: value=re.sub(r'\+'," ",value)
120: value=urllib.unquote(value)
121: if name=="-table":
122: table=urllib.unquote(value)
123: elif name=="-identify":
124: identify=urllib.unquote(value)
125: identify=identify.split("=")[0]+"="+libpq.PgQuoteString(identify.split("=")[1])
126: elif name=="-format":
127: format=urllib.unquote(value)
128: elif (not name[0]=="-") and (not len(value)==0):
129: changeList.append("\""+name+"\"="+libpq.PgQuoteString(urllib.unquote(value)))
130: changeString=string.join(changeList,",")
131: queryString="UPDATE %s SET %s WHERE %s"%(table,changeString,identify)
132: self.search(var=queryString)
133: return self.REQUEST.RESPONSE.redirect(format)
134:
135: def ZSQLChange_old(self):
1.1 dwinter 136: """Ändern von Einträgen"""
137: qs=self.REQUEST['QUERY_STRING']
138: #print "CHANGE QS",self.REQUEST
139: #return self.REQUEST
140: changeList=[]
141: for q in qs.split("&"):
142: name=urllib.unquote(re.sub("r'+'"," ",q.split("=")[0].lower()))
143: value=q.split("=")[1]
144: value=re.sub(r'\+'," ",value)
145: value=urllib.unquote(value)
146: if name=="-table":
147: table=urllib.unquote(value)
148: elif name=="-identify":
149: identify=urllib.unquote(value)
1.3 dwinter 150: identify=identify.split("=")[0]+"="+libpq.PgQuoteString(identify.split("=")[1])
1.1 dwinter 151: elif name=="-format":
152: format=urllib.unquote(value)
153: elif (not name[0]=="-") and (not len(value)==0):
1.3 dwinter 154: changeList.append("\""+name+"\"="+libpq.PgQuoteString(urllib.unquote(value)))
1.1 dwinter 155: changeString=string.join(changeList,",")
156: queryString="UPDATE %s SET %s WHERE %s"%(table,changeString,identify)
157: self.search(var=queryString)
158: return self.REQUEST.RESPONSE.redirect(format)
159:
1.5 dwinter 160: def ZSQLFind(self,qs="",select="*",storename=None):
1.1 dwinter 161: """Find"""
162:
163:
164: if qs=="":
165: if self.REQUEST['QUERY_STRING']:
166: qs=self.REQUEST['QUERY_STRING']
1.5 dwinter 167:
168:
1.1 dwinter 169: qs=string.join(qs.split("&"),",")
170: else:
1.5 dwinter 171:
1.1 dwinter 172: qs=self.REQUEST.SESSION['query']
173: else:
174: qs=string.join(qs.split("&"),",")
1.6 dwinter 175:
1.1 dwinter 176:
1.5 dwinter 177: qs=re.sub("\\+"," ",qs)# Austauschen da Leerzeichen bei http-get durch + ersetzt wird, generell sollte alles auf post umgeschrieben werden. vom search formular.
178: #print "QS",qs
179: if storename:
180: """store"""
181: else:
182: storename="foundCount"
183:
184: ret=self.parseQueryString(qs,"-",select=select,storemax="yes",storename=storename)
185: #print self.REQUEST.SESSION["foundCount"]
186:
187: return ret
188:
189: def ZSQLFoundCountLen(self,var):
190: return len(var)
191:
192: def ZSQLFoundCount(self,qs="",select="*",storename=None):
193:
194: ## if qs=="":
195:
196: ## if self.REQUEST['QUERY_STRING']:
197:
198: ## qs=self.REQUEST['QUERY_STRING']
199: ## qs=string.join(qs.split("&"),",")
200: ## else:
201:
202: ## qs=self.REQUEST.SESSION['query']
203: ## else:
204: ## qs=string.join(qs.split("&"),",")
205:
206:
207: ## temp= self.parseQueryString(qs,"-",select=select,storemax="yes",nostore="yes")
208: if storename:
209: """store"""
210: else:
211: storename="foundCount"
212:
213: return self.REQUEST.SESSION[storename]['count']
214:
215: def ZSQLRangeStart(self,storename=None):
216:
217: if storename:
218: """store"""
219: else:
220: storename="foundCount"
221:
222: return self.REQUEST.SESSION[storename]['rangeStart']
223:
224: def ZSQLRangeSize(self,storename=None):
225:
226: if storename:
227: """store"""
228: else:
229: storename="foundCount"
230:
231: return self.REQUEST.SESSION[storename]['rangeSize']
1.1 dwinter 232:
1.5 dwinter 233: def ZSQLRangeEnd(self,storename=None):
234:
235: if storename:
236: """store"""
237: else:
238: storename="foundCount"
239:
240: return self.REQUEST.SESSION[storename]['rangeEnd']
241:
242: def parseQueryString(self,qs,iCT,storemax="no",select=None,nostore=None,storename=None):
243: """analysieren den QueryString"""
244: #print "NO",nostore
1.1 dwinter 245: lop="AND" # standardsuche mit and
246: max="ALL" #standard alle auswählen
247: whereList=[]
248: sort=""
249: op="bw"
1.2 dwinter 250: opfields={}
1.5 dwinter 251: skip=""
252: rangeStart=0
1.2 dwinter 253:
1.1 dwinter 254: if not select:
255: select="*"
1.5 dwinter 256: #print "Q",nostore,qs
1.2 dwinter 257: #check for op in the case of inline search
1.6 dwinter 258:
259: splitted=qs.split(",")
260:
261:
262: for q in splitted:
1.5 dwinter 263:
1.2 dwinter 264: name=re.sub("r'+'"," ",q.split("=")[0].lower())
265: value=urllib.unquote(q.split("=")[1])
266:
267: if name[0:3]==iCT+"op":
268: op=value
269: field=name[4:]
270: opfields[field]=op
1.6 dwinter 271:
272: #print opfieldsa
1.2 dwinter 273: #now analyse the querystring
1.1 dwinter 274: for q in qs.split(","):
1.5 dwinter 275:
276:
277: #try:
1.1 dwinter 278:
1.5 dwinter 279: name=re.sub("r'+'"," ",q.split("=")[0].lower())
280: value=urllib.unquote(q.split("=")[1])
281: #value=libpq.PgQuoteString(value)
282:
283:
284: if name==iCT+"lop":
285: lop=value
286: elif name==iCT+"table":
287: table=value
288: elif name==iCT+"select":
289: select=value
290: elif name==iCT+"max":
291: max="LIMIT "+str(value)
292: limit=str(value)
293: elif name==iCT+"skip":
294: skip="OFFSET "+str(value)
295: rangeStart=str(value)
296: elif name==iCT+"join":
297: whereList.append(value)
298: elif name==iCT+"sort":
299: sort="ORDER BY "+value
300: elif name==iCT+"token":
301: if not nostore=="yes":
1.1 dwinter 302: self.REQUEST.SESSION['token']=value
303:
1.5 dwinter 304: elif name==iCT+"op":
305: op=value
306:
307:
1.1 dwinter 308:
1.5 dwinter 309: elif (not name[0]==iCT) and (not len(value)==0):
1.6 dwinter 310:
311: #print "OP",op,name
1.5 dwinter 312: value=value.lower()
313: if opfields.has_key(name):
314: op=opfields[name]
1.6 dwinter 315: else:
316: op="ct"
317: name="LOWER("+name+")"
1.5 dwinter 318: if op=="ct":
319: whereList.append(name+" LIKE "+libpq.PgQuoteString("%"+value+"%"))
320: elif op=="gt":
321: whereList.append(name+">"+libpq.PgQuoteString(value))
322: elif op=="lt":
323: whereList.append(name+"<"+libpq.PgQuoteString(value))
324: elif op=="eq":
325: whereList.append(name+"="+libpq.PgQuoteString(value))
326: elif op=="bw":
327: whereList.append(name+" LIKE "+libpq.PgQuoteString(value+"%"))
328: elif op=="ew":
329: whereList.append(name+" LIKE "+libpq.PgQuoteString("%"+value))
1.6 dwinter 330: op="ct"
1.5 dwinter 331:
332: #except:
333: # print "END",value,name,Exception
1.1 dwinter 334: if len(whereList)>0:
335: where="WHERE "+string.join(whereList," "+lop+" ")
336: else:
337: where=""
338: #print "QE",table
339:
1.5 dwinter 340: query="SELECT %s FROM %s %s %s %s %s"%(select,table,where,sort,max,skip)
341: if not nostore=="yes":
342:
343: self.REQUEST.SESSION['qs']=opfields
1.7 ! dwinter 344: #print "IAMHERE again:", query
1.5 dwinter 345:
346: if storename:
1.7 ! dwinter 347: query2="SELECT count(*) FROM %s %s"%(table,where)
1.5 dwinter 348: #print "storing",query2
349: #print "QUERYSTRING:",self.REQUEST.SESSION[storename]['queryString2']
350: if not self.REQUEST.SESSION.has_key(storename):
351: self.REQUEST.SESSION[storename]={}
352: if self.REQUEST.SESSION[storename].has_key('queryString2'):
353: #print "HI",storename
354: if not self.REQUEST.SESSION[storename]['queryString2']==query2:
355: #print "HOOOOO",storename
356: self.REQUEST.SESSION[storename]['queryString2']=query2
357: self.REQUEST.SESSION[storename]['count']=self.search(var=query2)[0].count
358: #print "QUERY",query2,"::::",self.REQUEST.SESSION[storename]['queryString2']
359:
360: else:
361: self.REQUEST.SESSION[storename]['queryString2']=query2
362: self.REQUEST.SESSION[storename]['count']=self.search(var=query2)[0].count
363: #print "QUERYNEW",self.REQUEST.SESSION[storename]['queryString2']
364:
365:
366: self.REQUEST.SESSION[storename]['rangeStart']=rangeStart
367: if limit=="all":
368: self.REQUEST.SESSION[storename]['rangeEnd']=self.REQUEST.SESSION[storename]['count']
369: else:
370: self.REQUEST.SESSION[storename]['rangeEnd']=int(rangeStart)+int(limit)
371: self.REQUEST.SESSION[storename]['rangeSize']=limit
372:
1.7 ! dwinter 373: #print query
1.1 dwinter 374: return self.search(var=query)
375:
376: def ZSQLSearch(self):
377: """To be done"""
378: rq=self.REQUEST['QUERY_STRING']
379: querys=rq.split("&")
380:
381:
382: for querytemp in querys:
383: query=querytemp.split("=")
384: try:
385: if query[0].lower()=="-format":
386: formatfile=query[1]
387: except:
388: """nothing"""
389: #print formatfile
390: self.REQUEST.SESSION['query']=string.join(self.REQUEST['QUERY_STRING'].split("&"),",")
391: return self.REQUEST.RESPONSE.redirect(urllib.unquote(formatfile))
392:
393:
394: def ZSQLint(self,string):
395: try:
396:
397: return(int(string))
398: except:
399: return 0
1.5 dwinter 400:
401: def nextLink(self,html,storename="foundCount"):
402: """nextLink"""
403: try:
404: limit=self.REQUEST.SESSION[storename]['rangeSize']
405: newRangeStart=int(self.REQUEST.SESSION[storename]['rangeStart'])+int(limit)
406: except:
407: limit=0
408: newRangeStart=0
409:
410: if newRangeStart>self.REQUEST.SESSION[storename]['count']:
411: newRangeStart=self.REQUEST.SESSION[storename]['count']-10
412:
413:
414: #create new query string
415:
416: if self.REQUEST['QUERY_STRING']=="":
417: qs=self.REQUEST.SESSION['query']
418:
419: queries=string.split(qs,",")
420:
421:
422: else:
423: qs=self.REQUEST['QUERY_STRING']
424: queries=string.split(qs,"&")
425:
426:
427:
428: newquery=[]
429:
430: skipFound=0
431:
432: for query in queries:
433: #print query.split("=")[0]
434: if query[0]=="_" : query[0]="-"
435:
436: if query.split("=")[0].lower()=="-skip":
437: skipFound=1
438: query="-skip=%i"%newRangeStart
439: newquery.append(query)
440:
441: if skipFound==0 :
442: query="-skip=%i"%newRangeStart
443: newquery.append(query)
444:
445: newquerystring=string.join(newquery,"&")
446: return "<a href='%s'>%s</a>"%(self.REQUEST['URL0']+"?"+newquerystring,html)
447:
448:
449: def prevLink(self,html,storename="foundCount"):
450: """prev link"""
451: try:
452: limit=self.REQUEST.SESSION[storename]['rangeSize']
453: newRangeStart=int(self.REQUEST.SESSION[storename]['rangeStart'])-int(limit)
454: except:
455: limit=0
456: newRangeStart=0
457:
458: #print "limit",limit,newRangeStart,int(self.REQUEST.SESSION[storename]['rangeStart'])
459:
460: if newRangeStart<0:
461: newRangeStart=0
462:
463: #create new query string
464:
465: if self.REQUEST['QUERY_STRING']=="":
466: qs=self.REQUEST.SESSION['query']
467: #qs=re.sub(r'_','-',qs) #aendern für query
468: queries=string.split(qs,",")
469:
470:
471: else:
472: qs=self.REQUEST['QUERY_STRING']
473: queries=string.split(qs,"&")
474:
475:
476:
477: newquery=[]
478:
479: skipFound=0
480:
481: for query in queries:
482: #print query.split("=")[0]
483:
484: if query[0]=="_" : query[0]="-"
485:
486: if query.split("=")[0].lower()=="-skip":
487: #print"HI"
488: query="-skip=%i"%newRangeStart
489: skipFound=1
490: newquery.append(query)
491:
492: if skipFound==0 :
493: query="-skip=%i"%newRangeStart
494: newquery.append(query)
495:
496: newquerystring=string.join(newquery,"&")
497: return "<a href='%s'>%s</a>"%(self.REQUEST['URL0']+"?"+newquerystring,html)
498:
499: def prevLink_old(self,html):
1.1 dwinter 500: """prev link"""
501: if self.REQUEST['QUERY_STRING']=="":
502: qs=self.REQUEST.SESSION['query']
503: else:
504: qs=self.REQUEST['QUERY_STRING']
505: max=re.search(r'max\=(.*)\,',qs.lower())
506: offset=re.search(r'offset\=(.*)\,',qs.lower())
507: if not offset:
508: offsetnew=0
509: else:
510: offsetnew=int(offset)-max
511: if offsetnew<0:
512: offsetnew=0
513: queries=string.split(qs,",")
514: newquery=[]
515: if offset:
516: for query in queries:
517: if query.split("=")[0].lower()=="offset":
518: query="-offset=%i"%offsetnew
519: newquery.append(query)
520: newquerystring=string.join(newquery,"&")
521: else:
522: queries.append("-offset=%i"%offsetnew)
523: newquerystring=string.join(queries,"&")
524: return "<a href='%s'>%s</a>"%(self.REQUEST['URL0']+"?"+newquerystring,html)
525:
1.5 dwinter 526: def nextLink_old(self,html):
1.1 dwinter 527: """prev link"""
528: if self.REQUEST['QUERY_STRING']=="":
529: qs=self.REQUEST.SESSION['query']
530: else:
531: qs=self.REQUEST['QUERY_STRING']
532: max=re.search(r'max\=(.*)\,',qs.lower())
533:
534: offset=re.search(r'offset\=(.*)\,',qs.lower())
535: if not offset:
536: offsetnew=1
537: else:
538: offsetnew=int(offset)+int(max)
539: if offsetnew<0:
540: offsetnew=0
541: queries=string.split(qs,",")
542: newquery=[]
543: if offset:
544: for query in queries:
545:
546: if query.split("=")[0].lower()=="-offset":
547: query="-offset=%i"%offsetnew
548: newquery.append(query)
549: newquerystring=string.join(newquery,"&")
550: else:
551: queries.append("-offset=%i"%offsetnew)
552: newquerystring=string.join(queries,"&")
553:
554: return "<a href='%s'>%s</a>"%(self.REQUEST['URL0']+"?"+newquerystring,html)
555:
1.5 dwinter 556:
1.1 dwinter 557: manage_addZSQLExtendFolderForm=DTMLFile('ZSQLExtendFolderAdd', globals())
558:
559: def manage_addZSQLExtendFolder(self, id, title='',
560: createPublic=0,
561: createUserF=0,
562: REQUEST=None):
563: """Add a new Folder object with id *id*.
564:
565: If the 'createPublic' and 'createUserF' parameters are set to any true
566: value, an 'index_html' and a 'UserFolder' objects are created respectively
567: in the new folder.
568: """
569:
570:
571: ob=ZSQLExtendFolder()
572: ob.id=str(id)
573: ob.title=title
574: self._setObject(id, ob)
575: ob=self._getOb(id)
576:
577: checkPermission=getSecurityManager().checkPermission
578:
579: if createUserF:
580: if not checkPermission('Add User Folders', ob):
581: raise Unauthorized, (
582: 'You are not authorized to add User Folders.'
583: )
584: ob.manage_addUserFolder()
585:
586: if createPublic:
587: if not checkPermission('Add Page Templates', ob):
588: raise Unauthorized, (
589: 'You are not authorized to add Page Templates.'
590: )
591: ob.manage_addProduct['PageTemplates'].manage_addPageTemplate(
592: id='index_html', title='')
593:
594: if REQUEST is not None:
595: return self.manage_main(self, REQUEST, update_menu=1)
596:
597:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>