Extending the DB Plugin to do Connection Tests

| September 30, 2015 | 0 Comments

SQL_DatabaseAfter working with a few of our customers, I decided it would be useful to be able to check database connections like we can check host connections in XL Deploy.  As a result, I created the xld-database-contest project in the XebiaLabs-community on Github.  This plugin adds control tasks for some of the database client containers.  Since XL Deploy uses an object model to describe the deployable objects we can easily extend these objects to have data or behavior beyond what comes with XL Deploy.

For this plugin we are going to assume that the database plugin has already been installed.  To make this plugin, I have created a synthetic.xml file that describes our type modifications to the default database plugin objects.  For each database type that we want to modify we need to make a type modification similar to the following:

<type-modification type="sql.OracleClient">
<property name="checkScript" default="sql/checkOracleDB" hidden="true"/>
<property name="testSql" kind="string" default="SELECT * FROM v\$version;" hidden="true" />
<method name="check"/>
</type-modification>

This will create a new control task “check” that will appear in the UI to allow us to check the database connection.  When we select the “check” control task the “checkScript” will be executed and the testSql property will be used to run a query against the database.  Now we need to write the script to check the database.  When defining scripts at this level we don’t need to specify the file extension because XL Deploy will figure that out based on the operating system we will be running the script on.  For a Unix operating system our script parameter sql/checkOracleDB will use the sql/checkOracleDB.sh.ftl and for Windows XL Deploy will use sql/checkOracleDB.bat.ftl.  Also note that both of these scripts end with ftl.  This is the extension for Freemarker scripts.  XL Deploy uses Freemarker replacement to put our testSql parameter into our scripts.  Let’s have a look at the scripts.  The Unix script is as follows:

<#--
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS
FOR A PARTICULAR PURPOSE. THIS CODE AND INFORMATION ARE NOT SUPPORTED BY XEBIALABS.
-->
#!/bin/sh
#======================================================================
<#import "/sql/commonFunctions.ftl" as cmn>
ORACLE_HOME="${container.oraHome}"
export ORACLE_HOME
# will override the declarations above if ORACLE_HOME or ORACLE_SID are present
<#include "/generic/templates/linuxExportEnvVars.ftl">
<#if !cmn.lookup('username')??>
echo 'ERROR: username not specified! Specify it in either SqlScripts or its OracleClient container'
exit 1
<#elseif !cmn.lookup('password')??>
echo 'ERROR: password not specified! Specify it in either SqlScripts or its OracleClient container'
exit 1
<#else>
echo "${container.testSql}"
echo EXIT | "${container.oraHome}/bin/sqlplus" ${cmn.lookup('additionalOptions')!}
-L ${cmn.lookup('username')}/${cmn.lookup('password')}@${container.sid} <<END_OF_WRAPPER
WHENEVER SQLERROR EXIT 1 ROLLBACK;
WHENEVER OSERROR EXIT 2 ROLLBACK;
${container.testSql}
END_OF_WRAPPER
res=$?
if [ $res != 0 ] ; then
exit $res
fi
</#if>

This is a Freemarker bat script.  The properties from the sql.OracleClient are replaced in the script before it is copied out to the target system and executed.  In the user interface we will now have a a menu item as follows:

Screen Shot 2015-07-13 at 3.33.34 PM

When we select the “Check” option from the menu we can run the test SQL against the database and see a result as follows:

Screen Shot 2015-07-13 at 3.33.54 PM

Not all of the databases have been implemented for all of the platforms.  More to come as time permits.  If you find this plugin useful, it can be downloaded from the XebiaLabs Community (https://github.com/xebialabs-community/xld-database-conntest).

 


Rick Broker

About the Author ()

Rick is a Sales Engineer for XebiaLabs based in Columbus, Ohio. He has worked as a software developer and as a system administrator for Unix systems and middleware components including Websphere and Weblogic.