Provide nix build

5 files changed, 265 insertions, 0 deletions

diff --git a/todo/db/__init__.py b/todo/db/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/todo/db/__init__.py
diff --git a/todo/db/init.py b/todo/db/init.py
new file mode 100644
index 0000000..5d847a3
--- /dev/null
+++ b/todo/db/init.py
@@ -0,0 +1,51 @@
+import sqlite3
+import os.path
+import time
+
+def init(path):
+
+    is_db_new = not os.path.isfile(path)
+
+    database = sqlite3.connect(path)
+
+    cursor = database.cursor()
+
+    if is_db_new:
+
+        cursor.execute(
+            " CREATE TABLE IF NOT EXISTS tasks("
+            "   id INTEGER PRIMARY KEY,"
+            "   created_at INTEGER NOT NULL,"
+            "   updated_at INTEGER NOT NULL,"
+            "   name TEXT NOT NULL,"
+            "   duration INTEGER,"
+            "   difficulty INT,"
+            "   priority INT,"
+            "   description TEXT,"
+            "   status TEXT"
+            " )")
+
+        cursor.execute(
+            " CREATE TABLE IF NOT EXISTS tags("
+            "   id INTEGER PRIMARY KEY,"
+            "   created_at INTEGER NOT NULL,"
+            "   updated_at INTEGER NOT NULL,"
+            "   name TEXT NOT NULL,"
+            "   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)"
+            " )")
+
+    cursor.execute("PRAGMA foreign_keys = ON")
+
+    database.commit()
+
+    return database
diff --git a/todo/db/tags.py b/todo/db/tags.py
new file mode 100644
index 0000000..c5ce33c
--- /dev/null
+++ b/todo/db/tags.py
@@ -0,0 +1,72 @@
+from sqlite3 import Cursor
+import time
+from typing import List
+
+from todo.model.tag import Tag, ValidTagForm
+
+def get(cursor: Cursor) -> List[Tag]:
+    cursor.execute(
+        " SELECT"
+        "   id,"
+        "   created_at,"
+        "   updated_at,"
+        "   name,"
+        "   color"
+        " FROM tags")
+
+    res = []
+
+    for tag in cursor.fetchall():
+        res.append(Tag(
+            id = tag[0],
+            created_at = tag[1],
+            updated_at = tag[2],
+            name = tag[3],
+            color = tag[4]
+        ))
+
+    return res
+
+def insert(cursor: Cursor, form: ValidTagForm):
+    now = int(time.time())
+    cursor.execute(
+        " INSERT INTO tags("
+        "   created_at,"
+        "   updated_at,"
+        "   name,"
+        "   color"
+        " ) VALUES (?, ?, ?, ?)",
+        (now, now, form.name, form.color))
+
+    return Tag(
+        id = cursor.lastrowid,
+        created_at = now,
+        updated_at = now,
+        name = form.name,
+        color = form.color
+    )
+
+def update(cursor: Cursor, tag: Tag, form: ValidTagForm):
+    now = int(time.time())
+
+    cursor.execute(
+        " UPDATE tags SET"
+        "   updated_at = ?,"
+        "   name = ?,"
+        "   color = ?"
+        " WHERE id = ?",
+        (now, form.name, form.color, tag.id))
+
+    return Tag(
+        id = tag.id,
+        created_at = tag.created_at,
+        updated_at = now,
+        name = form.name,
+        color = form.color
+    )
+
+def delete(cursor: Cursor, ids):
+    if len(ids) >= 1:
+        cursor.execute(
+            "DELETE FROM tags WHERE id IN (%s)" % ",".join("?"*len(ids)),
+            ids)
diff --git a/todo/db/task_tags.py b/todo/db/task_tags.py
new file mode 100644
index 0000000..0fae5f9
--- /dev/null
+++ b/todo/db/task_tags.py
@@ -0,0 +1,39 @@
+from sqlite3 import Cursor
+import time
+from typing import List
+
+from todo.model.task_tag import TaskTag
+
+def one_is_used(cursor: Cursor, tag_ids: List[int]) -> bool:
+    if len(tag_ids) >= 1:
+        cursor.execute(
+            "SELECT task_id FROM task_tags WHERE tag_id IN (%s) LIMIT 1" % ",".join("?"*len(tag_ids)),
+            tag_ids)
+        return len(cursor.fetchall()) == 1
+    else:
+        return False
+
+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/todo/db/tasks.py b/todo/db/tasks.py
new file mode 100644
index 0000000..fc23bf0
--- /dev/null
+++ b/todo/db/tasks.py
@@ -0,0 +1,103 @@
+from sqlite3 import Cursor
+import time
+from typing import List
+
+from todo.model.task import Task, ValidTaskForm
+from todo.model.status import Status
+from todo.model import difficulty, priority, status
+
+def get(cursor: Cursor, s: Status) -> List[Task]:
+    cursor.execute(
+        " SELECT"
+        "   id,"
+        "   created_at,"
+        "   updated_at,"
+        "   name,"
+        "   duration,"
+        "   difficulty,"
+        "   priority,"
+        "   description"
+        " FROM"
+        "   tasks"
+        " WHERE"
+        "   status = ?",
+        (status.format(s),))
+
+    res = []
+
+    for task in cursor.fetchall():
+        res.append(Task(
+            id = task[0],
+            created_at = task[1],
+            updated_at = task[2],
+            name = task[3],
+            duration = task[4],
+            difficulty = difficulty.parse(task[5]),
+            priority = priority.parse(task[6]),
+            description = task[7]
+        ))
+
+    return res
+
+def insert(cursor: Cursor, s: Status, form: ValidTaskForm):
+    now = int(time.time())
+    cursor.execute(
+        " INSERT INTO tasks("
+        "   created_at,"
+        "   updated_at,"
+        "   name,"
+        "   duration,"
+        "   difficulty,"
+        "   priority,"
+        "   description,"
+        "   status"
+        " ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
+        (now, now, form.name, form.duration, difficulty.format(form.difficulty), priority.format(form.priority), form.description, status.format(s)))
+
+    return Task(
+        id = cursor.lastrowid,
+        created_at = now,
+        updated_at = now,
+        name = form.name,
+        duration = form.duration,
+        difficulty = form.difficulty,
+        priority = form.priority,
+        description = form.description
+    )
+
+def update(cursor: Cursor, task: Task, form: ValidTaskForm):
+    now = int(time.time())
+
+    cursor.execute(
+        " UPDATE tasks SET"
+        "   updated_at = ?,"
+        "   name = ?,"
+        "   duration = ?,"
+        "   difficulty = ?,"
+        "   priority = ?,"
+        "   description = ?"
+        " WHERE id = ?",
+        (now, form.name, form.duration, difficulty.format(form.difficulty), priority.format(form.priority), form.description, task.id))
+
+    return Task(
+        id = task.id,
+        created_at = task.created_at,
+        updated_at = now,
+        name = form.name,
+        duration = form.duration,
+        difficulty = form.difficulty,
+        priority = form.priority,
+        description = form.description
+    )
+
+def delete(cursor: Cursor, ids: List[int]):
+    if len(ids) >= 1:
+        cursor.execute(
+            "DELETE FROM tasks WHERE id IN (%s)" % ",".join("?"*len(ids)),
+            ids)
+
+def update_status(cursor: Cursor, ids: List[int], s: Status):
+    if len(ids) >= 1:
+        cursor.execute(
+            "UPDATE tasks SET status = ? WHERE id IN (%s)" % ",".join("?"*len(ids)),
+            [status.format(s)] + ids)