1 # -*- coding: utf-8 -*-
2 """Test database access."""
3 from os import path, remove
4 from sqlite3 import connect
5 from unittest import TestCase
6 from cbconf import Conf
7 from cbdb import Db, FileNotSetError
9 TESTDB_FILENAME = ".tmptest.db"
11 def create_test_db(fn):
15 pragma foreign_keys = ON;
17 create table if not exists users (
18 id varchar(24) primary key not null,
19 name varchar(255) default "human"
22 create table if not exists flavors (
23 name varchar(255) primary key not null,
24 ord integer not null default 999
27 insert or ignore into flavors values
29 ("espresso lungo", 3),
31 ("latte macchiato", 4),
32 ("Club-Mate 0,5 l", 5),
33 ("Club-Mate 0,33 l", 6)
36 create table if not exists coffees (
37 num integer primary key,
38 id varchar(24) references users(id), -- id may be unknown
39 flavor varchar(255) not null references flavors(name),
40 time datetime default current_timestamp,
41 UNIQUE (id, flavor, time)
45 create table if not exists days (
46 num integer primary key not null
49 insert or ignore into days values
50 (0),(1),(2),(3),(4),(5),(6)
54 INSERT INTO users VALUES('1111','tester');
55 INSERT INTO users VALUES('2222','newer');
56 INSERT INTO coffees (id, flavor, time) VALUES
57 ('1111', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
58 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-99 seconds')),
59 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
60 ('1111', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
61 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-95 second')),
62 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second')),
63 ('1111', 'espresso lungo', datetime('now', 'localtime', '-90 second')),
64 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-90 second')),
65 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-90 second')),
66 ('1111', 'espresso lungo', datetime('now', 'localtime', '-85 second')),
67 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-85 second')),
68 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-85 second')),
69 ('2222', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
70 ('2222', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
71 ('2222', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
72 ('2222', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second'));
76 def delete_test_db(fn):
79 class CoffeeDb(TestCase):
80 def test_db_loads(self):
82 self.assertTrue(path.isfile(cfg.getCoffeeDbPath()))
83 db = Db(cfg.getCoffeeDbPath())
84 self.assertIsNotNone(db.con)
85 self.assertIsNotNone(db.cur)
87 def test_test_db_loads(self):
88 create_test_db(TESTDB_FILENAME)
90 db = Db(TESTDB_FILENAME)
91 self.assertIsNotNone(db.con)
92 self.assertIsNotNone(db.cur)
94 delete_test_db(TESTDB_FILENAME)
96 class FailCoffeeDb(TestCase):
97 def test_db_path_not_set(self):
98 self.assertRaises(FileNotSetError, Db)
100 class TopMateDrinkers(TestCase):
101 def test_test_top_mate_drinkers(self):
102 create_test_db(TESTDB_FILENAME)
104 db = Db(TESTDB_FILENAME)
105 top = db.getTopMateDrinkers()
109 ("tester", 4*0.5 + 4*0.33),
114 delete_test_db(TESTDB_FILENAME)
116 def test_test_top_mate_drinkers_len(self):
117 create_test_db(TESTDB_FILENAME)
119 db = Db(TESTDB_FILENAME)
120 top = db.getTopMateDrinkers()
121 self.assertIsNotNone(top)
122 self.assertEqual(len(top), 2)
124 delete_test_db(TESTDB_FILENAME)
126 def test_top_mate_drinkers_len(self):
128 db = Db(cfg.getCoffeeDbPath())
129 top = db.getTopMateDrinkers()
130 self.assertIsNotNone(top)
131 self.assertTrue(0 <= len(top))
133 def test_test_top_mate_drinkers_order(self):
134 create_test_db(TESTDB_FILENAME)
136 db = Db(TESTDB_FILENAME)
137 top = db.getTopMateDrinkers()
138 topsorted = list(top)
139 topsorted.sort(key=lambda x: x[1], reverse=True)
145 delete_test_db(TESTDB_FILENAME)
147 def test_top_mate_drinkers_order(self):
149 db = Db(cfg.getCoffeeDbPath())
150 top = db.getTopMateDrinkers()
151 topsorted = list(top)
152 topsorted.sort(key=lambda x: x[1], reverse=True)