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 TopMateDrinkers(TestCase):
 112     def test_test_top_mate_drinkers(self):
 113         create_test_db(TESTDB_FILENAME)
 114         try:
 115             db = Db(TESTDB_FILENAME)
 116             top = db.getTopMateDrinkers()
 117             self.assertEqual(
 118                 top,
 119                 [
 120                     ("tester", 4*0.5 + 4*0.33),
 121                     ("clone", 0.5 + 2*0.33),
 122                     ("newer", 2*0.33),
 123                 ]
 124             )
 125         finally:
 126             delete_test_db(TESTDB_FILENAME)
 127
 128     def test_test_top_mate_drinkers_len(self):
 129         create_test_db(TESTDB_FILENAME)
 130         try:
 131             db = Db(TESTDB_FILENAME)
 132             top = db.getTopMateDrinkers()
 133             self.assertIsNotNone(top)
 134             self.assertEqual(len(top), 3)
 135         finally:
 136             delete_test_db(TESTDB_FILENAME)
 137
 138     def test_top_mate_drinkers_len(self):
 139         cfg = Conf()
 140         db = Db(cfg.getCoffeeDbPath())
 141         top = db.getTopMateDrinkers()
 142         self.assertIsNotNone(top)
 143         self.assertTrue(0 <= len(top))
 144
 145     def test_test_top_mate_drinkers_order(self):
 146         create_test_db(TESTDB_FILENAME)
 147         try:
 148             db = Db(TESTDB_FILENAME)
 149             top = db.getTopMateDrinkers()
 150             topsorted = list(top)
 151             topsorted.sort(key=lambda x: (x[1], x[0]), reverse=True)
 152             self.assertEqual(
 153                 top,
 154                 topsorted
 155             )
 156         finally:
 157             delete_test_db(TESTDB_FILENAME)
 158
 159     def test_top_mate_drinkers_order(self):
 160         cfg = Conf()
 161         db = Db(cfg.getCoffeeDbPath())
 162         top = db.getTopMateDrinkers()
 163         topsorted = list(top)
 164         topsorted.sort(key=lambda x: (x[1], x[0]), reverse=True)
 165         self.assertEqual(
 166             top,
 167             topsorted
 168         )
 169
 170 class TopTeaDrinkers(TestCase):
 171     def setUp(self):
 172         create_test_db(TESTDB_FILENAME)
 173         self.db = Db(TESTDB_FILENAME)
 174
 175     def tearDown(self):
 176         delete_test_db(TESTDB_FILENAME)
 177
 178     def test_top_tea_drinkers(self):
 179         top = self.db.get_top_tea_drinkers()
 180         assert top == [
 181             ("clone", 2),
 182             ("tester", 1),
 183         ]
 184
 185 class DrunkSum(TestCase):
 186     tdy = datetime.now()
 187     tdy += timedelta(days=1)
 188     tdystr = tdy.strftime("%Y-%m-%d")
 189     lw = tdy - timedelta(days=8)
 190     lwstr = lw.strftime("%Y-%m-%d")
 191
 192     def test_espresso_sum(self):
 193         create_test_db(TESTDB_FILENAME)
 194         try:
 195             db = Db(TESTDB_FILENAME)
 196             drunk = db.getDrunkSum("espresso", self.lwstr, self.tdystr)
 197             self.assertEqual(drunk, [])
 198         finally:
 199             delete_test_db(TESTDB_FILENAME)
 200
 201     def test_espressolungo_sum(self):
 202         create_test_db(TESTDB_FILENAME)
 203         try:
 204             db = Db(TESTDB_FILENAME)
 205             drunk = db.getDrunkSum("espresso lungo", self.lwstr, self.tdystr)
 206             self.assertEqual(drunk, [
 207                 ("tester", 4),
 208                 ("newer", 2),
 209                 ("clone", 2),
 210             ])
 211         finally:
 212             delete_test_db(TESTDB_FILENAME)
 213
 214     def test_mate_3_sum(self):
 215         create_test_db(TESTDB_FILENAME)
 216         try:
 217             db = Db(TESTDB_FILENAME)
 218             drunk = db.getDrunkSum("Club-Mate 0,33 l", self.lwstr, self.tdystr)
 219             self.assertEqual(drunk, [
 220                 ("tester", 4),
 221                 ("newer", 2),
 222                 ("clone", 2),
 223             ])
 224         finally:
 225             delete_test_db(TESTDB_FILENAME)
 226
 227     def test_mate_5_sum(self):
 228         create_test_db(TESTDB_FILENAME)
 229         try:
 230             db = Db(TESTDB_FILENAME)
 231             drunk = db.getDrunkSum("Club-Mate 0,5 l", self.lwstr, self.tdystr)
 232             self.assertEqual(drunk, [
 233                 ("tester", 4),
 234                 ("clone", 1),
 235             ])
 236         finally:
 237             delete_test_db(TESTDB_FILENAME)
 238
 239     def test_nof_args(self):
 240         cfg = Conf()
 241         db = Db(cfg.getCoffeeDbPath())
 242         self.assertRaises(ArgCountError, db.getDrunkSum)
 243         self.assertRaises(ArgCountError, db.getDrunkSum, "espresso")
 244         self.assertRaises(
 245             ArgCountError,
 246             db.getDrunkSum,
 247             "espresso", "2010-10-20"
 248         )
 249         self.assertRaises(
 250             ArgCountError,
 251             db.getDrunkSum,
 252             "espresso", "lungo", "2010-10-20", "2020-10-20"
 253         )
 254
 255     def test_all_mate_sum(self):
 256         create_test_db(TESTDB_FILENAME)
 257         try:
 258             db = Db(TESTDB_FILENAME)
 259             drunk = db.getDrunkSum(
 260                 "Club-Mate 0,5 l;Club-Mate 0,33 l",
 261                 self.lwstr,
 262                 self.tdystr
 263             )
 264             self.assertEqual(drunk, [
 265                 ("tester", 8),
 266                 ("newer", 2),
 267                 ("clone", 3),
 268             ])
 269         finally:
 270             delete_test_db(TESTDB_FILENAME)
 271
 272     def test_all_mate_list(self):
 273         create_test_db(TESTDB_FILENAME)
 274         try:
 275             db = Db(TESTDB_FILENAME)
 276             drunk = db.getDrunkList(
 277                 "Club-Mate 0,5 l;Club-Mate 0,33 l",
 278                 self.lwstr,
 279                 self.tdystr
 280             )
 281             self.assertEqual(drunk, {
 282                 "tester": [4, 4],
 283                 "newer": [0, 2],
 284                 "clone": [1, 2],
 285             })
 286         finally:
 287             delete_test_db(TESTDB_FILENAME)