#!/bin/sh
#*******************************************************************************
#             PACOR II SOFTWARE
#       Property of the U.S. Government
#            NASA/GSFC/Code 560
#*******************************************************************************
#PURPOSE: Creates the referential integrity constraints. 
#
#INVOCATION METHOD: ins_referential user/pw log_file [A | D] 
#
#
#DEVELOPMENT HISTORY:
#
#	Author		Change-ID   Release	Date	Description of Change
#	------		---------   -------	----	---------------------
#    S. Azama                       Build 2   10/11/93  Initial release 
#    P. Matthews                    B3R2      03/23/94  Added referential constraints
#                                                        for build 3 tables and 
#                                                        combined/enhanced add/drop
#                                                        referential
#    P. Matthews        ICCR350     B3R2      04/27/94  Added constraints for handle 
#                                                        destination host information
#    P. Matthews                    B3R2      05/05/94  Modified constraints based on FE Mods
#    P. Matthews        ICCR350     B3R2      06/02/94  Modified constraints based on 
#                                                        destination design mods
#   M.Rauschenberger                B4R2.1    08/29/94  Modifed parameters
#    P. Matthews                    B4R2.1     11/2/94  Removed dynamic/dynamic referential
#                                                        constraints; Added constraints for
#                                                        UserLevel and UserAccessLevel
#    P. Matthews                    B4R2.1     11/3/94  Removed db_destination constraints
#   M.Rauschenberger    PAC2940546  B5R2.2    03/25/95  Removed db_RTOSUserSelectionSpec
#					                 constraint fk_RTOSUserDestCatalog
#   M.Rauschenberger    PAC2951002  B5R2.2    04/05/95  Removed constraints associated with
#					                 db_GroundTerminalInterfaceChnl
#    I. Horowitz        n/a         ACE R1.0  10/12/95  Added constraints for 
#							 db_GroupDestination
#							 db_ProdFileDeliveryAcct
#							 db_PEConfiguration
#    I. Horowitz        n/a         ACE R1.0  04/09/96  Dropped db_GroupDestination,
#							 db_ProdFileDeliveryAcct constraints;
#						         dropped constraints not 
#							 needed by ACE for tables 
#							 db_AcqSessionCharacteristics;
#							 db_MissionCatalog.  
#    I. Horowitz        TRACE0105 TRACE R2.0  02/04/97  reformatted to match Pacor II changes
#							 for R3.2; removed constraints 
#							 db_HST_PKSetup (fk_PKHSTSetDataStr),
#							 db_MissionCatalog (fk_MissCatDataSrc),
#							 db_MissionDataLine (fk_DataLineMiss,
#							  fk_DataLineLine),
#							 db_MissionDataRate (fk_DataRateMiss,
#							  fk_DataRateDataRate),
#							 db_RTOSConfig (fk_RTOSConfResID),
#							 db_RTOSUserSelectionSpec (
#							  fk_RTOSUserMissID, fk_RTOSUserSchedOption),
#							 db_RTMissionSetup (fk_RTMissMiss),
#							 db_ScheduleSource (fk_SchdDataSrc);
#						         added constraints
#							 db_FilterRates (fk_FilterMissVCIDAPID).
#
#
#NOTES: This script should be run after TableBuilder_DPS is run. 
#*********************************************************************************
clear

if [ $# -ne 3 ]
then
   echo '************************************************************'
   echo 'Error, you must enter an Oracle UserID/Password, Log file,  '
   echo 'and the add (A) or drop (D) option                          '
   echo '************************************************************'
   exit
fi

echo ''

if [ "$3" = D -o "$3" = d ]
then
 echo '**********************************'
 echo 'Dropping referential integrity ...'
 echo '**********************************'
 sqlplus $1 << ENDORACLE >> $2

   ALTER TABLE db_AcqSessionCharacteristics 
            DROP CONSTRAINT fk_AcqSesDSID;
   ALTER TABLE db_AcqSessionCharacteristics 
            DROP CONSTRAINT fk_AcqSesDataSrc;

   ALTER TABLE db_AppDestDefaults  
            DROP CONSTRAINT fk_AppDestDefDSID; 
   ALTER TABLE db_AppDestDefaults  
            DROP CONSTRAINT fk_AppDestDefDestID; 
   ALTER TABLE db_AppDestDefaults  
            DROP CONSTRAINT fk_AppDestDefFEID;


   ALTER TABLE db_ChnlInformation
            DROP CONSTRAINT fk_ChnlInfoDataStr ;

   ALTER TABLE db_DDFTransmissionConfig 
            DROP CONSTRAINT fk_DDFMissionID ;

   ALTER TABLE db_DestinationCatalog
            DROP CONSTRAINT fk_DestFacility;

   ALTER TABLE db_EventLog 
            DROP CONSTRAINT fk_EventlogMessID ;

   ALTER TABLE db_FddiFELookup
            DROP CONSTRAINT fk_FddiResID;

   ALTER TABLE db_FilterRates
	    DROP CONSTRAINT fk_FilterMissVCIDAPID;

   ALTER TABLE db_FrameSyncSetup
            DROP CONSTRAINT fk_FSynSetDataStr ;

   ALTER TABLE db_FSFilterSetup
            DROP CONSTRAINT fk_FSSetupMisDat;

   ALTER TABLE db_FSFilterValue
            DROP CONSTRAINT fk_FSFilterTab;

   ALTER TABLE db_HST_PKSetup
	    DROP CONSTRAINT fk_PKHSTSetDataStr;

   ALTER TABLE db_MissionCatalog
            DROP CONSTRAINT fk_MissCatDataSrc ;

   ALTER TABLE db_MissionDataLine 
            DROP CONSTRAINT fk_DataLineMiss; 
   ALTER TABLE db_MissionDataLine 
            DROP CONSTRAINT fk_DataLineLine;

   ALTER TABLE db_MissionDataRate 
            DROP CONSTRAINT fk_DataRateMiss;
   ALTER TABLE db_MissionDataRate 
            DROP CONSTRAINT fk_DataRateDataRate ;

   ALTER TABLE db_MissionDataStreamIDCatalog 
            DROP CONSTRAINT fk_MissDStreamCatMiss ;

   ALTER TABLE db_MissionDestination
            DROP CONSTRAINT fk_DestMiss; 
   ALTER TABLE db_MissionDestination
            DROP CONSTRAINT fk_MissDestFacility;

   ALTER TABLE db_MissionStation
            DROP CONSTRAINT fk_StationMiss;
   ALTER TABLE db_MissionStation
            DROP CONSTRAINT fk_StationIDStat ;

   ALTER TABLE db_MissionThresholds
            DROP CONSTRAINT fk_MissThresID ;

   ALTER TABLE db_NetworkID
            DROP CONSTRAINT fk_NetworkResID ;

   ALTER TABLE db_PDPConfiguration
	    DROP CONSTRAINT fk_PDPConfResID;

   ALTER TABLE db_PEConfiguration
            DROP CONSTRAINT fk_PEConfigResId;

   ALTER TABLE db_PKSetup
            DROP CONSTRAINT fk_PKSetDataStr ;

   ALTER TABLE db_ProductSpec
            DROP CONSTRAINT fk_ProdSpecMisID ;

   ALTER TABLE db_ResourceConstituentDefaults  
            DROP CONSTRAINT fk_MissResDSID;
   ALTER TABLE db_ResourceConstituentDefaults  
            DROP CONSTRAINT fk_MissResResID; 
   ALTER TABLE db_ResourceConstituentDefaults  
            DROP CONSTRAINT fk_MissResFEID;

   ALTER TABLE db_RTOSConfig
	    DROP CONSTRAINT fk_RTOSConfResID;

   ALTER TABLE db_RTOSUserSelectionSpec
            DROP CONSTRAINT fk_RTOSUserMissID; 
   ALTER TABLE db_RTOSUserSelectionSpec
            DROP CONSTRAINT fk_RTOSUserSchedOption;

   ALTER TABLE db_RTMissionSetup
            DROP CONSTRAINT fk_RTMissMiss;

   ALTER TABLE db_RSBSSetup
            DROP CONSTRAINT fk_RSBSetDataStr ;

   ALTER TABLE db_RSDSetup
            DROP CONSTRAINT fk_RSDSetDataStr ;

   ALTER TABLE db_ScheduleSource
            DROP CONSTRAINT fk_SchdDataSrc;

   ALTER TABLE db_UserAccessLevel
            DROP CONSTRAINT fk_UALUserCat;

   ALTER TABLE db_UserLevel
            DROP CONSTRAINT fk_ULUserCat;
exit;
ENDORACLE

elif [ "$3" = A -o "$3" = a ]
then
   echo '********************************'
   echo 'Adding referential integrity ...'
   echo '********************************'
   sqlplus $1 << ENDORACLE >> $2
   ALTER TABLE db_AcqSessionCharacteristics 
            ADD CONSTRAINT fk_AcqSesDSID FOREIGN KEY
                (MissionID, DataStreamID) references 
                 db_MissionDataStreamIDCatalog(MissionID, DataStreamID) 
            ADD CONSTRAINT fk_AcqSesDataSrc FOREIGN KEY
                (DataSource) references db_DataSource(DataSource);

   ALTER TABLE db_AppDestDefaults  
            ADD CONSTRAINT fk_AppDestDefDSID FOREIGN KEY 
                (MissionID, DataStreamID) references 
                db_MissionDataStreamIDCatalog(MissionID, DataStreamID) 
            ADD CONSTRAINT fk_AppDestDefDestID FOREIGN KEY 
                (DestResourceID) references db_ResourceCatalog(ResourceID) 
            ADD CONSTRAINT fk_AppDestDefFEID FOREIGN KEY 
                (FEID) references db_ResourceCatalog(ResourceID); 

   ALTER TABLE db_ChnlInformation
            ADD CONSTRAINT fk_ChnlInfoDataStr FOREIGN KEY 
                (MissionID, DataStreamID) references 
                db_MissionDataStreamIDCatalog(MissionID, DataStreamID);

   ALTER TABLE db_DDFTransmissionConfig 
            ADD CONSTRAINT fk_DDFMissionID FOREIGN KEY 
                (MissionID) references db_MissionCatalog(MissionID); 

   ALTER TABLE db_DestinationCatalog
            ADD CONSTRAINT fk_DestFacility FOREIGN KEY 
                (DestFacilityId) references db_DestinationFacility(DestFacilityId);

   ALTER TABLE db_EventLog 
            ADD CONSTRAINT fk_EventlogMessID FOREIGN KEY 
                (MessageID) references db_Message(MessageID); 

   ALTER TABLE db_FddiFELookup 
            ADD CONSTRAINT fk_FddiResID FOREIGN KEY 
                (ResourceID) references db_ResourceCatalog(ResourceID); 

   ALTER TABLE db_FilterRates
	    ADD CONSTRAINT fk_FilterMissVCIDAPID FOREIGN KEY
	        (MissionID, VCID, APID) references 
		db_MissionThresholds(MissionID, VCID, APID);

   ALTER TABLE db_FrameSyncSetup
            ADD CONSTRAINT fk_FSynSetDataStr FOREIGN KEY 
                (MissionID, DataStreamID) references 
                db_MissionDataStreamIDCatalog(MissionID, DataStreamID);

   ALTER TABLE db_FSFilterSetup
            ADD CONSTRAINT fk_FSSetupMisDat FOREIGN KEY
                (MissionID, DataStreamID) references
                db_MissionDataStreamIDCatalog(MissionID, DataStreamID);

   ALTER TABLE db_FSFilterValue
            ADD CONSTRAINT fk_FSFilterTab FOREIGN KEY 
                (MissionID, DataStreamID, O_Order, FilterTableNum) references 
		db_FSFilterSetup(MissionID, DataStreamID, O_Order, FilterTableNum);

   ALTER TABLE db_MissionDataRate 
            ADD CONSTRAINT fk_DataRateMiss FOREIGN KEY 
                (MissionID) references db_MissionCatalog(MissionID) 
            ADD CONSTRAINT fk_DataRateDataRate FOREIGN KEY 
                (DataRate) references db_DataRate(DataRate);

   ALTER TABLE db_MissionDataStreamIDCatalog 
            ADD CONSTRAINT fk_MissDStreamCatMiss FOREIGN KEY 
                (MissionID) references db_MissionCatalog(MissionID); 

   ALTER TABLE db_MissionDestination
            ADD CONSTRAINT fk_DestMiss FOREIGN KEY 
                (MissionID) references db_MissionCatalog(MissionID) 
            ADD CONSTRAINT fk_MissDestFacility FOREIGN KEY 
                (DestFacilityId) references db_DestinationFacility(DestFacilityId);

   ALTER TABLE db_MissionStation
            ADD CONSTRAINT fk_StationMiss FOREIGN KEY 
                (MissionID) references db_MissionCatalog(MissionID) 
            ADD CONSTRAINT fk_StationIDStat FOREIGN KEY
                (StationID) references db_Station(StationID); 

   ALTER TABLE db_MissionThresholds
            ADD CONSTRAINT fk_MissThresID FOREIGN KEY
                (MissionID) references db_MissionCatalog(MissionID);

   ALTER TABLE db_NetworkID
            ADD CONSTRAINT fk_NetworkResID FOREIGN KEY 
                (ResourceID) references db_ResourceCatalog(ResourceID); 

   ALTER TABLE db_PDPConfiguration
            ADD CONSTRAINT fk_PDPConfResId FOREIGN KEY 
                (PDPID) references db_ResourceCatalog(ResourceID); 

   ALTER TABLE db_PEConfiguration
            ADD CONSTRAINT fk_PEConfigResId FOREIGN KEY
                (PEID) references db_ResourceCatalog(ResourceID);

   ALTER TABLE db_PKSetup
            ADD CONSTRAINT fk_PKSetDataStr FOREIGN KEY 
                (MissionID, DataStreamID) references 
                db_MissionDataStreamIDCatalog(MissionID, DataStreamID);

   ALTER TABLE db_ProductSpec
            ADD CONSTRAINT fk_ProdSpecMisID FOREIGN KEY 
                (MissionID) references db_MissionCatalog(MissionID); 

   ALTER TABLE db_ResourceConstituentDefaults  
            ADD CONSTRAINT fk_MissResDSID FOREIGN KEY 
                (MissionID, DataStreamID) references 
                db_MissionDataStreamIDCatalog(MissionID, DataStreamID) 
            ADD CONSTRAINT fk_MissResResID FOREIGN KEY 
                (DestResourceID) references db_ResourceCatalog(ResourceID) 
            ADD CONSTRAINT fk_MissResFEID FOREIGN KEY 
                (FEID) references db_ResourceCatalog(ResourceID); 

   ALTER TABLE db_RSBSSetup
            ADD CONSTRAINT fk_RSBSetDataStr FOREIGN KEY 
                (MissionID, DataStreamID) references 
                db_MissionDataStreamIDCatalog(MissionID, DataStreamID);

   ALTER TABLE db_RSDSetup
            ADD CONSTRAINT fk_RSDSetDataStr FOREIGN KEY 
                (MissionID, DataStreamID) references 
                db_MissionDataStreamIDCatalog(MissionID, DataStreamID);

   ALTER TABLE db_UserAccessLevel
            ADD CONSTRAINT fk_UALUserCat FOREIGN KEY
                (Usercategory) references db_UserCategory(UserCategory);

   ALTER TABLE db_UserLevel
            ADD CONSTRAINT fk_ULUserCat FOREIGN KEY
                (Usercategory) references db_UserCategory(UserCategory);
   exit;
ENDORACLE

else
 exit
fi
