[Gnumed-devel] removing test data

[Karsten Hilbert]

I have added the following script which will enable people
to delete most test data on a stock v11 database. We cannot
add that to the upgrade/bootstrap because it would unfixably
break the data consistency tests. People will have to run it
manually.

I will provide two more scripts: one that drops data for the
"Hilbert" test persons and one that drops Kirk and McCoy.

--- ==============================================================
--- GNUmed database schema change script
--
--- License: GPL
--- Author: address@hidden
--
--- ==============================================================
--- $Id: v11-drop_obsolete_groups-dynamic.sql,v 1.1 2009/08/06 13:55:19 ncq Exp 
$
--- $Revision: 1.1 $

--- --------------------------------------------------------------
\set ON_ERROR_STOP 1

set default_transaction_read_only to off;

--- --------------------------------------------------------------
--- remove unwanted test data
begin;


--- temporary storage
create temporary table persons_to_delete (
        pk_identity integer
                unique
) on commit drop ;


--- gather people to delete
insert into persons_to_delete (pk_identity) values ((
        select pk_identity from dem.v_basic_person where
                lastnames = 'Haywood'
                        and
                firstnames = 'Ian'
                        and
                cob = 'UK'
                        and
                gender = 'm'
                        and
                date_trunc('day', dob) = '1977-12-18'::timestamp
));

insert into persons_to_delete (pk_identity) values ((
        select pk_identity from dem.v_basic_person where
                lastnames = 'Raby'
                        and
                firstnames = 'Cilla'
                        and
                cob = 'AU'
                        and
                gender = 'f'
                        and
                date_trunc('day', dob) = '1979-02-28'::timestamp
));

insert into persons_to_delete (pk_identity) values ((
        select pk_identity from dem.v_basic_person where
                lastnames = 'Herb'
                        and
                firstnames = 'Horst'
                        and
                cob = 'DE'
                        and
                gender = 'm'
                        and
                date_trunc('day', dob) = '1969-12-31'::timestamp
));

insert into persons_to_delete (pk_identity) values ((
        select pk_identity from dem.v_basic_person where
                lastnames = 'Terry'
                        and
                firstnames = 'Richard'
                        and
                cob = 'AU'
                        and
                gender = 'm'
                        and
                date_trunc('day', dob) = '1959-12-31'::timestamp
));

insert into persons_to_delete (pk_identity) values ((
        select pk_identity from dem.v_basic_person where
                lastnames = 'Berger'
                        and
                firstnames = 'Hilmar'
                        and
                cob = 'DE'
                        and
                gender = 'm'
                        and
                date_trunc('day', dob) = '1973-12-31'::timestamp
));

insert into persons_to_delete (pk_identity) values ((
        select pk_identity from dem.v_basic_person where
                lastnames = 'Chapel'
                        and
                firstnames = 'Christine'
                        and
                cob = 'US'
                        and
                gender = 'f'
                        and
                date_trunc('day', dob) = '1932-02-22'::timestamp
));


insert into persons_to_delete (pk_identity) values ((
        select pk_identity from dem.v_basic_person where
                lastnames = 'Bashier'
                        and
                firstnames = 'Julian'
                        and
                cob = 'SD'
                        and
                gender = 'm'
                        and
                date_trunc('day', dob) = '1965-11-20'::timestamp
));

insert into persons_to_delete (pk_identity) values ((
        select pk_identity from dem.v_basic_person where
                lastnames = 'Testwoman'
                        and
                firstnames = 'Laborata'
                        and
                cob = 'CA'
                        and
                gender = 'f'
                        and
                date_trunc('day', dob) = '1931-03-21'::timestamp
));

insert into persons_to_delete (pk_identity) values ((
        select pk_identity from dem.v_basic_person where
                lastnames = 'Test'
                        and
                firstnames = 'Testdob'
                        and
                gender = 'm'
                        and
                date_trunc('day', dob) = '1978-11-15'::timestamp
));

--- delete data
delete from clin.lab_request where fk_encounter in (
        select pk from clin.encounter where fk_patient in (
                select pk_identity from persons_to_delete
        )
);

--- delete episodes
delete from clin.episode where fk_encounter in (
        select pk from clin.encounter where fk_patient in (
                select pk_identity from persons_to_delete
        )
);

--- delete encounters
delete from clin.encounter where fk_patient in (select pk_identity from 
persons_to_delete);


--- delete names
delete from dem.names where id_identity in (select pk_identity from 
persons_to_delete);

--- delete identities
--- 1) enable dropping of people from dem.identity
drop rule r_del_identity on dem.identity;

--- 2) drop identities
delete from dem.identity where pk in (select pk_identity from 
persons_to_delete);

--- 3) re-disable dropping of people from dem.identity
create rule r_del_identity as
        on delete to dem.identity do instead
                update dem.identity set deleted = True where pk = OLD.pk;

commit;

--- --------------------------------------------------------------
select gm.log_script_insertion('$RCSfile: 
v11-drop_obsolete_groups-dynamic.sql,v $', '$Revision: 1.1 $');

--- ==============================================================
--- $Log: v11-drop_obsolete_groups-dynamic.sql,v $
--- Revision 1.1  2009/08/06 13:55:19  ncq
--- - new
--- 

-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346