postgres-make-concepts.sh

# This file makes tables for the concepts in this subfolder.
# Be sure to run postgres-functions.sql first, as the concepts rely on those function definitions.
# Note that this may take a large amount of time and hard drive space.

# string replacements are necessary for some queries
export REGEX_DATETIME_DIFF="s/DATETIME_DIFF\((.+?),\s?(.+?),\s?(DAY|MINUTE|SECOND|HOUR|YEAR)\)/DATETIME_DIFF(\1, \2, '\3')/g"
export REGEX_SCHEMA='s/`physionet-data.(mimic_derived|mimic_core|mimic_hosp|mimic_icu).(.+?)`/\2/g'
export CONNSTR='mimiciv mimic'

# this is set as the search_path variable for psql
# a search path of "public,mimiciii" will search both public and mimiciii
# schemas for data, but will create tables on the public schema
export PSQL_PREAMBLE='SET search_path TO mimic_derived,mimic_core,mimic_hosp,mimic_icu,public'

echo ''
echo '==='
echo 'Beginning to create tables for MIMIC database.'
echo 'Any notices of the form "NOTICE: MATERIALIZED VIEW "XXXXXX" does not exist" can be ignored.'
echo 'The scripts drop views before creating them, and these notices indicate nothing existed prior to creating the view.'
echo '==='
echo ''

# Durations (usually of treatments)
echo 'Directory 1 of 5: demographics'
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS weight_durations; CREATE MATERIALIZED VIEW weight_durations AS "; cat demographics/weight_durations.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}

echo 'Directory 2 of 5: measurement'
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS bg; CREATE MATERIALIZED VIEW bg AS "; cat measurement/bg.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS blood_differential; CREATE MATERIALIZED VIEW blood_differential AS "; cat measurement/blood_differential.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS cardiac_marker; CREATE MATERIALIZED VIEW cardiac_marker AS "; cat measurement/cardiac_marker.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS chemistry; CREATE MATERIALIZED VIEW chemistry AS "; cat measurement/chemistry.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS coagulation; CREATE MATERIALIZED VIEW coagulation AS "; cat measurement/coagulation.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS complete_blood_count; CREATE MATERIALIZED VIEW complete_blood_count AS "; cat measurement/complete_blood_count.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS enzyme; CREATE MATERIALIZED VIEW enzyme AS "; cat measurement/enzyme.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS gcs; CREATE MATERIALIZED VIEW gcs AS "; cat measurement/gcs.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS height; CREATE MATERIALIZED VIEW height AS "; cat measurement/height.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS icp; CREATE MATERIALIZED VIEW icp AS "; cat measurement/icp.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS inflammation; CREATE MATERIALIZED VIEW inflammation AS "; cat measurement/inflammation.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS oxygen_delivery; CREATE MATERIALIZED VIEW oxygen_delivery AS "; cat measurement/oxygen_delivery.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS rhythm; CREATE MATERIALIZED VIEW rhythm AS "; cat measurement/rhythm.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS urine_output; CREATE MATERIALIZED VIEW urine_output AS "; cat measurement/urine_output.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS ventilator_setting; CREATE MATERIALIZED VIEW ventilator_setting AS "; cat measurement/ventilator_setting.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS vital_sign; CREATE MATERIALIZED VIEW vital_sign AS "; cat measurement/vitalsign.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}

echo 'Directory 3 of 5: durations'
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS ventilator_durations; CREATE MATERIALIZED VIEW ventilator_durations AS "; cat durations/ventilator_durations.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}

echo 'Directory 4 of 5: medication'
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS dobutamine; CREATE MATERIALIZED VIEW dobutamine AS "; cat medication/dobutamine.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS dopamine; CREATE MATERIALIZED VIEW dopamine AS "; cat medication/dopamine.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS epinephrine; CREATE MATERIALIZED VIEW epinephrine AS "; cat medication/epinephrine.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS norepinephrine; CREATE MATERIALIZED VIEW norepinephrine AS "; cat medication/norepinephrine.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS vasopressin; CREATE MATERIALIZED VIEW vasopressin AS "; cat medication/vasopressin.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS phenylephrine; CREATE MATERIALIZED VIEW phenylephrine AS "; cat medication/phenylephrine.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS neuroblock; CREATE MATERIALIZED VIEW neuroblock AS "; cat medication/neuroblock.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}

echo 'Directory 5 of 5: treatment'
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS invasive_line; CREATE MATERIALIZED VIEW invasive_line AS "; cat treatment/invasive_line.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}
{ echo "${PSQL_PREAMBLE}; DROP MATERIALIZED VIEW IF EXISTS rrt; CREATE MATERIALIZED VIEW rrt AS "; cat treatment/rrt.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" | psql ${CONNSTR}

echo 'Finished creating tables.'