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.
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.
"data_collection_name" in local database
- Orthopride Total Femur registration "Primo-implantation" = OP_MP_PRIM_IMPLT
- Orthopride Total Femur registration "Revision" = OP_MP_REVIS
- Orthopride Total Femur registration "Resection" = OP_MP_RESEC
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" 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';
Query 4: Get all MyCareNet registrations, key value and key value plus.
SELECT value 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'and key = 'TX_REGN_CD'; select * from local_dwhmessage_key_value where msg_document_id in ( select msg_document_id from local_dwhmessage_key_value where key = 'TX_REGN_CD' and value = 'use value from first query'); select * from local_dwhmessage where document_id in ( select msg_document_id from local_dwhmessage_key_value where key = 'TX_REGN_CD' and value = 'use value from first query');
Query 5: Connect to the Nippin database postgresql://<server_ip>:5432/nippin (same user/password) to validate the current state and payload for the nippin message based on the registration code.
select * from nippin_message where current_registrationcode = 'use the value of Query 4 (first query)';
This documentation is under construction. 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 send us an email via firstname.lastname@example.org 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!