1 # -*- coding: utf-8 -*-
2 """Test database access."""
4 from sqlite3 import connect
5 from unittest import TestCase
8 TESTDB_FILENAME = ".tmptest.db"
11 con = connect(TESTDB_FILENAME)
14 pragma foreign_keys = ON;
16 create table if not exists users (
17 id varchar(24) primary key not null,
18 name varchar(255) default "human"
21 create table if not exists flavors (
22 name varchar(255) primary key not null,
23 ord integer not null default 999
26 insert or ignore into flavors values
28 ("espresso lungo", 3),
30 ("latte macchiato", 4),
31 ("Club-Mate 0,5 l", 5),
32 ("Club-Mate 0,33 l", 6)
35 create table if not exists coffees (
36 num integer primary key,
37 id varchar(24) references users(id), -- id may be unknown
38 flavor varchar(255) not null references flavors(name),
39 time datetime default current_timestamp,
40 UNIQUE (id, flavor, time)
44 create table if not exists days (
45 num integer primary key not null
48 insert or ignore into days values
49 (0),(1),(2),(3),(4),(5),(6)
53 INSERT INTO "users" VALUES('1111','tester');
54 INSERT INTO "coffees" VALUES(1,'1111','espresso lungo','2019-03-20 11:22:43');
55 INSERT INTO "coffees" VALUES(2,'1111','Club-Mate 0,5 l','2019-03-20 11:22:43');
56 INSERT INTO "coffees" VALUES(3,'1111','Club-Mate 0,33 l','2019-03-20 11:22:43');
57 INSERT INTO "coffees" VALUES(4,'1111','espresso lungo','2019-03-20 11:22:44');
58 INSERT INTO "coffees" VALUES(5,'1111','Club-Mate 0,5 l','2019-03-20 11:22:44');
59 INSERT INTO "coffees" VALUES(6,'1111','Club-Mate 0,33 l','2019-03-20 11:22:44');
60 INSERT INTO "coffees" VALUES(7,'1111','espresso lungo','2019-03-20 11:22:45');
61 INSERT INTO "coffees" VALUES(8,'1111','Club-Mate 0,5 l','2019-03-20 11:22:45');
62 INSERT INTO "coffees" VALUES(9,'1111','Club-Mate 0,33 l','2019-03-20 11:22:45');
63 INSERT INTO "coffees" VALUES(10,'1111','espresso lungo','2019-03-20 11:22:46');
64 INSERT INTO "coffees" VALUES(11,'1111','Club-Mate 0,5 l','2019-03-20 11:22:46');
65 INSERT INTO "coffees" VALUES(12,'1111','Club-Mate 0,33 l','2019-03-20 11:22:46');
70 remove(TESTDB_FILENAME)
72 class CoffeesTable(TestCase):
73 def test_db_exists(self):
75 self.assertIsNotNone(db.con)
76 self.assertIsNotNone(db.cur)
78 def test_test_db_exists(self):
81 db = Db(TESTDB_FILENAME)
82 self.assertIsNotNone(db.con)
83 self.assertIsNotNone(db.cur)