encoderPro/data/DATABASE-UPDATES.md

Database and UI Updates - 2025-12-28

Summary

Fixed the status filter issue and added container format and encoder columns to the dashboard table.

Changes Made

1. Fixed Status Filter (dashboard.py:717)

Issue: Status filter dropdown wasn't working for "Discovered" state - API was rejecting it as invalid.

Fix: Added 'discovered' to the valid_states list in the /api/files endpoint.

# Before
valid_states = ['pending', 'processing', 'completed', 'failed', 'skipped', None]

# After
valid_states = ['discovered', 'pending', 'processing', 'completed', 'failed', 'skipped', None]

Testing: Select "Discovered" in the status filter dropdown - should now properly filter files.

---

2. Added Container Format Column to Database

Files Modified:

• dashboard.py (lines 161, 210)
• reencode.py (lines 374, 388, 400, 414, 417, 934, 951, 966)

Database Schema Changes:

ALTER TABLE files ADD COLUMN container_format TEXT

Scanner Updates:

• Extracts container format from FFprobe output during library scan
• Format name extracted from format.format_name (e.g., "matroska", "mov,mp4,m4a,3gp,3g2,mj2")
• Takes first format if multiple listed

Migration: Automatic - runs on next dashboard or scanner startup

---

3. Added Dashboard Table Columns

dashboard.html Changes:

Table Headers (lines 667-675):

• Added "Container" column (shows file container format like MKV, MP4)
• Added "Encoder" column (shows encoder used for completed files)
• Moved existing columns to accommodate

Table Column Order:

1. Checkbox
2. File
3. State
4. Resolution (now shows actual resolution like "1920x1080")
5. Container (NEW - shows MKV, MP4, AVI, etc.)
6. Encoder (NEW - shows encoder used like "hevc_qsv", "h264_nvenc")
7. Original Size
8. Encoded Size
9. Savings
10. Status

Data Display (lines 1518-1546):

• Resolution: Shows widthxheight (e.g., "1920x1080") or "-"
• Container: Shows uppercase format name (e.g., "MATROSKA", "MP4") or "-"
• Encoder: Shows encoder_used from database (e.g., "hevc_qsv") or "-"

Colspan Updates: Changed from 8 to 10 to match new column count

---

4. Database Update Script

File: update-database.py

Purpose: Populate container_format for existing database records

Usage:

# Auto-detect database location
python update-database.py

# Specify database path
python update-database.py path/to/state.db

What It Does:

1. Finds all files with NULL or empty container_format
2. Uses ffprobe to extract container format
3. Updates database with format information
4. Shows progress for each file
5. Commits every 10 files for safety

Requirements: ffprobe must be installed and in PATH

Example Output:

Opening database: data/state.db
Found 42 files to update
[1/42] Updated: movie1.mkv -> matroska
[2/42] Updated: movie2.mp4 -> mov,mp4,m4a,3gp,3g2,mj2
...
Update complete!
  Updated: 40
  Failed: 2
  Total: 42

---

How Container Format is Populated

For New Scans (Automatic)

When you run "Scan Library", the scanner now:

1. Runs FFprobe on each file
2. Extracts format.format_name from JSON output
3. Takes first format if comma-separated list
4. Stores in database during add_file()

Example:

• MKV files: format_name = "matroska,webm" → stored as "matroska"
• MP4 files: format_name = "mov,mp4,m4a,3gp,3g2,mj2" → stored as "mov"

For Existing Records (Manual)

Run the update script to populate container format for files already in database:

python update-database.py

---

Encoder Column

The "Encoder" column shows which encoder was used for completed encodings:

Data Source: files.encoder_used column (already existed)

Display:

• Completed files: Shows encoder name (e.g., "hevc_qsv", "h264_nvenc")
• Other states: Shows "-"

Updated By: The encoding process already sets this when completing a file

Common Values:

• hevc_qsv - Intel QSV H.265
• av1_qsv - Intel QSV AV1
• h264_nvenc - NVIDIA NVENC H.264
• hevc_nvenc - NVIDIA NVENC H.265
• libx265 - CPU H.265
• libx264 - CPU H.264

---

Testing Checklist

Status Filter

• Select "All States" - shows all files
• Select "Discovered" - shows only discovered files
• Select "Pending" - shows only pending files
• Select "Completed" - shows only completed files
• Combine with attribute filter (e.g., Discovered + 4K)

Dashboard Table

• Table has 10 columns (was 8)
• Resolution column shows actual resolution or "-"
• Container column shows format name or "-"
• Encoder column shows encoder for completed files or "-"
• All columns align properly

New Scans

• Run "Scan Library"
• Check database - new files should have container_format populated
• Dashboard should show container formats immediately

Database Update Script

• Run python update-database.py
• Verify container_format populated for existing files
• Check dashboard - existing files should now show containers

---

Migration Notes

Backward Compatible: Yes

• New columns have NULL default
• Existing code works without changes
• Database auto-migrates on startup

Data Loss: None

• Existing data preserved
• Only adds new columns

Rollback: Safe

• Can remove columns with ALTER TABLE DROP COLUMN (SQLite 3.35+)
• Or restore from backup

---

Files Changed

1. dashboard.py

   • Line 161: Added container_format to schema
   • Line 210: Added container_format migration
   • Line 717: Fixed valid_states to include 'discovered'

2. reencode.py

   • Line 374: Added container_format migration
   • Line 388: Added container_format parameter to add_file()
   • Lines 400, 414, 417: Updated SQL to include container_format
   • Lines 934, 951: Extract and pass container_format during scan
   • Line 966: Pass container_format to add_file()

3. templates/dashboard.html

   • Lines 670-671: Added Container and Encoder column headers
   • Line 680: Updated colspan from 8 to 10
   • Line 1472: Updated empty state colspan to 10
   • Lines 1518-1525: Added resolution, container, encoder formatting
   • Lines 1544-1546: Added new columns to table row

4. update-database.py (NEW)

   • Standalone script to populate container_format for existing records

---

Next Steps

1. Restart Flask Application to load database changes
2. Test Status Filter - verify "Discovered" works
3. Scan Library (optional) - populates container format for new files
4. Run Update Script - python update-database.py to update existing files
5. Verify Dashboard - check that all columns display correctly