398 lines
12 KiB
PowerShell
398 lines
12 KiB
PowerShell
param(
|
|
[string]$SourcePath = (Join-Path $PSScriptRoot '..\sql_statements.txt')
|
|
)
|
|
|
|
$ErrorActionPreference = 'Stop'
|
|
|
|
$source = $SourcePath
|
|
$target = Join-Path $PSScriptRoot '..\sql\regression_from_sql_statements.sql'
|
|
$targetWithSkipped = Join-Path $PSScriptRoot '..\sql\regression_from_sql_statements_with_skipped.sql'
|
|
$skipReportCsv = Join-Path $PSScriptRoot '..\sql\regression_from_sql_statements_skip_report.csv'
|
|
|
|
function Convert-ParamLiteral {
|
|
param([string]$Value)
|
|
|
|
if ($null -eq $Value) {
|
|
return 'NULL'
|
|
}
|
|
|
|
$trimmed = $Value.Trim()
|
|
|
|
if ($trimmed -match '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$') {
|
|
return "'$trimmed'::uuid"
|
|
}
|
|
|
|
if ($trimmed -match '^(?i:true|false)$') {
|
|
return $trimmed.ToLowerInvariant()
|
|
}
|
|
|
|
if ($trimmed -match '^\d+$') {
|
|
return $trimmed
|
|
}
|
|
|
|
$escaped = $trimmed.Replace("'", "''")
|
|
return "'$escaped'"
|
|
}
|
|
|
|
function Apply-ParamsToSql {
|
|
param(
|
|
[string]$Sql,
|
|
[string]$ParamsText
|
|
)
|
|
|
|
$result = $Sql
|
|
$normalizedParams = if ($null -eq $ParamsText) { '' } else { $ParamsText.Trim() }
|
|
|
|
# Newer EF logs wrap parameter payload in quotes inside [Parameters=[...]].
|
|
if ($normalizedParams.StartsWith('"') -and $normalizedParams.EndsWith('"')) {
|
|
$normalizedParams = $normalizedParams.Trim('"')
|
|
}
|
|
|
|
if ([string]::IsNullOrWhiteSpace($normalizedParams) -or $normalizedParams -eq '[]') {
|
|
return $result
|
|
}
|
|
|
|
# Truncated arrays in logs contain ellipsis; these are skipped later.
|
|
if ($normalizedParams -like '*...*') {
|
|
return $result
|
|
}
|
|
|
|
$arrayPattern = [regex]"@(?<name>[A-Za-z0-9_]+)=\{(?<vals>.*?)\}\s*\(DbType\s*=\s*Object\)"
|
|
foreach ($match in $arrayPattern.Matches($normalizedParams)) {
|
|
$name = $match.Groups['name'].Value
|
|
$valsRaw = $match.Groups['vals'].Value
|
|
$valueMatches = [regex]::Matches($valsRaw, "'([^']*)'")
|
|
|
|
$renderedVals = @()
|
|
foreach ($valueMatch in $valueMatches) {
|
|
$renderedVals += (Convert-ParamLiteral -Value $valueMatch.Groups[1].Value)
|
|
}
|
|
|
|
$arrayExpr = if ($renderedVals.Count -gt 0) {
|
|
'ARRAY[' + ($renderedVals -join ', ') + ']'
|
|
}
|
|
else {
|
|
'ARRAY[]::text[]'
|
|
}
|
|
|
|
$tokenPattern = '(?<![A-Za-z0-9_])@' + [regex]::Escape($name) + '(?![A-Za-z0-9_])'
|
|
$result = [regex]::Replace(
|
|
$result,
|
|
$tokenPattern,
|
|
[System.Text.RegularExpressions.MatchEvaluator]{ param($m) $arrayExpr }
|
|
)
|
|
}
|
|
|
|
$scalarPattern = [regex]"@(?<name>[A-Za-z0-9_]+)='(?<val>(?:''|[^'])*)'"
|
|
foreach ($match in $scalarPattern.Matches($normalizedParams)) {
|
|
$name = $match.Groups['name'].Value
|
|
$rawVal = $match.Groups['val'].Value -replace "''", "'"
|
|
$literal = Convert-ParamLiteral -Value $rawVal
|
|
$tokenPattern = '(?<![A-Za-z0-9_])@' + [regex]::Escape($name) + '(?![A-Za-z0-9_])'
|
|
$result = [regex]::Replace(
|
|
$result,
|
|
$tokenPattern,
|
|
[System.Text.RegularExpressions.MatchEvaluator]{ param($m) $literal }
|
|
)
|
|
}
|
|
|
|
return $result
|
|
}
|
|
|
|
function Test-SqlStatementCompleteness {
|
|
param([string]$Sql)
|
|
|
|
if ([string]::IsNullOrWhiteSpace($Sql)) {
|
|
return $false
|
|
}
|
|
|
|
$trimmed = $Sql.Trim()
|
|
$oneLine = ($trimmed -replace '\s+', ' ')
|
|
|
|
if ($oneLine -match '^(?i:SELECT\s+[^;]*\s+WHERE\s+)' -and $oneLine -notmatch '(?i:\sFROM\s)') {
|
|
return $false
|
|
}
|
|
|
|
if ($oneLine -match '^(?i:SELECT\s+)' -and
|
|
$oneLine -notmatch '(?i:\sFROM\s|\sEXISTS\s*\(|^SELECT\s+1\b)') {
|
|
return $false
|
|
}
|
|
|
|
$depth = 0
|
|
foreach ($ch in $trimmed.ToCharArray()) {
|
|
if ($ch -eq '(') { $depth++ }
|
|
elseif ($ch -eq ')') {
|
|
$depth--
|
|
if ($depth -lt 0) { return $false }
|
|
}
|
|
}
|
|
if ($depth -ne 0) {
|
|
return $false
|
|
}
|
|
|
|
if ($oneLine -match '(?i:(SELECT|FROM|WHERE|JOIN|INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN|ON|ORDER BY|GROUP BY|LIMIT|OFFSET|UNION)\s*$)') {
|
|
return $false
|
|
}
|
|
|
|
if ($oneLine -match ',\s*$') {
|
|
return $false
|
|
}
|
|
|
|
return $true
|
|
}
|
|
|
|
function Split-SqlCandidates {
|
|
param([string]$Sql)
|
|
|
|
if ([string]::IsNullOrWhiteSpace($Sql)) {
|
|
return @()
|
|
}
|
|
|
|
$lines = $Sql -split "`n"
|
|
$startIndexes = New-Object System.Collections.Generic.List[int]
|
|
|
|
for ($i = 0; $i -lt $lines.Count; $i++) {
|
|
if ($lines[$i] -match '^(SELECT|UPDATE|INSERT|DELETE|CREATE|ALTER|DROP|WITH)\b') {
|
|
$startIndexes.Add($i)
|
|
}
|
|
}
|
|
|
|
if ($startIndexes.Count -le 1) {
|
|
return @($Sql.Trim())
|
|
}
|
|
|
|
$chunks = New-Object System.Collections.Generic.List[string]
|
|
for ($s = 0; $s -lt $startIndexes.Count; $s++) {
|
|
$start = $startIndexes[$s]
|
|
$end = if ($s -lt $startIndexes.Count - 1) { $startIndexes[$s + 1] - 1 } else { $lines.Count - 1 }
|
|
if ($end -lt $start) {
|
|
continue
|
|
}
|
|
|
|
$segment = ($lines[$start..$end] -join "`n").Trim()
|
|
if (-not [string]::IsNullOrWhiteSpace($segment)) {
|
|
$chunks.Add($segment)
|
|
}
|
|
}
|
|
|
|
return $chunks
|
|
}
|
|
|
|
function Normalize-SqlLogLine {
|
|
param([string]$Line)
|
|
|
|
if ($null -eq $Line) {
|
|
return ''
|
|
}
|
|
|
|
$normalized = $Line.Trim()
|
|
|
|
# SQL text in this log file uses C-style escaped identifier quotes.
|
|
$normalized = $normalized -replace '\\"', '"'
|
|
|
|
# Strip only leading wrapper quotes from EF logs (keep trailing identifier quotes).
|
|
if ($normalized.StartsWith('""')) {
|
|
$normalized = $normalized.Substring(2)
|
|
}
|
|
elseif ($normalized.StartsWith('"')) {
|
|
$normalized = $normalized.Substring(1)
|
|
}
|
|
|
|
return $normalized
|
|
}
|
|
|
|
$entries = New-Object System.Collections.Generic.List[object]
|
|
$current = $null
|
|
|
|
$commandLinePattern = [regex]'^\[(?<ts>[^\]]+)\].*Executed DbCommand\s*\("?(?<ms>\d+)"?ms\)\s*\[Parameters=\[(?<params>.*?)\],\s*CommandType=''Text'',\s*CommandTimeout=''(?<timeout>\d+)''\](?<tail>.*)$'
|
|
$logLinePattern = [regex]'^\[(?:\d{2}:\d{2}:\d{2}\.\d{3}|\d{4}-\d{2}-\d{2}\s+\d{2}:\d{2}:\d{2}\.\d{3}(?:\s+[^\]]+)?)\]'
|
|
|
|
Get-Content -Path $source | ForEach-Object {
|
|
$line = $_
|
|
$cmdMatch = $commandLinePattern.Match($line)
|
|
|
|
if ($cmdMatch.Success) {
|
|
if ($null -ne $current) {
|
|
$entries.Add([pscustomobject]@{
|
|
ms = $current.ms
|
|
timeout = $current.timeout
|
|
params = $current.params
|
|
sql = ($current.sqlLines -join "`n").Trim()
|
|
})
|
|
}
|
|
|
|
$current = [ordered]@{
|
|
ms = $cmdMatch.Groups['ms'].Value
|
|
timeout = $cmdMatch.Groups['timeout'].Value
|
|
params = $cmdMatch.Groups['params'].Value
|
|
sqlLines = New-Object System.Collections.Generic.List[string]
|
|
}
|
|
|
|
$tail = Normalize-SqlLogLine -Line $cmdMatch.Groups['tail'].Value
|
|
if (-not [string]::IsNullOrWhiteSpace($tail)) {
|
|
$current.sqlLines.Add($tail)
|
|
}
|
|
|
|
return
|
|
}
|
|
|
|
if ($null -eq $current) {
|
|
return
|
|
}
|
|
|
|
if ($logLinePattern.IsMatch($line)) {
|
|
$entries.Add([pscustomobject]@{
|
|
ms = $current.ms
|
|
timeout = $current.timeout
|
|
params = $current.params
|
|
sql = ($current.sqlLines -join "`n").Trim()
|
|
})
|
|
$current = $null
|
|
return
|
|
}
|
|
|
|
$normalizedLine = Normalize-SqlLogLine -Line $line
|
|
if (-not [string]::IsNullOrWhiteSpace($normalizedLine)) {
|
|
$current.sqlLines.Add($normalizedLine)
|
|
}
|
|
}
|
|
|
|
if ($null -ne $current) {
|
|
$entries.Add([pscustomobject]@{
|
|
ms = $current.ms
|
|
timeout = $current.timeout
|
|
params = $current.params
|
|
sql = ($current.sqlLines -join "`n").Trim()
|
|
})
|
|
}
|
|
|
|
$output = New-Object System.Collections.Generic.List[string]
|
|
$output.Add('-- Auto-generated from sql_statements.txt for regression testing')
|
|
$output.Add("-- Source: $source")
|
|
$output.Add("-- Generated: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss K')")
|
|
$output.Add('-- Unresolved parameterized statements are skipped and counted in summary.')
|
|
$output.Add('\\timing on')
|
|
$output.Add("SET statement_timeout = '0';")
|
|
$output.Add('')
|
|
|
|
$outputWithSkipped = New-Object System.Collections.Generic.List[string]
|
|
$outputWithSkipped.Add('-- Auto-generated from sql_statements.txt for regression testing')
|
|
$outputWithSkipped.Add("-- Source: $source")
|
|
$outputWithSkipped.Add("-- Generated: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss K')")
|
|
$outputWithSkipped.Add('-- Includes unresolved statements as commented blocks for manual editing.')
|
|
$outputWithSkipped.Add('\\timing on')
|
|
$outputWithSkipped.Add("SET statement_timeout = '0';")
|
|
$outputWithSkipped.Add('')
|
|
|
|
$totalExtracted = 0
|
|
$written = 0
|
|
$skipped = 0
|
|
$writtenWithSkipped = 0
|
|
$skipReasonCounts = @{}
|
|
|
|
function Add-SkipReason {
|
|
param([string]$Reason)
|
|
|
|
if (-not $skipReasonCounts.ContainsKey($Reason)) {
|
|
$skipReasonCounts[$Reason] = 0
|
|
}
|
|
|
|
$skipReasonCounts[$Reason]++
|
|
}
|
|
|
|
foreach ($entry in $entries) {
|
|
$totalExtracted++
|
|
|
|
$sqlText = $entry.sql
|
|
if ([string]::IsNullOrWhiteSpace($sqlText)) {
|
|
$skipped++
|
|
Add-SkipReason -Reason 'empty_sql_block'
|
|
continue
|
|
}
|
|
|
|
# Drop only whole-statement wrapper quotes emitted by EF logs.
|
|
$sqlText = $sqlText.Trim()
|
|
$sqlText = $sqlText -replace '^"+(?=(SELECT|UPDATE|INSERT|DELETE|CREATE|ALTER|DROP|WITH)\b)', ''
|
|
$sqlText = $sqlText -replace '"\s*$', ''
|
|
|
|
$sqlText = Apply-ParamsToSql -Sql $sqlText -ParamsText $entry.params
|
|
|
|
if ($sqlText -notmatch ';\s*$') {
|
|
$sqlText += ';'
|
|
}
|
|
|
|
$writtenWithSkipped++
|
|
$outputWithSkipped.Add("-- Query #$writtenWithSkipped | logged_duration_ms=$($entry.ms) | timeout_s=$($entry.timeout)")
|
|
|
|
$topLevelStarts = ([regex]::Matches($sqlText, '(?m)^(SELECT|UPDATE|INSERT|DELETE|CREATE|ALTER|DROP|WITH)\b')).Count
|
|
if ($topLevelStarts -gt 1) {
|
|
$skipped++
|
|
Add-SkipReason -Reason 'multiple_top_level_sql_starts'
|
|
$outputWithSkipped.Add('-- SKIPPED IN RUNNABLE FILE: block contains multiple top-level SQL starts (likely concatenated log fragments)')
|
|
foreach ($line in ($sqlText -split "`n")) {
|
|
$outputWithSkipped.Add('-- ' + $line)
|
|
}
|
|
$outputWithSkipped.Add('')
|
|
continue
|
|
}
|
|
|
|
if ($sqlText -match '@[A-Za-z_][A-Za-z0-9_]*') {
|
|
$skipped++
|
|
Add-SkipReason -Reason 'unresolved_parameter_placeholders'
|
|
$outputWithSkipped.Add('-- SKIPPED IN RUNNABLE FILE: unresolved placeholders remain')
|
|
if (-not [string]::IsNullOrWhiteSpace($entry.params)) {
|
|
$outputWithSkipped.Add("-- Logged parameters: [$($entry.params)]")
|
|
}
|
|
foreach ($line in ($sqlText -split "`n")) {
|
|
$outputWithSkipped.Add('-- ' + $line)
|
|
}
|
|
$outputWithSkipped.Add('')
|
|
continue
|
|
}
|
|
|
|
if (-not (Test-SqlStatementCompleteness -Sql $sqlText)) {
|
|
$skipped++
|
|
Add-SkipReason -Reason 'syntactically_incomplete_or_truncated'
|
|
$outputWithSkipped.Add('-- SKIPPED IN RUNNABLE FILE: statement appears truncated or syntactically incomplete')
|
|
foreach ($line in ($sqlText -split "`n")) {
|
|
$outputWithSkipped.Add('-- ' + $line)
|
|
}
|
|
$outputWithSkipped.Add('')
|
|
continue
|
|
}
|
|
|
|
$written++
|
|
$output.Add("-- Query #$written | logged_duration_ms=$($entry.ms) | timeout_s=$($entry.timeout)")
|
|
$output.Add($sqlText)
|
|
$output.Add('')
|
|
|
|
$outputWithSkipped.Add($sqlText)
|
|
$outputWithSkipped.Add('')
|
|
}
|
|
|
|
$output.Add("-- Summary: total_extracted=$totalExtracted, written=$written, skipped_unresolved=$skipped")
|
|
$outputWithSkipped.Add("-- Summary: total_extracted=$totalExtracted, written_total=$writtenWithSkipped, unresolved_commented=$skipped")
|
|
|
|
$skipReasonRows = @()
|
|
foreach ($key in ($skipReasonCounts.Keys | Sort-Object)) {
|
|
$count = [int]$skipReasonCounts[$key]
|
|
$pct = if ($skipped -gt 0) { [math]::Round(($count * 100.0) / $skipped, 2) } else { 0.0 }
|
|
$skipReasonRows += [pscustomobject]@{
|
|
reason = $key
|
|
count = $count
|
|
percent_of_skipped = $pct
|
|
}
|
|
}
|
|
|
|
$skipReasonRows | Export-Csv -Path $skipReportCsv -NoTypeInformation -Encoding UTF8
|
|
|
|
Set-Content -Path $target -Value $output -Encoding UTF8
|
|
Set-Content -Path $targetWithSkipped -Value $outputWithSkipped -Encoding UTF8
|
|
|
|
Write-Output "Generated: $target"
|
|
Write-Output "Generated: $targetWithSkipped"
|
|
Write-Output "Generated: $skipReportCsv"
|
|
Write-Output "Total extracted: $totalExtracted"
|
|
Write-Output "Written runnable: $written"
|
|
Write-Output "Skipped unresolved: $skipped"
|