Best way to deal with long ids

[fatzopilot]

Hi,

I just noticed that my 64-bit hashcode generated ids get truncated (actually rounded) and that this seems to be a JS limitation.
So they're better dealt with as Strings in SGWT/JS.
I wonder what you consider best practice in this case if the DB uses full range (bigint(20)) numbers as ids.
Is there something like built-in autoconversion from String to int and vice versa or do I need to solve the conversion on a lower level?
Just changing the type of the id field to text did not seem to make a difference and converting the id to a string in a DMI does not seem to help either.
(The ids seem to be truncated on the server as the console already shows them truncated whereas in a bean, they are OK.)

Thanks,
fatzopilot

SmartClient Version: v8.3p_2013-09-07/PowerEdition Deployment (built 2013-09-07)

[Isomorphic]

Did you already try just declaring the field as type "text" in your .ds.xml?

[fatzopilot]

Yes, that did not help.

[Isomorphic]

Could you be more specific :)

You should see *at least* conversion from int to String on data leaving the server, if you are using a recent version. Please the inbound and outbound requests and logs showing what's happening if you're not seeing this.

[fatzopilot]

As you please :)

<DataSource ID="Attachment"
    	xmlFromConfig="true"
		beanClassName="com.aCompany.Attachment"
		dataSourceVersion="1"
		serverType="hibernate"
		autoDeriveSchema="false"
		dropExtraFields="true">
    <fields>
        <field name="id" type="text" title="Id" primaryKey="true" detail="false">
        </field>
        <field name="lastUpdated" type="date" title="Last Updated" detail="false" canView="true">
            <validators>
                <Validator>
                    <type>isDate</type>
                    <typeCastValidator>true</typeCastValidator>
                </Validator>
            </validators>
        </field>
        <field name="dateCreated" type="date" title="Date Created" detail="false" canView="true">
            <validators>
                <Validator>
                    <type>isDate</type>
                    <typeCastValidator>true</typeCastValidator>
                </Validator>
            </validators>
        </field>
        <field name="deleted" type="boolean" title="Deleted" detail="false" canView="true">
            <validators>
                <Validator>
                    <type>isBoolean</type>
                    <typeCastValidator>true</typeCastValidator>
                    <defaultErrorMessage></defaultErrorMessage>
                </Validator>
            </validators>
        </field>
        <field name="file" type="imageFile" title="File" detail="false" canFilter="false"></field>
     	<field name="file_filename" type="text" title="File name" detail="true"></field>
        <field name="file_filesize" type="integer" title="File size" detail="true"></field>
        <field name="file_date_created" type="date" title="File date created" detail="true"></field>
    </fields>
    <allowAdvancedCriteria>true</allowAdvancedCriteria>
    <xmlFromConfig>true</xmlFromConfig>
</DataSource>

RPCRequest:

{
    "actionURL":"http://localhost:8080/aCompany/gwt/com.aCompany.GWTModule/sc/IDACall", 
    "showPrompt":true, 
    "prompt":"Finding Records that match your criteria...", 
    "transport":"xmlHttpRequest", 
    "promptStyle":"cursor", 
    "bypassCache":true, 
    "data":{
        "criteria":{
        }, 
        "operationConfig":{
            "dataSource":"Attachment", 
            "repo":null, 
            "operationType":"fetch", 
            "textMatchStyle":"substring"
        }, 
        "startRow":0, 
        "endRow":75, 
        "componentId":"AttachmentGrid_4", 
        "appID":"builtinApplication", 
        "operation":"ref:Attachment_fetch", 
        "oldValues":{
        }
    }
}

DSRequest:

{
    dataSource:"Attachment", 
    operationType:"fetch", 
    componentId:"AttachmentGrid_4", 
    data:{
    }, 
    startRow:0, 
    endRow:75, 
    textMatchStyle:"substring", 
    resultSet:[ResultSet ID:isc_ResultSet_12 (created by: AttachmentGrid_4)], 
    callback:{
        caller:[ResultSet ID:isc_ResultSet_12 (created by: AttachmentGrid_4)], 
        methodName:"fetchRemoteDataReply"
    }, 
    willHandleError:true, 
    showPrompt:true, 
    prompt:"Finding Records that match your criteria...", 
    oldValues:{
    }, 
    requestId:"Attachment$62716", 
    clientContext:{
        requestIndex:{
            Class:"Number", 
            formatter:"toString", 
            localeStringFormatter:"toString", 
            localeProperties:Obj
        }
    }, 
    fallbackToEval:false, 
    bypassCache:true
}

Response:

{
    "operationId":"ref:Attachment_fetch", 
    "transactionNum":14, 
    "httpResponseCode":200, 
    "transport":"xmlHttpRequest", 
    "status":0, 
    "httpHeaders":{
        "Server":"Apache-Coyote/1.1", 
        "Cache-Control":"no-cache", 
        "Pragma":"no-cache", 
        "Expires":"Tue, 10 Sep 2013 01:24:36 GMT", 
        "Content-Type":"text/plain;charset=UTF-8", 
        "Transfer-Encoding":"chunked", 
        "Date":"Tue, 10 Sep 2013 01:24:37 GMT"
    }, 
    "isStructured":true, 
    "results":{
        "data":[
            {
                "file_filename":"Smartgwt.odt", 
                "file_date_created":"2013-09-09", 
                "lastUpdated":"2013-09-09", 
                "deleted":false, 
                "id":-614287573389057800, 
                "dateCreated":"2013-09-09", 
                "file_filesize":4992
            }, 
            {
                "file_filename":"Ultimate Guide to SmartGWT.webm", 
                "file_date_created":"2013-09-09", 
                "lastUpdated":"2013-09-09", 
                "deleted":false, 
                "id":458539461474260540, 
                "dateCreated":"2013-09-09", 
                "file_filesize":9214523
            }, 
            {
                "file_filename":"spring prob.txt", 
                "file_date_created":"2013-09-10", 
                "lastUpdated":"2013-09-10", 
                "deleted":false, 
                "id":5332552039815583000, 
                "dateCreated":"2013-09-10", 
                "file_filesize":7988
            }, 
            {
                "file_filename":"Clock.pdf", 
                "file_date_created":"2013-09-10", 
                "lastUpdated":"2013-09-10", 
                "deleted":false, 
                "id":5324327873266224000, 
                "dateCreated":"2013-09-10", 
                "file_filesize":7350
            }
        ], 
        "endRow":4, 
        "invalidateCache":false, 
        "isDSResponse":true, 
        "operationType":"fetch", 
        "queueStatus":0, 
        "startRow":0, 
        "status":0, 
        "totalRows":4
    }, 
    "isDSResponse":true, 
    "invalidateCache":false, 
    "data":[
        {
            "file_filename":"Smartgwt.odt", 
            "file_date_created":"2013-09-09", 
            "lastUpdated":"2013-09-09", 
            "deleted":false, 
            "id":-614287573389057800, 
            "dateCreated":"2013-09-09", 
            "file_filesize":4992
        }, 
        {
            "file_filename":"Ultimate Guide to SmartGWT.webm", 
            "file_date_created":"2013-09-09", 
            "lastUpdated":"2013-09-09", 
            "deleted":false, 
            "id":458539461474260540, 
            "dateCreated":"2013-09-09", 
            "file_filesize":9214523
        }, 
        {
            "file_filename":"spring prob.txt", 
            "file_date_created":"2013-09-10", 
            "lastUpdated":"2013-09-10", 
            "deleted":false, 
            "id":5332552039815583000, 
            "dateCreated":"2013-09-10", 
            "file_filesize":7988
        }, 
        {
            "file_filename":"ClockToPrint.pdf", 
            "file_date_created":"2013-09-10", 
            "lastUpdated":"2013-09-10", 
            "deleted":false, 
            "id":5324327873266224000, 
            "dateCreated":"2013-09-10", 
            "file_filesize":7350
        }
    ], 
    "startRow":0, 
    "endRow":4, 
    "totalRows":4
}

The type of "id" in the hibernate/spring bean is Long.
Any more logs needed?

Thanks,
fatzopilot

[Isomorphic]

We did add a feature where declaring type "text" on a field that has integer type for a *SQL* DataSource will force the value to int.

In older versions, setting a trivial valueXPath (same name as the bean getter method) would be one way to force conversion.

[fatzopilot]

In older versions, setting a trivial valueXPath (same name as the bean getter method) would be one way to force conversion.

Changing to

<field name="id" type="text" valueXPath="id" title="Id" primaryKey="true" detail="false">

did not change anything. Or what did you have in mind?

Thanks,
fatzopilot

[Isomorphic]

Yes that's what we meant, but clearly it doesn't work.

On the old version, you'll need to either transform the data once the dsResponse has been retrieved, or create a parallel getter/setter on your bean that returns a String and can take a String.

[fatzopilot]

OK, I was able to solve this using getStringId/setStringId on the bean, a valueXPath="stringId" in the DS field definition for id, and a formula for stringId on the bean (to make queries work).

Cheers,
fatzopilot

[fatzopilot]

Hm, seems like I was a bit to optimistic. Updating is still an issue as hibernate needs a long valued id to access the object via id (it's a long in the db).

Option 1): The obvious option:

<field name="id" type="text" title="Id" valueXPath="stringId" primaryKey="true" detail="false"/> <!-- stringId delivers the id as string -->

The problem arises because the id from the client needs to be transformed back to long (in a DMI or using DSRequestModifiers) in order for hibernate to work.
I tried to replace both, the id criteria and the id value that are contained in a dsRequest. This works but if the request reaches Hibernate through DsRequest, AppBase, and HibernateDataSource, it is "magically" back converted to a String and consequently, Hibernate throws an exception:

org.hibernate.TypeMismatchException: Provided id of the wrong type for class com.aCompany.Attachment. Expected: class java.lang.Long, got class java.lang.String

Option 2): Use an extra alias field (stringId) for the update query. This is what works for add and fetch operations using the methods described earlier. However, for update operations the stringID must be set as primaryKey in order to get submitted from the client, i.e.

<field name="id" type="sequence" title="Id" detail="false"/>
<field name="stringId" type="text" title="Id" primaryKey="true" valueXPath="stringId" canView="true" hidden="false" detail="false"/>

and we need to get rid of the crippled id from the client (done in a DMI).
However, then I get

com.isomorphic.base.UpdateWithoutPKException: Criteria received from the client for update operation is missing the following required unique and/or primary fields: [id]. Either provide all primary key fields or set allowMultiUpdate on the OperationBinding

So SGWT still demands to submit id albeit it is not defined as primary key in the DS.
In addition, I feel that option 2 is clearly a bit too hacky.

Can you please check why the long id gets back-converted as described in 1 or propose another solution?

Thanks,
fatzopilot

[Isomorphic]

As far as Option 1, the field is declared as type "text" so validation forces it to a String.

On the PK error message, you've declared the type as "sequence" so that implies primaryKey=true.

[fatzopilot]

As far as Option 1, the field is declared as type "text" so validation forces it to a String.

Is there a way to disable validation for this case?

On the PK error message, you've declared the type as "sequence" so that implies primaryKey=true.

I changed it to "integer" but without any change.

Thanks,
fatzopilot

[Isomorphic]

No, a field declared as "text" is expected to be "text" and not something else.

Changing that field to "integer" and yet still having an error message about a missing primary key doesn't make sense and we can't reproduce that.

Sorry, we need to move on to bugs. Upgrading or using a DMI are two ways to solve this.

[fatzopilot]

No, a field declared as "text" is expected to be "text" and not something else.

Changing that field to "integer" and yet still having an error message about a missing primary key doesn't make sense and we can't reproduce that.

OK, seems like the last posts have caused some confusion.

There are basically two approaches here:
1) use the original id field to submit a text version of the long id
2) use an alias data source field stringId with type text (and do some tricks to interact with hibernate.

1:
In order to work with very long ids, I tried to circumvent the crippled ids by converting them to text. This can be done if an additional getter/setter/formula field is provided in the bean which is named e.g. stringId. This can then be used in a valueXPath of the id field to obtain a String. Of course, the type of the id field in these cases is text.
Problem here: A text id from the client (e.g. in update operations) cannot be converted back (as described in a previous post) and hibernate does not accept string ids

2:
The field id has a type of "long" in the DB and in usual cases a type of "sequence" in the DS description.
As advised, I changed the type of the id data source field from sequence to integer and removed the primary key attribute, because I wanted to use a different data source field (e.g. stringId with corresponding getter/setter/formula fields in the bean) for that purpose.
Problem here:
Still, the id field is identified as a primary key field for some reason (probably, because it is a primary key field in hibernate), and so, all update operations require it (and it cannot be removed in a DMI, which is a showstopper here).

Sorry, we need to move on to bugs. Upgrading or using a DMI are two ways to solve this.

I am working with DMIs here all the time...
And I tried the latest Evaluation build
SmartClient Version: SNAPSHOT_v9.1d_2013-09-17/EVAL Deployment
which exhibits the exact same behavior in these regards.

[Isomorphic]

To hopefully clarify things again:

The DMI approach means you simply declare fields normally, then add DMI logic to your "fetch" operation where you call the server-side DSResponse.getRecord()s and convert all the Long values to Strings, then call DSResponse.setData() with the updated data. Likewise, take any inbound values in a DSRequest that are in String format and change them to Long.

The alternative approach of creating an "alias field" on your Hibernate bean works as follows: say your bean has getter/setter methods getId()/setId() that work with Long.

1. Create getStringId()/setStringId() which return and take String, which actually convert between String and Long and call getId()/setId() with Long values

2. Declare a field "stringId" of type "text" in your DataSource and make that the primaryKey.

In theory, the same thing should also work by declaring a field "stringId" with a valueXPath to the field "id" - here you would be hoping JXPath is smart enough to handle String<-> Long conversion. It may not be, however, it doesn't look like you've tried this properly yet: in your previous attempts, you either did a no-op valueXPath (field name "id" valueXPath "id") or reversed the suggestion (field name "id" valueXPath "stringId").

And again the feature we referred to in the new version is for *SQL*DataSource, not Hibernate, so you won't get anything out of new versions, you'd have to switch to SQLDataSource first.

Also, what database product and version is this that is using full-range Longs as IDs?