SmartList and sequence or serial primary keys

Doug Easterbrook doug at artsman.com
Mon Mar 16 12:27:30 EDT 2015


something like that...


all the better not to do it in omnis and move to postgres.


its really too bad that omnisql didn't support things like

> update FCONTADOR set CT_PK_COUNTER = CT_PK_COUNTER + 1 where ct_seq =xx returning  CT_PK_COUNTER


I know, i know ....   its omnis.  but the benefit of automatic sequence generation at the server is that it doesn't cause locks on high transaction tables and possible contention/deadlock.

I know in our days with the omnis data file.....   we made absolutely sure that we grabbed keys in a separate method (like you are).


however, we also made sure of two other things:
- that at the time of getting the seq #'s, the only file that was open in read/write mode was the key generation file  AND
- we also got a block of 20 to 50 at a time for the table in question so that we could cache them for performance.


the read/write mode meant that we generally got the key BEFORE puting the update into smart list


eg
do ivList.$set_pk() returns tempkey
set smartlist(ktrue)
  mykey = tempkey
  do work
set smartlist(kfalse)


yes, it wastes keys, but no contention of you are worried about that.



and if you are even more worried, add some sort of cache key counter to $set_pk:


eg.    general pseudo code is.....

if currentKey < MAX_KEY
   ; havn't used all keys yet, so return the next available one
   calc currentkey as currentkey+1
else
  ; ran out of cached seq #'s ... so get another 50
   currentkey as CT_PK_COUNTER
   calc CT_PK_COUNTER as CT_PK_COUNTER + 50    (50 keys)
    calc MAX_KEY as CT_PK_COUNTER
    update keyTable
end if

 return currentkey


I know... wastes some keys --   but faster when there are multiple inserts, say in a frequently inserted table.   not so useful of most activity is updates.


just some thoughts for you.   hope they help.




Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 536-1205    Fax (403) 536-1210

> On Mar 16, 2015, at 8:44 AM, IT <it at plastipol.com> wrote:
> 
> Thanks for all replies. Now I have a better view point to solve this in a proper way.
> 
> If you need to use OmnisSQL, the better is generate your own primary keys.
> So, before executing the $dowork method I run a method that fills the primary keys of the table or row if needed.
> In this way I can enjoy the easy of $dowork method
> 
> 
> Do $clib.$tables.tSC.$sqlclassname.$assign('sFTEXTOS')
> Do ivList.$definefromsqlclass('tSC')
> Do ivList.$smartlist.$assign(kTrue)
> Enter data
> If flag true
> 	Do ivList.$set_pk()
> 	Do ivList.$dowork()
> Else
> 	Do ivList.$clear()
> End If
> Do $cwind.$redraw()
> 
> 
> 
> 
> $set_pk
> =======
> Calculate ivPK as 'TX_PK' ;; Now is assigned manually but I will assign automatically using the column of the schema that has the $primaryKey = kTrue
> 
> Begin statement
> 	Sta: select CT_SEQ, CT_PK_COUNTER
> 	Sta: from FCONTADOR for update
> End statement
> Do $cinst.$statementobject().$prepare()
> Do $cinst.$statementobject().$execute()
> Do $cinst.$statementobject().$fetchinto(ivContadorSEQ;ivContador)
> If $cinst.$type=kRow
> 			If $cinst.[ivPK]=0
> 				Calculate ivContador as ivContador+1
> 				Calculate $cinst.[ivPK] as ivContador
> 			End If
> 		Else
> 	For $cinst.$line from 1 to $cinst.$linecount step 1
> 		If $cinst.[ivPK]=0
> 			Calculate ivContador as ivContador+1
> 			Calculate $cinst.[ivPK] as ivContador
> 		End If
> 	End For
> End If
> Begin statement
> Sta: update FCONTADOR set CT_PK_COUNTER = [ivContador] where ct_seq = [ivContadorSEQ]
> End statement
> Do $cinst.$statementobject().$prepare()
> Do $cinst.$statementobject().$execute()
> 
> regards
> 
> x
> 
> 
> El 14/03/2015, a las 16:35, Jean Marc Azerad <azerad.jm at wanadoo.fr> escribió:
> 
>> 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
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com



More information about the omnisdev-en mailing list