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 :

TA name

squashtest.ta.dbunit.batchedStatements

DbUnit name

http://www.dbunit.org/features/batchedStatements

Default value

false

Meaning

Enable or disable the use of batch JDBC requests.

 

Case sensitive table names :

TA name

squashtest.ta.dbunit.caseSensitiveTableNames

DbUnit name

http://www.dbunit.org/features/caseSensitiveTableNames

Default value

false

Meaning

Enable or disable the case sensitivity of table names. When this property is activ, table names are considered case sensitive.

 

Qualified table names :

TA name

squashtest.ta.dbunit.qualifiedTableNames

DbUnit name

http://www.dbunit.org/features/qualifiedTableNames

Default value

false

Meaning

Enable or disable the possibility of taking in charge simultaneously several schemes. When this property is enabled tables names are preceded by the scheme name to which they belong: SCHEME.TABLE.

 

Table types :

TA name

squashtest.ta.dbunit.tableType

DbUnit name

http://www.dbunit.org/properties/tableType

Default value

String[]{"TABLE"}

Meaning

Permits to configure the type of known tables.

 

Datatype factory (Cf. issue 789) :

TA name

squashtest.ta.dbunit.datatypeFactory

DbUnit name

http://www.dbunit.org/properties/datatypeFactory

Default value

org.dbunit.dataset.datatype.DefaultDataTypeFactory

Meaning

Some datas types are specific to the management of relational database. To permit to DbUnit to manage this kind of datas, it's necessary to specify the "Datas types Factory" he must use.

The following factories are available in DbUnit:

·         org.dbunit.ext.db2.Db2DataTypeFactory

·         org.dbunit.ext.h2.H2DataTypeFactory

·         org.dbunit.ext.hsqldb.HsqldbDataTypeFactory

·         org.dbunit.ext.mckoi.MckoiDataTypeFactory

·         org.dbunit.ext.mssql.MsSqlDataTypeFactory

·         org.dbunit.ext.mysql.MySqlDataTypeFactory

·         org.dbunit.ext.oracle.OracleDataTypeFactory

·         org.dbunit.ext.oracle.Oracle10DataTypeFactory

·         org.dbunit.ext.postgresql.PostgresqlDataTypeFactory

·         org.dbunit.ext.netezza.NetezzaDataTypeFactory


Batch size :

TA name

squashtest.ta.dbunit.batchSize

DbUnit name

http://www.dbunit.org/properties/batchSize

Default value

100

Meaning

Integer representing the requests number in a batch requests (Only when the property "batchedStatements" is active.

 

Metadata handler :

TA name

squashtest.ta.dbunit.metadataHandler

DbUnit name

http://www.dbunit.org/properties/metadataHandler

Default value

org.dbunit.database.DefaultMetadataHandler

Meaning

The way of metadatas management of the base can differ according to the SGBDR. The following handlers are available:

·         org.dbunit.ext.db2.Db2MetadataHandler

·         org.dbunit.ext.mysql.MySqlMetadataHandler

·         org.dbunit.ext.netezza.NetezzaMetadataHandler

 For others SGBDR, default handler is enough.


Escape pattern : 

TA name

squashtest.ta.dbunit.escapePattern

DbUnit name

http://www.dbunit.org/properties/escapePattern

Default value

none

Meaning

Allows schema, table and column names escaping.

 Example

squashtest.ta.dbunit.escapePattern=`

The property above will permit to escape the table name and column names in the following query.

insert into `person` (`id`, `name`, `unique`) values (1, 'Doe', true);

This query will succeed even though "unique" is a SQL key word and is not normally allowed.


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'.
CONVERT {resourceToConvert<Res:file>} TO script.sql (script) AS {converted<Res:script.sql>}

Input:
  • resourceToConvert<Res:file>: The name (in the context) of the resource which references a file whose content is a SQL script.
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



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