Table Event onRecordUpdate() not Triggered by Foundset Updat

Questions, tips and tricks and techniques for scripting in Servoy

Table Event onRecordUpdate() not Triggered by Foundset Updat

Postby kwpsd » Tue Jul 08, 2014 12:27 am

Version: 7.4.1 - build 2028

I have a table event, onRecordUpdate(), that is not being invoked when databaseManager.getFoundSetUpdater()/fs_updater.performUpdate() is used on the same table. Is this normal? I was expecting the foundset updater to trigger onRecordUpdate() for each recorded visited during the update. Would someone please enlighten me? Also, does anyone know of a work-around?

Thanks!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby swingman » Wed Jul 09, 2014 11:41 pm

I don't think FoundSetUpdaters trigger table events... to trigger table events you need to loop through records and change them one by one.
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1472
Joined: Wed Oct 01, 2003 10:20 am
Location: London

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby kwpsd » Thu Jul 10, 2014 6:58 pm

I don't think FoundSetUpdaters trigger table events...


What is the underlying reason for this? It seems to me, for validation purposes, that onRecordUpdate() should trigger whether the record update is done by looping through the foundset or by using a foundset updater. I am obviously not understanding what the difference is...sorry to be so obtuse.

I will revert back to a foundset loop to get things to work.

Thanks!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby ROCLASI » Thu Jul 10, 2014 11:08 pm

Hi Kim,

To understand why the foundsetUpdater doesn't trigger the Table events you need to understand how the foundset updater and the Servoy Table Event works.
For one the Servoy Table Events are completely handled on the application layer so if you would use a third party tool and edit data from outside Servoy these events will not trigger (unlike database triggers).
FoundsetUpdater has 2 ways of working. The first way it actually loops through the foundset and updates one record at a time using the next() method. This will indeed trigger the Servoy Table Events (if not you should consider this a bug).
The other way is to update the foundset in one fell swoop using the performUpdate() method. What happens here is that Servoy doesn't use the foundset to loop through it but sends 1 single SQL update statement to the database and therefor the Servoy Table Event has no idea what happend.

Hope this explains things for you.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby kwpsd » Fri Jul 11, 2014 12:00 am

Now, I understand...great explanation...thanks, Robert!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby lwjwillemsen » Fri Jul 11, 2014 7:57 am

Hi,

What happens here is that Servoy doesn't use the foundset to loop through it but sends 1 single SQL update statement to the database and therefor the Servoy Table Event has no idea what happend.


Don't forget to mention the above is not so when Servoy logging is enabled on that table. In that case the update is always record by record.

Regards,
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 680
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby ROCLASI » Fri Jul 11, 2014 10:02 am

lwjwillemsen wrote:
What happens here is that Servoy doesn't use the foundset to loop through it but sends 1 single SQL update statement to the database and therefor the Servoy Table Event has no idea what happend.


Don't forget to mention the above is not so when Servoy logging is enabled on that table. In that case the update is always record by record.


True, almost forgot about that. The FoundSetUpdater will then default back to the looping.
I guess one could argue that if the table has Table Events attached it should do the same thing.
Kim, perhaps you should add a feature request for this.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby jgarfield » Wed Jul 16, 2014 5:01 pm

ROCLASI wrote:Hi Kim,
The other way is to update the foundset in one fell swoop using the performUpdate() method. What happens here is that Servoy doesn't use the foundset to loop through it but sends 1 single SQL update statement to the database and therefor the Servoy Table Event has no idea what happend.


I didn't want to believe this was true, since to me that would be completely unexpected behavior, and worse yet, is most definitely an undocumented deviation (https://wiki.servoy.com/display/DOCS/JSFoundSetUpdater).

I tested this locally with all three ways (you forgot one Robert :wink:) to use the JSFoundSetUpdater

Set by iterating: Triggers Table Events
Code: Select all
var fsu = databaseManager.getFoundSetUpdater(foundset);
while (fsu.next()) {
   fsu.setColumn(columnName, value);
}
fsu.performUpdate();


Set Column with Single Value: Does Not Trigger Table Events
Code: Select all
var fsu = databaseManager.getFoundSetUpdater(foundset);
fsu.setColumn(columnName, value);
fsu.performUpdate();



Set Column By Array: Triggers Table Events
Code: Select all
var fsu = databaseManager.getFoundSetUpdater(foundset);
fsu.setColumn(columnName, arrayOfValues); //arrayOfValues contains one value per record to be updated
fsu.performUpdate();


Since two of three methods of using the same object trigger Table Events, I would hesitate to call this a feature request and personally view it more as a bug. I believe that the general expectation, unless otherwise noted, is that when you work directly with Servoy provided objects (JSFoundSet, JSRecord, JSFoundSetUpdater) that you are in fact working within the provided application layer that would trigger your events. Only when you fall back to manual/raw methods (rawSQL, outside applications) would I lose the expectation of attached events triggering.

This seems to essentially mean that for any type of record that could ever trigger a record event, you cannot use the "Set Column With Single Value" option, or risk introducing bugs into your software down the line when adding a table event (maybe even in a different module that your original work has no knowledge of!). You could perhaps try and mitigate this by providing some sort of "per table, single value, column batch update" method that handles this edge case for you so that if you need to change the behavior in the future, you've only got one place to edit, but it certainly feels like unnecessary complexity and something that Servoy should be handling for you given their capacity to provide the most efficient solution possible.

TL;DR
This is preferably a bug, and, at minimum if not fixed, should be noted in the documentation as a "feature" :)


Edit: Reported https://support.servoy.com/browse/SVY-6854
Programmer.
adBlocks
http://www.adblocks.com
jgarfield
 
Posts: 223
Joined: Wed Sep 28, 2005 9:02 pm
Location: Boston, US

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby ROCLASI » Fri Jul 18, 2014 1:37 am

Hi James,

jgarfield wrote:I tested this locally with all three ways (you forgot one Robert :wink:) to use the JSFoundSetUpdater

You got me. :D To be honest I don't use the JSFoundSetUpdater, at all.
I know it when it was introduced (2.x). To me it's just a simple wrapper around a foundset OR a pure SQL query. I tend to do either one myself in code, it makes it all very clear what's happening.
In fact part of the issue is the hiding of SQL from the developer while SQL is in fact the developers friend. Sure there is the issue of the 'standard' SQL (which is not all that standard in practice) but an ORM like Servoy uses add an extra layer and adds more unexpected behavior than the SQL issues alone.
Sure it makes a lot of coding simple which is AWESOME, but when you really get your hands dirty with code you get a lot of unexpected behavior when using ORM's (value-lists anyone ?).
It's therefor important to know SQL and how Servoy translates code/objects into SQL because in the end it ALL ends up as SQL queries to the database.

Something I will talk about in an upcoming ServoyWorld 2014 session.
I would say to all who are reading this: SQL is a programming language, learn it.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby david » Fri Jul 18, 2014 7:20 pm

If you need data broadcasting with your batch data manipulation, foundset updater is the way to go.

With table events being implementing many versions after foundset updater, easy to see how one situation was forgotten about. Definitely a bug and I'm sure it was an oversight.
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby jgarfield » Fri Jul 18, 2014 7:53 pm

ROCLASI wrote:It's therefor important to know SQL and how Servoy translates code/objects into SQL because in the end it ALL ends up as SQL queries to the database.


I agree whole-heartedly. We have been known to apply raw SQL fairly liberally throughout our code base so I'm no stranger to the convolutions you need to go through (and the bugs this can cause..) to make sure that you manually databroadcast, or force refresh records from the database when needed.

That's why I think it's important to have clear boundaries of where you can work within the Servoy framework (and get all the associated events, side effects, conversions, etc) and when you are working outside of it. personally, I think the best way to deal with these boundaries is to have certain classes of abstraction (JSFoundSet, JSFoundSetUpdater) always work within the framework (to it's entirety). If the JSFoundSetUpdater didn't trigger a databroadcast event, everyone would agree that would be a bug. Personally I feel the same way about Table Events.

That being said, along with submitting a bug report, I've also submitted a documentation change request that would make this edge case clear within the documentation (in the event that the behaviour doesn't change). As you said, it's important to be able to know how working with particular objects translates to SQL and any side effects of this, especially when the abstraction is not consistent.
Programmer.
adBlocks
http://www.adblocks.com
jgarfield
 
Posts: 223
Joined: Wed Sep 28, 2005 9:02 pm
Location: Boston, US

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby jcompagner » Thu Jul 24, 2014 4:32 pm

what do you mean it is not documented?

https://wiki.servoy.com/display/DOCS/JSFoundSetUpdater

i see:

update entire foundset by a single sql statement; that is not possible when the table of the foundset has tracking enabled then it will loop over the whole foundset.
When a single sql statement is done, modification columns will not be updated, because it does the update directly in the database, without getting the records.

(also this page is under construction as far as i can see so it is then made a bit more clear)

But yes i guess if we detect table events we could just always start looping, but the performance case that JSFoundsetUpdater does bring is then gone.
(you could do that in your own code with a very simple loop anyway)
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby jgarfield » Thu Jul 24, 2014 5:54 pm

I'll agree that it says that modification columns will not be updated, and that in retrospect one could draw conclusions that Table Events would not fire, but you must admit that drawing conclusions is not quite the same as actual documentation :)

(my edits to make this more clear might be what you see as under construction)

If you do detect Table Events and start looping, would all the performance benefits really be gone? Does that mean that the other two methods of using the foundset updater (Set by Iterating and Set By Array) provide no performance benefits? I'm of the opinion that even if that is the case, I would personally prefer a consistent experience (i.e. using FoundSet related functions trigger all the appropriate framework events/side effects).

In the same way that you say we can perform that action on our own with loops, we also have the facilities to issue our own "UPDATE table SET column = ? WHERE id IN ()" statements if we are looking to eek out the performance and resolve events on our own.

By leaving the behaviour as it is, it is possible to introduce bugs into your system merely by adding a Table Event to a table that did not previously have one if you have used the Set Column with Single Value method anywhere in code. Conversely, if the JSFoundSetUpdater were to detect TableEvents and loop if necessary, then you are merely left with a performance hit, rather than buggy code, when you add a Table Event.
Programmer.
adBlocks
http://www.adblocks.com
jgarfield
 
Posts: 223
Joined: Wed Sep 28, 2005 9:02 pm
Location: Boston, US

Re: Table Event onRecordUpdate() not Triggered by Foundset U

Postby jcompagner » Fri Jul 25, 2014 10:01 am

i was more pointing the the line " update entire foundset by a single sql statement"
that should already say to everybody that just one update sql statement is send that can alter 100K rows in the db... so all records don't have to be on the client at all, so not all records are not loaded in, so no table events can be fired because the record itself is not even on the client.

If it starts looping then no there is no big performance gain at all, If you write a generic javascript function that does the same (so looping over records setting some value) then that would be quite the same.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet


Return to Methods

Who is online

Users browsing this forum: No registered users and 3 guests

cron