How To: Run SQLRules against all new and changed files

Run SQLRules against any new or modified files since the last successful Packaging operation

Customers may wish to run SQLRules against new or modified files for various reasons including:

Running rules as a gate prior to allowing a git merge
To address DAT-17427: Multiple -replace files for a single file cause SQLRules to be skipped (slated to be addressed in Liquibase Enterprise 8.7 in end of Q2 2024.)

The ability to runRules for a list of files is available with Liquibase Enterprise release versions 7.5+.

Code Overview

When packager runs successfully it performs a check-in to the SQL repository using a commit message “Datical automatic check-in”
Code will query the SQL branch’s git log for the most recent commit id with this message
1. If this is a new branch or if the branch has never had a successful Packager check-in it will use the initial commit id
Code will then perform a git diff against the SQL branch to get a list of all file changes since the commit id of the last successful packaging job
These file references will be relative from the SQL repository’s sql_code folder so there is code to prepend a path that is instead relative from the DDB repository.
Hammer runRules is then run with a list of these sql files.

Please Note

Git commands need to be run from the SQL Repository (housed in the ${PROJ_SQL} variable)
Hammer command needs to be run from the DDB Repository (housed in ${PROJ_DDB} variable)
Some of the commands below require use of the ` apostrophe verses the ' apostrophe.
As a workaround for DAT-17427, the “Run Rules” stage below can be run prior to the “Packager” stage. The Rules Code below will run against all versions of -cleanup and -replace files.

The below code is written for Jenkinsfiles using Liquibase Enterprise on Linux agents.

stage('Run Rules') {
    steps {        
		sh '''
			{ set +x; } 2>/dev/null

			cd ${PROJ_SQL}
                      
				# Get commit from the last time Packager ran successfully
				echo "Last Successful Packager Commit ID: " `git log -1 --pretty=format:"%h" --grep="Datical automatic check-in"`
				LAST_PACKAGER_ID=$(git log -1 --pretty=format:"%h" --grep="Datical automatic check-in")
				if [[ -z "$LAST_PACKAGER_ID" ]]
				then
					echo "Could not find Successful Packager job.  Setting to Initial Commit ID: " `git log --reverse --pretty=format:"%h" | head -1`
					LAST_PACKAGER_ID=$(git log --reverse --pretty=format:"%h" | head -1)
				fi
                      
				# Get a List of Files since this commit.  Do not include deleted files. 
				echo "List of Files Changed since this Commit: " `git diff --diff-filter=d --name-only ${LAST_PACKAGER_ID} | xargs | sed 's/ /,/g'`
				LIST_OF_FILES_TO_PROCESS=$(git diff --diff-filter=d --name-only ${LAST_PACKAGER_ID} | xargs | sed 's/ /,/g')
                      
            cd ../${PROJ_DDB}
                      
				# Update the List of Files to include a Path which points back to the SQL repository
				echo "Updated File Path: " `echo ${LIST_OF_FILES_TO_PROCESS//sql_code\\//..\\/${PROJ_SQL}\\/sql_code\\/}`
				LIST_OF_FILES_WITH_PATH=$(echo ${LIST_OF_FILES_TO_PROCESS//sql_code\\//..\\/${PROJ_SQL}\\/sql_code\\/})
				
				# Remove any occurrence of Jenkinsfile(,) in the list of files
                LIST_OF_FILES_WITH_PATH=$(echo ${LIST_OF_FILES_WITH_PATH/Jenkinsfile,/})
                LIST_OF_FILES_WITH_PATH=$(echo ${LIST_OF_FILES_WITH_PATH/Jenkinsfile/})
				
				if [[ -z "$LIST_OF_FILES_WITH_PATH" ]]
				then
					echo "Could not find files.  Setting Path to SQL Repo."
					LIST_OF_FILES_WITH_PATH="../${PROJ_SQL}"
				fi
                      
				echo "==== RUN RULES ===="
				hammer runRules ${LIST_OF_FILES_WITH_PATH}

		'''
	} // steps
}  // Run Rules stage

The below code is written for Liquibase Enterprise on Windows agents.

stage('Run Rules') {
    steps {        
		bat '''
			@echo off
			setlocal enableDelayedExpansion

			:: Run Rules
			(
				cd %PROJ_SQL%
    
				:: Get commit from the last time Packager ran successfully
				for /f "tokens=1" %%i in ('git log -1 --pretty^=format:"%%h" --grep="Datical automatic check-in"') do (
                echo Last Successful Packager Commit ID: %%i 
                echo:
                set "LAST_PACKAGER_ID=%%i"
                )
                if not defined LAST_PACKAGER_ID (
                for /f "tokens=1" %%i in ('git rev-list --max-parents^=0 HEAD') do (
                echo Could not find Successful Packager job. Setting to Initial Commit ID: %%i
                echo: 
                set "LAST_PACKAGER_ID=%%i"
                )
                )

                :: Get a List of Files since this commit. Do not include deleted files.
                set "LIST_OF_FILES_TO_PROCESS="
                for /f "tokens=* delims=" %%a in ('git diff --diff-filter^=d --name-only !LAST_PACKAGER_ID! -- ":(exclude)*metadata.properties"') do (
                set "LIST_OF_FILES_TO_PROCESS=!LIST_OF_FILES_TO_PROCESS!%%a,"
                )
                set "LIST_OF_FILES_TO_PROCESS=!LIST_OF_FILES_TO_PROCESS:~0,-1!"
                echo List of Files Changed since this Commit: !LIST_OF_FILES_TO_PROCESS!
                echo:

                :: Update the List of Files to include a Path which points back to the SQL repository
                set "LIST_OF_FILES_WITH_PATH=!LIST_OF_FILES_TO_PROCESS:/=\\!"
                set "LIST_OF_FILES_WITH_PATH=!LIST_OF_FILES_WITH_PATH:sql_code\\=..\\%PROJ_SQL%\\sql_code\\!"
                echo Updated List of Files: !LIST_OF_FILES_WITH_PATH!
                echo:

                :: If no files are found, set path to SQL repo.
                if "!LIST_OF_FILES_WITH_PATH!"=="" (
                echo Could not find files. Setting Path to SQL Repo.
                set "LIST_OF_FILES_WITH_PATH=..\\%PROJ_SQL%"
                )

                cd ../%PROJ_DDB%

                echo ==== RUN RULES ====
                hammer runRules !LIST_OF_FILES_WITH_PATH!
			)
			endlocal
		'''
    } // steps
}  // Run Rules stage

Liquibase Enterprise Knowledge Base

How To: Run SQLRules against all new and changed files

Run SQLRules against any new or modified files since the last successful Packaging operation

Code Overview

Please Note

Related content