<# .SYNOPSIS Migrates territory data from database\myAccessFile.accdb to database\app.sqlite. .DESCRIPTION Reads the Territories, Households, and HouseholderNames tables from the Access database and inserts them into the matching SQLite tables. Prerequisites: - 64-bit Microsoft Access Database Engine (ACE OLEDB 12.0): https://www.microsoft.com/en-us/download/details.aspx?id=54920 If you have 32-bit Office installed, run from 32-bit PowerShell instead: %SystemRoot%\SysWOW64\WindowsPowerShell\v1.0\powershell.exe - sqlite3.exe on PATH, in the project root, or in database\ . Download: https://www.sqlite.org/download.html (sqlite-tools-win-x64-*.zip) The script will attempt to download it automatically if not found. .PARAMETER AccessFile Path to the .accdb file. Relative paths resolve from the project root. Default: database\myAccessFile.accdb .PARAMETER SQLiteFile Path to the SQLite database. Relative paths resolve from the project root. Default: database\app.sqlite .PARAMETER DryRun Print Access row counts without writing any data to SQLite. .PARAMETER Sqlite3Path Explicit path to sqlite3.exe (overrides automatic search). .EXAMPLE .\database\Migrate-AccessToSQLite.ps1 .\database\Migrate-AccessToSQLite.ps1 -DryRun .\database\Migrate-AccessToSQLite.ps1 -Sqlite3Path C:\sqlite\sqlite3.exe #> [CmdletBinding()] param( [string]$AccessFile = "database\myAccessFile.accdb", [string]$SQLiteFile = "database\app.sqlite", [switch]$DryRun, [string]$Sqlite3Path = "" ) Set-StrictMode -Version Latest $ErrorActionPreference = 'Stop' # ── Resolve project root ─────────────────────────────────────────────────────── $projectRoot = if ($PSScriptRoot) { $PSScriptRoot } else { $PWD.Path } if ($projectRoot -match '[/\\]database$') { $projectRoot = Split-Path $projectRoot -Parent } function Resolve-ProjectPath([string]$p) { if ([System.IO.Path]::IsPathRooted($p)) { return $p } return Join-Path $projectRoot $p } $accessPath = Resolve-ProjectPath $AccessFile $sqlitePath = Resolve-ProjectPath $SQLiteFile if (-not (Test-Path $accessPath)) { Write-Error "Access file not found: $accessPath" exit 1 } if (-not (Test-Path $sqlitePath)) { Write-Error "SQLite file not found: $sqlitePath`nRun the PHP migrations first to create the schema." exit 1 } Write-Host "Access file : $accessPath" Write-Host "SQLite file : $sqlitePath" # ── Locate sqlite3.exe ───────────────────────────────────────────────────────── function Find-Sqlite3([string]$hint) { $candidates = @( $hint, (Get-Command sqlite3.exe -ErrorAction SilentlyContinue | Select-Object -ExpandProperty Source -ErrorAction SilentlyContinue), (Join-Path $projectRoot "sqlite3.exe"), (Join-Path $projectRoot "database\sqlite3.exe"), "C:\sqlite\sqlite3.exe", "C:\tools\sqlite3.exe", "C:\ProgramData\chocolatey\bin\sqlite3.exe" ) | Where-Object { $_ -and (Test-Path $_ -ErrorAction SilentlyContinue) } return $candidates | Select-Object -First 1 } $sqlite3 = Find-Sqlite3 $Sqlite3Path if (-not $sqlite3 -and -not $DryRun) { Write-Host "" Write-Host "sqlite3.exe not found — attempting automatic download ..." $destExe = Join-Path $projectRoot "sqlite3.exe" try { $dlPage = Invoke-WebRequest -Uri "https://www.sqlite.org/download.html" -UseBasicParsing $zipName = ($dlPage.Content | Select-String -Pattern 'sqlite-tools-win-x64-[\d]+\.zip' -AllMatches ).Matches[0].Value if (-not $zipName) { throw "Could not parse download filename from sqlite.org" } $zipUrl = "https://www.sqlite.org/$zipName" $zipPath = Join-Path $env:TEMP $zipName Write-Host " Downloading $zipUrl ..." Invoke-WebRequest -Uri $zipUrl -OutFile $zipPath -UseBasicParsing $extractDir = Join-Path $env:TEMP "sqlite_extract_$(Get-Random)" Expand-Archive -Path $zipPath -DestinationPath $extractDir -Force $extracted = Get-ChildItem -Path $extractDir -Filter sqlite3.exe -Recurse | Select-Object -First 1 if ($extracted) { Copy-Item $extracted.FullName $destExe -Force $sqlite3 = $destExe Write-Host " sqlite3.exe saved to: $destExe" } else { throw "sqlite3.exe not found inside downloaded archive" } } catch { Write-Host "" Write-Host " Automatic download failed: $_" Write-Host "" Write-Host " Please download sqlite3.exe manually:" Write-Host " 1. Visit https://www.sqlite.org/download.html" Write-Host " 2. Download 'sqlite-tools-win-x64-*.zip'" Write-Host " 3. Extract sqlite3.exe to: $projectRoot" Write-Host " Then re-run this script." exit 1 } } if ($sqlite3) { Write-Host "sqlite3.exe : $sqlite3" } # ── Open Access database via OleDb ──────────────────────────────────────────── Add-Type -AssemblyName System.Data $script:oleConn = [System.Data.OleDb.OleDbConnection]::new( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$accessPath;Mode=Read;" ) try { $script:oleConn.Open() } catch { Write-Error @" Cannot open Access database. Error: $($_.Exception.Message) Ensure the 64-bit Microsoft Access Database Engine is installed: https://www.microsoft.com/en-us/download/details.aspx?id=54920 If you have 32-bit Office, run this script from 32-bit PowerShell: %SystemRoot%\SysWOW64\WindowsPowerShell\v1.0\powershell.exe .\database\Migrate-AccessToSQLite.ps1 "@ exit 1 } # Reads all rows from an Access table and returns a List[hashtable]. # Using a DataReader avoids DataTable/pipeline-unwrap quirks in PowerShell. function Read-AccessTable([string]$TableName) { $rows = [System.Collections.Generic.List[hashtable]]::new() $cmd = [System.Data.OleDb.OleDbCommand]::new("SELECT * FROM [$TableName]", $script:oleConn) $rdr = $cmd.ExecuteReader() $n = $rdr.FieldCount $cols = [string[]]::new($n) for ($i = 0; $i -lt $n; $i++) { $cols[$i] = $rdr.GetName($i) } while ($rdr.Read()) { $row = @{} for ($i = 0; $i -lt $n; $i++) { $v = $rdr.GetValue($i) $row[$cols[$i]] = if ($v -is [System.DBNull]) { $null } else { $v } } $rows.Add($row) } $rdr.Dispose() $cmd.Dispose() return $rows } # ── SQL value helpers ───────────────────────────────────────────────────────── function Esc-Str([object]$v) { if ($null -eq $v) { return 'NULL' } $s = [string]$v if ($s -eq '') { return 'NULL' } return "'" + $s.Replace("'", "''") + "'" } # For NOT NULL VARCHAR columns: fall back to empty string rather than NULL. function EscStrNN([object]$v) { if ($null -eq $v) { return "''" } return "'" + ([string]$v).Replace("'", "''") + "'" } function Esc-Int([object]$v) { if ($null -eq $v) { return 'NULL' } $i = 0 if ([int]::TryParse([string]$v, [ref]$i)) { return [string]$i } return 'NULL' } function Esc-Float([object]$v) { if ($null -eq $v) { return 'NULL' } $f = 0.0 $style = [System.Globalization.NumberStyles]::Float $culture = [System.Globalization.CultureInfo]::InvariantCulture if ([double]::TryParse([string]$v, $style, $culture, [ref]$f)) { return $f.ToString($culture) } return 'NULL' } function Esc-Bool([object]$v) { if ($null -eq $v) { return '0' } $s = ([string]$v).ToLower().Trim() if ($s -in @('true', '1', 'yes', '-1', 'on')) { return '1' } return '0' } function Esc-Date([object]$v, [string]$fmt = 'yyyy-MM-dd') { if ($null -eq $v) { return 'NULL' } if ($v -is [datetime]) { return "'" + ([datetime]$v).ToString($fmt) + "'" } $dt = [datetime]::MinValue if ([datetime]::TryParse([string]$v, [ref]$dt)) { return "'" + $dt.ToString($fmt) + "'" } return 'NULL' } function Esc-DateTime([object]$v) { return Esc-Date $v 'yyyy-MM-dd HH:mm:ss' } # ── Read all three tables ───────────────────────────────────────────────────── Write-Host "" Write-Host "[1/3] Reading Territories ..." $tTerritories = Read-AccessTable 'Territories' Write-Host " $($tTerritories.Count) row(s)" Write-Host "[2/3] Reading Households ..." $tHouseholds = Read-AccessTable 'Households' Write-Host " $($tHouseholds.Count) row(s)" Write-Host "[3/3] Reading HouseholderNames ..." $tHouseholderNames = Read-AccessTable 'HouseholderNames' Write-Host " $($tHouseholderNames.Count) row(s)" $script:oleConn.Close() if ($DryRun) { Write-Host "" Write-Host "-- DRY RUN: no data written --" Write-Host (" {0,-25} {1}" -f "territories", $tTerritories.Count) Write-Host (" {0,-25} {1}" -f "households", $tHouseholds.Count) Write-Host (" {0,-25} {1}" -f "householder_names", $tHouseholderNames.Count) exit 0 } # ── Build SQL ───────────────────────────────────────────────────────────────── $now = Get-Date -Format 'yyyy-MM-dd HH:mm:ss' $sql = [System.Text.StringBuilder]::new() [void]$sql.AppendLine("PRAGMA foreign_keys = OFF;") [void]$sql.AppendLine("PRAGMA journal_mode = WAL;") [void]$sql.AppendLine("BEGIN TRANSACTION;") [void]$sql.AppendLine("") [void]$sql.AppendLine("DELETE FROM householder_names;") [void]$sql.AppendLine("DELETE FROM households;") [void]$sql.AppendLine("DELETE FROM territories;") [void]$sql.AppendLine("") # Territories foreach ($row in $tTerritories) { $id = [int]$row['Id'] $name = EscStrNN $row['Name'] $desc = Esc-Str $row['Description'] $coord = Esc-Str $row['Coordinates'] [void]$sql.AppendLine( "INSERT INTO territories (id, name, description, coordinates, created_at, updated_at) " + "VALUES ($id, $name, $desc, $coord, '$now', '$now');" ) } [void]$sql.AppendLine("") # Households foreach ($row in $tHouseholds) { $id = [int]$row['Id'] $terrId = [int]$row['TerritoryId'] $addr = EscStrNN $row['Address'] $sNum = Esc-Int $row['StreetNumber'] $sName = Esc-Str $row['StreetName'] $lat = Esc-Float $row['Latitude'] $lon = Esc-Float $row['Longitude'] $isBiz = Esc-Bool $row['IsBusiness'] $dnc = Esc-Bool $row['DoNotCall'] $dncDate = Esc-Date $row['DoNotCallDate'] $dncNotes = Esc-Str $row['DoNotCallNotes'] $dncPriv = Esc-Str $row['DoNotCallPrivateNotes'] [void]$sql.AppendLine( "INSERT INTO households (id, territory_id, address, street_number, street_name, " + "latitude, longitude, is_business, do_not_call, do_not_call_date, " + "do_not_call_notes, do_not_call_private_notes, created_at, updated_at) " + "VALUES ($id, $terrId, $addr, $sNum, $sName, $lat, $lon, $isBiz, $dnc, " + "$dncDate, $dncNotes, $dncPriv, '$now', '$now');" ) } [void]$sql.AppendLine("") # HouseholderNames foreach ($row in $tHouseholderNames) { $id = [int]$row['Id'] $hhId = [int]$row['HouseholdId'] $name = EscStrNN $row['Name'] $letRet = Esc-Bool $row['LetterReturned'] $retDate = Esc-DateTime $row['ReturnDate'] $creAt = Esc-DateTime $row['Created'] if ($creAt -eq 'NULL') { $creAt = "'$now'" } [void]$sql.AppendLine( "INSERT INTO householder_names (id, household_id, name, letter_returned, " + "return_date, created_at, updated_at) " + "VALUES ($id, $hhId, $name, $letRet, $retDate, $creAt, '$now');" ) } [void]$sql.AppendLine("") [void]$sql.AppendLine("COMMIT;") [void]$sql.AppendLine("PRAGMA foreign_keys = ON;") # ── Execute SQL via sqlite3 stdin ───────────────────────────────────────────── Write-Host "" Write-Host "Writing to SQLite ..." $output = $sql.ToString() | & $sqlite3 $sqlitePath 2>&1 if ($LASTEXITCODE -ne 0) { Write-Host "sqlite3 output:" $output | ForEach-Object { Write-Host " $_" } Write-Error "sqlite3 exited with code $LASTEXITCODE" exit 1 } # ── Verify ──────────────────────────────────────────────────────────────────── Write-Host "" Write-Host "Migration complete. SQLite row counts:" $countSql = @( "SELECT 'territories', COUNT(*) FROM territories;" "SELECT 'households', COUNT(*) FROM households;" "SELECT 'householder_names', COUNT(*) FROM householder_names;" ) -join "`n" $counts = ($countSql | & $sqlite3 $sqlitePath) 2>&1 foreach ($line in $counts) { $parts = [string]$line -split '\|' Write-Host (" {0,-25} {1}" -f $parts[0].Trim(), $parts[1].Trim()) }