[PATCH coffee-flask] 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. This patch resolves this issue by checking the database for the same records before adding the new one. --- Vyřešení bugu z 18/02/2019. coffee_db.py | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) diff --git a/coffee_db.py b/coffee_db.py index 7e28f92..d21b36c 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -51,7 +51,11 @@ 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)) + res = list(c.execute("select * from coffees where id = ? and flavor = ? and time = ?", (uid, flavor, time))) + + if len(res) < 1: + c.execute("insert into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time)) + close_db(conn) def flavors(): -- 2.7.4
On Mon, Feb 25, 2019 at 03:37:52PM +0100, Jaroslav Klapalek wrote:
In case that coffee machine loses connection during recording of a coffee, this coffee is added once again as a part of offlineQueue.
This patch resolves this issue by checking the database for the same records before adding the new one. --- Vyřešení bugu z 18/02/2019. coffee_db.py | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-)
diff --git a/coffee_db.py b/coffee_db.py index 7e28f92..d21b36c 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -51,7 +51,11 @@ 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)) + res = list(c.execute("select * from coffees where id = ? and flavor = ? and time = ?", (uid, flavor, time))) + + if len(res) < 1: + c.execute("insert into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time))
Vyřešil bych to možná rovnou SQL dotazem viz https://stackoverflow.com/questions/19337029/insert-if-not-exists-statement-... jiri
+ close_db(conn)
def flavors(): -- 2.7.4
_______________________________________________ Coffee mailing list Coffee@rtime.felk.cvut.cz https://rtime.felk.cvut.cz/mailman/listinfo/coffee
Aha, netušil jsem, že něco takového existuje. (Narazil jsem jen na 'INSERT OR IGNORE'.) Přepíšu a pošlu. J. Dne 26. 02. 19 v 7:08 Jiri Vlasak napsal(a):
On Mon, Feb 25, 2019 at 03:37:52PM +0100, Jaroslav Klapalek wrote:
In case that coffee machine loses connection during recording of a coffee, this coffee is added once again as a part of offlineQueue.
This patch resolves this issue by checking the database for the same records before adding the new one. --- Vyřešení bugu z 18/02/2019. coffee_db.py | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-)
diff --git a/coffee_db.py b/coffee_db.py index 7e28f92..d21b36c 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -51,7 +51,11 @@ 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)) + res = list(c.execute("select * from coffees where id = ? and flavor = ? and time = ?", (uid, flavor, time))) + + if len(res) < 1: + c.execute("insert into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time)) Vyřešil bych to možná rovnou SQL dotazem viz https://stackoverflow.com/questions/19337029/insert-if-not-exists-statement-...
jiri
+ close_db(conn)
def flavors(): -- 2.7.4
_______________________________________________ Coffee mailing list Coffee@rtime.felk.cvut.cz https://rtime.felk.cvut.cz/mailman/listinfo/coffee
In case that coffee machine loses connection during recording of a coffee, this coffee is added once again as a part of offlineQueue. This patch resolves this issue by using 'EXISTS' condition. --- Podle návrhu od Jirky: Nová verze, která využívá pouze jeden dotaz. coffee_db.py | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) diff --git a/coffee_db.py b/coffee_db.py index 7e28f92..84b5ca0 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -51,7 +51,12 @@ 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 into coffees (id, flavor, time) + select ?, ?, ? + where not exists(select * from coffees where id = ? and flavor = ? and time = ?) + """, (uid, flavor, time, uid, flavor, time)) + close_db(conn) def flavors(): -- 2.7.4
On Tue, Feb 26, 2019 at 01:58:42PM +0100, Jaroslav Klapalek wrote:
In case that coffee machine loses connection during recording of a coffee, this coffee is added once again as a part of offlineQueue.
This patch resolves this issue by using 'EXISTS' condition. --- Podle návrhu od Jirky: Nová verze, která využívá pouze jeden dotaz.
Za me dobry. jiri
coffee_db.py | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-)
diff --git a/coffee_db.py b/coffee_db.py index 7e28f92..84b5ca0 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -51,7 +51,12 @@ 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 into coffees (id, flavor, time) + select ?, ?, ? + where not exists(select * from coffees where id = ? and flavor = ? and time = ?) + """, (uid, flavor, time, uid, flavor, time)) + close_db(conn)
def flavors(): -- 2.7.4
_______________________________________________ Coffee mailing list Coffee@rtime.felk.cvut.cz https://rtime.felk.cvut.cz/mailman/listinfo/coffee
On Tue, Feb 26 2019, Jaroslav Klapalek wrote:
In case that coffee machine loses connection during recording of a coffee, this coffee is added once again as a part of offlineQueue.
This patch resolves this issue by using 'EXISTS' condition. --- Podle návrhu od Jirky: Nová verze, která využívá pouze jeden dotaz. coffee_db.py | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-)
diff --git a/coffee_db.py b/coffee_db.py index 7e28f92..84b5ca0 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -51,7 +51,12 @@ 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 into coffees (id, flavor, time) + select ?, ?, ? + where not exists(select * from coffees where id = ? and flavor = ? and time = ?) + """, (uid, flavor, time, uid, flavor, time)) +
Tohle by si ale zasloužilo aspoň komentář co to dělá. Stačilo by "insert new entry if it doesn't exit". Ale myslím, že srozumitelnější by byla následující změna. Netestoval jsem to. Můžete to někdo zkusit? diff --git a/coffee_db.py b/coffee_db.py index 7e28f92..c4845d9 100644 --- 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 d9f7d8e..380e229 100644 --- 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) ); -M.
Takže `insert or ignore` funguje. Aktualizace databaze je třeba udělat před dump: sqlite3 coffee.db .dump > dumped Upravit v souboru `dumped`:
- time datetime default current_timestamp + time datetime default current_timestamp, + UNIQUE (id, flavor, time)
A vytvořit databázi znovu: rm coffee.db sqlite3 coffee.db < dumped Duplicitní záznamy se již nepřidají, ale zbytek jo. jiri On Wed, Feb 27, 2019 at 02:00:22PM +0100, Michal Sojka wrote:
On Tue, Feb 26 2019, Jaroslav Klapalek wrote:
In case that coffee machine loses connection during recording of a coffee, this coffee is added once again as a part of offlineQueue.
This patch resolves this issue by using 'EXISTS' condition. --- Podle návrhu od Jirky: Nová verze, která využívá pouze jeden dotaz. coffee_db.py | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-)
diff --git a/coffee_db.py b/coffee_db.py index 7e28f92..84b5ca0 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -51,7 +51,12 @@ 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 into coffees (id, flavor, time) + select ?, ?, ? + where not exists(select * from coffees where id = ? and flavor = ? and time = ?) + """, (uid, flavor, time, uid, flavor, time)) +
Tohle by si ale zasloužilo aspoň komentář co to dělá. Stačilo by "insert new entry if it doesn't exit".
Ale myslím, že srozumitelnější by byla následující změna. Netestoval jsem to. Můžete to někdo zkusit?
diff --git a/coffee_db.py b/coffee_db.py index 7e28f92..c4845d9 100644 --- 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 d9f7d8e..380e229 100644 --- 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) );
-M.
_______________________________________________ Coffee mailing list Coffee@rtime.felk.cvut.cz https://rtime.felk.cvut.cz/mailman/listinfo/coffee
participants (4)
-
Jaroslav Klapalek -
Jaroslav Klapálek
-
Jiri Vlasak -
Michal Sojka