This gist is not maintained. If possible please use this webpage instead to get the FlyWay Octopus step template: https://library.octopusdeploy.com/step-templates/3868c17c-8471-4805-89be-e99513afd59a/actiontemplate-flyway-migrate

JSON for FlyWay migrate Octopus Deploy plugin 20170515

{
  "Id": "3868c17c-8471-4805-89be-e99513afd59a",
  "Name": "Flyway Migrate",
  "Description": "Deploy a database using Flyway",
  "Version": 1,
  "ExportedAt": "2015-11-13T10:25:35.129+00:00",
  "ActionType": "Octopus.Script",
  "Author": "Alex-Yates",
  "Parameters": [
    {
      "Name": "FlyWayPackageStep",
      "Label": "Flyway package step (required)",
      "HelpText": "A previous deployment step which deploys a NuGet package containing your FlyWay project.\n\n\n_Requirements:_\n\n1. The NuGet package must contain your FlyWay project.\n\n2. The flyway.conf file needs to contain the correct information for your target database. This can be achieved using the Octopus Deploy \"Substitute variables in files\" feature in your \"Deploy a NuGet package\" step:\nhttp://docs.octopusdeploy.com/display/OD/Substitute+Variables+in+Files\n\n3. You should be able to execute your deployment simply by running \"flyway migrate\" from the flyway.cmd directory.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "StepName"
      }
    },
    {
      "Name": "RelativePath",
      "Label": "Relative path to flyway.cmd (optional)",
      "HelpText": "If your FlyWay project is not in the root of your NuGet package specify the relative path here.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "locations",
      "Label": "-locations (relative path, optional)",
      "HelpText": "Comma-separated list of locations to scan recursively for migrations. The location type is determined by its prefix.\nUnprefixed locations or locations starting with classpath: point to a package on the classpath and may contain both sql and java-based migrations.\nLocations starting with filesystem: point to a directory on the filesystem and may only contain sql migrations.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "targetUrl",
      "Label": "Target -url (required)",
      "HelpText": "The url of the target database in the format specified in the default flyway.conf file.\n\nE.g.\nSQL Server: jdbc:jtds:sqlserver://host:port/database\nOracle: jdbc:oracle:thin:@//host:port/service\nMySQL: jdbc:mysql://host:port/database",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "targetUser",
      "Label": "Target -user (required)",
      "HelpText": "The username to connect to the target database.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "targetPassword",
      "Label": "Target -password (required)",
      "HelpText": "The password to connect to the target database.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "Sensitive"
      }
    },
    {
      "Name": "runDriftCheck",
      "Label": "Run pre-deploy drift check",
      "HelpText": "Check for drift with Redgate comparison tool.\n\n_Requirements_\n\nRequires that a Redgate command line comparison tool is installed on the target machine.\n\nFor SQL Server: SQL Compare\n\nhttp://www.red-gate.com/products/sql-development/sql-compare/\n\nFor Oracle: Schema Compare for Oracle\n\nhttp://www.red-gate.com/products/oracle-development/schema-compare-for-oracle/\n\nFor MySQL: MySQL Compare\n\nhttps://www.red-gate.com/products/mysql/mysql-compare/\n\nAll tools include a free trial licence for a limited period of time.\n\n_Limitations_\n\nSQL Server, Oracle and MySQL only",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Name": "comparePath",
      "Label": "Path to Redgate comparison tool (required for drift-check)",
      "HelpText": "Checking for drift requires a database comparison tool. This template uses the Redgate tooling.\n\nPlease provide the path to a Redgate command line tool. The default paths are\n\nSQL Compare (SQL Server):\n\nC:\\Program Files (x86)\\Red Gate\\SQL Automation Pack 1\\SC\\SQLCompare.exe\n\nSchema Compare for Oracle:\n\nC:\\Program Files\\Red Gate\\Schema Compare for Oracle 3\\SCO.exe\n\nMySQL Compare:\n\nC:\\Program Files (x86)\\Red Gate\\MySQL Compare 1\\MC.exe",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "shadowUrl",
      "Label": "Shadow -url (required for drift-check)",
      "HelpText": "The url of the target database in the format specified in the default flyway.conf file.\n\nE.g.\nSQL Server: jdbc:jtds:sqlserver://host:port/database\nOracle: jdbc:oracle:thin:@//host:port/service\nMySQL: jdbc:mysql://host:port/database",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "shadowUser",
      "Label": "Shadow -user (required for drift-check)",
      "HelpText": "The username to connect to the shadow database.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "shadowPassword",
      "Label": "Shadow -password (required for drift-check)",
      "HelpText": "The password to connect to the shadow database.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "Sensitive"
      }
    }
  ],
  "Properties": {
    "Octopus.Action.Script.ScriptBody": "$VerboseActionPreference=\"Continue\"\n\n# Declaring the path to the NuGet package\n$packagePath = $OctopusParameters[\"Octopus.Action[$flywayPackageStep].Output.Package.InstallationDirectoryPath\"]\n\n# Delcaring path to FlyWay\n$flywayPath = $packagePath\nif ($relativePath -ne $null){\n    $flywayPath = Join-Path -Path $packagePath -ChildPath $relativePath\n}\n\n$flywayCmd = Join-Path -Path $flywayPath -ChildPath 'flyway.cmd'\n\nif ($locations -ne $null){\n    $locationsPath = Join-Path -Path $packagePath -ChildPath $locations\n}\nelse{\n    $locationsPath = Join-Path -Path $flywayPath -ChildPath \"sql\"\n}\n\n\n# Logging for troubleshooting\nWrite-Host \"*******************************************\"\nWrite-Host \"Logging variables:\"\nWrite-Host \" - - - - - - - - - - - - - - - - - - - - -\"\nWrite-Host \"FlywayPackageStep: $FlyWayPackageStep\"\nWrite-Host \"FlywayPath: $flywayPath\"\nWrite-Host \"LocationsPath: $locationsPath\"\nWrite-Host \"Target DB -url: $targetUrl\"\nWrite-Host \"Target DB -user: $targetUser\"\nWrite-Host \"Run drift-check?: $runDriftCheck\"\nWrite-Host \"Compare tool path: $comparePath\"\nWrite-Host \"Shadow DB -url: $shadowUrl\"\nWrite-Host \"Shadow DB -user: $shadowUser\"\n\nif ($runDriftCheck){\n    # Can't do drift check without a shadow DB\n    if($shadowUrl -eq $null){\n        Write-Output \"Cannot run drift check because Shadow DB not provided.\"\n        $runDriftCheck = $FALSE\n    }\n    # Can't do drift check without a comparison tool\n    if($comparePath -eq $null){\n        Write-Output \"Cannot run drift check as path to comparison tool not provided.\"\n        $runDriftCheck = $FALSE\n    }\n}\n\n\n# Drift check preperation\n$dbPlatform = \"Unknown\"\n$targetDbVersion = 0\n$targetDbPath = \"Unknown\"\n\nif($runDriftCheck)\n{\n    # Determining name and version of target database\n    Write-Host \"*******************************************\"\n    Write-Host \"Determining name and version of target database:\"\n    Write-Host \" - - - - - - - - - - - - - - - - - - - - -\"\n    \n    # Saving target DB info\n    $arguments = @(\n        \"info\", \n        \"-locations=filesystem:$locationsPath\",\n        \"-url=$targetUrl\",\n        \"-user=$targetUser\",\n        \"-password=$targetPassword\"\n    )\n    Write-Host \"Determining version of target database:\"\n    Write-Host \"Executing the following: & $flywayCmd $arguments\"\n    $targetDbInfo = & $flywayCmd $arguments\n    Write-Host \"Target DB info:\"\n    Write-Host $targetDbInfo\n\n    # Finding intended version number of target database\n    $targetDbVersion = ($targetDbInfo | ? {$_.StartsWith(\"|\") } | ? { $_ -notcontains \"No migrations found\" } | % { $parts = $_.Split('|'); New-Object PSObject -Property @{Version = $parts[1].Trim(); State = $parts[4].Trim()}} | ? { $_.State -eq \"Success\" } | Select-Object -Last 1).Version\n    Write-Host \"Target database is at version $targetDbVersion\"\n\n    # Finding connection string for target DB\n    Write-Host \"Target database connection string $targetUrl\"\n    \n    if ($targetUrl -like '*sqlserver*'){\n        $dbPlatform = \"SQLServer\"       \n    }\n    \n    if ($targetUrl -like '*mysql*'){\n        $dbPlatform = \"MySQL\"       \n    }\n    \n    if ($targetUrl -like '*oracle*'){\n        $dbPlatform = \"Oracle\"       \n    }    \n    \n    Write-Output \"Database platform $dbPlatform detected.\"\n}\n\nif ($dbPlatform -eq \"Unknown\" -And $runDriftCheck){\n    Write-Host \"Cannot run drift check!\"\n    Write-Host \"Drift check only supported for SQL Server, Oracle or MySQL.\"\n    Write-Host \"Cannot determine from FlyWay info if your database platform is supported.\"\n    $runDriftCheck = $FALSE      \n}\n\nif ($runDriftCheck -eq $true)\n{\n    # Building a shadow DB for drift check\n    Write-Host \"*******************************************\"\n    Write-Host \"Building a shadow DB for drift check:\"\n    Write-Host \" - - - - - - - - - - - - - - - - - - - - -\"\n    #   In a future version it would be cool to build the shadow DB ourselves\n    #   and to clean up afterwards. For now, however, asking the user to provide \n    #   a shadow DB for us removes the requirement to work out how to build a fresh\n    #   DB on 3 different DB platforms. It also means we don't need to worry about\n    #   credentials etc\n    #   $shadowDbPath = $targetDbPath + \"_SHADOW\"\n    \n    Write-Host \"Cleaning shadow database\"\n    $arguments = @(\n        \"clean\", \n        \"-url=$shadowUrl\",\n        \"-user=$shadowUser\",\n        \"-password=$shadowPassword\"\n    )\n    Write-Host \"Executing the following: &$flywayCmd $arguments\"\n    &$flywayCmd $arguments\n    \n    Write-Host \"Migrating shadow database up to current target version\"\n    $arguments = @(\n        \"migrate\"\n        \"-locations=filesystem:$locationsPath\",\n        \"-url=$shadowUrl\",\n        \"-user=$shadowUser\",\n        \"-password=$shadowPassword\",\n        \"-target=$targetDbVersion\"\n    )\n    Write-Host \"Executing the following: &$flywayCmd $arguments\"\n    &$flywayCmd $arguments\n\n    # Using comparison tool to check for drift\n    Write-Host \"*******************************************\"\n    Write-Host \"Using comparison tool to check for drift:\"\n    Write-Host \" - - - - - - - - - - - - - - - - - - - - -\"\n    \n    function getServer ($connectionsString){\n        return ($connectionsString -split \"/\")[2]\n    }\n\n    function getDbName ($connectionsString){\n        # Note: For MySQL this may return key values as well. e.g:\n        # <database>?<key1>=<value1>&<key2>=<value2>...\n        # Should fix this!!!!\n        Write-Host \"Warning: Function getDbName() does not handle MySQL connection strings fully!\"\n        return ($connectionsString -split \"/\")[3]\n    }\n\n    # SQL Server\n    if ($dbPlatform -eq \"SQLServer\"){\n        \n        # Details for target DB\n        $targetServer = getServer($targetUrl)\n        $targetDb = getDbName($targetUrl)\n        \n        # Details for shadow DB\n        $shadowServer = getServer($shadowUrl)\n        $shadowDb = getDbName($shadowUrl) \n        \n        # Drift report name and location\n        $date = Get-Date -format yyyyMMddHHmmss\n        $reportName = \"FlySQLDriftReport_$targetDb_$date.\"\n        $reportPath = Join-Path -Path $flywayPath -ChildPath \"driftReport\"\n        \n        New-Item $reportPath -type directory\n        \n        $arguments = @(\n            \"/s1=$targetServer\",  \n            \"/db1=$targetDb\", \n            \"/u1=$targetUser\",  \n            \"/p1=$targetPassword\", \n            \"/s2=$shadowServer\",  \n            \"/db2=$shadowDb\", \n            \"/u2=$shadowUser\",  \n            \"/p2=$shadowPassword\",\n            \"/assertidentical\",\n            \"/r=$reportPath\\reportName.html\",\n            \"/rt=Simple\"\n        )\n\n        Write-Host \"Executing the following command: & $comparePath $arguments\"\n        & $comparePath $arguments \n        if ($LASTEXITCODE -ne 0){\n            Throw \"Drift check failed. For details see report here: $reportPath\"\n        }\n        else{\n            Write-Host \"Drift check passed.\"\n        }\n    }\n    \n    # Oracle\n    if ($dbPlatform -eq \"Oracle\"){\n        \n        # \"Target\" and \"Source\" names are very confusing.\n        # Consistently in this PS script I have used target\n        # to refer to the database we intend to deploy to.\n        # At this point we need to use that database as\n        # the source in order to create a roll-back\n        # script should the drift-check fail.\n        # Hence the terms source and target are used\n        # for opposite purposes when using Schema Compare\n        # for Oracle command line tool.\n        \n        # Details for target DB\n        $targetTns = getServer($targetUrl)\n        $targetSchema = getDbName($targetUrl)\n        $source = \"$targetUser/$targetPassword@$targetTns{$targetSchema}\"\n        \n        # Details for shadow DB\n        $shadowServer = getServer($shadowUrl)\n        $shadowDb = getDbName($shadowUrl) \n        $target = \"$sourceUser/$sourcePassword@$sourceTns{$sourceSchema}\"\n        \n        # Drift report name and location\n        $date = Get-Date -format yyyyMMddHHmmss\n        $reportName = \"FlySQLDriftReport_$targetSchema_$date.\"\n        $reportPath = Join-Path -Path $flywayPath -ChildPath \"driftReport\"\n        \n        New-Item $reportPath -type directory\n        \n        $arguments = @(\n            \"/source=$source\",  \n            \"/target=$target\",\n            \"/r=$reportPath\\reportName.html\",\n            \"/rt=Simple\"\n        )\n\n        Write-Host \"Executing the following command: & $comparePath $arguments\"\n        & $comparePath $arguments \n        if ($LASTEXITCODE -ne 0){\n            Throw \"Drift check failed. For details see report here: $reportPath\"\n        }\n        else{\n            Write-Host \"Drift check passed.\"\n        }\n        \n    }\n    # MySQL\n    if ($dbPlatform -eq \"MySQL\"){\n\n        # Details for target DB\n        $targetServerPort = getServer($targetUrl)\n        $targetServer = ($targetServerPort -Split \":\")[0]\n        $targetPort = ($targetServerPort -Split \":\")[1]\n        $targetDb = getDbName($targetUrl)\n\n        # Details for shadow DB        \n        $shadowServerPort = getServer($targetUrl)\n        $shadowServer = ($targetServerPort -Split \":\")[0]\n        $shadowPort = ($targetServerPort -Split \":\")[1]\n        $shadowDb = getDbName($shadowUrl) \n\n        # Drift report name and location        \n        $date = Get-Date -format yyyyMMddHHmmss\n        $reportName = \"FlySQLDriftReport_$targetDb_$date\"\n        $reportPath = Join-Path -Path $flywayPath -ChildPath \"driftReport\"\n        \n        New-Item $reportPath -type directory\n        \n        $arguments = @(\n            \"-verbose\", \n            \"-server1=$shadowServer\",  \n            \"-port1=$shadowPort\",\n            \"-database1=$shadowDb\", \n            \"-username1=$shadowUser\",  \n            \"-password1=$shadowPassword\",\n\n            \"-server2=$targetServer\",  \n            \"-database2=$targetDb\", \n            \"-port2=$targetPort\",\n            \"-username2=$targetUser\",\n            \"-password2=$targetPassword\", \n\n            \"-assertIdentical\",\n            \"-report=$reportPath\\$reportName.html\",\n            \"-reportType=Simple\",\n            \"-scriptfile=$reportPath\\undoDrift.sql\"\n        )\n\n        Write-Host \"Executing the following command: & $comparePath $arguments\"\n        & $comparePath $arguments \n        if ($LASTEXITCODE -ne 0){\n            Throw \"Drift check failed. For details see report here: $reportPath\"\n        }\n        else {\n            Write-Host \"Drift check passed.\"\n        }\n    }\n}\n\n# Executing deployment\nWrite-Host \"*******************************************\"\nWrite-Host \"Executing deployment:\"\nWrite-Host \" - - - - - - - - - - - - - - - - - - - - -\"\n    $arguments = @(\n        \"migrate\"\n        \"-locations=filesystem:$locationsPath\",\n        \"-url=$targetUrl\",\n        \"-user=$targetUser\",\n        \"-password=$targetPassword\"\n    )\nWrite-Host \"Executing the following command: & $flywayCmd $arguments\"\n\n& $flywayCmd $arguments",
    "Octopus.Action.Script.Syntax": "PowerShell"
  },
  "Category": "Flyway",
  "HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates/flyway-migrate.json",
  "Website": "/step-templates/3868c17c-8471-4805-89be-e99513afd59a",
  "Logo": "iVBORw0KGgoAAAANSUhEUgAAAMgAAADICAMAAACahl6sAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAAAA9QTFRF////JCUm19fXZGVmoaKjOvCyiwAABOpJREFUeNrsm4nSoyoQhdPL+z/zpRujICDGGOf2X+ermpqZLMqhVyC+XgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAuBdl/hMyiERFo8tgIWH7K7oSokUBhVbCRNs/OXJ4yPYfoX/k28m56Tt/0Orr/LxzsYoot0P5OMxrZxJ53BZaTCrfpeOLa120RnW7q57NJM2wnzSJNre/VgCYpBcz/Jg5tDMkvhLm3cl/KtxZuDuoL9PV4xm4r+PCPA5z3TNFUQc6Pr79Qc5+ItwPglrlMx186b277HHkP58413EN/Xm4HyfZDyZy8lGlf6njA9+e9TQ/Dvdp0Tt7f5l+7qcZWPjECM+touZX+qFJzjQhZ3rwXnvVzsHPTNKUQb7oXNxvS5TokShpyzldS5wDHWmxu3ud5BkdoxvxLK/2v0Ye/2VJZfqJjvMRe5iCB/FhZYX2EfaDothrE4WGJqkGwM67/R+bMX2L6zC5vyj27JHS48iJttfTmp4WbF2c4lkHjbOwXW9ng7tN0tVh3jwKx+xcKj58TaswfSsafyGJaN6+OQN3e1qf9bFJNO/kconqSEkKEHWdXCf1ezeGmnZXN6uPvNiGVat4i+lJSeNlU2GGrI2g8kN7+ODfrw6mjAc6EtImu6TNkoBFidThfp9JBuVjtcSgGRfhMdTZj7O05lGS3is7obsSV61D1v57G0q3aOjQHlmJ9BYmHkHpraoxvskk9erc3dfnsxx9705yqIOrkr0mDDsfSSIa57qh49p1u+4S1tpV9u4Y36r3IeX8FBNhOizXfbhymcpYb5dnJU+VZZ36Vq1z6VSIbGOvzhMsR6SblZHH3ynhTUb2JvNW8lIt+5E3zpVc/di3Nu8pr7WEuuwberoa8JbtXQaX/TWp13Juz0L2mcvL25ESWa8gpZN5GkmztTRx21vXbKJrJ5H3B9KwzJXSH3Gnkrai7Zd24oVPx/YgHnbCXt/9hsXoLylJnYSPlfPEpMRlljAXaP23uzLhLEHGtf398d46QO3kKL1O5lC6KbmWhe28W8xdV2OoO5ddjnXSuKusIU3WMlYqXN67vZHxun+pKFVlupSwdNHji7bl7PugOJVvFYawcXsDnPtfs7E1wzTZ2LIg4l1FYfoieeVJEDqxC7Q5nHVMdar1Bj5dRZY6mUN54izedlId8t91kCeNuk6wTGoI+1JwGr5k0yeV3ZQeOZFbvIsnJcSyQLPzM1ib6W6t9dC5dS7SSlOD0JkctPQSWq1OnzokNX+ZGkTpdNi63hxjOf3JU4ekw4Vht6yfdVmxVb91RiTy1GnvXInG+PGPryd0EuoR8P790CgxDLIUQ0uaQ4OE+DHWWgx1TTR7HRLDs7TuD9+NVt6e8zz6CuRZ5d7iuvWbN0yDCBm2WbqYRYP8XHFaDTVGhOh80yHGL2FTzk3Z6rCqBwp1ofE2Y5DcK1Qsy/ttbxDP0uLkYGcXzec4QUKd22qYm2/KdT6WZ9XVw7aVwhURmS4NOZxnTbbgg3lWk3w5WM6K7VmntrP+hpAoTxxNd0rDPDo1Sb9BPOuVTx3jJ98lA2v45Psuihp8KbLlrtFihOQVS4l0Y16i6VikNEuRmM/f8noCuhwfatxHVrMIe6Zb9U88oQ4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADgf8p/AgwAsU8XH6dJUA0AAAAASUVORK5CYII=",
  "$Meta": {
    "Type": "ActionTemplate"
  }
}