Re: A strange problem with org-babel and SQLite

[Robert Klein <roklein@roklein.de>]

Hi Cecil,

On Mon, 3 Sep 2018 03:23:17 +0200
Cecil Westerhof <cldwesterhof@gmail.com> wrote:

> It has to do with the data. With the following I can reproduce it:
> #+BEGIN_SRC sqlite :db ~/testingOrgBabel.sqlite :colnames yes
> DROP TABLE IF EXISTS quotes
> ;
> CREATE TABLE "quotes" (
>     quoteID         TEXT           PRIMARY KEY,
>     quote           TEXT NOT NULL  UNIQUE,
>     lastUsed        TEXT,
>     totalUsed       INT  DEFAULT 'unused'
> )
> ;
> INSERT INTO quotes
> (quoteID, quote)
> VALUES
> ("1230FCF5-B25D-4087-88A4-41DF3AC353DA", '[
>   "Limitations live only in our minds.
> But if we use our imaginations,
> our possibilities become limitless.
> 
> - Jamie Paolinett",
>   "Hoe gebruik jij je verbeelding om
> je mogelijkheden te vergroten?"
> ]'),
> (2, "Second record.")
> ;
> SELECT *
> FROM   quotes
> ;
> #+END_SRC
> 
> When I put a JSON field in the quote field the parsing goes wrong.
> 
> 

umm, yes.  Actually what seems to happen is that emacs tries to
evaluate the JSON part as emacs lisp code, in this case an array.  In
detail, I think, this happens:


- org-babel-execute:sqlite (ob-sqlite, line 60)
  calls (for converting the results)

- org-babel-sqlite-table-or-scalar (ob-sqlite, line 133),
  which apparently thinks the result looks like a “trivial table” and
  calls

- org-babel-read (ob-core.el, line 2912),
  which detects the JSON string (begins with a "[ ) as lisp and tries
  to evaluate the lisp form.  The call to “read” in line 2927 then
  fails, because there is no closing ]  (only the contents on one cell
  is sent to org-babel-read; note, there are no multi-line cells in org
  tables).


Line numbers are from Org release_9.1.14-1-g4931fc.




That's no solution of course. To resolve this,

- is there a reason to evaluate table cell contents as lisp code?

If no,

- don't use org-babel-read (in org-babel-sqlite-table-or-scalar)
- or compare “(org-babel-result-cond...)” code with other ob-*.el
  (ob-sql.el?) and rewrite.

If yes,

- is there a way to check if a string is correct lisp code before
  calling “read”?



In the “yes” case, there's still the issue of JSON being possibly
detected as “correct” lisp code (e.g. ["alfa"]).


In your case, if you haven't invested too much in the dependency on
JSON, you might want to redesign the database, e.g. 


CREATE TABLE "quotes" (
        quoteID         TEXT           PRIMARY KEY,
        quote_en        TEXT NOT NULL  UNIQUE,
	quote_nl	TEXT NOT NULL  UNIQUE,
        lastUsed        TEXT,
        totalUsed       INT  DEFAULT 'unused'
    );




Best regards
Robert






> 2018-09-03 3:09 GMT+02:00 Cecil Westerhof <cldwesterhof@gmail.com>:
> 
> > 2018-09-01 14:24 GMT+02:00 Robert Klein <roklein@roklein.de>:
> >  
> >> Hi Cecil,
> >>
> >> On Sat, 1 Sep 2018 11:12:57 +0200
> >> Cecil Westerhof <cldwesterhof@gmail.com> wrote:
> >>  
> >> > 2018-08-31 13:22 GMT+02:00 Robert Klein <roklein@roklein.de>:
> >> >  
> >> > > On Fri, 31 Aug 2018 12:24:33 +0200
> >> > > Cecil Westerhof <cldwesterhof@gmail.com> wrote:
> >> > >  
> >> > > > 2018-08-31 11:17 GMT+02:00 Robert Klein <roklein@roklein.de>:
> >> > > >  
> >> > > > > Hi Cecil,
> >> > > > >
> >> > > > > On Fri, 31 Aug 2018 10:47:50 +0200
> >> > > > > Cecil Westerhof <cldwesterhof@gmail.com> wrote:
> >> > > > >  
> >> > > > > > I have a strange problem with org-babel and SQLite.
> >> > > > > >
> >> > > > > > I have a database that is created with:
> >> > > > > >     CREATE TABLE "quotes" (
> >> > > > > >         quoteID         TEXT           PRIMARY KEY,
> >> > > > > >         quote           TEXT NOT NULL  UNIQUE,
> >> > > > > >         lastUsed        TEXT,
> >> > > > > >         totalUsed       INT  DEFAULT 'unused'
> >> > > > > >     )
> >> > > > > >
> >> > > > > > When using:
> >> > > > > >     #+BEGIN_SRC sqlite :db
> >> > > > > > ~/Twitter/twitter.sqlite :colnames yes SELECT   lastUsed
> >> > > > > >     ,        totalUsed
> >> > > > > >     FROM     quotes
> >> > > > > >     ORDER BY lastused  ASC
> >> > > > > >     ,        totalUsed DESC
> >> > > > > >     LIMIT    40
> >> > > > > >     #+END_SRC
> >> > > > > >
> >> > > > > > Everything is fine. But when I use (add the quote field
> >> > > > > > in the select): #+BEGIN_SRC sqlite :db
> >> > > > > > ~/Twitter/twitter.sqlite :colnames yes SELECT   quote
> >> > > > > >     ,        lastUsed
> >> > > > > >     ,        totalUsed
> >> > > > > >     FROM     quotes
> >> > > > > >     ORDER BY lastused  ASC
> >> > > > > >     ,        totalUsed DESC
> >> > > > > >     LIMIT    40
> >> > > > > >     #+END_SRC
> >> > > > > >
> >> > > > > > I get:
> >> > > > > >     executing Sqlite code block...
> >> > > > > >     Wrote /tmp/babel-27920y_/ob-input-2792BTG
> >> > > > > >     org-babel-read: End of file during parsing
> >> > > > > >
> >> > > > > > What could be the problem?
> >> > > > > >  
> >> > > > >
> >> > > > > does it work outside of org/babel/emacs, that is, when you
> >> > > > > use the query in a command line sqlite session, does it
> >> > > > > work? “quote” is also a function in sqlite, so this might
> >> > > > > be your issue.  
> >> > > >
> >> > > > Yes, in sqlite3 and sqlitebrowser it works without problems.
> >> > > > In org-babel even 'SELECT *' goes wrong.
> >> > > >  
> >> > >
> >> > > I can't reproduce the issue, it works for me.  What are your
> >> > > org-mode and Emacs versions?
> >> > >  
> >> >
> >> > GNU Emacs 25.1.1 (x86_64-pc-linux-gnu, GTK+ Version 3.22.11) of
> >> > 2017-09-15, modified by Debian
> >> > Org-mode version 8.2.10 (release_8.2.10
> >> > @ /usr/share/emacs/25.1/lisp/org/)
> >> >
> >> >  
> >> > >
> >> > > Can you provide an ECM (Example, complete, minimal) org-mode
> >> > > setup / Emacs initialization file?
> >> > >  
> >> >
> >> > (add-hook 'org-babel-after-execute-hook 'bh/display-inline-images
> >> > 'append)
> >> >
> >> > ; Make babel results blocks lowercase
> >> > (setq org-babel-results-keyword "results")
> >> >
> >> > (defun bh/display-inline-images ()
> >> >   (condition-case nil
> >> >       (org-display-inline-images)
> >> >     (error nil)))
> >> >
> >> > (org-babel-do-load-languages
> >> >  (quote org-babel-load-languages)
> >> >  (quote ((emacs-lisp . t)
> >> >          (sqlite . t)
> >> >          (dot . t)
> >> >          (ditaa . t)
> >> >          (R . t)
> >> >          (python . t)
> >> >          (ruby . t)
> >> >          (gnuplot . t)
> >> >          (clojure . t)
> >> >          (sh . t)
> >> >          (ledger . t)
> >> >          (org . t)
> >> >          (plantuml . t)
> >> >          (latex . t))))
> >> >
> >> > ; Do not prompt to confirm evaluation
> >> > ; This may be dangerous - make sure you understand the
> >> > consequences ; of setting this -- see the docstring for details
> >> > (setq org-confirm-babel-evaluate nil)
> >> >
> >> > ; Use fundamental mode when editing plantuml blocks with C-c '
> >> > (add-to-list 'org-src-lang-modes (quote ("plantuml" .
> >> > fundamental)))
> >> >
> >> > Is this what you need, or do you need more?
> >> >  
> >>
> >> sorry, I still can't reproduce the issue, even using the same stock
> >> Emacs and org-mode from Debian 9 as you do.
> >>
> >> Could you _attach_ the emacs init file and the org-mode file which
> >> shows the issue?  (so I can simply start “emacs -Q -l sqlite.emacs
> >> sqlite.org” and then press C-c C-c inside the sqlite code to see
> >> the issue?
> >>  
> >
> > It is quite strange. It looks like it has something to do with the
> > database itself, because the following works:
> > #+BEGIN_SRC sqlite :db ~/testingOrgBabel.sqlite :colnames yes
> > DROP TABLE IF EXISTS quotes
> > ;
> > CREATE TABLE "quotes" (
> >     quoteID         TEXT           PRIMARY KEY,
> >     quote           TEXT NOT NULL  UNIQUE,
> >     lastUsed        TEXT,
> >     totalUsed       INT  DEFAULT 'unused'
> > )
> > ;
> > INSERT INTO quotes
> > (quoteID, quote)
> > VALUES
> > (1, "First record."),
> > (2, "Second record.")
> > ;
> > SELECT *
> > FROM   quotes
> > ;
> > #+END_SRC
> >
> > The create is just copied from the original database.
> >
> > The following works:
> > #+BEGIN_SRC sqlite :db ~/testingOrgBabel.sqlite :colnames yes
> > SELECT *
> > FROM   quotes
> > LIMIT  2
> > ;
> > #+END_SRC
> >
> >
> > But this does not work:
> > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes
> > SELECT *
> > FROM   quotes
> > LIMIT  2
> > ;
> > #+END_SRC
> >
> >
> > Weird indeed.
> >
> >
> >
> > What I also tried:
> > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes
> > CREATE TABLE "quotes2" (
> >     quoteID         TEXT           PRIMARY KEY,
> >     quote           TEXT NOT NULL  UNIQUE,
> >     lastUsed        TEXT,
> >     totalUsed       INT  DEFAULT 'unused'
> > )
> > #+END_SRC
> >
> >
> > Then I can do:
> > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes
> > SELECT *
> > FROM   quotes2
> > #+END_SRC
> >
> > But when I do:
> > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes
> > INSERT INTO quotes2
> > SELECT * from quotes
> > #+END_SRC
> >
> > the select does not work any-more.
> >
> > But the select from the command-line tool works without a problem.
> >
> > --
> > Cecil Westerhof
> >  
> 
> 
>