Setup Demo Database (WAS:O$: SQLite, Table.$dowork() and sequence numbers)

Doug Easterbrook doug at
Wed Jul 27 13:28:01 UTC 2022

hi Mike/Paul

to the question - how do we do demo’s?

the foundational premise:  These days, the physical size of an installer is less of an issue than it was since there are lots of people with high bandwith internet connections.  After all ,APple and windows system updates are menacingly huge.

I’ve built installers that can have postgres + sample database + application and it just puts things into the right place.  the general gist is
- run postgres installer (from enterprise db, with flags that do it relatively silently with no input from users)

- on the mac, I create the postgres user and set password (using shell script).   windows has no need

- i use psql to create a user in postgres

- pg_restore to load up the demo database

- install our omnis app.

and done — an all-in-one installer.

 On mac, we use packages.     On windows, I’m still using an out of date installer package - but the process/commands is remarkably similar.

happy to share the key things we did.

as to size of installers:

omnis application is studio 10
- on windows - 200 meg
- on mac - 400 meg (because it is universal app, it was approx same size as windows before)

postgres + database
- approximately 400 meg on either platform.

so, for between 600 and 800 meg download (in total), have a look at this web site to make the time calculations

at 50 mbit download speed, its about 90 seconds
at 100 mbit download speed, it about 46 seconds

so, really not a long time.    triple that to account for virus checkers and other crap going on — and oyu can pretty much guarnatee a demo download in less than 5 minutes for most people.

and, what do you now get by using postgres

- don’t have to rewrite your app to use sqllite and then do double the testing, plus make sure that it works
- you can showcase multi user if you want
- you can show case performance, data, whatever you want to
- you don’t need to extract data from a postgres database and migrate to sqllite
- you don’t need to develop an indepth understanding of a second database

to me, that saves lots of time.

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at
Phone (403) 650-1978

> On Jul 27, 2022, at 12:57 AM, Mike Matthews - Omnis <omnis at> wrote:
> Hello Paul,
> We have the same problem.  We also have PG specific code, so then we would have to limit and kludge for SQLlite installs.
> But the main premise is that we get a working usable demo to a client that just runs, within the time it takes to download the demo files.  No faff, just click and run, and when they are either finished, it will continue, or be easily un-installed.
> Mike Matthews
> Lineal Software Solutions
> Commercial House, The Strand <x-apple-data-detectors://1/1>
> Barnstaple, Devon, EX31 1EU <x-apple-data-detectors://1/1>
> omnis at <mailto:mike.matthews at>
> <>
> <>
>> On 27 Jul 2022, at 01:49, Paul Mulroney via omnisdev-en <omnisdev-en at <mailto:omnisdev-en at>> wrote:
>> Hi Doug,
>> That's a good question.  The demo app is for windows, so isn't an option.  When I was creating Windows installers for our app, the install size is already quite large, and we separated the Postgres component out, so setting up the app requires two installers, and some configuration.
>> For the sake of a demo to show functionality (rather than performance), SQLite seemed like a reasonable option.  After all, the database is basically just a file.  When we were using Omnis Native Datafile for our database engine, a demo version was a no-brainer, because we could bundle a datafile with the install, set some flags and voila - demo version. Adding Postgres, while added incredible performance gains and functionality, also increased the installation complexity.
>> It really leads to the bigger question - how do people create demo versions of their software?  People ask us for demos, and I think that handing them some software to play with gives them a feel for the system.  On the other hand, I suppose we could do a screen recording and just save it as a video, but it doesn't really allow people to look at the parts of the system that matter to them the most.
>> I'd be interested to hear how other developers demo their Omnis-based software to potential new clients?
>> Regards,
>> Paul.
>>> On 26 Jul 2022, at 9:49 pm, Doug Easterbrook <doug at <mailto:doug at>> wrote:
>>> hi Paul.
>>> why do that?  why not use postgres for the demo?
>>> there is a personal   where postgres runs as an app.   ... <> < <>>
>>> in my thoughts, its easier to change the postgres you are takling to that to rewrite you app to use sqllite.    it would give potential customers a better sense of performance and actual application conditions.      I’ve used dam’s about a year ago  to try to talk to sqlite (I’d intended to used it to cache data locally)… and I found it slow.   very slow.   for not many records.
>>> Doug Easterbrook
>>> Arts Management Systems Ltd.
>>> mailto:doug at <mailto:doug at> <mailto:doug at <mailto:doug at>>
>>> <> < <>>
>>> Phone (403) 650-1978
>>>> On Jul 25, 2022, at 8:18 PM, Paul Mulroney via omnisdev-en <omnisdev-en at <mailto:omnisdev-en at> <mailto:omnisdev-en at <mailto:omnisdev-en at>>> wrote:
>>>> Hi $All
>>>> We're investigating using SQLite for a demo version of our software.  We use sequence numbers which are automatically generated for unique record IDs. Currently we use Postgres, and in our $dowork() method of our Table superclass we have the following to generate the new sequence number for us automatically:
>>>> $dowork:
>>>>> Do $cinst.$sendall($cinst.$setNewIdent(),$ref.$status=kRowInserted)
>>>>> Do default Returns vbOK
>>>>> Quit method vbOK
>>>> and the $setNewIdent method:
>>>>> Set reference voStatement to $cinst.$statementobject
>>>>> Begin statement
>>>>> Sta: {SELECT nextval('[$cinst.$servertablenames]_[isPrimaryKeyName]_seq')}     ;; Postgres - eg SELECT nextval('fContract_seq_seq')
>>>>> End statement
>>>>> Get statement vsQuery
>>>>> Do voStatement.$prepare() Returns vbOK
>>>>> If vbOK
>>>>> Do voStatement.$execute()
>>>>> Do voStatement.$fetchinto($cinst.[isPrimaryKeyName])     ;; If this is a row or a line in a list, return into this column
>>>>> Quit method vbOK
>>>> In SQLite, that doesn't exist.  Instead there's a RowID property, which is the equivalent of the sequence number.  The problem is, this is automatically generated AFTER the record is created, so we can't use the pre-emptive method that we use in Postgres.
>>>> My question is this:  is there a way to achieve the same in SQLite?    Is there a way we could add a "returning rowid" to the insert SQL command and then read that back into the table row/list instance?
>>>> Regards,
>>>> Paul.
>> "A user interface is well-designed when the program behaves exactly how the user thought it would."
>> Joel on Software
>> --
>> Paul W. Mulroney                                            We Don't Do Simple Pty Ltd
>> pmulroney at <mailto:pmulroney at>       Trading as Logical Developments
>> <>                   ACN 161 009 374
>> Ph: +61 8 9458 3889                                       86 Coolgardie Street
>>                                                                         BENTLEY  WA  6102
>> _____________________________________________________________
>> Manage your list subscriptions at <>
>> Start a new message -> mailto:omnisdev-en at <mailto:omnisdev-en at>

More information about the omnisdev-en mailing list