Changes between Version 2 and Version 3 of i2b2 HowTo Create an Ontology

Timestamp:

10/03/13 09:54:35 (12 years ago)

Author:

Richard Bramley

Comment:

--

i2b2 HowTo Create an Ontology

@@ -113 +113 @@
 
 }}}
+
+Or run this script - Edit the parameters first
+
+{{{#!sql
+
+DECLARE @OntologyName VARCHAR(50), -- The name of the ontology, e.g. 'Onyx'
+                @Prefix VARCHAR(10), -- The prefic for the concept code, e.g. 'ONX'
+                @OntologyDescription VARCHAR(100), -- A nice description, e.g. 'BRICCS Onyx Questionnaire'
+                @OntologyBaseNode VARCHAR(50), -- The name of the base node of the tree within i2b2, e.g. 'Onyx'
+                @SourceSystem VARCHAR(50), -- The system from where the data came, e.g. 'BRICCS Onyx'
+                @MetaDatabaseName VARCHAR(50), -- The name of the Meta Database in which to create the Ontology, e.g. 'i2b2_b1_metadata'
+                @DataDatabaseName VARCHAR(50) -- The name of the Data Database in which to create the Ontology, e.g. 'i2b2_b1_data'
+                
+SET @OntologyName = 'Test CiviCRM'
+SET @Prefix = 'TestGenCV'
+SET @OntologyDescription = 'TestGenvasc Study Details from CiviCRM'
+SET @OntologyBaseNode = 'TestCiviCRM'
+SET @SourceSystem = 'TestGenvasc CiviCRM'
+SET @MetaDatabaseName = 'i2b2_b1_metadata'
+SET @DataDatabaseName = 'i2b2_b1_data'
+
+----------------------------------------------------------------
+-------------- T H E   A C T U A L   S C R I P T ---------------
+----------------------------------------------------------------
+
+--
+-- CREATE ONTOLOGY TABLE
+--
+
+DECLARE @TCreateOntologyTable VARCHAR(MAX)
+SET @TCreateOntologyTable = '
+   USE ' + @MetaDatabaseName + '; 
+   
+   CREATE TABLE ' + @OntologyName + '
+   (    "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
+   ) ;'
+   
+EXEC (@TCreateOntologyTable);
+
+--
+-- CREATE SCHEME
+--
+
+DECLARE @TCreateScheme VARCHAR(MAX)
+SET @TCreateScheme = '
+   USE ' + @MetaDatabaseName + '; 
+   
+   INSERT INTO SCHEMES(C_KEY, C_NAME, C_DESCRIPTION)
+   VALUES(''' + @Prefix + ':'', ''' + @Prefix + ''', ''' + @OntologyDescription + ''');'
+   
+EXEC (@TCreateScheme);
+
+--
+-- CREATE TABLE ACCESS
+--
+
+DECLARE @TCreateTableAccess VARCHAR(MAX)
+SET @TCreateTableAccess = '
+   USE ' + @MetaDatabaseName + '; 
+   
+   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(''' + @OntologyName +  ''', ''' + @OntologyName + ''', 0, ''\' + @OntologyBaseNode + '\'', ''' + @OntologyDescription + ''', ''N'', ''CA'', NULL, NULL, NULL, ''concept_cd'', ''concept_dimension'', ''concept_path'', ''T'', ''LIKE'', ''\' + @OntologyBaseNode + '\'', NULL, ''' + @OntologyDescription + ''', NULL, NULL, NULL, ''N'', NULL);';
+   
+EXEC (@TCreateTableAccess);
+
+--
+-- CREATE THE BASE FOR THE ONTOLOGY
+--
+
+DECLARE @OntologyID INT
+
+-- Create the Ontology
+
+INSERT INTO [i2b2ClinDataIntegration].[dbo].[Ontology] ([Name],[Prefix],[SourceSystem])
+VALUES (
+                        @OntologyName
+           ,@Prefix
+           ,@SourceSystem);
+
+SET @OntologyID = SCOPE_IDENTITY();
+
+SELECT @OntologyID;
+
+-- Create the ontology base code
+
+INSERT INTO [i2b2ClinDataIntegration].[dbo].[OntologyCode] ([ParentID],[OntologyID],[Code],[Name],[OntologyNodeTypeCode],[OntologyNodeStatusCode],[Editable],[MetaDataTypeCode],[MetaUnits])
+VALUES (
+                        NULL
+           ,@OntologyID
+           ,1
+           ,@OntologyBaseNode
+           ,'C'
+           ,'A'
+           ,0
+           ,NULL
+           ,NULL
+        );
+
+-- Insert the base code into the Ontology table
+DECLARE @TCreateBaseInOntologyTable VARCHAR(MAX)
+SET @TCreateBaseInOntologyTable = '
+   USE ' + @MetaDatabaseName + '; 
+   
+   INSERT INTO  Onyxv2
+   SELECT [C_HLEVEL]
+      ,[C_FULLNAME]
+      ,[C_NAME]
+      ,[C_SYNONYM_CD]
+      ,[C_VISUALATTRIBUTES]
+      ,[C_TOTALNUM]
+      ,[C_BASECODE]
+      ,[C_METADATAXML]
+      ,[C_FACTTABLECOLUMN]
+      ,[C_TABLENAME]
+      ,[C_COLUMNNAME]
+      ,[C_COLUMNDATATYPE]
+      ,[C_OPERATOR]
+      ,[C_DIMCODE]
+      ,[C_COMMENT]
+      ,[C_TOOLTIP]
+      ,[UPDATE_DATE]
+      ,[DOWNLOAD_DATE]
+      ,[IMPORT_DATE]
+      ,[SOURCESYSTEM_CD]
+      ,[VALUETYPE_CD]
+  FROM [i2b2ClinDataIntegration].[dbo].[i2b2Ontology]
+  WHERE OntologyID = ' + @OntologyID;
+   
+EXEC (@TCreateBaseInOntologyTable);
+
+-- Insert the base code into the Concept Table
+DECLARE @TCreateBaseConceptTable VARCHAR(MAX)
+SET @TCreateBaseConceptTable = '
+   USE ' + @DataDatabaseName + '; 
+   
+INSERT INTO concept_dimension
+SELECT 
+      [concept_path]
+      ,[concept_cd]
+      ,[name_char]
+      ,[concept_blob]
+      ,[update_date]
+      ,[download_date]
+      ,[import_date]
+      ,[sourcesystem_cd]
+      ,[UPLOAD_ID]
+  FROM [i2b2ClinDataIntegration].[dbo].[i2b2OntologyConceptCodes]
+  WHERE OntologyID = ' + @OntologyID
+   
+EXEC (@TCreateBaseConceptTable);
+
+----------------------------------------------------------------
+------- E N D   O F   T H E   A C T U A L   S C R I P T --------
+----------------------------------------------------------------
+
+
+}}}