ocda db: full text index and three views

An FTS5 virtual table over the object text, external content
(content='objects', content_rowid='id') so the text is not stored
twice, built from the rows once they are written. This is per
document, for exploring one abstraction; the collection wide
search database is a separate thing and is unaffected.

  SELECT o.ocn, o.text FROM objects_fts f JOIN objects o ON o.id=f.rowid
    WHERE f.text MATCH 'mercatoria';

War and Peace, 12,135 objects: 100 full text queries in 0.23s
including 100 process starts, 400 hits for Napoleon.

Three views name the shapes worth asking for, so that datasette or
anyone opening the file finds them without writing SQL:
  outline (headings with their levels and interval),
  citable (ocn > 0),
  document_files (what the file carries).

Over the document sample the index size is/adds about a quarter
that of the files.

(assisted by Claude-Code)

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");
   }
 }