set echo on;
set pagesize 80;
set feedback on;
-- no way to unset this after it is set to a value
set linesize 150;
-- turns the heading on as long as pagesize is not 0
set heading on;
--------------------------------------------------------
-- turn off headers
set heading off ;
-- removes the report of number of records found on select
set feedback off;
-- ?
set verify off;
-- don't display the sqlplus command
set echo off;
-- don't put in page feeds
set pagesize 000;
set serveroutput on;
dbms_output.enable(100000);
dbms_output.put_line();

--------------------------------------------------------
column ID format 99999;
column OBJ_DEF_ID format 99999;
column PRIMARY_FILTER format a15;
column FILTER_KEY format a12;
column RFS_DECL_ID format 999;
column L3TRIGBITS_ID format 99999;

column OBJECT_NAME    format a23;
column LEVEL_NUMBER   format a3;
column OBJECT_VERSION format a15;
column OBJ_DEF_TYPE   format a15;
column VERSION_NUMBER format 999;

column PARAMETER_NAME  format a15;
column OBJECT_DATATYPE format a15;
column DEFAULT_VALUE   format a13;
column MINIMUM_VALUE   format a10;
column MAXIMUM_VALUE   format a10;
column OBJ_DEF_ID      format 99999;
column PARAMETER_SEQ   format 99999;

column REFSET_NAME     format a18;
column TRIG_LIST_ID    format 99999; 
column author	       format a20;
column version 	       format 99.9;

column PARAMETER_VALUE format a15;
column TOOL_DECL_ID    format 99999;
column OBJ_PARM_ID     format 99999;

column NAME            format a20;
column FILT_DECL_ID    format 99999;

column TRIGGER_BIT_NAME format a20;
--------------------------------------------------------
-- DELETE known corrupt records
select * from object_definitions where object_name='test_filter';
-- this filter object has ID=10
select * from object_parameter_definitions where obj_def_id='10';
-- this filter object has 9 parameters
select * from filter_declarations where obj_def_id='10';
-- this filter object is referred to in 2 filter declarations
-- where ID=2 and 7
select * from triggerdb.filter_parameter_values where filt_decl_id='2' or filt_decl_id='7';
-- this finds 12 values which also point back to the 9 obj_param_defs above

--------------------------------------------------------
-- object_definitions :select * from object_definitions where object_name='test_filter';


-- make sure all records have object_name, object_version (none found)
select * from object_definitions 
	where object_name is null or object_version is null;

-- make sure all records have obj_def_type = (TOOL or FILTER) (none found)
select * from object_definitions 
	where obj_def_type not in ('TOOL','FILTER');

-- version_number is never used (except for L3FJet - ignore it)
select * from object_definitions where version_number is null;
select * from object_definitions where version_number is not null;

-- all tool and filter definitions 
-- have object_verion=1.0, so print out v00-00-00 for the version
-- rather than rtrim(object_version,' ')
select * from object_definitions where object_version!=1.0;

-- dump the tool objects for table
-- TOOL_OBJECTS (NAME, CVS_PACKAGE, VERSION, STATUS, HISTORY)
spool l3_tool_objects.lis;
select  rtrim(object_name,' ')||'|dummy_package|v00-00-00|new|Initial version from Carmem''s L3 database'
	from object_definitions where obj_def_type = 'TOOL';
spool off;


-- dump the filter objects for table
-- FILTER_OBJECTS (NAME, CVS_PACKAGE, VERSION, STATUS, HISTORY, PRIMAL_STREAM)
spool l3_filter_objects.lis;
select  
rtrim(object_name,' ')||'|dummy_package|v00-00-00|new|Initial version from Carmem''s L3 database|dummy_stream'
	from object_definitions where obj_def_type = 'FILTER';
spool off;

--------------------------------------------------------
-- EGallas - I decided to make these changes in the sqlldr file

-- object_parameter_definitions :
-- look for invalid types:
select * from object_parameter_definitions 
where object_datatype not in ('int','float','string','bool','tool','filter','key');

-- 7! occurances of 'unknown' datatype.  Amber says they are type 'tool';
select * from object_parameter_definitions where object_datatype = 'unknown';
UPDATE object_parameter_definitions 
	SET object_datatype='unknown'
	WHERE object_datatype = 'tool';

-- !14 occurances of 'integer' datatypes found, update them to be 'int'
select * from object_parameter_definitions where object_datatype = 'integer';
UPDATE object_parameter_definitions 
	SET object_datatype='int'
	WHERE object_datatype = 'integer';

-- look for various null forms of default_value
select * from object_parameter_definitions 
	where upper(default_value) like ('%NO%') or upper(default_value) like ('%NU%');
-- these are the good default values
select * from object_parameter_definitions 
	where upper(default_value) not like ('%NO%') 
	and upper(default_value) not like ('%NU%');
-- 56 of these
UPDATE object_parameter_definitions 
	SET default_value=null
	WHERE upper(default_value) like ('%NO%') 
	or upper(default_value) like ('%NU%');

-- find records with no min,max range, UPDATE MIN to null (37 rows)
select * from object_parameter_definitions where minimum_value like '%no%';
UPDATE object_parameter_definitions 
	SET minimum_value = null
	where minimum_value like '%no%';

-- is the parameter_seq ever null ? (no)
select object_definitions.object_name,parameter_name,parameter_seq 
	from object_parameter_definitions,object_definitions
	where object_definitions.id=obj_def_id 
	order by object_definitions.object_name,parameter_seq;
select * from object_parameter_definitions where parameter_seq is null;

-- dump the tool parameters for table
-- replacing rtrim(object_definitions.object_version,' ') with v00-00-00 as in the object definitions
-- TOOL_PAR (NAME, CVS_PACKAGE, VERSION, PAR_NAME, PAR_ORDER, PAR_TYPE, PAR_DEFAULT, PAR_MIN, PAR_MAX)
spool l3_tool_par.lis;
select  rtrim(object_definitions.object_name,' ')||'|dummy_package|v00-00-00|'||
        rtrim(object_parameter_definitions.parameter_seq,' ')||'|'||
        rtrim(object_parameter_definitions.parameter_name,' ')||'|'||
        rtrim(object_parameter_definitions.object_datatype,' ')||'|'||
        rtrim(object_parameter_definitions.default_value,' ')||'|'||
        rtrim(object_parameter_definitions.minimum_value,' ')||'|'||
        rtrim(object_parameter_definitions.maximum_value,' ')
	from object_definitions,object_parameter_definitions 
	where object_parameter_definitions.obj_def_id=object_definitions.id
	and object_definitions.obj_def_type = 'TOOL'
	order by object_definitions.object_name,object_parameter_definitions.parameter_seq;
spool off;

-- dump the filter parameters for table FILTER_PAR 
-- replacing rtrim(object_definitions.object_version,' ') with v00-00-00 as in the object definitions
-- (NAME, CVS_PACKAGE, VERSION, PAR_ORDER, PAR_NAME, PAR_TYPE, PAR_DEFAULT, PAR_MIN, PAR_MAX)
spool l3_filter_par.lis;
select  rtrim(object_definitions.object_name,' ')||'|dummy_package|v00-00-00|'||
        rtrim(object_parameter_definitions.parameter_seq,' ')||'|'||
        rtrim(object_parameter_definitions.parameter_name,' ')||'|'||
        rtrim(object_parameter_definitions.object_datatype,' ')||'|'||
        rtrim(object_parameter_definitions.default_value,' ')||'|'||
        rtrim(object_parameter_definitions.minimum_value,' ')||'|'||
        rtrim(object_parameter_definitions.maximum_value,' ')
	from object_definitions,object_parameter_definitions 
	where object_parameter_definitions.obj_def_id=object_definitions.id
	and object_definitions.obj_def_type = 'FILTER'
	order by object_definitions.object_name,object_parameter_definitions.parameter_seq;
spool off;

--------------------------------------------------------
-- triggerdb.FILTER_PARAMETER_VALUES --> see ***

-- how many records have parameter_value null
select * from triggerdb.FILTER_PARAMETER_VALUES where parameter_value is null;
-- 13 of these,
-- what filters/parameters do they relate to?
select 
object_definitions.object_name
,object_parameter_definitions.parameter_name
,object_parameter_definitions.parameter_seq
,triggerdb.FILTER_PARAMETER_VALUES.parameter_value
,filter_declarations.primary_filter
,filter_declarations.filter_key
,filter_declarations.l3trigbits_id
,l3_trigger_bits.trigger_bit_name
from 
triggerdb.FILTER_PARAMETER_VALUES
,object_definitions
,object_parameter_definitions
,filter_declarations
,l3_trigger_bits
where 
triggerdb.FILTER_PARAMETER_VALUES.parameter_value is null 
and triggerdb.FILTER_PARAMETER_VALUES.obj_parm_id=object_parameter_definitions.id
and filter_declarations.id=triggerdb.FILTER_PARAMETER_VALUES.filt_decl_id
and object_definitions.id=filter_declarations.obj_def_id
and filter_declarations.l3trigbits_id=l3_trigger_bits.id
;
-- again, 13 found -- they all have a l3trigbits_id for trigger_bit_name
-- 'atrig' and 'trig_1'.  These were test filter scripts I think, so 
-- I think these filter_parameter_values with null values should be deleted
-- because when you look, there are skips in the parameter_seq so the
-- information cannot be complete to define a filter.
DELETE from triggerdb.FILTER_PARAMETER_VALUES where parameter_value is null;
--------------------------------------------------------
-- what about the above case where the parameter_value is not null?
select 
object_definitions.object_name
,object_parameter_definitions.parameter_name
,object_parameter_definitions.parameter_seq
,triggerdb.FILTER_PARAMETER_VALUES.parameter_value
,filter_declarations.primary_filter
,filter_declarations.filter_key
,filter_declarations.l3trigbits_id
,l3_trigger_bits.trigger_bit_name
from 
triggerdb.FILTER_PARAMETER_VALUES
,object_definitions
,object_parameter_definitions
,filter_declarations
,l3_trigger_bits
where 
triggerdb.FILTER_PARAMETER_VALUES.obj_parm_id=object_parameter_definitions.id
and filter_declarations.id=triggerdb.FILTER_PARAMETER_VALUES.filt_decl_id
and object_definitions.id=filter_declarations.obj_def_id
and filter_declarations.l3trigbits_id=l3_trigger_bits.id
order by
l3_trigger_bits.trigger_bit_name
,object_definitions.object_name
,object_parameter_definitions.parameter_seq
;
-- *** okay, here I see every filter declaration has a complete set of 
-- parameter value records in filter_parameter_values, but because
-- the default values were not recorded in the filter_parameter_values table,
-- the complete filter declaration is incomplete
-- Therefore, disregard info in tables:
--   filter_parameter_values
--   filter_declarations
--   l3_trigger_bits
--   trigger_list
-- except for possibly filter_declaration.id=8
--------------------------------------------------------
--------------------------------------------------------
-- tool_parameter_values :

-- how many records have parameter_value null
select * from triggerdb.TOOL_PARAMETER_VALUES where parameter_value is null;
-- 79 records have null values.
-- how many total records ?
select * from triggerdb.TOOL_PARAMETER_VALUES;
-- 128 total records

-- what tools/parameters do they relate to?
select 
object_definitions.object_name
,object_parameter_definitions.parameter_name
,object_parameter_definitions.parameter_seq
,TOOL_PARAMETER_VALUES.parameter_value
,tool_declarations.refset_name
,tool_declarations.trig_list_id
,triggerdb.trigger_list.author
,triggerdb.trigger_list.version
from 
TOOL_PARAMETER_VALUES
,object_definitions
,object_parameter_definitions
,tool_declarations
,triggerdb.trigger_list
where 
TOOL_PARAMETER_VALUES.obj_parm_id=object_parameter_definitions.id
and TOOL_PARAMETER_VALUES.tool_decl_id=tool_declarations.id
and object_definitions.id=tool_declarations.obj_def_id
and tool_declarations.trig_list_id=triggerdb.trigger_list.id
order by
triggerdb.trigger_list.author,triggerdb.trigger_list.version
,tool_declarations.refset_name
,object_definitions.object_name
,object_parameter_definitions.parameter_seq
;
-- 128 rows.  Only 2 GTRK_* refsets have all parameter values
-- and the latest trigger list shows that that tool now has
-- 4 parameters, not the 3 shown here,
-- so I am not going to transfer refset entries in tables:
--	tool_declarations
--	tool_parameter_values

----and object_definitions.object_name='L3TGlobalTracker'
----and object_definitions.object_name like '%OLSort%'
--------------------------------------------------------
--------------------------------------------------------
-- refset_definitions
select * from refset_definitions;

-- there are 44 entries in this table.  Either they 
-- don't have a refset_name or they don't have the 
-- combo primary_filter/filter_key whatever that means.
-- all records have an obj_def_id, but this just points
-- to a tool or filter object and does not set the value
-- of the parameters,
-- so in my way of thinking, none of these records 
-- have sufficient information to specify 
-- a REFSET (a TOOL object with parameters and values).

--------------------------------------------------------
--------------------------------------------------------
