Syntax highlighting (in Emacs) for SQL code in log files

Question

How can we highlight SQL code in some log files, with Emacs?

Sample log file:

[DEBUG][2016-05-26 09:04:51,714][FileHelper] - [realContextPath=[/server.xml]]
[DEBUG][2016-05-26 10:24:22,369][DbConnectionImpl] - [DELETE FROM xct_log]
[DEBUG][2016-05-26 11:00:46,925][DataSourceRestrictionConverter] - [parseRestrictions]
[DEBUG][2016-05-26 10:24:22,476][DbConnectionImpl] - [UPDATE hlpdsk_step_log
SET table_name= 'hxct_log' WHERE
 table_name='xct_log' AND pkey_value = 247296]

The goal would be to put a light yellow background behind SQL code, that is:

code found between the last [...] (can be on multiple physical lines, as shown in the last line of the log sample), and
code found on lines where the "source" (3rd bracket pair) is "DbConnectionImpl".

If possible, the yellow background should only be between those last 2 brackets, on the right lines.

(Originally posted on StackOverflow, but advised to post it here.)

Is there always a DbConnectionImpl in the "source" when the text between the last 2 brackets is SQL code? — Alexej Magura, Sep 20 '16 at 14:37
Though, there are other lines with SQL code, but I don't those SQL chunks to be highlighted. — user3341592, Sep 20 '16 at 16:31
You can use `(highlight-regexp "\s\\[[^\]]+?\\]")` to highlight all text within the last brackets of each line. If you were to put this in a script and wrap in an if-statement that'll check to make sure that the line contains `DbConnection` before highlighting then that should just about do it for you. — Alexej Magura, Sep 20 '16 at 18:44
Not sure to understand how to write that "if" construct. Must be in the regexp itself? Then, how to restrict the highlighting to the 4th brackets pair? — user3341592, Sep 20 '16 at 21:57
Confirmed not working: `(highlight-regexp "\\[.*\\]\\[.*\\]\\[DbConnectionImpl\\] - \\[.*\\]" 'calendar-month-today)` highlights the whole line, and that in the whole buffer at once -- There is no line by line processing where I can put my `if` condition. — user3341592, Sep 21 '16 at 19:19
In `(highlight-regexp "\\[.*\\]`, the greedy `.*` probably matches the entire line itself. Maybe use `"\\[[^\]]*\\]"` to match everything other than a `]`? Come to think of it, I don't know of a way to make `hightlight-regexp` only highlight part of a match. — omajid, Sep 21 '16 at 22:00

omajid · Accepted Answer · 2016-09-23T01:57:58.190

First, define a mode for this log file. Put the following somewhere in emacs and eval it:

(define-derived-mode sql-log-mode text-mode "sql-log"
  "Hack"
  (setq font-lock-multiline t)
  (font-lock-add-keywords
   nil
   '(("\\[DbConnectionImpl\\] - \\[\\([^\]]*\\)\\]" 1 'font-lock-warning-face))))

This creates a new mode for the sql log file. In this mode, we use a regexp to find the text and highlight it.

Then open the log file and then M-x sql-log-mode. This will activate the mode and turn on syntax highlighting for the sql using 'font-lock-warning-face, which is probably not yellow. 'font-lock-comment-face might be better. Play around with it.

If you don't like any built-in faces, define your own face and use that directly:

(defface sql-log-mode-sql-face '((t :inherit 'font-lock-string-face
                                    :foreground "yellow"))
  "font face for sql")

(define-derived-mode sql-log-mode text-mode "sql-log"
  "Hack"
  (setq font-lock-multiline t)
  (font-lock-add-keywords
   nil
   '(("\\[DbConnectionImpl\\] - \\[\\([^\]]*\\)\\]" 1 'sql-log-mode-sql-face))))

Edit: To add highlighting for other parts, simply extend the regexp and add more face names:

(define-derived-mode sql-log-mode text-mode "sql-log"
  "Hack"
  (setq font-lock-multiline t)
  (font-lock-add-keywords
   nil
   '(("\\[\\([^\]]*\\)\\]\\[\\([^\]]*\\)\\]\\[\\(DbConnectionImpl\\)\\] - \\[\\([^\]]*\\)\\]"
      (1 'font-lock-warning-face)
      (2 'font-lock-doc-face)
      (3 'font-lock-function-name-face)
      (4 'font-lock-string-face)))))

Hi omajid, this smells very good. It does already work for SQL code on one line... But it does not work yet for the 4th line of the sample. Can you try and fix it? Thanks. — user3341592, Sep 22 '16 at 06:09
It does work, sorry! How would you colorize the different pair brackets (with severity, time, and "source") -- to compare with Lindydancer's solution? — user3341592, Sep 22 '16 at 20:38
You've responded the first to my question; hence, you win. Thanks a lot. — user3341592, Sep 23 '16 at 17:42

score 2 · Answer 2 · answered Sep 22 '16 at 11:00

The following will highlight the fields of the log file. The first three fields are highlighted using plain highlight rules, the fourth use an anchored rule to highlight multiple lines.

To use this, add fl-sql-activate to a suitable major mode hook.

See comments in the code below for implementation details.

(defface fl-sql-yellow-background '((t :background "Yellow"))
  "Face for highlighting SQL statements in log file.")

(defvar fl-sql--inside-dbconnectionimpl nil)

(defun fl-sql-activate ()
  (setq font-lock-multiline t)
  (font-lock-add-keywords
   nil
   '(("^\\[\\([^]]*\\)\\]\\[\\([^]]*\\)\\]\\[\\([^]]*\\)\\] - \\["
      ;; Plain font-lock rules. Highlight the three initial fields.
      (1 'font-lock-keyword-face)
      (2 'font-lock-constant-face)
      (3 'font-lock-function-name-face)
      ;; Anchored font-lock rule. Highlight the SQL lines.
      ;;
      ;; An "anchored" rule is effectively a search within a
      ;; search. The search is performed from where the pre-match
      ;; form place the point. The end of the inner search is
      ;; determined by the value the pre-match form is evaluated
      ;; to. In this case, the inner search is performed between
      ;; the "[" and the "]".
      ;;
      ;; The inner search use the trivial regexp ".+", causing
      ;; each line to be highlighted.
      (".+"
       ;; Pre-match form. Place point where inner search should
       ;; start and the return point where it should end.
       (progn
         (ignore-errors
           ;; Remember if this is a DbConnectionImpl line.
           (setq fl-sql--inside-dbconnectionimpl
                 (equal (match-string-no-properties 3) "DbConnectionImpl"))
           ;; Place point after the "[" (start of inner search).
           (goto-char (match-end 0))
           ;; Return location before the "]" (end of inner search).
           (save-excursion
             (backward-char)
             (forward-sexp)
             (- (point) 1))))
       ;; Post-match form
       nil
       ;; Highlights of the anchored rule. Highlight SQL
       ;; statement, but only for DbConnectionImpl lines.
       (0 (if fl-sql--inside-dbconnectionimpl
              'fl-sql-yellow-background
            nil)))))))

(add-hook 'SUITABLE-MAJOR-MODE-hook #'fl-sql-activate)

Thanks a lot, Lindydancer! For my info, if easy to explain, how would you add a yellow background behind HH:MM:SS of the same DbConnectionImpl lines? — user3341592, Sep 22 '16 at 20:40
Imagining that my next wish would be to have yellow background for SELECT, red for DELETE, green for INSERT and blue for UPDATE, would that complexify a lot your code; or would it be, on the contrary, quite straightforward? — user3341592, Sep 22 '16 at 20:42
Both things should be relatively straight forward to match. One way is to have a rule that explicitly match lines containing `DbConnectionImpl`, that way you could drop the `fl-sql--inside-dbconnectionimpl` variable from code above. Highlighting `SELECT` etc. can be done using separate rules that simply color them using a face you pick -- you might need to set the OVERRIDES flag in the rule to `prepend`, to make it highlight the words on top of the yellow background. — Lindydancer, Sep 22 '16 at 21:11
While you've done more (and better) than what I asked, I must give the points to omajid who was the first to answer correclty to the question asked. However, I respectfully thank you for your correct answer, improvements to my request, and for your comments for how to extend this in the future. Thanks a lot. (I'll give you "likes"). — user3341592, Sep 23 '16 at 17:45

Syntax highlighting (in Emacs) for SQL code in log files

2 Answers2