SQLite interface

SQLite interface

[Florian Weimer]

Would a patch that exposes the SQLite C API at the Lisp level be
acceptable for inclusion in Emacs, or is this not a good idea because
SQLite is public domain software outside of the GNU project?

I would like to use SQLite to speed up updating the overview files in
the nnml backend for Gnus.  I have some folders where these files are
several hundred megabytes large, and they are rewritten from scratch
each time new mail is received.  So far, I've thrown hardware at the
problem, but that's becoming less and less cost-effective.

Re: SQLite interface

[joakim]

Florian Weimer <fw@deneb.enyo.de> writes:

> Would a patch that exposes the SQLite C API at the Lisp level be
> acceptable for inclusion in Emacs, or is this not a good idea because
> SQLite is public domain software outside of the GNU project?
>
> I would like to use SQLite to speed up updating the overview files in
> the nnml backend for Gnus.  I have some folders where these files are
> several hundred megabytes large, and they are rewritten from scratch
> each time new mail is received.  So far, I've thrown hardware at the
> problem, but that's becoming less and less cost-effective.
>

Another aproach is to work with a FFI interface in Emacs to expose
libraries such as sqlite.

There are however various opinions on FFI interfaces for emacs.

My personal opinion is that GObject introspection is a nice aproach for
Emacs to gain FFI abilities.

I have a preliminary patch for GObject introspection support in the
Emacs Xwidget branch.

There is a Gobject wrapper for sqlite https://code.google.com/p/sqlheavy/

If you are interested in exploring this avenue, I will be happy to assist.


-- 
Joakim Verona

Re: SQLite interface

[Florian Weimer]

> My personal opinion is that GObject introspection is a nice aproach for
> Emacs to gain FFI abilities.

Interesting.  Do you plan to add types to Emacs Lisp which currently
exist only in GObject as fundamental types?

Re: SQLite interface

[Stefan Monnier]

> Would a patch that exposes the SQLite C API at the Lisp level be
> acceptable for inclusion in Emacs,

Very probably, yes.  But not if it doesn't go through an FFI (which we
don't have yet).


        Stefan

Re: SQLite interface

[David Kastrup]

Stefan Monnier <monnier@iro.umontreal.ca> writes:

>> Would a patch that exposes the SQLite C API at the Lisp level be
>> acceptable for inclusion in Emacs,
>
> Very probably, yes.  But not if it doesn't go through an FFI (which we
> don't have yet).

Wouldn't that basically fall off the tree for an Eamcs running over
GUILE?

-- 
David Kastrup

Re: SQLite interface

[joakim]

Florian Weimer <fw@deneb.enyo.de> writes:

>> My personal opinion is that GObject introspection is a nice aproach for
>> Emacs to gain FFI abilities.
>
> Interesting.  Do you plan to add types to Emacs Lisp which currently
> exist only in GObject as fundamental types?

There is some preliminary marshalling code in the branch.


-- 
Joakim Verona

Re: SQLite interface

[Stefan Monnier]

> Wouldn't that basically fall off the tree for an Eamcs running over
> GUILE?

Probably,


        Stefan

Re: SQLite interface

[Ted Zlatanov]

On Thu, 13 Mar 2014 11:08:15 +0100 Florian Weimer <fw@deneb.enyo.de> wrote: 

FW> Would a patch that exposes the SQLite C API at the Lisp level be
FW> acceptable for inclusion in Emacs, or is this not a good idea because
FW> SQLite is public domain software outside of the GNU project?

FW> I would like to use SQLite to speed up updating the overview files in
FW> the nnml backend for Gnus.  I have some folders where these files are
FW> several hundred megabytes large, and they are rewritten from scratch
FW> each time new mail is received.  So far, I've thrown hardware at the
FW> problem, but that's becoming less and less cost-effective.

Given that any possible FFI interface is after the freeze and probably
will take months to get into a released Emacs, can you consider writing
this with a shell backend for now and use the C API opportunistically?
I don't know the details about SQLite's accessibility, but I believe it
can do at least some useful things non-interactively over STDOUT/STDIN.

Ted

Re: SQLite interface

[Florian Weimer]

* Ted Zlatanov:

> Given that any possible FFI interface is after the freeze and probably
> will take months to get into a released Emacs, can you consider writing
> this with a shell backend for now and use the C API opportunistically?

What does this mean?  Do you want me to write a stub for the SQLite
API that runs as a subprocess and exposes that over some sort of RPC
interface?

> I don't know the details about SQLite's accessibility, but I believe it
> can do at least some useful things non-interactively over STDOUT/STDIN.

Oh, you want me to use the existing sqlite3 command line client?
That's a non-starter.  It is not binary-transparent, and it does not
separated support query parameters.  My planned use case (Gnus)
handles untrusted data received over the network, so this approach
does not seem such a good idea to me.

Re: SQLite interface

[Kenneth Raeburn]

On Mar 13, 2014, at 16:31, Florian Weimer <fw@deneb.enyo.de> wrote:

> * Ted Zlatanov:
> 
>> Given that any possible FFI interface is after the freeze and probably
>> will take months to get into a released Emacs, can you consider writing
>> this with a shell backend for now and use the C API opportunistically?
> 
> What does this mean?  Do you want me to write a stub for the SQLite
> API that runs as a subprocess and exposes that over some sort of RPC
> interface?

A Perl stub using DBI and DBD::SQLite, with communication in JSON or YAML or some such (or s-expressions, if Perl module Data::SExpression is fully Emacs compatible even for untrusted and binary or multibyte-text data), may not be all that much work.

>> I don't know the details about SQLite's accessibility, but I believe it
>> can do at least some useful things non-interactively over STDOUT/STDIN.
> 
> Oh, you want me to use the existing sqlite3 command line client?
> That's a non-starter.  It is not binary-transparent, and it does not
> separated support query parameters.  My planned use case (Gnus)
> handles untrusted data received over the network, so this approach
> does not seem such a good idea to me.

I suppose using SELECT QUOTE(CAST (column AS BLOB)) ... and INSERT ... CAST(X'010203' AS TEXT), passing values as hexadecimal, would be horribly intrusive when writing the queries and processing the results, but is there more than that needed to use the command line client?

Ken

Re: SQLite interface

[Ted Zlatanov]

On Thu, 13 Mar 2014 21:31:23 +0100 Florian Weimer <fw@deneb.enyo.de> wrote: 

FW> Oh, you want me to use the existing sqlite3 command line client?
FW> That's a non-starter.  It is not binary-transparent, and it does not
FW> separated support query parameters.  My planned use case (Gnus)
FW> handles untrusted data received over the network, so this approach
FW> does not seem such a good idea to me.

OK, thanks for explaining.  I'd like to use SQLite with gnus-registry,
so I'm excited you're pursuing this.

Ted

Re: SQLite interface

[Florian Weimer]

* Kenneth Raeburn:

> I suppose using SELECT QUOTE(CAST (column AS BLOB)) ... and INSERT
> ... CAST(X'010203' AS TEXT), passing values as hexadecimal, would be
> horribly intrusive when writing the queries and processing the
> results, but is there more than that needed to use the command line
> client?

It would require parsing the queries and rewriting them.

I don't know yet how horrid the Emacs C internals are, but I'm pretty
sure it cannot be that bad, and it's easier to write something in C
now (and throw it away once the FFI arrives) than to implement two
sides of an IPC mechanism and all the query and result set massaging.

Re: SQLite interface

[Ted Zlatanov]

On Sat, 15 Mar 2014 16:00:52 +0100 Florian Weimer <fw@deneb.enyo.de> wrote: 

FW> * Kenneth Raeburn:
>> I suppose using SELECT QUOTE(CAST (column AS BLOB)) ... and INSERT
>> ... CAST(X'010203' AS TEXT), passing values as hexadecimal, would be
>> horribly intrusive when writing the queries and processing the
>> results, but is there more than that needed to use the command line
>> client?

FW> It would require parsing the queries and rewriting them.

FW> I don't know yet how horrid the Emacs C internals are, but I'm pretty
FW> sure it cannot be that bad, and it's easier to write something in C
FW> now (and throw it away once the FFI arrives) than to implement two
FW> sides of an IPC mechanism and all the query and result set massaging.

Yup.  Having done exactly this sort of query rewriting for an old
database, I agree it's not worth the pain.

Ted