Emacs Power: remote servers and shell commands

Emacs file and directory browsing

Emacs has Dired, a great method for browsing directories; especially in combination with ido-mode, I prefer it to Windows Explorer, OS X Finder, Gnome Nautilus, or anything else I’ve used over the decades to browse file systems. You can quickly begin entering paths, and Dired helps you with directory and file name completion.

As you browse directories, emacs lets you bookmark any path you may visit again. A very cool feature of emacs is the wide variety of shell-based modes: shell, term, interactive SQL, version control, recursive grep, and more. When you are looking at a Dired buffer and invoke any of those commands, the shell is started in the directory you are looking at.

TRAMP: powerful remote file server access

An emacs user will eventually discover that Dired becomes even more powerful with the built-in power of TRAMP (Transparent Remote (file) Access, Multiple Protocol). This extends directory path syntax to include FTP, SSH, Rsync and other protocols for accessing remote files.

An example of accessing a remote file on my development server smeagol from my laptop:

/ssh:gregj@smeagol:work/client1/web/index.php

A directory on an FTP server is accessed the same way:

/ftp:ftpuser@ftp.example.com:

Even sudo is considered a ‘protocol’; so to gain root access without leaving the comfort of your emacs session, use

/sudo::/etc/hosts

Emacs even helps you browse remote servers, providing the same name completion you get on your local directories.

The amazing combo of TRAMP and shell commands

As wonderful as all this is, we are still in the realm of “mere” GUI editors that can browse remote servers. But we are dealing with emacs, the superset of all editors, so we expect even more.

Imagine we are looking at a directory on a remote server at /ssh:myuser@remote.com:web/public_html, and decide to type

M-x shell

What happens? Why, emacs looks at our current directory, sees that it is a TRAMP remote path, and just does the Right Thing™: in this case, invokes ssh, sets the directory on the remote server to ~myuser/web/public_html, and sets us at the shell prompt.

Similarly, if we invoke version control (vc-dir, for example), or recursive grep (rgrep), or most other shell-based commands, emacs will open a secure shell first, then run the vc command (svn, git, etc) or grep on the remote server!! So for example, if I innocently invoke rgrep at the root of a remote WordPress installation, the grep command looks through all the files from the remote server. If on the other hand I were accessing the server using something like SSHFS, all those files would be transferred to my local machine first and then searched!

Some examples

I’ll save my favorite uses of this magic called Dired with TRAMP for last. I often need to access a development system or a live WordPress installation remotely. To access the SQL client, I found I often had to browse for the WordPress config file, open it, search for the database access info, open a SQL session using M-x sql-mysql and fill in all the prompts to authenticate to the MySQL server. It’s great that emacs with TRAMP starts a shell on the remote machine, and initiates the mysql client on that machine. But emacs allows you to do damn near anything you can imagine, so I realized I could write a function that does the following:

1. Looks for wp-config.php in the current directory
2. If not found, moves up a directory until it either finds the file or reaches the root of the filesystem
3. If found, opens the config file and parses the database authentication parameters
4. Feeds those parameters to the sql-mysql function and
5. plops me into the MySQL prompt all logged in and ready to go!

Another example: my iPod Touch has an SSH server running on it (don’t ask me how it got there). I have discovered that many apps use SQLite to store their data. I have been losing weight lately, and have been using the excellent Lose It! app to track my meals and exercise. The app gives me nice weekly summaries of my caloric intake, but does not give a weekly summary of my aerobic exercise. Here’s how I get that information now:

1. I have a nice bookmark to the Lose It! application directory at
   /scpc:mobile@172.16.17.118:/var/mobile/Applications/C6503545-700B-4395-9C8B-FE5B75CF6CD8/,
   so I hit the Return key on that bookmark and wait for Dired to show me the files there.
2. Browse to the Documents directory, wherein is stored the database for my personal history
3. Invoke M-x sql-sqlite and enter the database file UserDatabaseV1.sql (using dabbrev as a shortcut)
4. Wait for the SQLite prompt to appear, and run a lovely little SQL query using a YASnippets shortcut:

SELECT date('2001-01-01', '+' || Date || ' day', '-1 day', 'weekday 1', '-7 day') AS Weekdate, strftime('%W',date('2001-01-01', '+' || Date || ' day', '-1 day')) AS Week,   ExerciseName, ExerciseCategoryId, SUM(Minutes), SUM(CaloriesBurned)   FROM ExerciseLogEntries  GROUP BY Week;

Summary

I hope that this post gives an idea of the power of TRAMP on emacs. It should at least explain the occasional ecstatic post you may see from your geekier tweeps.

As a bonus, here’s what the above examples look like in use: it’s unbelievable how fast emacs makes you after a quick 15 years of study.

And here’s my emacs lisp code that opens up a SQL prompt for any WordPress installation. To use, eval the code and invoke M-x gjg/sql-mysql-wordpress

(defun gjg/parse-wp-config-db (wpconfig-path)
  "Read in and parse the DB settings from a WordPress config file; binds 'global' vars for use by sql-mode"
  (save-excursion ;; will restore current buffer and default dir afterwards
      (set-buffer (get-buffer-create (generate-new-buffer-name " wp-config.php")))
  (insert-file-contents wpconfig-path)
  ;; in regex: subexpr 1 is variable name, subexpr 3 is value: DB_{HOST,NAME,PASSWORD,USER}
      (while (search-forward-regexp "define\s*(\s*['\"]\\(DB_\\(HOST\\|NAME\\|PASSWORD\\|USER\\)\\)['\"]\s*,\s*['\"]\\([^'\"]*\\)['\"]\s*)" (point-max) 42   )
  (cond
  ((equal "DB_HOST" (match-string-no-properties 1))
  (setq sql-server (match-string-no-properties 3)))
  ((equal "DB_NAME" (match-string-no-properties 1))
  (setq sql-database (match-string-no-properties 3)))
  ((equal "DB_PASSWORD" (match-string-no-properties 1))
  (setq sql-password (match-string-no-properties 3)))
  ((equal "DB_USER" (match-string-no-properties 1))
  (setq sql-user (match-string-no-properties 3)))))
  (kill-buffer )))

  (defun gjg/sql-mysql-wordpress ()
  "Find WordPress config file in current tree, log into WP database if found."
  (interactive)
  (let ((mypath (locate-dominating-file default-directory "wp-config.php")))
  (if mypath
  (progn
  (gjg/parse-wp-config-db (concat mypath "wp-config.php"))
  (pop-to-buffer (sql-connect-mysql))
  (setq sql-interactive-product 'mysql)
  (setq sql-buffer (current-buffer))
  (sql-interactive-mode)
  (let* ((match (string-match (nth 0 tramp-file-name-structure) mypath))
  (myformat (if (eq nil match)
  (format " WordPress: local; %s; dbhost %s "
  mypath
  sql-server
  )
  (format " WordPress: Remote %s@%s %s; dbhost %s "
  (match-string (nth 2 tramp-file-name-structure) mypath)
  (match-string (nth 3 tramp-file-name-structure) mypath)
  (match-string (nth 4 tramp-file-name-structure) mypath)
  sql-server))))
  (setq header-line-format myformat))
  )
  (message "Did not find wp-config.php in current path"))
  ))

LocalWords: Dired emacs SQL Rsync smeagol pre rgrep WordPress MySQL iPod
LocalWords: SQLite