Technical onboarding

Technical onboarding

The HD4DP software is used for facilitating the data collection of health registers in Belgium. Most registers capture data related to individual patients. To protect the patient’s privacy, encoding of the patient's identifier, in each registration, is required before the data arrives.

The HD4DP software allows to capture data, either from manual data entry or from primary systems from the data provider. In order to send data to healthdata, it needs to be encrypted before it can be transferred via eHealthBox.

These are the following steps for Technical Onboarding:
 

Prepare the HD4DP-installation

The IT department of hospitals and laboratories have to prepare the installation by providing healthdata with the information needed. The HD4DP installation sheet is a form that contains all the information that Healthdata.be needs, in order to start, the installation of the HD4DP software.

An eHealth certificate is needed to complete the installation and therefor it is a section in the form. When sending information to the HD4DP installation, the data must be encrypted by healthdata  using the public key of the certificate of an organisation. 

The form will be adjusted to the needs of the requesting organisation and send by mail. The basis for this adjusted form can be found in the HD4DP installation form template
 

Install HD4DP

The software is remotely installed by healthdata.be.
 

Install encryption module

The encryption module encrypts registrations before they are sent to the registry via the eHealth platform. This module is not part of the HD4DP software.

Each data provider has the choice to either:

  • develop and implement this module
  • to make use of third party client software

​More information is available in the article about the Encryption module.

HD4DP needs to be configured (cf. article "How to configure HD4DP for an encryption module") for the encryption module used by an organisation.
 

Submit test registration 

To verify the installation, a number of registrations are submitted belonging to a test registry. 
 

Confirm installation 

Once the submitted data of the test registry are received correctly by healthdata, you will get a confirmation that the installation was successful.
 

Configure Mail Server Settings

The HD4DP application can notify users concerning necessary actions for their registers and to inform admins about changes or requests for user accounts. Mail Server settings need to be configured (cf. article "Configure Mail Server Settings") to make this functionality possible.

manager Thu, 11/11/2021 - 17:08

Technical requirements

Technical requirements

This article describes how to fill in the web form for carrying out the installation of HD4DP (consult article about technical onboarding of organizations for further details) within Healthdata.be. It contains the following points:

  1. Contact details
  2. Server information
    • Teamviewer: ID, password, link, username, token, password
    • VPN: connection details, link, username, token, password
    • Other: connection details, link, username, token, password
  3. Server information
    • Server name or IP adress
    • Operating System: Windows, Linux, other + version
    • RAM: minimum 4Gb
    • CPU: minimum 2
    • Disk space: minimum 50 Gb
    • Username (including domain if applicable)
    • Password Server
    • User is local admin Y/N
    • Installation directory (optional)
    • Installation directory (optional)
  4. Database information
  • Connect to existing database server Y/N
  • Database System (Microsoft SQL Server or Oracle)
  • Database Server name or IP adress
  • Database Server port (e.g. default, 1433)
  • Database Name (e.g. HD4DP)
  • Database Scheme (e.g. dbo)
  • Database Username
  • Database Password
  • User can read/write/create tables Y/N
  • Connect to Microsoft SQL Server using Windows Authentication Y/N
  1. User management
  • Combination Database (authorisation) and LDAP user management (authentication)
  • Database only (no integration with LDAP)
  1. External links: links should be accessible from the server, for the installation and operation of HD4DP. Please verify these URLs from the server’s web browser
  2. Software configuration / eHealth Public Key
    • Identification Type: NIHII-HOSPITAL, NIHII-LABO, CBE
    • Identification value: typically the organisation’s RIZIV/INAMI number
    • Application ID
  3. Software configuration / Encription module
    • REST web service / File system
  4. Prefilling demographic info: choose the service you want to connect to for retrieving patient information
    • None
    • RRN Connector
    • Custom Rest NRC
    • ADT-interface (e.g. Oazis)
  5. Mail server information
    • Mail SMTP Host
    • Mail "From" adress
    • Mail Security protocol
    • Mail SMTP Username (Optional)
  6. Controle - Captcha
  7. Review page (see below)

1. CONTACT DETAILS

View below the first page of the web form.

User-added image

Save (recommended) each page before going to the 'Next Page' by clicking on 'Save my progressandresume later'.
Consult (recommended) the saved from by clicking on 'Resume a previously saved form'.

2. SERVER INFO

This part requires 2 kinds of parameter : (1) Server Connection and (2) Server Machine.

  • Server Connection 

 Fill in all the fields in the screenshot below to give access to the future HD4DP server.

User-added image
  • Server Machine​

 Fill in all the fields to identify the HD4DP server in the screenshot below.

User-added image

3. DATABASE INFO

Fill in 'Yes' or 'No' on the screen below and fill in all fields.

User-added image

If you select 'Yes', you should obtain the screen below.

User-added image

If you click on 'No', Healthdata will install a new database (Oracle Express) on your server.

4. USER MANAGEMENT

You should see the screen below and fill in (make your choise).

User-added image

If you choose the first option 'Combination Database (authorisation) and LDAP user management (authentification)' consult this article.

If you choise the second option 'Database only (no integration with LDAP)' that implies using the application to handle the customer account management.

5. EXTERNAL LINKS

Select in the screen below for confirming the :

  • the access to website required for running the application HD4DP
  • the access to website required for installing the application HD4DP
  • the identification of a proxy server in front of the HD4DP server in the screen below .
User-added image

6. SOFTWARE CONFIGURATION / EHEALTH PUBLIC KEY

Fill in the info about your eHealth certificate data in the screen below.

User-added image

7. SOFTWARE CONFIGURATION / ENCRYPTION MODULE

Select the encryption module interface in the screen below.

User-added image

If you select  'REST web service' you have to fill in the info in the screenshot below.

User-added image

If you select 'File system' you have to fill in additional in the screenshot see below

User-added image

8. PREFILLING DEMOGRAPHIC INFO

Select the different options for prefilling demographic info in the screen below ( consult this article for further details).

User-added image

9. MAIL SERVER INFORMATION

Fill the info about the mail server in order to enable the email notification of the HD4DP users.

User-added image

10 CONTROL

Please enter the characters you see in this picture below

User-added image

11. REVIEW PAGE

  • Make sure your identification is correct by consulting the screenshot below.
  • Click on 'Confirm' to submit the form.
User-added image

PLEASE CONFIRM TO SEND US the FORM 
see at the end of the review page :

User-added image

manager Thu, 11/11/2021 - 17:09

Enable HD4DP v1 auto-upgrade functionality

Enable HD4DP v1 auto-upgrade functionality

When healthdata.be pushes a new version, you will receive a mail to inform you about the new version that has been set ready for your organisation. When having enabled the auto_upgrade, your HD4DP v1 will detect overnight that a new version is pushed for your organisation. The auto-update will execute the necessary checks and download the new version. If one of the steps or checks gives an error, the auto-update will not take place.  In that case, you will receive an email the next day to warn you that a planned update has not been installed yet. You can decide to trigger the update manually (see article "Manage auto-upgrades of HD4DP") or to contact support via support.healthdata@sciensano.be.be 

The article shows how to enable the HD4DP auto-upgrade functionality.

  • Login in HD4DP as administrator, you should get the screen below
  • Go in the 'Configurations' panel (highlighted in the screen below)
  • Click on the 'Configurations' button²
  • Type 'AUT' in the search bar,  you should get the screen below
  • Select 'True' to enable the auto-upgrade functionality of HD4DP
  • Click on the green V to save the change, if the saving is successful, you should get the below
manager Thu, 11/11/2021 - 17:09

Manage auto-upgrades of HD4DP v1

Manage auto-upgrades of HD4DP v1

This article describes the auto-upgrade procedure of HD4DP. The procedure includes 3 executable steps using 3 dedicated buttons which should be visible in  the 'Upgrade software' panel (emphasized in the screen below)  when you Login as administrator :

1. Enable the auto-upgrade functionality
2. Check the settings of HD4DP for supporting the auto-upgrade functionality : 'Test software upgrade' button
3. Check that an upgrade is not already ongoing : 'Check upgrade status' button
4. Perform the auto-upgrade : 'Update HD4DP software' button

1. Enable the auto-upgrade functionality

Read the article "Enable HD4DP auto-upgrade functionality".

2. Check the settings of HD4DP for supporting the auto-upgrade functionality 

  • Click on the 'Test software upgrade' button, you should get a confirmation pop-up window as the below
  • Click on 'Continue', you should get a 'Success' pop-up window as the below (if not, please contact healthdata@sciensano.be)

3. Check that an upgrade is not already ongoing

  • Click on 'Check upgrade status' on the top-right corner of the screen, you should see message mentioning 'No upgrade in progress' as shown in the below

4. Perform the auto-upgrade

  • Click on 'Upgrade HD4DP software' for launching the auto-upgrade
  • Check the success of the auto-upgrade by :
    • Checking the HD4DP version, you should got the latest  version number of HD4DP (e.g. v1.11.3 in the screen below)
    • Checking the status items at the bottom of the screen as shown in the below (except the 'mail-server' status, every status item should be in green)
  • Email healthdata@sciensano.be if these 2 requirements (i.e. expected HD4DP version number and status items all in green) are not met.

manager Thu, 11/11/2021 - 17:09

Encryption module

Encryption module

While HD4DP is developed by healthdata.be, the EM module is not. It can be developed by data providers or third party vendors.

The architecture workgroup of the eHealth platform have strict rules concerning the encoding. Encryption and decryption from the HD4DP software are kept separate.

A new component, whose main functionality, is the encryption of the data before it leaves the organization. Since “data encryption” is the main purpose of this module, it will be referred to as Encryption Module (or EM).

Development by Data Providers

cookbook containing detailed instructions onhow to implement the module are attached.

HD4DP needs to be configured for the encryption module (cf "How to configure HD4DP for an encryption module").

Finally, validation (cf. article "Encryption module validation scenario") the encryption module implementations is done by healthdata.

Development by Third Parties

The two main functionalities of the encryption module are:

  •   Encryption and decryption data
  •   Sending and receiving data

HD4DP needs to be configured for the encryption module (cf. article "How to configure HD4DP for an encryption module"​).
 

Encryption and decryption

Healthdata.be has validated (cf. Encryption module validation scenario) the encryption module implementations, developed by the following parties:

Sending and receiving data

In Belgium, different eHealthBox client softwareapplications are available.

An eHealthBox client application provides an interface to use the eHealthBox service. The eHealth-platform communicates medical files, lab results or other sensible information.

​The use of an eHealthBox client application is required for sending data to healthdata.

The main vendors are:

manager Thu, 09/14/2023 - 16:42

Encryption module validation scenario

Encryption module validation scenario

Parties that developed their own implementation of the encryption module:

Validating different implementations of the encryption module by different parties, healthdata recommends to execute the test scenario below. Encryption module implementations that successfully pass this test scenario are approved by healthdata and will be published as such on the healthdata website.
 

Test scenario:

  • Log in to HD4DP with an admin account and create an account with user rights for the TEST registry (refer to article on support.healthdata.be)
  • Log in to HD4DP with the newly created user account, and fill and submit a registration for the TEST registry
  • Verify that the status of your registration changes from 'In progress' to 'Sending' (for a few minutes) and finally to 'Submitted'
  • Verify that registration created a message that was successfully processed by the encryption module and the eHealthBox client software
  • Send an email to healthdata@sciensano.be to confirm that a test message was sent
  • If your message is successfully received by healthdata, you will receive a confirmation email from healthdata@sciensano.be
  • Between 6 to12 hours after registration has been submitted, a status message from healthdata (via eHealth ) should be received in the eHealthBox, In version 1.5 or older that status will change to 'review in progress'.
    In version 1.6, the status is 'Submitted' but the confirmation field will change to OK. 
  • Send a screenshot to healthdata@sciensano.be to confirm that the status changed to 'Review in progress' or confirmation 'OK'.
manager Thu, 11/11/2021 - 17:09

How to configure HD4DP v1 for an encryption module

How to configure HD4DP v1 for an encryption module

How to configure HD4DP for communication with the encryption module - it is assumed that both HD4DP and the encryption module are installed. 

Log in to HD4DP as admin and go to the configurations panel:

To configure HD4DP for communication with an encryption module​, parameters need to be set regarding the organisations system.

The parameters 'EM_INTERFACE_OUT' and 'EM_INTERFACE_IN' the type of communication between HD4DP and the encryption module

  • REST: if communication is via REST-interface
  • FILE_SYSTEM: if communication is file based.

Depending on the type of interaction (REST or FILE_SYSTEM), different parameters have to be configured.
 

​REST 

Specify the URL for incoming and outgoing messages via the parameters 'EM_REST_URL_OUT' and 'EM_REST_URL_IN':

FILE_SYSTEM

Specify the different directories as shown in the example below:

manager Thu, 11/11/2021 - 17:10

HD4DP v1 integration with a Patient Identifier Service

HD4DP v1 integration with a Patient Identifier Service

Integrating HD4DP with a patient identifier service allows basic demographic information such as date of birth, gender, place of residence. HD4DP is based on the national registry number or an internal reference number.

The NATIONAL_REGISTRY_CONNECTOR configuration allows to select one option among the different connectors supported by HD4DP.

Supported integrations

This section provides a brief description of different options, along with the different configuration keys that must be filled in properly.
 

NIN Connector

If your organisation uses the NIN (National Insurance Number)Connector from HealthConnect, integration can be done out-of-the box by configuring the Server URL, Application Token and User ID in HD4DP. 
 

Configuration

  • Select the RRNCONNECTOR option in the dropdown list
  • Fill in the RRNCONNECTOR_URL, RRNCONNECTOR_APPLICATION_TOKEN and RRNCONNECTOR_USER_ID configurations

ADT-interface

If your organisation uses a patient administration system that is able to accept ADTqueries (v2), it is possible to integrate this with HD4DP as from v1.4.0. This ADT query can be done based on either a national registry number or an internal reference number.
 

Configuration

  • Select the ADT option in the dropdown list
  • Fill in the ADT_SERVICE_HOST and ADT_SERVICE_PORT configurations with the host and the port of your ADT service
     

Customization of the National Registry Connector

For developing your own web service for retrieving patient information from another system, this service must comply with a few set requirements. The service proposes an API respecting the request pattern below, and answers with an XML formatted response similar to the one provided in the example.
 

Request

URLhttp://host:port/.../patientinss{?user,password}
HeaderAccept : Application/xml

URL Example: http://localhost:8080/identifierservice/patient/87120406775?user=restUser&password=restPassword
 

Response

HD4DP requires this layout without namespaces. Make sure that no namespaces are specified since this will prevent the completion of demographic information.

The correct Content-Type in the HTTP header of the response should be filled in. The application/xml is expected as other values will most likely cause error.
 

Configuration

  • Select the CUSTOM_REST_NRC option in the dropdown list.
  • Fill in the CUSTOM_REST_NRC_URL configuration.
  • If authentication is required for using the rest service, fill in the CUSTOM_REST_NRC_USER and CUSTOM_REST_NRC_PASSWORD configurations.
     

Example of usage

Once a connector has been chosen and correctly configured, the HD4DP application will fill in demographic information about a patient, based on a manually filled in identifier. For example, a valid National Insurance number has been manually entered in the "Patient ID" field. The application has updated other fields automatically with (test-) data it received from the national registry connector.

manager Thu, 11/11/2021 - 17:10

Configure Mail Server Settings

Configure Mail Server Settings

This article explains how to configure an organization's mail server settings in HD4DP so, that the application can notify users concerning necessary actions for their registers and to inform admins about changes or requests for user accounts.

In case  mail server settings are not yet configured, you will see in the status bar of your application that the mail server is offline as is shown in the image below:

If the status shows that the mail server is down, perform the following steps to configure them:

  1. Login as admin in the application
  2. Click on 'Configurations' in the Configuration-panel. You will see a list of parameters.
  3. Above the table on the right, enter 'mail' as search parameter. The list of displayed configuration parameters will change to those containing the 'mail' keyword.
  4. Please fill in the necessary values to configure the mail server: (do not forget to click the green mark to save entered values)
    • MAIL_FROM_ADDRESS: You can fill in a fictional email address e.g. noreply.hd4dp@yourdomain.be. You can also choose to use your organisation's helpdesk email so, that users are able to reply to the mails they receive in case they have questions
    • MAIL_SMTP_HOST: The hostname of your organisation's mail server
    • MAIL_SMTP_PORT: The standard port is 25
    • MAIL_SECURITY_PROTOCOL: Most of the time, this should be none, but you can choose from the dropdown
    • MAIL_SMTP_USERNAME: If authentication is required, fill in the username (empty if no authentication must be performed)
    • MAIL_SMTP_PASSWORD: If authentication is required, fill in the password of the user you filled in or leave it empty if no authentication must be performed

If all is configured correctly, you can refresh the status bar to recalculate the status and check if it works:

In case a mail security protocol is used or authentication via user/password was filled in, please verify the settings are working by going to the login page and click 'request new account'. Create a test account with your email and click 'Request'. You should receive an email for the account you requested. You can then login as admin and reject the account.

manager Thu, 11/11/2021 - 17:10

Oracle to Microsoft Database Migration

Oracle to Microsoft Database Migration

0. INTRODUCTION

This article shows how to carry out a database migration from ORACLE (ORCL) to Microsoft SQL (MS SQL) on a Microsoft computer or environment.This procedure contains three parts :

  • the database migration from ORCL to MS SQL
  • the connection of HD4DP to the MS SQL database
  • the further action to perform while performing such database migration

1. DATABASE MIGRATION FROM ORCL TO MS SQL

1.1.The database migration tool

Microsoft provides a free migration tool named Microsoft SQL Server Migration Assistant (SSMA).

  • Download it by clicking on this link, then follow the installation procedure of the application
  • Launch it, you should obtain the below.
SSMA Management tool

1.2. Set up a migration project

Create a project.
Select the MS SQL version within the dropdown list (e.g. SQL Server 2012 in the screenshot below)

User-added image

Define the database migration type :

  • Client Side Data Migration (the laptop where SSMA software is installed will bridge the SQL and the ORACLE servers, i.e. act as third party),
  • Server side Data Migration (the database migration will take place directly between the SQL and the ORACLE servers).
User-added image

1.3. Connect to the ORACLE and MICROSOFT SQL database

1.3.1. ORACLE

Fill out the credentials of the 2 database servers:

  • Click 'Connect to Oracle'
  • Fill out the pop-up window named 'Connect to Oracle'

User-added image

You should get the Output : 'Connection to Oracle established successfully' as illustrated below. If not, make sure you are using the right credentials.The loading of the ORCL database objects can take several minutes.

User-added image

Wait until the Output 'Done' is displayed within the console (bottom of the screen). 

User-added image

1.3.2. MS SQL Server

Fill out the Micosoft SQL Server

  • Click 'Connect to SQL Server'
  • Fill out the pop-up window named 'Connect to SQL Server'
User-added image

You could get a warning windows as below which explains that you do not have rights to perfom a 'Server side Migration'. Therefore, you have to select the option 'Client side migration' in the second step.

User-added image

You can ignore this warning by clicking on 'Continue'.
​You should get the Output : 'Connection to SQL Server established successfully' as illustrated below. If not, make sure :

  • the right credentials are used
  • the database server is up and correctly working by ping it using Windows command line
  • the connection string is correct (use another SQL Client for this purpose)

The loading of the ORCL database objects can take several minutes. 
Wait until the Output 'Done.' is displayed within the console ( bottom of the screen). 

User-added image

1.4. Convert the ORACLE database schema to MICROSOFT SQL Server

1.4.1. Launch the schema conversion on ORACLE

Migrate the database schema of ORCL database to MSSQL database (below, the final screen obtained after completing the 4 following actions) :

  • Go towards 'Oracle Metadata Explorer'
  • Click on the '+' sign  (the left of database server IP address or domain name, i.e 10.0.10.11 in the screen below )
  • Select the schema name of HD4DP database, i.e. 'HEALTHDATA' in the example below
  • Right click on the schema name, i.e. 'HEALTHDATA'  
  • Select and Click on 'Convert Schema'  to launch the Schema Conversion
User-added image

Display the HD4DP tables as displayed in the screen below

  • Go on 'Tables'
  • Click on '+' sign on the left the word 'Tables' to display the schema tables
  • Compare the HD4DP tables present initially in the ORCL schema
User-added image

1.4.2. Make sur the schema conversion succeeded on MS SQL

Make sure the schema conversion succeded by checking if the ORCL schema has been created with the MS SQL database. 

  • Go towards 'SQL Server Metadata Explorer'
  • Click on the '+' sign  (the left of database server IP address or domain name, i.e 'oraclemigtest.cvbniieywa3j.us-east-1.rds.amazonaws.com:1433' in the screen below )
  • Select the right database (i.e. 'test' in the screen below)
  • Retrieve the ORCL schema, i.e. 'HEALTHDATA' in that case

User-added image

  • Click on '+' sign on the left of the schema name (i.e. 'HEALTHDATA' on the screen below)
  • Go on 'Tables'
  • Click on '+' sign on the left the word 'Tables' to display the tables of the schema
  • Compare the HD4DP tables present intially in the ORCL schema
User-added image

1.5. Migrate the data from ORACLE database to MICROSOFT SQL database

1.5.1. Launch the data migration from ORACLE database server

Migrate the data from ORCL database to MS SQL database (! make sure the ORCL schema has been successfully performed !) : 

  • Follow the steps presented in section 1.4.1.
  • Select and click on 'Migrate Data' to launch the data migration towards MS SQL

User-added image

1.5.1. Make sure the data migration succeed

Once the migration will be done, you should received a report about the migration of each table.
It displays a for each table these useful fields:

  • 'From' : with table name on ORCL
  • 'To' : with table name on MS SQL
  • 'Total Rows' : number of rows of each table
  • 'Success Rate' : migration success assessment of the table

User-added image

1.5.2. Compare the data content of the same tables on ORCL and MS SQL

Compare the data content of the tables on ORCL and MS SQL (e.g. for the WORKFLOWS table in the screen below):

  • make sure the amount of rows is the same,
  • make sure the content of each field is the same.
User-added image

2. THE CONNECTION OF HD4DP TO THE MS SQL DATABASE

The connection of HD4DP to the MS SQL database has to be done via the configuration of the 'context.xml' file of the Apache Tomcat Server.

  • Consult the HD4DP installation manual for doing this setting,
  • Restart Tomcat after change,
  • Login into HD4DP and make sure the registrations within the TEST register are still visible,
  • Perform a registration test as explained in the installation manual.

3. THE FURTHER ACTION TO TAKE WHILE PERFORMING THE DATABASE MIGRATION

You should list all the HD4DP tables within the ORCL database. For instance, for the HD4DP Version 1.10.0, here are the 54 tables to migrate :

  1. MESSAGES
  2. ATTACHMENTS
  3. PARTICIPATION_WORKFLOWS
  4. ELASTICSEARCH_INFO
  5. DCD_REPLICATIONS
  6. AUTHORITIES
  7. AUDIT_LOGS
  8. ATTACHMENT_CONTENTS
  9. USER_REQUEST_DATA_COLLECTIONS
  10. STATUS_MESSAGES
  11. STABLE_DATA_UPLOADS
  12. REGISTRATIONS
  13. NOTES
  14. GUEST_USERS
  15. DOCUMENTS
  16. ABOUT
  17. USER_CONFIGURATIONS
  18. USERS
  19. STABLE_DATA
  20. PARTICIPATION_WF_HIST
  21. EVENTS_NOTIFICATION_USERS
  22. WORKFLOW_HISTORIES
  23. VIEWS
  24. USER_REQUESTS
  25. USER_DATA_COLLECTIONS
  26. TASKS
  27. REGISTRATION_OPTIONS
  28. ORGANIZATIONS
  29. INSTALLATION_DETAILS
  30. FOLLOW_UP_CONDITIONS
  31. FOLLOW_UPS
  32. FAST_TRACK_RECORD
  33. EVENTS_NOTIFICATION_TIMES
  34. EVENTS_DATA_COLLECTION_DEF
  35. DOCUMENTS_ATTACHMENTS
  36. WORKFLOWS_TO_MIGRATE_COMMENTS
  37. WORKFLOWS
  38. PARTICIPATION_PROGRESS
  39. PARTICIPATION_DOCUMENTS
  40. MESSAGE_METADATA
  41. FAST_TRACK_UPLOAD
  42. EVENTS_WORKFLOWS
  43. CODED_DOCUMENT_CONTENT
  44. TESTORACLE
  45. TECHNICAL_METADATA
  46. PROGRESS
  47. METADATA
  48. CONFIGURATIONS
  49. USER_METADATA
  50. UPGRADES
  51. UPDATED_USERS
  52. EVENTS
  53. DATE_METADATA
  54. COMMENTS

Regarding your version of HD4DP, the list can change, for further details about table structure, email support.healthdata.be@sciensano.be.  If a table is not on the list (because the list is outdated), look at the constraints and migrate it after those from the list. Through an example with the 'WORKFLOWS' table of HD4DP, this part will show you how to take knowledge of the constraints of one table. Below, you can find the 'CREATE SQL' statement used for creating the table 'WORKFLOWS' within the schema 'HEALTHDATA.WORKFLOWS'.

CREATE TABLE HEALTHDATA.WORKFLOWS

    WORKFLOW_ID NUMBER NOT NULL,
    CREATED_ON TIMESTAMP(6) NOT NULL,
    DATA_COLLECTION_NAME VARCHAR2(255),
    DATA_COLLECTION_DEFINITION_ID NUMBER,
    HD4DP_WORKFLOW_ID VARCHAR2(255),
    READABLE_ID VARCHAR2(255),
    STATUS VARCHAR2(255),
    UPDATED_ON TIMESTAMP(6) NOT NULL,
    ORGANIZATION_ID NUMBER,
    UNIQUE_ID VARCHAR2(255),
    SEND_STATUS VARCHAR2(255),
    IDENTIFICATION_TYPE VARCHAR2(255),
    IDENTIFICATION_VALUE VARCHAR2(255),
    SUBMITTED_ON TIMESTAMP(6),
    CORRECTIONS NUMBER(1, 0) DEFAULT 0 NOT NULL,
    FOLLOW_UP NUMBER(1, 0) DEFAULT 0 NOT NULL,
    DOCUMENT_ID NUMBER
);
ALTER TABLE HEALTHDATA.WORKFLOWS ADD CONSTRAINT SYS_C005259
    UNIQUE (HD4DP_WORKFLOW_ID);
ALTER TABLE HEALTHDATA.WORKFLOWS ADD CONSTRAINT WORKFLOWS_PK
    PRIMARY KEY (WORKFLOW_ID);
ALTER TABLE HEALTHDATA.WORKFLOWS ADD CONSTRAINT CSTRT_WF_DOCUMENT_ID
    FOREIGN KEY (DOCUMENT_ID)
        REFERENCES HEALTHDATA.DOCUMENTS (DOCUMENT_ID);
ALTER TABLE HEALTHDATA.WORKFLOWS ADD CONSTRAINT CONSTRAINT_WF_ORG_ID
    FOREIGN KEY (ORGANIZATION_ID)
        REFERENCES HEALTHDATA.ORGANIZATIONS (ORGANIZATION_ID);

An attention should be paid on the  'ALTER TABLE' statements (in green)  at the bottom of the code.  The table contains 4 constraints :

  • the field 'HD4DP_WORKFLOW_ID' has be unique regarding the constraint 'SYS_C005259'
  • the field 'WORKFLOW_ID' constitutes the primary key because of  'WORKFLOWS_PK PRIMARY KEY' constraint
  • there are 2 foreign key constraint
  • 'CONSTRAINT CSTRT_WF_DOCUMENT_ID', therefore the 'DOCUMENT_ID' field from the DOCUMENTS table has to be filled in
  • 'CONSTRAINT_WF_ORG_ID', therefore the field 'ORGANIZATION_ID' from the 'ORGANIZATIONS' table has to be filled in

Hence, make sure the tables 'DOCUMENTS' and 'ORGANIZATIONS' are filled in in the right way for preventing migration failure of 'WORKFLOWS' table. Such precaution has to be taken for all the tables you want to migrate. 

manager Thu, 11/11/2021 - 17:10