* Re: emacs sql
2003-12-24 16:32 emacs sql Oracle Learner
2003-12-24 17:18 ` Jeffery B. Rancier
2003-12-24 17:27 ` Billy O'Connor
@ 2003-12-24 18:24 ` Galen Boyer
2003-12-24 19:01 ` Oracle Learner
3 siblings, 0 replies; 5+ messages in thread
From: Galen Boyer @ 2003-12-24 18:24 UTC (permalink / raw)
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
^ permalink raw reply [flat|nested] 5+ messages in thread