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 users VALUES('3333','clone');
57 INSERT INTO coffees (id, flavor, time) VALUES
58 ('1111', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
59 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-99 seconds')),
60 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
61 ('1111', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
62 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-95 second')),
63 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second')),
64 ('1111', 'espresso lungo', datetime('now', 'localtime', '-90 second')),
65 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-90 second')),
66 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-90 second')),
67 ('1111', 'espresso lungo', datetime('now', 'localtime', '-85 second')),
68 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-85 second')),
69 ('1111', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-85 second')),
70 ('2222', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
71 ('2222', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
72 ('2222', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
73 ('2222', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second')),
74 ('3333', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
75 ('3333', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-99 seconds')),
76 ('3333', 'espresso lungo', datetime('now', 'localtime', '-95 second')),
77 ('3333', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second'));
81 def delete_test_db(fn):
84 class CoffeeDb(TestCase):
85 def test_db_loads(self):
87 self.assertTrue(path.isfile(cfg.getCoffeeDbPath()))
88 db = Db(cfg.getCoffeeDbPath())
89 self.assertIsNotNone(db.con)
90 self.assertIsNotNone(db.cur)
92 def test_test_db_loads(self):
93 create_test_db(TESTDB_FILENAME)
95 db = Db(TESTDB_FILENAME)
96 self.assertIsNotNone(db.con)
97 self.assertIsNotNone(db.cur)
99 delete_test_db(TESTDB_FILENAME)
101 class FailCoffeeDb(TestCase):
102 def test_db_path_not_set(self):
103 self.assertRaises(FileNotSetError, Db)
105 class TopMateDrinkers(TestCase):
106 def test_test_top_mate_drinkers(self):
107 create_test_db(TESTDB_FILENAME)
109 db = Db(TESTDB_FILENAME)
110 top = db.getTopMateDrinkers()
114 ("tester", 4*0.5 + 4*0.33),
120 delete_test_db(TESTDB_FILENAME)
122 def test_test_top_mate_drinkers_len(self):
123 create_test_db(TESTDB_FILENAME)
125 db = Db(TESTDB_FILENAME)
126 top = db.getTopMateDrinkers()
127 self.assertIsNotNone(top)
128 self.assertEqual(len(top), 3)
130 delete_test_db(TESTDB_FILENAME)
132 def test_top_mate_drinkers_len(self):
134 db = Db(cfg.getCoffeeDbPath())
135 top = db.getTopMateDrinkers()
136 self.assertIsNotNone(top)
137 self.assertTrue(0 <= len(top))
139 def test_test_top_mate_drinkers_order(self):
140 create_test_db(TESTDB_FILENAME)
142 db = Db(TESTDB_FILENAME)
143 top = db.getTopMateDrinkers()
144 topsorted = list(top)
145 topsorted.sort(key=lambda x: (x[1], x[0]), reverse=True)
151 delete_test_db(TESTDB_FILENAME)
153 def test_top_mate_drinkers_order(self):
155 db = Db(cfg.getCoffeeDbPath())
156 top = db.getTopMateDrinkers()
157 topsorted = list(top)
158 topsorted.sort(key=lambda x: (x[1], x[0]), reverse=True)