ConfluenceIngest

1.1.4.1

ConfluenceIngest.psm1

#Requires -Module SimplySQL
#Requires -Module ConfluencePS

# Module Constants

# GUID used to identify the connection names by SimplySQL
Set-Variable ConnectionNames -option Constant -value (@{
    Automatic = (New-Guid)
    Manual = (New-Guid)
})
Function Close-SimplySQL {
<#
    .SYNOPSIS
        Closes a SimplySQL connection, by its name
 
    .PARAMETER ConnectionName
        A name to identify this Connection (default: a GUID created automatically when the Module is laoded in memory)
 
    .EXAMPLE
        # Connect to a database using a Windows Named Pipe, prompting the user for credentials
        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 {
<#
    .SYNOPSIS
    Converts a "Body" String from for ConfluencePS' Page functions into a XDocument object
#>
[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)
    [System.Xml.Linq.XDocument]::Parse($XmlBody) | Write-Output
}
}
Function ConvertTo-ConfluencePageBody {
<#
    .SYNOPSIS
    Converts a XDocument object 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 '/*[1]' | Select-Object -ExpandProperty Node | Select-Object -ExpandProperty InnerXml | Write-Output
}
}
Function Get-SimplySQLResultset {
<#
    .SYNOPSIS
        Executes a SQL query using SimplySQL, returns a resultset if possible
 
    .PARAMETER SQL
        The SQL query to execute
 
    .PARAMETER NoResultset
        If set, the query is not expected to return a resultset (e.g. an UPDATE or DELETE statement)
 
    .PARAMETER ConnectionName
        A name to identify this Connection (default: a GUID created automatically when the Module is laoded in memory)
 
    .EXAMPLE
        # Run a select query 'Select 1 as one from dual'
        Open-SimplySQL -Pipe MariaDb -Credential root
        Get-SimplySQLResultset -Sql 'SELECT 1'
        Close-SimplySQL
 
    .EXAMPLE
        # Run a query stored in a file
        Open-SimplySQL -Pipe MariaDb -Credential root
        Get-Content -Raw C:\query.sql | Get-SimplySQLResultset
        Close-SimplySQL
 
    .EXAMPLE
        # Run a query that doesn't return a result set: 'delete from a where 1 = 0'
        Open-SimplySQL -Pipe MariaDb -Credential root
        Get-SimplySQLResultset -NonQuery -Sql 'delete from a where 1 = 0'
        Close-SimplySQL
#>
[CmdletBinding()]
param(
    [Parameter(Mandatory,ValueFromPipeline)]
    [string]
    $SQL

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

    , [Parameter(Mandatory=$false)]
    [string]
    $ConnectionName = $ConnectionNames.Manual
)
Process {
    "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 $SQL
    } else {
        Invoke-SqlQuery -ConnectionName ($ConnectionName) -Query $SQL
    }
}
}
Function Invoke-MariaDBClient {
<#
    .SYNOPSIS
        Invoke the mariadb client (mariadb.exe), and execute some SQL query
 
    .PARAMETER SQL
        The SQL query to execute
 
    .PARAMETER Credential
        The credential to use to connect to the database
 
    .PARAMETER Pipe
        The named pipe to use to connect to the database
     
    .PARAMETER Hostname
        The hostname to use to connect to the database
 
    .PARAMETER Port
        The port to use to connect to the database (3306 by default)
 
    .PARAMETER MariaDb
        The path to the mariadb client executable (default: 'c:\Program Files\MariaDB 11.4\bin\mariadb.exe')
 
    .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 {
        Set-Content -Path $ExtraConfig -Value '[client]'
        Add-Content -Path $ExtraConfig -Value "user=$($Credential.GetNetworkCredential().UserName)"
        Add-Content -Path $ExtraConfig -Value "password=$($Credential.GetNetworkCredential().Password)"
        if($Pipe) {
            Add-Content -Path $ExtraConfig -Value 'pipe'
            Add-Content -Path $ExtraConfig -Value 'protocol=pipe'
            Add-Content -Path $ExtraConfig -Value "socket=$($Pipe)"
        } elseif($Hostname) {
            Add-Content -Path $ExtraConfig -Value 'protocol=tcp'
            Add-Content -Path $ExtraConfig -Value "host=$($Hostname)"
            Add-Content -Path $ExtraConfig -Value "port=$($Port)"
        }

        $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 {
<#
    .SYNOPSIS
        Open a connection with a database using SimplySQL
 
    .PARAMETER Credential
        The credential to use to connect to the database
 
    .PARAMETER ConnectionString
        The connection string to use to connect to the database
 
    .PARAMETER Pipe
        The pipe to use to connect to the database
 
    .PARAMETER Hostname
        The hostname to use to connect to the database
 
    .PARAMETER Port
        The port to use to connect to the database (3306 by default)
 
    .PARAMETER ConnectionName
        A name to identify this Connection (default: a GUID created automatically when the Module is laoded in memory)
 
    .EXAMPLE
        # Connect to a database using a Windows Named Pipe, prompting the user for credentials
        Open-SimplySQL -Pipe MariaDb -Credential root
        Get-SimplySQLResultset -Sql 'SELECT 1'
        Close-SimplySQL
 
    .EXAMPLE
        # Connect to a database using a TCP/IP connection, prompting the user for credentials
        Open-SimplySQL -Server localhost -Port 3306 -Credential root
        Get-SimplySQLResultset -Sql 'SELECT 1'
        Close-SimplySQL
 
    .EXAMPLE
        # Connect to a database using a ConnectionString, using TCP/IP, and prompting the user for credentials
        Open-SimplySQL -ConnectionString 'Server=127.0.0.1; Port=3306; Allow User Variables=true; SSLMode=Disabled' -Credential root
        Get-SimplySQLResultset -Sql 'SELECT 1'
        Close-SimplySQL
 
    .EXAMPLE
        # Connect to a database using a ConnectionString, using Named Pipes, and prompting the user for credentials
        Open-SimplySQL -ConnectionString 'Server=.; Protocol=pipe; PipeName=MariaDB; Allow User Variables=true; SSLMode=Disabled' -Credential root
        Get-SimplySQLResultset -Sql 'SELECT 1'
        Close-SimplySQL
#>
[CmdletBinding(DefaultParameterSetName='Server')]
param(
    [Parameter(Mandatory)]
    [System.Management.Automation.Credential()]
    [System.Management.Automation.PSCredential]
    $Credential

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

    , [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)
    } elseif($ConnectionString) {
        Open-MySqlConnection -ConnectionName ($ConnectionName) -ConnectionString ($ConnectionString) -Credential ($Credential)
    } else {
        throw "Either a pipe, server or ConnectionString must be specified"
    }

    if(-not (Test-SqlConnection -ConnectionName ($ConnectionName))) {
        throw "Connection: $ConnectionName. Connection failed"
    }
}
}
Function Repair-HtmlEntities {
<#
    .SYNOPSIS
        This function will convert all Named HTML entities into XML numeric entities
 
        It helps dealing with Confluence documents, as Confluence prefers Named HTML entities, that are not readily
        available/defined in xml, and therefore not understood by [System.Xml.Linq.XDocument]
 
    .PARAMETER Html
        The HTML string to convert
 
    .EXAMPLE
        'This is a &nbsp; test' | Repair-HtmlEntities
        # This is a &#160; test
#>
[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 {
<#
    .SYNOPSIS
    Extract "Confluence Excerpt" macros from a XDocument or Node
 
    .PARAMETER Xml
        The XDocument or Node to extract the Excerpt macros from
#>
[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 {
<#
    .SYNOPSIS
    Extract "Confluence Image" macros from a XDocument or Node
 
    .PARAMETER Xml
        The XDocument or Node to extract the Image macros from
#>
[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 {
<#
    .SYNOPSIS
    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 {
<#
    .SYNOPSIS
    Extract "Confluence Page Link" macros from a XDocument or Node
 
    .PARAMETER Xml
        The XDocument or Node to extract the Page Link macros from
#>
[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 {
<#
    .SYNOPSIS
        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}}
 
    .PARAMETER Xml
        The XDocument or Node to extract the Page Link macros from
#>
[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 {
<#
    .SYNOPSIS
        Extract "Confluence PageProperties" macros from a XDocument or Node
 
    .PARAMETER Xml
        The XDocument or Node to extract the PageProperties macros from
#>
[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 {
<#
    .SYNOPSIS
    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 {
<#
    .SYNOPSIS
        Extract text from a XDocument or Node, including from special XML elements like "Confluence Status" and "Confluence Jira" macros
    .PARAMETER ValueXml
        The XDocument or Node to extract the text from
#>
[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 {
<#
.SYNOPSIS
    Produce [version] objects out of [string] representations.
    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 acenstor's list
        $InsertAncestorParameters = @{
            Id = $Artifact.ID
            Ancestor = $Artifact | Select-Object -Property $AncestorList | ConvertTo-Json -Compress -Depth 10
        }
        $AncestorsInserted = Invoke-SqlUpdate -ConnectionName $ConnectionName -Query $InsertAncestorStatement -Parameters $InsertAncestorParameters | Out-Null
        "$AncestorsInserted Ancestors 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 {
<#
    .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 $_
    }
}
}