A RequestSession no longer expires loaded objects at commit, so a handler serializes what it
committed without a reload query. An AsyncSession cannot run that reload at all, so this is
groundwork for moving request handling to async. Background sessions (get_db_session) keep
SQLAlchemy's default: they change data outside the ORM (raw SQL, triggers) and rely on the reload.
Collection, CollectionItem and Channel dict() now run inside the serializer guard. Fixes it found:
- Channel.dict() no longer loads the deferred yt-dlp catalog (info_json), which the channel
handler loaded only to discard.
- get_channel eager-loads what Channel.dict() reads (populate_existing replaces a refresh query).
- create_channel / get_or_create_download link through relationships, so a loaded
Collection.downloads stays current.
- Setting a Repo to Never removes the deleted Download from the loaded collection; the response
reported the old frequency.
- The downloader no longer calls channel.dict() to preload a Channel; its callers read it inside
the session.
Tests run each request with expiry off, as production does, and turn it back on for the background
work that shares the test Session.
Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>
* Count Channels with any of the Tags in the Other search estimate
The Other tab lists Channels with any of the selected Tags, but its estimate
only counted Channels with the first Tag, so the two disagreed when several
Tags were selected.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* Search Channels by name in the Other search tab
The Other tab only listed Channels with the selected Tags, so typing a
Channel's name found nothing. Channels now match the search text by name,
and the Tags when there are both. The list and its estimate share one query.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* Add Domains to the Other search tab
Domains match the search text by name and/or any of the Tags, like Channels.
The list and its estimate share one query.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* Add Playlists to the Other search tab
Domains and Playlists are Collections, so the Other tab searches both with
the existing /api/collections/search (by kind) instead of a Domain-only
endpoint. The search and the estimate share search_collections_query. The
search now loads each Collection's Downloads up front instead of once per
Collection.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
---------
Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>
* collections: refuse playlist changes until playlists.yaml is imported
While WROLPi starts, the API accepts requests before every config is imported. A playlist created then could not be
saved, and the import which followed deleted it as "no longer in config" (and rebuilt every playlist's items from
the config, undoing any change). Playlist changes now wait for the import (503, "still starting").
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* collections: refuse domain changes until domains.yaml is imported
A domain created, changed, tagged or deleted through the API before domains.yaml was imported (e.g. while WROLPi
starts) was undone by the import. These now wait for the import (503, "still starting").
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* archive: importing domains.yaml keeps domains which have archives
Archives create their domain (e.g. one uploaded, or found by a refresh, while WROLPi starts, before domains.yaml is
imported). The import deleted every domain missing from the config, and the database deleted that domain's Archives
with it. A domain with Archives is now kept (and saved to the config with the next change).
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* videos: refuse Channel changes until channels.yaml is imported
A Channel created through the API before channels.yaml was imported (e.g. while WROLPi starts) could not be saved,
and the import which followed deleted it, and its Downloads, as "not in the config". Creating, changing, tagging and
deleting a Channel (through the Channel or Collection API) now wait for the import (503, "still starting").
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
---------
Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>
* db: serializers read only loaded data
Serializing a model must not query. A lazy load there is a hidden query per row (search serialized 24 rows
with 55 statements), and would be a `MissingGreenlet` error under an async Session.
- `wrolpi.db.no_db_access(reason)` and `@serializer` (on every model `__json__`, and around `json_response`):
an engine `before_cursor_execute` listener raises `UnexpectedQuery` during tests, and in production logs once
per serializer and lets the query run. Tests record each trip as well, and the autouse
`no_unexpected_queries` fixture fails a test even when the code caught the error (Video's serializer was
swallowing its per-row loads of the deferred `ffprobe_json`).
- Relationships serializers read load with their parent: `selectin` for FileGroup.tag_files and
Collection.downloads; `joined` for TagFile.tag, Collection.tag, Channel.collection, Video.channel,
Archive.collection, ZimSubscription.download, CollectionItem.zim, and the file_group of Video, Archive, Doc,
Zim and CollectionItem.
- Search and the Video lookups the video page uses load the deferred `ffprobe_json` (`undefer`).
- `wrolpi/test/test_serializers.py`: every serialized model, freshly loaded, serializes without SQL.
Search: 55 -> 5 statements for 24 videos (31 -> 5 for archives), and a few ms faster locally.
Not in this change: guarding the `dict()` serializers and `expire_on_commit=False`, which go together (about 50
serialize-after-commit sites, and 40 tests that read values changed elsewhere after a commit).
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* db: serializer loads per query; the guard ignores transaction control
The query guard reported the engine's own BEGIN (emitted through the same event as the first statement on a
Session with no open transaction) instead of the lazy load after it, and in production that BEGIN used up the
one warning per serializer. Transaction-control statements are now ignored.
Mapper-level eager loads reached background code: every Video/Archive load in the modelers (one per file,
inside the write transaction) joined file_group/channel/collection/tag and selectin-loaded the Collection's
Download rows with their yt-dlp info_json. A second refresh of 100 videos took 0.40 s instead of 0.16 s.
Serializer needs are now declared per model (`json_options()`) and added only by the queries whose results
are serialized; Download.info_json is deferred there (no serializer reads it). Mapper defaults keep only
narrow joins nearly every use needs: TagFile.tag, Collection.tag and Channel.collection. A second refresh is
back to master's statement count and time; search still drops from 55 to 3 statements.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
---------
Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>
* db: request-path library functions use the request's connection
Library functions an API request reaches now take the request's Session (first argument) instead of
opening their own session or cursor, so a request needs one SQLite connection. Raw SQL runs through the
new `wrolpi.db.session_curs(session)`, a cursor on the Session's connection; `RequestDB.curs()` uses it.
- Search: files, videos, archives, flasher, and docs search share the request connection for the total,
the page of ids, and the page's models (3 connections -> 1).
- Zim search, including tag-filtered search and estimates (2 -> 1).
- Channel detail (`Channel.get_statistics` reads on the Channel's own Session) and the channel list.
- Video detail, edit, description, captions, comments and transcode. `get_video_for_app` and
`update_video` no longer commit; their handlers use `request.ctx.db.write()`, so editing a Video and
reading it back is one transaction.
- Tags (list, overlaps, recent), file statistics and estimates, other estimates, bulk tag preview, the
download list, retry, kill and the status summary, and docs/videos/archive statistics.
Not converted: file operations that refresh files inside the request (delete, rename, upload, channel
and collection tag/move) commit page by page in code shared with the file worker; the cached zim entry
lookup; config import.
The connection-count tests now assert one connection for every endpoint they cover (no xfails left), and
cover the videos and archive statistics endpoints, which had no API test.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* downloads: startup retries in a write session of its own
start_single_tasks still called retry_downloads() without a Session after it began requiring one, so any
start after a completed refresh raised TypeError there: incomplete downloads were not reset and the
download_on_startup enable after it never ran. Startup is not a request, so
DownloadManager.retry_downloads_on_startup() opens and commits its own short write transaction.
Tested through the method, not start_single_tasks: importing main registers its startup listeners on the
shared app, and every later test in that worker then runs them.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
---------
Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>
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>
A user reported videos placed in a Channel's directory did not appear on
that Channel's page. Their info json named another existing Channel, and
get_channel preferred the source_id match over the directory, so the
videos were assigned to the other Channel.
Reorder get_channel so the directory match (with its parent-directory
walk) runs before the source_id/url matches. A Video's files must live
in their Channel's directory, so the directory reflects the user's
intent and beats yt-dlp metadata. This also aligns validation with
VideoDownloader._get_channel, which already prefers the destination
directory, and stops validation from clobbering that decision.
channel_id still wins over all other params; source_id/url still apply
when the file is not in any Channel's directory.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Introduces FileWorker for background file operations with dual-queue
architecture (public/private). Handles refresh, move, and rename tasks
with progress tracking, job completion monitoring, and batch processing.
Compares DB FileGroups to filesystem state using FileGroupDiff to detect
new/modified/deleted files. Designed for memory efficiency on Raspberry Pi.
Replacing Channels and Domains with Collection.
Moving downloading relationships from Channel to Collection.
Created domains.yaml config to manage tagged domain collections.
Adding turn-key upgrade solution in the UI.
* A new zim module has been added, this module allows several things to interact with Zim files.
* Allow searching of Zim files using libzim.
* Allow downloading of Kiwix projects (wikipedia, wikibooks, etc) on a schedule which replaces the outdated files.
* Creation of a new zim docker container which allows viewing of the Zim file contents.
* This commit also adds move/rename/mkdir features in the File Browser.
* File tags are preserved when moving/renaming.
Moving upload destination input out of drop Segment.
Disabling uploads when downloads are disabled.
Inverting labels for checkboxes in inverted modal.
Deleting file_groups which no longer contain the exclusive primary path.
Adding debug logging for all API errors.
Checking that upload output is not a directory.
Expanding nginx request size.
Adding docs to upload endpoint.
Using dot-circle icon for more image mimetypes.
Using Form.Field to make upload destination required.
Adding border to inverted Segments.
Many files can be uploaded in chunks.
Files can be uploaded, progress is displayed.
Showing white border around share QR code.
Displaying WROLPi colors for download icons.
Creating button to clear DirectorySearch.
Excluding channel/domain directories so other directories can be returned.
Ordering search results.
Ignoring when no channel is found only if we have a destination.
Limiting directory search results.
Displaying categories when searching destination directories.
Handling when live_chat.json file exists.
Allowing refresh of directory directly.
Logging which download was successful.
Removing timeout.
Creating index for directory names.
Sleeping to catch cancel.
Arbitrary files can be downloaded using the new FileDownloader.
Tags can be created and deleted in UI.
Adding tag_file using id to avoid session error.
Using UTC in test.
Matching tag_names style in test.
Tag icon is displayed on file rows.
Archives, Videos and Files can be filtered by their tags.
Tags can be added and removed from UI.
Tags are displayed in VideoPlayer.
The tags of a file are returned in it's JSON.
Video favorites are converted to Favorite tag on channel config import.
Updating tests to match frontend usage.
PDF creator and modification time are displayed.
Reworking file search to use Video.__json__ for complex video relations.
Archives can be viewed.
Removing ebook.title.
Reworking alembic migration to be an upgrade rather than a replace.
Skipping test on circleci.
Clearing out video caption index only if extract_subtitles if off.
Channel tests pass.
Some video tests pass.
Missing videos can be found. A video can be downloaded.
Orphaned files can be found.
Videos can be tagged.
Videos can be searched.
Videos can be modeled.
Converting some tests to use FileGroup.primary_path.
Adding archive and domains to alembic migration.
Testing cascading Archive titles.
Archives now uses FileGroup.
archive.lib tests pass.
Archive is partially converted to FileGroup.
Calculating FileGroup.primary file on refresh, rather than on select.
wrolpi tests pass.
Using FilesType to convert between str/pathlib.Path for FileGroup.files.
Using list of files in FileGroup.files instead of dictionary.
Storing files in dedicated FileGroup.files to avoid overwriting them.
Adding comment explaining CASE.
Not overwriting FileGroup.data on refresh if no change has been detected.
Removing File model in favor of FileGroup.data.
Adding typing to Files methods.
Files are indexed using the primary mimetype of the FileGroup.
files test_lib tests pass.
Removing file_group_file in favor of simple one-to-many relationship.
PDF files are indexed during refresh.
Adding Tag and TagFile to tag files with specific tags.
Only iterating when full_stem_ids has contents.
Testing that FileGroups are related to their Files.
Files and file_groups are created and linked.
Bump webpack from 5.75.0 to 5.76.1 in /app (#160)
Bumps [webpack](https://github.com/webpack/webpack) from 5.75.0 to 5.76.1.
- [Release notes](https://github.com/webpack/webpack/releases)
- [Commits](https://github.com/webpack/webpack/compare/v5.75.0...v5.76.1)
---
updated-dependencies:
- dependency-name: webpack
dependency-type: indirect
...
Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Handling all combinations of toggle in light and dark.
Changing CPUUsageProgress colors to be more readable in dark mode.
Moving theme handling into Theme.js. Importing element wrappers from Theme.js to handle element theming.
Changing video menu to dark mode.
Creating ThemeContext to share Dark Mode setting.
Removing duplicate code for search functions.
Displaying X when downloads are disabled.
Using react context to share settings across components.
Refusing to start workers when WROL Mode is enabled.
Changing warning to debug.
Queuing recurring downloads first.
Adding Video.url index.
Adding special handling for youtube shorts to videos.
Reworking Downloads to have one worker per process.
Testing creating downloads and getting downloaders.
Testing video validation and poster generation. Testing video statistics.
Increasing inventory test coverage.
Adding pytest.ini to automatically detect async test.
Adding more archive test coverage.
Testing Archive CRUD.
Catching and logging when encountering error during indexing.
Using FileIcon when Archive has no screenshot.
A Video will be assigned to a Channel by its info json first, then to its directory.
Including suffix in File json.
Adding index for File.suffix.
Gathering suffix during indexing.
Passing entire file down to FileIcon.
Moving logging up.
Returning File.associated in file json.
Associated files are last in file search.
Removing duplicate refreshing code by creating new _refresh_directory function.
Changing File.idempotency to a datetime to avoid having to clear idempotency.
Truncating indexes to avoid error with large files.
Don't overwrite video data when info json is empty.
Using already_downloaded for channel videos.
Awaiting refresh in Channel.refresh.
Testing that videos already downloaded aren't downloaded again.
Adding indexes for video/archive referenced paths.
Changing grid width for mobile or computer.
Adding button to refresh specific directories.
Restoring Channel edit link.
Fixing caption path in video_modeler.
Adding ZIP files to mimetype colors.
Using model instead of first file's model.
Fetching File models in batches to avoid issuing a SELECT for every File with a model.
Flushing generated poster file.
Getting Channel in VideoPage, relying on Video.channl_id, falling back to URL's channel_id.
Attaching Video to it's Channel during download.
Using ExternalCardLink for ArchivePage singlefile link.
Validating and indexing videos after download.
Handling glob when file name contains matching characters.
Reworking and testing split_path_stem_and_suffix and glob_shared_stem.
Adding associated video files to DB during upsert.
Adding pending downloads to dashboard.
Hiding status when download is open.
Handling when load is empty.
Adding load statistics to Dashboard.
Limiting concurrent after_refresh functions.
Handling when no onSelect is declared.
Adding ImageRow for FileTable.
Moving image date into Card.Meta.
Using ExternalLink for FileCard.
Using ExternalLink for ImageCard.
Handling when error occurs during refresh of a directory. Committing during refresh to avoid losing all files if an error occurs.
Removing api health check to avoid spamming logs.
Creating ImageCard to display image posters when they aren't too large.
Removing archive.domain_id not null.
Reworking file refresh to process all files in a single directory.
Bumping version for major file rework.
No longer checking for video corruption.
Adding duration by default to speed up tests.
Using register_indexer for all indexers.
Adding CPU usage progress bar in Welcome.
Modifying TextIndexer to ignore large text files.
Counting all files during refresh.
Adding refresh_logger to clarify logging. Checking for wrol_mode before refresh functions.
Removing unused idempotency from video/channel tables.
Using magic module to speed up mimetype identification.
All tests pass.
Archive tests pass.
Changing video get endpoint to return 'file' to avoid ambiguity.
Video tests pass.
Simplifying regex filtering.
Using regex to ignore case when filtering.
Converting all channel classes to functions.
Restoring channel refresh and download buttons.
Matching format of Archive and Video pages.
Adding sorting to Archives search.
Reworking video orders for file search. Using SearchInput for videos search.
Archives can be searched on the Archives page. Domains can be filtered.
Reworking Archive search to use Files. Videos, Archives and Global search use the FilesView.
Videos are claimed by their Channel based on the Channel's directory. Prev and Next video cards are displayed on the VideoPage.
Creating useVideo for reworked VideoWrapper and VideoPage.
Adding Paginator to FilesView.
Moving file refresh button out of manage tab. Removing Files page tabs. Limiting refresh to one at a time.
Adding class for card icon to match poster height.
Video and Archives always re-claim their files on refresh.
Archive files are indexed and claimed.
FileView table is compact and striped.
Videos page uses FileView.
Updated navlinks to router v6.
Removing ! character from file names.
Disabling select button when cards are visible.
Adding show/hide button for FileTable selection.
The Archive Page is displayed.
Adding mimetype dropdown.
Upgrading to React Router v6 for useSearchParams.
Validating video during modeling. Removing screenshot during corruption check because it was very slow. Updating app to use video files as search results.
Updating video search to return Files.
Video and WROLPi tests pass.
Creating function to refresh files within a channel's directory.
Removing troubleshooting print. Updating result order in test.
Reworking video_factory to handle a specific video file location.
Returning video model on file search.
Getting session using video_file.
Screenshotting when checking for video corruption.
Reducing ffmpeg calls by gathering duration during poster generation.
Moving video file matching into model.
VideoIndexer is moved into video module. Video files are validated.
Moving files module into wrolpi in preparation to use it in modules.
Title and contents are indexed for text file.
Files are indexed on refresh by using their indexer.
Zip files are indexed using their contents.
Testing multiple zip files.
Adding unique to file.path. Checking that the correct indexer is used.
Adding index to file.modification_datetime and file.size.
Handling file info gathering in File.do_index. Only reading contents of file if file.indexed is False.
Video model is found during file refresh. A Video record is created that has the associated files.
Videos without associated files are found.
Videos are found using registered video_modeler() rather than in refresh code.
Removing old video columns.
Handling video info json files.
Trying to import each module individually. Will continue to import modules even if one fails.
Reworking refresh so it performs less DB calls.
Converting file.path to primary key.
file.path is always a pathlib.Path.
Updating video_factory to use new file system, testing it.
Bump sanic from 21.9.1 to 21.12.2 in /docker/archive
Bumps [sanic](https://github.com/sanic-org/sanic) from 21.9.1 to 21.12.2.
- [Release notes](https://github.com/sanic-org/sanic/releases)
- [Changelog](https://github.com/sanic-org/sanic/blob/main/CHANGELOG.rst)
- [Commits](https://github.com/sanic-org/sanic/compare/v21.9.1...v21.12.2)
---
updated-dependencies:
- dependency-name: sanic
dependency-type: direct:production
...
Signed-off-by: dependabot[bot] <support@github.com>
Removing timeout. Updating tests now that downloads work.
Bumping version for async downloads.
Downloads run asynchronously. Using aiohttp to make archive http request so ArchiveDownloader.do_download can be asynchronous.
Removing session for create_download(s) usages.
Reworkingk create_downloads to use optional_session.
Canceling download workers after tests and when stopping the manager.
All tests passing.
Tests pass!
Downloader tests pass.
DownloadManager uses multiple workers to download domains in parallel.
RSS download can be submitted in UI.
Checking if entry has link.
A video playlist can be downloaded.
Passing sub_downloader when submitting RSS feed.
Adding Archive downloader button.
Playlists need more info.
Removing expunge, it causes errors elsewhere. Fixing tests.
Showing errors on recurring downloads. Ignoring CharacterEncodingOverride bozo error for rss feed.
Changing download button order.
Fixing bug where channel attributes were unavailable after session. Simplifying video format selection.
Calling validate_video in Video.validate() to avoid duplicate code.
Adding more frequent downloads for RSS Feeds.
DownloadManager skips downloads in it's list.
Renaming get_next_download to more clear calculate_next_download.
Stopping download manager when server is shutting down.
Not shutting down downloads during tests, not all tests use downloads.
Making methods static.
