# HG changeset patch
# User casties
# Date 1297796347 -3600
# Node ID 0ade331198de87e0794e3c74142592ef05ef9fcd
# Parent  d70e57193731bc41a1f32c1fa6f943a5a02e940a
first version of ZDBInlineSearch

diff -r d70e57193731 -r 0ade331198de DBInterface.py
--- a/DBInterface.py	Mon Feb 14 23:20:43 2011 +0100
+++ b/DBInterface.py	Tue Feb 15 19:59:07 2011 +0100
@@ -53,13 +53,13 @@
            rc = rc + node.data
     return rc
 
-def sqlName(s,lc=True):
+def sqlName(s, lc=True, more=''):
     """returns restricted ASCII-only version of string"""
     if s is None:
         return ""
     
     # all else -> "_"
-    s = re.sub(r'[^A-Za-z0-9_]','_',s)
+    s = re.sub('[^A-Za-z0-9_'+more+']','_',s)
     if lc:
         return s.lower()
     
diff -r d70e57193731 -r 0ade331198de ZDBInterfaceFolder.py
--- a/ZDBInterfaceFolder.py	Mon Feb 14 23:20:43 2011 +0100
+++ b/ZDBInterfaceFolder.py	Tue Feb 15 19:59:07 2011 +0100
@@ -34,6 +34,96 @@
         self.connection_id = connection_id
 
 
+    def getSQLQuery(self, table=None, 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"
+        done = {}
+        # evaluate argv
+        for (key, val) in argv.items():
+            if key in done:
+                # parameter has been processed
+                continue
+            
+            if isinstance(val, list): 
+                # join multiple parameters with spaces (makes sense with checkbox and -op=all)
+                val = " ".join(val)
+
+            # beginning of a command should always be "_"
+            if key[0] == "-":
+                key[0] = "_"
+      
+            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[:3] == "_op":
+                # operation parameters _op_param=option
+                param = sqlName(key[4:])
+                if param in argv:
+                    doOp(op, param)
+                    done[param] = True
+                else:
+                    # no corresponding parameter
+                    logging.error("getSQLquery: param=%s for op not found!"%param)
+                
+        # process remaining parameters (without _op)
+        for (key, val) in argv.items():
+            if key not in done:
+                param = sqlName(key)
+                # default is begins-with
+                doOp("bw", param)
+
+        # join it all
+        query = "SELECT %s FROM %s"%(select, table)
+        if wheres:
+            query += " WHERE " + (" " + whereOp + " ").join(wheres)
+        
+        if order:
+            query += " ORDER BY " + order
+
+        return {'query' : query, 'args' : args}
+                
+        def doOp(op, param):
+            # handles comparison operations in WHERE clause
+            if op == "eq":
+                wheres.append(param + " = %s")
+                args.append(argv[param])
+            elif op == "lt":
+                wheres.append(param + " < %s")
+                args.append(argv[param])
+            elif op == "gt":
+                wheres.append(param + " > %s")
+                args.append(argv[param])
+            elif op == "bw":
+                wheres.append(param + " LIKE %s")
+                args.append("%" + argv[param])
+            elif op == "ew":
+                wheres.append(param + " LIKE %s")
+                args.append(argv[param] + "%")
+            elif op == "ct":
+                wheres.append(param + " LIKE %s")
+                args.append("%" + argv[param] + "%")
+            else:
+                logging.error("getSQLquery: unknown op=%s!"%op)
+                
+            return
+                
+
+    def ZDBInlineSearch(self, _table=None, **argv):
+        """returns result set from search"""
+        query = self.getSQLQuery(_table=_table, argv)
+        result = self.executeZSQL(query['query'], query['args'])
+        return result
     
     def manage_editZDBInterfaceFolder(self, title=None, connection_id=None,
                      REQUEST=None):