summaryrefslogtreecommitdiffhomepage
path: root/dlang/src/ocda/load_db.d
blob: 2f59437c7f398ed9f023532b5faf178a1c45f714 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
module ocda.load_db;

/+ ↓ build the model from a .ocda.db.
     .
     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 from the sisudoc-abstraction-format specification,
     ocda-db-format-specification.md, whose section 4 gives the column by
     column correspondence this module implements.
+/

import std.array : join, split;
import std.json : JSONType, parseJSON;
import std.conv : to;
import std.algorithm : startsWith;
import std.string : strip;
import ocda.model;
import ocda.sqlite;

/+ ↓ the schema major this loader knows (specification section 2) +/
enum schema_major_read = "2";

class OcdaDbError : Exception {
  this(string msg) { super(msg); }
}

/+ ↓ every language the database holds, in manifest order +/
struct DbLanguage {
  int    doc_id;
  string lang;
  string source_filename;
  string ssp_digest;
}

DbLanguage[] dbLanguages(ref Db db) {
  DbLanguage[] out_;
  foreach (r; db.rows(
    "SELECT id, lang, coalesce(source_filename,''), coalesce(ssp_digest,'')
       FROM documents ORDER BY id")) {
    out_ ~= DbLanguage(r[0].to!int, r[1], r[2], r[3]);
  }
  return out_;
}

/+ ↓ load one language of a database as the model.
     lang == "" takes the first language the manifest gave.
+/
OcdaDocument loadDbFile(string path, string lang = "", bool any_version = false) {
  auto db = Db.open(path);
  scope(exit) { db.close(); }

  if (!db.has("documents") || !db.has("objects") || !db.has("metadata")) {
    throw new OcdaDbError(path ~ ": no documents/objects/metadata tables;"
      ~ " it does not look like a .ocda.db");
  }

  /+ ↓ specification sections 2 and 3.2: the rows describing the file rather
       than a document in it are the only ones with doc_id null +/
  string schema_version = db.one(
    "SELECT value FROM metadata WHERE doc_id IS NULL AND key = 'schema.version'");
  if (schema_version.length > 0 && !any_version) {
    auto major = schema_version.split(".")[0];
    if (major != schema_major_read) {
      throw new OcdaDbError(path ~ " is schema v" ~ schema_version
        ~ ", this loader knows v" ~ schema_major_read ~ ".x");
    }
  }

  auto languages = dbLanguages(db);
  if (languages.length == 0) {
    throw new OcdaDbError(path ~ ": no documents rows");
  }
  DbLanguage chosen = languages[0];
  if (lang.length > 0) {
    bool found = false;
    foreach (l; languages) { if (l.lang == lang) { chosen = l; found = true; break; } }
    if (!found) {
      string have;
      foreach (i, l; languages) { have ~= (i > 0 ? " " : "") ~ l.lang; }
      throw new OcdaDbError(path ~ ": no language '" ~ lang ~ "'; it holds: " ~ have);
    }
  }

  OcdaDocument doc;
  doc.format_version  = schema_version;
  doc.source_filename = chosen.source_filename;

  /+ ↓ @source of the .ssp corresponds to the documents row and the
       source.digest metadata (specification section 5.1 of the .ssp, and
       section 7 here) +/
  doc.source["language"] = chosen.lang;
  string languages_joined;
  foreach (i, l; languages) { languages_joined ~= (i > 0 ? " " : "") ~ l.lang; }
  doc.source["languages"] = languages_joined;
  auto digest = db.one("SELECT value FROM metadata WHERE doc_id = "
    ~ chosen.doc_id.to!string ~ " AND key = 'source.digest'");
  if (digest.length > 0) { doc.source["digest"] = digest; }

  /+ ↓ @meta: every metadata row of this language that is not a source.*
       property. source.* describes the artefact rather than the document
       and lives in @source in the text form.
  +/
  foreach (r; db.rows("SELECT key, value FROM metadata WHERE doc_id = "
      ~ chosen.doc_id.to!string ~ " ORDER BY key")) {
    string key = r[0];
    if (key.startsWith("source.")) { continue; }
    /+ ↓ 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. So it is
         a fact about the *file* rather than about the document, and putting
         it in the document's metadata would let a writer tell which
         serialisation it was handed, which is the one thing this library
         exists to prevent. Left out until the model has a place for
         collection-wide facts.
    +/
    if (key.startsWith("translation.")) { continue; }
    /+ ↓ the database has one metadata table where the text form has three
         blocks, and tells them apart by a key prefix. Unprefixing them
         here is what makes the two loaders give one model: a writer asks
         doc.make["footer"] whichever serialisation it was handed, and
         never doc.metadata["make.footer"].
    +/
    if (key.startsWith("make.")) {
      doc.make[key["make.".length .. $]] = r[1].strip;
    } else if (key.startsWith("doc_has.")) {
      doc.doc_has[key["doc_has.".length .. $]] = r[1].strip;
    } else {
      doc.metadata[key] = r[1].strip;
    }
  }

  /+ ↓ 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. +/
  bool has_range = false;
  foreach (c; db.rows("PRAGMA table_info(objects)")) {
    if (c[1] == "ocn_range_first") { has_range = true; }
  }
  string range_cols = has_range
    ? "coalesce(ocn_range_first,0), coalesce(ocn_range_last,0)"
    : "0, 0";

  /+ ↓ the objects, in document order within each section +/
  foreach (r; db.rows(
      "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 = " ~ chosen.doc_id.to!string
      ~ " 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")) {
    OcdaObject obj;
    obj.section             = r[0];
    obj.ocn                 = r[2].to!int;
    obj.is_a                = r[3];
    obj.part                = r[4];
    obj.own_section         = (r[5] == r[0]) ? "" : r[5];
    obj.is_of_type          = r[6];
    /+ ↓ the database holds the level as a number, the text form as the
         letter or digit the markup wrote. Model section 4 is the mapping. +/
    obj.heading_level       = levelName(r[7].to!int);
    obj.identifier          = r[8];
    obj.parent_ocn          = r[9].to!int;
    obj.last_descendant_ocn = r[10].to!int;
    obj.children            = jsonInts(r[11]);
    obj.ancestors           = jsonInts(r[12]);
    obj.dummy_heading       = (r[13] != "0");
    obj.ocn_off             = (r[14] != "0");
    obj.indent_base         = r[15].to!int;
    obj.indent_hang         = r[16].to!int;
    obj.bullet              = (r[17] != "0");
    obj.lang                = r[18];
    obj.has_links           = (r[19] != "0");
    obj.has_notes_reg       = (r[20] != "0");
    obj.has_notes_star      = (r[21] != "0");
    obj.has_images          = (r[22] != "0");
    obj.segment             = r[23];
    obj.segment_prev        = r[24];
    obj.segment_next        = r[25];
    obj.anchor              = r[26];
    obj.segment_html_is     = r[27];
    obj.heading_lev_anchor  = r[37];
    obj.segment_epub        = r[38];
    obj.segment_epub_is     = r[39];
    obj.ocn_range_first     = r[40].to!int;
    obj.ocn_range_last      = r[41].to!int;
    obj.table_cols          = r[28].to!int;
    obj.table_widths        = jsonWords(r[29]);
    obj.table_aligns        = jsonWords(r[30]);
    obj.table_header        = (r[31] != "0");
    obj.code_linenumbers    = (r[32] != "0");
    obj.heading_ancestors_text = jsonJoined(r[33]);
    obj.sha256              = r[34];
    obj.text                = r[35];

    /+ ↓ the four repeatable properties of the text form are child tables
         here (specification section 4) +/
    string object_id = r[36];
    foreach (i; db.rows("SELECT name, coalesce(sha256,''), coalesce(bytes,0),
                                coalesce(width,0), coalesce(height,0),
                                coalesce(missing,0)
                           FROM object_images WHERE object_id = " ~ object_id
                        ~ " ORDER BY seq")) {
      OcdaImage img;
      img.name    = i[0];
      img.sha256  = i[1];
      img.bytes   = i[2].to!ulong;
      img.width   = i[3].to!int;
      img.height  = i[4].to!int;
      img.missing = (i[5] != "0");
      obj.images ~= img;
    }
    foreach (l; db.rows("SELECT url FROM object_links WHERE object_id = "
        ~ object_id ~ " ORDER BY seq")) {
      obj.stow_link ~= l[0];
    }
    foreach (a; db.rows("SELECT anchor FROM object_anchors WHERE object_id = "
        ~ object_id ~ " ORDER BY seq")) {
      obj.anchor_tags ~= a[0];
    }

    auto section = doc.sectionByName(r[0]);
    if (section !is null) { *section ~= obj; }
  }
  countUnread(db, chosen.doc_id, doc);
  return doc;
}

/+ ↓ 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: checked,
     36 of 36.
+/
private void countUnread(ref Db db, int doc_id, ref OcdaDocument doc) {
  string id = doc_id.to!string;
  void count(string property, string where) {
    auto n = db.one("SELECT count(*) FROM objects WHERE doc_id = " ~ id
      ~ " AND " ~ where).to!size_t;
    if (n > 0) { doc.unknown.property[property] = n; }
  }
  count("ancestors_collapsed",   "coalesce(ancestors_collapsed,'') != ''");
  count("dom_status",            "coalesce(dom_status,'') != ''");
  count("dom_status_collapsed",  "coalesce(dom_status_collapsed,'') != ''");
  count("heading_lev_collapsed", "heading_lev_collapsed IS NOT NULL");
  count("parent_lev",            "coalesce(parent_lev,0) != 0");
  count("segment_lv4_is",        "coalesce(segment_lv4_is,'') != ''");
  count("attrib",                "coalesce(attrib,'') != ''");
  if (db.has("object_subtoc")) {
    auto n = db.one("SELECT count(*) FROM object_subtoc s JOIN objects o"
      ~ " ON o.id = s.object_id WHERE o.doc_id = " ~ id).to!size_t;
    if (n > 0) { doc.unknown.property["lev4_subtoc"] = n; }
  }
}

/+ ↓ model section 4: the eight levels and their indices +/
private string levelName(int index) {
  switch (index) {
  case 0: return "A";
  case 1: return "B";
  case 2: return "C";
  case 3: return "D";
  case 4: return "1";
  case 5: return "2";
  case 6: return "3";
  case 7: return "4";
  default: return "";
  }
}

/+ ↓ the JSON columns, read as JSON.
     .
     The fixed width arrays are JSON in the database and space separated
     (or "|" separated) in the text form. This was first written as a
     character scan that threw away brackets and quotes, which is wrong the
     moment a value holds an escape: Phobos' JSONValue.toString escapes
     "/" as "\/", so every heading containing a slash came back with a
     backslash in it. 36 of 36 documents differed on
     heading_ancestors_text and nothing else had shown it.
     .
     So: parse. The values are small, there are few of them, and a real
     parser is shorter than the scan it replaces.
+/
private string[] jsonStrings(string v) {
  if (v.length == 0) { return []; }
  string[] out_;
  try {
    auto j = parseJSON(v);
    if (j.type != JSONType.array) { return []; }
    foreach (e; j.array) {
      switch (e.type) {
      case JSONType.string:   out_ ~= e.str;                break;
      case JSONType.integer:  out_ ~= e.integer.to!string;  break;
      case JSONType.uinteger: out_ ~= e.uinteger.to!string; break;
      case JSONType.float_:   out_ ~= e.floating.to!string; break;
      case JSONType.true_:    out_ ~= "true";               break;
      case JSONType.false_:   out_ ~= "false";              break;
      default:                out_ ~= "";                   break;
      }
    }
  } catch (Exception) {
    return [];
  }
  return out_;
}

private int[] jsonInts(string v) {
  int[] out_;
  foreach (s; jsonStrings(v)) {
    if (s.length == 0) { continue; }
    try { out_ ~= s.to!int; } catch (Exception) {}
  }
  return out_;
}

/+ ↓ space separated in the text form: table_widths, table_aligns +/
private string jsonWords(string v) {
  return jsonStrings(v).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, so the separators carry as much
     as the text does.
+/
private string jsonJoined(string v) {
  auto parts = jsonStrings(v);
  if (parts.length == 0) { return ""; }
  return parts.join("|");
}