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

Thu Jul 28 18:30:13 UTC 2022

I guess I’m going to have to learn the silent approach maybe.

However, I would also like to fully unload it all if the client doesn’t want to keep it.  I think there are a bunch of installers ready for PG, and if you use Packages, I guess the whole to will be unloaded?

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 lineal.co.uk<mailto:mike.matthews at lineal.co.uk>

www.lineal.co.uk<http://www.lineal.co.uk/>

www.sqlworks.co.uk<http://www.sqlworks.co/>

On 28 Jul 2022, at 03:52, Paul Mulroney <pmulroney at logicaldevelopments.com.au<mailto:pmulroney at logicaldevelopments.com.au>> wrote:

Hi Doug,

I baulked at creating such an installer, because I would need to figure out how to run the Postgres installer in unattended mode, pre-populate the database, as well as determine if there is already a Postgres installation.  Installer size is less of an issue - as you say, internet download times are getting much better, even in Western Australia where internet access is sometimes far from world-class.

All the pros that you list are valid points, however, and maybe I need to consider this further.

Regards,
Paul.

On 27 Jul 2022, at 9:28 pm, Doug Easterbrook <doug at artsman.com<mailto:doug at artsman.com>> wrote:

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 https://www.omnicalculator.com/other/download-time<https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fwww.omnicalculator.com%2fother%2fdownload-time&c=E,1,BUSK6vUhQ3sTPyv2o0Pg-iKPkBhr5e-nIkstx6bvMINbN1KMG8LaeNMSPxaT2gpLG9NWPKmkUmmMyrm4dF10cuLhv8TLuJtV4WX3VThXq7p2UraJ1m76QAbG&typo=1> 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 artsman.com
http://www.artsman.com<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.artsman.com%2f&c=E,1,r6ET_r3UJYWhLKQutXOWSFh87kjFwYe2aEda-Kzn2yDLxU65MWuT6fJELvqhfxSTQR23a0-ogKlKfSTKPJUEDJ7LnKb_IDyyTzz9nmc9&typo=1>
Phone (403) 650-1978

On Jul 27, 2022, at 12:57 AM, Mike Matthews - Omnis <omnis at lineal.co.uk<mailto:omnis at lineal.co.uk>> 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 lineal.co.uk<mailto:mike.matthews at lineal.co.uk>

www.lineal.co.uk<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.lineal.co.uk%2f&c=E,1,QlHyYBtlJpDrvbo0_PkFcydQkPjNhrxBXfenMkwJfnHeXodPBTMc3ophC56KpknwmK-i-hre9vu7EMofTMbbYWblwv1R3yLCVUyU82TdPvOX8_KNsevNOg,,&typo=1>

www.sqlworks.co.uk<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.sqlworks.co%2f&c=E,1,HK5FPViGDToPZ0vAdhkXeYLlPmHdiEkaeKaN8IVeuvAwVClGidA6k6a8k2xwx7T1sVZd-4iwbHeIQgl4eZ2QQJHQUb5tITOvfWryIB4UixqbRIfhBQ,,&typo=1>

On 27 Jul 2022, at 01:49, Paul Mulroney via omnisdev-en <omnisdev-en at lists.omnis-dev.com<mailto:omnisdev-en at lists.omnis-dev.com>> wrote:

Hi Doug,

That's a good question.  The demo app is for windows, so Postgres.app<https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fPostgres.app&c=E,1,8xbH7zNjRN5BU1ta1dBYSGg_gYpI0G1YD5bNGBgw3pmeCgRkl_Pk-9659uoKcUm33nP-S6L5Dop-8-K0So6bLB_JGUTLJQoJ3lvHU0vExGPS8UIT&typo=1&ancr_add=1> 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 artsman.com<mailto:doug at artsman.com>> wrote:

hi Paul.

why do that?  why not use postgres for the demo?

there is a personal postgres.app<https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fpostgres.app&c=E,1,qwM7dKBjo9x3QQAHmWuSzd_i8j3RQ6FsSDmunSy0pK3mERyUl6PSybCpZ8uXMLDkE-1yLnc8Sqk3DtB47DtXrFrB8483uG7bARFGK3EA4yzb4l-Wmkv-_w,,&typo=1&ancr_add=1>   where postgres runs as an app.   ... https://postgresapp.com<https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fpostgresapp.com%2f&c=E,1,qr_6_UgPe6Gc-6ajO6p_zYoxSw3RG-84TrIcdE_kbpj_hKTFUykNDY5_9aLJNPjIb7rd0tEKlP3MicBYP4W3b2CT23o97xEYkyCGWryZfAbHy_VaiImf2aw,&typo=1> <https://postgresapp.com/<https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fpostgresapp.com%2f&c=E,1,N13tEHw2S6JHeqM6sbarX_cVoheEfvv8bO84EoAehXNF4ON06XMGAPuXvNfLgwY3eyKnSx33UyJWPq7mO6gsISM0SsyKNMFB7W2g8MF-9is3IrPlHI4i0A,,&typo=1>>

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 artsman.com <mailto:doug at artsman.com>
http://www.artsman.com<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.artsman.com%2f&c=E,1,zf6N35Xq80N7vbs-WHdzM2Zyp5695LkJ1qdBdr-HV9UkvkwP95IOIEVttXtiAiKVnrInDdOeHBbOuD5I5sitEGhxaECJ9tV6kjtXgKKLeZzkOWP77lhL&typo=1> <http://www.artsman.com/<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.artsman.com%2f&c=E,1,MB6LSAYW_K0SZIwZCjGFr8nYv5UL5omG9pO8f1UWLPmW82C70NjG77-oadx00B1UAJyGGkI21COGb2nog3vmkENpidukvEpql7n3VlcZgwk,&typo=1>>
Phone (403) 650-1978

On Jul 25, 2022, at 8:18 PM, Paul Mulroney via omnisdev-en <omnisdev-en at lists.omnis-dev.com<mailto:omnisdev-en at lists.omnis-dev.com> <mailto:omnisdev-en at lists.omnis-dev.com>> 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

I refused to believe my road worker father was stealing from his job, but when I got home, all the signs were there.
--
Paul W. Mulroney                                            We Don't Do Simple Pty Ltd
pmulroney at logicaldevelopments.com.au<mailto:pmulroney at logicaldevelopments.com.au>       Trading as Logical Developments
www.logicaldevelopments.com.au<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.logicaldevelopments.com.au&c=E,1,9tDdcGQ_69y59Gn2SwfeCt8wqVXm3ZcLqsf9ruoEu2Cg201bCV4dgyueJBRskGRinZmTKySZByQ2mHAFu8xN0GbNWmr-8L70wb4N8VZUOKFSlo3YgBY,&typo=1>                   ACN 161 009 374
Ph: +61 8 9458 3889                                       86 Coolgardie Street
                                                                         BENTLEY  WA  6102