updateData & Custom Criteria

[mandrachek]

I have a situation where I want to to run an update on all records in a grid that match the current criteria.

I've tried creating it like this:

<operationBinding operationType="update" operationId="replace" allowMultiUpdate="true">
   <customSQL>
      UPDATE MYTABLE 
      SET $defaultValuesClause
      WHERE MYTABLE_ID IN (
        SELECT MYTABLE_ID FROM
        getValues($advancedCriteria.DATABASE_ID) MYTABLE
        WHERE $defaultWhereClause AND EDITABLE = 1
      )
</customSQL>
</operationBinding>

On the javascript side, I call:

ds.updateData({"FIELD":"NEW_VALUE"},null,{operationId:'replace',criteria:myGrid.getCriteria()});

The query that's output in the logs server side looks right, except it doesn't see my criteria at all (and it's there, if I do an isc.say(JSON.stringify(myGrid.getCriteria())); it looks just fine).

I get this on the console:
SQLDataSource - [builtinApplication.replace] Performing update operation with
criteria: {} values: {FIELD:"NEW_VALUE"}

and so getValues(null) returns nothing, and nothing gets updated.

Is there any way I get updateData to see my criteria?

SmartClient Version: v9.0p_2013-08-02/PowerEdition Deployment

User-Agent: User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/28.0.1500.95 Safari/537.36': Safari with Accept-Encoding header

[mandrachek]

I was able to temporarily work around this by copying the RPC created when calling updateData via isc.showConsole and setting my "values" and "criteria" manually, and sending it via RPCManager.sendRequest().

This is not ideal, but it works.

var ds = grid.getDataSource;
var request = {
   "actionURL":ds.dataURL, 
   "showPrompt":ds.showPrompt, 
   "transport":ds.dataTransport, 
   "promptStyle":"cursor", 
   "bypassCache":true, 
   "data":{
      "criteria":grid.getCriteria(),
      "values":{}, 
      "operationConfig":{
         "dataSource":ds.ID, 
         "repo":null,
         "operationType":"update"
      }, 
   "componentId":grid.ID, 
   "appID":"builtinApplication", 
   "operation":"replace", 
   "oldValues":{}
   },
   "callback": function() {
      grid.invalidateCache();
   }
}
// set values
request.data.values[field.name] = item.getValue();
// set old values == values ? 
request.data.oldValues[field.name] = item.getValue()
// send request.
RPCManager.sendRequest(request);

[Isomorphic]

It's not absolutely clear from your description what would be causing this.
We're taking a look, but if you could provide us with your datasource definition (no need for any server side code - just the basic .ds.xml file showing us the structure and any custom settings that may be relevant), and a snippet of script that demonstrates the problem -- an updateData call with hardcoded criteria should be sufficient, this might help pin down what's going on.

Regards
Isomorphic Software

[Isomorphic]

Actually on reflection, the right way to do this is really to make use of the performCustomOperation() method. updateData() makes some assumptions about the operation applying to just one record.

Calling performCustomOperation() (and setting the invalidateCache flag) should ensure the operation respects the properties set on the requestProperties object, and that when it completes the dataSource re-fetches as necessary

Regards
Isomorphic Software

[mandrachek]

Thanks. I'll look into replacing the RPC call with the performCustomOperation.

Assuming I pass the same content in the data parameter, (criteria, values), will the $defaultValuesClause, $advancedCriteria, and $defaultWhereClause still be available like in my first post?

[Isomorphic]

Yes, but indirectly. What you would do is implement a DMI for the custom operation that creates a new DSRequest() server-side and populates it with the data you pass to the custom operation. When you then execute() that dsRequest, your various SQL Templating declarations will execute as normal.

Note that we haven't added a more direct way to do this from the client because it's extremely rare that an end user is allowed to update or wipe out whole sets of records from a table. We think of allowMultiUpdate as mostly a feature used for server-initiated dsRequests where any inputs from the client have undergone careful validation.

[mandrachek]

Yeah, our use-case is pretty specific. The user has access to edit the contents of a large grid - large both in terms of the number of columns and the number of rows. And they quite often need to change all the values of column X to "newvalue", where the rows meet some criteria (column Y = Z, and expiration date < 1/1/2013 for example).

So that could be 2,000 rows out 10,000.

So we use the filters on the grid to establish the criteria, and have added an entry in the grid header that allows them to replace the values in a given column (we copy the editor configuration for that column), but which applies only for any rows that match the filter on the grid.

[Isomorphic]

Yes, that's probably the most common case of a user allowed to do multi-updates. And for this kind of case, you'd typically want DMI logic that verifies that column X is the *only* column in dsRequest.values.

If this comes up frequently and the need to use performCustomOperation() gets cumbersome, consider Feature Sponsorship - there could be a dedicated client-side API for multi-updates, and perhaps a declarative way to limit such updates to just one field (or the few fields that are allowed to be updated this way).

[mandrachek]

I just tried this method. While I am able to get the values and criteria, I couldn't successfully create a new DSRequest (must be missing some values that must be set).

Using the existing dsRequest however I can reset the values and criteria, and I can check the .ds.xml for a canReplace="true" attribute. Everything comes through like you'd expect in my <customSQL/> with one exception -- the $defaultValuesClause comes across as:
"(FIELD) VALUES (VALUE)", like an operationType="add" for an SQL insert, not an update "[FIELD] = VALUE".

Anyway to change this behavior, given that I'm using a "custom" type, or another variable I should be using?

ReplaceDMI.java:

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import com.isomorphic.datasource.DSField;
import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;

public class Replace {
	
   @SuppressWarnings("rawtypes")
   public DSResponse replace(DSRequest dsRequest, HttpServletRequest servletRequest) throws Exception {
			
      Map requestValues = (Map)dsRequest.getValues();
			
      if (requestValues == null || !requestValues.containsKey("values")) {
         throw new Exception("No values provided.");
      }

      Map values = null;
      if (requestValues.get("values") instanceof Map) {
         values = (Map)requestValues.get("values");
      }
			
      if (values == null) {
         throw new Exception("No values provided.");				
      }
			
      if (values.size() < 1) {
         throw new Exception("No column specified.");
      }
			
      if (values.size() > 1) {
         throw new Exception("Multi-column replace is not permitted.");
      }
			
      // look up the field definition from the datasource.
      String columnName = (String)values.keySet().toArray()[0];
      DSField field = dsRequest.getDataSource().getField(columnName);

      // no field, no luck.
      if (field == null) {
         throw new Exception("Unsupported Operation.");
      }
			
      // we allow replace by default, since we have more replace columns than not.
      Boolean canReplaceBoolean = field.getBoolean("canReplace");
			
      // canReplace is set, and it's set to false - boohoo bad hacker.

      if (canReplaceBoolean != null && !canReplaceBoolean) {
         throw new Exception("Illegal Operation");
      }

      // set values
      dsRequest.setValues(values);

      // set criteria
      if (requestValues.containsKey("criteria") && requestValues.get("criteria") != null) {
         dsRequest.setCriteria(dsRequest.getValues().get("criteria"));

      }

      return dsRequest.execute();
		
   }
}

ds.xml:

<operationBinding operationType="custom" operationId="replace">
      <serverObject className="package.Replace" methodName="replace"/>
      <customSQL>
         UPDATE MYTABLE 
         SET $defaultValuesClause
         WHERE MYTABLE_ID IN (
            SELECT MYTABLE_ID 
            FROM getValues($advancedCriteria.DATABASE_ID) MYTABLE
            WHERE $defaultWhereClause AND EDITABLE = 1
      )
      </customSQL>
</operationBinding>

JavaScript:

var data = {"values":{},"criteria":grid.getCriteria()};
data.values[field.name] = item.getValue();
ds.performCustomOperation(
   'replace',
   data,
   function() {grid.invalidateCache();}
);

[Isomorphic]

It looks like there's no attempt setOperationType() on the DSRequest.

Maybe that was the problem with creating a new DSRequest() as well?

Note that creating a new DSRequest is really what you should be doing. Changing fundamental things about a dsRequest that has already been partially process is just inviting bugs.

[mandrachek]

I actually did try that - setOperationType("update"), on a new request.

The problem at that point is that the processing skipped out of my <operationBinding type="custom"/>, and jumped to my <operationBinding type="update"/>, rather than calling the <customSQL/> in the current operation.

But I had already removed my
<operationBinding type="update" operationId="replace"><customSQL/>
</operationBinding>

Because you basically said I shouldn't use it.

It would be easy enough to just take my DMI and apply it to the update I already had working, rather than jumping through these kinds of hoops.

[Isomorphic]

You need *two* operationBindings.

The first is operationType=custom and is the target of performCustomOperation and should have the <serverObject>.

The second is operationType=update and should have the SQL Templating settings. It's the target of the dsRequest you create on the server.

[mandrachek]

Yeah. That's what I have now. Basically allows me to remove the allowMultiUpdate from the update operation, and set it programmatically from the DMI. I guess that's the point - make sure it's been checked.

Replace.java:

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import com.isomorphic.datasource.DSField;
import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;

public class Replace {
   
   @SuppressWarnings("rawtypes")
   public DSResponse replace(DSRequest dsRequest, HttpServletRequest servletRequest) throws Exception {
      
      Map requestValues = (Map)dsRequest.getValues();
      
      if (requestValues == null || !requestValues.containsKey("values")) {
         throw new Exception("No values provided.");
      }

      Map values = null;
      if (requestValues.get("values") instanceof Map) {
         values = (Map)requestValues.get("values");
      }
      
      if (values == null) {
         throw new Exception("No values provided.");         
      }
      
      if (values.size() < 1) {
         throw new Exception("No column specified.");
      }
      
      if (values.size() > 1) {
         throw new Exception("Multi-column replace is not permitted.");
      }
      
      // look up the field definition from the datasource.
      String columnName = (String)values.keySet().toArray()[0];
      DSField field = dsRequest.getDataSource().getField(columnName);

      // no field, no luck.
      if (field == null) {
         throw new Exception("Unsupported Operation.");
      }
      
      // we allow replace by default, since we have more replace columns than not.
      Boolean canReplaceBoolean = field.getBoolean("canReplace",true);
      if (!canReplaceBoolean) {
         throw new Exception("Illegal Operation");
      }

      // create a new request
      DSRequest newRequest = new DSRequest();
      
      // set the values
      newRequest.setValues(values);
      
      // set criteria (if applicable)
      if (requestValues.containsKey("criteria") && requestValues.get("criteria") != null) {
         newRequest.setCriteria(dsRequest.getValues().get("criteria"));
      }

      // datasource
      newRequest.setDataSource(dsRequest.getDataSource());
      
      // update!
      newRequest.setOperationType("update");
      
      // operationId.
      newRequest.setOperationId(dsRequest.getOperationId());
      
      // allowMultiUpdate!
      newRequest.setAllowMultiUpdate(true);

      return newRequest.execute();
   
   }
}

.ds.xml:

   <operationBinding operationType="custom" operationId="replace">
      <serverObject className="package.Replace" methodName="replace"/>
   </operationBinding>
   <operationBinding operationType="update" operationId="replace">
      <customSQL/>
   </operationBinding>

[Isomorphic]

Yup, exactly. So just to confirm - everything's working as required now?

[mandrachek]

I wouldn't say *everything* is working as required, but for this particular issue, yeah.