Package com.ibm.fhir.schema.control

Class FhirResourceTableGroup

  • public class FhirResourceTableGroup
extends Object
    Utility to create all the tables associated with a particular resource type

    • Method Summary

      All Methods Instance Methods Concrete Methods 
      Modifier and Type Method Description
      void addDateValues​(List<IDatabaseObject> group, String prefix)
      CREATE TABLE device_date_values ( row_id BIGINT NOT NULL, parameter_name_id INT NOT NULL, date_start TIMESTAMP, date_end TIMESTAMP, resource_id BIGINT NOT NULL ) ; CREATE INDEX idx_device_date_values_pser ON device_date_values(parameter_name_id, date_start, date_end, resource_id); CREATE INDEX idx_device_date_values_pesr ON device_date_values(parameter_name_id, date_end, date_start, resource_id); CREATE INDEX idx_device_date_values_rpse ON device_date_values(resource_id, parameter_name_id, date_start, date_end); ALTER TABLE device_date_values ADD CONSTRAINT fk_device_date_values_pn FOREIGN KEY (parameter_name_id) REFERENCES parameter_names; ALTER TABLE device_date_values ADD CONSTRAINT fk_device_date_values_r FOREIGN KEY (resource_id) REFERENCES device_resources;
      void addLatLngValues​(List<IDatabaseObject> group, String prefix)
      CREATE TABLE device_latlng_values ( row_id BIGINT NOT NULL, parameter_name_id INT NOT NULL, latitude_value DOUBLE, longitude_value DOUBLE, resource_id BIGINT NOT NULL ) CREATE INDEX idx_device_latlng_values_pnnlv ON device_latlng_values(parameter_name_id, latitude_value, resource_id); CREATE INDEX idx_device_latlng_values_pnnhv ON device_latlng_values(parameter_name_id, longitude_value, resource_id); CREATE INDEX idx_device_latlng_values_rplat ON device_latlng_values(resource_id, parameter_name_id, latitude_value); CREATE INDEX idx_device_latlng_values_rplng ON device_latlng_values(resource_id, parameter_name_id, longitude_value); ALTER TABLE device_latlng_values ADD CONSTRAINT fk_device_latlng_values_pn FOREIGN KEY (parameter_name_id) REFERENCES parameter_names; ALTER TABLE device_latlng_values ADD CONSTRAINT fk_device_latlng_values_r FOREIGN KEY (resource_id) REFERENCES device_resources;
      void addListLogicalResourceItems​(List<IDatabaseObject> group, String prefix)
      Special case for LIST resources where we attach a child table to its LIST_LOGICAL_RESOURCES to support usage of the list items in search queries.
      void addLogicalResources​(List<IDatabaseObject> group, String prefix)
      Add the logical_resources table definition for the given resource prefix
      void addNumberValues​(List<IDatabaseObject> group, String prefix)
      -- ---------------------------------------------------------------------------- -- -- ---------------------------------------------------------------------------- CREATE TABLE device_number_values ( row_id BIGINT NOT NULL, parameter_name_id INT NOT NULL, number_value DOUBLE, resource_id BIGINT NOT NULL ) ; CREATE INDEX idx_device_number_values_pnnv ON device_number_values(parameter_name_id, number_value, resource_id); CREATE INDEX idx_device_number_values_rps ON device_number_values(resource_id, parameter_name_id, number_value); ALTER TABLE device_number_values ADD CONSTRAINT fk_device_number_values_pn FOREIGN KEY (parameter_name_id) REFERENCES parameter_names; ALTER TABLE device_number_values ADD CONSTRAINT fk_device_number_values_r FOREIGN KEY (resource_id) REFERENCES device_resources;
      void addPatientCurrentRefs​(List<IDatabaseObject> group, String prefix)
      Add the extension table used to support references to the current resources lists defined by the spec: https://www.hl7.org/fhir/lifecycle.html#current
      void addQuantityValues​(List<IDatabaseObject> group, String prefix)
      CREATE TABLE device_quantity_values ( row_id BIGINT NOT NULL, parameter_name_id INT NOT NULL, code VARCHAR(255 OCTETS) NOT NULL, quantity_value DOUBLE, quantity_value_low DOUBLE, quantity_value_high DOUBLE, code_system_id INT, resource_id BIGINT NOT NULL ) ; CREATE INDEX idx_device_quantity_values_pnnv ON device_quantity_values(parameter_name_id, code, quantity_value, resource_id, code_system_id); CREATE INDEX idx_device_quantity_values_rps ON device_quantity_values(resource_id, parameter_name_id, code, quantity_value, code_system_id); CREATE INDEX idx_device_quantity_values_pclhsr ON device_quantity_values(parameter_name_id, code, quantity_value_low, quantity_value_high, code_system_id, resource_id); CREATE INDEX idx_device_quantity_values_pchlsr ON device_quantity_values(parameter_name_id, code, quantity_value_high, quantity_value_low, code_system_id, resource_id); CREATE INDEX idx_device_quantity_values_rpclhs ON device_quantity_values(resource_id, parameter_name_id, code, quantity_value_low, quantity_value_high, code_system_id); CREATE INDEX idx_device_quantity_values_rpchls ON device_quantity_values(resource_id, parameter_name_id, code, quantity_value_high, quantity_value_low, code_system_id); ALTER TABLE device_quantity_values ADD CONSTRAINT fk_device_quantity_values_pn FOREIGN KEY (parameter_name_id) REFERENCES parameter_names; ALTER TABLE device_quantity_values ADD CONSTRAINT fk_device_quantity_values_r FOREIGN KEY (resource_id) REFERENCES device_resources;
      void addResources​(List<IDatabaseObject> group, String prefix)
      Add the resources table definition
      Table addResourceTokenRefs​(List<IDatabaseObject> group, String prefix)
      parameter_name_id INT NOT NULL, code_system_id INT NOT NULL, token_value VARCHAR(255 OCTETS), resource_id BIGINT NOT NULL composite_id SMALLINT ) ; /** New schema for issue #1366.
      ObjectGroup addResourceType​(String resourceTypeName)
      Add all the tables required for the given resource type.
      void addStrValues​(List<IDatabaseObject> group, String prefix)
      Add the STR_VALUES table for the given resource name prefix
      void addTokenValuesView​(List<IDatabaseObject> group, String prefix)
      View created over common_token_values and resource_token_refs to hide the schema change (V0006 issue 1366) as much as possible from the search query generation.

    • Method Detail

      • addResourceType

        public ObjectGroup addResourceType​(String resourceTypeName)
        Add all the tables required for the given resource type. For example, if the resourceTypeName is Patient, the following tables will be added:
        • patient_logical_resources
        • patient_resources
        • patient_str_values
        • patient_date_values
        • patient_token_values
        • patient_number_values
        • patient_latlng_values
        • patient_quantity_values
        Parameters:
        resourceTypeName -

      • addLogicalResources

        public void addLogicalResources​(List<IDatabaseObject> group,
                                String prefix)
        Add the logical_resources table definition for the given resource prefix
        Parameters:
        group -
        prefix -

      • addResources

        public void addResources​(List<IDatabaseObject> group,
                         String prefix)
        Add the resources table definition 
          resource_id            BIGINT             NOT NULL,
  logical_resource_id    BIGINT             NOT NULL,
  version_id                INT             NOT NULL,
  last_updated        TIMESTAMP             NOT NULL,
  is_deleted               CHAR(1)          NOT NULL,
  data                     BLOB(2147483647) INLINE LENGTH 10240;

  CREATE UNIQUE INDEX device_resource_prf_in1    ON device_resources (resource_id) INCLUDE (logical_resource_id, version_id, is_deleted);
        Parameters:
        group -
        prefix -

      • addStrValues

        public void addStrValues​(List<IDatabaseObject> group,
                         String prefix)
        Add the STR_VALUES table for the given resource name prefix 
          parameter_name_id        INT             NOT NULL,
  str_value            VARCHAR(511 OCTETS),
  str_value_lcase      VARCHAR(511 OCTETS),
  resource_id           BIGINT             NOT NULL,
  composite_id        SMALLINT

CREATE INDEX idx_device_str_values_psr ON device_str_values(parameter_name_id, str_value, resource_id);
CREATE INDEX idx_device_str_values_plr ON device_str_values(parameter_name_id, str_value_lcase, resource_id);
CREATE INDEX idx_device_str_values_rps ON device_str_values(resource_id, parameter_name_id, str_value);
CREATE INDEX idx_device_str_values_rpl ON device_str_values(resource_id, parameter_name_id, str_value_lcase);
ALTER TABLE device_str_values ADD CONSTRAINT fk_device_str_values_pnid FOREIGN KEY (parameter_name_id) REFERENCES parameter_names;
ALTER TABLE device_str_values ADD CONSTRAINT fk_device_str_values_pnid FOREIGN KEY (parameter_name_id) REFERENCES parameter_names;
ALTER TABLE device_str_values ADD CONSTRAINT fk_device_str_values_rid  FOREIGN KEY (resource_id) REFERENCES device_resources;
        Parameters:
        group -
        prefix -

      • addResourceTokenRefs

        public Table addResourceTokenRefs​(List<IDatabaseObject> group,
                                  String prefix)
          parameter_name_id        INT NOT NULL,
  code_system_id           INT NOT NULL,
  token_value          VARCHAR(255 OCTETS),
  resource_id           BIGINT NOT NULL
  composite_id        SMALLINT
)
;

    /**
 New schema for issue #1366. Uses a map table to reduce cost of indexing repeated token values
        Parameters:
        pdm -
        Returns:

      • addTokenValuesView

        public void addTokenValuesView​(List<IDatabaseObject> group,
                               String prefix)
        View created over common_token_values and resource_token_refs to hide the schema change (V0006 issue 1366) as much as possible from the search query generation. Search queries simply need to join against this view instead of the old {resourceType}_token_values table
        Parameters:
        pdm -

      • addDateValues

        public void addDateValues​(List<IDatabaseObject> group,
                          String prefix)
        CREATE TABLE device_date_values  (
  row_id                BIGINT             NOT NULL,
  parameter_name_id         INT NOT NULL,
  date_start          TIMESTAMP,
  date_end            TIMESTAMP,
  resource_id            BIGINT NOT NULL
)
;

CREATE INDEX idx_device_date_values_pser ON device_date_values(parameter_name_id, date_start, date_end, resource_id);
CREATE INDEX idx_device_date_values_pesr ON device_date_values(parameter_name_id, date_end, date_start, resource_id);
CREATE INDEX idx_device_date_values_rpse   ON device_date_values(resource_id, parameter_name_id, date_start, date_end);
ALTER TABLE device_date_values ADD CONSTRAINT fk_device_date_values_pn FOREIGN KEY (parameter_name_id) REFERENCES parameter_names;
ALTER TABLE device_date_values ADD CONSTRAINT fk_device_date_values_r  FOREIGN KEY (resource_id)       REFERENCES device_resources;
        Parameters:
        group -
        prefix -

      • addNumberValues

        public void addNumberValues​(List<IDatabaseObject> group,
                            String prefix)
        -- ----------------------------------------------------------------------------
--
-- ----------------------------------------------------------------------------
CREATE TABLE device_number_values  (
  row_id               BIGINT NOT NULL,
  parameter_name_id       INT NOT NULL,
  number_value         DOUBLE,
  resource_id          BIGINT NOT NULL
)
;
CREATE INDEX idx_device_number_values_pnnv ON device_number_values(parameter_name_id, number_value, resource_id);
CREATE INDEX idx_device_number_values_rps ON device_number_values(resource_id, parameter_name_id, number_value);
ALTER TABLE device_number_values ADD CONSTRAINT fk_device_number_values_pn FOREIGN KEY (parameter_name_id) REFERENCES parameter_names;
ALTER TABLE device_number_values ADD CONSTRAINT fk_device_number_values_r  FOREIGN KEY (resource_id)       REFERENCES device_resources;
        Parameters:
        group -
        prefix -

      • addLatLngValues

        public void addLatLngValues​(List<IDatabaseObject> group,
                            String prefix)
        CREATE TABLE device_latlng_values  (
  row_id              BIGINT NOT NULL,
  parameter_name_id   INT NOT NULL,
  latitude_value      DOUBLE,
  longitude_value     DOUBLE,
  resource_id         BIGINT NOT NULL
)
CREATE INDEX idx_device_latlng_values_pnnlv ON device_latlng_values(parameter_name_id, latitude_value, resource_id);
CREATE INDEX idx_device_latlng_values_pnnhv ON device_latlng_values(parameter_name_id, longitude_value, resource_id);
CREATE INDEX idx_device_latlng_values_rplat ON device_latlng_values(resource_id, parameter_name_id, latitude_value);
CREATE INDEX idx_device_latlng_values_rplng ON device_latlng_values(resource_id, parameter_name_id, longitude_value);
ALTER TABLE device_latlng_values ADD CONSTRAINT fk_device_latlng_values_pn FOREIGN KEY (parameter_name_id) REFERENCES parameter_names;
ALTER TABLE device_latlng_values ADD CONSTRAINT fk_device_latlng_values_r  FOREIGN KEY (resource_id)       REFERENCES device_resources;
        Parameters:
        group -
        prefix -

      • addQuantityValues

        public void addQuantityValues​(List<IDatabaseObject> group,
                              String prefix)
        CREATE TABLE device_quantity_values  (
  row_id                BIGINT NOT NULL,
  parameter_name_id        INT NOT NULL,
  code                 VARCHAR(255 OCTETS) NOT NULL,
  quantity_value        DOUBLE,
  quantity_value_low    DOUBLE,
  quantity_value_high   DOUBLE,
  code_system_id           INT,
  resource_id           BIGINT NOT NULL
)
;

CREATE INDEX idx_device_quantity_values_pnnv   ON device_quantity_values(parameter_name_id, code, quantity_value, resource_id, code_system_id);
CREATE INDEX idx_device_quantity_values_rps    ON device_quantity_values(resource_id, parameter_name_id, code, quantity_value, code_system_id);

CREATE INDEX idx_device_quantity_values_pclhsr  ON device_quantity_values(parameter_name_id, code, quantity_value_low, quantity_value_high, code_system_id, resource_id);
CREATE INDEX idx_device_quantity_values_pchlsr  ON device_quantity_values(parameter_name_id, code, quantity_value_high, quantity_value_low, code_system_id, resource_id);
CREATE INDEX idx_device_quantity_values_rpclhs  ON device_quantity_values(resource_id, parameter_name_id, code, quantity_value_low, quantity_value_high, code_system_id);
CREATE INDEX idx_device_quantity_values_rpchls  ON device_quantity_values(resource_id, parameter_name_id, code, quantity_value_high, quantity_value_low, code_system_id);

ALTER TABLE device_quantity_values ADD CONSTRAINT fk_device_quantity_values_pn FOREIGN KEY (parameter_name_id) REFERENCES parameter_names;
ALTER TABLE device_quantity_values ADD CONSTRAINT fk_device_quantity_values_r  FOREIGN KEY (resource_id)       REFERENCES device_resources;
        Parameters:
        group -
        prefix -

      • addListLogicalResourceItems

        public void addListLogicalResourceItems​(List<IDatabaseObject> group,
                                        String prefix)
        Special case for LIST resources where we attach a child table to its LIST_LOGICAL_RESOURCES to support usage of the list items in search queries. The FK to LIST_LOGICAL_RESOURCES is its parent. We then point to the resource being referenced via a resourceType/logicalId tuple. This means that the list item record can be created before the referenced resource is created.
        Parameters:
        group -
        prefix -

      • addPatientCurrentRefs

        public void addPatientCurrentRefs​(List<IDatabaseObject> group,
                                  String prefix)
        Add the extension table used to support references to the current resources lists defined by the spec: https://www.hl7.org/fhir/lifecycle.html#current
        Parameters:
        group - - the group of tables for this resource (Patient in this case)
        prefix - - the resource name - PATIENT