ZSQLExtend/ZSQLExtend.py - view

File: [Repository] / ZSQLExtend / ZSQLExtend.py
Revision 1.81: download - view: text, annotated - select for diffs - revision graph
Thu Apr 20 17:40:11 2006 UTC (18 years, 1 month ago) by dwinter
Branches: MAIN
CVS tags: HEAD

new methods for importing the xml output format of access

1: from OFS.Folder import Folder 2: from Acquisition import Implicit 3: from Globals import DTMLFile,package_home,Persistent 4: import urllib 5: import re 6: import string 7: import sys 8: #from pyPgSQL import libpq 9: from AccessControl import getSecurityManager 10: from Products.PageTemplates.ZopePageTemplate import ZopePageTemplate 11: from Products.PageTemplates.PageTemplateFile import PageTemplateFile 12: 13: from Products.ZSQLMethods.SQL import SQLConnectionIDs 14: from xml.sax.saxutils import escape 15: from types import * 16: import Shared.DC.ZRDB.DA 17: import zLOG 18: import os.path 19: import os 20: from OFS.SimpleItem import SimpleItem 21: 22: def getTextFromNode(nodename): 23: nodelist=nodename.childNodes 24: rc = "" 25: for node in nodelist: 26: if node.nodeType == node.TEXT_NODE: 27: rc = rc + node.data 28: return rc 29: 30: 31: def analyseIntSearch(word): 32: #analyse integer searches 33: 34: splitted=word.split("-") 35: 36: if len(splitted)==1: 37: return "="+splitted[0] 38: 39: if splitted[0]=="": 40: return "< "+splitted[1] 41: 42: if splitted[1]=='': 43: return "> "+splitted[0] 44: else: 45: return "BETWEEN "+splitted[0]+" AND "+splitted[1] 46: 47: 48: 49: def sql_quote(v): 50: # quote dictionary 51: quote_dict = {"\'": "''", "\\": "\\\\"} 52: for dkey in quote_dict.keys(): 53: if string.find(v, dkey) >= 0: 54: v=string.join(string.split(v,dkey),quote_dict[dkey]) 55: return "'%s'" % v 56: 57: def showSQLConnectionIDs(self): 58: return SQLConnectionIDs(self) 59: 60: class ZSQLIndex(SimpleItem): 61: """index""" 62: meta_type="ZSQLIndex" 63: 64: def __init__(self,index,id,table=''): 65: self.index=[x for x in index] 66: self.id=id 67: self.table=table 68: 69: def setIndex(self,index): 70: self.index=[x for x in index] 71: 72: def getIndex(self): 73: return self.index 74: 75: class ZSQLExtendFolder(Folder,Persistent, Implicit): 76: """Folder""" 77: meta_type="ZSQLExtendFolder" 78: 79: def ZSQLQuote(self,str): 80: """quote str for sql""" 81: return sql_quote(str) 82: 83: def importAccessModell(self,configFileName,RESPONSE=None): 84: """import tables from access 85: @param configFileName: xml-configfile 86: """ 87: from Ft.Xml import Parse 88: fh=file(configFileName) 89: 90: doc=Parse(fh) 91: 92: x=doc.xpath("//pathToFolder/@path") 93: 94: 95: if not (len(x)==1): # tag ist nich eineindeutig 96: return False 97: 98: pathToFolder=x[0].value 99: 100: for db in doc.xpath("//db"): 101: 102: containers=db.xpath("./@container") 103: identifiers=db.xpath("./@identify") 104: 105: print containers 106: if not (len(containers)==1): 107: return False 108: else: 109: container=containers[0].value 110: 111: if not (len(identifiers)==1): 112: identifier=None 113: else: 114: identifier=identifiers[0].value 115: 116: self.xsdToTable(container.lower(),container,modus="drop",filename=os.path.join(pathToFolder,container.lower()+".xsd")) 117: self.importXMLFileAccess(container.lower(),container,filename=os.path.join(pathToFolder,container.lower()+".xml"),identify=identifier) 118: 119: return "<html><body>DONE</body></html>" 120: 121: def xsdToTable(self,table,elementNameForTable,modus="update", filename=None,data=None,RESPONSE=None): 122: """reads an xsd file an creates the columns of a table out of its structure 123: @param table: name of the table the xml shall be imported into 124: @param elementNameForTable: must be a element of type complex type. the element of the sequence in the complex type 125: define the columns of the table >table< 126: @param data (optional): data to be imported 127: @param filename (optional) or filename 128: @param identify: (optional) field res. tag which identifies a entry uniquely for updating purposes. 129: @param RESPONSE: (optional) 130: 131: """ 132: #from xml.dom.minidom import parseString,parse 133: 134: from Ft.Xml import Parse 135: zLOG.LOG("import xsd",zLOG.INFO,"called") 136: #fh=file("/tmp/fmpxml.xml") 137: import bz2 138: import base64 139: 140: 141: ret="" 142: if data: 143: data=bz2.decompress(base64.decodestring(data)) 144: 145: #zLOG.LOG("import xsd",zLOG.INFO,"received file") 146: doc=Parse(data) 147: #zLOG.LOG("import xsd",zLOG.INFO,"parsed file") 148: 149: elif filename: 150: fh=file(filename) 151: txt=fh.read() 152: 153: doc=Parse(txt) 154: #zLOG.LOG("import xsd",zLOG.INFO,"parsed file") 155: 156: 157: Nss={'xsd':'http://www.w3.org/2001/XMLSchema'} 158: definingSequence=doc.xpath("""//xsd:element[@name='%s']/xsd:complexType/xsd:sequence/xsd:element/@name"""%elementNameForTable,explicitNss=Nss) 159: 160: fieldNames=[x.value for x in definingSequence] 161: 162: 163: 164: #check if table exists 165: 166: qstr="""select relname from pg_class where relname = '%s'"""%table 167: if len (self.ZSQLSimpleSearch(qstr))<1: # if not the create the table 168: columns=[] 169: create=True 170: else: 171: create=False 172: 173: zLOG.LOG("update xsd: fieldnames",zLOG.INFO,repr(fieldNames)) 174: qstr="""select attname from pg_attribute, pg_class where attrelid = pg_class.oid and relname = '%s' """ 175: columns=[x.attname for x in self.ZSQLSimpleSearch(qstr%table)] 176: 177: 178: if (modus=="drop") and (not create): #table shall be deleted, therefore it should exist (not create) 179: print "drop" 180: qstr="""DROP TABLE %s """ 181: self.ZSQLSimpleSearch(qstr%table) 182: columns=[] 183: create=True 184: 185: for fieldName in fieldNames: 186: if type(fieldName) is UnicodeType: 187: fieldName=fieldName.encode('utf-8') 188: zLOG.LOG("update xml: fieldname",zLOG.INFO,repr(fieldName)) 189: if fieldName.lower() not in columns: 190: 191: if create:# table does not exist therefore create with one column 192: qstr="""create table %s (%s %s)""" 193: create=False 194: else:# otherwise add the field 195: qstr="""alter table %s add %s %s""" 196: 197: self.ZSQLSimpleSearch(qstr%(table,fieldName,'text')) 198: zLOG.LOG("update xsd: fieldname add",zLOG.INFO,qstr%(table,fieldName,'text')) 199: 200: 201: def importXMLFileAccess(self,table,container,data=None,identify=None,filename=None,RESPONSE=None): 202: ''' 203: Import XML file in access format into the table 204: @param table: name of the table the xml shall be imported into 205: @param containerTagName: XML-Tag which describes a dataset 206: @param data: data to be imported 207: @param identify: (optional) field res. tag which identifies a entry uniquely for updating purposes. 208: @param RESPONSE: (optional) 209: ''' 210: from xml.dom.pulldom import parseString,parse 211: 212: zLOG.LOG("import xml",zLOG.INFO,"called") 213: #fh=file("/tmp/fmpxml.xml") 214: import bz2 215: import base64 216: 217: ret="" 218: if data: 219: data=bz2.decompress(base64.decodestring(data)) 220: 221: zLOG.LOG("import xml",zLOG.INFO,"received file") 222: doc=parseString(data) 223: zLOG.LOG("import xml",zLOG.INFO,"parsed file") 224: 225: elif filename: 226: fh=file(filename) 227: doc=parse(fh) 228: zLOG.LOG("import xml",zLOG.INFO,"parsed file") 229: while 1: 230: node=doc.getEvent() 231: 232: if node is None: 233: break; 234: else: 235: if node[1].nodeName.lower()==container.lower(): # make everything case insensitive 236: doc.expandNode(node[1]) 237: 238: dataSet={} 239: for col in node[1].childNodes: 240: if col.nodeType is col.ELEMENT_NODE: 241: data=col.nodeName 242: dataSet[data]=getTextFromNode(col) 243: print dataSet.keys() 244: update=False 245: 246: if identify: 247: 248: field=dataSet[identify] 249: 250: searchStr="""select %s from %s where %s = '%s'"""%(identify,table,identify,field) 251: zLOG.LOG("import xml",zLOG.INFO,searchStr) 252: search=self.ZSQLSimpleSearch(searchStr) 253: if search: 254: update=True 255: 256: if update: 257: tmp=[] 258: for fieldName in dataSet.keys(): 259: tmp.append("""%s = %s"""%(fieldName,self.ZSQLQuote(dataSet[fieldName]))) 260: setStr=",".join(tmp) 261: 262: field=dataSet[identify] 263: 264: queryStr="""UPDATE %s SET %s WHERE %s = '%s' """%(table,setStr,identify,field) 265: zLOG.LOG("update xml",zLOG.INFO,queryStr) 266: self.ZSQLSimpleSearch(queryStr) 267: ret+="ud: %s \n"%field 268: else: 269: 270: 271: fields=",".join(dataSet.keys()) 272: values=",".join([""" %s """%self.ZSQLQuote(x) for x in dataSet.keys()]) 273: 274: 275: queryStr="""INSERT INTO %s (%s) VALUES (%s)"""%(table,fields,values) 276: self.ZSQLSimpleSearch(queryStr) 277: zLOG.LOG("update xml",zLOG.INFO,queryStr) 278: 279: 280: 281: 282: return ret 283: 284: 285: def importXMLFile(self,table,data=None,identify=None,filename=None,RESPONSE=None): 286: #TODO: finish importXMLFile 287: ''' 288: Import XML file into the table 289: @param table: name of the table the xml shall be imported into 290: @param containerTagName: XML-Tag which describes a dataset 291: @param data: data to be imported 292: @param identify: (optional) field res. tag which identifies a entry uniquely for updating purposes. 293: @param RESPONSE: (optional) 294: ''' 295: from xml.dom.pulldom import parseString,parse 296: 297: zLOG.LOG("import xml",zLOG.INFO,"called") 298: #fh=file("/tmp/fmpxml.xml") 299: import bz2 300: import base64 301: 302: ret="" 303: if data: 304: data=bz2.decompress(base64.decodestring(data)) 305: 306: zLOG.LOG("import xml",zLOG.INFO,"received file") 307: doc=parseString(data) 308: zLOG.LOG("import xml",zLOG.INFO,"parsed file") 309: 310: elif filename: 311: fh=file(filename) 312: doc=parse(fh) 313: zLOG.LOG("import xml",zLOG.INFO,"parsed file") 314: while 1: 315: node=doc.getEvent() 316: 317: if node is None: 318: break; 319: else: 320: if node[1].nodeName=='ROW': 321: doc.expandNode(node[1]) 322: cols=node[1].getElementsByTagName('COL') 323: dataSet=[] 324: for col in cols: 325: data=col.getElementsByTagName('DATA') 326: dataSet.append(getTextFromNode(data[0])) 327: update=False 328: if identify: 329: 330: nr=fieldNames.index(identify) 331: field=dataSet[nr] 332: 333: searchStr="""select %s from %s where %s = '%s'"""%(identify,table,identify,field) 334: zLOG.LOG("import xml",zLOG.INFO,searchStr) 335: search=self.ZSQLSimpleSearch(searchStr) 336: if search: 337: update=True 338: 339: if update: 340: tmp=[] 341: for fieldName in fieldNames: 342: tmp.append("""%s = %s"""%(fieldName,self.ZSQLQuote(dataSet[fieldNames.index(fieldName)]))) 343: setStr=",".join(tmp) 344: nr=fieldNames.index(identify) 345: field=dataSet[nr] 346: 347: queryStr="""UPDATE %s SET %s WHERE %s = '%s' """%(table,setStr,identify,field) 348: zLOG.LOG("update xml",zLOG.INFO,queryStr) 349: self.ZSQLSimpleSearch(queryStr) 350: ret+="ud: %s \n"%field 351: else: 352: 353: 354: fields=",".join(fieldNames) 355: values=",".join([""" %s """%self.ZSQLQuote(x) for x in dataSet]) 356: 357: 358: queryStr="""INSERT INTO %s (%s) VALUES (%s)"""%(table,fields,values) 359: self.ZSQLSimpleSearch(queryStr) 360: zLOG.LOG("update xml",zLOG.INFO,queryStr) 361: ret+="ad: %s \n"%field 362: 363: elif node[1].nodeName=="METADATA": 364: fieldNames=[] 365: doc.expandNode(node[1]) 366: 367: names=node[1].getElementsByTagName('FIELD') 368: 369: for name in names: 370: fieldNames.append(name.getAttribute('NAME')) 371: 372: zLOG.LOG("update xml: fieldnames",zLOG.INFO,repr(fieldNames)) 373: qstr="""select attname from pg_attribute, pg_class where attrelid = pg_class.oid and relname = '%s' """ 374: columns=[x.attname for x in self.ZSQLSimpleSearch(qstr%table)] 375: 376: for fieldName in fieldNames: 377: zLOG.LOG("update xml: fieldname",zLOG.INFO,repr(fieldName)) 378: if fieldName not in columns: 379: qstr="""alter table %s add %s %s""" 380: self.ZSQLSimpleSearch(qstr%(table,fieldName,'text')) 381: zLOG.LOG("update xml: fieldname add",zLOG.INFO,qstr%(table,fieldName,'text')) 382: #fn=node[1].getAttribute("xml:id") 383: #nf=file("xtf/"+fn+".xtf",'w') 384: #nf.write("""<texts xmlns="http://emegir.info/xtf" xmlns:lem="http://emegir.info/lemma" >"""+node[1].toxml()+"</texts>") 385: #print "wrote: %s"%fn 386: return ret 387: 388: def generateIndex(self,field,index_name,table,RESPONSE=None): 389: """erzeuge index aus feld""" 390: index={} 391: founds=self.ZSQLSimpleSearch("""SELECT %s,oid FROM %s LIMIT 2000"""%(field,table)) 392: 393: for found in founds: 394: tmp=getattr(found,field,None) 395: if tmp: 396: strings=tmp.split(" ") 397: for string in strings: 398: if index.get(string): 399: index[string].append(found.oid) 400: else: 401: index[string]=[found.oid] 402: RESPONSE.write(string+"\n") 403: 404: if not hasattr(self,index_name): 405: obj=ZSQLIndex(index,index_name,table) 406: self._setObject(index_name,obj) 407: 408: self._getOb(index_name).setIndex(index) 409: 410: def getIndex(self,index_name): 411: """getIndex""" 412: founds=self.ZopeFind(self,obj_ids=[index_name]) 413: 414: return founds[0][1].getIndex() 415: 416: 417: def testneu(self): 418: """test""" 419: relStatement="""period like '%s%%'""" 420: statement="select * from cdli_cat" 421: wherePart="museum_no like 'VAT%'" 422: classes=['Uruk III','Uruk IV'] 423: return self.searchRel(relStatement,statement,wherePart,classes) 424: 425: def URLquote(self,txt): 426: """urlquote""" 427: return urllib.quote(txt) 428: 429: def searchRel(self,relStatement,statement,wherePart,classes): 430: """suche relative haufigkeiten""" 431: ret={} 432: allRecords=len(self.ZSQLSimpleSearch(statement + " where "+wherePart)) 433: 434: for oneclass in classes: 435: ret[oneclass]=len(self.ZSQLSimpleSearch(statement + " where ("+wherePart+") and "+ relStatement%oneclass)) 436: 437: return (ret,allRecords) 438: 439: def content_html(self): 440: """template fuer content""" 441: 442: try: 443: obj=getattr(self,"ZSQLBibliography_template") 444: return obj() 445: except: 446: pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','ZSQLBibliography_template_standard.zpt'),content_type='text/html').__of__(self) 447: pt.content_type="text/html" 448: 449: return pt() 450: 451: 452: def getWeight(self): 453: """getLabe""" 454: try: 455: return self.weight 456: except: 457: return "" 458: 459: def getLabel(self): 460: """getLabel""" 461: try: 462: return self.label 463: except: 464: return "" 465: 466: def getTitle(self): 467: """getTitle""" 468: try: 469: return self.title 470: except: 471: return "" 472: 473: 474: def getDescription(self): 475: """getDescription""" 476: try: 477: return self.description 478: except: 479: return "" 480: 481: manage_options=Folder.manage_options+( 482: {'label':'Main Config','action':'changeZSQLExtendForm'}, 483: ) 484: 485: def changeZSQLExtendForm(self): 486: """change folder config""" 487: pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','changeZSQLExtendForm.zpt')).__of__(self) 488: return pt() 489: 490: 491: def changeZSQLExtend(self,label,description,weight=0,REQUEST=None,connection_id=None): 492: """change it""" 493: self.connection_id=connection_id 494: self.weight=weight 495: self.label=label 496: self.description=description 497: 498: if REQUEST is not None: 499: return self.manage_main(self, REQUEST) 500: 501: def formatAscii(self,str,url=None): 502: """ersetze ascii umbrueche durch <br>""" 503: #url=None 504: 505: if not str: 506: return "" 507: 508: str=str.rstrip().lstrip() 509: 510: if url and str: 511: 512: retStr="" 513: words=str.split("\n") 514: 515: for word in words: 516: strUrl=url%word 517: #print "str",strUrl 518: retStr+="""<a href="%s">%s</a><br/>"""%(strUrl,word) 519: str=retStr 520: if str: 521: return re.sub(r"[\n]","<br/>",str) 522: else: 523: return "" 524: 525: def getSAttribute(self,obj,atribute,pref=''): 526: """get Attribute or emptystring""" 527: 528: #print "obj",obj 529: try: 530: return pref+getattr(obj,atribute) 531: except: 532: return "" 533: 534: def getS(self,str): 535: """make none to empty string""" 536: if str: 537: return str 538: else: 539: return "" 540: 541: def actualPath(self,url=None): 542: """path""" 543: 544: if self.REQUEST['HTTP_X_FORWARDED_SERVER']=='': 545: host=self.REQUEST['HTTP_HOST'] 546: else: 547: host=self.REQUEST['HTTP_X_FORWARDED_SERVER'] 548: if not url: 549: return "http://"+host+self.REQUEST['PATH_TRANSLATED'] 550: else: 551: temp=self.REQUEST[url].split("/") 552: temp[2]=host 553: return string.join(temp,"/") 554: 555: def getRequest(self): 556: """request""" 557: return self.REQUEST 558: 559: def lowerEnd(self,path): 560: """oinly for demo""" 561: return os.path.splitext(path)[0]+".jpg" 562: 563: def ZSQLisEmpty(self,field): 564: """Teste ob Treffer leer""" 565: #print "field",field 566: if not field: 567: return 1 568: if field=="": 569: return 1 570: return 0 571: 572: def ZSQLsearchOptions(self,fieldname=""): 573: """return HTML Fragment with search options""" 574: 575: ret="""<select name="-op_%s"> 576: <option value="bw">begins with</option>  577: <option value="ew">ends with</option> 578: <option value="ct" selected>contains</option>  579: <option value="eq">equals</option>  580: </select>"""%fieldname 581: return ret 582: 583: def ZSQLSelectionFromCRList(self,fieldname,listField,boxType="checkbox",checked=None): 584: """generate select options from a cr seperated list""" 585: fields=listField.split("\n") 586: ret="" 587: for field in fields: 588: if checked and (field in checked.split("\n")): 589: ret+="""<input name="%s" type="%s" value="%s" checked>%s"""%(fieldname,boxType,field.encode('utf-8'),field.encode('utf-8')) 590: else: 591: ret+="""<input name="%s" type="%s" value="%s">%s"""%(fieldname,boxType,field.encode('utf-8'),field.encode('utf-8')) 592: return ret 593: 594: def ZSQLSelectionFromSearchList(self,fieldname,results,fieldnameResult,boxType="checkbox",checked=None): 595: """generate select options from a cr seperated list""" 596: 597: ret="" 598: if not results: return "" 599: 600: for result in results: 601: field=getattr(result,fieldnameResult) 602: if field: 603: if checked and (getattr(result,fieldnameResult) in checked.split("\n")): 604: ret+="""<input name="%s" type="%s" value="%s" checked>%s"""%(fieldname,boxType,field.encode('utf-8'),field.encode('utf-8')) 605: else: 606: ret+="""<input name="%s" type="%s" value="%s">%s"""%(fieldname,boxType,field.encode('utf-8'),field.encode('utf-8')) 607: return ret 608: 609: 610: def ZSQLOptionsFromCRList(self,fieldname,listField, multiple='',start=None,startValue=None,size=None,selected=None): 611: """generate select oprions form a cr seperated list""" 612: fields=listField.split("\n") 613: if size: 614: ret="""<select name="%s" %s size="%s" > 615: """%(fieldname,multiple,size) 616: 617: else: 618: ret="""<select name="%s" %s > 619: """%(fieldname,multiple) 620: if start: 621: if start==' ': 622: start='' 623: if not startValue: 624: startValue=start 625: 626: ret+="""<option value="%s" >%s</option>"""%(startValue,start) 627: for field in fields: 628: if selected and (field in selected.split("\n")): 629: ret+="""<option selected value="%s">%s</option>"""%(field.encode('utf-8'),field.encode('utf-8')) 630: else: 631: ret+="""<option value="%s">%s</option>"""%(field.encode('utf-8'),field.encode('utf-8')) 632: ret+="""</select>""" 633: return ret 634: 635: def ZSQLOptionsFromSearchList(self,fieldname,results,fieldName,valueName=None,start=None, multiple='',startValue=None,additionalSelect="",size=None,linelen=None): 636: """generate select options form a search list 637: es wird 638: <select name=fieldname mutiple> 639: <option value=startValue>start</option> 640: <option value=result.fieldName>result.fieldValue</option> 641: erzeugt. 642: 643: @parameter fieldname: Name fuer name-wert im select-tag 644: @results results: Resultobject einer SQL-suche 645: @parameter fieldName: Name des Feldes, das als value in den option-tag geschrieben werden soll. 646: @parameter valueName: (optional) Name des Feldes, dass als im option-tag ausgegeben wird, default wert ist valueName=fieldName 647: @start: (optional) falls zusaetzliches option tag erzeugt werden soll, gibt start an was im option tag steht 648: @startValue: gibt den entsprechenden Wert an. 649: """ 650: if not valueName: 651: valueName=fieldName 652: if size: 653: ret="""<select name="%s" %s size="%s" %s> 654: """%(fieldname,multiple,size,additionalSelect) 655: else: 656: ret="""<select name="%s" %s %s> 657: """%(fieldname,multiple,additionalSelect) 658: if start: 659: if start==' ': 660: start='' 661: 662: if not startValue: 663: startValue=start 664: 665: ret+="""<option value="%s" >%s</option>"""%(startValue,start) 666: for result in results: 667: field=getattr(result,fieldName) 668: fieldValue=getattr(result,valueName) 669: if fieldValue: 670: if not linelen: 671: ret+="""<option value="%s">%s</option>"""%(field,fieldValue) 672: else: 673: mist = """%s"""%(fieldValue) 674: if len(mist) > string.atoi(linelen): 675: mist = mist[:string.atoi(linelen)] 676: ret+="""<option value="%s">%s</option>"""%(field,mist) 677: ret+="""</select>""" 678: return ret 679: 680: 681: def ZSQLInlineSearchU(self,storename=None,**argv): 682: """one element if exists""" 683: qs=[] 684: if storename: 685: """store""" 686: storename=storename 687: else: 688: storename="foundCount" 689: 690: 691: 692: #print "INLINE:",argv 693: for a in argv.keys(): 694: qs.append(a+"="+urllib.quote(str(argv[a]))) 695: #return [] 696: ret = self.parseQueryString(string.join(qs,","),"_",storename=storename) 697: 698: try: 699: return ret[0] 700: except: 701: return None 702: 703: def ZSQLInlineSearch(self,storename=None,**argv): 704: """inlinesearch""" 705: 706: qs=[] 707: if storename: 708: """store""" 709: else: 710: storename="foundCount" 711: 712: 713: 714: #print "INLINE:",argv 715: for a in argv.keys(): 716: try: 717: qs.append(a+"="+urllib.quote(str(argv[a]))) 718: except: 719: import urllib 720: qs.append(a+"="+urllib.quote(str(argv[a]))) 721: 722: #return [] 723: return self.parseQueryString(string.join(qs,","),"_",storename=storename) 724: 725: def ZSQLInlineSearch2(self,query): 726: """inlinesearch""" 727: qs=[] 728: 729: #print "INLINE:",query 730: return self.ZSQLSimpleSearch(query) 731: 732: 733: def ZSQLResetConnection(self): 734: """reset the connectione""" 735: try: 736: self.getConnectionObj().manage_close_connection() 737: except: 738: zLOG.LOG("ZSQLResetConnection",zLOG.ERROR, '%s %s'%sys.exc_info()[:2]) 739: try: 740: self.getConnectionObj().manage_open_connection() 741: except: 742: zLOG.LOG("ZSQLResetConnection",zLOG.ERROR, '%s %s'%sys.exc_info()[:2]) 743: 744: def ZSQLSimpleSearch(self,query=None,max_rows=1000000,debug=None): 745: """simple search""" 746: 747: 748: if not query: 749: query=self.query 750: 751: if debug: 752: print "DEBUG: ZSQLSimpleSearch:", query 753: if (hasattr(self,"_v_searchSQL") and (self._v_searchSQL == None)) or (not hasattr(self,"_v_searchSQL")): 754: 755: self._v_searchSQL=Shared.DC.ZRDB.DA.DA("_v_searchSQL","_v_searchSQL",self.getConnectionObj().getId(),"var","<dtml-var var>") 756: 757: self._v_searchSQL.max_rows_=max_rows 758: try: 759: return self._v_searchSQL.__call__(var=query) 760: except : 761: 762: if sys.exc_info()[0]=="Database Error": 763: try: 764: self.getConnectionObj().manage_open_connection() 765: except: 766: zLOG.LOG("ZSQLSimpleSearch",zLOG.ERROR, '%s %s'%sys.exc_info()[:2]) 767: else: 768: try: 769: 770: self._v_searchSQL.max_rows_=max_rows 771: 772: return self._v_searchSQL.__call__(var=query) 773: except : 774: 775: if sys.exc_info()[0]=="Database Error": 776: try: 777: self.getConnectionObj().manage_open_connection() 778: except: 779: zLOG.LOG("ZSQLSimpleSearch",zLOG.ERROR, '%s %s'%sys.exc_info()[:2]) 780: 781: def getConnectionObj(self): 782: if hasattr(self,'connection_id'): 783: return getattr(self,self.connection_id) 784: 785: 786: def ZSQLSimpleSearch2(self,query=None): 787: """ returrn SQLSearch""" 788: 789: if not query: 790: query=self.query 791: if getattr(self,'_v_search',None): 792: 793: return self._v_search(var=query) 794: 795: if hasattr(self,'search') and (self.search.meta_type=="Z SQL Method"): 796: self._v_search=self.search 797: return self.search(var=query) 798: else: 799: if hasattr(self.aq_parent.aq_parent,'search') and (self.aq_parent.aq_parent.search.meta_type=="Z SQL Method"): 800: self._v_search=self.aq_parent.aq_parent.search 801: return self.aq_parent.aq_parent.search(var=query) 802: else: 803: search=self.ZopeFind(self,obj_metatypes=["Z SQL Method"],search_sub=1) 804: if search: 805: self._v_search=search[0][1] 806: return search[0][1](var=query) 807: else: 808: return [] 809: 810: 811: 812: def ZSQLAdd(self,format=None,RESPONSE=None,**argv): 813: """Neuer Eintrag""" 814: 815: qs_temp=[] 816: 817: for a in self.REQUEST.form.keys(): 818: qs_temp.append(a+"="+urllib.quote(str(self.REQUEST.form[a]))) 819: 820: qs=string.join(qs_temp,",") 821: 822: for field in argv.keys(): 823: if field[0]=="_": 824: fieldTmp="-"+field[1:] 825: else: 826: fieldTmp=field 827: 828: qs+=",%s=%s"%(fieldTmp,argv[field]) 829: 830: 831: addList={} 832: for q in qs.split(","): 833: name=re.sub("r'+'"," ",q.split("=")[0].lower()) 834: value=q.split("=")[1] 835: value=re.sub(r'\+'," ",value) 836: value=urllib.unquote(value) 837: if name=="-table": 838: table=urllib.unquote(value) 839: elif name=="-format": 840: format=urllib.unquote(value) 841: elif (not (name[0]=="-" or name[0]=="_")) and (not len(value)==0): 842: addList[urllib.unquote(name)]=urllib.unquote(value) 843: 844: keyList=[] 845: valueList=[] 846: for x in addList.keys(): 847: keyList.append("\""+x+"\"") 848: valueList.append(sql_quote(addList[x])) 849: 850: keyString=string.join(keyList,",") 851: valueString=string.join(valueList,",") 852: 853: queryString="INSERT INTO %s (%s) VALUES (%s)"%(table,keyString,valueString) 854: 855: self.ZSQLSimpleSearch(queryString) 856: 857: if RESPONSE and format: 858: return RESPONSE.redirect(format) 859: else: 860: return True 861: 862: def ZSQLChange(self,format=None,RESPONSE=None,USE_FORM=None,**argv): 863: """change entries""" 864: #qs=self.REQUEST['QUERY_STRING'] 865: # very bad hack 866: 867: qs_temp=[] 868: if USE_FORM or RESPONSE: 869: for a in self.REQUEST.form.keys(): 870: qs_temp.append(a+"="+urllib.quote(str(self.REQUEST.form[a]))) 871: 872: 873: 874: for field in argv.keys(): 875: 876: if field[0]=="_": 877: fieldTmp="-"+field[1:] 878: else: 879: fieldTmp=field 880: 881: qs_temp.append("%s=%s"%(fieldTmp,argv[field])) 882: 883: 884: changeList=[] 885: 886: for q in qs_temp: 887: 888: name=urllib.unquote(re.sub("r'+'"," ",q.split("=")[0].lower())) 889: value="=".join(q.split("=")[1:]) 890: value=re.sub(r'\+'," ",value) 891: value=urllib.unquote(value) 892: 893: if name=="-table": 894: table=urllib.unquote(value) 895: elif name=="-identify": 896: identify=urllib.unquote(value) 897: identify=identify.split("=")[0]+"="+sql_quote(identify.split("=")[1]) 898: elif name=="-format": 899: format=urllib.unquote(value) 900: elif (not (name[0]=="-" or name[0]=="_")) and (not len(value)==0): 901: 902: changeList.append("\""+name+"\"="+sql_quote(urllib.unquote(value))) 903: 904: changeString=string.join(changeList,",") 905: 906: queryString="UPDATE %s SET %s WHERE %s"%(table,changeString,identify) 907: 908: self.ZSQLSimpleSearch(queryString) 909: 910: 911: if RESPONSE and format: 912: return RESPONSE.redirect(format) 913: else: 914: return True 915: 916: 917: def ZSQLFindIndexed(self,qs="",select="oid,*",storename=None,indexedFields=['data_line'],restrictField='id_text',**argv): 918: """find2""" 919: 920: for index in self.ZopeFind(self,obj_ids=indexedFields): 921: txt=argv.get(index[0],None) 922: if txt: 923: oids=index[1].getIndex()[txt] 924: 925: search1= self.ZSQLFind(qs=qs,select=select,storename=storename,tableExt=tableList[1],restrictField=restrictField,NoQuery='yes',NoLimit='yes',**argv) 926: 927: search2 = self.ZSQLFind(tableExt=tableList[0],qs=qs,select=select,storename=storename,restrictConnect=(tableList[0]+"."+restrictField,search1),**argv) 928: return search2 929: 930: def ZSQLFind2(self,qs="",select="oid,*",storename=None,tableList=['cdli_translit','cdli_cat'],restrictField='id_text',**argv): 931: """find2""" 932: 933: search1= self.ZSQLFind(qs=qs,select=select,storename=storename,tableExt=tableList[1],restrictField=restrictField,NoQuery='yes',NoLimit='yes',**argv) 934: 935: search2 = self.ZSQLFind(tableExt=tableList[0],qs=qs,select=select,storename=storename,restrictConnect=(tableList[0]+"."+restrictField,search1),**argv) 936: return search2 937: 938: 939: 940: def ZSQLFind(self,qs="",select="oid,*",storename=None,tableExt=None,NoQuery=None,NoLimit=None,restrictField=None,restrictConnect=None,filter=None,**argv): 941: """search in database""" 942: 943: def delEmpty(list): 944: """"loesche leere elemente aus der liste""" 945: ret=[] 946: for x in list: 947: splitted=x.split("=") 948: if (len(splitted)>1) and not (splitted[1]==' '): 949: ret.append(x) 950: return ret 951: 952: if qs=="": 953: #kein querystring ubergeben 954: if self.REQUEST['QUERY_STRING']: 955: qs=self.REQUEST['QUERY_STRING'] 956: 957: qs=string.join(qs.split("&"),",") 958: 959: for field in argv.keys(): 960: if field[0]=="_": 961: fieldTmp="-"+field[1:] 962: else: 963: fieldTmp=urllib.unqoute(field) 964: 965: qs+=",%s=%s"%(fieldTmp,argv[field]) 966: 967: else: 968: 969: qs=self.REQUEST.SESSION.get('query','') 970: 971: for field in argv.keys(): 972: if field[0]=="_": 973: fieldTmp="-"+field[1:] 974: else: 975: fieldTmp=field 976: 977: qs+=",%s=%s"%(fieldTmp,argv[field]) 978: 979: 980: 981: else: 982: self.REQUEST['QUERY_STRING']=qs 983: 984: qs=string.join(qs.split("&"),",") 985: 986: 987: 988: qs=re.sub("\\+"," ",qs)#TODO: Austauschen da Leerzeichen bei http-get durch + ersetzt wird, generell sollte alles auf post umgeschrieben werden. vom search formular. 989: 990: qs=string.join(delEmpty(qs.split(",")),",") 991: 992: if storename: 993: """store""" 994: else: 995: storename="foundCount" 996: 997: #store query for further usage 998: self.REQUEST.SESSION['query']=qs 999: 1000: #print "calling Query with",repr(NoQuery) 1001: ret=self.parseQueryString(qs,"-",select=select,storemax="yes",storename=storename,tableExt=tableExt,NoQuery=NoQuery,NoLimit=NoLimit,restrictField=restrictField,restrictConnect=restrictConnect,filter=filter) 1002: #print self.REQUEST.SESSION["foundCount"] 1003: 1004: 1005: 1006: return ret 1007: 1008: def ZSQLFoundCountLen(self,var): 1009: return len(var) 1010: 1011: def ZSQLFoundCount(self,qs="",select="*",storename=None): 1012: 1013: ## if qs=="": 1014: 1015: ## if self.REQUEST['QUERY_STRING']: 1016: 1017: ## qs=self.REQUEST['QUERY_STRING'] 1018: ## qs=string.join(qs.split("&"),",") 1019: ## else: 1020: 1021: ## qs=self.REQUEST.SESSION['query'] 1022: ## else: 1023: ## qs=string.join(qs.split("&"),",") 1024: 1025: 1026: ## temp= self.parseQueryString(qs,"-",select=select,storemax="yes",nostore="yes") 1027: if storename: 1028: """store""" 1029: else: 1030: storename="foundCount" 1031: 1032: return self.REQUEST.SESSION[storename]['count'] 1033: 1034: def ZSQLRangeStart(self,storename=None): 1035: 1036: if storename: 1037: """store""" 1038: else: 1039: storename="foundCount" 1040: 1041: return int(self.REQUEST.SESSION[storename]['rangeStart'])+1 1042: 1043: def ZSQLRangeSize(self,storename=None): 1044: 1045: if storename: 1046: """store""" 1047: else: 1048: storename="foundCount" 1049: 1050: return self.REQUEST.SESSION[storename]['rangeSize'] 1051: 1052: def ZSQLRangeEnd(self,storename=None): 1053: 1054: if storename: 1055: """store""" 1056: else: 1057: storename="foundCount" 1058: 1059: return str(min(int(self.REQUEST.SESSION[storename]['rangeEnd']),int(self.REQUEST.SESSION[storename]['count']))) 1060: 1061: def ZSQLNewQuery(self,linkText,storename=None,**argv): 1062: """suche neu""" 1063: return self.ZSQLNewSearch(linkText,storename,url=self.REQUEST['URL'],args=argv) 1064: 1065: def ZSQLNewSearch(self,linkText,storename=None,url=None,args=None,**argv): 1066: """suche mit alten parametern bis auf die in argv getauschten""" 1067: 1068: if storename: 1069: """store""" 1070: else: 1071: storename="foundCount" 1072: qs=self.REQUEST.SESSION[storename]['qs'] 1073: 1074: if args: 1075: argv=args 1076: 1077: querys=qs.split(",") 1078: argList=[] 1079: arg="" 1080: queryList=[x.split("=")[0] for x in querys] 1081: 1082: for argTmp in argv.keys(): 1083: 1084: 1085: arg=argTmp[0:] 1086: if arg[0]=="_":arg="-"+argTmp[1:] # aender _ in - als standard 1087: 1088: if arg not in queryList: # noch nicht drin 1089: querys.append("%s=%s"%(arg,argv[argTmp])) 1090: argList.append(arg) 1091: 1092: 1093: 1094: newquery=[] 1095: for query in querys: 1096: queryArg=query.split("=")[0] 1097: qarg=queryArg[0:] 1098: if qarg[0]=="-":qarg="_"+queryArg[1:] # aender _ in - als standard 1099: 1100: if queryArg in argList: 1101: newquery.append("%s=%s"%(queryArg,argv[qarg])) 1102: else: 1103: newquery.append(query) 1104: 1105: if url: 1106: str=url+"?"+"&".join(newquery) 1107: else: 1108: str="ZSQLSearch?"+"&".join(newquery) 1109: 1110: return """<a href="%s"> %s</a>"""%(str,linkText) 1111: 1112: def parseQueryString(self,qs,iCT,storemax="no",select=None,nostore=None,storename=None,tableExt=None,NoQuery=None,NoLimit=None,restrictField=None,restrictConnect=None,filter=None): 1113: """analysieren den QueryString""" 1114: 1115: 1116: #setzte generische werte 1117: 1118: lop="AND" # standardsuche mit and 1119: max="ALL" #standard alle auswaehlen 1120: maxstr="" 1121: whereList=[] 1122: sort="" 1123: op="bw" 1124: opfields={} 1125: lopfields={} #Verknuepfung bei mehrfachauswahl von einem feld 1126: sortfields={} #order of sortfields 1127: sortAllFields=None 1128: skip="" 1129: rangeStart=0 1130: limit=0 1131: searchFields={} 1132: searchFieldsOnly={} 1133: queryTemplate=[] 1134: 1135: if not select: 1136: select="oid,*" 1137: 1138: #check for op 1139: splitted=qs.split(",") 1140: 1141: if tableExt: 1142: table=tableExt 1143: 1144: if restrictField: 1145: select=restrictField 1146: 1147: 1148: #erster durchgang suche operatoren 1149: for q in splitted: 1150: 1151: name=re.sub("r'+'"," ",q.split("=")[0].lower()) 1152: try: 1153: value=urllib.unquote(q.split("=",1)[1]) 1154: except: 1155: value="" 1156: #print "Hi",name[0:3],q 1157: if name[0:3]==iCT+"op": 1158: op=value 1159: 1160: field=name[4:] 1161: opfields[field]=op 1162: 1163: if name[0:4]==iCT+"lop": 1164: lop=value 1165: 1166: field=name[5:] 1167: lopfields[field]=lop 1168: 1169: if name[0:10]==iCT+"sortorder": 1170: #sort=value 1171: 1172: field=name[11:] 1173: sortAllFields=None 1174: #no field selected 1175: if field=="": 1176: sortAllFields=value 1177: else: 1178: sortfields[field]=value 1179: 1180: #zweiter durchgang analysiere felder 1181: for q in qs.split(","): 1182: 1183: 1184: 1185: name=re.sub("r'+'"," ",q.split("=")[0].lower()) 1186: try: 1187: value=urllib.unquote(q.split("=",1)[1]) 1188: except: 1189: value="" 1190: 1191: punktsplit=name.split(".") #sonderfall feld mit punkten(tabelle.suchFeld.ausgewaehltesFeld,feldinoriginal), d.h. suche in anderer tabelle: 1192: 1193: #analysiere alle anderen faelle 1194: if name==iCT+"lop": 1195: lop=value 1196: elif name==iCT+"table": 1197: if not tableExt: 1198: table=value 1199: elif name==iCT+"select": 1200: if not restrictField: 1201: select=value 1202: elif name==iCT+"max": 1203: if not NoLimit: 1204: maxstr="LIMIT "+str(value) 1205: limit=str(value) 1206: elif name==iCT+"skip": 1207: skip="OFFSET "+str(value) 1208: rangeStart=str(value) 1209: elif name==iCT+"join": 1210: whereList.append(value) 1211: elif name==iCT+"sort": 1212: sortstrs=[] 1213: for word in value.split(','): 1214: wordstr=word.lstrip().rstrip() 1215: if sortAllFields: 1216: order=sortAllFields 1217: else: 1218: order=sortfields.get(wordstr,'ASC') 1219: 1220: if not (wordstr == ""): 1221: sortstrs.append(wordstr+" "+order) 1222: if len(sortstrs)>0: 1223: sort="ORDER BY "+string.join(sortstrs,',') 1224: 1225: 1226: elif name==iCT+"token": 1227: if not nostore=="yes": 1228: self.REQUEST.SESSION['token']=value 1229: 1230: elif name==iCT+"op": 1231: op=value 1232: 1233: 1234: 1235: elif (not name[0]==iCT) and len(punktsplit)==4: 1236: if opfields.has_key(name): 1237: op=opfields[name] 1238: else: 1239: op="ct" 1240: namealt=name 1241: name="LOWER("+punktsplit[1]+")" 1242: 1243: if op=="ct": 1244: tmp=(name+" LIKE "+sql_quote("%"+value+"%")) 1245: elif op=="gt": 1246: tmp=(name+">"+sql_quote(value)) 1247: elif op=="lt": 1248: tmp=(name+"<"+sql_quote(value)) 1249: elif op=="eq": 1250: tmp=(name+"="+sql_quote(value)) 1251: elif op=="bw": 1252: tmp=(name+" LIKE "+sql_quote(value+"%")) 1253: elif op=="ew": 1254: tmp=(name+" LIKE "+sql_quote("%"+value)) 1255: elif op=="all": 1256: tmps=[] 1257: for word in value.split(" "): 1258: tmps.append(name+" LIKE "+sql_quote("%"+word+"%")) 1259: 1260: tmp=string.join(tmps,' AND ') 1261: 1262: elif op=="numerical": 1263: term=analyseIntSearch(value) 1264: tmp=(name+" "+term) 1265: elif op=="grep": 1266: tmp=(name+" ~* "+sql_quote(value)) 1267: elif op=="one": 1268: tmps=[] 1269: for word in value.split(" "): 1270: tmps.append(name+" LIKE "+sql_quote("%"+word+"%")) 1271: 1272: tmp=string.join(tmps,' OR ') 1273: 1274: op="all" 1275: 1276: 1277: searchTmp="""%s in (select %s from %s where %s)"""%(punktsplit[3],punktsplit[2],punktsplit[0],tmp) 1278: 1279: queryTemplate.append(searchTmp) 1280: 1281: elif (not name[0]==iCT) and (not len(value)==0): 1282: 1283: #print "OP",op,name 1284: value=value.lower() 1285: tmp="" 1286: if opfields.has_key(name): 1287: op=opfields[name] 1288: else: 1289: op="ct" 1290: namealt=name 1291: name="LOWER("+name+")" 1292: if op=="ct": 1293: tmp=(name+" LIKE "+sql_quote("%"+value+"%")) 1294: elif op=="gt": 1295: tmp=(name+">"+sql_quote(value)) 1296: elif op=="lt": 1297: tmp=(name+"<"+sql_quote(value)) 1298: elif op=="eq": 1299: tmp=(name+"="+sql_quote(value)) 1300: elif op=="bw": 1301: tmp=(name+" LIKE "+sql_quote(value+"%")) 1302: elif op=="ew": 1303: tmp=(name+" LIKE "+sql_quote("%"+value)) 1304: elif op=="all": 1305: tmps=[] 1306: for word in value.split(" "): 1307: tmps.append(name+" LIKE "+sql_quote("%"+word+"%")) 1308: 1309: tmp=string.join(tmps,' AND ') 1310: 1311: elif op=="numerical": 1312: term=analyseIntSearch(value) 1313: tmp=(name+" "+term) 1314: elif op=="grep": 1315: tmp=(name+" ~* "+sql_quote(value)) 1316: elif op=="one": 1317: tmps=[] 1318: for word in value.split(" "): 1319: tmps.append(name+" LIKE "+sql_quote("%"+word+"%")) 1320: 1321: tmp=string.join(tmps,' OR ') 1322: 1323: op="all" 1324: 1325: if (not tableExt) or (namealt.split('.')[0]==tableExt): 1326: if searchFields.has_key(namealt): 1327: searchFields[namealt]+=lopfields.get(name,'OR')+" "+tmp 1328: searchFieldsOnly[namealt]+=lopfields.get(name,'OR')+" "+value 1329: else: 1330: searchFields[namealt]=tmp 1331: searchFieldsOnly[namealt]=value 1332: 1333: 1334: whereList=["("+searchFields[x]+")" for x in searchFields.keys()] 1335: whereList+=queryTemplate 1336: 1337: if len(whereList)>0: 1338: if filter: 1339: whereStr="("+string.join(whereList," "+lop+" ")+") AND "+filter 1340: else: 1341: whereStr=string.join(whereList," "+lop+" ") 1342: where="WHERE "+whereStr 1343: else: 1344: if filter: 1345: where="WHERE "+filter 1346: else: 1347: where="" 1348: 1349: if restrictConnect: 1350: 1351: if len(where)==0: 1352: where="WHERE "+restrictConnect[0]+" in ("+restrictConnect[1]+")" 1353: else: 1354: where+="and "+restrictConnect[0]+" in ("+restrictConnect[1]+")" 1355: 1356: #print "QE",table 1357: #print (select,table,where,sort,maxstr,skip) 1358: query="SELECT %s FROM %s %s %s %s %s"%(select,table,where,sort,maxstr,skip) 1359: 1360: if not nostore=="yes": 1361: 1362: self.REQUEST.SESSION['qs']=opfields 1363: #print "IAMHERE again:", query 1364: 1365: if storename and (not NoQuery): 1366: 1367: query2="SELECT count(*) FROM %s %s"%(table,where) 1368: 1369: #print "QUERYSTRING:",self.REQUEST.SESSION[storename]['queryString2'] 1370: if not self.REQUEST.SESSION.has_key(storename): 1371: self.REQUEST.SESSION[storename]={} 1372: 1373: self.REQUEST.SESSION[storename]['qs']=qs #sichere Querystring 1374: 1375: if self.REQUEST.SESSION[storename].has_key('queryString2'): 1376: 1377: if not self.REQUEST.SESSION[storename]['queryString2']==query2: 1378: #print "HOOOOO",storename 1379: self.REQUEST.SESSION[storename]['queryString2']=query2 1380: try: 1381: self.REQUEST.SESSION[storename]['count']=self.ZSQLSimpleSearch(query2)[0].count 1382: except: 1383: self.REQUEST.SESSION[storename]['count']=0 1384: #print "QUERY",query2,"::::",self.REQUEST.SESSION[storename]['queryString2'] 1385: 1386: else: 1387: 1388: self.REQUEST.SESSION[storename]['queryString2']=query2 1389: if self.ZSQLSimpleSearch(query2): 1390: self.REQUEST.SESSION[storename]['count']=self.ZSQLSimpleSearch(query2)[0].count 1391: else: 1392: self.REQUEST.SESSION[storename]['count']=0 1393: #print "QUERYNEW",self.REQUEST.SESSION[storename]['queryString2'] 1394: 1395: 1396: self.REQUEST.SESSION[storename]['rangeStart']=rangeStart 1397: if limit=="all": 1398: self.REQUEST.SESSION[storename]['rangeEnd']=self.REQUEST.SESSION[storename]['count'] 1399: else: 1400: self.REQUEST.SESSION[storename]['rangeEnd']=int(rangeStart)+int(limit) 1401: self.REQUEST.SESSION[storename]['rangeSize']=limit 1402: self.REQUEST.SESSION[storename]['searchFields']=searchFields 1403: self.REQUEST.SESSION[storename]['searchFieldsOnly']=searchFieldsOnly 1404: 1405: 1406: if not NoQuery: 1407: 1408: return self.ZSQLQuery(query) 1409: else: 1410: 1411: return query 1412: 1413: def ZSQLUniqueQuery(self,value,returns): 1414: """unique relation""" 1415: if returns: 1416: statement="""select %s from %s where %s = '%s' """%(returns[1],returns[0],returns[2],value) 1417: founds=self.ZSQLSimpleSearch(statement) 1418: if founds: 1419: return getattr(founds[0],returns[1]) 1420: else: 1421: return value 1422: else: 1423: return value 1424: 1425: def ZSQLQuery(self,query,debug=None): 1426: """query""" 1427: if debug: 1428: zLOG.LOG("ZSQLQuery", zLOG.INFO, query) 1429: 1430: return self.ZSQLSimpleSearch(query) 1431: 1432: 1433: def ZSQLSearch(self): 1434: """To be done""" 1435: 1436: formatfile=self.REQUEST['URL1'] #generisch redirect zur gleichen url 1437: 1438: #zerlege querystring in key value paare 1439: #TODO: check if this is really necessary, use argv** 1440: 1441: rq=self.REQUEST['QUERY_STRING'] 1442: querys=rq.split("&") 1443: for querytemp in querys: #zerg 1444: query=querytemp.split("=") 1445: 1446: try: 1447: if query[0].lower()=="-format": 1448: formatfile=query[1] 1449: except: 1450: pass 1451: 1452: #sichern 1453: self.REQUEST.SESSION['query']=string.join(self.REQUEST['QUERY_STRING'].split("&"),",") 1454: self.REQUEST.SESSION['come_from_search']="yes" 1455: 1456: return self.REQUEST.RESPONSE.redirect(urllib.unquote(formatfile)) 1457: 1458: 1459: def ZSQLint(self,string): 1460: try: 1461: 1462: return(int(string)) 1463: except: 1464: return 0 1465: 1466: def getZSQLSearchFieldsList(self,storename="foundCount"): 1467: """get searchfieldList""" 1468: #print self.REQUEST.SESSION[storename]['searchFields'].keys() 1469: return self.REQUEST.SESSION[storename]['searchFieldsOnly'].keys() 1470: 1471: def getZSQLSearchFields(self,field,storename="foundCount"): 1472: """get searchfield""" 1473: #print "SF",self.REQUEST.SESSION[storename]['searchFields'] 1474: 1475: return self.REQUEST.SESSION[storename]['searchFieldsOnly'][field] 1476: 1477: 1478: def nextLink(self,html,storename="foundCount"): 1479: """nextLink""" 1480: 1481: try: 1482: limit=self.REQUEST.SESSION[storename]['rangeSize'] 1483: if int(limit)==0 : 1484: limit="1" 1485: newRangeStart=int(self.REQUEST.SESSION[storename]['rangeStart'])+int(limit) 1486: except: 1487: limit=1 1488: newRangeStart=0 1489: 1490: if newRangeStart>=self.REQUEST.SESSION[storename]['count']: 1491: #newRangeStart=self.REQUEST.SESSION[storename]['count']-1 1492: return "" 1493: 1494: #create new query string 1495: 1496: if self.REQUEST['QUERY_STRING']=="": 1497: qs=self.REQUEST.SESSION['query'] 1498: 1499: queries=string.split(qs,",")[0:] 1500: 1501: 1502: else: 1503: qs=self.REQUEST['QUERY_STRING'] 1504: queries=string.split(qs,"&")[0:] 1505: 1506: 1507: 1508: newquery=[] 1509: 1510: skipFound=0 1511: 1512: for query in queries: 1513: 1514: if query[0]=="_" : query="-"+query[1:] 1515: 1516: if query.split("=")[0].lower()=="-skip": 1517: 1518: skipFound=1 1519: query="-skip=%i"%newRangeStart 1520: newquery.append(query) 1521: 1522: if skipFound==0 : 1523: query="-skip=%i"%newRangeStart 1524: newquery.append(query) 1525: 1526: newquerystring=string.join(newquery,"&") 1527: return "<a href='%s'>%s</a>"%(self.REQUEST['URL']+"?"+newquerystring,html) 1528: 1529: 1530: 1531: def prevLink(self,html,storename="foundCount"): 1532: """prev link""" 1533: 1534: try: 1535: limit=self.REQUEST.SESSION[storename]['rangeSize'] 1536: if int(limit)==0 : 1537: limit="1" 1538: newRangeStart=int(self.REQUEST.SESSION[storename]['rangeStart'])-int(limit) 1539: except: 1540: limit=1 1541: newRangeStart=0 1542: if newRangeStart <0: 1543: return "" 1544: 1545: #print "limit",limit,newRangeStart,int(self.REQUEST.SESSION[storename]['rangeStart']) 1546: 1547: if newRangeStart<0: 1548: newRangeStart=0 1549: 1550: #create new query string 1551: 1552: if self.REQUEST['QUERY_STRING']=="": 1553: qs=self.REQUEST.SESSION['query'] 1554: #qs=re.sub(r'_','-',qs) #aendern fuer query 1555: queries=string.split(qs,",") 1556: 1557: 1558: else: 1559: qs=self.REQUEST['QUERY_STRING'] 1560: queries=string.split(qs,"&") 1561: 1562: 1563: 1564: newquery=[] 1565: 1566: skipFound=0 1567: 1568: for query in queries: 1569: #print query.split("=")[0] 1570: 1571: if query[0]=="_" : query[0]="-" 1572: 1573: if query.split("=")[0].lower()=="-skip": 1574: #print"HI" 1575: query="-skip=%i"%newRangeStart 1576: skipFound=1 1577: newquery.append(query) 1578: 1579: if skipFound==0 : 1580: query="-skip=%i"%newRangeStart 1581: newquery.append(query) 1582: 1583: newquerystring=string.join(newquery,"&") 1584: 1585: return "<a href='%s'>%s</a>"%(self.REQUEST['URL']+"?"+newquerystring,html) 1586: 1587: 1588: 1589: def prevLink_old(self,html): 1590: """prev link""" 1591: if self.REQUEST['QUERY_STRING']=="": 1592: qs=self.REQUEST.SESSION['query'] 1593: else: 1594: qs=self.REQUEST['QUERY_STRING'] 1595: max=re.search(r'max\=(.*)\,',qs.lower()) 1596: offset=re.search(r'offset\=(.*)\,',qs.lower()) 1597: if not offset: 1598: offsetnew=0 1599: else: 1600: offsetnew=int(offset)-max 1601: if offsetnew<0: 1602: offsetnew=0 1603: queries=string.split(qs,",") 1604: newquery=[] 1605: if offset: 1606: for query in queries: 1607: if query.split("=")[0].lower()=="offset": 1608: query="-offset=%i"%offsetnew 1609: newquery.append(query) 1610: newquerystring=string.join(newquery,"&") 1611: else: 1612: queries.append("-offset=%i"%offsetnew) 1613: newquerystring=string.join(queries,"&") 1614: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html) 1615: 1616: def nextLink_old(self,html): 1617: """prev link""" 1618: if self.REQUEST['QUERY_STRING']=="": 1619: qs=self.REQUEST.SESSION['query'] 1620: else: 1621: qs=self.REQUEST['QUERY_STRING'] 1622: max=re.search(r'max\=(.*)\,',qs.lower()) 1623: 1624: offset=re.search(r'offset\=(.*)\,',qs.lower()) 1625: if not offset: 1626: offsetnew=1 1627: else: 1628: offsetnew=int(offset)+int(max) 1629: if offsetnew<0: 1630: offsetnew=0 1631: queries=string.split(qs,",") 1632: newquery=[] 1633: if offset: 1634: for query in queries: 1635: 1636: if query.split("=")[0].lower()=="-offset": 1637: query="-offset=%i"%offsetnew 1638: newquery.append(query) 1639: newquerystring=string.join(newquery,"&") 1640: else: 1641: queries.append("-offset=%i"%offsetnew) 1642: newquerystring=string.join(queries,"&") 1643: 1644: return "<a href='%s'>%s</a>"%(self.actualPath()+"?"+newquerystring,html) 1645: 1646: 1647: manage_addZSQLExtendFolderForm=DTMLFile('ZSQLExtendFolderAdd', globals()) 1648: 1649: def manage_addZSQLExtendFolder(self, id, title='', label='', description='', 1650: createPublic=0, 1651: createUserF=0, 1652: connection_id=None, 1653: REQUEST=None): 1654: """Add a new Folder object with id *id*. 1655: 1656: If the 'createPublic' and 'createUserF' parameters are set to any true 1657: value, an 'index_html' and a 'UserFolder' objects are created respectively 1658: in the new folder. 1659: """ 1660: 1661: 1662: ob=ZSQLExtendFolder() 1663: ob.id=str(id) 1664: ob.title=title 1665: self._setObject(id, ob) 1666: ob=self._getOb(id) 1667: setattr(ob,'label',label) 1668: setattr(ob,'description',description) 1669: setattr(ob,'connection_id',connection_id) 1670: 1671: checkPermission=getSecurityManager().checkPermission 1672: 1673: if createUserF: 1674: if not checkPermission('Add User Folders', ob): 1675: raise Unauthorized, ( 1676: 'You are not authorized to add User Folders.' 1677: ) 1678: ob.manage_addUserFolder() 1679: 1680: if createPublic: 1681: if not checkPermission('Add Page Templates', ob): 1682: raise Unauthorized, ( 1683: 'You are not authorized to add Page Templates.' 1684: ) 1685: ob.manage_addProduct['PageTemplates'].manage_addPageTemplate( 1686: id='index_html', title='') 1687: 1688: if REQUEST is not None: 1689: return self.manage_main(self, REQUEST, update_menu=1) 1690: 1691: 1692: 1693: class ZSQLBibliography(Folder,ZSQLExtendFolder): 1694: """Bibliography""" 1695: meta_type="ZSQLBibliography" 1696: def getLabel(self): 1697: try: 1698: return self.label 1699: except: 1700: return "" 1701: 1702: def getDescription(self): 1703: try: 1704: return self.description 1705: except: 1706: return "" 1707: 1708: def changeZSQLBibliographyForm(self): 1709: """change folder config""" 1710: pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','changeZSQLBibliographyForm.zpt')).__of__(self) 1711: return pt() 1712: 1713: 1714: def content_html(self): 1715: """template fuer content""" 1716: 1717: try: 1718: obj=getattr(self,"ZSQLBibliography_template") 1719: return obj() 1720: except: 1721: pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','ZSQLBibliography_template_standard.zpt')).__of__(self) 1722: pt.content_type="text/html" 1723: return pt() 1724: 1725: def changeZSQLBibliography(self,tableName,label,description,connection_id=None,REQUEST=None): 1726: """change it""" 1727: self.connection_id=connection_id 1728: self.tableName=tableName 1729: self.label=label 1730: self.description=description 1731: 1732: if REQUEST is not None: 1733: return self.manage_main(self, REQUEST) 1734: 1735: manage_options=Folder.manage_options+( 1736: {'label':'Main Config','action':'changeZSQLBibliographyForm'}, 1737: {'label':'Generate RDF Template','action':'generateRDFTemplate'}, 1738: {'label':'Generate XML Template','action':'generateXMLTemplate'}, 1739: ) 1740: 1741: 1742: def getFieldLabel(self,fields,field): 1743: """get labels""" 1744: try: 1745: ret =fields[0][field] 1746: if ret == "": 1747: return field 1748: else: 1749: return ret 1750: except: 1751: return field 1752: 1753: def getFieldValue(self,found,field): 1754: """get value""" 1755: try: 1756: ret=getattr(found,field) 1757: if ret == "": 1758: return None 1759: else: 1760: return ret 1761: except: 1762: return None 1763: 1764: def findTagsFromMapping(self,referenceType): 1765: """gib hash mit label -> generic zurueck""" 1766: self.referencetypes=self.ZopeFind(self.standardMD) 1767: bibdata={} 1768: retdata={} 1769: fieldlist=self.standardMD.fieldList 1770: 1771: for referenceTypeF in self.referencetypes: 1772: #print referenceType,referenceTypeF[1].title 1773: if referenceTypeF[1].title == referenceType: 1774: bibdata[referenceTypeF[1].title]=referenceTypeF[1].fields 1775: bibdata['data']=referenceTypeF[1] 1776: self.fields=bibdata[referenceType] 1777: for field in fieldlist: 1778: if referenceTypeF[1].getValue(field)[0]==None: 1779: retdata[field]=field 1780: else: 1781: retdata[field]=referenceTypeF[1].getValue(field)[0] 1782: 1783: return retdata,fieldlist 1784: 1785: def findLabelsFromMapping(self,referenceType): 1786: """gib hash mit label -> generic zurueck""" 1787: self.referencetypes=self.ZopeFind(self.standardMD) 1788: bibdata={} 1789: retdata={} 1790: fieldlist=self.standardMD.fieldList 1791: 1792: for referenceTypeF in self.referencetypes: 1793: #print referenceType,referenceTypeF[1].title 1794: if referenceTypeF[1].title == referenceType: 1795: bibdata[referenceTypeF[1].title]=referenceTypeF[1].fields 1796: bibdata['data']=referenceTypeF[1] 1797: self.fields=bibdata[referenceType] 1798: for field in fieldlist: 1799: retdata[field]=referenceTypeF[1].getValue(field)[1] 1800: 1801: return retdata,fieldlist 1802: 1803: def createRDFTag(self,tag,content,namespace="cdli"): 1804: """create RDF""" 1805: if content: 1806: tag=namespace+":"+tag 1807: if (type(content) is StringType) or (type(content) is UnicodeType): 1808: ret=""" <%s>%s</%s>"""%(tag,escape(content),tag) 1809: else: 1810: ret=""" <%s>%s</%s>"""%(tag,content,tag) 1811: return ret.decode('latin-1') 1812: else: 1813: return "" 1814: 1815: def createIndexTag(self,tag,content): 1816: """create tag""" 1817: 1818: if content: 1819: if (type(content) is StringType) or (type(content) is UnicodeType): 1820: ret=""" <%s>%s</%s>"""%(tag,escape(content),tag) 1821: else: 1822: ret=""" <%s>%s</%s>"""%(tag,content,tag) 1823: return ret.decode('latin-1') 1824: 1825: else: 1826: return "" 1827: 1828: def getXML2(self): 1829: """crate index meta""" 1830: 1831: fn=os.path.splitext(self.REQUEST['fn'])[0]+"." 1832: self.REQUEST['fn']=fn 1833: 1834: pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','record2.xml')).__of__(self) 1835: pt.content_type="text/xml" 1836: return pt() 1837: 1838: 1839: def generateRDFTemplate(self,REQUEST=None): 1840: """generateRDFtemplate""" 1841: zt=ZopePageTemplate('record.rdf') 1842: self._setObject('record.rdf',zt) 1843: default_content_fn = os.path.join(package_home(globals()), 1844: 'zpt','record.rdf') 1845: text = open(default_content_fn).read() 1846: zt.pt_edit(text, 'text/xml') 1847: 1848: 1849: if REQUEST is not None: 1850: return self.manage_main(self, REQUEST) 1851: 1852: def generateXMLTemplate(self,REQUEST=None): 1853: """generateXMLtemplate""" 1854: zt=ZopePageTemplate('record.xml') 1855: self._setObject('record.xml',zt) 1856: default_content_fn = os.path.join(package_home(globals()), 1857: 'zpt','record.xml') 1858: text = open(default_content_fn).read() 1859: zt.pt_edit(text, 'text/xml') 1860: 1861: 1862: if REQUEST is not None: 1863: return self.manage_main(self, REQUEST) 1864: def getMetaDataRDF(self): 1865: """crate index meta""" 1866: find=self.ZopeFind(self,obj_ids=["record.rdf"]) 1867: if not find: 1868: pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','record.rdf')).__of__(self) 1869: pt.content_type="text/xml" 1870: return pt() 1871: else: 1872: return find[0][1]() 1873: 1874: def getMetaDataXML(self): 1875: """crate index meta""" 1876: find=self.ZopeFind(self,obj_ids=["record.xml"]) 1877: if not find: 1878: pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt','record.xml')).__of__(self) 1879: pt.content_type="text/xml" 1880: return pt() 1881: else: 1882: return find[0][1]() 1883: 1884: 1885: def getMetaDatasXML(self): 1886: """index""" 1887: # check if the request's host part was OK 1888: http_host = self.REQUEST['HTTP_HOST'] 1889: host_port = self.REQUEST['SERVER_PORT'] 1890: fix_host = None 1891: if http_host and http_host.rfind(host_port) == -1: 1892: #print "HTTP_HOST needs fixing!" 1893: fix_host = http_host + ":" + host_port 1894: 1895: ret="""<?xml version="1.0" ?> 1896: <index>""" 1897: for found in self.ZSQLSimpleSearch("select oid from %s limit ALL"%self.tableName): 1898: base_url = self.absolute_url() 1899: if fix_host: 1900: #print "replacing ", http_host, " by ", fix_host 1901: base_url = string.replace(base_url, http_host, fix_host, 1) 1902: 1903: link=base_url+"/"+"record.html?oid=%i"%found.oid 1904: metalink=base_url+"/"+"getMetaDataXML?oid=%i"%found.oid 1905: 1906: ret+="""<resource resourceLink="%s" metaLink="%s"/>\n"""%(link,metalink) 1907: 1908: return ret+"\n</index>" 1909: 1910: 1911: manage_addZSQLBibliographyForm=DTMLFile('ZSQLBibliographyAdd', globals()) 1912: 1913: def manage_addZSQLBibliography(self, id, tableName,label,description,title='', 1914: createPublic=0, 1915: createUserF=0, 1916: connection_id=None, 1917: REQUEST=None): 1918: """Add a new Folder object with id *id*. 1919: 1920: If the 'createPublic' and 'createUserF' parameters are set to any true 1921: value, an 'index_html' and a 'UserFolder' objects are created respectively 1922: in the new folder. 1923: """ 1924: 1925: 1926: ob=ZSQLBibliography() 1927: ob.id=str(id) 1928: ob.title=title 1929: self._setObject(id, ob) 1930: ob=self._getOb(id) 1931: setattr(ob,'tableName',tableName) 1932: setattr(ob,'label',label) 1933: setattr(ob,'description',description) 1934: setattr(ob,'connection_id',connection_id) 1935: 1936: checkPermission=getSecurityManager().checkPermission 1937: 1938: if createUserF: 1939: if not checkPermission('Add User Folders', ob): 1940: raise Unauthorized, ( 1941: 'You are not authorized to add User Folders.' 1942: ) 1943: ob.manage_addUserFolder() 1944: 1945: if createPublic: 1946: if not checkPermission('Add Page Templates', ob): 1947: raise Unauthorized, ( 1948: 'You are not authorized to add Page Templates.' 1949: ) 1950: ob.manage_addProduct['PageTemplates'].manage_addPageTemplate( 1951: id='index_html', title='') 1952: 1953: if REQUEST is not None: 1954: return self.manage_main(self, REQUEST, update_menu=1) 1955: 1956: 1957: 1958: