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
8: from pyPgSQL import libpq
9: from AccessControl import getSecurityManager
10: import os.path
11:
12: def quoteString(name):
13: #return re.sub(r'([\(\)\?])',"\\\1",name)
14: #return "Euklid"
15: return name
16:
17: class ZSQLExtendFolder(Persistent, Implicit, Folder):
18: """Folder"""
19: meta_type="ZSQLExtendFolder"
20:
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:
37: def actualPath(self,url=None):
38: """path"""
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']
44: if not url:
45: return "http://"+host+self.REQUEST['PATH_TRANSLATED']
46: else:
47: temp=self.REQUEST[url].split("/")
48: temp[2]=host
49: return string.join(temp,"/")
50:
51: def getRequest(self):
52: """request"""
53: return self.REQUEST
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"""
61: #print "field",field
62: if not field:
63: return 1
64: if field=="":
65: return 1
66: return 0
67:
68: def ZSQLsearchOptions(self,fieldname=""):
69: """return HTML Fragment with search options"""
70:
71: ret="""<select name="-op_%s">
72: <option value="bw">begins with</option> <!-- begins with / beginnt mit, "Wort*" -->
73: <option value="ew">ends with</option>
74: <option value="ct" selected>contains</option> <!-- contains / enthaellt, "Wort" -->
75: <option value="eq">equals</option> <!-- equals / ist, =Wort -->
76: </select>"""%fieldname
77: return ret
78:
79: def ZSQLInlineSearch(self,storename=None,**argv):
80: """inlinesearch"""
81: qs=[]
82: if storename:
83: """store"""
84: else:
85: storename="foundCount"
86:
87:
88:
89: #print "INLINE:",argv
90: for a in argv.keys():
91: qs.append(a+"="+urllib.quote(str(argv[a])))
92: #return []
93: return self.parseQueryString(string.join(qs,","),"_",storename=storename)
94:
95: def ZSQLInlineSearch2(self,query):
96: """inlinesearch"""
97: qs=[]
98:
99:
100:
101: #print "INLINE:",query
102:
103: return self.search(var=query)
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+"\"")
125: valueList.append(libpq.PgQuoteString(addList[x]))
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:
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):
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)
183: identify=identify.split("=")[0]+"="+libpq.PgQuoteString(identify.split("=")[1])
184: elif name=="-format":
185: format=urllib.unquote(value)
186: elif (not name[0]=="-") and (not len(value)==0):
187: changeList.append("\""+name+"\"="+libpq.PgQuoteString(urllib.unquote(value)))
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:
193: def ZSQLFind(self,qs="",select="oid,*",storename=None):
194: """Find"""
195:
196:
197: if qs=="":
198: if self.REQUEST['QUERY_STRING']:
199: qs=self.REQUEST['QUERY_STRING']
200:
201:
202: qs=string.join(qs.split("&"),",")
203: else:
204:
205: qs=self.REQUEST.SESSION['query']
206: else:
207: qs=string.join(qs.split("&"),",")
208:
209:
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']
265:
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
278: lop="AND" # standardsuche mit and
279: max="ALL" #standard alle auswählen
280: maxstr=""
281: whereList=[]
282: sort=""
283: op="bw"
284: opfields={}
285: skip=""
286: rangeStart=0
287: limit=0
288: searchFields={}
289:
290: if not select:
291: select="oid,*"
292: #print "Q",nostore,qs
293: #check for op in the case of inline search
294:
295: splitted=qs.split(",")
296:
297:
298: for q in splitted:
299:
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
307:
308: #print opfieldsa
309: #now analyse the querystring
310: for q in qs.split(","):
311:
312:
313: #try:
314:
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":
327: maxstr="LIMIT "+str(value)
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":
338: self.REQUEST.SESSION['token']=value
339:
340: elif name==iCT+"op":
341: op=value
342:
343:
344:
345: elif (not name[0]==iCT) and (not len(value)==0):
346:
347: #print "OP",op,name
348: value=value.lower()
349:
350: searchFields[name]=value
351:
352: if opfields.has_key(name):
353: op=opfields[name]
354: else:
355: op="ct"
356: name="LOWER("+name+")"
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))
369: op="ct"
370:
371: #except:
372: # print "END",value,name,Exception
373: if len(whereList)>0:
374: where="WHERE "+string.join(whereList," "+lop+" ")
375: else:
376: where=""
377: #print "QE",table
378:
379: query="SELECT %s FROM %s %s %s %s %s"%(select,table,where,sort,maxstr,skip)
380: if not nostore=="yes":
381:
382: self.REQUEST.SESSION['qs']=opfields
383: #print "IAMHERE again:", query
384:
385: if storename:
386: query2="SELECT count(*) FROM %s %s"%(table,where)
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
411: self.REQUEST.SESSION[storename]['searchFields']=searchFields
412:
413: print query
414: return self.search(var=query)
415:
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
441:
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:
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,"&")
498:
499: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html)
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,"&")
550:
551: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html)
552:
553:
554:
555: def prevLink_old(self,html):
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,"&")
580: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html)
581:
582: def nextLink_old(self,html):
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:
610: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html)
611:
612:
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>