How to implement referential integrity checks

[jay.l.fisher]

I'm using SmartGWT with a legacy database that does not have referential integrity built in to the DB tables and I can't change that. The standard SQL data source processing in SmartGWT works very well for my purposes, but I need to implement validations to prevent users from deleting domain table records or changing primary keys when that would break referential integrity (yes, the PKs are actually user specified values). All of the data source validations seem to be simple checks on the contents of a field (length, precision, regex, etc.). What is the easiest way to implement the kind of validations I need?

[Isomorphic]

SmartClient has some built-in RI validations that you can enable by setting "foreignKey" and "validateRelatedRecords" on your DataSources. Correctly set up, this will prevent your users from adding, eg, an order for a non-existent customer, or an order line for a non-existent order header or product.

Also, SmartClient has a couple of Validators that will help in this circumstance - there are no SmartGWT wrappers at the moment (and hence no SGWT docs), but that doesn't matter because you can specify these directly in you .ds.xml files. Here is the documentation, copied from the SmartClient docs (including the docs for DataSource.validateRelatedRecords):

validateRelatedRecords [IR] type:Boolean, defaultValue: null

If true, indicates that the SmartClient Server should automatically apply a hasRelatedRecord validator to every field on this dataSource that has a foreignKey defined.

See Also:
ValidatorTypes.hasRelatedRecord


hasRelatedRecord [IR] type:validatorDefinition, defaultValue: object

Returns true if the record implied by a relation exists. The relation can be derived automatically from the DataSourceField.foreignKey attribute of the field being validated, or you can specify it manually via validator.relatedDataSource and validator.relatedField.
You can specify at DataSource level that this validator should be automatically applied to all fields that specify a foreignKey - see DataSource.validateRelatedRecords.

By default, validators of this type have serverOnly set to true and do not run on the client.

Note that this validation is generally unnecessary for data coming from a UI. The typical UI uses a SelectItem or ComboBoxItem with an optionDataSource for user entry, such that the user can't accidentally enter a related record if that doesn't exist, and a typical SQL schema will include constraints that prevent a bad insert if the user attempts to circumvent the UI. The primary purpose of declaring this validation explicitly is to provide clear, friendly error messages for use cases such as BatchUploader, where values aren't individually chosen by the user.

See Also:
DataSource.validateRelatedRecords


isUnique [IR] type:validatorDefinition, defaultValue: object

Returns true if the value for this field is unique across the whole DataSource. By default, validators of this type have serverOnly set to true and do not run on the client.

[jay.l.fisher]

Thanks. I'll check it out. I'm not so concerned about validation on adding a record with a foreign key since, as the doc points out, the UI will usually take care of this by only allowing defined choices via a dropdown.

My real concern is deleting a record when there are references to it? Or changing the primary key when there are references to it? How would I accomplish that sort of validation?

[jay.l.fisher]

Any tips on how best to do this? I'd like the validation to occur server side. So, for example, the client tries to delete a product but the server responds with an error if there are order lines referencing the product. In this case the product would not have any foreignKey field to put the validator on. How can I implement this sort of server-side check while retaining all of the other built-in SmartGWT SQL functionality.

[Isomorphic]

Hi Jay,

We'd recommend adding logic to automatically query related tables when "remove" operations are performed. In outline:

1. create a custom DataSource subclassing the built-in SQLDataSource (using the serverConstructor property - see custom DataSource property)

2. come up with a property name or set of properties that you can declare in your .ds.xml file to say that this DataSource needs to check related tables on remove, for example, checkRelatedDS="orderLine"

3. in your custom class, override execute() and for "remove" operations, check for the special properties you made up via DataSource.getProperty(). Then query related records by just doing other DataSource operations. It's going to look something like this (this is pseudocode):

DSRequest dsRequest = new DSRequest("orderLine", "fetch");
dsRequest.setCriteria(new Map(){{
    put("[i]orderLineFKField[/i]", [i]currentRecordPKValue[/i]
}})
DSResponse dsResponse = dsRequest.execute();
if (dsResponse.getTotalRows() > 0) {
    // found a record that references the record the user tried to delete
    DSResponse errorResponse = new DSResponse();
    errorResponse.setFailure();
    return errorResponse;
}

You can then use your custom DataSource class for all your DataSources and you will be able to just declared the related entities to check via your .ds.xml file.

You're doing something pretty generic so it would be great if you posted your final code for others to use.

[jay.l.fisher]

I've basically got it working and will post it when I'm done, but I'm struggling with a silly detail and want to do it the best way. I want the properties in the datasource to support more than one related DS check so I've got the following construct.

<checkRelatedDS>
	<relatedDS name="IPPOHDR" keyField="HVEN" matchField="VVEN"/>
	<relatedDS name="SecondDS" keyField="KeyField" matchField="MatchField"/>
</checkRelatedDS>

In my server side code I can get the property with DataSource.getProperty("checkRelatedDS"), but that returns a JSON formatted string like this:

{relatedDS=[{keyField=HVEN, matchField=VVEN, name=IPPOHDR}, {keyField=KeyField, matchField=MatchField, name=SecondDS}]}

How do I convert that into an object array that I can iterate over and pull out the attributes? I can use a 3rd part lib like JSON.org to do the parsing but I'm sure you've got similar tools that I could use in JSOHelper. I just can't seem to figure out how to use them and I also don't seem to have the javadocs for JSOHelper.

[jay.l.fisher]

I resorted to using the JSON.org java source code for help with parsing the data source properties. If there is a better way please let me know, but this is working for me. Here's the code.

package com.islandpacific.gui.server.customDataSource;

import java.util.HashMap;

import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;
import com.isomorphic.sql.SQLDataSource;
import org.json.*;

public class IpDataSource extends SQLDataSource {
	@Override
	// Add custom validation logic for selected "remove" operations
	public DSResponse execute(DSRequest req) throws Exception {
		if (req.getOperationType().equals("remove")) {
			// See if there are any checkRelatedDS validations
			JSONObject checkRelatedDS = new JSONObject(this.getProperty("checkRelatedDS"));
			
			// If there are, put them into an array (may be one or more)
			if (checkRelatedDS!=null) {
	 			JSONArray relations = new JSONArray();
				if (checkRelatedDS.optJSONArray("relatedDS") != null) 
					relations = new JSONArray(checkRelatedDS.get("relatedDS").toString());
				else relations.put(checkRelatedDS.get("relatedDS"));
				
				// Check for related data in each related DS
				for (int i=0; i<relations.length(); i++) {
					JSONObject relation = relations.getJSONObject(i);
					String name = relation.getString("name");
					String keyField = relation.getString("keyField");
					String matchField = relation.getString("matchField");
					DSRequest dsRequest = new DSRequest(name, "fetch");
					dsRequest.setCriteria(new HashMap<String, String>().put(keyField, req
							.getOldValues().get(matchField).toString()));
					dsRequest.setStartRow(1); // We only need to see if there is one
					dsRequest.setEndRow(1);
					DSResponse dsResponse = dsRequest.execute();
					if (dsResponse.getTotalRows()>0) {
					DSResponse resp = new DSResponse();
						resp.setFailure();
						resp.setData("Can't delete when references exist. (" + name + ")");
						return resp;
					}
				}
			}
		}
		return super.execute(req);
	}
}

[Isomorphic]

That works - note we'll add an accessor that makes it easier to get to properties that are more than just a single String.