gps.docker/res/dacpacentrypoint.sh

sqlcmd="/opt/mssql-tools/bin/sqlcmd"
if [ -z "$1" ]; then
	echo "Usage: docker-compose run dbutils function"
	echo "     where function = 'gps-databases' for loading Databases. Requires second parameter with databases and optionally a branch"
	echo "                      gps.database.dataaccesslayer GPS.Database.DataAccessLayer for Data Access Layer and optionally a branch"
	echo "                      'seed' 'Database' for seeding data"
	echo "                      'bash' for shell"
	echo "						shrink db to shrink database"
elif [ "$1" = "bash" ]; then
	bash
elif [ "$1" = "args" ]; then
	echo "Got to args"
	sleep 10

	$sqlcmd -S "${HOST}" -U sa -P $SA_PASSWORD -Q "
		IF NOT EXISTS (select loginname from master.dbo.syslogins where name = 'gpspl')
		BEGIN
			CREATE LOGIN gpspl WITH PASSWORD='${SA_PASSWORD}', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
			ALTER SERVER ROLE sysadmin ADD MEMBER gpspl;
		END;

		IF NOT EXISTS (select loginname from master.dbo.syslogins where name = 'usrSPCaller')
		BEGIN
			CREATE LOGIN usrSPCaller WITH PASSWORD=N'1qazxsw23EDC'; 
		END; "


	for args in ${!DACPAC*}
	do
		arg=${!args}
		bash /entrypoint.sh $arg
	done
elif [ "$1" = "gps-databases" ] || [ "$1" = "gps.clearing" ]  || [ "$1" = "gps.database.dataaccesslayer" ] || [ "$1" = "gps.databases.alexissecure" ] ; then
	if [ -z "$2" ]; then
		echo "Usage: docker-compose run dbutils function"
		echo "     where function = 'gps-databases' for loading Databases. Requires second parameter with databases and optionally a branch"
		echo "                      gps.database.dataaccesslayer GPS.Database.DataAccessLayer for Data Access Layer and optionally a branch"
		echo "                      'seed' 'Database' for seeding data"
		echo "                      'bash' for shell"
		exit 1
	fi

	echo "Performing $2 migration"
	branch="develop"
	if [ ! -z $3 ]; then
		branch=$3
	fi
	if [ ! -d $1 ]; then
		git clone git@bitbucket.org:globalprocessing/$1.git --single-branch --branch $branch
	fi

	DBs="$2"
	for db in ${DBs//,/ }
	do
		if  [ "$1" = "gps.clearing" ]; then
			cd /root/$1/Databases/$db
		else
			cd /root/$1/$db
		fi
		bash /tmp/altersqlproj.sh
		sqlproj=`basename *$db*.sqlproj .sqlproj`
		echo "Building dacpac for $sqlproj"		
		dotnet build $sqlproj.sqlproj /p:NetCoreBuild=true /p:NETCoreTargetsPath="/root/.azuredatastudio/extensions/microsoft.sql-database-projects-0.12.0/BuildDirectory" /p:Configuration=Release /p:OutputPath=/dacpac /m:2 /clp:ErrorsOnly
		tdn=$db
		if [ "$db" = "Alexis"  ] || [ "$db" = "GPS.Database.DataAccessLayer.Tier1" ] || [ "$db" = "NGA_Processing_Alexis"  ]; then
			tdn="AlexisPTSTest"
		elif [ "$db" = "AlexisToken" ] ; then
			tdn="AlexisPTSTokenTest"
		elif [ "$db" = "GPS.Database.DataAccessLayer.Tier2" ] ; then
			tdn="Tier2"
		fi
		mkdir /tmp/$db
		
		pr=""
		if [ -f PublishProfiles/${db}AWS.publish.xml ]; then
			pr="/pr:PublishProfiles/${db}AWS.publish.xml"
		fi 

		dacpac=/dacpac/$sqlproj.dacpac
		/opt/sqlpackage/sqlpackage /a:Script /tsn:"${HOST}" /tdn:$tdn /tu:sa /tp:$SA_PASSWORD /sf:$dacpac /op:/tmp/$db/$db.sql $pr
		sed -i -e 's/^:on error exit/:on error ignore/' -e 's/\[$(LoggingServer)\].//' -e 's/\[$(SupplementalData)\].//' /tmp/$db/$db.sql

		# unzip -d /tmp/$db $dacpac predeploy.sql
		# if [ -f /tmp/$db/predeploy.sql ]; then
		# 	$sqlcmd -S "${HOST}" -U sa -P $SA_PASSWORD -d $tdn -i /tmp/$db/predeploy.sql
		# fi

		$sqlcmd -S "${HOST}" -U gpspl -P $SA_PASSWORD -i /tmp/$db/$db.sql

		# unzip -d /tmp/$db $dacpac postdeploy.sql
		# if [ -f /tmp/$db/postdeploy.sql ]; then
		# 	$sqlcmd -S "${HOST}" -U sa -P $SA_PASSWORD -d $tdn -i /tmp/$db/postdeploy.sql
		# fi
		rm -rf /tmp/$db

		if [ "$db" = "AlexisToken" ] ; then
			$sqlcmd -S "${HOST}" -U gpspl -P $SA_PASSWORD -d AlexisPTSTokenTest -Q "
			ALTER Procedure [dbo].[PR_GetKeyData] (@Path Int) 
			AS
			BEGIN
				DECLARE @file_contents NVARCHAR(4000)
				If @Path = 1
					SELECT @file_contents = N'+UAdKPLjoSG0lHPnBLVOZoCycNc2gBI/MY/M2HGI1qK5Q1hTJ5enLJKhKBrU8/Xa'
				Else If @Path = 2
				Begin
					SELECT @file_contents = N'17CC97140E479DF48CF3FEF88ADE4F8E'
				End
				SELECT @file_contents Data
			End"
			$sqlcmd -S "${HOST}" -U gpspl -P $SA_PASSWORD -d AlexisPTSTokenTest -Q "
			ALTER Procedure [dbo].[PR_GetEncryKey]
			@filecontents varchar(200) OUTPUT
			AS
			BEGIN
				SELECT @filecontents = N'17CC97140E479DF48CF3FEF88ADE4F8E'
			End"			
		fi
	done
elif [ "$1" = "seed" ]; then
	for file in /SeedData/$2/*.sql
	do 
		echo "Processing $file file.."
		$sqlcmd -S "${HOST}" -U gpspl -P $SA_PASSWORD -d $2 -i $file
	done
elif [ "$1" = "bcpdb" ]; then
	AlexisTables="ProductType,CurrencyMaster,Cards,Transaction,Transaction_Int"
	bcpdb=$2
	if [ $# -gt 1 ]; then
  		AlexisTables=$3
	fi
	bcpdir=$BCPDIR
	for table in ${AlexisTables//,/ }
	do
		if [ -d "/bcp" ]; then
			bcpdir=/bcp
		else
			/opt/mssql-tools/bin/bcp $bcpdb.dbo.[${table}] format nul -f $bcpdir/${table}.fmt -n -S $DATA_SERVER -U gpspl -P $SA_PASSWORD
			/opt/mssql-tools/bin/bcp $bcpdb.dbo.[${table}] out $bcpdir/${table}.bcp -n -S $DATA_SERVER -U gpspl -P $SA_PASSWORD
		fi
		echo "Bulk Copying $bcpdir/${table}.bcp file.."
		/opt/mssql-tools/bin/bcp $bcpdb.dbo.[${table}] in $bcpdir/${table}.bcp -f $bcpdir/${table}.fmt -E -b 50000 -S "${HOST}" -U gpspl -P $SA_PASSWORD
	done

elif [ "$1" = "bcp" ]; then
	AlexisTables="ProductType,CurrencyMaster,Cards,Transaction,Transaction_Int"
	bcpdb="AlexisPTSTest"
	if [ $# -gt 1 ]; then
  		AlexisTables=$2
	fi
	for table in ${AlexisTables//,/ }
	do
		/opt/mssql-tools/bin/bcp AlexisPTSTest.dbo.[${table}] format nul -f /tmp/${table}.fmt -n -S $DATA_SERVER -U gpspl -P yW2S6KXdY8
		/opt/mssql-tools/bin/bcp AlexisPTSTest.dbo.[${table}] out /tmp/${table}.bcp -n -S $DATA_SERVER -U gpspl -P yW2S6KXdY8
		/opt/mssql-tools/bin/bcp AlexisPTSTest.dbo.[${table}] in /tmp/${table}.bcp -f /tmp/${table}.fmt -E -b 50000 -S "${HOST}" -U gpspl -P $SA_PASSWORD
	done

	/opt/mssql-tools/bin/sqlcmd -S "${HOST}" -U gpspl -P $SA_PASSWORD -Q "
		ALTER TABLE AlexisPTSTest.dbo.Transaction_Int NOCHECK CONSTRAINT FK_Transaction__payment_token_id;
		UPDATE AlexisPTSTest.dbo.[Transaction]
		SET    TransDate = DATEADD(year, 1, TransDate); 
		SET IDENTITY_INSERT AlexisPTSTest.dbo.[Transaction_Int] ON;
		INSERT INTO AlexisPTSTest.dbo.[Transaction_Int] ([ID], [PAN], [SysDate], [TransDate], [TransCcy], [BillAmt], [Description], [OldStatus], [NewStatus], [TransactionType], [RecordID], [TransactionStatus], [TransAmt], [BillNat_Conv], [Actual_Balance], [Blocked_Amount], [flCredit], [ProcessingCode], [AuthMethod], [LoadSRC], [LoadType], [OrigStan], [Note], [Amount_other], [Fixed_Fee], [Rate_Fee], [Fx_Padding], [Mcc_Padding], [AuthPresDiff], [txnCountry], [FeeID], [Trans_Link], [SettlementAmt], [Additional_Data], [BillCcy], [SettleCcy], [TxnTypeProcCode], [Account_From], [Account_To], [traceid_lifecycle], [payment_token_id])
		SELECT * FROM AlexisPTSTest.dbo.[Transaction] WHERE transDate < '2020-01-01'; 
		SET IDENTITY_INSERT AlexisPTSTest.dbo.[Transaction_Int] OFF;
		DELETE FROM  AlexisPTSTest.dbo.[Transaction] WHERE transDate < '2020-01-01';
		
		USE AlexisPTSTest

		ALTER DATABASE AlexisPTSTest SET RECOVERY SIMPLE

		DBCC SHRINKFILE (AlexisPTSTest, 1);
		DBCC SHRINKFILE (AlexisPTSTest_Log, 1);

		ALTER DATABASE AlexisPTSTest SET RECOVERY FULL;"
elif [ "$1" = "bcpdir" ]; then
	for dir in /bcp/*/
	do
		dbname=`basename $dir`
		for file in /bcp/$dbname/*.bcp
		do 
			table=`basename $file .bcp`
			echo "Processing $table table.."
			/opt/mssql-tools/bin/bcp $dbname.${table} in /bcp/$dbname/${table}.bcp -f /bcp/$dbname/${table}.fmt -q -E -b 500000 -S "${HOST}" -U gpspl -P $SA_PASSWORD
		done
	done
elif [ "$1" = "shrink" ]; then
	/opt/mssql-tools/bin/sqlcmd -S "${HOST}" -U gpspl -P $SA_PASSWORD -Q "
		USE ${2}

		ALTER DATABASE ${2} SET RECOVERY SIMPLE

		DBCC SHRINKFILE (${2}, 1);
		DBCC SHRINKFILE (${2}_Log, 1);

		ALTER DATABASE ${2} SET RECOVERY FULL;"
else
	echo "Usage: docker-compose run dbutils function"
	echo "     where function = 'gps-databases' for loading Databases. Requires second parameter with databases and optionally a branch"
	echo "                      gps.database.dataaccesslayer GPS.Database.DataAccessLayer for Data Access Layer and optionally a branch"
	echo "                      'seed' 'Database' for seeding data"
	echo "                      'bash' for shell"
	echo "						shrink db to shrink database"
fi