trunk and branches for database configuration management

Trunk and Branches for Database Configuration Management

Copyright (c) www.scmsupport.com 2012. All right reserved. /11


Robert Berliński, support Mercedes Gavilan Document version 1.7, February 16, 2012

http://www.scmsupport.com/

Table of contents:

PREFACE ................................................................................................................................. 2

CHALLENGES .......................................................................................................................... 2

THE SOLUTION ........................................................................................................................ 2 The experience ........................................................................................................................................ 3 Repository structure ................................................................................................................................ 3

Comments in commits ............................................................................................................................ 4

Patches and Upgrades ............................................................................................................................. 4 The environment ..................................................................................................................................... 5 The workflow .......................................................................................................................................... 6

CONTINUOUS INTEGRATION ................................................................................................. 7

TOOLS ...................................................................................................................................... 8 The DEV environment ............................................................................................................................ 8 Building patches and an upgrade ............................................................................................................ 9

CONCLUSION ........................................................................................................................ 11

REFERENCES ........................................................................................................................ 11

Abstract The document describes the Trunk and Branches approach to Configuration Management for a Database

project. It explains the advantages and challenges of the strategy and provides solutions and describes

the dedicated tools that significantly improved the quality and efficiency of the process.




Preface The document describes author's unique approach to the Configuration Management for a Database

project. And the document is based on the authors' experience. The procedures had been successfully

applied to an Oracle Database project with Subversion repository. It resulted in significant

improvements of procedures and deployment of dedicated tools. The tools help to automate most of the

time-consuming processes and eliminate space for human mistakes. The automation saves more than

70% of Config Manager's time as compared to manual process and about 90% of Administrator's time.

The improved efficiency allows Config Manager and Administrator to handle more projects in the same

time. It also gives the Project Manager more flexibility to plan, schedule and when required dynamically

change the project's goals.

However the work is based on Oracle and SVN, the procedures might be applied to a different database

and/or to a different version control repositories.

Authors assume that the Reader is familiar with the process of using trunk and braches within revision

control repository (e.g. described in the " Trunk and Branches for Configuration Management"

document").

Challenges The trunk and branches approach within a revision control is well known for it's benefits e.g. in the Java

world. The benefits include:

better control and security of the code - separates the production version from the work-in-progress

version, imposes access control and manage read/write rights for each branch and the trunk,

support for parallel projects - allows to separate changes between branches, e.g. distribute changes

based on different delivery date or based on different functionality/skills, improves manageability

for the changes and allows better assignment of tasks to employees, therefore improving the overall

efficiency,

flexibility to satisfied unexpected business requirements to divide scope of a project, postpone or

obsolete selected parts, therefore giving better manageability in terms of time, resources and costs

required for a project.

The goal is to benefit from taking the same approach against a database project. But due to the nature of

a database some issues require to be addressed. Below are some of them:

strategy/granularity for merging - source code merge vs. file merge,

mapping variety of database objects (packages, types, procedures, model, etc.) into version control

repository,

strategy for handling scripts (data-modification and model-modification),

strategy for handling relevant dependencies between model/objects/data changes,

strategy for preserving time-order dependencies between objects that are being changed/removed.

The Solution First of all it is important to focus on the high level aspects of the approach. The experience shows the

following ways are valuable and efficient:

developers check-out and commit changes to revision control repository,

Config Manager collects changes from repository, builds patches and upgrade scripts; the processes

are supported by dedicated tools, then Config Manager tests the patches and upgrades in test

environment,

Config Manager delivers the files of patches and upgrade to Business/Administrator,



the Administrator deploys the changes in straightforward manner by executing the delivered scripts.

The experience

The authors work with an Oracle database. The characteristics of the database are shown below. INDEX 1500

TRIGGER (generated) 600

TABLE 550

SEQUENCE 380

PACKAGE 300

VIEW 50

TYPE 35

FUNCTION 30

PROCEDURE 20

Total lines of code 225000

Table 1. The characteristics of database.

Repository structure

The structure of SVN repository folders maps an Oracle database project. 1. trunk 2. branches

sal<version>

o patches

o scripts

database

docs

schema

dict

updates

data

dict

sc

o sources

Functions

Packages

Procedures

Triggers

Types

Views

Figure 1. The structure of SVN repository (the idea on the left and the implementation on the right).

The SCRIPTS directory contains all scripts and documents required to create and configure a database,

table spaces and a schema. There are also scripts creating roles and privileges and granting permissions

to project schema. The subdirectories are organized to contain the following:

DATABASE – scripts that create a database and table spaces

DOC – documentation that describes configuration procedures for a database; for example: startup

and tuning parameters, coding standard/recommendations for PL/SQL, name convention for

database objects; it is also a place to store the database physical model generated by a data modeling

tool (e.g. Sybase Power Designer, which we use) as well as the html documentation for any table,

etc.



SCHEMA – scripts that create a schema, grant user, create file system directories for database

directory object (e.g. used for exports). The subfolder DICT contains data inserts for application

dictionaries.

UPDATES – three groups of scripts that might be written during a development process (in contrast

to the scripts included in the SCHEMA and DATABASE directories meant to create a database); the

three groups are:

o SC (abbr. schema change) – scripts that change schema, add/modify columns in tables,

add/remove indexes, etc.,

o DICT (abbr. dictionary) – scripts that insert and modify dictionary tables used by an

application, e.g. Countries, Currencies,

o DATA – script that modify user data, e.g. reorganize data according to schema changes.

The SOURCES directory contains sources of the PL/SQL stored objects. Each type of the objects has its

own folder named after the object's type. The structure assumes that specification and body are stored

together in one file. The PL/SQL allows to split these in separate files.

The PATCH directory contains all patch scripts build in order to deploy updates. The changes are

selected based on a range set by two revisions numbers A and B and include all changes committed

between revisions A and B. Scripts are stored in subdirectories named after the branch name followed

by the build sequence number within the branch, for example PATCH_1.1.002 means version 1.1 and

patch number 2.

Experience shows, that the structure is both flexible and effective. It has been in use for the database in

example and it satisfied all needs for changes over two years of development.

Comments in commits

It is important, that every change is referenced by a unique ID. Developers must write the ID in

comments when committing changes. Later the comments are pulled out from repository by an

automatic tool when building a patch. It helps to provide to Business the list of changes delivered by the

give patch.

Patches and Upgrades

Patches and Upgrades are sql scripts meant to deploy changes to a database via SQLPLUS:

Patches deliver changes grouped during development of a branch and aims test regions. There might

be many patches depending on the complexity of a project, e.g. one patch representing each

following week of development.

An Upgrade delivers the sum of changes included in patches and it aims the Pilot/Production

environments. It is important that patches inside upgrade are ordered the same way, they were

deployed in tests.

A Patch includes control/meta files, sources and new scripts:

readme.txt file that describes the installing procedure, usually it is: sqlplus

<schema_user>/<schema_password> @patcher.sql.

patcher.sql script that is the primary script to be executed by Administrator. The script triggers

individual changes and it runs the versionhistory.sql script and a post-installation script.

versionhistory.sql script writes changes to the Version History table.

Post-installation scripts are responsible for re-compilation of the schema and might perform

additional tasks depending on the nature of changes.



Folder for each group of the source objects that changed. The types can be: Functions, Packages, Procedures, Triggers, Types, and Views.

Folders for scripts and changed dictionaries.

A Patch is stored in version repository inside patch subfolder. It includes dependable source objects

from the sources folder and scripts from the scripts folder. The elements are copied to the patch

folder. Therefore a patch always includes everything that is required for deploying changes that belong

to the scope of the patch.

An upgrade is an ordered collection of patches. The order is important to preserve all dependencies. An

upgrade includes control/meta files and the patches subfolders:

README.txt file that describes the installing procedure.

upgrder.sh script that is the primary script to be executed by Administrator. It iterates and

executes each included patch.

When necessary additional scripts, e.g. newdir.sh and newdir.sql that builds file system folders

and sets the relation with Oracle.

The rule of thumb is, that patches should be tested in a test environment before release. The names of

patches include ordering number. The number helps to preserve time-order when including patches in an

upgrade.

The environment

The environment is built based on multiple elements.

Figure 2. The work environment - elements and relations.

The above diagram shows the distribution of regions in the process of Configuration Management:

Starting from the left, there are three IT regions labeled as DEV1, DEV2 and DEV3.

There is SVN repository placed in the central. It provides branches for each DEV region.

Special Hot Fix region serves a shorter path for detection, fixes and tests in case of critical and

urgent production problems.

On the right are business environments: Tests reflecting the IT branches, Pilot and Production.



Please note the straight arrows from left to right. They mark processes (committing changes, building

and delivering patches, building and delivering upgrades) that are automated or supported by dedicated

tools. Elimination of the manual work not only saved time but also helped to eliminate many processes

exposed to human errors.

The workflow

The workflow starts from source code and finish with upgrade delivery to Production. The version

repository plays key role, as it guarantees flow of data between succeeding processes. The idea is to

provide steps meant to transform the level of work starting from source code into patches and upgrades.

Figure 3. The workflow.

Source code changes

There is one DEV* database for each branch shared between all developers. It is used by the developers

to code and test changes. After testing, developers commit changes to SVN. It is possible for a developer

to work with multiple branches. And with the help of additional tools (e.g. an SVN plugin for PL/SQL

Developer) the complexity of work with multiple branches is almost transparent for the developers.

Therefore it allows the optimum assignments of tasks in different branches to developers. When

preparing changes the developers are responsible for resolving file-context conflicts and keeping the

sources in repository in synchronization with the database. They also have to remember to comment

every committed change giving the change number provided by business. It makes possible to track

changes later on and prepare a report of changes included in a patch. The developers are also responsible

for retrofitting changes made in parallel branches and/or the Hot Fix region.

Changes delivered by Scripts

The developer might also prepare scripts for updating model/data. The developers doesn't commit the

scripts to SVN but sends them to the Config Manager, who applies to them a special name that follows a

schema for ordering and then commits them to the SVN. In this process the Config Manager might

request additional information from the developer who send a script about it's nature and relation with

other parts of the database. Thanks to that, the Config Manager keeps control of scripts and can order

them based on the given name. It is also recommended, that the Config Manager has a database

experience that will allow to verify the scripts.

Patches



On a scheduled time, Config Manager collects changes committed to SVN in a file called a patch. A

patch is a script that is used to update the database with the committed changes. Depending on the scale

of project and the number of changes patches might be build biweekly, weekly or daily.

Since there might be many changes it is important to automate the process of collecting the changes and

building each patch. It is possible based on a few assumption:

keeping track of revision numbers with reference to the previously built patches and collecting

changes that belong only to the newer revisions,

the source code changes are being ordered based on the type of the objects for execution within a

patch file; it works for most changes and in special situation the Config Manager might apply

manual changes (e.g. for type changes),

the scripts are saved, committed and their names include an ordering part.

In practice, when patches are build regularly and include all or most source code changes the automated

build of patches is possible without any need for manual edition. Usually it takes only a few minutes.

After building a patch the Config Manager commits it to the SVN, tests in Unit Tests and sends to

business repository with a special report including the list of changes. Then Business can decide to

request from Administrator to apply the patch to a User Test region. Since the patch itself is an

executable bash scripts and includes SQL commands, the Administrator's tasks are straightforward:

copy the patch file to a working directory based on Administrator's preferences (since the patch is

directory independent),

execute the patch,

verify the generated log file for potential errors,

send a confirmation e-mail with the log file to a group e-mail address (including Config Manger,

Project Manager, Business representatives).

It is important to build the patches regularly, so they preserve ordered milestones and can be used later

for upgrading a database. In case that there will be a need to restore/cancel a change that was included in

a patch it is recommended to provide a correcting change/script and build a next patch that will cancel

the previous change.

An Upgrade

An upgrade is another script built by Config Manager. The file includes all patches delivered within a

given branch. Therefore an upgrade is the only file required for upgrading Pilot/Production databases. At

the same time Config Manager merges the changes from the branch into trunk

Again the process of building an upgrade can be automated, since the required patches have been

already committed to the SVN branch. It is important that the order of execution of patches will follow

the same order as they have been build and tested. Besides the nature of the upgrade, the administrators'

procedure with regards to deploying it remains the same.

Delivery to production

The process of production delivery is accomplished by delivering and applying an upgrade. The Config

Manager performs a merge of the branch into trunk. The merge is based on files and should include the

sources, scripts, patches and upgrade.

Continuous integration CI = commit build automata + source code repository + tests automata + feedback report



The automation of building a database and applying patches opens the possibility for automated

continuous integration. It can play significant role for verifying database changes and detecting possible

issues as soon as possible, e.g. within nightly cycles.

Figure 4. The Continuous Integration.

The continuous integration process is currently under development and might assume both: presentation

layer Java application as well as the database. The database might be tested indirectly by performing a

set of requests to the application and then verifying the responses.

Tools The manual work with the Trunk and Branches strategy for Databases will be very time consuming.

Hopefully the tools described below helps to automate most of the process and eliminate places for

human errors.

The DEV environment

The work in developer's environment is supported by:

TortoiseSVN client,

PL/SQL Developer with dedicated plugin for SVN.

The TortoiseSVN and PL/SQL Developer are popular tools. They work just fine as expected. On the

other hand the SVN plugin requires a few words of comments. It serves as a bridge between the PL/SQL

Developer and the local SVN repository on developer's machine. It automates the process of saving a

database object (e.g. package or function)., And it automatically support multiple branches as long as

Config Manager and Developers follows the recommended naming conventions for SVN structure. A

developer needs to focus only on what database he/she is connected to and then the plugin guarantees,

that the files will be stored in the right place. Without that it would be a real nightmares to search for the

right folder in order to save a file when working with multiple branches and it could lead to many

human mistakes (writing files to wrong branch and/or directory).



Figure 5. An example of mapping SVN into local repository after check-out operation.

Figure 6. An example of using the SVN plugin to save a Function object.

Building patches and an upgrade

Depending on the calendar of tests, the number of changes and their importance, Business schedules

delivery of all the currently available changes to a test region. Before each delivery developers must

commit their stable changes and then config manager pulls-out and collects all the changes when

building a patch.



Figure 7. Patches and upgrades.

Config manager uses an automated tool. The tool checks for new changes committed in a branch of a

version repository and then collects the changes in a special patch that again is saved in the same branch

of the repository. Each patch is an executable script that applies the changes to a database. Automation

is the key aspect in the process of building a patch. As long as all developers follow the procedures for

committing and commenting changes.

Below is an example. Let's assume there are two, new changes #1 and #2. The first added SIMPLE.sql

function, the second added EMP_ACTIONS.sql package. The previous patch had the 000 number and

included revisions 20 to 21. New patch carry the 001 name and includes changes from revisions 22 to 23

(the Head).

23:06:56,522 [main] - #Start

23:06:56,524 [main] - #Config: true

23:06:56,634 [main] - #http://1....../......

23:06:56,634 [main] - #35ec356c-ed03-11e0-b048-9b1cc1dbb0c9

23:06:56,643 [main] - # BranchDir: true

23:06:56,649 [main] - # PatchDir: true

23:06:56,656 [main] - # SourceDir: true

23:06:56,673 [main] - # The latest patch [patch_1.1.000;0;20;21]

23:06:56,687 [main] - # The new patch [patch_1.1.001;1;22;23]

23:06:56,687 [main] - #Mkdir 'D:\Patches/patch_1.1.001'

23:06:56,710 [main] - #revision: 23, author: null, date: Thu Feb 09 23:00:01 CET 2012, log

message: Change#2

23:06:56,716 [main] - A /sal/branches/sal1_1/sources/Packages/EMP_ACTIONS.sql

23:06:56,716 [main] - #Adding the Packages subfolder

23:06:56,716 [main] - #Added 'Packages/EMP_ACTIONS'

23:06:56,756 [main] - #revision: 22, author: null, date: Thu Feb 09 22:59:32 CET 2012, log

message: Change#1

23:06:56,762 [main] - A /sal/branches/sal1_1/sources/Functions/SIMPLE.sql

23:06:56,762 [main] - #Adding the Functions subfolder

23:06:56,762 [main] - #Added 'Functions/SIMPLE.sql'

23:06:56,795 [main] - #Log changes: Change#2 Change#1

23:06:56,795 [main] - #Finish Figure 8. An example shows the log4j output from the patch building tool.

Similarly a series of patches can be collected by an automated tool and then saved as one upgrade.

Upgrades are collections of tested patches that can be used to deliver changes to Pilot/Production.



Conclusion The Trunk and Branch approach successfully works for an Oracle Database project with help of

additional procedures, scripts and dedicated tools. It required more discipline and structure from Config

Manager and from all developers. Thankfully the most time-consuming tasks performed by Config

Manager are automated. The benefits include better control of the changes, higher efficiency of work,

elimination of many places for human mistakes. It also gives more flexibility to managers to plan

changes and assign work to developers based on the business requirements.

References Martin Fowler, Continuous Integration http://martinfowler.com/articles/continuousIntegration.html

Paul M. Duval, Steve Matyas, Andrew Glover, Continuous Integration – Improving Software

Quality and Reducing Risk, Addison-Wesley, 2007

JT’s Blog, CI Builds with PL/SQL http://jts-blog.com/?p=48

Wikipedia http://en.wikipedia.org/wiki/Configuration_management

Wikipedia http://en.wikipedia.org/wiki/Revision_control

SCM Support http://www.scmsupport.com/

http://martinfowler.com/articles/continuousIntegration.html

http://jts-blog.com/?p=48

http://en.wikipedia.org/wiki/Configuration_management

http://en.wikipedia.org/wiki/Revision_control


trunk and branches for database configuration management

Technology

branches approach

challengesthe trunk

oracle database project

better control

project manager

authors unique approach

nature ofa database

better manageability