whiskers.git / crates / whiskers-store / migrations / 0004_speech_cache.sql

Version 4: the speech cache, and the count of what it saved.

A line of speech that is the same every time (a page name, a hint, a greeting, a preview) is kept under the 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 kept is the audio and the time of every character, never the words: the line is put back from the request 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);

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