# SQM Update 105: Database Schema Migration Remediation

## Problem
During mojoPortal setup upgrade from schema v2.3.5.8 → v2.3.5.9, the system was failing with:
```
Mono.Data.Sqlite.SqliteException: SQLite error duplicate column name: PasswordResetGuid
```

This occurs because the 2.3.5.9 upgrade script tries to `ALTER TABLE mp_Users ADD COLUMN PasswordResetGuid` without checking if the column already exists. In SQLite, this causes an error if:
1. The column was previously added but not recorded in the schema version table
2. A partial migration left the column in place
3. Manual schema modifications added the column earlier

## Root Cause
The schema upgrade scripts in `Web/Setup/applications/mojoportal-core/SchemaUpgradeScripts/sqlite/2.3.5.9.config` do not use existence checks before adding columns. SQLite doesn't support `IF NOT EXISTS` syntax for `ALTER TABLE ADD COLUMN`, so the script fails on idempotent reruns.

## Solution
`SQM_Update_105.ps1` runs **before** the setup upgrade attempt and:
1. Checks if `PasswordResetGuid` column already exists in `mp_Users`
2. If it doesn't exist, adds it and sets default values
3. Checks if `IncludeInSearch` column exists in `mp_Modules`
4. If it doesn't exist, adds it and sets default values
5. Creates a backup of the database before modifications
6. Provides rollback capability if needed

## Deployment

### Dry Run (Pre-Production Test)
```powershell
.\SQM_Update_105.ps1 -ProductionPath "C:\___Work\SqliteMojo\Web"
```

### Live Deployment (Production)
```powershell
.\SQM_Update_105.ps1 -ProductionPath "C:\___Fire\SqliteMojo\Web"
```

### Rollback
If needed, restore from the backup created during execution:
```powershell
Copy-Item "C:\___Fire\SqliteMojo\Backups\mojo_db_YYYYMMDD_HHMMSS.backup" `
    "C:\___Fire\SqliteMojo\Web\Data\sqlitedb\mojo.db.config" -Force
```

## Output
The script provides:
- Status messages for each operation
- Backup file location
- Column existence checks
- Safe migration with transaction-like behavior

## Next Steps
After running this update:
1. Ensure the script completes with status "Update 105 completed successfully"
2. Return to mojoPortal setup page
3. Refresh and attempt the upgrade again—it should now proceed past v2.3.5.9
4. The setup should complete all pending version upgrades

## Technical Notes
- Uses `sqlite3.exe` command-line tool (located in `Web/Data/sqlitedb/`)
- Uses `PRAGMA table_info()` to check for column existence
- Non-destructive: only adds missing columns and sets default values
- Database backup is always created before modifications