Syntactic Sugar

ORG MODE TABLES are a lot of fun to play with. To have a fully text-based table which will grow an shrink cells to fit their contents is very useful when getting organising your thoughts on screen, producing content for text only mediums, or just showing off. One more nifty trick is querying org tables using SQL commands. This method uses org-babel to load a table (or tables) into an SQLite in-memory database, perform the query, then spit out a table as a result.

FOR PRE-REQUISITES, you're going to want to ensure you have installed SQLite, and have enabled the SQLite language in your org-babel settings. SQLite installation should be pretty straightforward. I used chocolatey to install it on my windows machine. To enable SQLite as a org babel language, run the following elisp (add it to your .emacs if you're going to keep it around).

(org-babel-do-load-languages
 'org-babel-load-languages
 '((R . t)
(ledger . t)
(sqlite . t)
))

I've added ledger as another language to the list, just to demonstrate how to enable more language. If you don't want it enabled, just delete that line. Execute this and you should be all set.

SINGLE TABLE. Let's start with a single table. Open up an org file, or switch your current buffer to org-mode. Add the following:

#+NAME: tableexample
| id |  n |
|----+----|
|  1 |  5 |
|  2 |  9 |
|  3 | 10 |
|  4 |  9 |
|  5 | 10 |

We now have a table called `tableexample`. Now, add another source block:

#+header: :db ":memory:"
#+header: :colnames no
#+header: :var orgtable=tableexample
#+header: :mode csv
#+header: :header on
​#+begin_src sqlite
.import $orgtable testtable
select * from testtable;
​#+end_src

Executing this (C-c C-c) should spit out the following:

#+RESULTS:
| id |  n |
|  1 |  5 |
|  2 |  9 |
|  3 | 10 |
|  4 |  9 |
|  5 | 10 |

Not particularly exciting. But chaging `select * from testtable;` to `select avg(n) from testtable;` we get:

#+RESULTS:
| avg(n) |
|    8.6 |

You get the picture - arbitrary SQL code execution. The header values dictate what tables to use, and what databases to point to. You can use this method to point to an existing data base and pull out information, or even join two org tables:

#+name: students
| id | fName   |
|----+---------|
|  1 | sarah   |
|  2 | jack    |
|  3 | jessica |
|  4 | john    |

#+name: address
| studentId | address    |
|-----------+------------|
|         2 | north road |
|         3 | south road |

#+header: :db ":memory:"
#+header: :colnames no
#+header: :var address=address
#+header: :var students=students
#+header: :mode csv
#+header: :header on
​#+begin_src sqlite
.import $students students
.import $address address
select id, fName, address from students inner join address on students.id = address.studentId;
​#+end_src

#+RESULTS:
| id | fName   | address    |
|  2 | jack    | north road |
|  3 | jessica | south road |

The security confirmation can get a little irksome, to disable it, follow the steps in this stack exchange answer. https://emacs.stackexchange.com/questions/21124/execute-org-mode-source-blocks-without-security-confirmation

To tidy it all up, I move all the header statements into the begin src line, and save it as a ya snippet:

# -*- mode: snippet -*-
# name: sql-org
# key: sql-org
# --
​#+begin_src sqlite :db ":memory:" :colnames no :var $1=$1 :mode csv :header on
.import $orgtable orgtable
select * from $1;
​#+end_src

Now, when working with org tables, a sql query is but a sql-org [TAB] away!