Annotation of ZSQLExtend/ZSQLExtend.py, revision 1.13
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:
1.13 ! dwinter 24: #print "obj",obj
1.12 dwinter 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:
1.13 ! dwinter 207: self.REQUEST['QUERY_STRING']=qs
1.1 dwinter 208: qs=string.join(qs.split("&"),",")
1.13 ! dwinter 209:
1.1 dwinter 210:
1.5 dwinter 211: qs=re.sub("\\+"," ",qs)# Austauschen da Leerzeichen bei http-get durch + ersetzt wird, generell sollte alles auf post umgeschrieben werden. vom search formular.
1.13 ! dwinter 212:
1.5 dwinter 213: if storename:
214: """store"""
215: else:
216: storename="foundCount"
1.13 ! dwinter 217: #print "QS",qs
1.5 dwinter 218: ret=self.parseQueryString(qs,"-",select=select,storemax="yes",storename=storename)
219: #print self.REQUEST.SESSION["foundCount"]
220:
221: return ret
222:
223: def ZSQLFoundCountLen(self,var):
224: return len(var)
225:
226: def ZSQLFoundCount(self,qs="",select="*",storename=None):
227:
228: ## if qs=="":
229:
230: ## if self.REQUEST['QUERY_STRING']:
231:
232: ## qs=self.REQUEST['QUERY_STRING']
233: ## qs=string.join(qs.split("&"),",")
234: ## else:
235:
236: ## qs=self.REQUEST.SESSION['query']
237: ## else:
238: ## qs=string.join(qs.split("&"),",")
239:
240:
241: ## temp= self.parseQueryString(qs,"-",select=select,storemax="yes",nostore="yes")
242: if storename:
243: """store"""
244: else:
245: storename="foundCount"
246:
247: return self.REQUEST.SESSION[storename]['count']
248:
249: def ZSQLRangeStart(self,storename=None):
250:
251: if storename:
252: """store"""
253: else:
254: storename="foundCount"
255:
256: return self.REQUEST.SESSION[storename]['rangeStart']
257:
258: def ZSQLRangeSize(self,storename=None):
259:
260: if storename:
261: """store"""
262: else:
263: storename="foundCount"
264:
265: return self.REQUEST.SESSION[storename]['rangeSize']
1.1 dwinter 266:
1.5 dwinter 267: def ZSQLRangeEnd(self,storename=None):
268:
269: if storename:
270: """store"""
271: else:
272: storename="foundCount"
273:
274: return self.REQUEST.SESSION[storename]['rangeEnd']
275:
276: def parseQueryString(self,qs,iCT,storemax="no",select=None,nostore=None,storename=None):
277: """analysieren den QueryString"""
278: #print "NO",nostore
1.1 dwinter 279: lop="AND" # standardsuche mit and
280: max="ALL" #standard alle auswählen
1.11 dwinter 281: maxstr=""
1.1 dwinter 282: whereList=[]
283: sort=""
284: op="bw"
1.2 dwinter 285: opfields={}
1.5 dwinter 286: skip=""
287: rangeStart=0
1.11 dwinter 288: limit=0
289: searchFields={}
1.2 dwinter 290:
1.1 dwinter 291: if not select:
1.10 dwinter 292: select="oid,*"
1.5 dwinter 293: #print "Q",nostore,qs
1.2 dwinter 294: #check for op in the case of inline search
1.6 dwinter 295:
296: splitted=qs.split(",")
297:
298:
299: for q in splitted:
1.5 dwinter 300:
1.2 dwinter 301: name=re.sub("r'+'"," ",q.split("=")[0].lower())
1.13 ! dwinter 302: value=urllib.unquote(q.split("=",1)[1])
1.2 dwinter 303:
304: if name[0:3]==iCT+"op":
305: op=value
306: field=name[4:]
307: opfields[field]=op
1.6 dwinter 308:
309: #print opfieldsa
1.2 dwinter 310: #now analyse the querystring
1.1 dwinter 311: for q in qs.split(","):
1.5 dwinter 312:
313:
314: #try:
1.1 dwinter 315:
1.5 dwinter 316: name=re.sub("r'+'"," ",q.split("=")[0].lower())
1.13 ! dwinter 317: value=urllib.unquote(q.split("=",1)[1])
1.5 dwinter 318: #value=libpq.PgQuoteString(value)
319:
320:
321: if name==iCT+"lop":
322: lop=value
323: elif name==iCT+"table":
324: table=value
325: elif name==iCT+"select":
326: select=value
327: elif name==iCT+"max":
1.11 dwinter 328: maxstr="LIMIT "+str(value)
1.5 dwinter 329: limit=str(value)
330: elif name==iCT+"skip":
331: skip="OFFSET "+str(value)
332: rangeStart=str(value)
333: elif name==iCT+"join":
334: whereList.append(value)
335: elif name==iCT+"sort":
336: sort="ORDER BY "+value
337: elif name==iCT+"token":
338: if not nostore=="yes":
1.1 dwinter 339: self.REQUEST.SESSION['token']=value
340:
1.5 dwinter 341: elif name==iCT+"op":
342: op=value
343:
344:
1.1 dwinter 345:
1.5 dwinter 346: elif (not name[0]==iCT) and (not len(value)==0):
1.6 dwinter 347:
348: #print "OP",op,name
1.5 dwinter 349: value=value.lower()
1.11 dwinter 350:
351: searchFields[name]=value
352:
1.5 dwinter 353: if opfields.has_key(name):
354: op=opfields[name]
1.6 dwinter 355: else:
356: op="ct"
357: name="LOWER("+name+")"
1.5 dwinter 358: if op=="ct":
359: whereList.append(name+" LIKE "+libpq.PgQuoteString("%"+value+"%"))
360: elif op=="gt":
361: whereList.append(name+">"+libpq.PgQuoteString(value))
362: elif op=="lt":
363: whereList.append(name+"<"+libpq.PgQuoteString(value))
364: elif op=="eq":
365: whereList.append(name+"="+libpq.PgQuoteString(value))
366: elif op=="bw":
367: whereList.append(name+" LIKE "+libpq.PgQuoteString(value+"%"))
368: elif op=="ew":
369: whereList.append(name+" LIKE "+libpq.PgQuoteString("%"+value))
1.6 dwinter 370: op="ct"
1.5 dwinter 371:
372: #except:
373: # print "END",value,name,Exception
1.1 dwinter 374: if len(whereList)>0:
375: where="WHERE "+string.join(whereList," "+lop+" ")
376: else:
377: where=""
378: #print "QE",table
379:
1.11 dwinter 380: query="SELECT %s FROM %s %s %s %s %s"%(select,table,where,sort,maxstr,skip)
1.13 ! dwinter 381:
1.5 dwinter 382: if not nostore=="yes":
383:
384: self.REQUEST.SESSION['qs']=opfields
1.7 dwinter 385: #print "IAMHERE again:", query
1.5 dwinter 386:
387: if storename:
1.7 dwinter 388: query2="SELECT count(*) FROM %s %s"%(table,where)
1.5 dwinter 389: #print "storing",query2
390: #print "QUERYSTRING:",self.REQUEST.SESSION[storename]['queryString2']
391: if not self.REQUEST.SESSION.has_key(storename):
392: self.REQUEST.SESSION[storename]={}
393: if self.REQUEST.SESSION[storename].has_key('queryString2'):
394: #print "HI",storename
395: if not self.REQUEST.SESSION[storename]['queryString2']==query2:
396: #print "HOOOOO",storename
397: self.REQUEST.SESSION[storename]['queryString2']=query2
398: self.REQUEST.SESSION[storename]['count']=self.search(var=query2)[0].count
399: #print "QUERY",query2,"::::",self.REQUEST.SESSION[storename]['queryString2']
400:
401: else:
402: self.REQUEST.SESSION[storename]['queryString2']=query2
403: self.REQUEST.SESSION[storename]['count']=self.search(var=query2)[0].count
404: #print "QUERYNEW",self.REQUEST.SESSION[storename]['queryString2']
405:
406:
407: self.REQUEST.SESSION[storename]['rangeStart']=rangeStart
408: if limit=="all":
409: self.REQUEST.SESSION[storename]['rangeEnd']=self.REQUEST.SESSION[storename]['count']
410: else:
411: self.REQUEST.SESSION[storename]['rangeEnd']=int(rangeStart)+int(limit)
412: self.REQUEST.SESSION[storename]['rangeSize']=limit
1.11 dwinter 413: self.REQUEST.SESSION[storename]['searchFields']=searchFields
1.5 dwinter 414:
1.13 ! dwinter 415: #print "Q",query
1.1 dwinter 416: return self.search(var=query)
1.11 dwinter 417:
1.1 dwinter 418:
419: def ZSQLSearch(self):
420: """To be done"""
421: rq=self.REQUEST['QUERY_STRING']
422: querys=rq.split("&")
423:
424:
425: for querytemp in querys:
426: query=querytemp.split("=")
427: try:
428: if query[0].lower()=="-format":
429: formatfile=query[1]
430: except:
431: """nothing"""
432: #print formatfile
433: self.REQUEST.SESSION['query']=string.join(self.REQUEST['QUERY_STRING'].split("&"),",")
434: return self.REQUEST.RESPONSE.redirect(urllib.unquote(formatfile))
435:
436:
437: def ZSQLint(self,string):
438: try:
439:
440: return(int(string))
441: except:
442: return 0
1.5 dwinter 443:
1.11 dwinter 444: def getZSQLSearchFieldsList(self,storename="foundCount"):
445: """get searchfieldList"""
1.13 ! dwinter 446: #print self.REQUEST.SESSION[storename]['searchFields'].keys()
1.11 dwinter 447: return self.REQUEST.SESSION[storename]['searchFields'].keys()
448:
449: def getZSQLSearchFields(self,storename="foundCount"):
450: """get searchfield"""
1.13 ! dwinter 451: #print "SF",self.REQUEST.SESSION[storename]['searchFields']
1.11 dwinter 452: return self.REQUEST.SESSION[storename]['searchFields']
453:
454:
1.5 dwinter 455: def nextLink(self,html,storename="foundCount"):
456: """nextLink"""
457: try:
458: limit=self.REQUEST.SESSION[storename]['rangeSize']
459: newRangeStart=int(self.REQUEST.SESSION[storename]['rangeStart'])+int(limit)
460: except:
461: limit=0
462: newRangeStart=0
463:
464: if newRangeStart>self.REQUEST.SESSION[storename]['count']:
465: newRangeStart=self.REQUEST.SESSION[storename]['count']-10
466:
467:
468: #create new query string
469:
470: if self.REQUEST['QUERY_STRING']=="":
471: qs=self.REQUEST.SESSION['query']
472:
473: queries=string.split(qs,",")
474:
475:
476: else:
477: qs=self.REQUEST['QUERY_STRING']
478: queries=string.split(qs,"&")
479:
480:
481:
482: newquery=[]
483:
484: skipFound=0
485:
486: for query in queries:
487: #print query.split("=")[0]
488: if query[0]=="_" : query[0]="-"
489:
490: if query.split("=")[0].lower()=="-skip":
491: skipFound=1
492: query="-skip=%i"%newRangeStart
493: newquery.append(query)
494:
495: if skipFound==0 :
496: query="-skip=%i"%newRangeStart
497: newquery.append(query)
498:
499: newquerystring=string.join(newquery,"&")
1.9 dwinter 500:
501: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html)
1.5 dwinter 502:
503:
504: def prevLink(self,html,storename="foundCount"):
505: """prev link"""
506: try:
507: limit=self.REQUEST.SESSION[storename]['rangeSize']
508: newRangeStart=int(self.REQUEST.SESSION[storename]['rangeStart'])-int(limit)
509: except:
510: limit=0
511: newRangeStart=0
512:
513: #print "limit",limit,newRangeStart,int(self.REQUEST.SESSION[storename]['rangeStart'])
514:
515: if newRangeStart<0:
516: newRangeStart=0
517:
518: #create new query string
519:
520: if self.REQUEST['QUERY_STRING']=="":
521: qs=self.REQUEST.SESSION['query']
522: #qs=re.sub(r'_','-',qs) #aendern für query
523: queries=string.split(qs,",")
524:
525:
526: else:
527: qs=self.REQUEST['QUERY_STRING']
528: queries=string.split(qs,"&")
529:
530:
531:
532: newquery=[]
533:
534: skipFound=0
535:
536: for query in queries:
537: #print query.split("=")[0]
538:
539: if query[0]=="_" : query[0]="-"
540:
541: if query.split("=")[0].lower()=="-skip":
542: #print"HI"
543: query="-skip=%i"%newRangeStart
544: skipFound=1
545: newquery.append(query)
546:
547: if skipFound==0 :
548: query="-skip=%i"%newRangeStart
549: newquery.append(query)
550:
551: newquerystring=string.join(newquery,"&")
1.9 dwinter 552:
553: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html)
554:
555:
1.5 dwinter 556:
557: def prevLink_old(self,html):
1.1 dwinter 558: """prev link"""
559: if self.REQUEST['QUERY_STRING']=="":
560: qs=self.REQUEST.SESSION['query']
561: else:
562: qs=self.REQUEST['QUERY_STRING']
563: max=re.search(r'max\=(.*)\,',qs.lower())
564: offset=re.search(r'offset\=(.*)\,',qs.lower())
565: if not offset:
566: offsetnew=0
567: else:
568: offsetnew=int(offset)-max
569: if offsetnew<0:
570: offsetnew=0
571: queries=string.split(qs,",")
572: newquery=[]
573: if offset:
574: for query in queries:
575: if query.split("=")[0].lower()=="offset":
576: query="-offset=%i"%offsetnew
577: newquery.append(query)
578: newquerystring=string.join(newquery,"&")
579: else:
580: queries.append("-offset=%i"%offsetnew)
581: newquerystring=string.join(queries,"&")
1.9 dwinter 582: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html)
1.1 dwinter 583:
1.5 dwinter 584: def nextLink_old(self,html):
1.1 dwinter 585: """prev link"""
586: if self.REQUEST['QUERY_STRING']=="":
587: qs=self.REQUEST.SESSION['query']
588: else:
589: qs=self.REQUEST['QUERY_STRING']
590: max=re.search(r'max\=(.*)\,',qs.lower())
591:
592: offset=re.search(r'offset\=(.*)\,',qs.lower())
593: if not offset:
594: offsetnew=1
595: else:
596: offsetnew=int(offset)+int(max)
597: if offsetnew<0:
598: offsetnew=0
599: queries=string.split(qs,",")
600: newquery=[]
601: if offset:
602: for query in queries:
603:
604: if query.split("=")[0].lower()=="-offset":
605: query="-offset=%i"%offsetnew
606: newquery.append(query)
607: newquerystring=string.join(newquery,"&")
608: else:
609: queries.append("-offset=%i"%offsetnew)
610: newquerystring=string.join(queries,"&")
611:
1.9 dwinter 612: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html)
1.1 dwinter 613:
1.5 dwinter 614:
1.1 dwinter 615: manage_addZSQLExtendFolderForm=DTMLFile('ZSQLExtendFolderAdd', globals())
616:
617: def manage_addZSQLExtendFolder(self, id, title='',
618: createPublic=0,
619: createUserF=0,
620: REQUEST=None):
621: """Add a new Folder object with id *id*.
622:
623: If the 'createPublic' and 'createUserF' parameters are set to any true
624: value, an 'index_html' and a 'UserFolder' objects are created respectively
625: in the new folder.
626: """
627:
628:
629: ob=ZSQLExtendFolder()
630: ob.id=str(id)
631: ob.title=title
632: self._setObject(id, ob)
633: ob=self._getOb(id)
634:
635: checkPermission=getSecurityManager().checkPermission
636:
637: if createUserF:
638: if not checkPermission('Add User Folders', ob):
639: raise Unauthorized, (
640: 'You are not authorized to add User Folders.'
641: )
642: ob.manage_addUserFolder()
643:
644: if createPublic:
645: if not checkPermission('Add Page Templates', ob):
646: raise Unauthorized, (
647: 'You are not authorized to add Page Templates.'
648: )
649: ob.manage_addProduct['PageTemplates'].manage_addPageTemplate(
650: id='index_html', title='')
651:
652: if REQUEST is not None:
653: return self.manage_main(self, REQUEST, update_menu=1)
654:
655:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>