SmartList and sequence or serial primary keys

[Jean Marc Azerad]

Nice one Doug!

JM


> Le 13 mars 2015 à 12:21, Doug Easterbrook <doug at artsman.com> a écrit :
> 
> just to offer a twist on how Bas does it in postgres -- we didn't know about the $excludefrominsert
> 
> so we do it a different way in the table class.     we have a method (or methods) that return fields we don't want to actually insert -- like the unique key field.
> 
> 
> Calculate Field as $cinst.$getUniqueKeyField
> 
> 
> then we replace the   @[$cinst. field ]    in the insert values with  'DEFAULT'    eg
> 
> Do tStringFields.$replace(pStatement,con('@[$cinst.',Field,']'),'DEFAULT',pStatement)
> 
> 
> 
> this tells postgres to take the default value that the database suggests when inserting, rather than the value in the smart list.
> 
> 
> 
> This can be extended to a lot of things.  For example, we have timestamps on each of our tables for date entered and date updated.     So we make the default in the database for those kinds of fields to be now().
> 
> and then we replace a   @$cinst.XX_DATE_ENTERED  in the SQL statement with   'DEFAULT'
> 
> advantage of this approach is that the date entered field gets replaced server time (not local omnis workstation time -- which is generally off set in some fashion from the server).
> 
> With this same approach you can set the default value of fields to be functions if you wish...
> 
> 
> so, a statement that omnis generates in the $insert of a table class might go from
> 
> insert into   table (key_field,date_field,funny_field,...) values (@[$cinst.key_field], @[$cinst.date_field], @[$cinst.funny_field], ......)
> 
> 
> to
> 
> insert into   table (key_field,date_field,funny_field,...) values ('DEFAULT', 'DEFAULT', someFunction(), ......) returning key_field,date_field,funny_field...
> 
> 
> I find this approach incredibly helpful for synchronizing the date/time on log records across multiple machines --  its all about when the sql gets to the database and the order in which it is processed -- rather than what the time is on a machine.
> 
> 
> 
> but like Bas suggests ...  skip omnissql -- and go to postgres and you won't regret al the workarounds you have to build for omnissql
> 
> 
> 
> 
> 
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 536-1205    Fax (403) 536-1210