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