Upgrading HCLDetect Database

Mode of Operation

The upgrade script supports two modes:

1. Auto-Upgrade Mode (Default mode): The database_migration script scans the <Detect_Install_Path>/drive/etc/migration folder for available upgrade scripts, and retrieves the current Detect version from the database and identifies matching upgrade files.

   The script executes the upgrade files sequentially, starting with the script whose detectSourceVersion field exactly matches the current database version.

   Usage: For using this mode, use the "database_migration" script without the -q option.

   For example, ./drive/bin/database_migration -D=<database-name> -u=<username> -p=<password> -H=<host> -P=<port>.

2. Single File Mode: Manually executes SQL queries from a specified file.

   Usage: For using this mode, -q option must be used, with a path to the upgrade sql query file, while executing script.

   For example, ./drive/bin/database_migration -q=<path-to-upgrade-file>.

Script Options

The following table shows options and description:

If you don’t specify one or more options when you run the database_migration script, the script uses the corresponding values from the database section of the <Detect_Install_Path>/drive/etc/drive.json file.

"database": {
  "name": "drive_acme_core",
  "server": {
    "ip": "127.0.0.1",
    "port": 3306
  },
  "type": "MariaDB",
  "userName": "drive",
  "userPassword": "DECRYPTED_PASSWORD"
}

Examples

./drive/bin/database_migration

./drive/bin/database_migration -D=drive_acme_core -u=drive

./drive/bin/database_migration -D=<database-name> -u=<database-user-name> -p=<password> -H=<host> -P=<port> -q=<path-to-upgrade-query-file>

1. The script uses all database connection details exactly as provided in the command.
2. Thee script reads the upgrade queries from the specified JSON file path path-to-upgrade-query-file.

Log and Status Files

After execution, logs are saved in the database_migration folder. The script writes execution logs to the same location as the script file, at the same directory level.

The folder structure would be as below:

|- upgrade<source-version>to<target-version>.json file
|- database_migration
	|- log
		|- database_migration.log
	|- status
		|- database_migration_id_executed_status.txt

Below is a sample log of the database_migration script execution with external query file path provided:

Command

$ ./drive/bin/database_migration -q=/tmp/upgrade_query_files/detect_db_upgrade_from-1.0.0-to-1.1.0.json

2025-04-30 05:45:47,600 | INFO | Database migration will be performed on the database connection information displayed below:

Database Name: drive_acme_core
Database Hostname: 127.0.0.1
Database Port: 3306
Database User: drive

2025-04-30 05:45:47,600 | INFO | Please ensure above connection details are correct before proceeding
2025-04-30 05:45:49,923 | INFO | ==== IMPORTANT | Ensure DATABASE BACKUP has been taken before proceeding ====
2025-04-30 05:45:50,730 | INFO | Starting database migration
2025-04-30 05:45:50,732 | INFO | Database connection established
2025-04-30 05:45:50,732 | INFO | Product version `1.0.0` fetched from database `drive_acme_core`
2025-04-30 05:45:50,733 | INFO | Loading SQL from JSON file: /tmp/upgrade_query_files/detect_db_upgrade_from-1.0.0-to-1.1.0.json
2025-04-30 05:45:50,733 | INFO | starting sql execution
2025-04-30 05:45:50,743 | INFO | Statement executed : Id 1
2025-04-30 05:45:50,746 | INFO | Statement executed : Id 2
2025-04-30 05:45:50,930 | INFO | ending sql execution
2025-04-30 05:45:50,930 | INFO | Data migration completed successfully
2025-04-30 05:45:50,931 | INFO | Last executed query id: 2, and total queries to be executed: 2
2025-04-30 05:45:50,931 | INFO | Database migrated from Detect version '1.0.0' to '1.1.0'
2025-04-30 05:45:50,931 | INFO | Clearing status file. Successfully executed all queries from the upgrade file '/tmp/upgrade_query_files/detect_db_upgrade_from-1.0.0-to-1.1.0.json'
2025-04-30 05:45:50,932 | INFO | Ending Database migration

Below is a sample log of the database_migration script execution without external query file path:

Command: $ ./drive/bin/database_migration

database_migration.log -

2025-09-04 05:56:19,780 | INFO | Database migration will be performed on the database connection information displayed below:

Database Name: drive_acme_core
Database Hostname: 127.0.0.1
Database Port: 3306
Database User: drive

2025-09-04 05:56:19,780 | INFO | Please ensure above connection details are correct before proceeding
2025-09-04 05:56:19,781 | INFO | ==== IMPORTANT | Ensure DATABASE BACKUP has been taken before proceeding ====
2025-09-04 05:56:23,253 | INFO | Starting data migration
2025-09-04 05:56:23,254 | INFO | Database connection established
2025-09-04 05:56:23,255 | INFO | Product version `1.0.0` fetched from database `drive_acme_core`
2025-09-04 05:56:23,255 | INFO | No query file provided. Checking for Upgrade scripts
2025-09-04 05:56:23,261 | INFO | Available version upgrades: ['upgrade1.0.0to1.1.0.json', 'upgrade1.1.0to1.2.0.json']
2025-09-04 05:56:23,261 | INFO | Running upgrade script : upgrade1.0.0to1.1.0.json
2025-09-04 05:56:23,262 | INFO | Loading SQL from JSON file: /tmp/Detect_Installation/drive/etc/migration/upgrade1.0.0to1.1.0.json
2025-09-04 05:56:23,264 | INFO | starting sql execution
2025-09-04 05:56:23,275 | INFO | Statement executed : Id 1
2025-09-04 05:56:23,313 | INFO | Executing dependent queries for base query id: 2
2025-09-04 05:56:23,314 | INFO | Statement executed : Id 2.1
2025-09-04 05:56:23,375 | INFO | Statement executed : Id 2.2
2025-09-04 05:56:23,617 | INFO | Statement executed : Id 3
2025-09-04 05:56:23,645 | INFO | Statement executed : Id 4
2025-09-04 05:56:23,712 | INFO | ending sql execution
2025-09-04 05:56:23,712 | INFO | Data migration completed successfully
2025-09-04 05:56:23,713 | INFO | Last executed query id: 4, and total queries to be executed: 4
2025-09-04 05:56:23,713 | INFO | Database migrated from Detect version '1.0.0' to '1.1.0'
2025-09-04 05:56:23,713 | INFO | Clearing status file. Successfully executed all queries from the upgrade file '/tmp/Detect_Installation/drive/etc/migration/upgrade1.0.0to1.1.0.json'
2025-09-04 05:56:23,713 | INFO | Running upgrade script : upgrade1.1.0to1.2.0.json
2025-09-04 05:56:23,714 | INFO | Loading SQL from JSON file: /tmp/Detect_Installation/drive/etc/migration/upgrade1.1.0to1.2.0.json
2025-09-04 05:56:23,716 | INFO | Reading last successfully executed query id from migration status file
2025-09-04 05:56:23,864 | INFO | starting sql execution
2025-09-04 05:56:23,875 | INFO | Statement executed : Id 1
2025-09-04 05:56:23,913 | INFO | Statement executed : Id 2
2025-09-04 05:56:24,012 | INFO | ending sql execution
2025-09-04 05:56:24,012 | INFO | Data migration completed successfully
2025-09-04 05:56:24,013 | INFO | Last executed query id: 2, and total queries to be executed: 2
2025-09-04 05:56:23,013 | INFO | Database migrated from Detect version '1.1.0' to '1.2.0'
2025-09-04 05:56:24,014 | INFO | Clearing status file. Successfully executed all queries from the upgrade file '/tmp/Detect_Installation/drive/etc/migration/upgrade1.0.0to1.1.0.json'
2025-09-04 05:56:24,014 | INFO | Ending Database migration

Upgrade File Format

Upgrade files must follow the structure shown below:

{
    "detectSourceVersion": "25.1.0",
    "detectTargetVersion": "25.1.1",
    "queries": [
        {
            "qId": 1,
            "sql": "<sql query to be executed>""
        },
        {
            "qId": 2,
            "sql": "",
            "sqlDependent": [
                {
                    "qId": 1,
                    "sql": "<SELECT sql query, result of which will be used in next subsequent queries>"
                },
                {
                    "qId": 2,
                    "sql": "<sql query using result from above SELECT query>""
                }
            ]
        }
    ]
}