Files
WolfandClaude Opus 5.5 515d2250fc Deploy from a WSL checkout, from boilerplate 1679e88
When the checkout lives under \\wsl.localhost, step 1 runs its tests in the
distro through deploy/test-commit.sh and db.ps1 push makes its snapshot
there, because the .venv is a Linux one. pipeline/context.py narrows by
sys.platform so mypy passes on Linux. See boilerplate docs/wsl.md.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-09-26 19:43:50 +02:00

151 lines
6.1 KiB
PowerShell

<#
.SYNOPSIS
The database, in both directions.
.DESCRIPTION
push this machine's database -> the server
pull a fresh snapshot from the server -> .\backups
`push` is the one-off at the start: production begins knowing what you
already have, rather than starting empty. `pull` is the one you keep
running -- on a schedule from the server itself (server\backup.sh under
cron) and by hand from here whenever you want the copy somewhere the
server is not.
`push` moves the database and nothing else. It does not stop the app before
or start it after: a deployment knows what else is moving and in what order,
and this does not. Stop the app on the server first -- the import is a file
swap under SQLite's feet and is refused while the app is up. Start it again
afterwards.
Both go through SQLite's own `VACUUM INTO`. The database is in WAL mode, so
the .db file on disk is always missing whatever is still in the -wal beside
it, and copying the three files with the app running is a race you lose
quietly -- a database that opens fine and is short a day's writes.
After a push the two copies diverge from the first thing written on either.
There is no merge for that and this does not pretend there is: pick the one
that is authoritative, and use the other read-only or not at all.
.EXAMPLE
.\deploy\db.ps1 push
.EXAMPLE
.\deploy\db.ps1 pull
#>
[CmdletBinding()]
param(
[Parameter(Mandatory)]
[ValidateSet('push', 'pull')]
[string] $Action,
# Skip the confirmation before replacing the server's database.
[switch] $Yes
)
Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'
. "$PSScriptRoot/_common.ps1"
Set-Location $root
$SshHost = Setting SSH_HOST -Required
$RemoteDir = Setting REMOTE_DIR -Required
# Where the data actually is. Beside the compose file unless APP_DATA put it
# under a shared root; then REMOTE_DATA has to say so.
$RemoteData = Setting REMOTE_DATA "$RemoteDir/data"
$Python = Setting PYTHON (Join-Path $root '.venv\Scripts\python.exe')
$LocalDb = Setting LOCAL_DB (Join-Path $root "$Name.db")
switch ($Action) {
# ---------------------------------------------------------------- push
'push' {
if (-not (Test-Path $LocalDb)) { throw "no $LocalDb" }
$stamp = Get-Date -Format 'yyyyMMdd-HHmmss'
$snap = Join-Path $root "$Name-$stamp.db.gz"
Write-Host "snapshotting $LocalDb"
# One Python call for both halves. `VACUUM INTO` is the only way to get a
# consistent single file out of a WAL database, and Python is already the
# thing that has to be here -- PowerShell has no gzip that writes the format
# the server's `gunzip` expects.
$snapshot = @'
import gzip, pathlib, shutil, sqlite3, sys, tempfile
source, target = sys.argv[1], pathlib.Path(sys.argv[2])
with tempfile.TemporaryDirectory() as work:
plain = pathlib.Path(work) / "snapshot.db"
db = sqlite3.connect(source)
try:
db.execute("VACUUM INTO ?", (str(plain),))
finally:
db.close()
with plain.open("rb") as raw, gzip.open(target, "wb") as out:
shutil.copyfileobj(raw, out)
'@
# A checkout inside WSL has a Linux .venv, and SQLite cannot lock a WAL
# database through the \\wsl.localhost share, so the snapshot is taken in
# the distro. The script goes in on stdin, past both shells.
if ($Wsl.Success) {
$snapshot | wsl.exe -d $Wsl.Groups[1].Value --cd (ConvertTo-LinuxPath $root) --exec .venv/bin/python - (ConvertTo-LinuxPath $LocalDb) (ConvertTo-LinuxPath $snap)
} else {
& $Python -c $snapshot $LocalDb $snap
}
Assert-Ok 'snapshot'
' {0:N1} MB' -f ((Get-Item $snap).Length / 1MB) | Write-Host
try {
Write-Host ''
Write-Host "This replaces the database on $SshHost with this one." -ForegroundColor Yellow
Write-Host 'Anything written there since is gone.' -ForegroundColor Yellow
Write-Host 'The app must already be stopped there, and stays stopped after.' -ForegroundColor Yellow
if (-not $Yes) {
$reply = Read-Host 'go ahead? [y/N]'
if ($reply -notmatch '^[yY]') { Write-Host 'stopped.'; return }
}
scp -C $snap "${SshHost}:$RemoteData/incoming.db.gz"; Assert-Ok 'scp database'
scp (Join-Path $PSScriptRoot 'server\import_database.py') "${SshHost}:$RemoteDir/import_database.py"
Assert-Ok 'scp importer'
} finally {
Remove-Item -Force $snap -ErrorAction SilentlyContinue
}
# Base64 rather than stdin: PowerShell has no `<` for a native command, and
# piping the text would rewrite the script's line endings on the way.
$b64 = [Convert]::ToBase64String([IO.File]::ReadAllBytes((Join-Path $PSScriptRoot 'server\import.sh')))
ssh $SshHost "echo $b64 | base64 -d | bash -s -- '$RemoteDir'"; Assert-Ok 'import'
Write-Host "pushed. The app is still stopped; start it when the rest of"
Write-Host 'the deployment is in place:'
Write-Host " ssh $SshHost `"cd '$RemoteDir' && docker compose up -d --wait $Name`""
}
# ---------------------------------------------------------------- pull
'pull' {
$into = Join-Path $root 'backups'
New-Item -ItemType Directory -Force -Path $into | Out-Null
# The server's own copy of backup.sh, not a streamed one. It is the same
# script cron runs, it is already there, and it locates its own directory
# through BASH_SOURCE -- which is exactly the thing a script read from stdin
# does not have. import.sh takes its directory as an argument and so can be
# streamed; this one cannot.
Write-Host "snapshotting on $SshHost"
ssh $SshHost "cd '$RemoteDir' && bash backup.sh"; Assert-Ok 'backup'
$newest = (ssh $SshHost "ls -1t '$RemoteData'/backups/$Name-*.db.gz | head -1").Trim()
Assert-Ok 'listing backups'
if (-not $newest) { throw 'no backup appeared on the server' }
scp -C "${SshHost}:$newest" $into; Assert-Ok 'scp'
$file = Split-Path -Leaf $newest
Write-Host "backups\$file"
Write-Host ''
Write-Host 'To read it here, gunzip it, or in Python:'
Write-Host " import gzip, shutil; shutil.copyfileobj(gzip.open(r'backups\$file'), open('read-only.db','wb'))"
}
}