1-- Version 4: the speech cache, and the count of what it saved.
2--
3-- A line of speech that is the same every time (a page name, a hint, a greeting, a preview) is kept under the
4-- voice and the exact text, so it is paid for once. The key is a SHA-256 of the voice and the text, and what is
5-- kept is the audio and the time of every character, never the words: the line is put back from the request
6-- that asks for it. `used` is a counter that only rises, so "least recently used" is the smallest.
7CREATE TABLE speech_cache (
8    key BLOB PRIMARY KEY CHECK (length(key) = 32),
9    audio BLOB NOT NULL,
10    -- Start and end of each character in milliseconds, 32-bit little-endian, in order.
11    spans BLOB NOT NULL,
12    weight INTEGER NOT NULL CHECK (weight > 0),
13    used INTEGER NOT NULL
14) STRICT, WITHOUT ROWID;
15CREATE INDEX speech_cache_by_use ON speech_cache (used);
16
17-- A hit spends nothing; it is counted beside the day's spend as lines answered and characters saved.
18ALTER TABLE voice_spend ADD COLUMN hits INTEGER NOT NULL DEFAULT 0 CHECK (hits >= 0);
19ALTER TABLE voice_spend ADD COLUMN saved_chars INTEGER NOT NULL DEFAULT 0 CHECK (saved_chars >= 0);