Databasetools

Database tools

These are the tools to handle the basic parts of the database. These tools are only available for the owner of the database and should be handled with care as any changes in the database may disable the connection of your client to the database. Before changing any parts of the database it is recommended to backup the current state of the database. To use these tools, choose Administation -> Database -> Database tools ... from the menu. A window will open as shown below.

Description

The Description section (see above) shows the basic definitions of the objects in the database and enables you to enter a description for these objects including tables and their columns, functions and their parameter etc. With the buttons SQL for adding, update and both you can generate SQL statements for the creation of the descriptions in your database. Use the button both if you are not sure if a description is already present as it will generate a SQL statement working with existing and missing descriptions (see below).

The button Fill Cache fills the table CacheDescription where all descriptions are collected for easy access.

Log table and trigger

In the Log table and trigger section (see below) click on the List tables button to see all tables within the database. The Table section shows the basic definitions of a selected table. If columns for logging the date and responsible user for inserting and updating the data are missing, you can use the Attach ... button to attach these columns to the table. Furthermore you may add a RowGUID to the table as e.g. a preparation for a replication.

In the Log table section (see below) you can create a logging table for the selected table in a format as used within the Diversity Workbench. Click on the Show SQL ... button to show the SQL-statement that will create the logging table. If an old logging table should be kept, choose the Keep old log table option. If your table should support the version setting from a main table, choose the Add the column LogVersion option. To finally create the logging table click on the Create LogTable ... button.

The triggers for insert, update and delete are created in the according sections (see below). If an old trigger exists, its definition will be shown in the upper part of the window. Click on the Show SQL button to see the definition of the trigger according to the current definition of the table in a format as used in the Diversity Workbench. If a trigger should set the version in a main table, which the current table is related to, choose the Add version setting to trigger option. To enable this option you must select the version table first. To finally create the trigger click on the Create trigger button. The update and delete triggers will transfer the original version of the data into the logging tables as defined above, where you can inspect the history of the data sets.

If so far no procedure for setting the version in a main table is defined, you can create this procedure in the last section. Click on the Show SQL button to see the definition and on the Create Procedure button to create the procedure (see below).

Preparation for replication

If you want to use replication within you module, the tables need certain columns and a log table. These preparations can be performed by a script, generated in the section Replication (see below). Select the tables you want to include in the process and create the script. This script can than be included in an update of the database. Please ensure that these changes are only be done by expert staff.

Clear logtables

If for any reason you want to clear the log tables of the database, this can be done in the Clear log tab as shown below. Click on the List tables button to list the log tables. Then select those that should be cleared and click on the Clear log of selected tables button (see below). Please keep in mind that any restoration of data from the log is only possible as long as the data can be retrieved from the log.

Data protection

To implement the General Data Protection Regulation of the European Union several steps have to be performed in a database:

Generate a skript using this tool (see below) to convert all tables and insert objects according to the requirements:
- Add columns ID, PrivacyConsent and PrivacyConsentDate in table UserProxy
- Grant update to PrivacyConsent and PrivacyConsentDate in table UserProxy
- Create update trigger for UserProxy setting the PrivacyConsentDate
- Create the function providing the ID of the user from UserProxy
- Create the function providing the name of the user from UserProxy
- Create the function PrivacyConsentInfo providing common information
- For every table:
  - Insert users from a table into UserProxy (if missing so far)
  - Insert users from the corresponding log table into UserProxy (if missing so far)
  - Change the constraints for the logging columns (User_Name() → UserID())
  - Replace user name with ID in logging columns
  - Replace user name with ID in logging columns of the log table
  - Adapt description of the logging columns
Include the skript in an update of the database
Check the database for update triggers, functions using e.g. CURRENT_USER, USER_NAME, SUSER_SNAME etc. where user names must be replaced with their IDs. Create a script performing these tasks and include it into an update for the database
Adapt the client to the now changed informations (e.g. query for responsible etc.)

After these changes the only place where the name of a user is stored is the table UserProxy together with the ID. Removing the name (see below) will remove any information about the user leaving only a number linked to the information within depending data.

To generate a script for the objects and changes needed to implement the General Data Protection Regulation use the Data protection tab as shown below. The generated script will handle the standard objects (logging columns) but not any additional circumstances within the database. For these you need to inspect the database in detail and create a script to handle them on your own.

To set the website where detailed information about the handling of the General Data Protection Regulation within the DiversityWorkbench resp. the current database is provided, click on the button on the Info site tab. If unchanged, the default site for the DiversityWorkbench is set (see below).

If for any reason a user wants his name to be removed from the database, select the users name from the list as shown below and click on the button (see below).