from OFS.Folder import Folder
from Acquisition import Implicit
from Globals import DTMLFile,package_home,Persistent
import urllib
import re
import string
#from pyPgSQL import libpq
from AccessControl import getSecurityManager,Unauthorized
from Products.PageTemplates.ZopePageTemplate import ZopePageTemplate
from Products.PageTemplates.PageTemplateFile import PageTemplateFile
from Products.ZSQLMethods.SQL import SQLConnectionIDs
from xml.sax.saxutils import escape
from types import *
import Shared.DC.ZRDB.DA
import logging
import os.path
import os
import copy
import unicodedata
import tempfile
import sys
#ersetzt logging
def logger(txt,method,txt2):
"""logging"""
logging.info(txt+ txt2)
from OFS.SimpleItem import SimpleItem
def getTextFromNode(nodename):
"""get the cdata content of a node"""
if nodename is None:
return ""
nodelist=nodename.childNodes
rc = ""
for node in nodelist:
if node.nodeType == node.TEXT_NODE:
rc = rc + node.data
return rc
def analyseIntSearch(word):
#analyse integer searches
splitted=word.split("-")
if len(splitted)==1:
return "="+splitted[0]
if splitted[0]=="":
return "< "+splitted[1]
if splitted[1]=='':
return "> "+splitted[0]
else:
return "BETWEEN "+splitted[0]+" AND "+splitted[1]
def sql_quote(v):
# quote dictionary
quote_dict = {"\'": "''", "\\": "\\\\"}
for dkey in quote_dict.keys():
if string.find(v, dkey) >= 0:
v=string.join(string.split(v,dkey),quote_dict[dkey])
return "'%s'" % v
def showSQLConnectionIDs(self):
return SQLConnectionIDs(self)
class Options:
"""options class"""
class ZSQLIndex(SimpleItem):
"""index"""
meta_type="ZSQLIndex"
def __init__(self,index,id,table=''):
self.index=[x for x in index]
self.id=id
self.table=table
def setIndex(self,index):
self.index=[x for x in index]
def getIndex(self):
return self.index
class ZSQLExtendFolder(Folder,Persistent, Implicit):
"""Folder"""
meta_type="ZSQLExtendFolder"
def ZSQLQuote(self,str):
"""quote str for sql"""
return sql_quote(str)
def normalizeField(self,table,fieldname, newFieldName=None,mode="alter", RESPONSE=None):
"""normalize a field"""
import unicodedata
if not newFieldName:
newFieldName=fieldname+"_normal"
def normal(str):
if str:
return unicodedata.normalize('NFKD', str.decode('utf-8')).encode('ASCII', 'ignore')
else:
return ""
if mode=="create": # create the field
qstr="""alter table %s add %s %s"""
self.ZSQLSimpleSearch(qstr%(table,newFieldName,'text'))
qstr="select oid,%s from %s"%(fieldname,table)
for result in self.ZSQLSimpleSearch(qstr):
qstr="update %s set %s = %s where oid = %s"
self.ZSQLSimpleSearch(qstr%(table,newFieldName,self.ZSQLQuote(normal(getattr(result,fieldname))),result.oid))
def importAccessModell(self,configFileName,RESPONSE=None):
"""import tables from access
@param configFileName: xml-configfile
"""
from Ft.Xml import Parse
fh=file(configFileName)
doc=Parse(fh)
x=doc.xpath("//pathToFolder/@path")
if not (len(x)==1): # tag ist nich eineindeutig
return False
pathToFolder=x[0].value
for db in doc.xpath("//db"):
containers=db.xpath("./@container")
identifiers=db.xpath("./@identify")
if not (len(containers)==1):
return False
else:
container=containers[0].value
if not (len(identifiers)==1):
identifier=None
else:
identifier=identifiers[0].value
self.xsdToTable(container.lower(),container,modus="drop",filename=os.path.join(pathToFolder,container.lower()+".xsd"))
self.importXMLFileAccess(container.lower(),container,filename=os.path.join(pathToFolder,container.lower()+".xml"),identify=identifier)
return "
DONE"
def xsdToTable(self,table,elementNameForTable,modus="update", filename=None,data=None,RESPONSE=None):
"""reads an xsd file an creates the columns of a table out of its structure
@param table: name of the table the xml shall be imported into
@param elementNameForTable: must be a element of type complex type. the element of the sequence in the complex type
define the columns of the table >table<
@param data (optional): data to be imported
@param filename (optional) or filename
@param identify: (optional) field res. tag which identifies a entry uniquely for updating purposes.
@param RESPONSE: (optional)
"""
#from xml.dom.minidom import parseString,parse
from Ft.Xml import Parse
logger("import xsd",logging.INFO,"called")
#fh=file("/tmp/fmpxml.xml")
import bz2
import base64
ret=""
if data:
data=bz2.decompress(base64.decodestring(data))
#logger("import xsd",logging.INFO,"received file")
doc=Parse(data)
#logger("import xsd",logging.INFO,"parsed file")
elif filename:
fh=file(filename)
txt=fh.read()
doc=Parse(txt)
#logger("import xsd",logging.INFO,"parsed file")
Nss={'xsd':'http://www.w3.org/2001/XMLSchema'}
definingSequence=doc.xpath("""//xsd:element[@name='%s']/xsd:complexType/xsd:sequence/xsd:element/@name"""%elementNameForTable,explicitNss=Nss)
fieldNames=[x.value for x in definingSequence]
#check if table exists
qstr="""select relname from pg_class where relname = '%s'"""%table
if not(self.ZSQLSimpleSearch(qstr)) or (len (self.ZSQLSimpleSearch(qstr))<1): # if not the create the table
columns=[]
create=True
else:
create=False
logger("update xsd: fieldnames",logging.INFO,repr(fieldNames))
qstr="""select attname from pg_attribute, pg_class where attrelid = pg_class.oid and relname = '%s' """
columns=[x.attname for x in self.ZSQLSimpleSearch(qstr%table)]
if (modus=="drop") and (not create): #table shall be deleted, therefore it should exist (not create)
print "drop"
qstr="""DROP TABLE %s """
self.ZSQLSimpleSearch(qstr%table)
columns=[]
create=True
for fieldName in fieldNames:
if type(fieldName) is UnicodeType:
fieldName=fieldName.encode('utf-8')
logging.info("update xml: fieldname",logging.INFO,repr(fieldName))
if fieldName.lower() not in columns:
if create:# table does not exist therefore create with one column
qstr="""create table %s (%s %s)"""
create=False
else:# otherwise add the field
qstr="""alter table %s add %s %s"""
self.ZSQLSimpleSearch(qstr%(table,fieldName,'text'))
logger("update xsd: fieldname add",logging.INFO,qstr%(table,fieldName,'text'))
def importXMLFileAccess(self,table,container,data=None,identify=None,filename=None,RESPONSE=None):
'''
Import XML file in access format into the table
@param table: name of the table the xml shall be imported into
@param containerTagName: XML-Tag which describes a dataset
@param data: data to be imported
@param identify: (optional) field res. tag which identifies a entry uniquely for updating purposes.
@param RESPONSE: (optional)
'''
from xml.dom.pulldom import parseString,parse
logger("import xml",logging.INFO,"called")
#fh=file("/tmp/fmpxml.xml")
import bz2
import base64
ret=""
if data:
data=bz2.decompress(base64.decodestring(data))
logger("import xml",logging.INFO,"received file")
doc=parseString(data)
logger("import xml",logging.INFO,"parsed file")
elif filename:
fh=file(filename)
doc=parse(fh)
logger("import xml",logging.INFO,"parsed file")
while 1:
node=doc.getEvent()
if node is None:
break;
else:
if node[1].nodeName.lower()==container.lower(): # make everything case insensitive
doc.expandNode(node[1])
dataSet={}
for col in node[1].childNodes:
if col.nodeType is col.ELEMENT_NODE:
data=col.nodeName
dataSet[data]=getTextFromNode(col)
update=False
if identify:
field=dataSet[identify]
searchStr="""select %s from %s where %s = '%s'"""%(identify,table,identify,field)
logger("import xml",logging.INFO,searchStr)
search=self.ZSQLSimpleSearch(searchStr)
if search:
update=True
if update:
tmp=[]
for fieldName in dataSet.keys():
tmp.append("""%s = %s"""%(fieldName,self.ZSQLQuote(dataSet[fieldName])))
setStr=",".join(tmp)
field=dataSet[identify]
queryStr="""UPDATE %s SET %s WHERE %s = '%s' """%(table,setStr,identify,field)
logger("update xml",logging.INFO,queryStr)
self.ZSQLSimpleSearch(queryStr)
ret+="ud: %s \n"%field
else:
fields=",".join(dataSet.keys())
values=",".join([""" %s """%self.ZSQLQuote(dataSet[x]) for x in dataSet.keys()])
queryStr="""INSERT INTO %s (%s) VALUES (%s)"""%(table,fields,values)
self.ZSQLSimpleSearch(queryStr)
logger("update xml",logging.INFO,queryStr)
return ret
def importXMLFile(self,table,containerTagName,fieldNames,data=None,identify=None,filename=None,RESPONSE=None):
#TODO: finish importXMLFile
'''
Import XML file into the table
@param table: name of the table the xml shall be imported into
@param containerTagName: XML-Tag which describes a dataset
@param file: xmlfile handle
@param identify: (optional) field res. tag which identifies a entry uniquely for updating purposes.
@param RESPONSE: (optional)
'''
ret=""
from xml.dom.pulldom import parseString
doc=parseString(file.read())
while 1:
node=doc.getEvent()
if node is None:
break;
else:
if node[1].nodeName==containerTagName:
doc.expandNode(node[1])
cols=node[1].getElementsByTagName('COL')
dataSet=[]
for col in cols:
data=col.getElementsByTagName('DATA')
dataSet.append(getTextFromNode(data[0]))
update=False
if identify:
nr=fieldNames.index(identify)
field=dataSet[nr]
searchStr="""select %s from %s where %s = '%s'"""%(identify,table,identify,field)
logger("import xml",logging.INFO,searchStr)
search=self.ZSQLSimpleSearch(searchStr)
if search:
update=True
if update:
tmp=[]
for fieldName in fieldNames:
tmp.append("""%s = %s"""%(fieldName,self.ZSQLQuote(dataSet[fieldNames.index(fieldName)])))
setStr=",".join(tmp)
nr=fieldNames.index(identify)
field=dataSet[nr]
queryStr="""UPDATE %s SET %s WHERE %s = '%s' """%(table,setStr,identify,field)
logger("update xml",logging.INFO,queryStr)
self.ZSQLSimpleSearch(queryStr)
ret+="ud: %s \n"%field
else:
fields=",".join(fieldNames)
values=",".join([""" %s """%self.ZSQLQuote(x) for x in dataSet])
queryStr="""INSERT INTO %s (%s) VALUES (%s)"""%(table,fields,values)
self.ZSQLSimpleSearch(queryStr)
logger("update xml",logging.INFO,queryStr)
ret+="ad: %s \n"%field
elif node[1].nodeName=="METADATA":
fieldNames=[]
doc.expandNode(node[1])
names=node[1].getElementsByTagName('FIELD')
for name in names:
fieldNames.append(name.getAttribute('NAME'))
logger("update xml: fieldnames",logging.INFO,repr(fieldNames))
qstr="""select attname from pg_attribute, pg_class where attrelid = pg_class.oid and relname = '%s' """
columns=[x.attname for x in self.ZSQLSimpleSearch(qstr%table)]
for fieldName in fieldNames:
logger("update xml: fieldname",logging.INFO,repr(fieldName))
if fieldName not in columns:
qstr="""alter table %s add %s %s"""
self.ZSQLSimpleSearch(qstr%(table,fieldName,'text'))
logger("update xml: fieldname add",logging.INFO,qstr%(table,fieldName,'text'))
#fn=node[1].getAttribute("xml:id")
#nf=file("xtf/"+fn+".xtf",'w')
#nf.write(""""""+node[1].toxml()+"")
#print "wrote: %s"%fn
def importXMLFileFMP(self,table,dsn=None,uploadfile=None,update_fields=None,id_field=None,sync_mode=False,replace=False,redirect_url=None,ascii_db=False,RESPONSE=None):
'''
Import FileMaker XML file (FMPXMLRESULT format) into the table.
@param dsn: database connection string
@param table: name of the table the xml shall be imported into
@param uploadfile: xmlfile file
@param update_fields: (optional) list of fields to update; default is to create all fields
@param id_field: (optional) field which uniquely identifies an entry for updating purposes.
@param sync_mode: (optional) really synchronise, i.e. delete entries not in XML file
@param RESPONSE: (optional)
@param redirect_url: (optional) url for redirecting after the upload is done
'''
tfilehd,filename=tempfile.mkstemp()
tfile=os.fdopen(tfilehd,'w')
logging.error("import %s"%uploadfile)
for c in uploadfile.read():
tfile.write(c)
tfile.close()
from importFMPXML import importFMPXML
if not dsn:
dsn=self.getConnectionObj().connection_string
options=Options()
options.dsn=dsn
options.table=table
options.filename=filename
options.update_fields=update_fields
options.id_field=id_field
options.sync_mode=sync_mode
options.replace_table=replace
options.lc_names=True
options.ascii_db=ascii_db
importFMPXML(options)
os.remove(filename)
if RESPONSE and redirect_url:
RESPONSE.redirect(redirect_url)
def generateIndex(self,field,index_name,table,RESPONSE=None):
"""erzeuge index aus feld"""
index={}
founds=self.ZSQLSimpleSearch("""SELECT %s,oid FROM %s LIMIT 2000"""%(field,table))
for found in founds:
tmp=getattr(found,field,None)
if tmp:
strings=tmp.split(" ")
for string in strings:
if index.get(string):
index[string].append(found.oid)
else:
index[string]=[found.oid]
RESPONSE.write(string+"\n")
if not hasattr(self,index_name):
obj=ZSQLIndex(index,index_name,table)
self._setObject(index_name,obj)
self._getOb(index_name).setIndex(index)
def getIndex(self,index_name):
"""getIndex"""
founds=self.ZopeFind(self,obj_ids=[index_name])
return founds[0][1].getIndex()
def testneu(self):
"""test"""
relStatement="""period like '%s%%'"""
statement="select * from cdli_cat"
wherePart="museum_no like 'VAT%'"
classes=['Uruk III','Uruk IV']
return self.searchRel(relStatement,statement,wherePart,classes)
def URLquote(self,txt):
"""urlquote"""
return urllib.quote(txt)
def searchRel(self,relStatement,statement,wherePart,classes):
"""suche relative haufigkeiten"""
ret={}
allRecords=len(self.ZSQLSimpleSearch(statement + " where "+wherePart))
for oneclass in classes:
ret[oneclass]=len(self.ZSQLSimpleSearch(statement + " where ("+wherePart+") and "+ relStatement%oneclass))
return (ret,allRecords)
def content_html(self):
"""template fuer content"""
try:
obj=getattr(self,"ZSQLBibliography_template")
return obj()
except:
pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','ZSQLBibliography_template_standard.zpt'),content_type='text/html').__of__(self)
pt.content_type="text/html"
return pt()
def getWeight(self):
"""getLabe"""
try:
return self.weight
except:
return ""
def getLabel(self):
"""getLabe"""
try:
return self.label
except:
return ""
def getDescription(self):
"""getLabe"""
try:
return self.description
except:
return ""
manage_options=Folder.manage_options+(
{'label':'Main Config','action':'changeZSQLExtendForm'},
)
def changeZSQLExtendForm(self):
"""change folder config"""
pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','changeZSQLExtendForm.zpt')).__of__(self)
return pt()
def changeZSQLExtend(self,label,description,weight=0,REQUEST=None,connection_id=None):
"""change it"""
self.connection_id=connection_id
self.weight=weight
self.label=label
self.description=description
if REQUEST is not None:
return self.manage_main(self, REQUEST)
def formatAscii(self,str,url=None):
"""ersetze ascii umbrueche durch """
#url=None
str=str.rstrip().lstrip()
if url and str:
retStr=""
words=str.split("\n")
for word in words:
strUrl=url%word
#print "str",strUrl
retStr+="""%s """%(strUrl,word)
str=retStr
if str:
return re.sub(r"[\n]"," ",str)
else:
return ""
def getSAttribute(self,obj,atribute,pref=''):
"""get Attribute or emptystring"""
#print "obj",obj
try:
return pref+getattr(obj,atribute)
except:
return ""
def getS(self,str):
"""make none to empty string"""
if str:
return str
else:
return ""
def actualPath(self,url=None):
"""path"""
if self.REQUEST['HTTP_X_FORWARDED_SERVER']=='':
host=self.REQUEST['HTTP_HOST']
else:
host=self.REQUEST['HTTP_X_FORWARDED_SERVER']
if not url:
return "http://"+host+self.REQUEST['PATH_TRANSLATED']
else:
temp=self.REQUEST[url].split("/")
temp[2]=host
return string.join(temp,"/")
def getRequest(self):
"""request"""
return self.REQUEST
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.strip()=="":
return 1
return 0
def ZSQLMultiSearch(self,_table,_searchField,_value,_idField,_additionalStatement="",_select=None,_storename=None):
"""
Durchsucht in einer Tabelle "table" die Spalte "searchfield" nach dem allen Vorkommnissen
von Worten in value und gibt alle Werte mit gleichem id field zurŸck, d.h. es wird die "und" suche realisiert,
z.B. fŸr simplesearch ueber mehrere Felder
"""
if _storename:
"""store"""
else:
_storename="foundCount"
queries=[]
#baue jede einzelne abfrage
splitted=_value.split(" ")
if not _select:
_select=_idField
queries.append("select %s from %s %s where %s like '%%%s%%'"%(_select,_table,_additionalStatement,_searchField,splitted[0]))
if len(splitted)>1:
for v in splitted[1:]:
queries.append("select %s from %s where %s like '%%%s%%'"%(_idField,_table,_searchField,v))
q=" and %s in ("%_idField
query=q.join(queries) # nun baue sie zusammen
for i in range(len(queries)-1):
query+=")" #noch die klammern schliessen
if _additionalStatement:
query=query+" "
logging.info("ZSQLSimple: %s"%query)
retT=self.ZSQLSimpleSearch(query)
logging.info("ZSQLSimple: %s"%retT)
retFinalT={}
for x in retT:
split=_idField.split(".")
if len(split)>1:
f=split[1]
else:
f=_idField
retFinalT[getattr(x,f)]=x
ret=list(retFinalT.values())
if not self.REQUEST.SESSION.has_key(_storename):
self.REQUEST.SESSION[_storename]={}
self.REQUEST.SESSION[_storename]['searchFieldsOnly']={}
self.REQUEST.SESSION[_storename]['qs']=query
return ret
def ZSQLsearchOptions(self,fieldname=""):
"""return HTML Fragment with search options"""
ret=""""""%fieldname
return ret
def ZSQLSelectionFromCRList(self,fieldname,listField,boxType="checkbox",checked=None):
"""generate select options from a cr seperated list"""
fields=listField.split("\n")
ret=""
for field in fields:
if checked and (field in checked.split("\n")):
ret+="""%s"""%(fieldname,boxType,field.encode('utf-8'),field.encode('utf-8'))
else:
ret+="""%s"""%(fieldname,boxType,field.encode('utf-8'),field.encode('utf-8'))
return ret
def ZSQLSelectionFromSearchList(self,fieldname,results,fieldnameResult,boxType="checkbox",checked=None):
"""generate select options from a cr seperated list"""
ret=""
if not results: return ""
for result in results:
field=getattr(result,fieldnameResult)
if field:
if checked and (getattr(result,fieldnameResult) in checked.split("\n")):
ret+="""%s"""%(fieldname,boxType,field.encode('utf-8'),field.encode('utf-8'))
else:
ret+="""%s"""%(fieldname,boxType,field.encode('utf-8'),field.encode('utf-8'))
return ret
def ZSQLOptionsFromCRList(self,fieldname,listField, multiple='',start=None,startValue=None,size=None,selected=None):
"""generate select oprions form a cr seperated list"""
fields=listField.split("\n")
if size:
ret="""