Database Builds

Why would you want to place a database under source control?

• • Reproducible databases for testing.

  • Easier to compare environments

  • Create a Continuous Integration environment, triggered by changes in SCM

  • Central location where developers make their changes.

  • Automated updates to databases

Feature List

Process Overview

• Extract DDL from database

  • Populate SCM from existing production DB

    • Merge manual DB changes DB into SCM (emergency fixes or hacks)

    • Extracting into a branch can support audit process

  • Build Deliverable

    • Creates package that applies changes to a DB

    • Targets that can be applied:

      • Clean

        • Removes all traces of a Project from DB.

        • Primarily for build and development.

      • Deploy

        • Apply Bootstrap patches

        • Apply baseline objects that arent re-created by an update.

        • Only relevant to new DB or after a Clean

      • Update

        • Apply Bootstrap patches

        • Apply SYS and SYSTEM Patches

      • Apply Patches

      • Apply Code

      • Apply GRANTS, PRIVS and SYNONYMS

      • Compile invalid Objects

      • Compile and Validation

      • Apply GRANTS, PRIVS and SYNONYMS

      • Compile invalid Objects

      • Validate

        • No invalid objects

        • No failed grants, privs or synonyms

Creating a Database Project in SCM

A database project implies the generic layout in SCM used to manage a logical database. A common seto build scripts are used to populate the project and subsequently create a build artefact. The DDL object in a database are treated as either invariable or replacable. Invariable objects, such as tables, must be modified by patches, where as replacable objects, such as view and triggers, can be changed directly in SCM.

Taking a copy of a Gold Database

More often than not, there is a production database that needs to be managed, this is refered to as the GOLD database as it is always taken as "correct".

• • EXPORT/IMPORT: There needs to be a way or extracting the database contents and importing it into SCM. This includes DDL and DATA (segregated into reference and business data)

  • TOKENISE: Many of the exported objects can have database instance specific content e.g. tablespace names, directory paths etc. These need to be tokenised so that they can be replaced by instance specific content (A production environment is likely to be set up quite differently to a test environment).

  • MD5: For automated comparison of databases, an MD5 of the exported object can be useful (The tokenisation mentioned above helps to make the files consistent). To ensure that code formatting doesnt skew the result, all white space is removed and the characters transformed to lower case before the MD5 is calculated.

Building Database Project to create a Build Artefact

Building the Database Project results in a Build Artefact. This contains a complete instance of the the database at the point of the build and can be used to create a new database or update and existing one.

The build artefact should be uploaded to a central location (e.g. Nexus or a fileserver), so that it can automatically be applied to environments by a build system such as Jenkins.

Build Artefact targets

A build artefact has a set of embedded targets that can be used to modify a database. Some can be used to tear down an existing database and re-build it, others are to update an existing database.

• • clean: Delete the working files from previous target invocations

  • nuke: Delete the contents of the database that this build artefact creates

  • deploy: Apply invariable objects from gold database e.g. tables and constraints

  • update: Apply replaceable objects e.g. procedures and triggers