-- delete junk records created by the clientDemo

set echo on;
set heading off;
set linesize 256;
set pagesize 000;
column name format a20;
column stat_curr format a20;
column stat_used format a20;

-- A number of elements were identified in L3 objects, pars, values
--   and terms created by the clientDemo 
--   that cannot be deleted by the dbserver because they 
--   have an invalid stat_curr (the Get does not find them).
-- Change the stat_curr of those objects and terms so that 
--   these records can be deleted though the dbserver.

-- good l2_objects
select name,stat_curr from l2_objects where stat_curr!='statcurr';

-- junk l2_objects
select name,stat_curr,stat_used from l2_objects where stat_curr='statcurr';

-- these are all the good l2_pars
select name,cvs_package,version,par_name from l2_pars
	where exists
	(select name,cvs_package,version
	from l2_objects
	where l2_pars.name=l2_objects.name
	and   l2_pars.cvs_package=l2_objects.cvs_package
	and   l2_pars.version=l2_objects.version
	and   l2_objects.stat_curr!='statcurr');

-- these are all the junk l2_pars
select name,cvs_package,version,par_name from l2_pars
	where exists
	(select name,cvs_package,version
	from l2_objects
	where l2_pars.name=l2_objects.name
	and   l2_pars.cvs_package=l2_objects.cvs_package
	and   l2_pars.version=l2_objects.version
	and   l2_objects.stat_curr='statcurr');

-- bad l2_values
select name,cvs_package,version,par_name,pv_value from l2_values
	where exists
	(select name,cvs_package,version
	from l2_objects
	where l2_values.name=l2_objects.name
	and   l2_values.cvs_package=l2_objects.cvs_package
	and   l2_values.version=l2_objects.version
	and   l2_objects.stat_curr='statcurr');

-- all good l2_terms
select t_name,t_version from l2_globals;

select t_name,t_version from l2_values;


-- April 5, 2002: 
-- I deleted these records my making a temporary modification
-- to the trigdb_objects client code on d0ora1,
-- setting statCurr = statcur in readmenu,
-- allowing the delete button for the case of statused='statused'
-- and allowing the delete for the case of statused='statused'.
--
-- there were hundreds of records at L1, L2, L3.
-- I got no errors due to constraint violations.
-------------------------------------------------------





-- l3_values - make sure all pv_value/pv_term_version correspond to a 
-- 		real term in l3 terms
select t_name,t_version,
	name,cvs_package,version,
	par_name,pv_value,pv_term_version,
	create_user,create_date
	from l3_values
	where pv_term_version!=-1 
	and not exists
	(select t_name,t_version from l3_terms
	where l3_values.pv_value=l3_terms.t_name
	and l3_values.pv_term_version=l3_terms.t_version);

select * from l3_values
	where pv_term_version!=-1 
	and not exists
	(select t_name,t_version from l3_terms
	where l3_values.pv_value=l3_terms.t_name
	and l3_values.pv_term_version=l3_terms.t_version);


select * from l3_values
	where t_name='copytermname14000';
-- one of these
select * from l3_terms
	where t_name='copytermname14000';
-- one of these
select * from l3s_order_fs
	where t_name='copytermname14000';
-- none of these


