ut/test_db.py

   1 # -*- coding: utf-8 -*-
   2 """Test database access."""
   3 from datetime import datetime, timedelta
   4 from os import path, remove
   5 from sqlite3 import connect
   6 from unittest import TestCase
   7 from cbconf import Conf
   8 from cbdb import ArgCountError, Db, FileNotSetError
   9
  10 TESTDB_FILENAME = ".tmptest.db"
  11
  12 def create_test_db(fn):
  13     con = connect(fn)
  14     cur = con.cursor()
  15     cur.executescript("""
  16 pragma foreign_keys = ON;
  17
  18 create table if not exists users (
  19 id varchar(24) primary key not null,
  20 name varchar(255) default "human"
  21 );
  22
  23 create table if not exists flavors (
  24 name varchar(255) primary key not null,
  25 ord integer not null default 999
  26 );
  27
  28 insert or ignore into flavors values
  29 ("espresso", 2),
  30 ("espresso lungo", 3),
  31 ("cappuccino", 1),
  32 ("latte macchiato", 4),
  33 ("Club-Mate 0,5 l", 5),
  34 ("Club-Mate 0,33 l", 6)
  35 ;
  36
  37 create table if not exists coffees (
  38 num integer primary key,
  39 id varchar(24) references users(id), -- id may be unknown
  40 flavor varchar(255) not null references flavors(name),
  41 time datetime default current_timestamp,
  42 UNIQUE (id, flavor, time)
  43 );
  44
  45
  46 create table if not exists days (
  47 num integer primary key not null
  48 );
  49
  50 insert or ignore into days values
  51 (0),(1),(2),(3),(4),(5),(6)
  52 ;
  53     """)
  54     cur.executescript("""
  55 INSERT INTO users VALUES('1111','tester');
  56 INSERT INTO users VALUES('2222','newer');
  57 INSERT INTO users VALUES('3333','clone');
  58 INSERT INTO coffees (id, flavor, time) VALUES
  59 ('1111', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
  60 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-99 seconds')),
  61 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
  62 ('1111', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
  63 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-95 second')),
  64 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second')),
  65 ('1111', 'espresso lungo', datetime('now', 'localtime', '-90 second')),
  66 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-90 second')),
  67 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-90 second')),
  68 ('1111', 'espresso lungo', datetime('now', 'localtime', '-85 second')),
  69 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-85 second')),
  70 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-85 second')),
  71 ('2222', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
  72 ('2222', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
  73 ('2222', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
  74 ('2222', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second')),
  75 ('3333', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
  76 ('3333', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
  77 ('3333', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
  78 ('3333', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second'));
  79     """)
  80     con.close()
  81
  82 def delete_test_db(fn):
  83     remove(fn)
  84
  85 class CoffeeDb(TestCase):
  86     def test_db_loads(self):
  87         cfg = Conf()
  88         self.assertTrue(path.isfile(cfg.getCoffeeDbPath()))
  89         db = Db(cfg.getCoffeeDbPath())
  90         self.assertIsNotNone(db.con)
  91         self.assertIsNotNone(db.cur)
  92
  93     def test_test_db_loads(self):
  94         create_test_db(TESTDB_FILENAME)
  95         try:
  96             db = Db(TESTDB_FILENAME)
  97             self.assertIsNotNone(db.con)
  98             self.assertIsNotNone(db.cur)
  99         finally:
 100             delete_test_db(TESTDB_FILENAME)
 101
 102 class FailCoffeeDb(TestCase):
 103     def test_db_path_not_set(self):
 104         self.assertRaises(FileNotSetError, Db)
 105
 106 class TopMateDrinkers(TestCase):
 107     def test_test_top_mate_drinkers(self):
 108         create_test_db(TESTDB_FILENAME)
 109         try:
 110             db = Db(TESTDB_FILENAME)
 111             top = db.getTopMateDrinkers()
 112             self.assertEqual(
 113                 top,
 114                 [
 115                     ("tester", 4*0.5 + 4*0.33),
 116                     ("newer", 2*0.33),
 117                     ("clone", 2*0.33),
 118                 ]
 119             )
 120         finally:
 121             delete_test_db(TESTDB_FILENAME)
 122
 123     def test_test_top_mate_drinkers_len(self):
 124         create_test_db(TESTDB_FILENAME)
 125         try:
 126             db = Db(TESTDB_FILENAME)
 127             top = db.getTopMateDrinkers()
 128             self.assertIsNotNone(top)
 129             self.assertEqual(len(top), 3)
 130         finally:
 131             delete_test_db(TESTDB_FILENAME)
 132
 133     def test_top_mate_drinkers_len(self):
 134         cfg = Conf()
 135         db = Db(cfg.getCoffeeDbPath())
 136         top = db.getTopMateDrinkers()
 137         self.assertIsNotNone(top)
 138         self.assertTrue(0 <= len(top))
 139
 140     def test_test_top_mate_drinkers_order(self):
 141         create_test_db(TESTDB_FILENAME)
 142         try:
 143             db = Db(TESTDB_FILENAME)
 144             top = db.getTopMateDrinkers()
 145             topsorted = list(top)
 146             topsorted.sort(key=lambda x: (x[1], x[0]), reverse=True)
 147             self.assertEqual(
 148                 top,
 149                 topsorted
 150             )
 151         finally:
 152             delete_test_db(TESTDB_FILENAME)
 153
 154     def test_top_mate_drinkers_order(self):
 155         cfg = Conf()
 156         db = Db(cfg.getCoffeeDbPath())
 157         top = db.getTopMateDrinkers()
 158         topsorted = list(top)
 159         topsorted.sort(key=lambda x: (x[1], x[0]), reverse=True)
 160         self.assertEqual(
 161             top,
 162             topsorted
 163         )
 164
 165 class DrunkSum(TestCase):
 166     tdy = datetime.now()
 167     tdy += timedelta(days=1)
 168     tdystr = tdy.strftime("%Y-%m-%d")
 169     lw = tdy - timedelta(days=8)
 170     lwstr = lw.strftime("%Y-%m-%d")
 171
 172     def test_espresso_sum(self):
 173         create_test_db(TESTDB_FILENAME)
 174         try:
 175             db = Db(TESTDB_FILENAME)
 176             drunk = db.getDrunkSum("espresso", self.lwstr, self.tdystr)
 177             self.assertEqual(drunk, [])
 178         finally:
 179             delete_test_db(TESTDB_FILENAME)
 180
 181     def test_espressolungo_sum(self):
 182         create_test_db(TESTDB_FILENAME)
 183         try:
 184             db = Db(TESTDB_FILENAME)
 185             drunk = db.getDrunkSum("espresso lungo", self.lwstr, self.tdystr)
 186             self.assertEqual(drunk, [
 187                 ("tester", 4),
 188                 ("newer", 2),
 189                 ("clone", 2),
 190             ])
 191         finally:
 192             delete_test_db(TESTDB_FILENAME)
 193
 194     def test_mate_3_sum(self):
 195         create_test_db(TESTDB_FILENAME)
 196         try:
 197             db = Db(TESTDB_FILENAME)
 198             drunk = db.getDrunkSum("Club-Mate 0,33 l", self.lwstr, self.tdystr)
 199             self.assertEqual(drunk, [
 200                 ("tester", 4),
 201                 ("newer", 2),
 202                 ("clone", 2),
 203             ])
 204         finally:
 205             delete_test_db(TESTDB_FILENAME)
 206
 207     def test_mate_5_sum(self):
 208         create_test_db(TESTDB_FILENAME)
 209         try:
 210             db = Db(TESTDB_FILENAME)
 211             drunk = db.getDrunkSum("Club-Mate 0,5 l", self.lwstr, self.tdystr)
 212             self.assertEqual(drunk, [
 213                 ("tester", 4),
 214             ])
 215         finally:
 216             delete_test_db(TESTDB_FILENAME)
 217
 218     def test_nof_args(self):
 219         cfg = Conf()
 220         db = Db(cfg.getCoffeeDbPath())
 221         self.assertRaises(ArgCountError, db.getDrunkSum)
 222         self.assertRaises(ArgCountError, db.getDrunkSum, "espresso")
 223         self.assertRaises(
 224             ArgCountError,
 225             db.getDrunkSum,
 226             "espresso", "2010-10-20"
 227         )
 228         self.assertRaises(
 229             ArgCountError,
 230             db.getDrunkSum,
 231             "espresso", "lungo", "2010-10-20", "2020-10-20"
 232         )