public/Update-ConfluenceSql.ps1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 |
<#
.SYNOPSIS Update a Confluence SQL database with data from a Confluence Cloud instance .DESCRIPTION Executes the necessary steps to update a SQL datastore of Confluence data from a Confluence Cloud instance A PowerConfluence Session must be opened with Open-ConfluenceSession before calling this method .PARAMETER SpaceKeys Supply this parameter in order to limit the refresh to a specified set of spaces. Without this parameter, the method will update all spaces .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 SyncOptions A hashtable of options to configure which data elements to synchronize .EXAMPLE Open-ConfluenceSession @ConfluenceConnectionDetails Update-Confluence -RefreshType (Get-ConfluenceRefreshTypes).Full -SqlInstance localhost -SqlDatabase Confluence Close-ConfluenceSession Performs a full refresh of all Confluence projects on a local sql database .EXAMPLE Open-ConfluenceSession @ConfluenceConnectionDetails $Params = @{ RefreshType = (Get-ConfluenceRefreshTypes).Differential SpaceKeys = @("PROJKEY","MJPK","KEY3") SqlInstance = "my.remote.sql.server,1234" SqlDatabase = "Confluence" } Update-Confluence @Params Close-ConfluenceSession Performs a differential refresh of 3 Confluence projects on a remote Sql Server #> function Update-ConfluenceSql { [CmdletBinding()] [OutputType([bool])] param ( # Keys of specific projects to pull [Parameter(Position=0)] [string[]] $SpaceKeys, # The sql instance to update data in [Parameter(Mandatory,Position=1)] [string] $SqlInstance, # The sql database to update data in [Parameter(Mandatory,Position=2)] [string] $SqlDatabase, # Synchronization options to override the defaults [Parameter()] [hashtable] $SyncOptions ) begin { } process { Write-Verbose "Beginning Confluence 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-DefaultConfluenceSyncOptions if ($SyncOptions) { $options = Merge-Hashtable -Source $SyncOptions -Target $options } #################################################### # REFRESH STEP 0 - CONFIGURE # #################################################### #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 # #################################################### $lastRefresh = Get-LastConfluenceRefresh @sqlSplat $lastRefreshStamp = $lastRefresh.Refresh_Start_Unix $lastRefreshDate = $lastRefresh.Refresh_Start #################################################### # BEGIN THE REFRESH BATCH # #################################################### Clear-ConfluenceStaging @sqlSplat $refreshId = Start-ConfluenceRefresh @sqlSplat } catch { Write-Verbose "Error while initiating the batch! Terminating refresh" $ErrorActionPreference = $originalErrorAction Write-Error $_ return $false } # define a convenient hash for splatting the basic refresh arguments $refreshSplat = @{ RefreshId = $refreshId } + $sqlSplat try { #################################################### # REFRESH STEP 1 - USERS / GROUPS # #################################################### if ($options.ContainsKey("Users") -and $options.Users -ne $false) { Update-ConfluenceUsers @refreshSplat } else { Write-Verbose "Skipping Users" } if ($options.ContainsKey("Groups") -and $options.Groups -ne $false) { $groups = Update-ConfluenceGroups @refreshSplat | ForEach-Object { $_.Group_Name } if ($options.Groups.ContainsKey("Users") -and $options.Groups.Users -ne $false) { Update-ConfluenceGroupsUsers $groups @refreshSplat } else { Write-Verbose "Skipping Groups' Users" } } else { Write-Verbose "Skipping Groups" } #################################################### # REFRESH STEP 2 - SPACES # #################################################### if ($options.ContainsKey("Spaces") -and $options.Spaces -ne $false) { $settings = IIF ($options.Spaces.GetType().Name -eq "Hashtable") $options.Spaces $null Update-ConfluenceSpaces $SpaceKeys $settings @refreshSplat } else { Write-Verbose "Skipping Users" } #################################################### # REFRESH STEP X - SYNC STAGING TO LIVE TABLES # #################################################### #perform the sync Sync-ConfluenceStaging @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-ConfluenceRefresh @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 "Confluence update completed successfully!" return $true } else { Write-Verbose "Confluence 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 {} } |