emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
From: Robert Klein <roklein@roklein.de>
To: Cecil Westerhof <cldwesterhof@gmail.com>
Cc: emacs-orgmode@gnu.org
Subject: Re: A strange problem with org-babel and SQLite
Date: Sun, 9 Sep 2018 14:36:22 +0200	[thread overview]
Message-ID: <20180909143622.46baea3c@happy.intern.roklein.de> (raw)
In-Reply-To: <20180905085602.18488701@lt70.mpip-mainz.mpg.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

  reply	other threads:[~2018-09-09 12:36 UTC|newest]

Thread overview: 13+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2018-08-31  8:47 A strange problem with org-babel and SQLite Cecil Westerhof
2018-08-31  9:17 ` Robert Klein
2018-08-31 10:24   ` Cecil Westerhof
2018-08-31 11:22     ` Robert Klein
2018-09-01  9:12       ` Cecil Westerhof
2018-09-01 12:24         ` Robert Klein
2018-09-02 13:22           ` Cecil Westerhof
2018-09-03  0:19             ` Cecil Westerhof
2018-09-03  1:09           ` Cecil Westerhof
2018-09-03  1:23             ` Cecil Westerhof
2018-09-05  6:56               ` Robert Klein
2018-09-09 12:36                 ` Robert Klein [this message]
2018-09-10  7:05                   ` Cecil Westerhof

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

  List information: https://www.orgmode.org/

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=20180909143622.46baea3c@happy.intern.roklein.de \
    --to=roklein@roklein.de \
    --cc=cldwesterhof@gmail.com \
    --cc=emacs-orgmode@gnu.org \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line before the message body.
Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).