Sequences, forms, and foreign keys

[pmac]

Hi,

Suppose I have a SQL Server database table "Foo" and it has a primary key field "fooID". The column's DB data type is BIGINT, which translates into Long in the Java code. Are Long values compatible with forms and ListGrids?

I was using "INT" as the data type, and using IntegerItem in a form to render the ID. However, there is no corresponding "LongItem" for form items. What do you recommend for this?

Also, if I have a SelectItem with a foreign key reference to another table where the ID is also a BIGINT/Long, what's your recommendation?

I'm trying to change from int-based to Long-based primary-key columns and things are crashing spectacularly. :-)

Thank you!

[pmac]

BTW, I'm trying this because the DataSourceDMI implies that it's possible. The DMI field type is "sequence" in the supplyItemDMI.ds.xml, and the bean has a Long data type.

However, when I read the DataSource docs, it doesn't list any "LONG" FieldType. Also, the description of the "SEQUENCE" FieldType says it behaves just like an "INTEGER" FieldType.

Clarification?

Thanks much!

[pmac]

Bump? Any thoughts on this?

[Isomorphic]

When you use the type "integer" on a DataSource, that's a representation-independent declaration that you want to use whole numbers. Remember, the underlying representation in Java and JavaScript differs, even if GWT is pretending to give you Java on the client. On the server side, if you look, you'll notice we use Long for an "integer" field.

[pmac]

Oh, interesting. Thanks. So when you say if I were to look on the server side, where is it I would look? Server code's not visible, right?

Thx.

[Isomorphic]

You would look at the inbound data from the client, eg, dsRequest.values when saving to a field of integer type.

[will-gilbert]

This has some interesting ramifications.

Our databases use long ints for all primary and foreign keys. JavaScript uses 64 bits to represent ALL numbers, using a 'whole number' representation for numbers up to 2^53. This is well beyond the 2^32 MAX of ints but short of the 2^64 MAX of long ints. After 2^53, numbers are represented as floating point and with lower bits being lost. This is not healthy for database keys.

Currently, we create our own DTOs where we convert the long int keys into Strings where these keys are only going to be used for reference, doing the long to String and String to long conversions in the service layer of our servlet. Are thinking very seriously about adopting SmartGWTEE in order to get around all of these (damn) DTOs.

At one new key per second it would take 286 Million years to reach 2^53. We are confident that JavaScript will not convert our long int keys to floating point numbers anytime soon.

The question now becomes how does SmartGWTEE deal with long ints going to and coming from the JavaScript client -- does it overflow beyond 2^32, do things 'just work' up to 2^53 beyond which 'There be dragons, proceed at your own risk'?

Would love to see a 'Best Practices with longs' from Isomorphic.

Finally, love your your products, SmartGWT is working out well and have recommended SmartGWTEE for next year's budget.

NB: I have suppressed the "-1" for all MAX values for clarity.

--will gilbert
Java UI Architect and Open Source Developer

[Isomorphic]

The client serializes the JavaScript Number to a string, which is then parses server side as a Long. So we should be getting the entire 2^53 range, barring the possibility of a browser or SmartGWT bug.

[will-gilbert]

Thanks for the incredibly fast response on a summer Sunday morning!

Knowing this gives us a good strategy to work with. Here's hoping some day that a future version of JavaScript will give us a true 64 bit long ints.

[kevboy]

I set up a DMI Datasource with an ID field I wanted to use as a long. On the server-side, I create a record with an ID = -8890649417517900275 (just called Random.nextLong()). Then on the client-side, I set up a ListGrid to fetch the record. Since I set the ID field as hidden, I printed out the translated ID to the console, which was -8890649417517900000. The last three digits were truncated to 000. So it seems like something went amiss between server and client.

This happens consistently even with different IDs. Also changing the datasource field type did not help (I tried sequence, integer, and text). I also tried changing the length attribute of the field to no avail.

Any thoughts or fixes?

This is how I fetch the data:

listGrid.getDataSource().fetchData(new Criteria(),new DSCallback () {
    @Override
    public void execute(DSResponse response, Object rawData, DSRequest request) {
        System.err.println("Got data:");
        for (Record o : response.getData()) {
            System.err.println(o.getAttribute("id")); // prints out truncated id
        }
        listGrid.setData(response.getData());                
    }
});

[Isomorphic]

Look in the RPC tab in the Developer Console - is the number truncated there? If so, it happened before it left the server and you have an issue with how you've written your DMI.

[kevboy]

Thanks for the quick response! Yup, it's truncated in the developer console. It's weird because when I print out the data before sending it out, it's not truncated.

Here's my fetch() method (mostly taken from the DMI example):

List<Item> matchingItems =
    MyStore.findMatchingItems((Long)dsRequest.getFieldValue("itemId"),
                                      (String)dsRequest.getFieldValue("itemName"));

// this implementation shows data paging (returning only ranges of requested records)
long totalRows = matchingItems.size();
DSResponse dsResponse = new DSResponse();
dsResponse.setTotalRows(totalRows);
dsResponse.setStartRow(dsRequest.getStartRow());
dsResponse.setEndRow(Math.min(dsRequest.getEndRow(), totalRows));

// trim the data to the requested range of records.  In a real application, the startRow
// and endRow would be passed to the ORM layer or to SQL for maximum efficiency.
List<Item> results; 
if (totalRows > 0 && dsRequest.getEndRow() > 0) { 
    results = matchingItems.subList((int)dsResponse.getStartRow(),
                                    (int)dsResponse.getEndRow());
} else {
    results = matchingItems;
}

int i = 0;
for (Item item : results) {
    // Prints out id non-truncated
    System.err.println("["+ i++ +"]: " + item.getId() + " " + item.getName());
}

// just return the List of matching beans
dsResponse.setData(results);

i = 0;
for (Object o : dsResponse.getDataList()) {
    // Prints out id non-truncated
    Item item = (Item)o;
    System.err.println("["+ i++ +"]: " + item.getId() + " " + item.getName());
}

return dsResponse;

[Isomorphic]

This suggests that Long.toString() truncates the value, which is very strange. Are you on an unusual platform - a mainframe or something, or an unusual JVM?

To confirm the value really leaves the server truncated, please make sure that in the RPC tab of the Developer Console, you have the "Auto Format" checkbox in the response pane unchecked. Also, please take a look at the raw server response in Firebug, and post that here.

[kevboy]

Again, thanks for the quick response! In the developer console's RPC tab, I unchecked Auto-format and the correct values show in the raw response.

auto-format on:  7972953241307677000
auto-format off: 7972953241307676666

auto-format on:  6862221639718042000
auto-format off: 6862221639718041566

I have to leave now but I'll check Firebug tomorrow.

[Isomorphic]

It's a JavaScript limitation on representable numbers. Try this:

eval("var number = 6862221639718041566; number;")

The result is 6862221639718042000. JavaScript Numbers can only represent up to 2^53. However as the previous poster said:

At one new key per second it would take 286 Million years to reach 2^53. We are confident that JavaScript will not convert our long int keys to floating point numbers anytime soon.

So, no realistic problem with using Long as a key type, just don't use random() to generate keys (not a good idea anyway).