Enhancement: Generated includeFrom-JOINs do not use "fetch"-operationBinding criteria

[Blama]

Hi Isomorphic,

please see the two connected DS. The additionally joined DS has a criteria-clause, which works for fetch, but is not used during joins (neither in the ON-clause of the JOIN nor in the final WHERE-clause of the generated SQL-statement). I assume that this is on purpose, but I'm not sure. If it is on purpose, it might be a useful enhancement.

DS that is joined:

<DataSource dbName="Oracle" tableName="V_USER_CREATED_BY" ID="V_USER_CREATED_BY" dataSourceVersion="1" serverType="sql">
	<fields>
		<field primaryKey="true" hidden="true" name="ID" type="sequence"></field>
		<field hidden="true" name="TENANT_ID" type="integer" canEdit="false"></field>
		<field name="CREATED_BY" title="Erstellt von" type="creator"></field>
		<field name="CREATED_AT" title="Erstellt am" type="creatorTimestamp"></field>
		<field name="MODIFIED_BY" title="Geändert von" type="modifier"></field>
		<field name="MODIFIED_AT" title="Geändert am" type="modifierTimestamp"></field>

		<field foreignKey="T_COMPANY.ID" joinType="outer" name="BELONGSTO_COMPANY_ID" title="Firma" type="integer"></field>
		<field name="COMPANY_NAME" includeFrom="T_COMPANY.NAME" canSave="false" hidden="true"></field>
		<field foreignKey="T_DISTRIBUTOR.ID" joinType="outer" name="BELONGSTO_DISTRIBUTOR_ID" title="Distributor" type="integer"></field>
		<field name="DISTRIBUTOR_NAME" includeFrom="T_DISTRIBUTOR.NAME" canSave="false" hidden="true"></field>
	...
	</fields>
	<operationBindings>
		[B]<operationBinding operationType="fetch">
			<criteria fieldName="TENANT_ID" value="$session.authenticatedUserTenantID" />
		</operationBinding>[/B]
	</operationBindings>
</DataSource>

DS with includeFrom:

<DataSource dbName="Oracle" tableName="T_CAMPAIGN" ID="T_CAMPAIGN" dataSourceVersion="1" serverType="sql">
	<fields>
		<field primaryKey="true" hidden="true" name="ID" type="sequence"></field>
		<field hidden="true" name="TENANT_ID" type="integer" canEdit="false"></field>
		[B]<field foreignKey="V_USER_CREATED_BY.ID" name="CREATED_BY" title="Erstellt von" type="creator"></field>[/B]
		<field name="CREATED_AT" title="Erstellt am" type="creatorTimestamp"></field>
[B]		<field name="CREATED_BY_COMPANY_ID" includeFrom="V_USER_CREATED_BY.BELONGSTO_COMPANY_ID" hidden="true"></field>[/B]
		<field name="NAME" title="Name" length="60" type="text" escapeHTML="true" required="true"></field>
		<field name="ENDDATE" title="Enddatum" type="date"></field>
	</fields>
</DataSource>

The generated table-clause looks like this:

FROM T_CAMPAIGN JOIN V_USER_CREATED_BY ON T_CAMPAIGN.CREATED_BY = V_USER_CREATED_BY.ID

I'm looking for something like this:

FROM T_CAMPAIGN JOIN V_USER_CREATED_BY ON T_CAMPAIGN.CREATED_BY = V_USER_CREATED_BY.ID [B]AND V_USER_CREATED_BY.TENANT_ID = xxx[/B]

or

FROM T_CAMPAIGN JOIN V_USER_CREATED_BY ON T_CAMPAIGN.CREATED_BY = V_USER_CREATED_BY.ID
WHERE (..defaultWhereClauses...) [B]AND V_USER_CREATED_BY.TENANT_ID = xxx[/B]

or (in case of joinType="outer")

FROM T_CAMPAIGN JOIN V_USER_CREATED_BY ON T_CAMPAIGN.CREATED_BY = V_USER_CREATED_BY.ID
WHERE (..defaultWhereClauses...) [B]AND (V_USER_CREATED_BY.TENANT_ID = xxx OR V_USER_CREATED_BY.TENANT_ID IS NULL)[/B]

Perhaps the fetch-operation binding is not the place for this clause and a new join-operationBinding would be better suited for something like this.

Do you think this is a good enhancement?
My use-case is that I add a "TENANT_ID = xxx"-clause in my subclassed IDACall to every fetch operation. This works for the base-table, but not the joined tables. In order to optimize my execution plan, I'd like to add it to the join-clause or where-clause as well.

I could solve it by making all my "tenant_id" columns part of the PK, but then I'd have composite PKs, which is not recommended (http://forums.smartclient.com/showpo...89&postcount=7).

Thank you & Best regards,
Blama

[Isomorphic]

Just use includeFrom so that TENANT_ID is also an available field on this other DataSource, and you can apply criteria to the included TENANT_ID field to produce the SQL you want.

[Blama]

Hi Isomorphic,

thank you, that's a brilliant idea!
I'll add includeFroms for all tables I link to with fieldnames like {table_name}_TENANT_ID and will walk though the DS-fields in my IDACall subclass in order to add Criteria for all the fields.

I'll let the forums know about the result.

Best regards,
Blama

[Blama]

In case anyone wants to do similar request modifications, here my overridden handleDSRequest() from my subclassed IDACall:

@Override
public DSResponse handleDSRequest(DSRequest arg0, RPCManager arg1, RequestContext arg2) throws Exception {
	// Add "TENANT_ID = xxx" - Value for add
	if (arg0.getOperationType().equals(DataSource.OP_ADD)) {
		@SuppressWarnings("unchecked")
		Map<String, Object> requestValues = arg0.getValues();
		requestValues.put("TENANT_ID", User.getUserTenantId(arg0.getHttpServletRequest()));
		arg0.setValues(requestValues);
	} else
	// Add "TENANT_ID = x" - Criteria for fetch/update/remove
	if (arg0.getOperationType().equals(DataSource.OP_FETCH) || arg0.getOperationType().equals(DataSource.OP_UPDATE)
			|| arg0.getOperationType().equals(DataSource.OP_REMOVE)) {
		arg0.addToCriteria("TENANT_ID", "equals", User.getUserTenantId(arg0.getHttpServletRequest()));
		// Add additional "xyz_TENANT_ID = x" - Criteria for fetch for
		// "includeFrom"-fields
		if (arg0.getOperationType().equals(DataSource.OP_FETCH)) {
			for (Object fn : arg0.getDataSource().getFieldNames()) {
				String fieldName = fn.toString();
				if (fieldName.endsWith("_TENANT_ID")) {
					if (!isOuterJoined(fieldName, arg0.getDataSource()))
						arg0.addToCriteria(fieldName, "equals", User.getUserTenantId(arg0.getHttpServletRequest()));
					else {
						// xyz_TENANT_ID = x OR xyz_TENANT_ID IS NULL
						AdvancedCriteria xyzTenantID = new AdvancedCriteria(DefaultOperators.Or, new Criterion[] {
								new SimpleCriterion(fieldName, DefaultOperators.Equals, User.getUserTenantId(arg0
										.getHttpServletRequest())), new IsNullCriterion(fieldName) });
						// (old == null) -> new : (old)+(new)
						AdvancedCriteria newAc = (arg0.getAdvancedCriteria() == null) ? xyzTenantID : new AdvancedCriteria(
								DefaultOperators.And, new Criterion[] { arg0.getAdvancedCriteria().asCriterion(),
										xyzTenantID.asCriterion() });
						arg0.setAdvancedCriteria(newAc);
					}
				}
			}
		}
	}
	return super.handleDSRequest(arg0, arg1, arg2);
}

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 JoinType.OUTER.getValue().equals(dsField.getProperty("joinType"));
		}
	return false;
}

Best regards,
Blama

[Blama]

Hello everyone,

if you want to modify all requests like shown and not only clientside requests, look at this thread instead:
http://forums.smartclient.com/showthread.php?t=28646

Best regards,
Blama

[Blama]

Hello everyone,

please note the following thread which might be useful if you hit this page via the search: http://forums.smartclient.com/showth...234#post115234

Best regards,
Blama