Database Plugin - Converters¶

Contents :

From file …
From directory to dataset.dbunit
From properties to conf.dbunit.ppk
From result.sql to dataset.dbunit
From xml …
- … to dataset.dbunit
- … to filter.dbunit

From file …¶

… to conf.dbunit ¶

Category-Name : structured

What ?

This structured converter will convert a file type resource to a conf.dbunit type resource.

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 (conf.dbunit type resource).

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)

Batched statements :

SKF 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 :

SKF 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 :

SKF 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 schemas. When this property is enabled tables names are preceded by the schema name to which they belong : SCHEME.TABLE.

Table types :

SKF 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) :

SKF 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 allow 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 :

SKF 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 :

SKF 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 :

SKF 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.tf.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 SKF resource: conf.dbunit.ppk.

… to conf.dbunit.ppk ¶

Category-Name : structured

What ?

This structured converter will convert a file type resource to a``conf.dbunit.ppk`` type resource.

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).

> 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 file type resource to a parameter.indexed.sql type resource.

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 (parameter.indexed.sql type resource).

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 file type resource to a parameter.named.sql type resource.

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 (parameter.named.sql type resource).

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 file type resource to a query.sql type resource.

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 (query.sql type resource).

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 file type resource to a script.sql type resource. 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>} [USING {encoding}, {delimiter}]

> Input :

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

Optional - encoding : Parameter representing the query file encoding. Default value : “UTF-8”.

Optional - delimiter : Parameter representing the SQL block delimiter. Default value : “@@”. It 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 (script.sql type resource).

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.

Example :

Creating a MySQL procedure :

DROP PROCEDURE IF EXISTS `some_procedure`;
@@
CREATE PROCEDURE `some_procedure`(...)
BEGIN
 ...
END;
@@

Creating a PL/pgSQL function :

@@
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>” :
```
<DELIMITER>
BEGIN
    some_procedure;
END;
<DELIMITER>
```

In case of nested SQL blocks you only need to 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 directory type resource to a dataset.dbunit type resource.

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 (directory type resource). 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 relative path to the root directory towards the csv file containing the table.

> Output :

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

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 properties type resource to a conf.dbunit.ppk type resource.

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 (properties type resource). For each property, the key is a Table name, the value is a column or columns list separated with comma.

> Output :

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

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 result.sql type resource to a dataset.dbunit type resource.

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 an xml type resource to a dataset.dbunit type resource.

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 an 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 (dataset.dbunit type resource).

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 initial resource the attribute value is the sring character [NULL], the corresponding column will have the value null in the converted ressource (dataset.dbunit type resource).
During the conversion, table columns are determined from the attributs of the first element corresponding to this table in the initial ressource (xml type resource). For instance, if an xml resource contains ‘T’ elements :
- Case 1 : Attribute of the first ‘T’ element not present but present after :
If :
The first element ‘T’ doesn’t contain ‘C’ attribute and

A ‘T’ element following contains a ‘C’ attribute
Then :
The ‘C’ attribute will be ignored during the conversion. That means no ‘C’ column in the converted resource.
- Case 2 :
If :
The first ‘T’ element contains a ‘C’ attribute and

A ‘T’ element following doesn’t contain a ‘C’ attribute
Then :
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 an xml type resource to a filter.dbunit type resource.

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 exclusion :
<?xml version="1.0" encoding="UTF-8"?>
<filter>
 <tableExclude tableRegex="table_name"/>
</filter>
For a column exclusion :
<?xml version="1.0" encoding="UTF-8"?>
<filter>
<tableInclude tableRegex="table_name">
      <columnExclude>column_name</columnExclude>
 </tableInclude>
</filter>

> Output :

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

Example :

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

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