This post was originally published on this site
Introduction
A customer asked me if it is possible show unused identifiers in SQL Developer. Since there is no PL/SQL compile warning for that, you might be tempted to say no. But you can always use PL/SQL Cop for static code analysis. Guideline G-1030 deals with variables and constants and guideline G-7140 with procedures and functions. However, in this case it’s also possible to achieve the same result by tweaking SQL Developer’s preferences for PL/SQL Syntax Colors.
In this blog post I explain how custom syntax highlighting works in SQL Developer. I will use a simple example first and then show how to highlight unused identifiers in SQL Developer.
What Is Syntax Highlighting?
Wikipedia defines syntax highlighting as follows
Syntax highlighting is a feature of text editors that are used for programming, scripting, or markup languages, such as HTML. The feature displays text, especially source code, in different colors and fonts according to the category of terms.(…)
How Does Syntax Highlighting Work?
The next figure illustrates the highlighting process in SQL Developer. The similarities to the formatting process are no accident.
I will explain each step and component in the next chapters.
Please note that these are conceptual components, the actual implementation might look different.
1. Parser
This step identical to formatting process. The parser reads the unformatted plain SQL or PL/SQL input and generates a parse-tree. The parse-tree is a hierarchical representation of the significant tokens of the input. In other words, there are neither whitespaces nor comments in a parse-tree.
Each node in the parse-tree includes the start and end position within the plain SQL input.
2. Custom Styler
The custom styler needs the parse-tree and the Arbori program as input.
Arbori is a query language for parse-trees. See my previous post to learn more about it. The Arbori program is configured in the SQL Developer’s preferences under Code Editor -> PL/SQL Syntax Colors -> PL/SQL Custom Syntax Rules.
2.1. The Results
The custom styler basically only runs the Arbori program. It is responsible for producing two results:
- All custom style names (dotted line to Styles)
to be shown in the preferences underCode Editor->PL/SQL Syntax Colors, so that the user can configure the foreground and background colors as well as the font style (normal, bold, italic). SQL Developer discovers styles once during startup. For changes (new or renamed styles) to take effect you need to restart SQL Developer. - A list of node-style pairs (solid line to Node Style)
to be renderer according the configured style properties (foreground color, background color and font style).
2.2. The Default Arbori Program
SQL Developer 19.4.0 provides the following default (I removed all multiline comments):
PlSqlColTabAlases: --
[node) c_alias -- Search all the nodes in the parse tree which are column aliases
| [node) identifier -- Or nodes with identifier payload,
& [node-1) query_table_expression -- which younger siblings are labeled with table names
-> -- The semantic action symbol (to trigger syntax highlighting).
; -- End of the rule
PlSqlLogger:
[pkg) name
& (?pkg = 'DBMS_OUTPUT' | ?pkg = 'APEX_DEBUG' |
?pkg = 'LOG' | ?pkg = 'logger' -- pattern match is case insensitive
)
& (pkg^ = node | pkg^^ = node)
& [node) procedure_call
->
;
The query names defined on line 1 (PlsqlColTabAlases) and line 8 (PlSqlLogger) define the style name used in the preference dialog under Code Editor -> PL/SQL Syntax Colors.
2.3. Configuring Styles
This screenshot shows how the PlSqlLogger style is configured.
2.4 JavaScript Callback Functions
The default Arbori program uses predefined Java callback functions in the CustomSyntaxStyle class.
Since SQL Developer 19.2.0 you can use embedded JavaScript callback functions. As a result Java callback functions are not necessary anymore.
Here’s an example how to change PlSqlLogger query to use a JavaScript callback function:
PlSqlLogger:
[pkg) name
& (?pkg = 'DBMS_OUTPUT' | ?pkg = 'APEX_DEBUG' |
?pkg = 'LOG' | ?pkg = 'logger' -- pattern match is case insensitive
)
& (pkg^ = node | pkg^^ = node)
& [node) procedure_call
-> {
var node = tuple.get("node");
struct.addStyle(target, node, "PlSqlLogger");
}
;
Important is line 10. It shows how to add a style for a node in the parse-tree.
2.5 JavaScript Global Variables
The following variables are provided. You should know them to get them when writing JavaScript callback functions.
target- instance of
oracle.dbtools.parser.Parsed, that’s the complete parse-tree. The following properties and methods are helpful:- src or getSrc() – list of orace.dbtools.parser.LexerToken. Indexed by node number.
- root or getRoot() – the root node.
- input or getInput() – the source text.
- instance of
tuple- instance of
HashMap<String, oracle.dbtools.parser.ParseNode>. It contains an Arbori query result row. The structure is indexed by the query node names. E.g. for the previousPlSqlLoggerquery you can accesspkg,node,pkg^,pkg^^via thegetmethod ofHashMap. Basically these are the result columns shown when you execute a query in the Arbori editor.
- instance of
struct- instance of
oracle.dbtools.raptor.plsql.language.CustomSyntaxStyle, that’s the custom styler. You need only this method:addStyle(Parsed target, ParseNode node, String styleName)
- instance of
2.6 Important Classes
Two classes are really important. I’ve listed them with some properties and methods that you might need:
ParseNodefrom– start position of the node in the parse-tree.to– end position of the node (half-open interval, this means the last including position isto-1).parent– parent node.descendants()– list of all child nodes (including their children, recursively).intermediates(int from, int to)– list of all nodes in the half-open interval.toString()– string representation of the node including all symbols.printTree()– prints a nicely formatted parse-tree on the console. This textual format is used in the Arbori documentation.
LexerTokencontent– the token represented as string.begin– start position in characters of the token in the input string.end– end position in characters of the token in the input string (half-open interval).type– type of the token.toString()– string representation of the token.
2.7 Overriding Queries for Internal Styles
The custom styler is not designed to override queries for built-in styles such as PL/SQL String.
However, you can define additional styles. Custom styles are applied at the very end of the process. As a result, you can override previously applied styles.
3. Renderer
The renderer is attached to the PL/SQL editor. It runs in the background and needs access to the plain text, the parse tree, the list of node-style pairs and the settings (foreground color, background color, font style) for each style.
Now, the renderer can loop through the internal and custom list of node-style pairs and apply the requested style to all tokens within the node. The result is a nicely highlighted document.
Example 1 – Extending PlSqlLogger
Setup
I use the default configuration of SQL Developer 19.4.0 including the standard Arbori program.
Default Highlighting Result
The result looks good. Wait, no, the line two should be displayed in grayish color, as defined for the PlSqlLogger style.
Expected Highlighting Result
That’s what we expect:
In fact, it works as expected if I omit the sys prefix. However, it is good practice to use it. Why? See the Trivadis PL/SQL & SQL Coding Guidelines for G-7510.
Code Outline
SQL Developer’s code outline is a representation of the full parse-tree. Disable all filters to show all nodes.
Arbori Editor
Type arbori in the search field and press to open the Arbori editor. Copy the PlSqlLogger query from the preferences into the Arbori editor and press run.
The query returns no result.
Why? Because [pkg) expects a node of type name. But when you look in the outline you see the that DBMS_OUTPUT is a decl_id and also an identifier. But it is no name.
Fix Arbori Program
Here’s the fix:
PlSqlLogger:
[pkg) identifier
& (?pkg = 'DBMS_OUTPUT' | ?pkg = 'APEX_DEBUG' |
?pkg = 'LOG' | ?pkg = 'logger' -- pattern match is case insensitive
)
& (pkg^ = node | pkg^^ = node | (pkg^^^ = node & ?pkg-1-1 = 'SYS'))
& [node) procedure_call
->
;
On line 2 I said that an identifier is expected for [pkg).
And on line 6 I added the or condition (pkg^^^ = node & ?pkg-1-1 = 'SYS'). This means that the grand-grandparent of the node named pkg must be the same as the node named node. node must be of type procedure_call (see line 7). Furthermore I said that the name of the previous-previous node of pkg must be SYS. That’s it.
The post Syntax Highlighting With SQL Developer appeared first on Philipp Salvisberg’s Blog.
