private/Initialize-RecipeDatabase.ps1
|
function Initialize-RecipeDatabase { [CmdletBinding()] Param( [Parameter(Mandatory)] [String] $Schema, [Parameter(Mandatory)] [ValidateScript({ Test-Path $_ })] [String] $Database ) <# .SYNOPSIS Creates the database file if missing and applies schema. .DESCRIPTION This is idempotent: safe to run at startup and on every module import. #> # Apply schema $statements = $schema -split ';' | Where-Object { $_.Trim() -ne '' } foreach ($statement in $statements) { if ($statement.Trim()) { Invoke-UniversalSQLiteQuery -Path $Database -Query $statement } } # Ensure WAL and sensible concurrency/durability settings try { Invoke-UniversalSQLiteQuery -Path $Database -Query "PRAGMA journal_mode = WAL;" Invoke-UniversalSQLiteQuery -Path $Database -Query "PRAGMA synchronous = 2;" Invoke-UniversalSQLiteQuery -Path $Database -Query "PRAGMA wal_autocheckpoint = 1000;" Write-Verbose "Initialization: set PRAGMA journal_mode=WAL, synchronous=2, wal_autocheckpoint=1000" } catch { Write-Warning "Failed to set PRAGMA during initialization: $_" } # Migrations $cols = Invoke-UniversalSQLiteQuery -Path $Database -Query "PRAGMA table_info(Recipes);" if (($cols | Where-Object { $_.name -eq 'IsFavorite' }).Count -eq 0) { $Null = Invoke-UniversalSQLiteQuery -Path $Database -Query 'ALTER TABLE Recipes ADD COLUMN IsFavorite INTEGER NOT NULL DEFAULT 0;' } if (($cols | Where-Object { $_.name -eq 'FavoritedAt' }).Count -eq 0) { $null = Invoke-UniversalSQLiteQuery -Path $Database -Query 'ALTER TABLE Recipes ADD COLUMN FavoritedAt TEXT;' } if (($cols | Where-Object { $_.name -eq 'ImageFileName' }).Count -eq 0) { $null = Invoke-UniversalSQLiteQuery -Path $Database -Query 'ALTER TABLE Recipes ADD COLUMN ImageFileName TEXT NULL;' } if (($cols | Where-Object { $_.name -eq 'ImageUpdatedAt' }).Count -eq 0) { $null = Invoke-UniversalSQLiteQuery -Path $Database -Query 'ALTER TABLE Recipes ADD COLUMN ImageUpdatedAt TEXT NULL;' } } |