For now only XML format is supported

To create configuration from XML file use the following code:

        ConfigurationFactory factory = new ConfigurationFactory();
        factory.setResourceURL(resourceURL);
        Configuration configration = cf.createConfiguration();
    

Automatically discovers a driver based on the specified url.

Note: Autodiscovery serves for convenience purposes and works only with JDBC drivers and URL schemes supported by Scriptella, in other cases driver's name has to be specified manually.

General information

Example

    <!-- connection to a SQL Server database via JTDS driver-->
    <connection driver="auto" url="jdbc:jtds:sqlserver://localhost:1433/northwind"/>

    <!-- connection to a JNDI bound datasource -->
    <connection driver="auto" url="jndi:ds/appDataSource"/>

    <!-- connection to a Spring managed datasource -->
    <connection driver="auto" url="spring:myDataSource"/>

    <!-- connection to Mail driver-->
    <connection driver="auto" url="mailto:user@host?subject=Hello">
        mail.smtp.host=mail.host.name
        mail.user=user
        mail.password=password
    </connection>

CSV Driver for Scriptella.

Note: The driver doesn't use SQL syntax

General information

Driver Specific Properties

Query Syntax

,food
,^Beverages$
    

The columns of the matched row can be referenced by name in nested queries/scripts. It is also possible to reference columns by an index, i.e. $1, $2, ...

Script Syntax

<script>
id,priority,summary,status
1,Critical,NullPointerException in Main class,Open
</script>
    

IMPORTANT: Always use commas as a column separator inside both <script> and <query> elements no matter what separator is used in files being parsed or produced. This decision allows switching between different formats like tab or semicolumn while keeping scripts and queries unchanged.

Properties substitution

Examples

<connection id="in" driver="csv" url="data.csv" classpath="lib/opencsv.jar">

</connection>
<connection id="out" driver="csv" url="report.csv" classpath="lib/opencsv.jar">
    #Use empty quote to turn off quoting
    quote=
    separator=;
</connection>

<script connection-id="out">
    ID,Priority,Summary,Status
</script>

<query connection-id="in">
    <!--Empty query means select all-->
    <script connection-id="out">
        $rownum,$priority,$summary,$status
    </script>
</query>

General information

Driver Specific Properties

Examples

    <connection id="c1" driver="db2" url="jdbc:db2://localhost:50000/sample" user="username" password="password">
    </connection>

General information

Driver Specific Properties

Examples

    <connection driver="h2" url="jdbc:h2:file:database" user="sa">
    </connection>

    <!--empty URL attribute means private In-Memory database -->
    <connection driver="h2"/>

General information

Driver Specific Properties

Example

    <connection driver="hsqldb" url="jdbc:hsqldb:file:db" user="sa">
        hsql.shutdown_on_exit=true;
    </connection>

This driver allows to embed Java code into <query> and <script> elements. This code is compiled by Janino and executed by Scriptella engine. For simple scripting solutions refer to JEXL expression language which is interpreted and less powerful.

Janino scripting elements are implicit subclasses of Query and Script base classes.

General information

Driver Specific Properties

Script Syntax

In other words the script element is an implementation of execute method of JaninoScript class.

Query Syntax

In other words the query element is an implementation of execute method of JaninoQuery class.

Properties substitution

Example

    <connection driver="janino">/>

    <query>
        set("name", "John);
        next();
        <script>
            System.out.println("Processing: "+get("name"));
        </script>
    </query>

Processing: John

ODBC sample from Scriptella examples distribution also shows several use-cases for Janino.

JEXL Driver for Scriptella.

The driver uses commons-jexl library which is a part of Scriptella Core. JEXL scripts are interpreted at runtime and generally work slower than compiled scripts (e.g. Janino), nevertheless JEXL scripts are easier to write and maintain especially for non-Java developers.

General information

Query and Script Syntax

Bind variables from parent elements are accessible as simple variables. Assignment operator v="value" makes variable v available for nested ETL elements.

Implicit variable query is available in <query> elements. This variable should be used to produce a result set:

    <query>
        i=0; while(i lt 10) {
            i=i+1;
            query.next(); // Causes child script to be executed
        }
        <script>......</script>
    </query>

Examples

<connection id="jexl" driver="jexl"/>
<connection id="out" driver="oracle" url="jdbc:oracle:thin:@localhost:1521:DB"/>

<query connection-id="jexl">
    i=0;while (i lt 10) {
        i=i+1;
        login='login'+i;
        query.next();
    }
    <script connection-id="out">
        INSERT INTO Table(ID, Login) VALUES (?i, ?login);
    </script>
</query>

This driver allows to work with JNDI-bound datasource connections, useful in J2EE environment

This driver acts as a proxy and relies on JDBC Bridge.

General information

Notes

Depending on your environment you may need to specify additional JNDI properties inside a connection element. These settings will take precedence over jndi.properties and System properties.

Example

    <connection driver="jndi" url="ds/appDataSource">
        #Default settings should work in J2EE environment, but you may reconfigure JNDI here
        #java.naming.factory.initial=jndi.provider.Factory
    </connection>

LDAP Driver for Scriptella.

Details of LDIF syntax are described in RFC 2849, LDAP Search Filters are described in RFC 2254

This driver supports ${} syntax for variables substitution

General information

Driver Specific Properties

This driver is JNDI-based and uses com.sun.jndi.ldap.LdapCtxFactory LDAP provider. You may specify additional JNDI settings as connection properties. For a complete list of JNDI properties see LDAP Naming Service Provider for the JNDI

Example

<connection driver="ldap" url="ldap://localhost:389/dc=scriptella" user="cn=root,dc=scriptella" password="secret" >
    search.scope=subtree
    #search.basedn=dc=scriptella
</connection>

<query>
    
     <![CDATA[(&(objectClass=groupOfUniqueNames)(uniqueMember=$dn))]]>
</query>

<script>
#LDIF add entry
#Avoid leading whitespaces because LDIF is a whitespace sensitive format.
dn: ou=people,dc=scriptella
objectClass: organizationalUnit
ou: people
</script>

The script element is used to add ou=people,dc=scriptella entry.

This driver allows to search through Lucene v.2.x compatible indexed data.

Lucene scripting elements are implicit subclasses of Query base classe.

General information

Driver Specific Properties

Note: if MultiFieldQueryParser is used instead of QueryParser then all the query's terms must appear, but it doesn't matter in what fields they appear.

Example

    <connection id="search" driver="lucene" url="file://PATH_TO_YOUR_LUCENE_INDEX">
        fields=default_field
    </connection>
    <connection id="out" driver="text" url="tst://testfile"/>
    <query id="search">
        title:script luc*e
        <script connection-id="out">$rownum+'. '+$title+' : '+$default_field</script>
    </query>

E-Mail Driver for Scriptella.

General information

Driver Specific Properties

Properties substitution

Notes:

• In addition to the above mentioned properties the driver supports all JavaMail environment properties.
• Connection properties may be specified inside a connection element or as a a part of URL, e.g. mailto:user@nosuchhost.com?subject=Hello
• This driver supports dynamic properties substitution in an URL, e.g. mailto:$email?subject=$subject ,it makes possible sending emails to recipients specified by in a row set.

Examples

The following example sends a simple text message

<etl>
    <connection driver="mail" url="mailto:user@nosuchhost.com?subject=Hello"
            classpath="mail.jar:activation.jar">
        mail.smtp.host=mail.host.name
        mail.user=user
        mail.password=password
        mail.from=Firstname Lastname &lt;user@nosuchhost.com&gt;
    </connection>
    <script>Message produced by Scriptella ETL</script>
</etl>
    

Use type=html property to send HTML content.

<etl>
    <connection driver="mail" url="mailto:user@nosuchhost.com?subject=Hello"
            classpath="mail.jar:activation.jar">
        type=html
        mail.smtp.host=mail.host.name
        mail.user=user
        mail.password=password
        mail.from=Firstname Lastname &lt;user@nosuchhost.com&gt;
    </connection>
    <script><![CDATA[
        <html>
            <body>
                Hello,
                <hr>
                <a href="http://scriptella.javaforge.com/" title="Powered by Scriptella ETL">
                    <img src="http://scriptella.javaforge.com/images/scriptella-powered.gif" 
                            width="88" height="31" border="0" alt="Powered by Scriptella ETL">
                </a>
            </body>
        </html>]]>
    </script>
</etl>
    

The following example sends a message to a list selected from the database

<etl>
    <connection id="mail" driver="mail" url="mailto:$email?subject=Hello $name"
            classpath="mail.jar:activation.jar">
        mail.smtp.host=mail.host.name
        mail.user=user
        mail.password=password
        mail.from=Administrator &lt;user@nosuchhost.com&gt;
    </connection>
    <connection id="db" .../>
    <query connection-id="db" >
        SELECT * FROM Users
        <script connection-id="mail">
            #$rownum
            Message produced by Scriptella ETL
        </script>
   </query>
</etl>
    

Note: The driver tries to load any available JDBC driver for Microsoft SQL Server. It supports both Microsoft and JTDS JDBC drivers.

General information

Driver Specific Properties

Examples

    <connection id="c1" driver="mssql" url="jdbc:sqlserver://localhost;database=test" user="username" password="password">
    </connection>
    <connection id="c2" driver="mssql" url="jdbc:jtds:sqlserver://host/test" user="username" password="password">
    </connection>

Note: The driver tries to load MySQL JDBC driver.

General information

Driver Specific Properties

Examples

    <connection driver="mysql" url="jdbc:mysql://localhost:3306/test" user="username" password="password">
    </connection>

General information

Driver Specific Properties

Examples

    <connection id="c1" driver="oracle" url="jdbc:oracle:thin:@localhost:1521:DB" user="sys as sysdba" password="password">
    </connection>

    <connection id="c2" driver="oracle" url="jdbc:oracle:oci:@localhost:1521:DB" user="scott" password="tiger">
        plsql=true;
    </connection>

    <script connection-id="c2"/>
        CREATE OR REPLACE PACKAGE PK_TEST_PACKAGE1 AS
            PROCEDURE PP_TEST_PROCEDURE1( param   IN  VARCHAR2);
        END PK_TEST_PACKAGE1;
        /
        CREATE OR REPLACE PACKAGE PK_TEST_PACKAGE2 AS
            PROCEDURE PP_TEST_PROCEDURE2( param   IN  VARCHAR2);
        END PK_TEST_PACKAGE2;
        /
    </script>

General information

Driver Specific Properties

Examples

    <connection driver="posgresql" url="jdbc:postgresql://localhost:5432/test" user="username" password="password">
    </connection>

Scriptella bridge for the JSR 223: Scripting for the Java™ Platform.

The driver relies on javax.script package from Java SE 6. The default language expected by the driver is JavaScript which is bundled with JRE.

General information

Driver Specific Properties

Query and Script Syntax

Scripts and queries can reference variables from ancestor elements. Declared variables are exposed to nested elements.

Implicit variable query is available in <query> elements. This variable should be used to produce a result set:

    <query><![CDATA[
        var v1 = 'This variable is visible in a child script';
        for (var i = 0; i < 10; i++) {
            query.next(); // Triggers execution of a child script.
        }]]>
        <script>
            ......
            v2 = v1 + '!';
            ......
        </script>
    </query>

<connection id="script" driver="script"/>
<connection id="out" driver="oracle" url="jdbc:oracle:thin:@localhost:1521:DB"/>
<connection id="log" driver="script" url="log.txt"/>

<query connection-id="script">
    <![CDATA[
    for (var i = 0; i < 10; i++) {
        login = 'login' + i;
        //You can instantiate Java objects and invoke static methods
        var now = new java.sql.Timestamp(java.lang.System.currentTimeMillis());
        query.next(); //Executes a child script element
    }]]>

    <!-- Inserts a parameterized row into a database -->
    <script connection-id="out">
        INSERT INTO Table(ID, Login, Login_Time) VALUES (?i, ?login, ?now);
    </script>
    
    <!-- Logs the message using MessageFormat class and parent context variables -->
    <script connection-id="log">
        // create Java String array of 2 elements
        var a = java.lang.reflect.Array.newInstance(java.lang.Object, 2)
        a[0] = now;a[1] = i;
        println(format.format(a));
    >/script>
</query>

Driver for running Scriptella ETL files.

When ETL file is executed from another Scriptella ETL file variables context is propagated to the callee.

General information

Driver Specific Properties

Script Syntax

Use standard ${name} or $name syntax for properties substitution.

<query> elements are not supported.

<etl>
    <connection id="createdb" driver="scriptella" url="createdb.etl.xml"/>
    <connection id="db" driver="oracle" url="jdbc:...."/>

    <script connection-id="db">
        INSERT INTO ...
    </script>
</etl>

<etl>
    <connection id="db" driver="oracle" url="jdbc:...." />
    <connection id="scriptella" driver="scriptella" />

    <query connection-id="db">
        <!-- Selects User_ID and EMail, this variables are visible in nested scripts -->
        SELECT User_ID, Email FROM Users
        <script connection-id="scriptella">
            genreport.etl.xml <!-- Generates report for User_ID -->
            sendreport.etl.xml <!-- Sends produced report to Email -->
        </script>
    </query>
</etl>

This driver allows working in The Spring Framework environment and provides an ability to locate Spring-managed data sources.

This driver acts as a proxy and relies on JDBC Bridge.

Important:

General information

Driver Specific Properties

ETL Executor Configuration

Example

Spring Application Context

<beans>
    <bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName"><value>org.hsqldb.jdbcDriver</value></property>
        <property name="url"><value>jdbc:hsqldb:mem:spring</value></property>
        <property name="username"><value>sa</value></property>
        <property name="password"><value></value></property>
    </bean>
    <bean id="progress" class="scriptella.interactive.ConsoleProgressIndicator"/>
    <bean id="executor" class="scriptella.driver.spring.EtlExecutorBean">
        <property name="configLocation"><value>etl.xml</value></property>
        <property name="progressIndicator"><ref local="progress"/></property>
        <property name="properties"><map>
            <entry key="tableName"><value>Spring_Table</value></entry>
            </map>
        </property>
    </bean>
</beans>

<etl>
    <connection driver="spring" url="datasource"/>
    <script>
        CREATE TABLE ${tableName} (
            ID INT
        )
    </script>
</etl>

EtlExecutor exec = (EtlExecutor) beanFactory.getBean("executor");
exec.execute();

Callable exec = (Callable) beanFactory.getBean("executor");
exec.call();

Note: The driver tries to load any of the known JDBC drivers in the following order:

• jdbc3,jdbc2 or jdbc driver from Sybase.
• JTDS driver

General information

Driver Specific Properties

Examples

    <connection driver="sybase" url="jdbc:sybase:Tds:localhost:2638/tst" user="username" password="password">
    </connection>

Text Driver for Scriptella.

Text driver does depends on additional libraries and is generally faster than CSV or Velocity driver.

Note: The driver doesn't use SQL syntax

General information

Driver Specific Properties

Query Syntax

If a line or a part of it matches the pattern this match produces a virtual row in a result set. The column names in a virtual result set correspond to matched regex group names. For example query foo(.*) matches foobar line and the produced result set row contains two columns(groups): 0-foobar, 1-bar. These columns can be referenced in child script or query elements by a numeric name or by a string name columnN.

It also possible to specify more than one regular expressions to match file content. Specify each regular expression on a separate line to match them using OR condition.

The Text driver uses java.util.regex implementation for pattern matching. See java.util.Pattern for supported syntax Javadoc.

Additional notes:

• Regular expressions matching is case-insensitive
• Empty query selects all lines from the input file.
• The 0(zero) column name in the produced result set contains the matched line.
• Leading and trailing whitespaces in query element and input file lines are trimmed by default.
• Use ^ and $ boundary matchers to match the whole line.

<query>
  ^ERROR: (.*)
  WARNING: (.*Failed.*)
  ([\d]+) errors?
</query>
    

1. selects lines starting with ERROR: prefix
2. selects WARNING lines having Failed substring
3. selects lines containg a number of errors, e.g. "Found 5 errors".

Log file started...
INFO: INIT
WARNING: CPU is slow
WARNING: Failed to increase heap size
ERROR: Process interrupted
Operation completed with 1 error.

Script Syntax

Additional notes:

• Lines in the outputted file are separated by a EOL string specified by eol connection property.
• Leading and trailing whitespaces in the output file lines are trimmed by default.
• No escaping is performed when properties are expanded. Use String.replace or other escaping techniques to achieve output similar to CSV etc.
• If a script is executed multiple times (e.g. inside a parent query) the output is appended to the file content.

<script>
    Inserted a record with ID=$id. Table=${table}
</script>
    

Inserted a record with ID=1. Table=system
    

Properties substitution

Examples

<connection id="in" driver="text" url="data.csv">
</connection>
<connection id="out" driver="text" url="report.csv">
</connection>

<script connection-id="out">
    ID;Priority;Summary;Status
</script>

<query connection-id="in">
    <script connection-id="out">
        $rownum;$column0;$column1;$column2
    </script>
</query>

Velocity Adapter for Scriptella.

The content inside scipt elements managed by this driver is evaluated by Velocity engine and the output is sent to the URL specified as connection parameter.

General information

Driver Specific Properties

Example

<connection driver="scriptella.spi.velocity.Driver" url="report.html">
    encoding=UTF-8;
</connection>

XPath Driver for Scriptella.

General information

Driver Specific Properties

Query Syntax

The query is executed on a XML Document and produces a rowset for matched nodes. The attribute and element values can be referenced from nested scripts/queries. The following example illustrates querying mechanism:

Example:

XPath: /A selects root element <A>

<A B="1">
    <B>2</B>
    <C>3</C>
</A>

Additional notes:

• Currently only Node Set can be selected in XPath expressions, i.e. attributes or elements but not String, Boolean or Number

Script Syntax

Properties substitution

Examples

<connection id="in" driver="xpath" url="data.xml"/>
<connection id="out" driver="text" url="report.csv"/>

<query connection-id="in">
    /HTML/BODY/TABLE/TR
    <script connection-id="out">
        $rownum;${td[0]};${td[1]}
    </script>
</query>

As specified in Service Provider API all JDBC drivers are registered via this bridge. This procedure is transparent to end user, he only have to specify jdbc driver class name or an alias.

General information

JDBC Bridge Properties

JDBC bridge makes the following configuration properties available in configuration element:

Properties Substitution

Example:

var=_name
id=11
--------------------------------------
select * FROM table${var} where id=?id
-- is transformed to a JDBC prepared statement---
select * FROM table_name where id=?
-- where parameter id=11

Notes:

• $ prefixed expressions are substituted in all parts except comments.
• ? prefixed expressions are not substituted inside quotes and comments.

Example:

--only ${prop} and ?surname are handled
     SELECT * FROM "Table" WHERE NAME="?John${prop}" and SURNAME=?surname;
    

Examples

    <connection id="in" driver="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:file:tmp" user="sa" classpath="hsqldb.jar">
        #Disable cache - just for example
        statement.cache=0
        #Set SQL statements separator
        statement.separator=;
    </connection>

    <connection id="out" driver="h2" url="jdbc:h2:file:out" user="sa"/>
    <query connection-id="in">
        SELECT * from Bug
        <script connection-id="out">
            INSERT INTO Bug VALUES (?ID, ?priority, ?summary, ?status);
        </script>
    </query>

Service Provider Requirements

• Provide a class implementing Scriptella Driver Interface. This class must have a public no-arg constructor. We recommend to use AbstractScriptellaDriver as a base.
• Create a subclass of Provider Exception. Override methods if necessary

Driver Registration

    <connection driver="provider.class.name" url="provider:url"/>

    <connection driver="provider.class.name" url="provider:url" classpath="driver.jar;lib/driver-addons.jar"/>

Driver Alias

Additional Recommendations

• url is not a required attribute for custom providers, nevertheless it may make sense to specify URL protocol for your driver.
• If your solution performs scripts compilation and this procedure is expensive, use caching based on Resource indentities as described in javadoc for Connection.
• Pack your provider as a JAR file setting specification title, version and other attributes in manifest. Adding a simple readme file inside a JAR may help users to quickly get how to use it.
• Scriptella built-in drivers can be used as a reference implementation to create a new driver.

How to register

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

    <taskdef resource="antscriptella.properties" classpath="scriptella.jar;hsqldb.jar"/>

EtlExecuteTask (etl)

Parameters

Nested Elements

Examples

<etl/>

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

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

EtlTemplateTask (etl-template)

Parameters

Nested Elements

Supported Templates

• Default(no name) - Produces a simple ETL template for a quick start.
• DataMigrator - Produces an ETL template for transferring data between tables of different databases.

Examples

<etl-template/>

    <property file="etl.properties"/>
    <!--
    Ant properties
        driver,class,user,password
    must be set before calling "DataMigrator" etl-template 
    -->
    <etl-template name="DataMigrator"/>

Examples

scriptella

scriptella name

scriptella name.etl.xml

scriptella -t