| 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; |