Omnis Technical Note TNSQ0037a August 2019
Automatic Conversion to PostgreSQL
for Omnis Studio 10.0 or above
by Gary Ashford, Omnis Engineering
This feature allows applications that use legacy Omnis data files and DML commands to convert automatically into a PostgreSQL database. Once converted, the application continues to run almost entirely without modification, using PostgreSQL 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 DML commands 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 PostgreSQL
A new add-on tool in Studio 10 allows you to convert a single or multi-segment Omnis data file into a PostgreSQL database. 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 PostgreSQL. This will ensure that the conversion utility has access to your File classes and any connections.
When converting to PostgreSQL, you need to specify a logon configuration file (e.g. pgsql.dfp) as the conversion target. This is a simple text file which contains logon parameters and other session-specific properties. For Example:
After conversion, you can remove the Omnis data file(s) and retain them as backup if required.
Using the OmnisSQL DAM
Please note that if your application currently uses the OmnisSQL DAM, your librar(ies) will need to be modified to use the PostrgeSQL DAM. In most cases, this involves changing the session object's subtype to be PGSQLDAM instead of OMSQLDAM, and modifying the logon parameters. There is no direct PostgreSQL emulation facility built into the Studio 10 OmnisSQL DAM (as there is for SQLite).
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:
Your Open data file or Prompt for data file commands need to specify the PostgreSQL logon configuration file described above. If you name this to match your existing Omnis data file, then no code changes are necessary. For example, use:
Open data file /Users/myUser/data/myDatafile.dfp,myDatafile ## or
Open data file /Users/myUser/data/myDatafile.df1,myDatafile
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 PostgreSQL database.
Automatic Conversion to SQLite: TNSQ0037