Omnis Technical Note TNSQ0037 August 2019
Automatic Conversion to SQLite
for Omnis Studio 10.0 or above
by Gary Ashford, Omnis Engineering
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.
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. Namely, 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.
Using DML Commands
If your application uses old-style DML commands to connect and interact with its data file(s), you will need to change a library preference, namely $clib.$prefs.$mapdmltodam. For example:
When you start or re-load your library, it will then use the DML emulation features built into Studio 10. For this, Studio 10 uses a library named omsqlconv.lbs (inside the startup folder). This library emulates the DML commands and models the behavior of the Omnis Data Manipulation Language (DML) as closely as possible.
The following DML commands and functions are emulated by the omsqlconv library:
|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 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.