testsuite/sqldb/query.py

   1 # This file is part of the Enkel web programming library.
   2 #
   3 # Copyright (C) 2007 Espen Angell Kristiansen (espeak@users.sourceforge.net)
   4 #
   5 # This program is free software; you can redistribute it and/or
   6 # modify it under the terms of the GNU General Public License
   7 # as published by the Free Software Foundation; either version 2
   8 # of the License, or (at your option) any later version.
   9 #
  10 # This program is distributed in the hope that it will be useful,
  11 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13 # GNU General Public License for more details.
  14 #
  15 # You should have received a copy of the GNU General Public License
  16 # along with this program; if not, write to the Free Software
  17 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
  18
  19 from unittest import TestCase
  20 from cStringIO import StringIO
  21
  22 from enkel.wansgli.testhelpers import unit_case_suite, run_suite
  23 from enkel.sqldb.query import *
  24
  25
  26
  27 class TestQuery(TestCase):
  28         def setUp(self):
  29                 self.name = QueryField("name", "artists")
  30                 self.age = QueryField("age", "artists")
  31                 self.bname = QueryField("name", "band")
  32
  33         def test_paramgens(self):
  34                 for paramgen, expected in (
  35                                 (paramgen_qmark, "?"),
  36                                 (paramgen_numeric, ":9"),
  37                                 (paramgen_named, ":p8"),
  38                                 (paramgen_format, "%s"),
  39                                 (paramgen_pyformat, "%(p8)s")
  40                 ):
  41                         self.assertEquals(paramgen(8), expected)
  42
  43         def test_params_as_dict(self):
  44                 self.assertEquals(
  45                         params_as_dict([10, "yes", "Peter", 20]),
  46                         dict(p0=10, p1="yes", p2="Peter", p3=20))
  47
  48
  49         def test_op(self):
  50                 p = []
  51                 args = None, paramgen_named, p
  52
  53                 q = OneValueOp(self.name, "=", "Reginald").get_sql(*args)
  54                 self.assertEquals("artists.name = :p0", q)
  55                 self.assertEquals(p, ["Reginald"])
  56
  57                 q = SimpleOp(self.name, "NOTNULL").get_sql(*args)
  58                 self.assertEquals("artists.name NOTNULL", q)
  59                 self.assertEquals(p, ["Reginald"])
  60
  61                 q = InOp(self.name, "", "Reginald", "Peter", "Amy").get_sql(*args)
  62                 self.assertEquals("artists.name IN(:p1,:p2,:p3)", q)
  63                 self.assertEquals(p, ["Reginald", "Reginald", "Peter", "Amy"])
  64
  65                 q = BetweenOp(self.age, "", 10, 20).get_sql(*args)
  66                 self.assertEquals("artists.age BETWEEN :p4 AND :p5", q)
  67                 self.assertEquals(p, ["Reginald", "Reginald", "Peter", "Amy", 10, 20])
  68
  69
  70         def test_QueryField(self):
  71                 p = []
  72                 args = None, paramgen_qmark, p
  73
  74                 for op, expected in (
  75                                 ("__eq__", "="),
  76                                 ("__ne__", "!="),
  77                                 ("__ge__", ">="),
  78                                 ("__gt__", ">"),
  79                                 ("__le__", "<="),
  80                                 ("__lt__", "<"),
  81                 ):
  82                         q = getattr(self.name, op)("a").get_sql(*args)
  83                         self.assertEquals(q, "artists.name %s ?" % expected)
  84                 for x in p:
  85                         self.assertEquals(x, "a")
  86
  87                 p[:] = []
  88                 for op in "startswith", "endswith", "contains":
  89                         f = getattr(self.name, op)
  90                         self.assertEquals(f("j_j").get_sql(*args),
  91                                         "artists.name LIKE ? ESCAPE '|'")
  92                         f = getattr(self.name, "not_" + op)
  93                         self.assertEquals(f("%ja").get_sql(*args),
  94                                         "artists.name NOT LIKE ? ESCAPE '|'")
  95                 self.assertEquals(p,
  96                         ['j|_j%', '|%ja%', '%j|_j', '%|%ja', '%j|_j%', '%|%ja%'])
  97
  98                 p[:] = []
  99                 self.assertEquals(self.name.isnull().get_sql(*args),
 100                                 "artists.name IS NULL")
 101                 self.assertEquals(self.name.not_null().get_sql(*args),
 102                                 "artists.name IS NOT NULL")
 103
 104                 self.assertEquals(self.age.between(10, 20).get_sql(*args),
 105                                 "artists.age BETWEEN ? AND ?")
 106                 self.assertEquals(self.age.not_between(10, 20).get_sql(*args),
 107                                 "artists.age NOT BETWEEN ? AND ?")
 108
 109                 self.assertEquals(self.name.in_list("x", "y", "z").get_sql(*args),
 110                                 "artists.name IN(?,?,?)")
 111                 self.assertEquals(self.name.not_in_list("x", "y", "z").get_sql(*args),
 112                                 "artists.name NOT IN(?,?,?)")
 113
 114                 self.assertEquals(p, [10, 20, 10, 20, "x", "y", "z", "x", "y", "z"])
 115
 116
 117         def test_OpList(self):
 118                 x, p = And(self.name == "Reginald", self.age>=10).compile(
 119                                 None, paramgen_qmark)
 120                 self.assertEquals(x, "artists.name = ? AND artists.age >= ?")
 121                 self.assertEquals(p, ["Reginald", 10])
 122
 123                 x, p = Or(
 124                                 self.name == "Reginald", self.age>=10,
 125                                 And(self.bname.in_list("Jonathan", "Bender"), self.age < 3),
 126                                 id=25
 127                         ).compile("<name of table>", paramgen_qmark)
 128                 self.assertEquals(x,
 129                         "artists.name = ? OR artists.age >= ? OR "\
 130                         "(band.name IN(?,?) AND artists.age < ?) OR <name of table>.id = ?")
 131                 self.assertEquals(p, ["Reginald", 10, "Jonathan", "Bender", 3, 25])
 132
 133
 134         def test_WhereClause(self):
 135
 136                 # sanity check
 137                 e, p = WhereClause("mytable", paramgen_format,
 138                                 self.bname == "Korn", name=10).compile()
 139                 self.assertEquals(e, "WHERE band.name = %s AND mytable.name = %s")
 140                 self.assertEquals(p, ["Korn", 10])
 141
 142                 # "And" forwarding check
 143                 self.assertEquals(e[6:],
 144                         And(self.bname == "Korn", name=10).compile(
 145                                 "mytable", paramgen_format)[0])
 146
 147                 # Test extra keywords
 148                 e, p = WhereClause("mytable", paramgen_format,
 149                                 order_by=[Asc(self.bname), Desc("rating")],
 150                                 limit=10, offset=2).compile()
 151                 self.assertEquals(e,
 152                         "ORDER BY band.name ASC, mytable.rating DESC LIMIT 10 OFFSET 2")
 153
 154
 155 def suite():
 156         return unit_case_suite(TestQuery)
 157
 158 if __name__ == '__main__':
 159         run_suite(suite())