diff options
Diffstat (limited to 'org/out_src_abstraction_sqlite_db.org')
| -rw-r--r-- | org/out_src_abstraction_sqlite_db.org | 21 |
1 files changed, 21 insertions, 0 deletions
diff --git a/org/out_src_abstraction_sqlite_db.org b/org/out_src_abstraction_sqlite_db.org index 5308f33..6fb5ffc 100644 --- a/org/out_src_abstraction_sqlite_db.org +++ b/org/out_src_abstraction_sqlite_db.org @@ -211,6 +211,23 @@ template spineAbstractionDb() { CREATE INDEX idx_files_role ON files(role); CREATE INDEX idx_object_images_name ON object_images(name); + + -- full text over the object text, external content so the text is not + -- stored twice. per document: this is for exploring one abstraction, + -- the collection wide search database is a different thing + CREATE VIRTUAL TABLE objects_fts USING fts5( + text, content='objects', content_rowid='id' + ); + + -- the shapes worth asking for, named + CREATE VIEW outline AS + SELECT section, seq, ocn, heading_level, heading_lev_collapsed, + last_descendant_ocn, identifier, text + FROM objects WHERE is_a = 'heading' ORDER BY id; + CREATE VIEW citable AS + SELECT * FROM objects WHERE ocn > 0 ORDER BY ocn; + CREATE VIEW document_files AS + SELECT role, name, bytes, sha256, width, height FROM files ORDER BY role, name; "); /+ ↓ populate metadata +/ @@ -617,6 +634,10 @@ template spineAbstractionDb() { file_stmt.finalize(); } + /+ ↓ build the full text index from the rows just written +/ + db.run("INSERT INTO objects_fts(rowid, text)" + ~ " SELECT id, text FROM objects WHERE text IS NOT NULL"); + db.run("COMMIT TRANSACTION"); } } |
