def test_query(self):
# Assert table query results from Table.search().
for kwargs, sql, rows in (
(dict(fields=db.ALL), "select persons.* from `persons`;", [
(1, u"john", 30, 2), (2, u"jack", 20, 2), (3, u"jane", 30, 1)
]), (dict(fields=db.ALL, range=(0, 2)),
"select persons.* from `persons` limit 0, 2;", [
(1, u"john", 30, 2), (2, u"jack", 20, 2)
]), (dict(fields=db.ALL, filters=[
("age", 30, "<")
]), "select persons.* from `persons` where persons.age<30;",
[(2, u"jack", 20, 2)]),
(dict(fields=db.ALL,
filters=db.any(("age", 30, "<"), ("name", "john"))),
"select persons.* from `persons` where persons.age<30 or persons.name='john';",
[(1, u"john", 30, 2), (2, u"jack", 20, 2)]),
(dict(fields=["name", "gender.name"],
relations=[db.relation("gender", "id", "gender")]),
"select persons.name, gender.name from `persons` left join `gender` on persons.gender=gender.id;",
[(u"john", u"male"), (u"jack", u"male"), (u"jane", u"female")]),
(dict(fields=["name", "age"], sort="name"),
"select persons.name, persons.age from `persons` order by persons.name asc;",
[(u"jack", 20), (u"jane", 30), (u"john", 30)]),
(dict(fields=["name", "age"], sort=1, order=db.DESCENDING),
"select persons.name, persons.age from `persons` order by persons.name desc;",
[(u"john", 30), (u"jane", 30), (u"jack", 20)]),
(dict(fields=["age", "name"],
sort=["age", "name"],
order=[db.ASCENDING, db.DESCENDING]),
"select persons.age, persons.name from `persons` order by persons.age asc, persons.name desc;",
[(20, u"jack"), (30, u"john"), (30, u"jane")]),
(dict(fields=["age", "name"], group="age",
function=db.CONCATENATE),
"select persons.age, group_concat(persons.name) from `persons` group by persons.age;",
[(20, u"jack"), (30, u"john,jane")]),
(dict(fields=["id", "name", "age"],
group="age",
function=[db.COUNT,
db.CONCATENATE]),
"select count(persons.id), group_concat(persons.name), persons.age from `persons` group by persons.age;",
[(1, u"jack", 20), (2, u"john,jane", 30)])):
v = self.db.persons.search(**kwargs)
v.xml
self.assertEqual(v.SQL(), sql)
self.assertEqual(v.rows(), rows)
# Assert Database.link() permanent relations.
v = self.db.persons.search(fields=["name", "gender.name"])
v.aliases["gender.name"] = "gender"
self.db.link("persons", "gender", "gender", "id", join=db.LEFT)
self.assertEqual(
v.SQL(),
"select persons.name, gender.name as gender from `persons` left join `gender` on persons.gender=gender.id;"
)
self.assertEqual(v.rows(), [(u'john', u'male'), (u'jack', u'male'),
(u'jane', u'female')])
print "pattern.db.Table.search()"
print "pattern.db.Table.Query"
def test_group(self):
# Assert WHERE with AND/OR combinations from Group object().
yesterday = db.date()
yesterday -= db.time(days=1)
g1 = db.Group(("name", "garlic bread"))
g2 = db.Group(("name", "pizza"), ("price", 10, "<"), operator=db.AND)
g3 = db.Group(g1, g2, operator=db.OR)
g4 = db.Group(g3, ("date", yesterday, ">"), operator=db.AND)
self.assertEqual(g1.SQL(), "name='garlic bread'")
self.assertEqual(g2.SQL(), "name='pizza' and price<10")
self.assertEqual(g3.SQL(), "(name='garlic bread') or (name='pizza' and price<10)")
self.assertEqual(g4.SQL(), "((name='garlic bread') or (name='pizza' and price<10)) and date>'%s'" % yesterday)
# Assert subquery in group.
q = self._query(fields=["name"])
g = db.any(("name", u"Gödel"), ("name", q))
self.assertEqual(g.SQL(), u"name='Gödel' or name in (select persons.name from `persons`)")
print "pattern.db.Group"
def test_filterchain(self):
# Assert WHERE with AND/OR combinations from FilterChain object().
yesterday = db.date()
yesterday -= db.time(days=1)
f1 = db.FilterChain(("name", "garlic bread"))
f2 = db.FilterChain(("name", "pizza"), ("price", 10, "<"), operator=db.AND)
f3 = db.FilterChain(f1, f2, operator=db.OR)
f4 = db.FilterChain(f3, ("date", yesterday, ">"), operator=db.AND)
self.assertEqual(f1.SQL(), "name='garlic bread'")
self.assertEqual(f2.SQL(), "name='pizza' and price<10")
self.assertEqual(f3.SQL(), "(name='garlic bread') or (name='pizza' and price<10)")
self.assertEqual(f4.SQL(), "((name='garlic bread') or (name='pizza' and price<10)) and date>'%s'" % yesterday)
# Assert subquery in filter chain.
q = self._query(fields=["name"])
f = db.any(("name", u"Gödel"), ("name", q))
self.assertEqual(f.SQL(), u"name='Gödel' or name in (select persons.name from `persons`)")
print("pattern.db.FilterChain")
def test_filterchain(self):
# Assert WHERE with AND/OR combinations from FilterChain object().
yesterday = db.date()
yesterday -= db.time(days=1)
f1 = db.FilterChain(("name", "garlic bread"))
f2 = db.FilterChain(("name", "pizza"), ("price", 10, "<"), operator=db.AND)
f3 = db.FilterChain(f1, f2, operator=db.OR)
f4 = db.FilterChain(f3, ("date", yesterday, ">"), operator=db.AND)
self.assertEqual(f1.SQL(), "name='garlic bread'")
self.assertEqual(f2.SQL(), "name='pizza' and price<10")
self.assertEqual(f3.SQL(), "(name='garlic bread') or (name='pizza' and price<10)")
self.assertEqual(f4.SQL(), "((name='garlic bread') or (name='pizza' and price<10)) and date>'%s'" % yesterday)
# Assert subquery in filter chain.
q = self._query(fields=["name"])
f = db.any(("name", u"Gödel"), ("name", q))
self.assertEqual(f.SQL(), u"name='Gödel' or name in (select persons.name from `persons`)")
print "pattern.db.FilterChain"
def test_query(self):
# Assert table query results from Table.search().
for kwargs, sql, rows in (
(dict(fields=db.ALL),
"select persons.* from `persons`;",
[(1, u"john", 30, 2),
(2, u"jack", 20, 2),
(3, u"jane", 30, 1)]),
(dict(fields=db.ALL, range=(0, 2)),
"select persons.* from `persons` limit 0, 2;",
[(1, u"john", 30, 2),
(2, u"jack", 20, 2)]),
(dict(fields=db.ALL, filters=[("age", 30, "<")]),
"select persons.* from `persons` where persons.age<30;",
[(2, u"jack", 20, 2)]),
(dict(fields=db.ALL, filters=db.any(("age", 30, "<"), ("name", "john"))),
"select persons.* from `persons` where persons.age<30 or persons.name='john';",
[(1, u"john", 30, 2),
(2, u"jack", 20, 2)]),
(dict(fields=["name", "gender.name"], relations=[db.relation("gender", "id", "gender")]),
"select persons.name, gender.name from `persons` left join `gender` on persons.gender=gender.id;",
[(u"john", u"male"),
(u"jack", u"male"),
(u"jane", u"female")]),
(dict(fields=["name","age"], sort="name"),
"select persons.name, persons.age from `persons` order by persons.name asc;",
[(u"jack", 20),
(u"jane", 30),
(u"john", 30)]),
(dict(fields=["name","age"], sort=1, order=db.DESCENDING),
"select persons.name, persons.age from `persons` order by persons.name desc;",
[(u"john", 30),
(u"jane", 30),
(u"jack", 20)]),
(dict(fields=["age","name"], sort=["age","name"], order=[db.ASCENDING, db.DESCENDING]),
"select persons.age, persons.name from `persons` order by persons.age asc, persons.name desc;",
[(20, u"jack"),
(30, u"john"),
(30, u"jane")]),
(dict(fields=["age","name"], group="age", function=db.CONCATENATE),
"select persons.age, group_concat(persons.name) from `persons` group by persons.age;",
[(20, u"jack"),
(30, u"john,jane")]),
(dict(fields=["id", "name","age"], group="age", function=[db.COUNT, db.CONCATENATE]),
"select count(persons.id), group_concat(persons.name), persons.age from `persons` group by persons.age;",
[(1, u"jack", 20),
(2, u"john,jane", 30)])):
v = self.db.persons.search(**kwargs)
v.xml
self.assertEqual(v.SQL(), sql)
self.assertEqual(v.rows(), rows)
# Assert Database.link() permanent relations.
v = self.db.persons.search(fields=["name", "gender.name"])
v.aliases["gender.name"] = "gender"
self.db.link("persons", "gender", "gender", "id", join=db.LEFT)
self.assertEqual(v.SQL(),
"select persons.name, gender.name as gender from `persons` left join `gender` on persons.gender=gender.id;")
self.assertEqual(v.rows(),
[(u'john', u'male'),
(u'jack', u'male'),
(u'jane', u'female')])
print "pattern.db.Table.search()"
print "pattern.db.Table.Query"