sqlparser-oracle-complete.el

Last edit

Changed:

< ;; Last Updated: Joseph 2012-02-04 14:08:20 星期六
< ;; Version: 0.1.4

to

> ;; Last Updated: Joseph 2012-03-16 01:27:43 星期五
> ;; Version: 0.1.5

Deleted:

< ;; Features that might be required by this library:
< ;; `anything'
< ;;

Deleted:

< ;; (require 'anything nil t)

Deleted:

< ;; (if (featurep 'anything)
< ;; (define-key map (quote [tab]) 'anything-oracle-complete)
< ;; )

Deleted:

< ;; (defvar anything-c-source-oracle-candidates nil)
< ;; (defvar anything-init-postion-4-oracle)
< ;; (defvar anything-c-source-oracle
< ;; '((name . "SQL Object:")
< ;; (init (lambda() (setq anything-init-postion-4-oracle (point))(setq anything-c-source-oracle-candidates ( sqlparser-oracle-context-candidates))))
< ;; (candidates . anything-c-source-oracle-candidates)
< ;; (action . (("Complete" . (lambda(candidate) (goto-char anything-init-postion-4-oracle)(backward-delete-char (length (sqlparser-word-before-point-4-oracle))) (insert candidate)))))))
< ;; ;;;###autoload
< ;; (defun anything-oracle-complete()
< ;; "call `anything' to complete tablename and column name for oracle."
< ;; (interactive)
< ;; (let ((anything-execute-action-at-once-if-one t)
< ;; (anything-quit-if-no-candidate
< ;; (lambda () (message "complete failed."))))
< ;; (anything '(anything-c-source-oracle)
< ;; ;; Initialize input with current symbol
< ;; (sqlparser-word-before-point-4-oracle) nil nil)))


Download

;;; sqlparser-oracle-complete.el --- completing tablename,column name for oracle. -*- coding:utf-8 -*-

;; Copyright (C) 2011~2012 纪秀峰(Joseph) all rights reserved.

;; Created: 2011年07月31日 星期日 20时37分31秒
;; Last Updated: Joseph 2012-03-16 01:27:43 星期五
;; Version: 0.1.5
;; Author: 纪秀峰(Joseph)  jixiuf@gmail.com
;; Keywords: sql parse oracle complete
;; Filename: sqlparser-oracle-complete.el
;; Description:  completing tablename column for oracle when editing
;; URL:http://www.emacswiki.org/emacs/download/sqlparser-oracle-complete.el
;;     https://github.com/jixiuf/sqlparser
;; screencast:http://screencast-repos.googlecode.com/files/sqlparser0oracle0complete.mkv
;; Compatibility: Test on Linux
;;
;; Features  that be required by this library
;; `oracle-query.el'
;; http://www.emacswiki.org/emacs/download/oracle-query.el
;;

;; This program is free software; you can redistribute it and/or modify
;; it under the terms of the GNU General Public License as published by
;; the Free Software Foundation, either version 3 of the License, or
;; (at your option) any later version.

;; This program 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
;; along with this program.  If not, see <http://www.gnu.org/licenses/>.

;;; Commentary:
;; it can parsing current sql sentence ,it is smart enough to choose to
;; completing table name or column name depending on your position.
;; 1 after keyword 'use'   :complete schema name (on mysql)
;; 2 after keyword 'select' 'set' 'where'    :complete  columnname.
;; 3 after keyword 'alter', 'from' 'update' 'desc'  'show' : complete tablename
;; 4 after keyword 'into' and and there isn't a
;; "\\(" between 'into' and current postion :complete tablename
;; 4.1 after keyword 'into' but there is a "(" between 'into' and current
;; position  :complete columnname
;; 5 after keyword 'values'  :complete nothing.
;;

;;; Installation:
;;
;; 1 it required oracle-query.el you should download and add it to you load-path.
;; http://www.emacswiki.org/emacs/download/oracle-query.el
;; 2 run  lsnrctl start

;; 3 add sqlparser-oracle-complete.el to you load-path
;; The load-path is usually ~/elisp/.
;; It's set in your ~/.emacs like this:
;; (add-to-list 'load-path (expand-file-name "~/elisp"))
;;
;; And the following to your ~/.emacs startup file.
;; (require 'sqlparser-oracle-complete)
;; (add-hook  'sql-mode-hook 'oracle-complete-minor-mode)
;; (add-hook  'sqlplus-mode-hook 'oracle-complete-minor-mode)
;; or you can call M-x: oracle-complete-minor-mode
;;  and complete command is binded on `TAB' .
;;  with `C-u' you can complete with new connection string.

;;; Commands:
;;
;; Below are complete command list:
;;
;;  `oracle-complete-minor-mode'
;;    mode for editing oracle script
;;  `sqlparser-oracle-complete'
;;    complete tablename or column name depending on current point
;;
;;; Customizable Options:
;;
;; Below are customizable option list:
;;

;;; Code:

(require 'oracle-query)
(require 'thingatpt)

(defvar oracle-complete-minor-mode-map
  (let ((map (make-sparse-keymap)))
    (define-key map  (quote [tab]) 'sqlparser-oracle-complete)
    map))
(defvar  oracle-complete-minor-mode-hook nil)

;;;###autoload
(define-minor-mode oracle-complete-minor-mode
  "mode for editing oracle script"
  :lighter " OracleC"
  :keymap oracle-complete-minor-mode-map
  :group 'SQL
  (if oracle-complete-minor-mode
      (run-hooks 'oracle-complete-minor-mode-hook)))

(defvar sqlparser-sqlplus-connection nil)
;; (make-variable-buffer-local 'sqlparser-sqlplus-connection)

;;;###autoload
(defun sqlparser-oracle-complete(&optional arg)
  "complete tablename or column name depending on current point
position . with `C-u',use a new connection string to complete."
  (interactive "P")
  (let ((prefix  (sqlparser-word-before-point-4-oracle) )
        (init-pos (point))
        (candidates  (sqlparser-oracle-context-candidates arg))
        last-mark)
    (if (= 1 (length candidates))
        (insert (car candidates))
      (insert (completing-read "complete:" candidates nil t prefix )))
    (setq last-mark (point-marker))
    (goto-char init-pos)
    (backward-delete-char (length prefix))
    (goto-char (marker-position last-mark))
    ))

(defun  sqlparser-oracle-context-candidates(&optional arg)
  "it will decide to complete tablename or columnname depend on
  current position. with `C-u' use a new connection string to complete."
  (if arg
      (when (oracle-query-connection-alive-p sqlparser-sqlplus-connection)
        (oracle-query-close-connection  sqlparser-sqlplus-connection)
        (setq sqlparser-sqlplus-connection (call-interactively 'oracle-query-create-connection)))
    (unless  (oracle-query-connection-alive-p sqlparser-sqlplus-connection)
      (setq sqlparser-sqlplus-connection (call-interactively 'oracle-query-create-connection))))
  (let ((context (sqlparser-parse-4-oracle)) candidats)
    (cond
     ((string= "schema" context)
      (setq candidats (sqlparser-oracle-schemaname-candidates))
      )
     ((string= "table" context)
      (setq candidats (sqlparser-oracle-tablename-or-schemaname-candidates))
      )
     ((string= "column" context)
      (setq candidats (  sqlparser-oracle-column-candidates))
      )
     ((null context)))
    candidats))

;; elect view_name from all_views where owner = 'U1'
;; union all
;; select table_name from all_tables where owner = 'U1'
;; union all
;; select table_name from all_tab_privs where grantee = 'U1' and privilege = 'SELECT'
(defun  sqlparser-oracle-tablename-or-schemaname-candidates ()
  "all tablename viewname i can select."
  (let* ((prefix (sqlparser-get-prefix-4-oracle))
         (tmp-str-array (split-string prefix "\\."))
         owner table_name sql)
    (if  (= 2 (length tmp-str-array))
        (progn (setq owner (car tmp-str-array))
               (setq table_name (nth 1 tmp-str-array)))
      (setq table_name  (car tmp-str-array)))

    (if owner
        (setq sql (format
                   (concat " select view_name from all_views where upper(owner)=upper('%s') and upper(view_name) like upper('%s%%') "
                           "union all select table_name from all_tables where upper(owner)=upper('%s') and upper(table_name) like upper('%s%%') "
                           "union all  select table_schema||'.'||table_name from all_tab_privs where lower(grantee) = lower('%s') and privilege = 'SELECT' and upper(table_schema)=upper('%s') and upper(table_name) like upper('%s%%')"
                           "union all select table_name from dict where upper(table_name) like upper('%s%%')")

                   (nth 2 sqlparser-sqlplus-connection) table_name
                   (nth 2 sqlparser-sqlplus-connection) table_name
                   (nth 2 sqlparser-sqlplus-connection) owner table_name
                   table_name
                   ))
      (setq sql (format
                 (concat " select view_name from all_views where upper(owner)=upper('%s') and upper(view_name) like upper('%s%%') "
                         "union all select table_name from all_tables where upper(owner)=upper('%s') and upper(table_name) like upper('%s%%') "
                         "union all  select table_schema||'.'||table_name from all_tab_privs where lower(grantee) = lower('%s') and privilege = 'SELECT' and upper(table_name) like upper('%s%%')"
                         "union all select table_name from dict where upper(table_name) like upper('%s%%')"
                         "union all select USERNAME from ALL_USERS where upper(USERNAME) like upper('%s%%')"
                         )

                 (nth 2 sqlparser-sqlplus-connection) table_name
                 (nth 2 sqlparser-sqlplus-connection) table_name
                 (nth 2 sqlparser-sqlplus-connection) owner table_name
                 table_name
                 table_name           ;actual ,maybe username
                 ))
      )
    (mapcar 'car
            (oracle-query
             sql
             sqlparser-sqlplus-connection
             ))
    )
  )

(defun sqlparser-oracle-schemaname-candidates ()
  "all schema-name in oracle database"
  (mapcar 'car
          (oracle-query
            (format "select table_schema from all_tab_privs where lower(grantee) = lower('%s') and privilege = 'SELECT'"
                   (nth 2 sqlparser-sqlplus-connection))
           sqlparser-sqlplus-connection
           )))

(defun sqlparser-oracle-column-candidates ()
  "column name candidates of table in current sql "
  (let* ((sql "select column_name from user_tab_columns where 1=0")
         (table-names (sqlparser-fetch-tablename-from-sql-4-oracle
                       (sqlparser-sql-sentence-at-point-4-oracle)))
         (prefix (sqlparser-get-prefix-4-oracle))
         (sub-prefix (split-string prefix "\\." nil))
         tablename tablenamelist schemaname )
    (if (> (length sub-prefix) 1);;alias.columnsname
        (progn
          (setq tablename (sqlparser-guess-table-name-4-oracle (car sub-prefix)))
          (setq tablenamelist (split-string tablename "[ \t\\.]" t))
          (if (= 1 (length tablenamelist)) ;;just tablename ,not dbname.tablename
              (progn
                (setq tablename (car tablenamelist))
                (setq schemaname nil)
                (setq sql (format " select column_name from all_tab_columns  where upper(table_name)=upper('%s') and upper(column_name) like upper('%s%%') "
                                  tablename (nth 1 sub-prefix) ) ))
            (setq schemaname (car tablenamelist))
            (setq tablename (cadr tablenamelist))
            (setq sql (format "select column_name from all_tab_columns where upper(owner)=upper('%s') and upper(table_name) =upper('%s') and upper(column_name) like upper('%s%%')"
                              schemaname tablename (nth 1 sub-prefix)))
            ))
      (while (> (length table-names) 0)
        (setq tablename (pop table-names))
        (setq tablenamelist (split-string tablename "[ \t\\.]" t))
        (if (= 1 (length tablenamelist))
            (progn
              (setq tablename (car tablenamelist))
              (setq schemaname nil)
              (setq sql (format "%s union select column_name from all_tab_columns where upper(table_name)=upper('%s') and upper(column_name) like upper('%s%%') " sql tablename prefix ))
              )
          (setq tablename (cadr tablenamelist))
          (setq schemaname (car tablenamelist))
          (setq sql (format "%s union select column_name from all_tab_columns where upper(table_name)=upper('%s') and upper(owner)=upper('%s') and upper(column_name) like upper('%s%%') "
                            sql tablename schemaname prefix)))))
    (mapcar 'car (oracle-query sql sqlparser-sqlplus-connection))))

;; TEST :
;; (sqlparser-fetch-tablename-from-sql-4-oracle "select * from (select id from oracle.emp a , oracle.abc ad) ,abcd  as acd  where name=''")
;; (sqlparser-fetch-tablename-from-sql-4-oracle "update user set age=11 ")
;; (sqlparser-fetch-tablename-from-sql-4-oracle "alter  user add (tim datetime)")

(defun sqlparser-fetch-tablename-from-sql-4-oracle ( &optional sql1)
  "return a list of tablenames from a sql-sentence."
  (let ((sql (or sql1 (sqlparser-sql-sentence-at-point-4-oracle)))
        tablenames)
    (setq tablenames (sqlparser-fetch-tablename-from-select-sql-4-oracle sql))
    (unless tablenames
      (setq tablenames (append tablenames (list (sqlparser-fetch-tablename-from-insert-update-alter-sql-4-oracle sql)))))
    tablenames
    ))

(defun sqlparser-fetch-tablename-from-insert-update-alter-sql-4-oracle( &optional sql1)
  "fetch tablename ,or schema.tablename from a insert sentence or
update sentence or alter sentence."
  (let ((sql (or sql1 (sqlparser-sql-sentence-at-point-4-oracle)))
        tablename)
    (with-temp-buffer
      (insert sql)
      (goto-char (point-min))
      (when (search-forward-regexp "\\(\\binto\\|update\\|table\\)[ \t]+\\([a-zA-Z0-9\\$\\._]+\\)\\b" (point-max ) t)
        (setq tablename (match-string 2))
        )
      )))

(defun sqlparser-fetch-tablename-from-select-sql-4-oracle ( &optional sql1)
  "return a list of tablenames from a sql-sentence."
  (let* ((sql (or sql1 (sqlparser-sql-sentence-at-point-4-oracle)))
         (sql-stack (list sql)) ele pt result-stack tablename-stack )
    (while (> (length sql-stack) 0)
      (setq ele (pop sql-stack))
      (with-temp-buffer
        (insert ele)
        (goto-char (point-min))
        (while (search-forward-regexp "[ \t]*(" (point-max) t)
          (forward-char -1)
          (setq pt (point))
          (forward-sexp)
          (push (buffer-substring (1+ pt)  (1- (point))) sql-stack)
          (delete-region  pt (point))
          (insert "table "))
        (push (buffer-substring (point-min) (point-max))  result-stack)
        ))
    (while (> (length result-stack) 0)
      (setq ele (pop result-stack))
      (with-temp-buffer
        (insert ele)
        (goto-char (point-min))
        (when (search-forward-regexp "\\(?:\\([a-zA-Z0-9_ ]+\\)[ \t\n\r]+\\)?\\(?:\\(?:inner[ \t\r\n]+\\|\\(?:\\(?:left\\|right\\)[ \t\r\n]+\\(?:outer[ \t\r\n]+\\)?\\)\\)join[ \t\n\r]+\\)\\([a-zA-Z0-9_ ]+\\)[ \t\r\n]+" (point-max) t)
          (push  (match-string 1) tablename-stack)
          (push  (match-string 2) tablename-stack)
          )
        (goto-char (point-min))
        (when  (search-forward-regexp "[ \t]+from[ \t]+" (point-max) t)
          (delete-region (point-min) (point))
          (when (search-forward-regexp "[ \t]+where[ \t]+" (point-max) t)
            (backward-word)
            (delete-region (point) (point-max)))
          (goto-char (point-min))
          (while (search-forward-regexp "," (point-max) t)
            (push (buffer-substring 1 (1- (point))) tablename-stack)
            (delete-region  1 (point))
            )
          (push (buffer-substring (point-min) (point-max)) tablename-stack)
          )
        )
      )
    (while (> (length tablename-stack) 0)
      (setq ele (pop tablename-stack))
      (with-temp-buffer
        (insert ele)
        (goto-char (point-min))
        (while (re-search-forward "\n" nil t)
          (replace-match " " nil nil))
        (goto-char (point-min))
        (while (re-search-forward "[ \t]+as[ \t]+"  nil t)
          (replace-match " " nil nil))
        (goto-char (point-min))
        (delete-horizontal-space)
        (goto-char (point-max))
        (delete-horizontal-space)

        (if (= 1  (count-matches  "[a-zA-Z0-9_\\$\\. ]+" 1 (point-max)))
            (push (buffer-substring 1 (point-max)) result-stack)
          (goto-char 0)
          (when (search-forward-regexp "[a-zA-Z0-9_\\$\\. ]+" (point-max) t )
            (push (match-string 0) result-stack)
            )
          )
        )
      )
    (delete "table " result-stack)
    result-stack
    ))

;; TEST :
;; (sqlparser-fetch-tablename-from-select-sql-4-oracle "select * from (select id from oracle.emp a , oracle.abc ad) ,abcd  as acd  where name=''")


(defun sqlparser-guess-table-name-4-oracle (alias &optional sql1)
  "find out the true table name depends on the alias.
suppose the sql is `select * from user u where u.age=11'
then the `u' is `alias' and `user' is the true table name."
  (let ((sql  (or sql1 (sqlparser-sql-sentence-at-point-4-oracle)))
        (regexp (concat  "\\([a-zA-Z0-9_\\.\\$]+\\)[ \t]+\\(as[ \t]+\\)?" alias "[, \t\n\r]\\|$"))
        table-name)
    (setq sql (concat sql " "))
    (if (and  sql (string-match regexp sql))
        (progn
          (setq table-name (match-string 1 sql))
          (if (string-equal "from" table-name) alias table-name))
      alias)
    ))
;; TEST :
;; (sqlparser-guess-table-name "a"   "select * from (select id from oracle.emp a , oracle.abc ad) ,abcd  as acd  where name=''")

(defun sqlparser-sql-sentence-at-point-4-oracle()
  "get current sql sentence. "
  (let* ((bounds (bounds-of-sql-at-point-4-oracle))
         (beg (car bounds))
         (end (cdr bounds)))
    (buffer-substring-no-properties  beg end)
    ))

(defun bounds-of-sql-at-point-4-oracle()
  "get start and end point of current sql."
  (let ((pt (point))begin end empty-line-p empty-line-p next-line-included tail-p)
    (when (and
           (looking-at "[ \t]*\\(\n\\|\\'\\)")
           (looking-back "[ \t]*;[ \t]*" (beginning-of-line))
           )
      (search-backward-regexp "[ \t]*;[ \t]*" (beginning-of-line) t)
      )
    (save-excursion
      (skip-chars-forward " \t\n\r")
      (re-search-backward ";[ \t\n\r]*\\|\\`\\|\n[\r\t ]*\n[^ \t]" nil t)
      (setq begin (match-end 0)))
    (save-excursion
      (skip-chars-forward " \t\n\r")
      (re-search-forward "\n[\r\t ]*\n[^ \t]\\|\\'\\|[ \t\n\r]*;" nil t)
      (unless (zerop (length (match-string 0)))
        (backward-char 1))
      (skip-syntax-backward "-")
      (setq end   (point)))
    (goto-char pt)
    (cons begin end)
    )
  )

;; 1 after keyword 'use'   :complete schema name
;; 2 after keyword 'alter', 'from' 'update' 'desc'  'show' : complete tablename
;; 3 after keyword 'select' 'set' 'where'    :complete  columnname.
;; 4 after keyword 'into' and and there isn't a
;; "\\(" between 'into' and current postion :complete tablename
;; 4.1 after keyword 'into' but there is a "(" between 'into' and current
;; position  :complete columnname
;; 5 after keyword 'values'  :complete nothing.
(defun sqlparser-parse-4-oracle()
  "judge now need complete tablename or column name or don't complete .
it will return 'table' ,or 'column' ,or nil.
"
  (let* ((cur-pos (point))
         (sql-pos-info (bounds-of-sql-at-point-4-oracle))
         (sql-start-pos (car sql-pos-info ))
         (sql-end-pos (cdr sql-pos-info))
         map keyword returnVal)
    (when (search-backward-regexp "\\balter\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "alter") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\btable\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "table") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\bcolumn\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "column") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\bfrom\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "from") map))
    (when (search-backward-regexp "\\bdesc\\b\\|\\bdescribe\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "desc") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\bupdate\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "update") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\bselect\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "select") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\bset\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "set") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\bwhere\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "where") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\bon\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "on") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\bhaving\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "having") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\bvalues\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "values") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\binto\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "into") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\border\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "order") map))
    (goto-char cur-pos)
    (when (search-backward-regexp "\\bgroup\\b" sql-start-pos t 1)
      (push   (list (- cur-pos (point)) "group") map))
    (goto-char cur-pos)
    (setq map   (sort map (lambda (a b ) (when (< (car a ) (car b)) t))))
    (setq keyword  (car (cdar map)))
    (cond
     ( (null keyword)
       (setq returnVal nil)
       )
     ((string= "into" keyword)
      (progn
        ;; '(' between "into" and current position
        (if (search-backward-regexp (regexp-quote "(") (- cur-pos  (caar map)) t 1)
            (setq returnVal "column")
          (setq returnVal "table")
          )
        )
      )
     ((string-match "table\\|from\\|update\\|desc\\|describe" keyword)
      (setq returnVal "table")
      )
     ((string-match "column\\|select\\|set\\|where\\|on\\|having\\|group\\|order" keyword)
      (setq returnVal "column")
      )
     ((string-match "values" keyword)
      (setq returnVal nil)
      )
     (t
      (setq returnVal nil)
      )
     )
    (goto-char cur-pos)
    returnVal
    ))


(defun sqlparser-get-prefix-4-oracle()
  "for example `tablename.col' `table.' `str'"
  (with-syntax-table (copy-syntax-table (syntax-table))
    (modify-syntax-entry ?.  "w");treat . as part of word
    (or (thing-at-point 'word) ""))
  )

(defun sqlparser-word-before-point-4-oracle()
  "get word before current point or empty string."
  (with-syntax-table (copy-syntax-table (syntax-table))
    (modify-syntax-entry ?.  ".");treat . as punctuation character
    (or (thing-at-point 'word) ""))
  )


(provide 'sqlparser-oracle-complete)
;;; sqlparser-oracle-complete.el ends here