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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
|
CREATE TABLE "entries" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"datetime-string" TEXT NOT NULL, -- in utc
"datetime-local-string" TEXT NOT NULL, -- local
"state-name" TEXT NOT NULL,
"state-duration" INTEGER DEFAULT 0,
"elapsed" INTEGER DEFAULT 0
);
CREATE TABLE "aggregated-entries" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"date-string" TEXT NOT NULL, -- local
"state-name" TEXT NOT NULL,
"state-duration" INTEGER DEFAULT 0,
"elapsed" INTEGER DEFAULT 0
);
CREATE INDEX "entries-datetime-local-string" ON "entries" (
"datetime-local-string"
);
CREATE INDEX "aggregated-entries-date-string" ON "aggregated-entries" (
"date-string"
);
CREATE UNIQUE INDEX "aggregated-entries-date-string-state-name" ON "aggregated-entries" (
"date-string",
"state-name"
);
CREATE TRIGGER "entries-insert" AFTER INSERT ON "entries" FOR EACH ROW
BEGIN
UPDATE "aggregated-entries"
SET
"elapsed" = "elapsed" + NEW."elapsed",
"state-duration" = "state-duration" + NEW."state-duration"
WHERE
"date-string" = date(NEW."datetime-local-string") AND
"state-name" = NEW."state-name";
INSERT INTO "aggregated-entries" (
"date-string",
"state-name",
"state-duration",
"elapsed"
)
SELECT
date(NEW."datetime-local-string"),
NEW."state-name",
NEW."state-duration",
NEW."elapsed"
WHERE
changes() = 0;
END;
CREATE TRIGGER "entries-update" AFTER UPDATE ON "entries" FOR EACH ROW
BEGIN
UPDATE "aggregated-entries"
SET
"elapsed" = "elapsed" + NEW."elapsed",
"state-duration" = "state-duration" + NEW."state-duration"
WHERE
"date-string" = date(NEW."datetime-local-string") AND
"state-name" = NEW."state-name";
INSERT INTO "aggregated-entries" (
"date-string",
"state-name",
"state-duration",
"elapsed"
)
SELECT
date(NEW."datetime-local-string"),
NEW."state-name",
NEW."state-duration",
NEW."elapsed"
WHERE
changes() = 0;
UPDATE "aggregated-entries"
SET
"elapsed" = "elapsed" - OLD."elapsed",
"state-duration" = "state-duration" - OLD."state-duration"
WHERE
"date-string" = date(OLD."datetime-local-string") AND
"state-name" = OLD."state-name";
END;
CREATE TRIGGER "entries-delete" AFTER DELETE ON "entries" FOR EACH ROW
BEGIN
UPDATE "aggregated-entries"
SET
"elapsed" = "elapsed" - OLD."elapsed",
"state-duration" = "state-duration" - OLD."state-duration"
WHERE
"date-string" = date(OLD."datetime-local-string") AND
"state-name" = OLD."state-name";
END;
|
