from OFS.Folder import Folder
from Globals import Persistent
from Acquisition import Implicit
from Globals import DTMLFile
import urllib
import re
import string
from pyPgSQL import libpq
from AccessControl import getSecurityManager
import os.path
def quoteString(name):
#return re.sub(r'([\(\)\?])',"\\\1",name)
#return "Euklid"
return name
class ZSQLExtendFolder(Persistent, Implicit, Folder):
"""Folder"""
meta_type="ZSQLExtendFolder"
def lowerEnd(self,path):
"""oinly for demo"""
return os.path.splitext(path)[0]+".jpg"
def ZSQLisEmpty(self,field):
"""Teste ob Treffer leer"""
#print "field",field
if not field:
return 1
if field=="":
return 1
return 0
def ZSQLsearchOptions(self,fieldname=""):
"""return HTML Fragment with search options"""
ret=""""""%fieldname
return ret
def ZSQLInlineSearch(self,storename=None,**argv):
"""inlinesearch"""
qs=[]
if storename:
"""store"""
else:
storename="foundCount"
#print "INLINE:",argv
for a in argv.keys():
qs.append(a+"="+urllib.quote(str(argv[a])))
#return []
return self.parseQueryString(string.join(qs,","),"_",storename=storename)
def ZSQLInlineSearch2(self,query):
"""inlinesearch"""
qs=[]
#print "INLINE:",query
return self.search(var=query)
def ZSQLAdd(self):
"""Neuer Eintrag"""
qs=self.REQUEST['QUERY_STRING']
addList={}
for q in qs.split("&"):
name=re.sub("r'+'"," ",q.split("=")[0].lower())
value=q.split("=")[1]
value=re.sub(r'\+'," ",value)
value=urllib.unquote(value)
if name=="-table":
table=urllib.unquote(value)
elif name=="-format":
format=urllib.unquote(value)
elif (not name[0]=="-") and (not len(value)==0):
addList[urllib.unquote(name)]=urllib.unquote(value)
keyList=[]
valueList=[]
for x in addList.keys():
keyList.append("\""+x+"\"")
valueList.append(libpq.PgQuoteString(addList[x]))
keyString=string.join(keyList,",")
valueString=string.join(valueList,",")
queryString="INSERT INTO %s (%s) VALUES (%s)"%(table,keyString,valueString)
self.search(var=queryString)
return self.REQUEST.RESPONSE.redirect(format)
def ZSQLChange(self,**argv):
"""Ändern von Einträgen"""
#qs=self.REQUEST['QUERY_STRING']
# very bad hack
qs_temp=[]
for a in self.REQUEST.form.keys():
qs_temp.append(a+"="+urllib.quote(str(self.REQUEST.form[a])))
qs=string.join(qs_temp,"&")
#print "CHANGE QS",self.REQUEST
#return self.REQUEST
changeList=[]
for q in qs.split("&"):
name=urllib.unquote(re.sub("r'+'"," ",q.split("=")[0].lower()))
value=q.split("=")[1]
value=re.sub(r'\+'," ",value)
value=urllib.unquote(value)
if name=="-table":
table=urllib.unquote(value)
elif name=="-identify":
identify=urllib.unquote(value)
identify=identify.split("=")[0]+"="+libpq.PgQuoteString(identify.split("=")[1])
elif name=="-format":
format=urllib.unquote(value)
elif (not name[0]=="-") and (not len(value)==0):
changeList.append("\""+name+"\"="+libpq.PgQuoteString(urllib.unquote(value)))
changeString=string.join(changeList,",")
queryString="UPDATE %s SET %s WHERE %s"%(table,changeString,identify)
self.search(var=queryString)
return self.REQUEST.RESPONSE.redirect(format)
def ZSQLChange_old(self):
"""Ändern von Einträgen"""
qs=self.REQUEST['QUERY_STRING']
#print "CHANGE QS",self.REQUEST
#return self.REQUEST
changeList=[]
for q in qs.split("&"):
name=urllib.unquote(re.sub("r'+'"," ",q.split("=")[0].lower()))
value=q.split("=")[1]
value=re.sub(r'\+'," ",value)
value=urllib.unquote(value)
if name=="-table":
table=urllib.unquote(value)
elif name=="-identify":
identify=urllib.unquote(value)
identify=identify.split("=")[0]+"="+libpq.PgQuoteString(identify.split("=")[1])
elif name=="-format":
format=urllib.unquote(value)
elif (not name[0]=="-") and (not len(value)==0):
changeList.append("\""+name+"\"="+libpq.PgQuoteString(urllib.unquote(value)))
changeString=string.join(changeList,",")
queryString="UPDATE %s SET %s WHERE %s"%(table,changeString,identify)
self.search(var=queryString)
return self.REQUEST.RESPONSE.redirect(format)
def ZSQLFind(self,qs="",select="*",storename=None):
"""Find"""
if qs=="":
if self.REQUEST['QUERY_STRING']:
qs=self.REQUEST['QUERY_STRING']
qs=string.join(qs.split("&"),",")
else:
qs=self.REQUEST.SESSION['query']
else:
qs=string.join(qs.split("&"),",")
qs=re.sub("\\+"," ",qs)# Austauschen da Leerzeichen bei http-get durch + ersetzt wird, generell sollte alles auf post umgeschrieben werden. vom search formular.
#print "QS",qs
if storename:
"""store"""
else:
storename="foundCount"
ret=self.parseQueryString(qs,"-",select=select,storemax="yes",storename=storename)
#print self.REQUEST.SESSION["foundCount"]
return ret
def ZSQLFoundCountLen(self,var):
return len(var)
def ZSQLFoundCount(self,qs="",select="*",storename=None):
## if qs=="":
## if self.REQUEST['QUERY_STRING']:
## qs=self.REQUEST['QUERY_STRING']
## qs=string.join(qs.split("&"),",")
## else:
## qs=self.REQUEST.SESSION['query']
## else:
## qs=string.join(qs.split("&"),",")
## temp= self.parseQueryString(qs,"-",select=select,storemax="yes",nostore="yes")
if storename:
"""store"""
else:
storename="foundCount"
return self.REQUEST.SESSION[storename]['count']
def ZSQLRangeStart(self,storename=None):
if storename:
"""store"""
else:
storename="foundCount"
return self.REQUEST.SESSION[storename]['rangeStart']
def ZSQLRangeSize(self,storename=None):
if storename:
"""store"""
else:
storename="foundCount"
return self.REQUEST.SESSION[storename]['rangeSize']
def ZSQLRangeEnd(self,storename=None):
if storename:
"""store"""
else:
storename="foundCount"
return self.REQUEST.SESSION[storename]['rangeEnd']
def parseQueryString(self,qs,iCT,storemax="no",select=None,nostore=None,storename=None):
"""analysieren den QueryString"""
#print "NO",nostore
lop="AND" # standardsuche mit and
max="ALL" #standard alle auswählen
whereList=[]
sort=""
op="bw"
opfields={}
skip=""
rangeStart=0
if not select:
select="*"
#print "Q",nostore,qs
#check for op in the case of inline search
splitted=qs.split(",")
for q in splitted:
name=re.sub("r'+'"," ",q.split("=")[0].lower())
value=urllib.unquote(q.split("=")[1])
if name[0:3]==iCT+"op":
op=value
field=name[4:]
opfields[field]=op
#print opfieldsa
#now analyse the querystring
for q in qs.split(","):
#try:
name=re.sub("r'+'"," ",q.split("=")[0].lower())
value=urllib.unquote(q.split("=")[1])
#value=libpq.PgQuoteString(value)
if name==iCT+"lop":
lop=value
elif name==iCT+"table":
table=value
elif name==iCT+"select":
select=value
elif name==iCT+"max":
max="LIMIT "+str(value)
limit=str(value)
elif name==iCT+"skip":
skip="OFFSET "+str(value)
rangeStart=str(value)
elif name==iCT+"join":
whereList.append(value)
elif name==iCT+"sort":
sort="ORDER BY "+value
elif name==iCT+"token":
if not nostore=="yes":
self.REQUEST.SESSION['token']=value
elif name==iCT+"op":
op=value
elif (not name[0]==iCT) and (not len(value)==0):
#print "OP",op,name
value=value.lower()
if opfields.has_key(name):
op=opfields[name]
else:
op="ct"
name="LOWER("+name+")"
if op=="ct":
whereList.append(name+" LIKE "+libpq.PgQuoteString("%"+value+"%"))
elif op=="gt":
whereList.append(name+">"+libpq.PgQuoteString(value))
elif op=="lt":
whereList.append(name+"<"+libpq.PgQuoteString(value))
elif op=="eq":
whereList.append(name+"="+libpq.PgQuoteString(value))
elif op=="bw":
whereList.append(name+" LIKE "+libpq.PgQuoteString(value+"%"))
elif op=="ew":
whereList.append(name+" LIKE "+libpq.PgQuoteString("%"+value))
op="ct"
#except:
# print "END",value,name,Exception
if len(whereList)>0:
where="WHERE "+string.join(whereList," "+lop+" ")
else:
where=""
#print "QE",table
query="SELECT %s FROM %s %s %s %s %s"%(select,table,where,sort,max,skip)
if not nostore=="yes":
self.REQUEST.SESSION['qs']=opfields
#print "IAMHERE again:", query
if storename:
query2="SELECT count(*) FROM %s %s"%(table,where)
#print "storing",query2
#print "QUERYSTRING:",self.REQUEST.SESSION[storename]['queryString2']
if not self.REQUEST.SESSION.has_key(storename):
self.REQUEST.SESSION[storename]={}
if self.REQUEST.SESSION[storename].has_key('queryString2'):
#print "HI",storename
if not self.REQUEST.SESSION[storename]['queryString2']==query2:
#print "HOOOOO",storename
self.REQUEST.SESSION[storename]['queryString2']=query2
self.REQUEST.SESSION[storename]['count']=self.search(var=query2)[0].count
#print "QUERY",query2,"::::",self.REQUEST.SESSION[storename]['queryString2']
else:
self.REQUEST.SESSION[storename]['queryString2']=query2
self.REQUEST.SESSION[storename]['count']=self.search(var=query2)[0].count
#print "QUERYNEW",self.REQUEST.SESSION[storename]['queryString2']
self.REQUEST.SESSION[storename]['rangeStart']=rangeStart
if limit=="all":
self.REQUEST.SESSION[storename]['rangeEnd']=self.REQUEST.SESSION[storename]['count']
else:
self.REQUEST.SESSION[storename]['rangeEnd']=int(rangeStart)+int(limit)
self.REQUEST.SESSION[storename]['rangeSize']=limit
#print query
return self.search(var=query)
def ZSQLSearch(self):
"""To be done"""
rq=self.REQUEST['QUERY_STRING']
querys=rq.split("&")
for querytemp in querys:
query=querytemp.split("=")
try:
if query[0].lower()=="-format":
formatfile=query[1]
except:
"""nothing"""
#print formatfile
self.REQUEST.SESSION['query']=string.join(self.REQUEST['QUERY_STRING'].split("&"),",")
return self.REQUEST.RESPONSE.redirect(urllib.unquote(formatfile))
def ZSQLint(self,string):
try:
return(int(string))
except:
return 0
def nextLink(self,html,storename="foundCount"):
"""nextLink"""
try:
limit=self.REQUEST.SESSION[storename]['rangeSize']
newRangeStart=int(self.REQUEST.SESSION[storename]['rangeStart'])+int(limit)
except:
limit=0
newRangeStart=0
if newRangeStart>self.REQUEST.SESSION[storename]['count']:
newRangeStart=self.REQUEST.SESSION[storename]['count']-10
#create new query string
if self.REQUEST['QUERY_STRING']=="":
qs=self.REQUEST.SESSION['query']
queries=string.split(qs,",")
else:
qs=self.REQUEST['QUERY_STRING']
queries=string.split(qs,"&")
newquery=[]
skipFound=0
for query in queries:
#print query.split("=")[0]
if query[0]=="_" : query[0]="-"
if query.split("=")[0].lower()=="-skip":
skipFound=1
query="-skip=%i"%newRangeStart
newquery.append(query)
if skipFound==0 :
query="-skip=%i"%newRangeStart
newquery.append(query)
newquerystring=string.join(newquery,"&")
return "%s"%(self.REQUEST['URL0']+"?"+newquerystring,html)
def prevLink(self,html,storename="foundCount"):
"""prev link"""
try:
limit=self.REQUEST.SESSION[storename]['rangeSize']
newRangeStart=int(self.REQUEST.SESSION[storename]['rangeStart'])-int(limit)
except:
limit=0
newRangeStart=0
#print "limit",limit,newRangeStart,int(self.REQUEST.SESSION[storename]['rangeStart'])
if newRangeStart<0:
newRangeStart=0
#create new query string
if self.REQUEST['QUERY_STRING']=="":
qs=self.REQUEST.SESSION['query']
#qs=re.sub(r'_','-',qs) #aendern für query
queries=string.split(qs,",")
else:
qs=self.REQUEST['QUERY_STRING']
queries=string.split(qs,"&")
newquery=[]
skipFound=0
for query in queries:
#print query.split("=")[0]
if query[0]=="_" : query[0]="-"
if query.split("=")[0].lower()=="-skip":
#print"HI"
query="-skip=%i"%newRangeStart
skipFound=1
newquery.append(query)
if skipFound==0 :
query="-skip=%i"%newRangeStart
newquery.append(query)
newquerystring=string.join(newquery,"&")
return "%s"%(self.REQUEST['URL0']+"?"+newquerystring,html)
def prevLink_old(self,html):
"""prev link"""
if self.REQUEST['QUERY_STRING']=="":
qs=self.REQUEST.SESSION['query']
else:
qs=self.REQUEST['QUERY_STRING']
max=re.search(r'max\=(.*)\,',qs.lower())
offset=re.search(r'offset\=(.*)\,',qs.lower())
if not offset:
offsetnew=0
else:
offsetnew=int(offset)-max
if offsetnew<0:
offsetnew=0
queries=string.split(qs,",")
newquery=[]
if offset:
for query in queries:
if query.split("=")[0].lower()=="offset":
query="-offset=%i"%offsetnew
newquery.append(query)
newquerystring=string.join(newquery,"&")
else:
queries.append("-offset=%i"%offsetnew)
newquerystring=string.join(queries,"&")
return "%s"%(self.REQUEST['URL0']+"?"+newquerystring,html)
def nextLink_old(self,html):
"""prev link"""
if self.REQUEST['QUERY_STRING']=="":
qs=self.REQUEST.SESSION['query']
else:
qs=self.REQUEST['QUERY_STRING']
max=re.search(r'max\=(.*)\,',qs.lower())
offset=re.search(r'offset\=(.*)\,',qs.lower())
if not offset:
offsetnew=1
else:
offsetnew=int(offset)+int(max)
if offsetnew<0:
offsetnew=0
queries=string.split(qs,",")
newquery=[]
if offset:
for query in queries:
if query.split("=")[0].lower()=="-offset":
query="-offset=%i"%offsetnew
newquery.append(query)
newquerystring=string.join(newquery,"&")
else:
queries.append("-offset=%i"%offsetnew)
newquerystring=string.join(queries,"&")
return "%s"%(self.REQUEST['URL0']+"?"+newquerystring,html)
manage_addZSQLExtendFolderForm=DTMLFile('ZSQLExtendFolderAdd', globals())
def manage_addZSQLExtendFolder(self, id, title='',
createPublic=0,
createUserF=0,
REQUEST=None):
"""Add a new Folder object with id *id*.
If the 'createPublic' and 'createUserF' parameters are set to any true
value, an 'index_html' and a 'UserFolder' objects are created respectively
in the new folder.
"""
ob=ZSQLExtendFolder()
ob.id=str(id)
ob.title=title
self._setObject(id, ob)
ob=self._getOb(id)
checkPermission=getSecurityManager().checkPermission
if createUserF:
if not checkPermission('Add User Folders', ob):
raise Unauthorized, (
'You are not authorized to add User Folders.'
)
ob.manage_addUserFolder()
if createPublic:
if not checkPermission('Add Page Templates', ob):
raise Unauthorized, (
'You are not authorized to add Page Templates.'
)
ob.manage_addProduct['PageTemplates'].manage_addPageTemplate(
id='index_html', title='')
if REQUEST is not None:
return self.manage_main(self, REQUEST, update_menu=1)