110 lines
4.8 KiB
PowerShell
110 lines
4.8 KiB
PowerShell
<#
|
|
Apply IT-2 SQL scripts to apt_rev01 (idempotent, replayable).
|
|
Credentials come from dev/.env first, then APT_DB_* environment variables.
|
|
Passwords are never printed.
|
|
SQL files are streamed to mysql stdin as raw UTF-8 bytes: PowerShell 5.1's
|
|
text pipeline can replace Chinese literals with '?'.
|
|
Usage:
|
|
powershell -ExecutionPolicy Bypass -File dev/scripts/apply-it2-sql.ps1
|
|
powershell -ExecutionPolicy Bypass -File dev/scripts/apply-it2-sql.ps1 -DryRun
|
|
powershell -ExecutionPolicy Bypass -File dev/scripts/apply-it2-sql.ps1 -EnvFile C:\secrets\apt.env
|
|
#>
|
|
param(
|
|
[string]$EnvFile = '',
|
|
[string]$MysqlPath = 'C:\Program Files\MySQL\MySQL Server 9.5\bin\mysql.exe',
|
|
[switch]$DryRun
|
|
)
|
|
|
|
$ErrorActionPreference = 'Stop'
|
|
$devDir = Split-Path $PSScriptRoot -Parent
|
|
$sqlDir = Join-Path $devDir 'sql/apt_rev01'
|
|
$scripts = @('10_it2_columns.sql', '11_it2_seed.sql', '12_it2_permissions.sql', '13_it2_frontend_menus.sql')
|
|
|
|
foreach ($name in $scripts) {
|
|
$file = Join-Path $sqlDir $name
|
|
if (-not [System.IO.File]::Exists($file)) { throw "Missing SQL file: $file" }
|
|
}
|
|
if ($EnvFile -eq '') { $EnvFile = Join-Path $devDir '.env' }
|
|
|
|
$values = @{}
|
|
if ([System.IO.File]::Exists($EnvFile)) {
|
|
foreach ($line in [System.IO.File]::ReadAllLines($EnvFile)) {
|
|
$trimmed = $line.Trim()
|
|
if ($trimmed -eq '' -or $trimmed.StartsWith('#')) { continue }
|
|
if ($trimmed -match '^([A-Za-z0-9_]+)\s*=\s*(.*)$') { $values[$Matches[1]] = $Matches[2] }
|
|
}
|
|
Write-Host "Credentials source: $EnvFile"
|
|
} else {
|
|
Write-Host "No credentials file at $EnvFile; using APT_DB_* environment variables."
|
|
foreach ($key in @('APT_DB_HOST','APT_DB_PORT','APT_DB_NAME','APT_DB_USER','APT_DB_PASSWORD')) {
|
|
$values[$key] = [Environment]::GetEnvironmentVariable($key)
|
|
}
|
|
}
|
|
|
|
$dbHost = $values['APT_DB_HOST']; if ([string]::IsNullOrEmpty($dbHost)) { $dbHost = '127.0.0.1' }
|
|
$dbPort = $values['APT_DB_PORT']; if ([string]::IsNullOrEmpty($dbPort)) { $dbPort = '3306' }
|
|
$dbName = $values['APT_DB_NAME']; if ([string]::IsNullOrEmpty($dbName)) { $dbName = 'apt_rev01' }
|
|
$dbUser = $values['APT_DB_USER']; if ([string]::IsNullOrEmpty($dbUser)) { $dbUser = 'iotlocks' }
|
|
|
|
if ($DryRun) {
|
|
Write-Host '--- DRY RUN: no database connection, no credentials required ---'
|
|
Write-Host ("Target database: {0}:{1}/{2} (user {3})" -f $dbHost, $dbPort, $dbName, $dbUser)
|
|
foreach ($name in $scripts) {
|
|
$file = Join-Path $sqlDir $name
|
|
$lineCount = ([System.IO.File]::ReadAllLines($file, [System.Text.Encoding]::UTF8)).Count
|
|
Write-Host (" would apply {0} ({1} lines)" -f $name, $lineCount)
|
|
}
|
|
Write-Host 'Validation SELECTs in 11/12/13 print Mock marker counts, permission and frontend menu checks.'
|
|
return
|
|
}
|
|
|
|
if ([string]::IsNullOrEmpty($values['APT_DB_PASSWORD'])) {
|
|
$hint = @'
|
|
Missing APT_DB_PASSWORD. Provide one of:
|
|
1) copy dev/.env.example to dev/.env, fill APT_DB_PASSWORD, then re-run this script
|
|
2) set APT_DB_PASSWORD (plus optional APT_DB_HOST/PORT/NAME/USER) in the environment
|
|
Use -DryRun to preview SQL files without connecting to MySQL.
|
|
'@
|
|
throw $hint
|
|
}
|
|
if (-not [System.IO.File]::Exists($MysqlPath)) { throw "Missing mysql client: $MysqlPath" }
|
|
|
|
[Environment]::SetEnvironmentVariable('MYSQL_PWD', $values['APT_DB_PASSWORD'], 'Process')
|
|
try {
|
|
foreach ($name in $scripts) {
|
|
$file = Join-Path $sqlDir $name
|
|
Write-Host "--- applying $name ---"
|
|
|
|
$arguments = "--host=$dbHost --port=$dbPort --user=$dbUser --database=$dbName" +
|
|
' --default-character-set=utf8mb4 --batch'
|
|
$psi = New-Object System.Diagnostics.ProcessStartInfo
|
|
$psi.FileName = $MysqlPath
|
|
$psi.Arguments = $arguments
|
|
$psi.UseShellExecute = $false
|
|
$psi.RedirectStandardInput = $true
|
|
$psi.RedirectStandardOutput = $true
|
|
$psi.RedirectStandardError = $true
|
|
$psi.StandardOutputEncoding = [System.Text.Encoding]::UTF8
|
|
$psi.StandardErrorEncoding = [System.Text.Encoding]::UTF8
|
|
|
|
$process = [System.Diagnostics.Process]::Start($psi)
|
|
$bytes = [System.IO.File]::ReadAllBytes($file)
|
|
$offset = 0
|
|
if ($bytes.Length -ge 3 -and $bytes[0] -eq 0xEF -and $bytes[1] -eq 0xBB -and $bytes[2] -eq 0xBF) {
|
|
$offset = 3
|
|
}
|
|
$process.StandardInput.BaseStream.Write($bytes, $offset, $bytes.Length - $offset)
|
|
$process.StandardInput.BaseStream.Flush()
|
|
$process.StandardInput.Close()
|
|
$stdout = $process.StandardOutput.ReadToEnd()
|
|
$stderr = $process.StandardError.ReadToEnd()
|
|
$process.WaitForExit()
|
|
if ($stdout) { Write-Host $stdout }
|
|
if ($stderr) { Write-Host $stderr }
|
|
if ($process.ExitCode -ne 0) { throw "SQL failed: $name (exit $($process.ExitCode))" }
|
|
}
|
|
Write-Host 'IT-2 SQL scripts applied successfully.'
|
|
} finally {
|
|
[Environment]::SetEnvironmentVariable('MYSQL_PWD', $null, 'Process')
|
|
}
|