From 0f1610333324d58acafee8c0fa9d9c9bc293b219 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 31 May 2020 13:59:35 +0200
Subject: Use defined tags for tasks

---
 src/db/init.py      | 11 ++++++++++-
 src/db/tags.py      |  3 ++-
 src/db/task_tags.py | 30 ++++++++++++++++++++++++++++++
 src/db/tasks.py     | 21 ++++++++-------------
 4 files changed, 50 insertions(+), 15 deletions(-)
 create mode 100644 src/db/task_tags.py

(limited to 'src/db')

diff --git a/src/db/init.py b/src/db/init.py
index 8292dfc..6b4cbea 100644
--- a/src/db/init.py
+++ b/src/db/init.py
@@ -19,7 +19,6 @@ def init(path):
             "   updated_at INTEGER NOT NULL,"
             "   name TEXT NOT NULL,"
             "   duration INTEGER,"
-            "   tag TEXT,"
             "   difficulty INT,"
             "   priority INT,"
             "   description TEXT"
@@ -34,6 +33,16 @@ def init(path):
             "   color TEXT NOT NULL"
             " )")
 
+        cursor.execute(
+            " CREATE TABLE IF NOT EXISTS task_tags("
+            "   task_id INTEGER NOT NULL,"
+            "   tag_id INTEGER NOT NULL,"
+            "   created_at INTEGER NOT NULL,"
+            "   FOREIGN KEY (task_id) REFERENCES tasks(id),"
+            "   FOREIGN KEY (tag_id) REFERENCES tags(id),"
+            "   PRIMARY KEY (task_id, tag_id)"
+            " )")
+
         database.commit()
 
     return database
diff --git a/src/db/tags.py b/src/db/tags.py
index 0f0d345..76a276d 100644
--- a/src/db/tags.py
+++ b/src/db/tags.py
@@ -1,9 +1,10 @@
 from sqlite3 import Cursor
 import time
+from typing import List
 
 from model.tag import Tag, ValidTagForm
 
-def get(cursor: Cursor) -> Tag:
+def get(cursor: Cursor) -> List[Tag]:
     cursor.execute(
         " SELECT"
         "   id,"
diff --git a/src/db/task_tags.py b/src/db/task_tags.py
new file mode 100644
index 0000000..34366e0
--- /dev/null
+++ b/src/db/task_tags.py
@@ -0,0 +1,30 @@
+from sqlite3 import Cursor
+import time
+from typing import List
+
+from model.task_tag import TaskTag
+
+def get(cursor: Cursor) -> List[TaskTag]:
+    cursor.execute("SELECT task_id, tag_id FROM task_tags")
+    return [TaskTag(r[0], r[1]) for r in cursor.fetchall()]
+
+def insert_many(cursor: Cursor, task_id: int, tag_ids: List[int]) -> List[TaskTag] :
+    now = int(time.time())
+
+    task_tags = [TaskTag(task_id = task_id, tag_id = tag) for tag in tag_ids]
+
+    cursor.executemany(
+        " INSERT INTO task_tags("
+        "   task_id,"
+        "   tag_id,"
+        "   created_at"
+        " ) VALUES (?, ?, ?)",
+        [(t.task_id, t.tag_id, now) for t in task_tags])
+
+    return task_tags
+
+def delete(cursor: Cursor, task_ids: List[int]):
+    if len(task_ids) >= 1:
+        cursor.execute(
+            'DELETE FROM task_tags WHERE task_id IN (%s)' % ','.join('?'*len(task_ids)),
+            task_ids)
diff --git a/src/db/tasks.py b/src/db/tasks.py
index 29d3ba6..b72965b 100644
--- a/src/db/tasks.py
+++ b/src/db/tasks.py
@@ -1,9 +1,10 @@
 from sqlite3 import Cursor
 import time
+from typing import List
 
 from model.task import Task, ValidTaskForm
 
-def get(cursor: Cursor) -> Task:
+def get(cursor: Cursor) -> List[Task]:
     cursor.execute(
         " SELECT"
         "   id,"
@@ -11,7 +12,6 @@ def get(cursor: Cursor) -> Task:
         "   updated_at,"
         "   name,"
         "   duration,"
-        "   tag,"
         "   difficulty,"
         "   priority,"
         "   description"
@@ -26,10 +26,9 @@ def get(cursor: Cursor) -> Task:
             updated_at = task[2],
             name = task[3],
             duration = task[4],
-            tag = task[5],
-            difficulty = task[6],
-            priority = task[7],
-            description = task[8]
+            difficulty = task[5],
+            priority = task[6],
+            description = task[7]
         ))
 
     return res
@@ -42,12 +41,11 @@ def insert(cursor: Cursor, form: ValidTaskForm):
         "   updated_at,"
         "   name,"
         "   duration,"
-        "   tag,"
         "   difficulty,"
         "   priority,"
         "   description"
-        " ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
-        (now, now, form.name, form.duration, form.tag, int(form.difficulty), int(form.priority), form.description))
+        " ) VALUES (?, ?, ?, ?, ?, ?, ?)",
+        (now, now, form.name, form.duration, int(form.difficulty), int(form.priority), form.description))
 
     return Task(
         id = cursor.lastrowid,
@@ -55,7 +53,6 @@ def insert(cursor: Cursor, form: ValidTaskForm):
         updated_at = now,
         name = form.name,
         duration = form.duration,
-        tag = form.tag,
         difficulty = form.difficulty,
         priority = form.priority,
         description = form.description
@@ -69,12 +66,11 @@ def update(cursor: Cursor, task: Task, form: ValidTaskForm):
         "   updated_at = ?,"
         "   name = ?,"
         "   duration = ?,"
-        "   tag = ?,"
         "   difficulty = ?,"
         "   priority = ?,"
         "   description = ?"
         " WHERE id = ?",
-        (now, form.name, form.duration, form.tag, int(form.difficulty), int(form.priority), form.description, task.id))
+        (now, form.name, form.duration, int(form.difficulty), int(form.priority), form.description, task.id))
 
     return Task(
         id = task.id,
@@ -82,7 +78,6 @@ def update(cursor: Cursor, task: Task, form: ValidTaskForm):
         updated_at = now,
         name = form.name,
         duration = form.duration,
-        tag = form.tag,
         difficulty = form.difficulty,
         priority = form.priority,
         description = form.description
-- 
cgit v1.2.3