Syntax highlight your SQL HEREDOCs in VIM

I got tired of seeing my SQL in big, ugly grey walls of text in my Perl code, so I wanted to syntax highlight them. Of course, since I'm working with a large code base which has evolved over many years, it's simply not possible (or desirable) for me to rip these out and replace them with SQL::Abstract or something else (as has already been suggested) lest I spend a few weeks not developing features and hoping I didn't introduce bugs.

Fortunately, vim allows you to be rather flexible about this.

In our code, we usually have an SQL HEREDOC structure like this:

my $result = $object->do_something(<<'SQL', $var);
SELECT this
  FROM that
 WHERE bar = ?
SQL
# or
my $sql = <<"SQL";
SELECT this,
       that
  FROM $table
 WHERE $where
SQL

The key takeaway here is that our delimiters tend to be static. I wrote the following quick hack and dumped it in my .vim/after/ftplugin/perl.vim file, but it was magnificentears on Twitter who clued me into the "unlet" syntax.

syntax on
unlet b:current_syntax
syntax include @SQL syntax/sql.vim                                                                                                                                                                              
syntax region sqlSnip matchgroup=Snip start=+<<['"]SQL['"].*;\s*$+ end=+^\s*SQL$+ contains=@SQL
hi link Snip SpecialComment

Silly? Perhaps it is, but I like that extra bit of syntax coloring helping me see when I've screwed up some SQL.

Update: Here's his gist:

To those who believe I'm mistaken about it being undesirable to rip out this code, it's pretty straightforward: we refactor, but if and only if there is a clear, demonstrable benefit here and now. We do not do so for "speculative" reasons. It's one of the many reasons we have a very eye-opening work environment that is constantly causing me to reassess many of my fundamental faiths regarding how to develop software.

11 Comments

I do believe I might have to incorporate that into the core perl/syntax.vim file.

Cool. At first that didn't work at all for me. But if I removed the 'syntax on' and put the file under after/syntax/, then it worked. But I figured I was probably doing something else wrong and found 'filetype plugin indent off' in my vimrc (right after 'filetype plugin on' and 'filetype indent off'...I forget why that was there, maybe I'll find out again).

I left the 'filetype indent off' (I really hate auto indent), and put the file back under after/ftplugin, and then it worked.

If I find out that I don't like 'plugin on', then I'll turn it off put it back under 'syntax' :-)

Just because I can, (and because I tend not to quote my heredoc labels), I changed the start tag to:

start=+<<(['"]\?)SQL\1.;\s$+

Now all I have to do is change all my heredoc labels to 'SQL' (I tend to use EOT for everything, but I'm willing to change my ways for this).

@petdance - please do! I use your Perl stuff for vim! :)

It'd be nice if the region didn't start with the HEREDOC tag itself. For instance, I tweaked the snippet to work with an XML HEREDOC and the beginning << of the HEREDOC shows up in red because it's invalid XML. I tried to use a positive look-behind in the start but was unsuccessful:

syntax region perlHereDocXML start=/(<<['"]XML['"];)\@<=/ end=+^XML$+ contains=@XML

Perhaps someone with more vim-fu can get it to work

I have very little vim-fu, but this worked for me:

unlet b:current_syntax
syntax include @SQL syntax/sql.vim
syntax region sqlSnip matchgroup=Snip start=:<<\(['"]\?\)SQL\1\s*;\s*$: end=:^\s*SQL$: contains=@SQL

unlet b:current_syntax
syntax include @XML syntax/xml.vim
syntax region xmlSnip matchgroup=Snip start=:<<\(['"]\?\)XML\1\s*;\s*$: end=:^\s*XML$: contains=@XML

unlet b:current_syntax
syntax include @HTML syntax/html.vim
syntax region htmlSnip matchgroup=Snip start=:<<\(['"]\?\)HTML\1\s*;\s*$: end=:^\s*HTML$: contains=@HTML

hi link Snip SpecialComment

Brian, :help \zs and see \ze next to it. But I don’t know if that will work as expect here, either.

Brian: I just noticed that you don't have a 'matchgroup='. My understanding (which could easily be wrong) is that the matchgroup makes the start and end patterns NOT part of the region.

Ovid, you've got so many juicy bits of vim, but I would really like to see how they all fit together. Any chance of adding your config to your github account?

Leave a comment

About Ovid

user-pic Have Perl; Will Travel. Freelance Perl/Testing/Agile consultant. Photo by http://www.circle23.com/. Warning: that site is not safe for work. The photographer is a good friend of mine, though, and it's appropriate to credit his work.