310 lines
12 KiB
PowerShell
310 lines
12 KiB
PowerShell
param(
|
|
[string]$Server = $env:ONEC_SQL_SERVER,
|
|
[string]$Database = $env:ONEC_SQL_DATABASE,
|
|
[string]$User = $env:ONEC_SQL_USER,
|
|
[string]$Password = $env:ONEC_SQL_PASSWORD,
|
|
[string]$ReadResultPath,
|
|
[string]$RouteIndexPath,
|
|
[string]$OutputPath
|
|
)
|
|
|
|
Set-StrictMode -Version Latest
|
|
$ErrorActionPreference = "Stop"
|
|
|
|
if (-not $Server) { throw "Server is required. Use -Server or ONEC_SQL_SERVER." }
|
|
if (-not $Database) { throw "Database is required. Use -Database or ONEC_SQL_DATABASE." }
|
|
if (-not $User) { throw "User is required. Use -User or ONEC_SQL_USER." }
|
|
if (-not $Password) { throw "Password is required. Use -Password or ONEC_SQL_PASSWORD." }
|
|
if (-not $ReadResultPath) { throw "ReadResultPath is required." }
|
|
if (-not $RouteIndexPath) { throw "RouteIndexPath is required." }
|
|
if (-not $OutputPath) { throw "OutputPath is required." }
|
|
|
|
function Test-HasProperty {
|
|
param([object]$Object, [string]$Name)
|
|
return $null -ne $Object -and ($Object.PSObject.Properties.Name -contains $Name)
|
|
}
|
|
|
|
function Convert-HexToBytes {
|
|
param([string]$Hex)
|
|
if (-not $Hex -or ($Hex.Length % 2) -ne 0) { return $null }
|
|
$bytes = [byte[]]::new($Hex.Length / 2)
|
|
for ($i = 0; $i -lt $bytes.Length; $i++) {
|
|
$bytes[$i] = [Convert]::ToByte($Hex.Substring($i * 2, 2), 16)
|
|
}
|
|
return $bytes
|
|
}
|
|
|
|
function Convert-HexToInt32 {
|
|
param([string]$Hex)
|
|
if (-not $Hex) { return $null }
|
|
$bytes = Convert-HexToBytes $Hex
|
|
if (-not $bytes) { return $null }
|
|
$value = 0
|
|
foreach ($byte in $bytes) {
|
|
$value = ($value * 256) + [int]$byte
|
|
}
|
|
return $value
|
|
}
|
|
|
|
function Convert-SqlValue {
|
|
param([object]$Value)
|
|
if ($null -eq $Value -or $Value -is [DBNull]) { return $null }
|
|
if ($Value -is [byte[]]) {
|
|
return [pscustomobject]@{
|
|
kind = "binary"
|
|
length = $Value.Length
|
|
hex = ([BitConverter]::ToString($Value) -replace "-", "").ToLowerInvariant()
|
|
}
|
|
}
|
|
if ($Value -is [DateTime]) { return $Value.ToString("o") }
|
|
return $Value
|
|
}
|
|
|
|
function Quote-Identifier {
|
|
param([string]$Name)
|
|
return "[" + ($Name -replace "]", "]]") + "]"
|
|
}
|
|
|
|
function Get-Columns {
|
|
param([System.Data.SqlClient.SqlConnection]$Connection, [string]$Table)
|
|
$command = $Connection.CreateCommand()
|
|
$command.CommandText = @"
|
|
SELECT COLUMN_NAME
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME = @table
|
|
ORDER BY ORDINAL_POSITION
|
|
"@
|
|
$null = $command.Parameters.Add("@table", [System.Data.SqlDbType]::NVarChar, 128)
|
|
$command.Parameters["@table"].Value = $Table
|
|
$reader = $command.ExecuteReader()
|
|
$columns = New-Object System.Collections.Generic.List[string]
|
|
try {
|
|
while ($reader.Read()) {
|
|
$columns.Add([string]$reader.GetValue(0))
|
|
}
|
|
}
|
|
finally {
|
|
$reader.Close()
|
|
$command.Dispose()
|
|
}
|
|
return [string[]]$columns.ToArray()
|
|
}
|
|
|
|
function Select-PresentationColumns {
|
|
param([string[]]$Columns)
|
|
$wanted = New-Object System.Collections.Generic.List[string]
|
|
foreach ($column in @("_IDRRef", "_Code", "_Description", "_Number", "_Date_Time", "_Posted", "_Marked", "_EnumOrder")) {
|
|
if ($Columns -contains $column) {
|
|
$wanted.Add($column)
|
|
}
|
|
}
|
|
if ($wanted.Count -eq 0 -and ($Columns -contains "_IDRRef")) {
|
|
$wanted.Add("_IDRRef")
|
|
}
|
|
return [string[]]$wanted.ToArray()
|
|
}
|
|
|
|
function Resolve-Row {
|
|
param(
|
|
[System.Data.SqlClient.SqlConnection]$Connection,
|
|
[string]$Table,
|
|
[string[]]$Columns,
|
|
[byte[]]$IdBytes
|
|
)
|
|
if (-not ($Columns -contains "_IDRRef")) {
|
|
return [pscustomobject]@{ found = $false; reason = "target_table_without__IDRRef"; values = $null }
|
|
}
|
|
$select = ($Columns | ForEach-Object { Quote-Identifier $_ }) -join ", "
|
|
$command = $Connection.CreateCommand()
|
|
$command.CommandText = "SELECT TOP (1) $select FROM $(Quote-Identifier $Table) WHERE [_IDRRef] = @id;"
|
|
$null = $command.Parameters.Add("@id", [System.Data.SqlDbType]::Binary, 16)
|
|
$command.Parameters["@id"].Value = $IdBytes
|
|
$reader = $command.ExecuteReader()
|
|
try {
|
|
if (-not $reader.Read()) {
|
|
return [pscustomobject]@{ found = $false; reason = "not_found"; values = $null }
|
|
}
|
|
$values = [ordered]@{}
|
|
for ($i = 0; $i -lt $reader.FieldCount; $i++) {
|
|
$values[$reader.GetName($i)] = Convert-SqlValue $reader.GetValue($i)
|
|
}
|
|
return [pscustomobject]@{ found = $true; reason = $null; values = [pscustomobject]$values }
|
|
}
|
|
finally {
|
|
$reader.Close()
|
|
$command.Dispose()
|
|
}
|
|
}
|
|
|
|
function New-TableRouteIndex {
|
|
param([object]$RouteIndex)
|
|
$tables = @{}
|
|
foreach ($entry in $RouteIndex.objects.PSObject.Properties) {
|
|
$guid = [string]$entry.Name
|
|
$object = $entry.Value
|
|
foreach ($top in @($object.xml_top_objects)) {
|
|
if (-not (Test-HasProperty $top "xml_kind") -or -not (Test-HasProperty $top "name")) { continue }
|
|
foreach ($route in @($object.dbnames)) {
|
|
$table = $null
|
|
if ($route.storage_role -eq "Reference") { $table = "_Reference$($route.sql_number)" }
|
|
elseif ($route.storage_role -eq "Document") { $table = "_Document$($route.sql_number)" }
|
|
elseif ($route.storage_role -eq "Enum") { $table = "_Enum$($route.sql_number)" }
|
|
if (-not $table) { continue }
|
|
if (-not $tables.ContainsKey($table)) {
|
|
$tables[$table] = [pscustomobject]@{
|
|
guid = $guid
|
|
kind = $top.xml_kind
|
|
name = $top.name
|
|
synonym = if (Test-HasProperty $top "synonym") { $top.synonym } else { $null }
|
|
relative_path = if (Test-HasProperty $top "relative_path") { $top.relative_path } else { $null }
|
|
storage_role = $route.storage_role
|
|
sql_number = [int]$route.sql_number
|
|
table = $table
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return $tables
|
|
}
|
|
|
|
function Get-CellHex {
|
|
param([object]$Cell)
|
|
if (-not $Cell -or -not (Test-HasProperty $Cell "value") -or -not $Cell.value) { return $null }
|
|
if ((Test-HasProperty $Cell.value "hex") -and $Cell.value.hex) { return [string]$Cell.value.hex }
|
|
return $null
|
|
}
|
|
|
|
function Add-CompositeGroups {
|
|
param(
|
|
[System.Collections.Generic.List[object]]$Groups,
|
|
[object[]]$Rows,
|
|
[string]$Scope,
|
|
[string]$TablePartName,
|
|
[string]$TablePartTable
|
|
)
|
|
for ($rowIndex = 0; $rowIndex -lt $Rows.Count; $rowIndex++) {
|
|
$row = $Rows[$rowIndex]
|
|
$byPath = @{}
|
|
foreach ($property in $row.PSObject.Properties) {
|
|
$cell = $property.Value
|
|
if (-not (Test-HasProperty $cell "metadata_path") -or -not $cell.metadata_path) { continue }
|
|
if (-not (Test-HasProperty $cell "column") -or -not $cell.column) { continue }
|
|
if ($cell.column -notmatch '_(TYPE|RTRef|RRRef)$') { continue }
|
|
$path = [string]$cell.metadata_path
|
|
if (-not $byPath.ContainsKey($path)) { $byPath[$path] = @{} }
|
|
if ($cell.column -match '_TYPE$') { $byPath[$path]["TYPE"] = $cell }
|
|
elseif ($cell.column -match '_RTRef$') { $byPath[$path]["RTRef"] = $cell }
|
|
elseif ($cell.column -match '_RRRef$') { $byPath[$path]["RRRef"] = $cell }
|
|
}
|
|
foreach ($entry in $byPath.GetEnumerator()) {
|
|
$cells = $entry.Value
|
|
if (-not ($cells.ContainsKey("TYPE") -and $cells.ContainsKey("RTRef") -and $cells.ContainsKey("RRRef"))) { continue }
|
|
$typeCell = $cells["TYPE"]
|
|
if (-not (Test-HasProperty $typeCell "value_type") -or -not $typeCell.value_type) { continue }
|
|
$types = @($typeCell.value_type.types)
|
|
if ($types.Count -lt 2) { continue }
|
|
$rrrefHex = Get-CellHex $cells["RRRef"]
|
|
if (-not $rrrefHex -or $rrrefHex -eq "00000000000000000000000000000000") { continue }
|
|
$Groups.Add([pscustomobject]@{
|
|
scope = $Scope
|
|
table_part_name = $TablePartName
|
|
table_part_table = $TablePartTable
|
|
row_index = $rowIndex
|
|
metadata_path = $entry.Key
|
|
metadata_name = $typeCell.metadata_name
|
|
metadata_uuid = $typeCell.metadata_uuid
|
|
value_types = [object[]]$types
|
|
type_hex = Get-CellHex $cells["TYPE"]
|
|
rtref_hex = Get-CellHex $cells["RTRef"]
|
|
rtref_sql_number = Convert-HexToInt32 (Get-CellHex $cells["RTRef"])
|
|
rrref_hex = $rrrefHex
|
|
columns = [pscustomobject]@{
|
|
type = $cells["TYPE"].column
|
|
rtref = $cells["RTRef"].column
|
|
rrref = $cells["RRRef"].column
|
|
}
|
|
})
|
|
}
|
|
}
|
|
}
|
|
|
|
$readResult = Get-Content -LiteralPath $ReadResultPath -Raw -Encoding UTF8 | ConvertFrom-Json
|
|
$routeIndex = Get-Content -LiteralPath $RouteIndexPath -Raw -Encoding UTF8 | ConvertFrom-Json
|
|
$tableRoutes = New-TableRouteIndex $routeIndex
|
|
|
|
$groups = New-Object System.Collections.Generic.List[object]
|
|
Add-CompositeGroups -Groups $groups -Rows @($readResult.main.rows) -Scope "main" -TablePartName $null -TablePartTable $readResult.main.table
|
|
foreach ($part in @($readResult.table_parts)) {
|
|
Add-CompositeGroups -Groups $groups -Rows @($part.rows) -Scope "table_part" -TablePartName $part.name -TablePartTable $part.table
|
|
}
|
|
|
|
$connectionString = "Server=$Server;Database=$Database;User ID=$User;Password=$Password;Encrypt=False;TrustServerCertificate=True;Application Name=Codex 1C SQL Composite Reference Resolver;"
|
|
$connection = [System.Data.SqlClient.SqlConnection]::new($connectionString)
|
|
$columnCache = @{}
|
|
$resolved = New-Object System.Collections.Generic.List[object]
|
|
$resolvedCount = 0
|
|
$targetMisses = 0
|
|
$rowMisses = 0
|
|
|
|
try {
|
|
$connection.Open()
|
|
foreach ($group in [object[]]$groups.ToArray()) {
|
|
$table = "_Reference$($group.rtref_sql_number)"
|
|
$target = if ($tableRoutes.ContainsKey($table)) { $tableRoutes[$table] } else { $null }
|
|
if (-not $target) {
|
|
$targetMisses += 1
|
|
$resolved.Add([pscustomobject]@{ composite = $group; target = $null; found = $false; reason = "target_table_route_not_found"; values = $null })
|
|
continue
|
|
}
|
|
if (-not $columnCache.ContainsKey($table)) {
|
|
$columnCache[$table] = Get-Columns -Connection $connection -Table $table
|
|
}
|
|
$columns = Select-PresentationColumns -Columns $columnCache[$table]
|
|
$row = Resolve-Row -Connection $connection -Table $table -Columns $columns -IdBytes (Convert-HexToBytes $group.rrref_hex)
|
|
if ($row.found) { $resolvedCount += 1 } else { $rowMisses += 1 }
|
|
$resolved.Add([pscustomobject]@{
|
|
composite = $group
|
|
target = $target
|
|
found = $row.found
|
|
reason = $row.reason
|
|
values = $row.values
|
|
})
|
|
}
|
|
|
|
$result = [ordered]@{
|
|
schema = "onec_sql_composite_reference_resolution.v1"
|
|
read_result_path = (Resolve-Path -LiteralPath $ReadResultPath).ProviderPath
|
|
route_index_path = (Resolve-Path -LiteralPath $RouteIndexPath).ProviderPath
|
|
server = $Server
|
|
database = $Database
|
|
source = [pscustomobject]@{
|
|
kind = $readResult.kind
|
|
identity = $readResult.identity
|
|
}
|
|
summary = [pscustomobject]@{
|
|
composite_groups = $groups.Count
|
|
resolved = $resolvedCount
|
|
target_misses = $targetMisses
|
|
row_misses = $rowMisses
|
|
}
|
|
composites = [object[]]$resolved.ToArray()
|
|
}
|
|
|
|
$outputDirectory = Split-Path -Parent $OutputPath
|
|
if ($outputDirectory) { New-Item -ItemType Directory -Force -Path $outputDirectory | Out-Null }
|
|
$result | ConvertTo-Json -Depth 100 | Set-Content -LiteralPath $OutputPath -Encoding UTF8
|
|
|
|
[pscustomobject]@{
|
|
output = (Resolve-Path -LiteralPath $OutputPath).ProviderPath
|
|
composite_groups = $groups.Count
|
|
resolved = $resolvedCount
|
|
target_misses = $targetMisses
|
|
row_misses = $rowMisses
|
|
} | ConvertTo-Json -Depth 10
|
|
}
|
|
finally {
|
|
$connection.Close()
|
|
$connection.Dispose()
|
|
}
|