-- E. Gallas Nov 2005, March 2006
--
-- Look for duplicate entries in tables with integer primary keys
-- 
-- There are many tables in the Luminosity Database Schema with integer id's that
-- should unique keys to prevent duplicate entries (See my Lum DB document at
-- http://www-d0.fnal.gov/d0dist/dist/packages/lm_db/devel/www/LumDB.html ):
--
-- TABLE LBN_QUALITIES
--   Primary Key LQ_ID
--   Unique Keys LBN_LO, LBN_HI, QUAL_GROUP
--
-- TABLE TRANSITION_RECORDS
--   Primary Key TRANS_ID
--   Unique Keys LBN PNAME
--
-- TABLE LBN_TRIGGERS
--   Primary key LT_ID
--   Unique Keys LBN L3_BIT_NAME EG_NUM 
--
-- TABLE LBN_STREAMS
--   Primary Key LS_ID
--   Unique Keys LBN STREAM_NAME RUN_NUMBER
--
-- TABLE PRD_FILE_CHKS
--   Primary Key PFC_ID
--   Unique Keys LBN STREAM_NAME RUN_NUMBER APPL_NAME APPL_VERSION DATA_TIER 
--
-- When we looked at the ddl, we could not find the Unique Key definitions.
-- I looked in Oracle Designer, and they are in RON under
-- that table's index.
-- So the UK's were not in ddl and are not being enforced I guess.
-- How do we get them in the DDL
-- so that we can enforce these unique keys ?
-- or Was there some reason we decided not to  that I don't remember ?
--
-----------------------------------------------------------------
sqlplus d0read/reader@d0oflum1
-----------------------------------------------------------------
-- TABLE LBN_QUALITIES
--   Primary Key LQ_ID
--   Unique Keys LBN_LO, LBN_HI, QUAL_GROUP

desc LBN_QUALITIES;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LQ_ID                                     NOT NULL NUMBER(38)
 LBN_LO                                    NOT NULL NUMBER(38)
 LBN_HI                                    NOT NULL NUMBER(38)
 QUAL_GROUP                                NOT NULL VARCHAR2(10)
 QUALITY                                   NOT NULL VARCHAR2(20)
 CREATE_DATE                               NOT NULL DATE
 CREATE_USER                               NOT NULL VARCHAR2(30)
 RUN_NUMBER                                         NUMBER(38)
 MODIFY_DATE                                        DATE
 MODIFY_USER                                        VARCHAR2(30)
 PREVIOUS_QUALITY                                   VARCHAR2(20)
 WHOLE_RUN                                          VARCHAR2(5)

select count(*) from LBN_QUALITIES;

select LBN_LO,LBN_HI,QUAL_GROUP,count(*) 
	from  LBN_QUALITIES
	GROUP BY LBN_LO,LBN_HI,QUAL_GROUP HAVING COUNT(*)>=2 ;

-----------------------------------------------------------------
-- TABLE TRANSITION_RECORDS
--   Primary Key TRANS_ID
--   Unique Keys LBN PNAME
--
-- look for LBN's with multiple transition_records (are they duplicates?)

desc transition_records;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TRANS_ID                                  NOT NULL NUMBER(38)
 LBN                                       NOT NULL NUMBER(38)
 TRANS_TIME                                NOT NULL DATE
 PNAME                                     NOT NULL VARCHAR2(64)
 PVALUE                                             NUMBER

-- duplicates can also come in this form (surrounded by single quotes)
SELECT UNIQUE PNAME FROM TRANSITION_RECORDS

PNAME
----------------------------------------------------------------
'Coor'
'SCLinit'
'SCLinit-on-startRun'
'TFWinit'
'beginStore'
'endStore'
'lmDAQ'
'newDay'
'pauseRun'
'resumeRun'
'startRun'
'stopRun'
beginStore
endStore
newDay

15 rows selected.

select count(*) from transition_records;
    418688

column lbn format 9999999;
column PNAME format a20;

-- see if identical lbn transition_records been entered multiple times?
select lbn,PNAME,count(*) 
	from  TRANSITION_RECORDS
	GROUP BY LBN,PNAME HAVING COUNT(*)>=2 ;
...
307 rows selected.

-- see if the whole record is identical:
select lbn,PNAME,PVALUE,to_char(TRANS_TIME,'YYYY-MON-DD HH24:MI'),count(*) 
	from  transition_records
	GROUP BY lbn,PNAME,PVALUE,TRANS_TIME HAVING COUNT(*)>=2 
	ORDER BY lbn,PNAME;

     LBN PNAME                    PVALUE TO_CHAR(TRANS_TIM   COUNT(*)
-------- -------------------- ---------- ----------------- ----------
 1512092 endStore             1030920703 2002-SEP-01 00:00          2
 1512546 beginStore           1030942414 2002-SEP-01 00:00          2
 1512554 newDay               1030942814 2002-SEP-02 00:00          2
 1513139 endStore             1030969305 2002-SEP-02 00:00          2
 1514289 beginStore           1031014559 2002-SEP-02 00:00          2
 1514608 newDay               1031029229 2002-SEP-03 00:00          2
...
307 rows selected.

-- just curious: see how many LBNs have multiple transition records
select lbn,count(*) 
	from  transition_records
	GROUP BY LBN HAVING COUNT(*)>=2 ;

       LBN   COUNT(*)
---------- ----------
   3894996          2
   3895035          2
   3895037          2
   3895042          2
   3895048          2
   3895058          2
   3895081          2
   3895098          2
   3895115          2
   3895117          2
   3895135          2

40250 rows selected.
---------------------
-- just curious: see how many LBNs have more than 2 transition records
select lbn,count(*) 
	from  transition_records
	GROUP BY LBN HAVING COUNT(*)>2 ;
61 rows selected.
--------------------
-- just curious: see how many LBNs have more than 3 transition records
select lbn,count(*) 
	from  transition_records
	GROUP BY LBN HAVING COUNT(*)>3;
     LBN   COUNT(*)
-------- ----------
 1547342          4
 2835518          6
 2974007          4
3 rows selected.

-- more curious: see what the TR are for LBN's with 4 and 6 TRs:
select lbn,PNAME,PVALUE,to_char(TRANS_TIME,'YYYY-MON-DD HH24:MI')
	from  transition_records
	where LBN=1547342  or LBN=2835518  or LBN=2974007
	ORDER BY lbn,PNAME;

 1547342 'startRun'           1031569175 2002-SEP-09 00:00
 1547342 endStore             1031569235 2002-SEP-09 00:00
 1547342 endStore             1031569235 2002-SEP-09 00:00
 2835518 'Coor'               1068579778 2003-NOV-11 00:00
 2835518 'SCLinit'            1068579778 2003-NOV-11 00:00
 2835518 'TFWinit'            1068579778 2003-NOV-11 00:00
 2835518 'lmDAQ'              1068579778 2003-NOV-11 00:00
 2835518 'pauseRun'           1068579778 2003-NOV-11 00:00
 2835518 'resumeRun'          1068579778 2003-NOV-11 00:00
 2974007 'TFWinit'            1072703441 2003-DEC-29 00:00
 2974007 'beginStore'         1072703441 2003-DEC-29 00:00
 2974007 'pauseRun'           1072703441 2003-DEC-29 00:00
 2974007 'resumeRun'          1072703441 2003-DEC-29 00:00

14 rows selected.
-- conclusion:  there clearly are 309 duplicate records.
-- additional note: how can one LBN have 4 to 6 transition records?
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-- TABLE LBN_TRIGGERS
--   Primary key LT_ID
--   Unique Keys LBN L3_BIT_NAME EG_NUM 

desc LBN_TRIGGERS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LT_ID                                     NOT NULL NUMBER(38)
 LBN                                       NOT NULL NUMBER(38)
 L3_BIT_NAME                               NOT NULL VARCHAR2(100)
 EG_NUM                                    NOT NULL NUMBER(2)
 DECORR_SCALER                             NOT NULL NUMBER
 L1_ACCEPTS                                NOT NULL NUMBER
 L2_ACCEPTS                                NOT NULL NUMBER
 REC_ACCEPTS                               NOT NULL NUMBER
 PRESCALE                                  NOT NULL VARCHAR2(64)
 RUN_NUMBER                                         NUMBER(38)
 L1_LUM_NIM                                         NUMBER
 REC_LUM_NIM                                        NUMBER
 L1_LUM_VME                                         NUMBER
 REC_LUM_VME                                        NUMBER

column lbn format 9999999;
column L3_BIT_NAME format a20;
column EG_NUM format 99;

select LBN,L3_BIT_NAME,EG_NUM,COUNT(*) 
	from  LBN_TRIGGERS
	GROUP BY LBN,L3_BIT_NAME,EG_NUM HAVING COUNT(*)>=2 ;

-- takes a long time since there are so many records (211976655), 
-- this query finds no records

-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-- TABLE LBN_STREAMS
--   Primary Key LS_ID
--   Unique Keys LBN STREAM_NAME RUN_NUMBER
--
desc LBN_STREAMS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LS_ID                                     NOT NULL NUMBER(38)
 LBN                                       NOT NULL NUMBER(38)
 STREAM_NAME                               NOT NULL VARCHAR2(64)
 RUN_NUMBER                                NOT NULL NUMBER(38)
 NUM_EVENTS_ONL                            NOT NULL NUMBER(38)
 CHECKSUM_ONL                              NOT NULL VARCHAR2(25)

column lbn format 9999999;
column RUN_NUMBER format 9999999;
column STREAM_NAME format a20;

select LBN,STREAM_NAME,RUN_NUMBER,COUNT(*) 
	from  LBN_STREAMS
	GROUP BY LBN,STREAM_NAME,RUN_NUMBER HAVING COUNT(*)>=2 ;

no rows selected
-- conclusion: there are no duplicate lbn_streams at this time
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-- TABLE PRD_FILE_CHKS
--   Primary Key PFC_ID
--   Unique Keys LS_ID(LBN STREAM_NAME RUN_NUMBER) APPL_NAME APPL_VERSION DATA_TIER 
--
desc PRD_FILE_CHKS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PFC_ID                                    NOT NULL NUMBER(38)
 LS_ID                                     NOT NULL NUMBER(38)
 APPL_NAME                                 NOT NULL VARCHAR2(500)
 APPL_VERSION                              NOT NULL VARCHAR2(500)
 DATA_TIER_ID                              NOT NULL NUMBER(38)
 NUM_EVENTS_FILE                           NOT NULL NUMBER(38)
 CHECKSUM_FILE                             NOT NULL VARCHAR2(25)
 FILE_ID                                            NUMBER(38)

set linesize 120;
column LS_ID format 9999999;
column APPL_NAME    format a12;
column APPL_VERSION format a15;
column DATA_TIER_ID format 999;
column NUM_EVENTS_FILE format 99999;
column CHECKSUM_FILE format a15;
column FILE_ID format 99999999;

SQL> select count(*) from PRD_FILE_CHKS;

  COUNT(*)
----------
   1502029

select LS_ID,APPL_NAME,APPL_VERSION,DATA_TIER_ID,COUNT(*) 
	from  PRD_FILE_CHKS
	GROUP BY LS_ID,APPL_NAME,APPL_VERSION,DATA_TIER_ID HAVING COUNT(*)>=2 ;
-- there are tons of records here.

-- probe deeper comparing ALL columns to see if all are identical (except pfc_id)
select  LS_ID,APPL_NAME,APPL_VERSION,DATA_TIER_ID,NUM_EVENTS_FILE,CHECKSUM_FILE,FILE_ID,COUNT(*) 
	from  PRD_FILE_CHKS
	GROUP BY LS_ID,APPL_NAME,APPL_VERSION,DATA_TIER_ID,NUM_EVENTS_FILE,CHECKSUM_FILE,FILE_ID HAVING COUNT(*)>=2
	ORDER BY COUNT(*)  ;
-- >= 2 found 565922 rows .

-- probe deeper comparing ALL columns to see if all are identical (except pfc_id)
select  LS_ID,APPL_NAME,APPL_VERSION,DATA_TIER_ID,NUM_EVENTS_FILE,CHECKSUM_FILE,FILE_ID,COUNT(*) 
	from  PRD_FILE_CHKS
	GROUP BY LS_ID,APPL_NAME,APPL_VERSION,DATA_TIER_ID,NUM_EVENTS_FILE,CHECKSUM_FILE,FILE_ID HAVING COUNT(*)>=4 
	ORDER BY COUNT(*)  ;
-- >= 4 found 565922 rows .

select  LS_ID,APPL_NAME,APPL_VERSION,DATA_TIER_ID,NUM_EVENTS_FILE,CHECKSUM_FILE,FILE_ID,COUNT(*) 
	from  PRD_FILE_CHKS
	GROUP BY LS_ID,APPL_NAME,APPL_VERSION,DATA_TIER_ID,NUM_EVENTS_FILE,CHECKSUM_FILE,FILE_ID HAVING COUNT(*)>=8 
	ORDER BY COUNT(*)  ;
-- >= 8 found 4147 rows 
-- Eight (8) seems to be the maximum number of duplicates.

-- are any rows not duplicated?
select  LS_ID,APPL_NAME,APPL_VERSION,DATA_TIER_ID,NUM_EVENTS_FILE,CHECKSUM_FILE,FILE_ID,COUNT(*) 
	from  PRD_FILE_CHKS
	GROUP BY LS_ID,APPL_NAME,APPL_VERSION,DATA_TIER_ID,NUM_EVENTS_FILE,CHECKSUM_FILE,FILE_ID HAVING COUNT(*)=1 
	ORDER BY COUNT(*)  ;
-- = 1 found 565922 rows not duplicated.