-- Sept 2000 E. Gallas
-- Look for obvious constraint errors in l1l2_trig database

-----------------------------
-- check trigger_type in term_type table
select trigger_type,term_type,tet_version from term_type where not exists
(select trigger_type from trigger_type 
where trigger_type.trigger_type=term_type.trigger_type);

-----------------------------
-- check trigger_type,term_type,tet_version in property table
select trigger_type,term_type,tet_version,argument from property where not exists
(select trigger_type,term_type,tet_version from term_type 
where term_type.trigger_type=property.trigger_type 
and term_type.term_type=property.term_type 
and term_type.tet_version=property.tet_version);

-- found term_type TLQ with arguments (n,q) in property table
-- no corresponding term_type table or argument values in arg_value table
-- therefore:  remove TLQ arguments (n,q) from property table

select trigger_type,term_type,tet_version,argument from property where
term_type like '%T%' and trigger_type='L1' order by term_type;

-----------------------------
-- check trigger_type,term_type,tet_version,argument in arg_value table
select trigger_type,term_type,tet_version,argument,av_value from arg_value where not exists
(select trigger_type,term_type,tet_version,argument from property  
where property.trigger_type=arg_value.trigger_type 
and property.term_type=arg_value.term_type 
and property.tet_version=arg_value.tet_version
and property.argument=arg_value.argument);

-- found term_type CER with argument e with values L and 5 in arg_values table.
-- found term_type CER with arguments em and hd in properties table.
-- found term_type CJR with arguments jt in properties table.
-- action:  delete all these entries since the region terms do not have et thresholds.

-----------------------------
-- check trigger_type,term_type,tet_version,argument,av_value in rs_script table
select trigger_type,term_type,tet_version,argument,av_value,line_no from rs_script where not exists
(select trigger_type,term_type,tet_version,argument from arg_value
where arg_value.trigger_type=rs_script.trigger_type 
and arg_value.term_type=rs_script.term_type 
and arg_value.tet_version=rs_script.tet_version
and arg_value.argument=rs_script.argument
and arg_value.av_value=rs_script.av_value);

-- found different values of jt and lt threshold, mostly due to the different
-- thresholds used the the current standard physics list and Josh's list.
-- Action:  make av_values consistent, entering as many as needed.

-- check rs_key rs_script table
select rs_key,line_no from rs_script where not exists
(select rs_key from rs_key 
where rs_key.rs_key=rs_script.rs_key);

-- nty_attribute.nty_refset should be inhereted from rs_key.rs_key
-- add ercount and jrcount count refsets to neotype with attributes count and eta
-- for implementation of the Calorimeter em and jet Region terms as a placeholder.

-----------------------------
-- check trigger_type,term_type,tet_version,argument,av_value in term_arg_value table
select trigger_type,term_type,tet_version,argument,av_value,term_name from term_arg_value where not exists
(select trigger_type,term_type,tet_version,argument from arg_value
where arg_value.trigger_type=term_arg_value.trigger_type 
and arg_value.term_type=term_arg_value.term_type 
and arg_value.tet_version=term_arg_value.tet_version
and arg_value.argument=term_arg_value.argument
and arg_value.av_value=term_arg_value.av_value);

-- found av_values hd7, hd10, hd15 have extra spaces in l1tav_load

-- check term_name,tn_version in term_arg_value table
select term_name,tn_version,trigger_type,term_type,tet_version,argument,av_value from term_arg_value where not exists
(select term_name,tn_version from term 
where term.term_name=term_arg_value.term_name
and term.tn_version=term_arg_value.tn_version);

-----------------------------
-- test for constraint violation on insert

insert into term_arg_value 
(trigger_type,term_type,tet_version,term_name,tn_version,argument,av_value)
values ('L2','EM','1','eem_2_10_s_fpi_D','1','q','fpi');

-- this confirmed that constraint violations are caught in sqlplus
-- but not sqlldr
-----------------------------




