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

Paul Mulroney pmulroney at logicaldevelopments.com.au
Wed Jul 27 08:22:31 UTC 2022


Hi Mike

That's what we're trying to achieve - a "no faff" demo. :)

That's the other problem - the really cool Postgres stuff that we can't do in SQLite!  I figured that I could just add some tests and have a "Not available in the Demo" type warning.  That's being lazy I think!

That said, there aren't a lot of places that we use the cool Postgres-specific functions, like sequences, CTEs, window functions ... wait, maybe we do!  Doh!

Regards,
paul.


> On 27 Jul 2022, at 3:57 pm, Mike Matthews - Omnis <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 <http://www.lineal.co.uk/>
> www.sqlworks.co.uk <http://www.sqlworks.co/>
> 
> 
> 
>> 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 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   where postgres runs as an app.   ... https://postgresapp.com <https://postgresapp.com/> <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> <mailto:doug at artsman.com <mailto:doug at artsman.com>>
>>> http://www.artsman.com <http://www.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> <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.
>> 


Definition: Absentee - A missing golfing accessory.
-- 
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





More information about the omnisdev-en mailing list