| 679 | print "pattern.db.Group" |
| 680 | |
| 681 | def test_query(self): |
| 682 | # Assert table query results from Table.search(). |
| 683 | for kwargs, sql, rows in ( |
| 684 | (dict(fields=db.ALL), |
| 685 | "select persons.* from `persons`;", |
| 686 | [(1, u"john", 30, 2), |
| 687 | (2, u"jack", 20, 2), |
| 688 | (3, u"jane", 30, 1)]), |
| 689 | (dict(fields=db.ALL, range=(0, 2)), |
| 690 | "select persons.* from `persons` limit 0, 2;", |
| 691 | [(1, u"john", 30, 2), |
| 692 | (2, u"jack", 20, 2)]), |
| 693 | (dict(fields=db.ALL, filters=[("age", 30, "<")]), |
| 694 | "select persons.* from `persons` where persons.age<30;", |
| 695 | [(2, u"jack", 20, 2)]), |
| 696 | (dict(fields=db.ALL, filters=db.any(("age", 30, "<"), ("name", "john"))), |
| 697 | "select persons.* from `persons` where persons.age<30 or persons.name='john';", |
| 698 | [(1, u"john", 30, 2), |
| 699 | (2, u"jack", 20, 2)]), |
| 700 | (dict(fields=["name", "gender.name"], relations=[db.relation("gender", "id", "gender")]), |
| 701 | "select persons.name, gender.name from `persons` left join `gender` on persons.gender=gender.id;", |
| 702 | [(u"john", u"male"), |
| 703 | (u"jack", u"male"), |
| 704 | (u"jane", u"female")]), |
| 705 | (dict(fields=["name","age"], sort="name"), |
| 706 | "select persons.name, persons.age from `persons` order by persons.name asc;", |
| 707 | [(u"jack", 20), |
| 708 | (u"jane", 30), |
| 709 | (u"john", 30)]), |
| 710 | (dict(fields=["name","age"], sort=1, order=db.DESCENDING), |
| 711 | "select persons.name, persons.age from `persons` order by persons.name desc;", |
| 712 | [(u"john", 30), |
| 713 | (u"jane", 30), |
| 714 | (u"jack", 20)]), |
| 715 | (dict(fields=["age","name"], sort=["age","name"], order=[db.ASCENDING, db.DESCENDING]), |
| 716 | "select persons.age, persons.name from `persons` order by persons.age asc, persons.name desc;", |
| 717 | [(20, u"jack"), |
| 718 | (30, u"john"), |
| 719 | (30, u"jane")]), |
| 720 | (dict(fields=["age","name"], group="age", function=db.CONCATENATE), |
| 721 | "select persons.age, group_concat(persons.name) from `persons` group by persons.age;", |
| 722 | [(20, u"jack"), |
| 723 | (30, u"john,jane")]), |
| 724 | (dict(fields=["id", "name","age"], group="age", function=[db.COUNT, db.CONCATENATE]), |
| 725 | "select count(persons.id), group_concat(persons.name), persons.age from `persons` group by persons.age;", |
| 726 | [(1, u"jack", 20), |
| 727 | (2, u"john,jane", 30)])): |
| 728 | v = self.db.persons.search(**kwargs) |
| 729 | v.xml |
| 730 | self.assertEqual(v.SQL(), sql) |
| 731 | self.assertEqual(v.rows(), rows) |
| 732 | # Assert Database.link() permanent relations. |
| 733 | v = self.db.persons.search(fields=["name", "gender.name"]) |
| 734 | v.aliases["gender.name"] = "gender" |
| 735 | self.db.link("persons", "gender", "gender", "id", join=db.LEFT) |
| 736 | self.assertEqual(v.SQL(), |
| 737 | "select persons.name, gender.name as gender from `persons` left join `gender` on persons.gender=gender.id;") |
| 738 | self.assertEqual(v.rows(), |