emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
blob 49a455890c54a008b5ce2c4f5a822f2f7a007d6e 4275 bytes (raw)
name: contrib/lisp/orgtbl-sqlinsert.el 	 # note: path name is non-authoritative(*)

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
;;; orgtbl-sqlinsert.el --- orgtbl to SQL insert statements.

;; Copyright (C) 2008-2018  Free Software Foundation

;; Author: Jason Riedy <jason@acm.org>
;; Keywords: org, tables, sql

;; This file is not part of GNU Emacs.

;; 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 chosen 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))
         (firstheader t)
         (*orgtbl-default-fmt* 'orgtbl-sql-strip-and-quote)
	 (params2
	  (list
	   :sqlname (plist-get params :sqlname)
	   :tstart (lambda () (concat (if nowebname
					  (format "<<%s>>= \n" nowebname)
					"")
				      "BEGIN TRANSACTION;"))
	   :tend (lambda () (concat "COMMIT;" (if nowebname "\n@ " "")))
	   :hfmt (lambda (f) (progn (if firstheader (push f hdrlist) "")))
	   :hlfmt (lambda (&rest cells) (setq firstheader nil))
	   :lstart (lambda () (concat "INSERT INTO "
				      sqlname "( "
				      (mapconcat 'identity (reverse hdrlist)
						 ", ")
				      " )" (if breakvals "\n" " ")
				      "VALUES ( "))
	   :lend " );"
	   :sep " , "
	   :hline nil
	   :remove-nil-lines t))
	 (params (org-combine-plists params2 params))
         (sqlname (plist-get params :sqlname)))
    (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

debug log:

solving 49a455890 ...
found 49a455890 in https://git.savannah.gnu.org/cgit/emacs/org-mode.git

(*) Git path names are given by the tree(s) the blob belongs to.
    Blobs themselves have no identifier aside from the hash of its contents.^

Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).