Google Groups

Re: TouchDB J2SE/JDBC


Hank Jun 1, 2012 2:03 AM
Posted in group: Couchbase Mobile
From reviewing the code I see no theoretical obstacles to a J2SE/JDBC port:

Assuming H2 (h2database.com) is used via JDBC, the following concerns remain:

H2 vs. SQLite:
- in the SQL statement that creates the tables foreign keys have to be declared with the "FOREIGN_KEY" keyword 
- the PRAGMA "foreign_key" doesn't exist, this can be safely ignored as foreign keys are always on
- the PRAGMAs user_version doesn't exist, this will have to be mimicked using a single-row table
- possibly more changes w.r.t. SQL grammar in schema creation (?)
- the magic INTEGER PRIMARY KEY that becomes an auto-generated key and also row ID (see under "SQL data constraints" here) is an SQLite quirk. it will have to be explicitly declared as auto-generating using the AUTOINCREMENT keyword on table creation and queried using cursor.getInt(0) instead of getRowId().
- conflict resolution mechanisms à la INSERT OR REPLACE are another SQLite quirk, will have to be replaced with DELETE and subsequent INSERT in a transaction
- explicit, nested transactions via BEGIN TRANSACTION don't exist in H2/JDBC and have to be emulated by first switching off auto-commit, then using savepoints and commits or rollbacks to savepoints.
- VACCUUM: no online compacting in H2, however freed space is reused, details here
- JSON Collation: H2 gets its collators from the java.text.Collator.getInstance(String) factory method which as of J2SE 6 can be extended via the java.text.spi.CollatorProvider extension mechanism to plug in a collator that corresponds to the 'locale' "JSON". This collator is quickly written in ~100 lines of code on top of Jackson for low-level parsing, assuming the CollationKey.toByteArray() optimization method isn't needed. From grepping the source code, H2 doesn't seem to be calling CollationKey.toByteArray(), not explicitly that is.

JDBC database API vs. android.database:
- The JDBC "Connection" class corresponds more or less to android.database.sqlite.SQLiteDatabase and JDBC's ResultSet to android.database.cursor.Cursor, however JDBC semantics introduce the "Statement" as another step in running queries: database creates statement, statement creates result set and at the very least the statement has to be closed which will close the result set too. To add insult to injury, the close method on either can throw an exception. This unnecessary complexity might as well be hidden via a façade design pattern that presents statement and corresponding result set as one object. If the façade exposes methods named along the android naming scheme, e.g. Cursor.next() instead of ResultSet.moveNext() or Cursor.getBlob(int) instead of ResultSet.getBytes(int) then it could be dropped in place in the existing TouchDB Android code with next to no code change.
- Android's convenience methods like SQLiteDatabase.rawQuery, .insert, .update, .delete and .getVersion don't exist in JDBC, they will have to be implemented using the corresponding SQL statements. These implementations might as well be hosted by above façade. android.content.ContentValues can be substituted by a Map<String, Object>.

other Android APIs:
- Log can easily be replaced by slf4j (with Android plug-in when backporting to Android) and whatever logging framework desired.
- Handler/Looper tasks are fulfilled by java.util.Timer or a java.concurrency.ScheduledExecutorService created by Executors.newSingleThreadScheduledExecutor(). The latter allows specifying a ThreadFactory which allows setting thread name.

When considering databases other than H2, consider:
- do they allow multi-threaded access to connections as this is not required by JDBC
- custom collations, how? (Derby uses java.text.Collator.getInstance(), too, so that should work)
- otherwise see above concerns for SQLite vs. H2.

Again this is all theory, in practice it may yet all come apart.

Comments?


On Saturday, May 19, 2012 11:01:57 PM UTC+10, Marty Schoch wrote:
Contributions on this are certainly welcome.  It would be great if we could have a discussion around the replacement/alternative to Looper/Handler since this is used quite a lot to run code at a later time.

Sent from my iPhone

On May 19, 2012, at 8:47 AM, Hank wrote:

For Java apps on J2SE, not Android, I see a use case for a J2SE/JDBC based TouchDB. After all a smallish Java app that syncs to the cloud like e.g. a contacts app on your desktop/laptop needn't have a whole CouchDB installation.

These github issues/comments seem to be asking for the same thing: issue 24 / issue 23.

When based on JDBC, any of a number of embedded databases can be plugged in, some of which are even pure Java like H2.

The JDBC version could even be backported to Android if "SQLDroid" holds what it promises.

The necessary changes as laid out by Marty in github issue 24 above don't seem too onerous and I have a need for this soon-ish, so I might get this done unless someone does this sooner.