from OFS.Folder import Folder
from Acquisition import Implicit
from Globals import DTMLFile,package_home,Persistent
import urllib
import re
import string
import sys
#from pyPgSQL import libpq
from AccessControl import getSecurityManager
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 zLOG
import os.path
import os
import copy
import unicodedata
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 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
zLOG.LOG("import xsd",zLOG.INFO,"called")
#fh=file("/tmp/fmpxml.xml")
import bz2
import base64
ret=""
if data:
data=bz2.decompress(base64.decodestring(data))
#zLOG.LOG("import xsd",zLOG.INFO,"received file")
doc=Parse(data)
#zLOG.LOG("import xsd",zLOG.INFO,"parsed file")
elif filename:
fh=file(filename)
txt=fh.read()
doc=Parse(txt)
#zLOG.LOG("import xsd",zLOG.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
zLOG.LOG("update xsd: fieldnames",zLOG.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')
zLOG.LOG("update xml: fieldname",zLOG.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'))
zLOG.LOG("update xsd: fieldname add",zLOG.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
zLOG.LOG("import xml",zLOG.INFO,"called")
#fh=file("/tmp/fmpxml.xml")
import bz2
import base64
ret=""
if data:
data=bz2.decompress(base64.decodestring(data))
zLOG.LOG("import xml",zLOG.INFO,"received file")
doc=parseString(data)
zLOG.LOG("import xml",zLOG.INFO,"parsed file")
elif filename:
fh=file(filename)
doc=parse(fh)
zLOG.LOG("import xml",zLOG.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)
zLOG.LOG("import xml",zLOG.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)
zLOG.LOG("update xml",zLOG.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)
zLOG.LOG("update xml",zLOG.INFO,queryStr)
return ret
def importXMLFile(self,table,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)
'''
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)
zLOG.LOG("import xml",zLOG.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)
zLOG.LOG("update xml",zLOG.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)
zLOG.LOG("update xml",zLOG.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'))
zLOG.LOG("update xml: fieldnames",zLOG.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:
zLOG.LOG("update xml: fieldname",zLOG.INFO,repr(fieldName))
if fieldName not in columns:
qstr="""alter table %s add %s %s"""
self.ZSQLSimpleSearch(qstr%(table,fieldName,'text'))
zLOG.LOG("update xml: fieldname add",zLOG.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,data=None,filename=None,update_fields=None,id_field=None,sync_mode=False,RESPONSE=None):
'''
Import FileMaker XML file (FMPXMLRESULT format) into the table.
@param table: name of the table the xml shall be imported into
@param data: xml data as bz2 string
@param filename: xmlfile filename
@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)
'''
from xml.dom.pulldom import parseString,parse
import transaction
ret = ""
if data:
data=bz2.decompress(base64.decodestring(data))
zLOG.LOG("fmpxml",zLOG.INFO,"received file")
doc=parseString(data)
zLOG.LOG("fmpxml",zLOG.INFO,"parsed file")
elif filename:
fh=file(filename)
zLOG.LOG("fmpxml",zLOG.INFO,"reading file")
doc=parse(fh)
zLOG.LOG("fmpxml",zLOG.INFO,"parsed file")
dbIDs = {}
rowcnt = 0
if id_field is not None:
# prepare a list of ids for sync mode
qstr="select %s from %s"%(id_field,table)
for id in self.ZSQLSimpleSearch(qstr):
# value 0: not updated
dbIDs[id[0]] = 0;
rowcnt += 1
zLOG.LOG("fmpxml",zLOG.INFO,"%d entries in DB to sync"%rowcnt)
fieldNames = []
rowcnt = 0
id_val = ''
while 1:
node=doc.getEvent()
if node is None:
break;
# METADATA tag defines number and names of fields in FMPXMLRESULT
if node[1].nodeName == 'METADATA':
doc.expandNode(node[1])
names=node[1].getElementsByTagName('FIELD')
for name in names:
fn = name.getAttribute('NAME')
fieldNames.append(fn)
if update_fields is None:
# update all fields
update_fields = fieldNames
zLOG.LOG("fmpxml fieldnames:",zLOG.INFO,repr(fieldNames))
# get list of fields in db table
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)]
# adjust db table to fields in XML and fieldlist
for fieldName in fieldNames:
zLOG.LOG("fmpxml fieldname:",zLOG.INFO,repr(fieldName))
if (fieldName not in columns) and (fieldName in update_fields):
qstr="""alter table %s add %s %s"""
self.ZSQLSimpleSearch(qstr%(table,fieldName,'text'))
zLOG.LOG("fmpxml add field:",zLOG.INFO,qstr%(table,fieldName,'text'))
# ROW tags (in RESULTSET tag) hold data
elif node[1].nodeName == 'ROW':
rowcnt += 1
doc.expandNode(node[1])
cols=node[1].getElementsByTagName('COL')
dataSet={}
i = 0
# populate with data
for col in cols:
data=col.getElementsByTagName('DATA')
dataSet[fieldNames[i]] = getTextFromNode(data[0])
i += 1
update=False
# synchronize by id_field
if id_field:
id_val=dataSet[id_field]
if id_val in dbIDs:
dbIDs[id_val] += 1
update=True
if update:
# update existing row (by id_field)
setvals=[]
for fieldName in update_fields:
setvals.append("%s = %s"%(fieldName,self.ZSQLQuote(dataSet[fieldName])))
setStr=string.join(setvals, ',')
id_val=dataSet[id_field]
qstr="""UPDATE %s SET %s WHERE %s = '%s' """%(table,setStr,id_field,id_val)
#zLOG.LOG("fmpxml update:",zLOG.INFO,queryStr)
self.ZSQLSimpleSearch(qstr)
ret+="up: %s \n"%id_val
else:
# create new row
fields=string.join(update_fields, ',')
values=string.join([" %s "%self.ZSQLQuote(dataSet[x]) for x in update_fields], ',')
qstr="""INSERT INTO %s (%s) VALUES (%s)"""%(table,fields,values)
self.ZSQLSimpleSearch(qstr)
#zLOG.LOG("fmpxml: insert",zLOG.INFO,queryStr)
ret+="ad: %s \n"%dataSet.get(id_field, rowcnt)
#zLOG.LOG("fmpxml row:",zLOG.INFO,"%d (%s)"%(rowcnt,id_val))
if (rowcnt % 10) == 0:
zLOG.LOG("fmpxml row:",zLOG.INFO,"%d (%s)"%(rowcnt,id_val))
transaction.commit()
transaction.commit()
if sync_mode:
# delete unmatched entries in db
for id in dbIDs.keys():
# find all not-updated fields
if dbIDs[id] == 0:
zLOG.LOG("fmpxml delete:",zLOG.INFO,id)
qstr = "DELETE FROM %s WHERE %s = '%s'"
self.ZSQLSimpleSearch(qstr%(table,id_field,id))
elif dbIDs[id] > 1:
zLOG.LOG("fmpxml sync:",zLOG.INFO,"id used more than once?"+id)
transaction.commit()
return ret
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=="":
return 1
return 0
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="""