07-database

データベース

このページは自動生成です。直接編集しないこと。
python tools/gen_manual_ref.py で再生成する。内容を変えたいときは下の生成元を直す。

生成元

ライブラリごとに 1 つの SQLite。テーブルの実体と移行の連鎖はここが真実。個々の列の意味は repository.rs の読み書き箇所を見る。

版

現在のスキーマ版は 67（PRAGMA user_version）。
起動時に migrate() が現在値を読み、足りない分の SQL を順に流す。既に最新なら追加インデックスの確認だけ行う。

テーブル（66）

移行中の作業テーブル（通常は残らない）: transcripts_new, voice_dom_playlist_items_new, voice_dom_playlists_new, voice_interactions_new, voice_product_actors_new, voice_product_actors_v63, voice_product_tags_new, voice_product_tags_v63, voice_products_old, voice_search_sessions_new

assets の列

列型 / 制約追加
idINTEGER PRIMARY KEY AUTOINCREMENTv1
sha256TEXT NOT NULL UNIQUEv1
pathTEXT NOT NULLv1
filenameTEXT NOT NULLv1
folderTEXT NOT NULLv1
file_sizeINTEGERv1
date_addedINTEGER NOT NULLv1
date_modifiedINTEGERv1
containerTEXTv1
codecTEXTv1
duration_secREALv1
sample_rateINTEGERv1
channelsINTEGERv1
bits_per_sampleINTEGERv1
titleTEXTv1
artistTEXTv1
albumTEXTv1
album_artistTEXTv1
track_numberTEXTv1
disc_numberTEXTv1
dateTEXTv1
genreTEXTv1
composerTEXTv1
commentTEXTv1
bpm_tagTEXTv1
key_tagTEXTv1
key_camelotTEXTv1
is_favoriteINTEGER NOT NULL DEFAULT 0v1
is_hiddenINTEGER NOT NULL DEFAULT 0v1
needs_content_hashINTEGER NOT NULL DEFAULT 0v1
bpm_resolvedREAL後続
key_resolved_camelotTEXT後続
energy_resolvedINTEGER後続
beat_grid_jsonTEXT後続
analysis_sourceTEXT後続
analysis_confidenceREAL後続
analyzed_atINTEGER後続
bit_rateINTEGER後続
has_drmINTEGER NOT NULL DEFAULT 0後続
compilationINTEGER NOT NULL DEFAULT 0後続
groupingTEXT後続
movement_titleTEXT後続
movement_numberTEXT後続
workTEXT後続
showTEXT後続
episode_numberTEXT後続
media_kindTEXT後続
descriptionTEXT後続
sort_titleTEXT後続
sort_artistTEXT後続
sort_albumTEXT後続
sort_album_artistTEXT後続
sort_composerTEXT後続
ratingINTEGER後続
is_lovedINTEGER NOT NULL DEFAULT 0後続
is_checkedINTEGER NOT NULL DEFAULT 1後続
issue_flagINTEGER NOT NULL DEFAULT 0後続
custom_1TEXT後続
custom_2TEXT後続
custom_3TEXT後続
custom_4TEXT後続
custom_5TEXT後続
last_playedINTEGER後続
play_countINTEGER NOT NULL DEFAULT 0後続
skip_countINTEGER NOT NULL DEFAULT 0後続
last_analyzed_atINTEGER後続
danceabilityREAL後続
mood_acousticINTEGER後続
mood_electronicINTEGER後続
mood_aggressiveINTEGER後続
mood_relaxedINTEGER後続
mood_happyINTEGER後続
mood_sadINTEGER後続
mood_partyINTEGER後続
timbreTEXT後続
transition_scoreREAL後続
mpmREAL後続
tuning_centsREAL後続
time_signatureTEXT後続
notesTEXT後続
valenceREAL後続
arousalREAL後続
mood_inferred_atINTEGER後続
fingerprint_blobBLOB後続
fingerprint_algorithmTEXT後続
fingerprint_versionINTEGER後続
fingerprint_durationREAL後続
fingerprint_computed_atINTEGER後続
acoustid_idTEXT後続
mbid_recordingTEXT後続
mbid_match_scoreREAL後続
mbid_resolved_atINTEGER後続
genre_ml_jsonTEXT後続
genre_musicbrainz_jsonTEXT後続
genre_lastfm_jsonTEXT後続
genre_discogs_jsonTEXT後続
genre_listenbrainz_jsonTEXT後続
genre_external_fetched_atINTEGER後続
genre_consensus_jsonTEXT後続
genre_consensus_computed_atINTEGER後続
genre_consensus_priority_hashTEXT後続
genre_ml_model_idTEXT後続
genre_ml_inferred_atINTEGER後続
is_sampleINTEGER NOT NULL DEFAULT 0後続
sample_categoryTEXT後続
sample_is_loopINTEGER後続
sample_top3_jsonTEXT後続
sample_inferred_atINTEGER後続
integrated_lufsREAL後続
true_peak_dbREAL後続
analysis_engine_versionTEXT後続
vocal_presenceREAL後続
is_instrumentalINTEGER NOT NULL DEFAULT 0後続
brightnessREAL後続
harmonicityREAL後続
noisinessREAL後続
rms_dbREAL後続
crest_dbREAL後続
dominant_noteTEXT後続
dominant_hzREAL後続
bit_depthINTEGER後続
dominanceREAL後続
mood_sourceTEXT後続
needs_content_hashINTEGER NOT NULL DEFAULT 0後続
dominant_centsINTEGER後続
sample_loop_distributionTEXT後続
voice_sort_album_artistTEXT後続
voice_sort_albumTEXT後続
voice_sort_rule_versionINTEGER NOT NULL DEFAULT 0後続
sectionTEXT後続
section_rule_versionINTEGER NOT NULL DEFAULT 0後続
missing_sinceINTEGER後続

マイグレーションの連鎖（64 段）

古い DB を開くと、user_version の値から下の段を順に適用して現在版まで持ち上げる。段番号が飛んでいるのは、複数版をまとめて 1 段で移行しているところ。

段SQL 定数備考
初期化 (v1)INIT_SQL_V1Phase 2c base schema (v1).
v1 → v2MIGRATE_V1_TO_V2_SQLv1 → v2 migration (ADR-0006).
v2 → v3MIGRATE_V2_TO_V3_SQLv2 → v3 migration (ADR-0010 metadata revision history).
v3 → v4MIGRATE_V3_TO_V4_SQLv3 → v4: feature vectors table (Phase 4d).
v4 → v5MIGRATE_V4_TO_V5_SQLv4 → v5: smart playlists (Phase 4f).
v5 → v6MIGRATE_V5_TO_V6_SQLv5 → v6: Russell valence / arousal (Phase 5b / 5e).
v6 → v9MIGRATE_V6_TO_V9_SQLv6 → v9: user cue points + A-B loops (Phase 6d). Intermediate v7/v8 reserved.
v9 → v10MIGRATE_V9_TO_V10_SQLv9 → v10: plugin registry (ADR-0014 D9, Phase 7a).
v10 → v11MIGRATE_V10_TO_V11_SQLv10 → v11: master plugin chains + slots (Phase 7d, ADR-0014 D9).
v11 → v12MIGRATE_V11_TO_V12_SQLv11 → v12: editor window positions + chain presets (Phase 7e, ADR-0014 D4/D10).
v12 → v13MIGRATE_V12_TO_V13_SQLv12 → v13: stem separation cache metadata (Phase 8b).
v13 → v14MIGRATE_V13_TO_V14_SQLv13 → v14: AcoustID fingerprint + MBID resolution (Phase 6g.1).
v14 → v15MIGRATE_V14_TO_V15_SQLv14 → v15: repair diagnostics + per-track chain override (Phase 8e).
v15 → v16MIGRATE_V15_TO_V16_SQLv15 → v16: export history (Phase 8d).
v16 → v17MIGRATE_V16_TO_V17_SQLv16 → v17: external genre sources + consensus (Phase 6g.5).
v17 → v18MIGRATE_V17_TO_V18_SQLv17 → v18: ML genre metadata + audio embeddings (Phase 5c.5).
v18 → v19MIGRATE_V18_TO_V19_SQLv18 → v19: DTM sample classification (Phase 5d).
v19 → v20MIGRATE_V19_TO_V20_SQLv19 → v20: UCS tags + project management (Phase 6f).
v20 → v21MIGRATE_V20_TO_V21_SQLv20 → v21: path inference cache + folder artwork blobs (Phase 6h.1).
v21 → v22MIGRATE_V21_TO_V22_SQLv21 → v22: DLsite / DMM online lookup cache (Phase 6h.2).
v22 → v23MIGRATE_V22_TO_V23_SQLv22 → v23: song structure segments (Phase 7g.5).
v23 → v24MIGRATE_V23_TO_V24_SQLv23 → v24: loudness metrics for replay normalization (ana-02).
v24 → v25MIGRATE_V24_TO_V25_SQLv24 → v25: analyzer engine version for outdated re-analyze.
v25 → v26MIGRATE_V25_TO_V26_SQLv25 → v26: advanced analysis (chords / vocal presence).
v26 → v27MIGRATE_V26_TO_V27_SQLv26 -> v27: multi-aspect similarity vectors.
v27 → v28MIGRATE_V27_TO_V28_SQLv27 -> v28: audio embedding version column (ai-01).
v28 → v29MIGRATE_V28_TO_V29_SQLv28 -> v29: Whisper transcripts + FTS5 + Japanese text embeddings (ai-05).
v29 → v30MIGRATE_V29_TO_V30_SQLv29 -> v30: content_kind on libraries + DoujinVoice schema (voice-01 / content-01 partial).
v30 → v31MIGRATE_V30_TO_V31_SQLv30 -> v31: voice-log-01 compatible tables for DOM-Alpha import (voice-03).
v31 → v32MIGRATE_V31_TO_V32_SQLv31 -> v32: voice-05 playback resume + per-library prefs.
v32 → v33MIGRATE_V32_TO_V33_SQLv32 -> v33: voice-06 subtitle file links per asset.
v33 → v34MIGRATE_V33_TO_V34_SQLv33 -> v34: voice-log-01 nullable dom_id for GUI-generated rows (partial unique indexes).
v34 → v35MIGRATE_V34_TO_V35_SQLv34 -> v35: voice-mig-01 snapshots for libraries that already applied v33 subtitle-only.
v35 → v36MIGRATE_V35_TO_V36_SQLv35 → v36: waveform cache index (.wfm files live under <library>/.melodear/waveforms/).
v36 → v37MIGRATE_V36_TO_V37_SQLv36 → v37: Sononym-like scalar facets (filter-panel-01).
v37 → v38MIGRATE_V37_TO_V38_SQLv37 -> v38: emotion-01 mood source and dominance storage.
v38 → v39MIGRATE_V38_TO_V39_SQLv38 -> v39: art-01 アートワーク有無の判定結果（負キャッシュ含む）。 サムネ実体は .melodear/artwork-cache.db（別ファイル・派生データ）に置き、 「ある/ない」の知識のみ melodear.db に残す（キャッシュ削除後も再構築対象が分かる）。
v39 → v40MIGRATE_V39_TO_V40_SQLv39 -> v40: spectrogram cache index (.spec files live under <library>/.melodear/spectrograms/).
v40 → v41MIGRATE_V40_TO_V41_SQLv40 -> v41: fast scan identity. New imports may use a path-derived placeholder sha and ask a background worker to backfill the real content hash later.
v41 → v42MIGRATE_V41_TO_V42_SQLv41 -> v42: ana-10a-C. dominant note の f0 セント偏差（-50..50、NULL 可）を保存。
v42 → v43MIGRATE_V42_TO_V43_SQLv42 -> v43: ana-10b-C. ループのスライス分類カテゴリ分布を JSON で保存 （[{"category":"kick","score":0.4},…]、NULL 可）。
v43 → v44MIGRATE_V43_TO_V44_SQLv43 -> v44: colab-1. ユーザーの分類修正レコード（ローカルプロトタイプ用）。
v44 → v45MIGRATE_V44_TO_V45_SQLv44 -> v45: album-level user rating, independent from per-track rating.
v45 → v46MIGRATE_V45_TO_V46_SQLv45 -> v46: lookup_metadata (source, work_code) must allow multiple assets (same work across tracks). Drop UNIQUE; keep non-unique lookup index. (voicelib-7)
v46 → v47MIGRATE_V46_TO_V47_SQLv46 -> v47: multi-site voice product codes + lookup image URL cache (voicelib-9).
v47 → v48MIGRATE_V47_TO_V48_SQLv47 -> v48: voice_dom_playlists.dom_id nullable for GUI-created playlists (partial unique index). Table name kept for DOM-Alpha; UI label is 「プレイリスト」. Pattern mirrors MIGRATE_V33_TO_V34_SQL (voicelib-10).
v48 → v49MIGRATE_V48_TO_V49_SQLv48 -> v49: lookup cache parser generation + voice_products.age_rating (voicelib-11). migrate() may apply this as a whole or column-by-column when CREATE already added one side.
v49 → v50MIGRATE_V49_TO_V50_SQLv49 -> v50: DLsite title-search miss negative cache (voicelib-15).
v50 → v51MIGRATE_V50_TO_V51_SQLv50 -> v51: title-search miss reason + sample asset (voicelib-18 failure inbox).
v51 → v52MIGRATE_V51_TO_V52_SQLv51 -> v52: title-search miss search_version (voicelib-19 negative-cache generation).
v52 → v53MIGRATE_V52_TO_V53_SQLv52 -> v53: circles.dlsite_maker_id (voicelib-21 circle-first resolve). Later moved to circle_codes and dropped in v65→v66 (fanzacircle-2).
v53 → v54MIGRATE_V53_TO_V54_SQLv53 -> v54: path-derived section cache for SQL sort (sort-1). SSOT remains the file path; these columns are a derived cache (not user-editable).
v54 → v55MIGRATE_V54_TO_V55_SQLv54 -> v55: mirror stamp on libraries (ADR-0033 / dbloc-1).
v55 → v56MIGRATE_V55_TO_V56_SQLv55 -> v56: folder_artwork content hash (fix-22 B-1). Blob export + VACUUM are not done here (too heavy) — maintenance path only.
v56 → v57MIGRATE_V56_TO_V57_SQLv56 -> v57: tagfix-1 CP932-as-Latin-1 mojibake repair (data only; no DDL). Applied in Rust via [crate::cp932_tag_repair::repair_cp932_tag_mojibake].
v57 → v58MIGRATE_V57_TO_V58_SQLv57 -> v58: air-5 manual track playlists (ordered; duplicates allowed).
v58 → v59MIGRATE_V58_TO_V59_SQLv58 -> v59: agefilter-1 age rating provenance + fetch timestamp.
v59 → v60MIGRATE_V59_TO_V60_SQLv59 -> v60: subschema-1 — transcripts PK (asset_id, source) + voice_annotations (ADR-0035 D1/D2). Recreates transcripts (SQLite cannot ALTER PRIMARY KEY). Backfills source from model_id: rows whose model_id starts with subtitle: (DIALOGUE_MODEL_SUBTITLE_PREFIX) become sidecar; all others become whisper. Rebuilds transcripts_fts with source UNINDEXED and regenerates rows from the new table.
v60 → v61MIGRATE_V60_TO_V61_SQLv60 -> v61: rebuild transcripts_fts with trigram tokenizer (CJK substring search). unicode61 treats unbroken Japanese as one token, so partial matches fail. Transcript rows themselves are unchanged — FTS only.
v62 → v63MIGRATE_V62_TO_V63_SQLmissing-1: ディスクに無いファイルを行削除せず missing_since で表す。
v63 → v64MIGRATE_V63_TO_V64_SQLworkcode-3: tags / actors をサイト別に持てるようにする。
v64 → v65MIGRATE_V64_TO_V65_SQLsegvec-1: speaker/emotion 区間埋め込みの器。既存 audio_embeddings は無変更で共存。
v65 → v66MIGRATE_V65_TO_V66_SQLv65 -> v66: circle_codes(circle_id, site, code) + copy DLsite maker ids, then DROP circles.dlsite_maker_id (fanzacircle-2 / workcode-1 同形。二重の正を作らない).
v66 → v67MIGRATE_V66_TO_V67_SQLv66 -> v67: materialize Voice Album Artist / Album sort keys (voicesort-2). NULL-tolerant; version 0 = not backfilled (ORDER BY falls back to path expr).

← リファレンス目次へ

Backlinks