SQL formatter/uppercase indent automatic formatting tool | ZeroTools

The core of this system is an advanced lexical analysis engine that accurately tokenizes the input relational database manipulation language and converts it into an abstract syntax tree.

While there are standards for structured query languages ​​established by the American National Standards Association and the International Organization for Standardization, in actual systems each database management system implements its own extended syntax.

In addition to standard grammar rules, this analysis engine comprehensively recognizes the unique dialects of widely used product groups.

Specifically, it accurately identifies Postgres' typecasting syntax and array operators, MyEscuel's backtick-based identifier escapes, unique built-in functions, and Esculite's flexible syntax based on dynamic typing.

In addition, Oracle's hierarchical query functions, partition outer joins, vendor-specific non-standard keywords such as top clauses and cross-apply operators unique to SQL servers are also tokenized at high speed using a dedicated dictionary tree.

The input string is scanned by stream processing and divided into components such as keywords, identifiers, literals, and operator symbols, and then reassembled by a syntax analyzer into tree-structured data with parent-child relationships depending on the context.

This process forms the basis for sophisticated context-sensitive relocation operations that are not possible with simple string substitution.

A reconstruction process is performed to visually clarify the structure of the code while traversing the abstract syntax tree constructed by lexical analysis.

The first step is to apply a reserved word capitalization algorithm to clarify instructions to the database engine. All major keywords, such as the from clause that specifies the data acquisition source, the where clause that defines extraction conditions, the join clause that joins tables, and the group-by clause that narrows down conditions and the order-by clause that controls the sorting order, are forcibly converted to uppercase letters after determining the token type.

This creates a contrast with identifiers such as user-defined table names and column names, allowing you to instantly understand the skeleton of your code.

In the second processing stage, indentation control and line break position optimization are performed based on a logical hierarchical structure.

The multiple retrieved columns connected to the select clause are aligned according to the specified indentation width, and the conditional expressions in the where clause with consecutive AND and or logical operators are rearranged so that each is on an independent line.

In particular, the ON condition in a join clause is given an appropriate offset to minimize line-of-sight movement while maintaining the same logical hierarchy level as the table name to be joined, so even complex multiple joins can be transformed into a structure in which the relationships between tables can be intuitively understood.

This system is equipped with a function to maintain context-dependent inline comments and block comments in order to format the source code without destroying the intent or notes written in it.

Single-line comments starting with two hyphens or multi-line comments surrounded by slashes and asterisks are protected as special annotation nodes in the abstract syntax tree, and are recombined at the appropriate position following the rearrangement of adjacent syntax elements.

In particular, a dedicated nesting depth tracking algorithm is activated for subqueries that increase query complexity. When a parenthesized subquery is detected, the parser recursively applies a formatting function to the internal syntax tree to calculate a base offset that is the indentation level of the parent query plus the subquery's own hierarchy depth.

This process allows logical containment relationships to be accurately expressed as indentation depth even in multiple nested inline views and scalar subqueries.

Furthermore, in order to flexibly comply with the coding conventions of the development site, we provide formatting options that dynamically control the placement of commas that separate columns and conditional expressions.

From the general style of placing a comma at the end of a line to the style of placing a comma at the beginning of a line to minimize differences in the version control system when adding or deleting columns, by changing the output order of delimiters in the string generation phase from the abstract syntax tree, we can generate output results with a high degree of freedom to meet your requirements.

Files that describe operations for a company's core system or customer information management database contain highly confidential information such as schema information indicating table structure and specific business data included in extraction conditions.

This system employs an architecture that executes all lexical analysis, syntax analysis, and string generation algorithms on the client's web browser in order to complete the formatting process without sending this confidential information to an external server.

The raw query string input is processed only in local memory space without any intervening external API communication or processing layer on the backend server.

This makes it possible to fundamentally eliminate the security risk of data leakage, even when analyzing log data for acquiring execution plans output from the production environment database or raw slow query error logs extracted for failure investigation by pasting them into the input area as they are.

Even in an offline environment, you can continue to use the same formatting function by using the cache function of the web application, so you can continue to perform safe code formatting even in environments where strict network policies are applied, such as secure development rooms with restricted communication environments or financial institutions.

Once formatted, structured string data is immediately passed to a syntax highlighting engine that assists in visual analysis. This highlighting engine does not simply match string patterns, but instead operates by inheriting the attribute information of each token output from the abstract syntax tree, achieving highly accurate color coding.

Reserved words are highlighted with strong blue tones, and string literals and numerical data are drawn in warm colors such as green and orange that are easy to identify.

Additionally, by mapping system functions and user-defined variables in different colors, it becomes easier to visually trace the life cycle of variables and function calling relationships within long scripts.

The generated colored source code is displayed in the rendering area and provides an interface that allows it to be transferred to the operating system's clipboard with a single click.

At this time, the data copied to the clipboard is extracted as pure plain text without any decorative tags for highlighting, creating a seamless work flow that can be pasted directly into integrated development environments, query editors of database management tools, internal chat tools, and ticket management systems for immediate execution and sharing.

The readability of a structured query language is an extremely important element that is directly linked to query tuning, which affects system performance, and the quality of code reviews within the development team.

In the field of small query tuning that involves analysis of execution plans, the first step in identifying performance bottlenecks is to accurately understand the complex intertwined index reference conditions and table join order.

In the code formatted by this system, each condition in a where clause is expanded as an independent line, and the priority of logical operators is visualized by indentation, making it possible to immediately detect visual discomfort when omitting a condition or writing an inappropriate or condition that causes an unintended full scan.

In addition, in the peer review process through pull requests, by committing code that has been formatted according to unified formatting conventions, reviewers can focus their thinking resources on verifying the core parts of the architecture, such as the validity of data extraction logic and consistency with business requirements, without being distracted by non-essential differences such as personal writing habits.

Furthermore, by instantly structuring the inorganic one-line application logs output during the operation and maintenance phase with this system, the lead time for restoring the data state and investigating the cause in the event of a failure is greatly shortened, contributing to improved system availability.