The Deploy Database plugin supports 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.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
-rollbackattached 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
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
rollbackScriptRecognitionRegex properties of the
The default regular expression is configured such that Deploy expects each script to start with a number and a hyphen.
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
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
02- instead of
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
script2.sql and the new version of SqlScripts folder contains
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
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
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.
This is an example of a ZIP file structure that contains Oracle scripts:
|__ 01-CreateTable.sql | |__ some_other_util.sql | |__ some_resource.properties
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>.
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
02-CreateUser.sql must also be modified.
sql.SqlClient CIs are containers to which
sql.SqlScripts can be deployed. The plugin is provided with
SqlClient for the following databases:
- 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.