数据库设计
本文档基于以下源文件编写：
- internal/database/database.go -- DB 接口定义
- internal/database/sqlite.go -- SQLiteDB 实现、连接池、goose 迁移
- internal/database/unit_of_work.go -- UnitOfWork 事务模式
- internal/database/errors.go -- 统一错误哨兵
- internal/database/filters.go -- 动态过滤与排序白名单
- internal/database/song_repository.go -- 歌曲仓储（最大的 Repository）
- internal/database/playlist_repository.go -- 歌单仓储
- internal/database/playlist_song_repository.go -- 歌单-歌曲关联仓储
- internal/database/config_repository.go -- 配置项仓储
- internal/database/token_repository.go -- 认证令牌仓储
- internal/database/jsplugin_repository.go -- JS 插件仓储
- internal/database/play_history_repository.go -- 播放历史仓储
- internal/database/song_tag_repository.go -- 自定义标签仓储
- internal/database/theme_pack_repository.go -- 主题包仓储
- internal/database/song_artist_repository.go -- 歌手关联仓储
- internal/database/testutil/memdb.go -- 内存数据库测试工具
- internal/database/migrations/0001_init.sql -- 完整初始 schema（6 表 + 索引 + 触发器 + 预置数据）
- internal/database/migrations/0002_*.sql ~
0039_*.sql-- 增量迁移（含 0016 plugin_storage、0030 play_history、0031 theme_packs、0036 song_tags、0038 song_artists 等新表） - internal/database/queries/*.sql -- sqlc 查询定义（11 文件）
- sqlc.yaml -- sqlc 代码生成配置
目录
1. 简介
Songloft 的数据层基于 SQLite，采用 WAL（Write-Ahead Logging）模式实现读写并发。整个数据访问栈由四个层次组成：goose 嵌入式迁移管理 schema 演进、sqlc 从注释化 SQL 生成类型安全的 Go 代码处理固定查询、squirrel 构建动态 WHERE/ORDER 条件、Repository + UnitOfWork 封装业务级数据操作与跨表事务。
数据库驱动使用 modernc.org/sqlite（纯 Go 实现），无需 CGO，支持 CGO_ENABLED=0 静态编译，适配 Docker scratch/alpine 等极简镜像。
章节来源: internal/database/sqlite.go（Open 函数、DSN 参数）、AGENTS.md（数据库规范）
2. 数据库架构概览
系统包含 13 张业务表（songs、playlists、playlist_songs、play_history、configs、auth_tokens、js_plugins、plugin_storage、theme_packs、song_tags、song_tag_links、artists、song_artists），形成以歌曲（songs）和歌单（playlists）为核心的数据模型。
连接配置（sqlite.go Open 函数）：
| 参数 | 值 | 说明 |
|---|---|---|
journal_mode | WAL | 读写并发，读不被写阻塞 |
busy_timeout | 10000ms | 遇锁等待 10 秒，避免 SQLITE_BUSY |
synchronous | NORMAL | WAL 模式下已足够安全 |
cache_size | 10000 页 (~40MB) | 页缓存 |
foreign_keys | 1 | 启用外键约束（CASCADE 依赖此项） |
Go 连接池：MaxOpenConns=10、MaxIdleConns=5、ConnMaxLifetime=30min。
章节来源: internal/database/sqlite.go:28-55
3. ER 关系图
图表来源: internal/database/migrations/0001_init.sql、0007_*.sql ~ 0038_*.sql（plugin_storage 见 0016、play_history 见 0030、theme_packs 见 0031、song_tags 见 0036、artists/song_artists 见 0038）
4. 表结构详解
4.1 songs -- 歌曲表
存储本地、远程、电台三种类型的歌曲元数据，是系统最大的表（42 列）。
| 字段 | 类型 | 说明 |
|---|---|---|
id | INTEGER PK | 自增主键 |
type | TEXT, CHECK | local / remote / radio |
title / artist / album | TEXT | 基础元数据 |
duration | REAL | 时长（秒） |
file_path | TEXT | 本地文件路径（仅 local） |
url | TEXT | 远程/电台 URL |
cover_path / cover_url | TEXT | 封面本地路径 / 远程 URL |
lyric | TEXT | 歌词 JSON（LyricPayload） |
lyric_source | TEXT, CHECK | file / embedded / scraped / url / cached / manual |
lyric_remote_url | TEXT | 歌词远程 URL（迁移 0003） |
file_size / format / bit_rate / sample_rate | 混合 | 音频技术参数 |
is_live | INTEGER | 是否直播流（0/1） |
plugin_entry_path | TEXT | 来源插件标识 |
source_data | TEXT | 插件特有的源数据 |
dedup_key | TEXT | 去重键（同插件下唯一） |
year / genre | INTEGER / TEXT | 年份 / 流派（迁移 0007） |
fingerprint / fingerprint_duration | TEXT / REAL | 音频指纹（迁移 0008） |
isrc | TEXT | 国际标准录音编码（迁移 0009） |
cache_path | TEXT | 远程歌曲本地缓存路径（迁移 0014） |
cue_source_path / cue_track_index / cue_audio_path | TEXT / INTEGER / TEXT | CUE 分轨来源（迁移 0018） |
file_modified_at | DATETIME | 文件修改时间，扫描增量判定用（迁移 0019） |
track | TEXT | 音轨号（迁移 0020） |
language / style | TEXT | 语言 / 风格（迁移 0022） |
is_video | INTEGER | 是否视频（0/1，迁移 0024） |
cue_start_seconds / cue_end_seconds | REAL | CUE 分轨播放起止秒数（迁移 0027） |
fingerprint_attempted_at | INTEGER | 指纹计算已尝试标记（unix 秒，0=未尝试，迁移 0029） |
fingerprint_error | TEXT | 指纹提取失败错误信息（迁移 0037） |
added_at / updated_at | DATETIME | 入库 / 更新时间（触发器维护） |
关键设计：(plugin_entry_path, dedup_key) 联合唯一索引（部分索引，WHERE dedup_key != ''）用于远程歌曲按插件去重。UpsertRemote 利用此索引实现"已存在则更新、不存在则插入"。
章节来源: internal/database/migrations/0001_init.sql、0005_lyric_source_manual.sql（重建表）、0007~0024 增量 ALTER、internal/database/song_repository.go
4.2 playlists -- 歌单表
| 字段 | 类型 | 说明 |
|---|---|---|
id | INTEGER PK | 自增主键 |
type | TEXT, CHECK | normal / radio |
name | TEXT, UNIQUE | 歌单名（全局唯一） |
description | TEXT | 描述 |
cover_path / cover_url | TEXT | 封面 |
labels | TEXT | 标签 JSON 数组（built_in / auto_created） |
sort_by | TEXT | 视图排序字段（默认 position，迁移 0034） |
sort_order | TEXT | 视图排序方向（默认 asc，迁移 0034） |
position | INTEGER | 排序位置 |
pinned_at | DATETIME | 置顶时间（nullable，迁移 0035） |
created_at / updated_at | DATETIME | 时间戳 |
预置数据：id=1「收藏」（labels=["built_in"]）、id=2「电台收藏」。labels 通过 json_each() 查询，如批量删除时跳过内置歌单。
章节来源: internal/database/migrations/0001_init.sql:30-42,163-166
4.3 playlist_songs -- 歌单-歌曲关联表
| 字段 | 类型 | 说明 |
|---|---|---|
id | INTEGER PK | 自增主键 |
playlist_id | INTEGER FK | → playlists(id) ON DELETE CASCADE |
song_id | INTEGER FK | → songs(id) ON DELETE CASCADE |
position | INTEGER | 歌曲在歌单内的排序位置 |
added_at | DATETIME | 加入时间 |
UNIQUE(playlist_id, song_id) 防止重复。双向 CASCADE 确保删除歌曲/歌单时自动清理。
4.4 configs -- 配置表
key TEXT UNIQUE + value TEXT（通常 JSON）+ updated_at（触发器维护）。写入使用 UPSERT：INSERT ... ON CONFLICT(key) DO UPDATE SET value = excluded.value。
预置 9 条配置：迁移 0001 写入 8 条（music_path、cover_storage_path、scan_config、ffprobe_path、jwt_secret（随机生成）、source_validation/source_fallback/source_metrics），迁移 0006 追加 plugin_registries。
4.5 auth_tokens -- 认证令牌表
存储 JWT access/refresh token 记录。token_id UNIQUE，revoked_at NULLABLE（NULL 表示未撤销）。撤销判定：revoked_at IS NOT NULL OR expires_at < NOW。CleanExpired 定期清理过期记录。
4.6 js_plugins -- JS 插件表
存储已安装插件的元数据（23 列）。entry_path UNIQUE 作为路由标识。permissions、public_paths、external_paths 三个字段为 JSON 数组。status CHECK 约束 active/inactive/error。
章节来源: internal/database/migrations/0001_init.sql、0010_*.sql、0011_*.sql、0013_*.sql
4.7 plugin_storage -- 插件持久化 KV 表
供 JS 插件通过 storage Bridge API 读写的键值存储（迁移 0016 新增）。plugin_entry_path + key 组成 UNIQUE 约束，实现按插件隔离的命名空间。value TEXT（通常 JSON），created_at / updated_at 时间戳。查询定义在 internal/database/queries/plugin_storage.sql。
| 字段 | 类型 | 说明 |
|---|---|---|
id | INTEGER PK | 自增主键 |
plugin_entry_path | TEXT | 插件标识（命名空间） |
key | TEXT | 键 |
value | TEXT | 值（默认 ''，通常 JSON） |
created_at / updated_at | DATETIME | 时间戳 |
章节来源: internal/database/migrations/0016_plugin_storage.sql、internal/database/queries/plugin_storage.sql
4.8 play_history -- 播放历史表
按「播放上下文」记录最近播放过的歌曲（迁移 0030）。上下文由 (context_type, context_key) 二元组标识：歌单 ("playlist", "<ID>")、自定义标签 ("tag", "<ID>")、分面维度 ("artist", "...") 等。
| 字段 | 类型 | 说明 |
|---|---|---|
id | INTEGER PK | 自增主键 |
context_type | TEXT | 上下文类型（playlist / tag / artist / album / genre / year / decade / language / style） |
context_key | TEXT | 上下文键（歌单 ID 或分面取值） |
song_id | INTEGER FK | → songs(id) ON DELETE CASCADE |
played_at | DATETIME | 播放时间 |
play_count | INTEGER | 累计播放次数 |
UNIQUE(context_type, context_key, song_id) 去重，写入用 upsert（ON CONFLICT DO UPDATE 刷新 played_at、累加 play_count）。索引 (context_type, context_key, played_at DESC, id DESC) 保证读取与裁剪顺序确定。
章节来源: internal/database/migrations/0030_play_history.sql
4.9 theme_packs -- 主题包表
声明式 JSON 主题包（迁移 0031）。raw_json 存储完整原始 JSON，前端直接读取渲染；顶层字段做索引/查询用。
| 字段 | 类型 | 说明 |
|---|---|---|
id | INTEGER PK | 自增主键 |
theme_id | TEXT, UNIQUE | 全局唯一标识 |
name | TEXT | 主题名称 |
version | TEXT | 版本号（默认 1.0.0） |
author | TEXT | 作者 |
description | TEXT | 描述 |
schema_version | INTEGER | 主题 JSON schema 版本（默认 1） |
raw_json | TEXT | 完整主题 JSON |
created_at / updated_at | DATETIME | 时间戳（updated_at 由 TRIGGER 维护） |
章节来源: internal/database/migrations/0031_theme_packs.sql
4.10 song_tags / song_tag_links -- 自定义标签表
用户自定义歌曲标签（迁移 0036）。song_tags 存储标签实体，song_tag_links 实现歌曲与标签的 M:N 多对多关联。
song_tags：id(PK), name(UNIQUE NOCASE), color, created_at。
song_tag_links：song_id(FK) + tag_id(FK) 复合主键，双向 CASCADE。idx_song_tag_links_tag 索引加速“某标签下所有歌曲”查询。
章节来源: internal/database/migrations/0036_song_tags.sql
4.11 artists / song_artists -- 歌手表
规范化多值歌手（迁移 0038）。artists 是歌手实体（按 normalized_key = lower(trim(name)) 去重），song_artists 是歌曲与歌手的 M:N 多对多关联。
artists：id(PK), name, normalized_key(UNIQUE), created_at。
song_artists：song_id(FK) + artist_id(FK) + role 复合主键，role CHECK(artist / album_artist)，position 排序位置。双向 CASCADE。迁移 0038 自动回填现有 songs.artist 整串为单行关联。
章节来源: internal/database/migrations/0038_song_artists.sql
5. 索引与约束设计
| 索引名 | 表 | 列 | 类型 | 说明 |
|---|---|---|---|---|
idx_songs_type | songs | type | 普通 | 按类型筛选 |
idx_songs_title | songs | title | 普通 | 标题搜索 |
idx_songs_artist | songs | artist | 普通 | 艺术家搜索 |
idx_songs_added_at | songs | added_at DESC | 普通 | 默认排序优化 |
idx_songs_file_path | songs | file_path | 普通 | 文件夹前缀 LIKE（迁移 0004） |
idx_songs_plugin_entry_path | songs | plugin_entry_path | 部分 | WHERE != '' |
idx_songs_dedup_key_unique | songs | (plugin_entry_path, dedup_key) | 唯一+部分 | 远程歌曲去重 |
idx_songs_fingerprint | songs | fingerprint | 部分 | 重复检测（迁移 0008） |
idx_playlists_name_unique | playlists | name | 唯一 | 歌单名全局唯一 |
idx_playlist_songs_position | playlist_songs | (playlist_id, position) | 普通 | 歌单内排序 |
idx_auth_tokens_* | auth_tokens | token_id/type/expires/revoked | 普通 | 各维度查询 |
idx_js_plugins_* | js_plugins | status/entry_path | 普通 | 状态和路径查询 |
idx_play_history_* | play_history | (context_type, context_key, played_at DESC, id DESC) | 普通 | 按上下文查询与裁剪（迁移 0030） |
idx_song_tag_links_tag | song_tag_links | tag_id | 普通 | 标签下歌曲查询（迁移 0036） |
idx_song_artists_* | song_artists | artist_id / song_id | 普通 | 歌手关联查询（迁移 0038） |
idx_playlist_songs_song_id | playlist_songs | song_id | 普通 | 歌曲所属歌单查询（迁移 0033） |
四张核心表（songs/playlists/configs/js_plugins）及 theme_packs 各有 AFTER UPDATE 触发器，自动维护 updated_at = CURRENT_TIMESTAMP。
章节来源: internal/database/migrations/0001_init.sql:106-160
6. 迁移系统
迁移文件通过 //go:embed migrations/*.sql 嵌入 Go 二进制，启动时 goose.Up 自动执行所有未应用的迁移，无需外部工具。
//go:embed migrations/*.sql
var migrationsFS embed.FS
func runMigrations(db *sql.DB) error {
goose.SetBaseFS(migrationsFS)
goose.SetDialect("sqlite3")
return goose.Up(db, "migrations")
}迁移文件列表（39 个）
| 编号 | 说明 |
|---|---|
| 0001 | 初始 schema：6 张表 + 索引 + 触发器 + 内置歌单 + 默认配置 |
| 0002 | 新增 scan_auto_create_include_subdirs 配置项 |
| 0003 | 新增 lyric_remote_url 列，裸 LRC 迁移为 JSON |
| 0004 | 新增 file_path 索引（文件夹前缀查询） |
| 0005 | 放宽 lyric_source CHECK 约束新增 manual（重建表） |
| 0006 | 预置官方插件注册表 URL |
| 0007 | 新增 year、genre 字段 |
| 0008 | 新增 fingerprint、fingerprint_duration + 部分索引 |
| 0009 | 新增 isrc 字段 |
| 0010 | js_plugins 新增 public_paths JSON 数组 |
| 0011 | js_plugins 新增 icon 字段 |
| 0012 | 新增 scan_auto_create_playlists 配置项 |
| 0013 | js_plugins 新增 external_paths JSON 数组 |
| 0014 | songs 新增 cache_path（远程歌曲本地缓存路径） |
| 0015 | 新增 scan playlist 模式配置 |
| 0016 | 新增 plugin_storage 表（插件 KV 存储） |
| 0017 | 修正 cover 存储路径 |
| 0018 | songs 新增 CUE 支持（cue_source_path/cue_track_index/cue_audio_path） |
| 0019 | songs 新增 file_modified_at |
| 0020 | songs 新增 track（音轨号） |
| 0021 | 扫描支持 .mov 格式 |
| 0022 | songs 新增 language、style |
| 0023 | 扫描支持 .mp4 格式 |
| 0024 | 视频支持：songs 新增 is_video |
| 0025 | 新增 facet 索引 |
| 0026 | 扫描支持 .mka 格式 |
| 0027 | songs 新增 cue_start_seconds/cue_end_seconds（CUE 实时分轨） |
| 0028 | 扩展扫描支持格式（opus/m4b/oga/mpg/mpeg/m4v/flv/wmv/rm/rmvb/3gp） |
| 0029 | songs 新增 fingerprint_attempted_at（指纹失败不重试） |
| 0030 | 新增 play_history 表（播放历史） |
| 0031 | 新增 theme_packs 表（主题包） |
| 0032 | js_plugins 新增 render_engine 字段（插件页渲染引擎声明） |
| 0033 | 新增 idx_playlist_songs_song_id 索引 |
| 0034 | playlists 新增 sort_by/sort_order（歌单视图排序偏好） |
| 0035 | playlists 新增 pinned_at（置顶时间） |
| 0036 | 新增 song_tags + song_tag_links 表（自定义标签） |
| 0037 | songs 新增 fingerprint_error（指纹提取错误信息） |
| 0038 | 新增 artists + song_artists 表（规范化多值歌手，含回填逻辑） |
| 0039 | 扫描支持 .dsf（DSD）格式，保留自定义配置并幂等追加 |
注意事项
- SQLite 不支持
ALTER TABLE ... MODIFY COLUMN或修改 CHECK 约束，因此迁移 0005 采用"重建表"策略：CREATE TABLE songs_new → INSERT INTO ... SELECT → DROP TABLE songs → ALTER TABLE songs_new RENAME TO songs → 重建全部索引和触发器。 - 每条 SQL 必须独立包裹在
-- +goose StatementBegin/-- +goose StatementEnd中，否则 SQLite 驱动在单次 Exec 里只会执行第一条 prepared statement，后续 SQL 静默丢失。 - 禁止直接
ALTER data/songloft.db，所有 schema 变更必须通过迁移文件完成。 - 迁移文件命名遵循
000N_描述.sql格式，goose 按数字顺序执行。
章节来源: internal/database/sqlite.go:16,58-67、internal/database/migrations/ 全部文件
7. sqlc 代码生成
sqlc.yaml 配置 engine=sqlite，从 queries/ 读取 SQL、从 migrations/ 推导 schema，生成到 internal/database/sqlc/。关键选项：emit_interface: true 生成 DBTX 接口使 *sql.DB 和 *sql.Tx 可互换；emit_empty_slices: true 空结果返回 []T 而非 nil。
11 个查询文件对应 11 张表：
| 文件 | 查询数 | 代表性查询 |
|---|---|---|
songs.sql | 38 | GetSongByID, CreateSong, UpdateRemoteSongMutable, FindSongByDedupKey, ListDuplicateFingerprints |
playlists.sql | 15 | GetPlaylistByID, CreatePlaylist, FindPlaylistByName, InsertAutoCreatedPlaylist |
playlist_songs.sql | 11 | AddSongToPlaylist, GetPlaylistSongs(Paginated), AddSongToPlaylistIgnore |
configs.sql | 3 | GetConfig, SetConfig (UPSERT), DeleteConfig |
tokens.sql | 5 | CreateToken, RevokeToken, IsTokenRevoked, CleanExpiredTokens |
js_plugins.sql | 8 | ListJSPlugins, CreateJSPlugin, UpdateJSPluginStatus, UpdateJSPluginHashes |
plugin_storage.sql | 7 | 插件 KV 读写（GetPluginStorage/SetPluginStorage 等） |
play_history.sql | 8 | RecordPlay (upsert), TrimPlayHistory, ListPlayHistory, ClearByPlaylist/ByTag |
song_tags.sql | 10 | CreateSongTag, LinkSongTag, UnlinkSongTag, GetTagsBySongID, CountSongsByTag |
theme_packs.sql | 5 | CreateThemePack, GetThemePackByThemeID, ListThemePacks, DeleteThemePack |
song_artists.sql | 7 | UpsertArtist, LinkSongArtist, GetArtistsBySongID, DeleteSongArtistsBySong |
sqlc 注释约定：:one 返回单行、:many 返回切片、:exec 只执行、:execrows 返回影响行数、:execlastid 返回 last insert ID。修改查询后须 make sqlc 并提交生成产物。
章节来源: sqlc.yaml、internal/database/queries/*.sql
8. Repository 模式
8.1 架构
每个 Repository 持有 sqlc.DBTX 接口，固定查询委托 *sqlc.Queries，动态查询用 squirrel。通过 SQLiteDB 工厂方法获取。
type SongRepository struct {
db sqlc.DBTX // *sql.DB 或 *sql.Tx
queries *sqlc.Queries // sqlc 生成的查询
}8.2 SongRepository -- 歌曲仓储
最大的仓储，涵盖歌曲全生命周期。关键方法：
| 方法 | SQL 方式 | 说明 |
|---|---|---|
GetByID / Create / Update / Delete | sqlc | 基础 CRUD |
UpdateLyrics / UpdateDuration / UpdateFingerprint | sqlc | 局部字段更新 |
UpsertRemote | sqlc | 按 dedup_key 去重写入远程歌曲 |
List / ListIDs / Count | squirrel | 动态过滤 + 排序 + 分页 |
BatchCreate / BatchDelete | 混合 | 批量操作，自动管理事务 |
ListLocalPaths | sqlc | 扫描去重用 |
ListDuplicateGroups | sqlc | 指纹重复检测 |
8.3 PlaylistRepository -- 歌单仓储
| 方法 | SQL 方式 | 说明 |
|---|---|---|
Create / Update | sqlc (事务) | 含同名冲突检测 |
List / Count | squirrel | LEFT JOIN 统计歌曲数 |
AutoCreate | 混合 (事务) | 按目录结构批量生成歌单 |
BatchDelete | 手工 SQL | 跳过 built_in 歌单 |
BatchUpdatePositions | sqlc (事务) | 批量更新排序 |
8.4 PlaylistSongRepository -- 关联仓储
管理歌单与歌曲的多对多关联。
| 方法 | SQL 方式 | 说明 |
|---|---|---|
AddSong / AddSongIgnore | sqlc | 添加歌曲，后者已存在时静默跳过 |
AddSongsBatch | sqlc (事务) | 批量追加，自动跳过已有，返回 (added, skipped) |
RemoveSong | sqlc | 移除歌曲 |
ReplaceSong | sqlc (事务) | 用新歌曲替换旧歌曲并保留 position |
GetSongs / GetSongsPaginated | sqlc | 按 position 排序获取歌曲 |
CountSongs / MaxPosition | sqlc | 统计和位置查询 |
BatchUpdatePositions | sqlc (事务) | 按给定顺序重写所有 position |
ListPlaylistsContainingSong | sqlc | 查找包含指定歌曲的所有歌单（转换服务用） |
8.5 ConfigRepository -- 配置仓储
| 方法 | SQL 方式 | 说明 |
|---|---|---|
Get | sqlc | 按 key 读取，未找到返回 ErrNotFound |
Set | sqlc | UPSERT 写入（INSERT ON CONFLICT DO UPDATE） |
Delete | sqlc | 按 key 删除 |
List / Count | squirrel | 关键词过滤 + 白名单排序 + 分页 |
8.6 TokenRepository -- 令牌仓储
| 方法 | SQL 方式 | 说明 |
|---|---|---|
Create | sqlc | 写入新令牌并回填 ID |
GetByID | sqlc | 按 token_id 查找 |
Revoke | sqlc | 标记为已撤销（设置 revoked_at） |
IsRevoked | sqlc | 判断是否撤销或过期 |
ListActive | squirrel | 列出活跃令牌（动态过滤 + 排序） |
CleanExpired | sqlc | 批量清理过期记录 |
8.7 JSPluginRepository -- 插件仓储
纯 sqlc 仓储，无动态 SQL 需求。
| 方法 | SQL 方式 | 说明 |
|---|---|---|
GetAll / GetByID / GetByEntryPath | sqlc | 查询（JSON 字段自动反序列化） |
Create / Update | sqlc | 全字段操作（JSON 字段序列化） |
Delete | sqlc | 删除 |
UpdateStatus | sqlc | 仅更新 status 字段 |
UpdateHashes | sqlc | 更新 zip_hash/entry_hash/file_mod_time |
8.8 PlayHistoryRepository -- 播放历史仓储
纯 sqlc 仓储，用于播放历史的 upsert、裁剪、查询与清理。
| 方法 | SQL 方式 | 说明 |
|---|---|---|
RecordPlay | sqlc | upsert 播放记录（ON CONFLICT 刷新 played_at、累加 play_count） |
TrimPlayHistory | sqlc | 按上下文裁剪至上限 |
ListPlayHistory | sqlc | 按上下文查询历史列表 |
ClearPlayHistory | sqlc | 清空指定上下文 |
ClearByPlaylist / ClearByTag | sqlc | 按歌单/标签清理 |
8.9 SongTagRepository -- 自定义标签仓储
混合 sqlc + squirrel。sqlc 处理基础 CRUD 和链接操作，squirrel 用于动态查询。
| 方法 | SQL 方式 | 说明 |
|---|---|---|
Create / GetByID / Update / Delete | sqlc | 标签 CRUD |
Link / Unlink / UnlinkAllBySong | sqlc | 歌曲-标签链接操作 |
GetTagsBySongID | sqlc | 查询歌曲的所有标签 |
ListSongs / ListSongIDs | squirrel | 指定标签下的歌曲动态查询 |
8.10 ThemePackRepository -- 主题包仓储
纯 sqlc 仓储，管理主题包的增删改查。
| 方法 | SQL 方式 | 说明 |
|---|---|---|
Create / Update | sqlc | 创建/更新主题包 |
GetByThemeID | sqlc | 按 theme_id 查询 |
List | sqlc | 列出所有主题包（按 created_at DESC） |
Delete | sqlc | 按 theme_id 删除 |
8.11 SongArtistRepository -- 歌手关联仓储
纯 sqlc 仓储，管理歌手实体与歌曲-歌手关联。
| 方法 | SQL 方式 | 说明 |
|---|---|---|
UpsertArtist | sqlc | 按 normalized_key 去重写入歌手 |
GetArtistByNormalizedKey / GetArtistByName | sqlc | 查询歌手 |
DeleteSongArtistsBySong | sqlc | 删除歌曲的所有歌手关联 |
LinkSongArtist | sqlc | 链接歌曲与歌手 |
GetArtistsBySongID | sqlc | 查询歌曲的所有歌手 |
8.12 错误语义
var ErrNotFound = errors.New("database: record not found") // sql.ErrNoRows 或 RowsAffected()==0
var ErrConflict = errors.New("database: conflict") // UNIQUE 约束冲突Service 层用 errors.Is 判断：ErrNotFound → HTTP 404，ErrConflict → 业务语义（如 ErrPlaylistNameConflict）。
章节来源: internal/database/errors.go、各 *_repository.go
9. UnitOfWork 事务模式
SQLite 单 writer 架构下，跨表写必须在同一 *sql.Tx 完成。UnitOfWork 将三个写密集型 Repository 绑定到同一事务：
type UnitOfWork struct {
Songs *SongRepository
Playlists *PlaylistRepository
PlaylistSongs *PlaylistSongRepository
}SQLiteDB.RunInTx 自动管理事务生命周期，fn 返回 error 时回滚，panic 时也回滚后重新 panic：
db.RunInTx(ctx, func(ctx context.Context, uow *database.UnitOfWork) error {
if err := uow.Songs.Create(ctx, newSong); err != nil {
return err
}
return uow.PlaylistSongs.ReplaceSong(ctx, playlistID, oldSongID, newSong.ID)
})部分 Repository 方法内部也需事务（如 Create 的同名检查 + INSERT）。通过 runInTx 辅助函数实现智能嵌套：底层是 *sql.DB 时自启动事务，底层已是 *sql.Tx 时直接复用。
章节来源: internal/database/unit_of_work.go、internal/database/sqlite.go:80-102
10. 动态 SQL 与过滤系统
Filter 结构体
每种实体定义对应的 Filter：SongFilter（Type/Keyword/PathPrefix/分页/排序）、PlaylistFilter（Type/Labels/Keyword）、ConfigFilter（Keyword）、TokenFilter（TokenType/IsActive）。
排序白名单
防止 SQL 注入，排序字段必须在白名单内，否则回退默认排序：
var songOrderWhitelist = map[string]struct{}{
"id": {}, "title": {}, "artist": {}, "album": {},
"duration": {}, "added_at": {}, "updated_at": {},
}applyOrder 统一处理排序逻辑，支持带表前缀的 JOIN 查询（如 "p." 用于 playlist 的 LEFT JOIN）。applyPagination 统一处理 LIMIT/OFFSET（limit<=0 视为不分页）。
squirrel 构建流程
sb := songSelectBuilder() // 基础 SELECT
sb = applySongFilter(sb, filter) // 动态 WHERE
sb = applyOrder(sb, ...) // 白名单排序
sb = applyPagination(sb, ...) // 分页
query, args, _ := sb.ToSql() // 生成 SQLPathPrefix 过滤使用 escapeLikeLiteral 转义通配符 + ESCAPE '\' 子句。歌单列表使用 LEFT JOIN 子查询统计歌曲数。
章节来源: internal/database/filters.go、internal/database/song_repository.go:280-311,448-464
11. 测试策略
testutil.OpenMemoryDB 提供零配置测试数据库：
func OpenMemoryDB(t *testing.T) *database.SQLiteDB {
db, err := database.Open(":memory:") // 自动执行全部 goose 迁移
if err != nil { t.Fatalf(...) }
t.Cleanup(func() { _ = db.Close() })
return db
}- 真实 SQLite
:memory:，不是 mock，schema 与生产完全一致 - 每个测试独立实例，无状态污染
- 禁止手写 mock DB，所有测试走真实 Repository
- 行数断言需扣除内置数据（2 条歌单 + 9 条 configs）
章节来源: internal/database/testutil/memdb.go、AGENTS.md
12. 设计决策与约定
不使用 ORM：固定查询 → sqlc 编译时类型检查；动态查询 → squirrel 显式构建（禁止字符串拼接）；跨表写 → RunInTx + UnitOfWork。
JSON 字段存储：SQLite TEXT 字段存 JSON，利用 json_each()/json_extract() 查询。Go 层在 Create/Update 时 json.Marshal，RowToModel 时 json.Unmarshal。涉及字段：playlists.labels、js_plugins.permissions/public_paths/external_paths、configs.value。
CASCADE 删除：playlist_songs 双向 CASCADE 是核心设计 -- 删除歌曲自动清理该歌曲在所有歌单中的记录，删除歌单自动清理该歌单下所有关联。Repository 方法的注释中明确标注这一依赖（如 SongRepository.Delete 注释："playlist_songs 由 FK ON DELETE CASCADE 自动清理"）。依赖 foreign_keys=1 PRAGMA 开启。
Service 层禁止直接操作事务：必须通过 DB.RunInTx 获取 UnitOfWork，禁止自行 BeginTx，避免 SQLite 单 writer 下的 SQLITE_BUSY 死锁。
封面引用计数：封面按内容哈希共享存储，SongRepository.CountCoverPathReferences 统计 songs + playlists 两表中引用同一 cover_path 的总行数，调用方在物理删除封面文件前必须确认计数为 0。
章节来源: AGENTS.md（数据库规范）、各 Repository 源码