2018-09-09 14:36 GMT+02:00 Robert Klein <[email protected]>:
> On Wed, 5 Sep 2018 08:56:02 +0200
> Robert Klein <[email protected]> wrote:
>
> > Hi Cecil,
> >
> > On Mon, 3 Sep 2018 03:23:17 +0200
> > Cecil Westerhof <[email protected]> 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
