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 {}
}