Re: [O] 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

[O] 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: [O] sqlite im-/export

[Oliver Večerník]

Eric Schulte schulte.e...@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: [O] sqlite im-/export

[Oliver Večerník]

Eric Schulte schulte.e...@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: [O] 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

[O] diff in `src' blocks

[Oliver Večerník]

Hi,

in the following org file the second block gets not exported (tested with
text and html exporter, release_8.0.2-119-g646f1a):

--8---cut here---start-8---
#+OPTIONS: toc:nil author:nil

#+BEGIN_SRC sh :exports results :results output
cat testdiff.txt.orig
#+END_SRC

#+BEGIN_SRC sh :exports results :results output
diff testdiff.txt.orig testdiff.txt
#+END_SRC

#+BEGIN_SRC sh :exports results :results output
diff testdiff.txt.orig testdiff.txt | cat
#+END_SRC

#+BEGIN_SRC sh :exports results :results output
ls testdiff.*
#+END_SRC
--8---cut here---end---8---

   __

TESTDIFF
   __


,
| asdf
| ghjk
`

,
| 1c1
|  asdf
| ---
|  qwer
`

,
| testdiff.html
| testdiff.org
| testdiff.txt
| testdiff.txt.orig
`

Why is the `diff' command alone not exported while the piped `diff' via
`cat' works?  Is this this a bug?

-- 
Regards,
Oliver

Re: [O] diff in `src' blocks

[Oliver Večerník]

 Why is the `diff' command alone not exported while the piped `diff' via
 `cat' works?  Is this this a bug?

Figured out myself, sorry for the noise.  It has to do with the exit
status of the `diff', which is 1 if the files are different.  So I use
the pipe through `cat'.

Re: [O] diff in `src' blocks

[Oliver Večerník]

 Figured out myself, sorry for the noise.  It has to do with the exit
 status of the `diff', which is 1 if the files are different.  So I use
 the pipe through `cat'.

 Useless use of cat...

At least the return status is zero.  But I agree with you, a colon is
better here.  This is my first use of a `:' btw.  I never had case
before.

-- 
Thanks,
Oliver

Re: [O] Can I specify the location of generated html in org 8?

[Oliver Večerník]

So is there any way to change the location of the table of contents
parts?

What I did recently was disabling toc in the header and putting it in
the document later (info (org) Table of contents):

#+OPTIONS: toc:nil

#+BEGIN_HTML
---
layout: org
id: elispintro
title: An Introduction To Emacs Lisp
---
#+END_HTML

#+TOC: headlines 1

* h1
* h2

-- 
Regards,
Oliver

Re: [O] filter included files

[Oliver Večerník]

 I like to include files from the network to document their status at the
 time of publication e.g.:

   #+INCLUDE: /host:/etc/iptables/iptables.rules example

 But sometimes I'd like to do some more filtering (sed, whatever), to
 remove passwords or other sensible information from the output.  Is
 their a 'best practice' to achieve this goal?

To answer my own question, I still don't know if this is 'best
practice', but this worked for me quite well:

#+BEGIN_SRC sh :exports results :results output
ssh host cat /etc/iptables/iptables.rules | sed 's/pattern/string/'
#+END_SRC

-- 
Regards,
Oliver

Re: [O] filter included files

[Oliver Večerník]

 To answer my own question, I still don't know if this is 'best
 practice', but this worked for me quite well:

 #+BEGIN_SRC sh :exports results :results output
 ssh host cat /etc/iptables/iptables.rules | sed 's/pattern/string/'
 #+END_SRC

oops, useless use of cat:

#+BEGIN_SRC sh :exports results :results output
ssh host sed 's/pattern/string/' /etc/iptables/iptables.rules
#+END_SRC

[O] filter included files

[Oliver Večerník]

Hi,

I like to include files from the network to document their status at the
time of publication e.g.:

  #+INCLUDE: /host:/etc/iptables/iptables.rules example

But sometimes I'd like to do some more filtering (sed, whatever), to
remove passwords or other sensible information from the output.  Is
their a 'best practice' to achieve this goal?

-- 
Regards,
Oliver

Re: [O] Spreadsheet calculations (24.3/8.0-pre)

[Oliver Večerník]

Hi Ippei,

 | Product   |g | kJ/100g |   kJ | kcal |
 |---+--+-+--+--|
 | Bread | 50.6 |1372 |  694 |  166 |
 | Butter| 11.5 |3054 |  351 |   84 |
 | Marmalade | 19.7 | 926 |  182 |   44 |
 |---+--+-+--+--|
 |   |  | | 1227 |  294 |
 #+TBLFM: $3='(org-lookup-first $1 '(remote(nf,@I$1..@II$1))
 (remote(nf,@I$2..@II$2)))
 #+TBLFM: $4='(* $2 (/ $3 $b));N%.0f
 #+TBLFM: $5=$4/$j;%.0f
 #+TBLFM: @$4..$5=vsum(@I..II)
 (Each TBLFM line has no linebreak.)

thanks for your suggestion, but I didn't want an extra column.  I played
with `N' and `L' options and found following solution leaving them
out entirely:

#+TITLE: Nutrition Facts
#+CONSTANTS: b=100.0 j=4.184

#+TBLNAME: nf
| Product |   kJ | kcal |
|-+--+--|
| Bread white | 1372 |  328 |
| Butter  | 3054 |  730 |
| Marmalade   |  926 |  221 |
#+TBLFM: $3=$2/$j;%.0f

| Product |g |   kJ | kcal |
|-+--+--+--|
| Bread white | 50.6 |  694 |  166 |
| Butter  | 11.5 |  351 |   84 |
| Marmalade   | 19.7 |  182 |   43 |
| nonexistent |  |0 |0 |
|-+--+--+--|
| |  | 1227 |  293 |
#+TBLFM: $3='(* (string-to-number $2) (/ (string-to-number (org-lookup-last $1 
'(remote(nf,@I$1..@II$1)) '(remote(nf,@I$2..@II$2 
$b));%.0f::$4=$3/$j;%.0f::@$3..$4=vsum(@I..II)

$1 has to be a string, because the lookup column can have more than one
word.  For the math I have to convert the strings to numbers.  Maybe
someone has an idea for a more elegant solution, but this works for me now.

-- 
Best,
Oliver

Re: [O] Spreadsheet calculations (24.3/8.0-pre)

[Oliver Večerník]

Hi Bastien,

 #+CONSTANTS is meant to be used only once on the file, not per table.

that's how I understood it.

 When used several times, `org-table-formula-constants-local' was
 defining the same constant several times, which is wrong.  I fixed
 this.

I can confirm this is working now.

 Let's take other problems one by one if you have time.

Sure.  Let's start with following tables and Org-mode version 8.0-pre
(release_8.0-pre-144-g855dcf @ /home/ov/p/org-mode/lisp/):

#+TITLE: Nutrition Facts
#+CONSTANTS: b=100.0 j=4.182

#+TBLNAME: nf
| Product   |   kJ | kcal |
|---+--+--|
| Bread | 1372 |  328 |
| Butter| 3054 |  730 |
| Marmalade |  926 |  221 |
#+TBLFM: $3=$2/$j;%.0f

| Product   |g |   kJ | kcal |
|---+--+--+--|
| Bread | 50.6 |  658 |  157 |
| Butter| 11.5 |  150 |   36 |
| Marmalade | 19.7 |  256 |   61 |
|---+--+--+--|
|   |  | 1064 |  254 |
#+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) 
'(remote(nf,@I$2..@II$2))) $b));N%.0f::$4=$3/$j;%.0f::@$3..$4=vsum(@I..II)

I'm reapplying formulas by pressing `C-c C-c' on the hash mark of the
format line.  The expected results are:

| Product   |g |   kJ | kcal |
|---+--+--+--|
| Bread | 50.6 |  694 |  166 |
| Butter| 11.5 |  351 |   84 |
| Marmalade | 19.7 |  182 |   44 |
|---+--+--+--|
|   |  | 1227 |  294 |

Let's take this apart:

#+BEGIN_SRC emacs-lisp
(values (* 50.6 (/ 1372 100.0))
(* 11.5 (/ 3054 100.0))
(* 19.7 (/ 926 100.0)))
#+END_SRC
#+RESULTS: 
| 694.23201 | 351.21 | 182.422 |

I was bitten myself by setting `b=100', which is an integer and led to
the wrong result.  But Org-mode still calculates as if `b' were an
integer.  But even replacing `$b' with `100.0' still gives wrong results
(second and third line):

| Product   |g |   kJ | kcal |
|---+--+--+--|
| Bread | 50.6 |  694 |  166 |
| Butter| 11.5 |  158 |   38 |
| Marmalade | 19.7 |  270 |   65 |
|---+--+--+--|
|   |  | 1122 |  269 |
#+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) 
'(remote(nf,@I$2..@II$2))) 100.0));N%.0f::$4=$3/$j;%.0f::@$3..$4=vsum(@I..II)

-- 
Oliver

[O] Spreadsheet calculations (24.3/8.0-pre)

[Oliver Večerník]

Hi,

I'm trying to do some simple calculations, but the results are plain
wrong.  I started the minimal example with `emacs -Q -l minimal.emacs
org/minimal.org'.  My Emacs is 24.3 with Org-mode version 8.0-pre
(release_8.0-pre-116-g65cde8 @ /home/ov/p/org-mode/lisp/):

#+TITLE: Nutrition Facts
#+CONSTANTS: b=100 j=4.182

#+TBLNAME: nf
| Product   |   kJ | kcal |
|---+--+--|
| Bread | 1372 |  328 |
| Butter| 3054 |  730 |
| Marmalade |  926 |  221 |
#+TBLFM: $3=$2/$j;%.0f

Here are some calculations per portion (plain wrong):

| Product   |g |   kJ | kcal |
|---+--+--+--|
| Bread | 50.6 |  658 |  157 |
| Butter| 11.5 |  150 |   36 |
| Marmalade | 19.7 |  256 |   61 |
|---+--+--+--|
|   |  | 1064 |  254 |
#+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) 
'(remote(nf,@I$2..@II$2))) 100));N%.0f::$4=$3/$j;%.0f::@$3..$4=vsum(@I..II)

Expected:

| Product   |g |   kJ | kcal |
|---+--+--+--|
| Bread | 50.6 |  694 |  166 |
| Butter| 11.5 |  351 |   84 |
| Marmalade | 19.7 |  182 |   44 |
|---+--+--+--|
|   |  | 1227 |  294 |
#+TBLFM: $4=$3/$j;%.0f::@$3..$4=vsum(@I..II)

Using the constant b is also totally wrong:

| Product   |g | kJ | kcal |
|---+--++--|
| Bread | 50.6 |  0 |0 |
| Butter| 11.5 |  0 |0 |
| Marmalade | 19.7 |  0 |0 |
|---+--++--|
|   |  |  0 |0 |
#+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) 
'(remote(nf,@I$2..@II$2))) $b));N%.0f::$4=$3/$j;%.0f::@$3..$4=vsum(@I..II)

These results are achieved using `C-c C-c' on the first column of the
format line.  If I use `C-u C-c C-c' in the tabel I get different
results *every* time.  E.g. pressing `C-u C-c C-c' three times on the
`B' of `Bread':

| Product   |g | kJ |  kcal |
|---+--++---|
| Bread | 50.6 |  32606 |  7784 |
| Butter| 11.5 |  59888 | 14297 |
| Marmalade | 19.7 | 110192 | 26306 |
|---+--++---|
|   |  | 202686 | 48387 |
#+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) 
'(remote(nf,@I$2..@II$2))) 100));N%.0f::$4=$3/$j;%.0f::@$3..$4=vsum(@I..II)

If I go to the end of the format line and press `C-c C-c' I get totally
different results as at the beginning:

| Product   |g | 1064 |  254 |
|---+--+--+--|
| Bread | 50.6 | 1064 |  254 |
| Butter| 11.5 | 1470 |  351 |
| Marmalade | 19.7 | 2790 |  666 |
|---+--+--+--|
|   |  | 5324 | 1271 |
#+TBLFM: $3='(* $2 (/ (org-lookup-first $1 '(remote(nf,@I$1..@II$1)) 
'(remote(nf,@I$2..@II$2))) 100));N%.0f::$4=$3/$j;%.0f::@$3..$4=vsum(@I..II)

If I use `M-x org-table-recalculate-buffer-tables' even the headlines
get screwed up.  Am I doing something wrong or are there severe problems
in the spreadsheet mode?

Thanks in advance!

-- 
Regards, Oliver