Skip to content

Latest commit

 

History

History
479 lines (381 loc) · 12.6 KB

README.org

File metadata and controls

479 lines (381 loc) · 12.6 KB
Raw

Examples

Getting started

dsq source code blocks use the custom :input header argument to specify one or more data sources to query. Here’s a basic example that queries a list of people in a file called people.json. Results are formatted as a table with a header row by default (see below for other options).

#+begin_src dsq :input people.json
SELECT * FROM {}
#+end_src

#+RESULTS:
| id | name    |
|----+---------|
|  1 | Alice   |
|  2 | Bob     |
|  3 | Charles |

Querying multiple sources (files, with varying data types)

The :input header argument accepts one or more data sources. Here’s an example that runs a query across two input files with varying data types (JSON and CSV).

#+begin_src dsq :input people.json languages.csv
SELECT people.name AS name, languages.name AS language
FROM {0} people
INNER JOIN {1} languages ON  people.id = languages.person_id
#+end_src

#+RESULTS:
| name  | language |
|-------+----------|
| Alice | Ruby     |
| Alice | Elisp    |
| Bob   | Python   |

Querying Org references (in local or other files)

Besides regular files, and similar to the :var header argument, the :input header argument also accepts references to Org tables, Org quotes, Org source blocks, etc. Here’s an example that runs a query on an Org table named colors.

#+name: colors
| person_id | name  |
|-----------+-------|
|         1 | Blue  |
|         2 | Red   |
|         2 | Green |

#+begin_src dsq :input colors
SELECT name FROM {}
#+end_src

#+RESULTS:
| name  |
|-------|
| Blue  |
| Red   |
| Green |

References may also be located in other files.

#+begin_src dsq :input countries.org:countries
SELECT name FROM {}
#+end_src

#+RESULTS:
| name        |
|-------------|
| Austria     |
| New Zealand |

Both references and regular files can be used as data sources in a single dsq source block. ob-dsq transparently writes the data of each reference to a temporary file for dsq to query.

#+begin_src dsq :input people.json colors countries.org:countries
SELECT people.name AS name, colors.name AS color, countries.name AS country
FROM {0} people
INNER JOIN {1} colors ON  people.id = colors.person_id
INNER JOIN {2} countries ON  people.id = countries.person_id
#+end_src

#+RESULTS:
| name  | color | country     |
|-------+-------+-------------|
| Alice | Blue  | Austria     |
| Bob   | Red   | New Zealand |
| Bob   | Green | New Zealand |

Querying results of Org source blocks

Here’s an example of a dsq source block querying the results of an Org source block that downloads JSON data from an online API.

#+name: comments
#+begin_src shell :results verbatim
curl https://jsonplaceholder.typicode.com/comments
#+end_src

#+begin_src dsq :input comments
SELECT
    email,
    substr(name, 0, 32) AS name
FROM {}
WHERE email LIKE 'f%'
ORDER BY email
LIMIT 5
#+end_src

#+RESULTS:
| email                         | name                            |
|-------------------------------+---------------------------------|
| [email protected]              | deleniti facere tempore et pers |
| [email protected] | rerum voluptate dolor           |
| [email protected]       | ratione architecto in est volup |
| [email protected]        | dicta deserunt tempore          |
| [email protected]    | doloribus quibusdam molestiae a |

Querying results of Elisp forms

This is where it get’s a little meta: it’s possible to pass Elisp forms to the :input header argument, as long as they evaluate to a either a single value that is a valid :input header argument, or a list of values, each of which is either a valid :input header argument or tabular data (that is, a list of lists).

Let’s unpack this step by step.

Single data source

Here’s an example that queries the colors Org table from above:

#+begin_src dsq :input (concat "col" "ors")
SELECT name FROM {}
#+end_src

#+RESULTS:
| name  |
|-------|
| Blue  |
| Red   |
| Green |

List of data sources

Here’s an Elisp form that evaluates to a list of data sources to query:

#+begin_src dsq :input `("people.json" ,(concat "col" "ors"))
SELECT people.name AS name, colors.name AS color
FROM {0} people
INNER JOIN {1} colors ON  people.id = colors.person_id
#+end_src

#+RESULTS:
| name  | color |
|-------+-------|
| Alice | Blue  |
| Bob   | Red   |
| Bob   | Green |

Mixed list of data sources and tabular data

It’s also possible to either define tabular data to query or to call functions that generate such data on the fly. Consider this a shortcut to referencing an Org source block that defines or generates data.

Note that for this to work, the tabular data needs to be an element of a wrapping list; it can’t be passed in as a :input header argument directly, because the individual “rows” would be considered one data source each, like in the examples above.

Here’s what that would look like for tabular data defined inline:

#+begin_src dsq :input '("people.json" (("person_id" "name") (1 "Blue") (2 "Red") (2 "Green")))
SELECT people.name AS name, colors.name AS color
FROM {0} people
INNER JOIN {1} colors ON  people.id = colors.person_id
#+end_src

#+RESULTS:
| name  | color |
|-------+-------|
| Alice | Blue  |
| Bob   | Red   |
| Bob   | Green |

Dynamically generated tabular data

And finally, let’s do an example that calls a function to generate the data to query on the fly.

Assume you have defined a simple org-extract function which uses the fabulous org-ql package to fetch headlines from Org files for an org-ql query and continues to extract their meta-data and custom properties as tabular data:

(defun org-extract (files &optional query)
  "Extract meta-data and custom properties for headings in FILES matching QUERY."
  (let ((headlines (org-ql-select files query))
        keywords)
    ;; collect unique property keywords
    (mapcar (lambda (headline)
              (cl-loop for (keyword . _value) on (cadr headline) by #'cddr
                       unless (member keyword keywords)
                       do (push keyword keywords)))
            headlines)
    (cons
     ;; header row: normalized column names
     (mapcar (lambda (keyword)
               (substring (downcase (symbol-name keyword)) 1))
             keywords)
     ;; data rows
     (mapcar (lambda (headline)
               (mapcar (lambda (keyword)
                         (let ((value (plist-get (cadr headline) keyword)))
                           (if (or (stringp value) (numberp value))
                               value
                             (format "%s" value))))
                       keywords))
             headlines))))

Let’s sum up story points of tickets that are still “ready” to be worked on in this week’s sprint.org (raw view) by assignee and component to find out if we’d better reassess the ticket distribution among the team:

#+begin_src dsq :input `(,(org-extract "sprint.org" '(todo "READY")))
SELECT assignee, component, SUM("story-points") AS points FROM {} GROUP BY assignee, component
#+end_src

#+RESULTS:
| assignee | component | points |
|----------+-----------+--------|
| Fritz    | Backend   |      5 |
| Fritz    | Frontend  |      2 |
| Rainer   | Frontend  |      1 |

Querying JSON data with irregular attributes

Queried objects in JSON data might contain “irregular” attributes. For example, in an array of people objects, one object might specify a phone attribute, while another does not. In the query result, all attributes found in the queried data will be flattened out and null-ed where needed.

#+begin_src dsq :input irregular.json
SELECT * FROM {}
#+end_src

#+RESULTS:
| id | name  | newsletter | phone          |
|----+-------+------------+----------------|
|  1 | Alice | false      | nil            |
|  2 | Bob   | nil        | 1-123-123-1234 |

Result types and formats

Org supports a wide range of options to customize the formatting of the results of a source code block execution. Here are two quick examples; please see the docs for more details.

#+begin_src dsq :input people.json :results list
SELECT * FROM {}
#+end_src

#+RESULTS:
- ("id" "name")
- (1 "Alice")
- (2 "Bob")
- (3 "Charles")

#+begin_src dsq :input people.json :results verbatim code
SELECT * FROM {}
#+end_src

#+RESULTS:
#+begin_src dsq
[{"id":1,"name":"Alice"},
{"id":2,"name":"Bob"},
{"id":3,"name":"Charles"}]
#+end_src

:header and :hlines header arguments

Use :header yes (default) to include a header row in table results. Use any other value to render the result without a header. This can be useful for result types other than table (see below for other options).

#+begin_src dsq :input people.json :header no
SELECT * FROM {}
#+end_src

#+RESULTS:
| 1 | Alice   |
| 2 | Bob     |
| 3 | Charles |

Use :hlines yes to include horizontal lines between rows in table results. Use any other value to render the result without horizontal rows (default).

#+begin_src dsq :input people.json :hlines yes
SELECT * FROM {}
#+end_src

#+RESULTS:
| id | name    |
|----+---------|
|  1 | Alice   |
|----+---------|
|  2 | Bob     |
|----+---------|
|  3 | Charles |

Customizing the rendering of null and false values

The rendering of null and false values can be customized using the :null-value and :false-value header arguments.

#+begin_src dsq :input irregular.json :null-value "?" :false-value "nope"
SELECT * FROM {}
#+end_src

#+RESULTS:
| id | name  | newsletter | phone          |
|----+-------+------------+----------------|
|  1 | Alice | nope       | ?              |
|  2 | Bob   | ?          | 1-123-123-1234 |

Variable expansion

Arguments can be passed to the source code block via :var FOO=BAR as usual. dsq source code blocks replace occurrences of $FOO with BAR before its evaluation.

Note that BAR can be a literal value or an Org reference.

Quoting and list concatenation might happen based on the type of BAR’s value. Please see below for details.

Simple literal values

Passing in a literal string value.

Note how the value Alice needs to be quoted manually in the query if it’s used as a string value there: ob-dsq doesn’t assume that passed in values are used as values in the query. In fact, you could use variable expansion to pass in whole query clauses, subqueries, etc.

#+begin_src dsq :input people.json :var name='Alice'
SELECT * FROM {} WHERE name = '$name'
#+end_src

#+RESULTS:
| id | name  |
|----+-------|
|  1 | Alice |

Passing in a literal number value.

#+begin_src dsq :input people.json :var id=2
SELECT * FROM {} WHERE id = $id
#+end_src

#+RESULTS:
| id | name |
|----+------|
|  2 | Bob  |

Org source blocks with literal values

Passing in the result of a source block that produces a literal value.

#+name: generate-name
#+begin_src ruby
'B' + 'o' + 'b'
#+end_src

#+begin_src dsq :input people.json :var name=generate-name
SELECT * FROM {} WHERE name = '$name'
#+end_src

#+RESULTS:
| id | name |
|----+------|
|  2 | Bob  |

Org lists

Passing in a list.

Note how the values are quoted and joined with commas. In contrast to passing in a literal value (see above), ob-dsq does assume that a passed in list is used as a list value in the query.

#+name: players
- Alice
- Bob

#+begin_src dsq :input people.json :var names=players
SELECT * FROM {} WHERE name in ($names)
#+end_src

#+RESULTS:
| id | name  |
|----+-------|
|  1 | Alice |
|  2 | Bob   |

Org tables

Passing in a table.

ob-dsq extracts the first column of the table and treats it as a list. Rules for lists apply here as well.

#+name: scores
| player  | score |
|---------+-------|
| Alice   |   100 |
| Charles |    20 |

#+begin_src dsq :input people.json :var names=scores
SELECT * FROM {} WHERE name in ($names)
#+end_src

#+RESULTS:
| id | name    |
|----+---------|
|  1 | Alice   |
|  3 | Charles |