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