SQL Review
SQL review is guided by SQL development standards, helping developers discover and fix potential quality defects, performance bottlenecks, and security risks in SQL statements through static code analysis, quality rule checks, and optimization suggestions. This process aims to improve the correctness, efficiency, readability, maintainability, and security of SQL code.
Objectives
The design objectives of PawSQL's SQL review rule system are as follows:
- Use as few review rules as possible
- Cover as many SQL structures as possible
- Ensure a high review accuracy rate
PawSQL's SQL Review Rule System
Based on the above three objectives, PawSQL has designed its own SQL review rule system. The rule system is divided into three major categories based on the type of database operation performed by SQL: object design, object operations, and data operations. They do not have a one-to-one correspondence with the types of SQL statements. For example, the object design review rule "It is recommended to use precise floating-point numbers decimal or number for decimals" checks both CREATE TABLE and Alter Table Add COLUMN, as well as alter table modify column.
Even though we minimize the review rules through abstraction and parameterization, PawSQL's entire rule system includes three major categories: object design, object operations, and data operations, comprising a total of 171 review rules. These rules are applicable to most databases.
🗃️ Database Design
7 items
📄️ Object Operations
Operations on database objects typically refer to actions such as adding, deleting, and modifying various objects within the database. These operations include but are not limited to the management of tables, views, indexes, stored procedures, triggers, and other objects. Some modifications to database objects may affect the compatibility of existing applications, requiring the redesign of indexes and queries. In high-concurrency environments, structural modifications may temporarily affect service availability.
📄️ Data Manipulation
Data manipulation in SQL includes DML (Data Manipulation Language) statements and DQL (Data Query Language) statements, which mainly involve operations such as data insertion (INSERT), querying (SELECT), updating (UPDATE), and deletion (DELETE). Improper writing of these data manipulation statements may lead to various issues in correctness, security, maintainability, and performance of SQL. PawSQL has designed a total of 85 audit rules for data manipulation statements from these four perspectives.