Combine the SQL tables

[Aquafina-water-bottle]

From Zetta:

You could make the sqlite most likely faster than my original lazy memory solution. Combining the tables into one with a "source" column so you only have to run 1 query to get all results for all sources.

CREATE TABLE entries (
  id integer PRIMARY KEY,
  source text NOT NULL,
  expression text NOT NULL,
  reading text,
  speaker text,
  file text NOT NULL
);

SELECT * FROM entries WHERE expression = :expression AND (reading IS NULL OR reading = :reading)

Then adding an index (after inserting all the rows) so that you can minimize the page lookups.

CREATE INDEX idx_entries_expression ON entries(expression);

Minimally, so you don't have to try to make one big global query to match all your cases, just add indexes to each of the tables. I think there is a CREATE INDEX IF NOT EXISTS or similar to upgrade existing tables. It will make it take up a tad bit more storage but nothing compared to the multiple gigs of audio.

Reading material:

• https://stackoverflow.com/questions/23572342/sqlite3-how-do-i-use-indices
• https://www.sqlite.org/queryplanner.html
• https://www.sqlite.org/optoverview.html