Re: [Caml-list] SQL engine in OCaml with client side cache

[Gerd Stolpmann <info@gerd-stolpmann.de>]

Am Sonntag, den 29.01.2012, 10:56 +0100 schrieb Diego Olivier Fernandez
Pons:
> Caml-list,
> 
> I need an SQL server that a web-client can query and send the results to
> some JavaScript graphic package.
> My problem is that the bandwidth and server power are limited and paid per
> usage.
> 
> On the other hand, the data I am working with has good properties
> - read only
> - client always looking at the same subset of data from "different angles"
> - easy to compute a subset of data the client will work on
> 
> For instance the database contains 5 years of sales of a company in all
> their stores. Some clients will want to investigate all the products of a
> given store, other will want to compare a single product on all the stores,
> others the sales evolution per department per year, etc.
> 
> Therefore I thought I could add a "cache" on the client side, meaning an
> in-memory SQL database that would receive a big block of data from the
> server and work on it till the client writes a query that needs some data
> that is not available locally in which case it would request it from the
> server, etc.

I think this is not possible. SQL always needs access to the complete
table for executing queries (including the complete indexes). You would
have to restrict yourself to a subset of SQL where it is meaningful to
define caching. For example, a viable path would be to use
object-relational mappings (where range queries are usually not
possible). But this is not SQL anymore...

> I haven't found anything like that ready-to-use, so I was considering
> reengineering existing OCaml code (database + web) and maybe compile it to
> JavaScript. I have control on the client so I can afford installing an
> OCaml runtime if needed albeit impractical though.

As you are read-only: what about creating a dump of the whole SQL
tables, and to import it into a local SQL engine like sqlite? (There are
Ocaml bindings.) I don't know whether you can change the db schema, but
ideally it would support to make delta dumps (i.e. get all changes since
the last dump), for example by including time stamps in the rows.

Another option would be to use the replication feature of many SQL
servers. Basically, you would have to grab the logs (journals) of the
database, and to import them locally into an SQL server of the same
type. The keyword to look for is "log shipping". The nice thing is that
you automatically get a delta-based replication.

> Does anyone has an SQL engine written in OCaml ? I can only find bindings.

This is a lot of work. I have an on-disk btree implementation, if you
are interested (this is in the plasma download,
projects.camlcity.org/projects/plasma.html, look into src/pkv). It is
written for PlasmaFS, though, but a port to a local filesystem should
not be too difficult.

Gerd

> Also, has anyone tested the OCaml -> JavaScript on projects with
> significant computation work ?
> 
>         Diego Olivier
>