Preface

This document describes the functionality provided by the database plugin.

See the XL Deploy Reference Manual for background information on XL Deploy and deployment concepts.

Overview

The database plugin is an XL Deploy plugin that supports deployment of SQL files and folders to a database client.

Features

• Runs on XL Deploy 4.0 and up.
• Supports deployment to MySQL, Oracle, MS SQL and DB/2.
• Deploys and undeploys SQL files and folders.

Requirements

• XL Deploy requirements

  • XL Deploy: version 4.0+
  • Other XL Deploy Plugins: None

• Infrastructural requirements

  • User credentials for accessing the database client executables on the host running the database.

Plugin Concepts

SQL Scripts

The SqlScripts configuration item (CI) identifies a ZIP file that contains a folder with SQL scripts that are to be executed on a database. SQL scripts come in two flavors, namely installation scripts and rollback scripts. Installation scripts are used to execute changes on the database, such as creation of a table or inserting data. Each installation script is associated with a rollback script which undoes the actions performed by it's companion installation script. Rollback scripts must have the exact same name as the installation script they are associated with and have the moniker -rollback attached to it. Executing an installation script followed by the accompanying rollback script should leave the database in an unchanged state.

SQL scripts are ordered lexicographically based on their filename. This is an example of ordering of several installation scripts:

• 1-create-user-table.sql
• 1-create-user-table-rollback.sql
• 10-drop-user-index.sql
• 10-drop-user-index-rollback.sql
• 2-insert-user.sql
• 2-insert-user-rollback.sql
• ...
• 9-create-user-index.sql
• 9-create-user-index-rollback.sql

Note that in this example, the tenth script, 10-drop-user-index.sql would be incorrectly executed after the first script, 1-create-user-table.sql.

When upgrading a SqlScripts CI, only those scripts that were not present in the previous package version are executed. For example, if the previous SqlScripts folder contained script1.sql and script2.sql, and the new version of SqlScripts folder contains script2.sql and script3.sql, then only script3.sql will be executed as part of the upgrade.

When undeploying a SqlScripts CI, all rollback scripts are executed in reverse lexicographical order.

Additionally, since Deployit 3.9.3, scripts whose content has been modified are also executed. The old behaviour, where only the names of the scripts were taken into consideration, can be re-enabled by setting the hidden property sql.ExecutedSqlScripts.executeModifiedScripts is to false. If a rollback script is provided for that script, it will be run before the new script is run. This behaviour can be disabled by setting the hidden property sql.ExecutedSqlScripts.executeRollbackForModifiedScripts to false.

Dependencies

It is also possible to include dependencies together with the SQL scripts. Dependencies are included in the package using sub-folders. Sub-folders that have the same name as the script (without the file extension) are uploaded with the scripts in the sub-folder along with the main script to the target machine. The main script can then execute the dependent scripts in the same connection.

Common dependencies can be included in a sub-folder called common and will be available to all scripts.

Take this folder with Oracle scripts as an example:

mysqlfolder
|
|__ 01-CreateTable.sql
|
|__ 02-CreateUser.sql
|
|__ 02-CreateUser
|   |  
|   |__ create_admin_users.sql
|   |
|   |__ create_power_users.sql
|
|__ common
    |
    |__ some_other_util.sql
    |
    |__ some_resource.properties

The 02-CreateUser.sql script can use it's dependencies or common dependencies as follows:

--
-- 02-CreateUser.sql
--

INSERT INTO person2 (id, firstname, lastname) VALUES (1, 'xebialabs1', 'user1');
-- Execute a common dependency
@some_other_util.sql
-- Execute script-specific dependency: Create Admin Users
@create_admin_users.sql
-- Execute script-specific dependency: Create Power Users
@create_power_users.sql
COMMIT;

SQL Client

The SqlClient CIs are containers to which SqlScripts can be deployed. The plugin ships with SqlClient for the following databases:

• MySQL
• Oracle
• MS SQL
• DB/2

When SQL scripts are deployed to an SQL client, each script to be executed is run against the SQL client in turn. The SQL client can be configured with a username and password that is used to connect to the database. The credentials can be overridden on each SQL script if required.

Usage in Deployment Packages

The following is a manifest snippet that shows how SQL file and folder CIs can be included in a deployment package. The SQL scripts CI refers to a folder, sql, in the deployment package.

<udm.DeploymentPackage version="2.0" application="PetClinic-ear">
    <jee.Ear name="PetClinic" file="PetClinic-2.0.ear"/>
    <sql.SqlScripts name="sql" file="sql" />
</udm.DeploymentPackage>

Using the deployables and deployeds

The following table describes which deployable/container combinations are possible.

Deployable vs. Container table

The following table describes the effect a deployed has on it's container.

Deployed Actions Table