Manual User Data Repository Upgrade

The User Data Repository (UDR) is where SuperWEB2 stores user-saved data, such as saved tables and recodes. When there are changes to the database structure (for example as new features are added to the product) the database needs to be upgraded.

Normally this happens automatically when you start the new version of SuperWEB2 for the first time. SuperWEB2 checks the UDR version on startup, and if it is not on the latest version, it runs the upgrade scripts, then updates the version number stored in the UDR to prevent the scripts running again in future.

In some cases, you may not be able to use the automated scripts. For example, this might be because your database administrator will not allow the SuperWEB2 user to have the CREATE TABLE and ALTER TABLE privileges it needs to change the table structure. If SuperWEB2 does not have the appropriate privileges, you may see an "insufficient privileges" error message similar to one of the following in the SW2.log file:

Caused by: java.sql.SQLException: ORA-01031: insufficient privileges

au.com.str.services.repository.exception.SchemaUpdateException: Failed to perform automatic UDR setup from file sql/sw2/upgrade_9_18_0_mssql.sql. Manual database setup may be required. 

If you are unable to use the automated process, then you will need to manually upgrade the UDR.

Step 1 - Extract the Scripts from SuperWEB2

The upgrade SQL scripts are located inside a .jar file within the SuperWEB2 .war file. The first step is to unzip both of these to extract the scripts:

1. Make a copy of the SuperWEB2 webapi.war file somewhere outside of your production environment.

2. Unzip webapi.war to extract its contents (you may need to rename the file extension from .war to .zip, depending on the Zip tool you are using).

3. Within the extracted files, locate the file webapi\WEB-INF\lib\strwebapi-services-<version>.jar and unzip this to extract its contents.

4. Within the extracted files, navigate to strwebapi-services-<version>\sql\sw2.
   In this folder, you will see many individual SQL scripts named by version and RDBMS. For example:

   • upgrade_9_18_0_mssql.sql is the upgrade script from 9.18.0 for Microsoft SQL Server.

   • upgrade_9_19_0_mysql.sql is the upgrade script from 9.19.0 for MySQL.

5. Collect all the scripts that:

   • Match your RDBMS; and

   • Include a version number that either matches the version you are upgrading from or is higher than that version.

Not every release has an upgrade script.

For example, if you upgrading from version 9.17.1 to 9.20 on Postgres, you will need the following scripts:

• upgrade_9_17_1_pgsql.sql

• upgrade_9_18_0_pgsql.sql

• upgrade_9_19_0_pgsql.sql

Once you have extracted all your required SQL scripts, you can delete all the other files you extracted from the SuperWEB2 .war file.

Step 2 - Special Notes

The 9.18.0 and 9.19.0 scripts contain SQL statements similar to the following:

UPDATE RECODE SET LASTACCESSED = ${now};

This is due to the new LASTACCESSED column being added to the TXD, RECODE and UDF tables. The upgrade scripts set the value of LASTACCESSED for all existing records to an initial default value of the time the script was run.

When the scripts run automatically, ${now} is replaced with the current timestamp by the code that executes the script. When running it manually, you should replace this with a UNIX timestamp in milliseconds (13 digits).

Step 3 - Run the Scripts

Provide the scripts to your database administrator and ask them to run them on your UDR. It is a good idea to make a backup/snapshot of the UDR before running the scripts that you can rollback to in case of any issues.

The scripts must be run in order (e.g., 9.17.1, then 9.18.0, then 9.19.0).

If you encounter any issues running the scripts, contact support for assistance.

Step 4 - Update the UDR Version

Ask your database administrator to run the following commands to update the UDR to the current version:

TRUNCATE TABLE SCHEMAINFO;
INSERT INTO SCHEMAINFO (ID, VERSION) VALUES (1, 9);

Step 5 - Start SuperWEB2

Start SuperWEB2 and check the logs to verify that there are no error messages relating to the User Data Repository at startup.