File:  [Repository] / ZSQLExtend / ZSQLExtend.py
Revision 1.5: download - view: text, annotated - select for diffs - revision graph
Wed Jan 21 16:54:45 2004 UTC (20 years, 4 months ago) by dwinter
Branches: MAIN
CVS tags: HEAD
new standard elementes addes

    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: 
   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):
   46:         """inlinesearch"""
   47:         qs=[]
   48:         if storename:
   49:             """store"""
   50:         else:
   51:             storename="foundCount"
   52:             
   53:     
   54: 
   55:         #print "INLINE:",argv
   56:         for a in argv.keys():
   57:             qs.append(a+"="+urllib.quote(str(argv[a])))
   58:         #return []  
   59:         return self.parseQueryString(string.join(qs,","),"_",storename=storename)
   60: 
   61:     def ZSQLInlineSearch2(self,query):
   62:         """inlinesearch"""
   63:         qs=[]
   64:         
   65:     
   66: 
   67:         #print "INLINE:",query
   68:         
   69:         return self.search(var=query)
   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+"\"")
   91:             valueList.append(libpq.PgQuoteString(addList[x]))
   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:         
  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):
  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)
  149:                 identify=identify.split("=")[0]+"="+libpq.PgQuoteString(identify.split("=")[1])
  150:             elif name=="-format":
  151:                 format=urllib.unquote(value)
  152:             elif (not name[0]=="-") and (not len(value)==0):
  153:                 changeList.append("\""+name+"\"="+libpq.PgQuoteString(urllib.unquote(value)))
  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:     
  159:     def ZSQLFind(self,qs="",select="*",storename=None):
  160:         """Find"""
  161: 
  162: 
  163:         if qs=="":
  164:            if self.REQUEST['QUERY_STRING']:
  165:                qs=self.REQUEST['QUERY_STRING']
  166:                
  167:                
  168:                qs=string.join(qs.split("&"),",")
  169:            else:
  170:                
  171:                qs=self.REQUEST.SESSION['query']
  172:         else:
  173:             qs=string.join(qs.split("&"),",")
  174: 
  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']
  230: 
  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
  243:         lop="AND" # standardsuche mit and
  244:         max="ALL" #standard alle auswählen
  245:         whereList=[]
  246:         sort=""
  247:         op="bw"
  248:         opfields={}
  249:         skip=""
  250:         rangeStart=0
  251:         
  252:         if not select:
  253:             select="*"
  254:         #print "Q",nostore,qs
  255:         #check for op in the case of inline search
  256:         if iCT=="_":            
  257:             for q in qs.split(","):
  258:                 
  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
  268:         for q in qs.split(","):
  269:             
  270:             
  271:             #try:
  272: 
  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":
  296:                     self.REQUEST.SESSION['token']=value
  297: 
  298:             elif name==iCT+"op":
  299:                 op=value
  300: 
  301: 
  302: 
  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))
  320:                     op="ct"
  321:             
  322:             #except:
  323:             #    print "END",value,name,Exception       
  324:         if len(whereList)>0:
  325:             where="WHERE "+string.join(whereList," "+lop+" ")
  326:         else:
  327:             where=""
  328:         #print "QE",table
  329:         
  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:             
  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
  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):
  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:         
  516:     def nextLink_old(self,html):
  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:         
  546:     
  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>