From 1ea4fdf8072d6be99a13cc796c9775c05f8c9498 Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Sun, 13 Sep 2015 23:05:18 +0200 Subject: Add support for aliased EANs Some products are sold under different EANs (e.g. per-country EANs). The alias table can be used to map multiple EANs to a single product. --- src/database/database.vala | 43 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 43 insertions(+) (limited to 'src/database/database.vala') diff --git a/src/database/database.vala b/src/database/database.vala index f0b90c1..b4c2e7c 100644 --- a/src/database/database.vala +++ b/src/database/database.vala @@ -129,6 +129,9 @@ public class DataBase : Object { queries["cashbox_add"] = "INSERT INTO cashbox_diff ('user', 'amount', 'timestamp') VALUES (?, ?, ?)"; queries["cashbox_history"] = "SELECT user, amount, timestamp FROM cashbox_diff ORDER BY timestamp DESC LIMIT 10"; queries["cashbox_changes"] = "SELECT user, amount, timestamp FROM cashbox_diff WHERE timestamp >= ? and timestamp < ? ORDER BY timestamp ASC"; + queries["alias_ean_add"] = "INSERT OR IGNORE INTO ean_aliases (id, real_ean) VALUES (?, ?)"; + queries["alias_ean_get"] = "SELECT real_ean FROM ean_aliases WHERE id = ?"; + queries["alias_ean_list"] = "SELECT id, real_ean FROM ean_aliases ORDER BY id ASC"; /* compile queries into statements */ foreach(var entry in queries.entries) { @@ -969,4 +972,44 @@ public class DataBase : Object { return result; } + + public void ean_alias_add(uint64 ean, uint64 real_ean) throws DatabaseError { + statements["alias_ean_add"].reset(); + statements["alias_ean_add"].bind_text(1, "%llu".printf(ean)); + statements["alias_ean_add"].bind_text(2, "%llu".printf(real_ean)); + + int rc = statements["alias_ean_add"].step(); + + if(rc != Sqlite.DONE) { + throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc); + } + } + + public uint64 ean_alias_get(uint64 ean) { + uint64 result = ean; + + statements["alias_ean_get"].reset(); + statements["alias_ean_get"].bind_text(1, "%llu".printf(ean)); + + if(statements["alias_ean_get"].step() == Sqlite.ROW) + result = statements["alias_ean_get"].column_int64(0); + + return result; + } + + public EanAlias[] ean_alias_list() { + EanAlias[] result = {}; + + statements["alias_ean_list"].reset(); + + while(statements["alias_ean_list"].step() == Sqlite.ROW) { + EanAlias entry = { + statements["alias_ean_list"].column_int64(0), + statements["alias_ean_list"].column_int64(1), + }; + + result += entry; + }; + return result; + } } -- cgit v1.2.3