#!/bin/sh
#
#script: move_mars_attachments
#Author: ken
#Date: 10:37:54 2015-06-05
#
usage(){
    echo "$0 DBTYPE(PGSQL|ORACLE|SQLSERVER) DBHOST DBNAME USERNAME PASSWORD"
    echo "PASSWORD can be omitted only for PGSQL"
    exit
}

ARGU_COUNT=$#

if [ $ARGU_COUNT -lt 4 ]
then
	usage
fi

DBTYPE=$1
DBHOST=$2
DBNAME=$3
USERNAME=$4
PASSWORD=$5

#
# sql statement for pgsql and oracle
#
STAT_PG_ORACLE="SELECT 'mv ' || c.domain_id || '/' || c.company_id || '/mars/file/' || a.old_attach_obj_key || ' ' || c.domain_id || '/' || c.company_id || '/attachments/' ||  a.new_attach_obj_key  FROM company c,temp_marsattach_to_attach a, mars_attachment m WHERE m.attach_obj_key=a.old_attach_obj_key AND c.company_key=m.company_key;"

#
# sql statement for sql server
#
STAT_SQLSERVER="SELECT 'mv ' + c.domain_id + '/' + c.company_id + '/mars/file/' + LTRIM(STR(a.old_attach_obj_key)) + ' ' + c.domain_id + '/' + c.company_id + '/attachments/' + LTRIM(STR(a.new_attach_obj_key))  FROM company c,temp_marsattach_to_attach a, mars_attachment m WHERE m.attach_obj_key=a.old_attach_obj_key AND c.company_key=m.company_key;"

need_password(){
	if [ $ARGU_COUNT -ne 5 ]
	then
		usage
	fi
}

run_in_pgsql(){
	psql -c "$STAT_PG_ORACLE" $DBNAME -h $DBHOST -U $USERNAME
}

run_in_oracle(){
	need_password
        echo "$STAT_PG_ORACLE" | sqlplus -s $USERNAME/$PASSWORD@$DBHOST/$DBNAME
}

run_in_sqlserver(){
        need_password
        Sqlcmd -U $USERNAME -P $PASSWORD -S $DBHOST -d $DBNAME -Q "$STAT_SQLSERVER"
}

case $DBTYPE in
	"PGSQL")
		run_in_pgsql
		;;
	"ORACLE")
		run_in_oracle
		;;
	"SQLSERVER")
		run_in_sqlserver
		;;
esac
	
