User:Harimaron

From Inkipedia, the Splatoon wiki

Pages in my sandbox:

Current projects

Scripts

Parsing Jukebox BgmInfo

The following script parses the Jukebox data to build a SQLite database, using the data from https://github.com/Leanny/splat3/blob/main/data/mush/300/BgmInfo.json and language files from https://github.com/Leanny/leanny.github.io/tree/master/splat3/data/language:

bgminfo.py 
# Copyright (c) 2023 https://splatoonwiki.org/wiki/User:Harimaron
#
# Permission is hereby granted, free of charge, to any person obtaining
# a copy of this software and associated documentation files (the
# "Software"), to deal in the Software without restriction, including
# without limitation the rights to use, copy, modify, merge, publish,
# distribute, sublicense, and/or sell copies of the Software, and to
# permit persons to whom the Software is furnished to do so, subject to
# the following conditions:
#
# The above copyright notice and this permission notice shall be
# included in all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
# EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
# MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
# NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
# LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
# OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
# WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

import json
import os
import sqlite3
from typing import Any, List

BGMINFO_FILE = os.path.join(os.path.dirname(
    os.path.realpath(__file__)), "splatoon3_300_BgmInfo.json")
LANGUAGE_LIST = [
    "CNzh",
    "EUde",
    "EUen",
    "EUes",
    "EUfr",
    "EUit",
    "EUnl",
    "EUru",
    "JPja",
    "KRko",
    "TWzh",
    "USen",
    "USes",
    "USfr"
]
LANGUAGE_FILES = {
    lang: os.path.join(os.path.dirname(
        os.path.realpath(__file__)), f"splatoon3_300_language_{lang}.json")
    for lang in LANGUAGE_LIST
}
DB_FILE = os.path.join(os.path.dirname(
    os.path.realpath(__file__)), "splatoon3_300_BgmInfo.sqlite3")


def load_json(file: str) -> Any:
    with open(file, "r") as f:
        return json.load(f)


def track_mask_to_bitfield(track_mask: List[bool]) -> int:
    assert len(track_mask) == 2
    assert type(track_mask[0]) is bool
    assert type(track_mask[1]) is bool
    return int(bool(track_mask[0])) | (int(bool(track_mask[1])) << 1)


def main() -> None:
    bgm_list = load_json(BGMINFO_FILE)
    db = sqlite3.connect(DB_FILE)
    with db:
        db.execute("""
            DROP TABLE IF EXISTS BgmInfo
        """)
        db.execute("""
            DROP TABLE IF EXISTS AssetNames
        """)
        db.execute("""
            DROP TABLE IF EXISTS ArtistNames
        """)
        db.execute("""
            DROP TABLE IF EXISTS MusicNames
        """)
        db.execute("""
            CREATE TABLE BgmInfo (
                id INTEGER PRIMARY KEY,
                Artist TEXT NOT NULL,
                BgmHash INTEGER NOT NULL,
                Category TEXT NOT NULL,
                JacketImage TEXT NOT NULL,
                JukeboxAssetIndex INTEGER NOT NULL,
                JukeboxTrackMask INTEGER NOT NULL,
                S_Order REAL NOT NULL,
                UnlockConditionHash INTEGER NOT NULL,
                S__RowId TEXT NOT NULL
            )
        """)
        db.execute("""
            CREATE TABLE AssetNames (
                BgmInfoId INTEGER NOT NULL,
                AssetName TEXT NOT NULL,
                AssetNameIndex INTEGER NOT NULL,
                PRIMARY KEY(BgmInfoId, AssetName),
                FOREIGN KEY(BgmInfoId) REFERENCES BgmInfo(id)
            )
        """)
        db.execute("""
            CREATE TABLE ArtistNames (
                key TEXT NOT NULL,
                display TEXT NOT NULL,
                lang TEXT NOT NULL
            )
        """)
        db.execute("""
            CREATE TABLE MusicNames (
                key TEXT NOT NULL,
                display TEXT NOT NULL,
                lang TEXT NOT NULL
            )
        """)
        cur = db.cursor()
        for bgm in bgm_list:
            cur.execute("""
                INSERT INTO BgmInfo (
                    Artist,
                    BgmHash,
                    Category,
                    JacketImage,
                    JukeboxAssetIndex,
                    JukeboxTrackMask,
                    S_Order,
                    UnlockConditionHash,
                    S__RowId
                ) VALUES (
                    ?, ?, ?, ?, ?, ?, ?, ?, ?
                )
            """, (
                bgm["Artist"],
                bgm["BgmHash"],
                bgm["Category"],
                bgm["JacketImage"],
                bgm["JukeboxAssetIndex"],
                track_mask_to_bitfield(bgm["JukeboxTrackMask"]),
                bgm["Order"],
                bgm["UnlockConditionHash"],
                bgm["__RowId"]
            ))
            bgm_rowid = cur.lastrowid
            for i, asset_name in enumerate(bgm["AssetNames"]):
                cur.execute("""
                    INSERT INTO AssetNames (
                        BgmInfoId,
                        AssetName,
                        AssetNameIndex
                    ) VALUES (
                        ?, ?, ?
                    )
                """, (
                    bgm_rowid,
                    asset_name,
                    i
                ))

        for lang, lang_file in LANGUAGE_FILES.items():
            lang_data = load_json(lang_file)
            for key, display in lang_data["CommonMsg/Sound/ArtistName"].items():
                cur.execute("""
                    INSERT INTO ArtistNames (
                        key,
                        display,
                        lang
                    ) VALUES (
                        ?, ?, ?
                    )
                """, (
                    key,
                    display,
                    lang
                ))
            for key, display in lang_data["CommonMsg/Sound/MusicName"].items():
                cur.execute("""
                    INSERT INTO MusicNames (
                        key,
                        display,
                        lang
                    ) VALUES (
                        ?, ?, ?
                    )
                """, (
                    key,
                    display,
                    lang
                ))


if __name__ == "__main__":
    main()

A clean list of all jukebox songs can then be produced with the following query:

bgminfo.sql 
SELECT
	S_Order,
	Category,
	MAX(CASE WHEN ArtistNames.lang = 'CNzh' THEN ArtistNames.display END) AS ArtistCNzh,
	MAX(CASE WHEN ArtistNames.lang = 'EUde' THEN ArtistNames.display END) AS ArtistEUde,
	MAX(CASE WHEN ArtistNames.lang = 'EUen' THEN ArtistNames.display END) AS ArtistEUen,
	MAX(CASE WHEN ArtistNames.lang = 'EUes' THEN ArtistNames.display END) AS ArtistEUes,
	MAX(CASE WHEN ArtistNames.lang = 'EUfr' THEN ArtistNames.display END) AS ArtistEUfr,
	MAX(CASE WHEN ArtistNames.lang = 'EUit' THEN ArtistNames.display END) AS ArtistEUit,
	MAX(CASE WHEN ArtistNames.lang = 'EUnl' THEN ArtistNames.display END) AS ArtistEUnl,
	MAX(CASE WHEN ArtistNames.lang = 'EUru' THEN ArtistNames.display END) AS ArtistEUru,
	MAX(CASE WHEN ArtistNames.lang = 'JPja' THEN ArtistNames.display END) AS ArtistJPja,
	MAX(CASE WHEN ArtistNames.lang = 'KRko' THEN ArtistNames.display END) AS ArtistKRko,
	MAX(CASE WHEN ArtistNames.lang = 'TWzh' THEN ArtistNames.display END) AS ArtistTWzh,
	MAX(CASE WHEN ArtistNames.lang = 'USen' THEN ArtistNames.display END) AS ArtistUSen,
	MAX(CASE WHEN ArtistNames.lang = 'USes' THEN ArtistNames.display END) AS ArtistUSes,
	MAX(CASE WHEN ArtistNames.lang = 'USfr' THEN ArtistNames.display END) AS ArtistUSfr,
	MAX(CASE WHEN MusicNames.lang = 'CNzh' THEN MusicNames.display END) AS MusicCNzh,
	MAX(CASE WHEN MusicNames.lang = 'EUde' THEN MusicNames.display END) AS MusicEUde,
	MAX(CASE WHEN MusicNames.lang = 'EUen' THEN MusicNames.display END) AS MusicEUen,
	MAX(CASE WHEN MusicNames.lang = 'EUes' THEN MusicNames.display END) AS MusicEUes,
	MAX(CASE WHEN MusicNames.lang = 'EUfr' THEN MusicNames.display END) AS MusicEUfr,
	MAX(CASE WHEN MusicNames.lang = 'EUit' THEN MusicNames.display END) AS MusicEUit,
	MAX(CASE WHEN MusicNames.lang = 'EUnl' THEN MusicNames.display END) AS MusicEUnl,
	MAX(CASE WHEN MusicNames.lang = 'EUru' THEN MusicNames.display END) AS MusicEUru,
	MAX(CASE WHEN MusicNames.lang = 'JPja' THEN MusicNames.display END) AS MusicJPja,
	MAX(CASE WHEN MusicNames.lang = 'KRko' THEN MusicNames.display END) AS MusicKRko,
	MAX(CASE WHEN MusicNames.lang = 'TWzh' THEN MusicNames.display END) AS MusicTWzh,
	MAX(CASE WHEN MusicNames.lang = 'USen' THEN MusicNames.display END) AS MusicUSen,
	MAX(CASE WHEN MusicNames.lang = 'USes' THEN MusicNames.display END) AS MusicUSes,
	MAX(CASE WHEN MusicNames.lang = 'USfr' THEN MusicNames.display END) AS MusicUSfr,
	JacketImage,
	AssetName,
	JukeboxAssetIndex,
	(JukeboxTrackMask & 1) || ';' || ((JukeboxTrackMask & 2) >> 1) AS JukeboxTrackMask,
	BgmHash,
	UnlockConditionHash
FROM
	BgmInfo
LEFT JOIN
	(
		SELECT
			BgmInfoId,
			GROUP_CONCAT(AssetNameIndex || '=' || AssetName, ';') AS AssetName
		FROM
			AssetNames
		GROUP BY BgmInfoId
	) AS AssetNames
	ON BgmInfo.id = AssetNames.BgmInfoId
LEFT JOIN ArtistNames
	ON BgmInfo.Artist = ArtistNames.key
LEFT JOIN MusicNames
	ON BgmInfo.S__RowId = MusicNames.key
WHERE ArtistNames.lang = MusicNames.lang
GROUP BY id
ORDER BY Category, S_Order, ArtistNames.lang
Retrieved from "https://splatoonwiki.org/w/index.php?title=User:Harimaron&oldid=420320"