Home » Computers & Electronics

HowTo Query Multiple Databases With Emacs Db Mode

Ranked #18,380 in Computers & Electronics, #364,755 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

Database Tools

Medical Search Engine Shootout: ClinicalKey Vs. UpToDate
Powered by Google

by

jareddavison2009

My Emacs Lenses:
Creating An Emacs Command
Emacs Hooks - An Introduction
All About Ediff more »

Feeling creative? Create a Lens!

Explore related pages

Related Tags