SmartDB Upgrade and Migration Routine

Introduction

The SmartDB migration routine provides a simple and automated way to update or migrate the SmartDB when updating the SmartComponent Library to a new release. The SmartDB is the framework database that also contains data provided by Consultingwerk for framework functions of the SmartFramework such as security, localization, menu and user interface repository etc..

The SmartDB migration routine supports updating the schema of the SmartDB as well as loading data.

The migration routine is implemented using an ANT script and the PCT (https://github.com/Riverside-Software/pct and https://github.com/Riverside-Software/pct/wiki for documentation) extensions to ANT. PCT is contained in the deployment of the setup script, so that users of the migration routine will just have to install ANT version 1.8 or above. It is recommended to make ant.bat available using the operating system PATH environment variable. ANT can be downloaded from http://ant.apache.org

It may be required to copy the oe_common_services.jar from a subfolder of the %DLC%\oeide directory (e.g. C:\Progress\OpenEdge113\oeide\eclipse\plugins\com.openedge.pdt.core_11.3.2.00) to the ANT lib folder (the lib folder below the ANT installation directory, aka ANT_HOME). Further information is available here: https://github.com/jakejustus/pct/wiki/ClassDocumentation

Sample output from the ANT script, when the oe_common_services.jar file is required.

c:\WorkspaceCW\ABL\Setup\setup.xml:36: taskdef A class needed by class eu.rssw.pct.oedoc.OpenEdgeDocumentation cannot be found: com/openedge/pdt/core/ast/visitor/ASTVisitor
using the classloader AntClassLoader[c:\WorkspaceCW\ABL\Setup\Install\PCT\PCT.jar]

PCT Version Dependency

We do use the quickRequest argument to the PCTRun task in the setup script. As this attribute is only supported from PCT 208, it's mandatory to use at least that PCT release.

The migration routine is launched from an operating system command prompt. It is recommended to launch the ANT script from within the “Setup” folder. The command line to launch the migration routine looks like this:

Sample command line
ant -lib Install/PCT/PCT.jar -f setup.xml -Dsmartdb="smartdb" -Dsmartdbhost="localhost" -Dsmartdbport="4711" -Ddlc="c:\Progress\OpenEdge113" -Dinstallroot="c:\Work\SmartComponentLibrary"

In the proenv shell from OpenEdge 11.7 on the utility can also be executed like this:

Sample command line from proenv
%dlc%\ant\bin\ant -lib Install/PCT/PCT.jar -f setup.xml -Dsmartdb="smartdb" -Dsmartdbhost="localhost" -Dsmartdbport="4711" -Ddlc=%dlc% -Dinstallroot=".."

An alternative to passing in multiple options is to use a properties file, as below. See Running upgrade, migration and other routines using a properties file for more information about using a properties file.

Sample command line from proenv, using a properties file
%dlc%\ant\bin\ant -lib Install/PCT/PCT.jar -f setup.xml -propertyfile setup.properties -Ddlc=%dlc%

Creating a new SmartDB

Is is recommended to create a new SmartDB database using the DB\SmartDB\SmartDB.st schema definition file. This .st file defines an area for Data, Index and LOB. The database is further prepared for Auditing. 

  1. Copy the DB\SmartDB\SmartDB.st file to the folder in which you are creating the new SmartDB database
  2. Run the following command: prodb SmartDB empty
Sample commands used to create a new empty SmartDB
11.7 64 c:\Temp\NewDB\DB> copy ..\SmartComponentLibrary\db\SmartDB\SmartDB.st .
        1 Datei(en) kopiert.

11.7 64 c:\Temp\NewDB\DB> prodb SmartDB empty
Procopy session begin for mikef on CON:. (451)
Database copied from C:\Progress\OP3395~1\empty. (1365)
Procopy session end. (334)

11.7 64 c:\Temp\NewDB\DB> dir
 Datenträger in Laufwerk C: ist Windows
 Volumeseriennummer: FE60-F935

 Verzeichnis von c:\Temp\NewDB\DB

22.10.2017  13:41    <DIR>          .
22.10.2017  13:41    <DIR>          ..
22.10.2017  13:41           131.072 SmartDB.a1
22.10.2017  13:41           131.072 SmartDB.a2
22.10.2017  13:41           131.072 SmartDB.a3
22.10.2017  13:41           131.072 SmartDB.a4
22.10.2017  13:41         2.228.224 SmartDB.b1
22.10.2017  13:41         2.162.688 SmartDB.d1
22.10.2017  13:41            32.768 SmartDB.db
22.10.2017  13:41             1.436 SmartDB.lg
16.10.2017  23:30               129 SmartDB.st
22.10.2017  13:41            65.536 SmartDB_42.d1
22.10.2017  13:41           262.144 SmartDB_43.d1
22.10.2017  13:41            65.536 SmartDB_44.d1
              12 Datei(en),      5.342.749 Bytes
               2 Verzeichnis(se), 189.849.612.288 Bytes frei

Startup Parameters

The parameters must be adjusted to match your Environment:

Parameter

Description

-lib Install/PCT/PCT.jarPoint to a compatible PCT.jar library (currently at least PCT 208). We do provide a compatible PCT.jar in the Setup/Install/PCT folder.

-Dsmartdb

This must be the name of your SmartDB database, typically smartdb

-Dsmartdbhost

The host (server) that executes your SmartDB server process.

The migration routine does not support single user or shared memory connections.

-Dsmartdbport

The port number your SmartDB server is listening on

-DsmartdbdirThe directory that contains the SmartDB
-Dsmartdbsingleusertrue/false for single user vs. self-service DB connection (when -Dsmartdbdir is used)
-DsmartldbnameThe (optional) logical database name for the SmartDB. Should match the SmartDB setting in Consultingwerk/products.i
-Dsmartdbuser.name(Optional) The name of a user used for the SmartDB connection
-Dsmartdbuser.password(Optional) If a user name is provided with the smartdbuser.name property, a password for that user.
-Dsmartuser.prompt(Optional) A value of "yes" prompts the user for a username and password for the SmartDB connection. This may be used in cases where passwords may not be stored in scripts or property files

-Ddlc

Your Progress installation directory

-Dinstallroot

The folder that contains the current version of the SmartComponent Library.

This is the folder containing the “Consultingwerk” and “Setup” folder.

This is the folder that you unzipped the release of the SmartComponent Library into.

-Donline

This parameter is optional. When set to "true", an online schema change will be performed (-Donline="true").

A classic offline schema change will be performed when this parameter is either not set or set to "false".

-DMmCustom setting for -Mm parameter (defaults to 1024)
-DcpinternalCustom setting for cpinternal (defaults to iso8859-1)
-DcpstreamCustom setting for cpstream (defaults to iso8859-1)
-DcpcollCustom setting for cpcoll (defaults to German-Library)
-Dsilent.schemaWhen "yes", user will not be prompted before loading schema changes
-Dsilent.dataWhen "yes", user will not be prompted before loading new data
-DtableareaThe area name for table allocation
-DindexareaThe area name for index allocation
-DlobareaThe area name for LOBs
-DpreviewDeltaLogs the contents of the delta.df just before loading it
-DcompileDetermines what is compiled. Valid values are full, minimal, dependencies. Any other value will result in no compilation. Default value is "minimal"
-Ddelete-rcodeDetermines whether existing r-code is deleted. When "yes", rcode will be deleted. Defaults to "yes".
-Dassemblies

Value for -assemblies, required for -Dcompile="dependencies" and "full".

Database Connections

We support 3 methods of connecting to the SmartDB. 

Networked connection

For a networked connection it is mandatory to provide the -Dsmartdb, -Dsmartdbport and -Dsmartdbhost parameters but not the -Dsmartdbdir and -Dsmartdbsingleuser parameters.

Local, self-service client

For a local, self-service client connection it is mandatory to provide the -Dsmartdb, -Dsmartdbdir and -Dsmartdbsingleuser parameters but not the parameters -Dsmartdbport and -Dsmartdbhost. The -Dsmartdbsingleuser parameter needs to be set to false.

Local, single-user client

For a local, single-user client connection it is mandatory to provide the -Dsmartdb, -Dsmartdbdir and -Dsmartdbsingleuser parameters but not the parameters -Dsmartdbport and -Dsmartdbhost. The -Dsmartdbsingleuser parameter needs to be set to true.

Providing a username and password for the SmartDB connections

A username and password may optionally be provided to the SmartDB connections (for schema loading, for dumping delta .df's, etc). The -Dsmartdbuser.name and -Dsmartdbuser.password or the -Dsmartdbuser.prompt properties are used to provide this information to the script.

Compile options

The SmartFramework Upgrade and Migration routine will compile (parts) of the SmartComponent Library installation folder to ensure that data can be imported successfully after schema updates.

The following messages may be shown while executing the SmartFramework Upgrade and Migration utility.

[PCTRun] Usecount of the segment is <= 0 (14347)
[PCTRun] Usecount of the segment is <= 0 (14347)
[PCTRun] Usecount of the segment is <= 0 (14347)
[PCTRun] Usecount of the segment is <= 0 (14347)
[PCTRun] Usecount of the segment is <= 0 (14347)

Those errors might be related to http://knowledgebase.progress.com/articles/Article/Enum-input-to-method-gives-error-Usecount-of-the-segment-is-0-14347

Compiling the full SmartComponent Library code after the schema update instead of just the Consultingwerk.SmartFramework package and src/smartDBtrgs resolves this issue.

We have introduced two new parameters to the SmartDB Upgrade and Migration utility to control the compilation process:

Parameter NameDescription
-Dcompileminimal/dependencies/full/none
-DassembliesValue for -assemblies, required for -Dcompile="dependencies" or "full"


-Dcompile options:
-Dcompile="minimal" (default): Compile only SmartFramework 
-Dcompile="dependencies": Compile SmartFramework and dependencies
-Dcompile="full": Full compile
-Dcompile="none": No compile at all

Running the SmartDB Upgrade and Migration utiltiy

When launching the migration routine, it is first attempted to connect to the database. Errors are reported on the console.

The script will then load the new schema of the SmartDB database into a new empty database and will create a delta.df file that contains the required changes for your SmartDB database. The script does also support the initial setup (schema and data) of an empty SmartDB database. When changes are required, the script will prompt you for confirmation if the schema changes should be loaded into your database:

[echo] We are now going to load the new schema into the SmartDB.
[echo] Please back it up first!
[echo] The pending schema changes can be reviewed in temp/delta.df.
[input] Continue (y/n)? (y, n) 

Hit “y” and “Return” to continue. Note, that you can review the generated temp/delta.df file before continueing. 
The script will then load required schema changes into your database. When schema changes were required the migration routine will delete all R-Code in the Consultingwerk folder in the installroot folder (see parameters).
After loading the schema changes, the migration routine will load new and updated data into your SmartDB.

[echo] We are now going to load the new data into the SmartDB
[echo] Please back it up first!
[input] Continue (y/n)? (y, n)

 Hit “y” and “Return” to continue loading data.

When finished the routine will confirm completion with a message like this:

BUILD SUCCESSFUL
Total time: 1 minute 36 seconds

 You should pay attention to the console output and check for any error message. Please try to correct errors or report them to Consultingwerk for support or inspection.

The migration routine is designed so that it can repeatedly be executed.

Updating a SmartDB with alternative storage areas

By default, the areas are removed from the delta.df before loading that into the target SmartDB. When you have configured your own (advised) alternative storage areas you can specify those area's when starting the setup.xml routine.

-Dtablearea  The storage area where the SmartDB tables will be located

-Dindexarea The storage area where the SmartDB indices will be located

-Dlobarea The storage area where the LOBs will be located


Example:

C:\Work_Training\SmartComponentLibrary>ant -lib Install/PCT/PCT.jar -f setup/setup.xml -Dsmartdb="smartdb" -Ddlc="c:\Progress\OpenEdge122_64" -Dinstallroot="c:\Work_training\SmartComponentLibrary" -Dsmartdbdir=C:\Work_Training\DB\SmartDB -Dtablearea=Data128 -Dindexarea=Index64 -Dlobarea=MyLobArea

ANT Targets

The setup.xml contains these ANT targets intended to be called by customers. 

  • setup - Updates the SmartFramework databases schema and contents and optionally compiles the framework
  • createLibrary - creates a Progress Procedure Library (.PL) with the R-Code and a .ZIP file with configuration files (e.g. XML or JSON) and include files. This target can optionally be used to create archives used to execute the framework tools instead of from source code

The setup target is the default target. 

Both targets can be executed separately or together:

%DLC%\ant\bin\ant -lib Install/PCT/PCT.jar -f setup.xml -Dsmartdb="smartdb" -Dsmartdbdir="../../DB" -Dsmartdbsingleuser=true -Ddlc="%DLC%" -Dinstallroot=".." -Dcompile=full -Dassemblies="../Assemblies" setup createLibrary


  • createAssemblies - Creates an assemblies.xml file for a SmartComponent Library based development workspace. See assemblies.xml Generator for more details.