= i2b2 Create an Ontology

The procedure to create the tables for a new ontology.

1. Create the ontology table in the Meta Database

[1] Name of the ontology, e.g. 'ONYX'

{{{#!sql

  CREATE TABLE {Ontology Name from [1]}
   (	"C_HLEVEL" INT			NOT NULL, 
	"C_FULLNAME" VARCHAR(700)	NOT NULL, 
	"C_NAME" VARCHAR(2000)		NOT NULL, 
	"C_SYNONYM_CD" CHAR(1)		NOT NULL, 
	"C_VISUALATTRIBUTES" CHAR(3)	NOT NULL, 
	"C_TOTALNUM" INT			NULL, 
	"C_BASECODE" VARCHAR(50)	NULL, 
	"C_METADATAXML" TEXT		NULL, 
	"C_FACTTABLECOLUMN" VARCHAR(50)	NOT NULL, 
	"C_TABLENAME" VARCHAR(50)	NOT NULL, 
	"C_COLUMNNAME" VARCHAR(50)	NOT NULL, 
	"C_COLUMNDATATYPE" VARCHAR(50)	NOT NULL, 
	"C_OPERATOR" VARCHAR(10)	NOT NULL, 
	"C_DIMCODE" VARCHAR(700)	NOT NULL, 
	"C_COMMENT" TEXT			NULL, 
	"C_TOOLTIP" VARCHAR(900)	NULL, 
	"UPDATE_DATE" DATETIME		NOT NULL, 
	"DOWNLOAD_DATE" DATETIME	NULL, 
	"IMPORT_DATE" DATETIME	NULL, 
	"SOURCESYSTEM_CD" VARCHAR(50)	NULL, 
	"VALUETYPE_CD" VARCHAR(50)	NULL
   ) ;

}}}

2. Create a Scheme

A scheme is the prefix for a concept code.  Not exactly sure what it is used for, but hey ho.  You create one like this:

[2] Scheme name, e.g. 'CBO'
[3] Descriptive name for ontology, e.g. 'Onyx Ontology'

{{{#!sql

INSERT INTO SCHEMES(C_KEY, C_NAME, C_DESCRIPTION)
  VALUES('{Scheme name from [2]}:', '{Scheme name from [2]}', '{Descriptive name for ontology from [3]}');

}}}

3. Tell i2b2 about the new ontology

[4] Base path for ontology, e.g. '\onyx\'

{{{#!sql

INSERT INTO TABLE_ACCESS(C_TABLE_CD, C_TABLE_NAME, C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_DIMTABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, C_COMMENT, C_TOOLTIP, C_ENTRY_DATE, C_CHANGE_DATE, C_STATUS_CD, C_PROTECTED_ACCESS, VALUETYPE_CD)
  VALUES('{Ontology Name from [1]}', '{Ontology Name from [1]}', 0, '{Base path for ontology [4]}', '{Descriptive name for ontology [3]}', 'N', 'CA', NULL, NULL, NULL, 'concept_cd', 'concept_dimension', 'concept_path', 'T', 'LIKE', '{Base path for ontology [4]}', NULL, '{Descriptive name for ontology [3]}', NULL, NULL, NULL, 'N', NULL);

}}}