Administrators Guide¶

./mgridhdl-1.0-linux-x64-installer.run \
   --pg_config_path /usr/local/postgresql-9.2/bin/pg_config \
   --mode unattended \
   --enable-components hl7v3models_edition2011,snomedctvocab_20110731,loinc_242

createuser --superuser hcuser
createdb -U hcuser hcdb
psql -U hcuser hcdb

ALTER DATABASE hcdb SET search_path=public,hdl,hl7,r1,"$user";
CREATE EXTENSION hl7basetable;
CREATE EXTENSION ucum;
CREATE EXTENSION hl7;
CREATE EXTENSION hl7v3vocab_edition2011;
CREATE EXTENSION hl7v3datatypes;
CREATE EXTENSION hl7v3rim_edition2011;
CREATE EXTENSION hl7v3crud_edition2011;
CREATE EXTENSION hl7v3_c_contextconduction_edition2011;

ALTER ROLE hcuser WITH NOSUPERUSER;

psql --version
  psql (PostgreSQL) 8.2.15
  contains support for command-line editing

createdb hcdb

$(pg_config --sharedir)/contrib/install_hdl.sh hcdb 15432 localhost
        Performing master step 01 schemas ... done
        Performing segment step 02 basetables ... done
        Performing master step 03 ucum-and-uuid-funcs ... done
        Performing segment step 04 ucum-data ... done
        Performing master step 05 hdl ... done

## THE SEARCH_PATH IS NECESSARY TO BE ABLE TO USE THE HEALTHCARE DATATYPES ##
psql hcdb -c "ALTER DATABASE hcdb SET search_path TO public,hl7,hdl,r1"

psql hcdb -tc "SELECT '10 [ft_us]'::pq + '1 [yd_us]'"
 13 [ft_us]

psql --version
        psql (PostgreSQL)
        8.2.15 contains support for command-line editing

## Note: HDL must be loaded and the search_path altered, as is done in the
## previous example!

cat $(pg_config --sharedir)/contrib/hl7v3rim_edition2011--2.0.sql | \
    sed -e 's/MODULE_PATHNAME/\$libdir\/hl7/g' \
        -e 's/, TYPMOD_.*$//g' \
        -e 's/, MERGES//g' \
        -e 's/^COST[ 0-9]\+$//g' \
        -e '/CREATE OPERATOR CLASS "*gin_/,/;$/d' \
        -e '/DELETE FROM pg_depend/,/;$/d' \
        -e '/ALTER TABLE/d' \
        -e '/SELECT __warn_extension_deps_removal/,/;$/d' \
        -e 's/\([^a-z]\)cv\(([^)]\+)\)/\1"CS"\2/gi' \
        -e 's/\([^a-z]CS"*\)([^)]\+)/\1/gi' \
        -e 's/\([^a-z]set"*\)([^)]\+)/\1/gi' \
        -e 's/PRIMARY KEY,/PRIMARY KEY, _id_cluster BIGINT,/g' \
        -e 's/_clonename TEXT,/_clonename TEXT, _pond_timestamp TIMESTAMPTZ, _lake_timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, _record_hash TEXT, _record_weight INT,/g' \
        -e 's/"source" BIGINT, "target" BIGINT,/"source" BIGINT, "target" BIGINT, "source_original" BIGINT, "target_original" BIGINT,/g' \
        -e 's/"act" BIGINT, "role" BIGINT,/"act" BIGINT, "role" BIGINT, "act_original" BIGINT, "role_original" BIGINT,/g' \
        -e 's/"player" BIGINT, "scoper" BIGINT,/"player" BIGINT, "scoper" BIGINT, "player_original" BIGINT, "scoper_original" BIGINT,/g' \
        -e '/CREATE TABLE "[[:alpha:]]*Participation"/ {s/_clonename TEXT,/_clonename TEXT, _origin BIGINT,/}' \
        -e 's/ PRIMARY KEY//g' \
        -e 's/ REFERENCES \"[[:alpha:]]*\"//g' \
        -e 's/"code" "CD",/_code_code TEXT, _code_codesystem TEXT, "code" "CD",/g' \
        -e 's/"value" "ANY",/_value_pq pq,_value_pq_value NUMERIC, _value_pq_unit TEXT,_value_code_code TEXT, _value_code_codesystem TEXT, _value_int INT, _value_real NUMERIC, _value_ivl_real ivl_real, "value" "ANY",/g' \
        -e 's/, "effectiveTime"/, _effective_time_low TIMESTAMPTZ, _effective_time_low_year INT, _effective_time_low_month INT, _effective_time_low_day INT, _effective_time_high TIMESTAMPTZ, _effective_time_high_year INT, _effective_time_high_month INT, _effective_time_high_day INT, "effectiveTime"/g' \
        -e '/CREATE TABLE "Act"/ {s/;//}' \
        -e '/CREATE TABLE "Act"/ {a\
WITH (appendonly = false)\
DISTRIBUTED BY (_id);
}' \
        -e '/CREATE TABLE "Participation"/ {s/;//}' \
        -e '/CREATE TABLE "Participation"/ {a\
WITH (appendonly = true, compresslevel = 6)\
DISTRIBUTED BY (act);\
ALTER TABLE "Participation" SET DISTRIBUTED BY (act);
}' \
        -e '/CREATE TABLE "[[:alpha:]]*"/ {s/ INHERITS (\"Observation\")//}' \
        -e '/CREATE TABLE "Observation"/ {s/ INHERITS (\"[[:alpha:]]*\");//}' \
        -e '/CREATE TABLE "Observation"/ {a\
WITH (appendonly = true, orientation = column, compresslevel = 6)\
DISTRIBUTED BY (_id)\
PARTITION BY RANGE (_effective_time_low_year)\
    SUBPARTITION BY RANGE (_effective_time_low_month)\
    SUBPARTITION TEMPLATE (\
        START (1) END (13) EVERY (1),\
        DEFAULT SUBPARTITION other_months )\
    (START (2008) END (2016) EVERY (1),\
        DEFAULT PARTITION other_years );
}'| PGOPTIONS='--client-min-messages=warning' psql -q1 --dbname hcdb

psql hcdb
  psql (8.2.15)
  Type "help" for help.

hcdb=# \d
                                   List of relations
 Schema |                Name                |   Type   | Owner |       Storage
--------+------------------------------------+----------+-------+----------------------
 hdl | pg_cbinding                        | table    | m     | heap
 hdl | pg_code                            | table    | m     | heap
 hdl | pg_conceptdomain                   | table    | m     | heap
 hdl | pg_oid                             | table    | m     | heap
 hdl | pg_oid_seq                         | sequence | m     | heap
 hdl | pg_oidversion                      | table    | m     | heap
 hdl | pg_ucumprefix                      | table    | m     | heap
 hdl | pg_ucumunit                        | table    | m     | heap
 public | Access                             | table    | m     | heap
 public | Account                            | table    | m     | heap
 public | Acknowledgement                    | table    | m     | heap
 public | AcknowledgementDetail              | table    | m     | heap
 public | Act                                | table    | m     | heap
 public | ActRelationship                    | table    | m     | heap
..
 public | QuerySpec                          | table    | m     | heap
 public | Role                               | table    | m     | heap
 public | RoleLink                           | table    | m     | heap
 public | SortControl                        | table    | m     | heap
 public | SubstanceAdministration            | table    | m     | heap
 public | Supply                             | table    | m     | heap
 public | Transmission                       | table    | m     | heap
 public | TransmissionRelationship           | table    | m     | heap
 public | Transmission_CommunicationFunction | table    | m     | heap
 public | WorkingList                        | table    | m     | heap
(70 rows)

hcdb=# \d "Observation"
Append-Only Columnar Table "public.Observation"
       Column        |   Type    | Modifiers
---------------------+-----------+-----------
 _id                 | bigint    |
 _mif                | text      |
 _clonename          | text      |
 nullFlavor          | "CS"      |
 realmCode           | set       |
 typeId              | "II"      |
 templateId          | "LIST_II" |
 classCode           | "CS"      |
 moodCode            | "CS"      |
 id                  | "SET_II"  |
 code                | "CD"      |
 actionNegationInd   | bl        |
 negationInd         | bl        |
 derivationExpr      | st        |
 title               | "ED"      |
 text                | "ED"      |
 statusCode          | "CS"      |
 effectiveTime       | "GTS"     |
 activityTime        | "GTS"     |
 availabilityTime    | ts        |
 priorityCode        | set       |
 confidentialityCode | set       |
 repeatNumber        | ivl_int   |
 interruptibleInd    | bl        |
 levelCode           | "CS"      |
 independentInd      | bl        |
 uncertaintyCode     | "CS"      |
 reasonCode          | set       |
 languageCode        | "CS"      |
 isCriterionInd      | bl        |
 value               | "ANY"     |
 valueNegationInd    | bl        |
 interpretationCode  | set       |
 methodCode          | set       |
 targetSiteCode      | "SET_CD"  |
Checksum: f
Distributed by: (_id)

hcdb=# explain select * from "Observation";
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..0.00 rows=1 width=1096)
   ->  Append-only Columnar Scan on "Observation"  (cost=0.00..0.00 rows=1 width=1096)
(2 rows)