O$: SQLite, Table.$dowork() and sequence numbers

[Paul Mulroney]

Hi Doug,

That's a good question.  The demo app is for windows, so Postgres.app 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> wrote:
> 
> hi Paul.
> 
> why do that?  why not use postgres for the demo?    
> 
> there is a personal postgres.app   where postgres runs as an app.   ... https://postgresapp.com <https://postgresapp.com/>
> 
> 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 <http://www.artsman.com/>
> 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>> 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 logicaldevelopments.com.au       Trading as Logical Developments
www.logicaldevelopments.com.au                   ACN 161 009 374 
Ph: +61 8 9458 3889                                       86 Coolgardie Street
                                                                         BENTLEY  WA  6102