HowTo Query Multiple Databases With Emacs Db Mode

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

Ranked #7,778 in Tech & Geek, #176,608 overall

Working With Multiple Database Using Emacs

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 multiple databases. Can we extend sql-mode or db-mode to do what we need?

Set The Database Parameters 

Let me remind you how we set the database parameters using db-set-params.

(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))

Database Static Data 

Right - so in my company we have a bunch of databases that store client order details. Each region has its own orders database and there are two user accounts that I generally use - one for querying the database (restricted) and the other for updating the data (admin).

Okay, so the first thing we need is something that maps short aliases to database details - server, database, user and password. I'll store the details in a vector.

Let's say (for the purposes of the example) that all of the accounts have the same password :)

(defconst ro-pwd "********")
(defconst rw-pwd "********")

(defun _db-one-info (region server user password)
  (cons region (vector server "orders" user password)))

The Admin and Restricted Users 

We have a convenience function for creating the restricted and admin users.

(defun _db-region-info (region p-server q-server)
  (list (_db-one-info (concat region "-ro") p-server "restricted" ro-pwd)
        (_db-one-info (concat region "-rw") p-server "admin" rw-pwd)))

Creating The Database Aliases 

Then we create the aliases for London, Frankfurt and Mexico and we filter out the aliases using mapcar.

(defconst *db-info*
  (append (_db-region-info "ldn" "LDN_ORDERS_DB" "LDN_ORDERS_QA")
          (_db-region-info "fft" "FFT_ORDERS_DB" "FFT_ORDERS_QA")
          (_db-region-info "mex" "MEX_ORDERS_DB" "MEX_ORDERS_QA")))

(defconst *db-valid-regions*
  (mapcar (lambda (e) (car e)) *db-info*))

(defvar db-region "ldn-ro")

The Resultant Data 

This is what the static data looks like (in the actual data there are more production databases and QA aswell):

(insert (format "%s" *db-info*))

((ldn-ro . [LDN_ORDERS_DB orders restricted ********])
 (ldn-rw . [LDN_ORDERS_DB orders admin ********])
 (fft-ro . [FFT_ORDERS_DB orders restricted ********])
 (fft-rw . [FFT_ORDERS_DB orders admin ********])
 (mex-ro . [MEX_ORDERS_DB orders restricted ********])
 (mex-rw . [MEX_ORDERS_DB orders admin ********]))

Retrieving The Database Details 

And finally (for the static data) we have helper functions to select each piece of data depending on the region.

(defsubst db-get-param (region ref)
  (let ((tuple (assoc region *db-info*)))
    (if tuple
        (aref (cdr tuple) ref)
      "UNKNOWN")))

(defsubst db-get-server (region) (db-get-param region 0))
(defsubst db-get-database (region) (db-get-param region 1))
(defsubst db-get-user (region) (db-get-param region 2))
(defsubst db-get-password (region) (db-get-param region 3))

Setting the Mode Line 

Another feature we want is that the mode line displays which region we are looking at.

(defvar db-mode-string "")

(defun db-set-mode-string ()
  (setq db-mode-string (format "(db-region: %s)" db-region)))

mode-line-format 

My default mode-line-format looks like this:

(%e - mode-line-mule-info mode-line-client
      mode-line-modified mode-line-remote
      mode-line-frame-identification
      mode-line-buffer-identification
      mode-line-position (vc-mode vc-mode)
      mode-line-modes
      (which-func-mode ( which-func-format --))
      (global-mode-string (-- global-mode-string)) -%-)



As you can see, it is a list with a bunch of variables, some function calls and some special codes beginning with or containing a %. For example -%- produces an infinite number of dashes. Almost all modelines have this as a terminator.

If you add a variable to the mode-line-format, updating the variable automatically updates the modeline.

The Mode Line Update Function 

The first thing we need to do is see if the db-mode-string variable is part of the mode-line-format variable already using memq. If it is, we can just set it and force a mode-line-update. If not, we need to insert the variable before the final -%-.

(defun db-update-mode-line ()
  (unless (memq 'db-mode-string mode-line-format)
    (setq mode-line-format (delete "-%-" mode-line-format))
    (setq mode-line-format
          (append mode-line-format '(db-mode-string "-%-"))))
  (db-set-mode-string)
  (force-mode-line-update))

(add-hook 'db-mode-hook 'db-update-mode-line)

Selecting The Database By Its Alias 

Finally, the main function - db-set-region

db-set-region enables you to choose between multiple databases by the alias you selected. It uses ido so if you have enabled flex matching you only need to type enough characters to distinguish the alias you need. For example, if I type x, then w there is only one alias that contains those two letters and that is mex-rw.

It kills any active database process and sets up the parameters appropriately for next time we connect.

(defun db-set-region (&optional region)
  (interactive)
  (unless region
    (setq region
          (ido-completing-read (format "New region (%s): " db-region)
                               *db-valid-regions*
                               nil t)))
  (if (and (stringp region) (> (length region) 0))
      (let ((proc (db-get-process)))
        (when proc
          (kill-process proc)
          (kill-buffer (db-get-buffer)))
        (setq db-region region)
        (db-set-params (db-get-server db-region)
                       (db-get-database db-region)
                       (db-get-user db-region)
                       (db-get-password db-region))
        (db-set-mode-string)
        (message "Database region set to %s" region))
    (error "Failed to set region")))

Reader Feedback 

Let me know if you found this lens useful

submit
Show All

Emacs And Other Tech 

Loading Fetching RSS feed... please stand by

Explore related pages

VIM the Powerful Text Editor
A Text Editor allows you to write plain text  unlike Wordprocessors which a...
The Magic of Emacs Comint
Comint is one of the main reasons I moved to emacs after using vi/vim for 14 yea...
Emacs Hooks - An Introduction
Hooks provide an clean, organised mechanism for extending emacs. Any function in...
Creating An Emacs Command
Whenever you press a key in emacs, a command is run. E.g. if you press 'a' it ca...
Emacs Database Mode
Emacs is a text editor, but more than that, it is a framework that enables you t...
The Magic of Emacs Comint
Comint is one of the main reasons I moved to emacs after using vi/vim for 14 yea...

Related Topics

Create a Lens!