[SQL-CVS] r733 - in trunk/SQLObject: docs sqlobject sqlobject/tests

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

Author: phd
Date: 2005-04-21 15:18:01 +0000 (Thu, 21 Apr 2005)
New Revision: 733

Added:
   trunk/SQLObject/docs/SQLObjectSubqueries.txt
   trunk/SQLObject/sqlobject/tests/test_subqueries.py
Modified:
   trunk/SQLObject/docs/SQLObject.txt
   trunk/SQLObject/sqlobject/sqlbuilder.py
Log:
Implemented subqueries (subselects).


Modified: trunk/SQLObject/docs/SQLObject.txt
===================================================================
--- trunk/SQLObject/docs/SQLObject.txt	2005-04-20 21:43:11 UTC (rev 732)
+++ trunk/SQLObject/docs/SQLObject.txt	2005-04-21 15:18:01 UTC (rev 733)
@@ -39,6 +39,7 @@
 .. include:: SQLObjectDBConnection.txt
 .. include:: SQLObjectExported.txt
 .. include:: SQLObjectJoins.txt
+.. include:: SQLObjectSubqueries.txt
 
 For more information on SQLBuilder, read the `SQLBuilder
 Documentation`_.

Added: trunk/SQLObject/docs/SQLObjectSubqueries.txt
===================================================================
--- trunk/SQLObject/docs/SQLObjectSubqueries.txt	2005-04-20 21:43:11 UTC (rev 732)
+++ trunk/SQLObject/docs/SQLObjectSubqueries.txt	2005-04-21 15:18:01 UTC (rev 733)
@@ -0,0 +1,18 @@
+Subqueries (subselects)
+-----------------------
+
+You can run queries with subqueries (subselects) on those DBMS that can do
+subqueries (MySQL supports subqueries from version 4.1).
+
+Use corresponding classess and functions from sqlbuilder::
+
+    from sqlobject.sqlbuilder import EXISTS, Select
+    select = Test1.select(EXISTS(Select(Test2.q.col2, where=(Outer(Test1).q.col1 == Test2.q.col2))))
+
+generates the query::
+
+   SELECT test1.id, test1.col1 FROM test1 WHERE
+   EXISTS (SELECT test2.col2 FROM test2 WHERE (test1.col1 = test2.col2))
+
+Note the usage of Outer - this is the helper to allow refering to a table
+in the outer query.

Modified: trunk/SQLObject/sqlobject/sqlbuilder.py
===================================================================
--- trunk/SQLObject/sqlobject/sqlbuilder.py	2005-04-20 21:43:11 UTC (rev 732)
+++ trunk/SQLObject/sqlobject/sqlbuilder.py	2005-04-21 15:18:01 UTC (rev 733)
@@ -848,7 +848,70 @@
 def FULLOUTERJOINUsing(table1, table2, using_columns):
     return SQLJoinUsing(table1, table2, "FULL OUTER JOIN", using_columns)
 
+
 ########################################
+## Subqueries (subselects)
+########################################
+
+class OuterField(Field):
+    def tablesUsedImmediate(self):
+        return []
+
+class OuterTable(Table):
+    FieldClass = OuterField
+
+    def __init__(self, table):
+        if hasattr(table, "sqlmeta"):
+            tableName = table.sqlmeta.table
+        else:
+            tableName = table
+            table = None
+        Table.__init__(self, tableName)
+        self.table = table
+
+class Outer:
+    def __init__(self, table):
+        self.q = OuterTable(table)
+
+
+class INSubquery(SQLExpression):
+    op = "IN"
+
+    def __init__(self, item, subquery):
+        self.item = item
+        self.subquery = subquery
+    def __sqlrepr__(self, db):
+        return "%s %s (%s)" % (sqlrepr(self.item, db), self.op, sqlrepr(self.subquery, db))
+
+class NOTINSubquery(INSubquery):
+    op = "NOT IN"
+
+
+class Subquery(SQLExpression):
+    def __init__(self, op, subquery):
+        self.op = op
+        self.subquery = subquery
+
+    def __sqlrepr__(self, db):
+        return "%s (%s)" % (self.op, sqlrepr(self.subquery, db))
+
+def EXISTS(subquery):
+    return Subquery("EXISTS", subquery)
+
+def NOTEXISTS(subquery):
+    return Subquery("NOT EXISTS", subquery)
+
+def SOME(subquery):
+    return Subquery("SOME", subquery)
+
+def ANY(subquery):
+    return Subquery("ANY", subquery)
+
+def ALL(subquery):
+    return Subquery("ALL", subquery)
+
+
+########################################
 ## Global initializations
 ########################################
 

Added: trunk/SQLObject/sqlobject/tests/test_subqueries.py
===================================================================
--- trunk/SQLObject/sqlobject/tests/test_subqueries.py	2005-04-20 21:43:11 UTC (rev 732)
+++ trunk/SQLObject/sqlobject/tests/test_subqueries.py	2005-04-21 15:18:01 UTC (rev 733)
@@ -0,0 +1,48 @@
+from sqlobject import *
+from sqlobject.sqlbuilder import *
+from sqlobject.tests.dbtest import *
+
+########################################
+## Subqueries (subselects)
+########################################
+
+class TestIn1(SQLObject):
+    col1 = StringCol()
+
+class TestIn2(SQLObject):
+    col2 = StringCol()
+
+def setup():
+    setupClass(TestIn1)
+    setupClass(TestIn2)
+
+def insert():
+    setup()
+    TestIn1(col1=None)
+    TestIn1(col1='')
+    TestIn1(col1="test")
+    TestIn2(col2=None)
+    TestIn2(col2='')
+    TestIn2(col2="test")
+
+def test_1syntax_in():
+    setup()
+    select = TestIn1.select(INSubquery(TestIn1.q.col1, Select(TestIn2.q.col2)))
+    assert str(select) == \
+        "SELECT test_in1.id, test_in1.col1 FROM test_in1 WHERE test_in1.col1 IN (SELECT test_in2.col2 FROM test_in2)"
+
+def test_2perform_in():
+    insert()
+    select = TestIn1.select(INSubquery(TestIn1.q.col1, Select(TestIn2.q.col2)))
+    assert select.count() == 2
+
+def test_3syntax_exists():
+    setup()
+    select = TestIn1.select(NOTEXISTS(Select(TestIn2.q.col2, where=(Outer(TestIn1).q.col1 == TestIn2.q.col2))))
+    assert str(select) == \
+        "SELECT test_in1.id, test_in1.col1 FROM test_in1 WHERE NOT EXISTS (SELECT test_in2.col2 FROM test_in2 WHERE (test_in1.col1 = test_in2.col2))"
+
+def test_4perform_exists():
+    insert()
+    select = TestIn1.select(EXISTS(Select(TestIn2.q.col2, where=(Outer(TestIn1).q.col1 == TestIn2.q.col2))))
+    assert len(list(select)) == 2