db_changes.patch

From a00dd10787880368f2243e709dd219872476fa2a Mon Sep 17 00:00:00 2001
From: Ruslan Forostianov <ruslan@thehyve.nl>
Date: Wed, 25 Mar 2015 16:01:47 +0100
Subject: [PATCH 1/1] Fixes in favour of constraints

---
 data/common/i2b2metadata/i2b2_secure.tsv           |  1 -
 .../functions/i2b2_create_security_for_trial.sql   | 68 ----------------------
 .../tm_cz/functions/i2b2_process_mrna_data.sql     |  2 +-
 3 files changed, 1 insertion(+), 70 deletions(-)

diff --git a/data/common/i2b2metadata/i2b2_secure.tsv b/data/common/i2b2metadata/i2b2_secure.tsv
index dc3a930..e69de29 100644
--- a/data/common/i2b2metadata/i2b2_secure.tsv
+++ b/data/common/i2b2metadata/i2b2_secure.tsv
@@ -1 +0,0 @@
-"0"	"\Public Studies\"	"Public Studies"	"N"	"CA "		"365"		"CONCEPT_CD"	"CONCEPT_DIMENSION"	"CONCEPT_PATH"	"T"	"LIKE"	"\Public Studies\"		"\Public Studies\"		"2012-07-03 10:19:17.625681"	"2012-07-03 10:19:17.625681"	"2012-07-03 10:19:17.625681"							"EXP:PUBLIC"
diff --git a/ddl/postgres/tm_cz/functions/i2b2_create_security_for_trial.sql b/ddl/postgres/tm_cz/functions/i2b2_create_security_for_trial.sql
index 4f1f022..9a1c097 100644
--- a/ddl/postgres/tm_cz/functions/i2b2_create_security_for_trial.sql
+++ b/ddl/postgres/tm_cz/functions/i2b2_create_security_for_trial.sql
@@ -58,74 +58,6 @@ BEGIN
 
 	stepCt := 0;
   
-	begin
-	delete from i2b2demodata.observation_fact
-	where sourcesystem_cd = TrialID
-	  and concept_cd = 'SECURITY';
-	get diagnostics rowCt := ROW_COUNT;	
-	exception
-	when others then
-		errorNumber := SQLSTATE;
-		errorMessage := SQLERRM;
-		--Handle errors.
-		select tm_cz.cz_error_handler (jobID, procedureName, errorNumber, errorMessage) into rtnCd;
-		--End Proc
-		select tm_cz.cz_end_audit (jobID, 'FAIL') into rtnCd;
-		return -16;
-	end;
-	stepCt := stepCt + 1;
-	select tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Delete security records for trial from I2B2DEMODATA observation_fact',rowCt,stepCt,'Done') into rtnCd;
-
-	begin
-	insert into i2b2demodata.observation_fact
-    (encounter_num
-        ,patient_num
-	,concept_cd
-        ,start_date
-	,provider_id
-	,modifier_cd
-	,valtype_cd
-	,tval_char
-	,valueflag_cd
-	,location_cd
-	,update_date
-	,download_date
-	,import_date
-	,sourcesystem_cd
-	,instance_num
-	)
-	select patient_num
-                  ,patient_num
-		  ,'SECURITY'
-                  ,current_timestamp
-		  ,'@'
-		  ,TrialId
-		  ,'T'
-		  ,case when securedStudy = 'N' then 'EXP:PUBLIC' else 'EXP:' || trialID end
-		  ,'@'
-		  ,'@'
-		  ,current_timestamp
-		  ,current_timestamp
-		  ,current_timestamp
-		  --,sourcesystem_cd
-		  ,TrialId
-		  ,1
-	from i2b2demodata.patient_dimension
-	where sourcesystem_cd like TrialID || ':%';
-	get diagnostics rowCt := ROW_COUNT;	
-	exception
-	when others then
-		errorNumber := SQLSTATE;
-		errorMessage := SQLERRM;
-		--Handle errors.
-		select tm_cz.cz_error_handler (jobID, procedureName, errorNumber, errorMessage) into rtnCd;
-		--End Proc
-		select tm_cz.cz_end_audit (jobID, 'FAIL') into rtnCd;
-		return -16;	
-	end;
-	stepCt := stepCt + 1;
-	select tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert security records for trial from I2B2DEMODATA observation_fact',rowCt,stepCt,'Done') into rtnCd;
-	
 	--	insert patients to patient_trial table
 	
 	begin
diff --git a/ddl/postgres/tm_cz/functions/i2b2_process_mrna_data.sql b/ddl/postgres/tm_cz/functions/i2b2_process_mrna_data.sql
index 5615ca0..a6edae4 100755
--- a/ddl/postgres/tm_cz/functions/i2b2_process_mrna_data.sql
+++ b/ddl/postgres/tm_cz/functions/i2b2_process_mrna_data.sql
@@ -931,7 +931,7 @@ BEGIN
     )
     select distinct m.patient_id
 		  ,m.concept_code
-		  ,m.trial_name
+		  ,'@'
 		  ,'T' -- Text data type
 		  ,'E'  --Stands for Equals for Text Types
 		  ,m.trial_name
-- 
1.8.5.2 (Apple Git-48)