SQL templates

Search tools

A variable is a sequence of characters enclosed in dollar signs, for example, $name$ or $field$.

The screenshot below shows a template used to search for structs.

Modifiers are user-defined conditions that can be applied to variables and search templates. They allow you to:

• check how many times a variable occurs,

• validate the variable’s type,

• match the variable against plain text or a regular expression,

• apply a Groovy script.

For the complete list of available modifiers, see Search templates, modifiers, and script constraints.

The next example shows the same template as before, but with the $name$ variable using the Text modifier set to Employee. Notice how the highlighted search results in the editor have changed.

If we apply the type modifier with the [string] value, we will find all the string values in our SELECT statements. But to find all the string types in the table definition (the CREATE TABLE statement), you need to apply the types context. To set context, you also use filters. But in contrast to variables, where the filter is applied to a specific variable, context filters are applied to the whole search template.

Contexts are built-in internal templates that help the search engine identify the variable. Contexts are used only for SQL to eliminate language ambiguities. By using context, you can tell the search engine where and what variable to search. You can set the following contexts for search templates:

• expressions: one or more values, operators, or SQL functions that result in to a value. With the expressions context, IDE will look for $expression_1$ and $expression_2$ in structures like SELECT $expression_1$ FROM table_name where $expression_2$.

• types: the type of value that can be stored in a table column (for example, varchar(100)). With the types context, IDE will look for $type_1$ and $type_2$ in structures like CREATE TABLE table_name (id_1 $type_1$, id_2 $type_2$).

• query clauses: query clauses like DISTINCT, FROM, WHERE, ORDER BY, GROUP BY, or HAVING. In the query clauses context, you need to use a clause keyword with a variable. For example, the IDE will look for where $variables$ in structures like select * from table_name $variables$;. If you have an expression like a + b in the WHERE clause, you can specify your variables explicitly like where $variable_1$ + $variable_2$;. And define any variable as a search target.

The Search target is a list in which you can specify what part of your template the IDE should search: a single variable or the whole template. For example, you can use search targets to find the $b$ value from the where $a$ + $b$ search template with context = query clause.

The Scope defines sources of information where you want to perform your search. It can be open or current files, directories in the Project tool window Alt+1, recently changed files, and other sources. To search in the specific directory, select Directory, press the Browse button.

The File type defines dialects of files in which you want to search.

How do SQL templates for structural search work

Let's see how the search engine processes search templates. Consider that you have the WHERE $a$ search template in the search field, and you set context = query clause.

1. GoLand takes the definition of the query clauses context template. For the query clauses context template, the definition is SELECT id from dummy $pattern$. You can see all existing context templates under the Existing Templates menu item.

2. The IDE replaces the $pattern$ variable with the value that you defined in the search template. In our case, it is WHERE $a$. The replacement result will look like: SELECT id from dummy WHERE $a$.

3. The IDE parses the final SELECT statement and searches for WHERE $a$ in the syntax tree.

4. When WHERE $a$ is found, the IDE highlights it in the editor.

Search and replace structurally