Annotation of ZSQLExtend/ZSQLExtend.py, revision 1.10
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
264: whereList=[]
265: sort=""
266: op="bw"
1.2 dwinter 267: opfields={}
1.5 dwinter 268: skip=""
269: rangeStart=0
1.2 dwinter 270:
1.1 dwinter 271: if not select:
1.10 ! dwinter 272: select="oid,*"
1.5 dwinter 273: #print "Q",nostore,qs
1.2 dwinter 274: #check for op in the case of inline search
1.6 dwinter 275:
276: splitted=qs.split(",")
277:
278:
279: for q in splitted:
1.5 dwinter 280:
1.2 dwinter 281: name=re.sub("r'+'"," ",q.split("=")[0].lower())
282: value=urllib.unquote(q.split("=")[1])
283:
284: if name[0:3]==iCT+"op":
285: op=value
286: field=name[4:]
287: opfields[field]=op
1.6 dwinter 288:
289: #print opfieldsa
1.2 dwinter 290: #now analyse the querystring
1.1 dwinter 291: for q in qs.split(","):
1.5 dwinter 292:
293:
294: #try:
1.1 dwinter 295:
1.5 dwinter 296: name=re.sub("r'+'"," ",q.split("=")[0].lower())
297: value=urllib.unquote(q.split("=")[1])
298: #value=libpq.PgQuoteString(value)
299:
300:
301: if name==iCT+"lop":
302: lop=value
303: elif name==iCT+"table":
304: table=value
305: elif name==iCT+"select":
306: select=value
307: elif name==iCT+"max":
308: max="LIMIT "+str(value)
309: limit=str(value)
310: elif name==iCT+"skip":
311: skip="OFFSET "+str(value)
312: rangeStart=str(value)
313: elif name==iCT+"join":
314: whereList.append(value)
315: elif name==iCT+"sort":
316: sort="ORDER BY "+value
317: elif name==iCT+"token":
318: if not nostore=="yes":
1.1 dwinter 319: self.REQUEST.SESSION['token']=value
320:
1.5 dwinter 321: elif name==iCT+"op":
322: op=value
323:
324:
1.1 dwinter 325:
1.5 dwinter 326: elif (not name[0]==iCT) and (not len(value)==0):
1.6 dwinter 327:
328: #print "OP",op,name
1.5 dwinter 329: value=value.lower()
330: if opfields.has_key(name):
331: op=opfields[name]
1.6 dwinter 332: else:
333: op="ct"
334: name="LOWER("+name+")"
1.5 dwinter 335: if op=="ct":
336: whereList.append(name+" LIKE "+libpq.PgQuoteString("%"+value+"%"))
337: elif op=="gt":
338: whereList.append(name+">"+libpq.PgQuoteString(value))
339: elif op=="lt":
340: whereList.append(name+"<"+libpq.PgQuoteString(value))
341: elif op=="eq":
342: whereList.append(name+"="+libpq.PgQuoteString(value))
343: elif op=="bw":
344: whereList.append(name+" LIKE "+libpq.PgQuoteString(value+"%"))
345: elif op=="ew":
346: whereList.append(name+" LIKE "+libpq.PgQuoteString("%"+value))
1.6 dwinter 347: op="ct"
1.5 dwinter 348:
349: #except:
350: # print "END",value,name,Exception
1.1 dwinter 351: if len(whereList)>0:
352: where="WHERE "+string.join(whereList," "+lop+" ")
353: else:
354: where=""
355: #print "QE",table
356:
1.9 dwinter 357: query="SELECT %s FROM %s %s %s %s %s"%(select,table,where,sort,max,skip)
1.5 dwinter 358: if not nostore=="yes":
359:
360: self.REQUEST.SESSION['qs']=opfields
1.7 dwinter 361: #print "IAMHERE again:", query
1.5 dwinter 362:
363: if storename:
1.7 dwinter 364: query2="SELECT count(*) FROM %s %s"%(table,where)
1.5 dwinter 365: #print "storing",query2
366: #print "QUERYSTRING:",self.REQUEST.SESSION[storename]['queryString2']
367: if not self.REQUEST.SESSION.has_key(storename):
368: self.REQUEST.SESSION[storename]={}
369: if self.REQUEST.SESSION[storename].has_key('queryString2'):
370: #print "HI",storename
371: if not self.REQUEST.SESSION[storename]['queryString2']==query2:
372: #print "HOOOOO",storename
373: self.REQUEST.SESSION[storename]['queryString2']=query2
374: self.REQUEST.SESSION[storename]['count']=self.search(var=query2)[0].count
375: #print "QUERY",query2,"::::",self.REQUEST.SESSION[storename]['queryString2']
376:
377: else:
378: self.REQUEST.SESSION[storename]['queryString2']=query2
379: self.REQUEST.SESSION[storename]['count']=self.search(var=query2)[0].count
380: #print "QUERYNEW",self.REQUEST.SESSION[storename]['queryString2']
381:
382:
383: self.REQUEST.SESSION[storename]['rangeStart']=rangeStart
384: if limit=="all":
385: self.REQUEST.SESSION[storename]['rangeEnd']=self.REQUEST.SESSION[storename]['count']
386: else:
387: self.REQUEST.SESSION[storename]['rangeEnd']=int(rangeStart)+int(limit)
388: self.REQUEST.SESSION[storename]['rangeSize']=limit
389:
1.9 dwinter 390: #print query
1.1 dwinter 391: return self.search(var=query)
392:
393: def ZSQLSearch(self):
394: """To be done"""
395: rq=self.REQUEST['QUERY_STRING']
396: querys=rq.split("&")
397:
398:
399: for querytemp in querys:
400: query=querytemp.split("=")
401: try:
402: if query[0].lower()=="-format":
403: formatfile=query[1]
404: except:
405: """nothing"""
406: #print formatfile
407: self.REQUEST.SESSION['query']=string.join(self.REQUEST['QUERY_STRING'].split("&"),",")
408: return self.REQUEST.RESPONSE.redirect(urllib.unquote(formatfile))
409:
410:
411: def ZSQLint(self,string):
412: try:
413:
414: return(int(string))
415: except:
416: return 0
1.5 dwinter 417:
418: def nextLink(self,html,storename="foundCount"):
419: """nextLink"""
420: try:
421: limit=self.REQUEST.SESSION[storename]['rangeSize']
422: newRangeStart=int(self.REQUEST.SESSION[storename]['rangeStart'])+int(limit)
423: except:
424: limit=0
425: newRangeStart=0
426:
427: if newRangeStart>self.REQUEST.SESSION[storename]['count']:
428: newRangeStart=self.REQUEST.SESSION[storename]['count']-10
429:
430:
431: #create new query string
432:
433: if self.REQUEST['QUERY_STRING']=="":
434: qs=self.REQUEST.SESSION['query']
435:
436: queries=string.split(qs,",")
437:
438:
439: else:
440: qs=self.REQUEST['QUERY_STRING']
441: queries=string.split(qs,"&")
442:
443:
444:
445: newquery=[]
446:
447: skipFound=0
448:
449: for query in queries:
450: #print query.split("=")[0]
451: if query[0]=="_" : query[0]="-"
452:
453: if query.split("=")[0].lower()=="-skip":
454: skipFound=1
455: query="-skip=%i"%newRangeStart
456: newquery.append(query)
457:
458: if skipFound==0 :
459: query="-skip=%i"%newRangeStart
460: newquery.append(query)
461:
462: newquerystring=string.join(newquery,"&")
1.9 dwinter 463:
464: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html)
1.5 dwinter 465:
466:
467: def prevLink(self,html,storename="foundCount"):
468: """prev link"""
469: try:
470: limit=self.REQUEST.SESSION[storename]['rangeSize']
471: newRangeStart=int(self.REQUEST.SESSION[storename]['rangeStart'])-int(limit)
472: except:
473: limit=0
474: newRangeStart=0
475:
476: #print "limit",limit,newRangeStart,int(self.REQUEST.SESSION[storename]['rangeStart'])
477:
478: if newRangeStart<0:
479: newRangeStart=0
480:
481: #create new query string
482:
483: if self.REQUEST['QUERY_STRING']=="":
484: qs=self.REQUEST.SESSION['query']
485: #qs=re.sub(r'_','-',qs) #aendern für query
486: queries=string.split(qs,",")
487:
488:
489: else:
490: qs=self.REQUEST['QUERY_STRING']
491: queries=string.split(qs,"&")
492:
493:
494:
495: newquery=[]
496:
497: skipFound=0
498:
499: for query in queries:
500: #print query.split("=")[0]
501:
502: if query[0]=="_" : query[0]="-"
503:
504: if query.split("=")[0].lower()=="-skip":
505: #print"HI"
506: query="-skip=%i"%newRangeStart
507: skipFound=1
508: newquery.append(query)
509:
510: if skipFound==0 :
511: query="-skip=%i"%newRangeStart
512: newquery.append(query)
513:
514: newquerystring=string.join(newquery,"&")
1.9 dwinter 515:
516: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html)
517:
518:
1.5 dwinter 519:
520: def prevLink_old(self,html):
1.1 dwinter 521: """prev link"""
522: if self.REQUEST['QUERY_STRING']=="":
523: qs=self.REQUEST.SESSION['query']
524: else:
525: qs=self.REQUEST['QUERY_STRING']
526: max=re.search(r'max\=(.*)\,',qs.lower())
527: offset=re.search(r'offset\=(.*)\,',qs.lower())
528: if not offset:
529: offsetnew=0
530: else:
531: offsetnew=int(offset)-max
532: if offsetnew<0:
533: offsetnew=0
534: queries=string.split(qs,",")
535: newquery=[]
536: if offset:
537: for query in queries:
538: if query.split("=")[0].lower()=="offset":
539: query="-offset=%i"%offsetnew
540: newquery.append(query)
541: newquerystring=string.join(newquery,"&")
542: else:
543: queries.append("-offset=%i"%offsetnew)
544: newquerystring=string.join(queries,"&")
1.9 dwinter 545: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html)
1.1 dwinter 546:
1.5 dwinter 547: def nextLink_old(self,html):
1.1 dwinter 548: """prev link"""
549: if self.REQUEST['QUERY_STRING']=="":
550: qs=self.REQUEST.SESSION['query']
551: else:
552: qs=self.REQUEST['QUERY_STRING']
553: max=re.search(r'max\=(.*)\,',qs.lower())
554:
555: offset=re.search(r'offset\=(.*)\,',qs.lower())
556: if not offset:
557: offsetnew=1
558: else:
559: offsetnew=int(offset)+int(max)
560: if offsetnew<0:
561: offsetnew=0
562: queries=string.split(qs,",")
563: newquery=[]
564: if offset:
565: for query in queries:
566:
567: if query.split("=")[0].lower()=="-offset":
568: query="-offset=%i"%offsetnew
569: newquery.append(query)
570: newquerystring=string.join(newquery,"&")
571: else:
572: queries.append("-offset=%i"%offsetnew)
573: newquerystring=string.join(queries,"&")
574:
1.9 dwinter 575: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html)
1.1 dwinter 576:
1.5 dwinter 577:
1.1 dwinter 578: manage_addZSQLExtendFolderForm=DTMLFile('ZSQLExtendFolderAdd', globals())
579:
580: def manage_addZSQLExtendFolder(self, id, title='',
581: createPublic=0,
582: createUserF=0,
583: REQUEST=None):
584: """Add a new Folder object with id *id*.
585:
586: If the 'createPublic' and 'createUserF' parameters are set to any true
587: value, an 'index_html' and a 'UserFolder' objects are created respectively
588: in the new folder.
589: """
590:
591:
592: ob=ZSQLExtendFolder()
593: ob.id=str(id)
594: ob.title=title
595: self._setObject(id, ob)
596: ob=self._getOb(id)
597:
598: checkPermission=getSecurityManager().checkPermission
599:
600: if createUserF:
601: if not checkPermission('Add User Folders', ob):
602: raise Unauthorized, (
603: 'You are not authorized to add User Folders.'
604: )
605: ob.manage_addUserFolder()
606:
607: if createPublic:
608: if not checkPermission('Add Page Templates', ob):
609: raise Unauthorized, (
610: 'You are not authorized to add Page Templates.'
611: )
612: ob.manage_addProduct['PageTemplates'].manage_addPageTemplate(
613: id='index_html', title='')
614:
615: if REQUEST is not None:
616: return self.manage_main(self, REQUEST, update_menu=1)
617:
618:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>