diff options
Diffstat (limited to 'src/db')
| -rw-r--r-- | src/db/init.py | 21 | ||||
| -rw-r--r-- | src/db/tags.py | 71 | ||||
| -rw-r--r-- | src/db/tasks.py | 15 |
3 files changed, 97 insertions, 10 deletions
diff --git a/src/db/init.py b/src/db/init.py index 8fb7098..8292dfc 100644 --- a/src/db/init.py +++ b/src/db/init.py @@ -3,14 +3,20 @@ import os.path import time def init(path): + is_db_new = not os.path.isfile(path) + database = sqlite3.connect(path) + if is_db_new: - database.cursor().execute( + + cursor = database.cursor() + + cursor.execute( " CREATE TABLE IF NOT EXISTS tasks(" " id INTEGER PRIMARY KEY," " created_at INTEGER NOT NULL," - " modified_at INTEGER NOT NULL," + " updated_at INTEGER NOT NULL," " name TEXT NOT NULL," " duration INTEGER," " tag TEXT," @@ -18,5 +24,16 @@ def init(path): " priority INT," " description 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" + " )") + database.commit() + return database diff --git a/src/db/tags.py b/src/db/tags.py new file mode 100644 index 0000000..0f0d345 --- /dev/null +++ b/src/db/tags.py @@ -0,0 +1,71 @@ +from sqlite3 import Cursor +import time + +from model.tag import Tag, ValidTagForm + +def get(cursor: Cursor) -> 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/src/db/tasks.py b/src/db/tasks.py index 5fdd25e..29d3ba6 100644 --- a/src/db/tasks.py +++ b/src/db/tasks.py @@ -8,7 +8,7 @@ def get(cursor: Cursor) -> Task: " SELECT" " id," " created_at," - " modified_at," + " updated_at," " name," " duration," " tag," @@ -23,7 +23,7 @@ def get(cursor: Cursor) -> Task: res.append(Task( id = task[0], created_at = task[1], - modified_at = task[2], + updated_at = task[2], name = task[3], duration = task[4], tag = task[5], @@ -39,7 +39,7 @@ def insert(cursor: Cursor, form: ValidTaskForm): cursor.execute( " INSERT INTO tasks(" " created_at," - " modified_at," + " updated_at," " name," " duration," " tag," @@ -52,7 +52,7 @@ def insert(cursor: Cursor, form: ValidTaskForm): return Task( id = cursor.lastrowid, created_at = now, - modified_at = now, + updated_at = now, name = form.name, duration = form.duration, tag = form.tag, @@ -65,9 +65,8 @@ def update(cursor: Cursor, task: Task, form: ValidTaskForm): now = int(time.time()) cursor.execute( - " UPDATE tasks" - " SET" - " modified_at = ?," + " UPDATE tasks SET" + " updated_at = ?," " name = ?," " duration = ?," " tag = ?," @@ -80,7 +79,7 @@ def update(cursor: Cursor, task: Task, form: ValidTaskForm): return Task( id = task.id, created_at = task.created_at, - modified_at = now, + updated_at = now, name = form.name, duration = form.duration, tag = form.tag, |