R/m3_addInfo.R
In yikeshu0611/mimicR: MIMIC database with R
Defines functions
addInfo_3
Documented in
addInfo_3
#' add functions, schema and tables
#' create 13 funtions, shema mimic3 and 26 tables
#' @param conn connection, if missing, it will be get 'connectiontopsql' from global environment.
#'
#' @return 13 funtions, shema mimic3 and 26 tables in PostgreSQL.
#' @export
#'
#' @examples
#' \donttest{
#' addInfo_3()
#' }
addInfo_3 <- function(user='postgres',password = 'pg'){
con <- DBI::dbConnect(drv = RPostgreSQL::PostgreSQL(),
user = user,
password = password)
conn <- dbplyr::src_dbi(con, auto_disconnect = TRUE)
# create database
datname <- as.data.frame(dplyr::tbl(conn,dbplyr::sql('SELECT datname FROM pg_database')))[,1]
if (! 'mimic' %in% tolower(datname)){
message('\ncreate database mimic')
DBI::dbGetQuery(conn = conn$con,
statement = "CREATE DATABASE mimic;")
}
con <- DBI::dbConnect(drv = RPostgreSQL::PostgreSQL(),
user = user,
password = password,
dbname='mimic')
conn <- dbplyr::src_dbi(con, auto_disconnect = TRUE)
# add schema -------
message('\nadd schema mimic3')
DBI::dbGetQuery(conn = conn$con,
statement = "CREATE SCHEMA IF NOT EXISTS mimic3;")
# 13 functions ------------------------------------------
message('\nadd 13 functions')
bigquery_format_to_psql <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.bigquery_format_to_psql(character varying)\n\n-- DROP FUNCTION mimic3.bigquery_format_to_psql(character varying);\n\nCREATE OR REPLACE FUNCTION mimic3.bigquery_format_to_psql(\n\tformat_str character varying)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN \n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n format_str\n , '%S', 'SS'\n )\n , '%M', 'MI'\n )\n , '%H', 'HH24'\n )\n , '%d', 'dd'\n )\n , '%m', 'mm'\n )\n , '%Y', 'yyyy'\n )\n;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.bigquery_format_to_psql(character varying)\n OWNER TO postgres;\n"
datetime1 <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.datetime(date)\n\n-- DROP FUNCTION mimic3.datetime(date);\n\nCREATE OR REPLACE FUNCTION mimic3.datetime(\n\tdt date)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN CAST(dt AS TIMESTAMP(3));\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.datetime(date)\n OWNER TO postgres;\n"
datetime2 <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.datetime(integer, integer, integer, integer, integer, integer)\n\n-- DROP FUNCTION mimic3.datetime(integer, integer, integer, integer, integer, integer);\n\nCREATE OR REPLACE FUNCTION mimic3.datetime(\n\tyear integer,\n\tmonth integer,\n\tday integer,\n\thour integer,\n\tminute integer,\n\tsecond integer)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_TIMESTAMP(\n TO_CHAR(year, '0000') || TO_CHAR(month, '00') || TO_CHAR(day, '00') || TO_CHAR(hour, '00') || TO_CHAR(minute, '00') || TO_CHAR(second, '00'),\n 'yyyymmddHH24MISS'\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.datetime(integer, integer, integer, integer, integer, integer)\n OWNER TO postgres;\n"
datetime_add <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.datetime_add(timestamp without time zone, interval)\n\n-- DROP FUNCTION mimic3.datetime_add(timestamp without time zone, interval);\n\nCREATE OR REPLACE FUNCTION mimic3.datetime_add(\n\tdatetime_val timestamp without time zone,\n\tintvl interval)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN datetime_val + intvl;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.datetime_add(timestamp without time zone, interval)\n OWNER TO postgres;\n"
datetime_diff <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.datetime_diff(timestamp without time zone, timestamp without time zone, text)\n\n-- DROP FUNCTION mimic3.datetime_diff(timestamp without time zone, timestamp without time zone, text);\n\nCREATE OR REPLACE FUNCTION mimic3.datetime_diff(\n\tendtime timestamp without time zone,\n\tstarttime timestamp without time zone,\n\tdatepart text)\n RETURNS numeric\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN \n EXTRACT(EPOCH FROM endtime - starttime) /\n CASE\n WHEN datepart = 'SECOND' THEN 1.0\n WHEN datepart = 'MINUTE' THEN 60.0\n WHEN datepart = 'HOUR' THEN 3600.0\n WHEN datepart = 'DAY' THEN 24*3600.0\n WHEN datepart = 'YEAR' THEN 365.242*24*3600.0\n ELSE NULL END;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.datetime_diff(timestamp without time zone, timestamp without time zone, text)\n OWNER TO postgres;\n"
datetime_sub <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.datetime_sub(timestamp without time zone, interval)\n\n-- DROP FUNCTION mimic3.datetime_sub(timestamp without time zone, interval);\n\nCREATE OR REPLACE FUNCTION mimic3.datetime_sub(\n\tdatetime_val timestamp without time zone,\n\tintvl interval)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN datetime_val - intvl;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.datetime_sub(timestamp without time zone, interval)\n OWNER TO postgres;\n"
format_date <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.format_date(character varying, timestamp without time zone)\n\n-- DROP FUNCTION mimic3.format_date(character varying, timestamp without time zone);\n\nCREATE OR REPLACE FUNCTION mimic3.format_date(\n\tformat_str character varying,\n\tdatetime_val timestamp without time zone)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_CHAR(\n datetime_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.format_date(character varying, timestamp without time zone)\n OWNER TO postgres;\n"
format_datetime <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.format_datetime(character varying, timestamp without time zone)\n\n-- DROP FUNCTION mimic3.format_datetime(character varying, timestamp without time zone);\n\nCREATE OR REPLACE FUNCTION mimic3.format_datetime(\n\tformat_str character varying,\n\tdatetime_val timestamp without time zone)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_CHAR(\n datetime_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.format_datetime(character varying, timestamp without time zone)\n OWNER TO postgres;\n"
generate_array <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.generate_array(integer, integer)\n\n-- DROP FUNCTION mimic3.generate_array(integer, integer);\n\nCREATE OR REPLACE FUNCTION mimic3.generate_array(\n\ti integer,\n\tj integer)\n RETURNS SETOF integer \n LANGUAGE 'sql'\n COST 100\n VOLATILE PARALLEL UNSAFE\n ROWS 1000\n\nAS $BODY$\nSELECT GENERATE_SERIES(i, j)\n$BODY$;\n\nALTER FUNCTION mimic3.generate_array(integer, integer)\n OWNER TO postgres;\n"
parse_date <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.parse_date(character varying, character varying)\n\n-- DROP FUNCTION mimic3.parse_date(character varying, character varying);\n\nCREATE OR REPLACE FUNCTION mimic3.parse_date(\n\tformat_str character varying,\n\tstring_val character varying)\n RETURNS date\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_DATE(\n string_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.parse_date(character varying, character varying)\n OWNER TO postgres;\n"
parse_datetime <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.parse_datetime(character varying, character varying)\n\n-- DROP FUNCTION mimic3.parse_datetime(character varying, character varying);\n\nCREATE OR REPLACE FUNCTION mimic3.parse_datetime(\n\tformat_str character varying,\n\tstring_val character varying)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_TIMESTAMP(\n string_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.parse_datetime(character varying, character varying)\n OWNER TO postgres;\n"
regexp_contains <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.regexp_contains(text, text)\n\n-- DROP FUNCTION mimic3.regexp_contains(text, text);\n\nCREATE OR REPLACE FUNCTION mimic3.regexp_contains(\n\tstr text,\n\tpattern text)\n RETURNS boolean\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN str ~ pattern;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.regexp_contains(text, text)\n OWNER TO postgres;\n"
regexp_extract <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.regexp_extract(text, text)\n\n-- DROP FUNCTION mimic3.regexp_extract(text, text);\n\nCREATE OR REPLACE FUNCTION mimic3.regexp_extract(\n\tstr text,\n\tpattern text)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN substring(str from pattern);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.regexp_extract(text, text)\n OWNER TO postgres;\n"
xname = c("bigquery_format_to_psql", "datetime_add", "datetime_diff",
"datetime_sub", "datetime1", "datetime2", "format_date", "format_datetime",
"generate_array", "parse_date", "parse_datetime", "regexp_contains",
"regexp_extract")
x=c(bigquery_format_to_psql, datetime_add, datetime_diff,
datetime_sub, datetime1, datetime2, format_date, format_datetime,
generate_array, parse_date, parse_datetime,
regexp_contains, regexp_extract)
for (i in 1:length(x)) {
cat(" add",paste0(i,'. ',xname[i],'\n'))
DBI::dbGetQuery(conn = conn$con,
statement = x[i])
}
message('\nadd 26 tables names and columns')
# 26 tables ------------------------------
ADMISSIONS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS ADMISSIONS CASCADE;\nCREATE TABLE ADMISSIONS\n(\n ROW_ID INT NOT NULL,\n SUBJECT_ID INT NOT NULL,\n HADM_ID INT NOT NULL,\n ADMITTIME TIMESTAMP(0) NOT NULL,\n DISCHTIME TIMESTAMP(0) NOT NULL,\n DEATHTIME TIMESTAMP(0),\n ADMISSION_TYPE VARCHAR(50) NOT NULL,\n ADMISSION_LOCATION VARCHAR(50) NOT NULL,\n DISCHARGE_LOCATION VARCHAR(50) NOT NULL,\n INSURANCE VARCHAR(255) NOT NULL,\n LANGUAGE VARCHAR(10),\n RELIGION VARCHAR(50),\n MARITAL_STATUS VARCHAR(50),\n ETHNICITY VARCHAR(200) NOT NULL,\n EDREGTIME TIMESTAMP(0),\n EDOUTTIME TIMESTAMP(0),\n DIAGNOSIS VARCHAR(255),\n HOSPITAL_EXPIRE_FLAG SMALLINT,\n HAS_CHARTEVENTS_DATA SMALLINT NOT NULL,\n CONSTRAINT adm_rowid_pk PRIMARY KEY (ROW_ID),\n CONSTRAINT adm_hadm_unique UNIQUE (HADM_ID)\n) ; "
CALLOUT<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS CALLOUT CASCADE;\nCREATE TABLE CALLOUT\n(\n ROW_ID INT NOT NULL,\n SUBJECT_ID INT NOT NULL,\n HADM_ID INT NOT NULL,\n SUBMIT_WARDID INT,\n SUBMIT_CAREUNIT VARCHAR(15),\n CURR_WARDID INT,\n CURR_CAREUNIT VARCHAR(15),\n CALLOUT_WARDID INT,\n CALLOUT_SERVICE VARCHAR(10) NOT NULL,\n REQUEST_TELE SMALLINT NOT NULL,\n REQUEST_RESP SMALLINT NOT NULL,\n REQUEST_CDIFF SMALLINT NOT NULL,\n REQUEST_MRSA SMALLINT NOT NULL,\n REQUEST_VRE SMALLINT NOT NULL,\n CALLOUT_STATUS VARCHAR(20) NOT NULL,\n CALLOUT_OUTCOME VARCHAR(20) NOT NULL,\n DISCHARGE_WARDID INT,\n ACKNOWLEDGE_STATUS VARCHAR(20) NOT NULL,\n CREATETIME TIMESTAMP(0) NOT NULL,\n UPDATETIME TIMESTAMP(0) NOT NULL,\n ACKNOWLEDGETIME TIMESTAMP(0),\n OUTCOMETIME TIMESTAMP(0) NOT NULL,\n FIRSTRESERVATIONTIME TIMESTAMP(0),\n CURRENTRESERVATIONTIME TIMESTAMP(0),\n CONSTRAINT callout_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
CAREGIVERS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS CAREGIVERS CASCADE;\nCREATE TABLE CAREGIVERS\n(\n ROW_ID INT NOT NULL,\n\tCGID INT NOT NULL,\n\tLABEL VARCHAR(15),\n\tDESCRIPTION VARCHAR(30),\n\tCONSTRAINT cg_rowid_pk PRIMARY KEY (ROW_ID),\n\tCONSTRAINT cg_cgid_unique UNIQUE (CGID)\n) ; "
CHARTEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS CHARTEVENTS CASCADE;\nCREATE TABLE CHARTEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tICUSTAY_ID INT,\n\tITEMID INT,\n\tCHARTTIME TIMESTAMP(0),\n\tSTORETIME TIMESTAMP(0),\n\tCGID INT,\n\tVALUE VARCHAR(255),\n\tVALUENUM DOUBLE PRECISION,\n\tVALUEUOM VARCHAR(50),\n\tWARNING INT,\n\tERROR INT,\n\tRESULTSTATUS VARCHAR(50),\n\tSTOPPED VARCHAR(50),\n\tCONSTRAINT chartevents_rowid_pk PRIMARY KEY (ROW_ID)\n) ;"
CPTEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS CPTEVENTS CASCADE;\nCREATE TABLE CPTEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tCOSTCENTER VARCHAR(10) NOT NULL,\n\tCHARTDATE TIMESTAMP(0),\n\tCPT_CD VARCHAR(10) NOT NULL,\n\tCPT_NUMBER INT,\n\tCPT_SUFFIX VARCHAR(5),\n\tTICKET_ID_SEQ INT,\n\tSECTIONHEADER VARCHAR(50),\n\tSUBSECTIONHEADER VARCHAR(255),\n\tDESCRIPTION VARCHAR(200),\n\tCONSTRAINT cpt_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
DATETIMEEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS DATETIMEEVENTS CASCADE;\nCREATE TABLE DATETIMEEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tICUSTAY_ID INT,\n\tITEMID INT NOT NULL,\n\tCHARTTIME TIMESTAMP(0) NOT NULL,\n\tSTORETIME TIMESTAMP(0) NOT NULL,\n\tCGID INT NOT NULL,\n\tVALUE TIMESTAMP(0),\n\tVALUEUOM VARCHAR(50) NOT NULL,\n\tWARNING SMALLINT,\n\tERROR SMALLINT,\n\tRESULTSTATUS VARCHAR(50),\n\tSTOPPED VARCHAR(50),\n\tCONSTRAINT datetime_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
DIAGNOSES_ICD<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS DIAGNOSES_ICD CASCADE;\nCREATE TABLE DIAGNOSES_ICD\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tSEQ_NUM INT,\n\tICD9_CODE VARCHAR(10),\n\tCONSTRAINT diagnosesicd_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
DRGCODES<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS DRGCODES CASCADE;\nCREATE TABLE DRGCODES\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tDRG_TYPE VARCHAR(20) NOT NULL,\n\tDRG_CODE VARCHAR(20) NOT NULL,\n\tDESCRIPTION VARCHAR(255),\n\tDRG_SEVERITY SMALLINT,\n\tDRG_MORTALITY SMALLINT,\n\tCONSTRAINT drg_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
D_CPT<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS D_CPT CASCADE;\nCREATE TABLE D_CPT\n(\n ROW_ID INT NOT NULL,\n\tCATEGORY SMALLINT NOT NULL,\n\tSECTIONRANGE VARCHAR(100) NOT NULL,\n\tSECTIONHEADER VARCHAR(50) NOT NULL,\n\tSUBSECTIONRANGE VARCHAR(100) NOT NULL,\n\tSUBSECTIONHEADER VARCHAR(255) NOT NULL,\n\tCODESUFFIX VARCHAR(5),\n\tMINCODEINSUBSECTION INT NOT NULL,\n\tMAXCODEINSUBSECTION INT NOT NULL,\n\tCONSTRAINT dcpt_ssrange_unique UNIQUE (SUBSECTIONRANGE),\n\tCONSTRAINT dcpt_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
D_ICD_DIAGNOSES<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS D_ICD_DIAGNOSES CASCADE;\nCREATE TABLE D_ICD_DIAGNOSES\n(\n ROW_ID INT NOT NULL,\n\tICD9_CODE VARCHAR(10) NOT NULL,\n\tSHORT_TITLE VARCHAR(50) NOT NULL,\n\tLONG_TITLE VARCHAR(255) NOT NULL,\n\tCONSTRAINT d_icd_diag_code_unique UNIQUE (ICD9_CODE),\n\tCONSTRAINT d_icd_diag_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
D_ICD_PROCEDURES<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS D_ICD_PROCEDURES CASCADE;\nCREATE TABLE D_ICD_PROCEDURES\n(\n ROW_ID INT NOT NULL,\n\tICD9_CODE VARCHAR(10) NOT NULL,\n\tSHORT_TITLE VARCHAR(50) NOT NULL,\n\tLONG_TITLE VARCHAR(255) NOT NULL,\n\tCONSTRAINT d_icd_proc_code_unique UNIQUE (ICD9_CODE),\n\tCONSTRAINT d_icd_proc_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
D_ITEMS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS D_ITEMS CASCADE;\nCREATE TABLE D_ITEMS\n(\n ROW_ID INT NOT NULL,\n\tITEMID INT NOT NULL,\n\tLABEL VARCHAR(200),\n\tABBREVIATION VARCHAR(100),\n\tDBSOURCE VARCHAR(20),\n\tLINKSTO VARCHAR(50),\n\tCATEGORY VARCHAR(100),\n\tUNITNAME VARCHAR(100),\n\tPARAM_TYPE VARCHAR(30),\n\tCONCEPTID INT,\n\tCONSTRAINT ditems_itemid_unique UNIQUE (ITEMID),\n\tCONSTRAINT ditems_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
D_LABITEMS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nSET client_min_messages TO WARNING;\nDROP TABLE IF EXISTS D_LABITEMS CASCADE;\nCREATE TABLE D_LABITEMS\n(\n ROW_ID INT NOT NULL,\n\tITEMID INT NOT NULL,\n\tLABEL VARCHAR(100) NOT NULL,\n\tFLUID VARCHAR(100) NOT NULL,\n\tCATEGORY VARCHAR(100) NOT NULL,\n\tLOINC_CODE VARCHAR(100),\n\tCONSTRAINT dlabitems_itemid_unique UNIQUE (ITEMID),\n\tCONSTRAINT dlabitems_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
ICUSTAYS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS ICUSTAYS CASCADE;\nCREATE TABLE ICUSTAYS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tICUSTAY_ID INT NOT NULL,\n\tDBSOURCE VARCHAR(20) NOT NULL,\n\tFIRST_CAREUNIT VARCHAR(20) NOT NULL,\n\tLAST_CAREUNIT VARCHAR(20) NOT NULL,\n\tFIRST_WARDID SMALLINT NOT NULL,\n\tLAST_WARDID SMALLINT NOT NULL,\n\tINTIME TIMESTAMP(0) NOT NULL,\n\tOUTTIME TIMESTAMP(0),\n\tLOS DOUBLE PRECISION,\n\tCONSTRAINT icustay_icustayid_unique UNIQUE (ICUSTAY_ID),\n\tCONSTRAINT icustay_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
INPUTEVENTS_CV<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS INPUTEVENTS_CV CASCADE;\nCREATE TABLE INPUTEVENTS_CV\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tICUSTAY_ID INT,\n\tCHARTTIME TIMESTAMP(0),\n\tITEMID INT,\n\tAMOUNT DOUBLE PRECISION,\n\tAMOUNTUOM VARCHAR(30),\n\tRATE DOUBLE PRECISION,\n\tRATEUOM VARCHAR(30),\n\tSTORETIME TIMESTAMP(0),\n\tCGID INT,\n\tORDERID INT,\n\tLINKORDERID INT,\n\tSTOPPED VARCHAR(30),\n\tNEWBOTTLE INT,\n\tORIGINALAMOUNT DOUBLE PRECISION,\n\tORIGINALAMOUNTUOM VARCHAR(30),\n\tORIGINALROUTE VARCHAR(30),\n\tORIGINALRATE DOUBLE PRECISION,\n\tORIGINALRATEUOM VARCHAR(30),\n\tORIGINALSITE VARCHAR(30),\n\tCONSTRAINT inputevents_cv_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
INPUTEVENTS_MV<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS INPUTEVENTS_MV CASCADE;\nCREATE TABLE INPUTEVENTS_MV\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tICUSTAY_ID INT,\n\tSTARTTIME TIMESTAMP(0),\n\tENDTIME TIMESTAMP(0),\n\tITEMID INT,\n\tAMOUNT DOUBLE PRECISION,\n\tAMOUNTUOM VARCHAR(30),\n\tRATE DOUBLE PRECISION,\n\tRATEUOM VARCHAR(30),\n\tSTORETIME TIMESTAMP(0),\n\tCGID INT,\n\tORDERID INT,\n\tLINKORDERID INT,\n\tORDERCATEGORYNAME VARCHAR(100),\n\tSECONDARYORDERCATEGORYNAME VARCHAR(100),\n\tORDERCOMPONENTTYPEDESCRIPTION VARCHAR(200),\n\tORDERCATEGORYDESCRIPTION VARCHAR(50),\n\tPATIENTWEIGHT DOUBLE PRECISION,\n\tTOTALAMOUNT DOUBLE PRECISION,\n\tTOTALAMOUNTUOM VARCHAR(50),\n\tISOPENBAG SMALLINT,\n\tCONTINUEINNEXTDEPT SMALLINT,\n\tCANCELREASON SMALLINT,\n\tSTATUSDESCRIPTION VARCHAR(30),\n\tCOMMENTS_EDITEDBY VARCHAR(30),\n\tCOMMENTS_CANCELEDBY VARCHAR(40),\n\tCOMMENTS_DATE TIMESTAMP(0),\n\tORIGINALAMOUNT DOUBLE PRECISION,\n\tORIGINALRATE DOUBLE PRECISION,\n\tCONSTRAINT inputevents_mv_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
LABEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS LABEVENTS CASCADE;\nCREATE TABLE LABEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tITEMID INT NOT NULL,\n\tCHARTTIME TIMESTAMP(0),\n\tVALUE VARCHAR(200),\n\tVALUENUM DOUBLE PRECISION,\n\tVALUEUOM VARCHAR(20),\n\tFLAG VARCHAR(20),\n\tCONSTRAINT labevents_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
MICROBIOLOGYEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS MICROBIOLOGYEVENTS CASCADE;\nCREATE TABLE MICROBIOLOGYEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tCHARTDATE TIMESTAMP(0),\n\tCHARTTIME TIMESTAMP(0),\n\tSPEC_ITEMID INT,\n\tSPEC_TYPE_DESC VARCHAR(100),\n\tORG_ITEMID INT,\n\tORG_NAME VARCHAR(100),\n\tISOLATE_NUM SMALLINT,\n\tAB_ITEMID INT,\n\tAB_NAME VARCHAR(30),\n\tDILUTION_TEXT VARCHAR(10),\n\tDILUTION_COMPARISON VARCHAR(20),\n\tDILUTION_VALUE DOUBLE PRECISION,\n\tINTERPRETATION VARCHAR(5),\n\tCONSTRAINT micro_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
NOTEEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS NOTEEVENTS CASCADE;\nCREATE TABLE NOTEEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tCHARTDATE TIMESTAMP(0),\n\tCHARTTIME TIMESTAMP(0),\n\tSTORETIME TIMESTAMP(0),\n\tCATEGORY VARCHAR(50),\n\tDESCRIPTION VARCHAR(255),\n\tCGID INT,\n\tISERROR CHAR(1),\n\tTEXT TEXT,\n\tCONSTRAINT noteevents_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
OUTPUTEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS OUTPUTEVENTS CASCADE;\nCREATE TABLE OUTPUTEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tICUSTAY_ID INT,\n\tCHARTTIME TIMESTAMP(0),\n\tITEMID INT,\n\tVALUE DOUBLE PRECISION,\n\tVALUEUOM VARCHAR(30),\n\tSTORETIME TIMESTAMP(0),\n\tCGID INT,\n\tSTOPPED VARCHAR(30),\n\tNEWBOTTLE CHAR(1),\n\tISERROR INT,\n\tCONSTRAINT outputevents_cv_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
PATIENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS PATIENTS CASCADE;\nCREATE TABLE PATIENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tGENDER VARCHAR(5) NOT NULL,\n\tDOB TIMESTAMP(0) NOT NULL,\n\tDOD TIMESTAMP(0),\n\tDOD_HOSP TIMESTAMP(0),\n\tDOD_SSN TIMESTAMP(0),\n\tEXPIRE_FLAG INT NOT NULL,\n\tCONSTRAINT pat_subid_unique UNIQUE (SUBJECT_ID),\n\tCONSTRAINT pat_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
PRESCRIPTIONS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS PRESCRIPTIONS CASCADE;\nCREATE TABLE PRESCRIPTIONS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tICUSTAY_ID INT,\n\tSTARTDATE TIMESTAMP(0),\n\tENDDATE TIMESTAMP(0),\n\tDRUG_TYPE VARCHAR(100) NOT NULL,\n\tDRUG VARCHAR(100) NOT NULL,\n\tDRUG_NAME_POE VARCHAR(100),\n\tDRUG_NAME_GENERIC VARCHAR(100),\n\tFORMULARY_DRUG_CD VARCHAR(120),\n\tGSN VARCHAR(200),\n\tNDC VARCHAR(120),\n\tPROD_STRENGTH VARCHAR(120),\n\tDOSE_VAL_RX VARCHAR(120),\n\tDOSE_UNIT_RX VARCHAR(120),\n\tFORM_VAL_DISP VARCHAR(120),\n\tFORM_UNIT_DISP VARCHAR(120),\n\tROUTE VARCHAR(120),\n\tCONSTRAINT prescription_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
PROCEDUREEVENTS_MV<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS PROCEDUREEVENTS_MV CASCADE;\nCREATE TABLE PROCEDUREEVENTS_MV\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tICUSTAY_ID INT,\n\tSTARTTIME TIMESTAMP(0),\n\tENDTIME TIMESTAMP(0),\n\tITEMID INT,\n\tVALUE DOUBLE PRECISION,\n\tVALUEUOM VARCHAR(30),\n\tLOCATION VARCHAR(30),\n\tLOCATIONCATEGORY VARCHAR(30),\n\tSTORETIME TIMESTAMP(0),\n\tCGID INT,\n\tORDERID INT,\n\tLINKORDERID INT,\n\tORDERCATEGORYNAME VARCHAR(100),\n\tSECONDARYORDERCATEGORYNAME VARCHAR(100),\n\tORDERCATEGORYDESCRIPTION VARCHAR(50),\n\tISOPENBAG SMALLINT,\n\tCONTINUEINNEXTDEPT SMALLINT,\n\tCANCELREASON SMALLINT,\n\tSTATUSDESCRIPTION VARCHAR(30),\n\tCOMMENTS_EDITEDBY VARCHAR(30),\n\tCOMMENTS_CANCELEDBY VARCHAR(30),\n\tCOMMENTS_DATE TIMESTAMP(0),\n\tCONSTRAINT procedureevents_mv_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
PROCEDURES_ICD<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS PROCEDURES_ICD CASCADE;\nCREATE TABLE PROCEDURES_ICD\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tSEQ_NUM INT NOT NULL,\n\tICD9_CODE VARCHAR(10) NOT NULL,\n\tCONSTRAINT proceduresicd_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
SERVICES<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS SERVICES CASCADE;\nCREATE TABLE SERVICES\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tTRANSFERTIME TIMESTAMP(0) NOT NULL,\n\tPREV_SERVICE VARCHAR(20),\n\tCURR_SERVICE VARCHAR(20),\n\tCONSTRAINT services_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
TRANSFERS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS TRANSFERS CASCADE;\nCREATE TABLE TRANSFERS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tICUSTAY_ID INT,\n\tDBSOURCE VARCHAR(20),\n\tEVENTTYPE VARCHAR(20),\n\tPREV_CAREUNIT VARCHAR(20),\n\tCURR_CAREUNIT VARCHAR(20),\n\tPREV_WARDID SMALLINT,\n\tCURR_WARDID SMALLINT,\n\tINTIME TIMESTAMP(0),\n\tOUTTIME TIMESTAMP(0),\n\tLOS DOUBLE PRECISION,\n\tCONSTRAINT transfers_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
xname = c('ADMISSIONS', 'CALLOUT', 'CAREGIVERS', 'CHARTEVENTS', 'CPTEVENTS', 'D_CPT', 'D_ICD_DIAGNOSES', 'D_ICD_PROCEDURES', 'D_ITEMS', 'D_LABITEMS', 'DATETIMEEVENTS', 'DIAGNOSES_ICD', 'DRGCODES', 'ICUSTAYS', 'INPUTEVENTS_CV', 'INPUTEVENTS_MV', 'LABEVENTS', 'MICROBIOLOGYEVENTS', 'NOTEEVENTS', 'OUTPUTEVENTS', 'PATIENTS', 'PRESCRIPTIONS', 'PROCEDUREEVENTS_MV', 'PROCEDURES_ICD', 'SERVICES', 'TRANSFERS')
x=c(ADMISSIONS, CALLOUT, CAREGIVERS, CHARTEVENTS, CPTEVENTS, D_CPT, D_ICD_DIAGNOSES, D_ICD_PROCEDURES, D_ITEMS, D_LABITEMS, DATETIMEEVENTS, DIAGNOSES_ICD, DRGCODES, ICUSTAYS, INPUTEVENTS_CV, INPUTEVENTS_MV, LABEVENTS, MICROBIOLOGYEVENTS, NOTEEVENTS, OUTPUTEVENTS, PATIENTS, PRESCRIPTIONS, PROCEDUREEVENTS_MV, PROCEDURES_ICD, SERVICES, TRANSFERS)
for (i in 1:length(x)) {
cat(" table",paste0(i,'. ',xname[i],'\n'))
DBI::dbGetQuery(conn = conn$con,
statement = x[i])
}
message('\nDONE !!! ')
}
yikeshu0611/mimicR documentation built on Dec. 23, 2021, 7:21 p.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Defines functions addInfo_3
Documented in addInfo_3
#' add functions, schema and tables
#' create 13 funtions, shema mimic3 and 26 tables
#' @param conn connection, if missing, it will be get 'connectiontopsql' from global environment.
#'
#' @return 13 funtions, shema mimic3 and 26 tables in PostgreSQL.
#' @export
#'
#' @examples
#' \donttest{
#' addInfo_3()
#' }
addInfo_3 <- function(user='postgres',password = 'pg'){
con <- DBI::dbConnect(drv = RPostgreSQL::PostgreSQL(),
user = user,
password = password)
conn <- dbplyr::src_dbi(con, auto_disconnect = TRUE)
# create database
datname <- as.data.frame(dplyr::tbl(conn,dbplyr::sql('SELECT datname FROM pg_database')))[,1]
if (! 'mimic' %in% tolower(datname)){
message('\ncreate database mimic')
DBI::dbGetQuery(conn = conn$con,
statement = "CREATE DATABASE mimic;")
}
con <- DBI::dbConnect(drv = RPostgreSQL::PostgreSQL(),
user = user,
password = password,
dbname='mimic')
conn <- dbplyr::src_dbi(con, auto_disconnect = TRUE)
# add schema -------
message('\nadd schema mimic3')
DBI::dbGetQuery(conn = conn$con,
statement = "CREATE SCHEMA IF NOT EXISTS mimic3;")
# 13 functions ------------------------------------------
message('\nadd 13 functions')
bigquery_format_to_psql <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.bigquery_format_to_psql(character varying)\n\n-- DROP FUNCTION mimic3.bigquery_format_to_psql(character varying);\n\nCREATE OR REPLACE FUNCTION mimic3.bigquery_format_to_psql(\n\tformat_str character varying)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN \n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n format_str\n , '%S', 'SS'\n )\n , '%M', 'MI'\n )\n , '%H', 'HH24'\n )\n , '%d', 'dd'\n )\n , '%m', 'mm'\n )\n , '%Y', 'yyyy'\n )\n;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.bigquery_format_to_psql(character varying)\n OWNER TO postgres;\n"
datetime1 <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.datetime(date)\n\n-- DROP FUNCTION mimic3.datetime(date);\n\nCREATE OR REPLACE FUNCTION mimic3.datetime(\n\tdt date)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN CAST(dt AS TIMESTAMP(3));\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.datetime(date)\n OWNER TO postgres;\n"
datetime2 <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.datetime(integer, integer, integer, integer, integer, integer)\n\n-- DROP FUNCTION mimic3.datetime(integer, integer, integer, integer, integer, integer);\n\nCREATE OR REPLACE FUNCTION mimic3.datetime(\n\tyear integer,\n\tmonth integer,\n\tday integer,\n\thour integer,\n\tminute integer,\n\tsecond integer)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_TIMESTAMP(\n TO_CHAR(year, '0000') || TO_CHAR(month, '00') || TO_CHAR(day, '00') || TO_CHAR(hour, '00') || TO_CHAR(minute, '00') || TO_CHAR(second, '00'),\n 'yyyymmddHH24MISS'\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.datetime(integer, integer, integer, integer, integer, integer)\n OWNER TO postgres;\n"
datetime_add <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.datetime_add(timestamp without time zone, interval)\n\n-- DROP FUNCTION mimic3.datetime_add(timestamp without time zone, interval);\n\nCREATE OR REPLACE FUNCTION mimic3.datetime_add(\n\tdatetime_val timestamp without time zone,\n\tintvl interval)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN datetime_val + intvl;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.datetime_add(timestamp without time zone, interval)\n OWNER TO postgres;\n"
datetime_diff <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.datetime_diff(timestamp without time zone, timestamp without time zone, text)\n\n-- DROP FUNCTION mimic3.datetime_diff(timestamp without time zone, timestamp without time zone, text);\n\nCREATE OR REPLACE FUNCTION mimic3.datetime_diff(\n\tendtime timestamp without time zone,\n\tstarttime timestamp without time zone,\n\tdatepart text)\n RETURNS numeric\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN \n EXTRACT(EPOCH FROM endtime - starttime) /\n CASE\n WHEN datepart = 'SECOND' THEN 1.0\n WHEN datepart = 'MINUTE' THEN 60.0\n WHEN datepart = 'HOUR' THEN 3600.0\n WHEN datepart = 'DAY' THEN 24*3600.0\n WHEN datepart = 'YEAR' THEN 365.242*24*3600.0\n ELSE NULL END;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.datetime_diff(timestamp without time zone, timestamp without time zone, text)\n OWNER TO postgres;\n"
datetime_sub <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.datetime_sub(timestamp without time zone, interval)\n\n-- DROP FUNCTION mimic3.datetime_sub(timestamp without time zone, interval);\n\nCREATE OR REPLACE FUNCTION mimic3.datetime_sub(\n\tdatetime_val timestamp without time zone,\n\tintvl interval)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN datetime_val - intvl;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.datetime_sub(timestamp without time zone, interval)\n OWNER TO postgres;\n"
format_date <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.format_date(character varying, timestamp without time zone)\n\n-- DROP FUNCTION mimic3.format_date(character varying, timestamp without time zone);\n\nCREATE OR REPLACE FUNCTION mimic3.format_date(\n\tformat_str character varying,\n\tdatetime_val timestamp without time zone)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_CHAR(\n datetime_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.format_date(character varying, timestamp without time zone)\n OWNER TO postgres;\n"
format_datetime <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.format_datetime(character varying, timestamp without time zone)\n\n-- DROP FUNCTION mimic3.format_datetime(character varying, timestamp without time zone);\n\nCREATE OR REPLACE FUNCTION mimic3.format_datetime(\n\tformat_str character varying,\n\tdatetime_val timestamp without time zone)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_CHAR(\n datetime_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.format_datetime(character varying, timestamp without time zone)\n OWNER TO postgres;\n"
generate_array <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.generate_array(integer, integer)\n\n-- DROP FUNCTION mimic3.generate_array(integer, integer);\n\nCREATE OR REPLACE FUNCTION mimic3.generate_array(\n\ti integer,\n\tj integer)\n RETURNS SETOF integer \n LANGUAGE 'sql'\n COST 100\n VOLATILE PARALLEL UNSAFE\n ROWS 1000\n\nAS $BODY$\nSELECT GENERATE_SERIES(i, j)\n$BODY$;\n\nALTER FUNCTION mimic3.generate_array(integer, integer)\n OWNER TO postgres;\n"
parse_date <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.parse_date(character varying, character varying)\n\n-- DROP FUNCTION mimic3.parse_date(character varying, character varying);\n\nCREATE OR REPLACE FUNCTION mimic3.parse_date(\n\tformat_str character varying,\n\tstring_val character varying)\n RETURNS date\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_DATE(\n string_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.parse_date(character varying, character varying)\n OWNER TO postgres;\n"
parse_datetime <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.parse_datetime(character varying, character varying)\n\n-- DROP FUNCTION mimic3.parse_datetime(character varying, character varying);\n\nCREATE OR REPLACE FUNCTION mimic3.parse_datetime(\n\tformat_str character varying,\n\tstring_val character varying)\n RETURNS timestamp without time zone\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN TO_TIMESTAMP(\n string_val,\n -- use replace to convert BigQuery string format to postgres string format\n -- only handles a few cases since we don't extensively use this function\n BIGQUERY_FORMAT_TO_PSQL(format_str)\n);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.parse_datetime(character varying, character varying)\n OWNER TO postgres;\n"
regexp_contains <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.regexp_contains(text, text)\n\n-- DROP FUNCTION mimic3.regexp_contains(text, text);\n\nCREATE OR REPLACE FUNCTION mimic3.regexp_contains(\n\tstr text,\n\tpattern text)\n RETURNS boolean\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN str ~ pattern;\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.regexp_contains(text, text)\n OWNER TO postgres;\n"
regexp_extract <- "SET client_min_messages TO WARNING;\n-- FUNCTION: mimic3.regexp_extract(text, text)\n\n-- DROP FUNCTION mimic3.regexp_extract(text, text);\n\nCREATE OR REPLACE FUNCTION mimic3.regexp_extract(\n\tstr text,\n\tpattern text)\n RETURNS text\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\nBEGIN\nRETURN substring(str from pattern);\nEND;\n$BODY$;\n\nALTER FUNCTION mimic3.regexp_extract(text, text)\n OWNER TO postgres;\n"
xname = c("bigquery_format_to_psql", "datetime_add", "datetime_diff",
"datetime_sub", "datetime1", "datetime2", "format_date", "format_datetime",
"generate_array", "parse_date", "parse_datetime", "regexp_contains",
"regexp_extract")
x=c(bigquery_format_to_psql, datetime_add, datetime_diff,
datetime_sub, datetime1, datetime2, format_date, format_datetime,
generate_array, parse_date, parse_datetime,
regexp_contains, regexp_extract)
for (i in 1:length(x)) {
cat(" add",paste0(i,'. ',xname[i],'\n'))
DBI::dbGetQuery(conn = conn$con,
statement = x[i])
}
message('\nadd 26 tables names and columns')
# 26 tables ------------------------------
ADMISSIONS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS ADMISSIONS CASCADE;\nCREATE TABLE ADMISSIONS\n(\n ROW_ID INT NOT NULL,\n SUBJECT_ID INT NOT NULL,\n HADM_ID INT NOT NULL,\n ADMITTIME TIMESTAMP(0) NOT NULL,\n DISCHTIME TIMESTAMP(0) NOT NULL,\n DEATHTIME TIMESTAMP(0),\n ADMISSION_TYPE VARCHAR(50) NOT NULL,\n ADMISSION_LOCATION VARCHAR(50) NOT NULL,\n DISCHARGE_LOCATION VARCHAR(50) NOT NULL,\n INSURANCE VARCHAR(255) NOT NULL,\n LANGUAGE VARCHAR(10),\n RELIGION VARCHAR(50),\n MARITAL_STATUS VARCHAR(50),\n ETHNICITY VARCHAR(200) NOT NULL,\n EDREGTIME TIMESTAMP(0),\n EDOUTTIME TIMESTAMP(0),\n DIAGNOSIS VARCHAR(255),\n HOSPITAL_EXPIRE_FLAG SMALLINT,\n HAS_CHARTEVENTS_DATA SMALLINT NOT NULL,\n CONSTRAINT adm_rowid_pk PRIMARY KEY (ROW_ID),\n CONSTRAINT adm_hadm_unique UNIQUE (HADM_ID)\n) ; "
CALLOUT<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS CALLOUT CASCADE;\nCREATE TABLE CALLOUT\n(\n ROW_ID INT NOT NULL,\n SUBJECT_ID INT NOT NULL,\n HADM_ID INT NOT NULL,\n SUBMIT_WARDID INT,\n SUBMIT_CAREUNIT VARCHAR(15),\n CURR_WARDID INT,\n CURR_CAREUNIT VARCHAR(15),\n CALLOUT_WARDID INT,\n CALLOUT_SERVICE VARCHAR(10) NOT NULL,\n REQUEST_TELE SMALLINT NOT NULL,\n REQUEST_RESP SMALLINT NOT NULL,\n REQUEST_CDIFF SMALLINT NOT NULL,\n REQUEST_MRSA SMALLINT NOT NULL,\n REQUEST_VRE SMALLINT NOT NULL,\n CALLOUT_STATUS VARCHAR(20) NOT NULL,\n CALLOUT_OUTCOME VARCHAR(20) NOT NULL,\n DISCHARGE_WARDID INT,\n ACKNOWLEDGE_STATUS VARCHAR(20) NOT NULL,\n CREATETIME TIMESTAMP(0) NOT NULL,\n UPDATETIME TIMESTAMP(0) NOT NULL,\n ACKNOWLEDGETIME TIMESTAMP(0),\n OUTCOMETIME TIMESTAMP(0) NOT NULL,\n FIRSTRESERVATIONTIME TIMESTAMP(0),\n CURRENTRESERVATIONTIME TIMESTAMP(0),\n CONSTRAINT callout_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
CAREGIVERS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS CAREGIVERS CASCADE;\nCREATE TABLE CAREGIVERS\n(\n ROW_ID INT NOT NULL,\n\tCGID INT NOT NULL,\n\tLABEL VARCHAR(15),\n\tDESCRIPTION VARCHAR(30),\n\tCONSTRAINT cg_rowid_pk PRIMARY KEY (ROW_ID),\n\tCONSTRAINT cg_cgid_unique UNIQUE (CGID)\n) ; "
CHARTEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS CHARTEVENTS CASCADE;\nCREATE TABLE CHARTEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tICUSTAY_ID INT,\n\tITEMID INT,\n\tCHARTTIME TIMESTAMP(0),\n\tSTORETIME TIMESTAMP(0),\n\tCGID INT,\n\tVALUE VARCHAR(255),\n\tVALUENUM DOUBLE PRECISION,\n\tVALUEUOM VARCHAR(50),\n\tWARNING INT,\n\tERROR INT,\n\tRESULTSTATUS VARCHAR(50),\n\tSTOPPED VARCHAR(50),\n\tCONSTRAINT chartevents_rowid_pk PRIMARY KEY (ROW_ID)\n) ;"
CPTEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS CPTEVENTS CASCADE;\nCREATE TABLE CPTEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tCOSTCENTER VARCHAR(10) NOT NULL,\n\tCHARTDATE TIMESTAMP(0),\n\tCPT_CD VARCHAR(10) NOT NULL,\n\tCPT_NUMBER INT,\n\tCPT_SUFFIX VARCHAR(5),\n\tTICKET_ID_SEQ INT,\n\tSECTIONHEADER VARCHAR(50),\n\tSUBSECTIONHEADER VARCHAR(255),\n\tDESCRIPTION VARCHAR(200),\n\tCONSTRAINT cpt_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
DATETIMEEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS DATETIMEEVENTS CASCADE;\nCREATE TABLE DATETIMEEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tICUSTAY_ID INT,\n\tITEMID INT NOT NULL,\n\tCHARTTIME TIMESTAMP(0) NOT NULL,\n\tSTORETIME TIMESTAMP(0) NOT NULL,\n\tCGID INT NOT NULL,\n\tVALUE TIMESTAMP(0),\n\tVALUEUOM VARCHAR(50) NOT NULL,\n\tWARNING SMALLINT,\n\tERROR SMALLINT,\n\tRESULTSTATUS VARCHAR(50),\n\tSTOPPED VARCHAR(50),\n\tCONSTRAINT datetime_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
DIAGNOSES_ICD<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS DIAGNOSES_ICD CASCADE;\nCREATE TABLE DIAGNOSES_ICD\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tSEQ_NUM INT,\n\tICD9_CODE VARCHAR(10),\n\tCONSTRAINT diagnosesicd_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
DRGCODES<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS DRGCODES CASCADE;\nCREATE TABLE DRGCODES\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tDRG_TYPE VARCHAR(20) NOT NULL,\n\tDRG_CODE VARCHAR(20) NOT NULL,\n\tDESCRIPTION VARCHAR(255),\n\tDRG_SEVERITY SMALLINT,\n\tDRG_MORTALITY SMALLINT,\n\tCONSTRAINT drg_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
D_CPT<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS D_CPT CASCADE;\nCREATE TABLE D_CPT\n(\n ROW_ID INT NOT NULL,\n\tCATEGORY SMALLINT NOT NULL,\n\tSECTIONRANGE VARCHAR(100) NOT NULL,\n\tSECTIONHEADER VARCHAR(50) NOT NULL,\n\tSUBSECTIONRANGE VARCHAR(100) NOT NULL,\n\tSUBSECTIONHEADER VARCHAR(255) NOT NULL,\n\tCODESUFFIX VARCHAR(5),\n\tMINCODEINSUBSECTION INT NOT NULL,\n\tMAXCODEINSUBSECTION INT NOT NULL,\n\tCONSTRAINT dcpt_ssrange_unique UNIQUE (SUBSECTIONRANGE),\n\tCONSTRAINT dcpt_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
D_ICD_DIAGNOSES<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS D_ICD_DIAGNOSES CASCADE;\nCREATE TABLE D_ICD_DIAGNOSES\n(\n ROW_ID INT NOT NULL,\n\tICD9_CODE VARCHAR(10) NOT NULL,\n\tSHORT_TITLE VARCHAR(50) NOT NULL,\n\tLONG_TITLE VARCHAR(255) NOT NULL,\n\tCONSTRAINT d_icd_diag_code_unique UNIQUE (ICD9_CODE),\n\tCONSTRAINT d_icd_diag_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
D_ICD_PROCEDURES<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS D_ICD_PROCEDURES CASCADE;\nCREATE TABLE D_ICD_PROCEDURES\n(\n ROW_ID INT NOT NULL,\n\tICD9_CODE VARCHAR(10) NOT NULL,\n\tSHORT_TITLE VARCHAR(50) NOT NULL,\n\tLONG_TITLE VARCHAR(255) NOT NULL,\n\tCONSTRAINT d_icd_proc_code_unique UNIQUE (ICD9_CODE),\n\tCONSTRAINT d_icd_proc_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
D_ITEMS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS D_ITEMS CASCADE;\nCREATE TABLE D_ITEMS\n(\n ROW_ID INT NOT NULL,\n\tITEMID INT NOT NULL,\n\tLABEL VARCHAR(200),\n\tABBREVIATION VARCHAR(100),\n\tDBSOURCE VARCHAR(20),\n\tLINKSTO VARCHAR(50),\n\tCATEGORY VARCHAR(100),\n\tUNITNAME VARCHAR(100),\n\tPARAM_TYPE VARCHAR(30),\n\tCONCEPTID INT,\n\tCONSTRAINT ditems_itemid_unique UNIQUE (ITEMID),\n\tCONSTRAINT ditems_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
D_LABITEMS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nSET client_min_messages TO WARNING;\nDROP TABLE IF EXISTS D_LABITEMS CASCADE;\nCREATE TABLE D_LABITEMS\n(\n ROW_ID INT NOT NULL,\n\tITEMID INT NOT NULL,\n\tLABEL VARCHAR(100) NOT NULL,\n\tFLUID VARCHAR(100) NOT NULL,\n\tCATEGORY VARCHAR(100) NOT NULL,\n\tLOINC_CODE VARCHAR(100),\n\tCONSTRAINT dlabitems_itemid_unique UNIQUE (ITEMID),\n\tCONSTRAINT dlabitems_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
ICUSTAYS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS ICUSTAYS CASCADE;\nCREATE TABLE ICUSTAYS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tICUSTAY_ID INT NOT NULL,\n\tDBSOURCE VARCHAR(20) NOT NULL,\n\tFIRST_CAREUNIT VARCHAR(20) NOT NULL,\n\tLAST_CAREUNIT VARCHAR(20) NOT NULL,\n\tFIRST_WARDID SMALLINT NOT NULL,\n\tLAST_WARDID SMALLINT NOT NULL,\n\tINTIME TIMESTAMP(0) NOT NULL,\n\tOUTTIME TIMESTAMP(0),\n\tLOS DOUBLE PRECISION,\n\tCONSTRAINT icustay_icustayid_unique UNIQUE (ICUSTAY_ID),\n\tCONSTRAINT icustay_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
INPUTEVENTS_CV<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS INPUTEVENTS_CV CASCADE;\nCREATE TABLE INPUTEVENTS_CV\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tICUSTAY_ID INT,\n\tCHARTTIME TIMESTAMP(0),\n\tITEMID INT,\n\tAMOUNT DOUBLE PRECISION,\n\tAMOUNTUOM VARCHAR(30),\n\tRATE DOUBLE PRECISION,\n\tRATEUOM VARCHAR(30),\n\tSTORETIME TIMESTAMP(0),\n\tCGID INT,\n\tORDERID INT,\n\tLINKORDERID INT,\n\tSTOPPED VARCHAR(30),\n\tNEWBOTTLE INT,\n\tORIGINALAMOUNT DOUBLE PRECISION,\n\tORIGINALAMOUNTUOM VARCHAR(30),\n\tORIGINALROUTE VARCHAR(30),\n\tORIGINALRATE DOUBLE PRECISION,\n\tORIGINALRATEUOM VARCHAR(30),\n\tORIGINALSITE VARCHAR(30),\n\tCONSTRAINT inputevents_cv_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
INPUTEVENTS_MV<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS INPUTEVENTS_MV CASCADE;\nCREATE TABLE INPUTEVENTS_MV\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tICUSTAY_ID INT,\n\tSTARTTIME TIMESTAMP(0),\n\tENDTIME TIMESTAMP(0),\n\tITEMID INT,\n\tAMOUNT DOUBLE PRECISION,\n\tAMOUNTUOM VARCHAR(30),\n\tRATE DOUBLE PRECISION,\n\tRATEUOM VARCHAR(30),\n\tSTORETIME TIMESTAMP(0),\n\tCGID INT,\n\tORDERID INT,\n\tLINKORDERID INT,\n\tORDERCATEGORYNAME VARCHAR(100),\n\tSECONDARYORDERCATEGORYNAME VARCHAR(100),\n\tORDERCOMPONENTTYPEDESCRIPTION VARCHAR(200),\n\tORDERCATEGORYDESCRIPTION VARCHAR(50),\n\tPATIENTWEIGHT DOUBLE PRECISION,\n\tTOTALAMOUNT DOUBLE PRECISION,\n\tTOTALAMOUNTUOM VARCHAR(50),\n\tISOPENBAG SMALLINT,\n\tCONTINUEINNEXTDEPT SMALLINT,\n\tCANCELREASON SMALLINT,\n\tSTATUSDESCRIPTION VARCHAR(30),\n\tCOMMENTS_EDITEDBY VARCHAR(30),\n\tCOMMENTS_CANCELEDBY VARCHAR(40),\n\tCOMMENTS_DATE TIMESTAMP(0),\n\tORIGINALAMOUNT DOUBLE PRECISION,\n\tORIGINALRATE DOUBLE PRECISION,\n\tCONSTRAINT inputevents_mv_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
LABEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS LABEVENTS CASCADE;\nCREATE TABLE LABEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tITEMID INT NOT NULL,\n\tCHARTTIME TIMESTAMP(0),\n\tVALUE VARCHAR(200),\n\tVALUENUM DOUBLE PRECISION,\n\tVALUEUOM VARCHAR(20),\n\tFLAG VARCHAR(20),\n\tCONSTRAINT labevents_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
MICROBIOLOGYEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS MICROBIOLOGYEVENTS CASCADE;\nCREATE TABLE MICROBIOLOGYEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tCHARTDATE TIMESTAMP(0),\n\tCHARTTIME TIMESTAMP(0),\n\tSPEC_ITEMID INT,\n\tSPEC_TYPE_DESC VARCHAR(100),\n\tORG_ITEMID INT,\n\tORG_NAME VARCHAR(100),\n\tISOLATE_NUM SMALLINT,\n\tAB_ITEMID INT,\n\tAB_NAME VARCHAR(30),\n\tDILUTION_TEXT VARCHAR(10),\n\tDILUTION_COMPARISON VARCHAR(20),\n\tDILUTION_VALUE DOUBLE PRECISION,\n\tINTERPRETATION VARCHAR(5),\n\tCONSTRAINT micro_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
NOTEEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS NOTEEVENTS CASCADE;\nCREATE TABLE NOTEEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tCHARTDATE TIMESTAMP(0),\n\tCHARTTIME TIMESTAMP(0),\n\tSTORETIME TIMESTAMP(0),\n\tCATEGORY VARCHAR(50),\n\tDESCRIPTION VARCHAR(255),\n\tCGID INT,\n\tISERROR CHAR(1),\n\tTEXT TEXT,\n\tCONSTRAINT noteevents_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
OUTPUTEVENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS OUTPUTEVENTS CASCADE;\nCREATE TABLE OUTPUTEVENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT,\n\tICUSTAY_ID INT,\n\tCHARTTIME TIMESTAMP(0),\n\tITEMID INT,\n\tVALUE DOUBLE PRECISION,\n\tVALUEUOM VARCHAR(30),\n\tSTORETIME TIMESTAMP(0),\n\tCGID INT,\n\tSTOPPED VARCHAR(30),\n\tNEWBOTTLE CHAR(1),\n\tISERROR INT,\n\tCONSTRAINT outputevents_cv_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
PATIENTS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS PATIENTS CASCADE;\nCREATE TABLE PATIENTS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tGENDER VARCHAR(5) NOT NULL,\n\tDOB TIMESTAMP(0) NOT NULL,\n\tDOD TIMESTAMP(0),\n\tDOD_HOSP TIMESTAMP(0),\n\tDOD_SSN TIMESTAMP(0),\n\tEXPIRE_FLAG INT NOT NULL,\n\tCONSTRAINT pat_subid_unique UNIQUE (SUBJECT_ID),\n\tCONSTRAINT pat_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
PRESCRIPTIONS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS PRESCRIPTIONS CASCADE;\nCREATE TABLE PRESCRIPTIONS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tICUSTAY_ID INT,\n\tSTARTDATE TIMESTAMP(0),\n\tENDDATE TIMESTAMP(0),\n\tDRUG_TYPE VARCHAR(100) NOT NULL,\n\tDRUG VARCHAR(100) NOT NULL,\n\tDRUG_NAME_POE VARCHAR(100),\n\tDRUG_NAME_GENERIC VARCHAR(100),\n\tFORMULARY_DRUG_CD VARCHAR(120),\n\tGSN VARCHAR(200),\n\tNDC VARCHAR(120),\n\tPROD_STRENGTH VARCHAR(120),\n\tDOSE_VAL_RX VARCHAR(120),\n\tDOSE_UNIT_RX VARCHAR(120),\n\tFORM_VAL_DISP VARCHAR(120),\n\tFORM_UNIT_DISP VARCHAR(120),\n\tROUTE VARCHAR(120),\n\tCONSTRAINT prescription_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
PROCEDUREEVENTS_MV<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS PROCEDUREEVENTS_MV CASCADE;\nCREATE TABLE PROCEDUREEVENTS_MV\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tICUSTAY_ID INT,\n\tSTARTTIME TIMESTAMP(0),\n\tENDTIME TIMESTAMP(0),\n\tITEMID INT,\n\tVALUE DOUBLE PRECISION,\n\tVALUEUOM VARCHAR(30),\n\tLOCATION VARCHAR(30),\n\tLOCATIONCATEGORY VARCHAR(30),\n\tSTORETIME TIMESTAMP(0),\n\tCGID INT,\n\tORDERID INT,\n\tLINKORDERID INT,\n\tORDERCATEGORYNAME VARCHAR(100),\n\tSECONDARYORDERCATEGORYNAME VARCHAR(100),\n\tORDERCATEGORYDESCRIPTION VARCHAR(50),\n\tISOPENBAG SMALLINT,\n\tCONTINUEINNEXTDEPT SMALLINT,\n\tCANCELREASON SMALLINT,\n\tSTATUSDESCRIPTION VARCHAR(30),\n\tCOMMENTS_EDITEDBY VARCHAR(30),\n\tCOMMENTS_CANCELEDBY VARCHAR(30),\n\tCOMMENTS_DATE TIMESTAMP(0),\n\tCONSTRAINT procedureevents_mv_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
PROCEDURES_ICD<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS PROCEDURES_ICD CASCADE;\nCREATE TABLE PROCEDURES_ICD\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tSEQ_NUM INT NOT NULL,\n\tICD9_CODE VARCHAR(10) NOT NULL,\n\tCONSTRAINT proceduresicd_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
SERVICES<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS SERVICES CASCADE;\nCREATE TABLE SERVICES\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tTRANSFERTIME TIMESTAMP(0) NOT NULL,\n\tPREV_SERVICE VARCHAR(20),\n\tCURR_SERVICE VARCHAR(20),\n\tCONSTRAINT services_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
TRANSFERS<- "SET client_min_messages TO WARNING;\nset search_path to mimic3;\nDROP TABLE IF EXISTS TRANSFERS CASCADE;\nCREATE TABLE TRANSFERS\n(\n ROW_ID INT NOT NULL,\n\tSUBJECT_ID INT NOT NULL,\n\tHADM_ID INT NOT NULL,\n\tICUSTAY_ID INT,\n\tDBSOURCE VARCHAR(20),\n\tEVENTTYPE VARCHAR(20),\n\tPREV_CAREUNIT VARCHAR(20),\n\tCURR_CAREUNIT VARCHAR(20),\n\tPREV_WARDID SMALLINT,\n\tCURR_WARDID SMALLINT,\n\tINTIME TIMESTAMP(0),\n\tOUTTIME TIMESTAMP(0),\n\tLOS DOUBLE PRECISION,\n\tCONSTRAINT transfers_rowid_pk PRIMARY KEY (ROW_ID)\n) ; "
xname = c('ADMISSIONS', 'CALLOUT', 'CAREGIVERS', 'CHARTEVENTS', 'CPTEVENTS', 'D_CPT', 'D_ICD_DIAGNOSES', 'D_ICD_PROCEDURES', 'D_ITEMS', 'D_LABITEMS', 'DATETIMEEVENTS', 'DIAGNOSES_ICD', 'DRGCODES', 'ICUSTAYS', 'INPUTEVENTS_CV', 'INPUTEVENTS_MV', 'LABEVENTS', 'MICROBIOLOGYEVENTS', 'NOTEEVENTS', 'OUTPUTEVENTS', 'PATIENTS', 'PRESCRIPTIONS', 'PROCEDUREEVENTS_MV', 'PROCEDURES_ICD', 'SERVICES', 'TRANSFERS')
x=c(ADMISSIONS, CALLOUT, CAREGIVERS, CHARTEVENTS, CPTEVENTS, D_CPT, D_ICD_DIAGNOSES, D_ICD_PROCEDURES, D_ITEMS, D_LABITEMS, DATETIMEEVENTS, DIAGNOSES_ICD, DRGCODES, ICUSTAYS, INPUTEVENTS_CV, INPUTEVENTS_MV, LABEVENTS, MICROBIOLOGYEVENTS, NOTEEVENTS, OUTPUTEVENTS, PATIENTS, PRESCRIPTIONS, PROCEDUREEVENTS_MV, PROCEDURES_ICD, SERVICES, TRANSFERS)
for (i in 1:length(x)) {
cat(" table",paste0(i,'. ',xname[i],'\n'))
DBI::dbGetQuery(conn = conn$con,
statement = x[i])
}
message('\nDONE !!! ')
}
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Embedding an R snippet on your website
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.