拾遗笔记

为mysql、sqlserver、oracle编写sql脚本时补全表名、列名

链接

演示视频screencast screencast mkv格式 ,用bzip 进行压缩
文件上传到了 http://emacswiki.org/Joseph,
同时源代码用git 进行管理,地址在这里github.com/sqlparser.

简介

最近新写了一个以mysql 为后端,当编写sql 脚本时,能适时地从mysql 数据库中查询
适时插入到当前光标下的表名列名等内容的扩展. 即进行表名列名的补全。(现在也相应
的提供了 oracle sqlserver 版本的)
emacswiki上有SqlComplete 的介绍,用了一下,发现并不好用。不够智能,比如它不
清楚什么时候该插入表名,什么时候该插入列名。所以只好自已动手。

相初用这个sqlparser名字是因为 在emacs 中对sql 语句进行编写时,想写一些解析当前sql
语句的相关函数,所以用了sqlparser这个名,后来陆续添加了一些与sql相关的功能
用这个名字,似乎不太合适,但也懒得改了。

  • oracle-query.el
  • sqlserver-query.el
  • mysql-query.el
    这三个提供最基本的连接数据库的功能
  • sqlparser-mysql-complete.el
  • sqlparser-oracle-complete.el
  • sqlparser-sqlserver-complete.el
    这三个文件 分别提供编写 sql脚本时补全 表名、列名的功能。
    比如在from 之后,补全表名,在select ,where 之后补全列名等.
  • sqlserver-table2entity-4csharp.el
  • sqlserver-table2entity-4java.el
  • oracle-table2entity-4csharp.el
  • oracle-table2entity-4java.el
  • mysql-table2entity-4csharp.el
  • mysql-table2entity-4java.el
  • mysql-table2record-erlang.el
    这几个文件分别提供了将sqlserver mysql oracle的某个schema中所有的表导出成java
    csharp 实体类的功能。
    M-x: sqlserver-table2entity-4csharp-interactively
    M-x: sqlserver-table2entity-4java-interactively
    M-x: oracle-table2entity-4java-interactively
    M-x: oracle-table2entity-4csharp-interactively
    M-x: mysql-table2entity-4csharp-interactively
    M-x: mysql-table2entity-4java-interactively
    M-x: erlang-mysql-generate-records
    

    分别提供这样几个函数

    • mysql-query.el
      ;; the normal way to use mysql-query.el is :
      ;; 1:
      (defvar mysql-connection (mysql-query-create-connection connection-info))
      or
      (defvar mysql-connection nil)
      (unless (mysql-query-connection-alive-p  mysql-connection)
        (setq mysql-connection (call-interactively 'mysql-query-create-connection)))
      
      ;; 2:
        (mysql-query              "select user from mysql.user" mysql-connection)
        (mysql-query-with-heading "select user from mysql.user" mysql-connection)
      
    • oracle-query.el
      ;; execute sql using sqlplus and return as list .
      (oracle-query "select empno,ename from emp where empno<=7499")
      ;; got :     (("7369" "SMITH") ("7499" "ALLEN"))
      ;; or
      (oracle-query-with-heading "select empno,ename from emp where empno<=7499")
      ;; got :     (("EMPNO" "ENAME") ("7369" "SMITH") ("7499" "ALLEN"))
      ;; using default connection ,not recommended.
      (defvar connection (oracle-query-create-connection "scott/tiger"))
      (oracle-query "select empno from emp" connection)
      ;; recommended
      ;; the normal way to use oracle-query.el is :
      ;; 1:
      (defvar sqlplus-connection nil)
      (unless (oracle-query-connection-alive-p c)
        (setq sqlplus-connection (call-interactively 'oracle-query-create-connection)))
      ;; 2:
      (oracle-query "select empno from emp" sqlplus-connection)
      ;; 3:
      (oracle-query-close-connection sqlplus-connection)
      
    • sqlserver-query.el
        ;; the normal way to use sqlserver-query.el is :
      ;; 1:
      (defvar c nil)
      (unless (sqlserver-query-connection-alive-p c)
        (setq c (call-interactively 'sqlserver-query-create-connection)))
      ;; 2:
        (sqlserver-query "select empno from emp" c)
        ;; or
        (sqlserver-query-with-heading "select empno from emp" c)
      ;; 3:
        (sqlserver-query-close-connection c)
      

如何使用进行mysql 的表名列名补全:

(require 'sqlparser-mysql-complete)
(define-key sql-mode-map (quote [M-return]) 'sqlparser-mysql-complete)
(define-key sql-interactive-mode-map  (quote [M-return]) 'sqlparser-mysql-complete)
;; or
(defadvice sql-mysql (around start-mysql-complete-minor-mode activate)
  "enable `mysql-complete-minor-mode' minor mode."
  ad-do-it
  (mysql-complete-minor-mode))
(define-derived-mode mysql-mode sql-mode "mysql"
  "mysql mode"
  (mysql-complete-minor-mode))

;; `sqlparser-mysql-complete' default  bind on `TAB' in
;; with `C-u' you can change the dbname
;; with `C-uC-u' you can use another new mysql connection
(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.
 ;; there is a minor mode defined here
      (sqlserver-complete-minor-mode)
;; you can add it to sql-mode-hook
(add-hook  'sql-mode-hook 'sqlserver-complete-minor-mode)
;; or call M-x sqlserver-complete-minor-mode

 ;; and complete command is binded on `TAB' .

;; 1. you should custom these variable
;; `sqlserver-connection-info'
 ;; `sqlserver-cmd' ;sqlcmd or osql
;; for example
(setq sqlserver-connection-info
      '((username . "sa")
        (password . "sa")
        (server-instance . "localhost\\SQLEXPRESS")
        (dbname . "master"))
      )
;; or sometimes
(setq sqlserver-connection-info
      '((username . "sa")
        (password . "sa")
        (server-instance . "localhost")
        (dbname . "master")))

(setq sqlserver-cmd' 'sqlcmd) or (setq sqlserver-cmd' 'osql)


;; my config file about sqlserver-complete-minor-mode looks like this .
(require 'sql)
(require 'sqlparser-sqlserver-complete)
(add-hook  'sql-mode-hook 'sqlserver-complete-minor-mode)
(add-hook  'sqlserver-complete-minor-mode-hook 'sqlserver-complete-minor-mode-setup)
(defun sqlserver-complete-minor-mode-setup()
  (setq sqlserver-connection-info
    '((username . "sa")
      (password . "sa")
      (server-instance . "localhost")
      (dbname . "HAIHUA"))
    )
  (setq sqlserver-cmd 'sqlcmd)
  )

Comments

comments powered by Disqus