Public/Hide-PdqNonDownloadedPackageUpdates.ps1
|
<#
.SYNOPSIS Removes entries from the Updates tab for packages you haven't downloaded. .DESCRIPTION This function is for people who want the Updates tab of the Package Library to only show updates for packages they've downloaded. Current updates will be removed, and database triggers will be created to prevent future entries. The Updates tab uses the IsUpdated column of the LibraryPackages table. That's all that this function and the triggers it creates will edit. This function must be run after every PDQ Deploy update because updates delete triggers. .INPUTS None. .OUTPUTS None. .EXAMPLE Hide-PdqNonDownloadedPackageUpdates Removes current updates and creates database triggers. .EXAMPLE Hide-PdqNonDownloadedPackageUpdates -AllowNewPackages Removes current updates, creates a trigger to hide future updates, and does not create the trigger that hides new packages. #> function Hide-PdqNonDownloadedPackageUpdates { [CmdletBinding()] param ( # The path to the currently active database will be retrieved by default. # You can use this parameter if you wish to run this function against a different database. [String]$DatabasePath, # Deletes the triggers from your database. [Switch]$RemoveTrigger, # Disables the creation of the trigger that prevents new packages from appearing in the Updates tab. [Switch]$AllowNewPackages ) Write-Warning 'This function must be run after every PDQ Deploy update.' function New-PdqTrigger { [CmdletBinding()] param ( $TriggerTable ) $null = Invoke-SqlUpdate -Query $TriggerTable.Query -ConnectionName 'Deploy' Write-Verbose "Trigger $($TriggerTable.Name) created." } $HideExistingUpdates = @' UPDATE LibraryPackages SET IsUpdated = 0 WHERE IsUpdated = 1 AND LibraryPackageId NOT IN ( SELECT LibraryPackageId FROM Packages INNER JOIN LibraryPackageVersions USING (LibraryPackageVersionId) INNER JOIN LibraryPackages USING (LibraryPackageId) ) ; '@ $SharedBegin = @" BEGIN UPDATE LibraryPackages SET IsUpdated = 0 WHERE LibraryPackageId = NEW.LibraryPackageId ; END; "@ $Triggers = @{ 'Update' = @{ 'Name' = 'hide_non_downloaded_package_updates' } 'Insert' = @{ 'Name' = 'hide_new_package_updates' } } # I had to split these out so I could use the Name properties. $Triggers.Update.Query = @" CREATE TRIGGER $($Triggers.Update.Name) AFTER UPDATE OF IsUpdated ON LibraryPackages WHEN NEW.LibraryPackageId NOT IN ( SELECT LibraryPackageId FROM Packages INNER JOIN LibraryPackageVersions USING (LibraryPackageVersionId) INNER JOIN LibraryPackages USING (LibraryPackageId) ) $SharedBegin "@ $Triggers.Insert.Query = @" CREATE TRIGGER $($Triggers.Insert.Name) AFTER INSERT ON LibraryPackages $SharedBegin "@ Try { $CloseConnection = Open-PdqSqlConnection -Product 'Deploy' -DatabasePath $DatabasePath # Auto Download was introduced in 15.1.0.0. Assert-PdqMinimumVersion -Product 'Deploy' -MinimumVersion '15.1.0.0' foreach ( $Trigger in $Triggers.GetEnumerator() ) { $TriggerName = $Trigger.Value.Name $null = Invoke-SqlUpdate -Query "DROP TRIGGER IF EXISTS $TriggerName" -ConnectionName 'Deploy' Write-Verbose "Trigger $TriggerName was deleted, if it existed." } if ( $RemoveTrigger ) { Break } $UpdatesHidden = Invoke-SqlUpdate -Query $HideExistingUpdates -ConnectionName 'Deploy' Write-Verbose "$UpdatesHidden updates hidden." New-PdqTrigger -TriggerTable $Triggers.Update if ( -not $AllowNewPackages ) { New-PdqTrigger -TriggerTable $Triggers.Insert } Write-Host '' Write-Host 'Success! Please refresh the PDQ Deploy console.' } Finally { Close-PdqSqlConnection -Product 'Deploy' -CloseConnection $CloseConnection } } |