{"id":10514,"date":"2020-09-07T07:56:01","date_gmt":"2020-09-07T05:56:01","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=10514"},"modified":"2023-11-12T13:52:17","modified_gmt":"2023-11-12T12:52:17","slug":"highlight-hints-in-sql-developer","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2020\/09\/07\/highlight-hints-in-sql-developer\/","title":{"rendered":"Highlight Hints in SQL Developer"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

In this blog post, I explain how you can configure your SQL Developer to highlight hints and distinguish them from ordinary comments. The SQL Language Reference<\/a> for Oracle Database 19c defines hints as follows:<\/p>\n\n\n\n

\n

Hints are comments in a SQL statement that pass instructions to the Oracle Database optimizer. The optimizer uses these hints to choose an execution plan for the statement, unless some condition exists that prevents the optimizer from doing so.<\/p>\n<\/blockquote>\n\n\n\n

Furthermore<\/p>\n\n\n\n

\n

A statement block can have only one comment containing hints, and that comment must follow the SELECT<\/code>, UPDATE<\/code>, INSERT<\/code>, MERGE<\/code>, or DELETE<\/code> keyword.<\/p>\n<\/blockquote>\n\n\n\n

And here’s the syntax diagram:<\/p>\n\n\n\n

\n
\"\"<\/a><\/figure>\n<\/blockquote>\n\n\n\n

You see the comment containing hints starts with a +<\/code>. The syntax for a hint is simplified. string<\/code> must be read as a placeholder for various hint-specific options. You find a list of officially supported hints in the SQL Language Reference<\/a>.<\/p>\n\n\n\n

In this blog post<\/a>, I covered how syntax highlighting works in SQL Developer and how to add a custom styler. If you are interested in the details, then I recommend reading this blog post first.<\/p>\n\n\n\n

The Problem<\/h2>\n\n\n\n

A custom styler written in Arbori works with nodes provided in the parse-tree (target<\/code>). However, the parse-tree does not contain whitespace nor comments. We still have access to the source code through the parse-tree (target.input<\/code>) and therefore to all comments. For example, by creating a token stream via LexerToken.parse(target.input, true)<\/code> and looking for tokens of type LINE_COMMENT<\/code> and COMMENT<\/code>.  That’s not the problem.<\/p>\n\n\n\n

The problem is, that there is no functionality to style a token. We only can style a node by calling struct.addStyle(target, node, 'MyStyle')<\/code>. We cannot highlight hints with this method.<\/p>\n\n\n\n

Let’s dig deeper.<\/p>\n\n\n\n

The global variable struct<\/code> is an instance of the CustomSyntaxStyle<\/code> class. Here’s an excerpt based on the representation in IntelliJ IDEA:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

The excerpt shows the complete implementation of the addStyle<\/code> method. The tokens of the passed node are added to a local field named styles<\/code>. This field is defined on line 33 without an access modifier<\/a> and therefore it is not accessible by the Arbori program.<\/p>\n\n\n\n

What can we do?<\/p>\n\n\n\n

The Approach<\/h2>\n\n\n\n

We cannot solve the problem. Only the SQL Developer team can. But we can work around it. We can access the hidden field styles<\/code> through Java reflection. Have a look at this excellent tutorial<\/a> by Jakob Jenkov, if you want to learn more. We will use this approach in the Arbori program.<\/p>\n\n\n\n

The Arbori Program to Highlight Hints<\/h2>\n\n\n\n

This Arbori program applies the style Hints<\/code> for all hints within a worksheet or PL\/SQL editor. I’ve tested it successfully with SQL Developer 19.2, 19.4 and 20.2.<\/p>\n\n\n\n

Arbori Program Hints<\/span><\/path><\/path><\/svg><\/span>
Hints<\/span>: <\/span><\/span>\n  [<\/span>node<\/span>) <\/span>sql_statements<\/span> <\/span><\/span>\n  -> {<\/span><\/span>\n    var <\/span>getHints<\/span> = <\/span>function<\/span>() {<\/span><\/span>\n      <\/span>var<\/span> <\/span>LexerToken<\/span> = <\/span>Java<\/span>.<\/span>type<\/span>(<\/span>'oracle.dbtools.parser.LexerToken'<\/span>); <\/span><\/span>\n      <\/span>var<\/span> <\/span>Token<\/span> = <\/span>Java<\/span>.<\/span>type<\/span>(<\/span>'oracle.dbtools.parser.Token'<\/span>);<\/span><\/span>\n      <\/span>var<\/span> <\/span>tokens<\/span> = <\/span>LexerToken<\/span>.<\/span>parse<\/span>(<\/span>target<\/span>.<\/span>input<\/span>, <\/span>true<\/span>);<\/span><\/span>\n      <\/span>var<\/span> <\/span>hints<\/span> = [];<\/span><\/span>\n      <\/span>var<\/span> <\/span>prevToken<\/span> = <\/span>tokens<\/span>[<\/span>0<\/span>];<\/span><\/span>\n      <\/span>for<\/span> (<\/span>var<\/span> <\/span>i<\/span>=<\/span>1<\/span>; <\/span>i<\/span><<\/span>tokens<\/span>.<\/span>size<\/span>(); <\/span>i<\/span>++) {<\/span><\/span>\n        <\/span>if<\/span> ((<\/span>tokens<\/span>[<\/span>i<\/span>].<\/span>type<\/span> == <\/span>Token<\/span>.<\/span>LINE_COMMENT<\/span> || <\/span>tokens<\/span>[<\/span>i<\/span>].<\/span>type<\/span> == <\/span>Token<\/span>.<\/span>COMMENT<\/span>) && <\/span>tokens<\/span>[<\/span>i<\/span>].<\/span>content<\/span>.<\/span>length<\/span> > <\/span>3<\/span>) {<\/span><\/span>\n          <\/span>if<\/span> (<\/span>tokens<\/span>[<\/span>i<\/span>].<\/span>content<\/span>.<\/span>substring<\/span>(<\/span>2<\/span>, <\/span>3<\/span>) == <\/span>"+"<\/span>) {<\/span><\/span>\n            <\/span>var<\/span> <\/span>prev<\/span> = <\/span>prevToken<\/span>.<\/span>content<\/span>.<\/span>toLowerCase<\/span>();<\/span><\/span>\n            <\/span>if<\/span> (<\/span>prev<\/span> == <\/span>"select"<\/span> || <\/span>prev<\/span> == <\/span>"insert"<\/span> || <\/span>prev<\/span> == <\/span>"update"<\/span> || <\/span>prev<\/span> == <\/span>"delete"<\/span> || <\/span>prev<\/span> == <\/span>"merge"<\/span>) {<\/span><\/span>\n              <\/span>hints<\/span>[<\/span>hints<\/span>.<\/span>length<\/span>] = <\/span>tokens<\/span>[<\/span>i<\/span>];<\/span><\/span>\n              <\/span>prevToken<\/span> = <\/span>tokens<\/span>[<\/span>i<\/span>]<\/span><\/span>\n            }<\/span><\/span>\n          }<\/span><\/span>\n        }<\/span><\/span>\n        <\/span>if<\/span> (<\/span>tokens<\/span>[<\/span>i<\/span>].<\/span>type<\/span> != <\/span>Token<\/span>.<\/span>WS<\/span> && <\/span>tokens<\/span>[<\/span>i<\/span>].<\/span>type<\/span> != <\/span>Token<\/span>.<\/span>LINE_COMMENT<\/span> && <\/span>tokens<\/span>[<\/span>i<\/span>].<\/span>type<\/span> != <\/span>Token<\/span>.<\/span>COMMENT<\/span>) {<\/span><\/span>\n          <\/span>prevToken<\/span> = <\/span>tokens<\/span>[<\/span>i<\/span>];<\/span><\/span>\n        }<\/span><\/span>\n      }<\/span><\/span>\n      <\/span>return<\/span> <\/span>hints<\/span>;<\/span><\/span>\n    }<\/span><\/span>\n<\/span>\n    var <\/span>styleHints<\/span> = <\/span>function<\/span>(<\/span>hints<\/span>) {<\/span><\/span>\n      <\/span>var<\/span> <\/span>Service<\/span> = <\/span>Java<\/span>.<\/span>type<\/span>(<\/span>'oracle.dbtools.util.Service'<\/span>);<\/span><\/span>\n      <\/span>var<\/span> <\/span>Long<\/span> = <\/span>Java<\/span>.<\/span>type<\/span>(<\/span>'java.lang.Long'<\/span>);<\/span><\/span>\n      <\/span>var<\/span> <\/span>stylesField<\/span> = <\/span>struct<\/span>.<\/span>getClass<\/span>().<\/span>getDeclaredField<\/span>(<\/span>"styles"<\/span>);<\/span><\/span>\n      <\/span>stylesField<\/span>.<\/span>setAccessible<\/span>(<\/span>true<\/span>);<\/span><\/span>\n      <\/span>var<\/span> <\/span>styles<\/span> = <\/span>stylesField<\/span>.<\/span>get<\/span>(<\/span>struct<\/span>);<\/span><\/span>\n      <\/span>for<\/span> (<\/span>var<\/span> <\/span>i<\/span> <\/span>in<\/span> <\/span>hints<\/span>) {<\/span><\/span>\n        <\/span>var<\/span> <\/span>pos<\/span> = <\/span>new<\/span> <\/span>Long<\/span>(<\/span>Service<\/span>.<\/span>lPair<\/span>(<\/span>hints<\/span>[<\/span>i<\/span>].<\/span>begin<\/span>, <\/span>hints<\/span>[<\/span>i<\/span>].<\/span>end<\/span>));<\/span><\/span>\n        <\/span>styles<\/span>.<\/span>put<\/span>(<\/span>pos<\/span>, <\/span>"Hints"<\/span>);<\/span><\/span>\n      }<\/span><\/span>\n    }<\/span><\/span>\n<\/span>\n    <\/span>\/\/ main<\/span><\/span>\n    <\/span>styleHints<\/span>(<\/span>getHints<\/span>());<\/span><\/span>\n  }<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Here are some explanations:<\/p>\n\n\n\n