437 lines
15 KiB
PowerShell
437 lines
15 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]$ProjectionPath,
|
|
[string]$OutputPath,
|
|
[switch]$SkipTableParts
|
|
)
|
|
|
|
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 $ProjectionPath) { throw "ProjectionPath 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-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")
|
|
}
|
|
if ($Value -is [Guid]) {
|
|
return $Value.ToString()
|
|
}
|
|
return $Value
|
|
}
|
|
|
|
function Quote-SqlIdentifier {
|
|
param([string]$Name)
|
|
|
|
return "[" + ($Name -replace "]", "]]") + "]"
|
|
}
|
|
|
|
function New-SelectSql {
|
|
param(
|
|
[string]$Table,
|
|
[object[]]$Columns,
|
|
[int]$Top
|
|
)
|
|
|
|
if (-not $Table) {
|
|
return $null
|
|
}
|
|
|
|
$parts = New-Object System.Collections.Generic.List[string]
|
|
foreach ($column in @($Columns)) {
|
|
if (-not (Test-HasProperty $column "column")) { continue }
|
|
if (-not $column.column) { continue }
|
|
$alias = if ((Test-HasProperty $column "select_alias") -and $column.select_alias) { [string]$column.select_alias } else { [string]$column.column }
|
|
$parts.Add(" $(Quote-SqlIdentifier ([string]$column.column)) AS $(Quote-SqlIdentifier $alias)")
|
|
}
|
|
|
|
$selectList = if ($parts.Count -gt 0) { [string]::Join(",`n", $parts.ToArray()) } else { " *" }
|
|
return "SELECT TOP ($Top)`n$selectList`nFROM $(Quote-SqlIdentifier $Table);"
|
|
}
|
|
|
|
function Get-ExistingColumns {
|
|
param(
|
|
[System.Data.SqlClient.SqlConnection]$Connection,
|
|
[string]$Table
|
|
)
|
|
|
|
$set = [System.Collections.Generic.HashSet[string]]::new([System.StringComparer]::OrdinalIgnoreCase)
|
|
if (-not $Table) {
|
|
return $set
|
|
}
|
|
|
|
$command = $Connection.CreateCommand()
|
|
$command.CommandText = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName;"
|
|
$null = $command.Parameters.Add("@tableName", [System.Data.SqlDbType]::NVarChar, 256)
|
|
$command.Parameters["@tableName"].Value = $Table
|
|
$reader = $command.ExecuteReader()
|
|
try {
|
|
while ($reader.Read()) {
|
|
$null = $set.Add([string]$reader.GetValue(0))
|
|
}
|
|
}
|
|
finally {
|
|
$reader.Close()
|
|
$command.Dispose()
|
|
}
|
|
return $set
|
|
}
|
|
|
|
function Get-CandidateTables {
|
|
param(
|
|
[System.Data.SqlClient.SqlConnection]$Connection,
|
|
[string]$BaseTable
|
|
)
|
|
|
|
$tables = New-Object System.Collections.Generic.List[string]
|
|
if (-not $BaseTable) {
|
|
return [string[]]$tables.ToArray()
|
|
}
|
|
|
|
$command = $Connection.CreateCommand()
|
|
$command.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';"
|
|
$reader = $command.ExecuteReader()
|
|
try {
|
|
$pattern = "^" + [regex]::Escape($BaseTable) + "(X\d+)?$"
|
|
while ($reader.Read()) {
|
|
$table = [string]$reader.GetValue(0)
|
|
if ($table -match $pattern) {
|
|
$tables.Add($table)
|
|
}
|
|
}
|
|
}
|
|
finally {
|
|
$reader.Close()
|
|
$command.Dispose()
|
|
}
|
|
|
|
if (-not $tables.Contains($BaseTable)) {
|
|
$tables.Add($BaseTable)
|
|
}
|
|
return [string[]]($tables.ToArray() | Sort-Object {
|
|
if ($_ -eq $BaseTable) { 0 } elseif ($_ -match "X(\d+)$") { [int]$Matches[1] } else { 9999 }
|
|
})
|
|
}
|
|
|
|
function Get-TableRowCount {
|
|
param(
|
|
[System.Data.SqlClient.SqlConnection]$Connection,
|
|
[string]$Table
|
|
)
|
|
|
|
$command = $Connection.CreateCommand()
|
|
$command.CommandText = "SELECT COUNT(*) FROM $(Quote-SqlIdentifier $Table);"
|
|
try {
|
|
return [int]$command.ExecuteScalar()
|
|
}
|
|
finally {
|
|
$command.Dispose()
|
|
}
|
|
}
|
|
|
|
function Select-EffectiveTable {
|
|
param(
|
|
[System.Data.SqlClient.SqlConnection]$Connection,
|
|
[string]$BaseTable,
|
|
[object[]]$Columns,
|
|
[string]$TableView = "effective"
|
|
)
|
|
|
|
if ($TableView -eq "base") {
|
|
return [pscustomobject]@{
|
|
base_table = $BaseTable
|
|
selected_table = $BaseTable
|
|
matched_columns = 0
|
|
requested_columns = @($Columns).Count
|
|
row_count = Get-TableRowCount -Connection $Connection -Table $BaseTable
|
|
is_companion = $false
|
|
selection_mode = "base"
|
|
}
|
|
}
|
|
|
|
$best = $null
|
|
foreach ($table in @(Get-CandidateTables -Connection $Connection -BaseTable $BaseTable)) {
|
|
$existing = Get-ExistingColumns -Connection $Connection -Table $table
|
|
$matched = 0
|
|
foreach ($column in @($Columns)) {
|
|
if ((Test-HasProperty $column "column") -and $column.column -and $existing.Contains([string]$column.column)) {
|
|
$matched += 1
|
|
}
|
|
}
|
|
$rowCount = Get-TableRowCount -Connection $Connection -Table $table
|
|
$candidate = [pscustomobject]@{
|
|
base_table = $BaseTable
|
|
selected_table = $table
|
|
matched_columns = $matched
|
|
requested_columns = @($Columns).Count
|
|
row_count = $rowCount
|
|
is_companion = $table -ne $BaseTable
|
|
selection_mode = $TableView
|
|
}
|
|
if (
|
|
$null -eq $best -or
|
|
$candidate.matched_columns -gt $best.matched_columns -or
|
|
($candidate.matched_columns -eq $best.matched_columns -and $candidate.row_count -gt $best.row_count)
|
|
) {
|
|
$best = $candidate
|
|
}
|
|
}
|
|
return $best
|
|
}
|
|
|
|
function Resolve-ProjectionColumns {
|
|
param(
|
|
[System.Data.SqlClient.SqlConnection]$Connection,
|
|
[string]$Table,
|
|
[object[]]$Columns,
|
|
[string]$Scope,
|
|
[System.Collections.Generic.List[object]]$Diagnostics
|
|
)
|
|
|
|
$existing = Get-ExistingColumns -Connection $Connection -Table $Table
|
|
$result = New-Object System.Collections.Generic.List[object]
|
|
foreach ($column in @($Columns)) {
|
|
if (-not (Test-HasProperty $column "column") -or -not $column.column) {
|
|
$result.Add($column)
|
|
continue
|
|
}
|
|
if ($existing.Contains([string]$column.column)) {
|
|
$result.Add($column)
|
|
continue
|
|
}
|
|
$Diagnostics.Add([pscustomobject]@{
|
|
scope = $Scope
|
|
table = $Table
|
|
column = [string]$column.column
|
|
metadata_path = if (Test-HasProperty $column "metadata_path") { $column.metadata_path } else { $null }
|
|
})
|
|
}
|
|
return [object[]]$result.ToArray()
|
|
}
|
|
|
|
function New-ColumnMap {
|
|
param([object[]]$Columns)
|
|
|
|
$map = @{}
|
|
foreach ($column in @($Columns)) {
|
|
if (-not (Test-HasProperty $column "select_alias")) { continue }
|
|
if (-not $column.select_alias) { continue }
|
|
$map[[string]$column.select_alias] = $column
|
|
}
|
|
return $map
|
|
}
|
|
|
|
function New-Cell {
|
|
param(
|
|
[object]$ColumnInfo,
|
|
[object]$Value
|
|
)
|
|
|
|
$cell = [ordered]@{
|
|
alias = if (Test-HasProperty $ColumnInfo "select_alias") { $ColumnInfo.select_alias } else { $null }
|
|
column = if (Test-HasProperty $ColumnInfo "column") { $ColumnInfo.column } else { $null }
|
|
metadata_path = if (Test-HasProperty $ColumnInfo "metadata_path") { $ColumnInfo.metadata_path } else { $null }
|
|
metadata_name = if (Test-HasProperty $ColumnInfo "metadata_name") { $ColumnInfo.metadata_name } else { $null }
|
|
metadata_uuid = if (Test-HasProperty $ColumnInfo "metadata_uuid") { $ColumnInfo.metadata_uuid } else { $null }
|
|
metadata_field = if (Test-HasProperty $ColumnInfo "metadata_field") { $ColumnInfo.metadata_field } else { $null }
|
|
value_type = if (Test-HasProperty $ColumnInfo "value_type") { $ColumnInfo.value_type } else { $null }
|
|
sql_type = if (Test-HasProperty $ColumnInfo "sql_type") { $ColumnInfo.sql_type } else { $null }
|
|
value = Convert-SqlValue $Value
|
|
}
|
|
return [pscustomobject]$cell
|
|
}
|
|
|
|
function Invoke-ProjectionQuery {
|
|
param(
|
|
[System.Data.SqlClient.SqlConnection]$Connection,
|
|
[string]$Sql,
|
|
[object[]]$Columns
|
|
)
|
|
|
|
if (-not $Sql) {
|
|
return [pscustomobject]@{
|
|
row_count = 0
|
|
columns = @($Columns)
|
|
rows = @()
|
|
}
|
|
}
|
|
|
|
$columnMap = New-ColumnMap $Columns
|
|
$rows = New-Object System.Collections.Generic.List[object]
|
|
$command = $Connection.CreateCommand()
|
|
$command.CommandTimeout = 0
|
|
$command.CommandText = $Sql
|
|
|
|
$reader = $command.ExecuteReader()
|
|
try {
|
|
while ($reader.Read()) {
|
|
$row = [ordered]@{}
|
|
for ($i = 0; $i -lt $reader.FieldCount; $i++) {
|
|
$alias = $reader.GetName($i)
|
|
if ($columnMap.ContainsKey($alias)) {
|
|
$columnInfo = $columnMap[$alias]
|
|
} else {
|
|
$columnInfo = [pscustomobject]@{
|
|
select_alias = $alias
|
|
column = $alias
|
|
metadata_path = $alias
|
|
metadata_name = $alias
|
|
}
|
|
}
|
|
|
|
$metadataPath = if (Test-HasProperty $columnInfo "metadata_path") { [string]$columnInfo.metadata_path } else { $alias }
|
|
$physicalColumn = if (Test-HasProperty $columnInfo "column") { [string]$columnInfo.column } else { $alias }
|
|
$key = "$metadataPath::$physicalColumn"
|
|
$row[$key] = New-Cell $columnInfo $reader.GetValue($i)
|
|
}
|
|
$rows.Add([pscustomobject]$row)
|
|
}
|
|
}
|
|
finally {
|
|
$reader.Close()
|
|
$command.Dispose()
|
|
}
|
|
|
|
$resultRows = $rows.ToArray()
|
|
return [pscustomobject]@{
|
|
row_count = [int]$resultRows.Count
|
|
columns = [object[]]@($Columns)
|
|
rows = [object[]]$resultRows
|
|
}
|
|
}
|
|
|
|
$projection = Get-Content -LiteralPath $ProjectionPath -Raw -Encoding UTF8 | ConvertFrom-Json
|
|
$resolvedProjectionPath = (Resolve-Path -LiteralPath $ProjectionPath).ProviderPath
|
|
$connectionString = "Server=$Server;Database=$Database;User ID=$User;Password=$Password;Encrypt=False;TrustServerCertificate=True;Application Name=Codex 1C SQL Read Projection Executor;"
|
|
$connection = [System.Data.SqlClient.SqlConnection]::new($connectionString)
|
|
|
|
try {
|
|
$connection.Open()
|
|
$top = if (Test-HasProperty $projection "top") { [int]$projection.top } else { 10 }
|
|
$tableView = if ((Test-HasProperty $projection "view") -and $projection.view -eq "base") { "base" } else { "effective" }
|
|
$prunedColumns = [System.Collections.Generic.List[object]]::new()
|
|
$effectiveTables = [System.Collections.Generic.List[object]]::new()
|
|
$mainChoice = Select-EffectiveTable `
|
|
-Connection $connection `
|
|
-BaseTable $projection.main_table `
|
|
-Columns @($projection.main_columns) `
|
|
-TableView $tableView
|
|
$effectiveTables.Add($mainChoice)
|
|
$mainColumns = Resolve-ProjectionColumns `
|
|
-Connection $connection `
|
|
-Table $mainChoice.selected_table `
|
|
-Columns @($projection.main_columns) `
|
|
-Scope "main" `
|
|
-Diagnostics $prunedColumns
|
|
$mainSql = New-SelectSql -Table $mainChoice.selected_table -Columns @($mainColumns) -Top $top
|
|
|
|
$main = Invoke-ProjectionQuery `
|
|
-Connection $connection `
|
|
-Sql $mainSql `
|
|
-Columns @($mainColumns)
|
|
|
|
$tableParts = New-Object System.Collections.Generic.List[object]
|
|
if (-not $SkipTableParts) {
|
|
foreach ($part in @($projection.table_parts)) {
|
|
$partScope = "table_part:$($part.name)"
|
|
$partChoice = Select-EffectiveTable `
|
|
-Connection $connection `
|
|
-BaseTable $part.table `
|
|
-Columns @($part.columns) `
|
|
-TableView $tableView
|
|
$effectiveTables.Add($partChoice)
|
|
$partColumns = Resolve-ProjectionColumns `
|
|
-Connection $connection `
|
|
-Table $partChoice.selected_table `
|
|
-Columns @($part.columns) `
|
|
-Scope $partScope `
|
|
-Diagnostics $prunedColumns
|
|
$partSql = New-SelectSql -Table $partChoice.selected_table -Columns @($partColumns) -Top $top
|
|
$queryResult = Invoke-ProjectionQuery `
|
|
-Connection $connection `
|
|
-Sql $partSql `
|
|
-Columns @($partColumns)
|
|
$tableParts.Add([pscustomobject]@{
|
|
name = $part.name
|
|
uuid = $part.uuid
|
|
table = $partChoice.selected_table
|
|
base_table = $part.table
|
|
row_count = $queryResult.row_count
|
|
columns = $queryResult.columns
|
|
rows = $queryResult.rows
|
|
})
|
|
}
|
|
}
|
|
|
|
$tablePartArray = [object[]]$tableParts.ToArray()
|
|
$result = [ordered]@{
|
|
schema = "onec_sql_read_result.v1"
|
|
projection_schema = $projection.schema
|
|
projection_path = $resolvedProjectionPath
|
|
server = $Server
|
|
database = $Database
|
|
kind = $projection.kind
|
|
identity = $projection.identity
|
|
view = if (Test-HasProperty $projection "view") { $projection.view } else { $null }
|
|
extension = if (Test-HasProperty $projection "extension") { $projection.extension } else { $null }
|
|
main = [pscustomobject]@{
|
|
table = $mainChoice.selected_table
|
|
base_table = $projection.main_table
|
|
row_count = $main.row_count
|
|
columns = $main.columns
|
|
rows = $main.rows
|
|
}
|
|
table_parts = $tablePartArray
|
|
diagnostics = [pscustomobject]@{
|
|
projection = if (Test-HasProperty $projection "diagnostics") { $projection.diagnostics } else { $null }
|
|
pruned_missing_columns = [object[]]$prunedColumns.ToArray()
|
|
effective_tables = [object[]]$effectiveTables.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
|
|
main_rows = $main.row_count
|
|
table_parts = $tablePartArray.Count
|
|
table_part_rows = [object[]]@($tablePartArray | ForEach-Object { [pscustomobject]@{ name = $_.name; table = $_.table; rows = $_.row_count } })
|
|
} | ConvertTo-Json -Depth 10
|
|
}
|
|
finally {
|
|
$connection.Close()
|
|
$connection.Dispose()
|
|
}
|