File size: 8,060 Bytes
377fc96
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
// Every screen is one of these queries. No screen touches SQL directly.

import type { Database } from './db';
import { rows } from './db';
import type { Hit, Shelf, SortKey, Tag, TagCount, Volume } from './types';
import { sections } from './sections';

const SORT_SQL: Record<SortKey, string> = {
  latest: 'v.retrieved DESC',
  oldest: 'v.retrieved ASC',
  title: 'v.title ASC',
  pages: 'v.page_count DESC',
};

export function shelves(db: Database): Shelf[] {
  const counts = new Map(
    rows<{ tag: string; n: number }>(
      db,
      "SELECT t.tag AS tag, count(*) AS n FROM volume_tag t JOIN volume v ON v.id = t.volume_id WHERE v.carry = 'carry' AND v.page_count > 0 AND v.admission != 'rejected' AND t.tag LIKE 'section:%' GROUP BY t.tag",
    ).map((r) => [r.tag, r.n]),
  );
  return sections(db).map((s) => ({
    id: s.id,
    order: s.order,
    title: s.title,
    scopeLine: s.scope,
    icon: s.icon,
    count: counts.get(`section:${s.id}`) ?? 0,
  }));
}

function tagsOf(db: Database, volumeId: string): Tag[] {
  return rows<{ tag: string; source: Tag['source'] }>(
    db,
    'SELECT tag, source FROM volume_tag WHERE volume_id = ? ORDER BY tag',
    [volumeId],
  ).map((r) => {
    const [namespace, value] = r.tag.split(/:(.+)/);
    return { tag: r.tag, namespace, value, source: r.source };
  });
}

export function volumes(db: Database, opts: { tag?: string; sort?: SortKey } = {}): Volume[] {
  const where = opts.tag ? ' AND v.id IN (SELECT volume_id FROM volume_tag WHERE tag = ?)' : '';
  const list = rows<any>(
    db,
    `SELECT v.id, v.title, v.publisher, v.edition, v.format, v.licence, v.page_count, v.retrieved, v.admission, c.text AS cover, c.digest_line
     FROM volume v LEFT JOIN cover c ON c.volume_id = v.id WHERE v.carry = 'carry' AND v.page_count > 0 AND v.admission != 'rejected'${where} ORDER BY ${SORT_SQL[opts.sort ?? 'latest']}`,
    opts.tag ? [opts.tag] : [],
  );
  return list.map((r) => ({
    id: r.id,
    title: r.title,
    publisher: r.publisher ?? '',
    edition: r.edition ?? '',
    format: r.format,
    licence: r.licence ?? '',
    pageCount: r.page_count ?? 0,
    retrieved: r.retrieved ?? '',
    cover: r.cover ?? '',
    digestLine: r.digest_line ?? '',
    admission: r.admission ?? 'stock',
    tags: tagsOf(db, r.id),
  }));
}

/** Tag counts for the filter groups, optionally within one section. */
export function tagCounts(db: Database, within?: string): TagCount[] {
  const scope = within ? ' AND v.id IN (SELECT volume_id FROM volume_tag WHERE tag = ?)' : '';
  const counts = rows<{ tag: string; n: number }>(
    db,
    `SELECT t.tag AS tag, count(*) AS n FROM volume_tag t JOIN volume v ON v.id = t.volume_id WHERE v.carry = 'carry' AND v.page_count > 0 AND v.admission != 'rejected'${scope} GROUP BY t.tag ORDER BY n DESC, t.tag`,
    within ? [`section:${within}`] : [],
  );
  const byTag = new Map(counts.map((c) => [c.tag, c.n]));
  const out: TagCount[] = [];
  // every section, in order, empty ones included
  for (const s of sections(db)) {
    const tag = `section:${s.id}`;
    out.push({ tag, namespace: 'section', value: s.id, label: s.title, count: byTag.get(tag) ?? 0 });
  }
  for (const c of counts) {
    if (c.tag.startsWith('section:')) continue;
    const [namespace, value] = c.tag.split(/:(.+)/);
    out.push({ tag: c.tag, namespace, value, label: value, count: c.n });
  }
  return out;
}

const STOP = new Set(['the', 'and', 'for', 'how', 'can', 'what', 'with', 'you', 'are', 'should', 'who', 'someone', 'very', 'not', 'when', 'does', 'did', 'put', 'get', 'into', 'from', 'this', 'that', 'there', 'they', 'them', 'their', 'your', 'our', 'his', 'her', 'its', 'much', 'many']);
const ROLE_RANK: Record<string, number> = { primary: 0, reference: 0.5, depth: 0.7, figures: 1, archive: 2 };

function words(question: string): string[] {
  const all = question.toLowerCase().match(/[a-z0-9]+/g) ?? [];
  const kept = all.filter((w) => w.length > 2 && !STOP.has(w));
  return kept.length ? kept : all;
}

function ftsQueries(question: string): string[] {
  const w = words(question).map((x) => `"${x}"`);
  const queries = [w.join(' AND ')];
  if (w.length > 2) queries.push(w.map((drop) => `(${w.filter((x) => x !== drop).join(' AND ')})`).join(' OR '));
  queries.push(w.join(' OR '));
  return queries;
}

export function search(db: Database, question: string, opts: { tag?: string; limit?: number } = {}): Hit[] {
  const limit = opts.limit ?? 8;
  for (const query of ftsQueries(question)) {
    const hits = rows<any>(
      db,
      `SELECT f.passage_id AS passage, bm25(passage_fts) AS score, s.text, s.kind, p.number AS page, p.anchor,
              v.id AS volume, v.title, v.edition, sv.role
       FROM passage_fts f JOIN passage s ON s.id = f.passage_id JOIN page p ON p.id = s.page_id
       JOIN volume v ON v.id = p.volume_id JOIN shelf_volume sv ON sv.volume_id = v.id
       WHERE passage_fts MATCH ? AND v.admission != 'rejected' ORDER BY score LIMIT 60`,
      [query],
    )
      .filter((h) => !opts.tag || tagsOf(db, h.volume).some((t) => t.tag === opts.tag))
      .map((h) => ({
        ...h,
        score: h.score + (ROLE_RANK[h.role] ?? 1) - (['procedure_step', 'checklist', 'warning'].includes(h.kind) ? 1 : 0),
      }))
      .sort((a, b) => a.score - b.score)
      .slice(0, limit);
    if (hits.length >= Math.min(limit, 3)) return hits;
    if (query === ftsQueries(question).at(-1)) return hits;
  }
  return [];
}

/** Volumes whose title or cover matches the question's words. */
export function matchingVolumes(db: Database, question: string, limit = 3): Volume[] {
  const w = words(question);
  if (!w.length) return [];
  const like = w.map(() => '(lower(v.title) LIKE ? OR lower(c.text) LIKE ?)').join(' OR ');
  const params = w.flatMap((x) => [`%${x}%`, `%${x}%`]);
  const ids = rows<{ id: string }>(
    db,
    `SELECT v.id FROM volume v LEFT JOIN cover c ON c.volume_id = v.id WHERE v.carry = 'carry' AND v.page_count > 0 AND v.admission != 'rejected' AND (${like}) LIMIT ${limit}`,
    params,
  ).map((r) => r.id);
  return volumes(db).filter((v) => ids.includes(v.id));
}


export interface Page {
  id: string;
  number: number;
  anchor: string;
  text: string;
}

export function volume(db: Database, id: string): Volume | undefined {
  return volumes(db).find((v) => v.id === id);
}

export function volumeMeta(db: Database, id: string): { url: string; licence: string; statement: string; sha256: string; retrieved: string; history: string | null } | undefined {
  const r = rows<any>(db, 'SELECT url, licence, licence_statement, licence_read_at, sha256, retrieved FROM volume WHERE id = ?', [id])[0];
  const history = r && /action=history/.test(r.licence_read_at ?? '') ? r.licence_read_at : null;
  return r && { url: r.url, licence: r.licence, statement: r.licence_statement, sha256: r.sha256, retrieved: r.retrieved, history };
}

export function pageCount(db: Database, volumeId: string): number {
  return rows<{ n: number }>(db, 'SELECT count(*) AS n FROM page WHERE volume_id = ?', [volumeId])[0]?.n ?? 0;
}

export function page(db: Database, volumeId: string, number: number): Page | undefined {
  const r = rows<any>(db, 'SELECT id, number, anchor, text FROM page WHERE volume_id = ? AND number = ?', [volumeId, number])[0];
  return r && { id: r.id, number: r.number, anchor: r.anchor, text: r.text };
}

export function passagesOf(db: Database, pageId: string): { id: string; start: number; end: number; kind: string }[] {
  return rows<any>(db, 'SELECT id, start, "end" AS end, kind FROM passage WHERE page_id = ? ORDER BY start', [pageId]);
}

/** First-line headings of each page, for a contents outline. */
export function outline(db: Database, volumeId: string): { number: number; heading: string }[] {
  return rows<{ number: number; text: string }>(db, 'SELECT number, text FROM page WHERE volume_id = ? ORDER BY number', [volumeId]).map((r) => ({
    number: r.number,
    heading: (r.text.split('\n').find((l) => l.trim().length > 3) ?? '').slice(0, 80),
  }));
}