06.03 - Converters

From file...

• • to conf.dbunit

Category-Name: structured

What: This 'structured' converter will convert a resource of category 'file' to a resource of category 'conf.dbunit'.

CONVERT {resourceToConvert<Res:file>} TO conf.dbunit (structured) AS {converted<Res:conf.dbunit>}

Input:

• • resourceToConvert<Res:file>: The name (in the context) of the resource which references a configuration file for DbUnit. This file must be a '.properties' file (list of properties key / value using '=' like separator)

Output:

• • converted<Res:conf.dbunit>: The name of the converted resource (Resource of category conf.dbunit).

Example:

LOAD path/to/dbunit-configuration.properties AS dbunit-conf.file

CONVERT dbunit-conf.file TO conf.dbunit (structured) AS conf

The DbUnit "features & properties" supported are: (See DbUnit documentation: http://www.dbunit.org/properties.html).

Batched statements :

Case sensitive table names :

Qualified table names :

Table types :

Datatype factory (Cf. issue 789) :

Batch size :

Metadata handler :

Escape pattern :

Remark: The DbUnit property: http://www.dbunit.org/properties/primaryKeyFilter exist via the category of TA resource: ‘conf.dbunit.ppk’.

• • to conf.dbunit.ppk

Category-Name: structured

What: This 'structured' converter will convert a resource of category 'file' to a resource of category 'conf.dbunit.ppk'.

CONVERT {resourceToConvert<Res:file>} TO conf.dbunit.ppk (structured) AS {converted<Res:conf.dbunit.ppk>}

Input:

• • resourceToConvert<Res:file>: The name (in the context) of the resource which references a configuration file to define the pseudo primary keys. This configuration file must be of type '.properties'. (For each property, the key is the name of a Table, the value is the name of a column or a list of columns separated with comma and the '=' character is used like separator)

Note: For the table which name is used as key, its pseudo primary key is the column or the list of columns defined in the value part of the property.

Output:

• • converted<Res:conf.dbunit.ppk>: The name of the converted resource (Resource of type conf.dbunit.ppk)

Example:

LOAD path/to/valid_ppk.properties AS ppk.file

CONVERT ppk.file TO properties (structured) AS ppk.properties

CONVERT ppk.properties TO conf.dbunit.ppk (from.properties) AS ppk

• • to parameter.indexed.sql

Category-Name: from.text

What: This 'from.text' converter will convert a resource of category 'file' to a resource of category 'parameter.indexed.sql'.

CONVERT {resourceToConvert<Res:file>} TO parameter.indexed.sql (from.text) AS {converted<Res:parameter.indexed.sql>}

Input:

• • resourceToConvert<Res:file>: The name (in the context) of the resource which references a file which each line defines the value of a sql query parameter. Each line contains two character strings separated with the character '=':

    • The first character string corresponds to the parameter position in the SQL query.

    • The Second one corresponds to the value.

Remark: None of the two character strings can be empty.

Output:

• • converted<Res:parameter.indexed.sql>: The name of the converted resource (Resource of category parameter.indexed.sql)

Example:

LOAD path/to/parameter-indexed_value.properties AS value.file

CONVERT value.file TO parameter.indexed.sql (from.text) AS value.properties

• • to parameter.named.sql

Category-Name: from.text

What: This 'from.text' converter will convert a resource of category 'file' to a resource of category 'parameter.named.sql'.

CONVERT {resourceToConvert<Res:file>} TO parameter.named.sql (from.text) AS {converted<Res:parameter.named.sql>}

Input:

• • resourceToConvert<Res:file>: The name (in the context) of the resource which references a file which each line defines the value of a sql query parameter. Each line contains two character strings separated with the character '=':

    • The first character string corresponds to the parameter name in the SQL query.

    • The Second one corresponds to the value.

Remark: None of the two character strings can be empty but the name can be constituted with only space characters.

Output:

• • converted<Res:parameter.named.sql>: The name of the converted resource (Resource of category parameter.named.sql)

Example:

LOAD path/to/parameter-named_value.properties AS value.file

CONVERT value.file TO parameter.named.sql (from.text) AS value.properties

• • to query.sql

Category-Name: query

What: This 'query' converter will convert a resource of category 'file' to a resource of category 'query.sql'.

CONVERT {resourceToConvert<Res:file>} TO query.sql (query) AS {converted<Res:query.sql>}

Input:

• • resourceToConvert<Res:file>: The name (in the context) of the resource which references a file which respects the following rules:

    • The file must contain only one query.

    • The query can be written on one or several lines.

    • The query end with the character ';'

    • Comments at SQL format can be inserted in the file.

Output:

• • converted<Res:query.sql>: The name of the converted resource (Resource of category query.sql).

Example:

LOAD sql/my_query.sql AS my.file

CONVERT my.file TO query.sql (query) AS my.query

• • to script.sql

Category-Name: script

What: This 'script' converter will convert a resource of category 'file' to a resource of category 'script.sql'. It is possible to add an option for the encoding as well as the SQL block delimiter.

CONVERT {resourceToConvert<Res:file>} TO script.sql (script) AS {converted<Res:script.sql>} [WITH ENCODING {encoding} AND DELIMITER {delimiter}]

Input:

• • resourceToConvert<Res:file>: The name (in the context) of the resource which references a file whose content is a SQL script.

  • Optional - encoding: Parameter representing the query file encoding. Default value: "UTF-8".

  • Optional - delimiter: Parameter representing the SQL block delimiter. Default value: "@@". (can be used in conjunction with encoding or by itself - in which case encoding will take its value by default.

Output:

• • converted<Res:script.sql>: The name of the converted resource (Resource of type script.sql)

Example

LOAD path/to/my_script.sql AS my_script.file

CONVERT my_script.file TO script.sql (script) AS script [USING str_encoding, str_delimiter]

Remarks:

• • In your SQL script the delimiter should enclose any block of code that should be stored and passed in it's entirety to the database server.

  • Examples:

  • Creating a MySQL procedure:

Example

DROP PROCEDURE IF EXISTS `some_procedure`;

@@

CREATE PROCEDURE `some_procedure`(...)

BEGIN

...

END;

@@

• • Creating a PL/pgSQL function:

Example

@@

CREATE OR REPLACE FUNCTION somefunc() RETURNS ... AS $$

DECLARE

...

BEGIN

...

DECLARE

...

BEGIN

...

END;

RETURN ...;

END;

$$ LANGUAGE plpgsql;@@

• • Calling a stored PL/SQL procedure with delimiter set to "<DELIMITER>":

Example

<DELIMITER>

BEGIN

some_procedure;

END;

<DELIMITER>

• In case of nested SQL blocks you need only englobe the top level block with the delimiter.

  • Comments: refrain from using comments at the end of a line of code because it might induce a malfunction if it contains certain characters.

From directory to dataset.dbunit

Category-Name: dataset

What: This 'dataset' converter will convert a resource of category 'directory' to a resource of category 'dataset.dbunit'.

CONVERT {resourceToConvert<Res:directory>} TO dataset.dbunit (dataset) AS {converted<Res:dataset.dbunit>}

Input:

• • resourceToConvert<Res:directory>: The name (in the context) of the resource which references a directory (Resource of category directory).This directory must contain at the root a file named 'table-ordering.txt' which contains an ordered list of tables to add to the dataset. Each line of the file is a relativ path to the root directory toward the csv file containing the table.

Note: The relativ path of the file is expected WITHOUT the extension '.csv'.

Output:

• • converted<Res:dataset.dbunit>: The name of the converted resource (Resource of category dataset.dbunit)

Example:

LOAD csv/csv1 AS csv1.file

CONVERT csv1.file TO directory (filesystem) AS csv1.dir

CONVERT csv1.dir TO dataset.dbunit (dataset) AS csv1.dataset

From properties to conf.dbunit.ppk

Category-Name: from.properties

What: This 'from.properties' converter will convert a resource of category 'properties' to a resource of category 'conf.dbunit.ppk'.

CONVERT {resourceToConvert<Res:properties>} TO conf.dbunit.ppk (from.properties) AS {converted<Res:conf.dbunit.ppk>}

Input:

• • resourceToConvert<Res:properties>: The name (in the context) of the resource which references a '.properties' file (Resource of category properties). For each property, the key is a Table name, the value is a column or columns list separated with comma.

Note: For the table which name is used as key, its pseudo primary key is the column or the list of columns defined in the value part of the property.

Output:

• • converted<Res:conf.dbunit.ppk>: The name of the converted resource (Resource of category conf.dbunit.ppk)

Example:

LOAD path/to/valid-ppk.properties AS ppk.file

CONVERT ppk.file TO properties (structured) AS ppk.properties

CONVERT ppk.properties TO conf.dbunit.ppk (from.properties) AS ppk

From result.sql to dataset.dbunit

Category-Name: dataset

What: This 'dataset' converter will convert a resource of category 'result.sql' to a resource of category 'dataset.dbunit'.

CONVERT {resourceToConvert<Res:result.sql>} TO dataset.dbunit (dataset) AS {converted<Res:dataset.dbunit>} USING {config<Res:file>}

Input:

• • resourceToConvert<Res:result.sql>: The name (in the context) of the resource which references a 'result.sql' resource. It corresponds to the result of a SQL query 'SELECT'.

  • config<Res:file>: The name of the complementary resource which references a configuration file which contains only one key / value: 'tablename' separated of the value with the character ':'. It's mandatory and can be define with an inline instruction. (A result.sql resource does not have Table name and to transform a result.sql in dataset.dbunit we need a Table name).

Output:

• • converted<Res:dataset.dbunit>: The name of the converted resource (Resource of type dataset.dbunit)

Example:

CONVERT insertion_query.resultset TO dataset.dbunit (dataset) USING $(tablename : <name_Table>) AS dataset

From xml...

• • to dataset.dbunit

Category-Name: dataset

What: This 'dataset' converter will convert a resource of category 'xml' to a resource of category 'dataset.dbunit'.

CONVERT {resourceToConvert<Res:xml>} TO dataset.dbunit (dataset) AS {converted<Res:dataset.dbunit>}

Input:

• • resourceToConvert<Res:xml>: The name (in the context) of the resource which references a 'xml' file. The content of the file must be at the format of FlatXMLDataset of DbUnit. Each line of a table is represented by an XML element:

    • The tag name corresponds of the name table.

    • Each column of the table is represented by an attribut:

      • The attribute name corresponds to the column name

      • The attribute value corresponds to the column value in the represented line.

Output:

• • converted<Res:dataset.dbunit>: The name of the converted resource (Resource of category dataset.dbunit)

Example of XML file:

<?xml version="1.0" encoding="UTF-8"?>

<dataset>

<table1 colonne0="row 0 col 0" colonne1=”row 0 col 1" />

<table1 colonne0="row 1 col 0" colonne1=”row 1 col 0" />

<table1 colonne0="row 2 col 0" colonne1=”row 2 col 0" />

<table2 colonne0="row 0 col 0" />

<table3 />

</dataset>

Remarks:

• • If in the initiale resource the attribute value is the sring character '[NULL]' the corresponding column will have the value 'null' in the converted ressource (Resource of category dataset.dbunit).

  • During the conversion, table columns are determined from the attributs of the first element corresponding to this table in the initial ressource (Resource of category XML). For instance, if a resource of category xml contains 'T' elements:

    • Case 1: Attribute not present of the first 'T' element but present after:

If:

• • The first element 'T' doesn't contain 'C' attribute and

  • A 'T' element following contains a 'C' attribute

So:

• • The 'C' attribute will be ignored during the conversion. That means no 'C' column in the converted resource.

  • Case 2: Attribute present in the first 'T' element, not present after:

If:

• • The first 'T' element contains a 'C' attribute and

  • A 'T' element following doesn't contain a 'C' attribute

So:

• • There will be a 'C' column in the converted resource. In lines corresponding to the elements which doesn't contain the 'C' attribute, the 'C' column will have the value 'null'.

Example:

LOAD path/to/dataset.xml AS dataset.file

CONVERT dataset.file TO xml (structured) AS my_xml_file

CONVERT my_xml_file TO dataset.dbunit (dataset) AS dataset.dbu

• • to filter.dbunit

Category-Name: filter

What: This 'filter' converter will convert a resource of category 'xml' to a resource of category 'filter.dbunit'.

CONVERT {resourceToConvert<Res:xml>} TO filter.dbunit (filter) AS {converted<Res:filter.dbunit>}

Input:

• • resourceToConvert<Res:xml>: The name (in the context) of the resource which references a 'xml' file. This 'xml' file looks like:

    • For a Table inclusion:

Table exclusion

<?xml version="1.0" encoding="UTF-8"?>

<filter>

<tableExclude tableRegex="table_name"/>

</filter>

• • For a column exclusion:

Column Exclusion

<?xml version="1.0" encoding="UTF-8"?>

<filter>

<tableInclude tableRegex="table_name">

<columnExclude>column_name</columnExclude>

</tableInclude>

</filter>

Note: It's not allowed to have tableInclude and tableExclude in the same filter

Output:

• • converted<Res:dataset.dbunit>: The name of the converted resource (Resource of category dataset.dbunit)

Example:

LOAD path/to/column_exclude.xml AS filter_dbunit.file

CONVERT filter_dbunit.file TO filter.dbunit (filter) AS filter_dbunit