* [PATCH] Add a contrib/lisp entry for converting Org tables to SQL insertions.
@ 2008-04-16 21:41 Jason Riedy
0 siblings, 0 replies; only message in thread
From: Jason Riedy @ 2008-04-16 21:41 UTC (permalink / raw)
To: emacs-orgmode
The string safety is a bit iffy, but it works for converting
TeX descriptions into SQL-safe strings.
Signed-off-by: Jason Riedy <jason@acm.org>
---
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 <jason@acm.org>
+
+ * lisp/orgtbl-sqlinsert.el: New file.
+ * README: Add to list.
+
2008-04-07 Carsten Dominik <dominik@science.uva.nl>
* 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 <jason@acm.org>
+;; 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
^ permalink raw reply related [flat|nested] only message in thread
only message in thread, other threads:[~2008-04-16 21:41 UTC | newest]
Thread overview: (only message) (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2008-04-16 21:41 [PATCH] Add a contrib/lisp entry for converting Org tables to SQL insertions Jason Riedy
Code repositories for project(s) associated with this external index
https://git.savannah.gnu.org/cgit/emacs.git
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is an external index of several public inboxes,
see mirroring instructions on how to clone and mirror
all data and code used by this external index.