Database Plugin - Commands¶

Contents :

‘execute’ ‘query.sql’ on ‘database’
‘execute’ ‘query.sql’ on ‘database’ via ‘parameter.indexed.sql’
‘execute’ ‘query.sql’ on ‘database’ via ‘parameter.named.sql’
‘execute’ ‘script.sql’ on ‘database’
‘get.all’ on ‘database’
‘insert’ ‘dataset.dbunit’ on ‘database’
‘delete’ ‘dataset.dbunit’ on ‘database’

‘execute’ ‘query.sql’ on ‘database’¶

What ?

This command executes a SQL query represented by a query.sql resource on the specified database target.

EXECUTE execute WITH {query<Res:query.sql>} ON {<Tar:database>} AS {result<Res:result.sql>}

> Input :

query<Res:query.sql> : The name (in the context) of the resource which references a SQL query (query.sql type resource).

<Tar:database> : The name (in the context) of the database to use (database type target).

> Output :

result<Res:result.sql> : The name of the resource which contains the result of the SQL query (result.sql type resource).

Example :

LOAD path/to/my_query.sql AS query.file

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

EXECUTE execute WITH query1 ON mydatabase-db AS my_query_result

‘execute’ ‘query.sql’ on ‘database’ via ‘parameter.indexed.sql’¶

What ?

This command executes a SQL query represented by a query.sql resource on the specified database target via indexed parameters.

EXECUTE execute WITH {query<Res:query.sql>} ON {<Tar:database>} AS {result<Res:result.sql>} USING {config<Res:parameter.indexed.sql>}

> Input :

query<Res:query.sql> : The name (in the context) of the resource which references a SQL query (query.sql type resource).

<Tar:database> : The name (in the context) of the database to use (database type target).

config<Res:parameter.indexed.sql> : The name of the resource which contains indexed parameters for the SQL query (parameter.indexed.sql type resource).

> Output :

result<Res:result.sql> : The name of the resource which contains the result of the SQL query (result.sql type resource).

Example :

LOAD path/to/my_query.sql AS query.file

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

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

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

EXECUTE execute WITH query1 ON mydatabase-db AS my_query_result USING value.properties

‘execute’ ‘query.sql’ on ‘database’ via ‘parameter.named.sql’¶

What ?

This command executes a SQL query represented by a query.sql resource on the specified database target via named parameters.

EXECUTE execute WITH {query<Res:query.sql>} ON {<Tar:database>} AS {result<Res:result.sql>} USING {config<Res:parameter.named.sql>}

> Input :

query<Res:query.sql> : The name (in the context) of the resource which references a SQL query (query.sql type resource).

<Tar:database> : The name (in the context) of the database to use (database type target).

config<Res:parameter.named.sql> : The name of the resource which contains named parameters for the SQL query (parameter.named.sql type resource).

> Output :

result<Res:result.sql> : The name of the resource which contains the result of the SQL query (result.sql type resource).

Example :

LOAD path/to/my_query.sql AS query.file

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

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

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

EXECUTE execute WITH query1 ON mydatabase-db AS my_query_result USING value.properties

‘execute’ ‘script.sql’ on ‘database’¶

What ?

This command executes a SQL script represented by a script.sql resource on the specified database target.

EXECUTE execute WITH {script<Res:script.sql>} ON {<Tar:database>} AS $() [ USING $(keep.separator: <keepSeparator>)]

> Input :

script<Res:script.sql> : The name (in the context) of the resource which references a SQL script (script.sql type resource).

<Tar:database> : The name (in the context) of the database on which the SQL script should be used (database type target).

<keepSeparator> : Indicate to the command to keep or remove the separator (“;”) at the end of each SQL query of the script. This parameter can take one of two values : “true” or “false”. By default this parameter is set to “true”.

Example 1 :

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

CONVERT script.file TO script.sql (script) AS script1

EXECUTE execute WITH script1 ON mydatabase-db AS $()

Example 2 :

EXECUTE execute WITH script1 ON mydatabase-db AS $() USING $(keep.separator:false)

‘get.all’ on ‘database’¶

What ?

This command allows to create a DbUnit dataset from a specific database.

EXECUTE get.all WITH $() ON {<Tar:database>} AS {result<Res:dataset.dbunit>} [USING [{<Res:conf.dbunit>}],[{<Res:conf.dbunit.ppk>}] ]

> Input :

<Tar:database> : The name (in the context) of the database to use (database type target).

<Res:conf.dbunit> : This resource contains DbUnit configuration properties.

<Res:conf.dbunit.ppk> : The name of the resource which references a configuration file to define the pseudo primary keys.

Remarks

If for a table a primary key and a pseudo primary key are defined, the pseudo primary key override the primary key.
If for a table a pseudo primary key is defined with one or more non existent columns, the command fails.

> Output :

result<Res:dataset.dbunit> : The name of the resource which contains the DbUnit dataset of all the database.

Example 1 :

EXECUTE get.all WITH $() ON myDatabase-db AS myDataset

Example 2 :

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

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

EXECUTE get.all WITH $() ON my_Database-db USING conf.dbu AS my_dataset

‘insert’ ‘dataset.dbunit’ on ‘database’¶

What ?

This command insert a DbUnit dataset on the specified database target.

EXECUTE insert WITH {dataset<Res:dataset.dbunit>} ON {<Tar:database>} AS $() [USING [$(operation : <type>)],[{<Res:conf.dbunit>}],[{<Res:conf.dbunit.ppk>}]]

> Input :

dataset<Res:dataset.dbunit> : The name of the resource which references a DbUnit dataset (Resource of type dataset.dbunit).

<Tar:database> : The name (in the context) of the database to use (database type target).

<type> : 4 values are possible for this parameter :

INSERT : for a simple operation of insert. This operation assumes that table data does not exist in the target database and fails if this is not the case.

CLEAN_INSERT : a ‘delete all’ is realised before the ‘insert’ operation.

UPDATE : this operation assumes that table data already exists in the target database and fails if this is not the case.

REFRESH : data of existing rows are updated and non-existing row get inserted. Any rows which exist in the database but not in dataset stay unaffected.

Remark

If “$(operation : <type>)” is not defined, property is by default CLEAN_INSERT.

<Res:conf.dbunit> : This resource contains DbUnit configuration properties.

<Res:conf.dbunit.ppk> : The name of the resource which references a configuration file to define the pseudo primary keys.

Remark

If for a table a primary key and a pseudo primary key are defined, the pseudo primary key override the primary key.

If for a table a pseudo primary key is defined with one or more non existents columns, the command fails.

Example :

LOAD path/to/dataset_to_insert.xml AS dataset_file

CONVERT dataset_file TO xml (structured) AS dataset_xml

CONVERT dataset_xml TO dataset.dbunit (dataset) AS dataset_dbu

LOAD path/to/dbunit-conf.properties AS conf_file

CONVERT conf_file TO conf.dbunit (structured) AS conf_dbu

EXECUTE insert WITH dataset_dbu ON my_database-db USING conf_dbu,$( operation : INSERT ) AS $()

‘delete’ ‘dataset.dbunit’ on ‘database’¶

What ?

This command delete a DbUnit Dataset on the specified database target.

EXECUTE delete WITH {dataset<Res:dataset.dbunit>} ON {<Tar:database>} AS $() [USING [$(operation : <type>)],[{<Res:conf.dbunit>}],[{<Res:conf.dbunit.ppk>}]]

> Input :

dataset<Res:dataset.dbunit> : The name of the resource which references a DbUnit dataset (dataset.dbunit type resource).

<Tar:database> : The name (in the context) of the database to use (database type target).

<type> : 2 values are possible for this parameter :

DELETE : This operation deletes only the dataset contents from the database. This operation does not delete the entire table contents but only data that are present in the dataset.

DELETE_ALL : Deletes all rows of tables present in the specified dataset. If the dataset does not contains a particular table, but that table exists in the database, the database table is not affected. Table are truncated in reverse sequence.

Remark

If “$(operation : <type>)” is not defined, property is by default DELETE_ALL.

<Res:conf.dbunit> : This resource contains DbUnit configuration properties.

<Res:conf.dbunit.ppk> : The name of the resource which references a configuration file to define the pseudo primary keys.

Remarks

If for a table a primary key and a pseudo primary key are defined, the pseudo primary key override the primary key.

If for a table a pseudo primary key is defined with one or more non existents columns, the command fails.

Example :

LOAD path/to/dataset_to_insert.xml AS dataset_file

CONVERT dataset_file TO xml (structured) AS dataset_xml

CONVERT dataset_xml TO dataset.dbunit (dataset) AS dataset_dbu

LOAD path/to/dbunit-conf.properties AS conf_file

CONVERT conf_file TO conf.dbunit (structured) AS conf_dbu

EXECUTE delete WITH dataset_dbu ON my_database-db USING conf_dbu,$( operation : DELETE ) AS $()