Enhancement: foreignKey lookup operationID (optionOperationId in ds.xml)

[Blama]

Hi Isomorphic,

I have a use case where the user can configure a mapping from external status values to internal status values. The mapping table looks like this:

• userId: integer
• externalStatusValue: text(20)
• internalStatusValue: text(10)

I have an unique constraint on (userId, externalStatusValue). Now in my batchUpload DS I link UploadDS.externalStatusValue with Lookup.internalStatusValue via the existing uploadFieldName property.

As you can see this can return many rows (and not just one as required) if I do not also include a condition on userId = currentUserId.
Doing so is easy using an operationBinding and a criteria-tag with a velocity expression for the current user.

Only thing missing is that currently I can not specify this operationBinding via it's operationID in the batchUpload DS-foreignKey definition, as the normal fetch is used here.

I think this is not only limited to batchUpload but could be useful in other settings (e.g. the already happening automatic editor resolval of displayField/valueField for possible values in SelectItems where the DataSourceField has a foreignKey attribute).

What do you think about this? Roughly speaking it is a "optionOperationId in ds.xml (and Java DataSourceField)".

Thank you & Best regards
Blama

[Blama]

Hi Isomorphic,

thinking about the 2nd usecase which seems very common to me it might be possible that with some luck it is already there but just not doc'd yet?

Best regards
Blama

[Isomorphic]

Sorry, we don't see this as an interesting, general-purpose feature at this time. Allowing something like optionOperationId creates the possibility of a pseudo-FK relation, which only works through a particular fetch operation. This introduces a lot of potentially complexity in how automatic joins work, as they potentially have to incorporate an arbitrary subquery, and in fact arbitrary server-side Java behavior (since an operationId could imply DMI logic), in order to perform the join.

The existing optionOperationId used by FormItem.fetchMissingValues introduces no similar complex problem.

As far as adding something specific to the BatchUploader, we suggest you just solve this in application code.

[Blama]

Hi Isomorphic,

I was thinking about something way more simple. I definitely did not mean to change the way joins are created.
For the easier discussing let's leave out the "optionOperationId in .ds.xml for SelectItems" and lets only talk about a new attribute uploadOperationId (like uploadFieldName).
It would only be used for the Text-to-ID lookup query in BatchUploader with importStrategy="display".

See this testcase (v10.1p_2016-11-03 based):

BuiltInDS.java (+BuiltInDS.html change: DataSourceLoader?dataSource=employees,employeesUpload,batchUpload):

package com.smartgwt.sample.client;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.Version;
import com.smartgwt.client.core.KeyIdentifier;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.util.PageKeyHandler;
import com.smartgwt.client.util.Page;
import com.smartgwt.client.util.SC;
import com.smartgwt.client.widgets.BatchUploader;
import com.smartgwt.client.widgets.IButton;
import com.smartgwt.client.widgets.Window;
import com.smartgwt.client.widgets.events.ClickEvent;
import com.smartgwt.client.widgets.events.ClickHandler;
import com.smartgwt.client.widgets.grid.ListGridField;
import com.smartgwt.client.widgets.layout.VLayout;

public class BuiltInDS implements EntryPoint {
    private VLayout mainLayout;
    private IButton recreateBtn;
    private DataSource uploadDS = DataSource.get("employeesUpload");
    private DataSource optionDS = DataSource.get("employees");

    public void onModuleLoad() {
        KeyIdentifier debugKey = new KeyIdentifier();
        debugKey.setCtrlKey(true);
        debugKey.setKeyName("D");

        Page.registerKey(debugKey, new PageKeyHandler() {
            public void execute(String keyName) {
                SC.showConsole();
            }
        });

        mainLayout = new VLayout(20);
        mainLayout.setWidth100();
        mainLayout.setHeight100();

        recreateBtn = new IButton("Recreate");
        recreateBtn.addClickHandler(new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                recreate();
            }
        });
        mainLayout.addMember(recreateBtn);
        recreate();
        mainLayout.draw();
    }

    private void recreate() {
        Window w = new Window();
        w.setWidth("95%");
        w.setHeight("95%");
        w.setMembersMargin(0);
        w.setModalMaskOpacity(70);
    w.setTitle(" (" + Version.getVersion() + "/" + Version.getSCVersionNumber() + ")");
    w.setTitle("BatchUploader server foreignKey resolution" + w.getTitle());
        w.setShowMinimizeButton(false);
        w.setIsModal(true);
        w.setShowModalMask(true);
        w.centerInPage();

        BatchUploader batchUploader = new BatchUploader();
        batchUploader.setWidth100();
        batchUploader.setUploadDataSource(uploadDS);

        ListGridField employeeIdLGF = new ListGridField("EmployeeId");
        
        ListGridField nameLGF = new ListGridField("Name");

        ListGridField managerLGF = new ListGridField("ReportsTo");
        managerLGF.setOptionDataSource(optionDS);
        managerLGF.setValueField("EmployeeId");
        managerLGF.setDisplayField("Name");

        ListGridField jobLGF = new ListGridField("Job");

        ListGridField genderLGF = new ListGridField("Gender");

        batchUploader.setGridFields(employeeIdLGF, genderLGF, nameLGF, managerLGF, jobLGF);

        w.addItem(batchUploader);
        w.show();
    }
}

employees.ds.xml:

<DataSource
    ID="employees" xmlns="lmscompany/ds"
    serverType="sql"
    tableName="employeeTable"
    recordName="employee"
    testFileName="/examples/shared/ds/test_data/employees.data.xml"
    titleField="Name"
>
    <fields>
        <field name="userOrder"       title="userOrder"       type="integer"  canEdit="false"    hidden="true"/>
        <field name="Name"            title="Name"            type="text"     length="128"/>
        <field name="EmployeeId"      title="Employee ID"     type="integer"  primaryKey="true"  required="true"/>
        <field name="ReportsTo"       title="Manager"         type="integer"  required="true"
               foreignKey="employees.EmployeeId"  rootValue="1" detail="true"/>
        <field name="Job"             title="Title"           type="text"     length="128"/>
        <field name="Email"           title="Email"           type="text"     length="128"/>
        <field name="EmployeeType"    title="Employee Type"   type="text"     length="40"/>
        <field name="EmployeeStatus"  title="Status"          type="text"     length="40"/>
        <field name="Salary"          title="Salary"          type="float"/>
        <field name="OrgUnit"         title="Org Unit"        type="text"     length="128"/>
        <field name="Gender"          title="Gender"          type="text"     length="7">
            <valueMap>
                <value>male</value>
                <value>female</value>
            </valueMap>
        </field>
        <field name="MaritalStatus"   title="Marital Status"  type="text"     length="10">
            <valueMap>
                <value>married</value>
                <value>single</value>
            </valueMap>
        </field>
    </fields>
    <serverObject lookupStyle="new" className="com.smartgwt.sample.server.listener.Employees"/>
    <operationBindings>
        <operationBinding operationType="fetch" serverMethod="myFetch">
    </operationBinding>
    </operationBindings>
</DataSource>

Employees.java:

package com.smartgwt.sample.server.listener;

import javax.servlet.http.HttpServletRequest;

import com.isomorphic.criteria.criterion.NotNullCriterion;
import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;

public class Employees {
    public DSResponse myFetch(DSRequest request, HttpServletRequest servletRequest) throws Exception {
        request.addToCriteria(new NotNullCriterion("EmployeeType"));
        return request.execute();
    }
}

employeesUpload.ds.xml:

<DataSource ID="employeesUpload" serverType="sql" tableName="employeeTable" recordName="employee" useAnsiJoins="true">
    <fields>
        <field name="EmployeeId" uploadFieldName="EmployeeId" title="Employee ID" type="integer" primaryKey="true" required="true" />

        <field name="Name" uploadFieldName="Name" title="Name" type="text" length="128" />

        <!-- Removed type="integer" in ReportsTo, as this triggers another error -->
        <field name="ReportsTo" uploadFieldName="ReportsTo" displayField="employeeName" title="Manager"
               required="true" importStrategy="display" foreignKey="employees.EmployeeId"
               relatedTableAlias="relatedEmployees">
            <validators>
                <validator type="hasRelatedRecord" errorMessage="Unknown ReportsTo"/>
            </validators>
        </field>
        <field name="employeeName" includeFrom="employees.Name" />

        <field name="Job" uploadFieldName="Job" title="Title" type="text" length="128">
            <valueMap>
                <value>Developer</value>
                <value>IT-Infrastructure</value>
            </valueMap>
        </field>

        <field name="Gender" uploadFieldName="Gender" title="Gender" type="text" length="7">
            <valueMap>
                <value>male</value>
                <value>female</value>
            </valueMap>
        </field>
    </fields>
</DataSource>

TestdataImportEmployees.csv:

EmployeeId,Name,ReportsTo,Job,Gender
8000,Ray Sun,Charles Madigen,IT-Infrastructure,

If you start the sample and upload the file you'll see this SQL in the server log (see employeeTable.EmployeeType IS NOT NULL):

=== 2016-11-03 13:26:48,100 [5-53] INFO  SQLDataSource - [builtinApplication.null] 36: Executing SQL query on 'HSQLDB': SELECT employeeTable.userOrder, employeeTable.Name, employeeTable.EmployeeId, employeeTable.ReportsTo, employeeTable.Job, employeeTable.Email, employeeTable.EmployeeType, employeeTable.EmployeeStatus, employeeTable.Salary, employeeTable.OrgUnit, employeeTable.Gender, employeeTable.MaritalStatus FROM employeeTable WHERE ((LOWER(employeeTable.Name) = LOWER('charles madigen') AND employeeTable.Name IS NOT NULL) AND ([B]employeeTable.EmployeeType IS NOT NULL[/B]))

This is the Text-to-ID lookup query for the importStrategy="display".

You will also see this log:

=== 2016-11-03 13:26:48,319 [5-53] INFO  SQLDriver - [builtinApplication.null] Executing SQL query on 'HSQLDB' using connection '1848946059': SELECT employeeTable.userOrder, employeeTable.Name, employeeTable.EmployeeId, employeeTable.ReportsTo, employeeTable.Job, employeeTable.Email, employeeTable.EmployeeType, employeeTable.EmployeeStatus, employeeTable.Salary, employeeTable.OrgUnit, employeeTable.Gender, employeeTable.MaritalStatus FROM employeeTable WHERE ((LOWER(CAST(employeeTable.EmployeeId AS varchar(50))) = LOWER('4') AND CAST(employeeTable.EmployeeId AS varchar(50)) IS NOT NULL) AND ([B]employeeTable.EmployeeType IS NOT NULL[/B]))

This is the hasRelatedRecord-validator query which unfortunately now also uses the employeeTable.EmployeeType IS NOT NULL because I needed to change the default fetch in order to solve my use case.

My suggestion was/is: Could you add an attribute (now named uploadOperationId) that is only used for the Text-to-ID lookup query of the BatchUploader.
I need this in order to inject another WHERE-condition in the lookup SQL (as written in #1).

I do not see another way to solve this besides the way shown in this sample with it's huge downside that I need modify the default fetch for a one-time usage.
For you it is most likely just the application of the operationId to the fetch query if the suggested attribute is set.

Thank you & Best regards
Blama

[Isomorphic]

The approach you're taking (but haven't taken the last step on) is what we meant by solving this at the application level. You can go ahead and modify the fetch operation to address this.

We don't generally want to add properties whose usage is so obscure that no one will ever find it or use it. And we think this is an example of that - it simplifies your implementation but would never be used again.

[Blama]

Hi Isomorphic,

OK, thank you. I'll solve it that way, then.

Best regards
Blama

[Blama]

Hi Isomorphic,

I did as suggested, but it seems that the query issued for the importStrategy="display" does not use the normal way of querying.
The request is not marked as client request, therefore the criteria are not applied.

This is not a big problem, as I can use DMI injected criteria as well, but does not seem correct anyway.

See this test case using v10.1p_2016-11-08:

BuiltInDS.html change:

DataSourceLoader?dataSource=supplyItem,animals,employees,employeesUpload,batchUpload
[B][/B]

BuiltInDS.java:

package com.smartgwt.sample.client;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.core.KeyIdentifier;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.util.Page;
import com.smartgwt.client.util.PageKeyHandler;
import com.smartgwt.client.util.SC;
import com.smartgwt.client.widgets.BatchUploader;
import com.smartgwt.client.widgets.IButton;
import com.smartgwt.client.widgets.Window;
import com.smartgwt.client.widgets.events.ClickEvent;
import com.smartgwt.client.widgets.events.ClickHandler;
import com.smartgwt.client.widgets.grid.ListGridField;
import com.smartgwt.client.widgets.layout.VLayout;

public class BuiltInDS implements EntryPoint {
    private VLayout mainLayout;
    private IButton recreateBtn;

    public void onModuleLoad() {
        KeyIdentifier debugKey = new KeyIdentifier();
        debugKey.setCtrlKey(true);
        debugKey.setKeyName("D");

        Page.registerKey(debugKey, new PageKeyHandler() {
            public void execute(String keyName) {
                SC.showConsole();
            }
        });

        mainLayout = new VLayout(20);
        mainLayout.setWidth100();
        mainLayout.setHeight100();

        recreateBtn = new IButton("Recreate");
        recreateBtn.addClickHandler(new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                recreate();
            }
        });
        mainLayout.addMember(recreateBtn);
        recreate();
        mainLayout.draw();
    }

    private void recreate() {
        Window w = new Window();
        w.setWidth("95%");
        w.setHeight("95%");
        w.setMembersMargin(0);
        w.setModalMaskOpacity(70);
        w.setTitle("importStrategy=\"display\" does not use operationBinding");
        w.setShowMinimizeButton(false);
        w.setIsModal(true);
        w.setShowModalMask(true);
        w.centerInPage();

        BatchUploader batchUploader = new BatchUploader();
        batchUploader.setWidth100();
        batchUploader.setUploadDataSource(DataSource.get("employeesUpload"));

        ListGridField nameLGF = new ListGridField("Name");

        ListGridField managerLGF = new ListGridField("ReportsTo");
        managerLGF.setOptionDataSource(DataSource.get("employees"));
        managerLGF.setValueField("EmployeeId");
        managerLGF.setDisplayField("Name");

        ListGridField genderLGF = new ListGridField("Gender");

        batchUploader.setGridFields(nameLGF, genderLGF, managerLGF);

        w.addItem(batchUploader);
        w.show();
    }
}

employees.ds.xml:

<DataSource
    ID="employees"
    serverType="sql"
    tableName="employeeTable"
    recordName="employee"
    testFileName="/examples/shared/ds/test_data/employees.data.xml"
    titleField="Name"
>
    <fields>
        <field name="userOrder"       title="userOrder"       type="integer"  canEdit="false"    hidden="true"/>
        <field name="Name"            title="Name"            type="text"     length="128"/>
        <field name="EmployeeId"      title="Employee ID"     type="integer"  primaryKey="true"  required="true"/>
        <field name="ReportsTo"       title="Manager"         type="integer"  required="true" 
               foreignKey="employees.EmployeeId"  rootValue="1" detail="true"/>
        <field name="Job"             title="Title"           type="text"     length="128"/> 
        <field name="Email"           title="Email"           type="text"     length="128"/>
        <field name="EmployeeType"    title="Employee Type"   type="text"     length="40"/>
        <field name="EmployeeStatus"  title="Status"          type="text"     length="40"/>
        <field name="Salary"          title="Salary"          type="float"/>
        <field name="OrgUnit"         title="Org Unit"        type="text"     length="128"/>
        <field name="Gender"          title="Gender"          type="text"     length="7">
            <valueMap>
                <value>male</value>
                <value>female</value>
            </valueMap>
        </field>
        <field name="MaritalStatus"   title="Marital Status"  type="text"     length="10">
            <valueMap>
                <value>married</value>
                <value>single</value>
            </valueMap>
        </field>
    </fields>
    <operationBindings>
[B]        <operationBinding operationType="fetch">
            <criteria fieldName="Gender" operator="equals" value="female" />
        </operationBinding>[/B]
    </operationBindings>
</DataSource>

employeesUpload.ds.xml:

<DataSource ID="employeesUpload" serverType="sql" tableName="employeeTable" recordName="employee" useAnsiJoins="true">
    <fields>
        <field name="EmployeeId" primaryKey="true" hidden="true" type="sequence" />

        <field name="Name" uploadFieldName="Name" title="Name" type="text" length="128">
            <validators>
                <validator type="isUnique" caseSensitive="true" />
            </validators>
        </field>

        <field name="ReportsTo" uploadFieldName="ReportsTo" displayField="ReportsToName" title="Manager" required="true" importStrategy="display"
            foreignKey="employees.EmployeeId" relatedTableAlias="relatedReportsTo">
            <validators>
                <validator type="hasRelatedRecord" errorMessage="Unknown ReportsTo" />
                <validator type="serverCustom" errorMessage="$errorMessage">
                    <serverObject lookupStyle="new" className="com.smartgwt.sample.server.listener.TrueValidator" />
                </validator>
            </validators>
        </field>
        <field name="ReportsToName" includeFrom="employees.Name" includeVia="ReportsTo" />

        <field name="Gender" uploadFieldName="Gender" title="Gender" type="text" length="7">
            <valueMap>
                <value>male</value>
                <value>female</value>
            </valueMap>
        </field>
    </fields>
</DataSource>

TestdataImportEmployees.csv.txt:

Name,ReportsTo,Gender
Anna Sun,Charles Madigen,female
Mike Sun,Tamara Kane,male

If you start the sample and upload the file, you'll see this screen:


As you can see, row #2 does not have problems and also for row #1 the name was translated to an id. This should not have been possible with the operationBinding defined in employees.ds.xml.
If you open the SelectItem, you'll see that the list does only include female employees as expected.

The erroneous SQL from the server log is this:

=== 2016-11-10 18:59:14,046 [-381] DEBUG DSRequest - Caching instance 524 of DS 'employees' from DSRequest.getDataSource()
=== 2016-11-10 18:59:14,046 [-381] DEBUG DSRequest - Caching instance 524 of DS employees
=== 2016-11-10 18:59:14,046 [-381] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
[B]=== 2016-11-10 18:59:14,046 [-381] DEBUG DeclarativeSecurity - Request is not a client request, ignoring security checks.[/B]
=== 2016-11-10 18:59:14,046 [-381] DEBUG AppBase - [builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
=== 2016-11-10 18:59:14,046 [-381] DEBUG AppBase - [builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
=== 2016-11-10 18:59:14,046 [-381] INFO  SQLDataSource - [builtinApplication.null] Performing fetch operation with
    criteria: {Name:"Charles Madigen"}    values: {Name:"Charles Madigen"}
=== 2016-11-10 18:59:14,046 [-381] INFO  SQLDataSource - [builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2016-11-10 18:59:14,046 [-381] INFO  SQLDataSource - [builtinApplication.null] 524: Executing SQL query on 'HSQLDB': SELECT employeeTable.userOrder, employeeTable.Name, employeeTable.EmployeeId, employeeTable.ReportsTo, employeeTable.Job, employeeTable.Email, employeeTable.EmployeeType, employeeTable.EmployeeStatus, employeeTable.Salary, employeeTable.OrgUnit, employeeTable.Gender, employeeTable.MaritalStatus FROM employeeTable WHERE (LOWER(employeeTable.Name)='charles madigen')
=== 2016-11-10 18:59:14,047 [-381] DEBUG PoolableSQLConnectionFactory - [builtinApplication.null] DriverManager fetching connection for HSQLDB via jdbc url jdbc:hsqldb:hsql://localhost/isomorphic
=== 2016-11-10 18:59:14,047 [-381] DEBUG PoolableSQLConnectionFactory - [builtinApplication.null] Passing JDBC URL only to getConnection
=== 2016-11-10 18:59:14,148 [-381] DEBUG PoolableSQLConnectionFactory - [builtinApplication.null] makeObject() created an unpooled Connection '2026113381'
=== 2016-11-10 18:59:14,148 [-381] DEBUG SQLConnectionManager - [builtinApplication.null] Borrowed connection '2026113381'
[B]=== 2016-11-10 18:59:14,148 [-381] INFO  SQLDriver - [builtinApplication.null] Executing SQL query on 'HSQLDB' using connection '2026113381': SELECT employeeTable.userOrder, employeeTable.Name, employeeTable.EmployeeId, employeeTable.ReportsTo, employeeTable.Job, employeeTable.Email, employeeTable.EmployeeType, employeeTable.EmployeeStatus, employeeTable.Salary, employeeTable.OrgUnit, employeeTable.Gender, employeeTable.MaritalStatus FROM employeeTable WHERE (LOWER(employeeTable.Name)='charles madigen')[/B]
=== 2016-11-10 18:59:14,149 [-381] INFO  DSResponse - DSResponse: List with 1 items

I think the problem is that the BatchUploader does not use the client request's RPCManager and therefore the request is not marked as client request.

This is not an important one for me, as I can use the DMI way as well.

Best regards
Blama

[Blama]

Hi Isomorphic,

sorry, the DMI-workaround does not work, so I do have a big problem here:

This is my DMI code in order to restrict the lookup to the mappings created by the current user:

public class T_STATUS_IMPORTTRANSLATION_LOOKUP {
    public DSResponse fetch(DSRequest request, HttpServletRequest servletRequest) throws Exception { [B]// servletRequest is null![/B]
        request.addToCriteria(new SimpleCriterion(DatasourceFieldEnum.T_STATUS_IMPORTTRANSLATION__USER_ID.getValue(), DefaultOperators.Equals,
                servletRequest.getSession().getAttribute(UserPropertiesEnum.ID.getValue())));
        return request.execute();
    }
};

This results in a NPE. Problem is that the servletRequest parameter is null and I therefore can't get the userId from the session.

Could you please have a look? Perhaps fixing the "not-a-client-request" from the previous post will also heal this one.

This is an important one for me as it will lead to problems once many users create mappings.

Thank you & Best regards
Blama

[Blama]

Hi Isomorphic,

thinking about it, the "not-a-client-request" is correct the way it is - it is a server initiated request like any other request initiated in a DMI.
The problem is the servletRequest parameter being null.

Best regards
Blama

[Blama]

Hi Isomorphic,

could you reproduce the issue here?

Thanks you & Best regards
Blama

[Isomorphic]

This is fixed for 5.1 version and available for download in nightly builds since Nov 17.

[Blama]

Hi Isomorphic,

thanks for taking care of this. It is now working for me as expected using v10.1p_2016-11-17.
It is also working without DMI and using

        <operationBinding operationType="fetch">
            <!-- Needed as the DataSource is used as lookup during BatchUpload-upload. As the UC is on (user_id, externalname) this is needed
            in order to guarantee that only one row will be returned. Same for setting user_id on ADD. -->
            <criteria fieldName="USER_ID" operator="equals" value="$session.authenticatedUserID" />
        </operationBinding>

instead, so this is handled as client request.

Thank you & Best regards
Blama