//// Loading a `.ocda.db` into the model. //// //// The point of this module is that it returns *the same value* //// `ocda/load_ssp` returns from the same document's `.ssp`. A writer is //// then a function from the model to bytes and cannot tell which //// serialisation it was given, exactly as spine's own writers cannot. //// //// Written against `ocda-db-format-specification.org`, whose section 4 //// gives the column by column correspondence this module implements. import gleam/dict import gleam/dynamic/decode import gleam/float import gleam/int import gleam/json import gleam/list import gleam/option import gleam/result import gleam/string import ocda/model.{type Document, Image, Object} import sqlight /// The schema major version this loader knows. pub const schema_major_read = "2" /// One language of the database, as the manifest gives it. pub type Language { Language(doc_id: Int, lang: String, source_filename: String, ssp_digest: String) } /// Load one language of a database as the model. `lang` == "" takes the /// first language the manifest gives. pub fn load_file( path: String, lang: String, any_version: Bool, ) -> Result(Document, String) { // read only: a loader has no business writing, and a read-write open // *creates* an empty database where a path is mistyped, which then reads // as a document with nothing in it, a failure that looks like an answer use conn <- with_connection("file:" <> path <> "?mode=ro", path) use _ <- result.try(case has(conn, "documents") && has(conn, "objects") && has(conn, "metadata") { True -> Ok(Nil) False -> Error( path <> ": no documents/objects/metadata tables; it does not look like a .ocda.db", ) }) let schema_version = one(conn, "select value from metadata where doc_id is null and key = 'schema.version'") use _ <- result.try(case schema_version, any_version { "", _ -> Ok(Nil) _, True -> Ok(Nil) v, False -> case major_of(v) == schema_major_read { True -> Ok(Nil) False -> Error( path <> " is schema v" <> v <> ", this loader knows v" <> schema_major_read <> ".x", ) } }) let languages = languages(conn) use chosen <- result.try(case languages, lang { [], _ -> Error(path <> ": no documents rows") [first, ..], "" -> Ok(first) _, wanted -> case list.find(languages, fn(l) { l.lang == wanted }) { Ok(found) -> Ok(found) Error(_) -> Error( path <> ": no language '" <> wanted <> "'; it holds: " <> string.join(list.map(languages, fn(l) { l.lang }), " "), ) } }) let source = dict.new() |> dict.insert("language", chosen.lang) |> dict.insert( "languages", string.join(list.map(languages, fn(l) { l.lang }), " "), ) let digest = meta_value(conn, chosen.doc_id, "source.digest") let source = case digest { "" -> source d -> dict.insert(source, "digest", d) } let document = model.Document( ..model.new_document(), format_version: schema_version, source_filename: chosen.source_filename, source: source, ) let document = metadata(conn, chosen.doc_id, document) let document = objects(conn, chosen.doc_id, document) let document = count_unread(conn, chosen.doc_id, document) Ok(document) } fn with_connection( uri: String, path: String, then: fn(sqlight.Connection) -> Result(a, String), ) -> Result(a, String) { case sqlight.open(uri) { Error(e) -> Error(path <> ": " <> e.message) Ok(conn) -> { let outcome = then(conn) let _ = sqlight.close(conn) outcome } } } /// Every language the database holds, in manifest order. pub fn languages(conn: sqlight.Connection) -> List(Language) { let d = { use id <- decode.field(0, decode.int) use lang <- decode.field(1, decode.string) use source <- decode.field(2, decode.string) use digest <- decode.field(3, decode.string) decode.success(Language(id, lang, source, digest)) } sqlight.query( "select id, lang, coalesce(source_filename,''), coalesce(ssp_digest,'') from documents order by id", on: conn, with: [], expecting: d, ) |> result.unwrap([]) } /// The three preamble blocks of the text form, out of the one metadata /// table the database keeps them in. fn metadata( conn: sqlight.Connection, doc_id: Int, document: Document, ) -> Document { let d = { use key <- decode.field(0, decode.string) use value <- decode.field(1, decode.string) decode.success(#(key, value)) } let rows = sqlight.query( "select key, value from metadata where doc_id = " <> int.to_string(doc_id) <> " order by key", on: conn, with: [], expecting: d, ) |> result.unwrap([]) list.fold(rows, document, fn(doc, row) { let #(key, raw) = row let value = string.trim(raw) case key { // source.* describes the artefact rather than the document, and is // @source in the text form "source." <> _ -> doc // translation.ocn_aligned says whether the languages of this document // share one numbering. A .ocda.db can know it because it holds every // language; a .ssp is one language and cannot. It is a fact about the // file, not the document, and carrying it here would let a writer // tell which serialisation it was handed. "translation." <> _ -> doc // the database tells the three blocks apart by a key prefix. // Unprefixing them is what makes the two loaders give one model: a // writer asks make["footer"], never metadata["make.footer"]. "make." <> rest -> model.Document(..doc, make: dict.insert(doc.make, rest, value)) "doc_has." <> rest -> model.Document(..doc, doc_has: dict.insert(doc.doc_has, rest, value)) _ -> model.Document(..doc, metadata: dict.insert(doc.metadata, key, value)) } }) } /// The objects, in document order within each section, the sections in the /// order the format gives rather than the alphabetical one sqlite would. fn objects( conn: sqlight.Connection, doc_id: Int, document: Document, ) -> Document { let d = { use section <- decode.field(0, decode.string) use ocn <- decode.field(2, decode.int) use is_a <- decode.field(3, decode.string) use part <- decode.field(4, decode.string) use is_of_section <- decode.field(5, decode.string) use is_of_type <- decode.field(6, decode.string) use heading_level <- decode.field(7, decode.int) use identifier <- decode.field(8, decode.string) use parent <- decode.field(9, decode.int) use last_descendant <- decode.field(10, decode.int) use children <- decode.field(11, decode.string) use ancestors <- decode.field(12, decode.string) use dummy <- decode.field(13, decode.int) use ocn_off <- decode.field(14, decode.int) use indent_base <- decode.field(15, decode.int) use indent_hang <- decode.field(16, decode.int) use bullet <- decode.field(17, decode.int) use lang <- decode.field(18, decode.string) use has_links <- decode.field(19, decode.int) use has_notes_reg <- decode.field(20, decode.int) use has_notes_star <- decode.field(21, decode.int) use has_images <- decode.field(22, decode.int) use segment <- decode.field(23, decode.string) use segment_prev <- decode.field(24, decode.string) use segment_next <- decode.field(25, decode.string) use anchor <- decode.field(26, decode.string) use segment_html_is <- decode.field(27, decode.string) use table_cols <- decode.field(28, decode.int) use table_widths <- decode.field(29, decode.string) use table_aligns <- decode.field(30, decode.string) use table_header <- decode.field(31, decode.int) use code_linenumbers <- decode.field(32, decode.int) use hat <- decode.field(33, decode.string) use sha256 <- decode.field(34, decode.string) use text <- decode.field(35, decode.string) use id <- decode.field(36, decode.int) use hla <- decode.field(37, decode.string) use seg_epub <- decode.field(38, decode.string) use seg_epub_is <- decode.field(39, decode.string) use range_first <- decode.field(40, decode.int) use range_last <- decode.field(41, decode.int) decode.success(#( id, Object( ocn: ocn, is_a: is_a, section: section, heading_level: level_name(heading_level), identifier: identifier, part: part, is_of_type: is_of_type, own_section: case is_of_section == section { True -> "" False -> is_of_section }, segment_html_is: segment_html_is, table_aligns: json_words(table_aligns), stow_link: [], anchor_tags: [], parent_ocn: parent, last_descendant_ocn: last_descendant, ocn_range_first: range_first, ocn_range_last: range_last, children: json_ints(children), ancestors: json_ints(ancestors), dummy_heading: dummy != 0, ocn_off: ocn_off != 0, indent_base: indent_base, indent_hang: indent_hang, bullet: bullet != 0, lang: lang, has_links: has_links != 0, has_notes_reg: has_notes_reg != 0, has_notes_star: has_notes_star != 0, has_images: has_images != 0, segment: segment, segment_prev: segment_prev, segment_next: segment_next, anchor: anchor, heading_lev_anchor: hla, segment_epub: seg_epub, segment_epub_is: seg_epub_is, table_cols: table_cols, table_widths: json_words(table_widths), table_header: table_header != 0, code_syntax: "", code_linenumbers: code_linenumbers != 0, heading_ancestors_text: json_joined(hat), sha256: sha256, images: [], text: text, ), )) } let rows = sqlight.query( object_sql(doc_id, has_range_columns(conn)), on: conn, with: [], expecting: d, ) |> result.unwrap([]) // Built newest-first per section and reversed once at the end. Appending // to the tail of each section instead would be quadratic, and the largest // document in the reference collection has some thousands of objects in // one section. let sections = list.fold(rows, dict.new(), fn(acc, row) { let #(id, object) = row // the four repeatable properties of the text form are child tables // here, one query each let object = Object( ..object, images: images(conn, id), stow_link: links(conn, id), anchor_tags: anchors(conn, id), ) case model.is_section(object.section) { False -> acc True -> dict.upsert(acc, object.section, fn(existing) { case existing { option.Some(previous) -> [object, ..previous] option.None -> [object] } }) } }) |> dict.map_values(fn(_, objects) { list.reverse(objects) }) model.Document(..document, sections: sections) } /// 2.1 added the container range as two columns. A 2.0 database has /// neither, and reads as having no range, which is what 2.0 meant. fn has_range_columns(conn: sqlight.Connection) -> Bool { let d = decode.field(0, decode.int, decode.success) case sqlight.query( "select count(*) from pragma_table_info('objects') where name = 'ocn_range_first'", on: conn, with: [], expecting: d, ) { Ok([n]) -> n > 0 _ -> False } } fn object_sql(doc_id: Int, has_range: Bool) -> String { let range_cols = case has_range { True -> "coalesce(ocn_range_first,0), coalesce(ocn_range_last,0)" False -> "0, 0" } "select section, seq, ocn, is_a, coalesce(is_of_part,''), coalesce(is_of_section,''), coalesce(is_of_type,''), coalesce(heading_level,-1), coalesce(identifier,''), coalesce(parent_ocn,0), coalesce(last_descendant_ocn,0), coalesce(children,''), coalesce(ancestors,''), coalesce(dummy_heading,0), coalesce(object_number_off,0), coalesce(indent_base,0), coalesce(indent_hang,0), coalesce(bullet,0), coalesce(lang,''), coalesce(has_links,0), coalesce(has_notes_reg,0), coalesce(has_notes_star,0), coalesce(has_images,0), coalesce(segment,''), coalesce(segment_prev,''), coalesce(segment_next,''), coalesce(anchor,''), coalesce(segment_html_is,''), coalesce(table_cols,0), coalesce(table_widths,''), coalesce(table_aligns,''), coalesce(table_header,0), coalesce(code_linenumbers,0), coalesce(heading_ancestors_text,''), coalesce(sha256,''), coalesce(text,''), id, coalesce(heading_lev_anchor,''), coalesce(segment_epub,''), coalesce(segment_epub_is,''), " <> range_cols <> " from objects where doc_id = " <> int.to_string(doc_id) <> " order by case section when 'head' then 1 when 'toc' then 2 when 'body' then 3 when 'endnotes' then 4 when 'glossary' then 5 when 'bibliography' then 6 when 'bookindex' then 7 when 'blurb' then 8 when 'tail' then 9 else 10 end, seq" } fn images(conn: sqlight.Connection, object_id: Int) -> List(model.Image) { let d = { use name <- decode.field(0, decode.string) use sha256 <- decode.field(1, decode.string) use bytes <- decode.field(2, decode.int) use width <- decode.field(3, decode.int) use height <- decode.field(4, decode.int) use missing <- decode.field(5, decode.int) decode.success(Image( name: name, sha256: sha256, bytes: bytes, width: width, height: height, missing: missing != 0, fields: dict.new(), )) } sqlight.query( "select name, coalesce(sha256,''), coalesce(bytes,0), coalesce(width,0), coalesce(height,0), coalesce(missing,0) from object_images where object_id = " <> int.to_string(object_id) <> " order by seq", on: conn, with: [], expecting: d, ) |> result.unwrap([]) } fn links(conn: sqlight.Connection, object_id: Int) -> List(String) { let d = { use url <- decode.field(0, decode.string) decode.success(url) } sqlight.query( "select url from object_links where object_id = " <> int.to_string(object_id) <> " order by seq", on: conn, with: [], expecting: d, ) |> result.unwrap([]) } fn anchors(conn: sqlight.Connection, object_id: Int) -> List(String) { let d = { use anchor <- decode.field(0, decode.string) decode.success(anchor) } sqlight.query( "select anchor from object_anchors where object_id = " <> int.to_string(object_id) <> " order by seq", on: conn, with: [], expecting: d, ) |> result.unwrap([]) } fn one(conn: sqlight.Connection, sql: String) -> String { let d = { use v <- decode.field(0, decode.string) decode.success(v) } case sqlight.query(sql, on: conn, with: [], expecting: d) { Ok([v, ..]) -> v _ -> "" } } fn has(conn: sqlight.Connection, name: String) -> Bool { one( conn, "select cast(count(*) as text) from sqlite_master where name = '" <> name <> "'", ) != "0" } fn meta_value(conn: sqlight.Connection, doc_id: Int, key: String) -> String { one( conn, "select value from metadata where doc_id = " <> int.to_string(doc_id) <> " and key = '" <> key <> "'", ) } fn major_of(version: String) -> String { case string.split_once(version, ".") { Ok(#(major, _)) -> major Error(_) -> version } } /// The eight heading levels and their indices. fn level_name(index: Int) -> String { case index { 0 -> "A" 1 -> "B" 2 -> "C" 3 -> "D" 4 -> "1" 5 -> "2" 6 -> "3" 7 -> "4" _ -> "" } } /// The JSON columns, read as JSON. /// /// Not as a character scan that strips brackets and quotes: that is wrong /// the moment a value holds an escape, and spine's encoder writes "/" as /// "\/", so every heading containing a slash comes back with a backslash in /// it. The D library had exactly that bug and all 36 document-languages /// differed on heading_ancestors_text because of it. fn json_strings(value: String) -> List(String) { case value { "" -> [] v -> case json.parse(v, decode.list(of: loose_string())) { Ok(items) -> items Error(_) -> [] } } } /// An element that may be a string or a number, which is what these columns /// hold: children and ancestors are int arrays, table_aligns and /// heading_ancestors_text string arrays, and table_widths an int array /// except where the columns divide evenly, when it is [33.3333,...]. One /// table in the reference collection does that, and without the float /// branch the widths came back as three empty strings: the fallback turned /// a value it could not read into a value that looked read. fn loose_string() -> decode.Decoder(String) { decode.one_of(decode.string, [ decode.int |> decode.map(int.to_string), decode.float |> decode.map(float.to_string), decode.success(""), ]) } fn json_ints(value: String) -> List(Int) { value |> json_strings |> list.filter(fn(s) { s != "" }) |> list.map(fn(s) { s |> int.parse |> result.unwrap(0) }) } /// Space separated in the text form: table_widths, table_aligns. fn json_words(value: String) -> String { value |> json_strings |> string.join(" ") } /// heading_ancestors_text is eight slots, joined with "|" in the text form /// and a JSON array here. The empty slots are significant: they say which /// levels the heading sits under. fn json_joined(value: String) -> String { case json_strings(value) { [] -> "" parts -> string.join(parts, "|") } } /// What the database holds and the model does not. /// /// The text loader meets every property line and counts those it does not /// keep, so a reader can say what was read and not understood. This loader /// asks for columns by name, and would never see the ones it did not ask /// for: it would report that it had understood everything, which is the /// silence the count exists to prevent. So it counts them, on the /// conditions under which the `.ssp` writes the same property /// (specification section 6.3), and the report is the same from either /// form, as the D loader's is. fn count_unread( conn: sqlight.Connection, doc_id: Int, document: Document, ) -> Document { let id = int.to_string(doc_id) let counts = [ #("ancestors_collapsed", "coalesce(ancestors_collapsed,'') != ''"), #("dom_status", "coalesce(dom_status,'') != ''"), #("dom_status_collapsed", "coalesce(dom_status_collapsed,'') != ''"), #("heading_lev_collapsed", "heading_lev_collapsed is not null"), #("parent_lev", "coalesce(parent_lev,0) != 0"), #("segment_lv4_is", "coalesce(segment_lv4_is,'') != ''"), #("attrib", "coalesce(attrib,'') != ''"), ] let property = list.fold(counts, document.unknown.property, fn(acc, c) { let n = count( conn, "select count(*) from objects where doc_id = " <> id <> " and " <> c.1, ) case n > 0 { True -> dict.insert(acc, c.0, n) False -> acc } }) let property = case has(conn, "object_subtoc") { False -> property True -> { let n = count( conn, "select count(*) from object_subtoc s join objects o on o.id = s.object_id where o.doc_id = " <> id, ) case n > 0 { True -> dict.insert(property, "lev4_subtoc", n) False -> property } } } model.Document( ..document, unknown: model.Unknown(..document.unknown, property: property), ) } fn count(conn: sqlight.Connection, sql: String) -> Int { let d = decode.field(0, decode.int, decode.success) case sqlight.query(sql, on: conn, with: [], expecting: d) { Ok([n, ..]) -> n _ -> 0 } } /// Every language the database at `path` holds, in manifest order. pub fn languages_of_file(path: String) -> Result(List(Language), String) { use conn <- with_connection("file:" <> path <> "?mode=ro", path) Ok(languages(conn)) } /// What the database at `path` carries besides the abstraction, by role /// and storage: role, compression, count, original bytes, stored bytes. pub fn carried_of_file(path: String) -> Result(List(List(String)), String) { use conn <- with_connection("file:" <> path <> "?mode=ro", path) case has(conn, "files") { False -> Ok([]) True -> { let d = { use role <- decode.field(0, decode.string) use compression <- decode.field(1, decode.string) use n <- decode.field(2, decode.int) use bytes <- decode.field(3, decode.int) use stored <- decode.field(4, decode.int) decode.success([ role, compression, int.to_string(n), int.to_string(bytes), int.to_string(stored), ]) } sqlight.query( "select role, compression, count(*), sum(bytes), sum(length(data)) from files group by role, compression order by role", on: conn, with: [], expecting: d, ) |> result.map_error(fn(e) { path <> ": " <> e.message }) } } }