Cascde updates/deletes in sql connectors

[marwan12]

Hi ,

I have used inbuilt ds (serverType="sql") to fetch the data from the table , now while trying to delete any row if there is not foreign key data present for that record , then it gets deleted else throws error
Cannot delete or update a parent row: a foreign key constraint fails

Any idea of how can we implement cascading in ds.xml based on operation type?

[Blama]

See this excerpt for working with com.isomorphic.datasource.DataSource in server code as unrelated example.

    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;
    }

Basically you need to loop though all your DS and see if they link to the DS in question.
If you want to be faster, you could create an extra attribute next to primaryKey and list all the linking tables yourself. Then you don't need the loop, but need to maintain the list.
Of course you'll have to take care of the whole link-chain yourself (parent->child->grandchild).

For whatever way you decide to go - I'd be interested in that code.

Best regards
Blama

[marwan12]

Hi Blama,

But will this initiate cascade delete to all those tables for operation type as remove? if i list all the tables having the foreign key relation with the table defined in ds.xml?

[Blama]

No, you have to program that yourself. That's why I showed working serverside-DataSource code.