Jenkinsfile pipeline as code (state-based deployments)
Published 23 March 2022
Here is a Jenkinsfile that defines a pipeline 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%
