2023-02-24 17:31:08 -07:00
|
|
|
#! /usr/bin/env bash
|
2023-09-24 20:10:09 -06:00
|
|
|
# Installs any new dependencies of the App and API. Uses internet.
|
2023-02-24 17:31:08 -07:00
|
|
|
|
2023-09-24 20:10:09 -06:00
|
|
|
set -e
|
|
|
|
|
set -x
|
2023-05-23 16:41:48 -06:00
|
|
|
|
2025-12-07 17:03:55 -07:00
|
|
|
# Ensure help service is always restarted, but preserve exit code
|
|
|
|
|
cleanup() {
|
|
|
|
|
local exit_code=$?
|
|
|
|
|
systemctl restart wrolpi-help.service || :
|
|
|
|
|
exit $exit_code
|
|
|
|
|
}
|
|
|
|
|
trap cleanup EXIT
|
2025-12-06 13:35:32 -07:00
|
|
|
|
2026-07-18 15:35:00 -06:00
|
|
|
# --- Debian 12 (bookworm) hard cutoff --------------------------------------
|
|
|
|
|
# WROLPi's Debian 12 line ends at the signed tag `debian12-final`. Everything
|
|
|
|
|
# after it (the Postgres->SQLite move and the Debian 13 rebase) requires a
|
|
|
|
|
# fresh Debian 13 (trixie) system; there is no supported in-place 12 -> 13
|
|
|
|
|
# upgrade. ./upgrade.sh has already fetched and checked out the latest
|
|
|
|
|
# release by the time we run, so on a Debian 12 machine we pin back to
|
|
|
|
|
# `debian12-final` and never advance past it. On Debian 13+ this is a no-op
|
|
|
|
|
# and the normal upgrade proceeds.
|
|
|
|
|
GUARD_TAG="debian12-final"
|
|
|
|
|
GUARD_KEY="/opt/wrolpi/wrolpi/roland@learningselfreliance.com.gpg"
|
|
|
|
|
|
|
|
|
|
# True only on Debian 13 (trixie) or newer. A missing or non-numeric
|
|
|
|
|
# VERSION_ID is treated as "older" so the frozen line fails safe (pins).
|
|
|
|
|
os_is_debian_13_or_newer() {
|
|
|
|
|
local vid=""
|
|
|
|
|
[ -r /etc/os-release ] && vid=$(awk -F= '$1=="VERSION_ID"{gsub(/"/,"",$2);print $2}' /etc/os-release)
|
|
|
|
|
case "${vid}" in
|
|
|
|
|
'' | *[!0-9]*) return 1 ;;
|
|
|
|
|
esac
|
|
|
|
|
[ "${vid}" -ge 13 ]
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
# Verify the guard commit is signed by the trusted WROLPi key before we check
|
|
|
|
|
# it out and execute it. ./upgrade.sh only verified the release tip it landed
|
|
|
|
|
# on; `debian12-final` is off that line, so we re-verify here with a throwaway
|
|
|
|
|
# keyring (same trust model as ./upgrade.sh).
|
|
|
|
|
verify_guard_commit() {
|
|
|
|
|
local ghome rc=1
|
|
|
|
|
ghome=$(mktemp -d)
|
|
|
|
|
if GNUPGHOME="${ghome}" gpg --batch --quiet --import "${GUARD_KEY}" 2>/dev/null; then
|
|
|
|
|
# Mark the pinned key ultimately trusted to silence cosmetic web-of-trust
|
|
|
|
|
# warnings; validity (not trust) is what verify-commit actually checks.
|
|
|
|
|
GNUPGHOME="${ghome}" gpg --batch --list-keys --with-colons 2>/dev/null \
|
|
|
|
|
| awk -F: '/^fpr:/ {print $10":6:"; exit}' \
|
|
|
|
|
| GNUPGHOME="${ghome}" gpg --batch --quiet --import-ownertrust 2>/dev/null || :
|
|
|
|
|
GNUPGHOME="${ghome}" git -C /opt/wrolpi verify-commit "${GUARD_TAG}^{commit}" 2>&1 && rc=0
|
|
|
|
|
fi
|
|
|
|
|
rm -rf "${ghome}"
|
|
|
|
|
return "${rc}"
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
debian12_guard() {
|
|
|
|
|
if os_is_debian_13_or_newer; then
|
|
|
|
|
return 0
|
|
|
|
|
fi
|
|
|
|
|
|
2026-07-18 17:02:10 -06:00
|
|
|
# Debian 12 (or older): make sure the cutoff tag is present AND current.
|
2026-07-18 15:35:00 -06:00
|
|
|
# `debian12-final` is off the release line, so a plain fetch of the branch
|
2026-07-18 17:02:10 -06:00
|
|
|
# would not follow it. Force (+ / --force) so a moved tag (e.g. a bumped
|
|
|
|
|
# final release) overwrites a stale local tag instead of being rejected as
|
|
|
|
|
# "would clobber existing tag" — otherwise the guard pins to the old target.
|
|
|
|
|
# Safe to force: the commit's signature is verified before checkout below.
|
|
|
|
|
git -C /opt/wrolpi fetch origin "+refs/tags/${GUARD_TAG}:refs/tags/${GUARD_TAG}" 2>/dev/null \
|
|
|
|
|
|| git -C /opt/wrolpi fetch --force --tags origin 2>/dev/null || :
|
2026-07-18 15:35:00 -06:00
|
|
|
|
|
|
|
|
# --verify --quiet: fail cleanly (empty, non-zero) instead of echoing the
|
|
|
|
|
# unresolved arg when the tag is absent.
|
|
|
|
|
local head target
|
|
|
|
|
head=$(git -C /opt/wrolpi rev-parse --verify --quiet HEAD 2>/dev/null || echo none)
|
|
|
|
|
target=$(git -C /opt/wrolpi rev-parse --verify --quiet "${GUARD_TAG}^{commit}" 2>/dev/null || echo none)
|
|
|
|
|
|
|
|
|
|
if [ "${target}" = "none" ]; then
|
|
|
|
|
echo "ERROR: Debian 12 cutoff tag '${GUARD_TAG}' not found; refusing to run newer code on Debian 12."
|
|
|
|
|
exit 10
|
|
|
|
|
fi
|
|
|
|
|
|
|
|
|
|
if [ "${head}" != "${target}" ]; then
|
|
|
|
|
# ./upgrade.sh checked out a newer release; roll back to the last
|
|
|
|
|
# supported commit, then hand off to ITS copy of this script via exec so
|
|
|
|
|
# we stop running the newer (already-overwritten) version.
|
|
|
|
|
if ! verify_guard_commit; then
|
|
|
|
|
echo "ERROR: '${GUARD_TAG}' is not signed by the trusted WROLPi key; aborting."
|
|
|
|
|
exit 11
|
|
|
|
|
fi
|
|
|
|
|
echo "Debian 12 detected: pinning WROLPi to its final supported version (${GUARD_TAG})."
|
|
|
|
|
git -C /opt/wrolpi checkout -f -B release "${GUARD_TAG}" \
|
|
|
|
|
|| { echo "ERROR: could not check out ${GUARD_TAG}; aborting."; exit 12; }
|
|
|
|
|
# Loop guard: never re-exec unless the rollback actually landed on the target.
|
|
|
|
|
if [ "$(git -C /opt/wrolpi rev-parse HEAD)" != "${target}" ]; then
|
|
|
|
|
echo "ERROR: rollback did not land on ${GUARD_TAG}; aborting."
|
|
|
|
|
exit 13
|
|
|
|
|
fi
|
|
|
|
|
exec /opt/wrolpi/scripts/upgrade.sh "$@"
|
|
|
|
|
fi
|
|
|
|
|
|
|
|
|
|
# Already on the final supported commit: warn loudly, then fall through so
|
|
|
|
|
# this (final) version's normal upgrade runs and leaves the box healthy with
|
|
|
|
|
# services restarted.
|
|
|
|
|
cat <<'EOF'
|
|
|
|
|
|
|
|
|
|
================================================================================
|
|
|
|
|
This is the FINAL WROLPi version for Debian 12 (bookworm).
|
|
|
|
|
|
|
|
|
|
No further updates will be installed on Debian 12. To keep receiving
|
|
|
|
|
updates, reinstall WROLPi on Debian 13 (trixie):
|
|
|
|
|
* Download the latest image from https://wrolpi.org
|
|
|
|
|
* Write it to your SD card / USB and boot it.
|
|
|
|
|
* Reconnect your media drive; your files and configuration are preserved
|
|
|
|
|
on the drive and will be re-indexed automatically.
|
|
|
|
|
================================================================================
|
|
|
|
|
|
|
|
|
|
EOF
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
debian12_guard "$@"
|
|
|
|
|
# ---------------------------------------------------------------------------
|
2026-07-20 08:55:41 -06:00
|
|
|
|
|
|
|
|
# --- Rebuild virtual environments after a Python major-version change -------
|
|
|
|
|
# A Debian major upgrade (e.g. 12 -> 13) replaces /usr/bin/python3 (3.11 -> 3.13).
|
|
|
|
|
# The existing venvs still resolve their `python3` to the system interpreter,
|
|
|
|
|
# but their packages live in lib/python3.11/site-packages, which the new
|
|
|
|
|
# interpreter never searches -- so every import fails and `pip install --upgrade`
|
|
|
|
|
# cannot repair them. Detect this by comparing each venv's recorded Python
|
|
|
|
|
# version against the current one, and do a full rebuild (delete + recreate +
|
|
|
|
|
# reinstall) via reset_virtual_environments.sh when they differ.
|
|
|
|
|
rebuild_stale_venvs() {
|
|
|
|
|
local current
|
|
|
|
|
current=$(python3 -c 'import sys; print("%d.%d" % sys.version_info[:2])' 2>/dev/null) || return 0
|
|
|
|
|
local cfg venv_version
|
|
|
|
|
for cfg in /opt/wrolpi/venv/pyvenv.cfg \
|
|
|
|
|
/opt/wrolpi/controller/venv/pyvenv.cfg \
|
|
|
|
|
/opt/wrolpi-help/venv/pyvenv.cfg; do
|
|
|
|
|
[ -r "${cfg}" ] || continue
|
|
|
|
|
# pyvenv.cfg records e.g. "version = 3.11.2"; match that key exactly so
|
|
|
|
|
# the newer "version_info = ..." line is ignored. Reduce to major.minor.
|
|
|
|
|
venv_version=$(awk -F'=' '$1 ~ /^[[:space:]]*version[[:space:]]*$/ {gsub(/[[:space:]]/,"",$2); print $2; exit}' "${cfg}")
|
|
|
|
|
venv_version=${venv_version%.*}
|
|
|
|
|
if [ -n "${venv_version}" ] && [ "${venv_version}" != "${current}" ]; then
|
|
|
|
|
echo "Python changed (venv at ${cfg%/pyvenv.cfg} built for ${venv_version}, system is now ${current}); rebuilding all virtual environments..."
|
|
|
|
|
/opt/wrolpi/scripts/reset_virtual_environments.sh -y
|
|
|
|
|
return 0
|
|
|
|
|
fi
|
|
|
|
|
done
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
rebuild_stale_venvs
|
|
|
|
|
# ---------------------------------------------------------------------------
|
2026-07-18 15:35:00 -06:00
|
|
|
|
2026-03-19 15:02:12 -06:00
|
|
|
# Upgrade Controller first so users can monitor the rest of the upgrade.
|
|
|
|
|
upgrade_controller() {
|
|
|
|
|
echo "Upgrading WROLPi Controller..."
|
|
|
|
|
|
|
|
|
|
# Create Controller venv if it doesn't exist
|
|
|
|
|
if [ ! -d /opt/wrolpi/controller/venv ]; then
|
|
|
|
|
echo "Creating Controller virtual environment..."
|
|
|
|
|
python3 -m venv /opt/wrolpi/controller/venv
|
|
|
|
|
fi
|
|
|
|
|
|
|
|
|
|
# Clear bytecode cache before upgrading.
|
|
|
|
|
find /opt/wrolpi/controller/venv -type d -name __pycache__ -exec rm -rf {} + 2>/dev/null || :
|
|
|
|
|
find /opt/wrolpi/controller/venv -name "*.pyc" -delete 2>/dev/null || :
|
|
|
|
|
|
|
|
|
|
# Install dependencies while Controller is still running to minimize downtime.
|
|
|
|
|
echo "Updating Controller dependencies..."
|
|
|
|
|
/opt/wrolpi/controller/venv/bin/pip install --upgrade pip
|
|
|
|
|
/opt/wrolpi/controller/venv/bin/pip install --upgrade -r /opt/wrolpi/controller/requirements.txt
|
|
|
|
|
|
|
|
|
|
# Dependencies are ready, now restart the Controller as quickly as possible.
|
|
|
|
|
systemctl daemon-reload
|
|
|
|
|
systemctl restart wrolpi-controller
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
upgrade_controller || echo "Controller upgrade failed, continuing..."
|
|
|
|
|
|
2023-09-24 20:10:09 -06:00
|
|
|
# Install any App dependencies.
|
|
|
|
|
cd /opt/wrolpi/app || exit 1
|
|
|
|
|
npm install || npm install || npm install || npm install # try install multiple times :(
|
2023-05-01 13:00:23 -06:00
|
|
|
|
2026-02-16 08:08:46 -07:00
|
|
|
# Install/upgrade Deno runtime.
|
|
|
|
|
upgrade_deno() {
|
Single file deno (#478)
* Singlefile runs under Deno 2.8.3; browser leftovers are reaped.
single-file-cli is designed for Deno. Under node 18 (Debian 12/RPi OS) it
cannot reach the browser CDP on IPv6-enabled hosts ("localhost" resolves to
::1 first with no IPv4 fallback) and it never exits after a successful save,
blocking the download worker forever.
The native path and the Archive docker container now run single-file with
"deno run". Deno is pinned at v2.8.3 everywhere because yt-dlp 2026.06.09
requires at least 2.3.0 (our node is Debian's v18, below yt-dlp's v22
minimum). Deno >= 2.3 kills the browser single-file spawns (piped stderr
that single-file never reads), so the browser is run through
singlefile_browser_wrapper.sh which discards its stderr.
Commands are run in their own process group which is killed when they
finish, reaping the browser single-file leaves behind. The Archive
downloader now has a ten minute timeout. Install scripts install Deno
(previously only upgrade.sh and the image builds did).
* Singlefile writes to a file instead of dumping to stdout.
Deno >= 2.3 truncates large --dump-content output at exit; compressed
(SingleFileZ) singlefiles of large pages lost their ZIP tail, failed the
compression check in write_archive_files, and were destroyed by the HTML
prettifier (blank white archives). single-file now writes its result to a
temporary file which WROLPi reads back.
As insurance, a singlefile marked compressed (<html data-sfz) whose ZIP is
unreadable is now stored verbatim instead of being prettified.
* Compressed Archive screenshots and readability images are no longer broken.
The index.html inside a compressed (SingleFileZ) singlefile references its
resources by relative ZIP paths. Screenshots are now taken from the fully
extracted ZIP so those references resolve. Readability images are inlined
as data URIs read from the ZIP (readability-extractor absolutizes relative
srcs against the page URL, so both forms are matched).
* Compressed Archives are created by converting the uncompressed capture.
The page is always captured uncompressed; readability, screenshot, and
title come from that canonical page (its images are inline data URIs, so
no ZIP extraction or image inlining is needed). When compress_singlefile
is set, a second single-file run converts the captured file into a
compressed (SingleFileZ) self-extracting file. The conversion is a local
file:// operation - the page is not fetched again, and single-file
preserves the original URL header when re-saving its own pages. If the
conversion fails, the uncompressed singlefile is stored instead.
* Consolidating duplicate compressed-singlefile tests.
The write_archive_files tests (verbatim write, title fallback, broken-ZIP
guard, readability inlining) are combined into one test; the helper probes
and the setting-plumbing tests are each merged into one. The do_singlefile
compression-failure scenario and the timeout constant are folded into the
main do_singlefile test, which shared their setup.
* Addressing review comments: removeprefix, PermissionError when killing.
* Bumping version to 0.24.1-beta.
* Install scripts assert the deno.zip hash to avoid malicious or invalid files.
The hashes are the official sha256sum assets of the Deno v2.8.3 release,
per architecture.
* Archive Dockerfile asserts the deno.zip hash to avoid malicious or invalid files.
---------
Co-authored-by: Claude Fable 5 <noreply@anthropic.com>
2026-06-12 17:07:10 -06:00
|
|
|
DENO_VERSION="v2.8.3"
|
2026-02-16 08:08:46 -07:00
|
|
|
ARCH=$(uname -m)
|
|
|
|
|
if [ "$ARCH" = "aarch64" ]; then
|
|
|
|
|
DENO_ARCH="aarch64-unknown-linux-gnu"
|
|
|
|
|
elif [ "$ARCH" = "x86_64" ]; then
|
|
|
|
|
DENO_ARCH="x86_64-unknown-linux-gnu"
|
|
|
|
|
else
|
|
|
|
|
echo "Unsupported architecture for Deno: $ARCH"
|
|
|
|
|
return 1
|
|
|
|
|
fi
|
|
|
|
|
|
|
|
|
|
# Check if Deno is already installed at the correct version.
|
|
|
|
|
if command -v deno &> /dev/null; then
|
|
|
|
|
CURRENT_VERSION=$(deno --version | head -n1 | awk '{print "v"$2}')
|
|
|
|
|
if [ "$CURRENT_VERSION" = "$DENO_VERSION" ]; then
|
|
|
|
|
echo "Deno ${DENO_VERSION} already installed"
|
|
|
|
|
return 0
|
|
|
|
|
fi
|
|
|
|
|
fi
|
|
|
|
|
|
|
|
|
|
echo "Installing Deno ${DENO_VERSION} for ${ARCH}..."
|
|
|
|
|
curl -fsSL "https://github.com/denoland/deno/releases/download/${DENO_VERSION}/deno-${DENO_ARCH}.zip" -o /tmp/deno.zip
|
|
|
|
|
unzip -o /tmp/deno.zip -d /usr/local/bin/
|
|
|
|
|
chmod +x /usr/local/bin/deno
|
|
|
|
|
rm /tmp/deno.zip
|
|
|
|
|
deno --version
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
upgrade_deno || echo "Deno upgrade failed, continuing..."
|
|
|
|
|
|
2026-06-11 20:05:10 -06:00
|
|
|
# Upgrade single-file to the pinned version; old versions (1.x) create compressed Archives without
|
|
|
|
|
# the SingleFile comment header in the prelude, which WROLPi cannot validate.
|
|
|
|
|
# readability-extractor is pinned alongside it to match the install scripts.
|
|
|
|
|
upgrade_singlefile() {
|
|
|
|
|
SINGLE_FILE_VERSION="2.0.73"
|
|
|
|
|
if [ "$(single-file --version 2>/dev/null)" != "${SINGLE_FILE_VERSION}" ]; then
|
|
|
|
|
echo "Upgrading single-file-cli to ${SINGLE_FILE_VERSION}..."
|
|
|
|
|
npm i -g single-file-cli@${SINGLE_FILE_VERSION} readability-extractor@0.0.6
|
|
|
|
|
else
|
|
|
|
|
echo "single-file-cli ${SINGLE_FILE_VERSION} already installed"
|
|
|
|
|
fi
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
upgrade_singlefile || echo "single-file upgrade failed, continuing..."
|
|
|
|
|
|
2026-02-28 16:11:12 -07:00
|
|
|
# Clear Python bytecode cache before upgrading packages.
|
|
|
|
|
echo "Clearing Python bytecode cache..."
|
|
|
|
|
find /opt/wrolpi/venv -type d -name __pycache__ -exec rm -rf {} + 2>/dev/null || :
|
|
|
|
|
find /opt/wrolpi/venv -name "*.pyc" -delete 2>/dev/null || :
|
|
|
|
|
|
2023-09-24 20:10:09 -06:00
|
|
|
# Install any new Python requirements.
|
2024-07-31 15:01:31 -06:00
|
|
|
/opt/wrolpi/venv/bin/pip3 install --upgrade -r /opt/wrolpi/requirements.txt
|
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 13:59:20 -06:00
|
|
|
# Upgrade the WROLPi database. Never fatal: the media drive may not be mounted;
|
|
|
|
|
# the API creates/migrates the database itself at startup once the media directory is available.
|
2026-07-05 15:40:47 -06:00
|
|
|
(cd /opt/wrolpi && /opt/wrolpi/venv/bin/python3 /opt/wrolpi/main.py db upgrade) || \
|
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 13:59:20 -06:00
|
|
|
echo "WARNING: db upgrade failed (media directory unavailable?); the API will retry at startup."
|
|
|
|
|
|
|
|
|
|
# WROLPi now uses SQLite (inside the media directory); stop and disable PostgreSQL to free
|
2026-07-18 13:19:26 -06:00
|
|
|
# system resources on upgrades from older installs. The old Postgres data is NOT deleted --
|
|
|
|
|
# to get it back temporarily: sudo systemctl enable --now postgresql
|
|
|
|
|
# To reclaim disk later: sudo apt-get remove --purge postgresql*
|
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 13:59:20 -06:00
|
|
|
if id postgres >/dev/null 2>&1; then
|
|
|
|
|
echo "Disabling PostgreSQL; WROLPi now uses SQLite. Your old Postgres data is untouched."
|
|
|
|
|
# Stop the umbrella and any per-cluster instances (postgresql@15-main, etc.).
|
|
|
|
|
systemctl stop 'postgresql*' 2>/dev/null || :
|
|
|
|
|
systemctl disable postgresql 2>/dev/null || :
|
|
|
|
|
fi
|
2026-07-18 13:19:26 -06:00
|
|
|
# Remove legacy Postgres client credentials (no longer used).
|
|
|
|
|
rm -f /home/wrolpi/.pgpass
|
2023-07-11 17:23:56 -06:00
|
|
|
|
2023-11-23 16:20:28 -07:00
|
|
|
# Upgrade WROLPi Help.
|
2023-11-23 16:22:46 -07:00
|
|
|
/opt/wrolpi/scripts/install_help_service.sh || echo "Help install failed."
|
2023-11-23 16:20:28 -07:00
|
|
|
|
2026-03-29 18:58:31 -06:00
|
|
|
# Migrate from old Leaflet/renderd/Apache2 map stack to MapLibre GL JS + PMTiles.
|
|
|
|
|
/opt/wrolpi/scripts/install_protomaps.sh || echo "Protomaps migration failed."
|
|
|
|
|
|
|
|
|
|
# Download map fonts blob if missing.
|
|
|
|
|
MAP_FONTS_BLOB=/opt/wrolpi-blobs/map-fonts.tar.gz
|
|
|
|
|
if [[ ! -f ${MAP_FONTS_BLOB} || ! -s ${MAP_FONTS_BLOB} ]]; then
|
|
|
|
|
echo "Downloading map fonts..."
|
|
|
|
|
mkdir -p /tmp/map-fonts-dl
|
|
|
|
|
curl -fsSL https://github.com/protomaps/basemaps-assets/archive/refs/heads/main.tar.gz \
|
|
|
|
|
| tar -xz --strip-components=1 -C /tmp/map-fonts-dl basemaps-assets-main/fonts
|
|
|
|
|
tar -czf "${MAP_FONTS_BLOB}" -C /tmp/map-fonts-dl fonts
|
|
|
|
|
rm -rf /tmp/map-fonts-dl
|
|
|
|
|
fi || echo "Map fonts download failed, continuing..."
|
|
|
|
|
|
|
|
|
|
# Download map overview blob if missing (42 MB, provides global zoom 0-6).
|
|
|
|
|
MAP_OVERVIEW_BLOB=/opt/wrolpi-blobs/map-overview.pmtiles
|
|
|
|
|
if [[ ! -f ${MAP_OVERVIEW_BLOB} || ! -s ${MAP_OVERVIEW_BLOB} ]]; then
|
|
|
|
|
echo "Downloading map overview..."
|
|
|
|
|
curl -fsSL https://wrolpi.nyc3.cdn.digitaloceanspaces.com/maps/map-overview.pmtiles \
|
|
|
|
|
-o "${MAP_OVERVIEW_BLOB}"
|
|
|
|
|
fi || echo "Map overview download failed, continuing..."
|
2025-01-01 10:59:28 -07:00
|
|
|
|
2026-07-05 13:09:11 -06:00
|
|
|
# Finish configuring any package left half-configured by an interrupted apt/dpkg run
|
|
|
|
|
# (e.g. a prior upgrade that died at a conffile prompt).
|
|
|
|
|
DEBIAN_FRONTEND=noninteractive dpkg --force-confdef --force-confold --configure -a || :
|
|
|
|
|
|
Replace nginx with Caddy for TLS termination and reverse proxying.
- Replace nginx with Caddy as the web server for both Docker and native
(RPi/Debian) deployments. Caddy handles HTTPS termination, reverse
proxying to all services (API, React app, Controller, Kiwix, Help, Map),
and serves the media/download file browsers.
- Generate a Root CA and signed leaf certificates for HTTPS. The Root CA
uses Name Constraints to restrict signing to local addresses only
(.local, localhost, and RFC 1918 private IP ranges), mitigating MITM
risk if the CA key is compromised.
- Add an HTTP landing page that guides users through one-time CA
certificate installation on iOS, Android, macOS, Windows, Linux, and
Firefox, with auto-redirect when the cert is already trusted.
- Add trailing-slash redirects for /controller, /docs, /media, and
/download so bare paths don't fall through to the React catch-all.
- Add a Root CA download link on the Settings page under Configs.
- Update help.sh diagnostics for IPv6 listener detection and HTTP/2
status codes.
- Remove all nginx config files and the https_proxy Docker container.
- Bump wrolpi-help submodule with certificate documentation.
2026-03-07 12:08:27 -07:00
|
|
|
# Migrate from nginx to Caddy if necessary.
|
|
|
|
|
if ! command -v caddy &>/dev/null; then
|
|
|
|
|
echo "Installing Caddy (replacing nginx)..."
|
|
|
|
|
# Disable and stop nginx if it was previously used.
|
|
|
|
|
systemctl stop nginx 2>/dev/null || :
|
|
|
|
|
systemctl disable nginx 2>/dev/null || :
|
|
|
|
|
# Install Caddy from official apt repo.
|
2026-07-05 13:02:59 -06:00
|
|
|
curl -1sLf 'https://dl.cloudsmith.io/public/caddy/stable/gpg.key' | gpg --batch --yes --dearmor -o /usr/share/keyrings/caddy-stable-archive-keyring.gpg
|
Replace nginx with Caddy for TLS termination and reverse proxying.
- Replace nginx with Caddy as the web server for both Docker and native
(RPi/Debian) deployments. Caddy handles HTTPS termination, reverse
proxying to all services (API, React app, Controller, Kiwix, Help, Map),
and serves the media/download file browsers.
- Generate a Root CA and signed leaf certificates for HTTPS. The Root CA
uses Name Constraints to restrict signing to local addresses only
(.local, localhost, and RFC 1918 private IP ranges), mitigating MITM
risk if the CA key is compromised.
- Add an HTTP landing page that guides users through one-time CA
certificate installation on iOS, Android, macOS, Windows, Linux, and
Firefox, with auto-redirect when the cert is already trusted.
- Add trailing-slash redirects for /controller, /docs, /media, and
/download so bare paths don't fall through to the React catch-all.
- Add a Root CA download link on the Settings page under Configs.
- Update help.sh diagnostics for IPv6 listener detection and HTTP/2
status codes.
- Remove all nginx config files and the https_proxy Docker container.
- Bump wrolpi-help submodule with certificate documentation.
2026-03-07 12:08:27 -07:00
|
|
|
curl -1sLf 'https://dl.cloudsmith.io/public/caddy/stable/debian.deb.txt' | tee /etc/apt/sources.list.d/caddy-stable.list
|
|
|
|
|
apt-get update
|
2026-07-05 13:09:11 -06:00
|
|
|
# Non-interactive: a conffile prompt (e.g. repair.sh already wrote /etc/caddy/Caddyfile)
|
|
|
|
|
# would otherwise hang or abort under the tty-less wrolpi-upgrade.service.
|
|
|
|
|
DEBIAN_FRONTEND=noninteractive apt-get install -y \
|
|
|
|
|
-o Dpkg::Options::=--force-confdef -o Dpkg::Options::=--force-confold caddy
|
Replace nginx with Caddy for TLS termination and reverse proxying.
- Replace nginx with Caddy as the web server for both Docker and native
(RPi/Debian) deployments. Caddy handles HTTPS termination, reverse
proxying to all services (API, React app, Controller, Kiwix, Help, Map),
and serves the media/download file browsers.
- Generate a Root CA and signed leaf certificates for HTTPS. The Root CA
uses Name Constraints to restrict signing to local addresses only
(.local, localhost, and RFC 1918 private IP ranges), mitigating MITM
risk if the CA key is compromised.
- Add an HTTP landing page that guides users through one-time CA
certificate installation on iOS, Android, macOS, Windows, Linux, and
Firefox, with auto-redirect when the cert is already trusted.
- Add trailing-slash redirects for /controller, /docs, /media, and
/download so bare paths don't fall through to the React catch-all.
- Add a Root CA download link on the Settings page under Configs.
- Update help.sh diagnostics for IPv6 listener detection and HTTP/2
status codes.
- Remove all nginx config files and the https_proxy Docker container.
- Bump wrolpi-help submodule with certificate documentation.
2026-03-07 12:08:27 -07:00
|
|
|
fi
|
|
|
|
|
|
2026-03-28 16:28:47 -06:00
|
|
|
# Install Samba if not already installed.
|
|
|
|
|
if ! command -v smbd &>/dev/null; then
|
|
|
|
|
echo "Installing Samba..."
|
|
|
|
|
apt-get update
|
2026-07-05 13:09:11 -06:00
|
|
|
DEBIAN_FRONTEND=noninteractive apt-get install -y \
|
|
|
|
|
-o Dpkg::Options::=--force-confdef -o Dpkg::Options::=--force-confold samba
|
2026-03-28 16:28:47 -06:00
|
|
|
fi
|
|
|
|
|
|
2026-04-07 20:01:49 -06:00
|
|
|
# Install gnome-disk-utility if not already installed (provides desktop disk management GUI).
|
|
|
|
|
if ! command -v gnome-disks &>/dev/null; then
|
|
|
|
|
echo "Installing gnome-disk-utility..."
|
|
|
|
|
apt-get update
|
2026-07-05 13:09:11 -06:00
|
|
|
DEBIAN_FRONTEND=noninteractive apt-get install -y \
|
|
|
|
|
-o Dpkg::Options::=--force-confdef -o Dpkg::Options::=--force-confold gnome-disk-utility
|
2026-04-07 20:01:49 -06:00
|
|
|
fi
|
|
|
|
|
|
2026-07-09 07:30:36 -06:00
|
|
|
# Install dnsmasq-base if not already installed (NetworkManager needs it for the hotspot's
|
|
|
|
|
# DHCP; it is only a Recommends of network-manager, so ISOs built with --apt-recommends
|
|
|
|
|
# false shipped without it). iw and rfkill are WiFi diagnostic tools.
|
2026-07-09 07:34:56 -06:00
|
|
|
if ! command -v dnsmasq &>/dev/null || ! command -v iw &>/dev/null || ! command -v rfkill &>/dev/null; then
|
2026-07-09 07:30:36 -06:00
|
|
|
echo "Installing dnsmasq-base, iw, rfkill..."
|
|
|
|
|
apt-get update
|
|
|
|
|
DEBIAN_FRONTEND=noninteractive apt-get install -y \
|
|
|
|
|
-o Dpkg::Options::=--force-confdef -o Dpkg::Options::=--force-confold dnsmasq-base iw rfkill
|
|
|
|
|
fi
|
|
|
|
|
|
2026-04-07 20:01:49 -06:00
|
|
|
# Clean up stale landing page (Controller now serves port 80 directly).
|
|
|
|
|
rm -f /var/www/landing.html
|
|
|
|
|
|
2023-09-24 20:10:09 -06:00
|
|
|
# Install any configs, restart services.
|
|
|
|
|
/opt/wrolpi/repair.sh
|