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 a database trigger 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 trigger 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 a database trigger. #> function Hide-PdqNonDownloadedPackageUpdates { 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 trigger from your database. [Switch]$RemoveTrigger ) Write-Warning 'This function must be run after every PDQ Deploy update.' # Auto Download was introduced in 15.1.0.0. Assert-PdqMinimumVersion -Product 'Deploy' -MinimumVersion '15.1.0.0' $DatabasePath = Get-PdqDatabasePath -Product 'Deploy' -DatabasePath $DatabasePath $HideExistingUpdates = @' UPDATE LibraryPackages SET IsUpdated = 0 WHERE LibraryPackageId NOT IN ( SELECT LibraryPackageId FROM Packages INNER JOIN LibraryPackageVersions USING (LibraryPackageVersionId) INNER JOIN LibraryPackages USING (LibraryPackageId) ) ; '@ $TriggerName = 'hide_non_downloaded_package_updates' $TriggerQuery = @" CREATE TRIGGER $TriggerName BEFORE UPDATE OF IsUpdated ON LibraryPackages WHEN NEW.LibraryPackageId NOT IN ( SELECT LibraryPackageId FROM Packages INNER JOIN LibraryPackageVersions USING (LibraryPackageVersionId) INNER JOIN LibraryPackages USING (LibraryPackageId) ) BEGIN SELECT RAISE(IGNORE) ; END; "@ Try { Open-SQLiteConnection $DatabasePath $TriggerDeleted = Invoke-SqlUpdate -Query "DROP TRIGGER IF EXISTS $TriggerName" if ( $TriggerDeleted -eq 1 ) { Write-Verbose "Trigger $TriggerName was deleted." } if ( $RemoveTrigger ) { Exit } $UpdatesHidden = Invoke-SqlUpdate -Query $HideExistingUpdates Write-Verbose "$UpdatesHidden updates hidden." $TriggerCreated = Invoke-SqlUpdate -Query $TriggerQuery if ( $TriggerCreated -eq 1 ) { Write-Verbose "Trigger $TriggerName created." } else { throw "Unable to create trigger." } Write-Host '' Write-Host 'Success! Please refresh the PDQ Deploy console.' } Finally { Close-SqlConnection } } |