pyfpdb/test_Database.py

   1 #!/usr/bin/env python
   2 # -*- coding: utf-8 -*-
   3
   4 #Copyright 2008-2011 Carl Gherardi
   5 #This program is free software: you can redistribute it and/or modify
   6 #it under the terms of the GNU Affero General Public License as published by
   7 #the Free Software Foundation, version 3 of the License.
   8 #
   9 #This program is distributed in the hope that it will be useful,
  10 #but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 #MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  12 #GNU General Public License for more details.
  13 #
  14 #You should have received a copy of the GNU Affero General Public License
  15 #along with this program. If not, see <http://www.gnu.org/licenses/>.
  16 #In the "official" distribution you can find the license in agpl-3.0.txt.
  17
  18 import sqlite3
  19 import Database
  20 import math
  21
  22 # Should probably use our wrapper classes - creating sqlite db in memory
  23 sqlite3.register_converter("bool", lambda x: bool(int(x)))
  24 sqlite3.register_adapter(bool, lambda x: "1" if x else "0")
  25
  26 con = sqlite3.connect(":memory:")
  27 con.isolation_level = None
  28
  29 #Floor function
  30 con.create_function("floor", 1, math.floor)
  31
  32 #Mod function
  33 tmp = Database.sqlitemath()
  34 con.create_function("mod", 2, tmp.mod)
  35
  36 # Aggregate function VARIANCE()
  37 con.create_aggregate("variance", 1, Database.VARIANCE)
  38
  39
  40 cur = con.cursor()
  41
  42 def testSQLiteVarianceFunction():
  43     cur.execute("CREATE TABLE test(i)")
  44     cur.execute("INSERT INTO test(i) values (1)")
  45     cur.execute("INSERT INTO test(i) values (2)")
  46     cur.execute("INSERT INTO test(i) values (3)")
  47     cur.execute("SELECT variance(i) from test")
  48     result = cur.fetchone()[0]
  49
  50     print (_("DEBUG:") + " " + _("Testing variance function"))
  51     print (_("DEBUG:") + " " + _("result: %s expecting: 0.666666 (result-expecting ~= 0.0): %s") % (result, (result - 0.66666)))
  52     cur.execute("DROP TABLE test")
  53     assert (result - 0.66666) <= 0.0001
  54
  55 def testSQLiteFloorFunction():
  56     vars    = [0.1, 1.5, 2.6, 3.5, 4.9]
  57     cur.execute("CREATE TABLE test(i float)")
  58     for var in vars:
  59         cur.execute("INSERT INTO test(i) values(%f)" % var)
  60     cur.execute("SELECT floor(i) from test")
  61     result = cur.fetchall()
  62     print "DEBUG: result: %s" % result
  63     answer = 0
  64     for i in result:
  65         print "DEBUG: int(var): %s" % int(i[0])
  66         assert answer == int(i[0])
  67         answer = answer + 1
  68     cur.execute("DROP TABLE test")
  69
  70 def testSQLiteModFunction():
  71     vars    = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16 ,17, 18]
  72     cur.execute("CREATE TABLE test(i int)")
  73     for var in vars:
  74         cur.execute("INSERT INTO test(i) values(%i)" % var)
  75     cur.execute("SELECT mod(i,13) from test")
  76     result = cur.fetchall()
  77     idx = 0
  78     for i in result:
  79         print "DEBUG: int(var): %s" % i[0]
  80         assert vars[idx]%13 == int(i[0])
  81         idx = idx+1
  82
  83     cur.execute("DROP TABLE test")