From mboxrd@z Thu Jan 1 00:00:00 1970 From: Jason Riedy Subject: [PATCH] Add a contrib/lisp entry for converting Org tables to SQL insertions. Date: Wed, 16 Apr 2008 14:41:38 -0700 Message-ID: <1208382098-7974-1-git-send-email-jason@acm.org> Return-path: Received: from mailman by lists.gnu.org with tmda-scanned (Exim 4.43) id 1JmFNz-000106-Vz for emacs-orgmode@gnu.org; Wed, 16 Apr 2008 17:41:48 -0400 Received: from exim by lists.gnu.org with spam-scanned (Exim 4.43) id 1JmFNz-0000zm-6n for emacs-orgmode@gnu.org; Wed, 16 Apr 2008 17:41:47 -0400 Received: from [199.232.76.173] (helo=monty-python.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1JmFNz-0000zg-0j for emacs-orgmode@gnu.org; Wed, 16 Apr 2008 17:41:47 -0400 Received: from a.mail.sonic.net ([64.142.16.245]) by monty-python.gnu.org with esmtps (TLS-1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.60) (envelope-from ) id 1JmFNy-0007wR-HY for emacs-orgmode@gnu.org; Wed, 16 Apr 2008 17:41:46 -0400 Received: from localhost.localdomain (209-204-163-7.vpn.sonic.net [209.204.163.7]) (authenticated bits=0) by a.mail.sonic.net (8.13.8.Beta0-Sonic/8.13.7) with ESMTP id m3GLfe3c003687 for ; Wed, 16 Apr 2008 14:41:44 -0700 List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: emacs-orgmode@gnu.org The string safety is a bit iffy, but it works for converting TeX descriptions into SQL-safe strings. Signed-off-by: Jason Riedy --- 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 + + * lisp/orgtbl-sqlinsert.el: New file. + * README: Add to list. + 2008-04-07 Carsten Dominik * 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 +;; 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 . + +;;; 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