Re: emacs sql

[Galen Boyer]

Here is what I have written and stolen over the years.

;; Variables for sql.el
(setq sql-user nil)
(setq sql-password nil)
(setq sql-database nil)
(setq sql-server nil)
(setq sql-oracle-program "sqlplus")

(defun sql-sql-logons (instance username)
  (fset (intern (concat instance "-" username))
        `(lambda () "hello" (interactive)
           (let ((sql-user ,username)
                 (sql-password ,username)
                 (sql-database ,instance))
             (defalias 'sql-get-login 'ignore)
             (sql-ms))
           (let ((sql-alternate-buffer-name (concat "ISQL: " ,instance "/" 
,username)))
             (sql-rename-buffer)))))

(defun ora-sql-logons (instance username)
  (fset (intern (concat instance "-" username))
        `(lambda () "hello" (interactive)
           (let ((sql-user ,username)
                 (sql-password ,username)
                 (sql-database ,instance))
             (defalias 'sql-get-login 'ignore)
             (sql-oracle))
           (let ((sql-alternate-buffer-name (concat "ORA: " ,instance "/" 
,username)))
             (sql-rename-buffer)))))


(add-to-list 'auto-mode-alist '("\\.pks$" . sql-mode))
(add-to-list 'auto-mode-alist '("\\.pkb$" . sql-mode))

(define-abbrev sql-mode-abbrev-table  "se" "show errors" nil)
(define-abbrev sql-mode-abbrev-table  "ss" "select * from" nil)
(define-abbrev sql-mode-abbrev-table  "scd" "select count(distinct" nil)
(define-abbrev sql-mode-abbrev-table  "cd" "count(distinct" nil)
(define-abbrev sql-mode-abbrev-table  "sd" "select distinct" nil)
(define-abbrev sql-mode-abbrev-table  "cr" "create or replace" nil)
(define-abbrev sql-mode-abbrev-table  "d" "@desc" nil)
(define-abbrev sql-mode-abbrev-table  "da" "@desc_all" nil)
(define-abbrev sql-mode-abbrev-table  "c" "count(*)" nil)
(define-abbrev sql-mode-abbrev-table  "i" "insert" nil)
(define-abbrev sql-mode-abbrev-table  "s" "select" nil)
(define-abbrev sql-mode-abbrev-table  "f" "from" nil)
(define-abbrev sql-mode-abbrev-table  "g" "group by" nil)
(define-abbrev sql-mode-abbrev-table  "o" "order by" nil)
(define-abbrev sql-mode-abbrev-table  "w" "where" nil)
(define-abbrev sql-mode-abbrev-table  "l" "like '%%'" nil)
(define-abbrev sql-mode-abbrev-table  "tt" "truncate table" nil)
(define-abbrev sql-mode-abbrev-table  "dt" nil nil)
(define-abbrev sql-mode-abbrev-table  "ct" "create table" nil)
(define-abbrev sql-mode-abbrev-table  "ii" "insert into" nil)
(define-abbrev sql-mode-abbrev-table  "over" "over (partition by" nil)
(define-abbrev sql-mode-abbrev-table  "case" "case when XXX then YYY else ZZZ 
end" nil)
(define-abbrev sql-mode-abbrev-table  "ei" "execute immediate" nil)
(define-abbrev sql-mode-abbrev-table  "ask" "alter system kill session" nil)
(define-abbrev sql-mode-abbrev-table  "xfg" "explain plan set statement_id = 
'GBOYERSPLAN' into plan_table for" nil)
(define-abbrev sql-mode-abbrev-table  "xf" "explain plan for" nil)
(define-abbrev sql-mode-abbrev-table  "x" "@explain" nil)
(define-abbrev sql-mode-abbrev-table  "xg" "@explain_galen" nil)
(define-abbrev sql-mode-abbrev-table  "xd" "@explain_distributed" nil)
(define-abbrev sql-mode-abbrev-table  "xpart" "@explain_partitioned" nil)
(define-abbrev sql-mode-abbrev-table  "xparr" "@explain_parrallel" nil)
(define-abbrev sql-mode-abbrev-table  "do" "dbms_output.put_line(':' || );" nil)
(define-abbrev sql-mode-abbrev-table "ash" "alter session set HASH_AREA_SIZE=" 
nil)
(define-abbrev sql-mode-abbrev-table "ast" "alter session set 
timed_statistics=;" nil)
(define-abbrev sql-mode-abbrev-table "asst" "alter session set sql_trace=")
(define-abbrev sql-mode-abbrev-table "asev" "alter session set events '10046 
trace name context forever, level 12';")
(define-abbrev sql-mode-abbrev-table "sl" "set linesize")
(define-abbrev sql-mode-abbrev-table "st" "set timing")
(define-abbrev sql-mode-abbrev-table "cfs" "@columnformatstrings")

(require 'tempo)

(tempo-define-template
 "plsql-create-function" ;; template name
 '((p "Name of PLSQL Block: " plsqlname 'NOINSERT)
   "CREATE OR REPLACE FUNCTION "
   (s plsqlname) "("n>
   "    l_###  varchar2" n>
   ",   l_###  number" n>
   ")" n>
   "AS" n>
   "  l_var  XXX;" n>
   "BEGIN"   n>
   "    NULL;" n>
   "END " (s plsqlname) ";" n>
   "/"
   ))


(tempo-define-template
 "select-count-group-by" ;; template name
 '((p "Table Selected From: " tsf 'NOINSERT)
   (p "Fields To Group On: " fgo 'NOINSERT)
   "SELECT "
   (s fgo)
   ",count(*) " n>
   "from "
   (s tsf) n>
   " group by "
   (s fgo)
   ";"))

(define-abbrev sql-mode-abbrev-table  "sc" "select count(*) from" nil)
(define-abbrev sql-mode-abbrev-table  "scg" "" 
'tempo-template-select-count-group-by)

(tempo-define-template
 "sql-column-format"
 '((p "Varchar Column Name: " column-name 'NOINSERT)
   (p "Column Length: " column-length 'NOINSERT)
   "COLUMN "
   (s column-name)
   " FORMAT A"
   (s column-length)))

(define-abbrev sql-mode-abbrev-table "cf" "" 'tempo-template-sql-column-format)

(tempo-define-template
 "select-count-group-by-having" ;; template name
 '((p "Table Selected From: " tsf 'NOINSERT)
   (p "Fields To Group On: " fgo 'NOINSERT)
   "SELECT "
   (s fgo)
   ",count(*) " n>
   "from "
   (s tsf) n>
   " group by "
   (s fgo) n>
   " having count(*) > "))

(define-abbrev sql-mode-abbrev-table  "scgh" "" 
'tempo-template-select-count-group-by-having)

(tempo-define-template
 "createall" ;; template name
 '((p "FileName: " filename 'NOINSERT)
   "SELECT 'executing SQL in file "
   (s filename)
   "' WHAT from dual;" n>
   "@@"
   (s filename)))
(define-abbrev sql-mode-abbrev-table  "createall" "" 'tempo-template-createall)

(tempo-define-template
 "public-synonym" ;; template name
 '((p "ObjectName: " object_name 'NOINSERT)
   "CREATE PUBLIC SYNONYM  "
   (s object_name)
   " FOR "
   (s object_name)
   ";" n>
   "GRANT REFERENCES ON "
   (s object_name)
   " TO PUBLIC;"))
(define-abbrev sql-mode-abbrev-table  "pubsyn" "" 
'tempo-template-public-synonym)


(tempo-define-template
 "sql-create-package" ;; template name
 '((p "PACKAGE NAME: " package_name 'NOINSERT)
    "CREATE OR REPLACE PACKAGE "
    (s package_name) n>
   "AS" n>
   "    PROCEDURE truncate_table;" n>
   "    PROCEDURE load_table;" n>
   "END;" n>
   "/" n> n>
   "show errors" n>
   "-------------------------------------" n>
   "CREATE OR REPLACE PACKAGE BODY "
   (s package_name) n>
   "as" n>
   "    PROCEDURE truncate_table as" n>
   "    BEGIN" n>
   "        NULL" n>
   "    END;" n>
   "    PROCEDURE load_table as" n>
   "    BEGIN" n>
   "        NULL" n>
   "    END;" n>
   "END;" n>
   "/" n>
   "show errors" n>
   ))


(tempo-define-template
 "sql-create-view" ;; template name
 '((p "VIEW NAME: " view-name 'NOINSERT)
    "/********************************************************************" n>
    "* NAME: "
    (s view-name) n>
    "*" n>
    "* DESCRIPTION: " n>
    "*" n>
    "* EXCEL XREF: Report# " n>
    "*" n>
    "* QUESTIONS: " n>
    "*" n>
    "********************************************************************/" n>
    "CREATE OR REPLACE VIEW "
    (s view-name) n>
   "AS" n>
   "SELECT '1' as HELLO_WORLD " n>
   "FROM  dual   " n>
   "WHERE 1=1  " n>
   ";" n>
   ))

(tempo-define-template
 "sql-documentation-view" ;; template name
 '((p "VIEW NAME: " view-name 'NOINSERT)
    "/********************************************************************" n>
    "* NAME: "
    (s view-name) n>
    "*" n>
    "* DESCRIPTION: " n>
    "*" n>
    "*" n>
    "********************************************************************/"
   ))
(define-abbrev sql-mode-abbrev-table  "docv" "" 
'tempo-template-sql-documentation-view)
   

;; If you use your favorite SqlMode entry function such as
;; sql-oracle, you will be popped to the other buffer. That means,
;; the screen will split horizontally if it is not already split,
;; and the other window will show the SQLi buffer.

;; If you prefer the behaviour of switch-to-buffer, then put code
;; similar to this one into your ~/.emacs file:

(defun my-sql-oracle ()
  "Switch to buffer before popping."
  (interactive)
  (if (and (boundp 'sql-buffer)
           (buffer-live-p sql-buffer))
      (switch-to-buffer sql-buffer)
    (sql-oracle)))

;; Replace "oracle" with any of the other supported vendors to get
;; the desired behaviour for your implementation.

;; I use the following, then:

 (global-set-key (kbd "C-c s") 'my-sql-oracle)

;; Note that this uses switch-to-buffer only if a sql-buffer already
;; exists. If it does not, then the old behaviour remains in
;; effect. But I'm too lazy to fix it. --

(define-key sql-mode-map (kbd "TAB") 'indent-relative)

(defun eat-sqlplus-junk (str)
  "Eat the line numbers SQL*Plus returns.
    Put this on `comint-preoutput-filter-functions' if you are
    running SQL*Plus.


    If the line numbers are not eaten, you get stuff like this:
    ...
      2    3    4       from v$parameter p, all_tables u
              *
    ERROR at line 2:
    ORA-00942: table or view does not exist


    The mismatch is very annoying."
  (interactive "s")
  (while (string-match " [ 1-9][0-9]  " str)
    (setq str (replace-match "" nil nil str)))
  str)

(defun install-eat-sqlplus-junk ()
  "Install `comint-preoutput-filter-functions' if appropriate.
    Add this function to `sql-interactive-mode-hook' in your .emacs:
    \(add-hook 'sql-mode-hook 'install-eat-sqlplus-junk)"
  (if (string= (car (process-command (get-buffer-process sql-buffer)))
               sql-oracle-program)
      (add-to-list 'comint-preoutput-filter-functions
                   'eat-sqlplus-junk)))

(add-hook 'sql-interactive-mode-hook 'install-eat-sqlplus-junk)

;; When you are using SqlMode, you can send text from your SQL
;; buffer to your SQLi buffer. This text is usually not added to the
;; history of SQL statements in the SQLi buffer. If you want that,
;; add the following piece of advice to your .emacs file:
(defadvice sql-send-region (after sql-store-in-history)
  "The region sent to the SQLi process is also stored in the history."
  (let ((history (buffer-substring-no-properties start end)))
    (save-excursion
      (set-buffer sql-buffer)
      (message history)
      (if (and (funcall comint-input-filter history)
               (or (null comint-input-ignoredups)
                   (not (ring-p comint-input-ring))
                   (ring-empty-p comint-input-ring)
                   (not (string-equal (ring-ref comint-input-ring 0)
                                      history))))
          (ring-insert comint-input-ring history))
      (setq comint-save-input-ring-index comint-input-ring-index)
      (setq comint-input-ring-index nil))))
(ad-activate 'sql-send-region)

(setq sql-imenu-generic-expression
      '(("Comments" "^-- \\(.+\\)" 1)
        ("Function Definitions" "^\\s-*\\(function\\|procedure\\)[ 
\n\t]+\\([a-z0-9_]+\\)[ \n\t]*([a-z0-9 _,\n\t]*)[ \n\t]*\\(return[ 
\n\t]+[a-z0-9_]+[ \n\t]+\\)?[ai]s\\b" 2)
        ("Function Prototypes" "^\\s-*\\(function\\|procedure\\)[ 
\n\t]+\\([a-z0-9_]+\\)[ \n\t]*([a-z0-9 _,\n\t]*)[ \n\t]*\\(return[ 
\n\t]+[a-z0-9_]+[ \n\t]*\\)?;" 2)
        ("Indexes" "^\\s-*create\\s-+index\\s-+\\(\\w+\\)" 1)
        ("Tables" "^\\s-*create\\s-+table\\s-+\\(\\w+\\)" 1)))

-- 
Galen Boyer