Upgrading Datastores for New API Versions

Warn

This article is for general guidance. If you have a working Senzing system with data and insight you do not wish to lose, or are unsure of any steps please contact us for assistance.

 

Overview

When upgrading from one version of the Senzing APIs to another, there are sometimes changes made to the entity repository database schema. In these cases, the database(s) used by Senzing must be updated accordingly.

Database Upgrade SQL Scripts

Upgrade scripts for the Senzing entity repository are provided as part of the API packages. They provide the SQL statements needed to upgrade the database and are named with the database type and API version numbers, for example:

• g2core-schema-sqlite-upgrade-2.0-to-3.0.sql

Each SQL script file contains the SQL statements needed to update the database from one major/minor version to the next, for a specific database vendor. The following are example statements previously included in an upgrade script: 

CREATE TABLE SYS_CODES_USED (CODE_TYPE VARCHAR(25) NOT NULL, CODE VARCHAR(25) NOT NULL, CODE_ID SMALLINT NOT NULL) ;
ALTER TABLE SYS_CODES_USED ADD CONSTRAINT SYS_CODES_USED_PK PRIMARY KEY(CODE_TYPE,CODE) ;
CREATE UNIQUE INDEX SYS_CODES_USED_SK ON SYS_CODES_USED(CODE_TYPE, CODE_ID) ;

These SQL scripts may be combined in order to do a full upgrade of the entity repository database.

Generating Complete Scripts with g2dbupgrade

The g2dbupgrade utility, available in <project_path>/bin, is used to compute full database update scripts; even when crossing several versions or using multiple database types.

Example usage: 

• g2dbupgrade -c <INI file> -o <outputDirectory>

With example parameters: 

• g2dbupgrade -c etc/G2Module.ini -o myOutputDir

This command will generate the SQL script needed to upgrade an entity repository database from version 1.8 to 1.10. It will create the update script from the data files in the data directory and store the resulting script in the output directory.

The INI file specified in the command is the same as used to run the Senzing engine. It provides the information for the entity repository being used and generates scripts accordingly. For example, if the INI file is configured for a MySQL database, g2dbupgrade will generate the script for MySQL. If multiple databases are used and have different types, a script for each database type is created.

Precautions 

g2dbupgrade is capable of performing the database updates itself with the -a argument and omitting the -o argument. However, this action is not recommended in production and we advise creating and reviewing the scripts with your DBA to gauge the specific operational impact any changes might have.

Moreover, always test any updates and changes in a non-production system before implementing them in production!