Database Plugin - Macros - Delete DbUnit


# DELETE_DBUNIT {dataset} FROM {database}

What ?

This macro will delete all the data listed in the ‘dataset file’ from the ‘database’.

Underlying instructions :

LOAD {dataset} AS __temp_{%%rand1}.file

CONVERT __temp_{%%rand1}.file TO xml(structured) AS __temp_{%%rand2}.xml
CONVERT __temp_{%%rand2}.xml TO dataset.dbunit(dataset) AS __temp_{%%rand3}.dbu

EXECUTE delete WITH __temp_{%%rand3}.dbu ON {database} USING $(operation : delete) AS __temp_{%%rand4}.result

> Input :

  • database} : The name (in the context) of the database to use (database type target).
  • {dataset} : A flat xml dbunit dataset file.

Remark : The file designed by {dataset} must respect the same rules than a file which would serve to create an SKF dataset.dbunit type resource via the converter (From xml to dataset.dbunit).

Example :

# DELETE_DBUNIT path/to/dataset.xml FROM my_database

Database overview :

../../_images/delete-dbunit-database-overview.png

Dataset .xml File :

../../_images/delete-dbunit-dataset.png

SKF script :

../../_images/delete-dbunit-macro.png

There is only one employee left in the database :

../../_images/delete-dbunit-database-result.png

# DELETE_DBUNIT {dataset} FROM {database} WITH CONFIG {config}

What ?

This macro will delete all the data listed in the ‘dataset file’ from the ‘database’ using a DbUnit configuration file.

Underlying instructions :

LOAD {config} AS __temp{config}{%%rand1}.file
CONVERT __temp{config}{%%rand1}.file TO conf.dbunit AS __temp{config}{%%rand2}.conf

LOAD {dataset} AS __temp_{%%rand3}.file

CONVERT __temp_{%%rand3}.file TO xml(structured) AS __temp_{%%rand4}.xml
CONVERT __temp_{%%rand4}.xml TO dataset.dbunit(dataset) AS __temp_{%%rand5}.dbu

EXECUTE delete WITH __temp_{%%rand5}.dbu USING $(operation : delete),__temp{config}{%%rand2}.conf ON {database} AS __temp_{%%rand6}.result

> Input :

  • {database} : The name (in the context) of the database to use (database type target).
  • {dataset} : A flat xml dbunit dataset file.
  • {config} : A configuration file for DbUnit (‘.properties’).

Remarks :

  1. The file designed by {dataset} must respect the same rules than a file which would serve to create an SKF dataset.dbunit type resource via the converter (From xml to dataset.dbunit).
  2. The file designed by {config} must respect the same rules than a file which would serve to create an SKF conf.dbunit type resource via the converter (From file to conf.dbunit).

Example :

# DELETE_DBUNIT path/to/dataset.xml FROM my_database WITH CONFIG path/to/my_config_dbunit.properties

# DELETE_DBUNIT {dataset} FROM {database} USING {ppkfilter}

What ?

This macro will load the specified xml dataset and delete datas listed in from the ‘database’ using a filter DbUnit.

Underlying instructions :

// Load and convert the dbunit dataset
LOAD {dataset} AS __{%%r1}.file
CONVERT __{%%r1}.file TO file(param.relativedate) AS __{%%r2}.file
CONVERT __{%%r2}.file TO xml(structured) AS __{%%r3}.xml
CONVERT __{%%r3}.xml TO dataset.dbunit(dataset) AS __{%%r4}.dbu

// Load and convert the pseudo primary key filter
LOAD {ppkfilter} AS __{%%r5}.file
CONVERT __{%%r5}.file TO properties(structured) AS __{%%r6}.props
CONVERT __{%%r6}.props TO conf.dbunit.ppk(from.properties) AS __{%%r7}.ppk

// Execute delete operation using the pseudo primary key filter
EXECUTE delete WITH __{%%r4}.dbu ON {database} USING __{%%r7}.ppk,$(operation : delete) AS __{%%r8}.result

> Input :

  • {database} : The name (in the context) of the database to use (database type target).
  • {dataset} : A flat xml dbunit dataset file.
  • {ppkfilter} : A DbUnit filter referring to pseudo primary keys (‘.properties’).

Example :

# DELETE_DBUNIT path/to/dataset.xml FROM my_database USING path/to/my_filter_dbunit.properties

For this example, we set the table employee with no primary key :

../../_images/delete-dbunit-using-ppk-table-properties.png
../../_images/delete-dbunit-using-ppk-database-overview.png

We set “username” as pseudo primary key in properties file :

../../_images/delete-dbunit-using-ppk-ppkfile.png

Dataset .xml file :

../../_images/delete-dbunit-using-ppk-dataset.png

We execute the macro without using ppk properties file :

../../_images/delete-dbunit-using-ppk-macro.png

The following error occurs :

../../_images/delete-dbunit-using-ppk-error.png

We execute the macro with the ppk properties file :

../../_images/delete-dbunit-using-ppk-macro2.png

The operation succeeds and all employees are deleted :

../../_images/delete-dbunit-using-ppk-success.png
../../_images/delete-dbunit-using-ppk-database-result.png

# DELETE_ALL_DBUNIT {dataset} FROM {database}

What ?

This macro will load the specified xml dataset and truncate every table listed in from the ‘database’.

Underlying instructions :

LOAD {dataset} AS __temp_{%%rand1}.file

CONVERT __temp_{%%rand1}.file TO xml(structured) AS __temp_{%%rand2}.xml
CONVERT __temp_{%%rand2}.xml TO dataset.dbunit(dataset) AS __temp_{%%rand3}.dbu

EXECUTE delete WITH __temp_{%%rand3}.dbu ON {database} AS __temp_{%%rand4}.result

> Input :

  • {database} : The name (in the context) of the database to use (database type target).
  • {dataset} : A flat xml dbunit dataset file.

Remark : The file designed by {dataset} must respect the same rules than a file which would serve to create an SKF dataset.dbunit type resource via the converter (From xml to dataset.dbunit).

Example :

# DELETE_ALL_DBUNIT path/to/dataset.xml FROM my_database

# DELETE_ALL_DBUNIT {dataset} FROM {database} WITH CONFIG {config}

What ?

This macro will load the specified xml dataset and truncate every table listed in from the ‘database’ using a DbUnit configuration file.

Underlying instructions :

LOAD {config} AS __temp{config}{%%rand1}.file
CONVERT __temp{config}{%%rand1}.file TO conf.dbunit AS __temp{config}{%%rand2}.conf

LOAD {dataset} AS __temp_{%%rand3}.file

CONVERT __temp_{%%rand3}.file TO xml(structured) AS __temp_{%%rand4}.xml
CONVERT __temp_{%%rand4}.xml TO dataset.dbunit(dataset) AS __temp_{%%rand5}.dbu

EXECUTE delete WITH __temp_{%%rand5}.dbu USING __temp{config}{%%rand2}.conf ON {database} AS __temp_{%%rand6}.result

> Input :

  • {database} : The name (in the context) of the database to use (database type target).
  • {dataset} : A flat xml dbunit dataset file.
  • {config} : A configuration file for DbUnit (‘.properties’).

Remarks :

  1. The file designed by {dataset} must respect the same rules than a file which would serve to create an SKF dataset.dbunit type resource via the converter (From xml to dataset.dbunit).
  2. The file designed by {config} must respect the same rules than a file which would serve to create an SKF conf.dbunit type resource via the converter (From file to conf.dbunit).

Example :

# DELETE_ALL_DBUNIT path/to/dataset.xml FROM my_database WITH CONFIG path/to/my_config_dbunit.properties