Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    6.1p DataImport importToDataSource issue in combination with RESTHandler

    Hi Isomorphic,

    I'm currently creating an API to my application and hit some bumps here in DataImport I use in combination with RESTHandler (latest 6.1p, v11.1p_2017-11-08).
    The issues/questions are mainly DataImport-related and the whole testcase is serverside only (FF26 Dev mode in Eclipse).

    employeesUpload.ds.xml:
    Code:
    <DataSource xmlns="lmscompany/ds" xmlns:fmt="lmscompany/fmt" ID="employeesUpload" serverType="sql" tableName="employeeTable" recordName="employee" useAnsiJoins="true">
    <fmt:bundle basename="com.smartgwt.sample.server.listener.DSXMLResources-utf8" encoding="utf-8" />
        <fields>
            <field name="EmployeeId" title="Employee ID" type="integer" primaryKey="true" required="true"/>
    
            <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" required="true" importStrategy="display"
                foreignKey="employees.EmployeeId" relatedTableAlias="relatedReportsTo">
                <title><fmt:message key="ReportsTo" /></title>
                <validators>
                    <validator type="hasRelatedRecord" errorMessage="Unknown ReportsTo" />
                </validators>
            </field>
            <field name="ReportsToName" includeFrom="employees.Name" includeVia="ReportsTo" />
    
            <field name="Job" uploadFieldName="Job" title="Title" type="text" length="128">
                <validators>
                    <validator type="isOneOf" />
                </validators>
                <valueMap>
                    <value>Developer</value>
                    <value>IT-Infrastructure</value>
                    <value>Other</value>
                </valueMap>
            </field>
    
            <field name="Gender" uploadFieldName="Gender" title="Gender" type="text" length="7">
                <validators>
                    <validator type="isOneOf" />
                </validators>
                <valueMap>
                    <value>male</value>
                    <value>female</value>
                </valueMap>
            </field>
    
            <field name="Salary" uploadFieldName="Salary" title="Salary" type="integer">
            </field>
        </fields>
        <serverObject lookupStyle="new" className="com.smartgwt.sample.server.listener.EmployeesUpload" />
        <operationBindings>
            <operationBinding operationType="custom" operationId="upload" serverMethod="upload" />
        </operationBindings>
    </DataSource>
    DSXMLResources-utf8.properties:
    Code:
    ReportsTo = MyManager
    web.xml addition:
    Code:
        <servlet>
            <servlet-name>Upload</servlet-name>
            <servlet-class>com.smartgwt.sample.server.listener.Upload</servlet-class>
        </servlet>
        <servlet-mapping>
            <servlet-name>Upload</servlet-name>
            <url-pattern>/Upload</url-pattern>
        </servlet-mapping>
    EmployeesUpload.java (this will change for the different issues and I'll start different threads for this):
    Code:
    package com.smartgwt.sample.server.listener;
    
    import java.io.Reader;
    import java.io.StringReader;
    import java.io.StringWriter;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletRequest;
    
    import org.json.simple.JSONObject;
    
    import com.isomorphic.datasource.DSRequest;
    import com.isomorphic.datasource.DSResponse;
    import com.isomorphic.tools.DataImport;
    import com.isomorphic.tools.DataImport.ImportFormat;
    
    public class EmployeesUpload {
        public DSResponse upload(DSRequest dsRequest, HttpServletRequest servletRequest) throws Exception {
            @SuppressWarnings("unchecked")
            Map<String, Object> requestValues = dsRequest.getValues();
            Reader jsonReader = new StringReader(convertToCSV(requestValues));
    
            DataImport dataImporter = new DataImport(ImportFormat.CSV, ";");
            dataImporter.setPopulateDisplayFields(true);
    
            long i = dataImporter.importToDataSource(jsonReader, "employeesUpload");
            if (i == 1)
                return new DSResponse(dsRequest.getDataSource()).setSuccess();
            else
                return new DSResponse(dsRequest.getDataSource()).setFailure("Not successfully imported");
        }
    
        private String convertToCSV(Map<String, Object> requestValues) {
            StringWriter csvHeader = new StringWriter();
            StringWriter csvData = new StringWriter();
            for (Map.Entry<String, Object> entry : requestValues.entrySet()) {
                String cleanKey = entry.getKey().replace(";", "").replace("\"", "");
                String cleanValue = entry.getValue().toString().replace(";", "").replace("\"", "");
                csvHeader.append(cleanKey + ";");
                csvData.append(cleanValue + ";");
            }
            return csvHeader.toString() + "\r\n" + csvData.toString();
        }
    }
    Sent ARC request (Advanced REST client 10.0.8-stable from the Chrome Web store):
    Code:
    POST to http://127.0.0.1:8888/builtinds/sc/RESTHandler
    
    Body:
    <request>
        <dataSource>employeesUpload</dataSource>
        <operationType>custom</operationType>
        <operationId>upload</operationId>
        <data>
            <EmployeeId>100001</EmployeeId>
              <Name>New colleague</Name>
            <ReportsTo>Charles Madigen</ReportsTo>
            <Job>Developer</Job>
            <Gender>male</Gender>
            <Salary>12345</Salary>
          </data>
      </request>
    Response:
    Code:
    <response>
    <status>
    -1
    </status>
    <queueStatus>
    -1
    </queueStatus>
    <data>
    Not successfully imported
    </data>
     </response>
    Server log:
    Code:
    === 2017-11-08 21:18:08,342 [7-31] INFO  RequestContext - URL: '/builtinds/sc/RESTHandler', User-Agent: 'null': Unsupported WITHOUT Accept-Encoding header
    === 2017-11-08 21:18:08,343 [7-31] DEBUG RESTHandler - Defaulting response data format to xml
    === 2017-11-08 21:18:08,343 [7-31] DEBUG RestRequestParser - Parsing xml object: '<request>
        <dataSource>employeesUpload</dataSource>
        <operationType>custom</operationType>
        <operationId>upload</operationId>
        <data>
            <EmployeeId>100001</EmployeeId>
              <Name>New colleague</Name>
            <ReportsTo>Charles Madigen</ReportsTo>
            <Job>Developer</Job>
            <Gender>male</Gender>
            <Salary>12345</Salary>
          </data>
      </request>'
    === 2017-11-08 21:18:08,345 [7-31] DEBUG XML - Parsed XML from (in memory stream): 2ms
    === 2017-11-08 21:18:08,347 [7-31] INFO  RESTHandler - Performing 1 operation(s)
    === 2017-11-08 21:18:08,347 [7-31] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
    === 2017-11-08 21:18:08,347 [7-31] DEBUG DeclarativeSecurity - DataSource employeesUpload is not in the pre-checked list, processing...
    === 2017-11-08 21:18:08,348 [7-31] DEBUG LocaleMessage - Returning Properties from cache for props file com.smartgwt.sample.server.listener.DSXMLResources-utf8_de_DE
    === 2017-11-08 21:18:08,348 [7-31] DEBUG LocaleMessage - Returning Properties from cache for props file com.smartgwt.sample.server.listener.DSXMLResources-utf8_de_DE
    === 2017-11-08 21:18:08,350 [7-31] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
    === 2017-11-08 21:18:08,350 [7-31] DEBUG DeclarativeSecurity - Request is not a client request, ignoring security checks.
    === 2017-11-08 21:18:08,351 [7-31] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
    === 2017-11-08 21:18:08,351 [7-31] DEBUG DeclarativeSecurity - Request is not a client request, ignoring security checks.
    === 2017-11-08 21:18:08,351 [7-31] DEBUG AppBase - [builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
    === 2017-11-08 21:18:08,351 [7-31] DEBUG AppBase - [builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
    === 2017-11-08 21:18:08,351 [7-31] INFO  SQLDataSource - [builtinApplication.null] Performing fetch operation with
        criteria: {EmployeeId:"Charles Madigen"}    values: {EmployeeId:"Charles Madigen"}
    === 2017-11-08 21:18:08,352 [7-31] DEBUG SQLDataSource - [builtinApplication.null] DataSource 51 acquired SQLDriver instance 1994064315 during initialization
    [B]=== 2017-11-08 21:18:08,352 [7-31] WARN  SQLWhereClause - [builtinApplication.null] Got non-numeric value 'Charles Madigen' for numeric column 'EmployeeId', creating literal false expression: java.lang.NumberFormatException: For input string: "Charle"[/B]
    === 2017-11-08 21:18:08,352 [7-31] INFO  SQLDataSource - [builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
    [B]=== 2017-11-08 21:18:08,353 [7-31] INFO  SQLDataSource - [builtinApplication.null] 51: 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 ('0'='1')[/B]
    === 2017-11-08 21:18:08,353 [7-31] DEBUG SQLConnectionManager - [builtinApplication.null] Borrowed connection '998793675'
    === 2017-11-08 21:18:08,353 [7-31] INFO  SQLDriver - [builtinApplication.null] Executing SQL query on 'HSQLDB' using connection '998793675': 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 ('0'='1')
    === 2017-11-08 21:18:08,354 [7-31] INFO  DSResponse - DSResponse: List with 0 items
    === 2017-11-08 21:18:08,354 [7-31] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 51
    === 2017-11-08 21:18:08,354 [7-31] DEBUG SQLDriver - Freeing SQLDriver dbConnection 998793675 for SQLDriver instance 1994064315
    === 2017-11-08 21:18:08,354 [7-31] DEBUG SQLConnectionManager - About to close connection with hashcode "998793675"
    === 2017-11-08 21:18:08,354 [7-31] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 51
    === 2017-11-08 21:18:08,354 [7-31] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 51
    === 2017-11-08 21:18:08,354 [7-31] DEBUG ValidationContext - Adding validation errors at path '/employeesUpload/ReportsTo/ReportsTo': {errorMessage=Unknown ReportsTo}
    === 2017-11-08 21:18:08,355 [7-31] INFO  Validation - Validation error: [
        {
            ReportsTo:{
                errorMessage:"Unknown ReportsTo"
            }
        }
    ]
    === 2017-11-08 21:18:08,355 [7-31] DEBUG DataSourceDMI - Freeing resources in DataSourceDMI...
    === 2017-11-08 21:18:08,355 [7-31] DEBUG DataSourceDMI - Freeing QueueResources in DataSourceDMI
    === 2017-11-08 21:18:08,355 [7-31] DEBUG DataSourceDMI - Freeing resources in DataSourceDMI...
    === 2017-11-08 21:18:08,355 [7-31] DEBUG RPCManager - Content type for RPC transaction: text/html; charset=UTF-8
    === 2017-11-08 21:18:08,355 [7-31] DEBUG RPCManager - DMI response, dropExtraFields: false
    === 2017-11-08 21:18:08,356 [7-31] WARN  RequestContext - Content type has already been set to: text/html; charset=UTF-8 - setting to: text/xml
    === 2017-11-08 21:18:08,356 [7-31] DEBUG DataSourceDMI - Freeing QueueResources in DataSourceDMI
    As you can see, importToDataSource() does not use setPopulateDisplayFields(true), even though it is set. This does work for importDataSourceRecords().

    Best regards
    Blama
    Last edited by Blama; 8 Nov 2017, 13:02. Reason: web.xml change added to thread

    #2
    Hi Isomorphic,

    issue #2 (minor), same setup, but

    Code:
    public DSResponse upload(DSRequest dsRequest, HttpServletRequest servletRequest) throws Exception {
            return new DSResponse(dsRequest.getDataSource()).setSuccess();
    }
    For the request from #1, the result is:
    Code:
    <response>
    <status>
    0
    </status>
    <queueStatus>
    0
    </queueStatus>
    <startRow>
    -1
    </startRow>
    <endRow>
    -1
    </endRow>
    <totalRows>
    -1
    </totalRows>
    <queueStatus>
    0
    </queueStatus>
    <data />
     </response>
    Please note that <queueStatus> is in there twice.

    Best regards
    Blama

    Comment


      #3
      Hi Isomorphic,

      issue #3, same request:
      Code:
      package com.smartgwt.sample.server.listener;
      
      import java.io.Reader;
      import java.io.StringReader;
      import java.io.StringWriter;
      import java.util.List;
      import java.util.Map;
      
      import javax.servlet.http.HttpServletRequest;
      
      import com.isomorphic.datasource.DSRequest;
      import com.isomorphic.datasource.DSResponse;
      import com.isomorphic.datasource.DataSource;
      import com.isomorphic.tools.DataImport;
      import com.isomorphic.tools.DataImport.ImportFormat;
      
      public class EmployeesUpload {
          public DSResponse upload(DSRequest dsRequest, HttpServletRequest servletRequest) throws Exception {
      
              @SuppressWarnings("unchecked")
              Map<String, Object> requestValues = dsRequest.getValues();
              Reader jsonReader = new StringReader(convertToCSV(requestValues));
      
              DataImport dataImporter = new DataImport(ImportFormat.CSV, ";");
              dataImporter.setPopulateDisplayFields(true);
      
              @SuppressWarnings("unchecked")
              List<Map<String, Object>> dataImporterResult = dataImporter.importDataSourceRecords(jsonReader, "employeesUpload");
      
              DSRequest addRequest = new DSRequest("employeesUpload", DataSource.OP_ADD, dsRequest.getRPCManager());
              addRequest.setValues(dataImporterResult);
              DSResponse addResponse = addRequest.execute();
              return addResponse;
          }
      
          /*
              private String convertToJSON(Map<String, Object> requestValues) {
                  // json-simple-1.1.1.jar
                  // https://code.google.com/archive/p/json-simple/
                  JSONObject jsonFinalObject = new JSONObject();
                  for (Map.Entry<String, Object> entry : requestValues.entrySet()) {
                      jsonFinalObject.put(entry.getKey(), entry.getValue());
                  }
                  return jsonFinalObject.toJSONString();
              }
      
              private String convertToXML(Map<String, Object> requestValues) {
                  StringWriter sw = new StringWriter();
                  sw.append("<record>\r\n");
                  for (Map.Entry<String, Object> entry : requestValues.entrySet()) {
                      sw.append("\t<" + entry.getKey() + ">" + entry.getValue() + "</" + entry.getKey() + ">\r\n");
                  }
                  sw.append("</record>");
                  return sw.toString();
              }
          */
          private String convertToCSV(Map<String, Object> requestValues) {
              StringWriter csvHeader = new StringWriter();
              StringWriter csvData = new StringWriter();
              for (Map.Entry<String, Object> entry : requestValues.entrySet()) {
                  String cleanKey = entry.getKey().replace(";", "").replace("\"", "");
                  String cleanValue = entry.getValue().toString().replace(";", "").replace("\"", "");
                  csvHeader.append(cleanKey + ";");
                  csvData.append(cleanValue + ";");
              }
              return csvHeader.toString() + "\r\n" + csvData.toString();
          }
      }
      Response:
      Code:
      <response>
      <status>
      0
      </status>
      <queueStatus>
      0
      </queueStatus>
      <startRow>
      -1
      </startRow>
      <endRow>
      -1
      </endRow>
      <totalRows>
      -1
      </totalRows>
      <queueStatus>
      0
      </queueStatus>
      <queueStatus>
      0
      </queueStatus>
      <data>
      <record>
      <Salary>
      12345.0
      </Salary>
      <ReportsTo>
      4
      </ReportsTo>
      <Gender>
      male
      </Gender>
      <EmployeeId>
      100002
      </EmployeeId>
      <Job>
      Developer
      </Job>
      <ReportsToName>
      Charles Madigen
      </ReportsToName>
       </record>
      </data>
       </response>
      Issues:
      • As you can see, the queueStatus is now present three times.
      • Generally speaking this is working, but IMHO the workaround with CSV should not be necessary. DataImport-methods should have an overload that take Map<String, Object> and do the conversion displayName->ID.
      • When using JSON or XML, what is the expected structure? Could you add it to the docs? You can see my convertToXML() and convertToJSON(), but the DataImport-docs don't say anything about the enclosing tag or similar.
        Additionally, XML is mentioned as possibility besides XML/CSV in the class description, but never in the methods.
      DataImport contains methods that can be used to import data from a test data file to the List of Maps format commonly used for DataSource records (importToRows()), or directly into a DataSource (importToDataSource()).
      By default the input file is expected to contain comma-delimited data like a .csv file, but JSON and XML are also supported.
      Imported data may be transformed during import, for details search SmartClient Reference for "dataSourceField.importStrategy".
      • As enhancement: It would be great if it were possible to directly add data to a DataSource via a ARC request using the String-to-ID lookup and not to have to call DataImport oneself in a boilerplate method. If this is not a good idea as default for all ADDs for fields with importStrategy="display", then perhaps as additional attribute for an ADD or UPDATE operation binding.
      Best regards
      Blama

      Comment


        #4
        Hi Isomorphic,

        those now sat quite a while. Do you have an opinion on those?
        Especially the "queueStatus is present multiple times" is a bug IMHO. As I gave a customer access to the API now, I expect the question why this is to come up very soon now.

        Thank you & Best regards
        Blama

        Comment


          #5
          These issues are assigned. It's definitely a bug that queueStatus would appear twice; we haven't looked deeply enough into the others to say yet.

          Comment


            #6
            Hi Isomorphic,

            do you have an update on this one?

            Thank you & Best regards
            Blama

            Comment


              #7
              No updates yet, they will be posted here when we have them.

              Comment


                #8
                Issues #1 and #2 are fixed and are available for download in nightly builds since Dec 15 (today). Issues from #3 are in progress, thank you for your patience.

                Comment


                  #9
                  Hi Isomorphic,

                  I can see that #1 and #2 are fixed using v11.1p_2017-12-27. Do you have any further information on #3?

                  Thank you & Best regards
                  Blama

                  Comment


                    #10
                    Apologies for the delay, we are addressing issues you listed in #3 (same order as in your post):
                    • fixed
                    • this suggestion is in progress and will be reported here when it's done
                    • we've added XML and JSON formats to the DataImport docs with the reference to format descriptions and samples in Smartclient Reference
                    • taken into consideration

                    Comment


                      #11
                      Hi Isomorphic,

                      thanks. I can see that #3.1 is fixed and also the advice for #3.3 in the SmartGWT docs to look here.

                      I'll wait for an update of #3.2 and perhaps #3.4 then.

                      Best regards
                      Blama

                      Comment


                        #12
                        Hi Isomorphic,

                        #3 point 2 is indeed necessary, more a bug than an enhancement if you want to upload data that includes linefeeds (\r, \n, \r\n). If you do this now like convertToCSV() in #3 this will fail if you have linefeeds (because it seems like a new data row in the CSV). This is the one where you write "in progress" in #10.
                        Linefeeds are perfectly fine in data IMHO and must be converted to <br> at display time if you want to respect them in a label. This is also working as expected when entering them in a TextAreaItem. But they don't work if you need to use DataImport.

                        Best regards
                        Blama

                        Comment


                          #13
                          Just to clarify if we understand #12 correctly. Please answer following questions.

                          Are you talking about linefeeds inside field values? And the way DataImport is not working is that it treats those as new lines, which splits data into rows incorrectly and fails, right? We expect such linefeeds to be escaped. Also, do you get these values from some kind of tool? If so, we'd like to know what it is. Thank you.

                          Regarding #3.2 (providing record as a Map instead of Reader to DataImport): do you expect that if we implement it, then this problem would automatically go away? Sorry for delay, we *are* going to implement this and will update this thread when we do.

                          Comment


                            #14
                            Hi Isomorphic,

                            answering in different order:
                            Originally posted by Isomorphic View Post
                            Regarding #3.2 (providing record as a Map instead of Reader to DataImport): do you expect that if we implement it, then this problem would automatically go away?
                            Yes, expecting it to go away, because there is no escaping whatsoever, but only Java Map and no escaping or CSV/JSON/XML encode on my side and no CSV/JSON/XML decode on your side needed.

                            Originally posted by Isomorphic View Post
                            Are you talking about linefeeds inside field values? And the way DataImport is not working is that it treats those as new lines, which splits data into rows incorrectly and fails, right? We expect such linefeeds to be escaped. Also, do you get these values from some kind of tool? If so, we'd like to know what it is. Thank you.
                            Yes, linefeeds in the field values. Data is self generated. I have a (pretty dumbed down, JSON would be better, but (perhaps) more difficult to generate for websites with forms) mail format that I import:
                            Code:
                            fieldname: value1;
                            fieldname2: value2;
                            fieldname3: value3;
                            (with the requirement of no ; and : in the data). But for ease of mind, lets just assume it were JSON.
                            I then build this from it:
                            Code:
                            fieldname;fieldname2;fieldname3
                            "value1";"value2";"value3"
                            and feed this to DataImport as CSV. DataImport is needed, as some of the fields need to go though DataImport's fK-value ->ID conversion. Otherwise I'd just use plain ADD.
                            As you can see, I do have "value1" with quotes, which is what you mean by "We expect such linefeeds to be escaped" right?

                            So an 1-row import with linefeeds could look like this:
                            Code:
                            fieldname;fieldname2;fieldname3
                            "value1";"Some long data
                            in
                            field 2";"value3"
                            .

                            Or do you mean I should generated this instead by "We expect such linefeeds to be escaped"
                            Code:
                            fieldname;fieldname2;fieldname3
                            "value1";"Some long data\r\nin\r\nfield 2";"value3"
                            , so that it is always exactly 1 CSV row per data row?

                            Then I'd also might have problems with "\" (escape it to "\\"?).
                            This would also work for me for now and the change is easy on my side. But the correct way is using Java Map IMHO. Of course I could also build JSON or XML instead of CSV from the data, which is probably better, now that I think about it, but still, Java Map would be the one with basically no code needed.

                            Best regards
                            Blama
                            Last edited by Blama; 18 Oct 2018, 02:12.

                            Comment


                              #15
                              FYI: Edited the post. As meta problem it seems that phpBB (or whatever runs the forum) has problems with \\ (backslashes) in data, at least when editing posts :)

                              Comment

                              Working...
                              X