Scriptella reference

When to use Scriptella

Scriptella is a good fit when you need to:

• Store and execute database SQL without depending on vendor command-line tools, while retaining JDBC features such as references to BLOB content.
• Work with several data sources—for example, reading statistics from one database and storing them in another.
• Keep queries visible as SQL instead of expressing them through a visual query designer.
• Generate test data during execution instead of storing large generated files.
• Perform schema evolution or data migration.

For one-to-one database replication, a dedicated replication tool will usually be a better choice.

<properties>

Defines properties that can be substituted in other ETL elements, much like Ant properties. If a property is declared more than once, the first value takes precedence. Use <include> to insert properties from an external file.

<connection>

Defines a connection to a data source. Required attributes depend on the driver. JDBC connections commonly use url, user, and password; they may also specify a catalog or schema.

id

Required when an ETL declares more than one connection. Scripts and queries refer to this value with connection-id.

url

A driver-specific URL. File-based drivers accept absolute locations or paths relative to the ETL file.

driver

A driver class name or alias. The default, auto, attempts to infer the driver from the URL. See the driver matrix.

classpath

Optional driver libraries. Paths are resolved relative to the ETL file and separated with the platform path separator. Absolute URLs are also supported. Libraries in Scriptella's boot classpath take precedence.

lazy-init

When true, delays opening the connection until an element uses it. The default is false.

Lazy initialization is useful when every element using a connection is conditional, or when another connection must first create a database user or produce a required file.

Element text sets driver-specific connection properties. Some drivers also accept properties in their URL.

<!-- Connect to Oracle as the sys user. -->
<connection driver="oracle"
            url="jdbc:oracle:thin:@localhost:1521:DB"
            user="sys as sysdba"
            password="password">
  plsql=true
</connection>

<script>

Contains executable content in the language understood by its connection. A script may include external files and use variables supplied by a parent query.

connection-id

Selects the connection used to execute the script.

new-tx

When true, executes the script using a dedicated connection instead of the shared transaction.

if

Controls conditional execution. For example, if="rownum gt 1 and name != null and name.length() gt 0" executes only when the row number is greater than one and name is not empty.

<!-- Use a separate transaction when drop_tables is true. -->
<script connection-id="in" new-tx="true" if="drop_tables">
  DROP TABLE Table1;
  DROP TABLE Table2;
</script>

<query>

Contains a query expression in the language understood by its connection. It executes nested elements once per returned row, exposing each column or attribute as a variable.

connection-id

Selects the connection used for the query. Nested elements inherit the connection ID unless they override it.

if

Controls conditional execution. Values such as true, 1, on, and yes are treated as true.

<!-- Select users and write an LDIF entry for every row. -->
<query connection-id="in" if="migrate_users">
  SELECT * FROM Users;
  <script connection-id="out">
    dn: uid=$user_name,ou=people,dc=scriptella
    objectClass: inetOrgPerson
    uid: $user_name
    cn: $user_name
  </script>
</query>

<onerror>

Defines fallback content to execute when its parent script fails. If the fallback itself fails, another matching <onerror> element on the original script may handle that error. The thrown java.lang.Throwable is available to the handler as the error variable.

type

A regular expression matched against the exception type. Partial matching is allowed.

codes

A comma-separated list of vendor error codes or SQL states. The handler runs when any value matches a code reported by the driver.

message

A regular expression matched against the exception message. Partial matching is allowed.

retry

When true, retries the failed statement after the handler completes. The same handler will not run again for the retry, preventing an infinite loop.

connection-id

Runs the handler through another connection, which is useful for logging or recovery with a Java, text, or other driver.

This handler emulates CREATE TABLE IF NOT EXISTS by dropping an existing table and retrying the original statement:

<script connection-id="in">
  CREATE TABLE Table1;
  <onerror message=".*Table already exists.*" retry="true">
    DROP TABLE Table1;
  </onerror>
</script>

A handler can also pass the error to application code through another connection:

<script connection-id="in">
  INSERT INTO TableName VALUES (1, 'Value1');
  <onerror connection-id="java">
    Throwable error = (Throwable) get("error");
    com.app.ApplicationLogger.logEtlError(error);
  </onerror>
</script>

Implicit variables

SQL queries also expose selected columns as variables to nested elements. Columns can be referenced by name or result-set position, such as ?1 and ?2. Non-SQL queries typically expose an equivalent virtual row set; consult the driver Javadoc for its variables.

Referencing files from SQL

The JDBC bridge accepts ?{file expression} for binary files and ?{textfile expression} for text files. The expression can be a quoted file name or any valid JEXL expression:

INSERT INTO document(id, binary_data, text_data)
VALUES (?id, ?{file binaryPath}, ?{textfile textPath})

File references are passed as stream-valued prepared-statement parameters, allowing BLOBs and CLOBs up to the size supported by the JDBC driver. Uploading a large text file may create a temporary file on disk.

Declarative formatting and parsing

The text, CSV, and Velocity drivers accept formatting rules as connection properties. The same rule parses incoming text or formats an outgoing value, depending on context.

<connection id="csv_in" driver="csv" url="eurusd_in.csv">
  format.RateBid.type=number
  format.RateBid.pattern=#.#
</connection>

This treats RateBid as a decimal number when CSV or text input is read:

<query connection-id="csv_in">
  <script connection-id="db">
    INSERT INTO Rates(TIME, CurrencyPair, Bid, Ask)
    VALUES (?RateDateTime, ?CurrencyPair, ?RateBid, ?RateAsk);
  </script>
</query>

For output, the rule formats values written by the driver:

<connection id="csv_out" driver="csv" url="eurusd_out.csv">
  format.Bid.type=number
  format.Bid.pattern=##.0000
</connection>
<script connection-id="csv_out">
  $CurrencyPair,$TIME,$Bid,$Ask
</script>

See the text driver's formatting reference for the available types and patterns.

Expression example

This query selects rows from the table named by TABLE and inserts the first row into the table named by TABLE2:

<connection driver="hsqldb"/>
<query>
  SELECT V1, V2, V3 FROM $TABLE;
  <script if="rownum == 1">
    INSERT INTO $TABLE2 VALUES (?V1, ?{V2 + V3});
  </script>
</query>

Command-line options

scriptella [options] [file1] [file2] ... [fileN]

Options come first; ETL file names or paths follow, separated by spaces.

System requirements

Apache Ant 1.10.17 is the tested Release 1.3 environment. Other modern Ant versions may work but are not part of the documented release configuration.

Installation

Use scriptella.jar from the binary distribution. It contains the classes and resources required by the Ant tasks.

<taskdef resource="antscriptella.properties"
         classpath="/path/to/scriptella.jar"/>

Add database drivers to the task definition's classpath when you want them available to every connection:

<taskdef resource="antscriptella.properties">
  <classpath>
    <pathelement location="/path/to/scriptella.jar"/>
    <pathelement location="lib/hsqldb.jar"/>
    <pathelement location="lib/jconn2.jar"/>
  </classpath>
</taskdef>

Drivers loaded this way are on Scriptella's boot classpath, so individual <connection> elements do not need a classpath attribute.

<etl> task

<etl/>

<etl file="name"/>
<!-- Or specify the full name. -->
<etl file="name.etl.xml"/>

<etl>
  <fileset dir="db" includes="*.etl.xml"/>
</etl>

<etl-template> task

<etl-template/>

<property file="etl.properties"/>
<!-- Set driver, class, user, and password before this task. -->
<etl-template name="DataMigrator"/>

Attributes

The following read-only attributes are available:

ExecutedStatementsCount

Number of statements executed by all connections in the ETL task.

StartDate

Date and time when the ETL task started.

Throughput

Number of statements executed per second by the managed task.

Operations

The cancel operation terminates the ETL task and attempts to roll back changes made during execution.

Autodiscovery of JDBC drivers

When no driver name is specified, Scriptella can select a supported JDBC adapter from the connection URL. Internally, the auto driver performs this selection:

<connection url="jdbc:sybase:Tds:host:2048/database"/>

In this example, the URL causes the auto driver to select the Sybase adapter.

Performance and batching

JDBC batching sends multiple commands to the database in one call. Set statement.batchSize to the number of statements Scriptella should combine before submitting a batch.

statement.fetchSize gives the JDBC driver a hint about how many rows to fetch when more result-set rows are needed:

<connection url="jdbc:oracle:thin:@localhost:1521:orcl">
  statement.fetchSize = 1000
</connection>

Combining a suitable fetch size for queries with batching for bulk loads can significantly improve performance, especially when databases are on different machines. See the JDBC package batching documentation for details and examples.

Accessing LDAP directories

Scriptella lets transformations use a language suited to each data source. Its LDAP driver supports LDIF scripts and LDAP search-filter queries.

A third-party JDBC-to-LDAP bridge may also be used when SQL-like scripts and queries are preferred.

Working with CSV and text data

The CSV driver queries and generates CSV files. The text driver provides a general-purpose way to read and write text data. A database's own text-table feature can be another option when supported.

Working with XML data

The XPath driver queries XML files with XPath expressions. The text driver can generate XML output.

Using Java code

The bundled Janino provider offers a Java bridge for cases where invoking Java from an ETL file is useful. It exposes properties and methods to <script> and <query> elements.

There are two main ways to integrate Java logic:

• Implement a Scriptella service provider (driver) for the most capable integration.
• Call compiled application code from Janino elements by adding its JAR to the Janino connection's classpath. This keeps the called classes independent of the Scriptella API.

Other Java compilers or interpreters can also be plugged in through custom Scriptella drivers.

Interaction with scripting languages

Scriptella supports JSR 223-compatible scripting languages through the javax.script bridge driver. The specific script engines available depend on the runtime and libraries on the classpath.

Producing reports with Velocity

Velocity is a Java template engine that can generate reports. The Velocity driver runs templates in <script> elements. A typical report:

1. Prints a header.
2. Queries one or more data sources and produces the report body.
3. Prints a footer.

The Primes example demonstrates a simple Velocity report.

URL schemes supported by Scriptella

Connections can refer to external resources with URL protocols supported by the Java runtime, including HTTP, FTP, file, and JAR URLs:

<connection driver="xpath" url="https://example.org/data.xml"/>
<connection driver="csv" url="https://example.org/data.csv"/>
<connection driver="text" url="ftp://example.org/report.txt"/>

Using Scriptella as a database schema evolution tool

The DB Upgrade example shows how to build a simple database upgrade and downgrade script. A basic upgrade framework needs to:

• Create and initialize the current schema when no database exists.
• Apply incremental updates that migrate an existing database from version X to version Y.

Scriptella's ability to work with multiple data sources also permits migrations between database vendors or synchronization between systems such as LDAP and a database.

Because Scriptella is lightweight, applications can bundle it and invoke schema upgrades during startup, for example from web-application initialization code.