Databases
Databases Bart.Servaes Fri, 04/12/2024 - 13:15Retrieve PITTER data from the local database of HD4DP v2
Retrieve PITTER data from the local database of HD4DP v2Warning
The person with the login for the local database of "HD4DP v2 local" has access to all the data stored in the database. This means that the personal data of the patients will be VISIBLE to that user.Requirements
URL Local DWH Database: postgresql://<server_ip>:5432/localdwh. If this is not the case, the IT department hosting HD4DP v2 needs to open the port and allow traffic to this port.
URL NIPPIN Database: postgresql://<server_ip>:5432/nippin
Username/Password: The service desk of healthdata.be will forward, via a secure link, the username and password.
Client: Download one of the clients that support PostgreSQL . A list is available here.
IP network/subnet: Provide us with the IP network/subnet from where you will contact the database. The database only accepts incoming traffic of known IP networks/subnets. example: 0.0.0.0/32
Granted privileges
| database | user | privileges |
| localdwh | dpuser | CONNECT/local_dwhmessage:SELECT/local_dwhmessage_key_value:SELECT/local_dwhmessage_key_value_plus:SELECT |
| nippin | dpuser | CONNECT/nippin_message:SELECT/nippin_cleanup:SELECT |
"data_collection_name" in local database
- "data_collection_name" =
Query examples
With the "data_collection_name" and the following information, you will be able to link multiple tables with each other.
- local_dwhmessage_key_value: Key value table with more information about the registration
- msg_document_id: document id of your message located in local_dwhmessage table
- document_id: document id of your registration
- local_dwhmessage: table where you can find all the registrations
- local_dwhmessage_key_value_plus: Extra table to define attribute type and value of a key value
- key_value_id: Key value id linked to the id of the local_dwh_message_key_value
- local_dwhmessage_key_value:
"local_dwhmessage_key_value" column "msg_document_id" refer to the "document_id" of "local_dwhmessage".
"local_dwhmessage_key_value_plus" column "key_value_id" refer to the id of "local_dwhmessage_key_value".
Query 1: Get all registrations from the last 15 days.
SELECT * from local_dwhmessage WHERE data_collection_name = 'add data_collection_name' and created_on > current_date - interval '15' day;Query 2: Get all registrations and key value.
SELECT * from local_dwhmessage as ldm left join local_dwhmessage_key_value as ldmkv on ldmkv.msg_document_id = ldm.document_id WHERE ldm.data_collection_name = 'add data_collection_name';Query 3: Get all registrations, key value and key value plus from.
SELECT * from local_dwhmessage as ldm left join local_dwhmessage_key_value as ldmkv on ldmkv.msg_document_id = ldm.document_id left join local_dwhmessage_key_value_plus as ldmkvp on ldmkvp.key_value_id = ldmkv.id WHERE ldm.data_collection_name = 'add data_collection_name';This documentation is being updated regularly. We try to provide as correct, complete and clear as possible information on these pages. Nevertheless, if you see anything in the documentation that is not correct, does not match your experience or requires further clarification, please create a request (type : request for information) via our portal (https://sciensano.service-now.com/sp) or send us an e-mail via support.healthdata@sciensano.be to report this documentation issue. Please, do not forget to mention the URL or web address of the page with the documentation issue. We will then adjust the documentation as soon as possible. Thank you!Nippin database
Nippin databaseThe different statuses of a Nippin_message in the database
| Status | Description |
|---|---|
| TO_SEND | HD4DP v2 ready to be sent to MyCareNet or SFTP folder |
| ARCHIVED | Messages are set to ARCHIVED, only if the NippinCleanup table has 0 records. These messages will not be processed and won't receive any attention afterwards. |
| INVALID | XML payload is invalid, a ticket can be created at our service portal, including the payload of the invalid message. |
| BUFFERED | not used |
| ERROR | HD4DP v2 was not able to send the message to MyCareNet or SFTP folder |
| SENT | HD4DP v2 was able to send the message to MyCareNet or SFTP folder |
Nippin_message information table
| Column name | Data type | Description |
|---|---|---|
| id | bigserial | Primary Key |
| message_id | varchar(255) | Identifier for the message |
| identification_value | text | Identification value of the organization that is sending the message |
| name | text | Name of the organization that is sending the message |
| payload | text | Payload of the message |
| payload_after_validation | text | Payload after (xsd-)validation |
| response | text | Response to the message (received from myCarenet) |
| valid | boolean | Whether the message is valid or not |
| interface_type | varchar(25) | Type of interface used for the message (e.g. FILE_SYSTEM or REST) |
| status | varchar(25) | Current status of the message (possible states : INVALID (validation against xsd-scheme failed), TO_SEND (ready for sending to myCarenet), SENT (sent to myCarenet), ERROR (something went wrong during sending, e.g. unable to reach myCarenet)) |
| created_on | timestamp | Timestamp of when the message was created |
| input_reference | varchar(255) | Reference for the input message (this is a unique identifier that can be used for debugging/tracing with myCarenet) |
| issuer | text | Issuer of the message |
| postresponse_tack_applies_to | text | Applies-to value for the TACK post-response (received from myCarenet) |
| postresponse_tack_id | text | ID of the TACK post-response (received from myCarenet) |
| postresponse_tack_reference | text | Reference for the TACK post-response (received from myCarenet) |
| postresponse_tack_resultMajor | text | Result major for the TACK post-response (received from myCarenet) |
| postresponse_tack_resultMinor | text | Result minor for the TACK post-response (received from myCarenet) |
| postresponse_tack_resultMessage | text | Result message for the TACK post-response (received from myCarenet) |
| previous_registrationcode | varchar(255) | Previous registration code for the message (obsolete) |
| current_registrationcode | varchar(255) | Current registration code for the message (value will be identical to previous_registrationcode) |
| version_tag | varchar(255) | Current application-version |
Granted privileges
| Database | User | Privileges |
|---|---|---|
| nippin | dpuser | CONNECT/nippin_message:SELECT/nippin_cleanup:SELECT |
Queries
- Count records grouped by the type and status:
nippin=# select interface_type, status, count(id) from nippin_message group by interface_type,status;
interface_type | status | count
----------------+---------+-------
FILE_SYSTEM | SENT | 117
FILE_SYSTEM | INVALID | 352
FILE_SYSTEM | TO_SEND | 9238
(3 rows)- Get all error and invalid information:
select id, message_id, project_id, dcd_id, payload_after_validation from nippin_message where status in ('INVALID', 'ERROR');- Get previous and current registration code:
select id, message_id, project_id, dcd_id, previous_registrationcode, current_registrationcode from nippin_message;- Get nippin cleanup information:
select * from nippin_cleanup;MyCareNet integration-specific queries
Only for hospitals that are using the Nippin integration in HD4DP v2.
- Count records with status SENT and group them based on the reference ID received from MyCareNet:
select postresponse_tack_result_major, postresponse_tack_reference, count(*) from nippin_message where status = 'SENT' group by status, postresponse_tack_result_major, postresponse_tack_reference;
postresponse_tack_result_major | postresponse_tack_reference | count
-----------------------------------+--------------------------------------+-------
urn:nip:tack:result:major:success | ***** | 2
urn:nip:tack:result:major:success | ***** | 88