Homec4science
Diffusion SHRINE MedCo Fork (patch/1.22.2.4) Browse

shrine-medco/install/i2b2-1.7/i2b2/postgres/skel/ontology_create_tables.sql
308b6e8da08cpatch/1.22.2.4

Last Change
Raw File

ontology_create_tables.sql
View Options

-- modified to fit Postgres 2014-07-21
-- I2b2 1.5 convention: Holds the SHRINE Ontology Table
CREATE TABLE I2B2_DB_SHRINE_ONT_USER.SHRINE
(
C_HLEVEL NUMERIC(22,0),
C_FULLNAME VARCHAR(900),
C_NAME VARCHAR(2000),
C_SYNONYM_CD CHAR(1),
C_VISUALATTRIBUTES CHAR(3),
C_TOTALNUM NUMERIC(22,0),
C_BASECODE VARCHAR(450),
C_METADATAXML TEXT,
C_FACTTABLECOLUMN VARCHAR(50),
C_TABLENAME VARCHAR(50),
C_COLUMNNAME VARCHAR(50),
C_COLUMNDATATYPE VARCHAR(50),
C_OPERATOR VARCHAR(10),
C_DIMCODE VARCHAR(900),
C_COMMENT TEXT,
C_TOOLTIP VARCHAR(900),
UPDATE_DATE DATE,
DOWNLOAD_DATE DATE,
IMPORT_DATE DATE,
SOURCESYSTEM_CD VARCHAR(50),
VALUETYPE_CD VARCHAR(50),
M_APPLIED_PATH VARCHAR(900),
M_EXCLUSION_CD VARCHAR(900)
) ;
ALTER TABLE I2B2_DB_SHRINE_ONT_USER.SHRINE OWNER TO I2B2_DB_SHRINE_ONT_USER;
GRANT SELECT, INSERT, UPDATE, DELETE ON I2B2_DB_SHRINE_ONT_USER.SHRINE TO I2B2_DB_ONT_USER;
GRANT SELECT, INSERT, UPDATE, DELETE ON I2B2_DB_SHRINE_ONT_USER.SHRINE TO I2B2_DB_SHRINE_ONT_USER;
-- I2b2 1.5 convention: Governs access to the SHRINE Ont table
CREATE TABLE I2B2_DB_SHRINE_ONT_USER.TABLE_ACCESS
(
C_TABLE_CD VARCHAR(50),
C_TABLE_NAME VARCHAR(50),
C_PROTECTED_ACCESS CHAR(1),
C_HLEVEL NUMERIC(22,0),
C_FULLNAME VARCHAR(900),
C_NAME VARCHAR(2000),
C_SYNONYM_CD CHAR(1),
C_VISUALATTRIBUTES CHAR(3),
C_TOTALNUM NUMERIC(22,0),
C_BASECODE VARCHAR(450),
C_METADATAXML TEXT,
C_FACTTABLECOLUMN VARCHAR(50),
C_DIMTABLENAME VARCHAR(50),
C_COLUMNNAME VARCHAR(50),
C_COLUMNDATATYPE VARCHAR(50),
C_OPERATOR VARCHAR(10),
C_DIMCODE VARCHAR(900),
C_COMMENT TEXT,
C_TOOLTIP VARCHAR(900),
C_ENTRY_DATE DATE,
C_CHANGE_DATE DATE,
C_STATUS_CD CHAR(1),
VALUETYPE_CD VARCHAR(50)
) ;
ALTER TABLE I2B2_DB_SHRINE_ONT_USER.TABLE_ACCESS OWNER TO I2B2_DB_SHRINE_ONT_USER;
GRANT SELECT, INSERT, UPDATE, DELETE ON I2B2_DB_SHRINE_ONT_USER.TABLE_ACCESS TO I2B2_DB_ONT_USER;
GRANT SELECT, INSERT, UPDATE, DELETE ON I2B2_DB_SHRINE_ONT_USER.TABLE_ACCESS TO I2B2_DB_SHRINE_ONT_USER;
-- TODO: not sure how this is actually loaded up
CREATE TABLE I2B2_DB_SHRINE_ONT_USER.SCHEMES
(
C_KEY VARCHAR(50) NOT NULL,
C_NAME VARCHAR(50) NOT NULL,
C_DESCRIPTION VARCHAR(100),
CONSTRAINT SCHEMES_PK PRIMARY KEY (C_KEY)
)
;
ALTER TABLE I2B2_DB_SHRINE_ONT_USER.SCHEMES OWNER TO I2B2_DB_SHRINE_ONT_USER;
GRANT SELECT, INSERT, UPDATE, DELETE ON I2B2_DB_SHRINE_ONT_USER.SCHEMES TO I2B2_DB_ONT_USER;
GRANT SELECT, INSERT, UPDATE, DELETE ON I2B2_DB_SHRINE_ONT_USER.SCHEMES TO I2B2_DB_SHRINE_ONT_USER;
c4science · Help