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