bvandeusenandClaude Opus 5.5
1d4ef1a23f
Rewrites the internal/db/queries comments to rule 32 and regenerates dbq, whose doc comments and embedded SQL carry them. The bugs behind the tiered mixes, the md5 order seed, the score bands and the round-robin arms are restated as the constraint each one enforces; counts from one library and stale cross-references are gone. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
46 lines
1.8 KiB
SQL
46 lines
1.8 KiB
SQL
-- name: GetLibrarySettings :one
|
|
SELECT * FROM library_settings WHERE id = true;
|
|
|
|
-- name: UpdateLibraryScanPaths :one
|
|
UPDATE library_settings
|
|
SET scan_paths = sqlc.arg(scan_paths)::text[],
|
|
updated_at = now()
|
|
WHERE id = true
|
|
RETURNING *;
|
|
|
|
-- A library folder that moved: every path under old_root is rewritten
|
|
-- to the same place under new_root, so the tracks keep their ids and with them
|
|
-- their plays, likes and playlists. updated_at is left alone on purpose: the
|
|
-- scan compares it with each file's mtime, and the files have not changed.
|
|
|
|
-- name: RerootTrackPaths :execrows
|
|
UPDATE tracks
|
|
SET file_path = sqlc.arg(new_root)::text || substr(file_path, length(sqlc.arg(old_root)::text) + 1)
|
|
WHERE starts_with(file_path, sqlc.arg(old_root)::text || '/');
|
|
|
|
-- name: RerootAlbumCoverPaths :execrows
|
|
UPDATE albums
|
|
SET cover_art_path = sqlc.arg(new_root)::text || substr(cover_art_path, length(sqlc.arg(old_root)::text) + 1)
|
|
WHERE starts_with(cover_art_path, sqlc.arg(old_root)::text || '/');
|
|
|
|
-- name: RerootGrabDestinations :execrows
|
|
UPDATE grabs
|
|
SET destination = sqlc.arg(new_root)::text || substr(destination, length(sqlc.arg(old_root)::text) + 1)
|
|
WHERE destination = sqlc.arg(old_root)::text
|
|
OR starts_with(destination, sqlc.arg(old_root)::text || '/');
|
|
|
|
-- name: RerootImportRoot :execrows
|
|
UPDATE acquisition_settings
|
|
SET import_root = sqlc.arg(new_root)::text
|
|
WHERE import_root = sqlc.arg(old_root)::text;
|
|
|
|
-- name: SampleTrackPathsUnder :many
|
|
-- A few present tracks under a folder, to check a move before rewriting it.
|
|
SELECT file_path FROM tracks
|
|
WHERE starts_with(file_path, sqlc.arg(root)::text || '/') AND missing_since IS NULL
|
|
ORDER BY random()
|
|
LIMIT sqlc.arg(n)::int;
|
|
|
|
-- name: CountTracksUnder :one
|
|
SELECT count(*) FROM tracks WHERE starts_with(file_path, sqlc.arg(root)::text || '/');
|