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
|
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,"
" due_date TEXT,"
" 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
|
