How To: Clear out Test Changes (In Preparation For Go-Live)

During the user acceptance testing phase (UAT) your Datical project will package several test scripts. 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 prior to go-live such that your project has no test changes (or test data) which you packaged with Datical? This documents covers those steps:

1. SQL Repository: Clean Out "archive" Directory

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

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

Steps to clear out: In "archive" directory, delete all subdirectories, including all content.

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: Remove All Other SQL Scripts

Not all scripts get relocated to the "archive" directory. 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" directory. 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 directory (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 those scripts. Be sure to keep the original directories and subdirectories intact. E.g., if you have subdirectories in the "procedure" directory 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 directory 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: Clean out "changelog.xml" from 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 directory, in "Changelog" subdirectory

Steps to clear out: Open the "changelog.xml" in a text editor (e.g., notepad++, atom, etc.). Remove all changesets as shown in the screenshot below. 

You should be left with only these lines in your cleaned-out version of the "changelog.xml". 

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

4. Datical Project: Clean out "Resources" Directory

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

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

Steps to clear out: In "Resources" directory, delete all subdirectories, including all content. 

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 directory.

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. Clean out Objects from Your Databases

1. Remove all data from "DATABASECHANGELOG" table in each database that's in your Datical project. This includes all pipelines.
2. Clean up database test objects that were deployed - such as tables, views, procedures, etc.

7. (OPTIONAL) Clean out Artifacts

If you use JFrog Artifactory or Nexus or any other artifacts repository, you may want to clean out your older artifacts versions that were created during testing/validation.

Alternately, if deleting those artifacts is not allowed, determine a way to tag old artifacts such that those artifacts are no longer usable or available for Datical deploy jobs. 

Another thing to do is to make sure to create a new artifact with no changesets and start using artifact versions from this point onwards.

8. (OPTIONAL) Refresh environments from PROD

Take the DDL from PROD and apply it to the other environments.  (In this clean up scenario you don't have to copy the data from databasechangelog and databasechangeloglock tables from PROD to the other environments as you typically would during a refresh for other reasons, because we already removed the data in step 6 above so the databasechangelog tables are empty.)

9. (OPTIONAL) Delete project from DMC and Re-Register

Under Project Settings → <Project Name> there is a button to Remove Project. This will delete the project from the DMC application and underlying PostgreSQL database.

After deleting the project, Re-Register the project in the GUI (using Settings → DMC Database → Register Project) or run the CLI command hammer registerProjectWithDMCDB from within the Datical DB repo.