Database plugin

This topic describes the Deploy Database plugin, which supports the deployment of SQL files and folders to a database client.

The plugin is designed according to the principles described in Evolutionary Database Design. The plugin supports:

• Deployment to MySQL, PostgreSQL, Oracle, Microsoft SQL, and IBM DB2
• Deployment and undeployment of SQL files and folders

SQL scripts

The sql.SqlScripts configuration item (CI) identifies a ZIP file that contains SQL scripts that are to be executed on a database.

• The scripts must be located at the root of the ZIP file.

• SQL scripts can be installation scripts or 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 that undoes the actions performed by its companion installation script.

• Executing an installation script, followed by the accompanying rollback script, should leave the database in an unchanged state.

• A rollback script must have the same name as the installation script with which it is associated, and must have the moniker -rollback attached to it.

• Deploy tracks which installation scripts were executed successfully and only executes their associated rollback scripts. See Extend the Database plugin for information about rollback behavior for custom CI types that are based on sql.SqlScripts.

Important: We recommend that you set an environment variable before using a SQL script. For example, you can select the environment variable key as NLS_LANG and the value as AL32UTF8, for the sql.OracleClient.

Sample ZIP file structure

This is an example of the structure of a ZIP file that contains SQL scripts:

	|__ deployit-manifest.xml
	|
	|__ sql
		|
		|__ 01-create-tableA-rollback.sql
		|
		|__ 01-create-tableA.sql
		|
		|__ 01-create-tableZ-rollback.sql
		|
		|__ 01-create-tableZ.sql
		|
		|__ 02-create-tableA-view.sql
		|
		|__ 02-create-tableZ-view.sql
		|
		|__ 03-INSERT-tableA-data.sql

The content of the deployit-manifest.xml file is:

<udm.DeploymentPackage version="1.1" application="acme-app">
  <deployables>
    <sql.SqlScripts name="sql" file="sql"/>
  </deployables>
</udm.DeploymentPackage>

You can also provide a ZIP file that contains SQL scripts:

	Archive:  sql.zip

	    testing: 01-create-tableA-rollback.sql   OK
	    testing: 01-create-tableA.sql            OK
	    testing: 01-create-tableZ-rollback.sql   OK
	    testing: 01-create-tableZ.sql            OK
	    testing: 02-create-tableA-view.sql       OK
	    testing: 02-create-tableZ-view.sql       OK
	    testing: 03-INSERT-tableA-data.sql       OK

With the following deployit-manifest.xml file content:

<udm.DeploymentPackage version="1.1" application="acme-app">
  <deployables>
    <sql.SqlScripts name="sql" file="sql.zip"/>
  </deployables>
</udm.DeploymentPackage>

Note: If the ZIP file contains a subdirectory, the SQL scripts will not be executed.

Naming SQL scripts

Deploy uses a regular expression to identify SQL scripts. The regular expression is defined by the scriptRecognitionRegex and rollbackScriptRecognitionRegex properties of the sql.ExecutedSqlScripts CI.

The default regular expression is configured such that Deploy expects each script to start with a number and a hyphen.

For example: 1-create-user-table.sql

Even if there is only one script, it must start with a number and a hyphen.

You can change the regular expression in deployit-defaults.properties or by creating a type modification in the synthetic.xml file.

Order of SQL scripts

SQL scripts are ordered based on their file names. To execute the scripts in a correct order, make sure you add prefix to your script names accordingly using 01-, 02- instead of 1-, 2-.

For example:

• 01-create-user-table.sql
• 01-create-user-table-rollback.sql
• 02-insert-user.sql
• 02-insert-user-rollback.sql
• ...
• 09-create-user-index.sql
• 09-create-user-index-rollback.sql
• 10-drop-user-index.sql
• 10-drop-user-index-rollback.sql

Upgrading SQL scripts

When upgrading a SqlScripts CI, only the 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. If a rollbackscript is provided for script1.sql, it will also be executed.

Undeploying SQL scripts

When you undeploy an SqlScripts CI, all rollback scripts are executed in reverse lexicographical order.

Scripts with content that has been modified are also executed. To change this behavior to where only the names of the scripts are taken into consideration, set the hidden property sql.ExecutedSqlScripts.executeModifiedScripts to false. If a rollback script is provided for that script, it will be run before the new script is run. To disable this behavior, set the hidden property sql.ExecutedSqlScripts.executeRollbackForModifiedScripts to false.

Dependencies

You can include dependencies with 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 to the target machine with the scripts in the sub-folder. The main script can then execute the dependent scripts in the same connection.

Common dependencies that are placed in a sub-folder called common are available to all scripts.

Dependencies example

This is an example of a ZIP file structure that contains Oracle scripts:

	|__ 01-CreateTable.sql
	|
	|__ some_other_util.sql
	|
	|__ some_resource.properties

The 02-CreateUser.sql script can use its dependencies or common dependencies as follows:

	--
	-- 02-CreateUser.sql
	--

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

Note: The syntax for including the dependent scripts varies among database types. For example, Microsoft SQL databases use include <script file name>.

Updating dependencies

Because Deploy cannot interpret the content of an SQL script, it cannot detect when a dependent script has been modified between versions. If you modify a dependent script and you want Deploy to execute it when you update a deployed application, you must also modify the script that calls it.

Using the example above, assume that create_admin_users.sql has been modified in a new version of the application. For Deploy to execute create_admin_users.sql again, 02-CreateUser.sql must also be modified.

SQL client

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

• MySQL
• PostgreSQL
• Oracle
• Microsoft SQL
• IBM DB2

When SQL scripts are deployed to a 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.