MetaNullPortfolio

0.3.43.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)]
        [AllowEmptyString()]
        [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.Linq.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 | Where-Object { $_ -ne $null } | 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 {
<#
    .SYNOPSIS
    Converts a ConfluencePS.Page into an "Artifact" (a PSCustomObject understandable by the Portfolio module)
 
    .PARAMETER Page
    The ConfluencePS.Page object to convert
 
    .PARAMETER LabelSet
    The ConfluencePS.ContentLabelSet object to use as the LabelList of the Artifact
 
    .EXAMPLE
    # Get a ConfluencePS.Page object and convert it into an Artifact
    Get-ConfluencePage -Id 46564202 | ConvertFrom-ConfluencePage
#>
[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 {
<#
    .SYNOPSIS
    Expands an Artifact, issuing from ConvertFrom-ConfluencePage extracting the Artifact's specific data
 
    .PARAMETER Artifact
    The Artifact to expand
 
    .EXAMPLE
    # Get a ConfluencePS.Page object and convert it into an Artifact
    Get-ConfluencePage -Id 46564202 | ConvertFrom-ConfluencePage | Expand-Artifact
#>
[CmdletBinding()]
[OutputType([PSCustomObject])]
param(
    [Parameter(Mandatory,ValueFromPipeline,Position=0)]
    [PSCustomObject]
    $Artifact
)
Begin {

    Function Select-LinkListFromXmlValue {
        <#
            .SYNOPSIS
            Selects links matching a specific name within LinkList or VersionLinkList
 
            .PARAMETER LinkName
            The name of the link that have to be selected
 
            .PARAMETER Artifact
            The Artifact containing the list of links
 
            .PARAMETER UseVersionLinkList
            If set, the function will search in the VersionLinkList instead of the LinkList
 
        #>
        [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 {
<#
    .SYNOPSIS
        Stores an Artifact into the database
 
    .PARAMETER ConnectionName
        The connection name to use (See SimplySQL module's manual for more information)
 
    .PARAMETER Artifact
        The Artifact to store
 
    .EXAMPLE
        $MySqlConnection = @{
            ConnectionString = 'Server=.; Protocol=pipe; PipeName=MariaDb; Allow User Variables=true; SSLMode=Disabled'
            Credential = (Get-Credential -Message 'Provide the MariaDB user and password')
            ConnectionName = (New-GUID).ToString()
        }
        Open-MySqlConnection @MySqlConnection
        Read-Confluence | Export-Artifact -ConnectionName $ConnectionName
        Close-SqlConnection -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)
)
'@


    $DeleteAncestorStatement = @'
DELETE FROM portfolio.Ancestor WHERE Id = ?Id
'@
    $InsertAncestorStatement = @'
INSERT INTO portfolio.Ancestor (
    Id
, Ancestor
)
VALUES (
    ?Id
, ?Ancestor
)
'@
}
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 += Invoke-SqlUpdate -ConnectionName $ConnectionName -Query $DeleteAncestorStatement -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
        }
        "$LabelsInserted Label(s) 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
        }
        "$LinksInserted Link(s) inserted" | Write-Verbose

        # Store the artifact's AncestorList
        $AncestorInserted = 0
        $AncestorInserted += Invoke-SqlUpdate -ConnectionName $ConnectionName -Query $InsertAncestorStatement -Parameters @{
            Id = $Artifact.ID
            Ancestor = ($Artifact.AncestorList | ConvertTo-Json -Depth 10 -Compress)
        }
        "$AncestorInserted Ancestor Lists inserted" | Write-Verbose

        Complete-SqlTransaction -ConnectionName $ConnectionName
    } catch {
        Undo-SqlTransaction -ConnectionName $ConnectionName
        throw $_
    }
}
}
Function Import-Artifact {
<#
    .SYNOPSIS
        Load/Import specific artifacts from the Portfolio Database
 
    .PARAMETER ConnectionName
        The connection name to use (See SimplySQL module's manual for more information)
 
    .PARAMETER WithIdIn
        The list of Ids to load
 
    .EXAMPLE
        $MySqlConnection = @{
            ConnectionString = 'Server=.; Protocol=pipe; PipeName=MariaDb; Allow User Variables=true; SSLMode=Disabled'
            Credential = (Get-Credential -Message 'Provide the MariaDB user and password')
            ConnectionName = (New-GUID).ToString()
        }
        Open-MySqlConnection @MySqlConnection
        Import-Artifact -ConnectionName $ConnectionName
        Close-SqlConnection -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 {
<#
    .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 {
<#
    .SYNOPSIS
    Reads confluence into PSCustomObjects processable by the Portfolio module
 
    .EXAMPLE
    # Reads a specific page from Confluence using it's ID
    Read-Confluence -Credential $Credential -CQL "id = 46564202"
 
    .EXAMPLE
    # Reads all pages from Confluence in space ARC (without loading the labels)
    Read-Confluence -Credential $Credential -SkipLabels -CQL "SPACE = ARC"
 
    .EXAMPLE
    # Reads all pages tagged with 'product' under the ARC space
    Read-Confluence -Credential $Credential -SkipLabels -CQL "SPACE = ARC and label = 'product'"
     
    .EXAMPLE
    # Prints a table of all 'actors' in the ARC space
    Read-Confluence -Credential $Credential -CQL "SPACE = ARC and title ~ 'actor'" | Select-Object ID,ArtifactType,ArtifactName,Title | Format-Table
#>
[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 {
<#
    .SYNOPSIS
    Test a Portfolio's PSCustomObject (e.g. a page) checking that it is a valid Portfolio Artifact
 
    .PARAMETER Artifact
    The Portfolio's PSCustomObject to test
 
    .EXAMPLE
    Get-ConfluencePage -Id 46564202 | ConvertFrom-ConfluencePage | Expand-Artifact | Test-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 {
<#
    .SYNOPSIS
    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.
    In the event where a newer version of the page exists in confluence, the function will refuse to overwrite it unless the -Force parameter is set.
 
    .PARAMETER Artifact
    The Portfolio's PSCustomObject to write back into confluence
 
    .PARAMETER StoreUnparsedBody
    If set, Write the content of $_.Body (HTML string) instead of $_.BodyXml (XDocument object)
 
    .PARAMETER SkipLabels
    If set, don't write the labels of the page
 
    .PARAMETER Force
    If set, force the update of the page even if the version number doesn't match
 
    .PARAMETER Credential
    The credential to use to authenticate with Confluence
 
    .PARAMETER ConfluenceApiUri
    The URI of the Confluence API to use
 
    .EXAMPLE
    Read-Confluence -Credential $Credential -CQL "id = 46564202" | Write-Confluence -Credential $Credential -Force
#>
[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 $_
    }
}
}