def test_multiplePlaceholderComparisons(self):
"""
Test that using multiple different placeholders in a comparison at once
properly gives each a unique name.
"""
s = Store()
p1 = Placeholder(PlaceholderTestItem)
p2 = Placeholder(PlaceholderTestItem)
query = ItemQuery(
s, PlaceholderTestItem,
AND(PlaceholderTestItem.attr == p1.attr,
PlaceholderTestItem.other == p1.other,
PlaceholderTestItem.attr == p2.attr,
PlaceholderTestItem.characters == p2.characters))
sql, args = query._sqlAndArgs('SELECT', '*')
self.assertEquals(
sql, 'SELECT * '
'FROM %s, %s AS placeholder_0, %s AS placeholder_1 '
'WHERE ((%s = placeholder_0.[attr]) AND '
'(%s = placeholder_0.[other]) AND '
'(%s = placeholder_1.[attr]) AND '
'(%s = placeholder_1.[characters]))' %
(PlaceholderTestItem.getTableName(s),
PlaceholderTestItem.getTableName(s),
PlaceholderTestItem.getTableName(s),
PlaceholderTestItem.attr.getColumnName(s),
PlaceholderTestItem.other.getColumnName(s),
PlaceholderTestItem.attr.getColumnName(s),
PlaceholderTestItem.characters.getColumnName(s)))
self.assertEquals(args, [])
def test_simplestQuery(self):
"""
Test that an ItemQuery with no comparison, sorting, or limit generates
the right SQL for that operation.
"""
s = Store()
query = ItemQuery(s, A)
sql, args = query._sqlAndArgs('SELECT', '*')
self.assertEquals(sql, 'SELECT * FROM %s' % (A.getTableName(s), ))
self.assertEquals(args, [])
def test_simpleReferenceComparison(self):
"""
Test that an ItemQuery with a single attribute comparison on a
reference attribute generates SQL with the right WHERE clause.
"""
s = Store()
query = ItemQuery(s, A, A.reftoc == A.storeID)
sql, args = query._sqlAndArgs('SELECT', '*')
self.assertEquals(
sql, 'SELECT * FROM %s WHERE (%s.[reftoc] = %s.oid)' %
(A.getTableName(s), A.getTableName(s), A.getTableName(s)))
self.assertEquals(args, [])
def test_simpleIntegerComparison(self):
"""
Test that an ItemQuery with a single attribute comparison on an integer
attribute generates SQL with the right WHERE clause.
"""
s = Store()
query = ItemQuery(s, E, E.amount == 0)
sql, args = query._sqlAndArgs('SELECT', '*')
self.assertEquals(
sql, 'SELECT * FROM %s WHERE (%s.[amount] = ?)' %
(E.getTableName(s), E.getTableName(s)))
self.assertEquals(args, [0])
def test_unionComparison(self):
"""
Test that an ItemQuery with two comparisons joined with an L{AND}
generates the right WHERE clause.
"""
s = Store()
query = ItemQuery(s, A, AND(A.reftoc == B.storeID,
B.cref == C.storeID))
sql, args = query._sqlAndArgs('SELECT', '*')
self.assertEquals(
sql, 'SELECT * FROM %s, %s, %s '
'WHERE ((%s.[reftoc] = %s.oid) AND '
'(%s.[cref] = %s.oid))' %
(A.getTableName(s), B.getTableName(s), C.getTableName(s),
A.getTableName(s), B.getTableName(s), B.getTableName(s),
C.getTableName(s)))
self.assertEquals(args, [])
def test_placeholderSorting(self):
"""
Test that a placeholder attribute can be used as a sort key.
"""
s = Store()
p = Placeholder(PlaceholderTestItem)
query = ItemQuery(s, PlaceholderTestItem, sort=p.attr.ascending)
sql, args = query._sqlAndArgs('SELECT', '*')
expectedSQL = ('SELECT * '
'FROM %s, %s AS placeholder_0 '
'ORDER BY placeholder_0.[attr] ASC')
expectedSQL %= (PlaceholderTestItem.getTableName(s), ) * 2
self.assertEquals(sql, expectedSQL)
self.assertEquals(args, [])
def test_placeholderComparison(self):
"""
Test that a comparison which contains a Placeholder also results in
properly generated SQL.
"""
s = Store()
p = Placeholder(PlaceholderTestItem)
query = ItemQuery(s, PlaceholderTestItem,
PlaceholderTestItem.attr == p.attr)
sql, args = query._sqlAndArgs('SELECT', '*')
self.assertEquals(
sql, 'SELECT * '
'FROM %s, %s AS placeholder_0 '
'WHERE (%s.[attr] = placeholder_0.[attr])' %
(PlaceholderTestItem.getTableName(s),
PlaceholderTestItem.getTableName(s),
PlaceholderTestItem.getTableName(s)))
self.assertEquals(args, [])
def test_placeholderQuery(self):
"""
Test that a BaseQuery can be created with Placeholder instances and the
SQL it emits as a result correctly assigns and uses table aliases.
"""
s = Store()
p = Placeholder(PlaceholderTestItem)
sql, args = ItemQuery(s, p)._sqlAndArgs('SELECT', '*')
self.assertEquals(
sql, 'SELECT * FROM %s AS placeholder_0' %
(PlaceholderTestItem.getTableName(s), ))