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