Data Deletion is a feature available for subscribed DMM customers that allows delete the entities records. It is the entire process from setting up a deletion configuration to executing it. The structures and steps are very similar to those of the data migration as it is also separated in two steps: configuration and execution, which can be executed at different times. They can also be executed in different applications that leverage the DMM engine.

We will start by describing the configuration step and it's resulting JSON configuration file and afterward, we will detail the execution process.

Deletion Configuration

The purpose of performing the deletion configuration is to obtain a reusable JSON configuration file that describes a set of deletion options. When the DMM engine receives a configuration file, it will analyze it, connect to the database involved, and execute the necessary commands on the database to delete the selected entities.

To delete the selected entities, first DMM has to make sure that all the foreign keys to the entities are previous deleted. To do this, DMM will get all the dependent entities and validate if they also are in the configuration. If not, DMM has to create a command to set to null the foreign key.

In the set to null command described before, there are some scenarios that can happens where DMM can't solve and can generate errors:

• If the dependent entity is inactive, DMM will not do any action on the table.

• If the dependent entity column is also the table primary key or mandatory, updating the column to null will throw an exception, so it will be ignored.

To start your configuration, just click on "Executions > Data Deletion" in the top menu of DMM OutSystems Component.

Database Information

In this first step, a single dropdown will appear. The available values for these dropdowns are the configured connection string from the settings screen. It is always possible to pick "Runtime Connection" to use the environment where Infosistema DMM is installed.

Please note that no validations are made on this step, which means that if you pick a connection for a destination that does not have the required privileges for execution, an error may appear later on. A detailed list of the required privileges can be found on the Requirements page of this manual. Notice that you can have valid configuration files, in the sense that they correctly describe a deletion for DMM, which are not executable if the underlying connections are not valid.

You can also upload a previously saved configuration and jump directly to the Final Configuration Screen step.

Finally, you choose if you want to base your configuration at an Application Level where you simply pick the application(s) to delete or at a Module Level, where you can cherry-pick specific entities to be deleted.

Final Configuration Screen

Review fields to sync

In the final step of the configuration phase, you can review all entities to better understand what's being deleted. 

DMM keeps a short term cache  (60m) of the entity definition for performance reasons when doing configurations. This means that if you edit/change the entities structure, please guarantee enough minutes have passed so DMM can pickup those changes and show them to you in this final configuration screen.

The "Automatic Tenant Filtering" allows you to identify which tenants you wish to delete, and DMM will automatically add those filters to the entity data (on multi-tenant entities) when executing the deletion. If you don't specify anything, no automatic tenant filtering will be set (you can still define all filters you want individually for each entity, see below).

Entity configurations

Below the above general deletion options, there is a block where all entities that will be subject to deletion are listed.

Expanding an entity will allow the configuration of how Infosistema DMM will handle it during the deletion. There are just one possible configuration that can be made on each entity:

Filtering

This allows you to write the SQL syntax that you want to apply. Just write the segment that comes after the WHERE. By default, no filter is applied and the entire table is deleted. Note that the SQL syntax should match your specific database technology for the source database.

Direct JOIN clauses in the filter are not supported, since the filter is appended after WHERE. However, you can filter based on other tables using subqueries with IN or EXISTS. For example:

Using IN:

col1 IN (SELECT id FROM other_table WHERE status = 'active')

Using EXISTS:

EXISTS (SELECT 1 FROM other_table o WHERE o.id = main_table.col1 AND o.status = 'active')

By using subqueries in IN or EXISTS, you can effectively "join" another table in your filter logic.

By default, DMM will *not* cascade the filters. This means that if you apply a filter to an entity, and there are FKs to it in another second entity, in that other entity you should also create a JOIN clause in the filter and add the relevant filter - if you don't, when DMM goes to delete the second (dependent) entity,  all the records will be deleted.

If you want to cascade the filter, you can use the Propagate Clause button. With this feature, DMM retrieves all entities that have a foreign key referencing the selected entity and applies a clause to ensure the operation only affects records whose foreign key points to the filtered records in the parent entity. 

This operation is recursive, meaning DMM will also apply the same search to those dependent entities, and so on. If DMM detects a circular dependency, a message will appear at the top, and the clause will only be propagated up to the entity where the circular dependency was found.

When you're satisfied with your configurations, you can choose to save the configuration directly in DMM, download the configuration file and save it for later use, and/or you can continue directly to the execution screen.

Subscription Validation

Depending on your subscription version, a Validation & Subscription Check screen may appear here.

If it does, you can find more information about the process at this link.

Deletion Execution

Inside the OutSystems Component, there is a single screen that handles the execution. Whether you've followed the configuration process or you've uploaded a previously saved configuration file, the same screen appears.

On this screen, confirming the operation with the checkbox and clicking on "Start Deleting" will start the deletion process. Under the hood, Infosistema DMM is creating an execution plan and start deleting the entities. After the deletion is started, Infosistema DMM will jump to the reporting page where you may monitor the execution.