Fix duplicate recording of coffees

In case that coffee machine loses connection during recording of a
coffee, this coffee is added once again as a part of offlineQueue.

Note: This commit implements a simpler solution suggested by Michal
Sojka and tested by Jiří Vlasák.

diff --git a/coffee_db.py b/coffee_db.py
index 7e28f923d851e25c37e2e8eb22cbf49766441dba..b7206fe20563b910ac065b363cb41a6a9294f79b 100644 (file)
--- a/coffee_db.py
+++ b/coffee_db.py
@@ -51,7 +51,7 @@ def add_coffee(uid, flavor, time=None):
     if time is None:
         c.execute("insert into coffees (id, flavor) values (?,?)", (uid,flavor))
     else:
-        c.execute("insert into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time))
+        c.execute("insert or ignore into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time))
     close_db(conn)
 
 def flavors():

diff --git a/coffee_db.sql b/coffee_db.sql
index d9f7d8ea1dafff09de4a0376d3329842a87a64c1..380e229bba02e5afc94909e47e03f6c788c93c78 100644 (file)
--- a/coffee_db.sql
+++ b/coffee_db.sql
@@ -20,7 +20,8 @@ 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
+    time datetime default current_timestamp,
+    UNIQUE (id, flavor, datetime)
 );