Org Code Block cc/org-table-to-sqlite

cc-org-table-to-sql.org

cc/org-table-to-sqlite

Org Babel code block to import an Org table into an in-memory SQLite DB to enable SQL queries on aforementioned table.

Parameters

• table : Org table object The Org table object is referenced via the #+NAME value assigned to the table. The table must have a header row identifying each column. This name value must be a SQL-legal name.
• table-name : string Name of table in string form. It must be identical to the name of the table parameter above.

Example

id	n	city
1	5	Barcelona
2	9	New York
3	10.0	San Francisco
4	2	Seoul
5	10	Berlin
6	7	Singapore

Code Block

(defun cc/org-extract-table-header (table)
  "Extract header row from the Org table TABLE.

This function presumes that TABLE has both header and data sections.
This method is intended to be called from an Org Babel Elisp code block."
  (cl-first table))

(defun cc/org-extract-table-data (table)
  "Extract data rows from the Org table TABLE.

This function presumes that TABLE has both header and data sections.
This method is intended to be called from an Org Babel Elisp code block."
  (cdr table))

(defun cc/list-integerp (data)
  "Predicate to test if all elements in the list DATA are of integer type.

Note this function is O(n) and can potentially be slow for large tables."
  (seq-reduce '(lambda (x y) (and x y)) (mapcar 'integerp data) t))

(defun cc/list-floatp-or-integerp (data)
  "Predicate to test if all elements in the list DATA are of float or integer type.

Note this function is O(n) and can potentially be slow for large tables."
  (seq-reduce '(lambda (x y) (and x y)) (mapcar '(lambda (x) (or (floatp x) (integerp x))) data) t))

(defun cc/list-stringp (data)
  "Predicate to test if all elements in the list DATA are of string type.

Note this function is O(n) and can potentially be slow for large tables."
  (seq-reduce '(lambda (x y) (or x y)) (mapcar 'stringp data) nil))

(defun cc/table-column-sqlite-type (data)
  "Determine the SQL type of the elements in the list DATA.

Note this function is O(n) and can potentially be slow for large tables."
  (cond ((cc/list-integerp data) "INT")
        ((cc/list-floatp-or-integerp data) "REAL")
        ((cc/list-stringp data) "TEXT")
        (t "TEXT")))

(defun cc/org-table-sqllite-table-create (table table-name)
  "Emit Org Babel SQLite code block that imports Org table TABLE with name TABLE-NAME.

The SQLite table configured to be stored in-memory. A SQL query line is provided
to be subsequently edited.

This method is intended to be called from an Org Babel Elisp code block.
Returns string of SQLite statements implementing above described behavior."
  (let* ((header (cc/org-extract-table-header table))
         (data (cc/org-extract-table-data table))
         (rowsize (length data))
         (colsize (length (cl-first data))))

    (setq i 0)
    (setq temp-list (list))

    (while (< i colsize)
      (setq column-data (mapcar '(lambda (x) (nth i x)) (cdr table)))
      (setq temp-list (cons (cc/table-column-sqlite-type column-data) temp-list))
      (setq i (1+ i)))

    (setq formatted-arg-list (cl-mapcar (lambda (x y) (format "%s %s" x y )) header (reverse temp-list)))

    (setq result-list (list))

    (push (format "#+begin_src sqlite :db \":memory:\" :var orgtable=%s :colnames yes" table-name)
          result-list)

    (push (format "drop table if exists %s;" table-name)
          result-list)

    (push (format "create table %s(%s);"
                  table-name
                  (mapconcat 'identity formatted-arg-list ", "))
          result-list)

    (push (format ".import $orgtable %s" table-name)
          result-list)

    (push "\n-- Edit SQL Query Below" result-list)

    (push (format "select %s from %s;" (mapconcat 'identity header ", ") table-name)
          result-list)

    (push "#+end_src"
          result-list)

    (print (mapconcat 'identity (reverse result-list) "\n"))))

(cc/org-table-sqllite-table-create table table-name)