Captions/Subtitle Search feature is broken

[deldesir]

Describe the bug
Searching captions/subtitles no longer works
Error:
sqlite3.OperationalError: no such column: m.time_deleted

Expected behavior
Xklb should return the captions found and the titles of the video(s) related to them

To Reproduce

root@box:/usr/local/calibre-web-py3# lb search /library/calibre-web/xklb-metadata.db people
Traceback (most recent call last):
  File "/usr/local/bin/lb", line 8, in <module>
    sys.exit(library())
             ^^^^^^^^^
  File "/root/.local/share/pipx/venvs/xklb/lib/python3.12/site-packages/xklb/lb.py", line 304, in library
    return args.func()
           ^^^^^^^^^^^
  File "/root/.local/share/pipx/venvs/xklb/lib/python3.12/site-packages/xklb/lb.py", line 250, in import_func
    return getattr(module, function_name)()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/share/pipx/venvs/xklb/lib/python3.12/site-packages/xklb/mediadb/search.py", line 100, in search
    captions = list(args.db.query(query, bindings))
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/share/pipx/venvs/xklb/lib/python3.12/site-packages/sqlite_utils/db.py", line 503, in query
    cursor = self.execute(sql, params or tuple())
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/share/pipx/venvs/xklb/lib/python3.12/site-packages/sqlite_utils/db.py", line 521, in execute
    return self.conn.execute(sql, parameters)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.OperationalError: no such column: m.time_deleted

• library --version 2.8.0.49

[holta]

Thanks @deldesir for catching this!

(As this'll be very important among students/teachers searching their offline video collections...)

• PR Add lb search support iiab/calibre-web#140
• PR Support searching videos [subtitles, captions, transcripts?] iiab/calibre-web#152

[chapmanjacobd]

It would be interesting to know how the xklb-metadata.db was created so that I can fix other? bugs, if any. The problem with making all the schema/queries column-optional is that silent data bugs will exponentiate...

[holta]

interesting to know how the xklb-metadata.db was created

@deldesir can you link to precise code, showing were xklb-metadata.db is created?

Thanks if you can please.

[deldesir]

xklb-metadata.db is created by https://github.com/iiab/calibre-web/blob/916963a17cf5074e402e9c49ec608abf43809f32/scripts/lb-wrapper#L56

[chapmanjacobd]

okay it should be working in 2.8.050

It looks like this could also benefit from having one or two simple tests in tests/mediadb/test_search.py to prevent future regressions like these. You can look for examples of capsys in tests/mediadb/test_playlists.py.

It doesn't have to be very detailed to be useful--but you will need a database that has captions. You might need to move parts of tests/mediadb/test_download.py to tests/utils/__init__.py and then reference it in test_search.py similar to how tests/utils/__init__.py:v_db is referenced in other test files

[holta]

okay it should be working in 2.8.050

Priceless:

@deldesir and I have decided to focus on getting search (across all local books/videos/etc) working as rigorously/reliably as possible in coming days and weeks[*]. So hopefully he can respond to your (@chapmanjacobd) TDD-oriented suggestions very soon, as this all points precisely to what grassroots communities need (meaningful local learning, not info-bloat!) 💯

[*] CAVEAT: Bogus captions and extremely poor-quality transcriptions are something we all have to live with for now, all too often, and so be it. (Let us hope that extremely low-quality transcriptions / captions / sub-titles finally start becoming increasingly more sane in 2025 ;-)

[deldesir]

Thank you for the quick fix @chapmanjacobd

It looks like this could also benefit from having one or two simple tests

I appreciate the suggestion. I'll do my best to learn and get this done.