sql.el 202 KB
Newer Older
1
;;; sql.el --- specialized comint.el for SQL interpreters  -*- lexical-binding: t -*-
Richard M. Stallman's avatar
Richard M. Stallman committed
2

3
;; Copyright (C) 1998-2014 Free Software Foundation, Inc.
Richard M. Stallman's avatar
Richard M. Stallman committed
4

5
;; Author: Alex Schroeder <alex@gnu.org>
6
;; Maintainer: Michael Mauger <michael@mauger.com>
7
;; Version: 3.4
8
;; Keywords: comm languages processes
9
;; URL: http://savannah.gnu.org/projects/emacs/
Richard M. Stallman's avatar
Richard M. Stallman committed
10 11 12

;; This file is part of GNU Emacs.

13
;; GNU Emacs is free software: you can redistribute it and/or modify
Richard M. Stallman's avatar
Richard M. Stallman committed
14
;; it under the terms of the GNU General Public License as published by
15 16
;; the Free Software Foundation, either version 3 of the License, or
;; (at your option) any later version.
Richard M. Stallman's avatar
Richard M. Stallman committed
17 18 19 20 21 22 23

;; GNU Emacs 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
24
;; along with GNU Emacs.  If not, see <http://www.gnu.org/licenses/>.
Richard M. Stallman's avatar
Richard M. Stallman committed
25 26 27

;;; Commentary:

28
;; Please send bug reports and bug fixes to the mailing list at
29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
;; help-gnu-emacs@gnu.org.  If you want to subscribe to the mailing
;; list, see the web page at
;; http://lists.gnu.org/mailman/listinfo/help-gnu-emacs for
;; instructions.  I monitor this list actively.  If you send an e-mail
;; to Alex Schroeder it usually makes it to me when Alex has a chance
;; to forward them along (Thanks, Alex).

;; This file provides a sql-mode and a sql-interactive-mode.  The
;; original goals were two simple modes providing syntactic
;; highlighting.  The interactive mode had to provide a command-line
;; history; the other mode had to provide "send region/buffer to SQL
;; interpreter" functions.  "simple" in this context means easy to
;; use, easy to maintain and little or no bells and whistles.  This
;; has changed somewhat as experience with the mode has accumulated.

;; Support for different flavors of SQL and command interpreters was
;; available in early versions of sql.el.  This support has been
;; extended and formalized in later versions.  Part of the impetus for
;; the improved support of SQL flavors was borne out of the current
48
;; maintainers consulting experience.  In the past twenty years, I
49 50
;; have used Oracle, Sybase, Informix, MySQL, Postgres, and SQLServer.
;; On some assignments, I have used two or more of these concurrently.
Richard M. Stallman's avatar
Richard M. Stallman committed
51 52 53 54

;; If anybody feels like extending this sql mode, take a look at the
;; above mentioned modes and write a sqlx-mode on top of this one.  If
;; this proves to be difficult, please suggest changes that will
55 56
;; facilitate your plans.  Facilities have been provided to add
;; products and product-specific configuration.
Richard M. Stallman's avatar
Richard M. Stallman committed
57 58

;; sql-interactive-mode is used to interact with a SQL interpreter
59
;; process in a SQLi buffer (usually called `*SQL*').  The SQLi buffer
60 61 62
;; is created by calling a SQL interpreter-specific entry function or
;; sql-product-interactive.  Do *not* call sql-interactive-mode by
;; itself.
Richard M. Stallman's avatar
Richard M. Stallman committed
63 64

;; The list of currently supported interpreters and the corresponding
65
;; entry function used to create the SQLi buffers is shown with
Richard M. Stallman's avatar
Richard M. Stallman committed
66 67 68 69 70 71 72 73
;; `sql-help' (M-x sql-help).

;; Since sql-interactive-mode is built on top of the general
;; command-interpreter-in-a-buffer mode (comint mode), it shares a
;; common base functionality, and a common set of bindings, with all
;; modes derived from comint mode.  This makes these modes easier to
;; use.

74 75
;; sql-mode can be used to keep editing SQL statements.  The SQL
;; statements can be sent to the SQL process in the SQLi buffer.
Richard M. Stallman's avatar
Richard M. Stallman committed
76 77

;; For documentation on the functionality provided by comint mode, and
78
;; the hooks available for customizing it, see the file `comint.el'.
Richard M. Stallman's avatar
Richard M. Stallman committed
79

80 81
;; Hint for newbies: take a look at `dabbrev-expand', `abbrev-mode', and
;; `imenu-add-menubar-index'.
Richard M. Stallman's avatar
Richard M. Stallman committed
82 83 84

;;; Bugs:

85 86 87 88 89 90
;; sql-ms now uses osql instead of isql.  Osql flushes its error
;; stream more frequently than isql so that error messages are
;; available.  There is no prompt and some output still is buffered.
;; This improves the interaction under Emacs but it still is somewhat
;; awkward.

Paul Eggert's avatar
Paul Eggert committed
91
;; Quoted identifiers are not supported for highlighting.  Most
92 93 94
;; databases support the use of double quoted strings in place of
;; identifiers; ms (Microsoft SQLServer) also supports identifiers
;; enclosed within brackets [].
Richard M. Stallman's avatar
Richard M. Stallman committed
95

96 97 98 99 100 101
;;; Product Support:

;; To add support for additional SQL products the following steps
;; must be followed ("xyz" is the name of the product in the examples
;; below):

Michael Mauger's avatar
Michael Mauger committed
102
;; 1) Add the product to the list of known products.
103

Michael Mauger's avatar
Michael Mauger committed
104 105
;;     (sql-add-product 'xyz "XyzDB"
;;     	                '(:free-software t))
106

Michael Mauger's avatar
Michael Mauger committed
107 108 109
;; 2) Define font lock settings.  All ANSI keywords will be
;;    highlighted automatically, so only product specific keywords
;;    need to be defined here.
110

Michael Mauger's avatar
Michael Mauger committed
111 112 113 114
;;     (defvar my-sql-mode-xyz-font-lock-keywords
;;       '(("\\b\\(red\\|orange\\|yellow\\)\\b"
;;          . font-lock-keyword-face))
;;       "XyzDB SQL keywords used by font-lock.")
115

Michael Mauger's avatar
Michael Mauger committed
116 117 118
;;     (sql-set-product-feature 'xyz
;;                              :font-lock
;;                              'my-sql-mode-xyz-font-lock-keywords)
119

Michael Mauger's avatar
Michael Mauger committed
120 121 122 123
;; 3) Define any special syntax characters including comments and
;;    identifier characters.

;;     (sql-set-product-feature 'xyz
124
;;                              :syntax-alist ((?# . "_")))
Michael Mauger's avatar
Michael Mauger committed
125 126 127 128 129 130

;; 4) Define the interactive command interpreter for the database
;;    product.

;;     (defcustom my-sql-xyz-program "ixyz"
;;       "Command to start ixyz by XyzDB."
131 132 133
;;       :type 'file
;;       :group 'SQL)
;;
Michael Mauger's avatar
Michael Mauger committed
134 135 136 137 138 139 140 141 142 143 144
;;     (sql-set-product-feature 'xyz
;;                              :sqli-program 'my-sql-xyz-program)
;;     (sql-set-product-feature 'xyz
;;                              :prompt-regexp "^xyzdb> ")
;;     (sql-set-product-feature 'xyz
;;                              :prompt-length 7)

;; 5) Define login parameters and command line formatting.

;;     (defcustom my-sql-xyz-login-params '(user password server database)
;;       "Login parameters to needed to connect to XyzDB."
145
;;       :type 'sql-login-params
146
;;       :group 'SQL)
Michael Mauger's avatar
Michael Mauger committed
147 148 149
;;
;;     (sql-set-product-feature 'xyz
;;                              :sqli-login 'my-sql-xyz-login-params)
150

Michael Mauger's avatar
Michael Mauger committed
151 152 153 154 155 156 157
;;     (defcustom my-sql-xyz-options '("-X" "-Y" "-Z")
;;       "List of additional options for `sql-xyz-program'."
;;       :type '(repeat string)
;;       :group 'SQL)
;;
;;     (sql-set-product-feature 'xyz
;;                              :sqli-options 'my-sql-xyz-options))
158

159
;;     (defun my-sql-comint-xyz (product options)
Michael Mauger's avatar
Michael Mauger committed
160
;;       "Connect ti XyzDB in a comint buffer."
161 162 163
;;
;;         ;; Do something with `sql-user', `sql-password',
;;         ;; `sql-database', and `sql-server'.
164 165
;;         (let ((params
;;                (append
166
;;           (if (not (string= "" sql-user))
167 168 169 170 171 172 173 174
;;                     (list "-U" sql-user))
;;                 (if (not (string= "" sql-password))
;;                     (list "-P" sql-password))
;;                 (if (not (string= "" sql-database))
;;                     (list "-D" sql-database))
;;                 (if (not (string= "" sql-server))
;;                     (list "-S" sql-server))
;;                 options)))
175
;;           (sql-comint product params)))
Michael Mauger's avatar
Michael Mauger committed
176 177
;;
;;     (sql-set-product-feature 'xyz
178
;;                              :sqli-comint-func 'my-sql-comint-xyz)
179

180
;; 6) Define a convenience function to invoke the SQL interpreter.
Michael Mauger's avatar
Michael Mauger committed
181

Michael Mauger's avatar
Michael Mauger committed
182
;;     (defun my-sql-xyz (&optional buffer)
Michael Mauger's avatar
Michael Mauger committed
183
;;       "Run ixyz by XyzDB as an inferior process."
Michael Mauger's avatar
Michael Mauger committed
184 185
;;       (interactive "P")
;;       (sql-product-interactive 'xyz buffer))
186

Juanma Barranquero's avatar
Juanma Barranquero committed
187 188
;;; To Do:

Michael Mauger's avatar
Michael Mauger committed
189 190 191
;; Improve keyword highlighting for individual products.  I have tried
;; to update those database that I use.  Feel free to send me updates,
;; or direct me to the reference manuals for your favorite database.
Juanma Barranquero's avatar
Juanma Barranquero committed
192

Michael Mauger's avatar
Michael Mauger committed
193 194 195 196 197 198 199 200
;; When there are no keywords defined, the ANSI keywords are
;; highlighted.  ANSI keywords are highlighted even if the keyword is
;; not used for your current product.  This should help identify
;; portability concerns.

;; Add different highlighting levels.

;; Add support for listing available tables or the columns in a table.
Richard M. Stallman's avatar
Richard M. Stallman committed
201 202 203

;;; Thanks to all the people who helped me out:

Michael Mauger's avatar
Michael Mauger committed
204
;; Alex Schroeder <alex@gnu.org> -- the original author
Richard M. Stallman's avatar
Richard M. Stallman committed
205 206 207 208
;; Kai Blauberg <kai.blauberg@metla.fi>
;; <ibalaban@dalet.com>
;; Yair Friedman <yfriedma@JohnBryce.Co.Il>
;; Gregor Zych <zych@pool.informatik.rwth-aachen.de>
209
;; nino <nino@inform.dk>
210
;; Berend de Boer <berend@pobox.com>
211
;; Adam Jenkins <adam@thejenkins.org>
212
;; Michael Mauger <michael@mauger.com> -- improved product support
213 214
;; Drew Adams <drew.adams@oracle.com> -- Emacs 20 support
;; Harald Maier <maierh@myself.com> -- sql-send-string
215
;; Stefan Monnier <monnier@iro.umontreal.ca> -- font-lock corrections;
216
;;      code polish
217 218
;; Paul Sleigh <bat@flurf.net> -- MySQL keyword enhancement
;; Andrew Schein <andrew@andrewschein.com> -- sql-port bug
219
;; Ian Bjorhovde <idbjorh@dataproxy.com> -- db2 escape newlines
220
;;      incorrectly enabled by default
221 222 223
;; Roman Scherer <roman.scherer@nugg.ad> -- Connection documentation
;; Mark Wilkinson <wilkinsonmr@gmail.com> -- file-local variables ignored
;;
224

Richard M. Stallman's avatar
Richard M. Stallman committed
225 226 227 228


;;; Code:

229
(require 'cl-lib)
Richard M. Stallman's avatar
Richard M. Stallman committed
230 231
(require 'comint)
;; Need the following to allow GNU Emacs 19 to compile the file.
232 233
(eval-when-compile
  (require 'regexp-opt))
Richard M. Stallman's avatar
Richard M. Stallman committed
234
(require 'custom)
235
(require 'thingatpt)
236
(require 'view)
237

238 239 240 241
(defvar font-lock-keyword-face)
(defvar font-lock-set-defaults)
(defvar font-lock-string-face)

Richard M. Stallman's avatar
Richard M. Stallman committed
242 243 244
;;; Allow customization

(defgroup SQL nil
245
  "Running a SQL interpreter from within Emacs buffers."
Dave Love's avatar
Dave Love committed
246
  :version "20.4"
Michael Mauger's avatar
Michael Mauger committed
247
  :group 'languages
Richard M. Stallman's avatar
Richard M. Stallman committed
248 249
  :group 'processes)

250
;; These five variables will be used as defaults, if set.
Richard M. Stallman's avatar
Richard M. Stallman committed
251 252

(defcustom sql-user ""
Michael Mauger's avatar
Michael Mauger committed
253
  "Default username."
Richard M. Stallman's avatar
Richard M. Stallman committed
254
  :type 'string
255 256
  :group 'SQL
  :safe 'stringp)
Richard M. Stallman's avatar
Richard M. Stallman committed
257 258

(defcustom sql-password ""
Michael Mauger's avatar
Michael Mauger committed
259
  "Default password.
260 261
If you customize this, the value will be stored in your init
file.  Since that is a plaintext file, this could be dangerous."
Richard M. Stallman's avatar
Richard M. Stallman committed
262
  :type 'string
263 264
  :group 'SQL
  :risky t)
Richard M. Stallman's avatar
Richard M. Stallman committed
265 266

(defcustom sql-database ""
Michael Mauger's avatar
Michael Mauger committed
267
  "Default database."
Richard M. Stallman's avatar
Richard M. Stallman committed
268
  :type 'string
269 270
  :group 'SQL
  :safe 'stringp)
Richard M. Stallman's avatar
Richard M. Stallman committed
271 272

(defcustom sql-server ""
Michael Mauger's avatar
Michael Mauger committed
273
  "Default server or host."
Richard M. Stallman's avatar
Richard M. Stallman committed
274
  :type 'string
275 276
  :group 'SQL
  :safe 'stringp)
Michael Mauger's avatar
Michael Mauger committed
277

Michael Mauger's avatar
Michael Mauger committed
278
(defcustom sql-port 0
279
  "Default port for connecting to a MySQL or Postgres server."
280
  :version "24.1"
Michael Mauger's avatar
Michael Mauger committed
281
  :type 'number
282 283
  :group 'SQL
  :safe 'numberp)
Richard M. Stallman's avatar
Richard M. Stallman committed
284

285 286 287
(defcustom sql-default-directory nil
  "Default directory for SQL processes."
  :version "24.5"
288
  :type '(choice (const nil) string)
289 290 291
  :group 'SQL
  :safe 'stringp)

292 293 294 295 296
;; Login parameter type

(define-widget 'sql-login-params 'lazy
  "Widget definition of the login parameters list"
  :tag "Login Parameters"
Glenn Morris's avatar
Glenn Morris committed
297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334
  :type '(set :tag "Login Parameters"
              (choice :tag "user"
                      :value user
                      (const user)
                      (list :tag "Specify a default"
                            (const user)
                            (list :tag "Default"
                                  :inline t (const :default) string)))
              (const password)
              (choice :tag "server"
                      :value server
                      (const server)
                      (list :tag "Specify a default"
                            (const server)
                            (list :tag "Default"
                                  :inline t (const :default) string))
                      (list :tag "file"
                            (const :format "" server)
                            (const :format "" :file)
                            regexp)
                      (list :tag "completion"
                            (const :format "" server)
                            (const :format "" :completion)
                            (restricted-sexp
                             :match-alternatives (listp stringp))))
              (choice :tag "database"
                      :value database
                      (const database)
                      (list :tag "Specify a default"
                            (const database)
                            (list :tag "Default"
                                  :inline t (const :default) string))
                      (list :tag "file"
                            (const :format "" database)
                            (const :format "" :file)
                            regexp)
                      (list :tag "completion"
                            (const :format "" database)
335 336
                                (const :format "" :completion)
                                (restricted-sexp
337
                                 :match-alternatives (listp stringp))))
Glenn Morris's avatar
Glenn Morris committed
338
              (const port)))
339

340 341 342 343 344
;; SQL Product support

(defvar sql-interactive-product nil
  "Product under `sql-interactive-mode'.")

345 346 347
(defvar sql-connection nil
  "Connection name if interactive session started by `sql-connect'.")

348
(defvar sql-product-alist
349
  '((ansi
350
     :name "ANSI"
351 352
     :font-lock sql-mode-ansi-font-lock-keywords
     :statement sql-ansi-statement-starters)
Michael Mauger's avatar
Michael Mauger committed
353

354
    (db2
355
     :name "DB2"
356
     :font-lock sql-mode-db2-font-lock-keywords
Michael Mauger's avatar
Michael Mauger committed
357 358 359
     :sqli-program sql-db2-program
     :sqli-options sql-db2-options
     :sqli-login sql-db2-login-params
360
     :sqli-comint-func sql-comint-db2
Michael Mauger's avatar
Michael Mauger committed
361 362
     :prompt-regexp "^db2 => "
     :prompt-length 7
363
     :prompt-cont-regexp "^db2 (cont\.) => "
Michael Mauger's avatar
Michael Mauger committed
364 365
     :input-filter sql-escape-newlines-filter)

366
    (informix
Michael Mauger's avatar
Michael Mauger committed
367
     :name "Informix"
368
     :font-lock sql-mode-informix-font-lock-keywords
Michael Mauger's avatar
Michael Mauger committed
369 370 371
     :sqli-program sql-informix-program
     :sqli-options sql-informix-options
     :sqli-login sql-informix-login-params
372
     :sqli-comint-func sql-comint-informix
Michael Mauger's avatar
Michael Mauger committed
373 374 375 376
     :prompt-regexp "^> "
     :prompt-length 2
     :syntax-alist ((?{ . "<") (?} . ">")))

377
    (ingres
Michael Mauger's avatar
Michael Mauger committed
378
     :name "Ingres"
379
     :font-lock sql-mode-ingres-font-lock-keywords
Michael Mauger's avatar
Michael Mauger committed
380 381 382
     :sqli-program sql-ingres-program
     :sqli-options sql-ingres-options
     :sqli-login sql-ingres-login-params
383
     :sqli-comint-func sql-comint-ingres
Michael Mauger's avatar
Michael Mauger committed
384
     :prompt-regexp "^\* "
385 386
     :prompt-length 2
     :prompt-cont-regexp "^\* ")
Michael Mauger's avatar
Michael Mauger committed
387

388
    (interbase
Michael Mauger's avatar
Michael Mauger committed
389
     :name "Interbase"
390
     :font-lock sql-mode-interbase-font-lock-keywords
Michael Mauger's avatar
Michael Mauger committed
391 392 393
     :sqli-program sql-interbase-program
     :sqli-options sql-interbase-options
     :sqli-login sql-interbase-login-params
394
     :sqli-comint-func sql-comint-interbase
Michael Mauger's avatar
Michael Mauger committed
395 396 397
     :prompt-regexp "^SQL> "
     :prompt-length 5)

398
    (linter
Michael Mauger's avatar
Michael Mauger committed
399
     :name "Linter"
400
     :font-lock sql-mode-linter-font-lock-keywords
Michael Mauger's avatar
Michael Mauger committed
401 402 403
     :sqli-program sql-linter-program
     :sqli-options sql-linter-options
     :sqli-login sql-linter-login-params
404
     :sqli-comint-func sql-comint-linter
Michael Mauger's avatar
Michael Mauger committed
405 406 407
     :prompt-regexp "^SQL>"
     :prompt-length 4)

408
    (ms
Michael Mauger's avatar
Michael Mauger committed
409
     :name "Microsoft"
410
     :font-lock sql-mode-ms-font-lock-keywords
Michael Mauger's avatar
Michael Mauger committed
411 412 413
     :sqli-program sql-ms-program
     :sqli-options sql-ms-options
     :sqli-login sql-ms-login-params
414
     :sqli-comint-func sql-comint-ms
Michael Mauger's avatar
Michael Mauger committed
415 416
     :prompt-regexp "^[0-9]*>"
     :prompt-length 5
417
     :syntax-alist ((?@ . "_"))
Michael Mauger's avatar
Michael Mauger committed
418 419
     :terminator ("^go" . "go"))

420
    (mysql
421
     :name "MySQL"
Michael Mauger's avatar
Michael Mauger committed
422
     :free-software t
423
     :font-lock sql-mode-mysql-font-lock-keywords
Michael Mauger's avatar
Michael Mauger committed
424 425 426
     :sqli-program sql-mysql-program
     :sqli-options sql-mysql-options
     :sqli-login sql-mysql-login-params
427
     :sqli-comint-func sql-comint-mysql
428 429
     :list-all "SHOW TABLES;"
     :list-table "DESCRIBE %s;"
Michael Mauger's avatar
Michael Mauger committed
430 431
     :prompt-regexp "^mysql> "
     :prompt-length 6
432
     :prompt-cont-regexp "^    -> "
433
     :syntax-alist ((?# . "< b"))
Michael Mauger's avatar
Michael Mauger committed
434 435
     :input-filter sql-remove-tabs-filter)

436
    (oracle
Michael Mauger's avatar
Michael Mauger committed
437
     :name "Oracle"
438
     :font-lock sql-mode-oracle-font-lock-keywords
Michael Mauger's avatar
Michael Mauger committed
439 440 441
     :sqli-program sql-oracle-program
     :sqli-options sql-oracle-options
     :sqli-login sql-oracle-login-params
442
     :sqli-comint-func sql-comint-oracle
443 444 445
     :list-all sql-oracle-list-all
     :list-table sql-oracle-list-table
     :completion-object sql-oracle-completion-object
Michael Mauger's avatar
Michael Mauger committed
446 447
     :prompt-regexp "^SQL> "
     :prompt-length 5
448
     :prompt-cont-regexp "^\\(?:[ ][ ][1-9]\\|[ ][1-9][0-9]\\|[1-9][0-9]\\{2\\}\\)[ ]\\{2\\}"
449 450 451
     :statement sql-oracle-statement-starters
     :syntax-alist ((?$ . "_") (?# . "_"))
     :terminator ("\\(^/\\|;\\)$" . "/")
Michael Mauger's avatar
Michael Mauger committed
452 453
     :input-filter sql-placeholders-filter)

454
    (postgres
Michael Mauger's avatar
Michael Mauger committed
455 456
     :name "Postgres"
     :free-software t
457
     :font-lock sql-mode-postgres-font-lock-keywords
Michael Mauger's avatar
Michael Mauger committed
458 459 460
     :sqli-program sql-postgres-program
     :sqli-options sql-postgres-options
     :sqli-login sql-postgres-login-params
461
     :sqli-comint-func sql-comint-postgres
462 463
     :list-all ("\\d+" . "\\dS+")
     :list-table ("\\d+ %s" . "\\dS+ %s")
464 465
     :completion-object sql-postgres-completion-object
     :prompt-regexp "^\\w*=[#>] "
Michael Mauger's avatar
Michael Mauger committed
466
     :prompt-length 5
467
     :prompt-cont-regexp "^\\w*[-(][#>] "
Michael Mauger's avatar
Michael Mauger committed
468
     :input-filter sql-remove-tabs-filter
469
     :terminator ("\\(^\\s-*\\\\g$\\|;\\)" . "\\g"))
Michael Mauger's avatar
Michael Mauger committed
470

471
    (solid
Michael Mauger's avatar
Michael Mauger committed
472
     :name "Solid"
473
     :font-lock sql-mode-solid-font-lock-keywords
Michael Mauger's avatar
Michael Mauger committed
474 475 476
     :sqli-program sql-solid-program
     :sqli-options sql-solid-options
     :sqli-login sql-solid-login-params
477
     :sqli-comint-func sql-comint-solid
Michael Mauger's avatar
Michael Mauger committed
478 479 480
     :prompt-regexp "^"
     :prompt-length 0)

481
    (sqlite
482
     :name "SQLite"
Michael Mauger's avatar
Michael Mauger committed
483
     :free-software t
484
     :font-lock sql-mode-sqlite-font-lock-keywords
Michael Mauger's avatar
Michael Mauger committed
485 486 487
     :sqli-program sql-sqlite-program
     :sqli-options sql-sqlite-options
     :sqli-login sql-sqlite-login-params
488
     :sqli-comint-func sql-comint-sqlite
489 490
     :list-all ".tables"
     :list-table ".schema %s"
491
     :completion-object sql-sqlite-completion-object
Michael Mauger's avatar
Michael Mauger committed
492
     :prompt-regexp "^sqlite> "
493
     :prompt-length 8
494
     :prompt-cont-regexp "^   \.\.\.> "
495
     :terminator ";")
Michael Mauger's avatar
Michael Mauger committed
496

497
    (sybase
Michael Mauger's avatar
Michael Mauger committed
498
     :name "Sybase"
499
     :font-lock sql-mode-sybase-font-lock-keywords
Michael Mauger's avatar
Michael Mauger committed
500 501 502
     :sqli-program sql-sybase-program
     :sqli-options sql-sybase-options
     :sqli-login sql-sybase-login-params
503
     :sqli-comint-func sql-comint-sybase
Michael Mauger's avatar
Michael Mauger committed
504 505
     :prompt-regexp "^SQL> "
     :prompt-length 5
506
     :syntax-alist ((?@ . "_"))
Michael Mauger's avatar
Michael Mauger committed
507
     :terminator ("^go" . "go"))
Sam Steingold's avatar
Sam Steingold committed
508 509 510 511 512 513

    (vertica
     :name "Vertica"
     :sqli-program sql-vertica-program
     :sqli-options sql-vertica-options
     :sqli-login sql-vertica-login-params
514 515
     :sqli-comint-func sql-comint-vertica
     :list-all ("\\d" . "\\dS")
Sam Steingold's avatar
Sam Steingold committed
516 517 518 519
     :list-table "\\d %s"
     :prompt-regexp "^\\w*=[#>] "
     :prompt-length 5
     :prompt-cont-regexp "^\\w*[-(][#>] ")
520
    )
Michael Mauger's avatar
Michael Mauger committed
521 522 523 524
  "An alist of product specific configuration settings.

Without an entry in this list a product will not be properly
highlighted and will not support `sql-interactive-mode'.
525 526 527 528 529

Each element in the list is in the following format:

 \(PRODUCT FEATURE VALUE ...)

Michael Mauger's avatar
Michael Mauger committed
530 531 532 533 534 535 536 537 538
where PRODUCT is the appropriate value of `sql-product'.  The
product name is then followed by FEATURE-VALUE pairs.  If a
FEATURE is not specified, its VALUE is treated as nil.  FEATURE
may be any one of the following:

 :name                  string containing the displayable name of
                        the product.

 :free-software         is the product Free (as in Freedom) software?
539 540 541 542

 :font-lock             name of the variable containing the product
                        specific font lock highlighting patterns.

Michael Mauger's avatar
Michael Mauger committed
543 544 545 546 547
 :sqli-program          name of the variable containing the product
                        specific interactive program name.

 :sqli-options          name of the variable containing the list
                        of product specific options.
548

Michael Mauger's avatar
Michael Mauger committed
549 550 551 552 553
 :sqli-login            name of the variable containing the list of
                        login parameters (i.e., user, password,
                        database and server) needed to connect to
                        the database.

554
 :sqli-comint-func      name of a function which accepts no
555 556
                        parameters that will use the values of
                        `sql-user', `sql-password',
557 558 559 560 561
                        `sql-database', `sql-server' and
                        `sql-port' to open a comint buffer and
                        connect to the database.  Do product
                        specific configuration of comint in this
                        function.
562

563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579
 :list-all              Command string or function which produces
                        a listing of all objects in the database.
                        If it's a cons cell, then the car
                        produces the standard list of objects and
                        the cdr produces an enhanced list of
                        objects.  What \"enhanced\" means is
                        dependent on the SQL product and may not
                        exist.  In general though, the
                        \"enhanced\" list should include visible
                        objects from other schemas.

 :list-table            Command string or function which produces
                        a detailed listing of a specific database
                        table.  If its a cons cell, then the car
                        produces the standard list and the cdr
                        produces an enhanced list.

580 581 582 583 584 585 586 587 588 589 590 591 592 593
 :completion-object     A function that returns a list of
                        objects.  Called with a single
                        parameter--if nil then list objects
                        accessible in the current schema, if
                        not-nil it is the name of a schema whose
                        objects should be listed.

 :completion-column     A function that returns a list of
                        columns.  Called with a single
                        parameter--if nil then list objects
                        accessible in the current schema, if
                        not-nil it is the name of a schema whose
                        objects should be listed.

Michael Mauger's avatar
Michael Mauger committed
594
 :prompt-regexp         regular expression string that matches
595
                        the prompt issued by the product
Michael Mauger's avatar
Michael Mauger committed
596 597 598 599
                        interpreter.

 :prompt-length         length of the prompt on the line.

600 601 602 603
 :prompt-cont-regexp    regular expression string that matches
                        the continuation prompt issued by the
                        product interpreter.

Michael Mauger's avatar
Michael Mauger committed
604 605 606 607 608 609 610
 :input-filter          function which can filter strings sent to
                        the command interpreter.  It is also used
                        by the `sql-send-string',
                        `sql-send-region', `sql-send-paragraph'
                        and `sql-send-buffer' functions.  The
                        function is passed the string sent to the
                        command interpreter and must return the
611 612
                        filtered string.  May also be a list of
                        such functions.
Michael Mauger's avatar
Michael Mauger committed
613

614 615 616
 :statement             name of a variable containing a regexp that
                        matches the beginning of SQL statements.

Michael Mauger's avatar
Michael Mauger committed
617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635
 :terminator            the terminator to be sent after a
                        `sql-send-string', `sql-send-region',
                        `sql-send-paragraph' and
                        `sql-send-buffer' command.  May be the
                        literal string or a cons of a regexp to
                        match an existing terminator in the
                        string and the terminator to be used if
                        its absent.  By default \";\".

 :syntax-alist          alist of syntax table entries to enable
                        special character treatment by font-lock
                        and imenu.

Other features can be stored but they will be ignored.  However,
you can develop new functionality which is product independent by
using `sql-get-product-feature' to lookup the product specific
settings.")

(defvar sql-indirect-features
636
  '(:font-lock :sqli-program :sqli-options :sqli-login :statement))
637

638
(defcustom sql-connection-alist nil
639
  "An alist of connection parameters for interacting with a SQL product.
640 641 642 643
Each element of the alist is as follows:

  \(CONNECTION \(SQL-VARIABLE VALUE) ...)

644 645 646 647 648 649
Where CONNECTION is a case-insensitive string identifying the
connection, SQL-VARIABLE is the symbol name of a SQL mode
variable, and VALUE is the value to be assigned to the variable.
The most common SQL-VARIABLE settings associated with a
connection are: `sql-product', `sql-user', `sql-password',
`sql-port', `sql-server', and `sql-database'.
650 651

If a SQL-VARIABLE is part of the connection, it will not be
652 653 654 655
prompted for during login.  The command `sql-connect' starts a
predefined SQLi session using the parameters from this list.
Connections defined here appear in the submenu SQL->Start...  for
making new SQLi sessions."
656
  :type `(alist :key-type (string :tag "Connection")
657 658 659 660
                :value-type
                (set
                 (group (const :tag "Product"  sql-product)
                        (choice
661 662 663 664 665 666 667 668
                         ,@(mapcar
                            (lambda (prod-info)
                              `(const :tag
                                      ,(or (plist-get (cdr prod-info) :name)
                                           (capitalize
                                            (symbol-name (car prod-info))))
                                      (quote ,(car prod-info))))
                            sql-product-alist)))
669 670 671 672
                 (group (const :tag "Username" sql-user)     string)
                 (group (const :tag "Password" sql-password) string)
                 (group (const :tag "Server"   sql-server)   string)
                 (group (const :tag "Database" sql-database) string)
673 674 675 676 677
                 (group (const :tag "Port"     sql-port)     integer)
                 (repeat :inline t
                         (list :tab "Other"
                               (symbol :tag " Variable Symbol")
                               (sexp   :tag "Value Expression")))))
678 679 680
  :version "24.1"
  :group 'SQL)

681
(defcustom sql-product 'ansi
682 683
  "Select the SQL database product used.
This allows highlighting buffers properly when you open them."
684 685 686 687 688 689 690
  :type `(choice
          ,@(mapcar (lambda (prod-info)
                      `(const :tag
                              ,(or (plist-get (cdr prod-info) :name)
                                   (capitalize (symbol-name (car prod-info))))
                              ,(car prod-info)))
                    sql-product-alist))
691 692
  :group 'SQL
  :safe 'symbolp)
Michael Mauger's avatar
Michael Mauger committed
693
(defvaralias 'sql-dialect 'sql-product)
Michael Mauger's avatar
Michael Mauger committed
694

Paul Eggert's avatar
Paul Eggert committed
695
;; misc customization of sql.el behavior
696

697 698 699 700 701 702 703 704 705
(defcustom sql-electric-stuff nil
  "Treat some input as electric.
If set to the symbol `semicolon', then hitting `;' will send current
input in the SQLi buffer to the process.
If set to the symbol `go', then hitting `go' on a line by itself will
send current input in the SQLi buffer to the process.
If set to nil, then you must use \\[comint-send-input] in order to send
current input in the SQLi buffer to the process."
  :type '(choice (const :tag "Nothing" nil)
706
		 (const :tag "The semicolon `;'" semicolon)
707 708 709 710
		 (const :tag "The string `go' by itself" go))
  :version "20.8"
  :group 'SQL)

Michael Mauger's avatar
Michael Mauger committed
711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738
(defcustom sql-send-terminator nil
  "When non-nil, add a terminator to text sent to the SQL interpreter.

When text is sent to the SQL interpreter (via `sql-send-string',
`sql-send-region', `sql-send-paragraph' or `sql-send-buffer'), a
command terminator can be automatically sent as well.  The
terminator is not sent, if the string sent already ends with the
terminator.

If this value is t, then the default command terminator for the
SQL interpreter is sent.  If this value is a string, then the
string is sent.

If the value is a cons cell of the form (PAT . TERM), then PAT is
a regexp used to match the terminator in the string and TERM is
the terminator to be sent.  This form is useful if the SQL
interpreter has more than one way of submitting a SQL command.
The PAT regexp can match any of them, and TERM is the way we do
it automatically."

  :type '(choice (const  :tag "No Terminator" nil)
		 (const  :tag "Default Terminator" t)
		 (string :tag "Terminator String")
		 (cons   :tag "Terminator Pattern and String"
			 (string :tag "Terminator Pattern")
			 (string :tag "Terminator String")))
  :version "22.2"
  :group 'SQL)
Richard M. Stallman's avatar
Richard M. Stallman committed
739

740 741 742 743 744 745
(defvar sql-contains-names nil
  "When non-nil, the current buffer contains database names.

Globally should be set to nil; it will be non-nil in `sql-mode',
`sql-interactive-mode' and list all buffers.")

746 747
(defvar sql-login-delay 7.5 ;; Secs
  "Maximum number of seconds you are willing to wait for a login connection.")
748

Michael Mauger's avatar
Michael Mauger committed
749 750 751 752 753 754 755 756 757
(defcustom sql-pop-to-buffer-after-send-region nil
  "When non-nil, pop to the buffer SQL statements are sent to.

After a call to `sql-sent-string', `sql-send-region',
`sql-send-paragraph' or `sql-send-buffer', the window is split
and the SQLi buffer is shown.  If this variable is not nil, that
buffer's window will be selected by calling `pop-to-buffer'.  If
this variable is nil, that buffer is shown using
`display-buffer'."
758 759 760
  :type 'boolean
  :group 'SQL)

761 762 763
;; imenu support for sql-mode.

(defvar sql-imenu-generic-expression
764
  ;; Items are in reverse order because they are rendered in reverse.
765 766 767 768 769 770 771 772 773
  '(("Rules/Defaults" "^\\s-*create\\s-+\\(?:\\w+\\s-+\\)*\\(?:rule\\|default\\)\\(?:if\\s-+not\\s-+exists\\s-+\\)?\\s-+\\(\\(?:\\w+\\s-*[.]\\s-*\\)*\\w+\\)" 1)
    ("Sequences" "^\\s-*create\\s-+\\(?:\\w+\\s-+\\)*sequence\\s-+\\(?:if\\s-+not\\s-+exists\\s-+\\)?\\(\\(?:\\w+\\s-*[.]\\s-*\\)*\\w+\\)" 1)
    ("Triggers" "^\\s-*create\\s-+\\(?:\\w+\\s-+\\)*trigger\\s-+\\(?:if\\s-+not\\s-+exists\\s-+\\)?\\(\\(?:\\w+\\s-*[.]\\s-*\\)*\\w+\\)" 1)
    ("Functions" "^\\s-*\\(?:create\\s-+\\(?:\\w+\\s-+\\)*\\)?function\\s-+\\(?:if\\s-+not\\s-+exists\\s-+\\)?\\(\\(?:\\w+\\s-*[.]\\s-*\\)*\\w+\\)" 1)
    ("Procedures" "^\\s-*\\(?:create\\s-+\\(?:\\w+\\s-+\\)*\\)?proc\\(?:edure\\)?\\s-+\\(?:if\\s-+not\\s-+exists\\s-+\\)?\\(\\(?:\\w+\\s-*[.]\\s-*\\)*\\w+\\)" 1)
    ("Packages" "^\\s-*create\\s-+\\(?:\\w+\\s-+\\)*package\\s-+\\(?:body\\s-+\\)?\\(?:if\\s-+not\\s-+exists\\s-+\\)?\\(\\(?:\\w+\\s-*[.]\\s-*\\)*\\w+\\)" 1)
    ("Types" "^\\s-*create\\s-+\\(?:\\w+\\s-+\\)*type\\s-+\\(?:body\\s-+\\)?\\(?:if\\s-+not\\s-+exists\\s-+\\)?\\(\\(?:\\w+\\s-*[.]\\s-*\\)*\\w+\\)" 1)
    ("Indexes" "^\\s-*create\\s-+\\(?:\\w+\\s-+\\)*index\\s-+\\(?:if\\s-+not\\s-+exists\\s-+\\)?\\(\\(?:\\w+\\s-*[.]\\s-*\\)*\\w+\\)" 1)
    ("Tables/Views" "^\\s-*create\\s-+\\(?:\\w+\\s-+\\)*\\(?:table\\|view\\)\\s-+\\(?:if\\s-+not\\s-+exists\\s-+\\)?\\(\\(?:\\w+\\s-*[.]\\s-*\\)*\\w+\\)" 1))
774 775
  "Define interesting points in the SQL buffer for `imenu'.

776
This is used to set `imenu-generic-expression' when SQL mode is
777 778 779
entered.  Subsequent changes to `sql-imenu-generic-expression' will
not affect existing SQL buffers because imenu-generic-expression is
a local variable.")
780 781 782

;; history file

783
(defcustom sql-input-ring-file-name nil
Michael Mauger's avatar
Michael Mauger committed
784
  "If non-nil, name of the file to read/write input history.
785

786 787 788 789 790 791
You have to set this variable if you want the history of your commands
saved from one Emacs session to the next.  If this variable is set,
exiting the SQL interpreter in an SQLi buffer will write the input
history to the specified file.  Starting a new process in a SQLi buffer
will read the input history from the specified file.

792 793 794 795
This is used to initialize `comint-input-ring-file-name'.

Note that the size of the input history is determined by the variable
`comint-input-ring-size'."
796 797 798 799 800
  :type '(choice (const :tag "none" nil)
		 (file))
  :group 'SQL)

(defcustom sql-input-ring-separator "\n--\n"
Michael Mauger's avatar
Michael Mauger committed
801
  "Separator between commands in the history file.
802 803 804 805 806

If set to \"\\n\", each line in the history file will be interpreted as
one command.  Multi-line commands are split into several commands when
the input ring is initialized from a history file.

807 808 809 810 811 812
This variable used to initialize `comint-input-ring-separator'.
`comint-input-ring-separator' is part of Emacs 21; if your Emacs
does not have it, setting `sql-input-ring-separator' will have no
effect.  In that case multiline commands will be split into several
commands when the input history is read, as if you had set
`sql-input-ring-separator' to \"\\n\"."
Richard M. Stallman's avatar
Richard M. Stallman committed
813 814 815 816 817 818
  :type 'string
  :group 'SQL)

;; The usual hooks

(defcustom sql-interactive-mode-hook '()
Michael Mauger's avatar
Michael Mauger committed
819
  "Hook for customizing `sql-interactive-mode'."
Richard M. Stallman's avatar
Richard M. Stallman committed
820 821 822 823
  :type 'hook
  :group 'SQL)

(defcustom sql-mode-hook '()
Michael Mauger's avatar
Michael Mauger committed
824
  "Hook for customizing `sql-mode'."
Richard M. Stallman's avatar
Richard M. Stallman committed
825 826 827
  :type 'hook
  :group 'SQL)

828
(defcustom sql-set-sqli-hook '()
Michael Mauger's avatar
Michael Mauger committed
829
  "Hook for reacting to changes of `sql-buffer'.
830 831 832 833 834 835

This is called by `sql-set-sqli-buffer' when the value of `sql-buffer'
is changed."
  :type 'hook
  :group 'SQL)

836 837 838 839 840
(defcustom sql-login-hook '()
  "Hook for interacting with a buffer in `sql-interactive-mode'.

This hook is invoked in a buffer once it is ready to accept input
for the first time."
841
  :version "24.1"
842 843 844
  :type 'hook
  :group 'SQL)

845 846
;; Customization for ANSI

847 848 849 850 851
(defcustom sql-ansi-statement-starters
  (regexp-opt '("create" "alter" "drop"
                "select" "insert" "update" "delete" "merge"
                "grant" "revoke"))
  "Regexp of keywords that start SQL commands.
852 853 854

All products share this list; products should define a regexp to
identify additional keywords in a variable defined by
855
the :statement feature."
856
  :version "24.1"
857 858
  :type 'string
  :group 'SQL)
859

860
;; Customization for Oracle
Richard M. Stallman's avatar
Richard M. Stallman committed
861 862

(defcustom sql-oracle-program "sqlplus"
Michael Mauger's avatar
Michael Mauger committed
863
  "Command to start sqlplus by Oracle.
Richard M. Stallman's avatar
Richard M. Stallman committed
864 865 866

Starts `sql-interactive-mode' after doing some setup.

867 868 869
On Windows, \"sqlplus\" usually starts the sqlplus \"GUI\".  In order
to start the sqlplus console, use \"plus33\" or something similar.
You will find the file in your Orant\\bin directory."
870 871 872
  :type 'file
  :group 'SQL)

873
(defcustom sql-oracle-options '("-L")
Michael Mauger's avatar
Michael Mauger committed
874
  "List of additional options for `sql-oracle-program'."
875
  :type '(repeat string)
876
  :version "24.4"
877 878
  :group 'SQL)

Michael Mauger's avatar
Michael Mauger committed
879 880
(defcustom sql-oracle-login-params '(user password database)
  "List of login parameters needed to connect to Oracle."
881
  :type 'sql-login-params
Michael Mauger's avatar
Michael Mauger committed
882 883 884
  :version "24.1"
  :group 'SQL)

885 886 887
(defcustom sql-oracle-statement-starters
  (regexp-opt '("declare" "begin" "with"))
  "Additional statement starting keywords in Oracle."
888
  :version "24.1"
889 890
  :type 'string
  :group 'SQL)
891

Michael Mauger's avatar
Michael Mauger committed
892 893 894 895 896
(defcustom sql-oracle-scan-on t
  "Non-nil if placeholders should be replaced in Oracle SQLi.

When non-nil, Emacs will scan text sent to sqlplus and prompt
for replacement text for & placeholders as sqlplus does.  This
897
is needed on Windows where SQL*Plus output is buffered and the
Michael Mauger's avatar
Michael Mauger committed
898 899
prompts are not shown until after the text is entered.

900 901 902
You need to issue the following command in SQL*Plus to be safe:

    SET DEFINE OFF
Michael Mauger's avatar
Michael Mauger committed
903

904
In older versions of SQL*Plus, this was the SET SCAN OFF command."
905
  :version "24.1"
Michael Mauger's avatar
Michael Mauger committed
906 907 908
  :type 'boolean
  :group 'SQL)

909 910 911 912 913 914 915 916 917 918
(defcustom sql-db2-escape-newlines nil
  "Non-nil if newlines should be escaped by a backslash in DB2 SQLi.

When non-nil, Emacs will automatically insert a space and
backslash prior to every newline in multi-line SQL statements as
they are submitted to an interactive DB2 session."
  :version "24.3"
  :type 'boolean
  :group 'SQL)

919 920
;; Customization for SQLite

Michael Mauger's avatar
Michael Mauger committed
921 922 923
(defcustom sql-sqlite-program (or (executable-find "sqlite3")
                                  (executable-find "sqlite")
                                  "sqlite")
Michael Mauger's avatar
Michael Mauger committed
924
  "Command to start SQLite.
925

Michael Mauger's avatar
Michael Mauger committed
926
Starts `sql-interactive-mode' after doing some setup."
927 928 929 930
  :type 'file
  :group 'SQL)

(defcustom sql-sqlite-options nil
Michael Mauger's avatar
Michael Mauger committed
931
  "List of additional options for `sql-sqlite-program'."
932 933 934 935
  :type '(repeat string)
  :version "20.8"
  :group 'SQL)

Michael Mauger's avatar
Michael Mauger committed
936
(defcustom sql-sqlite-login-params '((database :file ".*\\.\\(db\\|sqlite[23]?\\)"))
Michael Mauger's avatar
Michael Mauger committed
937
  "List of login parameters needed to connect to SQLite."
938
  :type 'sql-login-params
Michael Mauger's avatar
Michael Mauger committed
939 940 941
  :version "24.1"
  :group 'SQL)

942
;; Customization for MySQL
943 944

(defcustom sql-mysql-program "mysql"
Michael Mauger's avatar
Michael Mauger committed
945
  "Command to start mysql by TcX.
946

Michael Mauger's avatar
Michael Mauger committed
947
Starts `sql-interactive-mode' after doing some setup."
948 949 950
  :type 'file
  :group 'SQL)

951
(defcustom sql-mysql-options nil
Michael Mauger's avatar
Michael Mauger committed
952
  "List of additional options for `sql-mysql-program'.
953 954
The following list of options is reported to make things work
on Windows: \"-C\" \"-t\" \"-f\" \"-n\"."
955 956 957 958
  :type '(repeat string)
  :version "20.8"
  :group 'SQL)

Michael Mauger's avatar
Michael Mauger committed
959
(defcustom sql-mysql-login-params '(user password database server)