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