19 lines
1.1 KiB
SQL
19 lines
1.1 KiB
SQL
CREATE TABLE task_comment_cursors (
|
|
seq INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
business TEXT NOT NULL,
|
|
task_id INTEGER NOT NULL CHECK (task_id > 0),
|
|
last_comment INTEGER NOT NULL CHECK (last_comment > 0)
|
|
) STRICT;
|
|
CREATE INDEX task_comment_cursors_latest ON task_comment_cursors (business, task_id, seq);
|
|
CREATE TRIGGER task_comment_cursors_no_update BEFORE UPDATE ON task_comment_cursors
|
|
BEGIN SELECT RAISE(ABORT, 'task_comment_cursors is append-only'); END;
|
|
CREATE TRIGGER task_comment_cursors_no_delete BEFORE DELETE ON task_comment_cursors
|
|
BEGIN SELECT RAISE(ABORT, 'task_comment_cursors is append-only'); END;
|
|
CREATE TRIGGER task_comment_cursors_no_replace BEFORE INSERT ON task_comment_cursors
|
|
WHEN EXISTS (SELECT 1 FROM task_comment_cursors WHERE seq = NEW.seq)
|
|
BEGIN SELECT RAISE(ABORT, 'task_comment_cursors is append-only'); END;
|
|
CREATE TRIGGER task_comment_cursors_forward BEFORE INSERT ON task_comment_cursors
|
|
WHEN NEW.last_comment <= COALESCE((SELECT last_comment FROM task_comment_cursors
|
|
WHERE business = NEW.business AND task_id = NEW.task_id ORDER BY seq DESC LIMIT 1), 0)
|
|
BEGIN SELECT RAISE(ABORT, 'comment cursor must advance'); END;
|