TouchDB J2SE/JDBC

[Hank]

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.

[Marty Schoch]

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

[Hank]

I yet have to closely examine the use of Looper/Handler in TouchDB but it should match what Executors/Callables do. (java.util.concurrency)

On Saturday, 19 May 2012 23:01:57 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

[Hank]

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

[Jens Alfke]

On Jun 1, 2012, at 2:03 AM, Hank wrote:

- 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.

The Objective-C implementation already uses SQLite savepoints instead of begin/end transaction; Marty may not have ported this over yet (or it may not be supported in the Android SQLite interface he's using.)

—Jens

[Marty Schoch]

Thanks for your interest in this. Here is my perspective on this topic.

1. What we have already works on Android. At least once in your
email you described "when backporting to Android" and I'd rather not
look at this way. Instead I would propose we break this into several
smaller taks. Each of which could be accomplished without breaking
what we already have.

2. I have no opinion on H2. What I would like to see is an
abstraction in TouchDB for the persistence layer. Then have a module
which maps the existing Android persistence to that abstraction. Then
have a JDBC implementation (possibly somewhat abstract) which allows
for specific database modules to plug-in where necessary. I think
this is the biggest difference from what you proposed below. I
wouldn't try to re-use much code between what we have now for Android
persistance and the new JDBC persistance. I would propose we have a
persistance API (interface), with multiple implementations. Obviously
the initial JDBC implementation would borrow much of its logic from
the Android one, but to me it seems like theres enough differences to
keep them separate.

That being said here is how I would break down the tasks.

1. Convert all logging calls to sl4j. This can probably be done with
a clever regular expression search and replace.

2. Reevaluate concurrency implementation in TouchDB. If it can be
written once in a way that works everywhere, without sacrificing
anything that would be my first choice. If not, introduce some
abstraction so that we can plug-in android/non-android
implementations. I need to do more research here before we make any
changes. I already think the way I did some of it in TouchDB wasn't
right to begin with (too many handler threads)

3. Introduce a persistence abstraction to TouchDB. This will take
some time to iron out. It's basically most of TDDatabase and part of
TDView, but there is non-persistence logic in these classes to that
must be broken out. The output of this step should be an interface.
We'll probably also need to introduce some TDServer factory that
instantiates the right implementation for you.

4. Once we have #3, create an Android implementation of #3 and rewire
things to go through this. This should be done in a separate module.
When this step is complete users would need a touchdb.jar and a
touchdb-android.jar.

5. Introduce a JDBC implementation (again possibly abstract). This
would allow non-android users to use touchdb.jar and touchdb-jdbc.jar

6. Introduce concrete implementations for other databases. Again, it
might be touchdb.jar, touchdb-jdbc.jar and touchdb-h2.jar

We have a reasonable number of unit tests right now, and I think we
take an approach like this we can get to where you want to go. If I
get a monster patch that mixes all these things together I may never
find the time to properly review it. And if I get patches that break
things for Android I'm not going to push them through.

Do we have anyone other people in the group interested in these changes?

marty

[Marty Schoch]

I actually used savepoints for a period of time but they were
problematic. The Android API doesn't give direct access to them. But
you can still call SAVEPOINT ... from execSQL(). The problem was
several of the other methods in the Android API would start their own
transactions (using the transaction API and not the SAVEPOINT API).
And unfortunately you cannot mix the two. So, I had to back out this
change.

marty

[Hank]

Sounds good, at least for abstracting away the differences between Android and JDBC so that you can swap JDBC for Android just by swapping out a jar. I am not convinced of the necessity of supporting multiple JDBC databases.

Now, personally I need this soon-ish (weeks, not months) for a prototype/proof-of-concept though so I might still go ahead and fork it into a just-JDBC/H2 version for the moment, all the while maintaining overall similarity to the evolving Android version so that changes in the Android branch can be easily merged in.

Rgd. "when backporting to Android", I could also imagine that porting to JDBC first and then back to Android would help finding the right interface for the JDBC/Android abstraction. Android is the more exotic/quirky environment in the scheme of things so its influence should be minimized.