PR #244 [searching] is prone to false positives [ALSO: database integrity questions about safely/synchronously deleting "videos" from both DB's]

[deldesir]

The queries made to xklb-metadata.db return titles to be used as terms to fetch videos records from metadata.db. What if different videos have the same title!

[holta]

The challenge will likely be establishing a primary key (much like a UUID / GUID) for every video-or-similar, cleanly guaranteeing integrity across both databases:

CLARIFS:

• Both above SQLite databases live in /library/calibre-web — RECAP:
  • Remember that metadata.db (for "book" and "video" titles, authors, descriptions and not much else!) has a quite fixed schema that we cannot change, as it's a de facto global standard dating back to Calibre's original introduction in ~2006.
  • Whereas the 2nd database xklb-metadata.db, that we added to IIAB Calibre-Web earlier this year to handle videos (especially for scraping of videos + their transcripts from the Internet, ETC), is defined by our very good friend who is the designer xklb — he might have suggestions for us?
• While likely not relevant to this design discussion, also just be aware of a 3rd SQLite database /library/calibre-web/config/app.db that is tangentially involved here. Though this 3rd DB is really only for (1) usernames/passwords (2) Calibre-Web settings, AND (3) bookshelves (that teachers use to organize books + videos!)

Related:

• PR #152

• 197

• 204

• PR #244

cc: @mabuelhagag, @avni, @codewiz

[holta]

Related IDEA from @deldesir:

• At the same time we should very likely transition to (unify our db approaches around) SQLAlchemy — rather than Python’s sqlite3 library that we're currently using to manipulate /library/calibre-web/xklb-metadata.db — so as to better align with @OzzieIsaacs’s upstream Calibre-Web!

[avni]

For metadata.db, according to the calibredb manual, these are the available book fields:

Available fields: author_sort, authors, comments, cover, formats, identifiers, isbn, languages, last_modified, pubdate, publisher, rating, series, series_index, size, tags, template, timestamp, title, uuid

uuid may be a unique identifier for a given book? It may still be helpful to map the relevant columns across the three tables, metadata.db, xklb-metadata.db, app.db to support solving this (i.e., a mini-specification). Foreign keys can be used to map across tables if that is not already being done.