Emacs Database Mode

1 - I can do better 2 - Jury's out 3 - Pretty darn good 4 - Splendiferous 5 - Awesometastic by 2 people | Log in to rate

Ranked #2,451 in Tech & Geek, #61,610 overall

Emacs Database Mode | A New Emacs Mode For Working With Databases

Emacs is a text editor, but more than that, it is a framework that enables you to develop applications that can manipulate text in a variety of ways. For example, when I am working with databases, I want to create queries and send them to the database. I'll need a text editor to create the query in the first place so it makes sense that should add functionality to emacs that can easily send my query straight to the database.

Let me demonstrate with a fairly substantial example that mimics typical usage of comint mode. Normally comint is used for interacting with an interpreter. I do a lot of database work with Sybase so a new emacs mode that helped with that would be great. Adapting this for other databases should be fairly straightforward.

I can leverage off the useful sql-mode that already provides syntax highlighting and database interaction. However, as far as I know, it doesn't really help me with my favourite usage style where I have a command window containing my queries and a separate results window.

When you have finished looking at this lens, have a look at the db-mode extension for working with multiple databases.

Emacs Core Libraries 

I use these libraries for almost all comint based code.

(require 'cl)
(require 'comint)
(require 'derived)

Constants 

Specify where the interpreter lives

(defconst *db-cmd* "/usr/bin/isql")

Choosing Between Different Databases 

I have a bunch of different databases I need to connect to so these are the variables and accessors I use.

(defvar db-server "")
(defvar db-database "")
(defvar db-user "")
(defvar db-password "")

(defun db-set-params (server database user password)
  (setq db-server server
        db-database database
        db-user user
        db-password password))

(db-set-params "DB_SERVER" "database" "user" "password")

Low Level Wrappers 

I wrap the low level stuff for accessing the buffer and associated process.

(defvar db-buffer "db")

(defsubst db-get-buffer () (concat "*" db-buffer "*"))

(defsubst db-get-process ()
  (get-buffer-process (db-get-buffer)))

Post Connection Hooks 

And I allow for a set of functions to be called on connection to the database.

(defvar db-post-connect-hooks nil
  "List of functions to call after connecting to the database")

Inserting Text Into The Result Buffer 

I need to insert text into the database buffer from time to time. ordinary-insertion-filter is taken from this link about filter functions.

(defun ordinary-insertion-filter (proc string)
  (with-current-buffer (process-buffer proc)
    (let ((moving (= (point) (process-mark proc))))
      (save-excursion
        ;; Insert the text, advancing the process marker.
        (goto-char (process-mark proc))
        (insert string)
        (set-marker (process-mark proc) (point)))
      (if moving (goto-char (process-mark proc))))))

(defun db-insert-text (str)
  (ordinary-insertion-filter (db-get-process) str))

Ugly Results Without Filtering 

Emacs will be connecting to the stdin and stdout of isql, but the problem is that when we send a query to isql, it doesn't echo back what the query was. For example:

echo 'select *
  from person
where surname = "Doe"' | isql



gives the following output

1> 2> 3> name surname
---- -------
John Doe

(1 row affected)

Required (prettier) Result 

I think those prompts 1> 2> 3> look ugly so I want to remove them and I also want to echo the query before the results.

select *
  from person
 where surname = "Doe"

name surname
---- -------
John Doe

(1 row affected)

Adding The Filter 

To remove the prompts we add a simple filter to comint-preoutput-filter-functions.

(defconst db-re-isql-prompt (concat "^ *"
                                    "\\([0-9]+> +\\)"
                                    "\\([0-9]+> +\\)*"))

(defun db-output-filter (output)
  (let ((match (string-match db-re-isql-prompt output)))
    (when (and (numberp match) (= 0 match))
      (setq output (concat (substring output 0 (match-beginning 0))
                           (substring output (match-end 0))))))
  (when (string-match "affected)[ \n]+\\(1>\\)" output)
    (setq output (concat (substring output 0 (match-beginning 1)) "\n")))
  output)

(add-hook 'comint-preoutput-filter-functions 'db-output-filter)
;; (remove-hook 'comint-preoutput-filter-functions 'db-output-filter)



I will add the echo query when the query is sent to the stdin of isql.

Connecting to the Database 

Connecting to the db uses the typical (apply 'make-comint ...) call we mentioned before. At this point I add the required configuration to the output buffer. I particularly like to toggle-truncate-lines as then my query result will line up nicely.

(defun db-cmd-args ()
  (list "-w9999"
        (concat "-S" db-server)
        (concat "-D" db-database)
        (concat "-U" db-user)
        (concat "-P" db-password)))

(defun db-connect-to-db ()
  (interactive)
  (when (not (comint-check-proc (db-get-buffer)))
    (message "Connecting to the database...")
    (apply 'make-comint db-buffer *db-cmd* nil (db-cmd-args))
    (set-buffer (db-get-buffer))
    (toggle-truncate-lines 1)
    (setq comint-move-point-for-output t)
    (add-hook 'comint-output-filter-functions
              'comint-postoutput-scroll-to-bottom nil t)
    (run-hooks 'db-post-connect-hooks))
  t)

Adding Quote Words Syntax 

Because I am controlling all of the input that isql sees, I can add some new syntax. I often do a lot of WHERE field in ("val1", "val2", ...) type queries and it is annoying to add all the quotes and commas. Wouldn't it be nice if I had something similar to qw(...) syntax in perl?

(disclaimer: this might be a bad idea, I haven't thought it through in a lot of detail. It is more to demonstrate what is possible)

(defun db-replace-quote-words (sql)
  (let ((case-fold-search nil))
    (with-temp-buffer
      (insert sql)
      (goto-char (point-min))
      (while (re-search-forward "qw(" nil t)
        (let (begin end words)
          (setq begin (point))
          (backward-char)
          (forward-sexp)
          (setq end (point))
          (goto-char begin)
          (while (re-search-forward "[^ \n]+" end t)
            (push (match-string 0) words))
          (delete-region (- begin 3) end)
          (insert (format "(%s)"
                          (mapconcat (lambda (s)
                                       (concat "\""
                                               (replace-quote s)
                                               "\""))
                                     (reverse words) ", ")))))
      (buffer-substring-no-properties (point-min) (point-max)))))



I replace all the qw(...) sections prior to sending the query to isql.

Sending Queries to the Database 

(defun db-send-sql-region (start end)
  (interactive "r")
  (db-connect-to-db)
  (db-set-window-layout)

  (let* ((sql (buffer-substring-no-properties start end)))
    (setq sql (db-replace-quote-words sql))
    (db-insert-text sql)
    (when (string-match "^go" sql)
      (db-insert-text "\n\n"))
    (comint-send-string (db-get-process) sql)
    (comint-send-string (db-get-process) "\n")))

Send The Current SQL 

I added db-send-sql-current that sends the current query. It assumes that queries are seperated by at least one blank line.

(defun db-send-sql-current ()
  (interactive)
  (save-excursion
    (backward-paragraph)
    (when (not (bobp))
      (next-line))
    (move-beginning-of-line nil)
    (let ((s (point)))
      (forward-paragraph)
      (when (not (eobp))
        (next-line -1))
      (move-end-of-line nil)
      (db-send-sql-region s (point)))))

Deriving DB Mode 

I derive my new mode from sql-mode to get the syntax-highlighting for free.

(define-derived-mode db-mode sql-mode "Database"
  "Major mode for interacting with databases.
Special commands:
\\{db-mode-map}")

Useful Key Chords 

C-x C-e mimics evaluation in other emacs inferior interpreter modes.

(define-key db-mode-map (kbd "C-x C-e") 'db-send-sql-current)
(define-key db-mode-map (kbd "C-c C-r") 'db-send-sql-region)

An Easy Way to Start DB Mode 

enter-db-mode is a convenience function.

(defun db-set-window-layout ()
  (switch-to-buffer-other-window (db-get-buffer) t)
  (other-window -1))

(defun enter-db-mode ()
  (interactive)
  (switch-to-buffer "*sql*" t)
  (db-mode)
  (db-connect-to-db)
  (db-set-window-layout))

(global-set-key [f12] 'enter-db-mode)

Other Emacs Lenses 

Check out these other lenses for more information about extending emacs
featured lens The Magic of Emacs Comint

Comint is one of the main reasons I moved to emacs after using vi/vim for 14 years. It is an emacs library for interacting with external processes. Many text editors have a way of calling out to a process and synchronously getting the result. Emacs g...

featured lens Emacs Hooks - An Introduction

Hooks provide an clean, organised mechanism for extending emacs. Any function in emacs may include one or more hooks where users can add their own functions to be run. Windows Notepad users can add .LOG to the top of a textfile in order to support a...

featured lens HowTo Query Multiple Databases With Emacs Db Mode

In my experience it is rare that you will work with a single database. even if you have data in only one region, you will hopefully at least have a QA database. Therefore to be a serious tool, you need to have some way of making it easy to query mult...

Reader Feedback 

Let me know if you found this lens useful

submit
  • Reply
    Patrick Patrick Apr 17, 2009 @ 10:50 pm | in reply to jareddavison2009
    Hey - you know what I'd love to see is an explanation of how to get pcomplete (or whatever) hooked up with sql-mode. In my experiments with this, the database-dictionary (which is supposed to examine and give you tab-complete on schema) has never worked. You obviously have more lisp chops or patience to get it to work. Cheers!
  • Reply
    jareddavison2009 jareddavison2009 Apr 16, 2009 @ 3:44 pm
    @Sojourn - thanks :)

    @Ian Eure - great, I was looking for a simple way to do this in sql-mode but I guess I missed this. Connecting to the database is just a couple of lines in db-mode. The rest is the additional features and I think it stands as a nice example of comint mode.

    @Patrick - fair enough. Look out for a blog post on using sql-mode in a similar way soon. :)
  • Reply
    Patrick Patrick Apr 14, 2009 @ 5:11 pm
    What Ian said.

    Nice description of comint, though, never-the-less.
  • Reply
    Ian Eure Ian Eure Apr 12, 2009 @ 4:26 pm
    Sadtrombone! This is alrady supported by sql-mode. Fire up an interactive SQL window (M-x sql-sybase), then open a sql-mode buffer (C-x b *sql* RET M-x sql-mode RET)

    Now you can write your SQL and use:
    C-c C-b sql-send-buffer
    C-c C-c sql-send-paragraph
    C-c C-r sql-send-region
    C-c C-s sql-send-string

    To send the contents to the sqli buffer. The other features you want, such as filtering, can be added with advice or a minor mode extending sqli-mode.
  • Reply
    Sojourn Sojourn Apr 12, 2009 @ 1:10 pm
    You obviously know your stuff, Jared. Nice job!
  • Load More
Show All

These Posts May Help With Your (Emacs) Lisp 

fare: Boston Lisp Meeting: Monday 2009-12-14 Sam Tobin-Hochstadt
Tell us by sending email to boston-lisp-meeting-register at common-lisp.net. We won't send any acknowledgement unless requested; importantly, we'll keep your identity and address confidential and won't communicate any such information ...
Powered by Google

Explore related pages

All About PostgreSQL
This lens aggregates postgresql news, information and blogs from around the web....
EnterpriseDB
News and Blogs about EnterpriseDB, the Oracle Compatible Database, from around t...
SQL Queries
SQL Queries Collection(http://www.sqlqueriescollection.blogspot.com/) site has c...
Database Driven Websites
The Internet has grown to become the fastest growing community where millions of...
A Web Standards Bookshelf
"... As to whether modules are a good idea for someone who doesn't like mod...

Related Topics

Create a Lens!