Download
(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)
(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)
(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))
(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
))
)
(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)
(progn
(setq tablename (sqlparser-guess-table-name-4-oracle (car sub-prefix)))
(setq tablenamelist (split-string tablename "[ \t\\.]" t))
(if (= 1 (length tablenamelist))
(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))))
(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
))
(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)
))
(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)
)
)
(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
(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")
(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 ?. ".")
(or (thing-at-point 'word) ""))
)
(provide 'sqlparser-oracle-complete)