[Orgmode] [PATCH] Add a contrib/lisp entry for converting Org tables to SQL insertions.

[Jason Riedy]

The string safety is a bit iffy, but it works for converting
TeX descriptions into SQL-safe strings.

Signed-off-by: Jason Riedy <address@hidden>
---
  Needs the table-format-functions series.  Also available as
  git://repo.or.cz/org-mode/ejr.git orgtbl-sqlinsert

  And I forgot to mention that the previous patch series is
  available from
  git://repo.or.cz/org-mode/ejr.git table-format-functions

 contrib/ChangeLog                |    5 ++
 contrib/README                   |    1 +
 contrib/lisp/orgtbl-sqlinsert.el |  114 ++++++++++++++++++++++++++++++++++++++
 3 files changed, 120 insertions(+), 0 deletions(-)
 create mode 100644 contrib/lisp/orgtbl-sqlinsert.el

diff --git a/contrib/ChangeLog b/contrib/ChangeLog
index 81c142b..5db4bb3 100644
--- a/contrib/ChangeLog
+++ b/contrib/ChangeLog
@@ -1,3 +1,8 @@
+2008-04-15  Jason Riedy  <address@hidden>
+
+       * lisp/orgtbl-sqlinsert.el: New file.
+       * README: Add to list.
+
 2008-04-07  Carsten Dominik  <address@hidden>
 
        * lisp/org-iswitchb.el: File removed, because the functionality is
diff --git a/contrib/README b/contrib/README
index e564cf9..6b49863 100644
--- a/contrib/README
+++ b/contrib/README
@@ -24,6 +24,7 @@ org-registry.el      --- a registry for Org links
 org2rem.el           --- Convert org appointments into reminders
 org-screen.el        --- visit screen sessions through Org-mode links
 org-toc.el          --- Table of contents for Org-mode buffer
+orgtbl-sqlinsert.el  --- Convert Org-mode tables to SQL insertions.
 
 PACKAGES
 ========
diff --git a/contrib/lisp/orgtbl-sqlinsert.el b/contrib/lisp/orgtbl-sqlinsert.el
new file mode 100644
index 0000000..869f74f
--- /dev/null
+++ b/contrib/lisp/orgtbl-sqlinsert.el
@@ -0,0 +1,114 @@
+;;; orgtbl-sqlinsert.el --- orgtbl to SQL insert statements.
+
+;; Copyright (C) 2008  Free Software Foundation
+
+;; Author: Jason Riedy <address@hidden>
+;; Keywords: org, tables, sql
+
+;; This program is free software; you can redistribute it and/or modify
+;; it under the terms of the GNU General Public License as published by
+;; the Free Software Foundation, either version 3 of the License, or
+;; (at your option) any later version.
+
+;; This program is distributed in the hope that it will be useful,
+;; but WITHOUT ANY WARRANTY; without even the implied warranty of
+;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+;; GNU General Public License for more details.
+
+;; You should have received a copy of the GNU General Public License
+;; along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+;;; Commentary:
+
+;; Converts an orgtbl to a sequence of SQL insertion commands.
+;; Table cells are quoted and escaped very conservatively.
+
+;;; Code:
+
+(defun orgtbl-to-sqlinsert (table params)
+  "Convert the orgtbl-mode TABLE to SQL insert statements.
+TABLE is a list, each entry either the symbol `hline' for a horizontal
+separator line, or a list of fields for that line.
+PARAMS is a property list of parameters that can influence the conversion.
+
+Names and strings are modified slightly by default.  Single-ticks
+are doubled as per SQL's standard mechanism.  Backslashes and
+dollar signs are deleted.  And tildes are changed to spaces.
+These modifications were chosed for use with TeX.  See
+ORGTBL-SQL-STRIP-AND-QUOTE.
+
+Supports all parameters from ORGTBL-TO-GENERIC.  New to this function
+are:
+
+:sqlname   The name of the database table; defaults to the name of the
+           target region.
+
+:nowebname If not nil, used as a wrapping noweb fragment name.
+
+The most important parameters of ORGTBL-TO-GENERIC for SQL are:
+
+:splice    When set to t, return only insert statements, don't wrap
+           them in a transaction.  Default is nil.
+
+:tstart, :tend
+           The strings used to begin and commit the transaction.
+
+:hfmt      A function that gathers the quoted header names into a
+           dynamically scoped variable HDRLIST.  Probably should
+           not be changed by the user.
+
+The general parameters :skip and :skipcols have already been applied when
+this function is called."
+  (let* (hdrlist
+        (alignment (mapconcat (lambda (x) (if x "r" "l"))
+                              org-table-last-alignment ""))
+        (nowebname (plist-get params :nowebname))
+        (breakvals (plist-get params :breakvals))
+        (params2
+         (list
+          :sqlname name
+          :tstart (lambda () (concat (if nowebname
+                                         (format "<<%s>>= \n" nowebname)
+                                       "")
+                                     "BEGIN TRANSACTION;"))
+          :tend (lambda () (concat "COMMIT;" (if nowebname "\n@ " "")))
+          :fmt (lambda (str) (orgtbl-sql-strip-and-quote str))
+;         :hfmt (lambda (f) (push (concat "[" f "]") hdrlist) "")
+          :hfmt (lambda (f) (push f hdrlist) "")
+          :hlfmt (lambda (lst) nil)
+          :lstart (lambda () (concat "INSERT INTO "
+                                     (plist-get params :sqlname) "( "
+                                     (mapconcat 'identity (reverse hdrlist)
+                                                ", ")
+                                     " )" (if breakvals "\n" " ")
+                                     "VALUES ( "))
+          :lend " );"
+          :sep " , "
+          :hline nil
+          :remove-nil-lines t))
+        (params (org-combine-plists params2 params)))
+    (orgtbl-to-generic table params)))
+
+(defun orgtbl-sql-quote (str)
+  "Convert single ticks to doubled single ticks and wrap in single ticks."
+  (concat "'" (mapconcat 'identity (split-string str "'") "''") "'"))
+
+(defun orgtbl-sql-strip-dollars-escapes-tildes (str)
+  "Strip dollarsigns and backslash escapes, replace tildes with spaces."
+  (mapconcat 'identity
+            (split-string (mapconcat 'identity
+                                     (split-string str "\\$\\|\\\\")
+                                     "")
+                          "~")
+            " "))
+
+(defun orgtbl-sql-strip-and-quote (str)
+  "Apply ORGBTL-SQL-QUOTE and ORGTBL-SQL-STRIP-DOLLARS-ESCAPES-TILDES
+to sanitize STR for use in SQL statements."
+  (cond ((stringp str)
+         (orgtbl-sql-quote (orgtbl-sql-strip-dollars-escapes-tildes str)))
+        ((sequencep str) (mapcar 'orgtbl-sql-strip-and-quote str))
+        (t nil)))
+
+(provide 'orgtbl-sqlinsert)
+;;; orgtbl-sqlinsert.el ends here
-- 
1.5.5.rc1.121.g1594