[LR2] 最終プレイ日時を記録できるようにし、最終プレイ日ソートとして活用する方法後編 +タグ付けで選曲画面で最終プレイ日を確認する方法 [BeMusicSeeker]

2024年9月13日 20:22

前提条件

LR2(Lunatic Rave 2) + BeMusicSeeker という環境で遊んでいること
「PowerShell 7」が使えること ※ スクリプトは7環境で作ったのでPowerShell 5.1だと動かない可能性が高いです
以下の前編記事での作業を終わらせていること(sqlite3.exeは紛失していませんか？)

(Windows標準環境ならだれでも簡単に行えることを目指しましたがPowerShell 7はまだ普及していないようです…)

PowerShell 7について

おそらく世の中のWindowsでPowerShellを実行すると以下のような画面が出るでしょう。これは、PowerShellをバージョンアップしろという意味ではなく、新しく「PowerShell 7」を作ったのでそれを入れてくださいという意味です。現在はPowerShell 5系と7系は別のアプリとして共存していますが、さすがに今後開発されるPowerShell資産は7系が主流になるでしょう。

PowerShell 7の入手方法

さすがにここに細かく書くのは話が逸れすぎるので、以下を参考に適当にググって入れてください。LR2とかbeatorajaの導入に比べたら簡単です。.ps1ファイルをダブルクリックで起動できる状態にしておくと便利だと思います。

おすすめはMicrosoftも推奨しているWingetを使う方法です。今どきのWindowsならWingetは使えるはずですが、ダメな場合は、以下を参照してください。「アプリインストーラー」の導入に見えますが、wingetはアプリインストーラー内に含まれているものなのです。

それでもwingetが使えない場合は環境変数PATHがおかしくなっていると思うので、PATHに「%LOCALAPPDATA%\Microsoft\WindowsApps」を追加すればwingetが使えると思います。

最終プレイ日でソートするカスタムフォルダを作るPowerShellスクリプト

難易度表のレベル毎にカスタムフォルダを出力し、フォルダ内では最終プレイ日(lastplay)でソートされるものを自動で出力するスクリプトです。

ついでに、minbp順やmaxbpm順のフォルダも出力するようにしています。
不要な方はスクリプトを編集して削ってください。$ORDERSの要素を消したし足したりすればOKです。SQLが分かる人はカスタマイズしてみると良いでしょう。

スクリプト冒頭にある3つの定数は環境に合わせて書き換えてください
$SQLITE_PATH "sqlite3.exeの場所" ←前編でダウンロードしたやつです
$SONGDB_PATH = "song.dbの場所"
$OUT_DIR = "カスタムフォルダの出力先"

2024-09-15: 「# 設定が有効か確認」の部分で設定値の不備を検出できていなかったので修正しました。本筋の動作に影響のある修正ではありません。

# これは「PowerShell 7」で動作確認したスクリプトです

# 設定用
$SQLITE_PATH = "D:\BIN\SQLite\sqlite-tools-win-x64-3460100\sqlite3.exe" # 環境変数PATHを設定しているなら"sqlite3"みたいな指定方法でもOK
$SONGDB_PATH = "D:\LR2beta3\LR2files\Database\song.db"
$OUT_DIR = "${PSScriptRoot}\カスタムフォルダ" #カスタムフォルダの出力先、.ps1ファイル自体の場所にある「カスタムフォルダ」

# どのようなソート順のカスタムフォルダを難易度表別に作っていきたいかの定義、難易度表のレベル毎にこの定義でソートされるカスタムフォルダを出力する
$ORDERS = @(
  @{SORT_NAME = "最終プレイ順"; ORDER_BY = "ORDER BY (SELECT lastplay FROM history WHERE hash = song.hash) ASC" },
  @{SORT_NAME = "minbp順"; ORDER_BY = "ORDER BY minbp ASC" },
  @{SORT_NAME = "maxbpm順"; ORDER_BY = "ORDER BY maxbpm" }
)

# 設定が有効か確認
try {
  @($SQLITE_PATH, $SONGDB_PATH) | ForEach-Object { Get-Command $_ -ErrorAction Stop } | Out-Null
  @($OUT_DIR) | ForEach-Object { if (!(Test-path -LiteralPath $_ -PathType Container)) { throw } } | Out-Null
}
catch {
  Write-Host "[Error] 設定値がおかしいです" -BackgroundColor Red
  Write-Host "スクリプトが完了しました。Enterキーを押すとウィンドウを閉じます。"; Read-Host
  exit
}

# フォルダ名に使えない文字を削除する関数
function Remove-InvalidFileNameChars {
  param ($name)
  # Windowsでファイル名に使えない文字を削除
  return $name -replace '[\/:*?"<>|]', ''
}

# カスタムフォルダにするフォルダのリストを得るクエリ
$SQL_QUERY = @"
-- playlist.folder_orderを基にカスタムフォルダの出力順を求めるための共通テーブル式
WITH folder_order_index AS (
  SELECT
    playlist_id AS 'foi_playlist_id',
    json_each.value AS 'foi_folder',
    json_each.rowid AS 'fo_index'
  FROM
    playlist,
    json_each(playlist.folder_order)
)
SELECT
  playlist_id, -- BeMusicSeekerで作られる表のid
  folder,      -- 表内の各難易度のフォルダ
  name         -- 表の名前
FROM
  playlist_entry
  LEFT OUTER JOIN playlist USING(playlist_id)
  LEFT OUTER JOIN folder_order_index ON playlist_id = foi_playlist_id AND folder = foi_folder
WHERE
  is_removed = 0       -- BeMusicSeekerではこれで論理削除しているので、削除されていないもののみを対象とする
  AND md5 IS NOT NULL  -- bmsonや査定中の譜面の場合にNULLの場合がよくあるのでこれは除外する
GROUP BY playlist_id, folder
ORDER BY playlist_id, CASE WHEN fo_index IS NULL THEN 1000 ELSE fo_index END
;
"@

# フォルダ順定義数、実際にあるフォルダ数を取得するクエリ
# (フォルダ順の定義があったとしても、実際のフォルダ数の方が多い場合はどうしようかと考えたが、フォルダ順定義がある分はフォルダ順定義を使って、NULLの場合は後ろに来るようにすることにした)
$SQL_QUERY2 = @"
WITH folder_order_index AS (
  SELECT
    playlist_id AS 'foidx_id',
    json_each.value AS 'folder',
    json_each.rowid AS 'fo_index'
  FROM
    playlist,
    json_each(playlist.folder_order)
)
, folder_order_count AS (
  SELECT
    foidx_id AS 'playlist_id',
    count(*) AS 'focnt'
  FROM folder_order_index
  GROUP BY foidx_id
)
, folder_count AS (
  SELECT
    playlist_id, 
    count(*) AS 'fcnt' 
  FROM (SELECT DISTINCT playlist_id, folder FROM playlist_entry WHERE is_removed = 0 AND md5 IS NOT NULL)
  GROUP BY playlist_id
)
SELECT
  playlist_id, -- BeMusicSeekerで作られる表のid
  focnt,       -- フォルダ順定義数
  fcnt         -- フォルダ数
FROM
  playlist_entry
  LEFT OUTER JOIN playlist USING(playlist_id)
  LEFT OUTER JOIN folder_order_index USING(folder)
  LEFT OUTER JOIN folder_order_count USING(playlist_id)
  LEFT OUTER JOIN folder_count USING(playlist_id)
WHERE
  is_removed = 0       -- BeMusicSeekerではこれで論理削除しているので、削除されていないもののみを対象とする
  AND md5 IS NOT NULL  -- bmsonや査定中の譜面の場合にNULLの場合がよくあるのでこれは除外する
GROUP BY playlist_id
ORDER BY playlist_id
;
"@

# #COMMANDとは以下のSQLの{COMMAND}部分を指す(LR2のhistory.txt:992行目より)
# SELECT * FROM song LEFT JOIN score ON song.hash = score.hash WHERE {COMMAND}

# .lr2folderテンプレートの定義
$LR2FOLDER_TEMPLATE = @"
#COMMAND song.hash in (SELECT md5 FROM playlist_entry WHERE playlist_id = {playlist_id} AND folder = '{folder}' AND is_removed = 0) {order_by}
#MAXTRACKS 0
#CATEGORY {name} ({sort_name})
#TITLE {folder} ({sort_name})
#INFORMATION_A 
#INFORMATION_B 
"@

# 以下メイン処理

# SQLite3 コマンドを実行し、結果を取得
Write-Host "[Info] song.dbから表の情報を取得中"
[console]::OutputEncoding = [System.Text.Encoding]::UTF8 # sqliteからの出力をUTF8して解釈するための設定
$folders = $SQL_QUERY | & $SQLITE_PATH -csv -header $SONGDB_PATH | ConvertFrom-Csv
$focnt_fcnt = $SQL_QUERY2 | & $SQLITE_PATH -csv -header $SONGDB_PATH | ConvertFrom-Csv
$folderCount = $folders.Count

# $ORDERSに定義されている分カスタムフォルダを作っていく
foreach ($ORDER in $ORDERS) {
  # プレイリストループ
  $totalFolder_cnt = 0
  foreach ($table in $focnt_fcnt) {
    # プレイリスト内のフォルダを取り出す
    $tableFolders = $folders | Where-Object { $_.playlist_id -eq $table.playlist_id }
    # folder_orderが完全に未定義の場合、フォルダ名で自然順ソートする(自然順参考文献:https://qiita.com/Variablob/items/3299333c143c193756d8)
    if (!$table.focnt) {
      $tableFolders = $tableFolders | Sort-Object { [regex]::Replace($_.folder, '\d+', { $args[0].Value.PadLeft(20) }) }
    }
    # プレイリスト内ループ
    $lr2folder_cnt = 0
    foreach ($tableFolder in $tableFolders) {
      # "表の名前+ソート名"を出力先フォルダとする
      $tablePath = Join-Path -Path "${OUT_DIR}\$($ORDER.SORT_NAME)" -ChildPath (Remove-InvalidFileNameChars $tableFolder.name)
      # 表のフォルダが存在しない場合は作成する
      New-Item -Path $tablePath -ItemType Directory -Force | Out-Null
      # .lr2folderのファイル名を設定
      $fileName = "{0:D4}.lr2folder" -f $lr2folder_cnt
      # テンプレートのプレースホルダーを置換
      $lr2folderContent = $LR2FOLDER_TEMPLATE -replace "{playlist_id}", $tableFolder.playlist_id `
        -replace "{folder}", $tableFolder.folder `
        -replace "{name}", $tableFolder.name `
        -replace "{order_by}", $ORDER.ORDER_BY `
        -replace "{sort_name}", $ORDER.SORT_NAME
      # ファイルに保存
      $filePath = Join-Path -Path $tablePath -ChildPath $fileName
      $lr2folderContent | Out-File -LiteralPath $filePath -Encoding "shift_jis"
      $lr2folder_cnt++
      $totalFolder_cnt++
      Write-Host "[Info] ${totalFolder_cnt}/${folderCount} : $($ORDER.SORT_NAME) : Table : $($tableFolder.name), Folder : $($tableFolder.folder)"
    }
  }
}

Write-Host "[Info] カスタムフォルダの出力が完了しました"

Write-Host "スクリプトが完了しました。Enterキーを押すとウィンドウを閉じます。"; Read-Host

タグ付けを利用して最終プレイ日を選曲画面で確認する方法

まず、多くのスキンではタグはそんなに目立つところには表示されていないと思うので以下のページを参考にスキンを弄ると良いと思います。

そして、以下はタグ付け用のSQLです。上述のHexさんのものを参考に、前編で用意したlastplayの情報も表示できるように改変してあります。
前編でも使用したDB Browser for SQLiteを使用すると良いでしょう。スコア用のDBはscoreという名前でATTACHされている前提です。前編での作業時に「LR2.sqbpro」としてATTACH状態のプロジェクトを保存していればそれを使ってください。保存していなかったら「ATTACH DATABASE 'スコアDBの場所.db' AS score;」を実行してからタグ付けSQLを実行してください。
実行後変更を書き込みボタンを押さないとsong.dbに書き込まれません。
既存のタグ情報は全て削除されるのでご注意ください。

--sqliteにメモリを使ってもらってディスクIOを減らす設定、1GBとしておく
PRAGMA mmap_size=1000000000;
--タグ付け
UPDATE song SET tag = (
  --難易度表搭載状況
  ifnull((
    SELECT
      group_concat(symbol||replace(folder,compat_prefix,''), ',')
    FROM
      playlist_entry INNER JOIN playlist USING(playlist_id)
    WHERE
      hash = md5
      AND is_removed = 0 --playlist_entryの削除フラグが立っていないものを対象とする
  ),'表外')
  ||'|'||
  --最終プレイ日
  ifnull((
    SELECT
      substr(date(history.lastplay,'unixepoch'), 3)  --日付の形式は'22-07-14'のように年を下2桁だけ表示する(1000-9999年まで機能)
    FROM
      history
    WHERE
      song.hash = history.hash
  ),'未')
);

私の選曲画面では以下のように表示されます。タグの末尾に24-09-13と最終プレイ日が表示されています。収録されている難易度表が多いことも一目でわかりますし、AI難易度表では★2になっていることなどもわかります。

タグ付け用のPowerShellスクリプト

いちいちDB Browser for SQLiteからSQLを実行するのは面倒なのでタグ付け用のPowerShellスクリプトを用意しました。
スクリプト冒頭にある3つの定数は環境に合わせて書き換えてください。
2024-09-15: 「# 設定が有効か確認」の部分で設定値の不備を検出できていなかったので修正しました。本筋の動作に影響のある修正ではありません。

# これは「PowerShell 7」で動作確認したスクリプトです

# 設定用
$SQLITE_PATH = "D:\BIN\SQLite\sqlite-tools-win-x64-3460100\sqlite3.exe" # 環境変数PATHを設定しているなら"sqlite3"みたいな指定方法でもOK
$SONGDB_PATH = "D:\LR2beta3\LR2files\Database\song.db"
$SCOREDB_PATH = "D:\LR2beta3\LR2files\Database\Score\NEETED.db"

# 設定が有効か確認
try {
  @($SQLITE_PATH, $SONGDB_PATH, $SCOREDB_PATH) | ForEach-Object { Get-Command $_ -ErrorAction Stop } | Out-Null
}
catch {
  Write-Host "[Error] 設定値がおかしいです" -BackgroundColor Red
  Write-Host "スクリプトが完了しました。Enterキーを押すとウィンドウを閉じます。"; Read-Host
  exit
}

# 難易度表を絞りたい場合(▽,▼,☆,★,★★,sl,st辺りのみにしたいなど)はWHERE句で制限すると良いでしょう
# AI難易度表(https://bms.hexlataia.xyz/tables/ai.html)を入れておくと便利でしょう
$SQL_QUERY = @"
--sqliteにメモリを使ってもらってディスクIOを減らす設定、1GBとしておく
PRAGMA mmap_size=1000000000;
--スコアDBをアタッチする
ATTACH DATABASE '${SCOREDB_PATH}' AS score;
--タグ付け
UPDATE song SET tag = (
  --難易度表搭載状況
  ifnull((
    SELECT
      group_concat(symbol||replace(folder,compat_prefix,''), ',')
    FROM
      playlist_entry INNER JOIN playlist USING(playlist_id)
    WHERE
      hash = md5
      AND is_removed = 0 --playlist_entryの削除フラグが立っていないものを対象とする
  ),'表外')
  ||'|'||
  --最終プレイ日
  ifnull((
    SELECT
      substr(date(history.lastplay,'unixepoch'), 3)  --日付の形式は'22-07-14'のように年を下2桁だけ表示する(1000-9999年まで機能)
    FROM
      history
    WHERE
      song.hash = history.hash
  ),'未')
);
DETACH DATABASE score; 
"@

Write-Host "タグ付け実行中…"

$executionTime = Measure-Command {
  $SQL_QUERY | & $SQLITE_PATH $SONGDB_PATH
}

Write-Host "$($executionTime.TotalMilliseconds)msでタグ付けが完了しました"

Write-Host "スクリプトが完了しました。Enterキーを押すとウィンドウを閉じます。"
Read-Host

タグ付け時の裏技

タグ付け時のgroup_concat()で結合される順番は、以下のようにORDER BYで設定することができます。例えば「後からいれたAI難易度表のシンボルを手前に持ってきたい」といったときに使えます。これをやらない場合、playlist_id順に結合された結果が得られることになります。
どの難易度表がどのplaylist_idかというのは各自の環境によって違うので、playlistテーブルを見てみると良いでしょう。

group_concat(
  symbol||replace(folder,compat_prefix,'')
  ORDER BY
    -表の並びを補正、☆の後に★が来るようになど
    CASE playlist_id
        WHEN 7   THEN 1
        WHEN 18  THEN 2
        WHEN 8   THEN 3
        WHEN 9   THEN 4
        WHEN 12  THEN 5
        WHEN 13  THEN 6
        WHEN 10  THEN 7
        WHEN 11  THEN 8
        WHEN 17  THEN 9
        WHEN 16  THEN 10
        WHEN 19  THEN 11
        WHEN 321 THEN 12
        ELSE playlist_id + 100 --並びが未定義の場合後ろに来るように+100
    END
)

この記事が気に入ったらサポートをしてみませんか？

[LR2] 最終プレイ日時を記録できるようにし、最終プレイ日ソートとして活用する方法 後編 +タグ付けで選曲画面で最終プレイ日を確認する方法 [BeMusicSeeker]