Developers

Omnis Technical Note TNSQ0018 March 2008

Using multiple SQL backends with Omnis

For Omnis Studio 4.3
By Doug Easterbrook (Arts Management Systems Ltd. www.artsman.com)

Preamble

This tech note started life as a response to a technical query entitled 'O$ PostgreSQL and Studio - multiple back ends' posted on the Omnis developer mailing list (hosted by www.omniscentral.com and subscribed to by most developers in the worldwide Omnis community). We asked the author of that response, Doug Easterbrook of Arts Management Systems Ltd., if we would publish the text of his response so all Omnis developers could benefit from his knowledge and experience of working with multiple SQL database backends. Doug's application, Theatre Manager, is a powerful, multi-database ticketing and customer management solution used across North America by many sports and arts organizations.

The Problem

Which SQL backend should you use?
Are you confused by the number of SQL databases out there?
Which one will be best for your application?
What are the benefits and features of each?

A Possible Solution

Whilst we cannot answer all of these questions easily, you can build your Omnis applications such that they are abstracted from the specific requirements of any given SQL backend database.

Take Doug Easterbrook and David McKeone at Arts Management Systems Ltd. They moved their Theatre Manager application from Omnis 7 to Omnis Studio and in the process faced a decision on which database to port their OmnisSQL data to... Let Doug explain the background to this.

It took us 5 years and many man months to port Theatre Manager from Classic to Studio. We started off with the design criteria that we'd use OmnisSQL. Then we found issues with SQL joins, so we looked at Frontbase and Openbase. Openbase was our choice at that time because it is so fast and because we also use Daylite; we wanted to leverage a single technology to talk to our database and to the Daylite database.

Then we discovered after a couple of years of intense development why Openbase is fast: because it holds all its indexes in memory. Although rock-solid however, we were finding that large databases could take a long time to recover following a crash. Our thanks to Scott and the people at Openbase for working through these issues with their excellent product, incidentally.

In an effort to improve on startup & database recovery times for large databases, we started looking for alternatives. We were extremely lucky a year ago when discussions with RD revealed the forthcoming existence of the PostgreSQL DAM [released in Omnis Studio 4.3]. It is one of the reasons that people should be on the Omnis Developer Partner Program (ODPP) to get full development support. RD has been fantastic in that regard.

Eventually, we went through four back ends... and entertained glorious thoughts of possibly supporting Oracle or MS SQL Server if clients should have those already.

How did we do it?

1) We created a table class called 'tablebase' (thank you Doug Kuyvenhoven and some insights from StudioWorks).

2) We created an abstract DAM layer that instantiates a 'PostgreSQL' or 'Openbase' or 'Frontbase' object. This allows us to always talk to an object we call 'tDatabase'. This is referred to throughout our application and in the table classes.

3) Whenever we construct SQL queries, we use the abstract object to help things, for example: dates, booleans, strings or what have you can be different in each back end, so we have tools in each backend server object that deals with that.

Our code might look like:

Calculate search as con('MY_FIELD ',tDatabase.$sqlLIKE('bob'))

the $sqlLIKE method converts this to a LIKE for Openbase and an ILIKE for PostgreSQL

We have helpers for $sqlLIKE, $sqlBOOLEAN, $sqlDATE, $sqlCHAR, $sqlTIMESTAMP, $sqlTIME, $sqlIN, $sqlBETWEEN.

4) We took it a stage further and added special functions such as $getUserCount() that would read the database and find out how many people are logged in to the database, which is handy for licence management and necessary because each back end does it differently. Since these are in our DAM object, we are now database independent.

All we have to do is create a DAM object for a database and we are nearly there.
Other functions we have implemented include:

$userExits()
$groupExits() (user name and group name management)
$getServerTime() (we no longer use #D and #T in our application - it is all based on the server timestamp so that things go into the database in the correct date/time order, regardless of the time at the workstation)
$createSequenceGenerators (i.e. the unique row keys- something has to create them!)
$createIndex()
$indexExists()
etc etc.

We've got some 40 or so functions that we can override if need be, depending on the DAM and the peculiarities of the database.

5) Join conditions and syntax are different in each database. So, our DAM object helps us there. We do not have any code that actually has the joins in them. We put the join conditions in a list and pass them off to a parser that returns the right SQL syntax for that back end.

We standardized on Openbase and Sybase syntax similar to field1 *= field2 or field1 =* field2 in our lists. For PostgreSQL this syntax becomes: left inner join and left outer join.

All that being said, you can take two approaches to database independence. You can pick one syntax, create your SQL and send it to a parser that picks strings apart to convert it to another backend. For example, suppose we have

Select * from myTable1 A, mytable2 B where a.field like '%bob% and a.seq =b.seq

which is fairly generic. Looks kind of like Openbase and you could convert it to PostgreSQL by:
$replace() like with iLike, etc.
but that gets complicated if there are sub selects, and so on.

The other approach is how we did it. A little more obtuse perhaps, but in the long run, who's afraid of another backend?

Calculate search as con('field ',tdatabase.$like('bob'),' and seq1=seq2)
Do list.$definefromtableclass('table')
Do list.$getallrecords(search)

and the table class does all the work of figuring out the join syntax, using the other helpers to inspect the SQL statement.

Not so obvious, but using this approach we only need change the object code if we add another database backend. Going from Openbase to PostgreSQL we had not been as rigorous as we should, so it took about a month. Should we work on another SQL back-end, the current rigor in the abstract classes means we can expect to implement another one in a fairly short time frame. (And we have some 2000+ classes and millions of lines in our application).

In summary, we say PostgreSQL is great! I'm just so happy that it runs so solidly and RD were the great people who made it possible.

Doug Easterbrook, Arts Management Systems Ltd.

About Theatre Manager: Theatre Manager is a integrated box office, development, education, tour, artist and volunteer management software solution for Theatre, Arts, Entertainment, and Sports Administration that runs seamlessly on Mac and Windows.

Contact details:-
Arts Management Systems Ltd.
Calgary, Canada
Web: www.artsman.com