1

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
  • Yes, always -- if you mean "SQL code to highlight". – user3341592 Sep 20 '16 at 16:30
  • 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
  • ... And those DON'T have DbConnectImpl in the "source". – user3341592 Sep 20 '16 at 17:36
  • 1
    <s>In (highlight-regexp "\\[.*\\], the greedy .* probably matches the entire line itself. Maybe use "\\[[^\]]*\\]" to match everything other than a ]?</s> Come to think of it, I don't know of a way to make hightlight-regexp only highlight part of a match. – Omair Majid Sep 21 '16 at 22:00
1
+50

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 6:09
  • It worked for me :( I will test again later. – Omair Majid Sep 22 '16 at 16:51
  • 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
2

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
  • @user3341592, fair enough. Good luck with your package! – Lindydancer Sep 23 '16 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.