ConvertToSQLNoteBook.ps1
|
function loadScriptDomModules{ #try {Import-Module SqlServer -ErrorAction Stop} catch {Install-Module -Name SqlServer -Scope CurrentUser} finally {Import-Module SqlServer} Import-Module -Name SqlServer $ScriptDom = Join-Path -Path (Get-Module -Name SqlServer).ModuleBase -ChildPath 'Microsoft.SqlServer.TransactSql.ScriptDom.dll' if((Test-Path $ScriptDom) -eq $true ) {Add-Type -LiteralPath $ScriptDom} } # Quick Helper-function to turn the file into a script fragment, using scriptdom. function Get-ParsedSql($ScriptPath){ loadScriptDomModules [Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser] $parser = new-object Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser($false) $Reader = New-Object -TypeName System.IO.StreamReader -ArgumentList $ScriptPath $Errors = $null $ScriptFrag = $parser.Parse($Reader, [ref]$Errors) return $ScriptFrag } function Get-ParsedSqlOffsets{ [CmdletBinding()] param( $ScriptPath, $IncludeGaps=$true, [switch]$ExtractCommentsInsideBatches ) <################################################################################################# Checking for Batch length #################################################################################################> $s = Get-Content -Raw ( Resolve-Path $ScriptPath ) $ParsedSql = Get-ParsedSql $ScriptPath $SqlBatches = @() $SqlBatch = @() $id=1 foreach($Batch in $ParsedSql.Batches) { $SqlBatch=[pscustomobject][Ordered]@{ StartOffset = $Batch.StartOffset; StopOffset = $Batch.StartOffset+$Batch.FragmentLength; Length = $Batch.FragmentLength; StartColumn = $Batch.StartColumn; BatchId = $id; BlockType = 'Code'; Text = $s.Substring($Batch.StartOffset, $Batch.FragmentLength) } $SqlBatches+=$SqlBatch $id++ } $ScriptFrags = (Get-ParsedSql -ScriptPath $ScriptPath).ScriptTokenStream.where({$_.TokenType -eq 'MultilineComment'}) #If there are no comments to extract, we will skip the next section of code. if($ScriptFrags){ $Comments = @() $Comment = @() foreach($Frag in $ScriptFrags ) { $Comment=[pscustomobject][Ordered]@{ StartOffset = $Frag.Offset; StopOffset = $Frag.Offset+$Frag.Text.Length; Length = $Frag.Text.Length; StartColumn = $Frag.Column; CommentLocation = $null; BlockType = 'Comment'; Text = $Frag.Text } foreach($SqlBatch in $SqlBatches){ if($Comment.StartOffset -ge $SqlBatch.StartOffset -and $Comment.StartOffset -le $SqlBatch.StopOffset) {$Comment.CommentLocation = "Within SQL Batch $($SqlBatch.BatchId)"} else {if($Comment.CommentLocation -notlike '*Within*'){$Comment.CommentLocation = "Outside"}} } $Comments+=$Comment } } <################################################################################################# This is the basic product of Mulit-line Coments that are outside of Batches. Can you detect parameters in a test? #################################################################################################> if($ExtractCommentsInsideBatches){ $ExtractAllComments = $Comments } else { $ExtractAllComments = $Comments | Where-Object { $_.CommentLocation -eq 'Outside' } } $NotebookBlocks = $SqlBatches + $ExtractAllComments if($IncludeGaps -eq $false){ return $NotebookBlocks | Sort-Object StartOffset } else { if($NotebookBlocks.Count -eq 1 -and $NotebookBlocks.StopOffset -eq $s.Length){ return $NotebookBlocks | Sort-Object StartOffset} else { <################################################################################################# This What we do with the offset results to identify Gaps. #################################################################################################> $SqlBlocks = $NotebookBlocks | Sort-Object StartOffset $BlocksWitGaps = @() $Previous = @{StartOffset=0;StopOffset=0} foreach($SqlBlock in $SqlBlocks ) { $BlockOffsets=[ordered]@{ StartOffset = $SqlBlock.StartOffset; StopOffset = $SqlBlock.StopOffset; Length = $SqlBlock.Length; GapLength = [int] $SqlBlock.StartOffset-$Previous.StopOffset; PreviousStartOffset = $Previous.StartOffset; PreviousStopOffset = $Previous.StopOffset; CommentLocation = $SqlBlock.CommentLocation; BlockType = $SqlBlock.BlockType; GapText = IF($SqlBlock.StartOffset-$Previous.StopOffset -gt 1){[string] $s.Substring($Previous.StopOffset, ($SqlBlock.StartOffset-$Previous.StopOffset))}else {[string] ''}; Text = $SqlBlock.Text } $Previous=$BlockOffsets $BlocksWitGaps+=[pscustomobject] $BlockOffsets } <################################################################################################# This is an extra step to combine Gaps with Batches & Comments in a single structure. #################################################################################################> $AllBlocks = @() $GapOffsets = @() $Previous = @{StartOffset=0;StopOffset=0} if($BlocksWitGaps.Count -eq 1){$AllBlocks = @($SqlBlocks;[pscustomobject][Ordered]@{ StartOffset=0; StopOffset = $BlocksWitGaps.GapLength; Length = $BlocksWitGaps.GapLength; StartColumn = $null; BatchId=0; BlockType = 'Gap'; Text = $BlocksWitGaps.GapText})} else{ $AllBlocks = $SqlBlocks foreach($GapBlock in $BlocksWitGaps ) { $GapOffsets=[ordered]@{ StartOffset = $GapBlock.PreviousStopOffset; StopOffset = $GapBlock.StartOffset; Length = $GapBlock.GapLength; StartColumn = $null; CommentLocation = 'Between'; BlockType = 'Gap'; Text = $GapBlock.GapText } $Previous=$GapOffsets $AllBlocks+=if($GapOffsets.Length -gt 2){[pscustomobject] $GapOffsets} } } #$AllBlocks | Sort-Object StartOffset | ft -AutoSize -Wrap return $AllBlocks } } } function ConvertTo-SQLNoteBook { <# .Example ConvertTo-SQLNoteBook -InputFileName 'C:\temp\AdventureWorksMultiStatementSBatch.sql' -OutputNotebookName 'C:\temp\AdventureWorksMultiStatementSBatch.ipynb' #> param( $InputFileName, $OutputNotebookName ) New-SQLNotebook -NoteBookName $OutputNotebookName { $s = Get-Content -Raw ( Resolve-Path $InputFileName ) $AllNoteBlocks = Get-ParsedSqlOffsets -ScriptPath $InputFileName foreach($Block in $AllNoteBlocks | Sort-Object StartOffset ) { switch ($Block.BlockType) { 'Code' {$CodeBlock = $s.Substring($Block.StartOffset, $Block.Length) Write-Verbose "CODE - $($CodeBlock)" if($CodeBlock.Trim().length -gt 0){ Add-NotebookCode -code (-join $CodeBlock) } } 'Comment' {$TextBlock = $s.Substring($Block.StartOffset+2, $Block.Length-4) -replace ("\r\n", " `n ") Write-Verbose "COMMENT - $($TextBlock)" if($TextBlock.Trim().length -gt 0){ Add-NotebookMarkdown -markdown (-join $TextBlock) } } 'Gap' {$GapBlock = ($s.Substring($Block.StartOffset, $Block.Length) -replace ("\n", " `n ")).TrimStart().TrimEnd() Write-Verbose "COMMENT - $($GapBlock)" if($GapBlock.Trim().length -gt 0){ Add-NotebookMarkdown -markdown (-join $GapBlock) } } } } } } |