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),
}));
}
|