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,5 l', datetime('now', 'localtime', '-95 second')),
  79 ('3333', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second'));
  80     """)
  81     con.close()
  82
  83 def delete_test_db(fn):
  84     remove(fn)
  85
  86 class CoffeeDb(TestCase):
  87     def test_db_loads(self):
  88         cfg = Conf()
  89         self.assertTrue(path.isfile(cfg.getCoffeeDbPath()))
  90         db = Db(cfg.getCoffeeDbPath())
  91         self.assertIsNotNone(db.con)
  92         self.assertIsNotNone(db.cur)
  93
  94     def test_test_db_loads(self):
  95         create_test_db(TESTDB_FILENAME)
  96         try:
  97             db = Db(TESTDB_FILENAME)
  98             self.assertIsNotNone(db.con)
  99             self.assertIsNotNone(db.cur)
 100         finally:
 101             delete_test_db(TESTDB_FILENAME)
 102
 103 class FailCoffeeDb(TestCase):
 104     def test_db_path_not_set(self):
 105         self.assertRaises(FileNotSetError, Db)
 106
 107 class TopMateDrinkers(TestCase):
 108     def test_test_top_mate_drinkers(self):
 109         create_test_db(TESTDB_FILENAME)
 110         try:
 111             db = Db(TESTDB_FILENAME)
 112             top = db.getTopMateDrinkers()
 113             self.assertEqual(
 114                 top,
 115                 [
 116                     ("tester", 4*0.5 + 4*0.33),
 117                     ("clone", 0.5 + 2*0.33),
 118                     ("newer", 2*0.33),
 119                 ]
 120             )
 121         finally:
 122             delete_test_db(TESTDB_FILENAME)
 123
 124     def test_test_top_mate_drinkers_len(self):
 125         create_test_db(TESTDB_FILENAME)
 126         try:
 127             db = Db(TESTDB_FILENAME)
 128             top = db.getTopMateDrinkers()
 129             self.assertIsNotNone(top)
 130             self.assertEqual(len(top), 3)
 131         finally:
 132             delete_test_db(TESTDB_FILENAME)
 133
 134     def test_top_mate_drinkers_len(self):
 135         cfg = Conf()
 136         db = Db(cfg.getCoffeeDbPath())
 137         top = db.getTopMateDrinkers()
 138         self.assertIsNotNone(top)
 139         self.assertTrue(0 <= len(top))
 140
 141     def test_test_top_mate_drinkers_order(self):
 142         create_test_db(TESTDB_FILENAME)
 143         try:
 144             db = Db(TESTDB_FILENAME)
 145             top = db.getTopMateDrinkers()
 146             topsorted = list(top)
 147             topsorted.sort(key=lambda x: (x[1], x[0]), reverse=True)
 148             self.assertEqual(
 149                 top,
 150                 topsorted
 151             )
 152         finally:
 153             delete_test_db(TESTDB_FILENAME)
 154
 155     def test_top_mate_drinkers_order(self):
 156         cfg = Conf()
 157         db = Db(cfg.getCoffeeDbPath())
 158         top = db.getTopMateDrinkers()
 159         topsorted = list(top)
 160         topsorted.sort(key=lambda x: (x[1], x[0]), reverse=True)
 161         self.assertEqual(
 162             top,
 163             topsorted
 164         )
 165
 166 class DrunkSum(TestCase):
 167     tdy = datetime.now()
 168     tdy += timedelta(days=1)
 169     tdystr = tdy.strftime("%Y-%m-%d")
 170     lw = tdy - timedelta(days=8)
 171     lwstr = lw.strftime("%Y-%m-%d")
 172
 173     def test_espresso_sum(self):
 174         create_test_db(TESTDB_FILENAME)
 175         try:
 176             db = Db(TESTDB_FILENAME)
 177             drunk = db.getDrunkSum("espresso", self.lwstr, self.tdystr)
 178             self.assertEqual(drunk, [])
 179         finally:
 180             delete_test_db(TESTDB_FILENAME)
 181
 182     def test_espressolungo_sum(self):
 183         create_test_db(TESTDB_FILENAME)
 184         try:
 185             db = Db(TESTDB_FILENAME)
 186             drunk = db.getDrunkSum("espresso lungo", self.lwstr, self.tdystr)
 187             self.assertEqual(drunk, [
 188                 ("tester", 4),
 189                 ("newer", 2),
 190                 ("clone", 2),
 191             ])
 192         finally:
 193             delete_test_db(TESTDB_FILENAME)
 194
 195     def test_mate_3_sum(self):
 196         create_test_db(TESTDB_FILENAME)
 197         try:
 198             db = Db(TESTDB_FILENAME)
 199             drunk = db.getDrunkSum("Club-Mate 0,33 l", self.lwstr, self.tdystr)
 200             self.assertEqual(drunk, [
 201                 ("tester", 4),
 202                 ("newer", 2),
 203                 ("clone", 2),
 204             ])
 205         finally:
 206             delete_test_db(TESTDB_FILENAME)
 207
 208     def test_mate_5_sum(self):
 209         create_test_db(TESTDB_FILENAME)
 210         try:
 211             db = Db(TESTDB_FILENAME)
 212             drunk = db.getDrunkSum("Club-Mate 0,5 l", self.lwstr, self.tdystr)
 213             self.assertEqual(drunk, [
 214                 ("tester", 4),
 215                 ("clone", 1),
 216             ])
 217         finally:
 218             delete_test_db(TESTDB_FILENAME)
 219
 220     def test_nof_args(self):
 221         cfg = Conf()
 222         db = Db(cfg.getCoffeeDbPath())
 223         self.assertRaises(ArgCountError, db.getDrunkSum)
 224         self.assertRaises(ArgCountError, db.getDrunkSum, "espresso")
 225         self.assertRaises(
 226             ArgCountError,
 227             db.getDrunkSum,
 228             "espresso", "2010-10-20"
 229         )
 230         self.assertRaises(
 231             ArgCountError,
 232             db.getDrunkSum,
 233             "espresso", "lungo", "2010-10-20", "2020-10-20"
 234         )
 235
 236     def test_all_mate_sum(self):
 237         create_test_db(TESTDB_FILENAME)
 238         try:
 239             db = Db(TESTDB_FILENAME)
 240             drunk = db.getDrunkSum(
 241                 "Club-Mate 0,5 l;Club-Mate 0,33 l",
 242                 self.lwstr,
 243                 self.tdystr
 244             )
 245             self.assertEqual(drunk, [
 246                 ("tester", 8),
 247                 ("newer", 2),
 248                 ("clone", 3),
 249             ])
 250         finally:
 251             delete_test_db(TESTDB_FILENAME)
 252
 253     def test_all_mate_list(self):
 254         create_test_db(TESTDB_FILENAME)
 255         try:
 256             db = Db(TESTDB_FILENAME)
 257             drunk = db.getDrunkList(
 258                 "Club-Mate 0,5 l;Club-Mate 0,33 l",
 259                 self.lwstr,
 260                 self.tdystr
 261             )
 262             self.assertEqual(drunk, {
 263                 "tester": [4, 4],
 264                 "newer": [2],
 265                 "clone": [1, 2],
 266             })
 267         finally:
 268             delete_test_db(TESTDB_FILENAME)