SmartGWT Pro UUID Primary Key Issues [with PostgreSQL database]

[mark1]

Firstly thanks for creating such a nice library - I am really enjoying working with it.

I have attached the files I had to change from the 'built-in-ds' sample - to demonstrate the problem. I have also included a sql script for PostgreSQL to load a simple database for it.

Firstly I found (for anyone else that tries this) that the options:
ignoreTextMatchStyleCaseSensitive="true" (for the datasource)
&
ignoreTextMatchStyle="true" (for the field)

Are required for the datasource so it doesn't run LOWER() on the UUID field when updating (which errors).


However I cannot find a way to get the datasource to not specify a 'null' value for the UUID primary key when inserting new records:

org.postgresql.util.PSQLException: ERROR: null value in column "itemid" violates not-null constraint
  Detail: Failing row contains (null, Test, 222222222, null, AA, null, 2.55, null, 2014-07-22).

Is there a way to get the datasource to leave the field out or specify for it to use database's 'DEFAULT' value. I was expecting:
autoGenerated="true" to do this as mentioned in the post below but it seems to have no effect in my case.

http://forums.smartclient.com/showthread.php?t=23690&page=2

I have also tried - customInsertExpression="DEFAULT" but it does not work in my case because I only have the "Pro" edition.


Thank-you very much in advance.

SmartClient Version: v9.1p_2014-07-12/Pro Deployment (built 2014-07-12)

server.properties changes:

sql.defaultDatabase: PostgreSQL

sql.PostgreSQL.driver.serverName: localhost
sql.PostgreSQL.driver.portNumber: 5432
sql.PostgreSQL.driver.databaseName: builtinds
sql.PostgreSQL.driver.user: postgres
sql.PostgreSQL.driver.password: test

[Blama]

Hi mark,

did you already see DataSourceField.FieldType#SEQUENCE?

I don't know if it applies to PostgreSQL as well, but I think it should be possible if you create your PK-values with an explicit sequence, like it is done in Oracle 11 and before.

Best regards,
Blama

[Blama]

Reading http://www.smartclient.com/smartgwte...#autoGenerated I think that it might be better to use the sequence with respect to cacheSync operations.

[mark1]

Hi Blama,

Thanks for the reply, though unfortunately PostgreSQL cannot define a UUID generating sequence - so I end up with this error:

ERROR: relation "supplyitem_itemid_seq" does not exist
  Position: 181

Even if I could get the database to return a UUID for the sequence I don't think the field would like it, based on the documentation I think it would only want to process an Integer in that FieldType:

If you are using the Smart GWT SQL datasource connector, a sequence is a unique, increasing whole number, incremented whenever a new record is added. Otherwise, sequence behaves identically to integer. This type is typically used with field.primaryKey to auto-generate unique primary keys. See also sequenceName and sequenceMode

autoGenerated="true" was defined in my attached "supplyItem.ds.xml" example - however it does not change the servers behaviour for me as expected.

Kind regards,

Mark

[Blama]

You might be right about the FieldType=Integer requirement. That's one for Isomorphic.

If it were an Integer, did you try this solution http://stackoverflow.com/a/13187557?

Best regards,
Blama

[Isomorphic]

This is an error in the way autoGenerated primaryKey fields are handled. It has now been fixed on 4.1 and 5.0 branches; the fix will be present in tomorrow's nightly builds (those dated July 24)

Thanks for the bug report!

Isomorphic Software Support

[mark1]

Thank-you very much for such a quick fix.

I look forward to trying it out tomorrow after the nightly build. :)