How To: Prune your Liquibase Enterprise repo and SQL repo

Owned by Former user (Deleted)
Last updated: Feb 23, 2024 by Amy Smith
6 min read

From time to time, you will need to clear out old scripts from your Datical projects. These scripts get added to your project's changelog.xml file and these changes could also be deployed into the pipeline, perhaps all the way to the PROD environment.

How do you clear out those changes? This document covers these steps. 

Note that the instructions are different for different versions of Datical, and they are also different depending on whether or not you use Deploy Packager.

1. SQL Repository: Delete scripts from "archive" folder

SQL scripts committed into "data_dml", "ddl", "ddl_direct", sql_direct" and "sql" directories get archived into the "archive" folder. 

Location: Browse into the branch where you commit your SQL scripts (e.g., "develop" branch). The "archive" folder is located in your branch's base folder (e.g., "sql_code", as shown in the screenshot below)

Steps to clear out: In "archive" folder, browse into each subfolder and delete old scripts.

Important: Be sure to commit your changes into your SQL repository.

Note: Make sure to merge your branches so that all branches, including "master" are also cleaned out and sync'd up to your main commit branch (e.g. "develop" branch). 

2. SQL Repository: Delete scripts from other SQL folders

Not all scripts get relocated to the "archive" folder. Scripts packaged from these folders stay in their original directories: "function", "package", "packagebody", "procedure", "trigger" and "view". Essentially, these are all stored logic scripts. 

In Step 3 below, you will clean out your "changelog.xml" and in Step 4 you will also clean out your "Resources" folder. Therefore, you will no longer need these stored logic scripts in SQL Repository.

Note: It's very important that if you complete Step 2 that you also follow through and complete Steps 3 and 4.

Location: Scripts stay in their original location after packaging. As such, these scripts could be located in any of the following directories under SQL Repository's base folder (e.g., "sql_code", as shown in the screenshot below):  "function", "package", "packagebody", "procedure", "trigger" and "view".

Steps to clear out: You will need to search for scripts in each of these directories and delete old scripts. Be sure to keep the original directories and subdirectories intact. E.g., if you have subdirectories in the "procedure" folder then you will need to preserve those subdirectories and only remove procedure scripts. You may also have a "metadata.properties" file in these directories which you will also need to preserve.

Important: Do not delete the folder hierarchy (or subdirectories). Also do not delete the metadata.properties file(s). Finally, be sure to commit your changes into your SQL repository.

Note: Make sure to merge your branches so that all branches, including "master" are also cleaned out and sync'd up to your main commit branch (e.g. "develop" branch). 

3. Datical Project: Remove change sets from "changelog.xml" in Datical project

Your project's "changelog.xml" file captures database changes that Datical has packaged over time.

Location: This file is located in your Datical project folder, in "Changelog" subfolder

Steps to clear out: Open the "changelog.xml" in a text editor (e.g., notepad++, atom, etc.) or in the Design tab of the Datical GUI. Remove old change sets as shown in the screenshot below.  Be sure to leave the first 2 lines at the top intact and also leave the last 1 line at the bottom of the changelog.xml intact.

Note: Do not delete any change sets that were configured for Auto-Permissions or Auto-Synonyms.  You may want to search the changelog for "runAlways" or "runOnChange" prior to making any deletes to identify these types of change sets. 

Important: Be sure to commit your changes into your Datical project repository.

4. Datical Project: Delete scripts from "Resources" Folder

As you accumulate scripts that have been packaged previously, those scripts get moved to the "Resources" folder in your Datical project. Your changesets in changelog.xml file actually reference these scripts in the "Resources" folder. There could also be subfolders within the "Resources" folder as shown in the screenshot below. Note these subdirectories: "ddb_tools", "sql", "sql_scripts". There could be other subdirectories too.

Location: The "Resources" folder is located in your Datical project. 

Steps to clear out: In "Resources" folder, you will need to search for scripts in each of these directories and delete old scripts. 

Note: Do not delete any resource files associated with run always or run on change Auto-Permissions or Auto-Synonyms.

Important: Be sure to commit your changes into your Datical project repository.

5. Datical Project: Reset your "sqlScmLastCommitID" in deployPackager.properties File

Datical project stores the ID of your last commit when it was able to successfully package scripts. This is the pointer that tells Datical to look for new scripts - indicating that Datical will catch incoming scripts since that last successful packaging job. Now that we are resetting all the testing and cleaning out both the Datical project repository as well as the SQL repository this pointer to the last commit ID needs to be reset.

Location: The "sqlScmLastImportID" is located in "deployPackager.properties" file in the Datical project folder.

  

Note that there could be multiple instances of this property - typically once for each pipeline:

Steps to reset: These IDs need to be reset to the new commits you made in Steps 3 and 4 above. You can retrieve your new commit IDs from your SCM tool. For example, if you use Git Bash, you will first need to change directory to your cloned SQL repository and then type "git log -1". The "-1" argument will give you the latest commit ID. You can also type "git log" without any arguments and it will return a screen full of chronologically ordered commit IDs and you will then select the first entry from this output. Copy this commit ID and paste it into your Datical project's "deployPackager.properties" file:

Important: Be sure to commit your changes into your Datical project repository.

Note: Repeat this step for each branch in your SQL Repository.

6. What about changes tracked in DATABASECHANGELOG tables? 

Datical stores which changes have been deployed in each database by storing change set information in tracking schema's DATABASECHANGELOG table. Datical uses this information during forecast and deploy operations to determine which changes need to be forecasted/deployed. It is optional to clean out change sets from this table.

Will leaving the changes in the DATABASECHANGELOG tables cause any issues?

  • No, it will not affect forecast or deploy operations.
  • However, when running Status or Status Details for your target database, the changes in your changelog.xml are no longer in sync with changes logged in DATABASECHANGELOG table. You may see that there are "Ignored" change sets shown in Status results or Status Detail results because they are still in the DATABASECHANGELOG table, but are no longer in the changelog.xml.  If you do not want to see this type of "Ignored" changes in Status or Status Details, then you could clear the pruned changes out of your DATABASECHANGELOG table. 

7. Optionally clear history out of your Source Control repository

If the accumulated history in your repository (such as git) is causing slow downs, you could OPTIONALLY clear out the history.  This typically is not needed for most customers.   But it may be helpful to clear the git history for your repository IF there is a very high quantity of changes being processed very frequently.  

Copyright © Datical 2012-2020 - Proprietary and Confidential