Commit graph

4 commits

Author SHA1 Message Date
lrnselfreliance
80678e1f6e
A library on the internal disk gets a database (#832)
"Run repair without external drive" kept the library on the internal disk,
but the API's unmounted-media guard refuses to create or use a database
beneath /media/wrolpi when it is not a mount.  After onboarding without a
drive the API restarted forever with no database, and downloads were
blocked as "media not mounted".

The Controller now records the choice as drives.library_on_internal_disk in
controller.yaml -- the only source of it.  The database guard and the
media_mounted check both read that marker, so require_media_mounted keeps
its meaning and needs no change.  Onboarding a drive later clears the
marker, so it is never copied onto the drive.

Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-14 16:15:33 -06:00
Roland
5bbbef3f0b db: address review — confirm WAL was applied; refuse fuseblk mounts
Two fixes from PR review:

- `PRAGMA journal_mode=WAL` returns the mode SQLite actually selected and can leave the
  prior rollback journal without raising.  Confirm the returned mode instead of assuming
  the request succeeded, so a silent WAL refusal correctly falls back to TRUNCATE +
  synchronous=FULL rather than caching WAL with the weaker synchronous=NORMAL.

- `fuseblk` (block-backed FUSE such as ntfs-3g) has unreliable byte-range locking that
  can corrupt the database under the workers' concurrent access.  Move it out of the
  WAL-incompatible allow-list into an UNSAFE_LOCKING_FS_TYPES refuse-list (renamed from
  NETWORK_FS_TYPES).  NTFS on a modern kernel mounts via the native `ntfs3` driver, which
  stays allowed; exFAT is unaffected.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
2026-07-22 18:54:30 -06:00
Roland
8988a45195 db: allow exFAT/FAT/NTFS media drives by degrading from WAL to a rollback journal
A media drive formatted for Windows compatibility (exFAT/FAT/NTFS) cannot support
SQLite WAL: it needs mmap'd shared-memory those filesystems don't provide, so
`PRAGMA journal_mode=WAL` raised `(sqlite3.OperationalError) disk I/O error` on
every connection, taking down the whole API (observed on 10.0.0.8; the tell was
`Media directory has the wrong permissions: 0o40777`, since these filesystems can't
store Unix permission bits).

The engine now tries WAL first and, on failure, falls back to a TRUNCATE rollback
journal with synchronous=FULL (crash-safe, since WROLPi is off-grid).  The decision
is memoized per-engine so WAL isn't re-attempted or re-logged on every fresh
NullPool connection.  The bootstrap environment check no longer refuses these
drives; it warns once that write-concurrency is reduced and recommends ext4.
Network filesystems remain refused: their locking is unsafe regardless of journal mode.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
2026-07-22 18:54:30 -06:00
Roland
d9303a0ea8
Postgres -> SQLite hard cutover: the database moves into the media directory.
The database now lives at <media>/config/wrolpi.db so a library (files,
configs, AND database) travels with the drive between WROLPi systems.

Core:
- wrolpi/db.py: lazy SQLite engine factory (WAL, busy_timeout=30s,
  foreign_keys=ON); get_db_curs yields a plain sqlite3.Cursor; native
  sqlite3 paramstyle everywhere (no compatibility shims); global sqlite3
  adapters keep raw-SQL datetime formats identical to the ORM's.
- wrolpi/fts.py: FTS5 external-content search replacing tsvector/GIN.
  Injection-proof websearch->FTS5 translator, bm25(10,4,2,1) reproducing
  A/B/C/D weighting, fast(abc)/deep column filtering, snippet headlines.
  Stemming contract pinned by dedicated tests (watch/watches/watched/
  watching are interchangeable).
- wrolpi/schema_ddl.py: SQLite triggers shared verbatim by the alembic
  baseline and test DBs; fixes the channel video_count DELETE/UPDATE
  bugs and a latent total_size bug (it used the video id as channel id).
- wrolpi/db_bootstrap.py: startup auto-create/migrate; refuses databases
  written by a newer WROLPi (db_version_mismatch); SQLite 3.40 floor;
  network-filesystem refusal; never creates/migrates/trusts a database
  beneath an unmounted production media directory (shadow-DB guard).
- Alembic: 85 Postgres-era migrations squashed into one baseline;
  revision ids are dates (2026_07_10_1350) so filenames self-sort and
  alembic_version says when a schema was created; render_as_batch=True.
- All raw Postgres SQL rewritten (ANY->json_each IN, array_agg @> ->
  HAVING COUNT(DISTINCT), unnest->executemany/UPDATE..FROM,
  generate_series histograms->strftime GROUP BY, DISTINCT ON->
  row_number(), writable CTEs->two statements, jsonb_*->json_*, explicit
  NULLS FIRST/LAST, LIMIT-before-OFFSET).  Simple statements converted
  to SQLAlchemy instead (flag upsert, download retry/summary/tag checks,
  channel-by-tag estimate, Directory delete).
- Config imports count skipped entries; background dumps are blocked
  after a lossy import so partial imports can never overwrite YAML
  configs (they are the sole source of truth for rebuilds now).
- Search tolerates explicit JSON nulls for limit/offset (Postgres
  treated OFFSET NULL as 0; SQLite errors).
- file_group.id/doc_section.id render as INTEGER PRIMARY KEY on SQLite
  (BIGINT is not a rowid alias; FTS5 content_rowid requires it).

Ops teardown: docker-compose db service, CI postgres sidecars,
initialize_api_db.sh, controller postgresql service, psycopg2-binary all
removed.  upgrade.sh stops/disables postgresql (data is NOT deleted;
`systemctl enable --now postgresql` restores it); systemd units no
longer Want postgresql.service.

Existing-user upgrade: first boot creates+migrates the DB, configs
import (tags recreate their FileGroups from disk), a refresh rebuilds
the library index.  QA'd on the 10.0.0.8 RPi: rebuild matched the
Postgres baseline exactly; survived an uncontrolled hard reboot with
clean integrity checks.

Accepted behavior changes: FTS5 has no stopwords; bm25 length
normalization shifts some rank orders; fast-path d_headline is
un-highlighted; all-negative queries match nothing.

Deferred: Debian/trixie package lists (incl. sqlite3 CLI), Cypress
re-baseline, VACUUM INTO snapshots, exFAT/NTFS warning, auto-kicked
rebuild refresh with progress UX.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
2026-07-12 14:09:37 -06:00