Re: A strange problem with org-babel and SQLite

[Cecil Westerhof <cldwesterhof@gmail.com>]

[-- Attachment #1: Type: text/plain, Size: 9620 bytes --]

2018-09-09 14:36 GMT+02:00 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)
>

It goes quit a way in the right direction.

When I execute:
SELECT quote
,      lastUsed
,      totalUsed
FROM   quotes
WHERE  lastUsed = DATE('now')

I get:
| quote                             |   lastUsed | totalUsed |
|-----------------------------------+------------+-----------|
| "[                                |            |           |
| The thought manifests the word;   |            |           |
| The word manifests the deed;      |            |           |
| The deed develops into habit;     |            |           |
| And habit hardens into character; |            |           |
| So watch the thought and          |            |           |
| its ways with care.               |            |           |
|                                   |            |           |
| - Juan Mascaro""                  |            |           |
| Je gedachten zijn                 |            |           |
| de grondslag van je:              |            |           |
| daden                             |            |           |
| gewoontes en                      |            |           |
| karakter.                         |            |           |
|                                   |            |           |
| Waak over je gedachten.""         |            |           |
| ]"                                | 2018-09-10 |         5 |

Is not completely correct, because the real quote is:

[

"The thought manifests the word;

The word manifests the deed;

The deed develops into habit;

And habit hardens into character;

So watch the thought and

its ways with care.


- Juan Mascaro",

"Je gedachten zijn

de grondslag van je:

daden,

gewoontes en

karakter.


Waak over je gedachten."

]

But that is really not a problem.


When I execute:
SELECT *
FROM   quotes
WHERE  lastUsed = DATE('now')

I get:
| quoteID                              | quote      | lastUsed | totalUsed |
|--------------------------------------+------------+----------+-----------|
| 55d0b51c-7b46-44f8-9e67-ebe59c63ca34 | "[         |          |           |
| The thought manifests the word;      |            |          |           |
| The word manifests the deed;         |            |          |           |
| The deed develops into habit;        |            |          |           |
| And habit hardens into character;    |            |          |           |
| So watch the thought and             |            |          |           |
| its ways with care.                  |            |          |           |
|                                      |            |          |           |
| - Juan Mascaro""                     |            |          |           |
| Je gedachten zijn                    |            |          |           |
| de grondslag van je:                 |            |          |           |
| daden                                |            |          |           |
| gewoontes en                         |            |          |           |
| karakter.                            |            |          |           |
|                                      |            |          |           |
| Waak over je gedachten.""            |            |          |           |
| ]"                                   | 2018-09-10 |        5 |           |

So everything after the first line of quote goes to quoteID. And the other
fields go a to the previous field.

At the moment that is not a query I need to do, and if I need everything I
could rewrite it as:
SELECT quote
,      quoteID
,      lastUsed
,      totalUsed
FROM   quotes
WHERE  lastUsed = DATE('now')

and then I get:
| quote                             | quoteID
|   lastUsed | totalUsed |
|-----------------------------------+--------------------------------------+------------+-----------|
| "[                                |
|            |           |
| The thought manifests the word;   |
|            |           |
| The word manifests the deed;      |
|            |           |
| The deed develops into habit;     |
|            |           |
| And habit hardens into character; |
|            |           |
| So watch the thought and          |
|            |           |
| its ways with care.               |
|            |           |
|                                   |
|            |           |
| - Juan Mascaro""                  |
|            |           |
| Je gedachten zijn                 |
|            |           |
| de grondslag van je:              |
|            |           |
| daden                             |
|            |           |
| gewoontes en                      |
|            |           |
| karakter.                         |
|            |           |
|                                   |
|            |           |
| Waak over je gedachten.""         |
|            |           |
| ]"                                | 55d0b51c-7b46-44f8-9e67-ebe59c63ca34
| 2018-09-10 |         5 |


I would get a problem when I have two fields that contain JSON data, but I
think it is very unlikely I will get in such a situation.

-- 
Cecil Westerhof

[-- Attachment #2: Type: text/html, Size: 18093 bytes --]