Built-In SQL Datasource Using Wrong Identity Table for Postgres

[chimpeenuts]

1. v8.2p_2012-06-28/PowerEdition Deployment (built 2012-06-28)

2. Firefox, 10.0.2

Database is Postgres 9.1. Postgres creates database 'objects' known as sequences to auto-generate primary key values for normal database tables. For example, if you have a 'users' table, postgres will generate a 'users_id_seq' sequence object that Postgres will use to generate auto-incrementing ids for the users' table primary id column.

Using Smart GWT built-in SQL datasource:

<DataSource ID="user" serverType="sql" tableName="users"
  dropExtraFields="true">
  <fields>
    <field name="id" type="sequence" hidden="true" primaryKey="true" />
    <field name="firstName" type="text" />
  </fields>    
</DataSource>

In my application, I'm now trying to insert a new 'User' using this code:

private void createDefaultUser() {
	final DataSource ds = DataSource.get("user");

	if (ds != null) {
		Criteria c = new Criteria();
		c.addCriteria("id", 1);

		ds.fetchData(c, new DSCallback() {

			public void execute(DSResponse response, Object rawData,
			    DSRequest request) {
				if (response.getTotalRows() == 0) {
					// Create a new user

					Record r = new Record();
					r.setAttribute("firstName", "DefaultUser");
					
					ds.addData(r);
				}
			}
		});
	}
}

The ds.addData(r) line above FAILS with this server stack trace exception:

=== 2012-10-31 16:41:39,544 [l0-5] WARN  RequestContext - dsRequest.execute() failed: 
org.postgresql.util.PSQLException: ERROR: relation "users_id" does not exist
  Position: 66
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at com.isomorphic.sql.SQLDriver.doUpdate(SQLDriver.java:530)
	at com.isomorphic.sql.SQLDriver.update(SQLDriver.java:482)
	at com.isomorphic.sql.SQLDriver.executeUpdate(SQLDriver.java:604)
	at com.isomorphic.sql.SQLDataSource.executeNativeUpdate(SQLDataSource.java:403)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1388)
	at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:293)
	at com.isomorphic.sql.SQLDataSource.executeAdd(SQLDataSource.java:245)
	at com.isomorphic.datasource.DataSource.execute(DataSource.java:1291)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:725)
	at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
	at com.isomorphic.application.AppBase.execute(AppBase.java:491)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:1954)
	at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:199)
	at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:156)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:121)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:73)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1097)
	at com.isomorphic.servlet.CompressionFilter.doFilter(CompressionFilter.java:259)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:324)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
	at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
	at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
	at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
	at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
	at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)

No new users are inserted into my table. The cause of the error is: relation "users_id" does not exist, which makes sense because there IS NO users_id table or sequence in my Postgres database.

It appears that the built-in SQL datasource is 'assuming' that the sequence name is "users_id" when in postgres it is actually "users_id_seq".

Is there any way to configure or tell the datasource what the primary key id generating sequence name is?

If I manually change my Postgres "users_id_seq" to "users_id" then my application DOES insert a new user. However, this is not ideal since I'm using an ORM to auto-generate my Postgres tables and naming sequences with _seq is a Postgres standard practice.

Thanks.

[Isomorphic]

See DataSourceField.sequenceName.

[chimpeenuts]

Isomorphic to the rescue again!

Thanks. Solved my issue. I spent a lot of time looking in the DataSource API but didn't think to look on the DataSourceField API for a setter method.

Thanks for the timely response!