ORM and Databases

[Mike Walker]

Inspired by Malte's recent GoogleGears and HTML5 Database work, I've been
thinking recently about the idea of a JSON-to-RDBMS bridge. Apparently
there's
already a production-quality JSON bridge for MySQL written (and
open-sourced!)
by the New York Times, called "DBSlayer." [1]

Recently, Alvaro Ortega[2], creator of the Cherokee Web Server[3], has
created
another implementation of DBSlayer[4] which gets even better
performance[5]. On
his hardware it can handle ~4000reqs/sec compared to DBSlayer's
~1000reqs/sec.

I'm considering adding support to Joose's ORM module for this
reimplementation
of DBSlayer, but I've got some concerns:

- It doesn't seem to support transactions, though the source of the module
itself[6] looks straightforward.

- Joose's ORM was originally targeted to GoogleGears, but now supports the
HTML5 Database API. What would be the best way to add support for a
third, or
perhaps more, databases?

I'd appreciate any thoughts you all might have, and thank you for your
work on
Joose :)

Cheers, Mike

1. DBSlayer
http://code.nytimes.com/projects/dbslayer/

2. Alvaro Ortega
http://www.alobbs.com/

3. Cherokee Web Server - A high-performance web server in the vein of
lighttpd,
nginx, etc.
http://www.cherokee-project.com/

4. Cherokee DBSlayer
http://www.cherokee-project.com/doc/modules_handlers_dbslayer.html

5. Cherokee DBSlayer vs. DBSlayer

http://www.alobbs.com/1344/MySQL_asynchronous_balancing_with_HTTP_JSON.html

6. Cherokee DBSlayer Module Source Code

http://svn.cherokee-project.com/browser/cherokee/trunk/cherokee/handler_dbslayer.c

[Malte Ubl]

Hey Mike,

both databases that are currently supported are client sided, so it
will be some work to get this to work with a server sided database.
They are also both actually sqlite based databases, so there was no
need to abstract out the database driver.

The primary difference between the interfaces of the gears and html5
databases is that the latter has an asynchronous interface which leads
to a kind of awkward programming model. For every db action a callback
is invoked whenever the action succeeds or fails. This should actually
fare quite well with adapting to a server sided db because one could
easily do async http request for every db invoke.

With respect to transactions: Did you look at examples/ORM/async/ ....
? Those are the sources for the ORM with html5 support and there is
transaction support for both db backends in there (Actually html5 does
not support autocommit).

Bye
Malte

[Mike Walker]

Hi Malte,

I meant to say that Cherokee DBSlayer doesn't seem to support transactions, though I think I was half-wrong on that. Looking through source code for Cherokee DBSlayer[1], it seems like a light wrapper around the MySQL C API, primarily a call to mysql_query(), with a mysql_rollback() in the error handler. So it should be OK to just send the queries "START TRANSACTION" and "COMMIT", but since each DBSlayer instance has one connection to the database, that means every query to a DBSlayer would be on a shared connection, so transactions client-side, with more than one transaction at a time, wouldn't work right, unless the client sends all the queries for his transaction at once, separated by semi-colons.

For simple SELECTs (that's what it was designed for) DBSlayer seems fantastic. I'm curious what other issues would show up with browser-based applications having direct access into a server-side database. Certainly the only server-side logic (security/data-integrity) would be what's in the database itself. Most database servers have enough granularity in user/permissions/triggers that this shouldn't be prohibitive.

Regarding your first point below, that's what concerned me about implementing it - should DBSlayer be shoved into ORM with even more FOO_COMPATs scattered around, or should there be a defined storage API with implementations for HTML5 Database, GoogleGears, and DBSlayer? Or should there be a local storage API, and a remote storage API? Then how would non-traditional databases like CouchDB fit into that?

It almost seems like you could bootstrap a whole application/website out of just DBSlayer with a clever bookmarklet :D

I wanted to talk to the group before actually writing any code, as I'm still new to Joose.

Cheers,
Mike

1. http://svn.cherokee-project.com/browser/cherokee/trunk/cherokee/handler_dbslayer.c


Malte Ubl wrote:

[Malte Ubl]

Hi Mike,

On Wed, Oct 8, 2008 at 8:10 PM, Mike Walker
<mike-...@napkindrawing.com> wrote:
<snip>

> Regarding your first point below, that's what concerned me about
> implementing it - should DBSlayer be shoved into ORM with even more
> FOO_COMPATs scattered around, or should there be a defined storage API with
> implementations for HTML5 Database, GoogleGears, and DBSlayer? Or should
> there be a local storage API, and a remote storage API? Then how would
> non-traditional databases like CouchDB fit into that?

With concern to the FOO_COMPAT problem, it might not be as bad as you think.

There is important Gears specific code in two regions:
ORM.transaction:
This nastiness is specific to mapping a synchronous transaction system
onto an async API. You won't need this for server sided stuff.

ORM.HTML5*
These classes are an implementation of the HTML5 db API on top of
gears. My preferred strategy for adding further backends would be to
implement the HTML5 db API in terms of a DBSlayer backend. We should
then factor out the Gears and DBSlayer specific code into external
packages that can be loaded on demand. Obviously those packages should
not override window.openDatabase but simply return a function.
The existing HTML5* classes should then move to ORM.Backend.Gears.* or similar.

With respect to transactions, I think it should be possible to create
a client sided transaction queue which would look like this

var transactionQueue = [];

dbSlayerHandle.transaction(function () {
dbSlayerHandle.executeSql('...?', 1);
dbSlayerHandle.executeSql('...? ?', 2, 3)
dbSlayerHandle.executeSql('...')
})

...executeSql: function () {
transactionQueue.push(arguments)
}

...transaction: function (func) {
func()
this.executeOnServer(transactionQueue)
}

Rather then being a global var transactionQueue should, of course, be
an instance var of the transaction.

> It almost seems like you could bootstrap a whole application/website out of
> just DBSlayer with a clever bookmarklet :D

Sure. With respect to security: I think this model is valid in all
situations where you would also use a tradition client-server app
(because it actually is a client-server app) without an application
server.

Bye
Malte