ZSQLExtend/ZSQLExtend.py - view

File: [Repository] / ZSQLExtend / ZSQLExtend.py
Revision 1.144: download - view: text, annotated - select for diffs - revision graph
Mon Sep 3 13:06:11 2012 UTC (11 years, 8 months ago) by dwinter
Branches: MAIN
CVS tags: HEAD

ohne osas
ohne amara

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 import psycopg2 from AccessControl import getSecurityManager,Unauthorized from Products.PageTemplates.ZopePageTemplate import ZopePageTemplate from Products.PageTemplates.PageTemplateFile import PageTemplateFile from Products.ZSQLMethods.SQL import SQLConnectionIDs from Shared.DC.ZRDB.Results import Results 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" #primaryKeys={} #primaryKey= def getSQLConnectionIDs(self): logging.debug(SQLConnectionIDs(self)) return SQLConnectionIDs(self) def getPrimaryKey(self,table=None): """returns primary key for the database""" if table is None: return getattr(self,'primaryKey','oid') pks= getattr(self, "primaryKeys",{}) k = pks.get(table,None) if k is None: logging.debug("getPrimaryKey: no primary key for table %s stored use standard key %s"%(table,getattr(self,'primaryKey','oid'))) logging.debug(pks) return getattr(self,'primaryKey','oid') return k def getPrimaryKeysAsString(self): """get all keys""" if not hasattr(self, 'primaryKeys'): return "" return ";".join(["%s:%s"%val for val in self.primaryKeys.items()]) def ZSQLQuote(self,str): """quote str for sql""" return sql_quote(str) def unicodify(self, s): """return unicode object for string (utf-8 or latin1) or unicode object s""" return unicodify(s) def utf8ify(self, s): """return utf-8 encoded string object for string or unicode object s""" return utf8ify(s) 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 %s from %s"%(fieldname,table) for result in self.ZSQLSimpleSearch(qstr): qstr="update %s set %s = %s where "+self.getPrimaryKey()+" = %s" self.ZSQLSimpleSearch(qstr%(table,newFieldName,self.ZSQLQuote(normal(getattr(result,fieldname))),getattr(result,self.getPrimaryKey))) 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 "<html><body>DONE</body></html>" 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 (may be comma-separated list) @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("importXMLFileFMP: importing %s"%uploadfile) for c in uploadfile.read(): tfile.write(c) tfile.close() from importFMPXML import importFMPXML if not dsn: dsn=self.getConnectionObj().connection_string logging.debug("dsn: %s"%dsn) logging.debug("table: %s"%table) logging.debug("update_fields: %s"%update_fields) logging.debug("id_field: %s"%id_field) logging.debug("sync_mode: %s"%sync_mode) logging.debug("lc_names: %s"%lc_names) logging.debug("keep_fields: %s"%keep_fields) logging.debug("ascii_db: %s"%ascii_db) logging.debug("replace: %s"%replace) logging.debug("backup: %s"%backup) logging.debug("debug: %s"%debug) logging.debug("log_to_response: %s"%log_to_response) logging.debug("RESPONSE: %s"%repr(RESPONSE)) tablelist=table.split(',') logging.debug("tablelist: %s"%tablelist) #table=tables for t in tablelist : logging.debug("table: %s"%table) options=Options() options.dsn=dsn options.table=t 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 logging.debug(options) if RESPONSE and log_to_response: # set up logging to response as plain text logging.debug("Setting up logging to RESPONSE") RESPONSE.setHeader("Content-Type","text/plain; charset=utf-8") RESPONSE.write("Import FMPXML file...\n\n") RESPONSE.flush() 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 try: err = None importFMPXML(options) logging.info("importXMLFileFMP: done") except Exception, err: logging.error("Error importing: %s"%err) if RESPONSE and log_to_response: loghandler.flush() if err is not None: RESPONSE.write("\n\nERROR while importing: %s"%err) else: RESPONSE.write("\n\n DONE!") elif RESPONSE and redirect_url: RESPONSE.redirect(redirect_url) os.remove(filename) 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 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(gettr(found,self.getPrimaryKey())) else: index[string]=[gettr(found,self.getPrimaryKey())] 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)""" logging.debug("createidset for idfield %s"%idField) 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""" logging.debug("opidset with op %s"%op) 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,primaryKey,primaryKeys="",weight=0,connection_id=None,autocommit=None,REQUEST=None,): """change the Konfiguration""" self.connection_id=connection_id self.weight=weight self.label=label self.description=description self.autocommit = (autocommit == "on") self.primaryKey=primaryKey if primaryKeys.lstrip().rstrip()!="": for vals in primaryKeys.split(";"): splitted=vals.split(":") if len(splitted)<2: if REQUEST is not None: return """<html><body>Wrong Syntax of keystring: %s<br/> %s is not of the form TABLE:key.</body></html>"""%(primaryKeys,vals) self.primaryKeys[splitted[0]]=splitted[1] if REQUEST is not None: return self.manage_main(self, REQUEST) def formatAscii(self,str,url=None): """ersetze ascii umbrueche durch <br> @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+="""<a href="%s">%s</a><br/>"""%(strUrl,word) str=retStr if str: retStr = re.sub(r"[\n]","<br/>",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 zurueck, d.h. es wird die "und" suche ueber mehrere Eintrsege in einer Tabelle mit gleichem idField werd realisiert, z.B. fuer 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 fuer die identifikation gleicher Eintraege @param _additionalStatement: (optional) Zusaetzliches SQL Statement, dass zwischen dem ersten "select from" und dem ersten "where" eingegefuegt wird. @param _subselectAddition: (optiona) Zusaetliche SQL Statement die hinter das select statement der subselects eingefuegt werde. @param _select: (optional) Alternativer Wert fuer 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="""<select name="-op_%s"> <option value="bw">begins with</option>  <option value="ew">ends with</option> <option value="ct" selected>contains</option>  <option value="eq">equals</option>  </select>"""%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+="""<input name="%s" type="%s" value="%s" checked>%s"""%(fieldname,boxType,field.encode('utf-8'),field.encode('utf-8')) else: ret+="""<input name="%s" type="%s" value="%s">%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+="""<input name="%s" type="%s" value="%s" checked>%s"""%(fieldname,boxType,field.encode('utf-8'),field.encode('utf-8')) else: ret+="""<input name="%s" type="%s" value="%s">%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="""<select name="%s"%s size="%s" > """%(fieldname,multiple,size) else: ret="""<select name="%s"%s > """%(fieldname,multiple) if start: if start==' ': start='' if not startValue: startValue=start ret+="""<option value="%s" >%s</option>"""%(startValue,start) for field in fields: if selected and (field in selected.split("\n")): ret+="""<option selected value="%s">%s</option>"""%(field.encode('utf-8'),field.encode('utf-8')) else: ret+="""<option value="%s">%s</option>"""%(field.encode('utf-8'),field.encode('utf-8')) ret+="""</select>""" return ret def ZSQLOptionsFromSearchList(self,fieldname, results,fieldName, valueName=None,start=None, multiple='',startValue=None, additionalSelect="",size=None, linelen=None,selected=None, clear=False): """generate select options form a search list es wird <select name=fieldname mutiple> <option value=startValue>start</option> <option value=result.fieldName>result.fieldValue</option> erzeugt. @param fieldname: Name fuer name-wert im select-tag @param results: Resultobject einer SQL-suche @param fieldName: Name des Feldes, das als value in den option-tag geschrieben werden soll. @param valueName: (optional) Name des Feldes, dass als im option-tag ausgegeben wird, default wert ist valueName=fieldName @param start: (optional) falls zusaetzliches option tag erzeugt werden soll, gibt start an was im option tag steht @param startValue (optional): gibt den entsprechenden Wert an. @param selected (optional): Wert der ausgewaehlt sein soll. @param linelen: (optional) maximale laenge eines Eintrages der im Klappmenue noch angezeigt wird, laengeres wird abgeschnitten. @param addionalSaelect (optional): zusaetzlicher text fuer den select tag @param clear (optional): setze auf den startwert. """ if not valueName: valueName=fieldName if size: ret="""<select name="%s" %s size="%s" %s> """%(fieldname,multiple,size,additionalSelect) else: ret="""<select name="%s" %s %s> """%(fieldname,multiple,additionalSelect) if start: if start==' ': start='' if not startValue: startValue=start if clear: ret+="""<option selected value="%s" >%s</option>"""%(startValue,start) else: ret+="""<option value="%s" >%s</option>"""%(startValue,start) for result in results: field=getattr(result,fieldName) fieldValue=getattr(result,valueName) if linelen and fieldValue and (len(fieldValue) > string.atoi(linelen)): displayValue = fieldValue[:string.atoi(linelen)] else: displayValue = fieldValue if displayValue: #show only if value not none if field == selected: ret+="""<option value="%s" selected>%s</option>"""%(field,displayValue) else: ret+="""<option value="%s">%s</option>"""%(field,displayValue) ret+="""</select>""" return ret def ZSQLInlineSearchU(self,storename=None,args=None,**argv): """one element if exists""" qs=[] if storename: """store""" storename=storename else: storename="foundCount" if args: argTmp=args else: argTmp=argv #print "INLINE:",argv for a in argTmp.keys(): aFiltered=re.sub(r"^-","_",a) # beginning of a command should always be "_" qs.append(aFiltered+"="+urllib.quote(str(argTmp[a]))) #return [] ret = self.parseQueryString(string.join(qs,","),"_",storename=storename) try: return ret[0] except: return None def ZSQLSequentialSearch(self,_fieldlist,_searchTerm,storename=None,args=None,**argv): """get a list of tuples (fields,searchoptions,table) to search the searchTerm in, returns dictionary. """ #print "do search with:",_fieldlist,_searchTerm,storename,args,argv ret={} if args: argTmp=args else: argTmp=argv for field in _fieldlist: argTmp2=copy.deepcopy(argTmp) argTmp2[field[0]]=_searchTerm argTmp2['_op_'+field[0]]=field[1] argTmp2['_table']=field[2] ret[field[0]]=(self.ZSQLInlineSearch(storename=storename,args=argTmp2),field[3],field[4],field[5],field[6]) return ret def ZSQLInlineSearch(self,storename=None,args=None,**argv): """inlinesearch""" #logging.debug("ZSQLInlineSearch args=%s argv=%s"%(args,argv)) qs=[] if storename: """store""" else: storename="foundCount" if args: argTmp=args else: argTmp=argv #print "INLINE:",argv for a in argTmp.keys(): aFiltered=re.sub(r"^-","_",a) # beginning of a command should always be "_" if type(argTmp[a]) is ListType: # ein parameter zweimal value="" #TODO find a better solution, currently only the last non empty entry is used. #for x in argTmp[a]: # if x: # value=x # version: join with spaces (makes sense with checkbox and -op=all) value = " ".join(argTmp[a]) else: try: value=str(argTmp[a]) except: value=utf8ify(argTmp[a]) qs.append(aFiltered+"="+urllib.quote(value)) #logging.debug("InlineSearch:"+string.join(qs,",")) #return [] return self.parseQueryString(string.join(qs,","),"_",storename=storename) def ZSQLInlineSearch2(self,query): """inlinesearch""" qs=[] #print "INLINE:",query return self.ZSQLSimpleSearch(query) def ZSQLResetConnection(self): """reset the connectione""" try: self.getConnectionObj().manage_close_connection() except: logger("ZSQLResetConnection",logging.ERROR, '%s %s'%sys.exc_info()[:2]) try: self.getConnectionObj().manage_open_connection() except: logger("ZSQLResetConnection",logging.ERROR, '%s %s'%sys.exc_info()[:2]) def ZSQLSimpleSearch(self,query=None,max_rows=1000000): """new simple search""" logging.debug("new ZSQLSimpleSearch %s"%query) # get Connection instance con = self.getConnectionObj() # call to get db object dbc = con() if getattr(self, 'autocommit', False): # force transaction isolation level (for psycopg2 0=autocommit) logging.debug(" old tilevel=%s"%dbc.tilevel) dbc.tilevel = 0 # modified code from ZPsycopgDA.db without _register: c = dbc.getcursor() desc = () r = [] try: try: c.execute(query) except psycopg2.OperationalError: #logging.exception("Operational error on connection, closing it.") try: # Only close our connection dbc.putconn(True) except: #logging.debug("Something went wrong when we tried to close the pool", exc_info=True) pass if c.description is not None: if max_rows: r = c.fetchmany(max_rows) else: r = c.fetchall() desc = c.description dbc.failures = 0 except StandardError, err: raise err res = (dbc.convert_description(desc), r) else: logging.debug(" no autocommit") # just use DA's query method res = dbc.query(query, max_rows=max_rows) # return result set as Result object with Brains return Results(res) def oldZSQLSimpleSearch(self,query=None,max_rows=1000000): """simple search""" logging.error("ZSQLSimpleSearch X %s"%query) #print query if not query: query=self.query if (hasattr(self,"_v_searchSQL") and (self._v_searchSQL == None)) or (not hasattr(self,"_v_searchSQL")): self._v_searchSQL=Shared.DC.ZRDB.DA.DA("_v_searchSQL","_v_searchSQL",self.getConnectionObj().getId(),"var","<dtml-var var>") #self._v_searchSQL=self.getConnectionObj()() self._v_searchSQL.max_rows_=max_rows #self._v_searchSQL.set_client_encoding('UNICODE') try: logging.error("I am here") t=self._v_searchSQL.__call__(var=query) #t=self._v_searchSQL.query(query) logging.error("I am here %s"%t) return t except : logger("ZSQLSimpleSearch ERROR1",logging.ERROR, '%s %s'%sys.exc_info()[:2]) if sys.exc_info()[0]=="Database Error": try: self.getConnectionObj().manage_open_connection() except: logger("ZSQLSimpleSearch ERROR2",logging.ERROR, '%s %s'%sys.exc_info()[:2]) else: try: self._v_searchSQL.max_rows_=max_rows #self._v_searchSQL.set_client_encoding('UNICODE') return self._v_searchSQL.__call__(var=query) #return self._v_searchSQL.query(query) except : logger("ZSQLSimpleSearch ERROR2",logging.ERROR, '%s %s'%sys.exc_info()[:2]) if sys.exc_info()[0]=="Database Error": try: self.getConnectionObj().manage_open_connection() except: logger("ZSQLSimpleSearch",logging.ERROR, '%s %s'%sys.exc_info()[:2]) def getConnectionObj(self): if hasattr(self,'connection_id'): return getattr(self,self.connection_id) def ZSQLSimpleSearch2(self,query=None): """ returrn SQLSearch""" if not query: query=self.query if getattr(self,'_v_search',None): return self._v_search(var=query) if hasattr(self,'search') and (self.search.meta_type=="Z SQL Method"): self._v_search=self.search return self.search(var=query) else: if hasattr(self.aq_parent.aq_parent,'search') and (self.aq_parent.aq_parent.search.meta_type=="Z SQL Method"): self._v_search=self.aq_parent.aq_parent.search return self.aq_parent.aq_parent.search(var=query) else: search=self.ZopeFind(self,obj_metatypes=["Z SQL Method"],search_sub=1) if search: self._v_search=search[0][1] return search[0][1](var=query) else: return [] def ZSQLAdd(self,format=None,RESPONSE=None,args=None,_useRequest=True,**argv): """Neuer Eintrag""" if args: argTmp=args else: argTmp=argv qs_temp=[] if _useRequest: for a in self.REQUEST.form.keys(): qs_temp.append(a+"="+urllib.quote(str(self.REQUEST.form[a]))) qs=string.join(qs_temp,",") for field in argTmp.keys(): if field[0]=="_": fieldTmp="-"+field[1:] else: fieldTmp=field qs+=",%s=%s"%(fieldTmp,argTmp[field]) addList={} for q in qs.split(","): if len(q.split("="))<2: continue 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]=="-" or 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(sql_quote(addList[x])) keyString=string.join(keyList,",") valueString=string.join(valueList,",") queryString="INSERT INTO %s (%s) VALUES (%s)"%(table,keyString,valueString) self.ZSQLSimpleSearch(queryString) if RESPONSE and format: return RESPONSE.redirect(format) else: return True def ZSQLChange(self,format=None,RESPONSE=None,USE_FORM=None,args=None,**argv): """change entries""" #qs=self.REQUEST['QUERY_STRING'] # very bad hack qs_temp=[] if USE_FORM or RESPONSE: for a in self.REQUEST.form.keys(): qs_temp.append(a+"="+urllib.quote(str(self.REQUEST.form[a]))) if args: arg_tmp=args else: arg_tmp=argv for field in arg_tmp.keys(): if field[0]=="_": fieldTmp="-"+field[1:] else: fieldTmp=field qs_temp.append("%s=%s"%(fieldTmp,arg_tmp[field])) changeList=[] logging.info("ZSQLChange qs_temp: %s"%repr(qs_temp)) for q in qs_temp: name=urllib.unquote(re.sub("r'+'"," ",q.split("=")[0].lower())) value="=".join(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) # old code did identify with lower() which doesn't work for oids #identify="lower("+identify.split("=")[0]+")="+sql_quote(identify.split("=")[1].lower()) (k,v) = identify.split("=") identify="%s=%s"%(k,sql_quote(v)) elif name=="-format": format=urllib.unquote(value) #elif (not (name[0]=="-" or name[0]=="_")) and (not len(value)==0): elif (not (name[0]=="-" or name[0]=="_")): if value=="": changeList.append("\""+name+"\"=null") else: changeList.append("\""+name+"\"="+sql_quote(urllib.unquote(value))) changeString=string.join(changeList,",") queryString="UPDATE %s SET %s WHERE %s"%(table,changeString,identify) logger("ZSQLExtend",logging.INFO,"CHANGE: "+queryString) self.ZSQLSimpleSearch(queryString) if RESPONSE and format: return RESPONSE.redirect(format) else: return True def ZSQLFindIndexed(self,tableList=[],qs="",select="*",storename=None,indexedFields=['data_line'],restrictField='id_text',**argv): """find2""" for index in self.ZopeFind(self,obj_ids=indexedFields): txt=argv.get(index[0],None) if txt: oids=index[1].getIndex()[txt] search1= self.ZSQLFind(qs=qs,select=select,storename=storename,tableExt=tableList[1],restrictField=restrictField,NoQuery='yes',NoLimit='yes',**argv) search2 = self.ZSQLFind(tableExt=tableList[0],qs=qs,select=select,storename=storename,restrictConnect=(tableList[0]+"."+restrictField,search1),**argv) return search2 def ZSQLFind2(self,qs="",select="*",storename=None,tableList=['cdli_translit','cdli_cat'],restrictField='id_text',**argv): """find2""" search1= self.ZSQLFind(qs=qs,select=select,storename=storename,tableExt=tableList[1],restrictField=restrictField,NoQuery='yes',NoLimit='yes',**argv) search2 = self.ZSQLFind(tableExt=tableList[0],qs=qs,select=select,storename=storename,restrictConnect=(tableList[0]+"."+restrictField,search1),**argv) return search2 def ZSQLFind(self,qs="",select="*",storename="foundCount",tableExt=None,NoQuery=None,NoLimit=None,restrictField=None,restrictConnect=None,filter=None,**argv): """search in database""" def delEmpty(list): """loesche leere elemente aus der liste""" ret=[] for x in list: splitted=x.split("=") if (len(splitted)>1) and not (splitted[1]==' '): ret.append(x) return ret if qs=="": #kein querystring ubergeben if self.REQUEST['QUERY_STRING']: qs=self.REQUEST['QUERY_STRING'] qs=string.join(qs.split("&"),",") for field in argv.keys(): if field[0]=="_": fieldTmp="-"+field[1:] else: fieldTmp=field qs+=",%s=%s"%(fieldTmp,argv[field]) else: qs=self.REQUEST.SESSION.get('query','') for field in argv.keys(): if field[0]=="_": fieldTmp="-"+field[1:] else: fieldTmp=field qs+=",%s=%s"%(fieldTmp,argv[field]) else: self.REQUEST['QUERY_STRING']=qs qs=string.join(qs.split("&"),",") qs=re.sub("\\+"," ",qs)#TODO: Austauschen da Leerzeichen bei http-get durch + ersetzt wird, generell sollte alles auf post umgeschrieben werden. vom search formular. qs=string.join(delEmpty(qs.split(",")),",") #store query for further usage #TODO: erste der beiden ist ueberfluessig self.REQUEST.SESSION['query']=qs if not self.REQUEST.SESSION.has_key(storename): self.REQUEST.SESSION[storename]={} self.REQUEST.SESSION[storename]['qs']=qs #print "calling Query with",repr(NoQuery) ret=self.parseQueryString(qs,"-",select=select,storemax="yes",storename=storename,tableExt=tableExt,NoQuery=NoQuery,NoLimit=NoLimit,restrictField=restrictField,restrictConnect=restrictConnect,filter=filter) #print self.REQUEST.SESSION["foundCount"] return ret def ZSQLFoundCountLen(self,var): return len(var) def ZSQLFoundCount(self,qs="",select="*",storename="foundCount"): return self.REQUEST.SESSION[storename]['count'] def ZSQLRangeStart(self,storename="foundCount"): return int(self.REQUEST.SESSION[storename]['rangeStart'])+1 def ZSQLRangeSize(self,storename="foundCount"): return self.REQUEST.SESSION[storename]['rangeSize'] def ZSQLRangeEnd(self,storename="foundCount"): return str(min(int(self.REQUEST.SESSION[storename]['rangeEnd']),int(self.REQUEST.SESSION[storename]['count']))) def ZSQLNewQuery(self,linkText,storename="foundCount",**argv): """suche neu""" return self.ZSQLNewSearch(linkText,storename,url=self.REQUEST['URL'],args=argv) def ZSQLNewSearch(self,linkText,storename="foundCount",url=None,args=None,**argv): """suche mit alten parametern bis auf die in argv getauschten""" str = self.ZSQLNewSearchURL(storename, url, args, **argv) return """<a href="%s"> %s</a>"""%(str,linkText) def ZSQLNewSearchURL(self, storename="foundCount",url=None,args=None,**argv): """suche mit alten parametern bis auf die in argv getauschten""" if args: argv=args #get the old queries qs=self.REQUEST.SESSION[storename]['qs'] querys=qs.split(",") #which arguments are in the old query string queryList={} for query in querys: arg=query.split("=")[0] if arg[0]=="_": arg="-"+arg[1:] # sicherstellen, dass an Anfang stets "_" try: queryList[arg]=urllib.unquote_plus(query.split("=")[1]) except: queryList[arg]='' argList=[] arg="" #gehe durch die zu aendernden Argumente for argTmp in argv.keys(): arg=argTmp[0:]# sicherstellen, dass der string auh kopiert wird if arg[0]=="_": arg="-"+arg[1:] # sicherstellen, dass an Anfang stets "_" queryList[arg]=argv[argTmp] if url: str=url+"?"+urllib.urlencode(queryList) else: str="ZSQLSearch?"+urllib.urlencode(queryList) return str def parseQueryString(self,qs,iCT,storemax="no",select=None,nostore=None,storename="foundCount",tableExt=None,NoQuery=None,NoLimit=None,restrictField=None,restrictConnect=None,filter=None): """analysieren den QueryString""" logging.debug("parseQueryString qs=%s"%qs) #setzte generische werte lop="AND" # standardsuche mit and max="ALL" #standard alle auswaehlen maxstr="" whereList=[] sort="" op="bw" opfields={} lopfields={} #Verknuepfung bei mehrfachauswahl von einem feld sortfields={} #order of sortfields diacritics={} #diacritische zeichen, falls "no", dann werden diese fuer das feld gefiltert. sortAllFields=None skip="" rangeStart=0 limit=0 searchFields={} searchFieldsOnly={} queryTemplate=[] outerjoin="" debug=None if not select: select="*" #check for op splitted=qs.split(",") if tableExt: table=tableExt if restrictField: select=restrictField params={} for q in splitted: name=re.sub("r'+'"," ",q.split("=")[0].lower()) if name=="_debug": debug=True try: value=urllib.unquote(q.split("=",1)[1]) except: value="" params[name]=value #set table primaryKey="" if not tableExt: table=params.get(iCT+'table') primaryKey=self.getPrimaryKey(table) logging.debug("table:"+table) logging.debug("primkey:"+primaryKey); #erster durchgang suche operatoren for name,value in params.items(): # name=re.sub("r'+'"," ",q.split("=")[0].lower()) # if name=="_debug": # debug=True # # try: # value=urllib.unquote(q.split("=",1)[1]) # except: # value="" #print "Hi",name[0:3],q if name[0:3]==iCT+"op": op=value field=name[4:] opfields[field]=op if name[0:4]==iCT+"lop": lop=value field=name[5:] lopfields[field]=lop if name[0:11]==iCT+"diacritics": field=name[12:] diacritics[field]=value if name[0:10]==iCT+"sortorder": #sort=value field=name[11:] sortAllFields=None #no field selected if field=="": sortAllFields=value else: sortfields[field]=value #zweiter durchgang analysiere felder for name,value in params.items(): # # name=re.sub("r'+'"," ",q.split("=")[0].lower()) # # try: # value=urllib.unquote(q.split("=",1)[1]) # # except: # value="" # punktsplit=name.split(".") #sonderfall feld mit punkten(tabelle.suchFeld.ausgewaehltesFeld,feldinoriginal), d.h. suche in anderer tabelle: #analysiere alle anderen faelle if diacritics.get(name,'yes')=='no': """filter diacritische zeichen""" value=unicodedata.normalize('NFKD', value.decode('utf-8')).encode('ASCII', 'ignore') if name==iCT+"lop": lop=value elif name==iCT+"table": if not tableExt: table=value elif name==iCT+"select": if not restrictField: select=value elif name==iCT+"max": if not NoLimit: maxstr="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+"outerjoin": outerjoin=value elif name==iCT+"sort": sortstrs=[] for word in value.split(','): wordstr=word.lstrip().rstrip() if sortAllFields: order=sortAllFields else: order=sortfields.get(wordstr,'ASC') if not (wordstr == ""): sortstrs.append(wordstr+" "+order) if len(sortstrs)>0: sort="ORDER BY "+string.join(sortstrs,',') elif name==iCT+"token": if not nostore=="yes": self.REQUEST.SESSION['token']=value elif name==iCT+"op": op=value #sonderfall Name hat das Format: #TABELLE.SUCHFELD_IN_DIESER_TABELLE.SELECT_FIELD.IDENTIFIER_IN_TABELLE_-table #i.e. erzeugt wird #das Statement #WHERE IDENTIFIER_IN_TABELLE in (select * from SELECT_FIELD #where LOWER(SUCHFELD_IN_DIESER_TABELLE) something value) #something is defined by _op_TABELLE.SUCHFELD_IN_DIESER_TABELLE.SELECT_FIELD.IDENTIFIER_IN_TABELLE elif (not name[0]==iCT) and len(punktsplit)==4: if opfields.has_key(name): op=opfields[name] else: op="ct" namealt=name name="LOWER("+punktsplit[1]+")" value=value.lower() if op=="ct": tmp=(name+" LIKE "+sql_quote("%"+value+"%")) elif op=="gt": tmp=(name+">"+sql_quote(value)) elif op=="lt": tmp=(name+"<"+sql_quote(value)) elif op=="eq": tmp=(name+"="+sql_quote(value)) elif op=="bw": tmp=(name+" LIKE "+sql_quote(value+"%")) elif op=="ew": tmp=(name+" LIKE "+sql_quote("%"+value)) elif op=="all": tmps=[] for word in value.split(" "): tmps.append(name+" LIKE "+sql_quote("%"+word+"%")) tmp=string.join(tmps,' AND ') elif op=="numerical": term=analyseIntSearch(value) tmp=(namealt+" "+term) # take namealt without LOWER elif op=="grep": tmp=(name+" ~* "+sql_quote(value)) elif op=="one": tmps=[] for word in value.split(" "): tmps.append(name+" LIKE "+sql_quote("%"+word+"%")) tmp=string.join(tmps,' OR ') op="all" if value!='': #lehre Werte werde nicht hinzugefuegt searchTmp="""%s in (select %s from %s where %s)"""%(punktsplit[3],punktsplit[2],punktsplit[0],tmp) queryTemplate.append(searchTmp) elif (not name[0]==iCT) and (not len(value)==0): #print "OP",op,name value=value.lower() tmp="" if opfields.has_key(name): op=opfields[name] else: op="ct" namealt=name ##DW hack 29-8-12 lower(oid) funktioniert nicht, generell muss oid handling noch korrigiert werden. if not name==primaryKey: name="LOWER("+name+")" #immer lower key nicht definiert fuer keys. else: op="eq" # bei keys immer eq benutzen logging.debug("NAME: %s"%name) if op=="ct": tmp=(name+" LIKE "+sql_quote("%"+value+"%")) elif op=="gt": tmp=(name+">"+sql_quote(value)) elif op=="lt": tmp=(name+"<"+sql_quote(value)) elif op=="eq": tmp=(name+"="+sql_quote(value)) elif op=="bw": tmp=(name+" LIKE "+sql_quote(value+"%")) #tmp=(name+" LIKE "+sql_quote("%"+value)) elif op=="all": tmps=[] for word in value.split(" "): tmps.append(name+" LIKE "+sql_quote("%"+word+"%")) tmp=string.join(tmps,' AND ') elif op=="numerical": term=analyseIntSearch(value) tmp=(namealt+" "+term) # take namealt without LOWER elif op=="grep": tmp=(name+" ~* "+sql_quote(value)) elif op=="one": tmps=[] for word in value.split(" "): tmps.append(name+" LIKE "+sql_quote("%"+word+"%")) tmp=string.join(tmps,' OR ') op="all" if (value!='') and ((not tableExt) or (namealt.split('.')[0]==tableExt)): #keine leeren werte und keine auschluss if searchFields.has_key(namealt): searchFields[namealt]+=lopfields.get(name,'OR')+" "+tmp searchFieldsOnly[namealt]+=lopfields.get(name,'OR')+" "+value else: searchFields[namealt]=tmp searchFieldsOnly[namealt]=value whereList=["("+searchFields[x]+")" for x in searchFields.keys()] whereList+=queryTemplate if len(whereList)>0: if filter: whereStr="("+string.join(whereList," "+lop+" ")+") AND "+filter else: whereStr=string.join(whereList," "+lop+" ") where="WHERE "+whereStr else: if filter: where="WHERE "+filter else: where="" if restrictConnect: if len(where)==0: where="WHERE "+restrictConnect[0]+" in ("+restrictConnect[1]+")" else: where+="and "+restrictConnect[0]+" in ("+restrictConnect[1]+")" #print "QE",table #print (select,table,where,sort,maxstr,skip) query="SELECT %s FROM %s %s %s %s %s %s"%(select,table,outerjoin,where,sort,maxstr,skip) if not nostore=="yes": self.REQUEST.SESSION['qs']=opfields #print "IAMHERE again:", query if not NoQuery: query2="SELECT count(*) FROM %s %s"%(table,where) #print "QUERYSTRING:",self.REQUEST.SESSION[storename]['queryString2'] if not self.REQUEST.SESSION.has_key(storename): self.REQUEST.SESSION[storename]={} self.REQUEST.SESSION[storename]['qs']=qs #sichere Querystring if self.REQUEST.SESSION[storename].has_key('queryString2'): if not self.REQUEST.SESSION[storename]['queryString2']==query2: #print "HOOOOO",storename self.REQUEST.SESSION[storename]['queryString2']=query2 try: self.REQUEST.SESSION[storename]['count']=self.ZSQLSimpleSearch(query2)[0].count except: self.REQUEST.SESSION[storename]['count']=0 #print "QUERY",query2,"::::",self.REQUEST.SESSION[storename]['queryString2'] else: self.REQUEST.SESSION[storename]['queryString2']=query2 if self.ZSQLSimpleSearch(query2): self.REQUEST.SESSION[storename]['count']=self.ZSQLSimpleSearch(query2)[0].count else: self.REQUEST.SESSION[storename]['count']=0 #print "QUERYNEW",self.REQUEST.SESSION[storename]['queryString2'] self.REQUEST.SESSION[storename]['rangeStart']=rangeStart self.REQUEST.SESSION[storename]['rangeEnd']=self.REQUEST.SESSION[storename]['count'] if (limit=="all") or (limit==int(limit)): 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 self.REQUEST.SESSION[storename]['searchFields']=searchFields self.REQUEST.SESSION[storename]['searchFieldsOnly']=searchFieldsOnly if debug: logging.error("ZSQLSimpleSearch %s"%query) if not NoQuery: return self.ZSQLQuery(query) else: return query def ZSQLUniqueQuery(self,value,returns): """unique relation""" if returns: statement="""select %s from %s where %s = '%s' """%(returns[1],returns[0],returns[2],value) founds=self.ZSQLSimpleSearch(statement) if founds: return getattr(founds[0],returns[1]) else: return value else: return value def ZSQLQuery(self,query,debug=None): """query""" if debug: logger("ZSQLQuery", logging.INFO, query) return self.ZSQLSimpleSearch(query) def ZSQLSearch(self): """To be done""" formatfile=self.REQUEST['URL1'] #generisch redirect zur gleichen url #zerlege querystring in key value paare #TODO: check if this is really necessary, use argv** rq=self.REQUEST['QUERY_STRING'] querys=rq.split("&") for querytemp in querys: #zerg query=querytemp.split("=") try: if query[0].lower()=="-format": formatfile=query[1] except: pass #sichern self.REQUEST.SESSION['query']=string.join(self.REQUEST['QUERY_STRING'].split("&"),",") self.REQUEST.SESSION['come_from_search']="yes" return self.REQUEST.RESPONSE.redirect(urllib.unquote(formatfile)+"?"+rq) def ZSQLint(self,string): try: return(int(string)) except: return 0 def getZSQLSearchFieldsList(self,storename="foundCount"): """get searchfieldList""" #print self.REQUEST.SESSION[storename]['searchFields'].keys() return self.REQUEST.SESSION[storename]['searchFieldsOnly'].keys() def getZSQLSearchFields(self,field,storename="foundCount"): """get searchfield""" #print "SF",self.REQUEST.SESSION[storename]['searchFields'] return self.REQUEST.SESSION[storename]['searchFieldsOnly'][field] def nextLink(self,html,storename="foundCount"): """nextLink""" try: limit=self.REQUEST.SESSION[storename]['rangeSize'] if int(limit)==0 : limit="1" newRangeStart=int(self.REQUEST.SESSION[storename]['rangeStart'])+int(limit) except: limit=1 newRangeStart=0 if (newRangeStart>=self.REQUEST.SESSION[storename]['count']) or (self.REQUEST.SESSION[storename]['count']==self.REQUEST.SESSION[storename]['rangeEnd']): #newRangeStart=self.REQUEST.SESSION[storename]['count']-1 return "" #create new query string if self.REQUEST['QUERY_STRING']=="": qs=self.REQUEST.SESSION['query'] queries=string.split(qs,",")[0:] else: qs=self.REQUEST['QUERY_STRING'] queries=string.split(qs,"&")[0:] newquery=[] skipFound=0 for query in queries: if query[0]=="_" : query="-"+query[1:] 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 "<a href='%s'>%s</a>"%(self.REQUEST['URL']+"?"+newquerystring,html) def prevLink(self,html,storename="foundCount"): """prev link""" try: limit=self.REQUEST.SESSION[storename]['rangeSize'] if int(limit)==0 : limit="1" newRangeStart=int(self.REQUEST.SESSION[storename]['rangeStart'])-int(limit) except: limit=1 newRangeStart=0 if newRangeStart <0: return "" #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 fuer 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 "<a href='%s'>%s</a>"%(self.REQUEST['URL']+"?"+newquerystring,html) def pydev_settrace(self): """do settrace to start debugging""" import pydevd pydevd.settrace() manage_addZSQLExtendFolderForm=DTMLFile('ZSQLExtendFolderAdd', globals()) def manage_addZSQLExtendFolder(self, id, title='', label='', description='', createPublic=0, createUserF=0, connection_id=None, 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) setattr(ob,'label',label) setattr(ob,'description',description) setattr(ob,'connection_id',connection_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) class ZSQLBibliography(Folder,ZSQLExtendFolder): """Bibliography""" meta_type="ZSQLBibliography" def getLabel(self): try: return self.label except: return "" def getDescription(self): try: return self.description except: return "" def changeZSQLBibliographyForm(self): """change folder config""" pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','changeZSQLBibliographyForm.zpt')).__of__(self) return pt() 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')).__of__(self) pt.content_type="text/html" return pt() def changeZSQLBibliography(self,tableName,label,description,primaryKey="",primaryKeys="",connection_id=None,REQUEST=None): """change it""" self.connection_id=connection_id self.tableName=tableName self.label=label self.description=description self.primaryKey=primaryKey self.primaryKeys={} if primaryKeys.lstrip().rstrip()!="": for vals in primaryKeys.split(";"): splitted=vals.split(":") if len(splitted)<2: if REQUEST is not None: return """<html><body>Wrong Syntax of keystring: %s<br/> %s is not of the form TABLE:key.</body></html>"""%(primaryKeys,vals) logging.debug(splitted) self.primaryKeys[splitted[0]]=splitted[1] if REQUEST is not None: return self.manage_main(self, REQUEST) manage_options=Folder.manage_options+( {'label':'Main Config','action':'changeZSQLBibliographyForm'}, {'label':'Generate RDF Template','action':'generateRDFTemplate'}, {'label':'Generate XML Template','action':'generateXMLTemplate'}, ) def getFieldLabel(self,fields,field): """get labels""" try: ret =fields[0][field] if ret == "": return field else: return ret except: return field def getFieldValue(self,found,field): """get value""" try: ret=getattr(found,field) if ret == "": return None else: return ret except: return None def getMetaDataManager(self): return self.metadata def findTagsFromMapping(self,referenceType): """gib hash mit label -> generic zurueck""" self.referencetypes=self.ZopeFind(self.getMetaDataManager(),search_sub=1) bibdata={} retdata={} #fieldlist=self.standardMD.fieldList for referenceTypeF in self.referencetypes: #print referenceType,referenceTypeF[1].title if referenceTypeF[1].title.lower() == referenceType.lower(): bibdata[referenceTypeF[1].title]=referenceTypeF[1].fields bibdata['data']=referenceTypeF[1] self.fields=bibdata[referenceTypeF[1].title] for field in referenceTypeF[1].getFieldList(): if referenceTypeF[1].getValue(field)[0]==None: retdata[field]=field else: retdata[field]=referenceTypeF[1].getValue(field)[0] return retdata,referenceTypeF[1].getFieldList() def findLabelsFromMapping(self,referenceType): """gib hash mit tagname -> lalbe zurueck""" mapping= self.getMetaDataManager().getBibMapping(referenceType) fields=mapping.getFields() fieldlist=fields.keys() retdata={} for field in fieldlist: retdata[field]=fields[field]['label'] # self.referencetypes=self.ZopeFind(self.getMetaDataManager(),search_sub=1) # # bibdata={} # retdata={} # #fieldlist=self.standardMD.fieldList # logging.debug("XX") # for referenceTypeF in self.referencetypes: # #print referenceType,referenceTypeF[1].title # logging.debug("%s=%s"%(referenceTypeF[1].title,referenceType)) # if referenceTypeF[1].title.lower() == referenceType.lower(): # # bibdata[referenceTypeF[1].title]=referenceTypeF[1].fields # bibdata['data']=referenceTypeF[1] # self.fields=bibdata[referenceTypeF[1].title] # for field in referenceTypeF[1].getFieldList(): # retdata[field]=referenceTypeF[1].getValue(field)[1] return retdata,fieldlist def createRDFTag(self,tag,content,namespace="cdli"): """create RDF""" if content: tag=namespace+":"+tag if (type(content) is StringType) or (type(content) is UnicodeType): ret=""" <%s>%s</%s>"""%(tag,escape(content),tag) else: ret=""" <%s>%s</%s>"""%(tag,content,tag) return ret.decode('latin-1') else: return "" def createIndexTag(self,tag,content): """create tag""" if content: if (type(content) is StringType) or (type(content) is UnicodeType): ret=""" <%s>%s</%s>"""%(tag,escape(content),tag) else: ret=""" <%s>%s</%s>"""%(tag,content,tag) return ret.decode('latin-1') else: return "" def getXML2(self): """crate index meta""" fn=os.path.splitext(self.REQUEST['fn'])[0]+"." self.REQUEST['fn']=fn pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','record2.xml')).__of__(self) pt.content_type="text/xml" return pt() def generateRDFTemplate(self,REQUEST=None): """generateRDFtemplate""" zt=ZopePageTemplate('record.rdf') self._setObject('record.rdf',zt) default_content_fn = os.path.join(package_home(globals()), 'zpt','record.rdf') text = open(default_content_fn).read() zt.pt_edit(text, 'text/xml') if REQUEST is not None: return self.manage_main(self, REQUEST) def generateXMLTemplate(self,REQUEST=None): """generateXMLtemplate""" zt=ZopePageTemplate('record.xml') self._setObject('record.xml',zt) default_content_fn = os.path.join(package_home(globals()), 'zpt','record.xml') text = open(default_content_fn).read() zt.pt_edit(text, 'text/xml') if REQUEST is not None: return self.manage_main(self, REQUEST) def getMetaDataRDF(self): """crate index meta""" find=self.ZopeFind(self,obj_ids=["record.rdf"]) if not find: pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','record.rdf')).__of__(self) pt.content_type="text/xml" return pt() else: return find[0][1]() def getMetaDataXML(self): """crate index meta""" find=self.ZopeFind(self,obj_ids=["record.xml"]) if not find: pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','record.xml')).__of__(self) pt.content_type="text/xml" return pt() else: return find[0][1]() def getMetaDatasXML(self): """index""" # check if the request's host part was OK http_host = self.REQUEST['HTTP_HOST'] host_port = self.REQUEST['SERVER_PORT'] fix_host = None if http_host and http_host.rfind(host_port) == -1: #print "HTTP_HOST needs fixing!" fix_host = http_host + ":" + host_port ret="""<?xml version="1.0" ?> <index>""" for found in self.ZSQLSimpleSearch("select % from %s limit ALL"%(self.getPrimaryKey,self.tableName)): base_url = self.absolute_url() if fix_host: #print "replacing ", http_host, " by ", fix_host base_url = string.replace(base_url, http_host, fix_host, 1) link=base_url+"/"+"record.html?oid=%i"%getattr(found,self.getPrimaryKey()) metalink=base_url+"/"+"getMetaDataXML?oid=%i"%getattr(found,self.getPrimaryKey()) ret+="""<resource resourceLink="%s" metaLink="%s"/>\n"""%(link,metalink) return ret+"\n</index>" manage_addZSQLBibliographyForm=DTMLFile('ZSQLBibliographyAdd', globals()) def manage_addZSQLBibliography(self, id, tableName,label,description,title='', createPublic=0, createUserF=0, connection_id=None, 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=ZSQLBibliography() ob.id=str(id) ob.title=title self._setObject(id, ob) ob=self._getOb(id) setattr(ob,'tableName',tableName) setattr(ob,'label',label) setattr(ob,'description',description) setattr(ob,'connection_id',connection_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)