A task tracker.

task_tracker.sql

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "task" (
         "id" INTEGER NOT NULL,
         "name" TEXT NOT NULL ON CONFLICT FAIL,
         "setting_id" INTEGER,
        PRIMARY KEY("id"),
        CONSTRAINT "setting_id" FOREIGN KEY ("setting_id") REFERENCES "task_setting" ("id")
);
INSERT INTO "task" VALUES(1,'Running',1);
INSERT INTO "task" VALUES(2,'JS Blocker',2);
INSERT INTO "task" VALUES(3,'Creating Task Tracker',3);
CREATE TABLE "task_setting" (
         "id" INTEGER NOT NULL ON CONFLICT FAIL,
         "maximum_duration" INTEGER DEFAULT 0,
         "countdown_mode" INTEGER DEFAULT 0,
        PRIMARY KEY("id")
);
INSERT INTO "task_setting" VALUES(1,300,0);
INSERT INTO "task_setting" VALUES(2,0,0);
INSERT INTO "task_setting" VALUES(3,1,0);
CREATE TABLE "task_start_date_lookup" (
         "id" INTEGER NOT NULL,
         "task_timer_id" INTEGER NOT NULL,
         "year" INTEGER NOT NULL DEFAULT 1990,
         "month" INTEGER NOT NULL DEFAULT 7,
         "day" INTEGER NOT NULL DEFAULT 16,
        PRIMARY KEY("id"),
        CONSTRAINT "task_timer_id" FOREIGN KEY ("task_timer_id") REFERENCES "task_timer" ("id") ON DELETE CASCADE
);
INSERT INTO "task_start_date_lookup" VALUES(1,1,2016,3,12);
INSERT INTO "task_start_date_lookup" VALUES(2,2,2016,3,12);
INSERT INTO "task_start_date_lookup" VALUES(3,3,2009,11,9);
INSERT INTO "task_start_date_lookup" VALUES(4,4,2016,3,12);
INSERT INTO "task_start_date_lookup" VALUES(5,5,2016,3,12);
INSERT INTO "task_start_date_lookup" VALUES(6,6,1970,1,1);
INSERT INTO "task_start_date_lookup" VALUES(7,7,1970,1,1);
CREATE TABLE "task_timer" (
         "id" INTEGER NOT NULL,
         "task_id" INTEGER NOT NULL,
         "start_time" INTEGER NOT NULL,
         "end_time" INTEGER,
         "duration" INTEGER DEFAULT 0,
         "overtime" INTEGER NOT NULL DEFAULT 0,
        PRIMARY KEY("id"),
        CONSTRAINT "task_id" FOREIGN KEY ("task_id") REFERENCES "task" ("id") ON DELETE CASCADE,
        CONSTRAINT "Cannot end before starting" CHECK (end_time > start_time),
        CONSTRAINT "Start time too small" CHECK (start_time > 0)
);
INSERT INTO "task_timer" VALUES(1,1,1457770790,1457771091,301,1);
INSERT INTO "task_timer" VALUES(2,1,1457770816,1457772503,1687,1387);
INSERT INTO "task_timer" VALUES(3,1,1257772502,1257773116,614,314);
INSERT INTO "task_timer" VALUES(4,2,1457775547,1457781248,5701,0);
INSERT INTO "task_timer" VALUES(5,2,1457776970,1457776986,16,0);
INSERT INTO "task_timer" VALUES(6,3,1,1515554042,1515554041,1515554040);
INSERT INTO "task_timer" VALUES(7,1,7999,848481,840482,840182);
CREATE UNIQUE INDEX "task_id" ON task ("id" ASC);
CREATE TRIGGER "Create settings"
 AFTER INSERT ON task
FOR EACH ROW
BEGIN
        INSERT INTO task_setting VALUES(NULL, 0, 0);
        UPDATE task SET setting_id = last_insert_rowid() WHERE task.id = new.id;
END;
CREATE TRIGGER "Delete settings"
 AFTER DELETE ON task
FOR EACH ROW
BEGIN
        DELETE FROM task_setting WHERE id = old.setting_id;
END;
CREATE UNIQUE INDEX "setting_id" ON task_setting ("id" ASC);
CREATE UNIQUE INDEX "task_start_date_lookup_id" ON task_start_date_lookup ("id" ASC);
CREATE INDEX "task_timer_id" ON task_timer ("id" ASC);
CREATE TRIGGER "Add start_date lookup"
 AFTER INSERT ON task_timer
FOR EACH ROW
BEGIN
        INSERT INTO task_start_date_lookup VALUES(
                NULL,
                new.id,
                STRFTIME('%Y', new.start_time, 'unixepoch', 'utc'),
                STRFTIME('%m', new.start_time, 'unixepoch', 'utc'),
                STRFTIME('%d', new.start_time, 'unixepoch', 'utc')
        );
END;
CREATE TRIGGER "Modify start_date lookup"
 AFTER UPDATE OF start_time ON task_timer
FOR EACH ROW
BEGIN
        UPDATE task_start_date_lookup SET
                year = STRFTIME('%Y', new.start_time, 'unixepoch', 'utc'),
                month = STRFTIME('%m', new.start_time, 'unixepoch', 'utc'),
                day = STRFTIME('%d', new.start_time, 'unixepoch', 'utc')
        WHERE task_timer_id = new.id;
END;
CREATE TRIGGER "Calculate duration - insert"
 AFTER INSERT ON task_timer
FOR EACH ROW
BEGIN
        UPDATE task_timer SET duration = (new.end_time - new.start_time) WHERE id = new.id;
        UPDATE task_timer SET overtime = (
                SELECT
                        COALESCE(
                                (
                                        SELECT (("task_timer"."end_time" - "task_timer"."start_time") - "task_setting"."maximum_duration")
                                        WHERE ("task_timer"."end_time" - "task_timer"."start_time") > "task_setting"."maximum_duration"
                                        AND "task_setting"."maximum_duration" > 0
                                )
                        , 0)
                FROM "task_timer"
                JOIN "task"
                ON "task_timer"."task_id" = "task"."id"
                JOIN "task_setting"
                ON "task"."setting_id" = "task_setting"."id"
                WHERE "task_timer"."id" = new.id
        );
END;
CREATE TRIGGER "Calculate duration - update"
 AFTER UPDATE OF start_time,end_time,duration,overtime ON task_timer
FOR EACH ROW
BEGIN
        UPDATE task_timer SET duration = (new.end_time - new.start_time) WHERE id = new.id;
        UPDATE task_timer SET overtime = (
                SELECT
                        COALESCE(
                                (
                                        SELECT (("task_timer"."end_time" - "task_timer"."start_time") - "task_setting"."maximum_duration")
                                        WHERE ("task_timer"."end_time" - "task_timer"."start_time") > "task_setting"."maximum_duration"
                                        AND "task_setting"."maximum_duration" > 0
                                )
                        , 0)
                FROM "task_timer"
              sk"
                ON "task_timer"."task_id" = "task"."id"
                JOIN "task_setting"
                ON "task"."setting_id" = "task_setting"."id"
                WHERE "task_timer"."id" = new.id
        )
        WHERE "task_timer"."id" = new.id;
END;
CREATE VIEW "task_list" AS SELECT
"task_timer"."id" AS task_timer_id,
"task"."id" AS task_id,
"task"."name" AS task_name,
"task_timer"."start_time",
"task_timer"."end_time",
"task_timer"."duration",
"task_timer"."overtime",
"task_start_date_lookup"."year",
"task_start_date_lookup"."month",
"task_start_date_lookup"."day"
FROM
"task"
JOIN "task_timer"
ON "task"."id" = "task_timer"."task_id" 
JOIN "task_setting"
ON "task"."setting_id" = "task_setting"."id" 
JOIN "task_start_date_lookup"
ON "task_timer"."id" = "task_start_date_lookup"."task_timer_id" 
ORDER BY "task_timer"."id" ASC;
COMMIT;