How to use H2 on Google App Engine?

[Jerzy]

How to set up H2 in-memory database in GAE and perform ACID opertions
using SQL in my app? Could you provide some tutorial with examples?
Which SQL commands are supported in GAE?

Thanks in advance!

[Thomas Mueller]

Hi,

> How to set up H2 in-memory database in GAE and perform ACID opertions
> using SQL in my app?

The problem with ACID and in-memory databases is that in-memory
database are not persisted, so you can't have durability.

> Could you provide some tutorial with examples?

See the source code, for example:
http://code.google.com/p/h2database/source/browse/#svn/trunk/h2/src/test/org/h2/samples%3Fstate%3Dclosed

> Which SQL commands are supported in GAE?

All statements, except those that require a file system.

Regards,
Thomas

[Jerzy]

> The problem with ACID and in-memory databases is that in-memory
> database are not persisted, so you can't have durability.

Is there a chance that H2 will be persisted on GAE?

[McKinley]

> Is there a chance that H2 will be persisted on GAE?

If someone implements all the Java IO classes and methods required
using the App Engine Datastore rather than a real filesystem then it
can happen. It shouldn't be too hard but I have no idea what the
overhead would be like of using Datastore tables and records as
essentially a block device. All the readers and writers in the Java
IO classes can just move on to the next Datastore record to get data
or more space as new bytes are written. Chaining up the sequence of
all those records and associating them with "file" and "directory"
names is the only tricky part. That and keeping the "files"
unfragmented which I guess might not be too hard.

Maybe there are some esoteric userspace file systems from projects
like FUSE or Linux in general that can reveal the proper algorithms
for this type of storage wrapper.

Regards,

McKinley

[McKinley]

> If someone implements all the Java IO classes and methods required
> using the App Engine Datastore rather than a real filesystem then it
> can happen.

Someone has done just what I described.

http://gaevfs.appspot.com/

When I looked weeks ago it didn't exist yet but checking again today I
see a new project.

Good luck,

McKinley

[Vince Bonfanti]

Hi,

I started an experimental project last week to modify H2 to use the
GAE datastore as a virtual file system as you described (I'm the
author of the GaeVFS project you referenced). So far I have basic
features working--CREATE, INSERT, SELECT--on very small data sets in
my development environment. I think the really tricky parts are going
to be: (1) getting it to work properly in a distributed environment
with multiple GAE application instances accessing the same database;
and, (2) getting it to perform reasonably well with larger data sets.

As soon as I have something worth sharing I'll post a message to this
group describing what I've done for feedback and advice. I guess my
first question is: Is anyone interested in this?

Vince

On Jun 17, 7:55 am, McKinley <mckinley1...@gmail.com> wrote:
> > If someone implements all the Java IO classes and methods required

> > using theAppEngineDatastore rather than a real filesystem then it

[McKinley]

> I guess my first question is: Is anyone interested in this?

I am very interested and I would assume the original poster is looking
for just such a thing. If you can make this work and apply it back to
your general file system classes to be able to store and randomly
access data in files greater than 1 MB on App Engine then I think it
can only be a good thing for uses beyond H2.

These type of file systems are likely to become more common and the
knowledge learned could be applied to open source projects that seek
to setup up distributed file systems.

Thanks,

McKinley

[Thomas Mueller]

Hi,

> (1) getting it to work properly in a distributed environment
> with multiple GAE application instances accessing the same database;

That will be hard, and in some cases it's the wrong solution... I
guess a more scalable solution is to use 'linked tables' that are
backed by the Google DataStore.

But having a stable virtual file system backed by the Google DataStore
will help others as well, not only H2.

Files greater than 1 MB is the most important missing piece I think.

Some kind of file locking support would be great. Currently H2 doesn't
use file locking to ensure the database is only opened in one process,
instead it uses it's own 'cooperative file locking' protocol. But
regular file locking is relatively easy to add (in the class
FileLock).

> Is anyone interested in this?

I'm interested, and I'm sure others are interested as well.

Regards,
Thomas

[Vince Bonfanti]

I've just released a new version of GaeVFS (version 0.3) that supports
files greater than 1 MB and also adds support for random read/write
file access (both of these were added for H2). I've also posted the
modifications I've made so far to H2 to get it running on GaeVFS:

http://code.google.com/p/gaevfs/wiki/H2GAE

My plans are to tackle the following issues next (in order):

1) Implement a distributed file locking mechanism for GaeVFS that
works across multiple JVM instances; implement exclusive write access
to files across multiple JVMs. (I'll look at the H2 FileLock class as
you suggest, but would like a general solution for GaeVFS that isn't
just for H2).

2) Create an H2 WriterThread implementation based on GAE Task Queues
(as soon as Google provides support for this in Java).

3) Investigate what sort of caching might make sense for GaeVFS/H2
based on the GAE memcache API.

Any feedback on what I've implemented so far, or advice on how I
should proceed would be welcomed.

Vince

[Thomas Mueller]

Hi,

> I've just released a new version of GaeVFS (version 0.3) that supports
> files greater than 1 MB and also adds support for random read/write
> file access (both of these were added for H2).

That's great!

>  1) Implement a distributed file locking mechanism for GaeVFS that
> works across multiple JVM instances; implement exclusive write access
> to files across multiple JVMs. (I'll look at the H2 FileLock class as
> you suggest, but would like a general solution for GaeVFS that isn't
> just for H2).

H2 currently uses a special 'cooperative locking mechanism', probably
it doesn't make sense if you try to emulate this. Instead, I guess it
would make more sense to support just 'lock' and 'unlock', in the
spirit of:

http://java.sun.com/javase/6/docs/api/java/nio/channels/FileChannel.html#tryLock()
http://java.sun.com/javase/6/docs/api/java/nio/channels/FileLock.html#release()

This can then be adapted to H2.

>  2) Create an H2 WriterThread implementation based on GAE Task Queues
> (as soon as Google provides support for this in Java).

I didn't know about this... interesting. I would wait to implement
this until Google supports it.

>  3) Investigate what sort of caching might make sense for GaeVFS/H2
> based on the GAE memcache API.

Maybe the file system could be made faster if the last few write
operations are first only written to the memcache API? Not just for
H2, but I guess many applications that use a file system could benefit
from a 'write behind cache'. Or maybe first cache writes in the local
memory, after some delay apply them in the memcache, and still later
apply them in the data store. Many applications don't require that the
changes are written to disk immediately. The biggest benefit would
probably be for application that don't buffer writes.

Regards,
Thomas

[Marek Gregor]

I am just curious about usage of H2 on GAE, as memory-only database
with keep the content as long as the virtual machine is alive
(DB_CLOSE_DELAY=-1).
Database will be small approx 10MB with requirement to have top
performance.

On daily manner I would like to do the backup of complete database to
external store which is on my local computer. So there are question:

1) Is it possible to access this database on GAE remotely using TCP/IP
or SSL/TLS (An example database URL is: jdbc:h2:tcp://localhost/
mem:db1) ?
2) If not is it possible to generate backup (like by SQL commands
BACKUP, SCRIPT) and not to store it on local file system but sent it
e.g. like backup-servlet response ?
3) Last possible way I thing will work is that I program the backup
manually querying every table as a response of backup-servlet.

4) I have I fundamental question if H2 can be runned as memory-only
database on GAE, I do not know if GAE is not running my application as
cluster on several JVM's so then H2 in memory-only mode is not
functional because it can be used only on the same JVM/class loader
(mentioned in http://www.h2database.com/html/features.html#memory_only_databases:
Accessing the same database in this way only works within the same
virtual machine and class loader environment.)

thanks for reponse: can save me time to start work on something which
is not possible

[Vince Bonfanti]

On Tue, Aug 4, 2009 at 5:38 AM, Marek Gregor<marek....@gmail.com> wrote:
>
> I am just curious about usage of H2 on GAE, as memory-only database
> with keep the content as long as the virtual machine is alive
> (DB_CLOSE_DELAY=-1).
> Database will be small approx 10MB with requirement to have top
> performance.
>
> On daily manner I would like to do the backup of complete database to
> external store which is on my local computer. So there are question:
>
> 1) Is it possible to access this database on GAE remotely using TCP/IP
> or SSL/TLS (An example database URL is: jdbc:h2:tcp://localhost/
> mem:db1) ?

No. GAE does not allow applications to open network ports, nor does it
allow applications to create background threads. Either of these
limitations would prevent H2 from running in Server Mode.

> 2) If not is it possible to generate backup (like by SQL commands
> BACKUP, SCRIPT) and not to store it on local file system but sent it
> e.g. like backup-servlet response ?
> 3) Last possible way I thing will work is that I program the backup
> manually querying every table as a response of backup-servlet.
>
> 4) I have I fundamental question if H2 can be runned as memory-only
> database on GAE, I do not know if GAE is not running my application as
> cluster on several JVM's so then H2 in memory-only mode is not
> functional because it can be used only on the same JVM/class loader
> (mentioned in http://www.h2database.com/html/features.html#memory_only_databases:
> Accessing the same database in this way only works within the same
> virtual machine and class loader environment.)

That is correct. GAE may launch several instances of your web
application, each instance running within a separate JVM. However, you
have no way of knowing (and no control over) how many instances are
running at any given time. If you use H2 in memory-only mode, each
instance of your web application--in its separate JVM--will have a
separate in-memory copy of H2. If your database is read-only then this
will work, but as soon as you write to the database your separate web
application instances will be using different data.

Our goal for the H2-GAE project is to get H2 working properly within
the GAE distributed environment using the GaeVFS virtual file system:

http://code.google.com/p/gaevfs/wiki/H2GAE

We still have a lot of work to do before we can claim success, but the
results so far are encouraging.