Add database UT

diff --git a/ut/test_db.py b/ut/test_db.py
new file mode 100644 (file)
index 0000000..913af2a
--- /dev/null
+++ b/ut/test_db.py
@@ -0,0 +1,85 @@
+# -*- coding: utf-8 -*-
+"""Test database access."""
+from os import remove
+from sqlite3 import connect
+from unittest import TestCase
+from cbdb import Db
+
+TESTDB_FILENAME = ".tmptest.db"
+
+def create_test_db():
+    con = connect(TESTDB_FILENAME)
+    cur = con.cursor()
+    cur.executescript("""
+pragma foreign_keys = ON;
+
+create table if not exists users (
+id varchar(24) primary key not null,
+name varchar(255) default "human"
+);
+
+create table if not exists flavors (
+name varchar(255) primary key not null,
+ord integer not null default 999
+);
+
+insert or ignore into flavors values
+("espresso", 2),
+("espresso lungo", 3),
+("cappuccino", 1),
+("latte macchiato", 4),
+("Club-Mate 0,5 l", 5),
+("Club-Mate 0,33 l", 6)
+;
+
+create table if not exists coffees (
+num integer primary key,
+id varchar(24) references users(id), -- id may be unknown
+flavor varchar(255) not null references flavors(name),
+time datetime default current_timestamp,
+UNIQUE (id, flavor, time)
+);
+
+
+create table if not exists days (
+num integer primary key not null
+);
+
+insert or ignore into days values
+(0),(1),(2),(3),(4),(5),(6)
+;
+    """)
+    cur.executescript("""
+INSERT INTO "users" VALUES('1111','tester');
+INSERT INTO "coffees" VALUES(1,'1111','espresso lungo','2019-03-20 11:22:43');
+INSERT INTO "coffees" VALUES(2,'1111','Club-Mate 0,5 l','2019-03-20 11:22:43');
+INSERT INTO "coffees" VALUES(3,'1111','Club-Mate 0,33 l','2019-03-20 11:22:43');
+INSERT INTO "coffees" VALUES(4,'1111','espresso lungo','2019-03-20 11:22:44');
+INSERT INTO "coffees" VALUES(5,'1111','Club-Mate 0,5 l','2019-03-20 11:22:44');
+INSERT INTO "coffees" VALUES(6,'1111','Club-Mate 0,33 l','2019-03-20 11:22:44');
+INSERT INTO "coffees" VALUES(7,'1111','espresso lungo','2019-03-20 11:22:45');
+INSERT INTO "coffees" VALUES(8,'1111','Club-Mate 0,5 l','2019-03-20 11:22:45');
+INSERT INTO "coffees" VALUES(9,'1111','Club-Mate 0,33 l','2019-03-20 11:22:45');
+INSERT INTO "coffees" VALUES(10,'1111','espresso lungo','2019-03-20 11:22:46');
+INSERT INTO "coffees" VALUES(11,'1111','Club-Mate 0,5 l','2019-03-20 11:22:46');
+INSERT INTO "coffees" VALUES(12,'1111','Club-Mate 0,33 l','2019-03-20 11:22:46');
+    """)
+    con.close()
+
+def delete_test_db():
+    remove(TESTDB_FILENAME)
+
+class CoffeesTable(TestCase):
+    def test_db_exists(self):
+        db = Db()
+        self.assertIsNotNone(db.con)
+        self.assertIsNotNone(db.cur)
+
+    def test_test_db_exists(self):
+        create_test_db()
+        try:
+            db = Db(TESTDB_FILENAME)
+            self.assertIsNotNone(db.con)
+            self.assertIsNotNone(db.cur)
+        finally:
+            delete_test_db()