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 ("tea", 7),
  30 ("espresso", 2),
  31 ("espresso lungo", 3),
  32 ("cappuccino", 1),
  33 ("latte macchiato", 4),
  34 ("Club-Mate 0,5 l", 5),
  35 ("Club-Mate 0,33 l", 6)
  36 ;
  37
  38 create table if not exists coffees (
  39 num integer primary key,
  40 id varchar(24) references users(id), -- id may be unknown
  41 flavor varchar(255) not null references flavors(name),
  42 time datetime default current_timestamp,
  43 UNIQUE (id, flavor, time)
  44 );
  45
  46
  47 create table if not exists days (
  48 num integer primary key not null
  49 );
  50
  51 insert or ignore into days values
  52 (0),(1),(2),(3),(4),(5),(6)
  53 ;
  54     """)
  55     cur.executescript("""
  56 INSERT OR IGNORE INTO users VALUES('1111','tester');
  57 INSERT OR IGNORE INTO users VALUES('2222','newer');
  58 INSERT OR IGNORE INTO users VALUES('3333','clone');
  59 INSERT INTO coffees (id, flavor, time) VALUES
  60 ('1111', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
  61 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-99 seconds')),
  62 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
  63 ('1111', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
  64 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-95 second')),
  65 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second')),
  66 ('1111', 'tea', datetime('now', 'localtime', '-95 second')),
  67 ('1111', 'espresso lungo', datetime('now', 'localtime', '-90 second')),
  68 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-90 second')),
  69 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-90 second')),
  70 ('1111', 'espresso lungo', datetime('now', 'localtime', '-85 second')),
  71 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-85 second')),
  72 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-85 second')),
  73 ('2222', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
  74 ('2222', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
  75 ('2222', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
  76 ('2222', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second')),
  77 ('3333', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
  78 ('3333', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
  79 ('3333', 'tea', datetime('now', 'localtime', '-99 second')),
  80 ('3333', 'tea', datetime('now', 'localtime', '-95 second')),
  81 ('3333', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
  82 ('3333', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-95 second')),
  83 ('3333', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second'));
  84     """)
  85     con.close()
  86
  87 def delete_test_db(fn):
  88     remove(fn)
  89
  90 class CoffeeDb(TestCase):
  91     def test_db_loads(self):
  92         cfg = Conf()
  93         self.assertTrue(path.isfile(cfg.getCoffeeDbPath()))
  94         db = Db(cfg.getCoffeeDbPath())
  95         self.assertIsNotNone(db.con)
  96         self.assertIsNotNone(db.cur)
  97
  98     def test_test_db_loads(self):
  99         create_test_db(TESTDB_FILENAME)
 100         try:
 101             db = Db(TESTDB_FILENAME)
 102             self.assertIsNotNone(db.con)
 103             self.assertIsNotNone(db.cur)
 104         finally:
 105             delete_test_db(TESTDB_FILENAME)
 106
 107 class FailCoffeeDb(TestCase):
 108     def test_db_path_not_set(self):
 109         self.assertRaises(FileNotSetError, Db)
 110
 111 class TopDrinks(TestCase):
 112     def setUp(self):
 113         create_test_db(TESTDB_FILENAME)
 114         self.db = Db(TESTDB_FILENAME)
 115
 116     def tearDown(self):
 117         delete_test_db(TESTDB_FILENAME)
 118
 119     def test_top_drinks(self):
 120         top = self.db.get_top_drinks()
 121         assert top == [
 122             ("espresso lungo", 8),
 123             ("Club-Mate 0,33 l", 8),
 124             ("Club-Mate 0,5 l", 5),
 125             ("tea", 3),
 126         ]
 127
 128 class TopMateDrinkers(TestCase):
 129     def test_test_top_mate_drinkers(self):
 130         create_test_db(TESTDB_FILENAME)
 131         try:
 132             db = Db(TESTDB_FILENAME)
 133             top = db.getTopMateDrinkers()
 134             self.assertEqual(
 135                 top,
 136                 [
 137                     ("tester", 4*0.5 + 4*0.33),
 138                     ("clone", 0.5 + 2*0.33),
 139                     ("newer", 2*0.33),
 140                 ]
 141             )
 142         finally:
 143             delete_test_db(TESTDB_FILENAME)
 144
 145     def test_test_top_mate_drinkers_len(self):
 146         create_test_db(TESTDB_FILENAME)
 147         try:
 148             db = Db(TESTDB_FILENAME)
 149             top = db.getTopMateDrinkers()
 150             self.assertIsNotNone(top)
 151             self.assertEqual(len(top), 3)
 152         finally:
 153             delete_test_db(TESTDB_FILENAME)
 154
 155     def test_top_mate_drinkers_len(self):
 156         cfg = Conf()
 157         db = Db(cfg.getCoffeeDbPath())
 158         top = db.getTopMateDrinkers()
 159         self.assertIsNotNone(top)
 160         self.assertTrue(0 <= len(top))
 161
 162     def test_test_top_mate_drinkers_order(self):
 163         create_test_db(TESTDB_FILENAME)
 164         try:
 165             db = Db(TESTDB_FILENAME)
 166             top = db.getTopMateDrinkers()
 167             topsorted = list(top)
 168             topsorted.sort(key=lambda x: (x[1], x[0]), reverse=True)
 169             self.assertEqual(
 170                 top,
 171                 topsorted
 172             )
 173         finally:
 174             delete_test_db(TESTDB_FILENAME)
 175
 176     def test_top_mate_drinkers_order(self):
 177         cfg = Conf()
 178         db = Db(cfg.getCoffeeDbPath())
 179         top = db.getTopMateDrinkers()
 180         topsorted = list(top)
 181         topsorted.sort(key=lambda x: (x[1], x[0]), reverse=True)
 182         self.assertEqual(
 183             top,
 184             topsorted
 185         )
 186
 187 class TopTeaDrinkers(TestCase):
 188     def setUp(self):
 189         create_test_db(TESTDB_FILENAME)
 190         self.db = Db(TESTDB_FILENAME)
 191
 192     def tearDown(self):
 193         delete_test_db(TESTDB_FILENAME)
 194
 195     def test_top_tea_drinkers(self):
 196         top = self.db.get_top_tea_drinkers()
 197         assert top == [
 198             ("clone", 2),
 199             ("tester", 1),
 200         ]
 201
 202 class DrunkSum(TestCase):
 203     tdy = datetime.now()
 204     tdy += timedelta(days=1)
 205     tdystr = tdy.strftime("%Y-%m-%d")
 206     lw = tdy - timedelta(days=8)
 207     lwstr = lw.strftime("%Y-%m-%d")
 208
 209     def test_espresso_sum(self):
 210         create_test_db(TESTDB_FILENAME)
 211         try:
 212             db = Db(TESTDB_FILENAME)
 213             drunk = db.getDrunkSum("espresso", self.lwstr, self.tdystr)
 214             self.assertEqual(drunk, [])
 215         finally:
 216             delete_test_db(TESTDB_FILENAME)
 217
 218     def test_espressolungo_sum(self):
 219         create_test_db(TESTDB_FILENAME)
 220         try:
 221             db = Db(TESTDB_FILENAME)
 222             drunk = db.getDrunkSum("espresso lungo", self.lwstr, self.tdystr)
 223             self.assertEqual(drunk, [
 224                 ("tester", 4),
 225                 ("newer", 2),
 226                 ("clone", 2),
 227             ])
 228         finally:
 229             delete_test_db(TESTDB_FILENAME)
 230
 231     def test_mate_3_sum(self):
 232         create_test_db(TESTDB_FILENAME)
 233         try:
 234             db = Db(TESTDB_FILENAME)
 235             drunk = db.getDrunkSum("Club-Mate 0,33 l", self.lwstr, self.tdystr)
 236             self.assertEqual(drunk, [
 237                 ("tester", 4),
 238                 ("newer", 2),
 239                 ("clone", 2),
 240             ])
 241         finally:
 242             delete_test_db(TESTDB_FILENAME)
 243
 244     def test_mate_5_sum(self):
 245         create_test_db(TESTDB_FILENAME)
 246         try:
 247             db = Db(TESTDB_FILENAME)
 248             drunk = db.getDrunkSum("Club-Mate 0,5 l", self.lwstr, self.tdystr)
 249             self.assertEqual(drunk, [
 250                 ("tester", 4),
 251                 ("clone", 1),
 252             ])
 253         finally:
 254             delete_test_db(TESTDB_FILENAME)
 255
 256     def test_nof_args(self):
 257         cfg = Conf()
 258         db = Db(cfg.getCoffeeDbPath())
 259         self.assertRaises(ArgCountError, db.getDrunkSum)
 260         self.assertRaises(ArgCountError, db.getDrunkSum, "espresso")
 261         self.assertRaises(
 262             ArgCountError,
 263             db.getDrunkSum,
 264             "espresso", "2010-10-20"
 265         )
 266         self.assertRaises(
 267             ArgCountError,
 268             db.getDrunkSum,
 269             "espresso", "lungo", "2010-10-20", "2020-10-20"
 270         )
 271
 272     def test_all_mate_sum(self):
 273         create_test_db(TESTDB_FILENAME)
 274         try:
 275             db = Db(TESTDB_FILENAME)
 276             drunk = db.getDrunkSum(
 277                 "Club-Mate 0,5 l;Club-Mate 0,33 l",
 278                 self.lwstr,
 279                 self.tdystr
 280             )
 281             self.assertEqual(drunk, [
 282                 ("tester", 8),
 283                 ("newer", 2),
 284                 ("clone", 3),
 285             ])
 286         finally:
 287             delete_test_db(TESTDB_FILENAME)
 288
 289     def test_all_mate_list(self):
 290         create_test_db(TESTDB_FILENAME)
 291         try:
 292             db = Db(TESTDB_FILENAME)
 293             drunk = db.getDrunkList(
 294                 "Club-Mate 0,5 l;Club-Mate 0,33 l",
 295                 self.lwstr,
 296                 self.tdystr
 297             )
 298             self.assertEqual(drunk, {
 299                 "tester": [4, 4],
 300                 "newer": [0, 2],
 301                 "clone": [1, 2],
 302             })
 303         finally:
 304             delete_test_db(TESTDB_FILENAME)