1 from datetime import datetime, timedelta
2 from unittest import TestCase
3 from sqlite3 import connect
5 from coffee_getter.db import Db, Q
6 import coffee_getter.conf as conf
9 class Queries(TestCase):
10 def test_get_drinks(self):
11 assert Q("get_drinks") == """
13 SELECT count(*), flavor
15 WHERE (coffees.time BETWEEN
16 datetime('now', 'localtime', '-7 days')
17 AND datetime('now', 'localtime'))
22 def test_get_drinkers(self):
23 assert Q("get_drinkers_of", "FOO") == """
25 SELECT count(*), users.name FROM coffees
26 LEFT JOIN identifiers on coffees.id = identifiers.userid
27 LEFT JOIN users on identifiers.userid = users.id
29 AND (coffees.time BETWEEN
30 datetime('now', 'localtime', '-7 days')
31 AND datetime('now', 'localtime'))
32 GROUP BY identifiers.userid
35 assert Q("get_drinkers_of", "BAR", ["FROM", "TO"]) == """
37 SELECT count(*), users.name FROM coffees
38 LEFT JOIN identifiers on coffees.id = identifiers.userid
39 LEFT JOIN users on identifiers.userid = users.id
41 AND (coffees.time BETWEEN
42 datetime('FROM', 'localtime')
43 AND datetime('TO', 'localtime'))
44 GROUP BY identifiers.userid
47 assert Q("get_drinkers_of", ["FOO", "BAR", "B"], ["FROM", "TO"]) == """
49 SELECT count(*), users.name FROM coffees
50 LEFT JOIN identifiers on coffees.id = identifiers.userid
51 LEFT JOIN users on identifiers.userid = users.id
52 WHERE (flavor = 'FOO' OR flavor = 'BAR' OR flavor = 'B')
53 AND (coffees.time BETWEEN
54 datetime('FROM', 'localtime')
55 AND datetime('TO', 'localtime'))
56 GROUP BY identifiers.userid
60 def test_assert_error_raised(self):
61 self.assertRaises(AssertionError, Q, "get_drinkers_of")
62 self.assertRaises(AssertionError, Q, "get_drinkers_of", "")
63 self.assertRaises(AssertionError, Q, "get_drinkers_of", [])
64 self.assertRaises(AssertionError, Q, "get_drinkers_of", "foo", "")
65 self.assertRaises(AssertionError, Q, "get_drinkers_of", "foo", "bar")
66 self.assertRaises(AssertionError, Q, "get_drinkers_of", "foo", [])
69 TESTDB_PATH = ".tmptest.db"
72 def create_test_db(fn):
76 pragma foreign_keys = ON;
78 create table if not exists users (
79 id varchar(24) primary key not null,
80 name varchar(255) default "human"
83 create table if not exists flavors (
84 name varchar(255) primary key not null,
85 ord integer not null default 999,
86 type varchar(24) not null default ""
89 insert or ignore into flavors values
90 ("espresso", 2, "coffee"),
91 ("espresso lungo", 3, "coffee"),
92 ("cappuccino", 1, "coffee"),
93 ("latte macchiato", 4, "coffee"),
94 ("Club-Mate 0,5 l", 5, "Club-Mate"),
95 ("Club-Mate 0,33 l", 6, "Club-Mate"),
99 create table if not exists coffees (
100 num integer primary key,
101 id varchar(24) references users(id), -- id may be unknown
102 flavor varchar(255) not null references flavors(name),
103 time datetime default current_timestamp,
104 UNIQUE (id, flavor, time)
108 create table if not exists days (
109 num integer primary key not null
112 insert or ignore into days values
113 (0),(1),(2),(3),(4),(5),(6)
116 CREATE TABLE if not exists identifiers (
117 `userid` varchar ( 24 ) NOT NULL,
118 `id` varchar ( 24 ) PRIMARY KEY NOT NULL,
119 `name` varchar ( 24 ),
120 `active` INTEGER NOT NULL DEFAULT 1,
121 FOREIGN KEY(`userid`) REFERENCES `users`(`id`)
124 cur.executescript("""
125 INSERT OR IGNORE INTO users VALUES('1111','tester');
126 insert into identifiers (userid, id, name) values ('1111', '10', 'chip 10');
127 INSERT OR IGNORE INTO users VALUES('2222','newer');
128 insert into identifiers (userid, id, name) values ('2222', '20', 'chip 20');
129 INSERT OR IGNORE INTO users VALUES('3333','clone');
130 insert into identifiers (userid, id, name) values ('3333', '30', 'chip 30');
131 INSERT or ignore INTO coffees (id, flavor, time) VALUES
132 ('1111', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
133 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-99 seconds')),
134 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
135 ('1111', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
136 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-95 second')),
137 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second')),
138 ('1111', 'tea', datetime('now', 'localtime', '-95 second')),
139 ('1111', 'espresso lungo', datetime('now', 'localtime', '-90 second')),
140 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-90 second')),
141 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-90 second')),
142 ('1111', 'espresso lungo', datetime('now', 'localtime', '-85 second')),
143 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-85 second')),
144 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-85 second')),
145 ('2222', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
146 ('2222', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
147 ('2222', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
148 ('2222', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second')),
149 ('3333', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
150 ('3333', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
151 ('3333', 'tea', datetime('now', 'localtime', '-99 second')),
152 ('3333', 'tea', datetime('now', 'localtime', '-95 second')),
153 ('3333', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
154 ('3333', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-95 second')),
155 ('3333', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second'));
160 def delete_test_db(fn):
164 class Database(TestCase):
165 def test_connection_to_database(self):
166 db = Db(conf.DB_PATH)
170 def test_connection_to_test_database(self):
171 create_test_db(TESTDB_PATH)
177 delete_test_db(TESTDB_PATH)
180 class DrunkSummary(TestCase):
182 tdy += timedelta(days=1)
183 tdystr = tdy.strftime("%Y-%m-%d")
184 lw = tdy - timedelta(days=8)
185 lwstr = lw.strftime("%Y-%m-%d")
188 create_test_db(TESTDB_PATH)
189 self.db = Db(TESTDB_PATH)
192 delete_test_db(TESTDB_PATH)
194 def test_top_drinks(self):
195 top = self.db.get_top_drinks()
197 ("espresso lungo", 8),
198 ("Club-Mate 0,33 l", 8),
199 ("Club-Mate 0,5 l", 5),
202 def test_top_mate_drinkers(self):
203 assert self.db.get_top_mate_drinkers() == (
204 ("tester", 4*0.5 + 4*0.33),
205 ("clone", 0.5 + 2*0.33),
207 top_sorted = list(self.db.get_top_mate_drinkers())
208 top_sorted.sort(key=lambda x: (x[1], x[0]), reverse=True)
209 assert self.db.get_top_mate_drinkers() == tuple(top_sorted)
211 def test_top_tea_drinkers(self):
212 assert self.db.get_top_tea_drinkers() == (("clone", 2), ("tester", 1))
214 def test_coffee_sums(self):
215 s = self.db.get_drunk_sum("espresso", self.lwstr, self.tdystr)
217 s = self.db.get_drunk_sum("espresso lungo", self.lwstr, self.tdystr)
218 assert s == (("tester", 4), ("newer", 2), ("clone", 2))
220 def test_mate_sums(self):
221 s = self.db.get_drunk_sum("Club-Mate 0,33 l", self.lwstr, self.tdystr)
222 assert s == (("tester", 4), ("newer", 2), ("clone", 2))
223 s = self.db.get_drunk_sum("Club-Mate 0,5 l", self.lwstr, self.tdystr)
224 assert s == (("tester", 4), ("clone", 1))
225 s = self.db.get_drunk_sum(
226 "Club-Mate 0,5 l;Club-Mate 0,33 l",
229 assert s == (("tester", 8), ("newer", 2), ("clone", 3))
230 s = self.db.get_drunk_list(
231 "Club-Mate 0,5 l;Club-Mate 0,33 l",
234 assert s == {"tester": [4, 4], "newer": [0, 2], "clone": [1, 2]}
236 def test_assert_error_raised_for_bad_number_of_arguments(self):
237 self.assertRaises(TypeError, self.db.get_drunk_sum)
238 self.assertRaises(TypeError, self.db.get_drunk_sum, "")
239 self.assertRaises(TypeError, self.db.get_drunk_sum, [])
240 self.assertRaises(TypeError, self.db.get_drunk_sum, "espresso")
243 self.db.get_drunk_sum,
248 self.db.get_drunk_sum,