Database DevOps for Oracle

Jenkinsfile pipeline as code (state-based deployments)

Page last updated 05 April 2021

The latest information can be found at https://documentation.red-gate.com/soco/deploying-database-changes/worked-examples/jenkinsfile-pipeline-as-code-state-based-deployments


Here is a Jenkinsfile that defines a pipeilne workflow. Each stage calls a separate batch file that does the real work. This is provided as an example and not designed to be used "as is". For example this deploys automatically to all environments except production, which may not be desirable.

Jenkinsfile_state

def schemachanges = false
def datachanges = false
node {  
		/* Begin by cleaning artifacts folder */
		try {	dir ('Artifacts') { deleteDir() } }
		catch (all)	{ echo "something went wrong with deletedir" }


	 stage ('Build')    {
		  	checkout scm
		  	def status = bat returnStatus: true, script:'call Tools\\CI-Build.cmd'

			if (status == 0) {
				//archiveArtifacts allowEmptyArchive: true, artifacts:'Artifacts/SchemaCreation.sql, Artifacts/DataCreation.sql, Artifacts/AllObjects.html'
				archiveArtifacts allowEmptyArchive: true, artifacts:'Artifacts/ReleaseState.onp'
				echo ('Database build was successful')
			}
			else if (status == 2) error ('No schema objects in the database') // empty schema, fail the build
			else error("Something went wrong:$status") // Shouldn't ever get here
		 	
	 }
	 	 stage ('Invalid Objects')    {
		  	def status = bat returnStatus: true, script:'call Tools\\CI-Invalid-Objects.cmd'
			//echo "Invalid Objects exit code: $status" 
		 	
			if (status == 0) echo "No invalid objects found"
			else if (status == 1) { // invalid object detected
				archiveArtifacts allowEmptyArchive: true, artifacts:'Artifacts/InvalidObjects.txt'
				error ('Invalid objects detected - see Artifacts/InvalidObjects.txt') // fail the build
			}
			else error("Something went wrong:$status") // Shouldn't ever get here

	 }

	 stage ('Code Analysis')    {
		  	def status = bat returnStatus: true, script:'call Tools\\CI-Code-Analysis.cmd'
			echo "Code analysis exit code: $status" 
		 	archiveArtifacts allowEmptyArchive: true, artifacts:'Artifacts/CodeAnalysisResults.xml'
			if (status == 0) echo "No code analysis issues found"
			else if (status == 1) echo "Some code analysis issues were identified - see Artifacts/CodeAnalysisResults.xml"
	 }

	 stage ('Unit Test')    {
		  	def status = bat returnStatus: true, script:'call Tools\\CI-Unit-Test.cmd'
		 	junit 'Artifacts/UnitTests.junit.xml'
		 	archiveArtifacts allowEmptyArchive: true, artifacts:'Artifacts/UnitTests.junit.xml' //no really need to publish artifact as Jenkins can render the results
			if (status == 0) echo "All unit tests passed"
			else if (status == 1) error ('One or more unit tests failed - see Tests tab for detail') // fail the build
			else error("Something went wrong:$status") // Shouldn't ever get here
	 }
	 stage ('QA')    {
		  def status = bat returnStatus: true, script:'call Tools\\Release-QA.cmd'
		  echo "QA is updated from the desired state"
	 }
	 stage ('Release Artifacts')    {
		  def status = bat returnStatus: true, script:'call Tools\\Release-Artifacts.cmd'
		  // Publish the appropriate artifacts depending on whether there are schema and/or static data differences.
		  if (status == 63) // Pause the pipeline if there are high warnings detected
		  {
				schemachanges = true // since there must be changes for warnings to be detected
				archiveArtifacts allowEmptyArchive: true, artifacts: 'Artifacts/Warnings.txt'
		 	    archiveArtifacts allowEmptyArchive: true, artifacts: 'Artifacts/ReleaseUpdate.sql, Artifacts/ReleaseChanges.html, Artifacts/DataUpdate.sql, Artifacts/DataChanges.html'
				archiveArtifacts allowEmptyArchive: true, artifacts: 'Artifacts/Rollback.sql'
				timeout(time: 10, unit: 'MINUTES') { // timeout after 10 minutes to not block the agent
					input 'High warnings detected for schema changes - Abort or Proceed anyway? (only schema changes will be considered for this deployment)' // Proceeding is at your own risk!
				}
				// In a non-demo scenario it would be recommended to abort the build:
				// currentBuild.result = 'ABORTED'
				// error("Build aborted owing to detection of high warnings")
		  }
		  else if (status == 1) {
		  // No changes to deploy so in this instance we set currentBuild.result='ABORTED' so that build status isn't marked as failed
				currentBuild.result = 'ABORTED'
				error ('There are no schema or static data changes to deploy: aborting')
		  }
		  else if (status == 2) {	// 2 - Schema changes, no static data changes
		  	  schemachanges = true
		 	  archiveArtifacts allowEmptyArchive: true, artifacts: 'Artifacts/ReleaseUpdate.sql, Artifacts/ReleaseChanges.html'
			  archiveArtifacts allowEmptyArchive: true, artifacts: 'Artifacts/Rollback.sql'
		  }
		  else if (status == 3) {	// 3 - No schema changes, static data changes
		 	  archiveArtifacts allowEmptyArchive: true, artifacts: 'Artifacts/DataUpdate.sql, Artifacts/DataChanges.html'
    		  datachanges = true
		  }
		  else if (status == 4) {	// 4 - Schema changes and static data changes
    		  schemachanges = true
    		  datachanges = true
		 	  archiveArtifacts allowEmptyArchive: true, artifacts: 'Artifacts/ReleaseUpdate.sql, Artifacts/ReleaseChanges.html, Artifacts/DataUpdate.sql, Artifacts/DataChanges.html'
			  archiveArtifacts allowEmptyArchive: true, artifacts: 'Artifacts/Rollback.sql'
		  }
		  else error("Something went wrong:$status") // Shouldn't ever get here
		echo "There are outstanding changes to deploy: schema changes: $schemachanges, static data changes: $datachanges"
	 }

	 stage ('Mask')    {
		  def status = bat returnStatus: true, script:"call Tools\\Release-Mask-Acceptance.cmd"
		  echo "Exit code: $status"
		  if (status != 0) { // there is a problem
				error("Masking failed - see log for details in %LOCALAPPDATA%-Red Gate-Logs-Data Masker for Oracle")
		  }
	 }
	 
	stage ('Acceptance')    {
		  def status = bat returnStatus: true, script:"call Tools\\Release-Acceptance.cmd $schemachanges $datachanges"
		  //echo "acceptance status:$status"
		  archiveArtifacts allowEmptyArchive: true, artifacts: 'Artifacts/RollbackState.onp'
		  if (status == 0) {
			  echo "No drift detected" // commented out of the demo script
			  echo "Deployment rehearsal successful"
			  echo "Roll-back test successful" // commented out of the demo script
		  }
		  else if (status == 1) error('Drift detected! See Artifacts/AcceptanceValidation.html') // Drift detected (commented from this demo)
		  else error("Something went wrong:$status") // Shouldn't ever get here
	 }

	 stage ('Approval Gate'){
			def message = "This allows us to say whether there are schema and/or static data changes to approve"
			if (schemachanges && datachanges) message="Schema and static data changes to deploy"
			else if (schemachanges && !datachanges) message="Schema changes to deploy"
			else if (!schemachanges && datachanges) message="Static data changes to deploy"
		  
		  // wrapping in a time out so it doesn't block the agent and simply fails the build after 5 minutes if there's no user intervention
		  timeout(time: 30, unit: 'MINUTES') {
		  		def userInput = input(
		  		id: 'userInput', message: "$message", parameters: [
		  		[$class: 'TextParameterDefinition', defaultValue: 'I Approve The Deployment', description: 'To Proceed, type I Approve The Deployment', name: 'Review deployment artifacts before proceeding']
		  		])		  
		  		// echo ("Env: "+userInput)
		  		if (userInput.indexOf('I Approve The Deployment') == -1)
		  		{
					currentBuild.result = 'ABORTED'
					error('Deployment aborted')
		  		}
		  }
	 }

	 stage ('Production')    {
				def status = bat returnStatus: true, script:"call Tools\\Release-Production.cmd $schemachanges $datachanges"
				//echo "Exit code: $status"
				if (status == 0) echo "Deployment to production successful" // ideally we'd also run the post-deploy validation check
				else if (status == 1) { // Drift detected
  					archiveArtifacts allowEmptyArchive: true, artifacts: 'Artifacts/ProductionDrift.html'
					currentBuild.result = 'ABORTED'
					error('Drift detected!')
				}
				else if (status == 61) { // differences after deployment - this isn't implemented in the demo script
	  				archiveArtifacts allowEmptyArchive: true, artifacts: 'Artifacts/ProductionDifferences.html'
					error('Post-deploy differences found - something went wrong')
				}
				else error("Something went wrong:$status") // Shouldn't ever get here
		  
	 }   

}

The above Jenkinsfile pipeline references the following batch files, which should be placed inside a Tools folder alongside where the jenkinsfile lives. 

CI-Build.cmd

echo off
echo ========================================================================================================
echo Build a TEST database as a validation step and for testing in the later stages
echo ========================================================================================================

echo == Create a schema snapshot that will represent the desired state ==
"C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe"  /i:sdwgvac /source:Database{HR} "/snp:Artifacts/ReleaseState.onp"
echo snapshot creation exit code:%ERRORLEVEL%


echo Run Tools/DropAllObjects.sql to drop all objects from the TEST schema
echo on
Call exit | sqlplus HR_TEST/Redgate1@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE))) @Tools/DropAllObjects.sql
echo off

echo Build the database with the objects and generate a creation script and a report listing all objects. 
"C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /deploy /i:sdwgvac /source:Artifacts/ReleaseState.onp{HR} /target HR_TEST/Redgate1@localhost/XE{HR_TEST} /sf:Artifacts/SchemaCreation.sql /report:Artifacts/AllObjects.html

echo Build database from state:%ERRORLEVEL%

rem IF ERRORLEVEL is 0 then there are no changes.
IF %ERRORLEVEL% EQU 0 (
    echo == Warning - Is the database empty of schema objects?
    SET ERRORLEVEL=2
	GOTO END
)

rem IF ERRORLEVEL is 61 there are differences, which we expect.
IF %ERRORLEVEL% EQU 61 (
    echo == Objects were found and built.
    rem Reset the ERRORLEVEL to 0 so the build doesn't fail 
    SET ERRORLEVEL=0
)

echo == Include static data, a feature of Data Compare v5
echo (this step is intentionally left out of this demo script)
rem "C:\Program Files\Red Gate\Data Compare for Oracle 5\dco.exe" /deploy /source Database{HR} /target HR_TEST/Redgate1@localhost/XE{HR_TEST} /sf:Artifacts/static_data_creation_script.sql
rem IF %ERRORLEVEL% EQU 61 (
rem     echo ========================================================================================================
rem     echo == Static data was found and added.
rem     echo ========================================================================================================
rem     rem Reset the ERRORLEVEL to 0 so the build doesn't fail 
rem     SET ERRORLEVEL=0
rem )

echo == Now that we've built a test database, we can validate that the end state schema is consistent with the state
echo (this step is intentionally left out of this demo script)
rem This is optional and is unlikely to fail, so could leave this out to reduce build time.
rem "C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /i:sdwgvac /source Database{HR_DEV} /target HR_TEST/Redgate1@localhost/XE{HR_TEST} /report:Artifacts/build_validation_report.html

rem There should be no differences
rem IF ERRORLEVEL is 0 then there are no changes.
rem IF %ERRORLEVEL% EQU 0 (
rem     echo ========================================================================================================
rem     echo == Validation successful! We have successfully built a database from the source state
rem     echo ========================================================================================================
rem     GOTO END
rem )

rem IF %ERRORLEVEL% NEQ 0 (
rem     echo ========================================================================================================
rem     echo == Validation FAILED! The build isn't consistent with the source
rem     echo ========================================================================================================
rem     GOTO END
rem )


:END
EXIT /B %ERRORLEVEL%


DropAllObjects.sql

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT object_name, object_type 
                  FROM   user_objects
                  WHERE  object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')) LOOP
    BEGIN
      IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"');
    END;
  END LOOP;
END;
/

CI-Code-Analysis.cmd

echo off
echo ========================================================================================================
echo Run code analysis checks
echo ========================================================================================================

echo == This demo uses the preview Code Analysis for Oracle command line
c:\tools\cao\cao.exe /source:Database /outfile:Artifacts\CodeAnalysisResults.xml
echo PMD exit code:%ERRORLEVEL%

:END
EXIT /B %ERRORLEVEL%

CI-Unit-Test.cmd

echo off
echo ========================================================================================================
echo Run unit tests
echo ========================================================================================================


echo This calls the utPLSQL framework
call Tools\utPLSQL-cli\bin\utplsql run HR_TEST/Redgate1@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE))) -f=ut_xunit_reporter -o=Artifacts/UnitTests.junit.xml
echo utplsql:%ERRORLEVEL%

:END
EXIT /B %ERRORLEVEL%


Release-Artifacts.cmd

echo off
echo =====================================================================================================
echo == Release Artifacts ==
echo =====================================================================================================

echo We generate the deployment preview script artifact here
"C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /abortonwarnings:High /b:hdre /i:sdwgvac /source:Artifacts/ReleaseState.onp{HR} /target:HR_PRODUCTION/Redgate1@localhost/XE{HR_PRODUCTION} /report:Artifacts/ReleaseChanges.html /scriptfile:Artifacts/ReleaseUpdate.sql > Artifacts\Warnings.txt
rem Note that for this demo example /b:e is used to exclude the target schema in the deployment script

echo Checking for warnings (no warnings=61, warnings=63), exit code:%ERRORLEVEL%
rem In the unlikely event that the exit code is 63, this mean that a deployment warning has exceeded the allowable threshold (eg, data loss may have been detected)
rem If this occurs it is recommended to review the script, customize it, and perform a manual deployment


IF %ERRORLEVEL% EQU 63 (
    echo ========================================================================================================
    echo == High Severity Warnings Detected! Aborting the build. 
    echo == Review the deployment script and consider deploying manually.
    echo ========================================================================================================
    rem Here we run the same comparison without /abortonwarnings to generate the deployment script as warnings abort the generation of the deployment script
    rem It is useful to keep the deployment script artifact for troubleshooting purposes, or as the starting point for a manual deployment
    rem Once we fix the "bug" whereby sco doesn't generate the deployment script when /aow:High finds an issue, then we can remove this step.
    "C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /b:hdre /i:sdwgvac /source:Artifacts/ReleaseState.onp{HR} /target:HR_PRODUCTION/Redgate1@localhost/XE{HR_PRODUCTION} /report:Artifacts/Changes.html /sf:Artifacts/ReleaseUpdate.sql
    rem Set the exit code back to 63 as the previous invocaion of sco.exe will reset this.
    SET ERRORLEVEL=63
    GOTO END
)
rem This is the happy path where we've identified changes and not detected any high warnings
IF %ERRORLEVEL% EQU 61 (
    echo ========================================================================================================
    echo == Schema changes found to deploy
    echo ========================================================================================================

    echo We generate the roll back script artifact simply by reversing the source and the target for the comparison
    "C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /b:hdre /i:sdwgvac /target:Artifacts/ReleaseState.onp{HR} /source:HR_PRODUCTION/Redgate1@localhost/XE{HR_PRODUCTION} /scriptfile:Artifacts/Rollback.sql

    SET SCHEMACHANGES=1
)
IF %ERRORLEVEL% EQU 0 (
    echo ========================================================================================================
    echo == No schema changes found to deploy
    echo ========================================================================================================
    SET SCHEMACHANGES=0
)

echo == Now check for static data changes ==
rem if error is "Error: source must specify a connection to a database" then static data needs to be enabled in a feature flag
	
rem workaround until static data can be deployed from a scripts folder

	"C:\Program Files\Red Gate\Data Compare for Oracle 5\dco.exe" /source:"Database{HR}" /target:HR_TEST/Redgate1@localhost/XE{HR_TEST} /deploy
	echo Deploy static data to test:%ERRORLEVEL%
	
	"C:\Program Files\Red Gate\Data Compare for Oracle 5\dco.exe" /include:Countries^|Regions /source:HR_TEST/Redgate1@localhost/XE{HR_TEST} /target:HR_PRODUCTION/Redgate1@localhost/XE{HR_PRODUCTION} /reporttype:HTML /report:Artifacts/DataChanges.html /scriptfile:Artifacts/DataUpdate.sql /overwritereports
	echo Data Compare exit code:%ERRORLEVEL%
	
IF %ERRORLEVEL% EQU 64 (
    echo ========================================================================================================
    echo == DATA COMPARE PROBLEM: returning exit code 64 - ENABLE FEATURE FLAG file OracleToolsFeatures.config and put in:
    echo == C:\Windows\System32\config\systemprofile\AppData\Local\Red Gate\
    echo ========================================================================================================
)
IF %ERRORLEVEL% EQU 61 (
    echo ========================================================================================================
    echo == Static data changes found to deploy
    echo ========================================================================================================
    SET DATACHANGES=1
)
IF %ERRORLEVEL% EQU 0 (
        echo ========================================================================================================
        echo == No static data changes to deploy
        echo ========================================================================================================
        SET DATACHANGES=0
)

rem deploy static data - v5 feature
rem WARNING - this only works if production has the tables in question. Maybe we need to create the script anyway in the 'null' case?
echo SCHEMACHANGES:%SCHEMACHANGES%
echo DATACHANGES:%DATACHANGES%
rem exit codes:
rem 63 - High Warnings!
rem 1 - No schema changes, no static data changes
if "%SCHEMACHANGES%"=="0" if "%DATACHANGES%"=="0" SET ERRORLEVEL=1
rem 2 - Schema changes, no static data changes
if "%SCHEMACHANGES%"=="1" if "%DATACHANGES%"=="0" SET ERRORLEVEL=2
rem 3 - No schema changes, static data changes
if "%SCHEMACHANGES%"=="0" if "%DATACHANGES%"=="1" SET ERRORLEVEL=3
rem 4 - Schema changes and static data changes
if "%SCHEMACHANGES%"=="1" if "%DATACHANGES%"=="1" SET ERRORLEVEL=4
echo ReleaseReviewErrorlevel:%ERRORLEVEL%


rem For the purposes of this demo we will provision an empty schema based on production, but ideally a database that closely resembles production should be used

echo == Provision an acceptance database ==
echo Run Tools/DropAllObjects.sql to drop all objects from the TEST schema
echo on
Call exit | sqlplus HR_ACCEPTANCE/Redgate1@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE))) @Tools/DropAllObjects.sql
echo off

echo == Provision schema
"C:\Program Files\Red Gate\Schema Compare for Oracle \sco.exe" /i:sdwgvac /source HR_PRODUCTION/Redgate1@localhost/XE{HR_PRODUCTION} /target HR_ACCEPTANCE/Redgate1@localhost/XE{HR_ACCEPTANCE} /deploy
echo Build Acceptance Schema: %ERRORLEVEL%
echo == Provision data
"C:\Program Files\Red Gate\Data Compare for Oracle 5\dco.exe" /source HR_PRODUCTION/Redgate1@localhost/XE{HR_PRODUCTION} /target HR_ACCEPTANCE/Redgate1@localhost/XE{HR_ACCEPTANCE} /deploy
echo Build Acceptance Data: %ERRORLEVEL%


:END
EXIT /B %ERRORLEVEL%


Release-QA.cmd

echo off
echo =====================================================================================================
echo == Release QA ==
echo =====================================================================================================

rem This syncs an QA database with the latest version so it can be used by a QA team to run regression tests and other manual or automated system tests.
rem There are two approaches to keep a database in sync
rem 1) Use the same process as used for deployment to Acceptance and Production
rem 2) Simply sync the latest changes to the database
rem This example will use (2). If using (1), simply duplicate the process used for Acceptance deployments on your QA database.

rem NOTE - This ignores any deployment warnings as these will be picked up by the Review step.

echo == Deploying schema changes using state-based deployment
"C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /b:hdre /i:sdwgvac /source:Artifacts/ReleaseState.onp{HR} /target HR_QA/Redgate1@localhost/XE{HR_QA} /deploy
rem Exit code will be 61 if there are differences
echo Schema Compare for Oracle /deploy exit code:%ERRORLEVEL%

echo == Deploying static data changes using state-based deployment
"C:\Program Files\Red Gate\Data Compare for Oracle 5\dco.exe" /source:"Database{HR}" /target:HR_QA/Redgate1@localhost/XE{HR_QA} /deploy
echo Data Compare for Oracle /deploy exit code:%ERRORLEVEL%

rem Set ERRORLEVEL back to 0 so errors to QA don't cause the pipeline to fail
SET ERRORLEVEL=0

echo == Validate against the desired schema state ==
echo (commented out from demo)
rem "C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /b:hdre /i:sdwgvac /source:Artifacts/ReleaseState.onp{HR} /target HR_QA/Redgate1@localhost/XE{HR_QA}
rem echo QA Deployment validation check:%ERRORLEVEL%
rem IF %ERRORLEVEL% EQU 61 (
rem    echo ========================================================================================================
rem    echo == QA deployment validation failed - review QA_deploy_success_report.html
rem    echo ========================================================================================================
rem 	"C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /b:hdre /i:sdwgvac /source:Artifacts/ReleaseState.onp{HR} /target HR_QA/Redgate1@localhost/XE{HR_QA} /report:Artifacts/QA_deploy_success_report.html
rem    GOTO END
rem )

:END
EXIT /B %ERRORLEVEL%

Release-Mask-Acceptance

echo off
echo =====================================================================================================
echo == Release-Mask-Acceptance.cmd ==
echo =====================================================================================================

rem Acceptance database has been provisioned in the Release-Artifacts stage
echo == Masking Acceptance Contacts table
start /wait "" "C:\Program Files\Red Gate\Data Masker for Oracle\DataMasker.exe" "C:\Program Files\Red Gate\Data Masker for Oracle\MaskingSets\HRContacts.MaskSet" -R -X 
echo DataMasker.exe EXIT CODE:%ERRORLEVEL%

EXIT /B %ERRORLEVEL%


Release-Acceptance.cmd

echo off
echo =====================================================================================================
echo == Release-Acceptance.cmd ==
echo =====================================================================================================

rem This script requires knowledge of whether it needs to deploy schema and/or data
SET RG_SCHEMACHANGES=%1
SET RG_DATACHANGES=%2

rem Set some defaults if nothing passed into batch file
IF "%RG_SCHEMACHANGES%"=="" (
  SET RG_SCHEMACHANGES=true
  SET RG_DATACHANGES=true
)
echo RG_SCHEMACHANGES is:%RG_SCHEMACHANGES%
echo RG_DATACHANGES is:%RG_DATACHANGES%

rem Acceptance DRIFT CHECK to validate that the schema state is consistent with production
rem If it isn't, it could be that production has drifted since the acceptance database was provisioned
echo == Check that the acceptance database schema matches production ==
echo (skip the drift check for the demo)
rem "C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /b:hdre /i:sdwgvac /source HR_ACCEPTANCE/Redgate1@localhost/XE{HR_ACCEPTANCE} /target HR_PRODUCTION/Redgate1@localhost/XE{HR_PRODUCTION} /report:Artifacts/acceptance_validation_report.html
rem echo Acceptance vs Production check:%ERRORLEVEL%

rem We expect there to be no differences, with exit code 0
rem IF %ERRORLEVEL% EQU 0 (
rem    echo == Validation successful: acceptance and production are the same
rem     echo Create a schema snapshot artifact of acceptance so we can later use this to perform the production drift check and, if necessary, for selective "roll back".
rem     "C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /i:sdwgvac /source HR_ACCEPTANCE/Redgate1@localhost/XE{HR_ACCEPTANCE} /snapshot:Artifacts/RollbackState.onp 
rem )

rem IF %ERRORLEVEL% EQU 61 (
rem     echo == Validation FAILED! The acceptance database schema isn't consistent with production
rem     SET ERRORLEVEL=1
rem     GOTO END
rem )

echo Create a schema snapshot artifact of acceptance so we can later use this to perform the production drift check and, if necessary, for selective "roll back".
"C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /i:sdwgvac /source HR_ACCEPTANCE/Redgate1@localhost/XE{HR_ACCEPTANCE} /snapshot:Artifacts/RollbackState.onp 


echo == Applying deployment script to the acceptance database ==
IF "%RG_SCHEMACHANGES%" == "true" (
    echo on
    Call exit | sqlplus "HR_ACCEPTANCE/Redgate1@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE)))" @Artifacts/ReleaseUpdate.sql
    echo off
)
IF "%RG_DATACHANGES%" == "true" (
    echo == Applying static data to the acceptance database ==
    rem Ordinarily should do this using the script - but because the demo unconventionally uses schemas for environments, this isn't possible.
    rem echo on
    rem Call exit | sqlplus "HR_ACCEPTANCE/Redgate1@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE)))" @Artifacts/DataUpdate.sql
    rem echo off
    rem Just for the demo we do a state-based deployment to acceptance for static data, rather than running the DataUpdate.sql
	
	rem "C:\Program Files\Red Gate\Data Compare for Oracle 5\dco.exe" /source:"Database{HR}" /target:HR_ACCEPTANCE/Redgate1@localhost/XE{HR_ACCEPTANCE} /deploy

	rem Workaround for static data deployments - the longer term solution is to deploy from a scripts folder once this is implemented
	"C:\Program Files\Red Gate\Data Compare for Oracle 5\dco.exe" /source:"Database{HR}" /target:HR_TEST/Redgate1@localhost/XE{HR_TEST} /deploy
	echo Deploy static data to test:%ERRORLEVEL%
	"C:\Program Files\Red Gate\Data Compare for Oracle 5\dco.exe" /include:Countries^|Regions /source:HR_TEST/Redgate1@localhost/XE{HR_TEST} /target:HR_ACCEPTANCE/Redgate1@localhost/XE{HR_ACCEPTANCE} /deploy
	echo Deploy static data from test to Acceptance:%ERRORLEVEL%

	SET ERRORLEVEL=0
)
echo Data deploy Acceptance Exit code:%ERRORLEVEL%

rem IF %ERRORLEVEL% EQU 61 (
rem     echo ========================================================================================================
rem     echo == Static data changes found to deploy
rem     echo ========================================================================================================
rem     SET ERRORLEVEL=0
rem )

echo == Check that the deployed acceptance database is now the same as the desired state ==
echo (This is commented out to speed up the demo)

rem Can comment this out in demo script for speed purposes
rem "C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /b:hdre /i:sdwgvac /source:Artifacts/ReleaseState.onp{HR} /target HR_ACCEPTANCE/Redgate1@localhost/XE{HR_ACCEPTANCE}
rem echo Acceptance Deployment Check:%ERRORLEVEL%
rem IF %ERRORLEVEL% EQU 61 (
rem     echo ========================================================================================================
rem    echo == Acceptance deployment validation failed - review accept_deploy_success_report.html
rem     echo ========================================================================================================
rem 	"C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /b:hdre /i:sdwgvac /source:Artifacts/ReleaseState.onp{HR} /target HR_ACCEPTANCE/Redgate1@localhost/XE{HR_ACCEPTANCE} /report:Artifacts/accept_deploy_success_report.html
rem     GOTO END
rem )
rem this is optional and will be left out to speed up the demo
echo == Rollback check ==
echo (This is commented out to speed up the demo)
rem Here we find out if there are any warnings associated with a rollback (ie is it possible without data loss?) by generating warnings
rem "C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /abortonwarnings:high /b:hdre /i:sdwgvac /source:Artifacts/RollbackState.onp{HR_ACCEPTANCE} /target HR_ACCEPTANCE/Redgate1@localhost/XE{HR_ACCEPTANCE} /report:Artifacts/Rollback_changes_report.html /sf:Artifacts/rollback_script.sql > Artifacts\rollback_warnings.txt
rem echo Acceptance Rollback Warnings ERRORLEVEL:%ERRORLEVEL%

rem Exit code 61 is what we expect. It means we can run the rollback with no warnings.
rem Exit code 61 Differences found.
rem IF %ERRORLEVEL% EQU 61 (
rem     echo ========================================================================================================
rem     echo == Rollback test on acceptance database successful!
rem     echo ========================================================================================================
rem     SET ERRORLEVEL=0
rem )

rem If we get exit code 63, it means that rollback is risky.
rem Exit code 63 Deployment warnings above threshold. Deployment aborted.
rem IF %ERRORLEVEL% EQU 63 (
rem     echo ========================================================================================================
rem     echo == Rollback has high warnings. A rollback to the snapshot may not be possible.
rem     echo ========================================================================================================
rem     rem To alert the user we could set the ERRORLEVEL to 1, which the Jenkins job will interpret as "Unstable".
rem     rem However, rollback warnings shouldn't stop us from deploying as we should be taking backups anyway.
rem     SET ERRORLEVEL=0
rem     GOTO END
rem )

rem TODO - If we want to fully test the rollback here we could apply the rollback script and check the resulting database against the RollbackState.onp snapshot

echo End of Acceptance Exit code:%ERRORLEVEL%

:END
EXIT /B %ERRORLEVEL%


Release-Production.cmd

echo off
echo =====================================================================================================
echo == Release-Production ==
echo =====================================================================================================
rem Here we apply the same deployment script that has been reviewed, approved and run successfully on an acceptance or staging database
rem To check that production hasn't drifted since the acceptance database deployment, we run a second drift check.
rem This script requires knowledge of whether it needs to deploy schema and/or data
SET RG_SCHEMACHANGES=%1
SET RG_DATACHANGES=%2

rem Set some defaults if nothing passed into batch file
IF "%RG_SCHEMACHANGES%"=="" (
  SET RG_SCHEMACHANGES=true
  SET RG_DATACHANGES=true
)
echo RG_SCHEMACHANGES is:%RG_SCHEMACHANGES%
echo RG_DATACHANGES is:%RG_DATACHANGES%


echo == Acceptance Drift Check ==
rem We have previously saved the pre-deployment snapshot state of the acceptance database as an artifact
"C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /i:sdwgvac /filter:Database/Filter.scpf /source:Artifacts/RollbackState.onp{HR_ACCEPTANCE} /target:HR_PRODUCTION/Redgate1@localhost/XE{HR_PRODUCTION} /report:Artifacts/ProductionDrift.html

rem We expect there to be no differences, so ERRORLEVEL should be 0
IF %ERRORLEVEL% EQU 0 (
     echo == Confirmed that production has not drifted since the deployment rehearsal
)

 IF %ERRORLEVEL% NEQ 0 (
     echo == DRIFT DETECTED! The production database schema is not at the validated starting point - failing the build
    rem We set the exit code to a value that will halt the deployment process.
     SET ERRORLEVEL=1
     GOTO END
)

echo == Deployment time! ==

IF "%RG_SCHEMACHANGES%" == "true" (
    echo =====================================================================================================
    echo Applying the schema changes deployment script
    echo =====================================================================================================
    echo on
    Call exit | sqlplus "HR_PRODUCTION/Redgate1@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE)))" @Artifacts/ReleaseUpdate.sql
    echo off
    echo SQLPLUS ReleaseUpdate.sql exit code:%ERRORLEVEL%
)
IF "%RG_DATACHANGES%" == "true" (
    echo =====================================================================================================
    echo Applying the static data changes deployment script - Note this is a Data Compare v5 feature only
    echo =====================================================================================================
    echo on
    Call exit | sqlplus "HR_PRODUCTION/Redgate1@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE)))" @Artifacts/DataUpdate.sql
    echo off
    echo SQLPLUS DataUpdate.sql exit code:%ERRORLEVEL%
)

echo Check that production is now equal to the desired state
echo (This is commented out to speed up the demo)
rem For there interests of a more efficient demo, can leave out post-deployment validation
rem  "C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /b:hdre /i:sdwgvac /source:Artifacts/desired_state_snapshot.onp{HR} /target:HR_PRODUCTION/Redgate1@localhost/XE{HR_PRODUCTION}

rem   echo Production Deployment Check:%ERRORLEVEL%
rem   IF %ERRORLEVEL% NEQ 0 (
rem        echo == Deployment FAILED! The production database schema is not equivalent to the desired state - see prod_deploy_report for differences
rem  	  "C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /b:hdre /i:sdwgvac /source:Artifacts/desired_state_snapshot.onp{HR} /target:HR_PRODUCTION/Redgate1@localhost/XE{HR_PRODUCTION} /report:Artifacts/ProductionDifferences.html
rem        GOTO END
rem    )
rem   IF %ERRORLEVEL% EQU 0 (
rem        echo == Congratulations - Deployment was successful!
rem        GOTO END
rem   )

:END
EXIT /B %ERRORLEVEL%

Didn't find what you were looking for?

Product Articles

Tips and how-to guides for Redgate products

University

Easy to follow video courses

Community Forums

Ask, discuss, and solve questions about Redgate's tools

Events & Friends

Meet us at an event, get sponsored, and join our Friends of Redgate

Simple Talk

In-depth articles and opinion from Redgate's technical journal