I use sql-mode a lot. It’s incredibly handy to have my abbrevs and Emacs bindings available when I’m interacting with SQL servers. But there’s always room for improvement, so I’ve identified a few areas where sql-mode could work a bit better, and fixed them.
I found this code somwhere a while back, and threw in some enhancements:
sql-name is set, that is used in the buffer name, e.g. “*SQL sql-name*”sql-server and sql-database. If sql-server is an IP address, it’s used; if it’s a DNS name, the hostname part is used. e.g. “*SQL: dbhost/dbname*” or “*SQL 192.168.0.1/dbname”
(defun sql-make-smart-buffer-name ()
"Return a string that can be used to rename a SQLi buffer.
This is used to set `sql-alternate-buffer-name' within
`sql-interactive-mode'."
(or (and (boundp 'sql-name) sql-name)
(concat (if (not(string= "" sql-server))
(concat
(or (and (string-match "[0-9.]+" sql-server) sql-server)
(car (split-string sql-server "\\.")))
"/"))
sql-database)))
(add-hook 'sql-interactive-mode-hook
(lambda ()
(setq sql-alternate-buffer-name (sql-make-smart-buffer-name))
(sql-rename-buffer)))
At Digg, we have a lot of databases. As you may have read, we have four main pools of databases. Each of these has one write master and several slaves. We have this setup duplicated in many places for production, development, QA, and so on. While the less-stable environments don’t require the full setup, we try to balance consistency with resource usage. What we’ve settled on is one physical host running four different MySQL instance, one for each pool, each listening on a different port. Unfortunately, sql-mode has no support for this. You can set the sql-*-options variables, but this is cumbersome when you need to quickly connect to servers on different ports.
This simple advice will add sql-port into sql-mysql-options, if it’s set.
(defadvice sql-connect-mysql (around sql-mysql-port activate)
"Add support for connecting to MySQL on other ports"
(let ((sql-mysql-options (or (and (boundp 'sql-port) sql-port (cons (concat "-P " (or (and (numberp sql-port) (number-to-string sql-port)) sql-port)) sql-mysql-options)) sql-mysql-options)))
ad-do-it))
Currently, this ony works for MySQL. It should be trivial to extend the support for other databases.
This doesn’t get read interactively like the other options, so you’ll have to create a new function with a let binding to connect. This isn’t such a bad thing when you throw in the next enhancement.
It’s not much fun to enter your connection information every time you want to connect. I had some functions which used let expressions to set the correct defaults for connections, but this was suboptimal, since you had to hit RET a bunch of times before the connection would start. This is fixed with preset connections. You define sql-connection-alist with a connection name and a list of sql variables to bind. Then you may call sql-connect-preset with the connection name, and you get your connection immediately.
Note that you can specify sql-port in the connection definition. Also, sql-name is defined to the name of the connection, so you can set the buffer name there, as well.
At some point in the future, it would be a good idea to make an interactive version of sql-connect-preset, which prompts you for one of the defined connections. For now, though, you need to define a function.
Example code:
(setq sql-connection-alist
'((pool-a
(sql-product 'mysql)
(sql-server "1.2.3.4")
(sql-user "me")
(sql-password "mypassword")
(sql-database "thedb")
(sql-port 3306))
(pool-b
(sql-product 'mysql)
(sql-server "1.2.3.4")
(sql-user "me")
(sql-password "mypassword")
(sql-database "thedb")
(sql-port 3307))))
(defun sql-connect-preset (name)
"Connect to a predefined SQL connection listed in `sql-connection-alist'"
(eval `(let ,(cdr (assoc name sql-connection-alist))
(flet ((sql-get-login (&rest what)))
(sql-product-interactive sql-product)))))
(defun sql-pool-a ()
(interactive)
(sql-connect-preset 'pool-a))
Now, you can just run sql-pool-a and get connected right away. Because the buffers have good names, you can easily fire up many connections.
Discussion