Navidrome DB malformed #60

New Issue

2026-04-11T22:41:58-04:00

sickprodigy commented

2026-04-11 22:41:58 -04:00

Hecka issues, not really sure what happened, updated navidrome.toml

LogLevel = 'info'
Scanner.Schedule = '@every 12h'
Scanner.WatcherWait = '20s'
TranscodingCacheSize = '150MiB'
MusicFolder = '/music'
DataFolder = '/data'

Backup.Path = '/data/backups'
Backup.Schedule = '@weekly'
Backup.Count = 4

trying to take backups now so can rollback easier, because right now it appears I have to rebuild the database from scratch and import stuff seperately..

Hecka issues, not really sure what happened, updated navidrome.toml ``` LogLevel = 'info' Scanner.Schedule = '@every 12h' Scanner.WatcherWait = '20s' TranscodingCacheSize = '150MiB' MusicFolder = '/music' DataFolder = '/data' Backup.Path = '/data/backups' Backup.Schedule = '@weekly' Backup.Count = 4 ``` trying to take backups now so can rollback easier, because right now it appears I have to rebuild the database from scratch and import stuff seperately..

sickprodigy commented

2026-04-12 12:48:10 -04:00

idk the database is working just erroring when getting to a certain part. prolly take a backup work out of another folder and leave that database live for now or something.

sickprodigy commented

2026-04-12 16:01:43 -04:00

creating a fresh database rn, going through the scan, and just gonna build some sql to import the old data somehow. be better to have a fresh database i think. then start taking backups

sickprodigy commented

2026-04-19 16:13:52 -04:00

easier said than done really lol, still haven't done this, been using the old database that's somewhat working kinda having issues with lidarr pulling new stuff though. albums missing like for tyler cassidy for example.

sickprodigy commented

2026-05-24 06:35:15 -04:00

alright tired of the old database not being able to search what I want or find what I want getting annoying. So let's get this thing fixed today.

sickprodigy commented

2026-05-25 17:35:40 -04:00

Actual Navidrome DB Metadata Migration - 2026-05-25

Workspace:
S:\Documents\prodigy\Coding-Programming\docker-containers\navidrome

Databases involved:

Source/old DB: navidrome-working-with-errors.db
Target/current DB: navidrome.db

Important backups created before writes:

navidrome.pre-metadata-migrate-20260525-150226.db
- Backup before restoring users/listening metadata.
navidrome.pre-metadata-migrate-20260525-160325.db
- Backup before restoring song/album date-added fields.

Main script used:
final_migrate.py

Removed helper scripts:

change_path.py
- Old standalone path rewrite helper.
- Not used by the migration anymore.
test_ids.py
- Temporary checker for source/target media ID matching.
- Not used after final_migrate.py handled mapping internally.

What final_migrate.py does:

Keeps the target navidrome.db schema and scanned library rows intact.
Attaches navidrome-working-with-errors.db as source_db.
Builds a temporary media_id_map by matching source and target media_file.path.
Builds a temporary user mapping by matching users by user_name.
Existing target users are not overwritten.
Old source users missing from the target are inserted using their old IDs when possible.
User-specific metadata is copied through the user ID map so old user IDs do not skew the new DB.

Migrated user/listening metadata:

Users: restored old users that did not already exist by username.
User library grants.
User preferences.
Player/client rows.
Annotations:
- play_count
- play_date
- rating
- starred
- starred_at
- rated_at
Bookmarks, if any exist.
Scrobbles/listen history.

Migrated date-added/order fields:

media_file.created_at
media_file.birth_time
album.created_at
album.imported_at

Useful script commands:

Dry run full metadata migration:

python -u final_migrate.py --dry-run

Run full metadata migration:

python -u final_migrate.py

Dry run only song/album date-added migration:

python -u final_migrate.py --only-library-dates --dry-run

Run only song/album date-added migration:

python -u final_migrate.py --only-library-dates

Validation results after migration:

PRAGMA quick_check(20) returned ok.
User/listening migration completed with:
- user=13
- annotation=235356
- scrobbles=2127
Date-added verification completed with:
- media_date_different=0
- album_date_different=0

Notes from troubleshooting:

Navidrome should be stopped before running write migrations against navidrome.db.
A stale Python dry-run process once held a SQLite lock on navidrome.db; stopping that process cleared the lock.
A Navidrome log message like database is locked for UPDATE user SET last_access_at = ... during scanning is usually not critical by itself. It means Navidrome could not update the user's last access timestamp while another DB write was active.
The scan log later showed Scanner: Finished scanning all libraries and Scan completed, so that specific lock message did not necessarily mean scan failure.
Spotify.ID in navidrome.toml is deprecated and Navidrome logs that it is ignored. It can be removed from config later.

Potential future carry-over items not migrated yet:

Old playlists and playlist tracks.
Radio stations.
Uploaded/custom playlist, artist, or album images.
Shares/bookmarks/playqueue had no useful source rows during this migration, based on inspected counts.

"""Migrate Navidrome persisted metadata into the current database.

The target database keeps its schema and scanned library rows. Source users are
matched by username; existing target users are not overwritten. New source users
are inserted with their old IDs when those IDs are available, then all related
metadata is copied through a source_id -> target_id user map.

Media/album date fields are copied by stable IDs/paths so Navidrome's generated
scan metadata stays intact while "date added" style ordering is restored.
"""

final_migrate.py

import argparse
import shutil
import sqlite3
import sys
from datetime import datetime
from pathlib import Path

DEFAULT_SOURCE = Path("navidrome-working-with-errors.db")
DEFAULT_TARGET = Path("navidrome.db")


def connect(path: Path) -> sqlite3.Connection:
    connection = sqlite3.connect(path)
    connection.row_factory = sqlite3.Row
    return connection


def apply_pragmas(conn: sqlite3.Connection) -> None:
    conn.executescript(
        """
        PRAGMA foreign_keys = ON;
        PRAGMA synchronous = NORMAL;
        PRAGMA temp_store = MEMORY;
        PRAGMA cache_size = -1000000;
        """
    )


def row_count(conn: sqlite3.Connection, table: str) -> int:
    return conn.execute(f"SELECT COUNT(1) FROM {table}").fetchone()[0]


def execute(conn: sqlite3.Connection, label: str, sql: str) -> int:
    cursor = conn.execute(sql)
    count = cursor.rowcount if cursor.rowcount >= 0 else 0
    print(f"{label}: {count}")
    return count


def create_backup(path: Path) -> Path:
    timestamp = datetime.now().strftime("%Y%m%d-%H%M%S")
    backup = path.with_name(f"{path.stem}.pre-metadata-migrate-{timestamp}{path.suffix}")
    shutil.copy2(path, backup)
    return backup


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(
        description="Migrate users, ratings, stars, play counts, date-added fields, and related Navidrome metadata."
    )
    parser.add_argument("--source", type=Path, default=DEFAULT_SOURCE)
    parser.add_argument("--target", type=Path, default=DEFAULT_TARGET)
    parser.add_argument("--dry-run", action="store_true", help="show what would be migrated, then roll back")
    parser.add_argument("--no-backup", action="store_true", help="skip the automatic target DB backup")
    parser.add_argument(
        "--only-library-dates",
        action="store_true",
        help="only copy media_file/album date-added fields; skip user/listening metadata",
    )
    return parser.parse_args()


def main() -> int:
    args = parse_args()

    if not args.source.exists():
        print(f"Source database not found: {args.source}")
        return 1
    if not args.target.exists():
        print(f"Target database not found: {args.target}")
        return 1

    if not args.dry_run and not args.no_backup:
        backup = create_backup(args.target)
        print(f"Backup created: {backup}")

    target = connect(args.target)
    try:
        apply_pragmas(target)
        target.execute("ATTACH DATABASE ? AS source_db", (str(args.source),))

        print("Building ID maps...")
        target.executescript(
            """
            DROP TABLE IF EXISTS temp.user_id_map;
            DROP TABLE IF EXISTS temp.media_id_map;
            DROP TABLE IF EXISTS temp.media_date_map;
            DROP TABLE IF EXISTS temp.album_date_map;

            CREATE TEMP TABLE user_id_map (
                source_id TEXT PRIMARY KEY,
                target_id TEXT NOT NULL
            );

            CREATE TEMP TABLE media_id_map (
                source_id TEXT PRIMARY KEY,
                target_id TEXT NOT NULL
            );

            CREATE TEMP TABLE media_date_map (
                target_id TEXT PRIMARY KEY,
                created_at DATETIME,
                birth_time DATETIME
            );

            CREATE TEMP TABLE album_date_map (
                target_id TEXT PRIMARY KEY,
                created_at DATETIME,
                imported_at DATETIME
            );

            INSERT INTO temp.media_id_map (source_id, target_id)
            SELECT sm.id, tm.id
            FROM source_db.media_file sm
            JOIN main.media_file tm ON sm.path = tm.path;

            INSERT INTO temp.media_date_map (target_id, created_at, birth_time)
            SELECT mm.target_id, sm.created_at, sm.birth_time
            FROM source_db.media_file sm
            JOIN temp.media_id_map mm ON mm.source_id = sm.id;

            INSERT INTO temp.album_date_map (target_id, created_at, imported_at)
            SELECT ta.id, sa.created_at, sa.imported_at
            FROM source_db.album sa
            JOIN main.album ta ON ta.id = sa.id;
            """
        )

        existing_user_matches = target.execute(
            """
            SELECT COUNT(1)
            FROM source_db.user su
            JOIN main.user tu ON tu.user_name = su.user_name
            """
        ).fetchone()[0]
        users_to_add = target.execute(
            """
            SELECT COUNT(1)
            FROM source_db.user su
            WHERE NOT EXISTS (
                SELECT 1 FROM main.user tu WHERE tu.user_name = su.user_name
            )
            AND NOT EXISTS (
                SELECT 1 FROM main.user ti WHERE ti.id = su.id
            )
            """
        ).fetchone()[0]
        id_conflicts = target.execute(
            """
            SELECT COUNT(1)
            FROM source_db.user su
            WHERE NOT EXISTS (
                SELECT 1 FROM main.user tu WHERE tu.user_name = su.user_name
            )
            AND EXISTS (
                SELECT 1 FROM main.user ti WHERE ti.id = su.id
            )
            """
        ).fetchone()[0]
        media_count = row_count(target, "temp.media_id_map")

        print(f"Existing users matched by username: {existing_user_matches}")
        print(f"New source users ready to add: {users_to_add}")
        print(f"New source users skipped because their old ID already exists: {id_conflicts}")
        print(f"Media files mapped by path: {media_count}")

        print("\nMigrating...")
        target.execute("BEGIN")
        try:
            if not args.only_library_dates:
                execute(
                    target,
                    "Users inserted",
                    """
                    INSERT INTO main.user
                    (id, user_name, name, email, password, is_admin, last_login_at, last_access_at, created_at, updated_at)
                    SELECT su.id, su.user_name, su.name, su.email, su.password, su.is_admin,
                           su.last_login_at, su.last_access_at, su.created_at, su.updated_at
                    FROM source_db.user su
                    WHERE NOT EXISTS (
                        SELECT 1 FROM main.user tu WHERE tu.user_name = su.user_name
                    )
                    AND NOT EXISTS (
                        SELECT 1 FROM main.user ti WHERE ti.id = su.id
                    )
                    """,
                )

                target.execute(
                    """
                    INSERT INTO temp.user_id_map (source_id, target_id)
                    SELECT su.id, tu.id
                    FROM source_db.user su
                    JOIN main.user tu ON tu.user_name = su.user_name
                    """
                )
                print(f"User IDs mapped: {row_count(target, 'temp.user_id_map')}")

                execute(
                    target,
                    "User library grants inserted",
                    """
                    INSERT OR IGNORE INTO main.user_library (user_id, library_id)
                    SELECT um.target_id, ul.library_id
                    FROM source_db.user_library ul
                    JOIN temp.user_id_map um ON um.source_id = ul.user_id
                    JOIN main.library l ON l.id = ul.library_id
                    """,
                )

                execute(
                    target,
                    "User preferences inserted",
                    """
                    INSERT OR IGNORE INTO main.user_props (user_id, key, value)
                    SELECT um.target_id, up.key, up.value
                    FROM source_db.user_props up
                    JOIN temp.user_id_map um ON um.source_id = up.user_id
                    """,
                )

                execute(
                    target,
                    "Players inserted",
                    """
                    INSERT OR IGNORE INTO main.player
                    (id, name, user_agent, user_id, client, ip, last_seen, max_bit_rate,
                     transcoding_id, report_real_path, scrobble_enabled)
                    SELECT p.id, p.name, p.user_agent, um.target_id, p.client, p.ip, p.last_seen,
                           p.max_bit_rate, p.transcoding_id, p.report_real_path, p.scrobble_enabled
                    FROM source_db.player p
                    JOIN temp.user_id_map um ON um.source_id = p.user_id
                    """,
                )

                execute(
                    target,
                    "Annotations migrated",
                    """
                    INSERT OR REPLACE INTO main.annotation
                    (user_id, item_id, item_type, play_count, play_date, rating, starred, starred_at, rated_at)
                    SELECT
                        um.target_id,
                        CASE
                            WHEN a.item_type = 'media_file' THEN mm.target_id
                            ELSE a.item_id
                        END,
                        a.item_type,
                        a.play_count,
                        a.play_date,
                        a.rating,
                        a.starred,
                        a.starred_at,
                        a.rated_at
                    FROM source_db.annotation a
                    JOIN temp.user_id_map um ON um.source_id = a.user_id
                    LEFT JOIN temp.media_id_map mm
                        ON a.item_type = 'media_file' AND mm.source_id = a.item_id
                    WHERE
                        (a.item_type = 'media_file' AND mm.target_id IS NOT NULL)
                        OR (a.item_type = 'album' AND EXISTS (
                            SELECT 1 FROM main.album ta WHERE ta.id = a.item_id
                        ))
                        OR (a.item_type = 'artist' AND EXISTS (
                            SELECT 1 FROM main.artist tar WHERE tar.id = a.item_id
                        ))
                    """,
                )

                execute(
                    target,
                    "Bookmarks migrated",
                    """
                    INSERT OR REPLACE INTO main.bookmark
                    (user_id, item_id, item_type, comment, position, changed_by, created_at, updated_at)
                    SELECT
                        um.target_id,
                        CASE
                            WHEN b.item_type = 'media_file' THEN mm.target_id
                            ELSE b.item_id
                        END,
                        b.item_type,
                        b.comment,
                        b.position,
                        b.changed_by,
                        b.created_at,
                        b.updated_at
                    FROM source_db.bookmark b
                    JOIN temp.user_id_map um ON um.source_id = b.user_id
                    LEFT JOIN temp.media_id_map mm
                        ON b.item_type = 'media_file' AND mm.source_id = b.item_id
                    WHERE
                        (b.item_type = 'media_file' AND mm.target_id IS NOT NULL)
                        OR (b.item_type = 'album' AND EXISTS (
                            SELECT 1 FROM main.album ta WHERE ta.id = b.item_id
                        ))
                        OR (b.item_type = 'artist' AND EXISTS (
                            SELECT 1 FROM main.artist tar WHERE tar.id = b.item_id
                        ))
                    """,
                )

                execute(
                    target,
                    "Scrobbles migrated",
                    """
                    INSERT INTO main.scrobbles (media_file_id, user_id, submission_time)
                    SELECT mm.target_id, um.target_id, s.submission_time
                    FROM source_db.scrobbles s
                    JOIN temp.user_id_map um ON um.source_id = s.user_id
                    JOIN temp.media_id_map mm ON mm.source_id = s.media_file_id
                    WHERE NOT EXISTS (
                        SELECT 1
                        FROM main.scrobbles existing
                        WHERE existing.media_file_id = mm.target_id
                          AND existing.user_id = um.target_id
                          AND existing.submission_time = s.submission_time
                    )
                    """,
                )

            execute(
                target,
                "Media file date-added fields updated",
                """
                UPDATE main.media_file
                SET
                    created_at = (
                        SELECT md.created_at
                        FROM temp.media_date_map md
                        WHERE md.target_id = main.media_file.id
                    ),
                    birth_time = (
                        SELECT md.birth_time
                        FROM temp.media_date_map md
                        WHERE md.target_id = main.media_file.id
                    )
                WHERE EXISTS (
                    SELECT 1
                    FROM temp.media_date_map md
                    WHERE md.target_id = main.media_file.id
                )
                """,
            )

            execute(
                target,
                "Album date-added fields updated",
                """
                UPDATE main.album
                SET
                    created_at = (
                        SELECT ad.created_at
                        FROM temp.album_date_map ad
                        WHERE ad.target_id = main.album.id
                    ),
                    imported_at = (
                        SELECT ad.imported_at
                        FROM temp.album_date_map ad
                        WHERE ad.target_id = main.album.id
                    )
                WHERE EXISTS (
                    SELECT 1
                    FROM temp.album_date_map ad
                    WHERE ad.target_id = main.album.id
                )
                """,
            )

            if not args.only_library_dates:
                fk_errors = target.execute("PRAGMA foreign_key_check").fetchall()
                if fk_errors:
                    raise RuntimeError(f"foreign_key_check failed with {len(fk_errors)} errors")

            if args.dry_run:
                target.rollback()
                print("\nDry run complete; rolled back all changes.")
            else:
                target.commit()
                print("\nMigration complete.")
            return 0
        except Exception:
            target.rollback()
            raise
    except Exception as error:
        print(f"Migration failed: {error}")
        return 1
    finally:
        target.close()


if __name__ == "__main__":
    sys.exit(main())

## Actual Navidrome DB Metadata Migration - 2026-05-25 Workspace: `S:\Documents\prodigy\Coding-Programming\docker-containers\navidrome` Databases involved: - Source/old DB: `navidrome-working-with-errors.db` - Target/current DB: `navidrome.db` Important backups created before writes: - `navidrome.pre-metadata-migrate-20260525-150226.db` - Backup before restoring users/listening metadata. - `navidrome.pre-metadata-migrate-20260525-160325.db` - Backup before restoring song/album date-added fields. Main script used: `final_migrate.py` Removed helper scripts: - `change_path.py` - Old standalone path rewrite helper. - Not used by the migration anymore. - `test_ids.py` - Temporary checker for source/target media ID matching. - Not used after `final_migrate.py` handled mapping internally. What `final_migrate.py` does: - Keeps the target `navidrome.db` schema and scanned library rows intact. - Attaches `navidrome-working-with-errors.db` as `source_db`. - Builds a temporary `media_id_map` by matching source and target `media_file.path`. - Builds a temporary user mapping by matching users by `user_name`. - Existing target users are not overwritten. - Old source users missing from the target are inserted using their old IDs when possible. - User-specific metadata is copied through the user ID map so old user IDs do not skew the new DB. Migrated user/listening metadata: - Users: restored old users that did not already exist by username. - User library grants. - User preferences. - Player/client rows. - Annotations: - `play_count` - `play_date` - `rating` - `starred` - `starred_at` - `rated_at` - Bookmarks, if any exist. - Scrobbles/listen history. Migrated date-added/order fields: - `media_file.created_at` - `media_file.birth_time` - `album.created_at` - `album.imported_at` Useful script commands: Dry run full metadata migration: ```powershell python -u final_migrate.py --dry-run ``` Run full metadata migration: ```powershell python -u final_migrate.py ``` Dry run only song/album date-added migration: ```powershell python -u final_migrate.py --only-library-dates --dry-run ``` Run only song/album date-added migration: ```powershell python -u final_migrate.py --only-library-dates ``` Validation results after migration: - `PRAGMA quick_check(20)` returned `ok`. - User/listening migration completed with: - `user=13` - `annotation=235356` - `scrobbles=2127` - Date-added verification completed with: - `media_date_different=0` - `album_date_different=0` Notes from troubleshooting: - Navidrome should be stopped before running write migrations against `navidrome.db`. - A stale Python dry-run process once held a SQLite lock on `navidrome.db`; stopping that process cleared the lock. - A Navidrome log message like `database is locked` for `UPDATE user SET last_access_at = ...` during scanning is usually not critical by itself. It means Navidrome could not update the user's last access timestamp while another DB write was active. - The scan log later showed `Scanner: Finished scanning all libraries` and `Scan completed`, so that specific lock message did not necessarily mean scan failure. - `Spotify.ID` in `navidrome.toml` is deprecated and Navidrome logs that it is ignored. It can be removed from config later. Potential future carry-over items not migrated yet: - Old playlists and playlist tracks. - Radio stations. - Uploaded/custom playlist, artist, or album images. - Shares/bookmarks/playqueue had no useful source rows during this migration, based on inspected counts. """Migrate Navidrome persisted metadata into the current database. The target database keeps its schema and scanned library rows. Source users are matched by username; existing target users are not overwritten. New source users are inserted with their old IDs when those IDs are available, then all related metadata is copied through a source_id -> target_id user map. Media/album date fields are copied by stable IDs/paths so Navidrome's generated scan metadata stays intact while "date added" style ordering is restored. """ final_migrate.py ``` import argparse import shutil import sqlite3 import sys from datetime import datetime from pathlib import Path DEFAULT_SOURCE = Path("navidrome-working-with-errors.db") DEFAULT_TARGET = Path("navidrome.db") def connect(path: Path) -> sqlite3.Connection: connection = sqlite3.connect(path) connection.row_factory = sqlite3.Row return connection def apply_pragmas(conn: sqlite3.Connection) -> None: conn.executescript( """ PRAGMA foreign_keys = ON; PRAGMA synchronous = NORMAL; PRAGMA temp_store = MEMORY; PRAGMA cache_size = -1000000; """ ) def row_count(conn: sqlite3.Connection, table: str) -> int: return conn.execute(f"SELECT COUNT(1) FROM {table}").fetchone()[0] def execute(conn: sqlite3.Connection, label: str, sql: str) -> int: cursor = conn.execute(sql) count = cursor.rowcount if cursor.rowcount >= 0 else 0 print(f"{label}: {count}") return count def create_backup(path: Path) -> Path: timestamp = datetime.now().strftime("%Y%m%d-%H%M%S") backup = path.with_name(f"{path.stem}.pre-metadata-migrate-{timestamp}{path.suffix}") shutil.copy2(path, backup) return backup def parse_args() -> argparse.Namespace: parser = argparse.ArgumentParser( description="Migrate users, ratings, stars, play counts, date-added fields, and related Navidrome metadata." ) parser.add_argument("--source", type=Path, default=DEFAULT_SOURCE) parser.add_argument("--target", type=Path, default=DEFAULT_TARGET) parser.add_argument("--dry-run", action="store_true", help="show what would be migrated, then roll back") parser.add_argument("--no-backup", action="store_true", help="skip the automatic target DB backup") parser.add_argument( "--only-library-dates", action="store_true", help="only copy media_file/album date-added fields; skip user/listening metadata", ) return parser.parse_args() def main() -> int: args = parse_args() if not args.source.exists(): print(f"Source database not found: {args.source}") return 1 if not args.target.exists(): print(f"Target database not found: {args.target}") return 1 if not args.dry_run and not args.no_backup: backup = create_backup(args.target) print(f"Backup created: {backup}") target = connect(args.target) try: apply_pragmas(target) target.execute("ATTACH DATABASE ? AS source_db", (str(args.source),)) print("Building ID maps...") target.executescript( """ DROP TABLE IF EXISTS temp.user_id_map; DROP TABLE IF EXISTS temp.media_id_map; DROP TABLE IF EXISTS temp.media_date_map; DROP TABLE IF EXISTS temp.album_date_map; CREATE TEMP TABLE user_id_map ( source_id TEXT PRIMARY KEY, target_id TEXT NOT NULL ); CREATE TEMP TABLE media_id_map ( source_id TEXT PRIMARY KEY, target_id TEXT NOT NULL ); CREATE TEMP TABLE media_date_map ( target_id TEXT PRIMARY KEY, created_at DATETIME, birth_time DATETIME ); CREATE TEMP TABLE album_date_map ( target_id TEXT PRIMARY KEY, created_at DATETIME, imported_at DATETIME ); INSERT INTO temp.media_id_map (source_id, target_id) SELECT sm.id, tm.id FROM source_db.media_file sm JOIN main.media_file tm ON sm.path = tm.path; INSERT INTO temp.media_date_map (target_id, created_at, birth_time) SELECT mm.target_id, sm.created_at, sm.birth_time FROM source_db.media_file sm JOIN temp.media_id_map mm ON mm.source_id = sm.id; INSERT INTO temp.album_date_map (target_id, created_at, imported_at) SELECT ta.id, sa.created_at, sa.imported_at FROM source_db.album sa JOIN main.album ta ON ta.id = sa.id; """ ) existing_user_matches = target.execute( """ SELECT COUNT(1) FROM source_db.user su JOIN main.user tu ON tu.user_name = su.user_name """ ).fetchone()[0] users_to_add = target.execute( """ SELECT COUNT(1) FROM source_db.user su WHERE NOT EXISTS ( SELECT 1 FROM main.user tu WHERE tu.user_name = su.user_name ) AND NOT EXISTS ( SELECT 1 FROM main.user ti WHERE ti.id = su.id ) """ ).fetchone()[0] id_conflicts = target.execute( """ SELECT COUNT(1) FROM source_db.user su WHERE NOT EXISTS ( SELECT 1 FROM main.user tu WHERE tu.user_name = su.user_name ) AND EXISTS ( SELECT 1 FROM main.user ti WHERE ti.id = su.id ) """ ).fetchone()[0] media_count = row_count(target, "temp.media_id_map") print(f"Existing users matched by username: {existing_user_matches}") print(f"New source users ready to add: {users_to_add}") print(f"New source users skipped because their old ID already exists: {id_conflicts}") print(f"Media files mapped by path: {media_count}") print("\nMigrating...") target.execute("BEGIN") try: if not args.only_library_dates: execute( target, "Users inserted", """ INSERT INTO main.user (id, user_name, name, email, password, is_admin, last_login_at, last_access_at, created_at, updated_at) SELECT su.id, su.user_name, su.name, su.email, su.password, su.is_admin, su.last_login_at, su.last_access_at, su.created_at, su.updated_at FROM source_db.user su WHERE NOT EXISTS ( SELECT 1 FROM main.user tu WHERE tu.user_name = su.user_name ) AND NOT EXISTS ( SELECT 1 FROM main.user ti WHERE ti.id = su.id ) """, ) target.execute( """ INSERT INTO temp.user_id_map (source_id, target_id) SELECT su.id, tu.id FROM source_db.user su JOIN main.user tu ON tu.user_name = su.user_name """ ) print(f"User IDs mapped: {row_count(target, 'temp.user_id_map')}") execute( target, "User library grants inserted", """ INSERT OR IGNORE INTO main.user_library (user_id, library_id) SELECT um.target_id, ul.library_id FROM source_db.user_library ul JOIN temp.user_id_map um ON um.source_id = ul.user_id JOIN main.library l ON l.id = ul.library_id """, ) execute( target, "User preferences inserted", """ INSERT OR IGNORE INTO main.user_props (user_id, key, value) SELECT um.target_id, up.key, up.value FROM source_db.user_props up JOIN temp.user_id_map um ON um.source_id = up.user_id """, ) execute( target, "Players inserted", """ INSERT OR IGNORE INTO main.player (id, name, user_agent, user_id, client, ip, last_seen, max_bit_rate, transcoding_id, report_real_path, scrobble_enabled) SELECT p.id, p.name, p.user_agent, um.target_id, p.client, p.ip, p.last_seen, p.max_bit_rate, p.transcoding_id, p.report_real_path, p.scrobble_enabled FROM source_db.player p JOIN temp.user_id_map um ON um.source_id = p.user_id """, ) execute( target, "Annotations migrated", """ INSERT OR REPLACE INTO main.annotation (user_id, item_id, item_type, play_count, play_date, rating, starred, starred_at, rated_at) SELECT um.target_id, CASE WHEN a.item_type = 'media_file' THEN mm.target_id ELSE a.item_id END, a.item_type, a.play_count, a.play_date, a.rating, a.starred, a.starred_at, a.rated_at FROM source_db.annotation a JOIN temp.user_id_map um ON um.source_id = a.user_id LEFT JOIN temp.media_id_map mm ON a.item_type = 'media_file' AND mm.source_id = a.item_id WHERE (a.item_type = 'media_file' AND mm.target_id IS NOT NULL) OR (a.item_type = 'album' AND EXISTS ( SELECT 1 FROM main.album ta WHERE ta.id = a.item_id )) OR (a.item_type = 'artist' AND EXISTS ( SELECT 1 FROM main.artist tar WHERE tar.id = a.item_id )) """, ) execute( target, "Bookmarks migrated", """ INSERT OR REPLACE INTO main.bookmark (user_id, item_id, item_type, comment, position, changed_by, created_at, updated_at) SELECT um.target_id, CASE WHEN b.item_type = 'media_file' THEN mm.target_id ELSE b.item_id END, b.item_type, b.comment, b.position, b.changed_by, b.created_at, b.updated_at FROM source_db.bookmark b JOIN temp.user_id_map um ON um.source_id = b.user_id LEFT JOIN temp.media_id_map mm ON b.item_type = 'media_file' AND mm.source_id = b.item_id WHERE (b.item_type = 'media_file' AND mm.target_id IS NOT NULL) OR (b.item_type = 'album' AND EXISTS ( SELECT 1 FROM main.album ta WHERE ta.id = b.item_id )) OR (b.item_type = 'artist' AND EXISTS ( SELECT 1 FROM main.artist tar WHERE tar.id = b.item_id )) """, ) execute( target, "Scrobbles migrated", """ INSERT INTO main.scrobbles (media_file_id, user_id, submission_time) SELECT mm.target_id, um.target_id, s.submission_time FROM source_db.scrobbles s JOIN temp.user_id_map um ON um.source_id = s.user_id JOIN temp.media_id_map mm ON mm.source_id = s.media_file_id WHERE NOT EXISTS ( SELECT 1 FROM main.scrobbles existing WHERE existing.media_file_id = mm.target_id AND existing.user_id = um.target_id AND existing.submission_time = s.submission_time ) """, ) execute( target, "Media file date-added fields updated", """ UPDATE main.media_file SET created_at = ( SELECT md.created_at FROM temp.media_date_map md WHERE md.target_id = main.media_file.id ), birth_time = ( SELECT md.birth_time FROM temp.media_date_map md WHERE md.target_id = main.media_file.id ) WHERE EXISTS ( SELECT 1 FROM temp.media_date_map md WHERE md.target_id = main.media_file.id ) """, ) execute( target, "Album date-added fields updated", """ UPDATE main.album SET created_at = ( SELECT ad.created_at FROM temp.album_date_map ad WHERE ad.target_id = main.album.id ), imported_at = ( SELECT ad.imported_at FROM temp.album_date_map ad WHERE ad.target_id = main.album.id ) WHERE EXISTS ( SELECT 1 FROM temp.album_date_map ad WHERE ad.target_id = main.album.id ) """, ) if not args.only_library_dates: fk_errors = target.execute("PRAGMA foreign_key_check").fetchall() if fk_errors: raise RuntimeError(f"foreign_key_check failed with {len(fk_errors)} errors") if args.dry_run: target.rollback() print("\nDry run complete; rolled back all changes.") else: target.commit() print("\nMigration complete.") return 0 except Exception: target.rollback() raise except Exception as error: print(f"Migration failed: {error}") return 1 finally: target.close() if __name__ == "__main__": sys.exit(main()) ```

sickprodigy closed this issue

2026-05-25 17:35:40 -04:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: sickprodigy/docker-home-server#60