sqlite im-/export

sqlite im-/export

[Oliver Večerník]

Hi,

I've got a small test case which I believe is a bug:

$ cat t1.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1(id integer,product text);
INSERT INTO "t1" VALUES(1,'apple pie');
INSERT INTO "t1" VALUES(2,'sugar');
COMMIT;
sqlite3 test.sqlite < t1.sql

#+BEGIN_SRC sh
echo "ID|product"
sqlite3 test.sqlite "
  SELECT * from t1;
"
#+END_SRC

#+RESULTS:
| ID | product |     |
|  1 | apple   | pie |
|  2 | sugar   |     |

Why are there three columns exported?  Tested with Org-mode version
8.0.3 (release_8.0.3-114-gab3f45 @ /home/user/org-mode/lisp/) on GNU
Emacs 24.3.50.2 (x86_64-unknown-linux-gnu, GTK+ Version 3.6.4) of
2013-05-13 on myhost?


Is it also possible to produce a separator line in the results table?  I
tried a couple of variations, but nothing worked:

echo "|-"
echo "-----+----"
echo "|-----+----|"


I have a couple of csv tables now which are imported with a src block
like this:

#+BEGIN_SRC sqlite :db test.sqlite :exports none :results silent
  PRAGMA foreign_keys=off;
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(id INTEGER, product TEXT);
  .import t1.csv t1
#+END_SRC

But it would be really nice to maintain these data in org tables and
convert them on fly maybe just in memory.  I couldn't figure out how to
achieve this.  Has anybody a sample?

-- 
Best,
Oliver

Re: sqlite im-/export

[Eric Schulte]

Oliver Večerník <ov@vecernik.at> writes:

> Hi,
>
> I've got a small test case which I believe is a bug:
>
> $ cat t1.sql
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE t1(id integer,product text);
> INSERT INTO "t1" VALUES(1,'apple pie');
> INSERT INTO "t1" VALUES(2,'sugar');
> COMMIT;
> sqlite3 test.sqlite < t1.sql
>
> #+BEGIN_SRC sh
> echo "ID|product"
> sqlite3 test.sqlite "
>   SELECT * from t1;
> "
> #+END_SRC
>
> #+RESULTS:
> | ID | product |     |
> |  1 | apple   | pie |
> |  2 | sugar   |     |
>

What about

#+BEGIN_SRC sqlite :csv :db test.sqlite
  SELECT * from t1;
#+END_SRC

#+RESULTS:
| 1 | apple pie |
| 2 | sugar     |

-- 
Eric Schulte
http://cs.unm.edu/~eschulte

Re: sqlite im-/export

[Oliver Večerník]

Eric Schulte <schulte.eric@gmail.com> writes:
> What about
>
> #+BEGIN_SRC sqlite :csv :db test.sqlite
>   SELECT * from t1;
> #+END_SRC
>
> #+RESULTS:
> | 1 | apple pie |
> | 2 | sugar     |

I needed some calculations and format tweaking with awk which I piped
the results.  That is why I choose the shell.  In this case this is not
possible.

-- 
Best,
Oliver

Re: sqlite im-/export

[Eric Schulte]

Oliver Večerník <ov@vecernik.at> writes:

> Eric Schulte <schulte.eric@gmail.com> writes:
>> What about
>>
>> #+BEGIN_SRC sqlite :csv :db test.sqlite
>>   SELECT * from t1;
>> #+END_SRC
>>
>> #+RESULTS:
>> | 1 | apple pie |
>> | 2 | sugar     |
>
> I needed some calculations and format tweaking with awk which I piped
> the results.  That is why I choose the shell.  In this case this is not
> possible.

In that case I'd suggest either

1. adding :separator support to ob-sh.el, so that you can specify a
   different results separator to be passed to
   `org-babel-import-elisp-from-file'

or

2. use ":results drawer", and explicitly formatting the results in
   Org-mode syntax w/awk

Cheers,

-- 
Eric Schulte
http://cs.unm.edu/~eschulte

Re: sqlite im-/export

[Oliver Večerník]

Eric Schulte <schulte.eric@gmail.com> writes:
> In that case I'd suggest either
>
> 1. adding :separator support to ob-sh.el, so that you can specify a
>    different results separator to be passed to
>    `org-babel-import-elisp-from-file'
>
> or
>
> 2. use ":results drawer", and explicitly formatting the results in
>    Org-mode syntax w/awk

I'm faster with your second suggestion.  Thanks for your hint and for
this wonderful extension to Org mode!

-- 
Cheers,
Oliver

Re: sqlite im-/export

[Oliver Večerník]

> 2. use ":results drawer", and explicitly formatting the results in
>    Org-mode syntax w/awk

Unfortunately this doesn't work.  The output is always printed to the
"#+RESULTS:" section and not piped through awk.  But working with
":results raw" works perfect for me.  I can even produce a separator
line in the table now with =print "|-"=.

The only question left, how to import org tables "on the fly" to sqlite,
to keep everthing in Org mode.  This would also be an interesting
candidate for an "ob-doc-sh.org" starter I guess.

-- 
Cheers,
Oliver

Re: sqlite im-/export

[Eric Schulte]

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

Oliver Večerník <ov@vecernik.at> writes:

>> 2. use ":results drawer", and explicitly formatting the results in
>>    Org-mode syntax w/awk
>
> Unfortunately this doesn't work.  The output is always printed to the
> "#+RESULTS:" section and not piped through awk.  But working with
> ":results raw" works perfect for me.  I can even produce a separator
> line in the table now with =print "|-"=.
>
> The only question left, how to import org tables "on the fly" to sqlite,
> to keep everthing in Org mode.  This would also be an interesting
> candidate for an "ob-doc-sh.org" starter I guess.

I may have missed you spelling this out in a previous email, but can you
not import Org tables directly into sqlite code blocks?


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: example.org --]
[-- Type: text/x-org, Size: 377 bytes --]

Evaluate this again after evaluating the second block.
#+BEGIN_SRC sqlite :csv :db test.sqlite
  SELECT * from t1;
#+END_SRC

#+RESULTS:
| 1 | apple pie |
| 2 | sugar     |

A manually created Org-mode block.

#+name: manual
| 3 | rhubarb |
| 4 | butter  |

#+BEGIN_SRC sqlite :csv :var data=manual :db test.sqlite :results silent
  .separator ","
  .import $data t1
#+END_SRC

[-- Attachment #3: Type: text/plain, Size: 46 bytes --]


-- 
Eric Schulte
http://cs.unm.edu/~eschulte

Re: sqlite im-/export

[Oliver Večerník]

> I may have missed you spelling this out in a previous email, but can you
> not import Org tables directly into sqlite code blocks?
>
> Evaluate this again after evaluating the second block.
> #+BEGIN_SRC sqlite :csv :db test.sqlite
>   SELECT * from t1;
> #+END_SRC

This works like a charm, thanks again.

But where is :csv or :db documented?  The info manual says to look at worg, but
there is no `ob-doc-sqlite' documentation link.  Are there any more
specific arguments to sqlite?

There is no `ob-doc-sh' either, but maybe there are no specific arguments
for shell.

-- 
Best,
Oliver

Re: sqlite im-/export

[Eric Schulte]

Oliver Večerník <ov@vecernik.at> writes:

>> I may have missed you spelling this out in a previous email, but can you
>> not import Org tables directly into sqlite code blocks?
>>
>> Evaluate this again after evaluating the second block.
>> #+BEGIN_SRC sqlite :csv :db test.sqlite
>>   SELECT * from t1;
>> #+END_SRC
>
> This works like a charm, thanks again.
>
> But where is :csv or :db documented?  The info manual says to look at worg, but
> there is no `ob-doc-sqlite' documentation link.  Are there any more
> specific arguments to sqlite?
>
> There is no `ob-doc-sh' either, but maybe there are no specific arguments
> for shell.

Sadly many of the languages are under documented.  That could be a good
thing, in that it generally takes less time to add a new language
specific header argument than it does to document one, but it is
obviously also a bad thing when header arguments aren't used because
no-one knows they exist.

In general browsing the source code of the lisp/ob-lang.el files (e.g.,
lisp/ob-sqlite.el) is the best way to find out what header arguments are
used.  For example at the top of ob-sqlite you'll find the following.

    (defvar org-babel-header-args:sqlite
      '((db        . :any)
        (header    . :any)
        (echo      . :any)
        (bail      . :any)
        (csv       . :any)
        (column    . :any)
        (html      . :any)
        (line      . :any)
        (list      . :any)
        (separator . :any)
        (nullvalue . :any))
      "Sqlite specific header args.")

In my opinion the source code of the language-specific files is
readable, but of course I'm not a good judge as I wrote most of it.  We
definitely need more volunteers to add documentation on worg.

-- 
Eric Schulte
http://cs.unm.edu/~eschulte