aboutsummaryrefslogtreecommitdiff
path: root/src/db/init.py
blob: 77920cfd93d9701c0bc6ec18025915ae2ff5dc5b (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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"
            " )")

        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