Provide convenient access to local database using SQL-annotated interface methods.

[GoogleCodeExporter]

Currently, querying the local database is quite hard and even less 
straightforward than JDBC. I 
propose to introduce a new method to query a local database, using annotated 
interfaces and a 
custom GWT generator.

The annotated interface might look like this (the example is 'ported' from 
http://code.google.com/p/gwt-mobile-webkit/wiki/DatabaseApi):

@DatabaseService(name="ClckCnt", version="1.0", description="Click Counter", 
maxsize=10000)
public interface MyDatabaseService {

  @SQL("SELECT clicked FROM clickcount")
  void getClickCounts(StatementCallback<ClickRow> callback);

  @SQL("INSERT INTO clickcount (clicked) VALUES ({timestamp})")
  void insertClick(long timestamp, StatementCallback<Void> callback);
}

The interface should be used much like we use an RPC service:

MyDatabaseService service = GWT.create(MyDatabaseService.class);

and the service methods can be invoked like:

service.getClickCounts(new StatementCallback<ClickRow>() {
  public boolean onFailure(SQLTransaction transaction, SQLError error) {
    return false;  // don't roll back
  }
  public void onSuccess(SQLTransaction transaction, SQLResultSet<ClickRow> resultSet) {
    clickedData.clear();
    for (ClickRow row : resultSet.getRows()) {
      clickedData.add(new Label("Clicked on " + row.getClicked()));
    }
  }
});

Thoughts?

Original issue reported on code.google.com by bguijt on 22 Oct 2009 at 8:26

[GoogleCodeExporter]

Thinking about it, the callback interface still exposes 'too much' of the 
Database API innards. If the query is 
already abstracted away as a service, at least the resultset should be exposed 
like a service would do.

e.g.:

  /** Returns a *collection* of items */
  @SQL("SELECT clicked FROM clickcount")
  void getClickCounts(DatabaseServiceListCallback<ClickRow> callback);

  /** Returns a *scalar* just one item */
  @SQL("SELECT count(*) FROM clickcount")
  void getClickCountSize(DatabaseServiceScalarCallback<Integer> callback);

  /** Returns *nothing* */
  @SQL("INSERT INTO clickcount (clicked) VALUES ({timestamp})")
  void insertClick(long timestamp, DatabaseServiceVoidCallback callback);

calls:

// Plural:
service.getClickCounts(new DatabaseServiceListCallback<ClickRow>() {
  public boolean onFailure(SQLError error) {
    return false;
  }
  public void onSuccess(List<ClickRow> result) {
    // 'result' is a List of ClickRow items. 0 or more.
  }
});

// Scalar:
service.getClickCountSize(new DatabaseServiceScalarCallback<Integer>() {
  public boolean onFailure(SQLError error) {
    return false;
  }
  public void onSuccess(Integer result) {
    // 'result' is a the number of rows in the clickcount table
  }
});

// No result:
service.insertClick(long timestamp, new DatabaseServiceVoidCallback() {
  public boolean onFailure(SQLError error) {
    return false;
  }
  public void onSuccess() {
    // No result value
  }
});

Original comment by bguijt on 22 Oct 2009 at 8:50

[GoogleCodeExporter]

This looks great! :)

I can't think of any improvements to make to your second suggestion; reusing 
standard
collections for rows (such as List<ClickRow>) looks great! I'm also liking the
list/scalar/void callbacks too. Very neat

For doing arbitrary queries & SQL statements with variable parameters I guess 
we'll
need some kind of SQL statement builder as an alternative to these annotation 
based
methods. They can certainly use the same callback interfaces to process any 
results -
but would need some methods for creating SQL and/or passing in parameters.

e.g. something vaguely like...

{{{
DatabaseConnection connection = GWT.create(DatabaseConnection.class);
Statement statement = connection.createStatement("select * from foo where x = ? 
and y
= ?", valueX, valueY);
statement.execute(new DatabaseServiceListCallback<ClickRow>() {...}) 
}}}

I guess processing an SQL string with optional parameters and a callback could 
be one
method - I just thought using a separate class & method to execute a statement 
might
make it a bit simpler. 

e.g. we might want to build a Criteria object for creating dynamic SQL queries
(adding extra conditions dynamically so the WHERE clause is generated at run 
time
based on user input or whatever) which can generate a similar Statement object. 
(See
Hibernate for an example Criteria API).

Original comment by james.st...@gmail.com on 22 Oct 2009 at 12:09

[GoogleCodeExporter]

A Criteria API is absolutely an interesting perspective to perform SQL queries. 
Perhaps JPA is a good starting 
point for that.

I think I'll move forward and implement the suggested DatabaseService as a 
start, see how that works out.

Original comment by bguijt on 22 Oct 2009 at 7:47

• Changed state: Accepted

[GoogleCodeExporter]

Made some progress with the DataServiceGenerator. For example,

@Connection(name = "ClckCnt", version = "1.0", description = "Click Counter", 
maxsize = 10000)
public interface ClickCountDataService extends DataService {

  @SQL({
    "INSERT INTO clickcount (clicked) VALUES ({when.getTime()})",
    "SELECT clicked FROM clickcount"
    })
  void insertClick(Date when, ListCallback<ClickRow> callback);

  @SQL("SELECT count(*) FROM clickcount")
  void getClickCount(ScalarCallback<Integer> callback);
}

is used as follows:

  ClickCountDataService dbService = GWT.create(ClickCountDataService.class);

  dbService.insertClick(new Date(), new ListCallback<ClickRow>() {
    public boolean onFailure(SQLError error) {
      return false;
    }
    public void onSuccess(List<ClickRow> result) {
      // ...
    }
  });

At least the generated code compiles correctly: Smoketest succeeded :-)

Original comment by bguijt on 24 Oct 2009 at 12:33

• Changed state: Started

[GoogleCodeExporter]

See http://code.google.com/p/gwt-mobile-webkit/wiki/DataServiceAPIDesign for 
design.

Original comment by bguijt on 24 Oct 2009 at 10:40

[GoogleCodeExporter]

This issue was closed by revision r160.

Original comment by bguijt on 25 Oct 2009 at 10:43

• Changed state: Fixed