How to write a script

A ScriptRunner file is an XML document structured according to the schema ScriptRunner.xsd. For instance:

  <task name="Simple work" type="Print">
    What?
  </task>
This describes a very simple piece of work, printing "What?" to stdout.

To do more serious work, like run some SQL:

  <task name="SQL work" type="SQL">
    <connections>
      <connection dataSourceName="mt_app"
                  dataSourceClass="org.postgresql.ds.PGPoolingDataSource" 
                  serverName="dbserver" databaseName="mt_prod" 
                  user="mt_user" password="secret" />
    </connections>
    BEGIN TRANSACTION ; INSERT INTO pawns VALUES ("a2") ; COMMIT ; 
    \g
    BEGIN TRANSACTION ; INSERT INTO pawns VALUES ("b2") ; COMMIT ;
    \g
    INSERT INTO pawns VALUES ("e4")
  </task>
The task element has acquired a child element, connections, which is itself a list of connection elements. The connection specifies a (usually unique) name, a javax.sql.DataSource implementation class and machine/database/user details. Note that all connections have auto-commit off and transaction isolation set to serializable.

The three INSERT statements are separated by lines that contain only \g and whitespace. The SQL task will split the script at the \g lines and execute each statement separately. The \g lines are used because they are used in the Postgres utility psql. For Oracle, sqlplus uses a slash, /, and ScriptRunner does too. Either choice can be changed by setting the SQLTask attribute statement-split to a Java regular expression that will be used to split the entire task element text into SQL statements. (For comparison, the Postgres regular expression is "[\n\r]++[ \t]*\\g[ \t]*[\n\r]++".)

Once the entire script has run, ScriptRunner will issue a COMMIT. If one statement fails, e.g. the "e4" insert, on re-start the task will start at the statement with the index of the failed statement, i.e. the third statement.

For large SQL scripts, it is easier to include *.sql files:

  <task xmlns:xi="http://www.w3.org/2001/XInclude" name="SQL file" type="SQL">
    <connections>
      <connection dataSourceName="mt_app"
                  dataSourceClass="org.postgresql.ds.PGPoolingDataSource" 
                  serverName="dbserver" databaseName="mt_prod" 
                  user="mt_user" password="secret" />
    </connections>
    <xi:include href="work.sql" parse="text" />
  </task>
The text of file work.sql is substituted into the SQL task element before the SQLTask object is created. Like the previous example, the file should be separated into individual statements by \g, / or … As far as the SQLTask is concerned there is no difference between this and the previous example. For the developer, working with a separate sql file - one that can be tested with the RDBMS's command line interface and is recognized by SQL editors - is noticeably easier.

A common approach when looking at a piece of work is to break it down into smaller pieces, usually logically separate and of less complexity. This reductionism is expressed by moving independent work into separate components. With ScriptRunner, both the components and top level are described in a task file. These can be run all together or independently, useful for debugging or resurrecting a failed run.

An example of a task description that organizes components is:

  <task xmlns:xi="http://www.w3.org/2001/XInclude" type="Serial" name="MT Apps">
    <connections>
      <!-- ScriptRunner audit trail user -->
      <xi:include href="sr.xml" parse="xml" />
    </connections>
    <xi:include href="create/create.xml" parse="xml" />
    <task type="If" name="MT Apps - Do initialization" ifname="initialize">
      <xi:include href="initialize/initialize.xml" parse="xml" />
    </task>
    <xi:include href="activity/run_po.xml" parse="xml" />
  </task>
There are three components in different directories: create, initialize and activity. The components' XML task descriptions are included using XInclude, as is an XML fragment (i.e. not a proper document) defining the srAudit connection in sr.xml. Unlike the SQL script, these are included as part of the XML document, as specified by the parse="xml" attribute.