Migrating Data from V4.1 to V5

Version Relevance: V5

Issue: We normally carry out our own Caliach Vision major upgrades and data migration. What do we need to know about migrating our Caliach Vision 4.1 datafile to V5 standards?

Background: From V5 onwards, Caliach Vision data is no longer held in an Omnis Native Datafile (OND), but is stored instead in a SQL database. This has ramifications for installation, data management and customisation.

Feb 3rd, 2015

Feedback: This article will outline the process of preparing for the migration of V4.1 data and customisation to V5 standards, and will explain how to use the V5 data migration tool, V4To5Upgrade.lbs. If you are on a version before V4, you should first read the Knowledge Base article on migrating older datafiles to the latest version: V4 - Data migration from older datafiles. Contact Caliach Ltd for V4.1 licence Omnis Studio serial numbers to enable the installation and download the latest V4.1xxx installer, installing the full System Manager option.

Database Engine Choice, and Installation

Four SQL database options are available for storage of Caliach Vision V5 data - SQLite for one-user sites or demonstrations; PostgreSQL (open-source); MySQL from Oracle (open-source for the time being); or MariaDB (an open-source drop-in alternative to MySQL). SQLite uses a single operating system datafile, and the other three use server-based database 'engines' (programs or services), which manage their own system files to store the data, and are communicated with using a TCP/IP address and port number.

For all but SQLite (which is not appropriate for multi-user sites) you will need to choose the appropriate database engine, and download it and register with the supplier. Caliach Ltd will provide the relevant Caliach Vision licence keys and Omnis Studio serial numbers for your choice. If you install MySQL or MariaDB you need to ensure that you are using the InnoDB engine with innodb_file_format='Barracuda', and inno_db_file_per_table='ON', and that when tables are created ROW_FORMAT=DYNAMIC is applied (these are all configuration options). For all engines you will need to add users to the database engine configuration with at least 'superuser' privileges, so that Vision can carry out all required actions on the V5 database to be hosted by the engine.

Database Engine Maintenance, Backup and Support

For versions of Caliach Vision before V5, the datafile is typically hosted on a server configured for safe network access and sharing of the datafile, and you ensure that the datafile segments and associated system files are regularly backed up for security. You may have an in-house IT support team responsible for this, or, more commonly now, you probably have a contract with an outside IT support provider who manage your computers, network and server to ensure appropriate configuration, backup and maintenance. Datafiles were standard operating system files and backups were only reliable if all users were disconnected.

For V5, you therefore need to ensure that your in-house or external IT support team have the necessary skills to install, configure and maintain your chosen database engine, including daily (at least) backups of the database (these typically take the form of SQL 'dumps', not simple operating system file backups). If this is not the case, you may want to review your support options - there are a number of 3rd party support organisations for each database engine in the marketplace. For backup, if you choose the (more expensive) Enterprise option when you download Oracle MySQL, it comes with a powerful backup utility. For all three of the database engines however, there are a range of open-source and paid-for backup utilities for your IT support team to choose from. There are also numerous options for replication, clustering, and connection pooling that can ensure high-reliability security.

Customisation

The V5 migration tool V4To5Upgrade.lbs will migrate any custom file classes or field additions or modifications found in your Change Management System (CMS) customisation transport file, ProgCode.usa. The tool does NOT migrate any other customisation such as reports, windows or oCustom objects, as all legacy references to V4.1 OND files or fields, and datafile management calls, will need to be replaced with SQL equivalents. (With SQL, OND files become tables, records become rows, and fields become columns). Any additional customisation you may have will need to be re-written to V5 standards, which you can commission Caliach Ltd to do for you. If you want to do this yourselves, there is some guidance in the following Knowledge Base article: Customisation Migration to V5. And if you have any more than simple file class field length modifications, you should particularly note the extra migration stages necessary in that article. If you have custom file classes or customised standard file classes, you will need to do a trial migration to prepare the new customised table and schema classes and/or a cCustomDataChanges code class first, before completing customisation and carrying out the final migration pass.

Folder and File Structure Preparation

After installing and configuring your database engine, we suggest you next install and licence the full System Manager installer option of Caliach Vision V5, on the same machine as the database engine, and connect temporarily to the TrainingDemo.db SQLite database in the local Data folder while you apply any customisation applicable as above, and update Vision over the internet. You can install Vision V5 on a machine connected across the network for the migration if you want, but migration performance across the network will be considerably slower, (which you could test with a trial migration first, to see if the necessary downtime will be acceptable to you).

You then need to gather all your datafile segments, and the shared supporting files and folders that reside alongside them on the same machine, if they are not already there (at least the Custom folder and it's contents should be present, as well as all the datafile segments and any ancillary datafiles for keyword, document linking and language changes).

Next you must set up a new folder structure for any new V5 equivalents generated by the migration process. While, for all but SQLite migrations, the main data will not be held in a datafile but managed by the database engine instead, this new folder structure on the server is necessary to hold ancillary SQLite datafiles generated on migration and ultimately shared by users, as well as the new Caliach licence file in the Extras folder, plus any custom libraries in the Custom folder, along with your new CMS file – ProgUser.db - in the root.

In Caliach Vision V5 parlance, the Direct Path is the full operating system path to the folder where common auxilliary files can found by the program. It is made up of two components appended: The optServerFolder, in your Terminal.inf file in the Terminal folder, and the Company Subfolder that is controlled by your logon settings and can vare for multi-company installations. - e.g.

...\DataServer\V5Database, where V5Database is your Direct Path folder, ...\DataServer\V5Database is your optServerFolder and with no Company Subfolder (illustrated below), or

...\DataServer\V5Database\BuildingServices, where BuildingServices is your Direct Path folder, ...\DataServer\V5Database is your optServerFolder and BuildingServices is the Company Subfolder

In the graphic below, the folder DataServer\V4Data\ represents your old server datafile drive or share, with the Custom folder containing ancillary datafiles that may be needed by the V4To5Upgrade.lbs migration tool, while the root contains your V4.1 datafiles to be migrated and ProgCode.usa CMS datafile, if it contains custom file classes or customised standard file classes. If you do not have such customisation you can leave it out.

The folder ...DataServer\V5Database\ is the new Direct Path as we are not using a Company Subfolder, with Custom and Extras folders (initially copied from the root of your new V5 Vision installation), here containing migrated ancillary databases (and it will also contain the eventual SQLite main database if that was your database migration choice). (Any legacy keyword, link and language datafiles are rolled into the main database on migration, and will no longer require separate databases).

Folder Structure

Migrating the Data

You should now be ready to use the V4To5Upgrade.lbs migration tool. You will find this in the Upgrading folder of your Caliach Vision folder tree. As each migration will modify this tool, in case you do multiple migrations we recommend you copy this tool to the root of your V5 installation each time, and always run it from there.

You should now run either the Design or Runtime Edition of Omnis Studio V6+, and from there open the V4To5Upgrade.lbs you have just copied over. When you open it, you will see Step 1 of the Migration Wizard:

Migration Wizard Stage 1

Browse to or manually enter your Server Folder path, and on migration it will automatically be added to a Terminal.inf file copied to the Resulting Direct Path folder, under the option optServerFolder. This file should be copied to your local Terminal folder immediately following data migration, before connecting with VisionV5.lbs.

For Mac OSX, the folder path above would be entered as:

Volumes:DataServer:V5Database, where DataServer has been mounted as a volume.

Note that where you have mixed operating systems, differing network access paths or mapped drives, following migration you may need to edit optServerFolder in the Terminal.inf file accordingly after copying to other users installations.

If you have multiple company datafiles, you may want to enter here the Company Subfolder, otherwise leave it blank.

On Next you will see the Step 2 Wizard pane and in this example Select has been clicked, and the datafile to be migrated has been selected, and any additional or auxiliary datafiles found:

Migration Wizard Stage 2

All V4.1 main datafile segments (.df1, .df2 etc), and the keyword datafile (.kwd), the linked files datafile (.lkf) and the language swap datafile (.lsw), when they exist, will be integrated into the main database on migration. Any of the 4 auxiliary datafiles found (in the Custom folder) will be listed, and will be migrated into separate SQLite databases in the V5 Custom folder on migration, if you select Include. Note that you only need to include these files if you have custom data within them.

For a trial migration, or a first pass migration for pre-customisation conversion, you can use a live datafile (as it only reads), but for the real thing you must shutdown all use of V4.1, and restart work on V5 when upgrading and migration is complete.

Note that from now on you can now see a summary of previous steps at the top of the window.

After clicking Next to load Step 3 you can choose to Check for Customised Data Structures to Migrate, which if found and listed, will be applied to the migration tool, and added to a ProgUser.db created in the Direct Path folder and the active Custom folder during migration. If this is the final migration after customisation, you should NOT click this button, as your customisation should already be present in the V4To5Upgrade.lbs you are using and the ProgUser.db will have already been prepared during the first trial migration pass and subsequent customisation full conversion.

Note: If you have any more than simple file field length modifications, you should follow the sections relating to maintaining the V4To5Upgrade.lbs outlined here: Customisation Migration to V5

Migration Wizard Stage 3

The Omnis Studio Trace Log has now opened to later report migration progress and any errors, and you should leave it open and re-arrange window positions so you can see both the Wizard window and the trace log.

After clicking Next again, you will see the Step 4 display, where you set up the SQL Database Logon parameters:

Migration Wizard Stage 4

Here, after operating the drop-down list of database engines, we have chosen to migrate the datafile TrainingDemo.df1 and additional datafiles to a PostgreSQL database called pgsqltrainingdemo, on localhost with the default port number of 5432, where the database engine has a user called caliach set up with suitable privileges and strong password. If the database already exists, it will be cleared and re-populated, if not it will be created first. If the database engine was hosted on another machine, localhost would be replaced with the IP address of that machine.

The details you enter here will end up in the file Logon.db in the Direct Path folder listed at the top of the window, in the Extras folder.

If we had selected SQLite as the database engine, we would just enter the file path of the database file to be created, as below:

SQLite database paths

For MacOSX clients, the path must be a POSIX-like path using / delimiters, not the usual : system, and where you have mixed clients, both must be entered. The first icon against the paths will enable you to select a folder to create a new database file in, while the right-most will let you browse to an existing database file which will be re-populated.

These paths can later be be pre-fixed by a Terminal option optHostPathPrefix to accommodate different individual machines, but for the migration you must enter an absolute path as above.

Next will take you to Step 5 where you will confirm the Company licensed name for the datafile to be migrated, enter a suffix for the user Logon database selection, and enter a Caliach vision User ID and password. You should enter an ID of SYS with no password, as when you first logon to the database your existing V4.1 user set will not yet have been migrated. If you are re-migrating the existing tables will be dropped and re-created.

In this example we have used a suffix of trainingdemolocal to distinguish the resulting logon entry from one where an IP address has been entered instead of localhost (where we might use trainingdemoremote instead) to allow logon from other machines.

Migration Wizard Stage 5

If you check Make this login the default, the Terminal.inf options will include an optDefaultCompanyName entry that will default users to this specific entry in Logon.db.

On Next you will be taken to the final Stage 6, where you can start the migration process.

Migration Wizard Stage 6

The options here relate to migration optimisation and trouble-shooting, and the defaults should be good in most cases. If errors are reported during the process, then you should contact Caliach with the resulting trace log and migration results report, for a review of your data and on-site assistance with the migration, if necessary.

On clicking Migrate, the following will take place:

  1. The local Terminal\Terminal.inf file will be adjusted with the appropriate options and copied to the Direct Path folder.
  2. The Logon Settings file Logon.db will be created or amended in the Direct Path folder Extras\Logon.db.
  3. Any Data Structure Customisation will be applied to the migration library and the V5CustomDataStructures.lbs library will be moved to the Direct Path folder Custom folder. (Only applies if this is the first migration pass with custom file classes, and if you have chosen to list classes found.)
  4. Any Data Structure Customisation will also be saved into the Direct Path in a ProgUser.db CMS data file to upgrade Vision.lbs. (Only applies if this is the first migration pass with custom file classes, and if you have chosen to list classes found.)
  5. Unless Include is unchecked, local WishBugs, HelpData, Language and Translate ancillary files found in the local or V4Data Custom folder will be migrated to .db files in the Direct Path Custom folder.
  6. A Logon will be performed to the SQL engine of your choice, and a database either created with a data structure of tables and indexes, or if already existing the data structure will be checked for synchronisation; a Caliach Vision user session will be started.
  7. Migration of data from the main V4.1 datafile segments into the SQL database will take place, table by table in alphabetic order.
  8. When complete, a Logoff is performed.

The open Trace Log will list progress, and any errors, and should be printed out. When complete, all files are closed and the SQL sessions logged off, so if errors are encountered you can attend to them and use Back to repeat the process.

If successful, you will be taken to the results page, where you should review and Print Results, clicking Finish to close the migration wizard and Omnis Studio.

Migration Wizard Completion

Immediately after closing, you should copy the Terminal.inf from the Direct Path folder to your local Terminal folder, to ensure Vision.lbs can find the server folder and hence your new ProgUser.db, to update Vision.lbs with the new data structure changes.

If you have custom file classes or customised standard file classes in V4.1, you should now use the ProgUser.db in the Direct Path folder as the starting point for any customisation conversion, as described in the V5 Customisation Knowledge Base article. Once all customisation is complete and tested, and fully applied to VisionV5.lbs and custom schema and table classes and cCustomDataChanges copied to V4To5Upgrade.lbs, you can get all users off the system and carry out the final data migration.

Following the final migration, you can start Vision and logon to the new database. You should be presented with the Logon window:

Vision Logon

Paul Wilson - Cosultant and QC