noteed · January 9, 2023 21:53 · Aug 22, 2021 · Sep 13, 2020 · Apr 7, 2020 · Nov 6, 2019
diff --git a/html.jq b/html.jq
@@ -0,0 +1,21 @@
+def td: "<td>\(.)</td>";
+def th: "<th>\(.)</th>";
+def tr:"<tr>\(.)</tr>";
+def table: "<table>\(.)</table>";
+def tbody: "<tbody>\(.)</tbody>";
+def thead: "<thead>\(.)</thead>";
+
+def render_as_table:
+ "\(
+    to_entries | map(.key|th) | join("") | tr|thead
+  )\n\(
+    to_entries | map("\(
+      .value |
+      if type=="object" then
+        render_as_table|td
+      elif type=="array" then
+        render_as_table|td
+      else
+        td end
+    )") | join("") | tr | tbody
+  )" | table ;
diff --git a/README.md b/README.md
@@ -1,4 +1,4 @@
-# Pandoc, YAML, JSON, CSV, Sqlite
+# Pandoc, YAML, JSON, CSV, SQLite
 
 This gists shows a little idea: use Pandoc to format data into HTML, or any
 other output format supported by Pandoc. This uses the ability of Pandoc to
@@ -9,6 +9,11 @@ It also shows a few scripts to use other data formats than YAML and extract
 metadata blocks as JSON. Note that multiple metadata blocks can be present
 within a single document.
 
+See also [this
+Gist](https://gist.github.com/noteed/012e9e8d9744f7204fa2abfee629ce00) to see a
+small Scotty-based web server using full-text search against a SQLite database
+containing Markdown files.
+
 
 ## Self-contained Pandoc document
 
@@ -67,11 +72,11 @@ used because Pandoc is left-biased when merging multiple metadata blocks.
 Several scripts are provided to use other data format. For instance:
 
 - From `hosts.json` (one object per line) to CSV,
-- From CSV to a Sqlite database,
-- From a Sqlite database to JSON again (a list of objects),
-- From a Sqlite database to YAML (using the previous script).
+- From CSV to a SQLite database,
+- From a SQLite database to JSON again (a list of objects),
+- From a SQLite database to YAML (using the previous script).
 
-This shows that the data rendered by Pandoc can come from Sqlite with minimal
+This shows that the data rendered by Pandoc can come from SQLite with minimal
 efforts.
 
 
@@ -84,3 +89,13 @@ using a dedicated template (here, `metadata.tpl`):
 $ pandoc --template metadata.tpl document.md
 {"title":"The document title","host":[{"ip":"172.17.0.1","name":"host-1"},{"ip":"172.17.0.2","name":"host-2"}]}
 ```
+
+
+## TODO
+
+Rendering JSON as a nested table (with the appropriate definitions in
+`html.jq`):
+
+```
+$ echo '{"a": {"b": {"c": 2}}, "d": 3}' | jq -r 'include "html"; render_as_table'
+```
diff --git a/.gitignore b/.gitignore
@@ -0,0 +1,2 @@
+hosts.csv
+hosts.db
diff --git a/README.md b/README.md
@@ -1,6 +1,13 @@
 # Pandoc, YAML, JSON, CSV, Sqlite
 
-Use Pandoc to format data.
+This gists shows a little idea: use Pandoc to format data into HTML, or any
+other output format supported by Pandoc. This uses the ability of Pandoc to
+uses templates, and to receive data as YAML either from a separate file, or
+within a metadata block contained in the document itself..
+
+It also shows a few scripts to use other data formats than YAML and extract
+metadata blocks as JSON. Note that multiple metadata blocks can be present
+within a single document.
 
 
 ## Self-contained Pandoc document
@@ -26,12 +33,12 @@ Some hosts:
 ```
 
 The result is a normal Markdown document that can be processed with Pandoc as
-usual.
+usual, e.g. to produce an HTML page.
 
 
 ## Separate data
 
-The data can also come from an external file:
+The data can also come from an external file (here, `hosts.yml`):
 
 ```
 $ pandoc --template document.md hosts.yml document.md
@@ -57,9 +64,23 @@ used because Pandoc is left-biased when merging multiple metadata blocks.
 
 ## Other formats
 
-Several scripts are provided to change data format. For instance:
+Several scripts are provided to use other data format. For instance:
 
 - From `hosts.json` (one object per line) to CSV,
 - From CSV to a Sqlite database,
 - From a Sqlite database to JSON again (a list of objects),
 - From a Sqlite database to YAML (using the previous script).
+
+This shows that the data rendered by Pandoc can come from Sqlite with minimal
+efforts.
+
+
+## Extract metadata block
+
+It is also possible to extract the data "stored" within a metadata block by
+using a dedicated template (here, `metadata.tpl`):
+
+```
+$ pandoc --template metadata.tpl document.md
+{"title":"The document title","host":[{"ip":"172.17.0.1","name":"host-1"},{"ip":"172.17.0.2","name":"host-2"}]}
+```
diff --git a/metadata.tpl b/metadata.tpl
@@ -0,0 +1 @@
+$meta-json$
diff --git a/README.md b/README.md
@@ -1 +1,65 @@
-#
+# Pandoc, YAML, JSON, CSV, Sqlite
+
+Use Pandoc to format data.
+
+
+## Self-contained Pandoc document
+
+The `document.md` file contains data in its metadata YAML block and is its own
+template:
+
+```
+$ pandoc --template document.md document.md
+---
+title: The document title
+host:
+- name: host-1
+  ip: 172.17.0.1
+- name: host-2
+  ip: 172.17.0.2
+---
+
+Some hosts:
+
+- host-1: 172.17.0.1
+- host-2: 172.17.0.2
+```
+
+The result is a normal Markdown document that can be processed with Pandoc as
+usual.
+
+
+## Separate data
+
+The data can also come from an external file:
+
+```
+$ pandoc --template document.md hosts.yml document.md
+---
+title: The document title
+host:
+- name: host-1
+  ip: 172.17.0.1
+- name: host-2
+  ip: 172.17.0.2
+---
+
+Some hosts:
+
+- host-1: 172.17.0.1
+- host-2: 172.17.0.2
+- host-3: 172.17.0.3
+```
+
+Note: here the `hosts` key in the metadata provided by `document.md` is not
+used because Pandoc is left-biased when merging multiple metadata blocks.
+
+
+## Other formats
+
+Several scripts are provided to change data format. For instance:
+
+- From `hosts.json` (one object per line) to CSV,
+- From CSV to a Sqlite database,
+- From a Sqlite database to JSON again (a list of objects),
+- From a Sqlite database to YAML (using the previous script).
diff --git a/csv-to-sqlite.sh b/csv-to-sqlite.sh
@@ -0,0 +1,7 @@
+#! /usr/bin/env bash
+
+sqlite3 hosts.db -cmd '.mode csv' '.import hosts.csv hosts'
+
+# Demonstrate the import succeeded
+sqlite3 hosts.db '.schema hosts'
+sqlite3 hosts.db 'select * from hosts'
diff --git a/hosts.json b/hosts.json
@@ -0,0 +1,3 @@
+{ "name":"host-1", "ip":"172.17.0.1" }
+{ "name":"host-2", "ip":"172.17.0.2" }
+{ "name":"host-3", "ip":"172.17.0.3" }
diff --git a/hosts.yml b/hosts.yml
@@ -1,9 +0,0 @@
----
-host:
-- name: host-1
-  ip: 172.17.0.1
-- name: host-2
-  ip: 172.17.0.2
-- name: host-3
-  ip: 172.17.0.3
----

diff --git a/json-to-csv.sh b/json-to-csv.sh
@@ -0,0 +1,7 @@
+#! /usr/bin/env bash
+
+# Construct the CSV header
+head -1 hosts.json | jq -r 'keys | @csv' > hosts.csv
+
+# Extract rows
+cat hosts.json | jq -r '[.[]] | @csv' >> hosts.csv
diff --git a/sqlite-to-json.sh b/sqlite-to-json.sh
@@ -0,0 +1,5 @@
+#! /usr/bin/env bash
+
+sqlite3 hosts.db \
+  "select json_group_array(json_object('ip', ip, 'name', name)) as host
+  from (select * from hosts)" | jq .
diff --git a/sqlite-to-yaml.sh b/sqlite-to-yaml.sh
@@ -0,0 +1,6 @@
+#! /usr/bin/env bash
+
+echo '---'
+echo host:
+./sqlite-to-json.sh | jq -r -f to-yaml.jq
+echo '---'
diff --git a/to-yaml.jq b/to-yaml.jq
@@ -0,0 +1,16 @@
+def y:
+  (objects | to_entries[] | (.value | type) as $type |
+    if $type == "array" then
+      "\(.key):", (.value | y)
+    elif $type == "object" then
+      "\(.key):", "  \(.value | y)"
+    else
+      "\(.key): \(.value)"
+    end
+  )
+  // (arrays | select(length > 0)[] | [y] |
+    "  - \(.[0])", "    \(.[1:][])"
+  )
+  // .
+  ;
+y
diff --git a/document.md b/document.md
@@ -0,0 +1,14 @@
+---
+title: The document title
+host:
+- name: host-1
+  ip: 172.17.0.1
+- name: host-2
+  ip: 172.17.0.2
+---
+
+Some hosts:
+
+$for(host)$
+- $host.name$: $host.ip$
+$endfor$
diff --git a/hosts.yml b/hosts.yml
@@ -0,0 +1,9 @@
+---
+host:
+- name: host-1
+  ip: 172.17.0.1
+- name: host-2
+  ip: 172.17.0.2
+- name: host-3
+  ip: 172.17.0.3
+---
diff --git a/README.md b/README.md
@@ -0,0 +1 @@
+#