From mboxrd@z Thu Jan 1 00:00:00 1970 Path: main.gmane.org!not-for-mail From: Galen Boyer Newsgroups: gmane.emacs.help Subject: Re: emacs sql Date: 24 Dec 2003 12:24:18 -0600 Sender: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane.org@gnu.org Message-ID: References: NNTP-Posting-Host: deer.gmane.org Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Trace: sea.gmane.org 1072290655 10740 80.91.224.253 (24 Dec 2003 18:30:55 GMT) X-Complaints-To: usenet@sea.gmane.org NNTP-Posting-Date: Wed, 24 Dec 2003 18:30:55 +0000 (UTC) Original-X-From: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane.org@gnu.org Wed Dec 24 19:30:51 2003 Return-path: Original-Received: from monty-python.gnu.org ([199.232.76.173]) by deer.gmane.org with esmtp (Exim 3.35 #1 (Debian)) id 1AZDmU-0002pR-00 for ; Wed, 24 Dec 2003 19:30:50 +0100 Original-Received: from localhost ([127.0.0.1] helo=monty-python.gnu.org) by monty-python.gnu.org with esmtp (Exim 4.24) id 1AZEim-0005Lz-On for geh-help-gnu-emacs@m.gmane.org; Wed, 24 Dec 2003 14:31:04 -0500 Original-Path: shelby.stanford.edu!newsfeed.stanford.edu!cyclone.bc.net!news.alt.net!pd7cy2so!pd7cy1no!shaw.ca!news-out1.nntp.be!propagator2-sterling!in.nntp.be!newsfeed1.easynews.com!easynews.com!easynews!newscene!novia!novia!sequencer.newscene.com!not-for-mail Original-Newsgroups: gnu.emacs.help Original-Lines: 317 Original-Sender: galenboyer@hotpop.com User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 Original-Xref: shelby.stanford.edu gnu.emacs.help:119623 Original-To: help-gnu-emacs@gnu.org X-BeenThere: help-gnu-emacs@gnu.org X-Mailman-Version: 2.1.2 Precedence: list List-Id: Users list for the GNU Emacs text editor List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane.org@gnu.org Xref: main.gmane.org gmane.emacs.help:15564 X-Report-Spam: http://spam.gmane.org/gmane.emacs.help:15564 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