Using a command-line program (sqlite3) as a backend

Using a command-line program (sqlite3) as a backend

[florian]

It seems to me I have a rather basic question:

I would like to use an SQLite database, and be able to query and
modify it from Elisp functions. It seems that nobody has written Elisp
bindings for the SQLite library yet, and I am definitely not smart
enough to do that myself.

So I gather I will have to use SQLite's command-line interface,
sqlite3. I don't see much of a problem in parsing the output (in fact,
I've already written that), but I am wondering about the differences
between running sqlite3 as a synchronous process for every query (in a
one-shot fashion), and starting it up once, as an asynchronous
process, and then having it linger in the background, sending it
commands and parsing the output it returns as needed.

To me, the synchronous method seems more robust, but I am wondering
whether it will scale well (supposing I get fond of using the database
and start to use it for more and more complex things). As to the
asynchronous method, I seem to have heard that I/O via stdin and
stdout is prone to, er, I don't know: hang? lock? I've forgotten, but
I would much appreciate to be warned if that is the case.

(I have tried to find hints in ispell.el (since that seems to be a
similar situation), but only found out that it implements indeed
both.)

Can anybody offer me any advice here? Thanks so much!

Florian

Re: Using a command-line program (sqlite3) as a backend

[Chetan]

florian <lorian@fsavigny.de> writes:

> It seems to me I have a rather basic question:
>
> I would like to use an SQLite database, and be able to query and
> modify it from Elisp functions. It seems that nobody has written Elisp
> bindings for the SQLite library yet, and I am definitely not smart
> enough to do that myself.
>
> So I gather I will have to use SQLite's command-line interface,
> sqlite3. I don't see much of a problem in parsing the output (in fact,
> I've already written that), but I am wondering about the differences
> between running sqlite3 as a synchronous process for every query (in a
> one-shot fashion), and starting it up once, as an asynchronous
> process, and then having it linger in the background, sending it
> commands and parsing the output it returns as needed.
>
> To me, the synchronous method seems more robust, but I am wondering
> whether it will scale well (supposing I get fond of using the database
> and start to use it for more and more complex things). As to the
> asynchronous method, I seem to have heard that I/O via stdin and
> stdout is prone to, er, I don't know: hang? lock? I've forgotten, but
> I would much appreciate to be warned if that is the case.
>
> (I have tried to find hints in ispell.el (since that seems to be a
> similar situation), but only found out that it implements indeed
> both.)
>
> Can anybody offer me any advice here? Thanks so much!
>
> Florian
Doesn't it work from shell running in emacs?

Re: Using a command-line program (sqlite3) as a backend

[Glenn Morris]

florian wrote:

> I would like to use an SQLite database, and be able to query and
> modify it from Elisp functions. It seems that nobody has written Elisp
> bindings for the SQLite library yet...

You don't say anything about having looked at sql.el, distributed with
Emacs for 10 years.

Re: Using a command-line program (sqlite3) as a backend

[florian]

On Mar 24, 10:38 pm, Chetan <Chetan.xs...@xspam.sbcglobal.net> wrote:

> Doesn't it work from shell running in emacs?

I suppose it does, but I want to use it from Elisp functions.

Best, Florian

Re: Using a command-line program (sqlite3) as a backend

[Chetan]

florian <lorian@fsavigny.de> writes:

> On Mar 24, 10:38 pm, Chetan <Chetan.xs...@xspam.sbcglobal.net> wrote:
>
>> Doesn't it work from shell running in emacs?
>
> I suppose it does, but I want to use it from Elisp functions.
>
> Best, Florian

It would be easiest to see if there are any problems working with
stdout, since that was mentioned.
Using it from elisp requires either loading a dynamically linked
library or compiling emacs with sqlite - neither of them are supported
at the moment, but the latter is possible, provided one is OK
with the datatypes as currently supported in emacs-lisp.

--
Chetan

Re: Using a command-line program (sqlite3) as a backend

[florian]

On Mar 25, 2:03 am, Glenn Morris <rgm+n...@stanford.edu> wrote:

> You don't say anything about having looked at sql.el, distributed with
> Emacs for 10 years.

That's true, I didn't mention that. I did have a look, but my
impression was that this is mainly concerned with editing SQL, and
invoking one of several databases to test (or evaluate) the SQL
statements written. This is definitely a useful thing, and might prove
handy later on, but is not quite what I want to do right now. I want
to use an SQLite database in a rather hidden (or let's call it "user-
friendly") way, i.e. write functions that do e.g. queries, but take
care of the SQL themselves.

Nevertheless, I tested the (sql-sqlite ..) function, and it does not
seem like a function that has actually ever been tested: it asks for a
user name, password, and server, none of which makes sense with SQLite
(or, as I suspect, has ever made sense with it). And it does complain
and die: "sqlite3.exe: unknown option: -user=florian". That leaves me
a little suspicious about whether sql.el can serve as a model, let
alone library,  for what I want to do. But I'm open to be taught
otherwise, of course ...

Best regards,
Florian

Re: Using a command-line program (sqlite3) as a backend

[florian]

Thanks for the instantaneous reply ;-) ...

On Mar 26, 12:03 am, Chetan <Chetan.xs...@xspam.sbcglobal.net> wrote:

> It would be easiest to see if there are any problems working with
> stdout, since that was mentioned.

> Using it from elisp requires either loading a dynamically linked
> library or compiling emacs with sqlite - neither of them are supported
> at the moment, but the latter is possible,

Oops. Compiling Emacs with sqlite? What would that be?

> provided one is OK with the datatypes as currently supported in emacs-lisp.

Well, yes; I'm quite sure they're more than adequate for my purposes.

Your reply makes me wonder, however, whether you consider my idea
(let's call it the ispell approach) a bad idea. Do you?

Best, Florian

Re: Using a command-line program (sqlite3) as a backend

[Glenn Morris]

florian wrote:

> That's true, I didn't mention that. I did have a look, but my
> impression was that this is mainly concerned with editing SQL, and
> invoking one of several databases to test (or evaluate) the SQL
> statements written. This is definitely a useful thing, and might prove
> handy later on, but is not quite what I want to do right now.

I thought that the (supposed) support for running sql as an inferior
process might have been useful, is all.

> Nevertheless, I tested the (sql-sqlite ..) function, and it does not
> seem like a function that has actually ever been tested: it asks for a
> user name, password, and server, none of which makes sense with SQLite

This was recently fixed

http://emacsbugs.donarmstrong.com/cgi-bin/bugreport.cgi?bug=1904

Re: Using a command-line program (sqlite3) as a backend

[Harald Hanche-Olsen]

+ florian <lorian@fsavigny.de>:

>> Using it from elisp requires either loading a dynamically linked
>> library or compiling emacs with sqlite - neither of them are
>> supported at the moment, but the latter is possible,
>
> Oops. Compiling Emacs with sqlite? What would that be?

Highly nontrivial. You would have to modiy emacs source code, adding
stuff to interface with the sqlite library and making data available in
the data structure that elisp uses. Don't even think of going there
unless you are, or wish to become, familiar with emacs internals.

-- 
* Harald Hanche-Olsen     <URL:http://www.math.ntnu.no/~hanche/>
- It is undesirable to believe a proposition
  when there is no ground whatsoever for supposing it is true.
  -- Bertrand Russell

Re: Using a command-line program (sqlite3) as a backend:observations with start-process

[florian]

On Mar 26, 12:28 am, Glenn Morris <rgm+n...@stanford.edu> wrote:

> This was recently fixed

I have to admit I did not have the patience to examine this more
thoroughly than to find out that it uses comint.el, which (since I did
not want an interactive thing) uses start-process. So I went ahead
writing my stuff using start-process. It did work, except for one
problem, which caused me to abandon the approach and write a
synchronous interface instead:

When using start-process, it seems rather nontrivial to tell when the
subprocess is done with its output: how often the filter function I
defined would be called did not seem to be predictable. The solution I
could think of, defining the filter function such as to collect and
concatenate any output until it received a string ending with the
prompt, only worked under some circumstances (and the other solution I
could think of, waiting for some amount of time, and then assuming
sqlite3 should be done, seemed like nothing more than a kludge):

When the subprocess is started using a pseudo-TTY, all output
eventually seems to reach Emacs. But when it is started using a pipe,
the prompt (and also the welcome message, for that matter) is never
received. Under Windows, start-process apparently always uses a pipe,
so the only signal to tell the output is complete is not received.

I am not sure if I abandoned this prematurely (after all, ispell.el
seems to work even under Windows), but in any case, the Elisp
documentation is a little scarce on which output is received and which
is not: "It is impossible to separate the standard output and standard
error streams of the subprocess, because Emacs normally spawns the
subprocess inside a pseudo-TTY, and a pseudo-TTY has only one output
channel." is all I can find, but this does not really explain it (and
from experiments with sqlite3 on the command line, it seems far from
clear that stderr/stdout could be the explanation).

Thanks for your help,
Florian

Re: Using a command-line program (sqlite3) as a backend:observations with start-process

[rustom]

Much more lightweight than compiling C is pymacs
Take a walk down the path:

emacs -> elisp -> pymacs -> python -> pysqlite -> sqlite

and then back