sqlive/
triggered_inserts.rs1pub const SCHEMA: &str = "CREATE TABLE scans(id TEXT NOT NULL PRIMARY KEY, path TEXT NOT NULL, title TEXT NOT NULL);
12CREATE TABLE edits(id TEXT NOT NULL PRIMARY KEY, title TEXT);
13CREATE TABLE verdicts(id TEXT NOT NULL PRIMARY KEY, kind TEXT, position INTEGER);
14CREATE TABLE matches(id TEXT NOT NULL, position INTEGER NOT NULL, pressing TEXT, failure TEXT, PRIMARY KEY(id, position));
15CREATE TABLE origins(id TEXT NOT NULL PRIMARY KEY, created_at INTEGER);
16CREATE TABLE skipped(path TEXT NOT NULL PRIMARY KEY);
17CREATE TABLE listing(id TEXT NOT NULL PRIMARY KEY, tab TEXT NOT NULL, state TEXT, title TEXT NOT NULL, unit_path TEXT NOT NULL);
18CREATE VIEW placed AS
19 SELECT s.id, CASE WHEN o.id IS NOT NULL THEN 'done' WHEN k.path IS NOT NULL THEN 'skipped' ELSE 'pending' END AS tab,
20 CASE WHEN v.kind IS NULL THEN 'not_looked_up'
21 WHEN (SELECT count(DISTINCT pressing) FROM matches m WHERE m.id=s.id) <> 1 THEN 'several'
22 WHEN EXISTS(SELECT 1 FROM matches m WHERE m.id=s.id AND m.failure IS NOT NULL) THEN 'unread'
23 WHEN v.kind = 'found' THEN 'identified' ELSE 'needs_you' END AS state,
24 coalesce(e.title, s.title) AS title,
25 CASE WHEN instr(s.path,'/')>0 THEN substr(s.path,1,instr(s.path,'/')-1) ELSE s.path END AS unit_path
26 FROM scans s LEFT JOIN edits e ON e.id=s.id LEFT JOIN verdicts v ON v.id=s.id
27 LEFT JOIN origins o ON o.id=s.id LEFT JOIN skipped k ON k.path=s.path;
28CREATE TRIGGER scans_listed AFTER INSERT ON scans BEGIN
29 INSERT OR REPLACE INTO listing SELECT * FROM placed WHERE id=NEW.id; END;
30CREATE TRIGGER scans_relisted AFTER UPDATE ON scans BEGIN
31 INSERT OR REPLACE INTO listing SELECT * FROM placed WHERE id=NEW.id; END;
32CREATE TRIGGER scans_unlisted AFTER DELETE ON scans BEGIN DELETE FROM listing WHERE id=OLD.id; END;";