[SQL-CVS] r1263 - in home/phd/SQLObject/paramstyles/sqlobject: . tests

[<sub...@co...>]

Author: phd
Date: 2005-11-14 20:15:41 +0000 (Mon, 14 Nov 2005)
New Revision: 1263

Modified:
   home/phd/SQLObject/paramstyles/sqlobject/sqlbuilder.py
   home/phd/SQLObject/paramstyles/sqlobject/tests/test_sqllist.py
Log:
Added __sqllist__() to Select, Insert, Update and Delete; added tests.


Modified: home/phd/SQLObject/paramstyles/sqlobject/sqlbuilder.py
===================================================================
--- home/phd/SQLObject/paramstyles/sqlobject/sqlbuilder.py	2005-11-14 18:54:57 UTC (rev 1262)
+++ home/phd/SQLObject/paramstyles/sqlobject/sqlbuilder.py	2005-11-14 20:15:41 UTC (rev 1263)
@@ -75,7 +75,9 @@
 import types
 
 from converters import sqlrepr, registerConverter, TRUE, FALSE
+from sqlobject.include.DBSingleStyle import Raw, Param
 
+
 safeSQLRE = re.compile(r'^[a-zA-Z_][a-zA-Z0-9_\.]*$')
 def sqlIdentifier(obj):
     # some db drivers return unicode column names 
@@ -105,13 +107,15 @@
 
 class SQLListMixin(object):
     def __sqllist__(self):
-       return self.__sqlrepr__(None)
+       return [self.__sqlrepr__(None)]
 
 def sqllist(obj):
     if isinstance(obj, types.StringTypes):
         return [obj]
+    elif hasattr(obj, "__sqllist__"):
+        return obj.__sqllist__()
     else:
-        return obj.__sqllist__()
+        return [Raw(obj)]
 
 ########################################
 ## Expression generation
@@ -505,6 +509,34 @@
             select += " LIMIT %s" % sqlrepr(self.limit, db)
         return select
 
+    def __sqllist__(self):
+        select = ["SELECT "]
+        for v in self.items:
+            select += sqllist(v)
+
+        tables = {}
+        things = self.items[:]
+        if self.whereClause is not NoDefault:
+            things.append(self.whereClause)
+        for thing in things:
+            if isinstance(thing, SQLExpression):
+                tables.update(tablesUsedDict(thing))
+        tables = tables.keys()
+        if tables:
+            select += [" FROM "] + tables
+
+        if self.whereClause is not NoDefault:
+            select += [" WHERE "] + sqllist(self.whereClause)
+        if self.groupBy is not NoDefault:
+            select += [" GROUP BY "] + sqllist(self.groupBy)
+        if self.having is not NoDefault:
+            select += [" HAVING "] + sqllist(self.having)
+        if self.orderBy is not NoDefault:
+            select += [" ORDER BY "] + sqllist(self.orderBy)
+        if self.limit is not NoDefault:
+            select += [" LIMIT "] + sqllist(self.limit)
+        return select
+
 registerConverter(Select, SQLExprConverter)
 
 class Insert(SQLExpression):
@@ -517,6 +549,7 @@
             self.valueList = valueList
         else:
             self.valueList = [values]
+
     def __sqlrepr__(self, db):
         if not self.valueList:
             return ''
@@ -544,6 +577,33 @@
             insert += "(%s)" % ", ".join([sqlrepr(v, db) for v in value])
         return insert
 
+    def __sqllist__(self):
+        if not self.valueList:
+            return ''
+        insert = ["INSERT INTO %s" % self.table]
+        allowNonDict = True
+        template = self.template
+        if template is NoDefault and type(self.valueList[0]) is type({}):
+            template = self.valueList[0].keys()
+            allowNonDict = False
+        if template is not NoDefault:
+            insert += [" (%s)" % ", ".join(template)]
+        first = True
+        insert += [" VALUES "]
+        for value in self.valueList:
+            if first:
+                first = False
+            else:
+                insert += [", "]
+            if type(value) is type({}):
+                if template is NoDefault:
+                    raise TypeError, "You can't mix non-dictionaries with dictionaries in an INSERT if you don't provide a template (%s)" % repr(value)
+                value = dictToList(template, value)
+            elif not allowNonDict:
+                raise TypeError, "You can't mix non-dictionaries with dictionaries in an INSERT if you don't provide a template (%s)" % repr(value)
+            insert += ["("] + [Param(v) for v in value] + [")"]
+        return insert
+
 registerConverter(Insert, SQLExprConverter)
 
 def dictToList(template, dict):
@@ -560,6 +620,7 @@
         self.values = values
         self.template = template
         self.whereClause = where
+
     def __sqlrepr__(self, db):
         update = "%s %s" % (self.sqlName(), self.table)
         update += " SET"
@@ -581,9 +642,31 @@
         if self.whereClause is not NoDefault:
             update += " WHERE %s" % sqlrepr(self.whereClause, db)
         return update
+
     def sqlName(self):
         return "UPDATE"
 
+    def __sqllist__(self):
+        update = ["%s %s SET" % (self.sqlName(), self.table)]
+        first = True
+        if self.template is not NoDefault:
+            for i in range(len(self.template)):
+                if first:
+                    first = False
+                else:
+                    update += ","
+                update += [' ', self.template[i], "=", Param(self.values[i])]
+        else:
+            for key, value in self.values.items():
+                if first:
+                    first = False
+                else:
+                    update += [","]
+                update += [' ', key, "=", Param(value)]
+        if self.whereClause is not NoDefault:
+            update += [" WHERE "] + sqllist(self.whereClause)
+        return update
+
 registerConverter(Update, SQLExprConverter)
 
 class Delete(SQLExpression):
@@ -599,6 +682,10 @@
             return "DELETE FROM %s" % self.table
         return "DELETE FROM %s WHERE %s" \
                % (self.table, sqlrepr(self.whereClause, db))
+    def __sqllist__(self):
+        if self.whereClause is None:
+            return ["DELETE FROM %s" % self.table]
+        return ["DELETE FROM %s WHERE " % (self.table,)] + sqllist(self.whereClause)
 
 registerConverter(Delete, SQLExprConverter)
 

Modified: home/phd/SQLObject/paramstyles/sqlobject/tests/test_sqllist.py
===================================================================
--- home/phd/SQLObject/paramstyles/sqlobject/tests/test_sqllist.py	2005-11-14 18:54:57 UTC (rev 1262)
+++ home/phd/SQLObject/paramstyles/sqlobject/tests/test_sqllist.py	2005-11-14 20:15:41 UTC (rev 1263)
@@ -1,3 +1,4 @@
+from sqlobject import *
 from sqlobject.sqlbuilder import *
 
 def test_simple_string():
@@ -5,3 +6,37 @@
 
 def test_sql_op():
     assert sqllist(SQLOp('+', '1', '2')) == ['(', '1', '+', '2', ')']
+
+
+class TestSQLList(SQLObject):
+    col1 = StringCol()
+
+def test_select():
+    assert sqllist(Select(TestSQLList.q.col1)) == \
+        ["SELECT ", "test_sql_list.col1", " FROM ", "test_sql_list"]
+
+def test_insert():
+    insert = sqllist(Insert("test_sql_list", values={"col1": '1'}))
+    param = insert[-2]
+    assert isinstance(param, Param)
+    assert param.value == '1'
+    del insert[-2] # Param does not support comparison
+    assert insert == ["INSERT INTO test_sql_list",  " (col1)", " VALUES ", "(", ")"]
+
+def test_update():
+    update = sqllist(Update("test_sql_list", values={"col1": '1'}))
+    param = update[-1]
+    assert isinstance(param, Param)
+    assert param.value == '1'
+    del update[-1] # Param does not support comparison
+    assert update == ["UPDATE test_sql_list SET",  ' ', "col1", "="]
+
+def test_delete():
+    assert sqllist(Delete(TestSQLList.sqlmeta.table, None)) == \
+        ["DELETE FROM test_sql_list"]
+    delete = sqllist(Delete(TestSQLList.sqlmeta.table, TestSQLList.q.col1==1))
+    raw = delete[-2]
+    assert isinstance(raw, Raw)
+    assert raw.value == 1
+    del delete[-2]
+    assert delete == ["DELETE FROM test_sql_list WHERE ", "(", "test_sql_list.col1", "=", ")"]