Enhancement: canCacheSync=false operationBindungs should return added PK

[Blama]

Hi Isomorphic,

please see this v10.1p_2016-01-19 BuiltInDS-based testcase:
supplyItem.ds.xml addition:

    <operationBindings>
            <operationBinding operationType="add" operationId="addNoCacheSync" canSyncCache="false" />
    </operationBindings>[B][/B]

BuiltInDS.java:

package com.smartgwt.sample.client;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.core.KeyIdentifier;
import com.smartgwt.client.data.DSRequest;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.data.Record;
import com.smartgwt.client.util.Page;
import com.smartgwt.client.util.PageKeyHandler;
import com.smartgwt.client.util.SC;
import com.smartgwt.client.widgets.IButton;
import com.smartgwt.client.widgets.Window;
import com.smartgwt.client.widgets.events.ClickEvent;
import com.smartgwt.client.widgets.events.ClickHandler;
import com.smartgwt.client.widgets.layout.VLayout;

public class BuiltInDS implements EntryPoint {
    private VLayout mainLayout;
    private IButton recreateBtn;

    public void onModuleLoad() {
        KeyIdentifier debugKey = new KeyIdentifier();
        debugKey.setCtrlKey(true);
        debugKey.setKeyName("D");

        Page.registerKey(debugKey, new PageKeyHandler() {
            public void execute(String keyName) {
                SC.showConsole();
            }
        });

        mainLayout = new VLayout(20);
        mainLayout.setWidth100();
        mainLayout.setHeight100();

        recreateBtn = new IButton("Recreate");
        recreateBtn.addClickHandler(new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                recreate();
            }
        });
        mainLayout.addMember(recreateBtn);
        recreate();
        mainLayout.draw();
    }

    private void recreate() {
        Window w = new Window();
        w.setWidth("95%");
        w.setHeight("95%");
        w.setMembersMargin(0);
        w.setModalMaskOpacity(70);
        w.setTitle("Enhancement: canCacheSync=false should return added PK.");
        w.setShowMinimizeButton(false);
        w.setIsModal(true);
        w.setShowModalMask(true);
        w.centerInPage();

        final DataSource ds = DataSource.get("supplyItem");

        {
            IButton btn = new IButton("Add normal");
            btn.addClickHandler(new ClickHandler() {
                @Override
                public void onClick(ClickEvent event) {
                    Record r = new Record();
                    r.setAttribute("itemName", "New with CacheSync");
                    r.setAttribute("SKU", "1");
                    r.setAttribute("category", "cat1");
                    r.setAttribute("unitCost", 1);
                    ds.addData(r);
                }
            });
            btn.setWidth(250);
            w.addItem(btn);
        }

        {
            IButton btn = new IButton("Add noCacheSync");
            btn.addClickHandler(new ClickHandler() {
                @Override
                public void onClick(ClickEvent event) {
                    Record r = new Record();
                    r.setAttribute("itemName", "New without CacheSync");
                    r.setAttribute("SKU", "2");
                    r.setAttribute("category", "cat2");
                    r.setAttribute("unitCost", 2);
                    ds.addData(r, null, new DSRequest() {
                        {
                            setOperationId("addNoCacheSync");
                        }
                    });
                }
            });
            btn.setWidth(250);
            w.addItem(btn);
        }

        w.show();
    }
}[B][/B]

If you click the 2nd button it will use the canSyncCache="false"-operationBinding.
This is returned in the Developer Console's RPC Tab:

{
    affectedRows:1, 
    invalidateCache:true, 
    isDSResponse:true, 
    operationType:"add", 
    queueStatus:0, 
    status:0, 
    data:null
}

More important, the SQL

=== 2016-01-19 17:33:28,067 [1-32] INFO  SQLDriver - [builtinApplication.supplyItem_add] Executing SQL query on 'HSQLDB' using connection '932072306': CALL IDENTITY()

or in Oracle "SELECT sequencename.CURRVAL FROM dual" is not executed.

In my application, in some places I'm only interested in the PK (eg when creating a log-entry), but not the cacheSync-data. Currently I have to leave canCacheSync enabled in order to get the generated PK.
For me, right now this is only server-side where I have those requests, so this would mean no changed facing the behavior towards the client. I'd just like to be able to get the generated PK on the server.

As the cacheSync request is most likely indexed and therefore not expensive, this is a very minor enhancement.

Best regards
Blama

[Isomorphic]

canCacheSync is usually set because something about the operation makes the cache sync data impossible to retrieve (as in, there would be a crash if we attempted it), or for performance reasons. So we definitely can't have it even attempt to return the PK.

We're not sure why you are trying to avoid returning the cache sync data but still return the PK. In most cases there is a negligible difference in performance between these two behaviors. But if you wanted to use cacheSync but just return the PK, the right approach would be to set operationbinding.cacheSyncOperation to a fetch operation that only returns the PK.

[Blama]

Hi Isomorphic,

as I wrote:

More important, the SQL

=== 2016-01-19 17:33:28,067 [1-32] INFO SQLDriver - [builtinApplication.supplyItem_add] Executing SQL query on 'HSQLDB' using connection '932072306': CALL IDENTITY()

or in Oracle "SELECT sequencename.CURRVAL FROM dual" is not executed.

In my application, in some places I'm only interested in the PK (eg when creating a log-entry), but not the cacheSync-data. Currently I have to leave canCacheSync enabled in order to get the generated PK.
For me, right now this is only server-side where I have those requests, so this would mean no changed facing the behavior towards the client. I'd just like to be able to get the generated PK on the server.

As the cacheSync request is most likely indexed and therefore not expensive, this is a very minor enhancement.

I'm only talking about serverside changes here (even though I displayed a Developer Console excerpt).
When you just need the generated PK for a FK field in the next ADD-request to a child table, a cheaper SELECT sequencename.CURRVAL FROM dual, that adds the new PK value to the sucess-DSResponse is enough.

Currently I can only do either full table-select or nothing. Your suggested solution is still accessing the table, while the sequencename.CURRVAL would be enough here.
I agree though, that this possibly is a very minor speedup and perhaps not worth the effort.

Best regards
Blama

[Isomorphic]

Given that the possible difference in performance seems very very minor and perhaps not even measurable on some DBs, and the feature seems unlikely to be used often, we don't currently plan to pursue this.

If you find a compelling and common use case for this feature and demonstrate a large performance difference we would consider putting it on the roadmap. And there is always Feature Sponsorship.

[Blama]

Hi Isomorphic, Hi @all finding this thread via search,

after reading 12.0p release notes today (a bit late), it seems that this feature is now available, see DSResponse.getKeys() and OperationBinding.makeKeysAvailable.

Best regards
Blama

[Blama]

Hi Isomorphic,

using this with v12.0p_2020-03-11 I noticed this warning (last row):

15:59:52.199 DEBUG com.isomorphic.sql.SQLValuesClause - [builtinApplication.addGUI] Sequences: {ID=__default}
15:59:52.301 DEBUG com.isomorphic.sql.SQLTransaction - [builtinApplication.addGUI] Started new mydb transaction "143154967"
15:59:52.305 DEBUG com.isomorphic.sql.SQLDataSource - [builtinApplication.addGUI] Setting DSRequest as being part of a transaction
15:59:52.311 INFO  com.isomorphic.sql.SQLDriver - [builtinApplication.addGUI] Executing SQL query on 'mydb' using connection '143154967': INSERT INTO .... nextval('T_LEAD_ID_seq'))
15:59:52.331 DEBUG com.isomorphic.sql.SQLDriver - [builtinApplication.addGUI] SequenceMode is not JDBC_DRIVER, skipping search for generated values
15:59:52.352 DEBUG com.isomorphic.sql.SQLDataSource - [builtinApplication.addGUI] Setting DSRequest as being part of a transaction
15:59:52.358 [B]INFO  com.isomorphic.sql.SQLDriver - [builtinApplication.addGUI] Executing SQL query on 'mydb' using connection '143154967': SELECT currval('T_LEAD_ID_seq')[/B]
15:59:52.391 INFO  com.isomorphic.sql.SQLDataSource - [builtinApplication.addGUI] primaryKeys: {ID=82}
15:59:52.394 [B]DEBUG com.isomorphic.sql.SQLDataSource - [builtinApplication.addGUI] Gathered all keys.  lastPrimaryKeys is now {ID=82}[/B]
15:59:52.396 [B]DEBUG com.isomorphic.sql.SQLDataSource - [builtinApplication.addGUI] add operation affected 1 rows[/B]
15:59:52.414 DEBUG com.isomorphic.datasource.DSRequest - freeOnExecute is false for request of type add on DataSource T_LEAD - not freeing resources!
15:59:55.278 [B]WARN  com.isomorphic.datasource.DSResponse - Failed to get field 'ID' value. DSResponse has no data.[/B]

I think the warning is not needed here as this is expected because of what I set on the operationBinding:

<operationBinding operationType="add" operationId="addGUI" serverMethod="doAdd" canSyncCache="false" makeKeysAvailable="true" />

Best regards
Blama