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 unicodify(str):
"""decode str (utf-8 or latin-1 representation) into unicode object"""
if not str:
return u""
if type(str) is StringType:
try:
return str.decode('utf-8')
except:
return str.decode('latin-1')
else:
return str
def utf8ify(str):
"""encode unicode object or string into byte string in utf-8 representation"""
if not str:
return ""
if type(str) is StringType:
return str
else:
return str.encode('utf-8')
def setPsycopg2UseUnicode():
"""force Psycopg2DA to return unicode objects"""
try:
import psycopg2
import psycopg2.extensions
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
except:
logging.error("Unable to force psycopg2 to use unicode")
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):
"""Klasse die Methoden fuer die Abfrage einer SQL-Datenbank zur Verfuegung stellt.
"""
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, d.h. entfernt alle diakritischen Zeichen und ersetzt diese
durch den Grundbuchstaben in einer Spalte einer Tabelle
@param table: Tabellename
@param fieldname: Name der Spalte
@param newFieldName: (optional) default ist fieldname+"_normal"
@param mode: (optional) default ist "alter". Mode "alter" aendert ein bestehendes Feld newFieldName, mode "create" erzeugt diese zuerst.
"""
import unicodedata
if not newFieldName:
newFieldName=fieldname+"_normal"
#normalisierungs routine
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 importXMLFileFMP(self,table,dsn=None,uploadfile=None,update_fields=None,id_field=None,sync_mode=False,
lc_names=True,keep_fields=False,ascii_db=False,replace=False,backup=False,
debug=False,log_to_response=False,
redirect_url=None,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 lc_names: (optional) lower case and clean up field names from XML
@param keep_fields: (optional) don't add fields to SQL database
@param ascii_db: (optional) assume ascii encoding in db
@param replace: (optional) delete and re-insert data
@param backup: (optional) create backup of old table (breaks indices)
@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.info("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.lc_names=lc_names
options.replace_table=replace
options.keep_fields=keep_fields
options.ascii_db=ascii_db
options.replace_table=replace
options.backup_table=backup
options.debug=debug
if RESPONSE and log_to_response:
# set up logging to response as plain text
RESPONSE.setHeader("Content-Type","text/plain; charset=utf-8")
RESPONSE.write("Import FMPXML file...\n\n")
loghandler = logging.StreamHandler(RESPONSE)
if debug:
loghandler.setLevel(logging.DEBUG)
else:
loghandler.setLevel(logging.INFO)
logger = logging.getLogger('db.import.fmpxml')
logger.addHandler(loghandler)
options.use_logger_instance = logger
importFMPXML(options)
os.remove(filename)
if RESPONSE and log_to_response:
loghandler.flush()
RESPONSE.write("\n\n DONE!")
return
if RESPONSE and redirect_url:
RESPONSE.redirect(redirect_url)
def generateIndex(self,field,index_name,table,RESPONSE=None):
"""erzeuge ein Index Objekt einem Feld (experimental)
@param field: Feldname zu dem ein Index erzeugt werden soll
@param index_name: Name des Index
@param table: Tabellen name"""
index={}
founds=self.ZSQLSimpleSearch("""SELECT %s,oid FROM %s """%(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 from index_name
return an indexObject with index_name
"""
founds=self.ZopeFind(self,obj_ids=[index_name])
return founds[0][1].getIndex()
def URLquote(self,txt):
"""urlquote"
@param txt: text der urlgequoted werden soll.
"""
return urllib.quote(txt)
def createIdSet(self, resultset, idField=None):
"""returns a (frozen)set of IDs from a SQL-resultset (using idField) or a list (if idField=None)"""
if idField is None:
return frozenset(resultset)
else:
idlist = [r[idField] for r in resultset]
return frozenset(idlist)
def opIdSet(self, a, b, op):
"""operate on sets a and b"""
if (op == 'intersect'):
return a.intersection(b)
elif (op == 'union'):
return a.union(b)
elif (op == 'diff'):
return a.difference(b)
def searchRel(self,relStatement,statement,wherePart,classes):
"""suche relative haufigkeiten (experimental)"""
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_html Aufruf, notwendig fuer Kompatibiliaet bei gemeinsamem Einsatz mich ECHO-Produkt"""
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):
"""getWeight, gewicht notwendig fuer Kompatibiliaet bei gemeinsamem Einsatz mich ECHO-Produkt"""
try:
return self.weight
except:
return ""
def getLabel(self):
"""getLabel notwendig fuer Kompatibiliaet bei gemeinsamem Einsatz mich ECHO-Produkt"""
try:
return self.label
except:
return ""
def getDescription(self):
"""getDEscription: notwendig fuer Kompatibiliaet bei gemeinsamem Einsatz mich ECHO-Produkt"""
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,connection_id=None,REQUEST=None,):
"""change the Konfiguration"""
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
@param str: string der Formatiert werden soll.
@param url: (optional) default ist "None", sonderfall erzeugt einen Link aus String mit unterliegender url
"""
#logging.debug("formatascii str=%s url=%s"%(repr(str),repr(url)))
if not str:
return ""
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:
retStr = re.sub(r"[\n]"," ",str)
#logging.debug("formatascii out=%s"%(repr(retStr)))
return retStr
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,str):
"""Teste ob String leer bzw. none ist.
"""
#print "field",field
if not str:
return 1
if str.strip()=="":
return 1
return 0
def ZSQLMultiSearch(self,_table,_searchField,_value,_idField,_additionalStatement="",_select=None,_subselectAddition="",_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 Ÿber mehrere Eintrsege in einer
Tabelle mit gleichem idField werd realisiert,
z.B. fŸr simplesearch ueber mehrere Felder
@param _table: Tabelle, die durchsucht werden soll.
@param _searchField: Feld, das durchsucht wird
@param _value: String der gesucht werden soll, gesucht wird nach allen Worten des Strings, die durch " "-getrennt sind.
@param _idField: Feld mit id fŸr die identifikation gleicher EintrŠge
@param _additionalStatement: (optional) Zusaetzliches SQL Statement, dass zwischen dem ersten "select from" und dem ersten "where" eingegefŸgt wird.
@param _select: (optional) Alternativer Wert fŸr den ersten SELECT Aufruf.
@param _storename: (optional) Name fuer die Zwischenspeicherung von Werten in der Session
"""
if _storename:
"""store"""
else:
_storename="foundCount"
queries=[]
#baue jede einzelne abfrage
splitted=_value.split(" ")
if not _select:
_select=_idField
query="select %s from %s %s where lower(%s) like '%%%s%%'"%(_select,_table,_additionalStatement,_searchField,splitted[0].lower())
if len(splitted)>1: # mehr als ein Wort
query+=" and %s in"%_idField # dann einschraenken
for v in splitted[1:]:
queries.append("select %s from %s %s where lower(%s) like '%%%s%%'"%(_idField,_table,_subselectAddition,_searchField,v.lower()))
intersect=" intersect ".join(queries) # nun baue sie zusammen
query+="(%s)"%intersect
logging.info("ZSQLSimple: %s"%query)
retT=self.ZSQLSimpleSearch(query)
logging.info("ZSQLSimple: %s"%retT)
#das Ergebis enthaelt unter u.U. eine id mehrfach, dieses wir jetzt vereinheitlicht.
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())
#aus Kompatibilaetsgruenen mit ZSQLSearch / ZSQLInlineSeach noch einzelne Felder in der SESSION belegen.
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 selection HTML Fragemnt from a cr seperated list
@param fieldname: Wert fuer das "name"-Attribute der erzeugten input-Tags
@param listField: "cr" (\n) getrennte Liste der Werte
@param boxType: (optional) default ist "checkbox", moegliche Werte "checkbox" und "radio"
@param checked: "cr" getrennt Liste von Werten aus listField, die als ausgewahlt markiert werden sollen.
"""
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 research-results Objekt
generate selection HTML Fragemnt from a cr seperated list
@param fieldname: Wert fuer das "name"-Attribute der erzeugten input-Tags
@param results: result Object einer SQL-suche
@param fieldNameResult: Feldname des Resultobjekts, das angezeigt werden soll.
@param boxType: (optional) default ist "checkbox", moegliche Werte "checkbox" und "radio"
@param checked: "cr" getrennt Liste von Werten aus results.fieldNameResult, die als ausgewahlt markiert werden sollen.
"""
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="""