public/Update-JiraSql.ps1
|
<#
.SYNOPSIS Update a Jira SQL database with data from a Jira Cloud instance .DESCRIPTION Executes the necessary steps to update a SQL datastore of Jira data from a Jira Cloud instance A PowerJira Session must be opened with Open-JiraSession before calling this method .PARAMETER RefreshType Indicates whether to do a Full or Differential refresh. Use values from Get-JiraRefreshTypes .PARAMETER ProjectKeys Supply this parameter in order to limit the refresh to a specified set of projects. Without this parameter, the method will update all projects .PARAMETER Obfuscate Supply this parameter to obfuscate issue information in sensitive projects Issue Summary and Description will be replaced the with the string [Obfuscated] .PARAMETER SqlInstance The connection string for the SQL instance where the data will be updated .PARAMETER SqlDatabase The name of the database to perform updates in .PARAMETER SyncDeployments Use this switch to sync deployment information from third-party providers. https://developer.atlassian.com/cloud/jira/software/modules/deployment/ .EXAMPLE Open-JiraSession @jiraConnectionDetails Update-Jira -RefreshType (Get-JiraRefreshTypes).Full -SqlInstance localhost -SqlDatabase Jira Close-JiraSession Performs a full refresh of all Jira projects on a local sql database .EXAMPLE Open-JiraSession @jiraConnectionDetails $Params = @{ RefreshType = (Get-JiraRefreshTypes).Differential ProjectKeys = @("PROJKEY","MJPK","KEY3") SqlInstance = "my.remote.sql.server,1234" SqlDatabase = "Jira" } Update-Jira @Params Close-JiraSession Performs a differential refresh of 3 Jira projects on a remote Sql Server #> function Update-JiraSql { [CmdletBinding()] param ( # Refresh type [Parameter(Mandatory, Position=0)] [ValidateSet("F","D")] [string] $RefreshType, # Keys of specific projects to pull [Parameter(Position=1)] [string[]] $ProjectKeys, # Keys of projects to be obfuscated [Parameter(Position=2)] [string[]] $Obfuscate, # The sql instance to update data in [Parameter(Mandatory,Position=3)] [string] $SqlInstance, # The sql database to update data in [Parameter(Mandatory,Position=4)] [string] $SqlDatabase, # Synchronization options to override the defaults [Parameter()] [hashtable] $SyncOptions ) begin {} process { Write-Verbose "Beginning Jira data update on $SqlInstance in database $SqlDatabase" #################################################### # CONFIGURATION # #################################################### #set the cmdlet to stop on error, but remember the original setting so we can put it back later $originalErrorAction = $ErrorActionPreference $ErrorActionPreference = "Stop" #set up some values to track occurance of errors $refreshSuccess = $true $refreshError = $null #configure the database targets $sqlSplat = @{ SqlInstance = $SqlInstance SqlDatabase = $SqlDatabase } #setup the sync options $options = Get-DefaultJiraSyncOptions if ($SyncOptions) { $options = Merge-Hashtable -Source $SyncOptions -Target $options } #test the sql connection if (!(Test-SqlConnection $SqlInstance $SqlDatabase)) { Write-Verbose "Error while validating database connection! Terminating refresh" $ErrorActionPreference = $originalErrorAction return $false } try { #################################################### # GET PREVIOUS BATCH INFO / CLEAR PREVIOUS BATCH # #################################################### $RefreshTypes = Get-JiraRefreshTypes if ($RefreshType -eq $RefreshTypes.Full) { Clear-JiraRefresh @sqlSplat $lastRefreshStamp = 0 $lastRefreshDate = (Get-Date '1970-01-01') } else { $lastRefresh = Get-LastJiraRefresh @sqlSplat $lastRefreshStamp = $lastRefresh.Refresh_Start_Unix $lastRefreshDate = $lastRefresh.Refresh_Start } #################################################### # BEGIN THE REFRESH BATCH # #################################################### Clear-JiraStaging @sqlSplat $refreshId = Start-JiraRefresh -RefreshType $RefreshType @sqlSplat } catch { Write-Verbose "Error while initiating the batch! Terminating refresh" $ErrorActionPreference = $originalErrorAction Write-Error $_ return $false } #################################################### # REFRESH STEP 0 - CONFIGURE # #################################################### # define a convenient hash for splatting the basic refresh arguments $refreshSplat = @{ RefreshId = $refreshId } + $sqlSplat try { #################################################### # REFRESH STEP 1 - NO CONTEXT DATA # #################################################### # these are mostly lookup tables if ($options.ProjectCategories) { Update-JiraProjectCategories @refreshSplat } else { Write-Verbose "Skipping Project Categories" } if ($options.StatusCategories) { Update-JiraStatusCategories @refreshSplat } else { Write-Verbose "Skipping Status Categories" } if ($options.Statuses) { Update-JiraStatuses @refreshSplat } else { Write-Verbose "Skipping Statuses" } if ($options.Resolutions) { Update-JiraResolutions @refreshSplat } else { Write-Verbose "Skipping Resolutions" } if ($options.Priorities) { Update-JiraPriorities @refreshSplat } else { Write-Verbose "Skipping Priorities" } if ($options.IssueLinkTypes) { Update-JiraIssueLinkTypes @refreshSplat } else { Write-Verbose "Skipping Issue Link Types" } if ($options.Users) { Update-JiraUsers @refreshSplat } else { Write-Verbose "Skipping Users" } #################################################### # REFRESH STEP 2 - PROJECTS # #################################################### if ($options.ContainsKey("Projects") -and $options.Projects -ne $false) { # update projects, and in the process get a full project key list if necessary $refreshProjectKeys = Update-JiraProjects -ProjectKeys $ProjectKeys @refreshSplat | ForEach-Object { $_.Project_Key } } else { $refreshProjectKeys = $ProjectKeys Write-Verbose "Skipping Projects" } #################################################### # REFRESH STEP 3 - PROJECT DETAILS # #################################################### if ($options.ContainsKey("Projects") -and $options.Projects.GetType().Name -eq "Hashtable") { # next do the updates where the only context is the list of projects if ($options.Projects.ContainsKey("Versions") -and $options.Projects.Versions -ne $false) { Update-JiraVersions -ProjectKeys $refreshProjectKeys @refreshSplat } else { Write-Verbose "Skipping Project Versions" } if ($options.Projects.ContainsKey("Components") -and $options.Projects.Components -ne $false) { Update-JiraComponents -ProjectKeys $refreshProjectKeys @refreshSplat } else { Write-Verbose "Skipping Project Components" } if ($options.Projects.ContainsKey("Actors") -and $options.Projects.Actors -ne $false) { Update-JiraProjectActors -ProjectKeys $refreshProjectKeys @refreshSplat } else { Write-Verbose "Skipping Project Actors" } } else { Write-Verbose "Skipping Project Details" } #################################################### # REFRESH STEP 4 - WORKLOGS # #################################################### if ($options.Worklogs) { # worklogs are refreshed based on the last unix timestamp of a refresh # need to both update changed / new worklogs, and remove any that have been deleted Update-JiraWorklogs -LastRefreshUnix $lastRefreshStamp @refreshSplat Remove-JiraWorklogs -LastRefreshUnix $lastRefreshStamp @sqlSplat } else { Write-Verbose "Skipping Worklogs" } #################################################### # REFRESH STEP 5 - ISSUES # #################################################### if ($options.ContainsKey("Issues") -and $options.Issues -ne $false) { # issues are retrieved using jql crafted from the date of last refresh and optionally a project key list # first format the date stamp and create the updated date clause $jqlUpdateDate = (Get-Date $lastRefreshDate -format "yyyy-MM-dd HH:mm") $updateJql = "updatedDate >= '$jqlUpdateDate'" # if we're refreshing a specific list of projects, create the clause; otherwise, don't add a project clause $projectJql = if($null -eq $ProjectKeys -or $ProjectKeys.Count -eq 0) { "" } else { " AND Project in (" + ($refreshProjectKeys -join ",") + ")" } # update issues with the crafted JQL $updateIssueParams = @{ Jql = $updateJql + $projectJql Obfuscate = $Obfuscate } if ($options.Issues.GetType().Name -eq "Hashtable") { $updateIssueParams.Add("SyncOptions",$options.Issues) } $updateIssueParams += $refreshSplat Update-JiraIssues @updateIssueParams #if we're doing a diff refresh, pull down ALL issue IDs for the listed projects, in order to detect deleted issues $deleteRetrieveSuccess = $false if ($RefreshType -eq (Get-JiraRefreshTypes).Differential) { # use the project list if we're doing a list, otherwise use a "true = true" type clause to get everything $deleteRetrieveSuccess = if ($null -eq $ProjectKeys) { Update-JiraDeletedIssues -Jql "project is not EMPTY" @sqlSplat } else { Update-JiraDeletedIssues -Jql $projectJql @sqlSplat } } } else { Write-Verbose "Skipping Issues" } #################################################### # REFRESH STEP 6 - SYNC STAGING TO LIVE TABLES # #################################################### #determine if deletes should be synced $syncDelete = $deleteRetrieveSuccess -and ($RefreshType -eq $RefreshTypes.Differential) #perform the sync Sync-JiraStaging -SyncDeleted $syncDelete @sqlSplat } catch { #################################################### # HANDLE ERROR OCCURING DURING REFRESH # #################################################### Write-Verbose "Error while executing the batch! Terminating refresh" $refreshSuccess = $false $refreshError = $_ } #################################################### # RECORD BATCH END # #################################################### $batchTermError = $null try { Stop-JiraRefresh @refreshSplat -Success $refreshSuccess } catch { Write-Verbose "Error while recording batch end! Exiting without recording" $refreshSuccess = $false $batchTermError = $_ } #################################################### # RETURN SUCCESS / FAILURE, OUTPUT ERRORS # #################################################### $ErrorActionPreference = $originalErrorAction if ($refreshSuccess) { Write-Verbose "Jira update completed successfully!" return $true } else { Write-Verbose "Jira updated completed with errors :(" if ($refreshError) { Write-Verbose "Terminating Error:" Write-Error $refreshError } if ($batchTermError) { Write-Verbose "Batch End Recording Error:" Write-Error $batchTermError } return $false } } end {} } |