Welcome to part 1 in this short series, where I explain how to set up a continuous integration process for Oracle databases. In part 2 I’ll set up a release management process that allows a user to deploy changes to staging and live databases at the click of a button.

Objective for part 1

Whenever a developer commits a change to source control, it’s automatically deployed to a shared integration environment for testing. (The testing itself isn’t covered here.) The task outputs an upgrade script and a deployment package.

Why’s this worth doing?

The earlier you find bugs or problems, the better. If you can fix issues before you context switch to your next task, you’ll do it more quickly and easily.

Often the alternative is to only hear about about problems when your customers contact your support team. When the dev team eventually finds time to work out what’s causing the issue, the developer who wrote the code isn’t around, so someone else has to unpick their code. It’s complicated, slow and generally unpleasant.

Continuous integration helps you clean up code early.

By automatically deploying to an integration environment, you are testing your deployment process every time you commit. You also integrate your code with code written by your team. And, you get a great platform for automated unit tests, integration tests, or performance tests etc.

You can also create artifacts which lay the foundations for a reliable release management process, saving time, reducing manual deployment steps, and increasing reliability and visibility. This reduces cycle time so you can be much more agile, and responsive to business needs. I’ll explain how to set up a release management process in part 2 of this series.

This blog post is split into three parts:

1. Making it work from the command prompt
2. Automating the commands with Bamboo
3. Parameterising the scripts with Bamboo variables to decouple config and process

Requirements

1. Redgate Schema Compare for Oracle
   You’ll need this to script out your schema objects.
2. Redgate Source Control for Oracle
   You’ll need this to get your schema objects into a source control system (eg TFS, SVN, Git). This tutorial assumes you already have this set up. (Instructions here).
3. Atlassian Bamboo
   This tutorial uses Bamboo, but I’ll try to explain the set-up in a way that lets you copy it on other automation platforms (eg Jenkins, TeamCity).
   TFS Build is wildly different. If you use TFS Build, you’ll need to wrap these steps into a C# project and call it with MSBuild.
   This tutorial assumes you’ve already got a build server set up.
4. NuGet
   You’ll need to install NuGet on your dev machine and on the build agent.

 

Deploying from the command prompt

Before you automate a task, you need to check you can complete it manually, so you understand the process and can troubleshoot errors.

The first thing our automated build will do is check out the source code and deploy it to an integration database, using Schema Compare for Oracle. So, let’s simulate this process. First we’ll create a script representation of a dev schema on disk, then we’ll deploy the objects to an integration environment.

If you prefer, you can skip steps 2 to 5 by checking out some existing schema scripts from your source control system. (Assuming they have been committed to source control using Redgate Source Control for Oracle).

1. Create two empty schemas to represent a dev and an integration schema.
   Add a simple table or procedure to the dev schema. Leave the integration schema empty.
2. Open Redgate Schema Compare for Oracle’s GUI. For the source, enter details of the dev schema.
   For the target, select Scripts Folder and enter the path to an empty directory.
3. Click Compare Now.
   The diff report shows that the objects in the dev schema don’t exist in the target directory.
4. Select all the objects, then click Deployment Wizard.
5. Go through the wizard. You can explore the options, but the defaults are fine for simple databases.
   The wizard creates a script representation of the dev schema in the directory you specified:
6. Now we have a script representation of the schema, we can deploy it to the integration
   Open a command prompt and run the following command (subbing in the details of your schema):

C:\Users\AlexYates> cd C:\Program Files\Red Gate\Schema Compare for Oracle 3

C:\Program Files\Red Gate\Schema Compare for Oracle 3>SCO.exe /source=C:\file\path\to\schema\scripts{schemaName} /target=dbUsername/dbPassword@tns{integrationSchema} /deploy

The file path for the /source is the directory containing DatabaseInformation.xml. The /schemaName is the name of the directory containing the scripts. In my example I used this command:

C:\Program Files\Red Gate\Schema Compare for Oracle 3>SCO.exe /source=C:\Users\RedGate\Documents\temp{WIDGETDEV} /target=System/******@localhost{WIDGETINTEGRATION} /deploy

This updates the integration schema to match the scripts folder on disk.

If you want to experiment with creating artifacts (eg upgrade scripts, diff reports), explore the command line switches in the Schema Compare for Oracle documentation.

We’ve deployed to integration. Next, we want to create a NuGet package that we can hand to a release management tool for deployment.

1. Add a .nuspec file to the files we want to package up. This is an index, defining which files go in the package. We want all the files, so our .nuspec file’s very simple.
   Create a new file in your temp directory called yourDatabase.nuspec (next to the DatabaseInformation.xml file) and copy this code into it. Don’t forget to sub in your own details.
   The directory should now look like this:
2. Open up a command prompt and execute the following commands, (substituting the path to your .nuspec file):

C:\Users\RedGate> “C:\Program Files (x86)\NuGet\nuget.exe” pack C:\Users\RedGate\Documents\temp\widget.nuspec /version 0.1

The NuGet package should be created. If you install NuGet Package Explorer you can open the package to see what’s inside:

Automating the commands with Bamboo

Now, let’s automate that process to happen every time we commit to source control.

This section is split into the following six steps:

1. Connect dev schema to source control
2. Create a build plan and link to source control
3. Add a task to deploy the schema
4. Add a task to create a NuGet package
5. Manage your artifacts
6. Modify the trigger

They’ll be very similar if you use either Jenkins or TeamCity; I’ll explain where Jenkins or TeamCity differ from Bamboo.

Connect dev schema to source control

Using Redgate Source Control for Oracle. See these instructions. Alternatively, if you already have a database in source control you can try deploying that to an integration schema instead.

Create a build plan

This is the equivalent to creating a new configuration in TeamCity or a new job in Jenkins.

To do this, log on to Bamboo as an admin. On the top menu, select Create > Create a new plan:

For this example, I’ll create a new project called “Widget” (Bamboo projects are like TeamCity projects. There isn’t a Jenkins equivalent but you can think of a project like a set of jobs that relate to each other.). I’ll call the plan “Schema CI build”.

Once you’ve filled out the form, click Configure plan.

Add a task to deploy the schema

Now you have a build plan, we need to tell it what to do.

The next screen shows the Default Job. (Jobs are equivalent to Build Steps in TeamCity or Jenkins.)

You’ll see that a Source Code Checkout task has been added by default, which is useful. This task checks out the latest version of your .sql files.

The next thing to do is call the Redgate Schema Compare for Oracle command line, to compare the latest version to the current integration schema, and deploy any changes.

To do this, click Add task and select the Script option. Give the task a descriptive name and select Run as PowerShell script. (Jenkins users might want to look at the PowerShell plugin. It’s pretty popular, but I haven’t used it and can’t vouch for it. In TeamCity simply select a PowerShell build step.)

It’s best to use a PowerShell script because Redgate Schema Compare for Oracle returns a 61 exit code if it spots changes and deploys them successfully. Bamboo will see a non-zero exit code and assume the build has failed. PowerShell allows us to handle this fairly smoothly:

# Configuration

$schemaComparePath = 'C:\\Program Files\\Red Gate\\Schema Compare for Oracle 3\\SCO.exe'

$targetSchemaName = “TargetSchemaName"

$schemaRelativePath = "relative\path\from\checkout\to\schema\scripts"

$tns = “OracleTns”

$username = "Username"

$password = "Password"

$sourceName = "SourceSchemaName"

 

# Logging config variables for troubleshooting

Write-Output "Using the following variables:"

Write-Output "schemaComparePath: $schemaComparePath"

Write-Output "schemaRelativePath: $schemaRelativePath"

Write-Output "targetSchemaName: $targetSchemaName"

Write-Output "tns: $tns"

Write-Output "username: $username"

Write-Output "sourceName: $sourceName"

 

# Deploy changes using Redgate Schema Compare for Oracle

Write-Warning "Upgrading $targetSchemaName"

& $schemaComparePath -source "${bamboo.build.working.directory}\$schemaRelativePath{$sourceName}" -target "$username/$password@$tns{$targetSchemaName}" -deploy -scriptfile "${bamboo.build.working.directory}\artifacts\upgradeScript.sql" -report  "${bamboo.build.working.directory}\artifacts\diffReport.html" -reporttype Simple -includeidentical | Out-Host

 

# Logging the Schema Compare exit code and path to artifacts

Write-Output "Schema Compare for Oracle exited with code $lastExitCode"

Write-Output "UpgradeScript and diff report created and saved to ${bamboo.build.working.directory}\artifacts"

 

# Exit code 61 is simply telling us there are differences that have been deployed.

if( $lastExitCode -eq 61)

{

exit 0

}

Paste the above script into Script body. Tweak the top section of the script by adding the specifics of your own environment, then hit Save.

You’ll notice that I’ve used a Bamboo system variable in this script: ${bamboo.build.working.directory}. Jenkins or TeamCity users: substitute this with the TeamCity or Jenkins equivalent.

It’s not great to hard-code database passwords and config into the script. We’ll parameterise these later, but right now let’s focus on getting something working.

With that in mind, let’s run it to see if it works. To do that, first enable the plan by clicking Build from the top menu and select All build plans. Then click the  button on the right, in your project’s row:

It’ll turn into a play button; click it, which runs a build. An icon in the build column indicates that a build’s in progress, alongside a build number. You can click the number to see information about this run, including build logs.

Check this all worked by seeing if the Integration schema has been updated to match source control. If it isn’t, investigate the build log to check the variables were correct, and to see if Redgate Schema Compare for Oracle logged any interesting errors/exit codes. (Exit code documentation can be found here.)

You can see, in the first few lines of the logs, where the build working directory is. You may need to go to the logs tab and click view (right side of screen under “Expand All”). After running a build, you can find your source code and an Artifacts directory (containing a diff report and, if there were changes, a copy of the upgrade script) in this directory.  We’ll discuss how to archive our artifacts neatly later.

If you are having problems try using the Schema Compare for Oracle GUI to compare the working directory subfolder (which contains the DatabaseInformation.xml file) to the integration schema and see if this throws up any more information. Check that the details you enter in the Schema Compare for Oracle GUI match those in the script.

Assuming that you have this working successfully, let’s move on.

Add a task to create a NuGet package

Before we add the Bamboo task to package up your scripts add a .nuspec file to source control like we did before. You’ll need to use the default tooling of your source control system to achieve this.

Once done, add another task to the default job. To do this, from the top menu click Build > All build plans. Then click the pencil (edit) button next to your project. Click Default Job and make sure you have the Tasks tab open.

Click Add task and select Command. Give the task a sensible description and (assuming you havne’t already added nuget.exe) click Add new executable. For Executable label enter nuget.exe and for Path provide the path to it, by default: “C:\Program Files (x86)\NuGet\nuget.exe”. (Download NuGet here)

Enter the following under arguments (providing your own relative path). This creates a NuGet package based on the latest source code each time you run a build:

pack ${bamboo.build.working.directory}\relative\path\widget.nuspec /version 0.${bamboo.buildNumber}

TeamCity and Jenkins users: add a batch command build step etc and substitute the Bamboo variables with the Jenkins/TeamCity equivalents.

Click Save.

Run another build using the run button in the top right corner. Once the build is complete, the last line of the build output should show where the package has been built to. Navigate to this location and open up the package with NuGet Package Explorer, so you can see it contains your database source code.

Manage your artifacts

The build process is working, but the process to recover build artifacts is a little clunky. Also, these artifacts are overwritten every time you run a build, so we need a way to persist them.

Edit the Default Job again, and open the Artifacts tab.

Create three definitions by clicking Create definition:

1. With the name NuGet, a blank location (as this is the relative path from the workspace), the copy pattern *.nupkg, and marked as shared.
2. With the name Diff report, the location artifacts, the copy pattern *.html, and not shared.
3. With the name Upgrade script, the location artifacts, the copy pattern *.sql, and not shared.

The NuGet package is shared so it can be used in a deployment project. We don’t need to share the other artifacts as they’re just for reference, and won’t be used in deployment. (If you want to use the upgrade script in a deployment project you should market as shared but I won’t be using it in this series).

Run another build. This time, you’ll see artifacts available for download in the artifacts tab. You might not see an upgrade script – it’s only created if changes are detected.

Modifying the trigger

The CI build now works, and is triggered to run whenever a change is detected. But the default trigger only polls every three minutes, and that’s ages to be waiting for feedback. I’m not that patient, so I like to increase the polling rate. (If you’re working with your live source control/Bamboo instances, check with the administrators first!)

Edit your build plan again (at the top of the page, click Build > All build plans, and click the pencil by your project.) On the Plan configuration page, click Triggers.

Here you’ll see a default Repository Polling trigger. (You could add more triggers if you wanted.) Click on this Repository Polling trigger and change the polling frequency to 10 (it’s in seconds).

Now commit a change to source control – and watch in Bamboo as everything *just works*. (Hopefully!)

Assuming it’s working, move on to the next section.

Parameterising the scripts with Bamboo variables to decouple config and process

At this point you’re basically done, apart from a little bit of tidy-up: the build works, but it could be improved. For example, we hard-coded a password into the deployment script!

Both Jenkins and TeamCity have equivalent variable functionality.

We’ll use Bamboo variables to separate our configuration details (eg schema names, passwords) from our build scripts, so we can re-use them for other databases. Once you set variables in Bamboo, you can reference them in build scripts.

Variables can have different scopes. Any Bamboo project can use global variables, but you can also have Plan variables or Build-specific variables. (Official Bamboo documentation here.)

For now let’s just add everything as Plan variables for simplicity. Edit your build plan again, and this time open the Variables tab. Enter the following variables:

• OracleTns
• schemaRelativePath
• OracleDbUser
• OracleDbPassword
• SourceSchemaName
• OracleDbIntegrationSchema

My variables looked like this (note that you can hide passwords):

Now, updated your build script (that deploys the changes) to use these variables. Select the Stages tab, edit the default job again, and paste in this script (also available on my GitHub):

# Configuration

$schemaComparePath = 'C:\\Program Files\\Red Gate\\Schema Compare for Oracle 3\\SCO.exe'

$schemaRelativePath = "${bamboo.schemaRelativePath}"

$targetSchemaName = "${bamboo.OracleDbIntegrationSchema}"

$tns = "${bamboo.OracleTns}"

$username = "${bamboo.OracleDbUser}"

$password = "${bamboo.OracleDbPassword}"

$sourceName = "${bamboo.SourceSchemaName}"

 

 

# Logging config variables for troubleshooting

Write-Output "Using the following variables:"

Write-Output "schemaComparePath: $schemaComparePath"

Write-Output "schemaRelativePath: $schemaRelativePath"

Write-Output "targetSchemaName: $targetSchemaName"

Write-Output "tns: $tns"

Write-Output "username: $username"

Write-Output "password: has been set to the bamboo variable OracleDbPassword"

Write-Output "sourceName: $sourceName"

 

# Deploy changes using Redgate Schema Compare for Oracle

Write-Warning "Upgrading $targetSchemaName"

 

& $schemaComparePath -source "${bamboo.build.working.directory}\${bamboo.schemaRelativePath}{$sourceName}" -target "$username/$password@$tns{$targetSchemaName}" -deploy -scriptfile "${bamboo.build.working.directory}\artifacts\upgradeScript.sql" -report  "${bamboo.build.working.directory}\artifacts\diffReport.html" -reporttype Simple -includeidentical | Out-Host

 

# Logging the Schema Compare exit code and path to artifacts

Write-Output "Schema Compare for Oracle exited with code $lastExitCode"

Write-Output "UpgradeScript and diff report created and saved to ${bamboo.build.working.directory}\artifacts"

 

# Exit code 61 is simply telling us there are differences that have been deployed.

if( $lastExitCode -eq 61)

{

exit 0

}

You could add a variable for the relative path in your NuGet command, but this is probably different from the relative path to your schema scripts, so for now I haven’t.

Run another build to check it still runs successfully. Now, you have a template you can copy for other databases. Just use variables to set the configuration appropriately, and you’re away.

But you should think about how to build on this further. What sorts of tests can you automate on that integration environment? And how can you promote these changes to your staging and live schemas?

In my next blog post I’ll tackle deployments.

Resources

The completed scripts: https://github.com/Alex-Yates/Powershell-for-Oracle-Database-CI-with-Bamboo

Redgate Deployment Suite for Oracle (for your dev machine): http://www.red-gate.com/products/oracle-development/deployment-suite-for-oracle/

Redgate DLM Automation Suite for Oracle (for your Bamboo build agent): http://www.red-gate.com/products/oracle-development/dlm-automation-suite-for-oracle/

All Redgate Oracle tools come with a fully functional 28-day free trial period.

Schema Compare for Oracle command line switches documentation: http://documentation.red-gate.com/display/SCO3/Command+line+switches