<?xml version="1.0" encoding="UTF-8"?>
<!--
- Licensed Materials - Property of IBM* and/or HCL**
- UrbanCode Deploy
- (c) Copyright IBM Corporation 2002, 2017. All Rights Reserved.
- (c) Copyright HCL Technologies Ltd. 2018. All Rights Reserved.
-
- U.S. Government Users Restricted Rights - Use, duplication or disclosure restricted by
- GSA ADP Schedule Contract with IBM Corp.
-
- * Trademark of International Business Machines
- ** Trademark of HCL Technologies Limited
-->
<plugin xmlns="http://www.urbancode.com/PluginXMLSchema_v1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <header>
    <identifier id="com.urbancode.air.plugin.SQLPlus" name="Oracle SQL*Plus" version="19"/>
    <description>The SQL*Plus plugin allows uDeploy to execute SQL scripts during a deployment.</description>
    <tag>Database/SQLPlus</tag>
  </header>
  <step-type name="Run SQLPlus script">
    <description>Run SQLPlus script</description>
    <properties>
      <property name="sqlPlusExecutable" required="true">
        <property-ui type="textBox"
                     label="SQLPlus Executable"
                     default-value="${p?:resource/sqlplus.exe}"
                     description="The full path to the sqlplus executable to be run or the command to run if it is on the path."/>
      </property>
      <property name="user" required="false">
        <property-ui type="textBox"
                     label="User name"
                     description="The user name for the scripts to be run as. Leave this property blank, and specify Oracle Home, if you wish to use Oracle Wallet authentication."/>
      </property>
      <property name="password" required="false">
        <property-ui type="secureBox"
                     label="Password"
                     description="The password of the user for the scripts to be run as. Leave this property blank, and specify Oracle Home, if you wish to use Oracle Wallet authentication."/>
      </property>
      <property name="passScript">
        <property-ui type="textAreaBox"
                     label="Password Script"
                     description="If you want to use a script to provide password, specify the path to script here, leaving Password field blank"/>
      </property>
      <property name="connectionID" required="true">
          <property-ui type="textBox"
                       label="Connection ID"
                       description="The connection ID to be used (example: localhost:1521/ORCL). If you use the SYS account to run SQL scripts, add the suffix 'AS SYSDBA'."/>
      </property>
      <property name="sqlFiles" required="true">
        <property-ui type="textAreaBox"
                     label="SQL Files"
                     description="A new-line or comma separated list of sql files to be executed. Order is preserved. Supports '*' and '?' as wildcards."/>
      </property>
      <property name="sqlFileArgs" required="false">
		<property-ui type="textAreaBox"
		             label="SQL Files Args"
					 description="A new-line or comma separated list of sql files Args to be executed, Use space to seperate args belonging to same file. Order is preserved. List the files having parameters first. Put the args in double quotes if it has space in it." />
	  </property>
      <property name="skipSqlErrors">
        <property-ui type="selectBox"
                     label="Skip SQL Errors"
                     default-value="${p?:skipSqlErrors}"
                     description="Ignore any PLS, SP2, and ORA errors encountered during script 
                        execution. Select True to skip error, or false to fail on errors. You may
                        also select the parameterized value ${p?:skipSqlErrors}, which will look 
                        for the skipSqlErrors property in UCD. If the property isn't defined, the
                        value will be false."/>
        <value label="True">true</value>
        <value label="False">false</value>
        <value label="${p?:skipSqlErrors}">${p?:skipSqlErrors}</value>
      </property>
      <property name="failOnORA">
        <property-ui type="checkBox"
                     label="Fail on Database Messages"
                     description="Check this box to fail on ORA- Database Messages. This option
                        will be ignored if the 'Skip SQL Errors' option is selected."/>
      </property>
      <property name="oracleHome">
        <property-ui type="textBox"
                     label="Oracle Home"
                     default-value="${p?:resource/oracle.home}"
                     description="Optional field to specify the ORACLE_HOME environment variable value. This field is required to authenticate through Oracle Wallet."/>
      </property>
      <property name="additionalEnvVars">
        <property-ui type="textAreaBox"
                     label="Environment Variables"
                     default-value="${p?:resource/environment.vars}"
                     hidden="true"
                     description="Optional field to specify additional environment variable values. Separate each pair with a new line or comma and delimit the variable and value with an equals sign. For example: 'ORACLE_SID=orcl'"/>
      </property>
    </properties>
    <post-processing><![CDATA[
        properties.put("Status", "Success");
        if (properties.get("exitCode") != 0) {
            properties.put("Status", "Failure");
        }
        else {
            scanner.register("(?i)ERROR at line",  function(lineNumber, line) {
                var errors = properties.get("Error");
                if (errors == null) {
                    errors = new java.util.ArrayList();
                }
                errors.add(line);
                properties.put("Error", errors);

                properties.put("Status", "Failure");
            });
            scanner.register("(?i)invalid username/password; logon denied",  function(lineNumber, line) {
                var errors = properties.get("Error");
                if (errors == null) {
                    errors = new java.util.ArrayList();
                }
                errors.add(line);
                properties.put("Error", errors);

                properties.put("Status", "Failure");
            });
            scanner.register("(?i)the account is locked",  function(lineNumber, line) {
                var errors = properties.get("Error");
                if (errors == null) {
                    errors = new java.util.ArrayList();
                }
                errors.add(line);
                properties.put("Error", errors);

                properties.put("Status", "Failure");
            });
            scanner.register("ERROR:",  function(lineNumber, line) {
                var errors = properties.get("Error");
                if (errors == null) {
                    errors = new java.util.ArrayList();
                }
                errors.add(line);
                properties.put("Error", errors);

                properties.put("Status", "Failure");
            });
            scanner.scan();

            var errors = properties.get("Error");
            if (errors == null) {
                errors = new java.util.ArrayList();
            }
            properties.put("Error", errors.toString());
        }
     ]]></post-processing>
    <command program="${GROOVY_HOME}/bin/groovy">
      <arg value='-cp' />
      <arg path='classes:lib/commons-io.jar:lib/securedata.jar:lib/jettison-1.1.jar:lib/commons-codec.jar:lib/CommonsUtil.jar' />
      <arg file="sqlplus.groovy"/>
      <arg file="${PLUGIN_INPUT_PROPS}"/>
      <arg file="${PLUGIN_OUTPUT_PROPS}"/>
    </command>
  </step-type>
  <step-type name="SQLPlus through Control File">
    <description>Run SQLPlus scripts using control files for orchestration.</description>
    <properties>
      <property name="sqlPlusExecutable" required="true">
        <property-ui type="textBox"
                     label="SQLPlus Executable"
                     default-value="${p?:resource/sqlplus.exe}"
                     description="The full path to the sqlplus executable scripts to be run or the command to run if it is on the path."/>
      </property>

      <property name="mode" required="false">
        <property-ui type="selectBox"
                     label="Mode"
                     description="Whether to do a deployment or just check that the control files match the valid syntax and all sql files are found. Both modes will do an initial check."
                     default-value="deploy"/>
            <value label="Check">check</value>
            <value label="Deploy">deploy</value>
      </property>
      <property name="controlFileIncludes" required="false">
        <property-ui type="textAreaBox"
                     label="Control File Includes"
                     description="A newline seperated list of control file includes for finding control files. Use * for matching any file or folder, and ** for recursively matching folders.
                     Each line of a control file should be formatted thusly 'scriptName\tconnectionId\tuser\tABORT_FLAG'. \t means whitespace tab.
                     For an example of what a control file should look like, see control.example.txt and the readme.txt in the root of the plugin."
                     default-value="**/*"/>
      </property>
      <property name="controlFileExcludes">
        <property-ui type="textAreaBox"
                     label="Control File Excludes"
                     description="A newline seperated list of control file excludes for finding control files. Use * for matching any file or folder, and ** for recursively matching folders."/>
      </property>
      <property name="connectionIdUserToPassword" required="true">
        <property-ui type="textAreaBox"
                     label="ConnectionId x User to Password"
                     description="A newline separated list of connection IDs, User Names and Passwords. The expected format for a line is username@connectionId=Password.
                                 To use Oracle Wallet authentication, use the format @connectionId. A combination of the two formats is allowed."/>
      </property>
      <property name="oracleHome">
        <property-ui type="textBox"
                     label="Oracle Home"
                     default-value="${p?:resource/oracle.home}"
                     description="Optional field to specify the ORACLE_HOME environment variable value. This field is required to authenticate through Oracle Wallet."/>
      </property>
      <property name="additionalEnvVars">
        <property-ui type="textAreaBox"
                     label="Environment Variables"
                     default-value="${p?:resource/environment.vars}"
                     hidden="true"
                     description="Optional field to specify additional environment variable values. Separate each pair with a new line or comma and delimit the variable and value with an equals sign. For example: 'ORACLE_SID=orcl'"/>
      </property>
    </properties>
    <post-processing><![CDATA[
        if (properties.get("exitCode") != 0) {
            properties.put("Status", "Failure");
        }
        else {
            properties.put("Status", "Success");
        }
        scanner.register("(?i)ERROR at line",  function(lineNumber, line) {
            var errors = properties.get("Error");
            if (errors == null) {
                errors = new java.util.ArrayList();
            }
            errors.add(line);
            properties.put("Error", errors);
        });
        scanner.register("(?i)invalid username/password; logon denied",  function(lineNumber, line) {
            var errors = properties.get("Error");
            if (errors == null) {
                errors = new java.util.ArrayList();
            }
            errors.add(line);
            properties.put("Error", errors);
        });
        scanner.register("(?i)the account is locked",  function(lineNumber, line) {
            var errors = properties.get("Error");
            if (errors == null) {
                errors = new java.util.ArrayList();
            }
            errors.add(line);
            properties.put("Error", errors);
        });
        scanner.register("ERROR:",  function(lineNumber, line) {
            var errors = properties.get("Error");
            if (errors == null) {
                errors = new java.util.ArrayList();
            }
            errors.add(line);
            properties.put("Error", errors);
        });
        scanner.register("Abort set to false, ignoring and continuing", function() {
            //do nothing just want to add a line of interest
        });
        scanner.register("Failure connecting", function() {
            //do nothing just want to add a line of interest
        });
        scanner.register("Invalid User", function() {
            //do nothing just want to add a line of interest
        });
        scanner.register("Expected format", function() {
            //do nothing just want to add a line of interest
        });
        scanner.register("Illegal Cnrtl", function() {
            //do nothing just want to add a line of interest
        });
        scanner.register("No passwords config", function() {
            //do nothing just want to add a line of interest
        });
        scanner.register("does not exist in expected", function() {
            //do nothing just want to add a line of interest
        });
        scanner.register("As declared by control file", function() {
            //do nothing just want to add a line of interest
        });
        scanner.register("Failure executing script", function() {
            //do nothing just want to add a line of interest
        });
        scanner.scan();

        var errors = properties.get("Error");
        if (errors == null) {
            errors = new java.util.ArrayList();
        }
        properties.put("Error", errors.toString());
    ]]></post-processing>
    <command program="${GROOVY_HOME}/bin/groovy">
      <arg value="-cp"/>
      <arg path="classes:lib/securedata.jar:lib/jettison-1.1.jar:lib/commons-codec.jar:lib/CommonsUtil.jar"/>
      <arg file="sqlplusThroughControlFile.groovy"/>
      <arg file="${PLUGIN_INPUT_PROPS}"/>
      <arg file="${PLUGIN_OUTPUT_PROPS}"/>
    </command>
  </step-type>
</plugin>
