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