MetaNullPortfolio

0.3.33.0

MetaNullPortfolio.psm1

#Requires -Module SimplySQL
#Requires -Module ConfluencePS

# Module Constants

Set-Variable ConnectionNames -option Constant -value (@{
    Automatic = '2ec0861e-fa45-4c16-874b-93d6713122ef'
    Manual = '52f4abd0-e418-494e-bfe7-468c07a5b58b'
})
Function Close-SimplySQL {
#Function Close-SimplySQL {
    <#
        .SYNOPSIS
            "Private" function: Close the SimplySQL connection
 
        .EXAMPLE
            .\Open-SimplySQL -Pipe MariaDb -Credential root
            .\Get-SimplySQLResultset -Sql 'SELECT 1'
            .\Close-SimplySQL
    #>
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$false)]
        [string]
        $ConnectionName = $ConnectionNames.Manual
    )
    End {
        "Closing ConnectionName: $ConnectionName" | Write-Verbose

        if((Test-SqlConnection -ConnectionName ($ConnectionName))) {
            Close-SqlConnection -ConnectionName ($ConnectionName)
        }
    }
#}
}
Function ConvertFrom-ConfluencePageBody {
#Function ConvertFrom-ConfluencePageBody {
    <#
        .SYNOPSIS
        "Private" Function: Converts a "Body" String from for ConfluencePS' Page functions into a XDocument object suitable for the Portfolio module
    #>
    [CmdletBinding()]
    [OutputType([System.Xml.Linq.XDocument])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [String]
        $Body
    )
    Process {
        $XmlBody = '<xml xmlns:ac="ac" xmlns:ri="ri">{0}</xml>' -f ($Body | Repair-HtmlEntities)
        # $XmlBody = $XmlBody.Replace('<ri:page ri:content-title','<ri:page ri:space-key="ARC" ri:content-title') # Quick & Dirty fix for the Link Cards
        [System.Xml.Linq.XDocument]::Parse($XmlBody) | Write-Output
    }
#}
}
Function ConvertTo-ConfluencePageBody {
#Function ConvertTo-ConfluencePageBody {
    <#
        .SYNOPSIS
        "Private" Function: Converts a XDocument from the Portfolio module into a String suitable for ConfluencePS' Page functions
    #>
    [CmdletBinding()]
    [OutputType([string])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [System.Xml.Linq.XDocument]
        $Xml
    )
    Process {
        $Xml |
            Select-Xml -Namespace @{ac='ac';ri='ri'} -XPath '/xml' |
            Select-Object -ExpandProperty Node |
            Select-Object -ExpandProperty InnerXml |
            Write-Output
    }
#}
}
Function Get-SimplySQLResultset {
#Function Get-SimplySQLResultset {
    <#
        .SYNOPSIS
            "Private" function: Execute a SQL query using SimplySQL
 
        .EXAMPLE
            .\Open-SimplySQL -Pipe MariaDb -Credential root
            .\Get-SimplySQLResultset -Sql 'SELECT 1'
            .\Close-SimplySQL
    #>
    [CmdletBinding()]
    param(
        [Parameter(Mandatory,ValueFromPipeline)]
        [string]
        $SQL

        , [Parameter(Mandatory=$false)]
        [switch] $NonQuery

        , [Parameter(Mandatory=$false)]
        [string]
        $ConnectionName = $ConnectionNames.Manual
    )
    Begin {
        $AggregatedSQL = @()
    }
    Process {
        $AggregatedSQL += $SQL
    }
    End {
        "Querying ConnectionName: $ConnectionName" | Write-Verbose

        if(-not (Test-SqlConnection -ConnectionName ($ConnectionName))) {
            throw "Connection: $ConnectionName. Connection was not open"
        }

        if ($NonQuery) {
            Invoke-SqlUpdate -ConnectionName ($ConnectionName) -Query ($AggregatedSQL -join "`n")
        } else {
            Invoke-SqlQuery -ConnectionName ($ConnectionName) -Query ($AggregatedSQL -join "`n")
        }
    }
#}
}
Function Invoke-MariaDBClient {
#Function Invoke-MariaDBClient {
    <#
        .SYNOPSIS
            "Private" function: Invoke the mariadb client (mariadb.exe), and execute some SQL
 
        .EXAMPLE
            .\Invoke-MariaDBClient -Pipe MariaDb -Credential root -SQL 'SELECT 1'
    #>
    [CmdletBinding(DefaultParameterSetName='Pipe')]
    param(
        [Parameter(Mandatory,ValueFromPipeline)]
        [string]
        $SQL

        , [Parameter(Mandatory)]
        [System.Management.Automation.Credential()]
        [System.Management.Automation.PSCredential]
        $Credential

        , [Parameter(Mandatory,ParameterSetName='Pipe')]
        [string]
        $Pipe

        , [Parameter(Mandatory,ParameterSetName='Server')]
        [string]
        $Hostname
        , [Parameter(Mandatory=$false,ParameterSetName='Server')]
        [int]
        $Port = 3306

        , [Parameter(Mandatory=$false)]
        [ValidateScript({ Test-Path $_ -PathType Leaf })]
        [string]
        $MariaDb = 'c:\Program Files\MariaDB 11.4\bin\mariadb.exe'
    )
    Begin {
        if(-not $Pipe -and -not $Hostname) {
            throw "Either a pipe or a server must be specified"
        }
        if(-not $Credential -or $Credential -eq [System.Management.Automation.PSCredential]::Empty) {
            throw "A credential must be specified"
        }
    }
    End {
    }
    Process {
        $ExtraConfig = New-TemporaryFile -ErrorAction Stop
        try {
            if($Pipe) {
                Set-Content -Path $ExtraConfig -Value '[client]'
                Add-Content -Path $ExtraConfig -Value 'pipe'
                Add-Content -Path $ExtraConfig -Value 'protocol=pipe'
                Add-Content -Path $ExtraConfig -Value "socket=$($Pipe)"
            } elseif($Hostname) {
                Set-Content -Path $ExtraConfig -Value '[client]'
                Add-Content -Path $ExtraConfig -Value 'protocol=tcp'
                Add-Content -Path $ExtraConfig -Value "host=$($Hostname)"
                Add-Content -Path $ExtraConfig -Value "port=$($Port)"
            }
            Add-Content -Path $ExtraConfig -Value "user=$($Credential.GetNetworkCredential().UserName)"
            Add-Content -Path $ExtraConfig -Value "password=$($Credential.GetNetworkCredential().Password)"

            $SQL | &($MariaDb) "--defaults-extra-file=$ExtraConfig" --table 2>&1 | ForEach-Object {
                if ($_ -is [System.Management.Automation.ErrorRecord]) {
                    Write-Error $_
                } else {
                    $_
                }
            }
        } finally {
            Remove-Item -Force -Path $ExtraConfig
        }
    }
#}
}
Function Open-SimplySQL {
#Dunction Open-SimplySQL {
    <#
        .SYNOPSIS
            "Private" function: Open a SimplySQL connection
 
        .EXAMPLE
            .\Open-SimplySQL -Pipe MariaDb -Credential root
            .\Get-SimplySQLResultset -Sql 'SELECT 1'
            .\Close-SimplySQL
    #>
    [CmdletBinding(DefaultParameterSetName='Pipe')]
    param(
        [Parameter(Mandatory)]
        [System.Management.Automation.Credential()]
        [System.Management.Automation.PSCredential]
        $Credential

        , [Parameter(Mandatory,ParameterSetName='Pipe')]
        [string]
        $Pipe

        , [Parameter(Mandatory,ParameterSetName='Server')]
        [string]
        $Hostname
        , [Parameter(Mandatory=$false,ParameterSetName='Server')]
        [int]
        $Port = 3306

        , [Parameter(Mandatory=$false)]
        [string]
        $ConnectionName = $ConnectionNames.Manual
    )
    End {
        "Opening ConnectionName: $ConnectionName" | Write-Verbose

        if((Test-SqlConnection -ConnectionName ($ConnectionName))) {
            throw "Connection: $ConnectionName. Connection is already opened"
        }

        if(-not $Credential -or $Credential -eq [System.Management.Automation.PSCredential]::Empty) {
            throw "A credential must be specified"
        }

        if($Pipe) {
            $ConnectionString = "Server=.; Protocol=pipe; PipeName=$($Pipe); Allow User Variables=true; SSLMode=Disabled"
            Open-MySqlConnection -ConnectionName ($ConnectionName) -Credential ($Credential) -ConnectionString ($ConnectionString)
        } elseif($Hostname) {
            Open-MySqlConnection -ConnectionName ($ConnectionName) -Server ($Hostname) -Port ($Port) -Credential ($Credential)
        } else {
            throw "Either a pipe or a server must be specified"
        }

        if(-not (Test-SqlConnection -ConnectionName ($ConnectionName))) {
            throw "Connection: $ConnectionName. Connection failed"
        }
    }
#}
}
Function Repair-HtmlEntities {
#Function Repair-HtmlEntities {
    <#
    .SYNOPSIS
        "Private" function, helps dealing with HTML entities in Confluence documents.
        Confluence prefers Named HTML entities, that are not readily parsable by System.Xml.Lniq.XDocument
    #>
    [CmdletBinding()]
    [OutputType([string])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [String]
        $Html
    )
    Process {
        $NewHtml = $Html.PSObject.Copy()
        # Find all named HTML entities (&nbsp;)
        [regex]::matches($NewHtml,'&[A-Za-z][A-Za-z0-9]+;') | Select-Object -ExpandProperty Value -Unique | Foreach-Object {
            $OriginalEntity = $_
            $ReencodedEntity = [System.Net.WebUtility]::HtmlEncode([System.Net.WebUtility]::HtmlDecode($OriginalEntity))
            if($OriginalEntity -eq $ReencodedEntity) {
                # "$Originalentity would not be changed, skipping" | Write-Verbose
                return
            }
            # Replace the named html entity by a XML numeric entity (e.g. &nbsp; becomes &#160;)
            "Re-encoding $OriginalEntity into $ReencodedEntity" | Write-Verbose
            $NewHtml = $NewHtml.Replace($OriginalEntity,$ReencodedEntity)
        }
        $NewHtml | Write-Output
    }
#}
}
Function Select-ConfluenceExcerpt {
#Function Select-ConfluenceExcerpt {
    <#
        .SYNOPSIS
        "Private" Function: Extract "Confluence Excerpt" macros from a XDocument or Node
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject[]])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [System.Object]
        $Xml
    )
    Begin {
        $ns = @{ac='ac';ri='ri'}
    }
    Process {
        $Xml | Select-Xml -namespace $ns -XPath './/ac:structured-macro[@ac:name="excerpt"]' | Foreach-Object {
            [pscustomobject]@{
                Name = $_ | Select-Xml -namespace $ns -XPath './ac:parameter[@ac:name="name"]' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty InnerText
                Body = $_ | Select-Xml -namespace $ns -XPath './ac:rich-text-body' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty InnerXml
            } | Write-Output
        }
    }
#}
}
Function Select-ConfluenceImage {
#Function Select-ConfluenceImage {
    <#
        .SYNOPSIS
        "Private" Function: Extract "Confluence Image" macros from a XDocument or Node
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject[]])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [System.Object]
        $Xml
    )
    Begin {
        $ns = @{ac='ac';ri='ri'}
    }
    Process {
        $Xml | Select-Xml -namespace $ns -XPath './/ac:image/ri:attachment' | Foreach-Object {
            [pscustomobject]@{
                Title = $_ | Select-Xml -namespace $ns -XPath './parent::ac:image/@ac:alt' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty Value
                Filename = $_ | Select-Xml -namespace $ns -XPath './@ri:filename' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty Value
            } | Write-Output
        }
    }
#}
}
Function Select-ConfluenceJira {
#Function Select-ConfluenceJira {
    <#
        .SYNOPSIS
        "Private" Function: Extract "Confluence Jira" macros from a XDocument or Node
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject[]])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [System.Object]
        $Xml
    )
    Begin {
        $ns = @{ac='ac';ri='ri'}
    }
    Process {
        $Xml | Select-Xml -namespace $ns -XPath './/ac:structured-macro[@ac:name="jira"]' | Foreach-Object {
            [pscustomobject]@{
                Key = $_ | Select-Xml -namespace $ns -XPath './ac:parameter[@ac:name="key"]' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty InnerText
                ServerId = $_ | Select-Xml -namespace $ns -XPath './ac:parameter[@ac:name="serverId"]' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty InnerText
                Server = $_ | Select-Xml -namespace $ns -XPath './ac:parameter[@ac:name="server"]' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty InnerText
            } | Where-Object {
                $_.Key -ne [string]::Empty
            } | Foreach-Object {
                $_ | Write-Output
            }
        }
    }
#}
}
Function Select-ConfluencePageLink {
#Function Select-ConfluencePageLink {
    <#
        .SYNOPSIS
        "Private" Function: Extract "Confluence Page Link" macros from a XDocument or Node
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject[]])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [System.Object]
        $Xml
    )
    Begin {
        $ns = @{ac='ac';ri='ri'}
    }
    Process {
        $Xml | Select-Xml -namespace $ns -XPath './/ac:link/ri:page' | Foreach-Object {
            [pscustomobject]@{
                Title = $_ | Select-Xml -namespace $ns -XPath './@ri:content-title' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty Value
                SpaceKey = $_ | Select-Xml -namespace $ns -XPath './@ri:space-key' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty Value
                # The version number of the referenced confluence page at the moment the link was created
                PageVersion = $_ | Select-Xml -namespace $ns -XPath './@ri:version-at-save' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty Value
            } | Write-Output
        }
    }
#}
}
Function Select-ConfluencePageLinkFollowedByVersionNumber {
#Function Select-ConfluencePageLinkFollowedByVersionNumber {
    <#
        .SYNOPSIS
        "Private" Function: Like Select-PageLink, it extracts "Confluence Page Link" macros from a XDocument or Node.
        But only the Links "followed by a version number" would be returned.
        e.g.: the following XML matches:
             <p><ac:link><ri:page ri:content-title="Java" ri:space-key="ARC" ri:version-at-save="1">Java</ac:link> v8.2</p>
        -> it will be parsed into:
            @{Title=Java;SpaceKey=ARC;PageVersion=1;VersionString=v8.2;Version={Major=8;Minor=2;Build=0;Revision=0}}
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject[]])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [System.Object]
        $Xml
    )
    Begin {
        $ns = @{ac='ac';ri='ri'}
    }
    Process {
        $Xml | Select-Xml -namespace $ns -XPath './/ac:link[ri:page]' | Foreach-Object {
            [pscustomobject]@{
                Title = $_ | Select-Xml -namespace $ns -XPath './ri:page/@ri:content-title' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty Value
                SpaceKey = $_ | Select-Xml -namespace $ns -XPath './ri:page/@ri:space-key' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty Value
                PageVersion = $_ | Select-Xml -namespace $ns -XPath './ri:page/@ri:version-at-save' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty Value
                VersionString = $_ | Select-Xml -namespace $ns -xpath '(./following-sibling::text()|./following-sibling::*//text())' | select-object -ExpandProperty Node | Select-Object -ExpandProperty Value
                Version = $null
                # ParentInner = $_ | Select-Xml -namespace $ns -xpath './parent::*' | select-object -ExpandProperty Node | Select-Object -ExpandProperty InnerXml
            } | Where-Object {
                # Is there a 'version' string immediatelly following the link?
                $_.VersionString | Select-Version
            } | Foreach-Object {
                $_.Version = $_.VersionString | Select-Version
                $_ | Write-Output
            }
        }
    }
#}
}
Function Select-ConfluencePageProperties {
#Function Select-ConfluencePageProperties {
    <#
        .SYNOPSIS
        "Private" Function: Extract "Confluence PageProperties" macros from a XDocument or Node
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject[]])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [System.Object]
        $Xml

        , [switch]
          $ReturnXml
    )
    Begin {
        $ns = @{ac='ac';ri='ri'}
    }
    Process {
        $Xml | Select-Xml -namespace $ns -XPath './/ac:structured-macro[@ac:name="details"]' | ForEach-Object {
            $PagePropertyId = ($_ | Select-Xml -namespace $ns -XPath './ac:parameter[@ac:name="id"]').Node.InnerText
            $_ | Select-Xml -Namespace $ns -XPath './/tr' | Foreach-Object {
                if($ReturnXml) {
                    $Value = ($_ | Select-Xml -Namespace $ns -XPath './/td')
                } else {
                    $Value = ($_ | select-xml -namespace $ns -xpath '(.//td//*[local-name()=name()]/text()|.//td//ac:link/ac:link-body|.//td//ac:structured-macro[@ac:name="status"]/ac:parameter[@ac:name="title"])').Node.InnerText # or: .Node.Value
                }
                if ($DebugPreference -eq 'Continue') {
                    [pscustomobject]@{
                        PagePropertyId = $PagePropertyId
                        PropertyId = ($_ | Select-Xml -Namespace $ns -XPath '(.//th//*[local-name()=name()]/text()|.//th//ac:link/ac:link-body|.//th//ac:structured-macro[@ac:name="status"]/ac:parameter[@ac:name="title"])').Node.InnerText
                        Value = $value
                        PropertyIdInnerXml = ($_ | select-xml -namespace $ns -xpath './/th' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty InnerXml)
                        ValueInnerXml = ($_ | select-xml -namespace $ns -xpath './/td' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty InnerXml)
                    } | Write-Output
                } else {
                    [pscustomobject]@{
                        PagePropertyId = $PagePropertyId
                        PropertyId = ($_ | Select-Xml -Namespace $ns -XPath '(.//th//*[local-name()=name()]/text()|.//th//ac:link/ac:link-body|.//th//ac:structured-macro[@ac:name="status"]/ac:parameter[@ac:name="title"])').Node.InnerText
                        Value = $value
                    } | Write-Output
                }
            }
        }
    }
#}
}
Function Select-ConfluenceStatus {
#Function Select-ConfluenceStatus {
    <#
        .SYNOPSIS
        "Private" Function: Extract "Confluence Status" macros from a XDocument or Node
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject[]])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [System.Object]
        $Xml
    )
    Begin {
        $ns = @{ac='ac';ri='ri'}
    }
    Process {
        $Xml | Select-Xml -namespace $ns -XPath './/ac:structured-macro[@ac:name="status"]' | Foreach-Object {
            [pscustomobject]@{
                Color = $_ | Select-Xml -namespace $ns -XPath './ac:parameter[@ac:name="colour"]' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty InnerText
                Title = $_ | Select-Xml -namespace $ns -XPath './ac:parameter[@ac:name="title"]' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty InnerText
            } | Write-Output
        }
    }
#}
}
Function Select-TextFromXmlValue {
#Function Select-TextFromXmlValue {
    [CmdletBinding()]
    [OutputType([string])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [AllowNull()]
        [System.Object]
        $ValueXml
    )
    Begin {
        $ns = @{ac='ac';ri='ri'}
    }
    Process {
        $ValueXml | ForEach-Object {
            $_ |
                Select-Xml -namespace $ns -xpath '(.//*[namespace-uri()=""][text()]/text()|.//ac:link/ac:link-body|.//ac:structured-macro[@ac:name="status"]/ac:parameter[@ac:name="title"])' |
                Select-Object -ExpandProperty Node |
                Select-Object -ExpandProperty InnerText |
                Write-Output
        }
    }
#}
}
Function Select-Version {
#Function Select-Version {
    <#
    .SYNOPSIS
        "Private" function, produce [version] objects out of [string].
        It can convert version strings matching the following patterns:
        - 1.2.3
        - v. 1.2
        - v1.2
        - ver. 1.2.3
        - version 1.2.3
        - version: 1.2.3
 
    .PARAMETER InputString
        The input string to select from
 
    .PARAMETER AsString
        By default the Version property is returned as a [Version] object. If -AsString is set, the string representation of the version is sent instead
    #>
    [CmdletBinding()]
    [OutputType([string])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [AllowEmptyString()]
        [String]
        $InputString,

        [Parameter(Mandatory=$false)]
        [switch]
        $AsString
    )
    Process {
        # Does the string matching a 'version' pattern?


        $VersionString = $InputString.Trim()
        if(-not ($VersionString -imatch '^\s*(?:(?:v|ver|version)\s*[\.:-]?\s*)?(\d+(?:\.\d+)*)')) {
            # No version found, skip
            return
        }
        # Convert in a [version] object
        $InputArray = ($Matches[1] -split '\.',5)
        $VersionArray = @(0,0,0,0)
        for($i = 0; ($i -lt $VersionArray.Length) -and ($i -lt $InputArray.Length); $i ++) {
            $VersionArray[$i] = [int]$InputArray[$i]
        }
        $VersionObject = ([version]::new($VersionArray -join '.'))
        if($versionObject -eq '0.0.0.0') {
            # Null version, skip
            return
        }
        if($AsString) {
            $VersionObject.ToString() | Write-Output
        } else {
            $VersionObject | Write-Output
        }
    }
#}
}
Function ConvertFrom-ConfluencePage {
#Function ConvertFrom-ConfluencePage {
    <#
        .SYNOPSIS
        "Public" Function: Converts a ConfluencePS.Page into an "Artifact" (a PSCustomObject understandable by the Portfolio module)
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [ConfluencePS.Page]
        $Page

        , [Parameter(Mandatory=$false,Position=1)]
          [ConfluencePS.ContentLabelSet]
          $LabelSet
    )
    Process {
        if(-not $Page.Body) {
            Write-Warning ("Page {0} '{1}' has no body!" -f $Page.ID,$Page.Title)
        } else {
            # Populate a new object from confluence's page data
            $Artifact = $Page | Select-Object ID,Title,Body,Status,URL,ShortURL,
                @{label='VersionNumber';expression={[uint64]$_.Version.Number}},
                @{label='SpaceKey';expression={$_.Space.Key}},
                @{label='AuthorName';expression={$_.Version.By.DisplayName}},
                @{label='Version';expression={$_.Version | Select-Object Number,When,Message,MinorEdit}},
                @{label='Space';expression={$_.Space | Select-Object Id,Key,Name}},
                @{label='Author';expression={$_.Version.By | Select-Object DisplayName,UserKey,UserName,@{label='Avatar';expression={$_.ProfilePicture | Select-Object Path,Width,Height}}}},
                @{label='AncestorList';expression={$_.Ancestors | Select-Object ID,Title}}
            if($null -ne $LabelSet) {
                # Add labels to the confluence's page data
                $Artifact | Add-Member -MemberType NoteProperty -Name LabelList -Value ($LabelSet | Select-Object -ExpandProperty Labels | Select-Object -ExpandProperty Name)
            } else {
                $Artifact | Add-Member -MemberType NoteProperty -Name LabelList -Value ([string[]]@())
            }
            # Body as an XML XDocument object
            $Artifact | Add-Member -MemberType NoteProperty -Name BodyXml -Value ($Artifact.Body | ConvertFrom-ConfluencePageBody)

            # Remove Confluence 'Place Holders'
            $PlaceHolderXName = ([System.Xml.Linq.XNamespace]::Get('ac')+'placeholder')
            $PlaceHolderList = $Artifact.BodyXml.Descendants($PlaceHolderXName)
            while($PlaceHolderList|Measure-Object|Where-Object{$_.Count}) {
                $PlaceHolderList | Select-Object -Last 1 | Foreach-Object {
                    $_.Remove()
                }
                $PlaceHolderList = $Artifact.BodyXml.Descendants($PlaceHolderXName)
            }

            # XML Extraction - Extract more information from the Confluence's page body
            # Page Properties
            $Artifact | Add-Member -MemberType NoteProperty -Name PagePropertyList -Value ($Artifact.BodyXml | Select-ConfluencePageProperties)
            # Exerpts
            $Artifact | Add-Member -MemberType NoteProperty -Name ExcerptList -Value ($Artifact.BodyXml | Select-ConfluenceExcerpt)

            # Links in Page Properties
            $PagePropertiesXml = $Artifact.BodyXml | Select-ConfluencePageProperties -ReturnXml
            # All Links within any "Page Property"
            $Artifact | Add-Member -MemberType NoteProperty -Name LinkList -Value ($PagePropertiesXml | Foreach-Object {
                    $PageProperty = $_
                    $PageProperty.Value | Select-ConfluencePageLink | Foreach-Object {
                        $_ | Add-Member -MemberType NoteProperty -Name PagePropertyId -Value ($PageProperty.PagePropertyId)
                        $_ | Add-Member -MemberType NoteProperty -Name PropertyId -Value ($PageProperty.PropertyId)
                        $_ | Write-Output
                    }
                })
            # All "Version Links" within any "Page Property"
            $Artifact | Add-Member -MemberType NoteProperty -Name VersionLinkList -Value ($PagePropertiesXml | Foreach-Object {
                    $PageProperty = $_
                    $PageProperty.Value | Select-ConfluencePageLinkFollowedByVersionNumber| Foreach-Object {
                        $_ | Add-Member -MemberType NoteProperty -Name PagePropertyId -Value ($PageProperty.PagePropertyId)
                        $_ | Add-Member -MemberType NoteProperty -Name PropertyId -Value ($PageProperty.PropertyId)
                        $_ | Write-Output
                    }
                })

            $Artifact | Write-Output
        }
    }
#}
}
Function Expand-Artifact {
#Function Expand-Artifact {
    <#
        .SYNOPSIS
        "Public" Function: Expands an Artifact, issuing from ConvertFrom-ConfluencePage extracting Artifact specific data
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [PSCustomObject]
        $Artifact
    )
    Begin {
        Function Select-LinkListFromXmlValue {
            [CmdletBinding()]
            [OutputType([string])]
            param(
                [Parameter(Mandatory,ValueFromPipeline,Position=0)]
                [AllowNull()]
                [string]
                $LinkName,

                [Parameter(Mandatory,Position=1)]
                [System.Object]
                $Artifact,

                [Parameter(Mandatory=$false)]
                [switch]
                $UseVersionLinkList
            )
            Begin {
                $ns = @{ac='ac';ri='ri'}
            }
            Process {
                $LinkName | Foreach-Object {
                    $CurrentName = $_
                    if($UseVersionLinkList) {
                        $Artifact.VersionLinkList | Where-Object {
                            $_.Title -eq $CurrentName
                        } | Write-Output
                    } else {
                        $Artifact.LinkList | Where-Object {
                            $_.Title -eq $CurrentName
                        } | Write-Output
                    }
                }
            }
        }

    }
    Process {
        # Artifact's Name and Type from Page Properties
        $Artifact.BodyXml | Select-ConfluencePageProperties -ReturnXml | Foreach-Object {
            # Product; Name is in info.Name
            if($_.PagePropertyId -eq 'info' -and $_.PropertyId -eq 'Name') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactName -Value ($_.Value | Select-TextFromXmlValue) -Force
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactType -Value 'Product' -Force
            }
            # Container; Name is in container.Container
            if($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'Container') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactName -Value ($_.Value | Select-TextFromXmlValue) -Force
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactType -Value 'Container' -Force
            }
            # Technology; Name is in technology.Technology
            if ($_.PagePropertyId -eq 'technology' -and $_.PropertyId -eq 'Technology') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactName -Value ($_.Value | Select-TextFromXmlValue) -Force
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactType -Value 'Technology' -Force
            }
            # Database instance; Name is in instance.Name
            if ($_.PagePropertyId -eq 'instance' -and $_.PropertyId -eq 'Name') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactName -Value ($_.Value | Select-TextFromXmlValue) -Force
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactType -Value 'DatabaseInstance' -Force
            }
            # Actor; Name is in actor.Actor
            if ($_.PagePropertyId -eq 'actor' -and $_.PropertyId -eq 'Actor') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactName -Value ($_.Value | Select-TextFromXmlValue) -Force
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactType -Value 'Actor' -Force
            }
            # Server; Name is in server.Name
            if ($_.PagePropertyId -eq 'server' -and $_.PropertyId -eq 'Name') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactName -Value ($_.Value | Select-TextFromXmlValue) -Force
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactType -Value 'Server' -Force
            }
            # Domain; Name is in domain.Domain
            if ($_.PagePropertyId -eq 'domain' -and $_.PropertyId -eq 'Domain') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactName -Value ($_.Value | Select-TextFromXmlValue) -Force
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactType -Value 'Domain' -Force
            }
            # BoundedContext; Name is in info.Name
            if($_.PagePropertyId -eq 'context' -and $_.PropertyId -eq 'Bounded context') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactName -Value ($_.Value | Select-TextFromXmlValue) -Force
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactType -Value 'BoundedContext' -Force
            }
            # KeyDataEntity; Name is in key-data-entity.Name
            if($_.PagePropertyId -eq 'key-data-entity' -and $_.PropertyId -eq 'Name') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactName -Value ($_.Value | Select-TextFromXmlValue) -Force
                $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactType -Value 'KeyDataEntity' -Force
            }
            # Disambiguation, ...; Name is in [null].Name
            if (-not ($Artifact.ArtifactName)) {
                if (($null -eq $_.PagePropertyId -or $_.PagePropertyId -eq [string]::Empty) -and $_.PropertyId -eq 'Name') {
                    $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactName -Value ($_.Value | Select-TextFromXmlValue) -Force
                    $Artifact | Add-Member -MemberType NoteProperty -Name ArtifactType -Value $null  -Force
                }
            }

            # Bounded Context;
            if ($_.PagePropertyId -eq 'context' -and $_.PropertyId -eq 'Data context') {
                $Artifact | Add-Member -MemberType NoteProperty -Name BoundedContextDataContext -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            if ($_.PagePropertyId -eq 'context' -and $_.PropertyId -eq 'Relations') {
                $Artifact | Add-Member -MemberType NoteProperty -Name BoundedContextRelationLink -Value ($_.Value | Select-TextFromXmlValue | Select-LinkListFromXmlValue -Artifact $Artifact) -Force
            }
            if ($_.PagePropertyId -eq 'context' -and $_.PropertyId -eq 'Data context') {
                $Artifact | Add-Member -MemberType NoteProperty -Name BoundedContextDataContextLink -Value ($_.Value | Select-TextFromXmlValue | Select-LinkListFromXmlValue -Artifact $Artifact) -Force
            }
            # Domain;
            if ($_.PagePropertyId -eq 'domain' -and $_.PropertyId -eq 'GUID') {
                $Artifact | Add-Member -MemberType NoteProperty -Name DomainSharepointGUID -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            # Product;
            if ($_.PagePropertyId -eq 'info' -and $_.PropertyId -eq 'Description') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ProductDescription -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            if ($_.PagePropertyId -eq 'info' -and $_.PropertyId -eq 'System architecture') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ProductArchitecture -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            if ($_.PagePropertyId -eq 'info' -and $_.PropertyId -eq 'Status') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ProductStatus -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            # Container;
            if ($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'Product') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ContainerProductLink -Value ($_.Value | Select-TextFromXmlValue | Select-LinkListFromXmlValue -Artifact $Artifact) -Force
            }
            if ($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'Type') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ContainerType -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            if ($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'Authentication') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ContainerAuthenticationLink -Value ($_.Value | Select-TextFromXmlValue | Select-LinkListFromXmlValue -Artifact $Artifact) -Force
            }
            if ($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'Authorization') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ContainerAuthorizationLink -Value ($_.Value | Select-TextFromXmlValue | Select-LinkListFromXmlValue -Artifact $Artifact) -Force
            }
            if ($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'Code repository') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ContainerCodeLink -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            if ($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'URL Production') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ContainerUri -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            if ($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'Hosting') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ContainerServerLink -Value $Links -Force
            }
            if ($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'Dependencies (internal)') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ContainerInternalLink -Value ($_.Value | Select-TextFromXmlValue | Select-LinkListFromXmlValue -Artifact $Artifact) -Force
            }
            if ($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'Dependencies (external)') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ContainerExternalLink -Value ($_.Value | Select-TextFromXmlValue | Select-LinkListFromXmlValue -Artifact $Artifact) -Force
            }
            if ($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'Actors') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ContainerActorLink -Value ($_.Value | Select-TextFromXmlValue | Select-LinkListFromXmlValue -Artifact $Artifact) -Force
            }
            if ($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'Main frameworks') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ContainerTechnologyLink -Value ($_.Value | Select-TextFromXmlValue | Select-LinkListFromXmlValue -UseVersionLinkList -Artifact $Artifact) -Force
            }
            if ($_.PagePropertyId -eq 'container' -and $_.PropertyId -eq 'Frameworks & libraries') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ContainerDependencyLink -Value ($_.Value | Select-TextFromXmlValue | Select-LinkListFromXmlValue -UseVersionLinkList -Artifact $Artifact) -Force
            }
            # Server;
            if ($_.PagePropertyId -eq 'server' -and $_.PropertyId -eq 'Os') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ServerOperatingSystem -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            if ($_.PagePropertyId -eq 'server' -and $_.PropertyId -eq 'IP') {
                $Artifact | Add-Member -MemberType NoteProperty -Name ServerIPAddress -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            # Database instance;
            if ($_.PagePropertyId -eq 'instance' -and $_.PropertyId -eq 'Vendor') {
                $Artifact | Add-Member -MemberType NoteProperty -Name DatabaseVendor -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            if ($_.PagePropertyId -eq 'instance' -and $_.PropertyId -eq 'Version') {
                $Artifact | Add-Member -MemberType NoteProperty -Name DatabaseVersion -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            # KeyDateEntity;

            # ??
            if (($null -eq $_.PagePropertyId -or $_.PagePropertyId -eq [string]::Empty) -and $_.PropertyId -eq 'A.k.a.') {
                $Artifact | Add-Member -MemberType NoteProperty -Name DataEntityAlias -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            if (($null -eq $_.PagePropertyId -or $_.PagePropertyId -eq [string]::Empty) -and $_.PropertyId -eq 'Bounded context') {
                $Artifact | Add-Member -MemberType NoteProperty -Name DataEntityContext -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            # Technology;
            if ($_.PagePropertyId -eq 'technology' -and $_.PropertyId -eq 'Vendor') {
                $Artifact | Add-Member -MemberType NoteProperty -Name TechnologyVendor -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            if ($_.PagePropertyId -eq 'technology' -and $_.PropertyId -eq 'Status') {
                $Artifact | Add-Member -MemberType NoteProperty -Name TechnologyStatus -Value ($_.Value | Select-TextFromXmlValue) -Force
            }
            if ($_.PagePropertyId -eq 'technology' -and $_.PropertyId -eq 'Outlook') {
                $Artifact | Add-Member -MemberType NoteProperty -Name TechnologyReplacementLink -Value $Links -Force
            }
            if ($_.PagePropertyId -eq 'technology-version' -and $_.PropertyId -eq 'Recommended') {
                $Artifact | Add-Member -MemberType NoteProperty -Name TechnologyReplacementLink -Value $Links -Force
            }
            # Technology; "Technology's Version Support" information
            if ($_.PagePropertyId -eq 'technology-version') {
                $VersionString =  ($_.Value | select-xml -namespace @{ac='ac';ri='ri'} -xpath ("(.//text()|.//*/text())")).Node.Value
                $Version = $VersionString | Select-Version
                if($Version -ne '0.0.0.0') {
                    $Artifact | Add-Member -MemberType NoteProperty -Name ("$($_.PropertyId)Version") -Value ($Version)
                    # $Artifact | Add-Member -MemberType NoteProperty -Name ("$($_.PropertyId)VersionString") -Value ($VersionString)
                }
            }
        }

        $Artifact | Write-Output
    }
#}
}
Function Export-Artifact {
#Function Export-Artifact {
    <#
        .SYNOPSIS
            "Public" function: Store an Artifact into the database
 
        .EXAMPLE
            $PipeName = 'MariaDb'
            $ConnectionName = (New-GUID).ToString()
            Open-SimplySQL -ConnectionName $ConnectionName -Credential $Credential -Pipe $PipeName
 
            Read-Confluence | Export-Artifact -ConnectionName $ConnectionName
 
            Close-SimplySQL -ConnectionName $ConnectionName
    #>
    [CmdletBinding()]
    [OutputType([void])]
    param(
        [Parameter(Mandatory,Position=0)]
        [ValidateNotNullOrEmpty()]
        [ValidateScript({Test-SqlConnection -ConnectionName $_})]
        [String]
        $ConnectionName

        ,[Parameter(Mandatory,ValueFromPipeline,Position=1)]
        [ValidateNotNullOrEmpty()]
        [ValidateScript({$_ | Test-Artifact})]
        [object]
        $Artifact
    )
    Begin {
        $InsertArtifactStatement = @'
REPLACE INTO portfolio.confluence (
      Id
    , Version
    , Space
    , Title
    , Attributes
)
VALUES (
      ?Id
    , ?Version
    , ?Space
    , ?Title
    , JSON_COMPACT(?Attributes)
)
'@
        $DeleteLabelStatement = @'
DELETE FROM portfolio.label
WHERE Id = ?Id
'@
        $InsertLabelStatement = @'
INSERT INTO portfolio.label (
      Id
    , Label
)
VALUES (
      ?Id
    , ?Label
)
'@
        $DeleteBodyStatement = @'
DELETE FROM portfolio.body WHERE Id = ?Id
'@
        $InsertBodyStatement = @'
INSERT INTO portfolio.body (
      Id
    , Body
)
VALUES (
      ?Id
    , ?Body
)
'@
        $DeleteBodyXmlStatement = @'
DELETE FROM portfolio.bodyxml WHERE Id = ?Id
'@
        $InsertBodyXmlStatement = @'
INSERT INTO portfolio.bodyxml (
      Id
    , Body
)
VALUES (
      ?Id
    , ?Body
)
'@

        $DeleteLinkStatement = @'
DELETE FROM portfolio.Link WHERE Id = ?Id
'@
        $InsertLinkStatement = @'
INSERT INTO portfolio.Link (
      Id
    , PagePropertyId
    , PropertyId
    , LinkTitle
    , LinkSpace
    , LinkVersion
    , LinkId
)
VALUES (
      ?Id
    , ?PagePropertyId
    , ?PropertyId
    , ?LinkTitle
    , ?LinkSpace
    , ?LinkVersion
    , portfolio.get_confluence_id_by_title(?LinkIdTitle)
)
'@
    }
    Process {
        "Inserting artifact for page $($Artifact.Id)" | Write-Verbose
        <#if(-not ($Artifact Test-Artifact)) {
            throw "Invalid input object"
        }#>

        try {
            Start-SqlTransaction -ConnectionName $ConnectionName

            # Delete associated content (Labels, Body and BodyXml), if any
            $DeleteAssociatedParameters = @{
                Id = $Artifact.ID
            }
            $DeleteAssociatedCount = 0
            $DeleteAssociatedCount += Invoke-SqlUpdate -ConnectionName $ConnectionName -Query $DeleteLabelStatement -Parameters $DeleteAssociatedParameters | Out-Null
            $DeleteAssociatedCount += Invoke-SqlUpdate -ConnectionName $ConnectionName -Query $DeleteBodyStatement -Parameters $DeleteAssociatedParameters | Out-Null
            $DeleteAssociatedCount += Invoke-SqlUpdate -ConnectionName $ConnectionName -Query $DeleteBodyXmlStatement -Parameters $DeleteAssociatedParameters | Out-Null
            $DeleteAssociatedCount += Invoke-SqlUpdate -ConnectionName $ConnectionName -Query $DeleteLinkStatement -Parameters $DeleteAssociatedParameters | Out-Null
            "$DeleteAssociatedCount associated rows deleted" | Write-Verbose

            # Store the artifact (without Body and BodyXml)
            $Properties = $Artifact | Get-Member -MemberType Property,NoteProperty | Select-Object -ExpandProperty Name | Where-Object {$_ -notin 'Body','BodyXml'}
            $Attributes = $Artifact | Select-Object -Property $Properties | ConvertTo-Json -Compress -Depth 10

            $InserArtifactParameters = @{
                Id = $Artifact.ID
                Version = $Artifact.VersionNumber
                Space = $Artifact.SpaceKey
                Title = $Artifact.Title
                Attributes = $Attributes
            }
            $ArtifactInserted = Invoke-SqlUpdate -ConnectionName $ConnectionName -Query $InsertArtifactStatement -Parameters $InserArtifactParameters | Out-Null
            "$ArtifactInserted Artifact inserted" | Write-Verbose

            # Store the artifact's Body
            $InsertBodyParameters = @{
                Id = $Artifact.ID
                Body = $Artifact.Body
            }
            $BodyInserted = Invoke-SqlUpdate -ConnectionName $ConnectionName -Query $InsertBodyStatement -Parameters $InsertBodyParameters | Out-Null
            "$BodyInserted Body inserted" | Write-Verbose

            # Store the artifact's BodyXml
            $InsertBodyXmlParameters = @{
                Id = $Artifact.ID
                Body = $Artifact.BodyXml.ToString([System.Xml.Linq.SaveOptions]::None)
            }
            $BodyXmlInserted = Invoke-SqlUpdate -ConnectionName $ConnectionName -Query $InsertBodyXmlStatement -Parameters $InsertBodyXmlParameters | Out-Null
            "$BodyXmlInserted Body(Xml) inserted" | Write-Verbose


            # Store the artifact's Labels
            $LabelsInserted = 0
            $Artifact.LabelList | Where-Object { $_ } | Foreach-Object {
                $LabelsInserted += Invoke-SqlUpdate -ConnectionName $ConnectionName -Query $InsertLabelStatement -Parameters @{
                    Id = $Artifact.ID
                    Label = $_
                } | Out-Null
            }
            if($LabelsInserted -gt 1) {
                "$LabelsInserted Label(s) inserted" | Write-Verbose
            } elseif($LabelsInserted -eq 1) {
                "$LabelsInserted Label inserted" | Write-Verbose
            }

            # Store the artifact's Links
            $LinksInserted = 0
            $Artifact.LinkList | Where-Object { $_ } | Foreach-Object {
                $LinksInserted += Invoke-SqlUpdate -ConnectionName $ConnectionName -Query $InsertLinkStatement -Parameters @{
                    Id = $Artifact.ID
                    PagePropertyId = $_.PagePropertyId
                    PropertyId = $_.PropertyId
                    LinkTitle = $_.Title
                    LinkSpace = $_.SpaceKey -as [string]
                    LinkVersion = $_.PageVersion
                    LinkIdTitle = $_.Title
                } | Out-Null
            }
            if($LinksInserted -gt 1) {
                "$LinksInserted Link(s) inserted" | Write-Verbose
            } elseif($LinksInserted -eq 1) {
                "$LinksInserted Link inserted" | Write-Verbose
            }

            Complete-SqlTransaction -ConnectionName $ConnectionName
        } catch {
            Undo-SqlTransaction -ConnectionName $ConnectionName
            throw $_
        }
    }

#}
}
Function Import-Artifact {
#Function Import-Artifact {
    <#
        .SYNOPSIS
            "Public" function: Create a new Artifact from the Database
 
        .EXAMPLE
            $PipeName = 'MariaDb'
            $ConnectionName = (New-GUID).ToString()
            Open-SimplySQL -ConnectionName $ConnectionName -Credential $Credential -Pipe $PipeName
 
            Import-Artifact -ConnectionName $ConnectionName | Format-Table
 
            Close-SimplySQL -ConnectionName $ConnectionName
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject])]
    param(
        [Parameter(Mandatory,Position=0)]
        [ValidateNotNullOrEmpty()]
        [ValidateScript({Test-SqlConnection -ConnectionName $_})]
        [String]
        $ConnectionName

        , [Parameter(Mandatory=$false)]
        [int[]]
        $WithIdIn
    )
    Begin {
        $WithIdStatement = @('SELECT 123456789 Id FROM DUAL WHERE 1 = 0')
        $WithIdStatement += ($WithIdIn | Where-Object { $null -ne $_ }| Foreach-Object {'SELECT {0} Id FROM DUAL' -f ($_ -as [int])})
        $WithIdStatement = $WithIdStatement -join ' UNION ALL '

        $SelectArtifactStatement = @'
WITH Ids AS (
    {0}
)
SELECT confluence.Id
    , confluence.Version
    , confluence.Space
    , confluence.Title
    , confluence.Attributes
    , body.Body
FROM portfolio.confluence
LEFT JOIN portfolio.body
  ON body.Id = confluence.Id
WHERE (NOT EXISTS(SELECT * FROM Ids) OR confluence.Id IN (SELECT Id FROM Ids))
'@ -f $WithIdStatement

$SelectArtifactStatement | Write-Debug

    }
    Process {
        Invoke-SqlQuery -ConnectionName $ConnectionName -Query $SelectArtifactStatement | ForEach-Object {
            $Artifact = $_.Attributes | ConvertFrom-Json
            $Artifact.ID = [uint64]$Artifact.ID
            $Artifact | Add-Member -MemberType NoteProperty -Name Body -Value ($_.Body -as [string])
            $Artifact | Add-Member -MemberType NoteProperty -Name BodyXml -Value ($_.Body | ConvertFrom-ConfluencePageBody)
            $Artifact | Write-Output
        }
    }

#}
}
Function Invoke-SimplySQL {
#Function Invoke-SimplySQL {
    <#
        .SYNOPSIS
            Invoke SimplySQL: Connect to the database, execute a SQL query, Close the connection and return the resultset
 
        .PARAMETER Credential
            Database Credential [example: (Get-Credential root)]
 
        .PARAMETER Pipe
            Database Named Pipe's name [example: MariaDB]
 
        .PARAMETER Hostname
            Database server's Hostname [example: 127.0.0.1]
 
        .PARAMETER Port
            Database server's Port number [example: 3306]
 
        .PARAMETER SQL
            A single SQL query to execute
 
        .PARAMETER NonQuery
            If set, the SQL query is a statement that does not return a resultset (such an update, an insert, a delete, etc.)
 
        .EXAMPLE
            # Invoke a SELECT query and dump the results
            Invoke-SimplySQL -Pipe MariaDb -Credential root -Sql 'SELECT 1 AS a, 2 AS b, 3 AS c' | Format-Table
 
        .EXAMPLE
            # Invoke a INSERT query
            Invoke-SimplySQL -Pipe MariaDb -Credential root -Sql 'INSERT INTO test(a,b,c) VALUES (1,2,3)' -NonQuery
 
        .EXAMPLE
            # Invoke a SELECT query from a file
            Get-Content -Path MyQuery.sql | Invoke-SimplySQL -Pipe MariaDb -Credential root
    #>
    [CmdletBinding(DefaultParameterSetName='Pipe')]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [string]
        $SQL

        , [Parameter(Mandatory)]
        [System.Management.Automation.Credential()]
        [System.Management.Automation.PSCredential]
        $Credential

        , [Parameter(Mandatory,ParameterSetName='Pipe')]
        [string]
        $Pipe

        , [Parameter(Mandatory,ParameterSetName='Server')]
        [string]
        $Hostname
        , [Parameter(Mandatory=$false,ParameterSetName='Server')]
        [int]
        $Port = 3306

        , [Parameter(Mandatory=$false)]
        [switch] $NonQuery
    )
    Begin {
        $ConnectionName = $ConnectionNames.Automatic
        "Using ConnectionName: $ConnectionName" | Write-Verbose
        $AggregatedSQL = @()
    }
    Process {
        $AggregatedSQL += $SQL
    }
    End {
        try {
            $OpenArguments = [hashtable]$PSBoundParameters
            $OpenArguments += @{ConnectionName = $ConnectionName}
            $OpenArguments.Remove('SQL')
            $OpenArguments.Remove('NonQuery')
            Open-SimplySQL @OpenArguments

            Get-SimplySQLResultset -SQL ($AggregatedSQL -join "`n") -NonQuery:$NonQuery -ConnectionName $ConnectionName
        } finally {
            Close-SimplySQL -ConnectionName $ConnectionName
        }
    }

#}
}
Function Read-Confluence {
#Function Read-Confluence {
    <#
        .SYNOPSIS
        "Private" Function: Reads the entire portfolio from confluence into PSCustomObjects processable by the Portfolio module
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject])]
    param(
        [Parameter(Mandatory)]
        [System.Management.Automation.Credential()]
        [System.Management.Automation.PSCredential]
        $Credential

        , [Parameter()]
            [switch]
            $SkipLabels

        , [Parameter(Mandatory=$false)]
            [string] $ConfluenceApiUri = 'https://eesc-cor.atlassian.net/wiki/rest/api'

        , [Parameter(Mandatory=$false)]
            [string] $CQL = "ancestor = 141754953"
            # [int] $PortfolioPageId = 141754953
    )
    Begin {
        # $Query = "ancestor = $PortfolioPageId and Id = 91750457"
        # $Query = "ancestor = $PortfolioPageId "
        $Query = $CQL
    }
    End {

        Get-ConfluencePage -Query $Query -ApiUri $ConfluenceApiUri -Credential $Credential | Foreach-Object {
            if( -not $SkipLabels ) {
                $Labels = Get-ConfluenceLabel -Id $_.ID -ApiUri $ConfluenceApiUri -Credential $Credential
            } else {
                $Labels = [ConfluencePS.ContentLabelSet]::new()
            }
            $_ | ConvertFrom-ConfluencePage -LabelSet $Labels | Expand-Artifact | Write-Output
        }
    }
#}
}
Function Test-Artifact {
#Function Test-Artifact {
    <#
        .SYNOPSIS
        "Public" Function: Test a Portfolio's PSCustomObject (e.g. a page) checking that it is a valid Portfolio Artifact
    #>
    [CmdletBinding()]
    [OutputType([bool])]
    param(
        [Parameter(Mandatory,ValueFromPipeline,Position=0)]
        [PSCustomObject]
        $Artifact
    )
    Process {
        $Artifact.ID -is [uint64]               -and
        $Artifact.ID -ge 1                      -and
        $null -ne $Artifact.Title               -and
        $Artifact.Title -ne [string]::Empty     -and
        $null -ne $Artifact.SpaceKey            -and
        $Artifact.SpaceKey -ne [string]::Empty  -and
        $null -ne $Artifact.Body                -and
        $Artifact.Body -ne [string]::Empty      -and
        $null -ne $Artifact.BodyXml
    }
#}
}
Function Write-Confluence {
#Function Write-Confluence {
    <#
        .SYNOPSIS
        "Private" Function: Write a portfolio PSCustomObject back into confluence
        The function is intended to save the object in it's original confluence page, as a new version, not to create a new page
 
        .PARAMETER StoreUnparsedBody
        If set, Write the content of $_.Body (HTML string) instead of $_.BodyXml (XDocument object)
    #>
    [CmdletBinding(SupportsShouldProcess = $true,ConfirmImpact="High")]
    [OutputType([PSCustomObject])]
    param(
        [Parameter(Mandatory=$true,ValueFromPipeline,Position=0)]
        [ValidateScript({$_ | Test-Artifact})]
        [pscustomobject] $Artifact

        , [Parameter()]
            [switch]
            $StoreUnparsedBody

        , [Parameter()]
            [switch]
            $SkipLabels

        , [Parameter()]
            [switch]
            $Force

        , [Parameter(Mandatory)]
            [System.Management.Automation.Credential()]
            [System.Management.Automation.PSCredential]
            $Credential

        , [Parameter(Mandatory=$false)]
            [string]
            $ConfluenceApiUri = 'https://eesc-cor.atlassian.net/wiki/rest/api'
    )
    Process {
        $Page = Get-ConfluencePage -ID $Artifact.ID -ApiUri $ConfluenceApiUri -Credential $Credential
        if( -not $Page ) {
            throw "Couldn't find a Confluence page with Id: $($Artifact.ID)"
        }
        if ((-not ($Force)) -and ($Page.Version.Number) -ne $Artifact.VersionNumber ) {
            throw "Confluence has a different version of the page with Id: $($Artifact.ID) (Ours: $($Artifact.VersionNumber), Confluence's: $($Page.Version.Number))"
        }

        try {
            if($StoreUnparsedBody) {
                if($pscmdlet.ShouldProcess("Overwrite confluence page $($Artifact.ID) with our unparsed HTML body")) {
                    Set-ConfluencePage -ID $Artifact.ID -ApiUri $ConfluenceApiUri -Credential $Credential -Title ($Artifact.Title) -Body ($Artifact.Body) -ErrorAction Stop
                }
            } else {
                if($pscmdlet.ShouldProcess("Overwrite confluence page $($Artifact.ID)")) {
                    Set-ConfluencePage -ID $Artifact.ID -ApiUri $ConfluenceApiUri -Credential $Credential -Title ($Artifact.Title) -Body ($Artifact.BodyXml | ConvertTo-ConfluencePageBody) -ErrorAction Stop
                }
            }
            if( -not $SkipLabels ) {
                if( $null -eq $Artifact.Labels -or $Artifact.Labels.Count -eq 0 ) {
                    if($pscmdlet.ShouldProcess("Remove all labels from confluence page $($Artifact.ID)")) {
                        Remove-ConfluenceLabel -ID $Artifact.ID -ApiUri $ConfluenceApiUri -Credential $Credential -ErrorAction Stop
                    }
                } else {
                    if($pscmdlet.ShouldProcess("Overwrite labels of confluence page $($Artifact.ID)")) {
                        Set-ConfluenceLabel -ID $Artifact.ID -ApiUri $ConfluenceApiUri -Credential $Credential -Label ($Artifact.Labels) -ErrorAction Stop
                    }
                }
            }
        } catch {
            throw $_
        }
    }
#}
}