New subject: [PATCH identifiers v3] Add support for multiple identifiers

16 Nov 2018

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

CREATE TABLE "identifiers"
( `userid` varchar ( 24 ) NOT NULL, `id`
varchar ( 24 ) NOT NULL UNIQUE, `name` varchar ( 24 ),
`status` INTEGER NOT NULL DEFAULT 0,
FOREIGN KEY(`userid`) REFERENCES `users`(`id`) )

Note: 'status' is currently unused, so it may be omitted. In current
state it is designed for future extensions.

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.

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/remove 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.
...
templates/main.js:addIdentifier() and addIdentifier_finish()
Modifies UI for the user and changes 'updateRemote' variable.
...
templates/main.js:catchIdentifier()
Used instead of 'hiddenUpdateRemote' during identifier registration.
Otherwise it would be trying to login with the identifier.
...
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.
---
Verze, která opravuje překrytí jedné funkce s prvkem ve stránce.

 app.py              | 54 ++++++++++++++++++++++++++++++++++++++++-------
 coffee_db.py        | 42 +++++++++++++++++++++++++++++++------
 templates/main.js   | 60 ++++++++++++++++++++++++++++++++++++++++++++++++++++-
 templates/user.html | 21 +++++++++++++++++++
 4 files changed, 162 insertions(+), 15 deletions(-)

diff --git a/app.py b/app.py
index d66eff1..8374966 100644
--- a/app.py
+++ b/app.py
@@ -27,19 +27,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)
+
+        db.add_user_identifier(iid, iid, "Default")
+        session["iid"] = iid
+
+        if uid is None:
+            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'))
 
 
@@ -51,6 +60,8 @@ def user():
                                name=db.get_name(uid),
                                flavors=db.flavors(),
                                count=db.coffee_count(uid, 0),
+                               identifiers=db.list_user_identifiers(uid),
+                               iid=session["iid"],
                                stamp=time.time()
                                )
     # TODO: Replace stamp parameter with proper cache control HTTP
@@ -67,6 +78,31 @@ 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
+        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
+        db.rename_user_identifier(session["uid"], json["id"], json["name"])
+    return redirect(url_for('user'))
+
+
+@app.route('/user/identifier/remove', methods=["POST"])
+def user_remove_identifier():
+    if request.method == "POST":
+        json = request.json
+        db.remove_user_identifier(session["uid"], json["id"])
+    return logout() # force logout
+    #return redirect(url_for('user'))
+
+
 @app.route("/coffee/graph_flavors")
 def coffee_graph_flavors():
     days = request.args.get('days', default = 0, type = int)
@@ -160,8 +196,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"])
+        # Check if identifier is valid for current user
+        if json["iid"] in [i[1] for i in db.list_user_identifiers(db.get_uid(json["iid"]))]:
+            print("User '%(iid)s' had '%(flavor)s' at %(time)s" % json)
+            db.add_coffee(json["iid"], json["flavor"], json["time"])
     return redirect(url_for('user'))
 
 
diff --git a/coffee_db.py b/coffee_db.py
index 7e28f92..1db4290 100644
--- a/coffee_db.py
+++ b/coffee_db.py
@@ -25,6 +25,11 @@ def add_user(uid):
     c.execute("insert or ignore into users (id) values (?)", (uid,))
     close_db(conn)
 
+def add_user_identifier(uid, iid, name):
+    conn, c = open_db()
+    c.execute("insert or ignore into identifiers values (?, ?, ?)", (uid, iid, name, ))
+    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 +38,43 @@ 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 = ?
+            """, (iid,)))
+
+    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 remove_user_identifier(uid, iid):
+    conn, c = open_db()
+    c.execute("delete from identifiers where userid = ? and id = ?", (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 = ?
+            """, (uid,)))
+    close_db(conn)
+    return res
+
 
 def add_coffee(uid, flavor, time=None):
     conn, c = open_db()
@@ -80,15 +110,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 = ?"
             variables.append(uid)
     elif uid is not None:
-        query += " where id = ?"
+        query += " where ids.userid = ?"
         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
         """, variables))
@@ -113,7 +143,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 = ?) c
             on d = date(c.time) group by d, c.flavor
             """
             , (uid,)))
@@ -128,7 +158,7 @@ def coffee_count(uid=None, start=None, stop=None):
     clauses = []
 
     if uid is not None:
-        clauses.append("id = ?")
+        clauses.append("ids.userid = ?")
         args.append(uid)
 
     if start is not None:
@@ -138,7 +168,7 @@ def coffee_count(uid=None, start=None, stop=None):
         clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop))
 
     for count, in c.execute(
-            "select count(*) from coffees where " +
+            "select count(*) from coffees co left join identifiers ids on co.id = ids.id where " +
             " and ".join(clauses)
             , args):
         res = count
diff --git a/templates/main.js b/templates/main.js
index 8135983..e927bfa 100644
--- a/templates/main.js
+++ b/templates/main.js
@@ -160,6 +160,7 @@ function logout() {
     ajax("GET", "logout", "", "user");
     id_user = undefined;
     timeToLogout = undefined;
+    updateRemote = hiddenUpdateRemote;
 }
 
 function countingTimeLogout(count_time)
@@ -192,7 +193,7 @@ function addCoffee(flavor, time = new Date()) {
     var data = JSON.stringify({
         time: time.toISOString(),
         flavor: flavor,
-        uid: id_user
+        iid: id_user
     });
     if (id_user) {
         ajax("POST", "coffee/add", data, "user");
@@ -202,6 +203,63 @@ function addCoffee(flavor, time = new Date()) {
     }
 }
 
+function addIdentifier_start() {
+    updateRemote = catchIdentifier;
+    document.getElementById("addIdentifier").disabled = true;
+    document.getElementById("labelIdentifier").style.visibility = "visible";
+    document.getElementById("abortIdentifier").disabled = false;
+}
+
+function addIdentifier_finish() {
+    updateRemote = hiddenUpdateRemote;
+    document.getElementById("addIdentifier").disabled = false;
+    document.getElementById("labelIdentifier").style.visibility = "hidden";
+    document.getElementById("abortIdentifier").disabled = true;
+}
+
+function catchIdentifier(json, time = new Date()) {
+    var msg = JSON.parse(json);
+
+    var data = JSON.stringify({
+        id: msg.uid,
+        uid: id_user
+    });
+
+    switch(msg.type) {
+        case "empty":
+            break;
+        case "rfid":
+            ajax("POST", "user/identifier/add", data, "user");
+            break;
+        case "keys":
+            break;
+        case "ajax_failure":
+            ajax(msg.method, msg.route, msg.data, msg.id);
+            break;
+    }
+    addIdentifier_finish();
+    sendLog(json);
+}
+
+function renameIdentifier(i) {
+    var data = JSON.stringify({
+        id: document.getElementById("identifier_" + i).innerText,
+        uid: id_user,
+        name: document.getElementById("identifier_name_" + i).value
+    });
+
+    ajax("POST", "user/identifier/rename", data, "user");
+}
+
+function removeIdentifier(id) {
+    var data = JSON.stringify({
+        id: id,
+        uid: id_user
+    });
+
+    ajax("POST", "user/identifier/remove", data, "user");
+}
+
 function sendLog(json) {
     ajax("POST", "log", json, "log");
 }
diff --git a/templates/user.html b/templates/user.html
index 7e549b9..ee016b2 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="removeIdentifier({{ 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>
+        </ul>
+    </form>
 {% else %}
     <p>Use your card/token to log in...</p>
 
-- 
2.7.4

    

[PATCH coffee-flask v2] Add support for multiple identifiers

Jaroslav Klapalek

Michal Sojka

Jaroslav Klapalek

Michal Sojka

Jaroslav Klapalek

Michal Sojka

tags

participants (2)