# V. Sadilovskiy on 12/18/2003
#
# Modified 05/21/2004 (bug #1081453): Saving in UNIX format
#
# This utility locks a temporary migraion schema to in favor of pdm account from state of
# transition of data from Pro/INTRALINK schema to EPM. Name of migration schema is:
# "${PDM_USER}_migration". Where ${PDM_USER} is the name of Pro/INTRALINK schema.
# Migration schema name should be specified in the parameters.
#
# It restores normal operation of Pro/INTRALINK dataserver.
#
# Required environment variables and parameters:
# PROI_HOME       - environment variable that specifies Pro/INTRALINK DS home folder
# ORA_SYS_PWD     - environment variable that specifies system's schema password
# <1st parameter> - an access password to the migration schema.
#

# 1. drops synonyms and tables in migration account
# 2. locks migration account and unlocks pdm account
# 3. revokes select grants from migration account

#########################################################################
#  Procedure:
#      Sqlplus - run SQL*Plus with connection by $UserPwd and executes
#                   all commands in $CmdTxt.
#
#  Input parameters:
#      UserPwd - "username/password"
#      CmdTxt  - commands to execute
#
#  Returned Value:
#      Return SQL*Plus output.
#
proc SqlPlusDbaRmt {CmdTxt} {
    global db_props errorCode ilnk_platform

    set ProcName SqlPlusDbaRmt
    DebugOutput "Procedure SqlPlusDbaRmt (RunSqlplus wrapper) started."

    catch {RunSqlplus "$db_props(SystemConnectStr)" $CmdTxt} sql_out
    return $sql_out
}

proc disableMigrationSchema {pdm_schema_user mig_schema} {
    global db_props

    # Unlock migration account and lock PDM account
    set SqlCmd "
    set termout on
    set echo off
    set serveroutput on
    set feedback off
    set pages 0
    set lines 300

    whenever sqlerror exit 1;
    alter user ${mig_schema} account lock;
    alter user ${pdm_schema_user} account unlock;
"
    WriteStdAndLog "Locking ${mig_schema} schema ... "
    set sql_out [SqlPlusDbaRmt ${SqlCmd}]
    DebugOutput "sql_out : ${sql_out}" 2

    set err_rep [OraErrorReport $sql_out ""]

    if {$err_rep != ""} {
        set ErrMsg "\nError during running sqlplus:"
        WriteErrorLog "$ErrMsg\n\n${err_rep}\n$sql_out"
        return -code "error" "createMigraitonSchema: $ErrMsg"
    }
    WriteStdAndLog " OK\n"
}

proc cleanMigrationSchema {pdm_schema_user mig_schema} {
    # Generating synonym list
    # Generating table list
    set SqlDropCmd "
    set termout on
    set echo off
    set serveroutput on
    set feedback off
    set pages 0
    set lines 300

    select \'drop synonym ${mig_schema}.\'||object_name||\';\'
      from dba_objects
     where owner=upper(\'${mig_schema}\') and object_type in (\'SYNONYM\');

    select \'drop table ${mig_schema}.\'||object_name||\';\'
      from dba_objects
     where owner=upper(\'${mig_schema}\') and object_type in (\'TABLE\')
       and object_name like '%TOWCSTATUS%';
"

    WriteStdAndLog "Generating drop synonyms from migration schema ${mig_schema} ... "
    set sql_out [SqlPlusDbaRmt ${SqlDropCmd}]
    set SqlDropCmd $sql_out

    DebugOutput "sql_out : ${sql_out}" 2

    set err_rep [OraErrorReport $sql_out ""]

    if {$err_rep != ""} {
        set ErrMsg "\nError during running sqlplus:"
        WriteErrorLog "$ErrMsg\n\n${err_rep}\n$sql_out"
        return -code "error" "createMigraitonSchema: $ErrMsg"
    }
    WriteStdAndLog " OK\n"

    # Dropping synonyms and private objects
    set SqlCmd "
    set termout on
    set echo off
    set serveroutput on
    set feedback off
    set pages 0
    set lines 300

    -- Dropping synonyms
    -- Dropping private objects
    $SqlDropCmd
"
    WriteStdAndLog "Dropping private objects and synonyms from migration schema ${mig_schema} ... "
    set sql_out [SqlPlusDbaRmt ${SqlCmd}]
    DebugOutput "sql_out : ${sql_out}" 2

    set err_rep [OraErrorReport $sql_out "ORA-00942"]

    if {$err_rep != ""} {
        set ErrMsg "\nError during running sqlplus:"
        WriteErrorLog "$ErrMsg\n\n${err_rep}\n$sql_out"
        return -code "error" "createMigraitonSchema: $ErrMsg"
    }
    WriteStdAndLog " OK\n"
}

proc revokeGrantsFromMigrationSchema {pdm_schema_user mig_schema} {
    global db_props

    # Generating Grant list
    set SqlCmd "
    set termout on
    set echo off
    set serveroutput on
    set feedback off
    set pages 0
    set lines 300

    select \'revoke select on ${pdm_schema_user}.\'||object_name||\' from ${mig_schema};\'
      from dba_objects
     where owner=upper(\'${pdm_schema_user}\') and object_type in (\'TABLE\',\'VIEW\');
"

    WriteStdAndLog "Generating revoke statements for migration schema ${mig_schema} ... "
    set sql_out [SqlPlusDbaRmt ${SqlCmd}]
    DebugOutput "sql_out : ${sql_out}" 2

    set err_rep [OraErrorReport $sql_out ""]

    if {$err_rep != ""} {
        set ErrMsg "\nError during running sqlplus:"
        WriteErrorLog "$ErrMsg\n\n${err_rep}\n$sql_out"
        return -code "error" "createMigraitonSchema: $ErrMsg"
    }
    WriteStdAndLog " OK\n"

    # Opening PDM account to grant select privileges
    BeginSchemaUpdate
    WriteStdAndLog "Revoking grants for migration schema ${mig_schema} ... "
    set SqlCmd $sql_out
    set sql_out [SqlPlus $db_props(PdmConnectStr) $SqlCmd ]
    DebugOutput "sql_out : ${sql_out}" 2

    # Closing PDM account
    EndSchemaUpdate
    set err_rep [OraErrorReport $sql_out "ORA-01927"]

    if {$err_rep != ""} {
        set ErrMsg "\nError during running sqlplus:"
        WriteErrorLog "$ErrMsg\n\n${err_rep}\n$sql_out"
        return -code "error" "createMigraitonSchema: $ErrMsg"
    }
    WriteStdAndLog " OK\n"
}

if {![info exists env(TCL_AUTO_PATH)]} {
    puts "Please, set TCL_AUTO_PATH environment variable properly."
    return
}

lappend auto_path $env(TCL_AUTO_PATH)
ilnk_init 1
lappend auto_path "$ilnk_platform(DatabaseDir)${PathDlmtr}Tcl"

if {![info exists env(PROI_INHOUSE_INSTALL)] && ![info exists env(PROI_SYS_PWD)]} {
    puts "Please, set PROI_SYS_PWD environment variable."
    exit 1
} elseif {[info exists env(PROI_SYS_PWD)]} {
    set ProiPasswd $env(PROI_SYS_PWD)
}

if {(![info exists env(PROI_INHOUSE_INSTALL)] || $env(PROI_INHOUSE_INSTALL) == "customer") && $argc == 0} {
    set mig_schema_name $db_props(PdmUser)_migration
} elseif {$argc == 1 && [info exists env(PROI_INHOUSE_INSTALL)] && $env(PROI_INHOUSE_INSTALL) == "developer"} {
    set mig_schema_name [lindex $argv 0]
} else {
    puts "Incorrect parameter number."
    exit 1
}

if {![info exists db_props(SystemConnectStr)] && ![info exists env(ORA_SYS_PWD)]} {
    puts "Please, set ORA_SYS_PWD environment variable."
    exit 1
} elseif {![info exists db_props(SystemConnectStr)]} {
    set db_props(SystemConnectStr) "system/$env(ORA_SYS_PWD)@$db_props(ServiceName)"
}

SetupLogs "EPM Migration Installation." UPDATE
set LogFile "$ilnk_platform(LogDir)${PathDlmtr}epm_migration_schema.log"
if {![catch {package require ProILogger}]} {
	ProILogger::addLogFile "$LogFile" "details" "append"
	ProILogger::setLogTiming "$LogFile" "TRUE"
}
WriteStdAndLog "\n##############################################################################\n"
WriteStdAndLog "Enabling Pro/I for migration. Log file located at: $LogFile"
WriteStdAndLog "\n##############################################################################\n"

cd $ilnk_platform(DatabaseDir)

# 1. drops synonyms and tables in migration account
cleanMigrationSchema $db_props(PdmUser) $mig_schema_name

# 2. locks migration account and unlocks pdm account
disableMigrationSchema $db_props(PdmUser) $mig_schema_name

# 3. revokes select grants from migration account
revokeGrantsFromMigrationSchema $db_props(PdmUser) $mig_schema_name
