encoderPro/DATABASE-MIGRATION-FIX.md

Database Migration Fix

Issue

When running a scan in the Docker container, got this error:

sqlite3.OperationalError: table files has no column named video_codec

Root Cause

The new video attribute columns were only being added by dashboard.py's database initialization, but reencode.py has its own StateDatabase class that also needs to handle the migration.

When the scanner runs in the container (via reencode.py), it tried to insert data into columns that didn't exist yet.

Fix

Added the same migration logic to reencode.py's StateDatabase._init_database() method (lines 362-381):

# Migration: Add new columns if they don't exist
cursor.execute("PRAGMA table_info(files)")
columns = {row[1] for row in cursor.fetchall()}

migrations = [
    ("video_codec", "ALTER TABLE files ADD COLUMN video_codec TEXT"),
    ("audio_codec", "ALTER TABLE files ADD COLUMN audio_codec TEXT"),
    ("audio_channels", "ALTER TABLE files ADD COLUMN audio_channels INTEGER"),
    ("width", "ALTER TABLE files ADD COLUMN width INTEGER"),
    ("height", "ALTER TABLE files ADD COLUMN height INTEGER"),
    ("duration", "ALTER TABLE files ADD COLUMN duration REAL"),
    ("bitrate", "ALTER TABLE files ADD COLUMN bitrate INTEGER"),
]

for column_name, alter_sql in migrations:
    if column_name not in columns:
        logging.info(f"Adding column '{column_name}' to files table")
        cursor.execute(alter_sql)

How It Works

1. Uses PRAGMA table_info(files) to get list of existing columns
2. Checks if each new column exists
3. If missing, adds it with ALTER TABLE
4. Safe to run multiple times (won't duplicate columns)

Impact

• ✅ Existing databases automatically upgraded
• ✅ New databases created with all columns
• ✅ Scanner can now save media attributes
• ✅ Filters will work once data is populated

Next Steps

After deploying this fix:

1. Restart the container (or it will auto-reload if using the updated code)
2. Run "Scan Library" from the dashboard
3. Scanner will extract and save media attributes for all files
4. Filter buttons will work with the populated data