extensions/net.sf.basedb.reggie/trunk/dev/cleanup_dev_clone_pg.sql

Code
Comments
Other
Rev Date Author Line
2864 28 Oct 14 nicklas 1 /*
2864 28 Oct 14 nicklas 2   Use this SQL file to remove sensitive personal information from
2864 28 Oct 14 nicklas 3   a development copy of the production server database. 
2864 28 Oct 14 nicklas 4   
2864 28 Oct 14 nicklas 5   !!!!! *********************************** !!!!!
2864 28 Oct 14 nicklas 6   !!!!! DO NOT USE ON THE PRODUCTION SERVER !!!!!
2864 28 Oct 14 nicklas 7   !!!!! *********************************** !!!!!
2864 28 Oct 14 nicklas 8   
2864 28 Oct 14 nicklas 9   For certain functions in Reggie, eg. generating statistics, it is
2864 28 Oct 14 nicklas 10   more or less required to use real data. It is not really possible 
2864 28 Oct 14 nicklas 11   to autogenerate the required amount of fake data that is needed and
2864 28 Oct 14 nicklas 12   the statistical functions also need to be compared for correctness 
2864 28 Oct 14 nicklas 13   against the current statistics produces by other means.
2864 28 Oct 14 nicklas 14   
2864 28 Oct 14 nicklas 15   This script works with PostgreSQL and will:
2864 28 Oct 14 nicklas 16   
2864 28 Oct 14 nicklas 17   * Replace the ROOT account login and password with 'root'/'root'
2864 28 Oct 14 nicklas 18   * Replace the DateOfBirth with a random date for all patients
2864 28 Oct 14 nicklas 19   * Replace the PersonalNumber for all patients with a random based on the 'DateOfBith' annotation
2864 28 Oct 14 nicklas 20   * Replace the AllFirstNames and FamilyName with other values
2864 28 Oct 14 nicklas 21   * Replace PAD with 'PAD-' + hex value derived from internal id
2864 28 Oct 14 nicklas 22 */
2864 28 Oct 14 nicklas 23
2864 28 Oct 14 nicklas 24 /* Change ROOT login to 'root' */
2864 28 Oct 14 nicklas 25 update "Users" set login='root' where system_id='net.sf.basedb.core.User.ROOT';
2864 28 Oct 14 nicklas 26
2864 28 Oct 14 nicklas 27 /* Change ROOT password to 'root' */
2864 28 Oct 14 nicklas 28 update "Passwords" set crypted_password = '$2a$10$yBj1SdJy7K.998hx7XKGauME6fUpIC1yOqzp.TKvoBQmW7bm0ZlFW' 
2864 28 Oct 14 nicklas 29 where id = (select u.id from "Users" u where u.system_id='net.sf.basedb.core.User.ROOT');
2864 28 Oct 14 nicklas 30
2864 28 Oct 14 nicklas 31 /* Replace DateOfBirth with random values for all patients */
2864 28 Oct 14 nicklas 32 update "DateValues" set value=to_date(concat(1900+floor(100*random()), ' ', ceil(365*random())), 'YYYY DDD')
2864 28 Oct 14 nicklas 33 where id in 
2864 28 Oct 14 nicklas 34   (select a.value_id from "Annotations" a inner join "AnnotationTypes" at on at.id=a.annotationtype_id 
2864 28 Oct 14 nicklas 35    where at.name='DateOfBirth');
2864 28 Oct 14 nicklas 36
2864 28 Oct 14 nicklas 37 /* Replace PersonalNumber with '201212121212' for all patients */
2864 28 Oct 14 nicklas 38 /*
2864 28 Oct 14 nicklas 39 update "StringValues" set value='201212121212'
2864 28 Oct 14 nicklas 40 where id in 
2864 28 Oct 14 nicklas 41   (select a.value_id from "Annotations" a inner join "AnnotationTypes" at on at.id=a.annotationtype_id 
2864 28 Oct 14 nicklas 42    where at.name='PersonalNumber');
2864 28 Oct 14 nicklas 43 */
2864 28 Oct 14 nicklas 44
2864 28 Oct 14 nicklas 45 /* Replace PersonalNumber with value generated from corresponding DateOfBirth
2864 28 Oct 14 nicklas 46  * padded with 'X' and a random 3-digit number. Patients with no BirthOfDate will
2864 28 Oct 14 nicklas 47  * get '201212121212' as PersonalNumber. If this query doesn't work, use the one
2864 28 Oct 14 nicklas 48  * above instead which set all PersonalNumber's to '201212121212'.
2864 28 Oct 14 nicklas 49  */
2864 28 Oct 14 nicklas 50 update "StringValues" pnr set value=coalesce(concat(to_char(
2864 28 Oct 14 nicklas 51   (
2864 28 Oct 14 nicklas 52     select dofb.value from "DateValues" dofb 
2864 28 Oct 14 nicklas 53     inner join "Annotations" a1 on dofb.id=a1.value_id 
2864 28 Oct 14 nicklas 54     inner join "AnnotationTypes" at1 on at1.id=a1.annotationtype_id and at1.name='DateOfBirth'
2864 28 Oct 14 nicklas 55     inner join "Annotations" a2 on a1.annotationset_id=a2.annotationset_id
2864 28 Oct 14 nicklas 56     inner join "AnnotationTypes" at2 on at2.id=a2.annotationtype_id and at2.name='PersonalNumber'
2864 28 Oct 14 nicklas 57     where a2.value_id=pnr.id
2864 28 Oct 14 nicklas 58   ), 
2864 28 Oct 14 nicklas 59   'YYYYMMDD'), 100+floor(900*random()), 'X'), '201212121212')
2864 28 Oct 14 nicklas 60 where pnr.id in 
2864 28 Oct 14 nicklas 61   (select a.value_id from "Annotations" a inner join "AnnotationTypes" at on at.id=a.annotationtype_id 
2864 28 Oct 14 nicklas 62    where at.name='PersonalNumber');
2864 28 Oct 14 nicklas 63    
2864 28 Oct 14 nicklas 64 /* Replace AllFirstNames with 'Blue', 'Red', 'Green', 'Yellow' or 'White' for all patients */
2864 28 Oct 14 nicklas 65 update "StringValues" set value=
2864 28 Oct 14 nicklas 66     case id % 5 
2864 28 Oct 14 nicklas 67     when 0 then 'Blue'
2864 28 Oct 14 nicklas 68     when 1 then 'Red'
2864 28 Oct 14 nicklas 69     when 2 then 'Green'
2864 28 Oct 14 nicklas 70     when 3 then 'Yellow'
2864 28 Oct 14 nicklas 71     else 'White'
2864 28 Oct 14 nicklas 72     end
2864 28 Oct 14 nicklas 73 where id in 
2864 28 Oct 14 nicklas 74   (select a.value_id from "Annotations" a inner join "AnnotationTypes" at on at.id=a.annotationtype_id 
2864 28 Oct 14 nicklas 75    where at.name='AllFirstNames');
2864 28 Oct 14 nicklas 76    
2864 28 Oct 14 nicklas 77 /* Replace FamilyName with 'Winter', 'Spring', 'Summer' or 'Autumn' for all patients */
2864 28 Oct 14 nicklas 78 update "StringValues" set value=
2864 28 Oct 14 nicklas 79     case id % 4 
2864 28 Oct 14 nicklas 80     when 0 then 'Winter'
2864 28 Oct 14 nicklas 81     when 1 then 'Spring'
2864 28 Oct 14 nicklas 82     when 2 then 'Summer'
2864 28 Oct 14 nicklas 83     else 'Autumn'
2864 28 Oct 14 nicklas 84     end
2864 28 Oct 14 nicklas 85 where id in 
2864 28 Oct 14 nicklas 86   (select a.value_id from "Annotations" a inner join "AnnotationTypes" at on at.id=a.annotationtype_id 
2864 28 Oct 14 nicklas 87    where at.name='FamilyName');
2864 28 Oct 14 nicklas 88    
2864 28 Oct 14 nicklas 89    
2864 28 Oct 14 nicklas 90 /* Replace PAD with PAD-HEX(<id>) values for all specimen */
2864 28 Oct 14 nicklas 91 update "StringValues" set value=upper(concat('PAD-', to_hex(id)))
2864 28 Oct 14 nicklas 92 where id in 
2864 28 Oct 14 nicklas 93   (select a.value_id from "Annotations" a inner join "AnnotationTypes" at on at.id=a.annotationtype_id 
2864 28 Oct 14 nicklas 94    where at.name='PAD');

Generated on Tue Nov 21 03:29:41 2023 (UTC) by svndigest 0.11.2pre (dev build 1672)