3 Creating MPI and IHE Databases and Tables

This chapter provides procedures that lead you through creating Master Person Index and IHE Profiles Application databases and tables.

This chapter includes the following section:

• "Creating the IHE Profiles Application Databases and Tables"

Creating the IHE Profiles Application Databases and Tables

After installing OHMPI you need to create the Master Person Index, IHE Profiles Application database tables. You must create these tables before you can work with the IHE Profiles Application. You can either create the databases and tables through OHMPI design-time (which is recommended), or create them manually.

Note:

Creating a database is optional; if desired you can create your own database.

To create databases and table manually, use:

• "Creating Master Person Index Databases and Tables for MySQL Manually"

• "Creating Master Person Index Databases and Tables for Oracle Manually"

To create databases and tables using OHMPI design-time, use:

• "Creating the Database and Tables Automatically"

To configure optional connecting information, use:

• "Maintaining Subscriptions to Patient Updates"

Creating Master Person Index Databases and Tables for MySQL Manually

Before you begin, make sure you have MySQL installed as a service with remote host access enabled. Perform the following steps to create all databases:

• "To Create the Master Person Index Database (MySQL)"

• "To Create the IHE Profiles Application Database Tables (MySQL)"

To Create the Master Person Index Database (MySQL)

The IHE Profiles Application solution currently supports MySQL 5.1.x. Before you begin this process, be sure you have MySQL installed as a service with remote host access enabled.

• Create a password for the root user.

• Run the following command against the root user:

  grant all privileges on *.* to 'root'@'%' with grant option;

1. Create a schema in your MySQL environment named mpi by running the following command:

   create database mpi;

2. Create a user for the Master Person Index tables by running the following command:

   grant alter,alter routine,create,create routine,delete,drop,execute,index,insert,select,trigger,update,create temporary tables on mpi.* to '<USER_NAME>' identified by '<PASSWORD>'

3. Open systems.sql in a text editor, and add the HL7 systems with which you will be sharing data.

   Note:

   See Step 6 for the location of this file.

   For more information about modifying this file, see "Step 4: Define Master Person Index External Systems" in Oracle Healthcare Master Person Index User's Guide (Part Number E18468-01).

4. Open codelist.sql in a text editor, and add any common code information you need for the master person index system.

   Note:

   See Step 6 for the location of this file.

   For more information about code lists and modifying this file, see "Step 5: Define Master Person Index Code Lists" in Oracle Healthcare Master Person Index User's Guide (Part Number E18468-01).

   Note:

   This step is optional. The default configuration does not use common code tables. Adding this feature will cause additional validations to be performed against incoming data.

5. Log in to the mpi schema as the user you created above.

6. Run the following SQL files against the mpi schema (<project_name>\ihe-mpi\src\DatabaseScript). The create.sql file must be run first.

   • create.sql

   • systems.sql

   • codelist.sql

Continue to "To Create the IHE Profiles Application Database Tables (MySQL)".

To Create the IHE Profiles Application Database Tables (MySQL)

Before you begin, complete "To Create the Master Person Index Database (MySQL)".

1. Navigate to the directory to which you created the IHE Profiles Application projects and then navigate to the <project_name>\src\DatabaseScripts folder.

   • create_ihe_ohmpi_tables.sql creates all the IHE Profiles Application tables.

   • create_ihe_ohmpi_sample_data.sql creates sample domain configuration data.

   • clean_ihe_ohmpi_tables.sql cleanses all the records from IHE Profiles Application tables and MPI tables.

2. Open create_ihe_ohmpi_sample_data.sql in a text editor, comment out all existing insert statements, and add any systems you added to the systems.sql file in "To Create the Master Person Index Database (MySQL)".

   You need to insert the corresponding namespace ID, universal ID, universal ID type and a description for each new system; for example:

   insert into IHE_DOMAINS (NAMESPACEID, UNIVERSALID, UNIVERSALIDTYPE, DESCRIPTON) values ('HOSPITAL1', '1.4.5.2.6.2.23455', 'ISO', 'HOSPITAL1 DESCRIPTION');

3. (Optional) To configure patient update notification, see "Maintaining Subscriptions to Patient Updates".

4. Save and close the file.

5. Log in to the mpi schema as the user you created in "To Create the Master Person Index Database (MySQL)".

6. Run the following scripts against the mpi schema.

   • create_ihe_ohmpi_tables.sql

   • create_ihe_ohmpi_sample_data.sql

Creating Master Person Index Databases and Tables for Oracle Manually

Before you begin, make sure you have Oracle installed as a service with remote host access enabled. Perform the following steps to create all databases:

• To create the IHE Profiles Application Database Tables (Oracle)

To Create the Master Person Index Database (Oracle)

1. Create an Oracle environment and user for the Master Person Index tables by running the following command:

   grant CREATE SESSION, CREATE TABLE, CREATE TRIGGER, CREATE PROCEDURE, UNLIMITED TABLESPACE, CREATE TYPE to <USER_NAME> identified by <PASSWORD>

2. Open systems.sql in a text editor, and add the HL7 systems with which you will be sharing data.

   Note:

   See Step 5 for the location of this file.

   For more information about modifying this file, see "Step 4: Define Master Person Index External Systems" in Oracle Healthcare Master Person Index User's Guide (Part Number E18468-01).

3. Open codelist.sql in a text editor, and add any common code information you need for the master person index system.

   Note:

   See Step 5 for the location of this file.

   For more information about code lists and modifying this file, see "Step 5: Define Master Person Index Code Lists" in Oracle Healthcare Master Person Index User's Guide (Part Number E18468-01).

   Note:

   This step is optional. The default configuration does not use common code tables. Adding this feature will cause additional validations to be performed against incoming data.

4. Log in to the mpi schema as the user you created above.

5. Run the following SQL files against the mpi schema (<project_name>\ihe-mpi\src\DatabaseScript). The create.sql file must be run first.

   • create.sql

   • systems.sql

   • codelist.sql

Continue to "To Create the IHE Profiles Application Database Tables (Oracle)".

To Create the IHE Profiles Application Database Tables (Oracle)

Before you begin, complete "To Create the Master Person Index Database (Oracle)".

1. Navigate to the directory to which you created the IHE Profiles <project_name> Application projects, and then navigate to the \src\DatabaseScripts folder.

   • create_ihe_ohmpi_tables.sql creates all the IHE Profiles Application tables.

   • create_ihe_ohmpi_sample_data.sql creates sample domain configuration data.

   • clean_ihe_ohmpi_tables.sql cleans all the records from IHE Profiles Application tables and MPI tables.

2. Open create_ihe_ohmpi_sample_data.sql in a text editor, comment out all existing insert statements, and add any systems you added to the systems.sql file in "To Create the Master Person Index Database (Oracle)".

   You need to insert the corresponding namespace ID, universal ID, universal ID type and a description for each new system; for example:

   insert into IHE_DOMAINS (NAMESPACEID, UNIVERSALID, UNIVERSALIDTYPE, DESCRIPTON) values ('HOSPITAL1', '1.4.5.2.6.2.23455', 'ISO', 'HOSPITAL1 DESCRIPTION');

3. (Optional) To configure patient update notification, see "Maintaining Subscriptions to Patient Updates".

4. Save and close the file.

5. Log in to the mpi schema as the user you created in "To Create the Master Person Index Database (Oracle)".

6. Run the following scripts against the mpi schema.

   • create_ihe_ohmpi_tables.sql

   • create_ihe_ohmpi_sample_data.sql

Creating the Database and Tables Automatically

1. Right click the name of your IHE Profiles Application project in the left panel of the NetBeans IDE (for example, IHEProject1) and choose Create Database.

   The Create Database dialog appears. Here you provide the credentials for an existing Oracle or MySQL server.

2. With the Root Connection tab selected, do the following to configure the database:

   • Server Host: The name of the server where the database is located.

   • Root User - The login name to the database.

   • Root Password: The password used to log in to the database.

   • Server Port: The port number of the server where the database is located.

   • SQL URL: The URL for the database.

3. After the Connection requirements for the database are set, click Test Connection.

4. When satisfied with the Connection settings, click the User-Schema tab.

5. With the User-Schema tab selected, do the following to configure the database User:

   • View the User for the database from the list.

   • Accept the default password or type a new password.

   • Click Apply.

6. Click the Database Scripts tab to view the scripts and then do the following:

   • To run the scripts, click Run.

   • After the scripts complete running, click Close.

Maintaining Subscriptions to Patient Updates

The IHE Profiles Application was designed to be implemented with minimal required customizations. You can customize and extend the functionality of OHMPI's connectivity information for a product environment.

In order for a target domain (PIX Consumer) to receive PIX Update Notifications from a sending domain (source domain), add a subscription record to the IHE_PIXUPDATE_SUBSCRIPTIONS table. For example,

insert into IHE_PIXUPDATE_SUBSCRIPTIONS (NAMESPACEID, SOURCENAMESPACEID) values ('IHEGHCPHARM', 'IHEGHCADT');

where IHEGHCPHARM identifies a PIX Consumer that receives updates and IHEGHCADT is the source domain that sends updates.

For each PIX Consumer that is interested in receiving PIX Update Notifications, a record describing the PIX Consumer's endpoint is required to be added to the IHE_PIXCONSUMER_ENDPOINTS table.

Make sure the following is correct when configuring PIX Consumer Endpoints:

• The identifier of the PIX Consumer. This needs to match the NAMESPACEID in the IHE_PIXUPDATE_SUBSCRIPTIONS table.

• The endpoint type (either "HL7V2" or "HL7V3").

• The LLPType (applicable only if the endpoint type is "HL7V2").

• The receiving URL for the PIX Consumer.

• The name of the HL7 application or device.

• The name of the facility or organization (organization is optional).

The following is an example describing a PIXv2 Consumer:

insert into IHE_PIXCONSUMER_ENDPOINTS (NAMESPACEID, ENDPOINTTYPE, LLPTYPE, ADDRESS, APPL_DEV, FACL_ORG, DESCRIPTION) values ('SUNPIXPDQRAD', 'HL7V2', 'MLLPv1', 'hl7://localhost:9906', 'SUNPIXPDQRAD', NULL, 'Test domain');

The following is an example describing a PIXv3 Consumer:

insert into IHE_PIXCONSUMER_ENDPOINTS (NAMESPACEID, ENDPOINTTYPE, LLPTYPE, ADDRESS, APPL_DEV, FACL_ORG, DESCRIPTION) values ('IHEGHCPHARM', 'HL7V3', NULL, 'http://localhost:8080/PIXConsumer2_Service/PIXConsumer2', '1.2.840.114350.1.13.99997.2.3412', NULL, 'Test domain');