SQL YAML Documentation Parser

sqlYamlParser.ps1

param (
    [string]$output = "wiki", # can be html or wiki
    [string]$DatabaseServer = "nt7565",
    [string]$Database = "MARKITEDM_DEV_DX"
    #[Parameter(Mandatory=$true)][string]$username,
    #[string]$password = $( Read-Host "Input password, please" )
 )

Clear-Host
(new-object Net.WebClient).DownloadString("http://psget.net/GetPsGet.ps1") | iex
Import-Module PsGet
Install-Module PowerYaml
Import-Module PowerYaml.psm1 # import the YAML parser if necessary (get it via PSGET)
set-psdebug -strict # catch a few extra bugs

 
$SQL =@" 
SELECT
  ROUTINE_TYPE AS [type],
  ROUTINE_SCHEMA AS [schema],
  ROUTINE_NAME AS [name],
  CREATED AS [created],
  LAST_ALTERED AS [modified],
  DATA_TYPE AS returnType,
  ROUTINE_DEFINITION AS definition
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA IN('ajr', 'dbo', 'cadis')
  AND (
    (
      ROUTINE_TYPE = 'FUNCTION'
      AND ROUTINE_NAME LIKE 'fn%'
    ) OR (
      ROUTINE_TYPE = 'PROCEDURE'
      AND ROUTINE_NAME LIKE 'sp%'
    )
  )
ORDER BY ROUTINE_TYPE, ROUTINE_SCHEMA, ROUTINE_NAME
"@
$scriptPath = Split-Path -parent $PSCommandPath
"The directory $($scriptPath) will contain the output"


function Parse-YamlDocs() {
    $functions = @() #initialise the array of hashtables
    $sprocs = @() # array of hashtables storing stored procedure information
    # create the SqlClient connection
    $conn = new-Object System.Data.SqlClient.SqlConnection("Server=$DatabaseServer;DataBase=$Database;Integrated Security=True")#
    $conn.Open() | out-null #open the connection

    # We add a handler for the warnings just in case we get one
    $message = [string]'';
    $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { param ($sender,
                  $event)    $global:message = "$($message)`n $($event.Message)" };
    $conn.add_InfoMessage($handler);
    $conn.FireInfoMessageEventOnUserErrors = $true

    $cmd = new-Object System.Data.SqlClient.SqlCommand($SQL, $conn)
    $rdr = $cmd.ExecuteReader()
    $datatable = new-object System.Data.DataTable
    $datatable.Load($rdr)
    if ($message -ne '') { Write-Warning $message } # tell the user of any warnings or info messages

    # add manual comments for SQL CLR functions, which lose their comments when deployed to SQL Server
    $row = $datatable.NewRow()
    $row["type"] = "FUNCTION"
    $row["schema"] = "dbo"
    $row["name"] = "fnClassResolve_v3"
    $row["created"] = "2017-04-01"
    $row["modified"] = "2017-04-01"
    $row["returnType"] = "TABLE"
    $row["definition"] = "      
    /**
summary: >
  This function calls the classification rules engine and returns outputs from the rule evaluations
  This is version 3 of the classification engine function fnClassResolve
  To convert your code from version 1, make the following change
  
  old code - select * from fnClassResolve(@RefClassValues, @SchemeId)
  new code - select * from fnClassResolve_v2(@SchemeId, (select * from @RefClassValues Input for xml auto, root('Inputs')), DEFAULT) 
  
  Multiple rules may match a given input. If so, all matches will be returned. Make your rules more specific, or use the Priority column to determine which is the best match
  Multiple outcomes may match a given input. If so, all matches will be returned. Make your rules more specific
  If the classification is a hierarchy, matches for each level of the hierarchy will be returned as individual rows. Use the ParentClassId if you need to create the hierarchy
parameters:
  - name: SchemeId
    type: int
    description: an identifier from RefClassScheme.SchemeId, representing a rule scheme
    ifNull: no results will be returned
  - name: InputXml
    type: xml
    description: an XML fragment containing inputs. Follows the table structure of the user type dbo.RefClassValues. You can use that type as your definition, or you can manually construct the XML. e.g. <Inputs><Input SourceUniqueId='11' TermTitle='Country of Domicile' TermValue='US' /></Inputs>
    ifNull: no results will be returned
  - name: ConfigXml
    type: xml
    description: > 
      an XML fragment containing configuration information. The intent is to pass flags that will cause the function to behave in desired ways. Format is <config><Param1>Value1</Param1><Param2>Value2</Param2></config>. Supported parameters are:
      * MostSpecificLevelOnly: boolean. True will only return the deepest match instead of returning each level in the hierarchy. Default value is false (for backwards compatibility; the more common use case is true)
    ifNULL: the default behaviors will take effect
author: rod3095
examples:
  - select * from fnClassResolve_v3(@SchemeId, (select * from @RefClassValues Input for xml auto, root('Inputs')), DEFAULT)
  - select * from FnClassResolve_v3(@SchemeId, (select * from @RefClassValues Input for xml auto, root('Inputs')), '<Config><MostSpecificMatchLevelOnly>true</MostSpecificMatchLevelOnly></Config>')
  - select * from fnClassResolve_v3(@SchemeId, '<Inputs><Input SourceUnique='11' TermTitle='A' TermValue='123' /><Input SourceUniqueId='11' TermTitle='B' TermValue='999' /><Input SourceUniqueId='22' TermTitle='A' TermValue='243' /><Input SourceUniqueId='22' TermTitle='B' TermValue='999' /></Inputs>', DEFAULT)
returns: > 
  SourceUniqueId bigint - the unique identifer from your input data
  OutcomeValue nvarchar(1000) - this is the answer you are looking for. This is the result of the rule evaluations, the classification that matched
  RuleId int - this is the identifier of the rule that matched, resulting in this outcome
  RuleTitle nvarchar(1000)- this is the title of the rule that matched, resulting in this outcome
  ClassId int - this is the identifier of the classification that matched
  SchemeId int - this is the identifier of the scheme. Will be the same as the @SchemeId input parameter
  IsMatch bit - this is always true for every outcome row. Included for testing and backwards compatibility
  IsDynamicScheme bit - true if the scheme is dynamic, false if static
  ParentClassId int - if the outcome classification has a parent in a hierarchy, this will contain the identifier of the parent classification. 0 or NULL otherwise
  Priority int - Priorities are intended to help decide among multiple outcomes for a single input. Use this as a last resort--prefer instead to make more specific rules
**/"

    $datatable.rows.Add($row)

    foreach ($row in $datatable.Rows) # we read the routines row by row
    {
        if ("$($row['definition'])" -cmatch '(?ism)(\/\*\*).*?(summary.*?)(\*\*\/)')
        {
        #Write-Host $matches[2]
            $fn = @{}
            #parse the YAML into a hashtable
            try { 
                $fn = Get-Yaml $($matches[2])
            }
            catch {
                $fn.warning = "could not parse header for $($row['name']): " + $_ # the error message
                $fn.warningHeaderDocs = $($matches[2])
                write-warning $fn.warning 
            }
            #add the rest of the objects
            $fn.name = $row.name
            $fn.schema = $row.schema
            $fn.created = $row.created
            $fn.modified = $row.modified
            $fn.returnType = $row.returnType
            $fn.type = $row.type

            $functions += $fn; #and add-in each routine to the array.
            #Exit 0
        }
    }
    return $functions
}
function Generate-Html-Function-Param-Table($f) {
    # parameter list
    $pHtml = @"
            <table>
                <tr>
                    <th>Name</th>
                    <th>Type</th>
                    <th>Description</th>
                    <th>If NULL</th>
                </tr>

"@

    foreach ($p in $f.parameters) {
            $pHtml += @"
                <tr>
                    <td>$($p.name)</td>
                    <td>$($p.type)</td>
                    <td>$($p.description)</td>
                    <td>$($p.ifNull)</td>
                </tr>
"@
    }
    $pHtml += "</table>"
    return $pHtml;
}
function Generate-Html-Function-Examples($f) {
    # example list
    $eHtml = "<ol class=""examples"">"
    foreach ($e in $f.examples) {
        $eHtml += "<li>$($e)</li>"
    }
    $eHtml += "</ol>"
    return $eHtml;
}
function Generate-Html-Function-PBIs($f) {
    $h = "<ul>"
    foreach ($pbi in $f.relatedPbis) {
        $h += "<li><b><a href=""http://tfsprod.nml.com:8080/tfs/NMCollection/MEDM/_workitems?_a=edit&id=$($pbi.number)"" target=""_blank"">$($pbi.number)</a></b> - $($pbi.desc)</li>"
    }
    $h += "</ul>"
    return $h
}
function Generate-Html-Function($f) {
    $h = "<a name=""$($f.schema).$($f.name)""></a>"
    $h += "<h3>$($f.schema).$($f.name)</h3>"
    $h += "<p>Created by $($f.author) on $($f.created)</p>"
    $h += "<h4>Summary</h4>"
    $h += "<p>$($f.summary)</p>"
    $h += "<h4>Parameters</h4>"
    if (0 -lt $f.parameters.Length) { $h += Generate-Html-Function-Param-Table($f) } else { $h += "none" }
    $h += "<h4>Returns $($f.returnType)</h4>"
    $h += "<p>$($f.returns)</p>"
    if (0 -lt $f.examples.Length) {
        $h += "<h4>Examples</h4>"
        $h += Generate-Html-Function-Examples($f)
    }
    $h += "<h4>Related PBIs</h4>"
    if (0 -lt $f.relatedPbis.Length) { $h += Generate-Html-Function-PBIs($f) } else { $h += "none" }
    $h += "<hr />"
    return $h;
}
function Generate-Html-Function-Menu($functions) {
    $h = "<ul>"
    foreach ($f in $functions) {
        $h += "<li><a href=""#$($f.schema).$($f.name)"">$($f.schema).$($f.name)</a></li>"
    }
    $h += "</ul>"

    return $h;
}
function Generate-Html($functions) {
    $html = Get-Content $scriptPath\documentationTemplate.html

    ######## HTML generation
    $html = $html.Replace("{{database}}", $DatabaseServer + "\" + $Database)

    $dateGenerated = Get-Date
    $html = $html.Replace("{{dateGenerated}}", $dateGenerated)


    #### do the functions' HTML
    $h = ""
    foreach ($f in $functions | Where { $_.type -eq 'FUNCTION' }) {
        $h += Generate-Html-Function($f);
    }
    $html = $html.Replace("{{functions}}", $h)

    # functions index/menu HTML block
    $h = Generate-Html-Function-Menu($functions | Where { $_.type -eq 'FUNCTION' })
    $html = $html.Replace("{{functionsIndex}}", $h)


    #### do the stored procedures' HTML
    $h = ""
    foreach ($f in $functions | Where { $_.type -eq 'PROCEDURE' }) {
        $h += Generate-Html-Function($f);
    }
    $html = $html.Replace("{{sprocs}}", $h);

    #sproc index/menu HTML block
    $h = Generate-Html-Function-Menu($functions | Where { $_.type -eq 'PROCEDURE' })
    $html = $html.Replace("{{sprocsIndex}}", $h)

    $html > $scriptPath\documentation.html
}


function Generate-Wiki($functions) {
    $functionHeaderTemplate = @"
== {0}.{1} == 

"@

    $functionTemplate = @"
== {0}.{1} == 

Created by {2} on {3} 

'''Summary'''

{4} 

'''Parameters'''

{{| class="wikitable"
! Name
! Type
! Description
! If NULL
{5}
|}}

'''Returns ''{6}'''''

{7}

'''Examples'''

{8}

'''Related PBIs'''

{9}

"@
    $wiki = @"
Generated on $(Get-Date) from '''$($DatabaseServer)\$($Database)'''

''Do not edit this wiki page directly.'' Instead, modify the header docs of your SQL function. For examples, see [[Sql_Object_Documentation#dbo.fnMedmDbVarValue|dbo.fnMedmDbVarValue]], [[Sql_Object_Documentation#ajr.fnIsPositive|ajr.fnIsPositive]], or [[Sql_Object_Documentation#dbo.fnUtilChars|dbo.fnUtilChars]]. Because we generate this documentation from the dev environment, ''you do not have to deploy a function just to update its inline documentation''. Just update it in the dev environment, and you are done.

[[File:SQL function inline documentation.png|200px]]

[[How to Generate Sql Object Documentation]]

"@

    $h = ""
    foreach ($f in $functions) {
        if ($f.warning) {
            $wiki += ($functionHeaderTemplate -f $f.schema, $f.name) + "$($f.warning)`r`n`r`n<nowiki>$($f.warningHeaderDocs)</nowiki>`r`n"
            continue
        }

        # parameter list
        $parmsWiki = ""
        foreach ($p in $f.parameters) {
            $parmsWiki += @"
|-
|@$($p.name)
|$($p.type)
|$($p.description)
|$($p.ifNull)

"@
        }

        # example list
        $egWiki = ""
        foreach ($e in $f.examples) {
            $egWiki += "# $($e)`r`n"
        }

        #related PBI list
        $pbiWiki = ""
        foreach ($pbi in $f.relatedPbis) {
            $pbiWiki += "* '''[http://tfsprod.nml.com:8080/tfs/NMCollection/MEDM/_workitems?_a=edit&id=$($pbi.number) $($pbi.number)]''' $($pbi.desc)`r`n"
        }
        if (0 -eq $pbiWiki.Length) { $pbiWiki = "none" }

        $functionWiki = $functionTemplate -f $f.schema, $f.name, $f.author, $f.created, $f.summary, $parmsWiki, $f.returnType, $f.returns, $egWiki, $pbiWiki
        $wiki += $functionWiki
    }

    $wiki > $scriptPath\documentation.wiki.txt
}


$sqlFunctions = Parse-YamlDocs
switch ($output) {
    "html" {
        Generate-Html $sqlFunctions  
    }
    "wiki" {
        Generate-Wiki $sqlFunctions
    }
}