diff options
Diffstat (limited to 'org/out_ocda_sqlite_db.org')
| -rw-r--r-- | org/out_ocda_sqlite_db.org | 143 |
1 files changed, 111 insertions, 32 deletions
diff --git a/org/out_ocda_sqlite_db.org b/org/out_ocda_sqlite_db.org index 6916fa0..1c8a3c7 100644 --- a/org/out_ocda_sqlite_db.org +++ b/org/out_ocda_sqlite_db.org @@ -98,9 +98,29 @@ template spineAbstractionDb() { db.run("PRAGMA synchronous=OFF"); db.run(" - CREATE TABLE metadata ( - key TEXT PRIMARY KEY, - value TEXT NOT NULL + -- documents: a row per language of the document this database holds. + -- One today, since a database is written per language; the table is + -- what lets one hold a document's whole set, which is what it is for. + -- Everything a reader needs to tell one language's rows from another's + -- keys on documents.id, called doc_id wherever it is referred to. + CREATE TABLE IF NOT EXISTS documents ( + id INTEGER PRIMARY KEY, + lang TEXT NOT NULL, + source_filename TEXT, + ssp_digest TEXT, + UNIQUE(lang) + ); + -- metadata: the document's own header, a row per property. + -- Keyed on (doc_id, key) and not on key alone: every language has a + -- title and a creator, and a key-only primary key refuses the second + -- one. schema.name and schema.version describe the file rather than a + -- document in it, so they are written with doc_id null and are the + -- only rows that are. + CREATE TABLE IF NOT EXISTS metadata ( + doc_id INTEGER REFERENCES documents(id), + key TEXT NOT NULL, + value TEXT NOT NULL, + UNIQUE(doc_id, key) ); -- objects: one row per abstraction object, in document order. -- the fixed width arrays (ancestors, dom status, children, heading @@ -108,8 +128,9 @@ template spineAbstractionDb() { -- json_extract, json_each and json_array_length can reach inside them; -- the open ended lists (images, links, anchor tags, subtoc) are rows -- in their own tables, not columns - CREATE TABLE objects ( + CREATE TABLE IF NOT EXISTS objects ( id INTEGER PRIMARY KEY, + doc_id INTEGER NOT NULL REFERENCES documents(id), section TEXT NOT NULL, seq INTEGER NOT NULL, ocn INTEGER DEFAULT 0, @@ -167,14 +188,14 @@ template spineAbstractionDb() { table_header INTEGER, code_linenumbers INTEGER DEFAULT 0, text TEXT, - UNIQUE(section, seq) + UNIQUE(doc_id, section, seq) ); - CREATE INDEX idx_objects_section ON objects(section); - CREATE INDEX idx_objects_ocn ON objects(ocn); - CREATE INDEX idx_objects_parent ON objects(parent_ocn); - CREATE INDEX idx_objects_is_a ON objects(is_a); - CREATE INDEX idx_objects_heading ON objects(heading_level) + CREATE INDEX IF NOT EXISTS idx_objects_section ON objects(doc_id, section); + CREATE INDEX IF NOT EXISTS idx_objects_ocn ON objects(ocn); + CREATE INDEX IF NOT EXISTS idx_objects_parent ON objects(parent_ocn); + CREATE INDEX IF NOT EXISTS idx_objects_is_a ON objects(is_a); + CREATE INDEX IF NOT EXISTS idx_objects_heading ON objects(heading_level) WHERE heading_level IS NOT NULL; -- files: what this database carries with it, so that a reader needs @@ -182,7 +203,7 @@ template spineAbstractionDb() { -- output writers must have in order to produce a document. bytes are -- stored exactly as read, so sha256 matches the digest the abstraction -- was built with - CREATE TABLE files ( + CREATE TABLE IF NOT EXISTS files ( id INTEGER PRIMARY KEY, role TEXT NOT NULL, name TEXT NOT NULL, @@ -195,7 +216,7 @@ template spineAbstractionDb() { ); -- the open ended per object lists, one row each, in order - CREATE TABLE object_images ( + CREATE TABLE IF NOT EXISTS object_images ( object_id INTEGER NOT NULL REFERENCES objects(id), seq INTEGER NOT NULL, name TEXT NOT NULL, @@ -206,43 +227,52 @@ template spineAbstractionDb() { missing INTEGER DEFAULT 0, PRIMARY KEY(object_id, seq) ); - CREATE TABLE object_links ( + CREATE TABLE IF NOT EXISTS object_links ( object_id INTEGER NOT NULL REFERENCES objects(id), seq INTEGER NOT NULL, url TEXT NOT NULL, PRIMARY KEY(object_id, seq) ); - CREATE TABLE object_anchors ( + CREATE TABLE IF NOT EXISTS object_anchors ( object_id INTEGER NOT NULL REFERENCES objects(id), seq INTEGER NOT NULL, anchor TEXT NOT NULL, PRIMARY KEY(object_id, seq) ); - CREATE TABLE object_subtoc ( + CREATE TABLE IF NOT EXISTS object_subtoc ( object_id INTEGER NOT NULL REFERENCES objects(id), seq INTEGER NOT NULL, entry TEXT NOT NULL, PRIMARY KEY(object_id, seq) ); - CREATE INDEX idx_files_role ON files(role); - CREATE INDEX idx_object_images_name ON object_images(name); + CREATE INDEX IF NOT EXISTS idx_files_role ON files(role); + CREATE INDEX IF NOT EXISTS 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( + CREATE VIRTUAL TABLE IF NOT EXISTS 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 + -- both name the language, and order by it first. A view over a file + -- that may hold several languages and does not say which would read + -- as one document and be several: the language is in the projection + -- so that what comes back says what it is, and a reader wanting one + -- language adds WHERE lang = '..' rather than knowing to. + CREATE VIEW IF NOT EXISTS outline AS + SELECT d.lang AS lang, o.doc_id, o.section, o.seq, o.ocn, + o.heading_level, o.heading_lev_collapsed, + o.last_descendant_ocn, o.identifier, o.text + FROM objects o JOIN documents d ON d.id = o.doc_id + WHERE o.is_a = 'heading' ORDER BY o.doc_id, o.id; + CREATE VIEW IF NOT EXISTS citable AS + SELECT d.lang AS lang, o.* + FROM objects o JOIN documents d ON d.id = o.doc_id + WHERE o.ocn > 0 ORDER BY o.doc_id, o.ocn; + CREATE VIEW IF NOT EXISTS document_files AS SELECT role, name, bytes, sha256, width, height FROM files ORDER BY role, name; "); @@ -255,18 +285,51 @@ template spineAbstractionDb() { +/ db.run("BEGIN TRANSACTION"); + /+ ↓ the document this call is writing: one row in documents, and its id + is the doc_id every row of this language then carries. Written with + OR REPLACE on the language so that re-writing a language replaces + its row rather than refusing it. + +/ + long _doc_id; + { + auto doc_stmt = db.prepare( + "INSERT OR REPLACE INTO documents (lang, source_filename, ssp_digest)" + ~ " VALUES (:lang, :fn, :dig)" + ); + doc_stmt.bind(":lang", doc_matters.src.language); + doc_stmt.bind(":fn", ssp_doc.source); + doc_stmt.bind(":dig", ssp_doc.ssp_digest); + doc_stmt.execute(); + doc_stmt.finalize(); + _doc_id = db.lastInsertRowid; + } + auto meta_stmt = db.prepare( - "INSERT INTO metadata (key, value) VALUES (:key, :value)" + "INSERT INTO metadata (doc_id, key, value) VALUES (:doc_id, :key, :value)" ); void insertMeta(string key, string value) { if (value.length > 0) { + meta_stmt.bind(":doc_id", _doc_id); meta_stmt.bind(":key", key); meta_stmt.bind(":value", value); meta_stmt.execute(); meta_stmt.reset(); } } + /+ ↓ what the file is rather than what a document in it is, so these two + carry no doc_id and are the only rows that do not + +/ + void insertFileMeta(string key, string value) { + auto _s = db.prepare( + "INSERT OR REPLACE INTO metadata (doc_id, key, value)" + ~ " VALUES (NULL, :key, :value)" + ); + _s.bind(":key", key); + _s.bind(":value", value); + _s.execute(); + _s.finalize(); + } void insertBlock(string prefix, string[] keys_in_order, string[string] kv) { foreach (k; keys_in_order) { if (k in kv) { insertMeta(prefix ~ k, kv[k]); } @@ -277,8 +340,8 @@ template spineAbstractionDb() { version is the .ssp's own: the two serialisations are one format and move together +/ - insertMeta("schema.name", "sisu-abstraction-db"); - insertMeta("schema.version", ssp_format_version); + insertFileMeta("schema.name", "sisu-abstraction-db"); + insertFileMeta("schema.version", ssp_format_version); insertMeta("source.filename", ssp_doc.source); insertBlock("source.", ssp_doc.source_info_order, ssp_doc.source_info); /+ ↓ the digest of the .ssp this database was built from. a file cannot @@ -297,7 +360,7 @@ template spineAbstractionDb() { /+ ↓ populate objects +/ auto obj_stmt = db.prepare( "INSERT INTO objects (" - ~ "section, seq, ocn, is_a, is_of_part, is_of_section, is_of_type," + ~ "doc_id, section, seq, ocn, is_a, is_of_part, is_of_section, is_of_type," ~ "heading_level, heading_lev_collapsed, identifier," ~ "parent_ocn, parent_lev, last_descendant_ocn," ~ "children, ancestors, ancestors_collapsed," @@ -312,7 +375,7 @@ template spineAbstractionDb() { ~ "table_cols, table_widths, table_aligns, table_header," ~ "code_linenumbers, text" ~ ") VALUES (" - ~ ":section, :seq, :ocn, :is_a, :is_of_part, :is_of_section, :is_of_type," + ~ ":doc_id, :section, :seq, :ocn, :is_a, :is_of_part, :is_of_section, :is_of_type," ~ ":heading_level, :heading_lev_collapsed, :identifier," ~ ":parent_ocn, :parent_lev, :last_descendant_ocn," ~ ":children, :ancestors, :ancestors_collapsed," @@ -357,6 +420,7 @@ template spineAbstractionDb() { if (section_objs.length == 0) continue; foreach (seq, obj; section_objs) { + obj_stmt.bind(":doc_id", _doc_id); obj_stmt.bind(":section", section); obj_stmt.bind(":seq", cast(int) seq); obj_stmt.bind(":ocn", obj.metainfo.ocn); @@ -630,8 +694,23 @@ template spineAbstractionDb() { } /+ ↓ 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"); + /+ ↓ scoped to this document, not the whole table. + (objects_fts is external content over objects.id, so re-running this + for a second language would re-insert every row the first language + put in: duplicate rowids, which fts5 does not refuse and which give + duplicated and wrong results. Nothing in the test suite queries the + index, so this one would not have been caught.) + +/ + { + auto _fts = db.prepare( + "INSERT INTO objects_fts(rowid, text)" + ~ " SELECT id, text FROM objects" + ~ " WHERE doc_id = :doc_id AND text IS NOT NULL" + ); + _fts.bind(":doc_id", _doc_id); + _fts.execute(); + _fts.finalize(); + } db.run("COMMIT TRANSACTION"); } |
