#!/bin/csh -f

##############################################################################
#
# Filename         - $Workfile:   mk_arch_schema.csh  $
#
#                     Name              Date      Version     CR Number
# Author           - See revision history below.
# Last Update      - See revision history below.
#
# Project Name     - TAGTRAK(Oracle database)
#
# Source Code
# Identifier       - 0/3/300/8010/112
#
# Design Spec
# Section          -
#
# Contents         -
#     Build the Archive schema under the given username, which
#     must already exist as an Oracle user account.
#
# Language         - UNIX C-shell script
#
# Classification   - Commercial In Confidence
#
#          (c) Ultra Electronics Limited, 1997
#
##############################################################################

##############################################################################
# @(#)$Workfile:   mk_arch_schema.csh  $  $Revision:   1.7.1.4  $
#
# Revision History
#
# Note that user tagtrak is many users. For these users the user
# name is entered as the last line in the change description.
#
# $Log:   //Manfred/UltraTrakCore/Archives/Database/arch_schema/mk_arch_schema.csh-arc  $
# 
#    Rev 1.7.1.4   Jul 23 2008 14:49:30   dave.banks
# CA03166 Add a command to create triggers for FLIGHT_LOOP_DATA
# 
#    Rev 1.7.1.3   Oct 16 2007 14:22:46   gary.roberts
# CA02851 - Fix Core 9.17 ATS7 build issues
# 
#    Rev 1.7.1.2   19 Sep 2007 13:17:20   gary.roberts
# CA02743 - Fix Core 9.17 ATS3 build issues
# 
#    Rev 1.7.1.1   23 Aug 2007 17:33:10   dave.banks
# CA02607 - Dbinit changes for removal of arbk
# 
#    Rev 1.7.1.0   Aug 10 2007 14:33:28   robert.cooke
# CA02679
# 
#    Rev 1.7   Jul 02 2007 12:24:16   phil.burns
# Move 9.16.2 to trunk
# 
#    Rev 1.6.1.5   24 May 2007 16:18:56   dave.banks
# CA02526 - Minor dbinit errors
# 
#    Rev 1.6.1.4   May 11 2007 11:10:24   robert.cooke
# CA02444
# 
#    Rev 1.6.1.3   May 11 2007 11:08:18   robert.cooke
# CA02444
# 
#    Rev 1.6.1.2   09 May 2007 10:55:20   Dave.banks
# CA02369 - Merged from 9.13.0.0.0.7
# 
#    Rev 1.6.1.1   22 Mar 2007 17:05:16   gary.roberts
# CA02341 - BVIS Subheader on BAG and FLIGHT tables should be RAW datatype
# 
#    Rev 1.6.1.0   25 Jan 2007 12:03:14   peter.charalambous
# FALCP/CA/2271 - ZRH 9.16 Merge Database
# 
#    Rev 1.6   08 Nov 2006 11:41:58   ian.byass
# Move Core 9.14.1 to Trunk
# 
#    Rev 1.5.2.0.1.0   Oct 12 2006 16:41:30   dave.banks
# CA02029 Changes required to make BAG_TRACE_DATA a view
# 
#    Rev 1.5.2.0   Jun 01 2006 16:26:44   dave.banks
# UAS/FALCP/CA/01791 - Add option for Master Standby
# 
#    Rev 1.5   15 Nov 2005 10:31:02   david.rolfe
# Core 9.12 moved to tip
# 
#    Rev 1.4   19 Nov 2004 15:00:22   peter.charalambous
# FALCP/CA/1083 - Core fixes to Expired Schema issues with not displaying bag trace data via MWS
#
#    Rev 1.4   19 Nov 2004 12:43:28   peter.charalambous
# FALCP/CA/1083 - Core 9 fixes to Expired Schema issues with bag trace details not displaying in MWS Bag Trace Screen
#
#    Rev 1.4   19 Nov 2004 12:31:32   peter.charalambous
# FALCP/CA/1083 - Core 9 fixes to Expired Schema issues with bag trace data not displaying on bag trace screen
#
#    Rev 1.3   18 Nov 2004 12:15:28   peter.charalambous
# FALCP/CA/1074 - Altered some constraints which has been causing archive and backup to fail.
#
#    Rev 1.2   Sep 21 2004 12:04:44   mik.davis
# FALCP/CA/856
#
#    Rev 1.1   Oct 10 2003 10:25:32   peter.dawson
# FAS / FALCP / CA / 261
# Equipment Tracking for BARA Phase 2
#
#    Rev 1.0   Sep 22 2003 14:25:08   mik.davis
# Initial revision.
#
#    Rev 1.3   12 Sep 2002 18:08:28   tagtrak
# OR:
# CR: 15644
# DESC: BA BRS Segregation changes
#
#    Rev 1.2   12 Oct 1998 16:40:54   tagtrak
# OR: 12682 12683 12678 12654 12419 12265 12805 12285
# CR: 12682 12683 12678 12654 12419 12265 12805
# REV:
# Vienna enhancements/fixes for database V0.4.1E
# branch ATS revision moved to trunk.
# Andrew Walker
#
#    Rev 1.1.1.0   25 Sep 1998 12:35:54   tagtrak
# OR: 12682 12683 12678 12654 12419 12265 12285
# CR: 12682 12683 12678 12654 12419 12265
# REV:
# Vienna enhancements for Database 0.4.1
# Andrew Walker
#
#    Rev 1.1   02 Apr 1998 18:48:42   tagtrak
# OR:
# CR:
# REV:
# Initial Issue
#
#    Rev 1.0   26 Feb 1998 14:36:58   tagtrak
# OR:
# CR:
# REV:
# Initial Core product version
#
#    Rev 1.2   06 Nov 1997 13:48:12   tagtrak
# OR:
# CR:
# REV:
# Will use Instance identifier from caller's environment
#
#    Rev 1.1   28 Oct 1997 13:53:16   tagtrak
# date and time created 97/10/28 13:54:49 by andrew
#
##############################################################################


if ( $#argv != 2 ) then
	echo usage: $0 username passwd
	exit 1
endif

echo "Instance: " $ORACLE_SID
sleep 2

# Create tables
sqlplus $1/$2 << EOM
@tables/tables.sql arch;
EOM

# Issue grants for archive

grant_arch_to_live

# Remove foreign key constraints 

remove_fk_cons $1/$2

# Create synonyms

sqlplus $1/$2 << EOM
@tables/syns.sql
EOM

# Create the archive tables
../archive/create_archive_tables $1/$2

sqlplus $1/$2 << EOM
create synonym eql_id_s for live.eql_id_s;
@stored_procs/arch_procs.sql;
@views/all_views.sql;
EOM

../live_schema/grant_schema.ksh arch $2 temp

echo "Creating Archive Packages ..."

../archive/create_archive_pkgs $1/$2

echo "Creating grants to/from OPS schema ..."

../ops_schema/create_ops_grants $1/$2
../ops_schema/create_ops_synonyms $1/$2

sqlplus $1/$2 << EOM
@triggers/all_triggs.sql;
EOM

sqlplus $1/$2 <<EOM
@../arch_schema/stored_procs/no_archive_modify.sql
@../archive/triggers/arch_ARCHIVE_RULES_triggers.sql
@../archive/triggers/arch_ARCHIVE_SUB_RULES_triggers.sql
exit
EOM
make_trigs -b $1 ARCHIVE_PARAMS <<EOF
$2
EOF
make_trigs -b $1 FLIGHT_LOOP_DATA <<EOF
$2
EOF

sqlplus $1/$2 << EOM
grant select on flight_access to live;
grant select on lba_info to live;
GRANT UPDATE ON ARCH.FLIGHT_INFO_INBOUND TO TEMP;

exit;
EOM

