# SQM Updates 108-110: Continuous Schema Migration Fix

## Problem
After successfully advancing past version 2.3.5.9, the setup upgrade hit the **same duplicate column error pattern** at version **2.3.7.7** trying to add the `RolesChanged` column.

This indicated a systemic issue: multiple versions throughout the schema upgrade path had columns that already existed from previous partial migrations.

## Solution: Three-Stage Proactive Fixes

### Update 108: Fix 2.3.7.7 Duplicate Error
**Status**: ✅ Complete

- Checked `RolesChanged` column in `mp_Users` → Found it already existed
- Advanced schema version from `2.3.7.6` to `2.3.7.7`
- Backup created: `mojo_db_20260415_005550.backup`

### Update 109: Pre-scan Strategy Development
**Status**: ✅ Validated approach

- Attempted to scan config files for remaining ALTER TABLE ADD COLUMN operations
- Identified that versions 2.3.7.8+ have 22 potential problematic versions
- Developed strategy to pre-add missing columns before setup attempts them

### Update 110: Comprehensive Pre-Scan & Pre-Add
**Status**: ✅ Completed - 7 columns added

**Columns Pre-Added** (preventing future duplicate errors):
1. ✅ `mp_Tag.VocabularyGuid` (char(36)) - v2.3.8.6
2. ✅ `mp_FileAttachment.ContentLength` (int) - v2.3.9.2
3. ✅ `mp_FileAttachment.ContentType` (varchar(36)) - v2.3.9.2
4. ✅ `mp_FileAttachment.ContentTitle` (varchar(255)) - v2.3.9.2
5. ✅ `mp_ModuleSettings.InheritFromSiteSettings` (varchar(50)) - v2.3.9.3
6. ✅ `mp_UserProperties.MenuDesc` (text) - v2.3.9.5
7. ✅ `mp_SharedFiles.Exclude` (INTEGER default 1) - v2.3.9.9

**Columns Already Present** (no action needed):
- `mp_Sites.AllowUserSkins` - v2.3.9.4
- `mp_SiteSettingsExDef.GroupName` - v2.3.9.7

## Complete Ordination Sequence

```
[2026-04-15 00:45:09] ✓ Update 105 - Column existence check (2.3.5.9)
[2026-04-15 00:52:33] ✓ Update 106 - Schema version advance to 2.3.5.9
[2026-04-15 00:52:51] ✓ Update 107 - Settings entries for 2.3.5.9
[2026-04-15 00:55:50] ✓ Update 108 - Fix RolesChanged duplicate at 2.3.7.7
[2026-04-15 00:56:13] ✓ Update 109 - Pre-scan strategy (validation)
[2026-04-15 00:56:32] ✓ Update 109b - Regex pattern refinement
[2026-04-15 00:57:05] ✓ Update 110 - Pre-add 7 future columns
```

## Expected Upgrade Path Forward

With these updates deployed, the setup upgrade should now:
1. ✅ Skip 2.3.5.9 (already applied)
2. ✅ Pass through 2.3.6.x and 2.3.7.x versions without duplicate errors
3. ✅ Skip duplicate checks for 2.3.8.6+ versions (columns pre-added)
4. ✅ Continue through 2.3.9.x to completion
5. ✅ Reach code version 2.9.2.3 successfully

## Deployment Verification

All backups are available for rollback:
```
mojo_db_20260415_004509.backup (Update 105)
mojo_db_20260415_005206.backup (Update 106 - rolled back due to schema mismatch)
mojo_db_20260415_005233.backup (Update 106 - final)
mojo_db_20260415_005251.backup (Update 107)
mojo_db_20260415_005550.backup (Update 108)
mojo_db_20260415_005613.backup (Update 109)
mojo_db_20260415_005632.backup (Update 109b)
mojo_db_20260415_005705.backup (Update 110)
```

## Next Steps

1. **Refresh mojoPortal setup page** (clear browser cache)
2. **Allow setup to continue** - it should advance through remaining versions
3. **Monitor completion** - setup should reach code/schema parity (2.9.2.3)
4. **Verify success** - Setup page should display "Database up to date" or similar completion message

## Technical Summary

- **Root Cause**: Schema upgrade scripts use `ALTER TABLE ADD COLUMN` without existence checks
- **Pattern**: Columns were partially added in earlier migration attempts, causing duplicates
- **Solution**: Pre-migration validation and proactive column addition before setup attempts them
- **Database**: SQLite with `sqlite3.exe` CLI-based operations
- **Framework**: PowerShell with .NET Framework 4.8.1
- **Scope**: Covers mojoPortal core schema versions 2.3.5.9 → 2.9.2.3

## Files Created/Modified
- `Updates/SQM_Update_108.ps1` - Fix 2.3.7.7 RolesChanged duplicate
- `Updates/SQM_Update_109.ps1` - First pre-scan attempt
- `Updates/SQM_Update_109b.ps1` - Regex refinement attempt
- `Updates/SQM_Update_110.ps1` - Comprehensive pre-add (DEPLOYED)
- `Updates/SQM_UPDATES_108_110_COMPREHENSIVE.md` - This document