Announcement

**Isomorphic** · 7 Feb 2014, 11:10

It could be better reported, but it looks like the issue here is that your update contains a value for the PK field, and should not. The PK should only be present in criteria.

**Blama** · 11 Feb 2014, 13:14

Hi Isomorphic,

it's true, the PK-value is contained in values. But I didn't include it and I can't see how it came in there (if it really shouldn't be there, of which I'm not sure).
Please the this request in BuiltInDS sample (SNAPSHOT_v9.1d_2014-02-10/EVAL Deployment), where the PK for an update of the animals DataSource (set "Allligator mississippiensis" lifespan to 100) is present in both, values and criteria.
Could it be that this is a regression? Because I see the same behaviour for other DS as well.
Or the bug is somewhere else and it is OK that the PK is present in both (values+criteria)?

I see the sequence.CurrVal behaviour in my app (Oracle) for other DataSources as well and I'm pretty sure that this changed in the last weeks, because it destroys cache sync for me, which I definitely would have noticed, if it was that way all the time.

Client DS Request (builtInDS):

Code:

{
    dataSource:"animals", 
    operationType:"update", 
    componentId:"isc_DynamicForm_0", 
    data:{
        picture:"Alligator.jpg", 
        information:"In the 16th century, Spanish explorers in what is now Florida encountered a large formidable animal which they called \"el largo\" meaning \"the lizard\". The name \"el largo\" gradually became pronounced \"alligator\".", 
        commonName:"Alligator (American)", 
        lifeSpan:100, 
        scientificName:"Allligator mississippiensis", 
        diet:"Carnivore", 
        status:"Not Endangered"
    }, 
    textMatchStyle:"exact", 
    callback:{
        target:[DynamicForm ID:isc_DynamicForm_0], 
        methodName:"saveEditorReply"
    }, 
    showPrompt:true, 
    prompt:"Saving form...", 
    oldValues:{
        picture:"Alligator.jpg", 
        information:"In the 16th century, Spanish explorers in what is now Florida encountered a large formidable animal which they called \"el largo\" meaning \"the lizard\". The name \"el largo\" gradually became pronounced \"alligator\".", 
        commonName:"Alligator (American)", 
        lifeSpan:50, 
        scientificName:"Allligator mississippiensis", 
        diet:"Carnivore", 
        status:"Not Endangered"
    }, 
    requestId:"animals$6272", 
    internalClientContext:{
    }, 
    fallbackToEval:false, 
    afterFlowCallback:"isc_DynamicForm_0.$49z(dsRequest, dsResponse, data)", 
    editor:[DynamicForm ID:isc_DynamicForm_0], 
    lastClientEventThreadCode:"MUP4", 
    bypassCache:true
}

Server log with the PK in criteria+values:

Code:

=== 2014-02-11 22:00:40,516 [2-26] INFO  RequestContext - URL: '/builtinds/sc/IDACall', User-Agent: 'Mozilla/5.0 (Windows NT 6.2; WOW64; rv:26.0) Gecko/20100101 Firefox/26.0': Moz (Gecko) with Accept-Encoding header
=== 2014-02-11 22:00:40,517 [2-26] DEBUG IDACall - Header Name:Value pair: Cookie:isc_cState=ready; JSESSIONID=1w98jquhtuiuq17h0qazinqtct; GLog=%7B%0D%20%20%20%20left%3A276%2C%20%0D%20%20%20%20top%3A0%2C%20%0D%20%20%20%20width%3A636%2C%20%0D%20%20%20%20height%3A478%2C%20%0D%20%20%20%20priorityDefaults%3A%7B%0D%20%20%20%20%20%20%20%20Log%3A4%2C%20%0D%20%20%20%20%20%20%20%20testReplay%3A3%0D%20%20%20%20%7D%2C%20%0D%20%20%20%20defaultPriority%3A3%2C%20%0D%20%20%20%20trackRPC%3Atrue%0D%7D
=== 2014-02-11 22:00:40,517 [2-26] DEBUG IDACall - Header Name:Value pair: DNT:1
=== 2014-02-11 22:00:40,517 [2-26] DEBUG IDACall - Header Name:Value pair: Host:127.0.0.1:8888
=== 2014-02-11 22:00:40,517 [2-26] DEBUG IDACall - Header Name:Value pair: Accept:text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
=== 2014-02-11 22:00:40,517 [2-26] DEBUG IDACall - Header Name:Value pair: Content-Length:2566
=== 2014-02-11 22:00:40,517 [2-26] DEBUG IDACall - Header Name:Value pair: Content-Type:application/x-www-form-urlencoded; charset=UTF-8
=== 2014-02-11 22:00:40,517 [2-26] DEBUG IDACall - Header Name:Value pair: Accept-Language:de-de,de;q=0.8,en-us;q=0.5,en;q=0.3
=== 2014-02-11 22:00:40,518 [2-26] DEBUG IDACall - Header Name:Value pair: Pragma:no-cache
=== 2014-02-11 22:00:40,518 [2-26] DEBUG IDACall - Header Name:Value pair: Connection:keep-alive
=== 2014-02-11 22:00:40,518 [2-26] DEBUG IDACall - Header Name:Value pair: Referer:http://127.0.0.1:8888/BuiltInDS.html?gwt.codesvr=127.0.0.1:9997
=== 2014-02-11 22:00:40,518 [2-26] DEBUG IDACall - Header Name:Value pair: User-Agent:Mozilla/5.0 (Windows NT 6.2; WOW64; rv:26.0) Gecko/20100101 Firefox/26.0
=== 2014-02-11 22:00:40,518 [2-26] DEBUG IDACall - Header Name:Value pair: Cache-Control:no-cache
=== 2014-02-11 22:00:40,518 [2-26] DEBUG IDACall - Header Name:Value pair: Accept-Encoding:gzip, deflate
=== 2014-02-11 22:00:40,518 [2-26] DEBUG IDACall - session exists: 1w98jquhtuiuq17h0qazinqtct
=== 2014-02-11 22:00:40,518 [2-26] DEBUG IDACall - remote user: null
=== 2014-02-11 22:00:40,537 [2-26] DEBUG XML - Parsed XML from (in memory stream): 6ms
=== 2014-02-11 22:00:40,542 [2-26] DEBUG RPCManager - Processing 1 requests.
=== 2014-02-11 22:00:40,543 [2-26] DEBUG DSRequest - Caching instance 14 of DS animals from DSRequest.getDataSource()
=== 2014-02-11 22:00:40,544 [2-26] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    [B]criteria:{
        scientificName:"Allligator mississippiensis"[/B]
    },
    [B]values:[/B]{
        picture:"Alligator.jpg",
        information:"In the 16th century, Spanish explorers in what is now Florida encountered a large formidable animal which they called \"el largo\" meaning \"the lizard\". The name \"el largo\" gradually became pronounced \"alligator\".",
        commonName:"Alligator (American)",
        lifeSpan:100,
        [B]scientificName:"Allligator mississippiensis",[/B]
        diet:"Carnivore",
        status:"Not Endangered",
        _selection_3:true
    },
    operationConfig:{
        dataSource:"animals",
        operationType:"update",
        textMatchStyle:"exact"
    },
    componentId:"isc_DynamicForm_0",
    appID:"builtinApplication",
    operation:"animals_update",
    oldValues:{
        picture:"Alligator.jpg",
        information:"In the 16th century, Spanish explorers in what is now Florida encountered a large formidable animal which they called \"el largo\" meaning \"the lizard\". The name \"el largo\" gradually became pronounced \"alligator\".",
        commonName:"Alligator (American)",
        lifeSpan:50,
        scientificName:"Allligator mississippiensis",
        diet:"Carnivore",
        status:"Not Endangered",
        _selection_3:true
    }
}
=== 2014-02-11 22:00:40,544 [2-26] INFO  IDACall - Performing 1 operation(s)
=== 2014-02-11 22:00:40,544 [2-26] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2014-02-11 22:00:40,544 [2-26] DEBUG DeclarativeSecurity - DataSource animals is not in the pre-checked list, processing...
=== 2014-02-11 22:00:40,545 [2-26] DEBUG AppBase - [builtinApplication.animals_update] No userTypes defined, allowing anyone access to all operations for this application
=== 2014-02-11 22:00:40,545 [2-26] DEBUG AppBase - [builtinApplication.animals_update] No public zero-argument method named '_animals_update' found, performing generic datasource operation
=== 2014-02-11 22:00:40,547 [2-26] INFO  SQLDataSource - [builtinApplication.animals_update] Performing update operation with
	criteria: {scientificName:"Allligator mississippiensis"}	values: {picture:"Alligator.jpg",information:"In the 16th century, Spanish explorers in what is now Florida encountered a large formidable animal which they called \"el largo\" meaning \"the lizard\". The name \"el largo\" gradually became pronounced \"alligator\".",commonName:"Alligator (American)",lifeSpan:100,scientificName:"Allligator mississippiensis",diet:"Carnivore",status:"Not Endangered",_selection_3:true}
=== 2014-02-11 22:00:40,548 [2-26] INFO  SQLValuesClause - [builtinApplication.animals_update] Ignored data for non-existent or included columns: [_selection_3]
=== 2014-02-11 22:00:40,551 [2-26] DEBUG PoolableSQLConnectionFactory - [builtinApplication.animals_update] DriverManager fetching connection for HSQLDB via jdbc url jdbc:hsqldb:hsql://localhost/isomorphic
=== 2014-02-11 22:00:40,551 [2-26] DEBUG PoolableSQLConnectionFactory - [builtinApplication.animals_update] Passing JDBC URL only to getConnection
=== 2014-02-11 22:00:40,654 [2-26] DEBUG PoolableSQLConnectionFactory - [builtinApplication.animals_update] makeObject() created an unpooled Connection '186715218'
=== 2014-02-11 22:00:40,654 [2-26] DEBUG SQLConnectionManager - [builtinApplication.animals_update] Borrowed connection '186715218'
=== 2014-02-11 22:00:40,655 [2-26] DEBUG SQLTransaction - [builtinApplication.animals_update] Started new HSQLDB transaction "186715218"
=== 2014-02-11 22:00:40,655 [2-26] DEBUG SQLDriver - [builtinApplication.animals_update] About to execute SQL update in 'HSQLDB' using connection'186715218'
=== 2014-02-11 22:00:40,656 [2-26] INFO  SQLDriver - [builtinApplication.animals_update] Executing SQL update on 'HSQLDB': UPDATE animals SET commonName='Alligator (American)', diet='Carnivore', information='In the 16th century, Spanish explorers in what is now Florida encountered a large formidable animal which they called "el largo" meaning "the lizard". The name "el largo" gradually became pronounced "alligator".', lifeSpan=100, picture='Alligator.jpg', status='Not Endangered' WHERE (LOWER(animals.scientificName)='allligator mississippiensis')
=== 2014-02-11 22:00:40,662 [2-26] DEBUG SQLDataSource - [builtinApplication.animals_update] update operation affected 1 rows
=== 2014-02-11 22:00:40,662 [2-26] INFO  SQLDataSource - [builtinApplication.animals_update] primaryKeys: {scientificName=Allligator mississippiensis}
=== 2014-02-11 22:00:40,662 [2-26] DEBUG DeclarativeSecurity - [builtinApplication.animals_update] Processing security checks for DataSource null, field null
=== 2014-02-11 22:00:40,662 [2-26] DEBUG DeclarativeSecurity - [builtinApplication.animals_update] DataSource animals is not in the pre-checked list, processing...
=== 2014-02-11 22:00:40,663 [2-26] DEBUG AppBase - [builtinApplication.animals_update, builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
=== 2014-02-11 22:00:40,663 [2-26] DEBUG AppBase - [builtinApplication.animals_update, builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
=== 2014-02-11 22:00:40,664 [2-26] INFO  SQLDataSource - [builtinApplication.animals_update, builtinApplication.null] Performing fetch operation with
	criteria: {scientificName:"Allligator mississippiensis"}	values: {scientificName:"Allligator mississippiensis"}
=== 2014-02-11 22:00:40,665 [2-26] INFO  SQLDataSource - [builtinApplication.animals_update, builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2014-02-11 22:00:40,666 [2-26] INFO  SQLDataSource - [builtinApplication.animals_update, builtinApplication.null] 14: Executing SQL query on 'HSQLDB': SELECT animals.commonName, animals.diet, animals.information, animals.lifeSpan, animals.picture, animals.scientificName, animals.status FROM animals WHERE (LOWER(animals.scientificName)='allligator mississippiensis')
=== 2014-02-11 22:00:40,666 [2-26] DEBUG SQLDriver - [builtinApplication.animals_update, builtinApplication.null] About to execute SQL query in 'HSQLDB' using connection '186715218'
=== 2014-02-11 22:00:40,666 [2-26] INFO  SQLDriver - [builtinApplication.animals_update, builtinApplication.null] Executing SQL query on 'HSQLDB': SELECT animals.commonName, animals.diet, animals.information, animals.lifeSpan, animals.picture, animals.scientificName, animals.status FROM animals WHERE (LOWER(animals.scientificName)='allligator mississippiensis')
=== 2014-02-11 22:00:40,671 [2-26] INFO  DSResponse - [builtinApplication.animals_update, builtinApplication.null] DSResponse: List with 1 items
=== 2014-02-11 22:00:40,671 [2-26] INFO  DSResponse - [builtinApplication.animals_update] DSResponse: List with 1 items
=== 2014-02-11 22:00:40,672 [2-26] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2014-02-11 22:00:40,672 [2-26] DEBUG SQLTransaction - Committing HSQLDB transaction "186715218"
=== 2014-02-11 22:00:40,672 [2-26] DEBUG RPCManager - non-DMI response, dropExtraFields: false
=== 2014-02-11 22:00:40,674 [2-26] DEBUG SQLTransaction - getConnection() found transactional connection for HSQLDB with hashcode "186715218"
=== 2014-02-11 22:00:40,674 [2-26] DEBUG SQLTransaction - Ending HSQLDB transaction "186715218"
=== 2014-02-11 22:00:40,675 [2-26] DEBUG SQLConnectionManager - About to close JDBCConnection with hashcode "186715218"
=== 2014-02-11 22:00:40,676 [2-26] INFO  Compression - /builtinds/sc/IDACall: 549 -> 375 bytes
=== 2014-02-11 22:00:45,475 [2-37] INFO  RequestContext - URL: '/builtinds/sc/system/reference/skin/images/server_client_exchange.png', User-Agent: 'Mozilla/5.0 (Windows NT 6.2; WOW64; rv:26.0) Gecko/20100101 Firefox/26.0': Moz (Gecko) with Accept-Encoding header
=== 2014-02-11 22:00:45,476 [2-49] INFO  RequestContext - URL: '/builtinds/sc/skins/Enterprise/images/DynamicForm/checkbox_sprite.png', User-Agent: 'Mozilla/5.0 (Windows NT 6.2; WOW64; rv:26.0) Gecko/20100101 Firefox/26.0': Moz (Gecko) with Accept-Encoding header
=== 2014-02-11 22:00:45,481 [2-49] INFO  Download - done streaming: __USE_CONTAINER__/builtinds/sc/skins/Enterprise/images/DynamicForm/checkbox_sprite.png
=== 2014-02-11 22:00:45,482 [2-37] INFO  Download - done streaming: __USE_CONTAINER__/builtinds/sc/system/reference/skin/images/server_client_exchange.png

Best regards,
Blama

**Isomorphic** · 11 Feb 2014, 13:49

In this the unmodified builtinDS sample?

If so, what specific steps do you take in the UI to cause this request?

**Blama** · 11 Feb 2014, 14:11

Hi Isomorphic,

yes, it's the unmodified example.
I'm at home now, but here the steps from memory:

Start Sample in DevMode
Open in FF, click Animals
Select Alligator from list
Change lifespan in DynamicForm to 100
Click "Safe"/"Apply"/"OK"

Best regards,
Blama

**Isomorphic** · 13 Feb 2014, 04:39

This is a bit mysterious - we don't easily see how it could come about that an UPDATE query would cause an attempt to obtain a sequence's CurrVal, yet from your log that is clearly happening.

There is also something going on here that you haven't shared. Your log shows the generated whereClause as:

Code:

WHERE ((T_LEAD_ONLYRESELLER.ID = 2 AND T_LEAD_ONLYRESELLER.ID IS NOT NULL) AND (T_LEAD_ONLYRESELLER.TENANT_ID = 0 AND T_LEAD_ONLYRESELLER.TENANT_ID IS NOT NULL))

But this is not the clause that would be generated if the DataSOurce were as straightforward as the .ds.xml file suggests. So we presume you must be doing some manipulation of the request in your LMSSQLDataSource class - if we knew what that manipulation was, it might suggest a way of reproducing this problem.

Also, you say you have seen this problem with other DataSources. Is this common or rare? Reproducible or intermittent? And have you been able to discern any pattern to incidences of the problem?

**Blama** · 13 Feb 2014, 05:18

Hi Isomorphic,

here my LMSSQLDataSource. It has the purpose to add TENANT_ID-Criteria to all requests.

Code:

package com.lmscompany.lms.server;

import java.util.Map;

import com.isomorphic.criteria.AdvancedCriteria;
import com.isomorphic.criteria.Criterion;
import com.isomorphic.criteria.DefaultOperators;
import com.isomorphic.criteria.criterion.IsNullCriterion;
import com.isomorphic.criteria.criterion.SimpleCriterion;
import com.isomorphic.datasource.DSField;
import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;
import com.isomorphic.datasource.DataSource;
import com.isomorphic.sql.SQLDataSource;
import com.lmscompany.lms.server.util.User;

public class LMSSQLDataSource extends SQLDataSource {
	private static final long serialVersionUID = 3994104605640773331L;

	/*
	 * See:
	 * http://forums.smartclient.com/showthread.php?t=28646
	 * http://www.smartclient.com/smartgwtee/javadoc/com/smartgwt/client/docs/WriteCustomDataSource.html
	 * http://www.smartclient.com/smartgwtee/server/javadoc/com/isomorphic/sql/SQLDataSource.html
	 */

	@Override
	public DSResponse executeAdd(DSRequest req) throws Exception {
		@SuppressWarnings("unchecked")
		Map<String, Object> requestValues = req.getValues();
		requestValues.put("TENANT_ID", User.getUserTenantId(req.getHttpServletRequest()));
		req.setValues(requestValues);
		return super.executeAdd(req);
	};

	@Override
	public DSResponse executeFetch(DSRequest dsRequest) throws Exception {
		// T_USER.fetchUserIdForLoginname occurs before session parameter are set,
		// so do not try to modify.
		if (!dsRequest.getDataSourceName().equals("T_USER") || !dsRequest.getOperationId().equals("fetchUserIdForLoginname"))
			modifyCriteria(dsRequest);
		return super.executeFetch(dsRequest);
	}

	@Override
	public DSResponse executeUpdate(DSRequest req) throws Exception {
		modifyCriteria(req);
		return super.executeUpdate(req);
	}

	@Override
	public DSResponse executeRemove(DSRequest req) throws Exception {
		modifyCriteria(req);
		return super.executeRemove(req);
	}

	private static DSRequest modifyCriteria(DSRequest dsRequest) throws Exception {
		// Add "TENANT_ID = x" - Criteria for fetch/update/remove
		if (dsRequest.getOperationType().equals(DataSource.OP_FETCH) || dsRequest.getOperationType().equals(DataSource.OP_UPDATE)
				|| dsRequest.getOperationType().equals(DataSource.OP_REMOVE)) {
			dsRequest.addToCriteria("TENANT_ID", DefaultOperators.Equals, User.getUserTenantId(dsRequest.getHttpServletRequest()));
			// Add additional "xyz_TENANT_ID = x" - Criteria for fetch for
			// "includeFrom"-fields
			if (dsRequest.getOperationType().equals(DataSource.OP_FETCH)) {
				for (Object fn : dsRequest.getDataSource().getFieldNames()) {
					String fieldName = fn.toString();
					if ((dsRequest.getConsolidatedOutputs() == null || dsRequest.getConsolidatedOutputs().contains(fn))
							&& fieldName.endsWith("_TENANT_ID")) {
						if (!isOuterJoined(fieldName, dsRequest.getDataSource())) {
							dsRequest.addToCriteria(fieldName, DefaultOperators.Equals, User.getUserTenantId(dsRequest.getHttpServletRequest()));
						} else {
							AdvancedCriteria ac = new AdvancedCriteria(DefaultOperators.Or, new Criterion[] {
									new SimpleCriterion(fieldName, DefaultOperators.Equals, User.getUserTenantId(dsRequest.getHttpServletRequest())),
									new IsNullCriterion(fieldName) });
							dsRequest.addToCriteria(ac);
						}
					}
				}
			}
		}
		return dsRequest;
	}

	private static boolean isOuterJoined(String includingField, DataSource ds) {
		DSField includingFieldDSField = ds.getField(includingField);
		String includeFromString = includingFieldDSField.getProperty("includeFrom");

		String includeFromTable = (includeFromString == null || includeFromString.indexOf(".") == -1) ? null : includeFromString.substring(0,
				includeFromString.indexOf("."));

		if (includeFromTable != null)
			for (Object fn : ds.getFieldNames()) {
				String fieldName = fn.toString();
				DSField dsField = ds.getField(fieldName);
				if (dsField.getProperty("foreignKey") != null && dsField.getProperty("foreignKey").startsWith(includeFromTable + "."))
					return "outer".equals(dsField.getProperty("joinType"));
			}
		return false;
	}
}

I see the sequence.CurrVal behaviour in my app (Oracle) for many DataSources and I'm pretty sure that this changed in the last weeks, because it destroys cache sync for me, which I definitely would have noticed, if it was that way all the time.

Right now it's reproducible, but I can't see a pattern. But as I wrote above I think that it was not that way all the time.

Best regards,
Blama

**Isomorphic** · 13 Feb 2014, 07:28

Is it reproducible in the sense that an update through this DataSource always gives this problem? Your initial report seemed to suggest otherwise

**Isomorphic** · 13 Feb 2014, 07:54

Oh, actually I have a hunch. Please try changing your addToCriteria("TENANT_ID" ... ) call to use the two-argument signature of that method - in other words, drop the operator and just pass the fieldname and value. If that makes the problem go away then this is a manifestation of a seemingly unrelated breakage in one of our automated tests that we fixed earlier today. The easiest fix for you will be to use that signature throughout your code when you have these kinds of circumstances where you want to add an additional clause to some update criteria - the three-argument signature now causes AdvancedCriteria to be generated, which is confusing the update subsystem

**Blama** · 13 Feb 2014, 08:54

Originally posted by Isomorphic View Post

Is it reproducible in the sense that an update through this DataSource always gives this problem? Your initial report seemed to suggest otherwise

It indeed was. But it is reproducible. I don't know why I wrote otherwise first.

Originally posted by Isomorphic View Post

try changing your addToCriteria("TENANT_ID" ... ) call to use the two-argument signature of that method...

I did so and it solves the problem for me! I attached you an update log (other DS, but it appeared everywhere) as two text files (one before and one after the change). In the one before the change I used the sequence before in order not to get an Oracle error. There you can see nicely how it breaks cache-sync.

I removed the timestamps from the log so that you can put them in WinMerge and see how the change fixes the problem.

I'll keep the code like it is now and retest the old version with a newer build and report back.

Thank you very much for your help,
Blama

Attached Files

**Blama** · 4 Sep 2014, 01:44

Hi Isomorphic,

while looking at the problem in this thread, I found this report of mine.

Is it now safe to use any way (two/three argument method signature) of adding additional criteria in a SQLDataSource subclass?

FYI: I'm not having a problem here right now.

Best regards,
Blama

**Blama** · 18 Aug 2015, 05:06

Hi Isomorphic,

I'm having the same effect again, this time for a DELETE.

See this server-side request:

Code:

Map<String, Object> valueMap = new HashMap<String, Object>();
valueMap.put("TYPE", "DELETE");
DSRequest removeFromDeleteAllowedTenantRequest = new DSRequest(ServerDatasourceEnum.T_SYS_DELETEALLOWEDTENANT.getValue(),
DataSource.OP_REMOVE, dsRequest.getRPCManager());
addToDeleteAllowedTenantRequest.setValues(valueMap); //Bit useless, not criteria, but the way my code is by chance.
DSResponse removeFromDeleteAllowedTenantResponse = removeFromDeleteAllowedTenantRequest.execute();

.ds.xml:

Code:

<DataSource dbName="Oracle" tableName="T_SYS_DELETEALLOWEDTENANT" ID="T_SYS_DELETEALLOWEDTENANT"
	serverType="sql" serverConstructor="com.lmscompany.lms.server.LMSSQLDataSource" allowedClientOperationTypes="">
	<fields>
		<field primaryKey="true" hidden="true" name="ID" type="sequence" />
		<field hidden="true" name="TENANT_ID" type="integer" canEdit="false" />
		<field name="TYPE" length="10" type="text" />
	</fields>
</DataSource>

TENANT_ID injected by serverConstructor. Relevant methods:

Code:

	@Override
	public DSResponse executeRemove(DSRequest request) throws Exception {
		modifyCriteria(request);
		return super.executeRemove(request);
	}

	private static DSRequest modifyCriteria(DSRequest request) throws Exception {
		// Add "TENANT_ID = x" - Criteria for fetch/update/remove
		if (request.getOperationType().equals(DataSource.OP_FETCH) || request.getOperationType().equals(DataSource.OP_UPDATE)
				|| request.getOperationType().equals(DataSource.OP_REMOVE)) {

			// TODO: See http://forums.smartclient.com/showthread.php?t=29408 for why the two-param version is used here.
			// dsRequest.addToCriteria("TENANT_ID", DefaultOperators.Equals,
			// User.getUserTenantId(dsRequest.getHttpServletRequest()));
			request.addToCriteria("TENANT_ID", getTenantId(request));
			// Add additional "xyz_TENANT_ID = x" - Criteria for fetch for
			// "includeFrom"-fields
			if (request.getOperationType().equals(DataSource.OP_FETCH)) {
				for (Object fn : request.getDataSource().getFieldNames()) {
					String fieldName = fn.toString();
					if ((request.getConsolidatedOutputs() == null || request.getConsolidatedOutputs().contains(fn))
							&& fieldName.endsWith("_TENANT_ID")) {
						if (!isOuterJoined(fieldName, request.getDataSource())) {
							request.addToCriteria(fieldName, DefaultOperators.Equals, getTenantId(request));
						} else {
							AdvancedCriteria ac = new AdvancedCriteria(DefaultOperators.Or, new Criterion[] {
									new SimpleCriterion(fieldName, DefaultOperators.Equals, getTenantId(request)), new IsNullCriterion(fieldName) });
							request.addToCriteria(ac);
						}
					}
				}
			}
		}
		return request;
	}

	private static boolean isOuterJoined(String includingField, DataSource ds) {
		DSField includingFieldDSField = ds.getField(includingField);
		String includeFromString = includingFieldDSField.getProperty("includeFrom");

		String includeFromTable = (includeFromString == null || includeFromString.indexOf(".") == -1) ? null : includeFromString.substring(0,
				includeFromString.indexOf("."));

		if (includeFromTable != null)
			for (Object fn : ds.getFieldNames()) {
				String fieldName = fn.toString();
				DSField dsField = ds.getField(fieldName);
				if (dsField.getProperty("foreignKey") != null && dsField.getProperty("foreignKey").startsWith(includeFromTable + "."))
					return "outer".equals(dsField.getProperty("joinType"));
			}
		return false;
	}

	private static long getTenantId(DSRequest request) {
		long retVal = User.getUserTenantId(request.getHttpServletRequest());
		if (retVal == -1) {
			Config.getGlobal();
			String tenantId = (String) Config.getProperty("tenantId");
			retVal = new Long(tenantId);
		}
		return retVal;
	}

Server log:

Code:

=== 2015-08-18 14:47:09,162 [c-35] DEBUG AppBase - [builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
=== 2015-08-18 14:47:09,163 [c-35] DEBUG AppBase - [builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
=== 2015-08-18 14:47:09,163 [c-35] INFO  SQLDataSource - [builtinApplication.null] Performing remove operation with
	criteria: {TENANT_ID:12}	values: {TENANT_ID:12}
=== 2015-08-18 14:47:09,165 [c-35] DEBUG SQLDriver - [builtinApplication.null] About to execute SQL update in 'Oracle' using connection'1715189021'
=== 2015-08-18 14:47:09,165 [c-35] INFO  SQLDriver - [builtinApplication.null] Executing SQL update on 'Oracle': DELETE FROM T_SYS_DELETEALLOWEDTENANT WHERE (T_SYS_DELETEALLOWEDTENANT.TENANT_ID=12)
=== 2015-08-18 14:47:09,167 [c-35] DEBUG SQLDataSource - [builtinApplication.null] remove operation affected 1 rows
=== 2015-08-18 14:47:09,167 [c-35] DEBUG SQLDriver - [builtinApplication.null] About to execute SQL query in 'Oracle' using connection '1715189021'
=== 2015-08-18 14:47:09,167 [c-35] INFO  SQLDriver - [builtinApplication.null] Executing SQL query on 'Oracle': SELECT T_SYS_DELETEALLOWEDTENANT_ID.CurrVal FROM DUAL
=== 2015-08-18 14:47:09,169 [c-35] INFO  SQLDataSource - [builtinApplication.null] primaryKeys: {ID=11}
=== 2015-08-18 14:47:09,169 [c-35] INFO  DSResponse - [builtinApplication.null] DSResponse: List with 1 items

As you can see I'm not setting setAllowMultiUpdate(true), but nevertheless a non-PK-bounded DELETE is executed. This is perhaps even more of a bug than the CurrVal-SELECT. I'm using v10.0p_2015-08-18/PowerEdition Deployment.

Best regards
Blama

**Blama** · 18 Aug 2015, 05:39

UPDATE:

Hi Isomorphic,

it seems that even setting the criteria correctly with

Code:

removeFromDeleteAllowedTenantRequest.addToCriteria(new SimpleCriterion("TYPE", DefaultOperators.Equals, "DELETE"));

does not create the needed WHERE clause. It is only generated when setting

Code:

removeFromDeleteAllowedTenantRequest.setAllowMultiUpdate(true);

as well.

I think this leads to a very dangerous situation where unbounded DELETEs are generated even if the developer gave criteria - only when setting setAllowMultiUpdate(true), these criteria are used as well.

These are the logs for

Code:

DSRequest removeFromDeleteAllowedTenantRequest = new DSRequest(ServerDatasourceEnum.T_SYS_DELETEALLOWEDTENANT.getValue(),
		DataSource.OP_REMOVE, dsRequest.getRPCManager());
removeFromDeleteAllowedTenantRequest.addToCriteria(new SimpleCriterion("TYPE", DefaultOperators.Equals, "DELETE"));
[B]//[/B] removeFromDeleteAllowedTenantRequest.setAllowMultiUpdate(true);
DSResponse removeFromDeleteAllowedTenantResponse = removeFromDeleteAllowedTenantRequest.execute();

Server log:

Code:

=== 2015-08-18 15:34:01,550 [c-35] DEBUG AppBase - [builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
=== 2015-08-18 15:34:01,550 [c-35] DEBUG AppBase - [builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
=== 2015-08-18 15:34:01,551 [c-35] INFO  SQLDataSource - [builtinApplication.null] Performing remove operation with
	criteria: {criteria:[{value:12,fieldName:"TENANT_ID",operator:"equals"}],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[{value:12,fieldName:"TENANT_ID",operator:"equals"}],operator:"and",_constructor:"AdvancedCriteria"}
=== 2015-08-18 15:34:01,552 [c-35] DEBUG SQLDriver - [builtinApplication.null] About to execute SQL update in 'Oracle' using connection'1037209377'
=== 2015-08-18 15:34:01,553 [c-35] INFO  SQLDriver - [builtinApplication.null] Executing SQL update on 'Oracle': DELETE FROM T_SYS_DELETEALLOWEDTENANT WHERE ((T_SYS_DELETEALLOWEDTENANT.TENANT_ID = 12 AND T_SYS_DELETEALLOWEDTENANT.TENANT_ID IS NOT NULL))
=== 2015-08-18 15:34:01,554 [c-35] DEBUG SQLDataSource - [builtinApplication.null] remove operation affected 1 rows
=== 2015-08-18 15:34:01,554 [c-35] DEBUG SQLDriver - [builtinApplication.null] About to execute SQL query in 'Oracle' using connection '1037209377'
=== 2015-08-18 15:34:01,555 [c-35] INFO  SQLDriver - [builtinApplication.null] Executing SQL query on 'Oracle': SELECT T_SYS_DELETEALLOWEDTENANT_ID.CurrVal FROM DUAL
=== 2015-08-18 15:34:01,556 [c-35] INFO  SQLDataSource - [builtinApplication.null] primaryKeys: {ID=17}
=== 2015-08-18 15:34:01,556 [c-35] INFO  DSResponse - [builtinApplication.null] DSResponse: List with 1 items

With setAllowMultiUpdate(true) it is:
Server log:

Code:

=== 2015-08-18 15:36:55,807 [c-37] DEBUG AppBase - [builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
=== 2015-08-18 15:36:55,808 [c-37] DEBUG AppBase - [builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
=== 2015-08-18 15:36:55,808 [c-37] INFO  SQLDataSource - [builtinApplication.null] Performing remove operation with
	criteria: {criteria:[{value:"DELETE",fieldName:"TYPE",operator:"equals"},{value:12,fieldName:"TENANT_ID",operator:"equals"}],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[{value:"DELETE",fieldName:"TYPE",operator:"equals"},{value:12,fieldName:"TENANT_ID",operator:"equals"}],operator:"and",_constructor:"AdvancedCriteria"}
=== 2015-08-18 15:36:55,810 [c-37] DEBUG SQLDriver - [builtinApplication.null] About to execute SQL update in 'Oracle' using connection'1037209377'
=== 2015-08-18 15:36:55,810 [c-37] INFO  SQLDriver - [builtinApplication.null] Executing SQL update on 'Oracle': DELETE FROM T_SYS_DELETEALLOWEDTENANT WHERE ((T_SYS_DELETEALLOWEDTENANT.TYPE = 'DELETE' AND T_SYS_DELETEALLOWEDTENANT.TYPE IS NOT NULL) AND (T_SYS_DELETEALLOWEDTENANT.TENANT_ID = 12 AND T_SYS_DELETEALLOWEDTENANT.TENANT_ID IS NOT NULL))
=== 2015-08-18 15:36:55,812 [c-37] DEBUG SQLDataSource - [builtinApplication.null] remove operation affected 1 rows

As you can see, in the last logs, the CurrVal-SELECT is gone as well.

Best regards
Blama

**Isomorphic** · 18 Aug 2015, 13:48

Since you've already provided a primary key, your additional criteria cannot have any effect.

Ignoring something that has no effect does not seem "dangerous".. ?

**Blama** · 18 Aug 2015, 14:22

Hi Isomorphic,

I did not provide a primaryKey-value. PK is ID, I provided TENANT_ID and (ignored) TYPE.

Additionally there is the CurrVal-SELECT problem.

Best regards
Blama

Announcement

Bug in SQL Generation for UPDATE on a Oracle table with sequence-PK

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment