aboutsummaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
Diffstat
-rw-r--r--org/in_abstraction_artefacts.org43
-rw-r--r--org/out_ocda_sqlite_db.org143
-rw-r--r--src/sisudoc/ocda/abstraction/db_in.d43
-rw-r--r--src/sisudoc/outputs/io_out/sqlite_ocda_db.d143
4 files changed, 292 insertions, 80 deletions
diff --git a/org/in_abstraction_artefacts.org b/org/in_abstraction_artefacts.org
index 9da6d32..c2b4c7a 100644
--- a/org/in_abstraction_artefacts.org
+++ b/org/in_abstraction_artefacts.org
@@ -659,16 +659,41 @@ template spineAbstractionDbRead() {
default: return "";
}
}
- @trusted SSPdocument dbReadFile(string db_file) {
+ @trusted SSPdocument dbReadFile(string db_file, string _lang = "") {
SSPdocument doc;
if (!db_file.exists) {
writeln("ERROR: no such file: ", db_file);
return doc;
}
auto db = Database(db_file, SQLITE_OPEN_READONLY);
+ /+ ↓ which document in the file. A file holds one per language; with
+ no language named, take the only one, and say so if there is a
+ choice to be made rather than picking for the caller.
+ +/
+ long _doc_id = -1;
+ {
+ string[] _langs;
+ foreach (row; db.execute("SELECT id, lang FROM documents ORDER BY id")) {
+ string _l = row["lang"].as!string;
+ _langs ~= _l;
+ if (_lang.length > 0 && _l == _lang) { _doc_id = row["id"].as!long; }
+ else if (_lang.length == 0 && _doc_id < 0) { _doc_id = row["id"].as!long; }
+ }
+ if (_doc_id < 0) {
+ writeln("ERROR: ", db_file, " holds no document in '", _lang,
+ "'; it has: ", _langs.join(" "));
+ return doc;
+ }
+ if (_lang.length == 0 && _langs.length > 1) {
+ writeln("ERROR: ", db_file, " holds ", _langs.length,
+ " languages and none was named: ", _langs.join(" "));
+ return doc;
+ }
+ }
/+ ↓ the four header blocks, from the metadata table. the key prefix
says which block a row belongs to, and insertion order is kept +/
- foreach (row; db.execute("SELECT key, value FROM metadata ORDER BY rowid")) {
+ foreach (row; db.execute("SELECT key, value FROM metadata WHERE doc_id = " ~ _doc_id.to!string
+ ~ " OR doc_id IS NULL ORDER BY rowid")) {
string _k = row["key"].as!string;
string _v = row["value"].as!string;
if (_k.startsWith("make.")) {
@@ -717,7 +742,7 @@ template spineAbstractionDbRead() {
string[][long] _subtoc_by_id;
foreach (r; db.execute(
"SELECT object_id, name, bytes, sha256, width, height, missing"
- ~ " FROM object_images ORDER BY object_id, seq")
+ ~ " FROM object_images WHERE object_id IN (SELECT id FROM objects WHERE doc_id = " ~ _doc_id.to!string ~ ") ORDER BY object_id, seq")
) {
ST_file_name_hash_size_ _img;
_img.fileName = r["name"].as!string;
@@ -729,18 +754,19 @@ template spineAbstractionDbRead() {
_images_by_id[r["object_id"].as!long] ~= _img;
}
foreach (r; db.execute(
- "SELECT object_id, url FROM object_links ORDER BY object_id, seq")
+ "SELECT object_id, url FROM object_links WHERE object_id IN (SELECT id FROM objects WHERE doc_id = " ~ _doc_id.to!string ~ ") ORDER BY object_id, seq")
) { _links_by_id[r["object_id"].as!long] ~= r["url"].as!string; }
foreach (r; db.execute(
- "SELECT object_id, anchor FROM object_anchors ORDER BY object_id, seq")
+ "SELECT object_id, anchor FROM object_anchors WHERE object_id IN (SELECT id FROM objects WHERE doc_id = " ~ _doc_id.to!string ~ ") ORDER BY object_id, seq")
) { _anchors_by_id[r["object_id"].as!long] ~= r["anchor"].as!string; }
foreach (r; db.execute(
- "SELECT object_id, entry FROM object_subtoc ORDER BY object_id, seq")
+ "SELECT object_id, entry FROM object_subtoc WHERE object_id IN (SELECT id FROM objects WHERE doc_id = " ~ _doc_id.to!string ~ ") ORDER BY object_id, seq")
) { _subtoc_by_id[r["object_id"].as!long] ~= r["entry"].as!string; }
/+ ↓ the objects, section by section, in the order they were written +/
string[] _sections;
foreach (row; db.execute(
- "SELECT section FROM objects GROUP BY section ORDER BY MIN(id)")
+ "SELECT section FROM objects WHERE doc_id = " ~ _doc_id.to!string
+ ~ " GROUP BY section ORDER BY MIN(id)")
) {
_sections ~= row["section"].as!string;
}
@@ -758,7 +784,8 @@ template spineAbstractionDbRead() {
+/
int[string] _col;
foreach (row; db.execute(
- "SELECT * FROM objects WHERE section = '" ~ section ~ "' ORDER BY seq")
+ "SELECT * FROM objects WHERE doc_id = " ~ _doc_id.to!string
+ ~ " AND section = '" ~ section ~ "' ORDER BY seq")
) {
if (_col.length == 0) {
foreach (_i; 0 .. row.length) { _col[row.columnName(_i)] = _i.to!int; }
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");
}
diff --git a/src/sisudoc/ocda/abstraction/db_in.d b/src/sisudoc/ocda/abstraction/db_in.d
index 6e90820..a8a216c 100644
--- a/src/sisudoc/ocda/abstraction/db_in.d
+++ b/src/sisudoc/ocda/abstraction/db_in.d
@@ -129,16 +129,41 @@ template spineAbstractionDbRead() {
default: return "";
}
}
- @trusted SSPdocument dbReadFile(string db_file) {
+ @trusted SSPdocument dbReadFile(string db_file, string _lang = "") {
SSPdocument doc;
if (!db_file.exists) {
writeln("ERROR: no such file: ", db_file);
return doc;
}
auto db = Database(db_file, SQLITE_OPEN_READONLY);
+ /+ ↓ which document in the file. A file holds one per language; with
+ no language named, take the only one, and say so if there is a
+ choice to be made rather than picking for the caller.
+ +/
+ long _doc_id = -1;
+ {
+ string[] _langs;
+ foreach (row; db.execute("SELECT id, lang FROM documents ORDER BY id")) {
+ string _l = row["lang"].as!string;
+ _langs ~= _l;
+ if (_lang.length > 0 && _l == _lang) { _doc_id = row["id"].as!long; }
+ else if (_lang.length == 0 && _doc_id < 0) { _doc_id = row["id"].as!long; }
+ }
+ if (_doc_id < 0) {
+ writeln("ERROR: ", db_file, " holds no document in '", _lang,
+ "'; it has: ", _langs.join(" "));
+ return doc;
+ }
+ if (_lang.length == 0 && _langs.length > 1) {
+ writeln("ERROR: ", db_file, " holds ", _langs.length,
+ " languages and none was named: ", _langs.join(" "));
+ return doc;
+ }
+ }
/+ ↓ the four header blocks, from the metadata table. the key prefix
says which block a row belongs to, and insertion order is kept +/
- foreach (row; db.execute("SELECT key, value FROM metadata ORDER BY rowid")) {
+ foreach (row; db.execute("SELECT key, value FROM metadata WHERE doc_id = " ~ _doc_id.to!string
+ ~ " OR doc_id IS NULL ORDER BY rowid")) {
string _k = row["key"].as!string;
string _v = row["value"].as!string;
if (_k.startsWith("make.")) {
@@ -187,7 +212,7 @@ template spineAbstractionDbRead() {
string[][long] _subtoc_by_id;
foreach (r; db.execute(
"SELECT object_id, name, bytes, sha256, width, height, missing"
- ~ " FROM object_images ORDER BY object_id, seq")
+ ~ " FROM object_images WHERE object_id IN (SELECT id FROM objects WHERE doc_id = " ~ _doc_id.to!string ~ ") ORDER BY object_id, seq")
) {
ST_file_name_hash_size_ _img;
_img.fileName = r["name"].as!string;
@@ -199,18 +224,19 @@ template spineAbstractionDbRead() {
_images_by_id[r["object_id"].as!long] ~= _img;
}
foreach (r; db.execute(
- "SELECT object_id, url FROM object_links ORDER BY object_id, seq")
+ "SELECT object_id, url FROM object_links WHERE object_id IN (SELECT id FROM objects WHERE doc_id = " ~ _doc_id.to!string ~ ") ORDER BY object_id, seq")
) { _links_by_id[r["object_id"].as!long] ~= r["url"].as!string; }
foreach (r; db.execute(
- "SELECT object_id, anchor FROM object_anchors ORDER BY object_id, seq")
+ "SELECT object_id, anchor FROM object_anchors WHERE object_id IN (SELECT id FROM objects WHERE doc_id = " ~ _doc_id.to!string ~ ") ORDER BY object_id, seq")
) { _anchors_by_id[r["object_id"].as!long] ~= r["anchor"].as!string; }
foreach (r; db.execute(
- "SELECT object_id, entry FROM object_subtoc ORDER BY object_id, seq")
+ "SELECT object_id, entry FROM object_subtoc WHERE object_id IN (SELECT id FROM objects WHERE doc_id = " ~ _doc_id.to!string ~ ") ORDER BY object_id, seq")
) { _subtoc_by_id[r["object_id"].as!long] ~= r["entry"].as!string; }
/+ ↓ the objects, section by section, in the order they were written +/
string[] _sections;
foreach (row; db.execute(
- "SELECT section FROM objects GROUP BY section ORDER BY MIN(id)")
+ "SELECT section FROM objects WHERE doc_id = " ~ _doc_id.to!string
+ ~ " GROUP BY section ORDER BY MIN(id)")
) {
_sections ~= row["section"].as!string;
}
@@ -228,7 +254,8 @@ template spineAbstractionDbRead() {
+/
int[string] _col;
foreach (row; db.execute(
- "SELECT * FROM objects WHERE section = '" ~ section ~ "' ORDER BY seq")
+ "SELECT * FROM objects WHERE doc_id = " ~ _doc_id.to!string
+ ~ " AND section = '" ~ section ~ "' ORDER BY seq")
) {
if (_col.length == 0) {
foreach (_i; 0 .. row.length) { _col[row.columnName(_i)] = _i.to!int; }
diff --git a/src/sisudoc/outputs/io_out/sqlite_ocda_db.d b/src/sisudoc/outputs/io_out/sqlite_ocda_db.d
index 79be05e..d94fa26 100644
--- a/src/sisudoc/outputs/io_out/sqlite_ocda_db.d
+++ b/src/sisudoc/outputs/io_out/sqlite_ocda_db.d
@@ -116,9 +116,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
@@ -126,8 +146,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,
@@ -185,14 +206,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
@@ -200,7 +221,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,
@@ -213,7 +234,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,
@@ -224,43 +245,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;
");
@@ -273,18 +303,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]); }
@@ -295,8 +358,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
@@ -315,7 +378,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,"
@@ -330,7 +393,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,"
@@ -375,6 +438,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);
@@ -648,8 +712,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");
}
generated by cgit v1.2.3 (git 2.25.1) at 2026-10-06 22:43:59 +0000