237 lines
6.5 KiB
Markdown
237 lines
6.5 KiB
Markdown
# 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.
|
|
|
|
```python
|
|
# 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**:
|
|
```sql
|
|
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**:
|
|
```bash
|
|
# 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:
|
|
```bash
|
|
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
|