emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
blob 82e9e6a68ba2ea69e3577b7aa0266c0e710abd30 6074 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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
 
;;; orgtbl-sqlinsert.el --- orgtbl to SQL insert statements.

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

;; Author: Jason Riedy <jason@acm.org>,
;;         modified by Thierry Banel  tbanelwebmin at free dot fr
;; 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.

;;  Example:
;;  Using this table as source,
;;  adding somewhere in the same file a block like this:
;;     -- BEGIN RECEIVE ORGTBL faraway
;;     -- END RECEIVE ORGTBL faraway
;;  and typing C-c C-c on the first "pipe" of the table,
;;  fills the BEGIN RECEIVE ORGTBL block
;;  
;;  #+ORGTBL: SEND faraway orgtbl-to-sqlinsert :splice nil :skipcols (5) :datefmt "'%Y-%m-%d %H:%M:%S'" :sqlname "thetable"
;;  | date                    |  quty | color  | status      | useless |
;;  |-------------------------+-------+--------+-------------+---------|
;;  | [2013-04-17 wed. 11:34] | 123.4 | red    | yes ok nogo | a       |
;;  | [2014-04-18 fri.]       |  75.3 | yellow | no ko go    | bb      |
;;  | <2015-04-19 sun. 11:35> | 355.1 | blue   | no ok go    | ccc     |
;;  | <2016-04-20 wed.>       | 233.7 | white  | yes ko go   | dddd    |
;;  
;;  
;;  -- BEGIN RECEIVE ORGTBL faraway
;;  BEGIN TRANSACTION;
;;  INSERT INTO thetable( date, quty, color, status ) VALUES ( '2013-04-17 11:34:00' , '123.4' , 'red' , 'yes ok nogo' );
;;  INSERT INTO thetable( date, quty, color, status ) VALUES ( '2014-04-18 00:00:00' , '75.3' , 'yellow' , 'no ko go' );
;;  INSERT INTO thetable( date, quty, color, status ) VALUES ( '2015-04-19 11:35:00' , '355.1' , 'blue' , 'no ok go' );
;;  INSERT INTO thetable( date, quty, color, status ) VALUES ( '2016-04-20 00:00:00' , '233.7' , 'white' , 'yes ko go' );
;;  COMMIT;
;;  -- END RECEIVE ORGTBL faraway

;;; 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.

:datefmt   The format used for converting Ord dates
           by defaut it is '%Y-%m-%d %H:%M:%S', suitable for Sqlite

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
	   :datefmt "'%Y-%m-%d %H:%M:%S'"
	   :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 ((string-match org-ts-regexp0 str)
         (format-time-string (plist-get params :datefmt) (org-time-string-to-time str)))
        ((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 82e9e6a ...
found 82e9e6a in https://list.orgmode.org/orgmode/52C82570.6080607@free.fr/
found b00c93d in https://git.savannah.gnu.org/cgit/emacs/org-mode.git
preparing index
index prepared:
100644 b00c93d390f578d618b259c2ca4fa5a551a170dd	contrib/lisp/orgtbl-sqlinsert.el

applying [1/1] https://list.orgmode.org/orgmode/52C82570.6080607@free.fr/
diff --git a/contrib/lisp/orgtbl-sqlinsert.el b/contrib/lisp/orgtbl-sqlinsert.el
index b00c93d..82e9e6a 100644

1:26: trailing whitespace.
;;  
1:34: trailing whitespace.
;;  
1:35: trailing whitespace.
;;  
Checking patch contrib/lisp/orgtbl-sqlinsert.el...
Applied patch contrib/lisp/orgtbl-sqlinsert.el cleanly.
warning: 3 lines add whitespace errors.

index at:
100644 82e9e6a68ba2ea69e3577b7aa0266c0e710abd30	contrib/lisp/orgtbl-sqlinsert.el

(*) 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).