diff options
| -rw-r--r-- | org/out_src_abstraction_sqlite_db.org | 21 | ||||
| -rw-r--r-- | src/sisudoc/outputs/io_out/create_abstraction_db.d | 21 |
2 files changed, 42 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"); } } diff --git a/src/sisudoc/outputs/io_out/create_abstraction_db.d b/src/sisudoc/outputs/io_out/create_abstraction_db.d index a0cbd57..539dd55 100644 --- a/src/sisudoc/outputs/io_out/create_abstraction_db.d +++ b/src/sisudoc/outputs/io_out/create_abstraction_db.d @@ -229,6 +229,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 +/ @@ -635,6 +652,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"); } } |