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,5 l', 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,5 l', datetime('now', 'localtime', '-95 second')),
74 ('2222', 'Club-Mate 0,33 l', datetime('now', 'localtime', '-95 second'));
78 def delete_test_db(fn):
81 class CoffeeDb(TestCase):
82 def test_db_loads(self):
84 self.assertTrue(path.isfile(cfg.getCoffeeDbPath()))
85 db = Db(cfg.getCoffeeDbPath())
86 self.assertIsNotNone(db.con)
87 self.assertIsNotNone(db.cur)
89 def test_test_db_loads(self):
90 create_test_db(TESTDB_FILENAME)
92 db = Db(TESTDB_FILENAME)
93 self.assertIsNotNone(db.con)
94 self.assertIsNotNone(db.cur)
96 delete_test_db(TESTDB_FILENAME)
98 class FailCoffeeDb(TestCase):
99 def test_db_path_not_set(self):
100 self.assertRaises(FileNotSetError, Db)