Modules/businessdev.ALbuild.Containers/Public/Get-BcContainerTableData.ps1
|
function Get-BcContainerTableData { <# .SYNOPSIS Reads rows of a Business Central table out of a running container. .DESCRIPTION Business Central offers no general way to read arbitrary table data from outside: OData needs an API page per table, and the management cmdlets administer the server rather than query it. What every single-container image does have is its own SQL Express instance, so this reads through SQL from INSIDE the container - nothing is exposed to the host. The SQL name of a BC table is '<Company>$<Table Name>$<app GUID>', and a field's column name is the AL field name with characters SQL cannot take replaced ('.' becomes '_'). Rather than guessing the GUID, the table is located by pattern, which also makes the failure mode useful: if two apps define a table of the same name, both are reported instead of one being picked silently. Intended for seeding a local probe with realistic data - not for bulk export, which is why -First is capped and defaulted low. Reading a production-sized table into memory to answer a question about one record helps nobody. .PARAMETER Name The container to read from. .PARAMETER Table The AL table name, e.g. 'Currency'. .PARAMETER Company Company whose data to read. Defaults to the first company in the database. .PARAMETER Filter A SQL WHERE clause without the keyword, e.g. "[Code] = 'EUR'". .PARAMETER First Maximum number of rows (default 50, maximum 1000). .PARAMETER DockerExecutable The Docker executable to use (default 'docker'). .OUTPUTS PSCustomObject with Table (the SQL table found), Company, RowCount and Rows (an array of ordered hashtables keyed by SQL column name). .EXAMPLE Get-BcContainerTableData -Name bld -Table 'Currency' -Filter "[Code] = 'EUR'" Reads the EUR currency record from the container's database. #> [CmdletBinding()] [OutputType([PSCustomObject])] param( [Parameter(Mandatory)] [string] $Name, [Parameter(Mandatory)] [string] $Table, [string] $Company, [string] $Filter, [ValidateRange(1, 1000)] [int] $First = 50, [string] $DockerExecutable = 'docker' ) Test-BcPlatform -Require | Out-Null # -Variables injects each entry as a variable of that name; the block takes no param() block. $script = { $ErrorActionPreference = 'Stop' $srv = '.\SQLEXPRESS' # The BC database is the only user database in a standard container image. $db = (Invoke-Sqlcmd -ServerInstance $srv -Query ` "SELECT TOP 1 name FROM sys.databases WHERE database_id > 4 ORDER BY database_id").name if (-not $db) { throw "No Business Central database found in the container." } # SQL cannot take a '.' or a '/' in an identifier, so BC substitutes '_' - in the TABLE part of # the name as well as the company part. 'No. Series' is stored as 'No_ Series' and 'G/L Account' # as 'G_L Account'. Searching for the literal AL name finds neither, which made every table # whose name carries punctuation unreadable - No. Series, Gen. Journal Template, G/L Account. # '_' is LIKE's own single-character wildcard, so the pattern matches whatever BC stored there. $escaped = ($tableName.Replace("'", "''") -replace '[./\\]', '_') $like = "%`$$escaped`$%" $candidates = @(Invoke-Sqlcmd -ServerInstance $srv -Database $db -Query ` "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '$like'" | ForEach-Object { $_.TABLE_NAME }) if ($company) { # SQL cannot take a '.' in an identifier, so BC substitutes '_' - in the COMPANY part of the # name too. 'CRONUS International Ltd.' is stored as 'CRONUS International Ltd_'. Accepting # only the literal name would reject the very name the user reads in the client. $prefix = ($company -replace '\.', '_') + '$' $candidates = @($candidates | Where-Object { $_ -like "$prefix*" }) } if ($candidates.Count -eq 0) { throw "No SQL table matching '$tableName' in database '$db'. Company-specific tables are named '<Company>`$<Table>`$<app guid>'." } # A table someone EXTENDED is stored in two places: the base table, and one '$ext' table per # extension holding the added fields with the same primary key. Those are the SAME AL table, and # treating them as rival candidates made every extended table unreadable - including General # Ledger Setup, which is extended in almost every installation. # A SIFT index is stored as its own table, '<Table>$VSIFT$Key<n>'. It holds the same rows # pre-aggregated, and it is not the table anyone asked for - counting it as a rival candidate # made every table with a SIFT key read as ambiguous. $candidates = @($candidates | Where-Object { $_ -notlike '*$VSIFT$*' }) $extensions = @($candidates | Where-Object { $_ -like '*$ext' }) $bases = @($candidates | Where-Object { $_ -notlike '*$ext' }) if ($bases.Count -eq 0) { $bases = $candidates; $extensions = @() } if ($bases.Count -gt 1) { # Now it is genuinely ambiguous: several companies, or two apps with the same table name. # Naming them beats picking one - and the remedy only mentions -Company when it would help. $hint = if ($company) { ' Give the full table name, or read the companies separately.' } else { ' Narrow it with -Company.' } throw ("'$tableName' is ambiguous - " + $bases.Count + " matching SQL table(s): " + (($bases | Select-Object -First 5) -join '; ') + '.' + $hint) } $sqlTable = $bases[0] $where = if ($filter) { " WHERE $filter" } else { '' } $rows = @(Invoke-Sqlcmd -ServerInstance $srv -Database $db -Query ` "SELECT TOP $first * FROM [$sqlTable]$where") # The extension fields belong to the same row, so they are merged onto it by the primary key # the two tables share. A pack missing them would be quietly incomplete: a test reading an # extension field would see an empty value and blame the code. if ($extensions.Count -gt 0 -and $rows.Count -gt 0) { $keyColumns = @(Invoke-Sqlcmd -ServerInstance $srv -Database $db -Query " SELECT k.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON k.CONSTRAINT_NAME = c.CONSTRAINT_NAME WHERE c.TABLE_NAME = '$($sqlTable.Replace("'","''"))' AND c.CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY k.ORDINAL_POSITION" | ForEach-Object { $_.COLUMN_NAME }) foreach ($ext in $extensions) { $extRows = @(Invoke-Sqlcmd -ServerInstance $srv -Database $db -Query ` "SELECT TOP $first * FROM [$ext]") foreach ($row in $rows) { $match = $extRows | Where-Object { $same = $true foreach ($key in $keyColumns) { if ($_[$key] -ne $row[$key]) { $same = $false; break } } $same } | Select-Object -First 1 if (-not $match) { continue } foreach ($column in $match.Table.Columns) { if ($keyColumns -contains $column.ColumnName) { continue } if ($row.Table.Columns.Contains($column.ColumnName)) { continue } $row.Table.Columns.Add($column.ColumnName, $column.DataType) | Out-Null $row[$column.ColumnName] = $match[$column.ColumnName] } } } } $out = @() foreach ($r in $rows) { $o = [ordered]@{} foreach ($c in $r.Table.Columns) { $v = $r[$c.ColumnName] if ($v -is [System.DBNull]) { $v = $null } $o[$c.ColumnName] = $v } $out += , $o } # Invoke-BcContainerCommand returns the container's STDOUT as text, so the result has to cross # the boundary as JSON. Emitting objects here would arrive as PowerShell's formatted table. [PSCustomObject]@{ Table = $sqlTable Database = $db RowCount = $out.Count Rows = $out } | ConvertTo-Json -Depth 6 -Compress } Write-ALbuildLog "Reading '$Table' from container '$Name' (max $First row(s))..." $stdout = Invoke-BcContainerCommand -ContainerName $Name -ScriptBlock $script ` -Variables @{ tableName = $Table; company = $Company; filter = $Filter; first = $First } ` -DockerExecutable $DockerExecutable $json = ($stdout | Out-String).Trim() if (-not $json) { throw "Reading '$Table' from '$Name' produced no output." } try { $result = $json | ConvertFrom-Json } catch { throw "Could not read the container's answer as JSON: $($_.Exception.Message)`n$json" } Write-ALbuildLog -Level Success "Read $($result.RowCount) row(s) from '$($result.Table)'." return $result } |