#+ Sync Airsonic/Airsonic-derived ratings and favorites to file metadata

This repository helps export song ratings and favorites (stars) from an Airsonic (HSQLDB) music database and write them into your local music files' metadata so your player shows the same ratings/favorites.

Quick overview

• Extract ratings and starred (favorites) entries from the Airsonic HSQLDB database into TSV files.
• Apply those TSV entries to files in your music collection (MP3 and FLAC supported).

Prerequisites

• Download HSQLDB tools (required to read the Airsonic HSQLDB file):

curl -fLO https://hsqldb.org/download/hsqldb_274/hsqldb.jar
curl -fLO https://hsqldb.org/download/hsqldb_274/sqltool.jar

• Python dependencies (see requirements.txt):

python -m pip install -r requirements.txt

Extracting ratings and favorites

Use the included helper script extract-meta.sh to run the SQL queries in sql/ against your Airsonic HSQLDB and produce TSV files. Run the script without arguments for the help screen:

./extract-meta.sh ~/path/to/airsonic/db

Common commands:

# export to ratings.tsv using sql/select-ratings.sql
./extract-meta.sh ~/path/to/airsonic/db ratings

# export to starred.tsv using sql/select-starred.sql
./extract-meta.sh ~/path/to/airsonic/db starred

The script writes TSV files like ratings.tsv and starred.tsv in the repository root.

Applying ratings/favorites to your files

Use apply-meta.py to write ratings and favorites into your music files. Example:

# minimal example
python apply-meta.py --music-root /path/to/music-root --ratings ratings.tsv --starred starred.tsv

Options of note:

• --music-root: root directory where the files referenced in the TSV live.
• --ratings / --starred: TSV files produced by extract-meta.sh.
• --username: the Airsonic username to filter the TSV rows for.
• --popm-id: identifier used for MP3 POPM frames (default: MusicBee).
• --dry-run: print what would be changed without writing tags.

Supported formats:

• MP3: writes a POPM frame for ratings and a TXXX:FAVORITE text frame for favorites.
• FLAC: writes RATING and FAVORITE tags.

Files of interest

• apply-meta.py — script that applies ratings.tsv / starred.tsv to files.
• extract-meta.sh — helper that runs SQL queries against HSQLDB.
• sql/select-ratings.sql, sql/select-starred.sql — queries used to produce the TSVs.

Notes

• The TSV files contain relative paths matching how Airsonic stores them; pass the same root you use for playback via --music-root. You can include multiple routes using the system directory separator (':' on linux).
• If a file listed in the TSV is missing under --music-root, it will be reported and skipped.
• MP3 POPM rating values are translated from 1..5 stars to the standard POPM 0..255 scale.