[IGN-14601] Database Query Browser can destroy table records when editing sorted data

Ignition
1

v8.1 / v8.3

A colleague found out the hard way that if you sort the result set from a query in the DB query browser and then edit the records and apply them, the edits are applied to the same selected indexes (not ids), of the unsorted table data. I.e. it will most likely edit the wrong records :dizzy_face:

This could be catastrophic for your table data.

Note in the screen capture the critical part is sorting the field, in this case the "name" field so that it reorders the data.

2

Dang, this is one of the oldest bugs I've seen in a while; the affected parts of this code haven't changed since we switched from another VCS system to git in 2011. And after all that time it's the classic Swing model index vs view index bug.

3

Huh. I never noticed that it was even possible to edit in the designer query browser result sets.

4

The edit button only enables if we detect the result set has a 'primary key' field we can use to correlate the edits back.

5

Is there a fix coming for this? I ask because it affects Power Table data when sorted too I believe?
mostly when attempting to ensure a selected row is visible.

6

The issue described on this thread has nothing to do with the power table; the power table doesn't do anything to your database you didn't script it to do.

7

Thanks for the clarification @paul-griffith - It was just that I wasted quite a bit of effort simply trying to get a sorted power table to ensure the programmatically selectedRow was always made visible. I ended up resorting to the jidetable methods to get it to function as desired. The only reason I even messaged here was because of your:

statement, which turns out to be almost the exact same one that was causing my problem.

In case anyone else stumbles across this thread with a similar issue this is my exact fix:

if event.propertyName == "selectedRow":

#### Do your selected row change logic here first then

# Using invokeLater fixes the timing bug where the UI hasn't painted the selection yet
def scrollTable():
    jideTable = event.source.getTable()
    selectedRowView = jideTable.getSelectedRow()
    if selectedRowView != -1:
        cellRect = jideTable.getCellRect(selectedRowView, 0, True)
        jideTable.scrollRectToVisible(cellRect)

system.util.invokeLater(scrollTable)
8

Jide tables have a scrollRowToVisible method.

Try:
event.source.table.scrollRowToVisible(event.newValue) or maybe
event.source.table.scrollRowToVisible(event.source.rowIndexToModelIndex(event.newValue))

9

I did try those from memory... I did try many forms, but what I noticed was that:

rowIndexToModelIndex(event.newValue)
convertRowIndexToView(event.newValue)
convertRowIndexToModel(event.newValue)

all returned exactly the same value as the event.newValue whether sorted or not.

I just tried both of your suggested forms of scrollRowToVisible() in the property changed and as an invoke later and neither work if the table has column sorting active and sorted by any column. (FYI the underlying dataset doesn't have a "primary key" as such if that makes a difference?)
I'll stick with what I have that is working, but thanks anyway for the suggestion.