#!/bin/csh -f

##############################################################################
#
# Filename         - $Workfile:   mk_schema.csh  $
#
#                     Name              Date      Version     CR Number
# Author           - See revision history below.
# Last Update      - See revision history below.
#
# Project Name     - TAGTRAK(Oracle port)
#
# Source Code
# Identifier       - 0/3/300/8010/112
#
# Design Spec
# Section          -
#
# Contents         - Live schema creation script
#
# Language         - UNIX C-shell script
#
# Classification   - Commercial In Confidence
#
#          (c) Ultra Electronics Limited, 1997
#
##############################################################################

##############################################################################
# @(#)$Workfile:   mk_schema.csh  $  $Revision:   1.14.1.3  $
#
# 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:   P:/PVCSDATA/UltraTrakCore/Database/live_schema/mk_schema.csh-arc  $
# 
#    Rev 1.14.1.3   May 07 2008 12:17:28   michael.clark
# CA03094 - fixes for config data
# 
#    Rev 1.14.1.2   19 Sep 2007 13:17:16   gary.roberts
# CA02743 - Fix Core 9.17 ATS3 build issues
# 
#    Rev 1.14.1.1   23 Aug 2007 17:33:16   dave.banks
# CA02607 - Dbinit changes for removal of arbk
# 
#    Rev 1.14.1.0   31 Jul 2007 11:20:26   chris.sawer
# CA02658 - Add MWS+ stop/clear multiple passenger support
# 
#    Rev 1.14   Jul 02 2007 12:26:54   phil.burns
# Move 9.16.2 to trunk
# 
#    Rev 1.13.1.2   May 11 2007 11:11:48   robert.cooke
# CA02444
# 
#    Rev 1.13.1.1   22 Mar 2007 17:05:44   gary.roberts
# CA02341 - BVIS Subheader on BAG and FLIGHT tables should be RAW datatype
# 
#    Rev 1.13.1.0   Feb 13 2007 14:50:00   phil.burns
# UAS/FALCP/CA/2155 - Fix BSM views for master standby
# 
#    Rev 1.13   08 Nov 2006 11:46:06   ian.byass
# Move Core 9.14.1 to Trunk
# 
#    Rev 1.12.2.1   Jul 14 2006 09:07:46   dave.banks
# CA 01934 - Create casacde delete DBR triggers
# 
#    Rev 1.12.2.0   Jun 01 2006 16:21:00   dave.banks
# UAS/FALCP/CA/01791 - Add Master Standby option
# 
#    Rev 1.12   15 Nov 2005 10:34:36   david.rolfe
# Core 9.12 moved to tip
# 
#    Rev 1.11   13 Oct 2005 13:22:00   himanshu.saini
# CA01583
# 
#    Rev 1.10   Jul 18 2005 14:47:46   mik.davis
# FALCP/CA/1444 - An element of mk_schema.csh does not execute in sqlplus in oracle8i
# 
# Encapsulate the connection string in quotes when connecting as sysdba
# 
#    Rev 1.9   Apr 18 2005 10:07:14   david.rolfe
# 9.6 merge
# 
#    Rev 1.8.1.2   Mar 23 2005 14:16:16   chris.penny
# CA1169 Privileges to grant for ARCH and TEMP for compilation mws_tracking package in arch and temp.
# 
#    Rev 1.8.1.1   Mar 17 2005 15:29:36   yomi.kogbe
# CA1176
# 
#    Rev 1.7   05 Jan 2005 11:04:38   peter.charalambous
# FALCP/CA/1139 - Corrected syntax errors in script for Core 9.4
#
#    Rev 1.6   Dec 16 2004 16:39:26   Ian.byass
# UAS/FALCP/CA/01134 Reflighting Gateway
#
#    Rev 1.5   Sep 21 2004 13:59:16   mik.davis
# FALCP/CA/856
#
#    Rev 1.4   Sep 21 2004 12:04:44   mik.davis
# FALCP/CA/856
#
#    Rev 1.3   Sep 21 2004 11:04:52   mik.davis
# FACLP/CA/856 -
#
#    Rev 1.2   Jan 28 2004 14:35:34   GGC
# CA/480 - Added code to grant access to the tracking packages
#
#    Rev 1.1   Sep 29 2003 13:51:02   mik.davis
# FALCP/CA/174 - Archive n Backup
#
#    Rev 1.0   Sep 22 2003 14:26:00   mik.davis
# Initial revision.
#
#    Rev 1.3   Jun 19 2003 11:25:58   mik.davis
# FAS/FALCP/CA/053: Invalidate a flight's HBMD if its flight status is rolled back from 'PD'
#
# FAS/FALCP/CA/056: Bug fixes to copy_pax.sql, proc8.sql, arrival_flight_load_popup_v.sql
#
# FAS/FALCP/CA/013: Flight Closeout
#
# FAS/FALCP/CA/045: Carrier code Lookup fixes for Bara FIDS
#
# FAS/FALCP/CA/058: For fetching inbound flight states from the database.
#
#    Rev 1.6   21 Jun 2001 14:06:52   tagtrak
# OR:
# CR:
# Re-baseline version 4.0.0 to include patches.
#
#    Rev 1.4   12 Oct 1998 16:42:02   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.3.1.0   25 Sep 1998 12:34:14   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.3   02 Apr 1998 19:29:16   tagtrak
# OR:
# CR:
# REV:
# Initial Issue
# See label for concession
#
#    Rev 1.2   06 Nov 1997 13:45:18   tagtrak
# OR:
# CR:
# REV:
# Will use instance identifier from the environment
#
#    Rev 1.1   22 Oct 1997 14:42:34   tagtrak
# OR:
# CR:
# REV:
# Added DO reference number to header
#
#    Rev 1.0   21 Oct 1997 14:25:28   tagtrak
# OR:
# CR:
# REV:
# Initial check-in
#
##############################################################################


#
# Build the Live schema under the given username, which
# must already exist as an Oracle user account.
#

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

echo "Instance = " $ORACLE_SID
sleep 2

set sys_username = "sys"
set sys_password = "change_on_install"
set arch_username = "arch"
set arch_password = "arch"

###############################################################################
#NOTE:any grants not requiring sysdba login should be put in appropriate      #
#script i.e live_schema/tables/grnts.sql or live_schema/stored_procs/grnts.sql#
#and not in this script                                                       #
###############################################################################
#
# Issue grants
#
echo "Issue System Grants ..." 
sqlplus -s "$sys_username/$sys_password as sysdba" <<EOT
whenever oserror exit sql.oscode rollback
whenever sqlerror continue
set doc off
set pagesize 0;

/* * SYS schema */

grant create table to live;
grant create view to live;
grant execute on DBMS_ALERT to live;
grant execute on DBMS_LOCK to arch;
grant execute on DBMS_LOCK to live;
grant all on  dbms_job to live;
grant all on dba_jobs to live;
grant create type to live;
grant create library to live;
grant select on v_\$instance to live;
grant select on dba_objects to live;
quit;
EOT

#
# Use Oracle SQL*Plus to execute the script.
#

echo "Create Database Tables ..."

sqlplus $1/$2 << EOM
@tables/tables.sql live;
exit;
EOM


# Create the archive tables 

echo "Creating archive tables..."

../archive/create_archive_tables $1/$2

if ( x$APPN_MASTER_SBY == xY ) then

    # For Master Standby we need to create sequences starting with a number based on the node number

    echo "Creating Master Standby sequences ..."

    create_master_sby_live_seqs $1/$2

else

    # Not Master Standby - the sequence start values are standard

    sqlplus $1/$2 << EOM
    @initialise/all.sql;
    exit;
EOM
endif

echo "Creating types..."

sqlplus $1/$2 << EOM
@types/all_types.sql
exit;
EOM

echo "Creating Stored Procedures and Packages ..."

cd stored_procs
sqlplus $1/$2 << EOM
@proc1.sql
exit;
EOM
sqlplus $1/$2 << EOM
@proc2.sql
exit;
EOM
sqlplus $1/$2 << EOM
@proc3.sql
exit;
EOM
sqlplus $1/$2 << EOM
@proc4.sql
exit;
EOM
sqlplus $1/$2 << EOM
@proc5.sql
exit;
EOM
sqlplus $1/$2 << EOM
@proc6.sql
exit;
EOM
sqlplus $1/$2 << EOM
@proc7.sql
exit;
EOM
sqlplus $1/$2 << EOM
@proc8.sql
exit;
EOM


echo "Creating Archive Packages ..."

../../archive/create_archive_pkgs $1/$2

echo "Creating live package and stored procedure grants ..."

sqlplus $1/$2 << EOM
@grnts.sql arch
@grnts.sql temp
exit;
EOM

cd ..
sqlplus $1/$2 << EOM
@views/all_views.sql;
exit;
EOM

echo "Creating live table and view grants..."

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

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

../ops_schema/create_ops_grants $1/$2

echo "Creating synonyms for the OPS schema ..."

../ops_schema/create_ops_synonyms $1/$2

# Finally create the live triggers

create_live_triggers <<EOF
${2}
EOF

