Changeset 1955

Timestamp:

Apr 24, 2013, 1:47:20 PM (10 years ago)

Author:

Nicklas Nordborg

Message:

Script for anonymizing data update to create fake PersonalNumbers based on the randomly generated DateOfBith annotation. To ensure that no real personal number is generated, the last digit is replaced with an 'X'.

File:

• extensions/net.sf.basedb.reggie/trunk/testdata/cleanup_dev_clone.sql (modified) (3 diffs)

extensions/net.sf.basedb.reggie/trunk/testdata/cleanup_dev_clone.sql

@@ -16 +16 @@
   
   * Replace the ROOT account login and password with 'root'/'root'
-  * Replace the PersonalNumber for all patients with '201212121212'
   * Replace the DateOfBirth with a random date for all patients
+  * Replace the PersonalNumber for all patients with a random based on the 'DateOfBith' annotation
   * Replace the AllFirstNames and FamilyName with other values
 */
@@ -28 +28 @@
 where id = (select u.id from `Users` u where u.system_id='net.sf.basedb.core.User.ROOT');
 
-/* Replace PersonalNumber with '201212121212' for all patients */
-update `StringValues` set value='201212121212'
-where id in 
-  (select a.value_id from `Annotations` a inner join `AnnotationTypes` at on at.id=a.annotationtype_id 
-   where at.name='PersonalNumber');
-
 /* Replace DateOfBirth with random values for all patients */
 update `DateValues` set value=makedate(1900+floor(100*rand()), ceil(365*rand()))
@@ -40 +34 @@
    where at.name='DateOfBirth');
 
+/* Replace PersonalNumber with '201212121212' for all patients */
+/*
+update `StringValues` set value='201212121212'
+where id in 
+  (select a.value_id from `Annotations` a inner join `AnnotationTypes` at on at.id=a.annotationtype_id 
+   where at.name='PersonalNumber');
+*/
+
+/* Replace PersonalNumber with value generated from corresponding DateOfBirth
+ * padded with 'X' and a random 3-digit number. Patients with no BirthOfDate will
+ * get '201212121212' as PersonalNumber. If this query doesn't work, use the one
+ * above instead which set all PersonalNumber's to '201212121212'.
+ */
+update `StringValues` pnr set pnr.value=coalesce(concat(date_format(
+  (
+    select dofb.value from `DateValues` dofb 
+    inner join `Annotations` a1 on dofb.id=a1.value_id 
+    inner join `AnnotationTypes` at1 on at1.id=a1.annotationtype_id and at1.name='DateOfBirth'
+    inner join `Annotations` a2 on a1.annotationset_id=a2.annotationset_id
+    inner join `AnnotationTypes` at2 on at2.id=a2.annotationtype_id and at2.name='PersonalNumber'
+    where a2.value_id=pnr.id
+  ), 
+  '%Y%m%d'), 100+floor(900*rand()), 'X'), '201212121212')
+where pnr.id in 
+  (select a.value_id from `Annotations` a inner join `AnnotationTypes` at on at.id=a.annotationtype_id 
+   where at.name='PersonalNumber');
+   
 /* Replace AllFirstNames with 'Blue', 'Red', 'Green', 'Yellow' or 'White' for all patients */
 update `StringValues` set value=