Very Computer

by **Jens Albrech** » Sat, 12 Apr 1997 04:00:00

This is a multi-part message in MIME format.

--------------446B794B15FB
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi folks,

whoever is interested in a better sqlplus interface, here is one
for the xemacs 19.14.
I wrote it for a unix environment. It is not perfect, but ok.
It provides a sqlplus session interface, some nice keyboard
shortcuts and syntax highlighting (also for sql-scripts) as
well as command completion and table name completion.

Whoever is interested, it is appended. Changes for gnu emacs should
be minor and only apply to the highlighting function (I used
font-lock).

I would greatly appreciate any comments.

-- Jens
======================================================================
Jens Albrecht

University of Erlangen-Nuremberg
Dept. of Computer Science VI (Database Systems)
Martensstr. 3 Phone: +49 (0)9131-85 7884
91058 Erlangen Fax: +49 (0)9131-85 32090

Jens.Albre...@informatik.uni-erlangen.de
http://www6.informatik.uni-erlangen.de/ja.html
======================================================================

--------------446B794B15FB
Content-Type: text/plain; charset=us-ascii; name="sql-mode.el"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="sql-mode.el"

;; sql-mode.el --- an sql interface for Emacs

;; Author: Jens Albrecht
;; Modified: 1997/04/11 11:48:44

;; You should put these lines into your .emacs file:
;; (autoload 'sql-mode "sql-mode")
;; (autoload 'sql "sql-mode" "Open SQL connection." t)
;; Or if you want to load it
;; (load-file "~albrecht/lisp/sql-mode.el")

(require 'shell)
(require 'font-lock)
(require 'etags)

(defvar sql-init-message-german "SQL Modus

Tastaturbelegung:

[return] Zeilenumbruch
[control return] Kommando abschicken
[tab] Kommandovervollstaendigung
[up][down] im Buffer auf und ab bewegen
[kp_up][kp_down] Kommandohistorie durchblaettern
[Meta-p][Meta-n] Kommandohistorie durchblaettern (p = previous, n = next)

[f1] SQL-Hilfe (in anderem Buffer)
[f2] diese Meldung anzeigen
[f3] Bufferinhalt loeschen
[f4] Schluesselwoerter highlighten (ein/aus)
[f5] Persoenliche Tabellen anzeigen
[f6] Tabellenamenvervollstaendigung einschalten
[f7] 'describe <Tabelle>' fuer Tabellename, auf dem der Cursor steht
[f8] Ausgabezeilengroesse auf Fenstergroesse einstellen

[Maus 3] Selbe Funktion wie [f7]
[shift Maus 3] 'select * from <Tabelle>' fuer Tabellename,
auf dem der Cursor steht

Wichtig:

Befehle duerfen keine Leerzeilen enthalten.

Befehle muessen durch ein Semikolon abgeschlossen werden.

Nur der letzte Befehl (am Ende des Buffers) wird korrekt gesendet -
also nicht einfach mit dem Cursor hochlaufen und [control return] druecken,
sondern mit [Meta-p] den letzten Befehl zurueckholen und editieren!

Der Emacs kann extrem langsam werden, wenn die Buffer sehr gross wird.
Beim Ausgeben grosser Tabellen kann das schnell passieren. Nach
dem Ansehen der Ausgabe, sollte man dann moeglichst viel Text wieder
loeschen ([f3] druecken).

Bugs:

Das Syntaxhighlighting funktioniert manchmal nicht richtig. Einfach ein
paar mal [f4] druecken, dann geht's schon wieder.

Fehlermeldungen, Anfragen, Kritik an:
Jens.Albre...@informatik.uni-erlangen.de
")

(defvar sql-init-message-english "SQL Mode

Hot Keys:

[return] Indented newline
[control return] Send input
[tab] Command completion
[up][down] Move up and down in buffer
[kp_up][kp_down] History up
[Meta-p][Meta-n] History down

[f1] SQL help in another buffer
[f2] Show this message
[f3] Delete buffer content
[f4] Enable/disable syntax highlighting
[f5] Show personal tables
[f6] Enable table name completion
[f7] 'describe <table>' for table name under cursor
[f8] Set output line size to window size

[Mouse 3] Same like [f7]
[shift Mouse 3] 'select * from <table>' for table name under cursor

Important:

Commands must not contain empty lines.

Commands must end with a semicolon ';'.

Only the last command in the buffer will be sent correctly -
use the history functions to edit previous commands.

The Emacs can become extremely slow, if the buffer size grows.
That can quickly happen, if your select statements return large tables.
Thus, press [f3] from time to time.

Bugs:

Syntax highlighting sometimes does not work correctly. Pressing [f4]
helps.

Bugs, critics etc. to
Jens.Albre...@informatik.uni-erlangen.de
")

(defvar sql-init-message
(if (string-match "\.de$" (system-name))
sql-init-message-german
sql-init-message-english))
(defvar sql-command "sqlplus")
(defvar sql-name "Oracle")
(defvar sql-help-name "Oracle Help")
(defvar sql-host (replace-in-string
(system-name) "\.informatik\.uni-erlangen\.de" ""))
(defvar sql-user-name "/")

(defun sql-create-buffer (name user)
"Create buffer running SQL"
(let ((buffer (set-buffer (apply 'make-comint
name
"/usr/bin/rsh" nil
(list sql-host
(concat sql-command
"\ "
user))))))
(sql-mode)
buffer))

(defun sql ()
"Start SQL connection to database."
(interactive)
(let ((buffer)
(buf-name (concat sql-name "-" (user-login-name))))
(setq buf-name (read-from-minibuffer "Open SQL buffer: " buf-name))
(cond ((not (get-buffer (concat "*" buf-name "*")))
(setq sql-user-name
(read-from-minibuffer "Account name: " sql-user-name))
(setq sql-host (read-from-minibuffer "Host name: " sql-host))
(with-output-to-temp-buffer "*Help*" (princ sql-init-message))
(pop-to-buffer (get-buffer "*Help*"))
(help-mode)
(switch-to-buffer-other-window
(sql-create-buffer buf-name sql-user-name)))
(t
(pop-to-buffer buffer)))))

(defun sql-help ()
"Get help for SQL command."
(interactive)
(let ((cmd (find-tag-default))
(buffer (get-buffer (concat "*" sql-help-name "*"))))
(if (not buffer)
(pop-to-buffer (sql-create-buffer sql-help-name sql-user-name))
(pop-to-buffer buffer))
(kill-region (point-min) (point-max))
(font-lock-mode 0)
(insert "help ")
(if (equal cmd "SQL>")
(setq cmd ""))
(insert (read-from-minibuffer "Enter SQL command: " cmd))
(insert ";")
(comint-send-input)
(beginning-of-buffer)))

(defvar sql-keyword-list
(list "connect " "CONNECT "
"disconnect" "DISCONNECT"
"select " "SELECT "
"distinct " "DISTINCT "
"from " "FROM "
"where " "WHERE "
"group by " "GROUP BY "
"having " "HAVING "
"order by " "ORDER BY "
"union " "UNION "
"describe " "DESCRIBE "
"create " "CREATE "
"replace " "REPLACE "
"drop " "DROP "
"table " "TABLE "
"view " "VIEW "
"index " "INDEX "
"schema " "SCHEMA "
"procedure " "PROCEDURE "
"declare " "DECLARE "
"begin " "BEGIN "
"end" "END"
"trigger " "TRIGGER "
"before " "BEFORE "
"after " "AFTER "
"cascade " "CASCADE "
"update " "UPDATE "
"set " "SET "
"delete " "DELETE "
"insert " "INSERT "
"into " "INTO "
"values " "VALUES "
"commit " "COMMIT "
"primary " "PRIMARY "
"foreign " "FOREIGN "
"unique " "UNIQUE "
"key " "KEY "
"references " "REFERENCES "
"not " "NOT "
"null " "NULL "
"any " "ANY "
"all " "ALL "
"as " "AS "
"on " "ON "
"integer" "INTEGER"
"number" "NUMBER"
"char" "CHAR"
"varchar" "VARCHAR"
"date" "DATE"
"decimal" "DECIMAL"
"alter " "ALTER "
"add " "ADD "
"constraint" "CONSTRAINT"
"check" "CHECK"
"min" "MIN"
"max" "MAX"
"sum" "SUM"
"count" "COUNT"
"avg" "AVG"
))

(defun sql-table-names ()
"Append user table names to keyword list."
(interactive)
(message "Connecting to database ...")
(let ((buffer (current-buffer)))
(goto-char (point-min buffer))
(while (not (search-forward "SQL> " nil t 0 buffer))
(goto-char (point-min buffer)))
(pop-to-buffer buffer)
(goto-char (point-max))
(message "Retrieving table names ... ")
(let ((start (point-max)) (end) (string))
(insert "select table_name from user_tables
union
select view_name from user_views;")
(comint-send-input)
(sleep-for 5)
(setq end (point-max))
(goto-char start)
(while (search-forward-regexp "\$^[A-Za-z0-9_]+$\$" end t)
(setq string (match-string 1))
(if (and (not (equal string "TABLE_NAME")))
(progn
(if (eq 0 (let ((list sql-keyword-list))
(while (and list
(not (equal (car list)
string)))
(setq list (cdr list)))
(length list)))
(setq sql-keyword-list
(append sql-keyword-list
(list string))))
(setq string (downcase string))
(if (eq 0 (let ((list sql-keyword-list))
(while (and list
(not (equal (car list)
string)))
(setq list (cdr list)))
(length list)))
(setq sql-keyword-list
(append sql-keyword-list
(list string)))))))
(delete-region start end))
(message "Retrieving table names ... done.")))

(defun sql-tab-expand ()
"SQL tab expansion for keywords."
(interactive)
(let ((end (point)) (start) (prefix) (len)
(list sql-keyword-list) (result (list)))
(backward-char)
(while (and (> (point) 1)
(looking-at "[^\t\n( ]"))
(backward-char))
(setq start (if (eq (point) 1)
1
(+ (point) 1)))
(setq len (- end start))
(setq prefix (buffer-string start end))
(goto-char end)
(while list
(if (eq 0 (string-match prefix (car list)))
(setq result (append result (list (car list)))))
(setq list (cdr list)))
(cond ((eq 1 (length result))
(insert (substring (car result) len)))
(result
(let ((maxfix (car result)) (list result) (msg "") (i))
(progn
(while list
(setq i (+ len 1))
(while (and (<= i (length maxfix))
(equal (substring (car list) len i)
(substring maxfix len i)))
(setq i (+ i 1)))
(setq maxfix (substring maxfix 0 (- i 1)))
(setq msg (concat msg (car list) " "))
(setq list (cdr list)))
(insert (substring maxfix len (length maxfix)))
(message msg))))
(t
(message "No completion found.")))))

(defun sql-newline ()
"Insert newline in SQL mode and, in case buffer has a process, \
send input if last char was a ';'."
(interactive)
(cond ((get-buffer-process (current-buffer))
(if (or (equal (point) 1)
(equal (char-after (- (point) 1)) 59)) ; ";"
(comint-send-input)
(let ((p (point)) (prompt nil))
(beginning-of-line)
(if (<= (point) (- (point-max) 5))
(setq prompt
(eq 0 (string-match "SQL> "
(buffer-string (point) (+ (point) 5))))))
(goto-char p)
(newline)
(if prompt
(insert " ")
(indent-relative-maybe)))))
(t
(newline)
(indent-relative-maybe))))

(defun sql-send-input ()
"Send SQL input."
(interactive)
(end-of-buffer)
(if (search-backward-regexp "[^\t\n ]" (point-min) t)
(let ((semi (looking-at ";")))
(forward-char)
(if (not semi)
(insert ";"))))
(while (looking-at "[\t\n ]")
(delete-char 1))
(comint-send-input))

(defun sql-previous-input ()
"Previous SQL input."
(interactive)
(goto-char (point-max))
(comint-previous-input 1))

(defun sql-next-input ()
"Next SQL input."
(interactive)
(goto-char (point-max))
(comint-next-input 1))

(defun sql-format-output (&optional string)
"SQL output filter function.
Deletes line numbers at beginning of SQL error messages."
(interactive)
(goto-char (point-max))
(if (search-backward ";" (point-min) t)
(let ((start (point)))
(next-line 1)
(end-of-line)
(if (re-search-backward "^[ ]*[ 0-9]*[0-9]+ " start t)
(replace-match ""))))
(goto-char (point-max)))

(defvar sql-mode-map (copy-keymap shell-mode-map)
"Keymap used in SQL mode.")

(defun sql-mode (&optional fontify)
"A major mode with tab expansion for SQL keywords."
(interactive)
(use-local-map sql-mode-map)
(setq major-mode 'sql-mode)
(font-lock-mode 1)
(setq mode-name "SQL")

(if (get-buffer-process (current-buffer))
(setq comint-output-filter-functions
(append '(sql-format-output)
comint-output-filter-functions)))

(run-hooks 'sql-mode-hook)
)

(put 'sql-mode 'font-lock-defaults '(sql-font-lock-keywords))

(setq-default auto-mode-alist
(append '(("\\.sql$" . sql-mode))
auto-mode-alist))

(defun sql-set-width (&optional size)
"Set output line size to prefix arg size."
(interactive "PNew window width: ")
(let ((width (if size
(int-to-string size)
(int-to-string (window-width)))))
(goto-char (point-max))
(insert "set linesize ")
(insert width)
(insert ";")
(comint-send-input)
(message (concat "New window width: " width " characters."))))

(defun sql-describe-table ()
"Insert 'describe <table_name>' for table name under cursor."
(interactive)
(let ((table (find-tag-default)))
(goto-char (point-max))
(insert (concat "describe " table ";\n"))
(insert (concat " select count(*) from " table ";"))
(comint-send-input)))

(defun sql-select-all ()
"Insert 'select * from <table_name>' for table name under cursor."
(interactive)
(let ((table (find-tag-default)))
(goto-char (point-max))
(insert (concat "select * from " table ";"))
(comint-send-input)))

;; hot key definitions
(define-key sql-mode-map [tab] 'sql-tab-expand)
(define-key sql-mode-map [return] 'sql-newline)
(define-key sql-mode-map [(control return)] 'sql-send-input)
(define-key sql-mode-map [up] 'previous-line)
(define-key sql-mode-map [down] 'next-line)
(define-key sql-mode-map [kp_up] 'sql-previous-input)
(define-key sql-mode-map [kp_down] 'sql-next-input)
(define-key sql-mode-map "\M-p" 'sql-previous-input)
(define-key sql-mode-map "\M-n" 'sql-next-input)
(define-key sql-mode-map [f1] 'sql-help)
(define-key sql-mode-map [f2]
'(lambda ()
(interactive)
(let ((buffer (current-buffer)))
(with-output-to-temp-buffer "*Help*" (princ sql-init-message))
(pop-to-buffer (get-buffer "*Help*"))
(help-mode)
(switch-to-buffer-other-window buffer))))
(define-key sql-mode-map [f3]
'(lambda ()
(interactive)
(cond ((get-buffer-process (current-buffer))
(kill-region (point-min) (point-max))
(comint-send-input)))))
(define-key sql-mode-map [f4] 'font-lock-mode)
(define-key sql-mode-map [f5]
'(lambda ()
(interactive)
(insert "select table_name from user_tables
union
select view_name from user_views;")
(comint-send-input)))
(define-key sql-mode-map [f6] 'sql-table-names)
(define-key sql-mode-map [f7] 'sql-describe-table)
(define-key sql-mode-map [f8] 'sql-set-width)

(define-key sql-mode-map [button3]
'(lambda ()
(interactive)
(mouse-set-point current-mouse-event)
(sql-describe-table)))
(define-key sql-mode-map [(shift button3)]
'(lambda ()
(interactive)
(mouse-set-point current-mouse-event)
(sql-select-all)))

(provide 'sql-mode)

--------------446B794B15FB--

Very Computer

XEmacs SQL-Plus interface for Oracle

XEmacs SQL-Plus interface for Oracle