# V. Sadilovskiy on 12/18/2003
#
# Modified 05/21/2004 (bug #1081453): Saving in UNIX format
#
# This utility creates a temporary migraion schema to assist with 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 and password of the schema should be specified in the parameters.
#
# It also restricts everyone from using Pro/INTRALINK client. To unlock - use
# enable_il_from_migration.tcl
#
# 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.
#

#
# Procedure performs these steps
#
# 1. add local domain entry in intralink
# pdm_domain table if it is not there already
#
# 2. populate synonyms to point to required tables in ${PDM_USER} schema
#
# 3. enabling a migration schema ${PDM_USER}_migration/<input_migration_schema_password>@
# ${PDM_ORACLE_SERVER} and locking PDM user
#

#########################################################################
#  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
}

#################################################################
#  setLocalDomain - Configuring and setting local domain name and ID
#
proc setMigrationDomain {DomainID DomainName} {
    global db_props ilnk_platform

    set ReplConnectString $db_props(ReplConnectStr)
    set PdmUser $db_props(PdmUser)

    set SqlCmd "
    set termout on
    set echo off
    set serveroutput on
    set feedback off
    set pages 0
    set lines 300

    alter session set current_schema=$db_props(PdmUser);

    -- special script created for migration
    -- objective: creating a local domain automatically.
    -- Lijun 12/29/03

    set serveroutput on size 1000000
    set pages 999
    set verif off
    set recsep off
    set lines 160

    DECLARE
    v_domainid      NUMBER := $DomainID;
    v_domainName    varchar2(1024);
    v_count         NUMBER;
    v_protocol      NUMBER;

    BEGIN

        v_domainid := $DomainID;
        v_domainName := \'$DomainName\';

    	SELECT count(*) INTO v_count  FROM pdm_domain
    		WHERE DOMISLOCAL = 0;

    	IF(v_count = 1) THEN
            dbms_output.put_line('There is already a local domain.');
    		return;
    	END IF;

    	IF(v_count >1 ) THEN
            dbms_output.put_line('Error! There are more than one local domains existing.');
    		return;
    	END IF;

    	BEGIN
    		SELECT PROTOCOLID INTO v_protocol
    		FROM PDM_PROTOCOL
    		WHERE PROTOCOLNAME = 'protocol_intralink_standalone';

    	EXCEPTION WHEN NO_DATA_FOUND THEN
            -- if it was not there, insert one...
            INSERT INTO PDM_PROTOCOL(protocolid, protocolname, CreatedBy, CreatedOn)
            VALUES (PDM_PROTOCOL_SEQ.nextval, 'protocol_intralink_standalone', 'SYSTEM', sysdate)
            RETURN protocolid INTO v_protocol;
    	END;

    	SELECT count(*) INTO v_count
    		FROM pdm_domain
    		WHERE DOMAINID = v_domainid OR
    		      DOMNAME = v_domainName;

    	IF(v_count != 0) THEN
            dbms_output.put_line('There is already a non local domain with the same name or ID');
    		return;
    	END IF;

    	-- it seems to be ok now.

    	INSERT INTO pdm_domain(instdomid, domainid, domname,
    			       domislocal, createdby, createdon,
    			       protocolid, domonline)
    	VALUES(PDM_DOMAIN_SEQ.NEXTVAL, v_domainid, v_domainName,
    		0, 'SYSTEM', SYSDATE, v_protocol, 0);

    	return;
    EXCEPTION WHEN OTHERS THEN
        dbms_output.put_line(sqlerrm);
    END;
/
"
    WriteStdAndLog "Setting local domain ... "
    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" "setMigrationDomain: $ErrMsg"
    }

    WriteStdAndLog "OK\n"
}

proc lockPDMSchema {pdm_schema_user} {
    global db_props

    # 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 $pdm_schema_user account lock;
"
    WriteStdAndLog "Locking $pdm_schema_user schema in $db_props(ServiceName) ... "
    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" "lockPDMSchema: $ErrMsg"
    }
    WriteStdAndLog " OK\n"
}

proc enableMigrationSchema {mig_schema} {
    global db_props

    # Unlock migration 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 unlock;
"
    WriteStdAndLog "Unlocking $mig_schema schema in $db_props(ServiceName) ... "
    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 createMigrationSP { scriptPath } {

    global db_props

    WriteStdAndLog "Creating stored procedures required for PIVSpecifier from pdm_oqlfunc.sql ..."
    catch { RunSqlplus $db_props(migschemaConnectStr) @$scriptPath/pdm_oqlfunc.sql} 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" "Compiling stored procedures required for PIVSpecifier from pdm_oqlfunc.sql: $ErrMsg"
    }
    WriteStdAndLog " OK\n"
    WriteStdAndLog "Creating preprocess stored procedure  ..."
    catch { RunSqlplus $db_props(migschemaConnectStr) @$scriptPath/mig_preprocess.sql} sql_out
    DebugOutput "sql_out : ${sql_out}" 2
#   puts $sql_out

    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" "Compiling preprocess stored procedure from mig_preprocess.sql: $ErrMsg"
    }

    WriteStdAndLog " OK\n"

    WriteStdAndLog "Recreate stopresumestatus table  ..."
    set SqlCmd "
    drop table stopresumestatus;
    create table StopResumeStatus ( WCDBLinkName varchar2(80), Status number );"
    catch { RunSqlplus $db_props(migschemaConnectStr) ${SqlCmd}} sql_out
    DebugOutput "sql_out : ${sql_out}" 2

    #
    # Ignore following error
    # ORA-00942: table or view does not exist
    #

    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 populateMigrationSchema {pdm_schema_user mig_schema mig_passwd} {
    global db_props env

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

    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%';

    select \'drop synonym ${mig_schema}.\'||object_name||\';\'||chr(10)||
           \'create synonym ${mig_schema}.\'||object_name||\' for ${pdm_schema_user}.\'||object_name||\';\'
      from dba_objects
     where owner=upper(\'${pdm_schema_user}\') and object_type in (\'TABLE\',\'VIEW\')
           and object_name not like '%TOWCSTATUS%';
"

    WriteStdAndLog "Generating object synonyms for migration schema ${mig_schema} ... "
    set sql_out [SqlPlusDbaRmt ${SqlCmd}]
    set SqlCmd $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"

    # Creating migration schema account
    set SqlCmd "
    set termout on
    set echo off
    set serveroutput on
    set feedback off
    set pages 0
    set lines 300

    -- Create Migration account
    create user ${mig_schema} identified by $mig_passwd;
    alter user ${mig_schema} identified by $mig_passwd default tablespace PDM_TABLE temporary tablespace TEMP;
    alter user ${mig_schema} account lock;
    grant connect, resource, create table to ${mig_schema};

    -- Dropping private objects
    -- Create required synonyms
    $SqlCmd
"
    WriteStdAndLog "Preparing migration schema ${mig_schema} ... "
    set sql_out [SqlPlusDbaRmt ${SqlCmd}]
    DebugOutput "sql_out : ${sql_out}" 2

    set err_rep [OraErrorReport $sql_out [list "ORA-01920" "ORA-01434" "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"

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

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

    WriteStdAndLog "Generating object grants 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

    # following for inhouse only
    if {[info exists env(PROI_INHOUSE_INSTALL)] && $env(PROI_INHOUSE_INSTALL) == "developer"} {
        set SqlCmd "
        alter user $pdm_schema_user account unlock;

    "
        set sql_tmp_out [SqlPlusDbaRmt ${SqlCmd}]
        DebugOutput "sql_out : ${sql_tmp_out}" 2

        set err_rep [OraErrorReport $sql_tmp_out ""]

        if {$err_rep != ""} {
            set ErrMsg "\nError during running sqlplus:"
            WriteErrorLog "$ErrMsg\n\n${err_rep}\n$sql_tmp_out"
            return -code "error" "createMigraitonSchema: $ErrMsg"
        }
    }
    # end of inhouse part

    WriteStdAndLog "Providing 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-01920"]

    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."
    return
} 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" || $env(PROI_INHOUSE_INSTALL) == "developer") && $argc == 1} {
	if {[info exists env(MIGUSER)] && $env(PROI_INHOUSE_INSTALL) == "developer" } {
		set mig_schema_name $env(MIGUSER)
	} else {
		set mig_schema_name $db_props(PdmUser)_migration
	}
	    set mig_schema_pass [lindex $argv 0]
} else {
    puts "Incorrect parameter number."
    return
}

if {![info exists db_props(SystemConnectStr)] && ![info exists env(ORA_SYS_PWD)]} {
    puts "Please, set ORA_SYS_PWD environment variable."
    return
} elseif {![info exists db_props(SystemConnectStr)]} {
    set db_props(SystemConnectStr) "system/$env(ORA_SYS_PWD)@$db_props(ServiceName)"
}
set db_props(migschemaConnectStr) "$mig_schema_name/$mig_schema_pass@$db_props(ServiceName)"
if {[file pathtype [info script]] == "absolute"} {
    set scriptPath [file dirname [info script]]
} else {
    set scriptPath [file dirname "[pwd]/[info script]"]
}

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 "Disabling Pro/I for migration. Log file located at: $LogFile"
WriteStdAndLog "\n##############################################################################\n"

cd $ilnk_platform(DatabaseDir)

# 0. Check if we already locked PDM account
if {![info exists env(PROI_INHOUSE_INSTALL)] || $env(PROI_INHOUSE_INSTALL) != "developer"} {
	WriteStdAndLog "Checking if account $db_props(PdmUser) is disabled ... "
	set SqlCmd "select 1 from dual"
	catch {SqlPlus $db_props(PdmConnectStr) $SqlCmd} sql_out
	DebugOutput "sql_out : ${sql_out}" 2

	if {[string first "ORA-28000" $sql_out] >= 0} {
		WriteStdAndLog "Account $db_props(PdmUser) is already disabled.\n"
		return
	}
	WriteStdAndLog " OK\n"
}


# 1. add local domain entry in intralink
# pdm_domain table if it is not there already
set CSID [generateCSID]
set CSName [exec hostname]
setMigrationDomain $CSID $CSName

# 2. populate synonyms to point to required tables in ${PDM_USER} schema
populateMigrationSchema $db_props(PdmUser) $mig_schema_name $mig_schema_pass

# 3. create a migration schema ${PDM_USER}_migration/<input_migration_schema_password>@
# ${PDM_ORACLE_SERVER} and set proper state flag
enableMigrationSchema $mig_schema_name

#4. compile the preprocess stored procedure
createMigrationSP $scriptPath

#5 lock PDM account
lockPDMSchema $db_props(PdmUser)
