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(IN(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(IN(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

def test_4syntax_direct():
    setup()
    select = TestIn1.select(TestIn1.q.col1 == Select(TestIn2.q.col2, where=(TestIn2.q.col2 == "test")))
    assert str(select) == \
        "SELECT test_in1.id, test_in1.col1 FROM test_in1 WHERE ((test_in1.col1) = (SELECT test_in2.col2 FROM test_in2 WHERE ((test_in2.col2) = ('test'))))"

def test_4perform_direct():
    insert()
    select = TestIn1.select(TestIn1.q.col1 == Select(TestIn2.q.col2, where=(TestIn2.q.col2 == "test")))
    assert select.count() == 1

def test_5perform_direct():
     insert()
     select = TestIn1.select(TestIn1.q.col1 == Select(TestIn2.q.col2, where=(TestIn2.q.col2 == "test")))
     assert select.count() == 1

def test_6syntax_join():
     insert()
     j = LEFTOUTERJOINOn(TestIn2, TestIn1, TestIn1.q.col1==TestIn2.q.col2)
     select = TestIn1.select(TestIn1.q.col1 == Select(TestIn2.q.col2, where=(TestIn2.q.col2 == "test"), join=j))
     assert str(select) == \
        "SELECT test_in1.id, test_in1.col1 FROM test_in1 WHERE ((test_in1.col1) = (SELECT test_in2.col2 FROM  test_in2 LEFT OUTER JOIN test_in1 ON ((test_in1.col1) = (test_in2.col2)) WHERE ((test_in2.col2) = ('test'))))"

def test_6perform_join():
     insert()
     j = LEFTOUTERJOINOn(TestIn2, TestIn1, TestIn1.q.col1==TestIn2.q.col2)
     select = TestIn1.select(TestIn1.q.col1 == Select(TestIn2.q.col2, where=(TestIn2.q.col2 == "test"), join=j))
     assert select.count() == 1