たくさんの自由帳

批評空間を使ってエロゲソングのプレイリストを作りたい

投稿日 : 2024/1/8 | 0 日前

文字数(だいたい) : 13761

どうもこんばんわ。
初夢は悪夢でした。いや普段は起きたら忘れちゃうんですけどなんか覚えてた（？）

本題

Imgur

持ってるエロゲソング、いつもアルバムか歌手のプレイリストなので、もっとなんかいい感じのプレイリストを作りたい！！
メーカー別とか、年代別とか、OP / 挿入歌 / ED 別とかのプレイリストが欲しい！！！

しかし

手動で一つずつ入れていくのは確実に無理。
数が多すぎる。。。

ErogameScape

https://erogamescape.dyndns.org/

えろすけとか、批評空間とか呼ばれているこのサイト、エロゲの情報が集まってるサイトなんですが、
ありがたいことに！、SQLを書くことでサイトのデータベースに問い合わせることが出来ます。

https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/sql_for_erogamer_form.php

SELECT ml.*,    -- 曲テーブル
    gm.*,       -- ゲーム曲テーブル
    gl.*,       -- ゲームテーブル
    bl.*        -- メーカーテーブル
FROM musiclist ml
    INNER JOIN game_music gm ON ml.id = gm.music
    INNER JOIN gamelist gl ON gm.game = gl.id
    INNER JOIN brandlist bl ON gl.brandname = bl.id
WHERE name = '恋するMODE'; -- エロゲソング名

コレを使って、エロゲソング名からゲーム情報やメーカー情報をとって、プレイリストを作ろうと思います！！！

SQL フォーム

一部のテーブルは文書化されています。
https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/sql_for_erogamer_tablelist.php

が、全て文書化されているわけではないので、どうするのが正攻法かは知りませんが、PostgreSQLのテーブル一覧を吐き出すSQLを叩いて、それっぽいテーブルを適当にSELECT * FROM hogehoge LIMIT 10とかで見て、探す必要が多分あります？

今回のエロゲソング関連のテーブルはこんな感じになってます。

musiclist
- エロゲソングの情報が格納されています
gamelist
- エロゲソングがどのエロゲで使わたかの情報が格納されています
- 中間テーブルですね
gamelist
- エロゲ情報が格納されています

中間テーブルがあるので、一つのエロゲソングが複数のエロゲに使われてもちゃんと表現できるわけなんですね。
データベース設計難しそう

https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/music.php?music=1331

環境

スクレイピングができればなんでも良いはず。
私はKotlin（Java）でいきます

入ってる曲一覧が欲しい

さて、問題が。
パソコンに入ってる曲から曲一覧が無いと始まらないのですが、どうやって取ろうかなあ...

音楽ファイルを音楽フォルダから探す

音楽の入ってるフォルダを渡して、ファイル一覧を出すやつ。

fun getFileList(filePath: String): List<File> {
    // 音楽ファイル一覧
    val result = arrayListOf<File>()
    File(filePath)
        .listFiles()
        ?.forEach {
            // フォルダなら再帰的に
            // 拡張子 flac ならリストに追加 
            when {
                it.isDirectory -> result += getFileList(it.path)
                it.extension == "flac" -> result += it
            }
        }
    return result
}

ただ、これだとファイル名が曲名として使えるかは微妙なんですよね。
なんかインデックスがファイル名に入っちゃってるのと、なんか_で置換されちゃってるファイルもある。

やっぱ正規ルートはID3 タグ?を解析する...？
FFmpegについてくるffprobeがもしかするとID3 タグのパースに対応しているかも。

01-ダカーポ5～メグリメグル世界～.flac
02-どっち_.flac
03-哀しみリフレイン.flac
04-アイノキオク.flac
05-暁に祈りを.flac
06-キミが微笑むから.flac

Music Center から取り出す

私はMusic Center for PCという音楽プレイヤーを使ってるのですが、
tracks.dbファイルがこのアプリが認識している曲一覧を持っているファイルであることが分かりました。

パスはここで、拡張子こそSQLiteぽく見えますが、テキストファイルとして開くことが出来ます。

C:\Users\{ユーザー名}\AppData\Roaming\Sony\Music Center\db

Imgur

テキストの構造ですが、1行ごとがJSONになっているみたいなので、1行取り出して、JSONパーサーにかけると良いと思います。
kotlinx.serializationだとこんな感じ？

// kotlinx.serialization で JSON パース
private val json = Json

fun main(args: Array<String>) {

    // Music Center の tracks.db ファイルのパス
    val tracksDb = File("""C:\\Users\\takusan23\\Desktop\\Dev\\Kotlin\\ErogePlaylistMaker\\tracks.db""")
    // 1行ごとに JSON パーサーにかける
    tracksDb
        .readText()
        .lines()
        .filter { it.isNotEmpty() }
        .forEach {
            val jsonElement = json.decodeFromString<JsonElement>(it)
            println(jsonElement.jsonObject.entries)
        }

}

いいかんじ！！に出力されています！

Imgur

Android から取り出す

Android端末が音楽プレイヤーでAndroidの開発環境があれば、曲一覧の取得は多分コレが一番早いかもしれない。

// 曲一覧を問い合わせる
val result = context.contentResolver.query(
    MediaStore.Audio.Media.EXTERNAL_CONTENT_URI,
    arrayOf(MediaStore.MediaColumns.TITLE, MediaStore.MediaColumns.DISPLAY_NAME, MediaStore.MediaColumns.RELATIVE_PATH),
    null,
    null,
    null
)?.use { cursor ->
    // 配列にして返す
    cursor.moveToFirst()
    (0 until cursor.count)
        .map {
            // タイトルとファイル名、ファイルパスを取得
            // ファイルパスは sdcard/Music からの相対パス
            val title = cursor.getString(cursor.getColumnIndexOrThrow(MediaStore.MediaColumns.TITLE))
            val name = cursor.getString(cursor.getColumnIndexOrThrow(MediaStore.MediaColumns.DISPLAY_NAME))
            val relativePath = cursor.getString(cursor.getColumnIndexOrThrow(MediaStore.MediaColumns.RELATIVE_PATH))
            val data = TrackData(title, "$relativePath$name")
            cursor.moveToNext()
            data
        }
} ?: emptyList()

要らなそうなのを消す

カラオケ版（Instrumental、Karaoke、off volcal）を消します。
それから、明らかにエロゲソングじゃないやつも手動で弾きます、ほとんどがエロゲソングなので手動で何とかなってそう。
あとはエロゲソングじゃないBGMとかも残っちゃうんだけどこれはもうSQLに書いちゃおうかな...

あんまり参考にならんと思うけどこんな感じにしてみた

/** 曲とパスが書かれた JSON */
private val musicListJsonFile = File("trackdata_list.json")

private fun generateTrackDataListJson() {
    // タイトルに入っていれば消す
    // カラオケ版を消すため
    // タイトルに instrumental とか入っていれば
    val deleteFilterKeywordList = listOf(
        "instrumental",
        "off vocal",
        "karaoke",
        "inst"
    )

    // 例外にするフォルダ名
    val ignoreFolderNameList = listOf(
        // あれば
    )

    // Music Center の tracks.db ファイルのパス
    val tracksDb = File("""C:\\Users\\takusan23\\Desktop\\Dev\\Kotlin\\ErogePlaylistMaker\\tracks.db""")

    // タイトルだけのが欲しい
    val titleList = File("""title_list.txt""")
    // 一応消しておく
    titleList.delete()
    musicListJsonFile.delete()

    // 正規表現で余計なかっこを消す
    val bracketRegex = """(\[.+?\]|\(.+?\))""".toRegex()
    val prefixNumberRegex = """^\d{2}""".toRegex()

    // data.TrackData の配列する
    val trackDataList = arrayListOf<TrackData>()

    // 1行ごとに JSON パーサーにかける
    tracksDb
        .readText()
        .lines()
        .filter { it.isNotEmpty() }
        .map { json.decodeFromString<JsonElement>(it) }
        // もし重複があれば消す
        // TODO 同じタイトルがあればやめる
        .distinctBy { jsonElement ->
            jsonElement.jsonObject["title"]?.jsonPrimitive?.content!!
        }
        // カラオケ版とかを消す
        .filter { jsonElement ->
            val title = jsonElement.jsonObject["title"]?.jsonPrimitive?.content!!
            deleteFilterKeywordList.none { keyword -> title.contains(keyword, ignoreCase = true) }
        }
        // 例外にするフォルダ名
        .filter { jsonElement ->
            val filePath = jsonElement.jsonObject["file"]?.jsonObject?.get("uri")?.jsonPrimitive?.content!!
            ignoreFolderNameList.none { filePath.contains(it, ignoreCase = true) }
        }
        // テキストに吐き出す
        // ここでも色々やる
        .forEach { jsonElement ->
            val title = jsonElement
                .jsonObject["title"]
                ?.jsonPrimitive
                ?.content!!
                // 正規表現
                // カッコが含まれている場合は消す
                .replace(bracketRegex, "")
                // 先頭に2桁の数字があれば消す
                .replace(prefixNumberRegex, "")
                // 余計な空白があれば消す
                .trim()
                .trimEnd()

            val filePath = jsonElement
                .jsonObject["file"]
                ?.jsonObject
                ?.get("uri")
                ?.jsonPrimitive
                ?.content!!

            // JSON
            trackDataList += TrackData(title, filePath)

            // タイトルだけ
            titleList.appendText(title)
            titleList.appendText("\r\n")
        }

    musicListJsonFile.writeText(json.encodeToString(trackDataList))
}

SQL を書く

複数の文字一致はWHERE カラム名 in ('文字', '文字')みたいな感じでできるらしい。
カラム名 = '文字' or カラム名 = '文字'と同じらしい。

というわけで試してみる。
ちゃんと複数でも動いていそう。

SELECT ml.*,
    gm.*,
    gl.*,
    bl.*
FROM musiclist ml
    INNER JOIN game_music gm ON ml.id = gm.music
    INNER JOIN gamelist gl ON gm.game = gl.id
    INNER JOIN brandlist bl ON gl.brandname = bl.id
WHERE name in (
    'Pleasure garden',
    '奇跡メロディ'
);

あとはこれをもとにSQLを組み立てて（今回は面倒なので文字列連結しますが、プレースホルダー等を使いましょうね）、
POSTリクエストしてスクレイピングすれば良さそう！

というわけでこんな感じ（かなり端折ってるので雰囲気つかんで）
OkHttpを使ってます。

/** 批評空間からとってきた結果 */
private val hihyoukuukanJsonFile = File("hihyoukuukan.json")

private fun requestErogameScapeSql() {

    // 曲名を入れる
    // PostgreSQL でエスケープ必須なものはエスケープしてね
    val musicList = """
        
    Pleasure garden
    奇跡メロディ
    
    """.trimIndent()
        .lines()
        .filter { it.isNotEmpty() }
        .joinToString(separator = ",") { name -> """'$name'""" }

    val sql = """
        SELECT ml.*,
            gm.*,
            gl.*,
            bl.*
        FROM musiclist ml
            INNER JOIN game_music gm ON ml.id = gm.music
            INNER JOIN gamelist gl ON gm.game = gl.id
            INNER JOIN brandlist bl ON gl.brandname = bl.id
        WHERE name in ( $musicList );
    """.trimIndent()

    // 批評空間に SQL を流す
    val formData = FormBody.Builder().apply {
        add("sql", sql)
    }.build()
    val request = Request.Builder().apply {
        url("https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/sql_for_erogamer_form.php")
        addHeader("User-Agent", "@takusan_23@diary.negitoro.dev")
        post(formData)
    }.build()

    val response = okHttpClient.newCall(request).execute()
    val html = response.body?.string()

    // 失敗時は例外
    if (!response.isSuccessful) {
        throw RuntimeException("リクエストに失敗しました")
    }

    // スクレイピングする
    val document = Jsoup.parse(html)
    val column = document
        .getElementsByTag("tr")
        .drop(1)
        .map { tr -> tr.getElementsByTag("td").map { it.text() } }

    // 前回のを足す
    val currentJson = hihyoukuukanJsonFile
        .takeIf { it.exists() }
        ?.readText()
        ?.takeIf { it.isNotEmpty() }
        ?.let { json.decodeFromString<List<HihyoukuukanData>>(it) } ?: emptyList()

    // 書き込む
    val jsonArray = currentJson + column.map { item -> HihyoukuukanData(item[1], item) }
    hihyoukuukanJsonFile.writeText(json.encodeToString(jsonArray))
}

これでJSON形式でいい感じに批評空間でSQL叩いた結果が保存されるようになりました。
これを煮るなり焼くなりすればプレイリスト作れそう。

プレイリストをつくる

作ります！
手始めに年代別出してみます！

21 番目なのはSQL実行結果の表の列のことですね。

private fun generatePlaylistFromHihyoukuukanJson() {
    // 批評空間からとってきた結果と、曲一覧のJSONをパースする
    val hihyoukuukanDataList = json.decodeFromString<List<HihyoukuukanData>>(hihyoukuukanJsonFile.readText())
    val musicList = json.decodeFromString<List<TrackData>>(musicListJsonFile.readText())

    // 年代別！
    // 21 番目が sellday
    val selldayList = hihyoukuukanDataList.groupBy { it.queryResult[21].split("-").first() }
    selldayList.forEach { (year, dataList) ->
        println(year)
        println(dataList.map { it.name })
        println("---")
    }
}

ほい！

Imgur

あとはプレイリストを作るだけですね！

m3u8 ファイル

.m3uとか、.m3u8言われてるこれ、音楽ファイルのパスを列挙すればいいらしい？
.m3u8といえばHLSですが（一筋縄でダウンロードさせてくれないやつで有名）、音楽のプレイリスト作るのにも使うんですね。

こんな感じでしょうか。
どうやらMusic Centerは.m3u8にしないとダメだった、.m3uだとなんか私の作り方が悪いのかダメでした。

#EXTM3U8
C:\music\track1.flac
C:\music\track2.flac

というわけでコレを目指して作ってみます。
年代別でm3u8ファイルを作って

private fun generatePlaylistFromHihyoukuukanJson() {
    // 批評空間からとってきた結果と、曲一覧のJSONをパースする
    val hihyoukuukanDataList = json.decodeFromString<List<HihyoukuukanData>>(hihyoukuukanJsonFile.readText())
    val musicList = json.decodeFromString<List<TrackData>>(musicListJsonFile.readText())

    // 年代別！
    // 21 番目が sellday
    val selldayList = hihyoukuukanDataList.groupBy { it.queryResult[21].split("-").first() }
    selldayList.forEach { (year, dataList) ->
        // ファイルパスを探す
        // 曲一覧 JSON にある
        val filePathList = dataList.map { hihyoukuukan -> musicList.first { it.name == hihyoukuukan.name }.filePath }
        // .m3u8 を作る
        val m3u8Text = "#EXTM3U8\n${filePathList.joinToString(separator = "\n")}"
        // 吐き出す
        playlistFolder.resolve("${year}年発売エロゲソング.m3u8").writeText(m3u8Text)
    }
}

出来てる出来てる

Imgur

Music Centerのここから、生成した.m3u8を取り込めば良いはず！

Imgur

はい！できたああああ

Imgur

いろいろ作ってみた

ブランド別

// ブランド別
val brandList = hihyoukuukanDataList.groupBy { it.queryResult[96] }
brandList.forEach { (brandName, dataList) ->
    // ファイルパスを探す
    // 曲一覧 JSON にある
    val filePathList = dataList
        .distinctBy { hihyoukuukan -> hihyoukuukan.name }
        .map { hihyoukuukan ->
            musicList.first { it.name == hihyoukuukan.name }.filePath
        }
    // 10曲以上あれば（なんとなく
    if (filePathList.size >= 10) {
        // .m3u8 を作る
        val m3u8Text = "#EXTM3U8\n${filePathList.joinToString(separator = "\n")}"
        // 吐き出す
        playlistFolder.resolve("${brandName}の曲一覧.m3u8").writeText(m3u8Text)
    }
}

OP / ED / 挿入歌別
挿入歌、地味にすごい

// OP ED
val musicCategory = hihyoukuukanDataList.groupBy { it.queryResult[15] }
musicCategory.forEach { (category, dataList) ->
    // ファイルパスを探す
    // 曲一覧 JSON にある
    val filePathList = dataList
        .distinctBy { hihyoukuukan -> hihyoukuukan.name }
        .map { hihyoukuukan ->
            musicList.first { it.name == hihyoukuukan.name }.filePath
        }
    // 10曲以上あれば（なんとなく
    if (filePathList.size >= 10) {
        // .m3u8 を作る
        val m3u8Text = "#EXTM3U8\n${filePathList.joinToString(separator = "\n")}"
        // 吐き出す
        playlistFolder.resolve("${category}曲一覧.m3u8").writeText(m3u8Text)
    }
}

いい感じ！！

Imgur

もっと頑張らないとダメそう

次やるならこの点にも気をつけたい

コンシューマー版（家庭用ゲーム機版）の発売日が紛れ込んでいる
- もうクエリ叩き直すのめんどかったので、Music Center側のリリース年と批評空間側の発売日を突き合わせて、リリース年が一致するか、リリース年がそもそも無ければそれも無条件で入れる。ようにしました
  - コンシューマー版を消すクエリを書く？
同じ曲が2個はいってる場合
- 例えば原曲verとヒロインが歌ったverみたいなのには対応していない
タイトル被り
- 大変そう
- タイトル被ったら被ったものだけ集めて、追加情報を渡してふるいにかけるようなSQLを書くと良いかもしれない。
カラオケ版以外にもアレンジ版とかがあれば弾くべき？

おわりに

つかったコード置いておきます、断片的でまともに使えないと思いますが...

https://github.com/takusan23/ErogePlaylistMaker/blob/master/src/main/kotlin/Main.kt

そして批評空間にエロゲソング情報を残してくれた方、ありがとうございます。大変助かりました；；

本題
しかし
ErogameScape
SQL フォーム
環境
入ってる曲一覧が欲しい
音楽ファイルを音楽フォルダから探す
Music Center から取り出す
Android から取り出す
要らなそうなのを消す
SQL を書く
プレイリストをつくる
m3u8 ファイル
いろいろ作ってみた
もっと頑張らないとダメそう
おわりに