Mercurial > hg > ZDBInterface
view ZDBInterfaceFolder.py @ 10:283c01ebe96e
cleaned comments
author | casties |
---|---|
date | Wed, 15 Jun 2011 16:59:43 +0200 |
parents | 87087f4f4059 |
children | 09882d5a3989 |
line wrap: on
line source
''' Created on 14.2.2011 @author: casties ''' from OFS.Folder import Folder from Products.PageTemplates.PageTemplateFile import PageTemplateFile from AccessControl import getSecurityManager, Unauthorized import logging import re from DBInterface import * class ZDBInterfaceFolder(DBInterface, Folder): """Folder for database queries """ meta_type="ZDBInterfaceFolder" manage_options=Folder.manage_options+( {'label':'Config','action':'manage_editZDBInterfaceFolderForm'}, ) # management templates manage_editZDBInterfaceFolderForm=PageTemplateFile('zpt/editZDBInterfaceFolder',globals()) def __init__(self, id, title, connection_id=None, autocommit=False, default_search='bw'): """init""" self.id = id self.title = title # database connection id self.connection_id = connection_id # set db connection to autocommit self.autocommit = autocommit # default text search mode self.default_search = default_search def getSQLQuery(self, table, argv): """returns dict with SQL query string and args""" logging.debug("getSQLquery table=%s argv=%s"%(table,argv)) args = [] select = "*" order = None wheres = [] whereOp = "AND" processed = {} unprocessed = {} limit = None offset = None def doOp(op, param, val): # handles comparison operations in WHERE clause logging.debug("doop op=%s param=%s val=%s"%(op,param,val)) if isinstance(val, list): # join multiple parameters with spaces (makes sense with checkbox and -op=all) val = " ".join(val) # string comparisons are case-insensitive if isinstance(val, basestring): param = "LOWER(%s)"%param val = val.lower() if op == "eq": wheres.append(param + " = %s") args.append(val) elif op == "lt": wheres.append(param + " < %s") args.append(val) elif op == "gt": wheres.append(param + " > %s") args.append(val) elif op == "ew": wheres.append(param + " ILIKE %s") args.append("%" + val) elif op == "bw": wheres.append(param + " ILIKE %s") args.append(val + "%") elif op == "ct": wheres.append(param + " ILIKE %s") args.append("%" + val + "%") elif op == "all": words = [] for word in val.split(" "): words.append(param + " ILIKE %s") args.append("%" + word + "%") wheres.append("(" + " AND ".join(words) + ")") elif op == "one": words = [] for word in val.split(" "): words.append(param + " ILIKE %s") args.append("%" + word + "%") wheres.append("(" + " OR ".join(words) + ")") else: logging.error("getSQLquery: unknown op=%s!"%op) return # evaluate argv for (key, val) in argv.items(): logging.debug("process key=%s val=%s"%(key,val)) if key in processed: # parameter has been processed logging.debug(" key=%s processed"%(key)) continue # beginning of a command should always be "_" if key[0] == "-": key = "_" + key[1:] if key == "_select": # SELECT expression select = sqlName(val, more="*,") elif key == "_sort": # sort i.e. ORDER BY expression order = sqlName(val, more=",") elif key == "_lop": # logical operation joining WHERE clauses whereOp = sqlName(val) elif key == "max": # number of results limit = sqlName(val) elif key == "skip": # start at result number offset = sqlName(val) elif key[:3] == "_op": # operation parameters _op_param=val param = sqlName(key[4:]) logging.debug("param=%s key=%s val=%s"%(param,key,val)) if param in argv: doOp(val, param, argv[param]) processed[param] = True else: # no corresponding parameter logging.error("getSQLquery: param=%s for op not found!"%param) else: # parameter=value pair unprocessed[key] = val # process remaining parameters (without _op) for (key, val) in unprocessed.items(): if key not in processed: param = sqlName(key) # default operation doOp(self.default_search, param, val) # join it all query = "SELECT %s FROM %s"%(select, table) if wheres: query += " WHERE " + (" " + whereOp + " ").join(wheres) if order: query += " ORDER BY " + order if limit: query += " LIMIT " + limit if offset: query += " OFFSET " + offset return {'query' : query, 'args' : args} def ZDBInlineSearch(self, _table=None, **argv): """returns result set from search with given parameters""" query = self.getSQLQuery(_table, argv) result = self.executeZSQL(query['query'], query['args']) return result def manage_editZDBInterfaceFolder(self, title=None, connection_id=None, autocommit=None, default_search=None, REQUEST=None): """Change the object""" logging.debug("editZDBInterfaceFolder title=%s, connection_id=%s, autocommit=%s default_search=%s"%(title,connection_id,autocommit,default_search)) if title is not None: self.title = title if connection_id is not None: self.connection_id = connection_id if default_search is not None: self.default_search = default_search self.autocommit = (autocommit == "on") #checkPermission=getSecurityManager().checkPermission REQUEST.RESPONSE.redirect('manage_main') manage_addZDBInterfaceFolderForm=PageTemplateFile('zpt/addZDBInterfaceFolder',globals()) def manage_addZDBInterfaceFolder(self, id, title='', label='', description='', createPublic=0, createUserF=0, REQUEST=None): """Add a new object with id *id*.""" ob=ZDBInterfaceFolder(str(id),title) self._setObject(id, ob) #checkPermission=getSecurityManager().checkPermission REQUEST.RESPONSE.redirect('manage_main')