[PATCH identifiers v6] Add support for multiple identifiers
Users can register multiple identifiers for a single account. Also they can rename identifiers (each identifier has its own name) and unregister/remove them from the account. Required: Create new table in database (will be created automatically on first startup) CREATE TABLE if not exists identifiers ( `userid` varchar ( 24 ) NOT NULL, `id` varchar ( 24 ) PRIMARY KEY NOT NULL, `name` varchar ( 24 ), `status` INTEGER NOT NULL DEFAULT 1, FOREIGN KEY(`userid`) REFERENCES `users`(`id`) ); Note: `status` is used for activating/deactivating associations. This table creates 1:N relation (users:identifiers), where 'userid' is marked as foreign key from 'users' table. There is no reason for changing 'users.id', but it could be done. Column 'id' is marked as primary key to avoid creating M:N relation instead. Each time a relation is removed, it is marked as deactivated and it is deleted from the table next time, when a new relation with the same identifier is created. Each coffee is now saved with 'identifiers.id' instead of 'users.id'. Because we were not using custom id in 'users' table, we do not have to change anything in currently recorded data/design of tables. On user creation / first login 'users.id' (or id of identifier) and id of identifier are added to 'identifiers' table. Selected changes:
app.py:login() From now on we expect that received variable is 'iid' (id of identi- fier). At first this identifier is checked if it belongs to a user. If True: 'uid' (users.id) is found in database, Otherwise: 'iid' is registred as 'uid' for a new user.
app.py:user_XXX_identifier() Functions add/rename/disable modifies identifiers for current user. Note: 'session["uid"]' is used because client side (.js) does not know the difference between 'uid' and 'iid' (and there is no reason to change this). This solves a problem of invalid ids when trying to modify ID#1 of account ID#0 while logged by ID#2 in case we are assuming that id of identifier ID#0 is the same as 'users.id'.
app.py:coffee_add() Add consistency check to avoid situations when user without valid identifier is trying to register a coffee.
coffee_db.py Add functions to match new functions in app.py. Also modify current functions to use 'identifiers' table in SQL queries.
coffee_db.py:add_user_identifier() Creates relation between user and identifier. It is also deleting old (and deactivated!) relation from the database before registering a new one.
templates/main.js:addIdentifier_start() and addIdentifier_finish() Modifies UI for the user and changes 'identifier_registration' variable.
templates/user.html Add table of identifiers for current user, which contains names, ids and buttons to rename/remove identifier from current account.
Possible TODOs: - add option to merge two identifiers (e. g. if one is lost, it should be possible to transfer all coffees to different one), - divide identifiers to 2+ groups -- 'master key' and 'regular', so the public ones (e. g. mugs) cannot remove identifiers from an account. --- Změny oproti v5: - přidany komentáře k určitým částem kódu - v tabulce je udržována pouze poslední relace identifikátoru - ID identifikátoru je v tabulce jako PRIMARY KEY app.py | 56 ++++++++++++++++++++++++++++++++++++++++++-------- coffee_db.py | 59 ++++++++++++++++++++++++++++++++++++++++++++++------- coffee_db.sql | 8 ++++++++ templates/main.js | 55 +++++++++++++++++++++++++++++++++++++++++-------- templates/user.html | 21 +++++++++++++++++++ 5 files changed, 175 insertions(+), 24 deletions(-) diff --git a/app.py b/app.py index b1f50cc..25bfe14 100644 --- a/app.py +++ b/app.py @@ -31,19 +31,28 @@ def hello(): @app.route('/login', methods=["POST"]) -@app.route('/login/<uid>') -def login(uid=None): +@app.route('/login/<iid>') +def login(iid=None): if request.method == "POST": - uid = request.data.decode("utf-8") - if uid is not None: - db.add_user(uid) - session["uid"] = uid + iid = request.data.decode("utf-8") + if iid is not None: + uid = db.get_uid(iid) + + session["iid"] = iid + + if uid is None: + db.add_user_identifier(iid, iid, "Default") + session["uid"] = iid + db.add_user(iid) + else: + session["uid"] = uid return redirect(url_for('user')) @app.route('/logout') def logout(): session.pop('uid', None) + session.pop('iid', None) return redirect(url_for('user')) @@ -57,6 +66,8 @@ def user(): flavors=[_name for (_name, _ord) in db.flavors()], count=counts.get("Coffee", 0), count_mate=counts.get("Club-Mate", 0), + identifiers=db.list_user_identifiers(uid), + iid=session["iid"], stamp=time.time() ) # TODO: Replace stamp parameter with proper cache control HTTP @@ -73,6 +84,33 @@ def user_rename(): return redirect(url_for('user')) +@app.route('/user/identifier/add', methods=["POST"]) +def user_add_identifier(): + if request.method == "POST": + json = request.json + if "uid" in session and "id" in json: + db.add_user_identifier(session["uid"], json["id"], 'None') + return redirect(url_for('user')) + + +@app.route('/user/identifier/rename', methods=["POST"]) +def user_rename_identifier(): + if request.method == "POST": + json = request.json + if "uid" in session and all(key in json for key in ["id", "name"]): + db.rename_user_identifier(session["uid"], json["id"], json["name"]) + return redirect(url_for('user')) + + +@app.route('/user/identifier/disable', methods=["POST"]) +def user_disable_identifier(): + if request.method == "POST": + json = request.json + if "uid" in session and "id" in json: + db.disable_user_identifier(session["uid"], json["id"]) + return logout() # force logout + + @app.route("/coffee/graph_flavors") def coffee_graph_flavors(): days = request.args.get('days', default = 0, type = int) @@ -166,8 +204,10 @@ def coffee_graph_history(): def coffee_add(): if request.method == "POST": json = request.json - print("User '%(uid)s' had '%(flavor)s' at %(time)s" % json) - db.add_coffee(json["uid"], json["flavor"], json["time"]) + + if "iid" in session and all(key in json for key in ["flavor", "time"]): + print("User '%s' had '%s' at %s" % (session["iid"], json["flavor"], json["time"])) + db.add_coffee(session["iid"], json["flavor"], json["time"]) return redirect(url_for('user')) diff --git a/coffee_db.py b/coffee_db.py index acfd398..5c41d51 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -25,6 +25,29 @@ def add_user(uid): c.execute("insert or ignore into users (id) values (?)", (uid,)) close_db(conn) +def add_user_identifier(uid, iid, name): + # Check if this identifier is not currently associated with different account + if not get_uid(iid): + conn, c = open_db() + + # Try to remove old relation of identifier + # As 'delete or ignore' does not exist, workaround is used + res = c.execute("select * from identifiers where id = ? and status = ?", (iid, 0, )) + + # This is True when some rows were found before; delete old relation + if res.fetchone(): + res = c.execute("delete from identifiers where id = ? and status = ?", (iid, 0, )) + + # Add new relation + res = c.execute("insert into identifiers (userid, id, name) values (?, ?, ?)", (uid, iid, name, )) + + close_db(conn) + +def disable_user_identifier(uid, iid): + conn, c = open_db() + c.execute("update identifiers set status = ? where userid = ? and id = ?", (0, uid, iid, )) + close_db(conn) + def get_name(uid): conn, c = open_db() for name, in c.execute("select name from users where id = ?",(uid,)): @@ -33,18 +56,39 @@ def get_name(uid): close_db(conn) return None +def get_uid(iid): + conn, c = open_db() + res = list(c.execute(""" + select userid from identifiers where id = ? and status + """, (iid,))) + close_db(conn) + + return res[0][0] if len(res) > 0 else None def name_user(uid, name): conn, c = open_db() c.execute("update users set name = ? where id = ?", (name, uid)) close_db(conn) +def rename_user_identifier(uid, iid, name): + conn, c = open_db() + c.execute("update identifiers set name = ? where userid = ? and id = ?", (name, uid, iid, )) + close_db(conn) + def list_users(): conn, c = open_db() for row in c.execute("select * from users"): print(row) close_db(conn) +def list_user_identifiers(uid): + conn, c = open_db() + res = list(c.execute(""" + select * from identifiers where userid = ? and status + """, (uid,))) + close_db(conn) + return res + def add_coffee(uid, flavor, time=None): conn, c = open_db() @@ -80,15 +124,15 @@ def coffee_flavors(uid=None, days=0, start=0): query += " where date(time) between date('now', 'localtime', '-"+ str(days+start-1) +" days') and date('now', 'localtime', '-"+ str(start) +" days')" if uid is not None: - query += " and id = ?" + query += " and ids.userid = ? and ids.status" variables.append(uid) elif uid is not None: - query += " where id = ?" + query += " where ids.userid = ? and ids.status" variables.append(uid) res = list(c.execute(""" select f.name, count(c.flavor) from flavors f - left join (select * from coffees + left join (select * from coffees co left join identifiers ids on co.id=ids.id """+query+""") c on f.name=c.flavor group by f.name order by f.ord asc @@ -114,7 +158,7 @@ def coffee_history(uid=None): select strftime('%s', ds.d),count(c.flavor),c.flavor from (select num,date('now', 'localtime', -num || ' days') as d from days) ds left join - (select date(time, 'localtime') as time,flavor from coffees where id = ?) c + (select date(time, 'localtime') as time,flavor from coffees co left join identifiers ids on co.id = ids.id where ids.userid = ? and ids.status) c on d = date(c.time) group by d, c.flavor """ , (uid,))) @@ -129,7 +173,7 @@ def drink_count(uid=None, start=None, stop=None): clauses = [] if uid is not None: - clauses.append("id = ?") + clauses.append("ids.userid = ? and ids.status") args.append(uid) if start is not None: @@ -138,5 +182,6 @@ def drink_count(uid=None, start=None, stop=None): if stop is not None: clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop)) - return dict(c.execute("SELECT CASE WHEN flavor LIKE 'Club%' THEN 'Club-Mate' ELSE 'Coffee' END AS drink, COUNT() " - "FROM coffees WHERE " + " AND ".join(clauses) + " GROUP BY drink", args)) + return dict(c.execute("select case when flavor like 'Club%' then 'Club-Mate' else 'Coffee' end as drink, count(*) " + "from coffees co left join identifiers ids on co.id = ids.id where " + + " and ".join(clauses) + " group by drink", args)) diff --git a/coffee_db.sql b/coffee_db.sql index acb42a2..c7c88d1 100644 --- a/coffee_db.sql +++ b/coffee_db.sql @@ -35,3 +35,11 @@ create table if not exists days ( insert or ignore into days values (0),(1),(2),(3),(4),(5),(6) ; + +CREATE TABLE if not exists identifiers ( + `userid` varchar ( 24 ) NOT NULL, + `id` varchar ( 24 ) PRIMARY KEY NOT NULL, + `name` varchar ( 24 ), + `status` INTEGER NOT NULL DEFAULT 1, + FOREIGN KEY(`userid`) REFERENCES `users`(`id`) +); diff --git a/templates/main.js b/templates/main.js index b28b826..417dc40 100644 --- a/templates/main.js +++ b/templates/main.js @@ -2,11 +2,12 @@ var flask = "{{ url_for('hello', _external=True) }}" // State variables -var updateRemote = undefined; // defined iff remote server accessible -var timeToLogout = undefined; // defined during logout countdown +var updateRemote = undefined; // defined iff remote server accessible +var timeToLogout = undefined; // defined during logout countdown var logoutTimer; var reloadTimer = undefined; -var id_user; // ID of the user who is to be accounted for the next coffee +var id_user; // ID of the user who is to be accounted for the next coffee +var identifier_registration = false; // true if identifier is supposed to be registered for user console.log("hello from flask"); //sendJSON("{\"type\":\"empty\"}"); @@ -66,18 +67,27 @@ function hiddenUpdateRemote(json, time = new Date()) { case "empty": break; case "rfid": - login(msg.uid); + if (identifier_registration) { + ajax("POST", "user/identifier/add", JSON.stringify({ id: msg.uid }), "user"); + + addIdentifier_finish(); + } else { + login(msg.uid); + } break; case "keys": - var flavor = getFlavor(msg.key); - if (flavor !== "") { - addCoffee(flavor, time); + if (!identifier_registration) { + var flavor = getFlavor(msg.key); + if (flavor !== "") { + addCoffee(flavor, time); + } } break; case "ajax_failure": ajax(msg.method, msg.route, msg.data, msg.id); break; } + sendLog(json); } @@ -160,6 +170,7 @@ function logout() { ajax("GET", "logout", "", "user"); id_user = undefined; timeToLogout = undefined; + identifier_registration = false; } function countingTimeLogout(count_time) @@ -191,8 +202,7 @@ function getFlavor(letter) { function addCoffee(flavor, time = new Date()) { var data = JSON.stringify({ time: time.toISOString(), - flavor: flavor, - uid: id_user + flavor: flavor }); if (id_user) { ajax("POST", "coffee/add", data, "user"); @@ -202,6 +212,33 @@ function addCoffee(flavor, time = new Date()) { } } +function addIdentifier_start() { + identifier_registration = true; + document.getElementById("addIdentifier").disabled = true; + document.getElementById("labelIdentifier").style.visibility = "visible"; + document.getElementById("abortIdentifier").disabled = false; +} + +function addIdentifier_finish() { + identifier_registration = false; + document.getElementById("addIdentifier").disabled = false; + document.getElementById("labelIdentifier").style.visibility = "hidden"; + document.getElementById("abortIdentifier").disabled = true; +} + +function disableIdentifier(id) { + ajax("POST", "user/identifier/disable", JSON.stringify({ id: id }), "user"); +} + +function renameIdentifier(i) { + var data = JSON.stringify({ + id: document.getElementById("identifier_" + i).innerText, + name: document.getElementById("identifier_name_" + i).value + }); + + ajax("POST", "user/identifier/rename", data, "user"); +} + function sendLog(json) { ajax("POST", "log", json, "log"); } diff --git a/templates/user.html b/templates/user.html index 2cc3dec..790dab2 100644 --- a/templates/user.html +++ b/templates/user.html @@ -25,10 +25,31 @@ </p> <p> <form> + <label for="username">Username:</label> <input id="username" type="text" name="name"> <input type="button" value="rename" onclick="renameUser()"> </form> </p> + <form> + <table style="padding: 2px"> + <tr> + <td colspan="4" align="center"><b>Identifiers:</b></td> + </tr> + {% for id in identifiers %} + <tr> + <td><input type="text" id="identifier_name_{{ loop.index }}" value="{{ id[2] }}" /></td> + <td {% if iid == id[1] %} style="font-weight: bold" {% endif %}>#<span id="identifier_{{ loop.index }}">{{ id[1] }}</span></td> + <td><input type="button" value="rename" onclick="renameIdentifier({{ loop.index }})" /></td> + <td><input type="button" value="remove" onclick="disableIdentifier({{ id[1] }})" /></td> + </tr> + {% endfor %} + <tr> + <td><input type="button" id="addIdentifier" value="add identifier" onclick="addIdentifier_start()" /></td> + <td colspan="2" style="visibility: hidden" id="labelIdentifier"><b>Use your identifier.</b></td> + <td><input type="button" id="abortIdentifier" value="abort" disabled onclick="addIdentifier_finish()" /></td> + </tr> + </table> + </form> {% else %} <p>Use your card/token to log in...</p> -- 2.7.4
Ahoj, teď už to vypadá docela dobře, tak ti to konečně aplikuji. Pár drobností jsem tam ještě upravil (viz mé commity). Je tam ale ještě jeden závažnější problém a budu po tobě chtít abys ho opravil. Viz níže: On Wed, Aug 14 2019, Jaroslav Klapalek wrote:
Possible TODOs: - add option to merge two identifiers (e. g. if one is lost, it should be possible to transfer all coffees to different one), - divide identifiers to 2+ groups -- 'master key' and 'regular', so the public ones (e. g. mugs) cannot remove identifiers from an account. --- Změny oproti v5: - přidany komentáře k určitým částem kódu - v tabulce je udržována pouze poslední relace identifikátoru - ID identifikátoru je v tabulce jako PRIMARY KEY app.py | 56 ++++++++++++++++++++++++++++++++++++++++++-------- coffee_db.py | 59 ++++++++++++++++++++++++++++++++++++++++++++++------- coffee_db.sql | 8 ++++++++ templates/main.js | 55 +++++++++++++++++++++++++++++++++++++++++-------- templates/user.html | 21 +++++++++++++++++++ 5 files changed, 175 insertions(+), 24 deletions(-)
diff --git a/app.py b/app.py index b1f50cc..25bfe14 100644 --- a/app.py +++ b/app.py @@ -31,19 +31,28 @@ def hello():
@app.route('/login', methods=["POST"]) -@app.route('/login/<uid>') -def login(uid=None): +@app.route('/login/<iid>') +def login(iid=None): if request.method == "POST": - uid = request.data.decode("utf-8") - if uid is not None: - db.add_user(uid) - session["uid"] = uid + iid = request.data.decode("utf-8") + if iid is not None: + uid = db.get_uid(iid) + + session["iid"] = iid + + if uid is None:
Předpokládám, že tenhle if má sloužit k tomu, aby v nové tabulce indetifiers byly vytvořeny záznamy odpovídající aktuálním uživatelům. Bohužel to má jeden (možná nechtěný) efekt navíc: Mějme uživatele A a B. Uživatel A bude mít ID A1 a později si zaregistruje A2. Pak se rozhodne, že ID A1 věnuje uživateli B a bude používat jen A2. Uživatel A odstaní A1 ze svého účtu a dá ho uživateli B. B si bude chtít přidat A1 ke svému účtu, ale zapomene zmáčknout "add identifier" a jen si "pípne". V tom okamžiku tento kód id A1 aktivuje a přiřadí ho opět k uživateli A, protože jeho userid je A1. Uživatel B si tedy vesele může dávat kafe na účet A! Mělo by se to opravit minimálně tak, že v podmínce výše se nebude testovat jestli existuje uid odpovídající iid, ale jestli je dané iid v tabulce identifiers - ať aktivní nebo ne. Změna v podmínce zabrání problému popsanému výše, ale přinese další, méně závažný problém: Pokud dám A1 novému uživateli, který zatím není zaregistrován, nebude schopen začít A1 používat, protože při "pípnutí" se nic nestane (v kódu níže by se měla opravit větev else). Kdybychom chtěli, aby se nový uživatel mohl s A1 zaregistrovat, bylo by potřeba vygenerovat mu nové userid - takové, které v databázi ještě není, tedy ne A1. Asi nějaké náhodné. Bez tohoto bych dovedl žít, ale to, aby se B mohl bez vědomí A vydávat za něj, bych rád měl opravené.
+ db.add_user_identifier(iid, iid, "Default") + session["uid"] = iid + db.add_user(iid) + else: + session["uid"] = uid return redirect(url_for('user'))
-Michal
Ahoj, jasně, kouknu na to, až budu zpátky / budu mít chvíli u počítače. :-) J. Cituji Michal Sojka <michal.sojka@cvut.cz>:
Ahoj,
teď už to vypadá docela dobře, tak ti to konečně aplikuji. Pár drobností jsem tam ještě upravil (viz mé commity). Je tam ale ještě jeden závažnější problém a budu po tobě chtít abys ho opravil. Viz níže:
On Wed, Aug 14 2019, Jaroslav Klapalek wrote:
Possible TODOs: - add option to merge two identifiers (e. g. if one is lost, it should be possible to transfer all coffees to different one), - divide identifiers to 2+ groups -- 'master key' and 'regular', so the public ones (e. g. mugs) cannot remove identifiers from an account. --- Změny oproti v5: - přidany komentáře k určitým částem kódu - v tabulce je udržována pouze poslední relace identifikátoru - ID identifikátoru je v tabulce jako PRIMARY KEY app.py | 56 ++++++++++++++++++++++++++++++++++++++++++-------- coffee_db.py | 59 ++++++++++++++++++++++++++++++++++++++++++++++------- coffee_db.sql | 8 ++++++++ templates/main.js | 55 +++++++++++++++++++++++++++++++++++++++++-------- templates/user.html | 21 +++++++++++++++++++ 5 files changed, 175 insertions(+), 24 deletions(-)
diff --git a/app.py b/app.py index b1f50cc..25bfe14 100644 --- a/app.py +++ b/app.py @@ -31,19 +31,28 @@ def hello():
@app.route('/login', methods=["POST"]) -@app.route('/login/<uid>') -def login(uid=None): +@app.route('/login/<iid>') +def login(iid=None): if request.method == "POST": - uid = request.data.decode("utf-8") - if uid is not None: - db.add_user(uid) - session["uid"] = uid + iid = request.data.decode("utf-8") + if iid is not None: + uid = db.get_uid(iid) + + session["iid"] = iid + + if uid is None:
Předpokládám, že tenhle if má sloužit k tomu, aby v nové tabulce indetifiers byly vytvořeny záznamy odpovídající aktuálním uživatelům. Bohužel to má jeden (možná nechtěný) efekt navíc:
Mějme uživatele A a B. Uživatel A bude mít ID A1 a později si zaregistruje A2. Pak se rozhodne, že ID A1 věnuje uživateli B a bude používat jen A2. Uživatel A odstaní A1 ze svého účtu a dá ho uživateli B. B si bude chtít přidat A1 ke svému účtu, ale zapomene zmáčknout "add identifier" a jen si "pípne". V tom okamžiku tento kód id A1 aktivuje a přiřadí ho opět k uživateli A, protože jeho userid je A1. Uživatel B si tedy vesele může dávat kafe na účet A!
Mělo by se to opravit minimálně tak, že v podmínce výše se nebude testovat jestli existuje uid odpovídající iid, ale jestli je dané iid v tabulce identifiers - ať aktivní nebo ne.
Změna v podmínce zabrání problému popsanému výše, ale přinese další, méně závažný problém: Pokud dám A1 novému uživateli, který zatím není zaregistrován, nebude schopen začít A1 používat, protože při "pípnutí" se nic nestane (v kódu níže by se měla opravit větev else). Kdybychom chtěli, aby se nový uživatel mohl s A1 zaregistrovat, bylo by potřeba vygenerovat mu nové userid - takové, které v databázi ještě není, tedy ne A1. Asi nějaké náhodné. Bez tohoto bych dovedl žít, ale to, aby se B mohl bez vědomí A vydávat za něj, bych rád měl opravené.
+ db.add_user_identifier(iid, iid, "Default") + session["uid"] = iid + db.add_user(iid) + else: + session["uid"] = uid return redirect(url_for('user'))
-Michal
participants (3)
-
Jaroslav Klapalek -
klapajar＠fel.cvut.cz
-
Michal Sojka