Skip to main content

max / goingson

3.1 KB · 81 lines History Blame Raw
1 -- Tasks get a short title alongside the detailed description.
2 --
3 -- Until now `description` was the only text a task had, and it did three jobs:
4 -- the row-view label (CSS-truncated mid-sentence), the detail-drawer heading,
5 -- and the markdown body of that same drawer -- so the drawer rendered the same
6 -- string twice. Callers worked around the missing field by packing a synthetic
7 -- title and the body into one column separated by a blank line, which is what
8 -- the dellm import did.
9 --
10 -- `title` is the short label; `description` is the detail body and may be
11 -- empty. Nothing concatenates them.
12 --
13 -- Backfill splits each existing row on its first line:
14 -- * first line <= 80 chars -> that is the title, the remainder is the body
15 -- * longer -> hard-truncate to 77 + "..." for the title and keep the whole
16 -- original text as the body, so nothing is lost to truncation.
17 -- The Rust helper (`Task::split_description`) applies the same rule with word
18 -- boundary awareness for text entered from here on.
19
20 ALTER TABLE tasks ADD COLUMN title TEXT NOT NULL DEFAULT '';
21
22 UPDATE tasks
23 SET title = CASE
24 WHEN length(s.first_line) <= 80 THEN s.first_line
25 ELSE substr(s.first_line, 1, 77) || '...'
26 END,
27 description = CASE
28 WHEN length(s.first_line) > 80 THEN tasks.description
29 WHEN s.nl > 0 THEN trim(substr(tasks.description, s.nl + 1), char(10) || ' ')
30 ELSE ''
31 END
32 FROM (
33 SELECT id,
34 instr(description, char(10)) AS nl,
35 CASE
36 WHEN instr(description, char(10)) > 0
37 THEN substr(description, 1, instr(description, char(10)) - 1)
38 ELSE description
39 END AS first_line
40 FROM tasks
41 ) AS s
42 WHERE tasks.id = s.id AND tasks.title = '';
43
44 -- The full-text index covered `description` only. Now that the title carries
45 -- the text a search is most likely to match, it has to be indexed too, so the
46 -- external-content table and its triggers are rebuilt around both columns.
47
48 DROP TRIGGER IF EXISTS tasks_ai;
49 DROP TRIGGER IF EXISTS tasks_au;
50 DROP TRIGGER IF EXISTS tasks_ad;
51 DROP TABLE IF EXISTS tasks_fts;
52
53 CREATE VIRTUAL TABLE tasks_fts USING fts5(
54 id UNINDEXED,
55 user_id UNINDEXED,
56 title,
57 description,
58 tags,
59 content='tasks',
60 content_rowid='rowid'
61 );
62
63 CREATE TRIGGER tasks_ai AFTER INSERT ON tasks BEGIN
64 INSERT INTO tasks_fts(rowid, id, user_id, title, description, tags)
65 VALUES (NEW.rowid, NEW.id, NEW.user_id, NEW.title, NEW.description, NEW.tags);
66 END;
67
68 CREATE TRIGGER tasks_ad AFTER DELETE ON tasks BEGIN
69 INSERT INTO tasks_fts(tasks_fts, rowid, id, user_id, title, description, tags)
70 VALUES ('delete', OLD.rowid, OLD.id, OLD.user_id, OLD.title, OLD.description, OLD.tags);
71 END;
72
73 CREATE TRIGGER tasks_au AFTER UPDATE ON tasks BEGIN
74 INSERT INTO tasks_fts(tasks_fts, rowid, id, user_id, title, description, tags)
75 VALUES ('delete', OLD.rowid, OLD.id, OLD.user_id, OLD.title, OLD.description, OLD.tags);
76 INSERT INTO tasks_fts(rowid, id, user_id, title, description, tags)
77 VALUES (NEW.rowid, NEW.id, NEW.user_id, NEW.title, NEW.description, NEW.tags);
78 END;
79
80 INSERT INTO tasks_fts(tasks_fts) VALUES('rebuild');
81