Same code for both databases

Sat Feb 11 15:05:06 UTC 2023

hi Daniel

We wrote our app with the intention of being able to switch out backends because we were not sure which db backend we’d end up using.    There was openbase, frontbase, oracle, postgres, the omnis DF1, and more as possible options.      (the history shows that postgres was the clear winner for many reasons — but thats a different topic).

so back to your question — how to make your application work independently of the chosen back end database choice.

like you, we made database objects for  postgres sessions, openbase sessions, omnis sessions, etc.

then we made a task variable called ’tDatabase which was an object reference.

at the beginning of the app, all we needed to do was create an instance of ONE OF (postgres, sqllite, openbase, frontbase) object and assign it to  tDatabase.

from that point on, we would use tDatabase to talk to the database — since it is now pointing to the right database object.

within each database back end, there are flavours of SQL since all SQL is more or less the same standard, with some minor differences.

omnis provides commonality in that each dam has $insert, $update and $delete, so most of that is taken card of to create independence.

one of the bigger differences in each flavour of SQL is the date format.

for example, postgres likes the following     MY_DATE >= DATE ‘2022-01-01'

so ,what we did was create HELPERS in each of the separate database objects that would create the database format for that falvour of SQL.   Examples that w have for postgres are

$login
$sqlIN
$sqlBetween
$sqlBoolean
$sqlDate
$sqlTime
$sqlTimeStamp
$sqlLike
$sqlChar
$sqlArray
$sqlOverlaps (which is a postgres feature for dates that overlap a range)

Using BOOLEAN as an example, since the comparisons are different for different flavours of sql.

postgres treats booleans as TRUE and FALSE so the dam has a $sqlBoolean function which is 

If pos(low(pValue),'yes`true`ktrue')>0|int(pValue)=kTrue ## v70501 - expanded to allow more options of what kTrue can be. DDS
Calculate queryValue as "TRUE"
Else
Calculate queryValue as "FALSE"
End If

Quit method queryValue

Frontbase treats booleans as 0 and 1,  $sqllBoolean is

If pValue=kTrue
Calculate Query as 1
Else
Calculate Query as 0
End If

Quit method Query

so, now I have TWO different $sqlBoolean funtions in each dam — that make the correct sql for the flavour of SQL I’m using.

how to make that work.     Lets build up a where clause using a ’statement’ block.

begin statement
sta:MyVariable = [tDatabase.$sqlBoolean(kfalse)]
end statement
get statement  whereclause

when using postgres, whereclause will look like

MyVariable = FALSE

when using frontbase, whereclause will look like

MyVariable = 0

NOW I AM DATABASE INDEPENDENT because I have functions that call back to tDatabase that give me independance of the variations in the language.

so, lets go over the steps again

1) make an object for each flavour of SQL you want to support (i.e. database backends.)  you’ll have one for postgres and one for sqllite

2) add some functions to each dam to handle when the sql standard results in ’slight’ differences.   some samples I’ve given you are below.  these will be in BOTH of the postgres and sqllite objects

$sqlIN
$sqlBetween
$sqlBoolean
$sqlDate
$sqlTime
$sqlTimeStamp
$sqlLike
$sqlChar
$sqlArray
$sqlOverlaps
$login

3) Make a task variable of type object reference.     in our application we called it tDatabase

this is what gives you the first step in making a general database connection

4) when you start your application, assign tDatabase with the object for ‘postgres’ or ’sqllite’, depending on what database you want to connect to.

5) whenever creating where clauses or any other SQL that you want to make,  use THE GENERIC FUNCTIONS to handle syntax difference in the sql.   eg.

begin statement
sta:MyVariable = [tDatabase.$sqlBoolean(kfalse)]
end statement
get statement  whereclause

6) Create a table class and query class that describes your table int he database — so you can use the V3Dam approach

do list.$defineFromSqlClass(’tMyTable’).   (where tMyTable is a table class for ‘myTable’)

7) combine it all together like

do list.$defineFromSqlClass(’tMyTable’)

begin statement
sta:MyVariable = [tDatabase.$sqlBoolean(kfalse)]
end statement
get statement  whereclause

do list.$getAllRecords(whereclause)

and the function $getAllRecords would be something that you have in the table class that executes a fetch of the data and puts it into the list

you could use the standard omnis tableclass methods for $fetch, $insert, $update, $delete, $dowork and they should generally work,    WE’ve found, over time, that we have extended those.

for example.    the $getAllRecords() that I mention above, executes a Fetch

we have extended it to be able to 
1) use cursors (a postgres function) to read XXX records at a time. (or read them all)
2) create queries that we can give to the postgers worker and occurr in the bakground
etc.

do you need to do step 7 at the beginning … no.       all you need to do is make sure that your key postgres and sql lite objects have al lthe helpers you need to keep you languate independent and do it in the way suggested using a ’tDatabase’ variable to abstract the function that helps build your SQL.

hope that helps.    its been very powerful for us.    as a testament to that — we were implementing in openbase as our first choice of database some 20 years ago.     it turns out it had a fatal flaw (start up time and all database in memory, so it did not scale at all)….  so we just made a postgers dam and in a couple of hard weeks, we converted the entire app over to postgres.

and that was only possible because we had 

tDatabase
a postgres object
an openbase object

and we had already abstracted the $sqlBoolean functions etc.

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Feb 11, 2023, at 12:24 AM, Daniel Sananes <daniel.s at kopparbergs.se> wrote:
> 
> Hi
> 
> I got an answer that solved the logon-problem. Now logon works.
> Now I can work with both an SQLite  database and a Postgres database.
> 
> Am thinking to continue the programming-studies making it possible to connect to either one of the two databases.
> With the same code.
> I have created two objects, oSqlite and oPostgres, with Superclass set to the 2 DAM's SQLITESESS and PGSQLSESS.
> There are also two taskvariables, tSqlite and tPostgres, with subtypes oSqlite and oPostgres.
> Do I really need the two objects oSqlite and oPostgres?
> Can I not instead set subtypes for the two taskvariables directly to the DAMs SQLITESESS and PGSQLSESS?
> This may be a stupid question but I do not understand the concept of objects at the moment.
> 
> To be able to use the same code (as far as it is possible) for both databases can I create a taskvariable that subtypes either of them?
> So instead of having Do tSqlite.$logon() and tPostgres.$logon() one can use tAnyDAM.$logon().
> Lets say I create a window asking if logon should be on Sqlite or Postgres.
> Would I then maybe have a taskvariable that in code assigns its subtype to one of them?
> 
> I am thankful for all answers to my questions and hope you don't get fed up with them.
> 
> Best regards
> Daniel
> 0736 704070
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 