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