Just to have a way to see my progress at Udemy, I created the following
table and view:
CREATE TABLE rawSummaryUdemy (
date TEXT NOT NULL DEFAULT CURRENT_DATE,
total INTEGER NOT NULL,
completed INTEGER NOT NULL,
CONSTRAINT formatDate CHECK(date = date(strftime('%s',
date), 'unixepoch')),
CONSTRAINT notInFuture CHECK(date <= date()),
CONSTRAINT totalIsInt CHECK(TYPEOF(total) = 'integer'),
CONSTRAINT completedIsInt CHECK(TYPEOF(completed) = 'integer'),
CONSTRAINT totalGEZero CHECK(total >= 0),
CONSTRAINT completedGEZero CHECK(completed >= 0),
CONSTRAINT completedLETotal CHECK(completed <= total),
PRIMARY KEY(date)
);
CREATE VIEW summaryUdemy AS
SELECT *
, total - completed AS toComplete
FROM rawSummaryUdemy
;
If this can be done better: let me know.
Normally speaking total and completed should never decrease. It is not
really important, but just as an exercise: is it possible to add
constraints so that you cannot enter a total, or a completed that is lower
as the previous one?
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
