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
51
52
53
54
|
BEGIN TRANSACTION;
DROP TABLE IF EXISTS "Schedules";
CREATE TABLE IF NOT EXISTS "Schedules" (
"id" INTEGER,
"name" TEXT NOT NULL,
PRIMARY KEY("id")
);
DROP TABLE IF EXISTS "Tasks";
CREATE TABLE IF NOT EXISTS "Tasks" (
"id" INTEGER,
"name" TEXT NOT NULL,
"description" TEXT,
"frequency" INTEGER NOT NULL,
"estimated_length" INTEGER,
"date_completed" INTEGER,
PRIMARY KEY("id")
);
DROP TABLE IF EXISTS "TimeSlotTasks";
CREATE TABLE IF NOT EXISTS "TimeSlotTasks" (
"timeslot_id" INTEGER NOT NULL,
"task_id" INTEGER NOT NULL,
PRIMARY KEY("timeslot_id","task_id"),
FOREIGN KEY("task_id") REFERENCES "Tasks"("id") ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY("timeslot_id") REFERENCES "TimeSlots"("id") ON UPDATE CASCADE ON DELETE CASCADE
);
DROP TABLE IF EXISTS "SubTasks";
CREATE TABLE IF NOT EXISTS "SubTasks" (
"id" INTEGER,
"task_id" INTEGER NOT NULL,
"name" TEXT NOT NULL,
"date_completed" INTEGER,
PRIMARY KEY("id"),
FOREIGN KEY("task_id") REFERENCES "Tasks"("id") ON UPDATE CASCADE ON DELETE CASCADE
);
DROP TABLE IF EXISTS "TimeSlots";
CREATE TABLE IF NOT EXISTS "TimeSlots" (
"id" INTEGER,
"schedule_id" INTEGER NOT NULL,
"start_time" INTEGER NOT NULL,
"end_time" INTEGER NOT NULL,
"name" TEXT NOT NULL,
"date_completed" INTEGER,
PRIMARY KEY("id"),
FOREIGN KEY("schedule_id") REFERENCES "Schedules"("id") ON UPDATE CASCADE ON DELETE CASCADE
);
DROP TABLE IF EXISTS "Version";
CREATE TABLE IF NOT EXISTS "Version" (
"major" INTEGER NOT NULL,
"minor" INTEGER NOT NULL,
"patch" INTEGER NOT NULL
);
INSERT INTO "Schedules" VALUES (1,'default');
INSERT INTO "Version" VALUES (0,1,0);
COMMIT;
|
