Introduction

This document should be read as an extension to the MidVision RapidDeploy User Guide. It details the specifics related to the using the database tasks within a database project. Please ensure you have read and understood the concepts in that guide before reading this one.

The document is intended for use by experienced middleware system administrators and therefore users are expected to possess the core capabilities required to manually administer environments.

This user guide is supplemented by the MidVision on-line media including quick-start video guides, all of which can be accessed from www.midvision.com

RapidDeploy Database Deployment Script Organisation

A RapidDeploy database project can have many application database scripts. For this purpose of demonstrating how scripts are organised, this example has an application named “projectX”.

The database project environment configuration files will be located in this directory:

<PROJECT_HOME/db/config

All scripts are located in the folder which is named after the application. The folder should be located in this project path:

<PROJECT_HOME/db/scripts

Database scripts have to be organized in a folder structure, following example:

<PROJECT_HOME/db/script/projectX/incremental/01_v0.1/01_$owner_projectX.tab
02_$owner_projectX.vw
03_$owner_projectX.ind

Folder and script names must start with an index number followed by an underscore and a description. The index indicates the sequence of the scripts or script folders.

Suppose you add a new script called 04_$owner_projectX.con. The next time you deploy the schema, the database is updated incrementally by executing this new script. Unless you use the fromScratch option, updating an already executed incremental script is not allowed: the deployment will fail if you try to do so. If the fromScratch option is used and an existing incremental script is changed, the database is cleared (i.e. all database objects are dropped) and all scripts are executed again.

Incremental and repeatable scripts

The database scripts from the example above are incremental: each script contains a delta, and should be executed only once in the proper sequence. But RapidDeploy also supports repeatable scripts: A repeatable script must be written in such a way that it can be executed multiple times. For example, function or stored procedure definitions and views can be organised as repeatable scripts. The advantage of a repeatable script is that it can be modified: When changed, the script is simply executed again.

You can recognize repeatable scripts from the file name: it has no index number. The directories that contain repeatable scripts also cannot have an index number. If you put a non-indexed script inside an indexed one, RapidDeploy will give an error.

Repeatable scripts are always executed after all incremental (indexed) scripts. Therefore, they cannot be located inside an indexed folder. If your database project contains repeatable scripts, it’s a good idea to clearly separate them from the incremental scripts, like in following example:

projectX/incremental/01_v0.2/01_$owner_projectX.vw
02_$owner_projectX.grt
03_$owner_projectX_version_control.sql

projectX/repeatable/someView.sql
someFunction.sql
someStoredProcedure.sql

Post processing scripts

Some projects have scripts that need to be executed each time a script was executed, such as for example a script that compiles all stored procedures. Post processing scripts can be defined for this purpose. Post processing scripts have to be located in the directory called postprocessing, located directly in the root of the configured scripts folder. They can be indexed, but they don’t have to be: Indexes can be used to indicate the execution sequence of the post-processing scripts. An indexed post-processing script can be modified without causing an error or triggering a from-scratch update. If a post-processing script is modified, all of them are executed again. For example:

projectX/incremental/01_v0.2/01_$owner_projectX.vw
02_$owner_projectX.grt
03_$owner_projectX_version_control.sql

projectX/postprocessing /01_compile_all.sql
02_grant_select_to_read_user.sql

projectX/repeatable/someView.sql
someFunction.sql
someStoredProcedure.sql

Multi-database / user support

On some projects, database scripts need to be executed using different database connections. For instance, if your database consists of multiple schemas and you need to log in with a different user to be able to perform modifications in another schema.

You can configure multiple databases, each of them identified with a different logical name. This logical name can be used in the script name to indicate the target database of the script. If the script name doesn’t indicate a target database, it will be executed on the default database.

The target database can be indicated in the script name using a $ sign. For incremental scripts the target database is separated from the index with an underscore; repeatable script names start with the target database indication. For example:

projectX/incremental/01_v0.3/01_$user_projectX.drp
02_$owner_projectX.drp

ProjectX Script and Folder Example

projectX/incremental/01_v0.1/01_$owner_projectX.tab
02_$owner_projectX.vw
03_$owner_projectX.ind
04_$owner_projectX.con
05_$owner_projectX.sqs
06_$owner_projectX.trg
07_$owner_projectX.grt
08_$owner_projectX.syn
projectX/incremental/01_v0.2/01_$owner_projectX.tab
02_$owner_projectX.vw
03_$owner_projectX.ind
04_$owner_projectX.con
05_$owner_projectX.grt
06_$owner_projectX.syn
projectX/incremental/02_v0.1/01_$owner_projectX.vw
02_$owner_projectX_version_control.sql
projectX/incremental/02_v0.1/01_$owner_projectX.tab
02_$owner_projectX.vw
projectX/postprocessing /01_compile_all.sql
02_grant_select_to_read_user.sql

projectX/repeatable/someView.sql
someFunction.sql
someStoredProcedure.sql

Database Orchestration Task Descriptions

Introduction

A MidVision Orchestration is defined at the project level in the MidVision tab, on the orchestration sub-tab.

An orchestration can consist of multiple task steps. You can add/remove/change the order of the chosen steps on this panel. Additionally they can be activated or deactivated. Please see the MidVision RapidDeploy User Guide for a tutorial on how to set up and configure a project, including orchestration.

Each Orchestration must have an initialisation task as the first task and a finalisation task as the last task.

You may choose to add/remove/reconfigure tasks in the orchestration to suit the needs of your site, project or deployment.

In the remainder of this section we look at each of the individual Database specific orchestration tasks in turn.

Database Task Resources

All the RapidDeploy database tasks use the same set of resource to configure the tasks. These are listed below and shared across every task defined in this document.

Attribute Name	Type	Description
failOnError	Boolean (true/false)	This task will not fail the deployment on error if set to false.
dbConfigPath	String	The project relative path to the project environment configuration files. Defaults to “db/config”.
dbScriptPath	String	The project relative path to the project application database files. Defaults to “db/data”.
scriptRunner	String	Defaults to “jdbc”

Update Database

This task updates the database to the latest version.

Task Name: UpdateDatabaseTask

Mark Error Script Performed

This task indicates that the failed script was manually performed. The script will NOT be run again in the next update. No scripts will be executed by this task.

Task Name: MarkErrorScriptPerformedTask

Mark Error Script Reverted

This task indicates that the failed script was manually performed. The script will NOT be run again in the next update. No scripts will be executed by this task.

Task Name: MarkErrorScriptRevertedTask

Mark Database as Up To Date

This operation updates the state of the database to indicate that all scripts have been executed, without actually executing them. This can be useful when you want to start using DbMaintain on an existing database, or after having fixed a problem directly on the database.

Task Name: MarkDatabaseAsUpToDateTask

Check Database Scripts Updates

This task performs a dry run of the database update. May be used to verify if there are any updates or in a test that fails if it appears that an irregular script update was performed.

Task Name: CheckScriptUpdatesTask

Clear Database

This task that removes all database items like tables, views, etc. from the database and empties the DBMAINTAIN_SCRIPTS table.

Task Name: ClearDatabaseTask

Clean Database

This task removes the data in all database tables, except for the DBMAINTAIN_SCRIPTS table.

Task Name: CleanDatabaseTask

Disable Database Constraints

This task disables or drops all foreign key and not null constraints.

Task Name: DisableConstraintsTask

Update Database Sequences

This task updates all sequences and identity columns to a minimum value.

Task Name: UpdateSequencesTask

Create a Database Script Archive

This task creates a jar file that packages all database update scripts. This jar can then be used as input for the updateDatabase task to apply changes on a target database. This way, database updates can be distributed as a deliverable, just like a war or ear file.

The created jar file will contain all configurations concerning the scripts in the META-INF folder.

Task Name: CreateScriptArchiveTask

Baseline Database

This task extracts all the schema definitions and data into text files and then compresses them into a JAR archive file.

Task Name: BaselineDatabaseTask

Example Orchestration

This shows an example orchestration file task definition.

<task name=”UpdateDatabase” order=”2″ active=”true”>
<class>com.midvision.rapiddeploy.orchestration.tasks.db.UpdateDatabaseTask</class>
<resource type=”dbConfigPath”>db/config</resource>
<resource type=”dbScriptPath”>db/scripts</resource>
<resource type=”scriptRunner”>sqlplus</resource>
<resource type=”failOnError”>true</resource>
</task>