onto2

-- I2b2 1.5 convention: Holds the SHRINE Ontology Table
CREATE TABLE shrine_ont.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 shrine_ont.SHRINE OWNER TO shrine_ont;
GRANT SELECT, INSERT, UPDATE, DELETE ON shrine_ont.SHRINE TO i2b2metadata;
GRANT SELECT, INSERT, UPDATE, DELETE ON shrine_ont.SHRINE TO shrine_ont;
  
-- I2b2 1.5 convention: Governs access to the SHRINE Ont table
CREATE TABLE shrine_ont.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 shrine_ont.TABLE_ACCESS OWNER TO shrine_ont;
GRANT SELECT, INSERT, UPDATE, DELETE ON shrine_ont.TABLE_ACCESS TO i2b2metadata;
GRANT SELECT, INSERT, UPDATE, DELETE ON shrine_ont.TABLE_ACCESS TO shrine_ont;

-- TODO: not sure how this is actually loaded up
CREATE TABLE shrine_ont.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 shrine_ont.SCHEMES OWNER TO shrine_ont;
GRANT SELECT, INSERT, UPDATE, DELETE ON shrine_ont.SCHEMES TO i2b2metadata;
GRANT SELECT, INSERT, UPDATE, DELETE ON shrine_ont.SCHEMES TO shrine_ont;

-- i2b2metadata.table_access
grant all privileges on all tables in schema shrine_ont to shrine_ont;
grant all privileges on all sequences in schema shrine_ont to shrine_ont;
grant all privileges on all functions in schema shrine_ont to shrine_ont;