Re: A strange problem with org-babel and SQLite

[Robert Klein <roklein@roklein.de>]

On Wed, 5 Sep 2018 08:56:02 +0200
Robert Klein <roklein@roklein.de> wrote:

> 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

Hi Cecil,

could you try to put the following code in your .emacs _after_
“org-babel-do-load-languages” for ob-sqlite?

(defun org-babel-read (cell &optional inhibit-lisp-eval)
  "Convert the string value of CELL to a number if appropriate.
Otherwise if cell looks like lisp (meaning it starts with a
\"(\", \"\\='\", \"\\=`\" or a \"[\") then read it as lisp,
otherwise return it unmodified as a string.  Optional argument
NO-LISP-EVAL inhibits lisp evaluation for situations in which is
it not appropriate."
  (if (and (stringp cell) (not (equal cell "")))
      (or (org-babel-number-p cell)
          (if (and (not inhibit-lisp-eval)
                   (or (member (substring cell 0 1) '("(" "'" "`" "["))
                       (string= cell "*this*")))
              (eval (read cell))
            (if (and (not inhibit-lisp-eval)
                     (string= (substring cell 0 1) "\""))
                (read cell)
              (progn (set-text-properties 0 (length cell) nil cell)
cell)))) cell))


(This should work for Emacs 25.x)


Best regards
Robert