Debian Codegraph @

resources/fts_sqlite.sql

raw
1 CREATE TABLE fts_db.dirtied_formats ( id INTEGER PRIMARY KEY,
2 book INTEGER NOT NULL,
3 format TEXT NOT NULL COLLATE NOCASE,
4 in_progress INTEGER NOT NULL DEFAULT FALSE,
5 UNIQUE(book, format)
6 );
7
8 CREATE TABLE fts_db.books_text ( id INTEGER PRIMARY KEY,
9 book INTEGER NOT NULL,
10 timestamp REAL NOT NULL,
11 format TEXT NOT NULL COLLATE NOCASE,
12 format_hash TEXT NOT NULL COLLATE NOCASE,
13 format_size INTEGER NOT NULL DEFAULT 0,
14 searchable_text TEXT NOT NULL DEFAULT '',
15 text_size INTEGER NOT NULL DEFAULT 0,
16 text_hash TEXT NOT NULL COLLATE NOCASE DEFAULT '',
17 err_msg TEXT DEFAULT '',
18 UNIQUE(book, format)
19 );
20
21
22 CREATE VIRTUAL TABLE fts_db.books_fts USING fts5(searchable_text, content = 'books_text', content_rowid = 'id', tokenize = 'calibre remove_diacritics 2');
23 CREATE VIRTUAL TABLE fts_db.books_fts_stemmed USING fts5(searchable_text, content = 'books_text', content_rowid = 'id', tokenize = 'porter calibre remove_diacritics 2');
24
25 CREATE TRIGGER fts_db.books_fts_insert_trg AFTER INSERT ON fts_db.books_text
26 BEGIN
27 INSERT INTO books_fts(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
28 INSERT INTO books_fts_stemmed(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
29 DELETE FROM dirtied_formats WHERE book=NEW.book AND format=NEW.format;
30 END;
31
32 CREATE TRIGGER fts_db.books_fts_delete_trg AFTER DELETE ON fts_db.books_text
33 BEGIN
34 INSERT INTO books_fts(books_fts, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
35 INSERT INTO books_fts_stemmed(books_fts_stemmed, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
36 END;
37
38 CREATE TRIGGER fts_db.books_fts_update_trg AFTER UPDATE ON fts_db.books_text
39 BEGIN
40 INSERT INTO books_fts(books_fts, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
41 INSERT INTO books_fts(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
42 INSERT INTO books_fts_stemmed(books_fts_stemmed, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
43 INSERT INTO books_fts_stemmed(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
44 DELETE FROM dirtied_formats WHERE book=NEW.book AND format=NEW.format;
45 END;
46
47 PRAGMA fts_db.user_version=1;