PowerShell.PowerLibrary.IncrementalUpdate.psm1
|
<#
IncrementalUpdate Module #> #region Functions FUNCTION Get-DynamicParams { <# .Synopsis Generate dynamic parameters .DESCRIPTION Generate dynamically the provider parameters that are specified in param section. Filters the parameters by removing "CommonParameters". Generate AttributeCollection by iterating on parameter attributes . It return [System.Management.Automation.RuntimeDefinedParameterDictionary]. .PARAMETER ParameterDictionary Dictionary contains all parameters for specified provider #> [CmdletBinding()] param ( [Parameter(Mandatory=$true, ValueFromPipeline=$true)] $ParameterDictionary ) begin { $CommonParameterNames = [System.Runtime.Serialization.FormatterServices]::GetUninitializedObject([type] [System.Management.Automation.Internal.CommonParameters]) | ` Get-Member -MemberType Properties | ` Select-Object -ExpandProperty Name; # Param attributes will be copied later. You basically have to create a blank attrib, then change the # properties. Knowing the writable ones up front helps: $WritableParamAttributePropertyNames = [System.Management.Automation.ParameterAttribute]::new() | ` Get-Member -MemberType Property | ` Where-Object { $_.Definition -match "{.*set;.*}$" } | ` Select-Object -ExpandProperty Name; } process { # Convert to object array and get rid of Common params and (Parameters hashtable): $Parameters = $ParameterDictionary.GetEnumerator() | Where-Object ` { ($CommonParameterNames -notcontains $_.Key) -and ($_.Key -ne "Parameters") }; # Create the Runtime Param dictionary $DynParamDictionary = [System.Management.Automation.RuntimeDefinedParameterDictionary]::new(); FOREACH ($Parameter in $Parameters) { $AttributeCollection = [System.Collections.ObjectModel.Collection[System.Attribute]]::new(); $ParameterType = $Parameter.Value.ParameterType; FOREACH ($CurrentAttribute in $Parameter.Value.Attributes) { $AttributeTypeName = $CurrentAttribute.TypeId.FullName; switch -wildcard ($AttributeTypeName) { System.Management.Automation.ArgumentTypeConverterAttribute { continue; # So blank param doesn't get added } System.Management.Automation.Validate*Attribute { $NewParamAttribute = $CurrentAttribute; $AttributeCollection.Add($NewParamAttribute); } System.Management.Automation.ParameterAttribute { $NewParamAttribute = [System.Management.Automation.ParameterAttribute]::new(); FOREACH ($PropName in $WritableParamAttributePropertyNames) { IF ($NewParamAttribute.$PropName -ne $CurrentAttribute.$PropName) { # nulls cause an error if you assign them to some of the properties $NewParamAttribute.$PropName = $CurrentAttribute.$PropName; } } $NewParamAttribute.ParameterSetName = $CurrentAttribute.ParameterSetName; $AttributeCollection.Add($NewParamAttribute); } default { Write-Warning "Function doesn't handle dynamic param copying for $AttributeTypeName"; continue; } } } $DynamicParameter = [System.Management.Automation.RuntimeDefinedParameter]::new( $Parameter.Key, $ParameterType, $AttributeCollection ); Write-Verbose "Added Parameter $($Parameter.Key)"; $DynParamDictionary.Add($Parameter.Key, $DynamicParameter); } Write-Verbose "Successfully created dynamic parameter dictionary"; # Return the dynamic parameters RETURN $DynParamDictionary; } } FUNCTION Get-Provider { <# .Synopsis Get full path of the provider .DESCRIPTION Get full path of the provider by specifing "ProviderType" only or "ProviderType" and "Custom". - ProviderType Only : "Database" or "FileSystem" - ProviderType and Custom : ProviderType as "Custom" and Custom contain the full path to the custom provider. .PARAMETER ProviderType Specify the provider type that will be used for logging registry. "Database", "FileSystem" or "Custom" .PARAMETER Custom Specify full path of the custom provider. This is used with ProviderType = "Custom" .NOTES Convenstion Used for defined providers : Providers are found under "Providers" folder in module root directory. In case selected provider not found, Database provider will be used. #> Param ( [Parameter(Mandatory=$true)] [string] $ProviderType, [Parameter(Mandatory=$false)] [string] $Custom ) Write-Verbose "Getting provider : $ProviderType"; IF($ProviderType -eq "Custom") { IF($Custom -eq $null) { Throw "Custom param is needed !"; } IF(-not ([System.IO.File]::Exists($Custom))) { Throw "Custom Path is needed ! Make sure it's full path"; } RETURN $Custom; } ELSE { $BaseDirectory = "$PSScriptRoot\Providers\"; $Provider = Get-ChildItem -Path $BaseDirectory -Recurse -Filter "$($ProviderType)Provider.ps1"; IF($Provider -eq $null -or $Provider.Length -eq 0) { Write-Host "$ProviderType does not exist."; Write-Host "Database Provider will be used."; $Provider = Get-ChildItem -Path $BaseDirectory -Recurse -Filter "DatabaseProvider.ps1"; } RETURN $Provider.FullName; } } FUNCTION Validate-File { <# .Synopsis Validate template file .DESCRIPTION Validating template file if changed. .PARAMETER SourceHash Hash generated by Get-FileHash .PARAMETER Source Source is the source of template. (part of the path) .PARAMETER RegistryList List of registry returned from Get-Registry .NOTES It return valid in case there is no registry logged. #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [object] $SourceHash, [Parameter(Mandatory=$true)] [string] $Source, [Array] $RegistryList ) $IsValid = $true; Write-Verbose "Validating file if changed"; IF($RegistryList -eq $null) { RETURN $IsValid; } FOREACH($Item in $RegistryList) { IF(($Item.Hash -eq $SourceHash.Hash) -and ($Item.Source -eq $Source)) { $IsValid = $false; break; } } RETURN $IsValid; } FUNCTION Write-Note { [CmdletBinding()] param ( [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [String] $PreMessage, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [String] $Message, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [String] $PostMessage, [ValidateSet('Black', 'DarkBlue', 'DarkGreen', 'DarkCyan', 'DarkRed', 'DarkMagenta', 'DarkYellow', 'Gray', 'DarkGray', 'Blue', 'Green', 'Cyan', 'Red', 'Magenta', 'Yellow', 'White')] [ConsoleColor] $ForegroundColor, [ValidateSet('Black', 'DarkBlue', 'DarkGreen', 'DarkCyan', 'DarkRed', 'DarkMagenta', 'DarkYellow', 'Gray', 'DarkGray', 'Blue', 'Green', 'Cyan', 'Red', 'Magenta', 'Yellow', 'White')] [ConsoleColor] $MessageForegroundColor, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [switch] $NoNewline = $false ) Write-Host $PreMessage -ForegroundColor $ForegroundColor -NoNewline; Write-Host $Message -ForegroundColor $MessageForegroundColor -NoNewline; Write-Host $PostMessage -ForegroundColor $ForegroundColor; Write-Host -NoNewline:$NoNewline; } FUNCTION Start-DatabaseMaintenance { <# .Synopsis Execute incremental sql scripts .DESCRIPTION Execute Incremental SQL scripts while setting up registry based on the specified provider. Registry will be used to skip SQL scripts that are already executed. .PARAMETER SqlQueryTimeout Specifies Invoke-Sqlcmd QueryTimeout .PARAMETER SQLScriptsPathes Specifies list of script pathes that contain the sql scripts. There is a convention to be followed inorder to use this module correctly and execute scripts in order : - SQLScriptsPathes should contain the path for the main folder that contains the patches ex: C:\Example\PaTcheS\R4 - Folders inside a path of SQLScriptsPathes are named with date as "20181217" ex: C:\Example\PaTcheS\R4\20181217 - Folders inside "20181217" should contain at least one folder with index "0000" ex: C:\Example\PaTcheS\R4\20181217\0000 - SQL template must end with ".template" as extension .PARAMETER SQLServerName Specifies SQL server name. .PARAMETER SQLServerUserName Specifies SQL server username. .PARAMETER SQLServerPassword Specifies SQL server password. .PARAMETER TemplateModel Specifies the path of binding model. .PARAMETER ModelName Specifies the model name used for binding. .PARAMETER ProviderType Specifies the provider type that will be used for logging the execution. .PARAMETER Custom Specifies the path of the custom provider. This is used when you specify "ProviderType" as Custom. .PARAMETER DatabaseName This is required when specifing ProviderType as "Database". DatabaseName : specifies the database of the registry. .PARAMETER OutputPath This is required when specifing ProviderType as "FileSystem". OutputPath : specifies the full path of registry JSON file. ex: C:\Example\XXX.json .EXAMPLE ProviderType = Database Start-DatabaseMaintenance ` -SQLScriptsPathes @("C:\Example\PaTcheS\R4") ` -SQLServerName . ` -SQLServerUserName sa ` -SQLServerPassword password ` -TemplateModel "C:\Example\XXX.json" ` -ProviderType Database ` -DatabaseName Saico_Portal ` -ModelName "Model"; .EXAMPLE ProviderType = FileSystem Start-DatabaseMaintenance ` -SQLScriptsPathes @("C:\Example\PaTcheS\R4") ` -SQLServerName . ` -SQLServerUserName sa ` -SQLServerPassword password ` -TemplateModel "C:\Example\XXX.json" ` -ProviderType FileSystem ` -OutputPath "C:\Example\XXX.json" ` -ModelName "Model"; .EXAMPLE ProviderType = Custom Start-DatabaseMaintenance ` -SQLScriptsPathes @("C:\Example\PaTcheS\R4") ` -SQLServerName . ` -SQLServerUserName sa ` -SQLServerPassword password ` -TemplateModel "C:\Example\XXX.json" ` -ProviderType Custom ` -Custom "C:\Example\XXXProvider.ps1" ` -ModelName "Model"; .NOTES Convenstion to be followed : Additional parameters will be requested at runtime based on specified provider. There is a convention to be followed inorder to use this module correctly : - SQLScriptsPathes should contain the path for the main folder that contains the patches ex: C:\Example\PaTcheS\R4 - Folders inside a path of SQLScriptsPathes are named with date as "20181217" ex: C:\Example\PaTcheS\R4\20181217 - Folders inside "20181217" should contain at least one folder with index "0000" ex: C:\Example\PaTcheS\R4\20181217\0000 - SQL template must end with ".template" as extension Author: Ibrahim Al Khaled Date: Dec 18, 2018 #> [CmdletBinding()] param ( [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [int] $SqlQueryTimeout = 0, [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [Array] $SQLScriptsPaths, [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [String] $SQLServerName, [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [String] $SQLServerUserName, [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [String] $SQLServerPassword, [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [String] $TemplateModel, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [String] $ModelName = "Model", [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [ValidateSet("Database", "FileSystem", "Custom")] [String] $ProviderType, [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [String] $Custom ) DynamicParam { IF($ProviderType -eq $null) { throw "ProviderType is needed !!"; } $ProviderFullPath = Get-Provider -ProviderType $ProviderType -Custom $Custom; $FolderName = [System.IO.Path]::GetFileNameWithoutExtension($(split-path -path $ProviderFullPath -Leaf)); #$ProviderParameters = $(Get-COmmand $ProviderFullPath).Parameters; $DynamicParams = Get-DynamicParameters $ProviderFullPath; #Get-DynamicParams $ProviderParameters; return $DynamicParams; } Begin { $ParameterObj = @{}; FOREACH($DynamicParameter in $DynamicParams.Keys) { $ParameterObj[$DynamicParameter] = $PSCmdlet.MyInvocation.BoundParameters[$DynamicParameter]; } . $ProviderFullPath @ParameterObj; $RegistryList = Get-Registry; $Buffer = @{}; $Buffer.Succeeded = [System.Collections.ArrayList]::new(); $Buffer.Failed = [System.Collections.ArrayList]::new(); $Buffer.Skipped = [System.Collections.ArrayList]::new(); Write-Host "Maintaining database releases..." -ForegroundColor Cyan; Write-Host; } Process { FOREACH($ReleasePath in $SQLScriptsPaths) { IF([System.IO.Directory]::Exists($ReleasePath)) { $Release = [System.IO.DirectoryInfo]::new($ReleasePath); Write-Note -PreMessage "Maintaining database release (" -Message $Release.Name -PostMessage ")..." -ForegroundColor DarkCyan -MessageForegroundColor White; $Patches = Get-ChildItem -Path $Release.FullName -Directory | Sort-Object Name; IF($Patches -eq $null) { Write-Note -PreMessage "Patch folder for (" -Message $Release.Name -PostMessage ") is missing !" -ForegroundColor Magenta -MessageForegroundColor Yellow; } foreach($Patch in $Patches) { Write-Note -PreMessage "Maintaining database release patch (" -Message $Patch.Name -PostMessage ")..." -ForegroundColor DarkGray -MessageForegroundColor White; $Indexes = Get-ChildItem -Path $Patch.FullName -Directory | Sort-Object Name; IF($Indexes -eq $null) { Write-Note -PreMessage "Index folder for (" -Message $Patch.Name -PostMessage ") is missing !" -ForegroundColor Magenta -MessageForegroundColor Yellow; } foreach($Index in $Indexes) { Write-Note -PreMessage "Processing index (" -Message $Index.Name -PostMessage ")..." -ForegroundColor DarkYellow -MessageForegroundColor White; $Templates = Get-ChildItem -Path $Index.FullName -File -Recurse -Depth 0 -Filter '*.template' | Sort-Object LastWriteTime; IF($Templates -eq $null) { Write-Note -PreMessage "There is no templates to be executed under (" -Message $Index.Name -PostMessage ")..." -ForegroundColor Magenta -MessageForegroundColor Yellow; } foreach($Template in $Templates) { Write-Note -PreMessage "Processing Template (" -Message $Template.Name -PostMessage ")..." -ForegroundColor DarkGreen -MessageForegroundColor White; $ReleaseName = $(Split-Path $ReleasePath -leaf); $Source = $("$ReleaseName\$($Patch.Name)\$($Index.Name)"); $SourceHash = Get-FileHash -Path $Template.FullName -Algorithm SHA256; $IsValid = Validate-File -SourceHash $SourceHash -Source $Source -RegistryList $RegistryList; IF($IsValid -eq $true) { $TemplateQuery = Get-FormattedTemplate -ModelName $ModelName -Model $TemplateModel -Path $Template.FullName -Silent:$true; IF($TemplateQuery -ne $null) { Write-Verbose "Executing the below QUERY..."; Write-Verbose $TemplateQuery; [psobject]$Model = ` @{ FileName = $Template.Name; Hash = $SourceHash.Hash; ExceptionMessage = "NULL"; ExceptionDetails = "NULL"; Source = $Source; CreatedDate = $(Get-Date -Format "yyyy-MM-dd HH:mm:ss"); }; Try { Invoke-Sqlcmd ` -AbortOnError:$false ` -ErrorAction Stop ` -Query $TemplateQuery ` -QueryTimeout $SqlQueryTimeout ` -ServerInstance $SQLServerName ` -Username $SQLServerUserName ` -Password $SQLServerPassword; $Buffer.Succeeded += ($Template.FullName); Write-Note -PreMessage "Template (" -Message $Template.Name -PostMessage ") executed successfully" -ForegroundColor Green -MessageForegroundColor White; Set-Registry -Model $Model; } Catch { Write-Note -PreMessage "Error while executing (" -Message $Template.Name -PostMessage ")" -ForegroundColor Magenta -MessageForegroundColor White -NoNewline; Write-Host "Error: $_.Exception" -ForegroundColor Red; Write-Host; $Buffer.Failed += ($Template.FullName); $Model.ExceptionDetails = $([string]$_.Exception).Replace("`'", "`""); $Model.ExceptionMessage = $([string]$_.Exception.Message).Replace("`'", "`""); Set-Registry -Model $Model; } } } ELSE { $Buffer.Skipped += ($Template.FullName); Write-Note -PreMessage "Template (" -Message $Template.Name -PostMessage ") skipped already executed." -ForegroundColor Green -MessageForegroundColor White; } } Write-Note -PreMessage "Processing index (" -Message $Index.Name -PostMessage ") Completed" -ForegroundColor DarkYellow -MessageForegroundColor White; } Write-Note -PreMessage "Maintaining database release patch (" -Message $Patch.Name -PostMessage ") Completed" -ForegroundColor DarkGray -MessageForegroundColor White; } Write-Note -PreMessage "Maintaining database release (" -Message $Release.Name -PostMessage ") Completed." -ForegroundColor DarkCyan -MessageForegroundColor White; } } } End { Write-Host "Maintaining database releases completed..." -ForegroundColor DarkCyan; RETURN $Buffer; } } #endregion |