ctakes-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From vjapa...@apache.org
Subject svn commit: r1551254 [8/26] - in /ctakes/branches/ytex: ctakes-ytex-res/ ctakes-ytex-res/.settings/ ctakes-ytex-res/src/ ctakes-ytex-res/src/main/ ctakes-ytex-res/src/main/resources/ ctakes-ytex-res/src/main/resources/org/ ctakes-ytex-res/src/main/reso...
Date Mon, 16 Dec 2013 16:30:40 GMT
Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/drop_document.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/drop_document.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/drop_document.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/drop_document.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,32 @@
+-- drop 'operational' data
+-- legacy begin
+drop table if exists document_class;
+drop table if exists anno_word_token;
+drop table if exists anno_base_token;
+drop table if exists anno_num_token;
+drop table if exists anno_umls_concept; 
+drop table if exists anno_source_doc_info;
+drop table if exists anno_umls_concept;
+-- legacy end
+drop table if exists anno_mm_cuiconcept;
+drop table if exists anno_mm_candidate;
+drop table if exists anno_mm_acronym;
+drop table if exists anno_mm_utterance;
+drop table if exists anno_mm_negation;
+drop table if exists fracture_demo;
+drop table if exists anno_drug_mention;
+drop table if exists anno_markable;
+drop table if exists anno_treebank_node;
+drop table if exists anno_link;
+drop table if exists anno_contain;
+drop table if exists anno_num_token;
+drop table if exists anno_token;
+drop table if exists anno_segment;
+drop table if exists anno_ontology_concept;
+drop table if exists anno_named_entity;
+drop table if exists anno_med_event;
+drop table if exists anno_sentence;
+drop table if exists anno_date;
+drop table if exists anno_base;
+drop table if exists document;
+

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/drop_reference.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/drop_reference.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/drop_reference.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/drop_reference.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,7 @@
+-- drop 'reference' data
+drop table if exists hibernate_sequences;
+drop table if exists anno_base_sequence;
+drop table if exists ref_uima_type;
+drop table if exists ref_named_entity_regex;
+drop table if exists ref_segment_regex;
+drop table if exists ref_stopword;
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/drop_view.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/drop_view.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/drop_view.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/drop_view.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,11 @@
+drop table if exists v_document;
+drop table if exists v_annotation;
+drop table if exists v_document_cui_sent;
+drop table if exists v_document_ontoanno;
+
+drop view if exists v_document;
+drop view if exists v_annotation;
+drop view if exists v_document_cui_sent;
+drop view if exists v_document_ontoanno;
+
+

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/fracture_demo.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/fracture_demo.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/fracture_demo.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/uima/fracture_demo.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1 @@
+update fracture_demo set note_text = replace(note_text, '<br/>', CHAR(10));
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/create_indices.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/create_indices.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/create_indices.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/create_indices.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,2 @@
+create index IX_fword on umls_aui_fword (fword);
+create index IX_fstem on umls_aui_fword (fstem);

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/create_tables.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/create_tables.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/create_tables.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/create_tables.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,9 @@
+
+CREATE CACHED TABLE umls_aui_fword (
+	aui varchar(10) not null primary key,
+	fword varchar(70) not null,
+	fstem varchar(70) null,
+	tok_str varchar(250) not null,
+	stem_str varchar(250) null
+);
+

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/create_view.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/create_view.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/create_view.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/create_view.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,8 @@
+CREATE CACHED TABLE v_snomed_fword_lookup (
+  cui char(8) not null,
+  tui char(8) null,
+  fword varchar(70) not null,
+  fstem varchar(70) not null,
+  tok_str varchar(250) not null,
+  stem_str varchar(250) not null
+);

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/drop_tables.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/drop_tables.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/drop_tables.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/drop_tables.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1 @@
+drop table if exists umls_aui_fword;

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/drop_view.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/drop_view.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/drop_view.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/drop_view.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,3 @@
+-- for backwards compatability, drop the view if it's there
+drop view if exists v_snomed_fword_lookup;
+drop table if exists v_snomed_fword_lookup;

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/import_umls.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/import_umls.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/import_umls.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/import_umls.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,38 @@
+-- this is only executed if you don't have umls installed in your db 
+-- load the umls mrconso table from a dump file.
+-- the following was copied directly from the umls load script.
+-- intentionally do not drop MRCONSO - if it exists then we should use it 
+-- instead of overwriting it
+CREATE CACHED TABLE MRCONSO (
+    CUI	char(8) NOT NULL,
+    LAT	char(3) NOT NULL,
+    TS	char(1) NOT NULL,
+    LUI	varchar(10) NOT NULL,
+    STT	varchar(3) NOT NULL,
+    SUI	varchar(10) NOT NULL,
+    ISPREF	char(1) NOT NULL,
+    AUI	varchar(9) NOT NULL PRIMARY KEY,
+    SAUI	varchar(50),
+    SCUI	varchar(50),
+    SDUI	varchar(50),
+    SAB	varchar(20) NOT NULL,
+    TTY	varchar(20) NOT NULL,
+    CODE	varchar(50) NOT NULL,
+    STR	longvarchar NOT NULL,
+    SRL	int  NOT NULL,
+    SUPPRESS	char(1) NOT NULL,
+    CVF	int 
+)
+;
+
+CREATE CACHED TABLE MRSTY (
+    CUI	char(8) NOT NULL,
+    TUI	char(4) NOT NULL,
+    STN	varchar(100) NOT NULL,
+    STY	varchar(50) NOT NULL,
+    ATUI	varchar(11) NOT NULL PRIMARY KEY ,
+    CVF	int
+);
+
+commit
+;
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/index_umls.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/index_umls.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/index_umls.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/index_umls.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,20 @@
+CREATE INDEX X_MRCONSO_CUI ON MRCONSO(CUI);
+
+CREATE INDEX X_MRCONSO_SUI ON MRCONSO(SUI);
+
+CREATE INDEX X_MRCONSO_LUI ON MRCONSO(LUI);
+
+CREATE INDEX X_MRCONSO_CODE ON MRCONSO(CODE);
+
+CREATE INDEX X_MRCONSO_SAB_TTY ON MRCONSO(SAB,TTY);
+
+CREATE INDEX X_MRCONSO_SCUI ON MRCONSO(SCUI);
+
+CREATE INDEX X_MRCONSO_SDUI ON MRCONSO(SDUI);
+
+CREATE INDEX X_MRCONSO_STR ON MRCONSO(STR);
+
+CREATE INDEX X_MRSTY_CUI ON MRSTY(CUI);
+
+CREATE INDEX X_MRSTY_STY ON MRSTY(STY);
+

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/index_view.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/index_view.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/index_view.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/index_view.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,3 @@
+create index idx_fword on v_snomed_fword_lookup (fword);
+create index idx_fstem on v_snomed_fword_lookup (fstem);
+create index idx_cui on v_snomed_fword_lookup (cui);

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/insert_view.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/insert_view.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/insert_view.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/hsql/umls/insert_view.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,33 @@
+insert into v_snomed_fword_lookup (cui, tui, fword, fstem, tok_str, stem_str)
+select mrc.cui, t.tui, c.fword, c.fstem, c.tok_str, c.stem_str
+from umls_aui_fword c
+inner join MRCONSO mrc on c.aui = mrc.aui and mrc.SAB in ( 'SNOMEDCT', 'RXNORM')
+inner join 
+(
+	select cui, min(tui) tui
+	from MRSTY sty
+	where sty.tui in
+	(
+    /* diseasesAndDisordersTuis */
+    'T019', 'T020', 'T037', 'T046', 'T047', 'T048', 'T049', 'T050', 
+      'T190', 'T191', 'T033',
+    /* signAndSymptomTuis */
+    'T184',
+    /* anatomicalSitesTuis */
+    'T017', 'T029', 'T023', 'T030', 'T031', 'T022', 'T025', 'T026',
+        'T018', 'T021', 'T024',
+    /* medicationsAndDrugsTuis */
+     'T116', 'T195', 'T123', 'T122', 'T118', 'T103', 'T120', 'T104',
+        'T200', 'T111', 'T196', 'T126', 'T131', 'T125', 'T129', 'T130',
+        'T197', 'T119', 'T124', 'T114', 'T109', 'T115', 'T121', 'T192',
+        'T110', 'T127',
+	/* proceduresTuis */
+    'T060', 'T065', 'T058', 'T059', 'T063', 'T062', 'T061',
+    /* deviceTuis */
+    'T074', 'T075',
+    /* laboratoryTuis */
+    'T059'
+	)
+	group by cui
+) t on t.cui = mrc.cui
+;

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/abs/create_abstraction.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/abs/create_abstraction.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/abs/create_abstraction.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/abs/create_abstraction.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,115 @@
+CREATE TABLE [ESLD].[abs_studyid](
+	[studyid] [int] NOT NULL,
+	[abstractor] [nvarchar](70) NULL,
+	[no_abdominal_ultrasound] [bit] NOT NULL  default 0,
+	[no_abdominal_ct] [bit] NOT NULL default 0,
+	[no_abdominal_mri] [bit] NOT NULL default 0,
+	[notes] [nvarchar](max) NULL,
+	[lab_afp_ngml] [float] NULL,
+	[lab_afp_date] [datetime] NULL,
+	[lab_afp_not_present] [bit] NOT NULL  default 0,
+	[ammonia_not_present] [bit] NOT NULL  default 0,
+	[ammonia_mmolL] [float] NULL,
+	[ammonia_date] [datetime] NULL,
+	[paracentesis_not_present] [bit] NOT NULL  default 0,
+	[egd_report_not_present] [bit] NOT NULL default 0,
+	[hepatic_enc_not_present] [bit] NOT NULL  default 0,
+	[hepatic_enc_date] [datetime] NULL,
+	[asterixis_not_present] [bit] NOT NULL  default 0,
+	[asterixis_date] [datetime] NULL,
+	[variceal_bleed_not_present] [bit] NOT NULL  default 0,
+	[variceal_bleed_date] [datetime] NULL,
+	[liver_biopsy_not_present] [bit] NOT NULL default 0,
+	CONSTRAINT PK_abs_studyid PRIMARY KEY  
+	(
+		studyid ASC
+	)	
+)
+;
+
+CREATE TABLE [ESLD].[abs_radiology](
+	[ID] [int] IDENTITY(1,1) NOT NULL,
+	[studyid] [int] NOT NULL,
+	[procedure_date] [datetime] NULL,
+	[procedure_type] [nvarchar](20) NOT NULL,
+	[ascites_reported] [bit] not NULL default 0,
+	[ascites_amount] [nvarchar](20) NULL,
+	[liver_masses_reported] [bit] not NULL default 0,
+	[liver_masses_multiple] [bit] not NULL default 0,
+	[liver_masses_count] [smallint] NULL,
+	[liver_mass_dim1] [float] NULL,
+	[liver_mass_dim2] [float] NULL,
+	[liver_mass_dim3] [float] NULL,
+	[liver_mass_arterial_enhancing] [bit] not NULL default 0,
+	[varices_reported] [bit] not NULL default 0,
+	[uid] [int] NULL,
+	CONSTRAINT PK_abs_radiology PRIMARY KEY  
+	(
+		ID ASC
+	),
+	FOREIGN KEY (studyid) references esld.abs_studyid(studyid)
+) 
+;
+
+CREATE TABLE [ESLD].[abs_biopsy](
+	[ID] [int] IDENTITY(1,1) NOT NULL,
+	[fibrosis_stage] [nvarchar](50) NULL,
+	[inflammation_grade] [nvarchar](50) NULL,
+	[cirrhosis_reported] [bit] NOT NULL default 0,
+	[hepatocellular_carcinoma] [bit] NOT NULL default 0,
+	[liver_biopsy_date] [datetime] NULL,
+	[studyid] [int] not null,
+	CONSTRAINT PK_abs_biopsy PRIMARY KEY  
+	(
+		ID ASC
+	),
+	FOREIGN KEY (studyid) references esld.abs_studyid(studyid)
+)
+;
+
+CREATE TABLE [ESLD].[abs_endoscopy](
+	[ID] [int] IDENTITY(1,1) NOT NULL,
+	[studyid] [int] not null,
+	[endoscopy_date] [datetime] NULL,
+	[performed_bc_bleeding] [bit] NOT NULL default 0,
+	[gastritis_reported] [bit] NOT NULL default 0,
+	[peptic_ulcer_reported] [bit] NOT NULL default 0,
+	[portal_gastropathy_reported] [bit] NOT NULL default 0,
+	[varices_reported] [bit] NOT NULL default 0,
+	[varices_active] [bit] NOT NULL default 0,
+	[varices_cherry] [bit] NOT NULL default 0,
+	[varices_pale] [bit] NOT NULL default 0,
+	[varices_banded] [bit] NOT NULL default 0,
+	[varices_esophogeal] [bit] NOT NULL default 0,
+	[varices_gastric] [bit] NOT NULL default 0,
+	CONSTRAINT PK_abs_endoscopy PRIMARY KEY  
+	(
+		ID ASC
+	),
+	FOREIGN KEY (studyid) references esld.abs_studyid(studyid)
+);
+
+
+CREATE TABLE [ESLD].[abs_paracentesis](
+	[ID] [int] IDENTITY(1,1) NOT NULL,
+	[studyid] [int] not NULL,
+	[total_white_cells] [float] NULL,
+	[percent_neutrophils] [float] NULL,
+	[culture] [nvarchar](255) NULL,
+	[date_recorded] [datetime] NULL,
+	CONSTRAINT PK_abs_paracentesis PRIMARY KEY  
+	(
+		ID ASC
+	),
+	FOREIGN KEY (studyid) references esld.abs_studyid(studyid)
+) 
+;
+
+CREATE INDEX IX_abs_endoscopy_studyid ON esld.abs_endoscopy (studyid)
+;
+CREATE INDEX IX_abs_biopsy_studyid ON esld.abs_biopsy (studyid)
+;
+CREATE INDEX IX_abs_radiology_studyid ON esld.abs_radiology (studyid)
+;
+CREATE INDEX IX_abs_paracentesis_studyid ON esld.abs_paracentesis (studyid)
+;

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/abs/create_sp.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/abs/create_sp.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/abs/create_sp.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/abs/create_sp.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,27 @@
+/*
+ * stored procedure called from the access database to create a 
+ * abs_radiology record out of an abs_radiology_review record
+ */
+drop procedure esld.rad_review_to_abs;
+go
+
+create procedure esld.rad_review_to_abs 
+	@studyid int,
+	@doc_uid int,
+	@abs_radiology_id int OUT
+AS
+-- insert the record
+insert into esld.abs_radiology (studyid, uid, uid_reviewed, procedure_type, procedure_date)
+select r.studyid, r.uid, 1 uid_reviewed, t.rad_procedure_name, l.doc_date
+from esld.abs_radiology_review r
+inner join esld.ref_rad_procedure_type t on r.rad_procedure_type_id = t.rad_procedure_type_id
+inner join esld.v_document_current l on l.study_id = r.studyid and l.uid = r.uid and l.document_type_name = 'RADIOLOGY'
+left join esld.abs_radiology ar on r.studyid = ar.studyid and r.uid = ar.uid
+where r.rad_procedure_type_id <> 0
+and ar.id is null
+and r.studyid = @studyid
+and r.[uid] = @doc_uid
+;
+-- return the id of the inserted record.  assume the combo studyid + uid is unique
+set @abs_radiology_id = (select top 1 id from esld.abs_radiology where studyid = @studyid and [uid] = @doc_uid);
+GO

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/abs/drop_abstraction.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/abs/drop_abstraction.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/abs/drop_abstraction.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/abs/drop_abstraction.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,5 @@
+drop table esld.abs_radiology;
+drop table esld.abs_biopsy;
+drop table [ESLD].[abs_endoscopy];
+drop table [ESLD].[abs_paracentesis];
+drop table esld.abs_studyid;
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/create_document.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/create_document.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/create_document.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/create_document.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1 @@
+alter table test.document add copy_of_document_id int;
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/create_view.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/create_view.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/create_view.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/create_view.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,68 @@
+drop view $(db_schema).[v_all_documents]
+;
+GO
+
+create view $(db_schema).[v_all_documents]
+as
+select
+	r.UID, 
+	e.studyid, 
+	(select document_type_id from $(db_schema).ref_document_type where document_type_name = 'PROGRESS_NOTE') document_type_id, 
+	r.[site] site_id,
+	r.notes doc_text
+from ALL_PROGNOTES_V r 
+inner join esld_sample_v e on r.ssn = e.ssn
+-- inner join $(db_schema).adjudication a on a.studyid = e.studyid
+union
+select
+	r.UID, 
+	e.studyid, 
+	(select document_type_id from $(db_schema).ref_document_type where document_type_name = 'RADIOLOGY') document_type_id,
+	'' site_id,
+	r.note doc_text
+from ALL_RADNOTES_V r 
+inner join esld_sample_v e on r.ssn = e.ssn
+-- inner join $(db_schema).adjudication a on a.studyid = e.studyid
+union
+select 
+	r.UID, 
+	e.studyid, 
+	(select document_type_id from $(db_schema).ref_document_type where document_type_name = 'PATHOLOGY') document_type_id,
+	r.[site] site_id,
+	r.note doc_text
+from ALL_PATHNOTES_V r 
+inner join esld_sample_v e on r.ssn = e.ssn
+-- inner join $(db_schema).adjudication a on a.studyid = e.studyid
+;
+GO
+
+
+
+drop view $(db_schema).[V_ESLD_FWORD_LOOKUP];
+go
+
+create view $(db_schema).[V_ESLD_FWORD_LOOKUP]
+as
+select fword, cui, text
+from $(db_schema).umls_ms_2009
+where 
+(
+	tui in 
+	(
+	'T021','T022','T023','T024','T025','T026','T029','T030','T031',
+	'T059','T060','T061',
+	'T019','T020','T037','T046','T047','T048','T049','T050','T190','T191',
+	'T033','T034','T040','T041','T042','T043','T044','T045','T046','T056','T057','T184'
+	)
+	and sourcetype = 'SNOMEDCT'
+) 
+or cui in (
+	'C0079304', /* EGD */
+	'C0521362', /* Gastrointestinal */
+	'C0040405', /* CT */
+	'C0041618', /* ultrasound */ 
+	'C0205054' /* hepatic */
+	)
+or sourcetype = ('ESLD')
+;
+GO
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/insert_reference.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/insert_reference.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/insert_reference.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/insert_reference.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,65 @@
+/* 
+ * additional umls lexical variants / 
+ * additional concepts
+ */
+delete from $(db_schema).umls_ms_2009 where sourcetype = 'ESLD'
+insert into $(db_schema).umls_ms_2009 (  cui, fword, text, code, sourcetype, tui)
+values ('C0994163','MRCP','MRCP','314635004','ESLD', 'T060');
+
+insert into $(db_schema).umls_ms_2009 (  cui, fword, text, code, sourcetype, tui)
+values ('C0439734','RUQ','RUQ','255497008','ESLD','T082');
+
+insert into $(db_schema).umls_ms_2009 (  cui, fword, text, code, sourcetype, tui)
+values ('C0024485','MAGNETIC','MAGNETIC IMAGE','113091000','ESLD','T060');
+
+insert into $(db_schema).umls_ms_2009 (  cui, fword, text, code, sourcetype, tui)
+values ('C0041618','echogram','echogram','C0041618','ESLD','T060');
+
+insert into $(db_schema).umls_ms_2009 (  cui, fword, text, code, sourcetype, tui)
+values ('ESLD_MASS','echogenic','echogenic focus','ESLD_MASS','ESLD','T060');
+
+
+insert into $(db_schema).umls_ms_2009 (  cui, fword, text, code, sourcetype, tui)
+values ('ESLD_MASS','low','low attenuation region','ESLD_MASS','ESLD','T060');
+
+
+insert into $(db_schema).umls_ms_2009 (  cui, fword, text, code, sourcetype, tui)
+values ('ESLD_MASS','hypodense','hypodense area','ESLD_MASS','ESLD','T060');
+
+insert into $(db_schema).umls_ms_2009 (  cui, fword, text, code, sourcetype, tui)
+values ('ESLD_MASS','decreased','decreased attenuation','ESLD_MASS','ESLD','T060');
+
+insert into $(db_schema).umls_ms_2009 (  cui, fword, text, code, sourcetype, tui)
+values ('ESLD_MASS','low-attenuated','low-attenuated area','ESLD_MASS','ESLD','T060');
+
+insert into $(db_schema).umls_ms_2009 (  cui, fword, text, code, sourcetype, tui)
+values ('C0227486','left','left lobe','ESLD_MASS','ESLD','T060');
+
+insert into $(db_schema).umls_ms_2009 (  cui, fword, text, code, sourcetype, tui)
+values ('C0227481','right','right lower lobe','ESLD_MASS','ESLD','T060');
+
+insert into $(db_schema).umls_ms_2009 (  cui, fword, text, code, sourcetype, tui)
+values ('C0227481','right','right lobe','ESLD_MASS','ESLD','T060');
+
+/* 
+ * ref_named_entity_regex
+ */
+insert into $(db_schema).ref_named_entity_regex (regex, coding_scheme, code, context)
+values ('\(US\)','UMLS','C0041618','TITLE')
+;
+
+insert into $(db_schema).ref_named_entity_regex (regex, coding_scheme, code, context)
+values ('\bCT\b','UMLS','C0040405','TITLE')
+;
+
+insert into $(db_schema).ref_named_entity_regex (regex, coding_scheme, code)
+values ('(?i)\bSEE\s+.*#{0,1}+\s*\d+','ESLD','DOCREF')
+;
+
+insert into $(db_schema).ref_named_entity_regex (regex, coding_scheme, code)
+values ('(?i)\bREFER\s+TO\s+.*#{0,1}+\s*\d+','ESLD','DOCREF')
+;
+
+insert into $(db_schema).ref_named_entity_regex (regex, coding_scheme, code)
+values ('(?i)\bfluid\b[^\p{Punct}]*\bliver\b|\bfluid\b[^\p{Punct}]*\babdomen\b|\bfluid\b[^\p{Punct}]*abdominal\b|\bfluid\b[^\p{Punct}]*hepatic\b|abdominal\s+fluid','UMLS','C0401020')
+;
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classifyDocs.bat
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classifyDocs.bat?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classifyDocs.bat (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classifyDocs.bat Mon Dec 16 16:30:30 2013
@@ -0,0 +1,6 @@
+time /T
+call runScriptOnAnalysisBatch.bat classify_radiology.sql %1
+call runScriptOnAnalysisBatch.bat classify_ascites.sql %1
+call runScriptOnAnalysisBatch.bat classify_varices.sql %1
+call runScriptOnAnalysisBatch.bat classify_livermass.sql %1
+time /T
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_ascites.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_ascites.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_ascites.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_ascites.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,63 @@
+-- clean up
+delete from esld.document_class
+where document_id in
+(select document_id from esld.document where analysis_batch='$(analysis_batch)')
+and task = 'ASCITES'
+;
+
+-- filter to only documents classified as radiology reports (depends on RADIOLOGY_IR being run first)
+-- left join on chart reviews
+-- if no matching chart reviews, assume negative ascites as gold standard
+-- count asserted and negated ascites cuis in each document
+-- ascites positive if # assertions > # negations
+insert into esld.document_class (document_id, task, class_auto, class_gold)
+select 
+	r.document_id, 
+	'ASCITES',
+	r.ascites class_auto,
+	coalesce(ar.ascites_reported, 0) class_gold 
+from
+( 
+select
+	r.document_id,
+	r.study_id,
+	r.uid,
+	case
+		when ascites_p > ascites_n then 1
+		else 0
+	end ascites
+from
+	(
+	select
+		d.document_id, 
+		d.study_id,
+		d.uid,
+		coalesce(ascites.ascites_p, 0) ascites_p,
+		coalesce(ascites.ascites_n, 0) ascites_n
+	from esld.v_document d
+	inner join esld.ref_document_type doctype on d.document_type_id = doctype.document_type_id
+	-- only include abdominal radiology documents 
+	inner join esld.document_class s on s.document_id = d.document_id
+	left join
+		(
+		select o.document_id, 
+			sum(case when certainty <> -1 then 1 else 0 end) ascites_p,
+			sum(case when certainty = -1 then 1 else 0 end) ascites_n
+		from esld.v_document_ontoanno o
+		-- limit to terms in the report section and after
+		inner join esld.anno_base segda on segda.document_id = o.document_id
+		inner join esld.anno_segment seg on seg.anno_base_id = segda.anno_base_id
+		where code in ('C0003962', 'C0003964', 'C0401020')
+		and seg.segment_id = 'REPORT'
+		and o.span_begin >= segda.span_begin
+		group by o.document_id
+		) ascites on ascites.document_id = d.document_id
+	where d.analysis_batch = '$(analysis_batch)'
+	-- limit to document previously classified as abdominal radiology reports
+	and s.task = 'RADIOLOGY_TYPE'
+	and (s.class_gold <> 0 or s.class_auto <> 0 )
+	) r
+) r
+left join esld.abs_radiology ar 
+		on r.study_id = ar.studyid 
+		and r.uid = ar.uid 

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_livermass.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_livermass.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_livermass.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_livermass.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,118 @@
+/*
+ * Liver Mass?
+ * structure in right upper abdomen related to liver could be gall bladder filled with echogenic sludge
+ * However, there is an area of decreased attenuation seen at the junction of the anterior segment of the right lobe of the liver and the medial segment of the left lobe of the liver, measuring approximately 1.5 cm in diameter
+ * A rounded, echogenic focus is seen in the left lobe of the liver,
+ * 
+ * Not a liver Mass:
+ * Cirrhotic appearing liver without evidence of any abnormal arterial phase enhancement to suggest the presence of a hepatoma.  
+ * There are no abnormal areas of early arterial enhancement within the liver to suggest focal hepatocellular carcinoma
+ * Small lesion in the liver, possibly a cyst
+ */
+
+-- clean up
+/*
+ * Liver Mass?
+ * structure in right upper abdomen related to liver could be gall bladder filled with echogenic sludge
+ * However, there is an area of decreased attenuation seen at the junction of the anterior segment of the right lobe of the liver and the medial segment of the left lobe of the liver, measuring approximately 1.5 cm in diameter
+ * A rounded, echogenic focus is seen in the left lobe of the liver,
+ * 
+ * Not a liver Mass:
+ * Cirrhotic appearing liver without evidence of any abnormal arterial phase enhancement to suggest the presence of a hepatoma.  
+ * There are no abnormal areas of early arterial enhancement within the liver to suggest focal hepatocellular carcinoma
+ * Small lesion in the liver, possibly a cyst
+ */
+
+-- clean up
+delete from esld.document_class
+where document_id in
+(select document_id from esld.document where analysis_batch='$(analysis_batch)')
+and task = 'LIVER_MASSES'
+;
+
+insert into esld.document_class (document_id, task, class_auto, class_gold)
+select 
+	r.document_id, 
+	'LIVER_MASSES',
+	r.LIVER_MASSES class_auto ,
+	coalesce(ar.LIVER_MASSES_reported, 0) class_gold
+from
+( 
+	select
+		r.document_id,
+		r.study_id,
+		r.uid,
+		case
+			when r.LIVER_MASSES_p > 0 and r.LIVER_MASSES_p > r.LIVER_MASSES_n then 1
+			else 0
+		end LIVER_MASSES
+	from
+	(
+		select
+			d.document_id, 
+			d.study_id,
+			d.uid,
+			coalesce(l.LIVER_MASSES_p, 0)+coalesce(l2.LIVER_MASSES_p, 0) LIVER_MASSES_p,
+			coalesce(l.LIVER_MASSES_n, 0)+coalesce(l2.LIVER_MASSES_n, 0) LIVER_MASSES_n
+		from esld.v_document d
+		inner join esld.document_class c on d.document_id = c.document_id
+		left join 
+		-- single-term liver masses (like hepatoma)
+		(
+			select da.document_id, 
+			sum(case when ne.certainty <> -1 then 1 else 0 end) LIVER_MASSES_p, 
+			sum(case when ne.certainty = -1 then 1 else 0 end) LIVER_MASSES_n
+			FROM ESLD.anno_base AS da 
+			INNER JOIN ESLD.anno_named_entity AS ne ON da.anno_base_id = ne.anno_base_id 
+			INNER JOIN ESLD.anno_ontology_concept AS o ON o.anno_base_id = ne.anno_base_id
+			inner join esld.anno_base sda on sda.document_id = da.document_id
+			inner join esld.anno_segment s 
+				on sda.anno_base_id = s.anno_base_id 
+				and s.segment_id = 'REPORT'
+			where o.code in ('C0240225' /*, 'C0019204', 'C0023903' */)
+			and da.span_begin >= sda.span_begin
+			group by da.document_id
+		) l on l.document_id = d.document_id
+		-- count for multiple-term liver mass (like hepatic lesion)
+		-- look for sentences that have both words
+		left join 
+		(
+			select document_id, sum(LIVER_MASSES_p) LIVER_MASSES_p, sum(LIVER_MASSES_n) LIVER_MASSES_n
+			from
+			(
+				-- select distinct to avoid duplicate counts due to joins
+				select distinct s.document_id, s.anno_base_id,
+					(case when mass.certainty <> -1 then 1 else 0 end) LIVER_MASSES_p, 
+					(case when mass.certainty = -1 then 1 else 0 end) LIVER_MASSES_n
+				from esld.v_annotation s
+				inner join esld.v_document_ontoanno liv
+					on liv.document_id = s.document_id
+					and liv.span_begin >= s.span_begin
+					and liv.span_end <= s.span_end
+				inner join esld.v_document_ontoanno mass
+					on mass.document_id = s.document_id
+					and mass.span_begin >= s.span_begin
+					and mass.span_end <= s.span_end
+				inner join esld.anno_base sda on sda.document_id = s.document_id
+				inner join esld.anno_segment seg
+					on sda.anno_base_id = seg.anno_base_id 
+					and seg.segment_id = 'REPORT'
+				where s.uima_type_id in 
+					(select uima_type_id 
+					from esld.ref_uima_type 
+					where uima_type_name = 'edu.mayo.bmi.uima.core.sentence.type.Sentence')
+				and liv.code in ('C1278929', 'C0023884', 'C0205054' /* hepatic */, 'C0227486' /* left lobe */, 'C0227481' /* right lobe */)
+				and mass.code in ('C0221198', 'C0577559', 'ESLD_MASS')
+				and mass.span_begin >= sda.span_begin
+			) l2 
+			group by document_id
+		) l2 on l2.document_id = d.document_id
+		where d.analysis_batch = '$(analysis_batch)'
+		and c.task = 'RADIOLOGY_TYPE'
+		and (c.class_gold <> 0 or c.class_auto <> 0)
+	) r
+) r
+left join esld.abs_radiology ar 
+		on r.study_id = ar.studyid 
+		and r.uid = ar.uid 
+;
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_radiology.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_radiology.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_radiology.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_radiology.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,290 @@
+
+-- clean up
+delete from esld.document_class
+where document_id in
+(select document_id from esld.document where analysis_batch='$(analysis_batch)')
+and task = 'RADIOLOGY_TYPE'
+;
+
+-- insert the classification for all radiology documents for this analysis batch
+-- set the gold class to empty for now
+insert into esld.document_class (document_id, task, class_auto, class_gold)
+select
+	r.document_id,
+	'RADIOLOGY_TYPE' task,
+	case 
+	-- this document has a reference to another document - throw it out
+		when docref_count > 0 and doc_len < 500 then 0
+		when title_biopsy_count > 0 then 0
+	-- first see if abdomen or liver and procedure in title
+		when title_abds_count > 0 and title_ultrasound_count > 0 then 1
+		when title_abds_count > 0 and title_ct_count > 0 then 3
+		when title_abds_count > 0 and title_mri_count > 0 then 2
+		when title_mrcp_count > 0 then 2
+	-- first see if some abdominal term and procedure in title, and liver or abdomen in body
+		when title_abd_count > 0 and title_ultrasound_count > 0 and abds_count > 0 then 1
+		when title_abd_count > 0 and title_ct_count > 0 and abds_count > 0 then 3
+		when title_abd_count > 0 and title_mri_count > 0 and abds_count > 0 then 2
+	-- then see if abdomen in title and procedure somewhere in body
+	/*
+		when title_abd_count > 0 and mri_count > 0 then 2
+		when title_abd_count > 0 and ct_count > 0 then 3
+		when title_abd_count > 0 and ultrasound_count > 0 then 1
+	*/
+		when mri_sent_count > 0 and abds_count > 0 then 2
+		when ct_sent_count > 0 and abds_count > 0 then 3
+		when us_sent_count > 0 and abds_count > 0 then 1
+		else 0
+	end class_auto,
+	0 class_gold
+from
+(
+	select 	
+		d.document_id, 
+		d.doc_len,
+		coalesce(title_biopsy_count,0) title_biopsy_count,
+		coalesce(title_abd_count,0) title_abd_count,
+		coalesce(title_abds_count,0) title_abds_count,
+		coalesce(title_ultrasound_count,0) title_ultrasound_count,
+		coalesce(title_ct_count,0) title_ct_count,
+		coalesce(title_mri_count,0) title_mri_count,
+		coalesce(title_mrcp_count,0) title_mrcp_count,
+		coalesce(sentence.mri_sent_count, 0) mri_sent_count,
+		coalesce(sentence.ct_sent_count, 0) ct_sent_count,
+		coalesce(sentence.us_sent_count, 0) us_sent_count,
+		coalesce(s.ultrasound_count, 0) ultrasound_count,
+		coalesce(s.mri_count, 0) mri_count,
+		coalesce(s.ct_count, 0) ct_count,
+		coalesce(s.docref_count, 0) docref_count,
+		coalesce(abds.abds_count, 0) abds_count
+	from 
+		(
+			select d.*, len(d.doc_text) doc_len
+			from esld.v_document d
+			where d.analysis_batch = '$(analysis_batch)'
+			and d.copy_of_document_id is null
+			and d.document_type_name = 'RADIOLOGY'
+		) d
+		left join 
+		-- radiology and abdomen terms in title
+		(
+			select document_id, 
+				sum(us) title_ultrasound_count, 
+				sum(mri) title_mri_count,
+				sum(mrcp) title_mrcp_count,
+				sum(ct) title_ct_count,
+				sum(abd) title_abd_count,
+				sum(abds) title_abds_count,
+				sum(biopsy) title_biopsy_count
+			from
+			(
+				select oda.document_id, 
+					case when oa.code in ('C0041618', 'C0554756') then 1 else 0 end us,
+					case when oa.code in ('C0024485', 'C0994163', 'C0243032') then 1 else 0 end mri,
+					case when oa.code in ('C0994163') then 1 else 0 end mrcp,
+					case when oa.code in ('C0040405') then 1 else 0 end ct,
+					case when oa.code in ('C1278929', 'C0023884', 'C0000726', 'C1281594', 'C0017189', 'C1281182', 'C0817096', 'C0449202', 'C1279864', 'C0030797', 'C0581480', 'C0035359', 'C0439734')
+						then 1 else 0 end abd,
+					case when oa.code in ('C0000726', 'C0023884', 'C0230165', 'C0439734')
+						then 1 else 0 end abds,
+					case when oa.code in ('C0005560')
+						then 1 else 0 end biopsy
+				from esld.anno_base oda 
+				inner join esld.anno_ontology_concept oa on oda.anno_base_id = oa.anno_base_id
+				inner join esld.anno_base tda 
+					on oda.document_id = tda.document_id
+					and oda.span_begin >= tda.span_begin 
+					and oda.span_end <= tda.span_end
+				inner join esld.ref_uima_type ut on tda.uima_type_id = ut.uima_type_id
+				where ut.uima_type_name = 'gov.va.vacs.esld.uima.types.DocumentTitle'
+				and oa.code in 
+					( 
+					'C0041618', 'C0554756', /* ultrasound */ 
+					'C0024485', 'C0994163', 'C0243032', /* mri */
+					'C0040405', /* ct */ 
+					'C1278929', 'C0023884', 'C0000726', 'C1281594', 'C0017189', 'C1281182', 'C0817096', 'C0449202', 'C1279864', 'C0030797', 'C0581480', 'C0035359', 'C0439734', /* abdomen */
+					'C0005560' /* biopsy */
+					)
+			) t
+			group by t.document_id
+		) title_rad 
+			on title_rad.document_id = d.document_id 
+		left join 
+		-- radiology and abdomen terms in a sentence
+		(
+			select document_id, 
+				sum(coalesce(us,0)) us_sent_count, 
+				sum(coalesce(mri,0)) mri_sent_count,
+				sum(coalesce(ct,0)) ct_sent_count
+			from
+			(
+				select oda.document_id, 
+					case when oa.code in ('C0041618', 'C0554756') then 1 else 0 end us,
+					case when oa.code in ('C0024485', 'C0994163', 'C0243032') then 1 else 0 end mri,
+					case when oa.code in ('C0040405') then 1 else 0 end ct
+				from 
+				-- radiology annotion
+				esld.anno_base oda 
+				inner join esld.anno_ontology_concept oa on oda.anno_base_id = oa.anno_base_id
+				-- abdomen annotation
+				inner join esld.anno_base oda_abd on oda_abd.document_id = oda.document_id
+				inner join esld.anno_ontology_concept oa_abd on oda_abd.anno_base_id = oa_abd.anno_base_id
+				-- segment annotation
+				inner join esld.anno_base segda on segda.document_id = oda.document_id
+				inner join esld.anno_segment seg on seg.anno_base_id = segda.anno_base_id 
+				-- sentence annotation
+				inner join esld.anno_base s on oda.document_id = s.document_id
+				inner join esld.ref_uima_type ut on s.uima_type_id = ut.uima_type_id
+				where ut.uima_type_name = 'edu.mayo.bmi.uima.core.sentence.type.Sentence'
+				-- get the report section
+				and seg.segment_id = 'REPORT'
+				-- radiology & abdomen annotations in same sentence
+				and oda.span_begin >= s.span_begin 
+				and oda.span_end <= s.span_end
+				and oda_abd.span_begin >= s.span_begin 
+				and oda_abd.span_end <= s.span_end
+				-- sentence within 200 characters of report beginning
+				and s.span_end <= (segda.span_begin + 200)
+				and s.span_begin >= segda.span_begin 
+				and oa.code in 
+					( 
+					'C0041618', 'C0554756', /* ultrasound */ 
+					'C0024485', 'C0994163', 'C0243032', /* mri */
+					'C0040405' /* ct */ 
+					)
+				and oa_abd.code in
+					('C1278929', 'C0023884', 'C0000726', 'C1281594', 'C0017189', 'C1281182', 'C0817096', 'C0449202', 'C1279864', 'C0030797', 'C0581480', 'C0035359', 'C0439734') /* abdomen */
+			) s
+			group by s.document_id
+		) sentence
+			on sentence.document_id = d.document_id 
+		left join 
+		-- radiology term somewher in document
+		(
+			select document_id, 
+				sum(us) ultrasound_count, 
+				sum(mri) mri_count,
+				sum(ct) ct_count,
+				sum(docref) docref_count
+			from
+			(
+				select oda.document_id, 
+					case when oa.code = 'C0041618' then 1 else 0 end us,
+					case when oa.code in ('C0024485', 'C0994163', 'C0243032') then 1 else 0 end mri,
+					case when oa.code in ('C0040405') then 1 else 0 end ct,
+					case when oa.code in ('DOCREF') then 1 else 0 end docref
+				from esld.anno_base oda 
+				inner join esld.anno_ontology_concept oa on oda.anno_base_id = oa.anno_base_id
+				-- segment annotation
+				inner join esld.anno_base segda on segda.document_id = oda.document_id
+				inner join esld.anno_segment seg on seg.anno_base_id = segda.anno_base_id 
+				where oa.code in 
+					( 
+					'C0041618', /* ultrasound */ 
+					'C0024485', 'C0994163', 'C0243032', /* mri */
+					'C0040405', /* ct */ 
+					'DOCREF' /* document reference - see if the document is a stub */
+					)
+				and seg.segment_id = 'REPORT'
+				and oda.span_begin >= segda.span_begin
+				and oda.span_end <= (segda.span_begin + 200)
+			) s
+			group by s.document_id
+		) s on s.document_id = d.document_id 
+		left join
+		(
+			select oda.document_id, 
+				count(*) abds_count
+			from esld.anno_base oda 
+			inner join esld.anno_ontology_concept oa on oda.anno_base_id = oa.anno_base_id
+			-- segment annotation
+			inner join esld.anno_base segda on segda.document_id = oda.document_id
+			inner join esld.anno_segment seg on seg.anno_base_id = segda.anno_base_id 
+			where oa.code in 
+				( 
+				'C0000726', 'C0023884', 'C0230165', 'C0439734', /* abdomen/liver terms */
+				'C0003962', 'C0003964', /* ascites terms */
+				'C0014867' /* varices terms */
+				)
+			and seg.segment_id = 'REPORT'
+			and oda.span_begin >= segda.span_begin
+			group by oda.document_id
+		) abds on abds.document_id = d.document_id 		
+) r
+;
+
+-- update the gold standard classifications
+update esld.document_class
+set class_gold = 
+	(
+	case 
+		when ar.procedure_type = 'Ultrasound' then 1 
+		when ar.procedure_type = 'MRI' then 2
+		when ar.procedure_type = 'CT' then 3
+	end
+	)
+	from esld.document_class c
+	inner join esld.v_document d 
+		on c.document_id = d.document_id
+	inner join esld.abs_radiology ar 
+		on d.study_id = ar.studyid 
+		and d.uid = ar.uid 
+	where c.task = 'RADIOLOGY_TYPE'
+	and d.analysis_batch='$(analysis_batch)'
+;
+
+
+-- delete classifications for documents where the gold standard is 'unclear'
+-- these are radiology documents for which there are multiple potential
+-- matches
+delete esld.document_class
+from esld.document_class
+inner join esld.v_document d 
+	on esld.document_class.document_id = d.document_id
+inner join esld.abs_radiology ar 
+	on d.study_id = ar.studyid 
+	and ar.uid is null
+	and datepart(yyyy, d.doc_date) = datepart(yyyy, ar.procedure_date)
+	and datepart(dy, d.doc_date) = datepart(dy,ar.procedure_date)
+where esld.document_class.task = 'RADIOLOGY_TYPE'
+and d.analysis_batch='$(analysis_batch)'
+;
+
+/*
+ * Even if the document is linked to the chart review, 
+ * there may be multiple abdominal radiology  documents for the given date.
+ * Delete other documents for the date, because they may be abdominal radiology
+ * documents, but were not reviewed (no gold standard for these)
+ */
+delete esld.document_class
+from esld.document_class
+inner join esld.v_document d 
+	on esld.document_class.document_id = d.document_id
+inner join esld.abs_radiology ar 
+	on d.study_id = ar.studyid 
+	and datepart(yyyy, d.doc_date) = datepart(yyyy, ar.procedure_date)
+	and datepart(dy, d.doc_date) = datepart(dy,ar.procedure_date)
+where esld.document_class.task = 'RADIOLOGY_TYPE'
+and d.analysis_batch='$(analysis_batch)'
+and d.uid <> ar.uid
+;
+
+/*
+ * The chart review was only on notes 1 year prior to the 1st abnormal lab/icd-9.
+ * We currently don't have those dates; so just use the earliest note for the study id 
+ * as the cutoff date
+ */
+delete esld.document_class
+from esld.document_class
+inner join esld.v_document d 
+	on esld.document_class.document_id = d.document_id
+inner join
+	(
+	select ar.studyid, min(ar.procedure_date) procedure_date
+	from esld.abs_radiology ar
+	group by ar.studyid
+	) mindate on mindate.studyid = d.study_id
+where d.doc_date < mindate.procedure_date
+and d.analysis_batch='$(analysis_batch)'
+;
+

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_varices.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_varices.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_varices.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/classify_varices.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,103 @@
+-- mesenteric varices C0267791
+-- esophageal varices C0014867
+-- clean up
+delete from esld.document_class
+where document_id in
+(select document_id from esld.document where analysis_batch='$(analysis_batch)')
+and task = 'VARICES'
+;
+
+-- filter to only documents classified as radiology reports (depends on RADIOLOGY_IR being run first)
+-- left join on chart reviews
+-- if no matching chart reviews, assume negative varices as gold standard
+-- count asserted and negated varices cuis in each document
+-- varices positive if # assertions > # negations
+insert into esld.document_class (document_id, task, class_auto, class_gold)
+select 
+	r.document_id, 
+	'VARICES',
+	r.varices class_auto,
+	coalesce(ar.varices_reported, 0) class_gold 
+from
+( 
+select
+	r.document_id,
+	r.study_id,
+	r.uid,
+	case
+		when varices_p > varices_n then 1
+		when mvarices_p > mvarices_n then 1
+		when evarices_p > evarices_n then 1
+		else 0
+	end varices
+from
+	(
+	select
+		d.document_id, 
+		d.study_id,
+		d.uid,
+		coalesce(varices.varices_p, 0) varices_p,
+		coalesce(varices.varices_n, 0) varices_n,
+		coalesce(varices2.mvarices_p, 0) mvarices_p,
+		coalesce(varices2.mvarices_n, 0) mvarices_n,
+		coalesce(varices2.evarices_p, 0) evarices_p,
+		coalesce(varices2.evarices_n, 0) evarices_n
+	from esld.v_document d
+	inner join esld.ref_document_type doctype on d.document_type_id = doctype.document_type_id
+	-- only include abdominal radiology documents 
+	inner join esld.document_class s on s.document_id = d.document_id
+	left join
+		(
+			select document_id, sum(varices_p) varices_p, sum(varices_n) varices_n
+			from
+			(
+				-- do a select distinct because the joins will amplify the counts
+				select distinct o.document_id, o.span_begin,
+					case when o.certainty <> -1 then 1 else 0 end varices_p,
+					case when o.certainty = -1 then 1 else 0 end varices_n
+				from esld.v_document_ontoanno o
+				-- limit to terms in the report section and after
+				inner join esld.anno_base segda on segda.document_id = o.document_id
+				inner join esld.anno_segment seg on seg.anno_base_id = segda.anno_base_id
+				where o.code in ('C0042345')
+				and not exists (
+					-- filter out varices terms contained in more specific varices terms
+					select o2.code 
+					from esld.v_document_ontoanno o2 
+					where o2.document_id = o.document_id
+					and o2.code in ('C0267791', 'C0014867')
+					and o.span_end <= o2.span_end 
+					and o.span_begin >= o2.span_begin
+					)
+				and seg.segment_id = 'REPORT'
+				and o.span_begin >= segda.span_begin
+			) v
+			group by document_id
+		) varices on varices.document_id = d.document_id
+	left join
+		(
+			select o.document_id, 
+				sum(case when code = 'C0267791' and certainty <> -1 then 1 else 0 end) mvarices_p,
+				sum(case when code = 'C0267791' and certainty = -1 then 1 else 0 end) mvarices_n,
+				sum(case when code = 'C0014867' and certainty <> -1 then 1 else 0 end) evarices_p,
+				sum(case when code = 'C0014867' and certainty = -1 then 1 else 0 end) evarices_n
+			from esld.v_document_ontoanno o
+			-- limit to terms in the report section and after
+			inner join esld.anno_base segda on segda.document_id = o.document_id
+			inner join esld.anno_segment seg on seg.anno_base_id = segda.anno_base_id
+			where o.code in ('C0267791', 'C0014867')
+			and seg.segment_id = 'REPORT'
+			and o.span_begin >= segda.span_begin
+			group by o.document_id
+		) varices2 on varices2.document_id = d.document_id
+	where d.analysis_batch = '$(analysis_batch)'
+	-- limit to document previously classified as abdominal radiology reports
+	and s.task = 'RADIOLOGY_TYPE'
+	and (s.class_gold in (2,3) or s.class_auto in (2,3))
+	) r
+) r
+left join esld.abs_radiology ar 
+		on r.study_id = ar.studyid 
+		and r.uid = ar.uid 
+;
+

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/create_view.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/create_view.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/create_view.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/create_view.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,59 @@
+drop view [ESLD].[v_abs_radiology_doclink]
+;
+go
+
+create view [ESLD].[v_abs_radiology_doclink]
+as
+select r.studyid, r.id, d.uid, d.doc_date, d.doc_title, d.doc_text 
+from esld.abs_radiology r
+left join esld.v_document d 
+	on r.studyid = d.study_id
+	and datepart(yyyy, r.procedure_date) = datepart(yyyy, d.doc_date)
+	and datepart(dy, r.procedure_date) = datepart(dy, d.doc_date)
+where d.analysis_batch = '$(analysis_batch)'
+and d.copy_of_document_id is null
+and d.document_type_name = 'RADIOLOGY'
+;
+go
+
+drop view esld.v_abs_endoscopy_doclink;
+go
+
+create view esld.v_abs_endoscopy_doclink
+as
+SELECT r.studyid, r.id, d.uid, d.site_id, d.doc_date, d.doc_title, d.doc_text
+FROM esld.abs_endoscopy r 
+LEFT JOIN esld.v_document d 
+	ON r.studyid = d .study_id 
+	AND datepart(yyyy, r.endoscopy_date) = datepart(yyyy, d .doc_date) 
+	AND datepart(dy, r.endoscopy_date) = datepart(dy, d .doc_date)
+WHERE d .analysis_batch = '$(analysis_batch)' 
+AND d.copy_of_document_id IS NULL
+and d.document_type_name = 'PROGRESS_NOTE';
+go
+
+
+
+drop view esld.v_document_class_review;
+go
+
+create view esld.v_document_class_review
+as
+select dk.document_id, dk.doc_text, c.*
+from esld.[document_class_review] c
+inner join esld.v_document dk 
+	on c.uid = dk.uid 
+	and c.studyid = dk.study_id 
+	and c.site_id = dk.site_id 
+	and c.document_type_id = dk.document_type_id
+where dk.analysis_batch = '$(analysis_batch)'
+;
+go
+
+drop view esld.v_document_current;
+go
+
+create view esld.v_document_current
+as
+select * from esld.v_document where analysis_batch = '$(analysis_batch)'
+;
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/delete_analysis_batch.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/delete_analysis_batch.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/delete_analysis_batch.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/delete_analysis_batch.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,81 @@
+delete from esld.document
+where analysis_batch='$(analysis_batch)'
+;
+/*
+delete from esld.document_class
+where document_id in (select document_id from esld.document where analysis_batch='$(analysis_batch)')
+;
+
+delete from esld.segment_annotation
+where document_annotation_id in 
+	(
+	select document_annotation_id 
+	from esld.document d 
+	inner join esld.document_annotation da on d.document_id = da.document_id 
+	where analysis_batch='$(analysis_batch)'
+	)
+;
+
+delete from esld.umls_concept_annotation
+where ontology_concept_annotation_id in 
+	(
+	select ontology_concept_annotation_id 
+	from esld.document d 
+	inner join esld.document_annotation da on d.document_id = da.document_id 
+	inner join esld.named_entity_annotation ne on ne.document_annotation_id = da.document_annotation_id
+	inner join esld.ontology_concept_annotation o on o.document_annotation_id = da.document_annotation_id
+	where analysis_batch='$(analysis_batch)'
+	)
+;
+
+delete from esld.ontology_concept_annotation
+where ontology_concept_annotation_id in 
+	(
+	select ontology_concept_annotation_id 
+	from esld.document d 
+	inner join esld.document_annotation da on d.document_id = da.document_id 
+	inner join esld.named_entity_annotation ne on ne.document_annotation_id = da.document_annotation_id
+	where analysis_batch='$(analysis_batch)'
+	)
+;
+
+delete from esld.named_entity_annotation
+where document_annotation_id in 
+	(
+	select ne.document_annotation_id 
+	from esld.document d 
+	inner join esld.document_annotation da on d.document_id = da.document_id 
+	inner join esld.named_entity_annotation ne on ne.document_annotation_id = da.document_annotation_id
+	where analysis_batch='$(analysis_batch)'
+	)
+;
+delete from esld.sentence_annotation
+where document_annotation_id in 
+	(
+	select ne.document_annotation_id 
+	from esld.document d 
+	inner join esld.document_annotation da on d.document_id = da.document_id 
+	inner join esld.sentence_annotation ne on ne.document_annotation_id = da.document_annotation_id
+	where analysis_batch='$(analysis_batch)'
+	)
+;
+delete from esld.docdate_annotation
+where document_annotation_id in 
+	(
+	select ne.document_annotation_id 
+	from esld.document d 
+	inner join esld.document_annotation da on d.document_id = da.document_id 
+	inner join esld.docdate_annotation ne on ne.document_annotation_id = da.document_annotation_id
+	where analysis_batch='$(analysis_batch)'
+	)
+;
+delete from esld.document_annotation
+where document_annotation_id in 
+	(
+	select da.document_annotation_id 
+	from esld.document d 
+	inner join esld.document_annotation da on d.document_id = da.document_id 
+	where analysis_batch='$(analysis_batch)'
+	)
+;
+*/

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/insert_radiology_review.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/insert_radiology_review.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/insert_radiology_review.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/insert_radiology_review.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,15 @@
+/*
+ * update the abs_radiology_review table with 'false positives'
+ * manually review these to see if these are indeed false positives
+ */
+insert into esld.abs_radiology_review (uid, studyid, rad_procedure_type_id)
+select d.uid, d.study_id, c.class_auto
+from esld.document_class c
+inner join esld.v_document d on c.document_id = d.document_id
+left join esld.abs_radiology_review r on r.uid = d.uid and r.studyid = d.study_id
+where c.class_gold = 0
+and c.class_auto <> c.class_gold
+and c.task = 'RADIOLOGY_TYPE'
+and d.analysis_batch = '$(analysis_batch)'
+and r.id is null
+;
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/link_documents.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/link_documents.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/link_documents.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/link_documents.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,39 @@
+-- set uid for radiology reviews where the corresponding document can be identified
+
+
+update esld.abs_radiology 
+set esld.abs_radiology.uid = r.uid
+from esld.abs_radiology ar 
+inner join esld.v_document r
+	on r.study_id = ar.studyid 
+	and datepart(yyyy, r.doc_date) = datepart(yyyy, ar.procedure_date)
+	and datepart(dy, r.doc_date) = datepart(dy,ar.procedure_date)
+left join esld.v_document_ontoanno o on r.document_id = o.document_id and o.code = 'DOCREF' and o.coding_scheme = 'ESLD'
+where r.document_type_name = 'RADIOLOGY'
+-- and CHARINDEX ('CHEST', r.doc_title) = 0
+and r.copy_of_document_id is null
+and r.analysis_batch = '$(analysis_batch)'
+and o.code is null
+and ar.uid is null
+and ar.id not in
+(
+	select id 
+	from
+	(
+		select ar.id, ar.studyid, ar.procedure_date, ar.procedure_type, count(r.document_id) doc_count
+		from esld.abs_radiology ar 
+		inner join esld.v_document r
+			on r.study_id = ar.studyid 
+			and datepart(yyyy, r.doc_date) = datepart(yyyy, ar.procedure_date)
+			and datepart(dy, r.doc_date) = datepart(dy,ar.procedure_date)
+		left join esld.v_document_ontoanno o on r.document_id = o.document_id and o.code = 'DOCREF' and o.coding_scheme = 'ESLD'
+		where r.document_type_name = 'RADIOLOGY'
+		-- and CHARINDEX ('CHEST', r.doc_title) = 0
+		and r.copy_of_document_id is null
+		and r.analysis_batch = '$(analysis_batch)'
+		and o.code is null
+		and ar.uid is null
+		group by ar.id, ar.studyid, ar.procedure_date, ar.procedure_type
+	) d 
+	where d.doc_count > 2
+)
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/mark_duplicates.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/mark_duplicates.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/mark_duplicates.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/mark_duplicates.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,51 @@
+-- documents for the same subject with the same date that are identical are marked as duplicates
+-- for each document for the same subject on the same day
+-- compare the sum of sentence lengths of each sentence after the 'Report:' string
+-- the copy_of_document_id links the duplicate to the 'original'
+-- one of the documents will arbitrarily be picked as an original
+-- parameter: analysis_batch
+
+-- :setvar analysis_batch '02-04'
+
+update esld.document
+set copy_of_document_id = null
+where analysis_batch = '$(analysis_batch)'
+;
+
+
+update esld.document
+set esld.document.copy_of_document_id = doc_parent.parent_document_id
+from
+esld.document inner join
+(
+select d1.study_id, min(d1.document_id) parent_document_id, d2.document_id, datepart(yyyy, d1.doc_date) doc_year, datepart(dy, d1.doc_date) doc_day, d1.rep_size
+from
+(
+select d.document_id, d.study_id, d.doc_date, d.document_type_id,  d.analysis_batch, sum(s.span_end - s.span_begin) rep_size
+from esld.v_document d
+inner join esld.anno_base s on d.document_id = s.document_id
+inner join esld.anno_sentence s2 on s2.anno_base_id = s.anno_base_id
+where s.span_begin > charindex('Report:', d.doc_text)
+group by d.document_id, d.study_id, d.doc_date, d.document_type_id,  d.analysis_batch
+) d1
+inner join
+(
+select d.document_id, d.study_id, d.doc_date, d.document_type_id, d.analysis_batch, sum(s.span_end - s.span_begin) rep_size
+from esld.v_document d
+inner join esld.anno_base s on d.document_id = s.document_id
+inner join esld.anno_sentence s2 on s2.anno_base_id = s.anno_base_id
+where s.span_begin > charindex('Report:', d.doc_text)
+group by d.document_id, d.study_id, d.doc_date, d.document_type_id,  d.analysis_batch
+) d2
+	on d1.study_id = d2.study_id 
+	and datepart(yyyy, d1.doc_date) = datepart(yyyy, d2.doc_date)
+	and datepart(dy, d1.doc_date) = datepart(dy, d2.doc_date)
+	and d1.rep_size = d2.rep_size
+	and d1.document_type_id = d2.document_type_id
+	and d1.document_id < d2.document_id
+	and d1.analysis_batch = d2.analysis_batch
+where
+	d1.analysis_batch = '$(analysis_batch)'
+group by d1.study_id, d2.document_id, datepart(yyyy, d1.doc_date), datepart(dy, d1.doc_date), d1.rep_size
+) doc_parent on esld.document.document_id = doc_parent.document_id
+;
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/runPostProcessing.bat
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/runPostProcessing.bat?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/runPostProcessing.bat (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/runPostProcessing.bat Mon Dec 16 16:30:30 2013
@@ -0,0 +1,4 @@
+call runScriptOnAnalysisBatch.bat mark_duplicates.sql 02-04
+call runScriptOnAnalysisBatch.bat score_radiology_ir.sql 02-04
+call runScriptOnAnalysisBatch.bat score_ascites.sql 02-04
+call runScriptOnAnalysisBatch.bat score_varices.sql 02-04
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/runScriptOnAnalysisBatch.bat
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/runScriptOnAnalysisBatch.bat?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/runScriptOnAnalysisBatch.bat (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/runScriptOnAnalysisBatch.bat Mon Dec 16 16:30:30 2013
@@ -0,0 +1,5 @@
+@set DB=VACS_PROGNOTES
+@set SERVER=VHACONSQLR
+@set SCHEMA=ESLD
+
+sqlcmd -S VHACONSQLR -E -d VACS_PROGNOTES -v analysis_batch="%2" -i %1
\ No newline at end of file

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/score_report.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/score_report.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/score_report.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/score_report.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,106 @@
+select *, 2*precision*recall/(precision+recall) fscore
+from
+(
+	select *, cast(tp as numeric(6,2))/(tp + fp) precision, cast(tp as numeric(6,2))/(tp+fn) recall
+	from
+	(
+		select task, sum(tp) tp, sum(fn) fn, sum(fp) fp, sum(tn) tn
+		from
+		(
+		-- 'raw' scores
+			select
+				task, 
+				case
+					when class_auto = class_gold and class_gold <> 0 then 1
+					else 0
+				end tp,
+				case
+					when class_auto <> class_gold and class_gold <> 0 then 1
+					else 0
+				end fn,
+				case
+					when class_auto <> class_gold and class_gold = 0 then 1
+					else 0
+				end fp,
+				case
+					when class_auto = class_gold and class_gold = 0 then 1
+					else 0
+				end tn
+			from esld.document_class s
+			inner join esld.v_document d on s.document_id = d.document_id 
+			inner join esld.abs_studyid s2 on s2.studyid = d.study_id
+			where d.analysis_batch = '$(analysis_batch)'
+			and s2.gold_standard = 1
+		) s 
+		group by task
+		-- a good deal of the 'false positive' radiology classifications are indeed true positives
+		-- for subsequent classification tasks, these 'false positives' cumulate
+		-- calculate adjusted scores without these 'false positives'
+		-- scores for ascites, varices, liver masses with just radiology true positives 
+		union
+		select task, sum(tp) tp, sum(fn) fn, sum(fp) fp, sum(tn) tn
+		from
+		(
+			select
+				s.task + '-RAD FP' task, 
+				case
+					when s.class_auto = s.class_gold and s.class_gold <> 0 then 1
+					else 0
+				end tp,
+				case
+					when s.class_auto <> s.class_gold and s.class_gold <> 0 then 1
+					else 0
+				end fn,
+				case
+					when s.class_auto <> s.class_gold and s.class_gold = 0 then 1
+					else 0
+				end fp,
+				case
+					when s.class_auto = s.class_gold and s.class_gold = 0 then 1
+					else 0
+				end tn
+			from esld.document_class s
+			inner join esld.v_document d on s.document_id = d.document_id 
+			inner join esld.document_class s2 on s2.document_id = d.document_id
+			inner join esld.abs_studyid st on st.studyid = d.study_id
+			where d.analysis_batch = '$(analysis_batch)'
+			and st.gold_standard = 1
+			and s2.task = 'RADIOLOGY_TYPE'
+			and s2.class_gold <> 0 
+			and s.task in ('ASCITES', 'VARICES', 'LIVER_MASSES')
+		) s 
+		group by task
+		union
+		-- scores for radiology with reviewed documents
+		select task, sum(tp) tp, sum(fn) fn, sum(fp) fp, sum(tn) tn
+		from
+		(
+			select
+				'RADIOLOGY_TYPE_REVIEWED' task, 
+				case
+					when s.class_auto = coalesce(r.rad_procedure_type_id, s.class_gold) and coalesce(r.rad_procedure_type_id, s.class_gold) <> 0 then 1
+					else 0
+				end tp,
+				case
+					when s.class_auto <> coalesce(r.rad_procedure_type_id, s.class_gold) and coalesce(r.rad_procedure_type_id, s.class_gold) <> 0 then 1
+					else 0
+				end fn,
+				case
+					when s.class_auto <> coalesce(r.rad_procedure_type_id, s.class_gold) and coalesce(r.rad_procedure_type_id, s.class_gold) = 0 then 1
+					else 0
+				end fp,
+				case
+					when s.class_auto = coalesce(r.rad_procedure_type_id, s.class_gold) and coalesce(r.rad_procedure_type_id, s.class_gold) = 0 then 1
+					else 0
+				end tn
+			from esld.document_class s
+			inner join esld.v_document d on s.document_id = d.document_id 
+			inner join esld.abs_studyid st on st.studyid = d.study_id
+			left join esld.abs_radiology_review r on r.uid = d.uid and r.studyid = d.study_id
+			where d.analysis_batch = '$(analysis_batch)'
+			and st.gold_standard = 1
+			and s.task = 'RADIOLOGY_TYPE'
+		) s 
+		group by task
+	) s
+) s

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/score_report_all.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/score_report_all.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/score_report_all.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/esld/postprocessing/score_report_all.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,100 @@
+select *, 2*precision*recall/(precision+recall) fscore
+from
+(
+	select *, cast(tp as numeric(6,2))/(tp + fp) precision, cast(tp as numeric(6,2))/(tp+fn) recall
+	from
+	(
+		select analysis_batch, task, sum(tp) tp, sum(fn) fn, sum(fp) fp, sum(tn) tn
+		from
+		(
+		-- 'raw' scores
+			select
+				d.analysis_batch,
+				task, 
+				case
+					when class_auto = class_gold and class_gold <> 0 then 1
+					else 0
+				end tp,
+				case
+					when class_auto <> class_gold and class_gold <> 0 then 1
+					else 0
+				end fn,
+				case
+					when class_auto <> class_gold and class_gold = 0 then 1
+					else 0
+				end fp,
+				case
+					when class_auto = class_gold and class_gold = 0 then 1
+					else 0
+				end tn
+			from esld.document_class s
+			inner join esld.document d on s.document_id = d.document_id 
+		) s 
+		group by analysis_batch, task
+		-- a good deal of the 'false positive' radiology classifications are indeed true positives
+		-- for subsequent classification tasks, these 'false positives' cumulate
+		-- calculate adjusted scores without these 'false positives'
+		-- scores for ascites, varices, liver masses with just radiology true positives 
+		union
+		select analysis_batch, task, sum(tp) tp, sum(fn) fn, sum(fp) fp, sum(tn) tn
+		from
+		(
+			select
+				d.analysis_batch,
+				s.task + '-RAD FP' task, 
+				case
+					when s.class_auto = s.class_gold and s.class_gold <> 0 then 1
+					else 0
+				end tp,
+				case
+					when s.class_auto <> s.class_gold and s.class_gold <> 0 then 1
+					else 0
+				end fn,
+				case
+					when s.class_auto <> s.class_gold and s.class_gold = 0 then 1
+					else 0
+				end fp,
+				case
+					when s.class_auto = s.class_gold and s.class_gold = 0 then 1
+					else 0
+				end tn
+			from esld.document_class s
+			inner join esld.document d on s.document_id = d.document_id 
+			inner join esld.document_class s2 on s2.document_id = d.document_id
+			where s2.task = 'RADIOLOGY_TYPE'
+			and s2.class_gold <> 0 
+			and s.task in ('ASCITES', 'VARICES', 'LIVER_MASSES')
+		) s 
+		group by analysis_batch, task
+		union
+		-- scores for radiology with reviewed documents
+		select analysis_batch, task, sum(tp) tp, sum(fn) fn, sum(fp) fp, sum(tn) tn
+		from
+		(
+			select
+				d.analysis_batch,
+				'RADIOLOGY_TYPE_REVIEWED' task, 
+				case
+					when s.class_auto = coalesce(r.rad_procedure_type_id, s.class_gold) and coalesce(r.rad_procedure_type_id, s.class_gold) <> 0 then 1
+					else 0
+				end tp,
+				case
+					when s.class_auto <> coalesce(r.rad_procedure_type_id, s.class_gold) and coalesce(r.rad_procedure_type_id, s.class_gold) <> 0 then 1
+					else 0
+				end fn,
+				case
+					when s.class_auto <> coalesce(r.rad_procedure_type_id, s.class_gold) and coalesce(r.rad_procedure_type_id, s.class_gold) = 0 then 1
+					else 0
+				end fp,
+				case
+					when s.class_auto = coalesce(r.rad_procedure_type_id, s.class_gold) and coalesce(r.rad_procedure_type_id, s.class_gold) = 0 then 1
+					else 0
+				end tn
+			from esld.document_class s
+			inner join esld.v_document d on s.document_id = d.document_id 
+			left join esld.abs_radiology_review r on r.uid = d.uid and r.studyid = d.study_id
+			where s.task = 'RADIOLOGY_TYPE'
+		) s 
+		group by analysis_batch, task
+	) s
+) s

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/kernel/create_tables.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/kernel/create_tables.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/kernel/create_tables.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/kernel/create_tables.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,240 @@
+
+CREATE TABLE  $(db_schema).kernel_eval (
+	kernel_eval_id int identity NOT NULL,
+	corpus_name varchar(50) NOT NULL DEFAULT '' ,
+	experiment varchar(50) not null ,
+	label varchar(50) not NULL default '' ,
+	cv_fold_id int not null default 0 ,
+	param1 float not null default 0,
+	param2 varchar(50) not null default '',
+	PRIMARY KEY (kernel_eval_id)
+);
+create UNIQUE index NK_kernel_eval on  $(db_schema).kernel_eval (corpus_name, experiment, label, cv_fold_id, param1, param2);
+
+create table  $(db_schema).kernel_eval_instance (
+	kernel_eval_instance int not null identity primary key,
+	kernel_eval_id int not null,
+	instance_id1 bigint NOT NULL,
+	instance_id2 bigint NOT NULL,
+	similarity float NOT NULL,
+	foreign key (kernel_eval_id) references $(db_schema).kernel_eval (kernel_eval_id) ON DELETE CASCADE
+);
+create index IX_kernel_eval1 on  $(db_schema).kernel_eval_instance (kernel_eval_id, instance_id1);
+create index IX_kernel_eval2 on   $(db_schema).kernel_eval_instance (kernel_eval_id, instance_id2);
+create UNIQUE index NK_kernel_eval on  $(db_schema).kernel_eval_instance(kernel_eval_id, instance_id1, instance_id2);
+
+create table $(db_schema).classifier_eval (
+	classifier_eval_id int identity not null primary key,
+	name varchar(50) not null,
+	experiment varchar(50) null default '',
+	fold int null,
+	run int null,
+	algorithm varchar(50) null default '',
+	label varchar(50) null default '',
+	options varchar(1000) null default '',
+	model varbinary(max) null,
+	param1 float NULL,
+	param2 varchar(50) NULL
+);
+
+create table $(db_schema).classifier_eval_svm (
+	classifier_eval_id int not null primary key ,
+	cost float DEFAULT '0',
+  	weight varchar(50),
+	degree int DEFAULT '0',
+	gamma float DEFAULT '0',
+	kernel int DEFAULT NULL,
+	supportVectors int default null,
+	vcdim float null,
+	foreign key (classifier_eval_id) references $(db_schema).classifier_eval (classifier_eval_id) ON DELETE CASCADE
+);
+
+create table $(db_schema).classifier_eval_semil (
+	classifier_eval_id int not null primary key,
+	distance varchar(50),
+	degree int not null default 0,
+	gamma float not null default 0,
+	soft_label bit not null default 0,
+	norm_laplace bit not null default 0,
+	mu float not null default 0,
+	lambda float not null default 0,
+	pct_labeled float not null default 0,
+	foreign key (classifier_eval_id) references $(db_schema).classifier_eval (classifier_eval_id) ON DELETE CASCADE
+) ;
+
+create table $(db_schema).classifier_eval_ir (
+	classifier_eval_ir_id int identity not null primary key,
+	classifier_eval_id int not null ,
+	ir_type varchar(5) not null ,
+	ir_class varchar(5) not null ,
+	ir_class_id int null ,
+	tp int not null,
+	tn int not null,
+	fp int not null,
+	fn int not null,
+	ppv float not null default 0,
+	npv float not null default 0,
+	sens float not null default 0,
+	spec float not null default 0,
+	f1 float not null default 0,
+	foreign key (classifier_eval_id) references $(db_schema).classifier_eval (classifier_eval_id) ON DELETE CASCADE
+);
+create unique index NK_classifier_eval_ircls on  $(db_schema).classifier_eval_ir(classifier_eval_id, ir_type, ir_class);
+
+
+create table $(db_schema).classifier_instance_eval (
+	classifier_instance_eval_id int identity not null primary key,
+	classifier_eval_id int not null ,
+	instance_id bigint not null,
+	pred_class_id int not null,
+	target_class_id int null,
+	foreign key (classifier_eval_id) references $(db_schema).classifier_eval (classifier_eval_id) ON DELETE CASCADE
+);
+create unique index nk_result on  $(db_schema).classifier_instance_eval(classifier_eval_id, instance_id);
+
+create table $(db_schema).classifier_instance_eval_prob (
+	classifier_eval_result_prob_id int identity not null primary key,
+	classifier_instance_eval_id int ,
+	class_id int not null,
+	probability float not null,
+	foreign key (classifier_instance_eval_id) references $(db_schema).classifier_instance_eval (classifier_instance_eval_id) ON DELETE CASCADE
+);
+create unique index nk_result_prob on  $(db_schema).classifier_instance_eval_prob(classifier_instance_eval_id, class_id);
+
+create table $(db_schema).cv_fold (
+  cv_fold_id int identity not null primary key,
+  corpus_name varchar(50) not null ,
+  split_name varchar(50) not null default '' ,
+  label varchar(50) not null default '' ,
+  run int not null default 0,
+  fold int not null default 0
+);
+create unique index nk_cv_fold on  $(db_schema).cv_fold (corpus_name, split_name, label, run, fold);
+
+create table $(db_schema).cv_fold_instance (
+  cv_fold_instance_id int identity not null primary key,
+  cv_fold_id int not null,
+  instance_id bigint not null,
+  train bit not null default 0,
+  foreign key (cv_fold_id) references $(db_schema).cv_fold (cv_fold_id) ON DELETE CASCADE
+);
+create unique index nk_cv_fold_instance on $(db_schema).cv_fold_instance (cv_fold_id, instance_id, train);
+
+create table $(db_schema).cv_best_svm (
+  corpus_name varchar(50) NOT NULL,
+  label varchar(50) NOT NULL,
+  experiment varchar(50) NOT NULL DEFAULT '',
+  f1 float DEFAULT NULL,
+  kernel int DEFAULT NULL,
+  cost float DEFAULT NULL,
+  weight varchar(50) DEFAULT NULL,
+  param1 float DEFAULT NULL,
+  param2 varchar(50) DEFAULT NULL,
+  PRIMARY KEY (corpus_name,label,experiment)
+) ;
+
+create table $(db_schema).feature_eval (
+  feature_eval_id int identity not null primary key,
+  corpus_name varchar(50) not null ,
+  featureset_name varchar(50) not null default '' ,
+  label varchar(50) not null default ''  ,
+  cv_fold_id int not null default 0 ,
+  param1 float not null default 0 ,
+  param2 varchar(50) not null default '' ,
+  type varchar(50) not null,
+);
+create unique index nk_feature_eval on $(db_schema).feature_eval(corpus_name, featureset_name, label, cv_fold_id, param1, param2, type);
+create index ix_feature_eval on $(db_schema).feature_eval (corpus_name, cv_fold_id, type);
+
+create table  $(db_schema).feature_rank (
+  feature_rank_id int identity not null primary key,
+  feature_eval_id int not null ,
+  feature_name varchar(50) not null ,
+  evaluation float not null default 0 ,
+  rank int not null default 0,
+  foreign key (feature_eval_id) references $(db_schema).feature_eval (feature_eval_id) ON DELETE CASCADE
+) ;
+create unique index nk_feature_name on  $(db_schema).feature_rank(feature_eval_id, feature_name);
+create index ix_feature_rank  on  $(db_schema).feature_rank(feature_eval_id, rank);
+create index ix_feature_evaluation  on  $(db_schema).feature_rank(feature_eval_id, evaluation);
+
+CREATE TABLE $(db_schema).feature_parchd (
+  feature_parchd_id int identity NOT NULL primary key,
+  par_feature_rank_id int NOT NULL ,
+  chd_feature_rank_id int NOT NULL
+);
+create UNIQUE index NK_feature_parent on $(db_schema).feature_parchd(par_feature_rank_id,chd_feature_rank_id);
+
+CREATE TABLE $(db_schema).tfidf_doclength (
+  tfidf_doclength_id int NOT NULL identity primary key,
+  feature_eval_id int NOT NULL ,
+  instance_id bigint NOT NULL,
+  length int NOT NULL DEFAULT '0',
+  foreign key (feature_eval_id) references $(db_schema).feature_eval (feature_eval_id) ON DELETE CASCADE
+)
+;
+create UNIQUE index nk_instance_id  on $(db_schema).tfidf_doclength(feature_eval_id,instance_id)
+
+create table $(db_schema).hotspot (
+  hotspot_id int identity not null primary key,
+  instance_id int not null ,
+  anno_base_id int not null ,
+  feature_rank_id int not null
+);
+create unique index NK_hotspot on $(db_schema).hotspot (instance_id, anno_base_id, feature_rank_id);
+create index ix_instance_id on $(db_schema).hotspot (instance_id);
+create index ix_anno_base_id on $(db_schema).hotspot (anno_base_id);
+create index ix_feature_rank_id on $(db_schema).hotspot (feature_rank_id);
+
+create table $(db_schema).hotspot_instance (
+    hotspot_instance_id int identity primary key,
+    corpus_name varchar(50) not null,
+    experiment varchar(50) not null default '',
+    label varchar(50) not null default '',
+    instance_id int not null,
+    max_evaluation float not null default 0,
+    min_rank int not null default 0
+);
+create unique index NK_hotspot_instance on $(db_schema).hotspot_instance (corpus_name, experiment, label, instance_id);
+    
+create table $(db_schema).hotspot_sentence (
+    hotspot_sentence_id int identity not null primary key,
+    hotspot_instance_id int not null ,
+    anno_base_id int not null ,
+    evaluation float not null default 0 ,
+    rank int not null default 0 ,
+	foreign key (hotspot_instance_id) references $(db_schema).hotspot_instance (hotspot_instance_id) ON DELETE CASCADE
+) ;
+create unique index NK_hotspot_sentence on $(db_schema).hotspot_sentence (hotspot_instance_id, anno_base_id);
+create index FK_anno_base_id on $(db_schema).hotspot_sentence (anno_base_id);
+create INDEX IX_evaluation on $(db_schema).hotspot_sentence (hotspot_instance_id, evaluation);
+create INDEX IX_rank on $(db_schema).hotspot_sentence (hotspot_instance_id, rank);
+
+create table $(db_schema).corpus_doc (
+	corpus_name varchar(50) not null ,
+	instance_id bigint not null ,
+	doc_text nvarchar(max),
+	doc_group varchar(50) DEFAULT NULL ,
+	primary key (corpus_name, instance_id)
+) ;
+create index IX_doc_group on $(db_schema).corpus_doc(corpus_name, doc_group);
+
+create table $(db_schema).corpus_label (
+	corpus_name varchar(50) not null ,
+	instance_id bigint not null ,
+	label varchar(20) not null default '',
+	class varchar(5) not null default '',
+	primary key (corpus_name, instance_id, label)
+); 
+create index FK_corpus_doc on $(db_schema).corpus_label(corpus_name, instance_id);
+
+go
+
+create view $(db_schema).v_corpus_group_class
+as
+select distinct d.corpus_name, l.label, doc_group, class
+from $(db_schema).corpus_doc d
+inner join $(db_schema).corpus_label l 
+    on d.corpus_name = l.corpus_name 
+    and d.instance_id = l.instance_id
+;

Added: ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/kernel/drop_tables.sql
URL: http://svn.apache.org/viewvc/ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/kernel/drop_tables.sql?rev=1551254&view=auto
==============================================================================
--- ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/kernel/drop_tables.sql (added)
+++ ctakes/branches/ytex/ctakes-ytex/scripts/data/mssql/kernel/drop_tables.sql Mon Dec 16 16:30:30 2013
@@ -0,0 +1,90 @@
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[hotspot_sentence]') AND type in (N'U'))
+	drop TABLE  $(db_schema).hotspot_sentence
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[hotspot_instance]') AND type in (N'U'))
+	DROP TABLE $(db_schema).[hotspot_instance]
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[hotspot]') AND type in (N'U'))
+	drop TABLE  $(db_schema).hotspot
+;
+
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[feature_parchd]') AND type in (N'U'))
+	drop TABLE  $(db_schema).feature_parchd
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[feature_rank]') AND type in (N'U'))
+	drop TABLE  $(db_schema).feature_rank
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[tfidf_doclength]') AND type in (N'U'))
+	drop TABLE  $(db_schema).tfidf_doclength
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[feature_eval]') AND type in (N'U'))
+	drop TABLE  $(db_schema).feature_eval
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[classifier_eval_ir]') AND type in (N'U'))
+DROP TABLE $(db_schema).[classifier_eval_ir]
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[classifier_eval_svm]') AND type in (N'U'))
+DROP TABLE $(db_schema).[classifier_eval_svm]
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[classifier_eval_semil]') AND type in (N'U'))
+DROP TABLE $(db_schema).[classifier_eval_semil]
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[classifier_instance_eval_prob]') AND type in (N'U'))
+DROP TABLE $(db_schema).[classifier_instance_eval_prob]
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[classifier_instance_eval]') AND type in (N'U'))
+DROP TABLE $(db_schema).[classifier_instance_eval]
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[classifier_eval]') AND type in (N'U'))
+DROP TABLE $(db_schema).[classifier_eval]
+;
+
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[cv_fold_instance]') AND type in (N'U'))
+DROP TABLE $(db_schema).[cv_fold_instance]
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[cv_fold]') AND type in (N'U'))
+DROP TABLE $(db_schema).[cv_fold]
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[cv_best_svm]') AND type in (N'U'))
+DROP TABLE $(db_schema).[cv_best_svm]
+;
+
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[kernel_eval_instance]') AND type in (N'U'))
+DROP TABLE $(db_schema).[kernel_eval_instance]
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[kernel_eval]') AND type in (N'U'))
+DROP TABLE $(db_schema).[kernel_eval]
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[v_corpus_group_class]') AND type in (N'V'))
+drop VIEW $(db_schema).[v_corpus_group_class]
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[corpus_label]') AND type in (N'U'))
+DROP TABLE $(db_schema).[corpus_label]
+;
+
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$(db_schema).[corpus_doc]') AND type in (N'U'))
+DROP TABLE $(db_schema).[corpus_doc]
+;
+
+
+
+



Mime
View raw message