Debian Codegraph @

resources/notes_sqlite.sql

raw
1 CREATE TABLE notes_db.notes ( id INTEGER PRIMARY KEY AUTOINCREMENT,
2 item INTEGER NOT NULL,
3 colname TEXT NOT NULL COLLATE NOCASE,
4 doc TEXT NOT NULL DEFAULT '',
5 searchable_text TEXT NOT NULL DEFAULT '',
6 ctime REAL DEFAULT (unixepoch('subsec')),
7 mtime REAL DEFAULT (unixepoch('subsec')),
8 UNIQUE(item, colname)
9 );
10
11 CREATE INDEX notes_db.notes_colname_idx ON notes (colname);
12
13 CREATE TABLE notes_db.resources (
14 hash TEXT NOT NULL PRIMARY KEY ON CONFLICT FAIL,
15 name TEXT NOT NULL UNIQUE ON CONFLICT FAIL
16 ) WITHOUT ROWID;
17
18 CREATE TABLE notes_db.notes_resources_link ( id INTEGER PRIMARY KEY,
19 note INTEGER NOT NULL,
20 resource TEXT NOT NULL,
21 FOREIGN KEY(note) REFERENCES notes(id),
22 FOREIGN KEY(resource) REFERENCES resources(hash),
23 UNIQUE(note, resource)
24 );
25
26 CREATE VIRTUAL TABLE notes_db.notes_fts USING fts5(searchable_text, content = 'notes', content_rowid = 'id', tokenize = 'calibre remove_diacritics 2');
27 CREATE VIRTUAL TABLE notes_db.notes_fts_stemmed USING fts5(searchable_text, content = 'notes', content_rowid = 'id', tokenize = 'porter calibre remove_diacritics 2');
28
29 CREATE TRIGGER notes_db.notes_fts_insert_trg AFTER INSERT ON notes_db.notes
30 BEGIN
31 INSERT INTO notes_fts(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
32 INSERT INTO notes_fts_stemmed(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
33 END;
34
35 CREATE TRIGGER notes_db.notes_db_notes_delete_trg BEFORE DELETE ON notes_db.notes
36 BEGIN
37 DELETE FROM notes_resources_link WHERE note=OLD.id;
38 INSERT INTO notes_fts(notes_fts, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
39 INSERT INTO notes_fts_stemmed(notes_fts_stemmed, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
40 END;
41
42 CREATE TRIGGER notes_db.notes_fts_update_trg AFTER UPDATE ON notes_db.notes
43 BEGIN
44 INSERT INTO notes_fts(notes_fts, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
45 INSERT INTO notes_fts(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
46 INSERT INTO notes_fts_stemmed(notes_fts_stemmed, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
47 INSERT INTO notes_fts_stemmed(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
48 UPDATE notes SET mtime=unixepoch('subsec') WHERE id = OLD.id;
49 END;
50
51 CREATE TRIGGER notes_db.notes_db_resources_delete_trg BEFORE DELETE ON notes_db.resources
52 BEGIN
53 DELETE FROM notes_resources_link WHERE resource=OLD.hash;
54 END;
55
56 PRAGMA notes_db.user_version=1;