OpenRIMS - Database Cleaning Part 1

Purpose

The OpenRIMS-RP database contains configuration and user-defined data. The configuration data is defined by Supervisor. This data intends to manage electronic forms and workflows of applications. The user-defined data is data collected using electronic forms of applications and workflows.

Before the database will become production it is necessary to define the primary configuration data. Because the configuration data is particular for a country, this data should be created and tested on the sandbox or demo installation of OpenRIMS-RP.

While testing, the test data will be created. Thus, to prepare a production database it will be necessary to clean up all user-defined data that was created while testing, presenting, and, possible, learning.

For the current release, there is no special defined software to clean up the database, because it is an on-time operation.

Prerequisites

• MySQL Workbench is installed
• Connection to the database is established
• Connection rights to the pdx2 database allow at least select, delete, insert, execute, update, show view
• The below cleaning script

The conceptual model[1] of the OpenRIMS-RP database is a forest of data trees built upon a relation logical model using the MySQL database engine.

The stored procedure “print_tree” allows selecting all branches of a data tree from the root or any node.

Any tree in this forest is a configuration or user-defined data, but not both. The clean database assumed the absence of user-defined data. To remove the user-defined data all data trees that contain user-defined data should be removed.

To remove a data tree, it will be enough to remove the data tree root using the “remove_branch” stored procedure.

Thus, it will be necessary to recognize all roots of user-defined data and remove them by the “remove_branch” stored procedure.

Currently, there are no SQL queries to select roots of the user-defined-data roots, however, SQL queries to select roots of the configurations have been developed for Administrative features implementations.

The approach is to select all roots of configurations and, then, subtract them from the whole roots. This is described in detail in the sections just below the "Generate cleaning script" here:.

Generate cleaning script

Login to MySQL CLI or Workbench.

Select you OpenRIMS database and open a new query.

SELECT concat('call remove_branch(',c.ID, ');', '-- ', c.Identifier) as 'sql'

FROM tree_userdefined c

This script may run for a long time!

The results table created is the actual cleaning script. Now "Select All" from the table, copy and then paste into a New Query.

Click Run/Execute.

This script will also run for a long time!

After the script has executed please login to OpenRIMS as administrator and verify that the script has cleaned the workflows.

The easiest way is to go to the Application Tracker of Monitor as verify that table is empty.

SQL Description of the cleaning process

Do NOT run the below scripts individually!

They are for educational purpose only but might assist in troubleshooting.

Roots of configurations

The data tree roots as well as all data tree nodes are recorded in the “concept” table. All roots can be selected using the “tree_roots” view.

This view is a result of SQL query

SELECT

      `concept`.`ID` AS `ID`,

      `concept`.`Identifier` AS `Identifier`

   FROM

      `concept`

       LEFT JOIN `closure` `clos` ON `clos`.`childID` = `concept`.`ID`  AND (`clos`.`Level` <> 0)

   WHERE

      ISNULL(`clos`.`ID`) and `concept`.`Active` -- only roots

Each data tree root is defined by unique field “Identifier”.

The data configuration trees are:

1.     Authorities and users

2.     Workflows

3.     Dictionaries

4.     Resources

5.     Files uploaded to resources

6.     Configurations of data

7.     Configurations of reports

8.     Tiles

9.     Legacy data

These configurations will not be extended until the next version of OpenRIMS-RP software.

Authorities and users

              All users are branches of a tree. The root of this tree is “user.data”.

All authorities are branches of a tree “organization.authority”.

              The SQL query to select roots:

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier in ('organization.authority', 'user.data')

Workflow definitions

              Workflow definitions allow control user’s application processing.

              The definition of a workflow is stored in a tree. The root URL is “configuration.”URL of a workflow”, e.g., “configuration.application.retail.site.owned.pvt”. All root URLs can be gotten from lifecycle dictionaries:

·       dictionary.guest.deregistration

·       dictionary.guest.amendments

·       dictionary.guest.applications

·       dictionary.guest.renewal

·       dictionary.shutdown.applications

·       dictionary.host.applications

The SQL query to select all root URL’s from the life cycle dictionaries is:

SELECT distinct concat('configuration.',val.Label)

FROM concept c

join closure clo1 on clo1.parentID=c.ID and clo1.`Level`=3

join concept var on var.ID=clo1.childID and var.Identifier='applicationurl'

join closure clo2 on clo2.parentID=var.ID and clo2.Level=1

join concept val on val.ID=clo2.childID and val.Identifier='EN_US'

left join closure clo on clo.childID=c.ID and clo.Level<>0

where

clo.ID is null and

c.Identifier in (

'dictionary.guest.deregistration',

'dictionary.guest.amendments',

'dictionary.guest.applications',

'dictionary.guest.renewal',

'dictionary.shutdown.applications',

'dictionary.host.applications'

)

              The SQL to select all workflow definition roots is

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier in (

                                             -- query for workflow definition URLs from the lifecycle dictionaries

                                             SELECT distinct concat('configuration.',val.Label)

                                             FROM concept c

                                             join closure clo1 on clo1.parentID=c.ID and clo1.`Level`=3

                                             join concept var on var.ID=clo1.childID and var.Identifier='applicationurl'

                                             join closure clo2 on clo2.parentID=var.ID and clo2.Level=1

                                             join concept val on val.ID=clo2.childID and val.Identifier='EN_US'

                                             left join closure clo on clo.childID=c.ID and clo.Level<>0

                                             where

                                             clo.ID is null and

                                             c.Identifier in (

                                             'dictionary.guest.deregistration',

                                             'dictionary.guest.amendments',

                                             'dictionary.guest.applications',

                                             'dictionary.guest.renewal',

                                             'dictionary.shutdown.applications',

                                             'dictionary.host.applications'

                                             )

)

Dictionaries

              Dictionaries contains pre-defined classifiers used by data input forms. Each dictionary is a tree.

              By the agreement, all URLs of dictionaries should start with “dictionary.”. Thus, SQL is

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier like 'dictionary.%'

Resources

              The definition of a resource consists of:

1.     Resource definition

2.     Data configuration

3.     Dictionary

4.     Stored files

The data configuration and dictionary are common configuration trees,  that can be gotten by the appropriate SQLs (see above and below)

Resource definitions are branches of the root URL “configuration.resources”.

The SQL is

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier='configuration.resources'

Files, uploaded to resources

The resource files are stored in a tree for each resource. The root URL is defined in the resource data configurations. A data configuration of a resource is a uniform data configuration.  Thus, it is necessary:

1.     Select URLs of data configurations for all resources

2.     Select data configurations for all resources, using data configuration URLs

3.     Select URLs of file trees from the data configurations

4.     Select file trees, using the URLs

URLs of data configurations for all resources

SELECT item.Label

FROM concept c

join closure clo1 on clo1.parentID=c.ID and clo1.`Level`=1

join concept lang on lang.ID=clo1.childID and lang.Identifier='EN_US'

join closure clo2 on clo2.parentID=lang.ID and clo2.`Level`=1

join concept item on item.ID=clo2.childID

left join closure clo on clo.childID=c.ID and clo.`Level`<>0

where

clo.ID is null and

item.Active and

c.Active and

c.Identifier='configuration.resources'

Data configurations for all resources:

SELECT conf.ID, conf.Identifier

FROM concept c

join closure clo1 on clo1.parentID=c.ID and clo1.`Level`=1

join concept conf on conf.ID=clo1.childID

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null

and c.Active

and c.Identifier in ('configuration.data')

and conf.Identifier in (

                              -- URLs of data configurations for all resources

                              SELECT item.Label

                              FROM concept c

                              join closure clo1 on clo1.parentID=c.ID and clo1.`Level`=1

                              join concept lang on lang.ID=clo1.childID and lang.Identifier='EN_US'

                              join closure clo2 on clo2.parentID=lang.ID and clo2.`Level`=1

                              join concept item on item.ID=clo2.childID

                              left join closure clo on clo.childID=c.ID and clo.`Level`<>0

                              where

                              clo.ID is null and

                              item.Active and

                               c.Active and

                               c.Identifier='configuration.resources'

)            

URLs of file trees from the data configurations

SELECT asm.Url

              FROM concept c  -- roots of configurations (right table on the screen)

   -- variables for configurations (left table on the screen)

              join closure clo1 on clo1.parentID=c.ID and clo1.`Level`=1

              join concept conf on conf.ID=clo1.childID

              join closure clo2 on clo2.parentID=conf.ID and clo2.Level='1'

              join concept var on var.ID=clo2.childID and var.Identifier <> '_LITERALS_' and var.Active

   -- get only files (documents) from the configuration variables

              join assembly asm on asm.conceptID=var.ID and asm.Clazz='documents'

   -- roots

              left join closure clo on clo.childID=c.ID and clo.Level<>0

              where clo.ID is null

               and c.Active

               and c.Identifier in ('configuration.data')

               and conf.Identifier in (

                                             -- URLs of data configurations for all resources

                                             SELECT item.Label

                                             FROM concept c

                                             join closure clo1 on clo1.parentID=c.ID and clo1.`Level`=1

                                             join concept lang on lang.ID=clo1.childID and lang.Identifier='EN_US'

                                             join closure clo2 on clo2.parentID=lang.ID and clo2.`Level`=1

                                             join concept item on item.ID=clo2.childID

                                             left join closure clo on clo.childID=c.ID and clo.`Level`<>0

                                             where

                                             clo.ID is null and

                                             item.Active and

                                              c.Active and

                                              c.Identifier='configuration.resources'

               )

File trees:

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier in

(

       -- URLs of file trees from the data configurations

       SELECT asm.Url

       FROM concept c  -- roots of configurations (right table on the screen)

  -- variables for configurations (left table on the screen)

       join closure clo1 on clo1.parentID=c.ID and clo1.`Level`=1

       join concept conf on conf.ID=clo1.childID

       join closure clo2 on clo2.parentID=conf.ID and clo2.Level='1'

       join concept var on var.ID=clo2.childID and var.Identifier <> '_LITERALS_' and var.Active

  -- get only files (documents) from the configuration variables

       join assembly asm on asm.conceptID=var.ID and asm.Clazz='documents'

  -- roots

       left join closure clo on clo.childID=c.ID and clo.Level<>0

       where clo.ID is null

        and c.Active

        and c.Identifier in ('configuration.data')

        and conf.Identifier in (

                                      -- URLs of data configurations for all resources

                                      SELECT item.Label

                                      FROM concept c

                                      join closure clo1 on clo1.parentID=c.ID and clo1.`Level`=1

                                      join concept lang on lang.ID=clo1.childID and lang.Identifier='EN_US'

                                      join closure clo2 on clo2.parentID=lang.ID and clo2.`Level`=1

                                      join concept item on item.ID=clo2.childID

                                      left join closure clo on clo.childID=c.ID and clo.`Level`<>0

                                      where

                                      clo.ID is null and

                                      item.Active and

                                       c.Active and

                                       c.Identifier='configuration.resources'

        )

)

Configurations of data

              Configurations of data intends to manage on-screen forms for configurations and user-defined data  For each form the configuration allows to assign:

·       Form layout on a screen

·       Data input fields, i.e., text, numeric, dates, logical

·       Data input components, i.e., dictionaries, registers, resource downloads, etc.

·       Data validation rules

              Details may be found here (MSH, 2022)

              For each data form the configuration is a branch in the tree with URL “configuration.data”

The SQL is

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier in ('configuration.data')

Configurations of reports

              Configurations of internal reports intend to define parameters to execute internal reports. For each report configuration is a branch in a tree with root URL “report.configuration”

The SQL is

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier in ('report.configuration')

Tiles

              Tiles is a system dictionary “dictionary.system.tiles”

Legacy Data

              The legacy data may be placed to one or more trees. The URLs of the roots should be clarified for the installation. For Nepal database URLs are “system.import.legacy.data”, “legacy.pharmacies” and “legacy.ws”.

The SQL is

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier in ('legacy.pharmacies', 'legacy.ws', 'system.import.legacy.data')

Put it all together

Create two database views:

tree_configurations:

-- 1.        Authorities and users

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier in ('organization.authority', 'user.data')

union

-- 2.        Workflows

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier in (

                                             -- query for workflow definition URLs from the lifecycle dictionaries

                                             SELECT distinct concat('configuration.',val.Label)

                                             FROM concept c

                                             join closure clo1 on clo1.parentID=c.ID and clo1.`Level`=3

                                             join concept var on var.ID=clo1.childID and var.Identifier='applicationurl'

                                             join closure clo2 on clo2.parentID=var.ID and clo2.Level=1

                                             join concept val on val.ID=clo2.childID and val.Identifier='EN_US'

                                             left join closure clo on clo.childID=c.ID and clo.Level<>0

                                             where

                                             clo.ID is null and

                                             c.Identifier in (

                                             'dictionary.guest.deregistration',

                                             'dictionary.guest.amendments',

                                             'dictionary.guest.applications',

                                             'dictionary.guest.renewal',

                                             'dictionary.shutdown.applications',

                                             'dictionary.host.applications'

                                             )

)

union

-- 3.        Dictionaries

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier like 'dictionary.%'

union

-- 4.        Resources

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier='configuration.resources'

union

-- 5.       Files uploaded to resources

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier in

(

              -- URLs of file trees from the data configurations

              SELECT asm.Url

              FROM concept c  -- roots of configurations (right table on the screen)

   -- variables for configurations (left table on the screen)

              join closure clo1 on clo1.parentID=c.ID and clo1.`Level`=1

              join concept conf on conf.ID=clo1.childID

              join closure clo2 on clo2.parentID=conf.ID and clo2.Level='1'

              join concept var on var.ID=clo2.childID and var.Identifier <> '_LITERALS_' and var.Active

   -- get only files (documents) from the configuration variables

              join assembly asm on asm.conceptID=var.ID and asm.Clazz='documents'

   -- roots

              left join closure clo on clo.childID=c.ID and clo.Level<>0

              where clo.ID is null

               and c.Active

               and c.Identifier in ('configuration.data')

               and conf.Identifier in (

                                             -- URLs of data configurations for all resources

                                             SELECT item.Label

                                             FROM concept c

                                             join closure clo1 on clo1.parentID=c.ID and clo1.`Level`=1

                                             join concept lang on lang.ID=clo1.childID and lang.Identifier='EN_US'

                                             join closure clo2 on clo2.parentID=lang.ID and clo2.`Level`=1

                                             join concept item on item.ID=clo2.childID

                                             left join closure clo on clo.childID=c.ID and clo.`Level`<>0

                                             where

                                             clo.ID is null and

                                             item.Active and

                                              c.Active and

                                              c.Identifier='configuration.resources'

               )

)

union

-- 6.        Configurations of data

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier in ('configuration.data')

union

-- 7.        Configurations of reports

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier in ('report.configuration')

union

-- 8.        Tiles are a dictionary

-- 9.        Legacy data

SELECT c.ID, c.Identifier

FROM concept c

left join closure clo on clo.childID=c.ID and clo.Level<>0

where clo.ID is null and c.Active and c.Identifier in ('legacy.pharmacies', 'legacy.ws', 'system.import.legacy.data')

tree_userdefined:

SELECT c.*

   FROM

              `concept` `c`

       LEFT JOIN `closure` `clo` ON `clo`.`childID` = `c`.`ID`

           AND (`clo`.`Level` <> 0)

   WHERE

      ISNULL(`clo`.`ID`) AND `c`.`Active`

           AND NOT  `c`.`ID` IN (SELECT

              `tree_configurations`.`ID`

           FROM

               `tree_configurations`)

---

[1] sciencedirect.com/topics/computer-science/conceptual-data-model