Developers

Omnis Technical Note TNSQ0037 August 2019

Automatic Conversion to SQLite

for Omnis Studio 10.0 or above
by Gary Ashford, Omnis Engineering

Introduction
This feature allows applications that use legacy Omnis data files to convert automatically to SQLite data files. Once converted, the application continues to run almost entirely without modification, using SQLite in place of OmnisSQL.

Why Convert?
We are taking steps to allow easy migration away from Omnis data files to more mainstream relational databases. This will ensure the future longevity and portability of your applications. Converting to a system which emulates OmnisSQL also allows us to address several limitations and long term issues that affect the aging code base that sits behind the Omnis Data Manager. Moving this functionality out of the Omnis Studio "core" and "componentising" it also facilitates easier code maintenance.

Converting to SQLite
A new add-on tool in Studio 10 allows you to convert a single or multi-segment Omnis data file into a SQLite data file. The conversion process copies tables, data and indexes and also preserves any Omnis File class connections (emulating them as foreign keys).

We recommend that you load your application before converting to SQLite. This will ensure that the conversion utility has access to your File classes and any connections.

After conversion, you can remove the Omnis data file(s) and retain them as backup if required.

Using the OmnisSQL DAM
If your application uses the OmnisSQL DAM to connect to its data file(s), you will need to make a change to your library code after conversion to SQLite. You will need to change the hostname parameter supplied to the $logon() session method to specify a .DB file in place of the .DF1 file. For example:

Do omSqlSess.$logon('/users/myUser/data/mydatafile.db','','') Returns #F

Changing the logon filename extension to .DB tells the DAM to use SQLite features that are built into the Studio 10 OmnisSQL DAM.

The SQLite code built into the OmnisSQL DAM is designed to model the old-style OmnisSQL DAM as closely as possible. In particular, it uses a SQL parser that replicates the features (and limitations) of OmnisSQL to ensure backward compatibility. If your application is still in development and you want to employ SQLite-specific functionally (e.g. encryption, procedures, triggers or extended ISO SQL), you may wish to further modify your library to use the SQLite DAM in place of the OmnisSQL DAM.

Emulating DML Commands
To enable emulation, you will need to change a root preference, namely $root.$prefs.$mapdmltodam. For example:

Do $croot.$prefs.$mapdmltodam.$assign('SQLITEDAM')

It is recommended that you re-start Omnis if you change modes as this allows the emualtor to re-initialse. To enable a specific library for DML emulation, set its $clib.$prefs.$dmlemulation property to kTrue:

Do $clib.$prefs.$dmlemulation.$assign(kTrue)

Once enabled, the DAM emulates all DML commands using SQL statements against your converted database. The accompanying omsqlconv library may still be called upon to perform certain tasks including dialog boxes and prompts.

The following DML commands and functions are emulated:

Data files: Close data file, Close lookup file, Create data file, Floating default data file, Open data file, Open lookup file, Prompt for data file, Set current data file, Set default data file, lookup().

Data management: Build indexes, Delete data, Drop indexes, Open runtime data file browser, Rename data.

Changing data: Cancel prepare for update, Delete, Delete with confirmation, Do not flush data, Do not wait for semaphores, Flush data, Flush data now, Prepare for edit, Prepare for insert, Prepare for insert with current values , Test for only one user, Update files, Update files if flag set, Wait for semaphores.

Files: Clear all files, Clear main & connected, Clear main file, Clear range of fields, Clear selected files, Set main file.

Finding data: Clear find table, Disable relational finds, Enable relational finds, Find, Find first, Find last, Load connected records, Next, Previous, Prompted find, Single file find, Test for a current record, Test for a unique index value.

Searches: Clear search class, Reinitialize search class, Set search as calculation, Set search name, Test data with search class.

Sort fields: Clear sort fields, Set sort field.

Lists: Build list from file.

Others: Begin reversible block, End reversible block, Quit all methods, $root.$getodbfilelist(), $datas.$makelist(), various sys() calls including sys(11..20), sys(82), sys(83), sys(139), sys(3000) & sys(3001).

Aside from setting the root preference and library preference, it should not be necessary to make changes to the library code since the emulated commands will automatically execute against the SQLite database. When $mapdmltodam is set, the Open data file and Prompt for data file commands will also invoke conversion to SQLite if a data file with the ".db" extension is not found. Create data file will create a new SQLite data file.

Using a Logon Config File
Once enabled, the emulator will automatically interpret a Omnis data file (.df1) file extension as a SQLite data file (.db). To set additional session properties during logon, it is possible to specify a SQLite logon config file (.dfq) in place of the data file name, for example:

Open data file c:\users\OmnisUser\data\myDataFile.dfq, mydatafile

The logon config file contains one or more key=value pairs and is formatted as follows:

hostname=c:\users\OmnisUser\data\myDataFile.db
logging=5

The special value logging directs logging information to the Omnis trace log. For basic logging, use 1. For additional logging, add 2. For performance monitoring and SQL logging, add 4.

See also
Omnis SQL Language Definition: Omnis Programming, Chapter 9
Automatic Conversion to PostgreSQL: TNSQ0037a