Octopus.Script exported 2022-10-17 by farhanalam belongs to ‘Grate’ category.
Database migrations using grate.
With this template you can either include grate with your package or use the Download grate? feature to download it at deploy time. If you’re downloading, you can choose the version by specifying it in the Version of grate.
NOTE:
- AWS EC2 IAM Role authentication requires the AWS CLI be installed.
- To run on Linux, the machine must have both PowerShell Core and .NET Core 3.1 installed.
Parameters
When steps based on the template are included in a project’s deployment process, the parameters below can be set.
grate Package
gratePackage =
The package containing the scripts for grate to deploy.
Database Server Name
grateServerName =
Name or IP address of the server being deployed to.
Database Server Port
grateServerPort =
Port number for the database server. Uses default server port if left blank.
Authentication Method
grateAuthenticationMethod = usernamepassword
Method used to authenticate to the database server.
Database Name
grateDatabaseName =
Name of the database to deploy to.
Force SSL
grateSsl =
Check this box for force connection string to use SSL. Only applicable to MariaDB, MySQL, SQL Server, and PostgreSQL database types.
Database Version
grateVersion = #{Octopus.Action.Package[gratePackage].PackageVersion}
Version number of your database migration. Default value is the version of the grate package.
Database Username
grateUsername =
Username of the account with sufficient permissions to execute scripts. (Leave blank for Integrated Authentication.)
Database User Password
grateUserPassword =
Password for the Database Username account.
Database Server Type
grateDatabaseServerType =
The database technology being deployed to.
Use Transaction?
grateWithTransaction = False
Check this box if you want all scripts to be run within the same transaction.
Dry Run?
grateDryRun = False
Check this box if you want to perform a dry run. Results are recorded and attached as deployment artifacts if you check Record Output.
Record Output?
grateRecordOutput = False
Check this box to record the output of the run. Useful for gathering what would be changed for approval purposes.
Command Timeout
grateCommandTimeout = 60
Customizable command timeout (in seconds). Default is 60
Use Baseline
grateBaseline = False
Check this box if you want grate to mark the scripts as run, but not to actually run anything against the database. More information about this option can be found here
SQL Script folder
grateSqlScriptFolder =
Script location to use for grate (if not in the root of the package)
Log Level
grateLogVerbosity = information
Configure log level when running Grate.
Download grate?
grateDownloadNuget = False
Check this box if you want the template to download RoundhousE and use the downloaded version for deployment. Requires .NET Core be installed on the machine executing the deployment.
Version of grate
grateNugetVersion =
Version of grate to download (used with Download grate option), leave blank for latest.
Grate environment
grateEnvironment = #{Octopus.Environment.Name}
The environment specific scripts that grate will execute
Grate schema for migration tables
grateSchema = grate
Useful if migrating from RoundhousE.
Script body
Steps based on this template will execute the following PowerShell script.
# Configure template
# Check to see if $IsWindows is available
if ($null -eq $IsWindows)
{
Write-Host "Determining Operating System..."
switch ([System.Environment]::OSVersion.Platform)
{
"Win32NT"
{
# Set variable
$IsWindows = $true
$IsLinux = $false
}
"Unix"
{
$IsWindows = $false
$IsLinux = $true
}
}
}
if ($IsWindows)
{
$ProgressPreference = 'SilentlyContinue'
}
# Define parameters
$grateExecutable = ""
$grateOutputPath = [System.IO.Path]::Combine($OctopusParameters["Octopus.Action.Package[gratePackage].ExtractedPath"], "output")
$grateSsl = [System.Convert]::ToBoolean($grateSsl)
Function Get-LatestVersionDownloadUrl {
# Define parameters
param(
$Repository,
$Version
)
# Define local variables
$releases = "https://api.github.com/repos/$Repository/releases"
# Get latest version
Write-Host "Determining latest release of $Repository ..."
$tags = (Invoke-WebRequest $releases -UseBasicParsing | ConvertFrom-Json)
if ($null -ne $Version) {
# Get specific version
$tags = ($tags | Where-Object { $_.tag_name.EndsWith($Version) })
# Check to see if nothing was returned
if ($null -eq $tags) {
# Not found
Write-Host "No release found matching version $Version, getting highest version using Major.Minor syntax..."
# Get the tags
$tags = (Invoke-WebRequest $releases -UseBasicParsing | ConvertFrom-Json)
# Parse the version number into a version object
$parsedVersion = [System.Version]::Parse($Version)
$partialVersion = "$($parsedVersion.Major).$($parsedVersion.Minor)"
# Filter tags to ones matching only Major.Minor of version specified
$tags = ($tags | Where-Object { $_.tag_name.Contains("$partialVersion.") -and $_.draft -eq $false })
# Grab the latest
if ($null -eq $tags)
{
# decrement minor version
$minorVersion = [int]$parsedVersion.Minor
$minorVersion --
# Check to make sure that minor version isn't negative
if ($minorVersion -ge 0)
{
# return the urls
return (Get-LatestVersionDownloadUrl -Repository $Repository -Version "$($parsedVersion.Major).$($minorVersion)")
}
else
{
# Display error
Write-Error "Unable to find a version within the major version of $($parsedVersion.Major)!"
}
}
}
}
# Find the latest version with a downloadable asset
foreach ($tag in $tags) {
if ($tag.assets.Count -gt 0) {
return $tag.assets.browser_download_url
}
}
# Return the version
return $null
}
# Change the location to the extract path
Set-Location -Path $OctopusParameters["Octopus.Action.Package[gratePackage].ExtractedPath"]
# Check to see if download is specified
if ([System.Boolean]::Parse($grateDownloadNuget))
{
# Set secure protocols
[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls11 -bor [System.Net.SecurityProtocolType]::Tls12
$downloadUrls = @()
# Check to see if version number specified
if ([string]::IsNullOrWhitespace($grateNugetVersion))
{
# Get the latest version number
$downloadUrls = Get-LatestVersionDownloadUrl -Repository "erikbra/grate"
}
else
{
# Get specific version
$downloadUrls = Get-LatestVersionDownloadUrl -Repository "erikbra/grate" -Version $grateNugetVersion
}
# Check to make sure something was returned
if ($null -ne $downloadUrls -and $downloadUrls.Length -gt 0)
{
# Check for download folder
if ((Test-Path -Path "$PSSCriptRoot/grate") -eq $false)
{
# Create the folder
New-Item -ItemType Directory -Path "$PSSCriptRoot/grate"
}
# Get URL of grate-dotnet-tool
$downloadUrl = $downloadUrls | Where-Object {$_.Contains("grate-dotnet-tool")}
# Check to see if something was returned
if ($null -eq $downloadUrl)
{
# Attempt to get nuget package
Write-Host "An asset with grate-dotnet-tool was not found, attempting to locate nuget package ..."
$downloadUrl = $downloadUrls | Where-Object {$_.Contains(".nupkg")}
# Check to see if something was returned
if ($null -eq $downloadUrl)
{
Write-Error "Unable to find appropriate asset for download."
}
}
# Download nuget package
Write-Output "Downloading $downloadUrl ..."
# Get download file name
$downloadFile = $downloadUrl.Substring($downloadUrl.LastIndexOf("/") + 1)
# Download the file
Invoke-WebRequest -Uri $downloadUrl -OutFile "$PSSCriptRoot/grate/$downloadFile"
# Check the extension
if ($downloadFile.EndsWith(".zip"))
{
# Extract the file
Write-Host "Extracting $downloadFile ..."
Expand-Archive -Path "$PSSCriptRoot/grate/$downloadFile" -Destination "$PSSCriptRoot/grate"
# Delete the downloaded .zip
Remove-Item -Path "$PSSCriptRoot/grate/$downloadFile"
# Get extracted files
$extractedFiles = Get-ChildItem -Path "$PSSCriptRoot/grate"
# Check to see if what was extracted was simply a nuget file
if ($extractedFiles.Count -eq 1 -and $extractedFiles[0].Extension -eq ".nupkg")
{
# Zip file contained a nuget package </facepalm>
Write-Host "Archive contained a NuGet package, extracting package ..."
$nugetPackage = $extractedFiles[0]
$nugetPackage | Rename-Item -NewName $nugetPackage.Name.Replace(".nupkg", ".zip")
Expand-Archive -Path $nugetPackage.FullName.Replace(".nupkg", ".zip") -Destination "$PSSCriptRoot/grate"
}
}
if ($downloadFile.EndsWith(".nupkg"))
{
# Zip file contained a nuget package </facepalm>
$nugetPackage = Get-ChildItem -Path "$PSSCriptRoot/grate/$($downloadFile)"
$nugetPackage | Rename-Item -NewName $nugetPackage.Name.Replace(".nupkg", ".zip")
Expand-Archive -Path "$PSSCriptRoot/grate/$($downloadFile.Replace(".nupkg", ".zip"))" -Destination "$PSSCriptRoot/grate"
}
}
else
{
Write-Error "No download url returned!"
}
}
if ([string]::IsNullOrWhitespace($grateExecutable))
{
# Look for just grate.dll
$grateExecutable = Get-ChildItem -Path $PSSCriptRoot -Recurse | Where-Object {$_.Name -eq "grate.dll"}
# Check for multiple results
if ($grateExecutable -is [array])
{
# choose one that matches highest version of .net
$dotnetVersions = (dotnet --list-runtimes) | Where-Object {$_ -like "*.NetCore*"}
$maxVersion = $null
foreach ($dotnetVersion in $dotnetVersions)
{
$parsedVersion = $dotnetVersion.Split(" ")[1]
if ($null -eq $maxVersion -or [System.Version]::Parse($parsedVersion) -gt [System.Version]::Parse($maxVersion))
{
$maxVersion = $parsedVersion
}
}
$grateExecutable = $grateExecutable | Where-Object {$_.FullName -like "*net$(([System.Version]::Parse($maxVersion).Major))*"}
}
}
if ([string]::IsNullOrWhitespace($grateExecutable))
{
# Couldn't find grate
Write-Error "Couldn't find the grate executable!"
}
# Build the arguments
$grateSwitches = @()
# Update the connection string based on authentication method
switch ($grateAuthenticationMethod)
{
"awsiam"
{
# Region is part of the RDS endpoint, extract
$region = ($grateServerName.Split("."))[2]
Write-Host "Generating AWS IAM token ..."
$grateUserPassword = (aws rds generate-db-auth-token --hostname $grateServerName --region $region --port $grateServerPort --username $grateUserName)
$grateUserInfo = "Uid=$grateUserName;Pwd=$grateUserPassword;"
break
}
"azuremanagedidentity"
{
# SQL Server driver doesn't assign password
if ($grateDatabaseServerType -ne "sqlserver")
{
# Get login token
Write-Host "Generating Azure Managed Identity token ..."
$token = Invoke-RestMethod -Method GET -Uri "http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https://ossrdbms-aad.database.windows.net" -Headers @{"MetaData" = "true"}
$grateUserPassword = $token.access_token
$grateUserInfo = "Uid=$grateUserName;Pwd=$grateUserPassword;"
}
break
}
"gcpserviceaccount"
{
# Define header
$header = @{ "Metadata-Flavor" = "Google"}
# Retrieve service accounts
$serviceAccounts = Invoke-RestMethod -Method Get -Uri "http://metadata.google.internal/computeMetadata/v1/instance/service-accounts/" -Headers $header
# Results returned in plain text format, get into array and remove empty entries
$serviceAccounts = $serviceAccounts.Split([Environment]::NewLine, [StringSplitOptions]::RemoveEmptyEntries)
# Retreive the specific service account assigned to the VM
$serviceAccount = $serviceAccounts | Where-Object {$_.Contains("iam.gserviceaccount.com") }
Write-Host "Generating GCP IAM token ..."
# Retrieve token for account
$token = Invoke-RestMethod -Method Get -Uri "http://metadata.google.internal/computeMetadata/v1/instance/service-accounts/$serviceAccount/token" -Headers $header
$grateUserPassword = $token.access_token
# Append remaining portion of connection string
$grateUserInfo = "Uid=$grateUserName;Pwd=$grateUserPassword;"
}
"usernamepassword"
{
# Append remaining portion of connection string
$grateUserInfo = "Uid=$grateUserName;Pwd=$grateUserPassword;"
break
}
"windowsauthentication"
{
# Append remaining portion of connection string
$grateUserInfo = "integrated security=true;"
# Append username (required for non
$grateUserInfo += "Uid=$grateUserName;"
}
}
# Configure connnection string based on technology
switch ($grateDatabaseServerType)
{
"sqlserver"
{
# Check to see if port has been defined
if (![string]::IsNullOrEmpty($grateServerPort))
{
# Append to servername
$grateServerName += ",$grateServerPort"
# Empty the port
$grateServerPort = [string]::Empty
}
}
"mariadb"
{
$grateServerPort = "Port=$grateServerPort;Allow User Variables=true;"
}
"mysql"
{
# Use the MySQL client
$grateDatabaseServerType = "mariadb"
$grateServerPort = "Port=$grateServerPort;Allow User Variables=true;"
}
"oracle"
{
# Oracle connection strings are built different than all others
$grateServerConnectionString = "--connectionstring=`"Data source=$($grateServerName):$($grateServerPort)/$grateDatabaseName;$($grateUserInfo.Replace("Uid", "User Id").Replace("Pwd", "Password")) "
}
default
{
$grateServerPort = "Port=$grateServerPort;"
}
}
# Build base connection string
if ([string]::IsNullOrWhitespace($grateServerConnectionString))
{
$grateServerConnectionString = "--connectionstring=`"Server=$grateServerName;$grateServerPort $grateUserInfo Database=$grateDatabaseName;"
}
# Check for SQL Server and Azure Managed Identity
if (($grateDatabaseServerType -eq "sqlserver") -and ($grateAuthenticationMethod -eq "azuremanagedidentity"))
{
# Append AD component to connection string
$grateServerConnectionString += "Authentication=Active Directory Default;"
}
if ($grateSsl -eq $true)
{
if (($grateDatabaseServerType -eq "mariadb") -or ($grateDatabaseServerType -eq "mysql") -or ($grateDatabaseServerType -eq "postgres"))
{
# Add sslmode
$grateServerConnectionString += "SslMode=Require;Trust Server Certificate=true;"
}
elseif ($grateDatabaseServerType -eq "sqlserver")
{
$grateServerConnectionString += "Trust Server Certificate=true;"
}
else
{
Write-Warning "Invalid Database Server Type selection for SSL, ignoring setting."
}
}
# Add terminating double quote to connection string
$grateServerConnectionString += "`""
$grateSwitches += $grateServerConnectionString
$grateSwitches += "--databasetype=$grateDatabaseServerType"
$grateSwitches += "--silent"
if ([System.Boolean]::Parse($grateDryRun))
{
$grateSwitches += "--dryrun"
}
if ([System.Boolean]::Parse($grateRecordOutput))
{
$grateSwitches += "--outputPath=$grateOutputPath"
# Check to see if path exists
if ((Test-Path -Path $grateOutputPath) -eq $false)
{
# Create folder
New-Item -Path $grateOutputPath -ItemType "Directory"
}
}
# Add transaction switch
$grateSwitches += "--transaction=$($grateWithTransaction.ToLower())"
# Add Command Timeout
if (![string]::IsNullOrEmpty($grateCommandTimeout)){
$grateSwitches += "--commandtimeout=$([int]$grateCommandTimeout)"
}
# Add Baseline switch
if ([System.Boolean]::Parse($grateBaseline)) {
$grateSwitches += "--baseline"
}
# Add SQL Files Directory parameter
if (![string]::IsNullOrEmpty($grateSqlScriptFolder)) {
# Add up folder
$grateSwitches += "--sqlfilesdirectory=$grateSqlScriptFolder"
}
# Add log verbosity flag
if (![string]::IsNullOrEmpty($grateLogVerbosity)) {
# Add up folder
$grateSwitches += "--verbosity=$grateLogVerbosity"
}
# Check for version
if (![string]::IsNullOrEmpty($grateVersion))
{
# Add version
$grateSwitches += "--version=$grateVersion"
}
# Set grate environment
if (![string]::IsNullOrEmpty($grateEnvironment))
{
# Add environment
$grateSwitches += "--environment=$grateEnvironment"
}
# Set grate schema. Especially useful when migrating from RoundhousE
if (![string]::IsNullOrEmpty($grateSchema))
{
# Add schema
$grateSwitches += "--schema=$grateSchema"
}
# Display what's going to be run
if (![string]::IsNullOrWhitespace($grateUserPassword))
{
Write-Host "Executing $($grateExecutable.FullName) with $($grateSwitches.Replace($grateUserPassword, "****"))"
}
else
{
Write-Host "Executing $($grateExecutable.FullName) with $($grateSwitches)"
}
# Execute grate
if ($grateExecutable.FullName.EndsWith(".dll"))
{
& dotnet $grateExecutable.FullName $grateSwitches
}
else
{
& $grateExecutable.FullName $grateSwitches
}
# If the output path was specified, attach artifacts
if ([System.Boolean]::Parse($grateRecordOutput))
{
# Zip up output folder content
Add-Type -Assembly 'System.IO.Compression.FileSystem'
$zipFile = "$($OctopusParameters["Octopus.Action.Package[gratePackage].ExtractedPath"])/output.zip"
[System.IO.Compression.ZipFile]::CreateFromDirectory($grateOutputPath, $zipFile)
New-OctopusArtifact -Path "$zipFile" -Name "output.zip"
}
Provided under the Apache License version 2.0.
To use this template in Octopus Deploy, copy the JSON below and paste it into the Library → Step templates → Import dialog.
{
"Id": "ca23d18f-ab03-403d-bfb8-3ff74d3ddab3",
"Name": "grate Database Migrations",
"Description": "Database migrations using [grate](https://github.com/erikbra/grate).\nWith this template you can either include grate with your package or use the `Download grate?` feature to download it at deploy time. If you're downloading, you can choose the version by specifying it in the `Version of grate`.\n\nNOTE: \n - AWS EC2 IAM Role authentication requires the AWS CLI be installed.\n - To run on Linux, the machine must have both PowerShell Core and .NET Core 3.1 installed.",
"Version": 9,
"ExportedAt": "2022-10-17T22:47:54.861Z",
"ActionType": "Octopus.Script",
"Author": "farhanalam",
"Packages": [
{
"Id": "e0d1bcb0-4a7e-41dc-ab53-2799d6f2b051",
"Name": "gratePackage",
"PackageId": null,
"FeedId": null,
"AcquisitionLocation": "Server",
"Properties": {
"Extract": "True",
"SelectionMode": "deferred",
"PackageParameterName": "gratePackage",
"Purpose": ""
}
}
],
"Parameters": [
{
"Id": "5d54012a-1f10-4b3c-bbfd-fe70bf843904",
"Name": "gratePackage",
"Label": "grate Package",
"HelpText": "The package containing the scripts for grate to deploy.",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "Package"
}
},
{
"Id": "57a43e61-86c0-46a3-8446-aacbb67cf596",
"Name": "grateServerName",
"Label": "Database Server Name",
"HelpText": "Name or IP address of the server being deployed to.",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "ba3705a1-e620-4fb1-b494-2b9e65fbb194",
"Name": "grateServerPort",
"Label": "Database Server Port",
"HelpText": "Port number for the database server. Uses default server port if left blank.",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "c3a7c802-babd-470b-8447-5f6159128b4c",
"Name": "grateAuthenticationMethod",
"Label": "Authentication Method",
"HelpText": "Method used to authenticate to the database server.",
"DefaultValue": "usernamepassword",
"DisplaySettings": {
"Octopus.ControlType": "Select",
"Octopus.SelectOptions": "awsiam|AWS EC2 IAM Role\nazuremanagedidentity|Azure Managed Identity\ngcpserviceaccount|GCP Service Account\nusernamepassword|Username\\Password\nwindowsauthentication|Windows Authentication"
}
},
{
"Id": "c955ad8c-9686-419c-a1cf-129ffbe0acb4",
"Name": "grateDatabaseName",
"Label": "Database Name",
"HelpText": "Name of the database to deploy to.",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "0438580a-8825-40db-a782-569a35144f4b",
"Name": "grateSsl",
"Label": "Force SSL",
"HelpText": "Check this box for force connection string to use SSL. Only applicable to MariaDB, MySQL, SQL Server, and PostgreSQL database types.",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "Checkbox"
}
},
{
"Id": "e007d182-88f5-4e1f-906f-c85ff955c51e",
"Name": "grateVersion",
"Label": "Database Version",
"HelpText": "Version number of your database migration. Default value is the version of the grate package.",
"DefaultValue": "#{Octopus.Action.Package[gratePackage].PackageVersion}",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "58ceec6b-f374-4c2e-83bb-fbd7ce26dc05",
"Name": "grateUsername",
"Label": "Database Username",
"HelpText": "Username of the account with sufficient permissions to execute scripts. (Leave blank for Integrated Authentication.)",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "29f6bb35-c076-4b5d-98cf-8ee8494cb38b",
"Name": "grateUserPassword",
"Label": "Database User Password",
"HelpText": "Password for the Database Username account.",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "Sensitive"
}
},
{
"Id": "5e5d68f1-fdc8-41ce-8cd3-ab082278218d",
"Name": "grateDatabaseServerType",
"Label": "Database Server Type",
"HelpText": "The database technology being deployed to.",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "Select",
"Octopus.SelectOptions": "mariadb|MariaDB\nmysql|MySQL\noracle|Oracle\npostgresql|PostgreSQL\nsqlserver|SQL Server"
}
},
{
"Id": "67c016c3-d779-4300-b3d1-e11463bb9fc4",
"Name": "grateWithTransaction",
"Label": "Use Transaction?",
"HelpText": "Check this box if you want all scripts to be run within the same transaction.",
"DefaultValue": "False",
"DisplaySettings": {
"Octopus.ControlType": "Checkbox"
}
},
{
"Id": "29a4f568-23f7-4bb2-a5ba-910b922c5406",
"Name": "grateDryRun",
"Label": "Dry Run?",
"HelpText": "Check this box if you want to perform a dry run. Results are recorded and attached as deployment artifacts if you check Record Output.",
"DefaultValue": "False",
"DisplaySettings": {
"Octopus.ControlType": "Checkbox"
}
},
{
"Id": "eaad7639-05d8-46ed-bab3-b72b8ad81d5a",
"Name": "grateRecordOutput",
"Label": "Record Output?",
"HelpText": "Check this box to record the output of the run. Useful for gathering what would be changed for approval purposes.",
"DefaultValue": "False",
"DisplaySettings": {
"Octopus.ControlType": "Checkbox"
}
},
{
"Id": "84b46c25-6762-44c2-8ec6-4ba30ff599f5",
"Name": "grateCommandTimeout",
"Label": "Command Timeout",
"HelpText": "Customizable command timeout (in seconds). Default is 60",
"DefaultValue": "60",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "3382a212-f467-409e-950b-317f05f59ea5",
"Name": "grateBaseline",
"Label": "Use Baseline",
"HelpText": "Check this box if you want grate to mark the scripts as run, but not to actually run anything against the database. [More information about this option can be found here](https://erikbra.github.io/grate/configuration-options/)",
"DefaultValue": "False",
"DisplaySettings": {
"Octopus.ControlType": "Checkbox"
}
},
{
"Id": "60dc1e2a-93ec-4bc9-abde-aacf6aeba0a7",
"Name": "grateSqlScriptFolder",
"Label": "SQL Script folder",
"HelpText": "Script location to use for grate (if not in the root of the package)",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "b792092e-e3c1-487b-8349-e17832506f12",
"Name": "grateLogVerbosity",
"Label": "Log Level",
"HelpText": "Configure log level when running Grate.",
"DefaultValue": "information",
"DisplaySettings": {
"Octopus.ControlType": "Select",
"Octopus.SelectOptions": "critical|Critical\ndebug|Debug\nerror|Error\ninformation|Information\nnone|None\ntrace|Trace\nwarning|Warning"
}
},
{
"Id": "b8a17064-900f-4d77-9354-e5de1d3057f0",
"Name": "grateDownloadNuget",
"Label": "Download grate?",
"HelpText": "Check this box if you want the template to download RoundhousE and use the downloaded version for deployment. Requires .NET Core be installed on the machine executing the deployment.",
"DefaultValue": "False",
"DisplaySettings": {
"Octopus.ControlType": "Checkbox"
}
},
{
"Id": "409bd29d-79cf-4209-a005-ce768714eb64",
"Name": "grateNugetVersion",
"Label": "Version of grate",
"HelpText": "Version of grate to download (used with Download grate option), leave blank for latest.",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "d9660ee0-8376-4f9b-8d1d-99f3b4701a42",
"Name": "grateEnvironment",
"Label": "Grate environment",
"HelpText": "The environment specific scripts that grate will execute",
"DefaultValue": "#{Octopus.Environment.Name}",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "a93b61a7-74ae-471e-ace3-d09cdfab8858",
"Name": "grateSchema",
"Label": "Grate schema for migration tables",
"HelpText": "Useful if migrating from RoundhousE.",
"DefaultValue": "grate",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
}
],
"Properties": {
"Octopus.Action.Script.ScriptSource": "Inline",
"Octopus.Action.Script.Syntax": "PowerShell",
"Octopus.Action.Script.ScriptBody": "# Configure template\n\n# Check to see if $IsWindows is available\nif ($null -eq $IsWindows)\n{\n\tWrite-Host \"Determining Operating System...\"\n switch ([System.Environment]::OSVersion.Platform)\n {\n \t\"Win32NT\"\n {\n \t# Set variable\n $IsWindows = $true\n $IsLinux = $false\n }\n \"Unix\"\n {\n \t$IsWindows = $false\n $IsLinux = $true\n }\n }\n}\n\nif ($IsWindows)\n{\n\t$ProgressPreference = 'SilentlyContinue'\n}\n\n# Define parameters\n$grateExecutable = \"\"\n$grateOutputPath = [System.IO.Path]::Combine($OctopusParameters[\"Octopus.Action.Package[gratePackage].ExtractedPath\"], \"output\")\n$grateSsl = [System.Convert]::ToBoolean($grateSsl)\n\nFunction Get-LatestVersionDownloadUrl {\n # Define parameters\n param(\n $Repository,\n $Version\n )\n \n # Define local variables\n $releases = \"https://api.github.com/repos/$Repository/releases\"\n \n # Get latest version\n Write-Host \"Determining latest release of $Repository ...\"\n \n $tags = (Invoke-WebRequest $releases -UseBasicParsing | ConvertFrom-Json)\n \n if ($null -ne $Version) {\n # Get specific version\n $tags = ($tags | Where-Object { $_.tag_name.EndsWith($Version) })\n\n # Check to see if nothing was returned\n if ($null -eq $tags) {\n # Not found\n Write-Host \"No release found matching version $Version, getting highest version using Major.Minor syntax...\"\n\n # Get the tags\n $tags = (Invoke-WebRequest $releases -UseBasicParsing | ConvertFrom-Json)\n\n # Parse the version number into a version object\n $parsedVersion = [System.Version]::Parse($Version)\n $partialVersion = \"$($parsedVersion.Major).$($parsedVersion.Minor)\"\n\n # Filter tags to ones matching only Major.Minor of version specified\n $tags = ($tags | Where-Object { $_.tag_name.Contains(\"$partialVersion.\") -and $_.draft -eq $false })\n \n # Grab the latest\n if ($null -eq $tags)\n {\n \t# decrement minor version\n $minorVersion = [int]$parsedVersion.Minor\n $minorVersion --\n \n # Check to make sure that minor version isn't negative\n if ($minorVersion -ge 0)\n {\n \t# return the urls\n \treturn (Get-LatestVersionDownloadUrl -Repository $Repository -Version \"$($parsedVersion.Major).$($minorVersion)\")\n }\n else\n {\n \t# Display error\n Write-Error \"Unable to find a version within the major version of $($parsedVersion.Major)!\"\n }\n }\n }\n }\n\n # Find the latest version with a downloadable asset\n foreach ($tag in $tags) {\n if ($tag.assets.Count -gt 0) {\n return $tag.assets.browser_download_url\n }\n }\n\n # Return the version\n return $null\n}\n\n# Change the location to the extract path\nSet-Location -Path $OctopusParameters[\"Octopus.Action.Package[gratePackage].ExtractedPath\"]\n\n# Check to see if download is specified\nif ([System.Boolean]::Parse($grateDownloadNuget))\n{\n # Set secure protocols\n [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls11 -bor [System.Net.SecurityProtocolType]::Tls12\n $downloadUrls = @()\n\n\t# Check to see if version number specified\n if ([string]::IsNullOrWhitespace($grateNugetVersion))\n {\n \t# Get the latest version number\n $downloadUrls = Get-LatestVersionDownloadUrl -Repository \"erikbra/grate\"\n }\n else\n {\n \t# Get specific version\n $downloadUrls = Get-LatestVersionDownloadUrl -Repository \"erikbra/grate\" -Version $grateNugetVersion\n }\n\n\t# Check to make sure something was returned\n if ($null -ne $downloadUrls -and $downloadUrls.Length -gt 0)\n\t{\n \n # Check for download folder\n if ((Test-Path -Path \"$PSSCriptRoot/grate\") -eq $false)\n {\n # Create the folder\n New-Item -ItemType Directory -Path \"$PSSCriptRoot/grate\"\n }\n\n # Get URL of grate-dotnet-tool\n $downloadUrl = $downloadUrls | Where-Object {$_.Contains(\"grate-dotnet-tool\")}\n \n # Check to see if something was returned\n if ($null -eq $downloadUrl)\n {\n \t# Attempt to get nuget package\n Write-Host \"An asset with grate-dotnet-tool was not found, attempting to locate nuget package ...\"\n $downloadUrl = $downloadUrls | Where-Object {$_.Contains(\".nupkg\")}\n \n # Check to see if something was returned\n if ($null -eq $downloadUrl)\n {\n \tWrite-Error \"Unable to find appropriate asset for download.\"\n }\n }\n\n # Download nuget package\n Write-Output \"Downloading $downloadUrl ...\"\n\n # Get download file name\n $downloadFile = $downloadUrl.Substring($downloadUrl.LastIndexOf(\"/\") + 1)\n\n # Download the file\n Invoke-WebRequest -Uri $downloadUrl -OutFile \"$PSSCriptRoot/grate/$downloadFile\"\n\n # Check the extension\n if ($downloadFile.EndsWith(\".zip\"))\n {\n # Extract the file\n Write-Host \"Extracting $downloadFile ...\"\n Expand-Archive -Path \"$PSSCriptRoot/grate/$downloadFile\" -Destination \"$PSSCriptRoot/grate\"\n\n # Delete the downloaded .zip\n Remove-Item -Path \"$PSSCriptRoot/grate/$downloadFile\"\n\n # Get extracted files\n $extractedFiles = Get-ChildItem -Path \"$PSSCriptRoot/grate\"\n\n # Check to see if what was extracted was simply a nuget file\n if ($extractedFiles.Count -eq 1 -and $extractedFiles[0].Extension -eq \".nupkg\")\n {\n # Zip file contained a nuget package </facepalm>\n Write-Host \"Archive contained a NuGet package, extracting package ...\"\n $nugetPackage = $extractedFiles[0]\n $nugetPackage | Rename-Item -NewName $nugetPackage.Name.Replace(\".nupkg\", \".zip\")\n Expand-Archive -Path $nugetPackage.FullName.Replace(\".nupkg\", \".zip\") -Destination \"$PSSCriptRoot/grate\"\n }\n }\n\n if ($downloadFile.EndsWith(\".nupkg\"))\n {\n # Zip file contained a nuget package </facepalm>\n $nugetPackage = Get-ChildItem -Path \"$PSSCriptRoot/grate/$($downloadFile)\"\n $nugetPackage | Rename-Item -NewName $nugetPackage.Name.Replace(\".nupkg\", \".zip\")\n Expand-Archive -Path \"$PSSCriptRoot/grate/$($downloadFile.Replace(\".nupkg\", \".zip\"))\" -Destination \"$PSSCriptRoot/grate\" \n }\n }\n else\n {\n \tWrite-Error \"No download url returned!\"\n }\n}\n\n\n\nif ([string]::IsNullOrWhitespace($grateExecutable))\n{\n\t# Look for just grate.dll\n $grateExecutable = Get-ChildItem -Path $PSSCriptRoot -Recurse | Where-Object {$_.Name -eq \"grate.dll\"}\n \n # Check for multiple results\n if ($grateExecutable -is [array])\n {\n # choose one that matches highest version of .net\n\t\t$dotnetVersions = (dotnet --list-runtimes) | Where-Object {$_ -like \"*.NetCore*\"}\n\n\t\t$maxVersion = $null\n\t\tforeach ($dotnetVersion in $dotnetVersions)\n\t\t{\n \t\t$parsedVersion = $dotnetVersion.Split(\" \")[1]\n \t\tif ($null -eq $maxVersion -or [System.Version]::Parse($parsedVersion) -gt [System.Version]::Parse($maxVersion))\n \t\t{\n \t\t$maxVersion = $parsedVersion\n \t\t}\n\t\t}\n \n $grateExecutable = $grateExecutable | Where-Object {$_.FullName -like \"*net$(([System.Version]::Parse($maxVersion).Major))*\"}\n }\n}\n\nif ([string]::IsNullOrWhitespace($grateExecutable))\n{\n # Couldn't find grate\n Write-Error \"Couldn't find the grate executable!\"\n}\n\n# Build the arguments\n$grateSwitches = @()\n\n# Update the connection string based on authentication method\nswitch ($grateAuthenticationMethod)\n{\n \"awsiam\"\n {\n # Region is part of the RDS endpoint, extract\n $region = ($grateServerName.Split(\".\"))[2]\n\n Write-Host \"Generating AWS IAM token ...\"\n $grateUserPassword = (aws rds generate-db-auth-token --hostname $grateServerName --region $region --port $grateServerPort --username $grateUserName) \n $grateUserInfo = \"Uid=$grateUserName;Pwd=$grateUserPassword;\"\n\n break\n }\n\t\n \"azuremanagedidentity\"\n {\n \t# SQL Server driver doesn't assign password\n if ($grateDatabaseServerType -ne \"sqlserver\")\n {\n # Get login token\n Write-Host \"Generating Azure Managed Identity token ...\"\n $token = Invoke-RestMethod -Method GET -Uri \"http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https://ossrdbms-aad.database.windows.net\" -Headers @{\"MetaData\" = \"true\"}\n\n $grateUserPassword = $token.access_token\n $grateUserInfo = \"Uid=$grateUserName;Pwd=$grateUserPassword;\"\n }\n \n break\n }\n\n \"gcpserviceaccount\"\n {\n # Define header\n $header = @{ \"Metadata-Flavor\" = \"Google\"}\n\n # Retrieve service accounts\n $serviceAccounts = Invoke-RestMethod -Method Get -Uri \"http://metadata.google.internal/computeMetadata/v1/instance/service-accounts/\" -Headers $header\n\n # Results returned in plain text format, get into array and remove empty entries\n $serviceAccounts = $serviceAccounts.Split([Environment]::NewLine, [StringSplitOptions]::RemoveEmptyEntries)\n\n # Retreive the specific service account assigned to the VM\n $serviceAccount = $serviceAccounts | Where-Object {$_.Contains(\"iam.gserviceaccount.com\") }\n\n\t\tWrite-Host \"Generating GCP IAM token ...\"\n # Retrieve token for account\n $token = Invoke-RestMethod -Method Get -Uri \"http://metadata.google.internal/computeMetadata/v1/instance/service-accounts/$serviceAccount/token\" -Headers $header\n $grateUserPassword = $token.access_token\n \n # Append remaining portion of connection string\n $grateUserInfo = \"Uid=$grateUserName;Pwd=$grateUserPassword;\"\n }\n\n\n \"usernamepassword\"\n {\n \t# Append remaining portion of connection string\n $grateUserInfo = \"Uid=$grateUserName;Pwd=$grateUserPassword;\"\n\n\t\tbreak \n\t}\n\n \"windowsauthentication\"\n {\n # Append remaining portion of connection string\n\t $grateUserInfo = \"integrated security=true;\"\n \n # Append username (required for non\n $grateUserInfo += \"Uid=$grateUserName;\"\n }\n \n}\n\n# Configure connnection string based on technology\nswitch ($grateDatabaseServerType)\n{\n \"sqlserver\"\n {\n # Check to see if port has been defined\n if (![string]::IsNullOrEmpty($grateServerPort))\n {\n # Append to servername\n $grateServerName += \",$grateServerPort\"\n\n # Empty the port\n $grateServerPort = [string]::Empty\n }\n }\n \"mariadb\"\n {\n \t$grateServerPort = \"Port=$grateServerPort;Allow User Variables=true;\"\n }\n \"mysql\"\n {\n \t# Use the MySQL client\n $grateDatabaseServerType = \"mariadb\"\n $grateServerPort = \"Port=$grateServerPort;Allow User Variables=true;\"\n }\n \"oracle\"\n {\n \t# Oracle connection strings are built different than all others\n $grateServerConnectionString = \"--connectionstring=`\"Data source=$($grateServerName):$($grateServerPort)/$grateDatabaseName;$($grateUserInfo.Replace(\"Uid\", \"User Id\").Replace(\"Pwd\", \"Password\")) \"\n }\n default\n {\n $grateServerPort = \"Port=$grateServerPort;\"\n }\n}\n\n# Build base connection string\nif ([string]::IsNullOrWhitespace($grateServerConnectionString))\n{\n\t$grateServerConnectionString = \"--connectionstring=`\"Server=$grateServerName;$grateServerPort $grateUserInfo Database=$grateDatabaseName;\"\n}\n\n# Check for SQL Server and Azure Managed Identity\nif (($grateDatabaseServerType -eq \"sqlserver\") -and ($grateAuthenticationMethod -eq \"azuremanagedidentity\"))\n{\n\t# Append AD component to connection string\n $grateServerConnectionString += \"Authentication=Active Directory Default;\"\n}\n\nif ($grateSsl -eq $true)\n{\n\tif (($grateDatabaseServerType -eq \"mariadb\") -or ($grateDatabaseServerType -eq \"mysql\") -or ($grateDatabaseServerType -eq \"postgres\"))\n {\n \t# Add sslmode\n $grateServerConnectionString += \"SslMode=Require;Trust Server Certificate=true;\"\n }\n elseif ($grateDatabaseServerType -eq \"sqlserver\")\n {\n \t$grateServerConnectionString += \"Trust Server Certificate=true;\"\n }\n else\n {\n \tWrite-Warning \"Invalid Database Server Type selection for SSL, ignoring setting.\"\n }\n}\n\n# Add terminating double quote to connection string\n$grateServerConnectionString += \"`\"\"\n\n$grateSwitches += $grateServerConnectionString\n\n$grateSwitches += \"--databasetype=$grateDatabaseServerType\"\n$grateSwitches += \"--silent\"\n\nif ([System.Boolean]::Parse($grateDryRun))\n{\n $grateSwitches += \"--dryrun\"\n}\n\nif ([System.Boolean]::Parse($grateRecordOutput))\n{\n $grateSwitches += \"--outputPath=$grateOutputPath\"\n \n # Check to see if path exists\n if ((Test-Path -Path $grateOutputPath) -eq $false)\n {\n \t# Create folder\n New-Item -Path $grateOutputPath -ItemType \"Directory\"\n }\n}\n\n# Add transaction switch\n$grateSwitches += \"--transaction=$($grateWithTransaction.ToLower())\"\n\n# Add Command Timeout\nif (![string]::IsNullOrEmpty($grateCommandTimeout)){\n $grateSwitches += \"--commandtimeout=$([int]$grateCommandTimeout)\"\n}\n\n# Add Baseline switch\nif ([System.Boolean]::Parse($grateBaseline)) {\n $grateSwitches += \"--baseline\"\n}\n\n# Add SQL Files Directory parameter\nif (![string]::IsNullOrEmpty($grateSqlScriptFolder)) {\n # Add up folder\n $grateSwitches += \"--sqlfilesdirectory=$grateSqlScriptFolder\"\n}\n\n# Add log verbosity flag\nif (![string]::IsNullOrEmpty($grateLogVerbosity)) {\n # Add up folder\n $grateSwitches += \"--verbosity=$grateLogVerbosity\"\n}\n\n\n# Check for version\nif (![string]::IsNullOrEmpty($grateVersion))\n{\n # Add version\n $grateSwitches += \"--version=$grateVersion\"\n}\n\n# Set grate environment\nif (![string]::IsNullOrEmpty($grateEnvironment))\n{\n # Add environment\n $grateSwitches += \"--environment=$grateEnvironment\"\n}\n\n# Set grate schema. Especially useful when migrating from RoundhousE\nif (![string]::IsNullOrEmpty($grateSchema))\n{\n # Add schema\n $grateSwitches += \"--schema=$grateSchema\"\n}\n\n# Display what's going to be run\nif (![string]::IsNullOrWhitespace($grateUserPassword))\n{\n\tWrite-Host \"Executing $($grateExecutable.FullName) with $($grateSwitches.Replace($grateUserPassword, \"****\"))\"\n}\nelse\n{\n\tWrite-Host \"Executing $($grateExecutable.FullName) with $($grateSwitches)\"\n}\n\n# Execute grate\nif ($grateExecutable.FullName.EndsWith(\".dll\"))\n{\n\t& dotnet $grateExecutable.FullName $grateSwitches\n}\nelse\n{\n\t& $grateExecutable.FullName $grateSwitches\n}\n\n# If the output path was specified, attach artifacts\nif ([System.Boolean]::Parse($grateRecordOutput))\n{ \n # Zip up output folder content\n Add-Type -Assembly 'System.IO.Compression.FileSystem'\n \n $zipFile = \"$($OctopusParameters[\"Octopus.Action.Package[gratePackage].ExtractedPath\"])/output.zip\"\n \n\t[System.IO.Compression.ZipFile]::CreateFromDirectory($grateOutputPath, $zipFile)\n New-OctopusArtifact -Path \"$zipFile\" -Name \"output.zip\"\n}\n"
},
"Category": "Grate",
"HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/75443764cd38076d/step-templates/grate-database-migration.json",
"Website": "/step-templates/ca23d18f-ab03-403d-bfb8-3ff74d3ddab3",
"Logo": "iVBORw0KGgoAAAANSUhEUgAAADIAAAAyCAMAAAAp4XiDAAAC91BMVEUAAAAAAAAAAABVVf9AQECAQP8zMzMrKyskJCQkJEkgIEAcHDkzGjMuLi50XegVK0AkJCQkNzciIjMgMEAbKDYoKCgmJiYkJDEjIy4rKysfMz0pKSknJycmJi8bLkAkJC4jIywpKSknJy4eLTwmJi0kJCwrIyspKSl1U+ooKCgnJy4mJi0kJCsqJCopKSknJywmJisgMDolJSsfLj0pJClzVetxU+smJisgLjsoJC0oKCxzUu9xVe90VOsnJytyVOweMTwmJilxU+wpJSwoJCwnJysmJipzVO4fMDsmJilyU+4lJSx0Uu4eLzxyVO4oJCsdLjsnJysmJiwoJSsnJysnJyomJixyUu0mJitxVO4oJSsfMDwnJypzVO4eLz0nJywmJisgMDsnJSonJywmJiseMD0mJiseMDwoJitzU+4oJSpyVO5xU+4nJyxyU+4mJitxUu4mJisoJitxU+weLz0nJSxyU+wnJyxzU+1yUu1zU+0fLzxxUu1xU+4nJysmJitzU+4oJipyUuwfLztyUu1yVO0nJysnJysmJiwnJitzVO0oJitxU+1yVO0nJSsnJypyU+1yVO4mJityU+5yU+4nJityU+wnJyxyUu0oJityU+0fLzxzUu0mJityU+xyUuwnJysmJityU+1zUu0nJionJSsnJytyVO1yU+5yU+5yVO4oJityU+1xU+0fLzwnJSsnJysmJisoJiwnJisoJiwfLzwnJisfLzwnJisnJytzUu0mJixyU+0fLzxxU+0fLjwnJywoJisnJisnJityU+0mJityU+1yU+0nJityU+1xU+1yU+4nJityU+wfLzwnJityU+0nJisnJisfLzwnJisnJysfLzwnJisnJityU+1yU+0nJisfLzwnJisnJisfLj0nJitzU+0nJisnJityU+0nJityU+0nJityU+0nJisnJysnJisfLzwnJisnJisfLzwnJityU+1yU+0nJisnJisnJityU+0fLzwnJisfLzwnJityU+3///8ZordNAAAA+XRSTlMAAQIDBAQFBgcHCAkKCwsMDg4PEBMTFBUWGBkZGhscHB0fISIiIyQlJSYnKCorLC4vMDAyMjM0Njg5Oj4/QEJDRERERUZISUlKSkpLS0xMTU5OUVNUVVdXWFhZWltbXFxeX2FiZGVlZmZmZ2doaWlqamtsbG1ubm9vcHFyc3V3eHh5eX1/gIOEhoiMjo6PkJGRkpOTlJWWmJianZ6en6Okpaamp6mrrK6vsLG0tba3uLm6u73Bw8PFxcbGx8fIyMvNztDR09TU1dbZ2tvc3N3d3d7f4ODh4+Xm5ufp6urr7Ozs7u/x8vLz8/T19vf3+Pn5+fr7/P39/v5vNae6AAAAAWJLR0T8PA6jfwAAAoRJREFUGBntwXdUTXEAwPHvC2XvrZS9s0f2yki2jGwZ2bPszUP23p69x8MzIyuUFdmbSJSerMu9v390XjfnvI7ueQd/9vmQ4m/lW/0o9uIoe5Io4Ehy8oZHLhq7/ecWHdaMJpKzMbYCMEVpjGvbjN41oWxPr+zgfinEyxlyd+7oRBIO0WuJl9bLFX2EKW6+3drvIU/fVOJwTGyYO82jnj6P6Yu1MkofVHplbgYKHepGhvCdYDRB1ojNqXXz4pywUl7pDK3DwsIWo/+WiXh2hRvdOQVGE3RQGhQtWl3phZU8ynQo5uv7aiN6M+CwMjLu8sNAMJrAT3kTEW8k1s5ftgccv05FbwaG/mxnz5FAMJrAW3HmD9oqO1wodTLSGb0ZmPMhEyUiA2HfjdTkjl6mo8XR0iQxNEYxKy8agd4MlIu6f/zhhUDoo0Q1wTvu3oXvm+1IKpe3X7uMgGt74rn4DHJyqwtU8SkOzv2Hu5Hi/yg5bKnBsHRISWy2RhIW0ipsJX4jy0fZ4seJbGi5KlRXoMcAizFyV7S0GrEn9PXr0N3DWpFIJ/dDiwhe7t+9u/+KYIFq4i352Vk0SEIlocpxTv7UBg2T3gmLtxNIlPNYS7Ts9fQYPWvWaA/PvdhKiM/Xg4KufxaCBJkb+s+e7V8vM8naJlRbsZj8Xli8m0Syqs3YtH//pmlVSSAJlYStngjVY7RUXp+ORLUXXpGEkIIX1EJDxZfywfSovuwa59G0qef43V/QcFeW5ZmoxG9o6SfrSLRKEhbSCrT0lpvVt6gBAXUGLzEYlgysHYCW/A/kBD+yIMTNAwbDgdtCoCldkQQFIUioTmOrNF02nLl27cy6TqlI8Q9+AUpiXLmlXI7qAAAAAElFTkSuQmCC",
"$Meta": {
"Type": "ActionTemplate"
}
}
Page updated on Monday, October 17, 2022