Continuous integration

  1. Introduction to database continuous integration
  2. How to setup continuous integration for your Oracle Database

1. Introduction to database continuous integration

Continuous integration (CI) is the process of ensuring that all code and related resources in a development project are integrated regularly and tested by an automated build system. Code changes are checked into source control, triggering an automated build with unit tests and early feedback in the form of errors returned. A stable current build is consistently available, and if a build fails, it can be fixed rapidly and re-tested.

A CI server uses a build script to execute a series of commands that build an application. Generally, these commands clean directories, run a compiler on source code, and execute unit tests. However, for applications that rely on a database back-end, build scripts can be extended to perform additional tasks such as creating, testing, and updating a database.

The following diagram illustrates a typical integration process. The automated continuous integration process begins each time the server detects a change that has been committed to source control by the development team. Continuous integration ensures that if at any stage a process fails, the ‘build' is deemed broken and developers are alerted immediately.

Diagram: database continuous integration process

Database code

Database code is code, and should therefore be treated in the same way as your application code. However, the principal difficulty underlying continuous integration for databases is the lack of a simple way to keep a database in source control and deploy it to a target server.

The database is unlike application code in as much as it contains a state that needs to be preserved after an upgrade. Where a production database already exists, DML and DDL queries modify the existing state of a database, and unlike for application code, there is no source code to compile. Migration and deployment therefore rely on creating upgrade scripts specifically for that purpose.

The lack of database source code makes it difficult to maintain a current stable version in source control. Creation and migration scripts can be checked into the source control repository, but despite its importance, the disciplined creation and on-going maintenance of these scripts is often not considered to be a core part of the database development cycle.

Migration scripts may contain ALTER and UPDATE statements to update the target version of the database with the development version; alternatively, the scripts may create a new database. Where changes are deployed to an existing database, all differences and dependencies must be accounted for. In some production deployments, this involves multiple targets with different schemas and data. In either case, the manual process is time consuming, prone to errors, and one that should not be left unresolved at the end of the project cycle.

Benefits of Database CI

1. Keeping a database up-to-date

Databases may figure in your CI process simply because the application code requires there to be a database present to function correctly. The database schema version corresponds to an analogous application code version. Any changes to the application code or the database structure could in theory break the system and should consequently trigger the CI process.

Once a database is maintained in source control, Red Gate tools are able to build a clean database from its source files to accompany the application build.

If you already have internal test databases that need to match the development databases, you can keep them up-to-date with the latest version using continuous integration.

2. Testing the database creation script

An artifact of the database build process, the database creation script, is one that builds the database from scratch. This is useful not only for when a test database needs to be built, but also if new installations of the application are required, for example, to new customers.

3. Testing the database upgrade script

Unlike for application code, upgrading a production database isn't a simple case of replacing it with a fresh copy. Databases have a mission critical state that needs to be preserved.

Safeguarding existing data is the most challenging task to be faced during the upgrade process. This is why it is a highly recommended best practice to repeatedly test the deployment script as part of the CI process and ensure that a working upgrade script can be generated at all times.

In order to test the upgrade, it is necessary to create a database at the version corresponding to the existing production database, and not just create a new database representing the latest version. Using Red Gate tools, the deployment script is generated, applied against the production-level CI database, and subsequently validated against the expected target version. If this validation fails, the failed upgrade process should be regarded as a ‘broken build', and measures should be taken to troubleshoot and promptly resolve the issue.

Using the Deployment Suite for Oracle for CI

The Deployment Suite for Oracle contains command line tools to handle the scripting and deployment process, and enables easy database source control.


2. How to set up continuous integration for your Oracle Database

This page explains how to setup continuous integration (CI) for your Oracle database with the Deployment Suite for Oracle and TeamCity or Jenkins (see highlighted notes) but the process and command line hooks explained below should help you get set up with any CI system.

If you're not ready to setup continuous integration you can learn more about the benefits it brings to database development.

Prerequisites

1. Install the Schema Compare for Oracle and Data Compare for Oracle command line interface

To run Schema Compare for Oracle and Data Compare for Oracle from the command line on a build server, you need a SQL Automation Pack (licensed per build agent). This will enable the TeamCity or Jenkins build agent to call the Schema Compare and Data Compare commands.

2. Install Source Control for Oracle

If you're using SVN or TFS for your version control system, Source Control for Oracle removes the manual processes for source-controlling database changes.

All three tools are included in the Deployment Suite for Oracle:

Video: setting up CI for Oracle with Jenkins

This video guides you through setting up continuous integration for your Oracle database with Jenkins.

Walk through: setting up CI for Oracle with TeamCity or Jenkins

In the following example we're using TeamCity with notes on Jenkins. However, the process and command line hooks explained below should help you get set up with any CI system.

1. Create a new project

In Jenkins this is called a job.

Screenshot: Step 1. Create a new project in TeamCity (job in Jenkins).

2. Create a build configuration

Add artefact paths to produce reports and a deployment script.

In Jenkins this is a post build step to archive files.

PreDeploy.html => Reports
										PreDeploy_images => Reports\PreDeploy_images
										UpgradeScript.sql => Scripts
										PostDeploy.html => Reports
										PostDeploy_images => Reports\PostDeploy_images
Screenshot: Step 2. Create a build configuration.

3. Create and attach a version control Settings root

Specify a custom checkout path to use in build steps later

In Jenkins this a custom workspace, under advanced options.

Screenshot: Step 3. Create and attach a version control Settings root.

4. Add build steps

In Jenkins these are called Windows batch commands.

PreDeployReport (compares the contents of svn with a live database and generates a report of the differences). Command Line Executable:

C:\Program Files\Red Gate\Schema Compare for Oracle 3\SCO.exe

Parameters:

/source C:\DatabaseCI{WIDGET} /target User/Pass@host{WIDGET} /includeidentical /report:"PreDeploy.html" /reporttype:Interactive

CreateScriptAndDeploy (create the SQL script and upgrade the target database). Command Line Executable:

C:\Program Files\Red Gate\Schema Compare for Oracle 3\SCO.exe

Parameters:

/source C:\DatabaseCI {WIDGET} /target User/Pass@host{WIDGET} scriptfile:UpgradeScript.sql /deploy

Execute: Only if all previous steps were successful

PostDeployReport (compares the contents of svn with a live db and generates a report of the differences). Command Line Executable:

C:\Program Files\Red Gate\Schema Compare for Oracle 3\SCO.exe

Parameters:

/source C:\DatabaseCI{WIDGET} /target User/Pass@host{WIDGET} /includeidentical /report:"PostDeploy.html" /reporttype:Interactive

Execute: Only if all previous steps were successful

5. Set build failure conditions

Turn off 'fail if build process exit code is not zero'.

Screenshot: Turn off 'fail if build process exit code is not zero'

6. Add a new trigger to build on each check-in

Creation scripts checked in to version control need to be generated by either Source Control for Oracle or Schema Compare for Oracle.

7. A build will create a deployment script and reports as artifacts

A build will generate reports and deployment scripts as artifacts.