DbApply

Oracle database schema changes deployment tool.

Integration of Subversion and Oracle

DbApply is the command line tool responsible for integration of Subversion and Oracle. Software developers keep all scripts with database structure and content in Subversion repository. DbApply delivers all committed changes to database in most effective manner. It detects the changed scripts, sort them in order to avoid dependency errors and executes against Oracle database. Also scripts can be exported into a set of SQL*Plus commands. These commands will be executed then on servers where DbApply cannot be installed.

Apply scripts to database

Suppose you have scripts describing the objects of Oracle database schema. These scripts are under Subversion version control system. The goal is to deploy changes from local subversion repository to Oracle database.


First of all you need to bind target Oracle database with Subversion repository URL (Branch). Branch lets DbApply to identify scripts and prevent from database synchronization with wrong repositories.

dbapply --add-branch <url> --database <database connection string>

<database connection string> has format: user/password@host:port/sid (e. g. scott/tiger@localhost/orcl)

<url> - usually SVN attribute URL of local repository is what you need. But you may specify any part of URL as branch.

svn info <local repository> | grep URL

Once you linked the SVN branch to Oracle database, you are ready for synchronization:

dbapply --paths <local svn repository> --encoding <scripts sources encoding> --database <database connection string>

DbApply will determine the scripts that need to be applied, sort them in order to avoid dependency errors and execute. That is it.

Export scripts

In case if you need to synchronize database on remote server where DbApply is not installed, you may Export Scripts into either Batch or Shell files:

dbapply --paths <local svn repository>,... --batch <directory for export>

or

dbapply --paths <local svn repository>,... --shell <directory for export>

DbApply will prepare command file run.bat (or run.sh) with SQL*Plus calls for each command from your scripts and two directories:

  • scripts - Scripts that will be applied.
  • aux - DbApply misc SQL commands.

Command file checks every script if it needs to be applied and executes it.

You may also include branch administration commands into exported script:

dbapply --add-branch <url> --paths <local svn repository> --shell <directory for export>

After that you may send exported command file (with two directories) to remote server and execute it:

./run.sh <database connection string>

On remote server you need SQL*Plus installed.

Ignored errors

Add reliability to your scripts - make it possible to execute their commands more than once. And if something will go wrong with their execution you will be able to re-run them.

Just specify before the command the commented list of errors that can be ignored:

-- ignore_error(code, code, ...)

For example:

-- ignore_error(955)
CREATE TABLE a (n NUMBER);

or

/* ignore_error(955) */ CREATE TABLE a (n NUMBER);

If these commands will be executed two times, first execution will be successful, but second will fail with "ORA-00955: name is already being used by existing object" exception. DbApply understands this type of annotations and ignores mentioned errors.

Branch administration

DbApply executes scripts only from attached repositories. Other scripts will be just ignored. The very first action that needs to be done for new database is to specify SVN branches. Usually you may need one branch per local repository.

dbapply --add-branch <url> --database <database connection string>

Example:

dbapply.exe --add-branch http://svn.myhost.com/rep/tags/tag1 --database scott/tiger@localhost/orcl

When you need to switch database to another SVN branch or repository:

dbapply --switch-branch <old url> <new url> --database <database connection string>

Example:

dbapply.exe --switch-branch http://svn.myhost.com/rep/branch/1.0 http://svn.myhost.com/rep/branch/2.0
    --database scott/tiger@localhost/orcl

If necessary you can detach Subversion branch from database:

dbapply --delete-branch <url> --database <database connection string>

Example:

dbapply --delete-branch http://svn.myhost.com/rep/branch/1.0 --database scott/tiger@localhost/orcl

To see the list of SVN branches attached to databases execute:

dbapply --list-branches --database <database connection string>

Apply changes to database

Pass local repositories paths and database connection string. DbApply will prepare list of changed scripts since last synchronization and execute them:

dbapply --paths <local svn repository 1> <local svn repository 2> ... --encoding <scripts sources encoding> --database <database connection string>

Make sure each local repository has branch linked to database. You can add any number of branch administration options into the same command. For example:

dbapply.sh --add-branch http://svn.myhost.com/rep/branch/1.0 --paths /home/usr/svn/branch_1 --database scott/tiger@localhost/orcl

If you would like to check the order of scripts execution prior to database modification, use --dry mode. In this mode DbApply just prepares the list of modified scripts, sorts them and prints to output.

dbapply.sh --dry --paths /home/usr/svn/branch_1 --database scott/tiger@localhost/orcl

Export changes into SQL*Plus script

Pass the directory where DbApply should export scripts via either --batch or --shell options. For example:

dbapply --add-branch <url> --paths <local svn repository> --batch <export directory>

DbApply will prepare command file "run.bat" (or run.sh) file with two directories:

  • scripts - Scripts that will be applied.
  • aux - DbApply misc SQL commands.

Command file checks every script if it needs to be applied and executes it.

Scripts encoding

Pass the name of encoding used for your SQL files as value of option --encoding:

dbapply --paths <local svn repository> --encoding <scripts sources encoding> --database <database connection string>

Example:

dbapply --paths /space/db/scripts --encoding cp1251 --database scott/tiger@localhost/orcl

For value please use "Canonical name for java.lang API" described on this page. If this option omitted, platform default value will be used.

Version 4.0.0.239 (Beta)   Released: May 05, 2013;

System requirements: Java Runtime Environment (JRE) 1.6 or 1.7


Installation Instructions:

  • Run dbapply.4.0.0.239.exe
  • Follow all steps suggested by the wizard.

Installation Instructions:

  • Unpack the dbapply.4.0.0.239.tar.gz file using the following command: tar xfz dbapply.4.0.0.239.tar.gz

TMate Software

SVNKit is a pure Java Subversion client library. You would like to use SVNKit when you need to access or modify Subversion repository from your Java application, be it a standalone program, plugin or web application.

Also check their new project. SubGit - safe migration from Svn to Git (and back).

ANTLR

ANTLR (ANother Tool for Language Recognition) is a powerful parser generator for reading, processing, executing, or translating structured text or binary files.

StringTemplate is a java template engine (with ports for C#, Python) for generating source code, web pages, emails, or any other formatted text output.