functions/public/Update-PSWorkItemDatabase.ps1

<#
update the PSWorkItemArchive table to add the Original ID column to support
#Issue #7
https://github.com/jdhitsolutions/PSWorkItem/issues/7
#>


Function Update-PSWorkItemDatabase {
    [cmdletbinding(SupportsShouldProcess)]
    [OutputType('none','MySQLiteTableDetail')]
    [alias('alias')]
    Param(
        [Parameter(
            Position = 0,
            HelpMessage = "The path to the PSWorkItem SQLite database file. It should end in .db")]
        [ValidatePattern("\.db$")]
        [ValidateScript(
            {
                $parent = Split-Path -Path $_ -Parent
                Test-Path $parent
            },
            ErrorMessage = "Failed to validate the parent path."
        )]
        [String]$Path = $PSWorkItemPath,
        [Switch]$PassThru
    )

    Begin {
        StartTimer
        $PSDefaultParameterValues["_verbose:Command"] = $MyInvocation.MyCommand
        $PSDefaultParameterValues["_verbose:block"] = "Begin"
        _verbose -message $strings.Starting
        _verbose -message ($strings.PSVersion -f $PSVersionTable.PSVersion)
        _verbose -message ($strings.UsingModule -f (Get-Command -name $MyInvocation.MyCommand).Version)
        _verbose -message ($strings.UsingDB -f $path)

        $dbConnection = Open-MySQLiteDB -Path $Path
        $splat = @{
            Connection = $dbConnection
            Query = ""
            KeepAlive = $True
        }
    } #begin

    Process {
        $PSDefaultParameterValues["_verbose:block"] = "Process"
        _verbose -message ($strings.UsingDB -f $Path)
        #UPDATE ARCHIVE TABLE
        #test for column existence
        _verbose -message ($strings.TestColumnID)
        $splat.Query = "pragma table_info('archive')"
        $test = Invoke-MySQLiteQuery @splat | Where-Object name -eq 'ID'
        if ($test) {
            Write-Warning $strings.IDColumnExists
        }
        else {
            _verbose -message $strings.AddIDColumn
            #append the new column
            #It is impossible to set a value for the ID column in the archive table
            #since there is no way of knowing what the original ID was. Set the ID to 0.
            If ($PSCmdlet.ShouldProcess("table archive","Adding column ID")) {
                $splat.query = "ALTER TABLE archive ADD id integer;"
                Invoke-MySQLiteQuery @splat

                _verbose -message $strings.UpdateArchiveTable
                $splat.query = "Select taskId,RowID from archive"
                $items = Invoke-MySQLiteQuery @splat
                Foreach ($item in $items) {
                    $splat.query = "UPDATE archive set id = '0' Where taskid='{0}'" -f $item.taskid
                    _verbose -message $splat.query
                    Invoke-MySQLiteQuery @splat
                }
            } #WhatIf
        }
        #UPDATE TASKS TABLE
        #test for column existence
        _verbose -message $strings.TestingColumnID
        $splat.query = "pragma table_info('tasks')"
        $test = Invoke-MySQLiteQuery @splat | Where-Object name -eq 'ID'
        if ($test) {
            Write-Warning $strings.IDColumnExists
        }
        else {
            _verbose -message $strings.AddIDColumn
            If ($PSCmdlet.ShouldProcess("table tasks","Adding column ID")) {
                $splat.query = "ALTER TABLE tasks ADD id integer;"
                Invoke-MySQLiteQuery @splat

                #Update ID column with RowID
                _verbose -message $strings.UpdateTaskTable
                $splat.query = "Select taskId,RowID from tasks"
                $items = Invoke-MySQLiteQuery @splat
                Foreach ($item in $items) {
                    $splat.query = "UPDATE tasks set id = '{0}' Where taskid='{1}'" -f $item.rowid,$item.taskid
                    _verbose -message $splat.query
                    Invoke-MySQLiteQuery @splat
                }
            } #WhatIf
        }
    } #process

    End {
        $PSDefaultParameterValues["_verbose:block"] = "End"
        $PSDefaultParameterValues["_verbose:Command"] = $MyInvocation.MyCommand
        If ($PassThru -AND (-Not $WhatIfPreference)) {
            Get-MySQLiteTable -Connection $dbConnection -KeepAlive -Detail | Where-Object table -eq archive
        }
        If ($PassThru -AND (-Not $WhatIfPreference)) {
            Get-MySQLiteTable -Connection $dbConnection -Detail | Where-Object table -eq archive
        }
        _verbose -message $strings.CloseDBConnection
        Close-MySQLiteDB -Connection $dbConnection
        _verbose -message $strings.Ending
        _verbose -message ($strings.RunTime -f (StopTimer))
    } #end

} #close Update-PSWorkItemDatabase