Skip to main content

SQL Code Review

NineData SQL Code Review is used for security and efficiency reviews of SQL statements before application code is released. It ensures that the released SQL statements comply with specifications and recommends appropriate indexes for SQL statements without indexes, improving the efficiency and accuracy of code releases.

Background Information​

As an essential means of database operations, SQL Code Review plays an indispensable role in ensuring the stability of online databases. In principle, to eliminate release accidents and standardize SQL statements, all unaudited SQL statements should be rejected from being released to the online environment. However, with the rapid iteration of business and the expansion of scale, relying on professional DBAs to manually review each SQL statement has become increasingly difficult to achieve.

In this reality, there is an urgent need for an automated SQL Code Review method that can ensure the high quality and security of SQL statements while the business iterates rapidly. Platform automation review becomes an innovative approach to solving this problem. By introducing automated reviews, we can track and govern the quality of SQL during the development phase, thus fundamentally avoiding the occurrence of slow SQL problems online.

What is NineData SQL Code Review?​

The NineData SQL Code Review tool provides a comprehensive solution for the production release environment of enterprises. It supports the review of all SQL statements that need to be released before the application code is released. Based on intelligent algorithms and SQL development specifications configured by the enterprise, it automatically identifies and prevents problematic SQL. Only when the SQL Code Review is passed can the application be released.

Through NineData SQL Code Review, the following objectives can be achieved:

  • Accelerate Development Iteration: SQL Code Review can quickly examine a large number of SQL statements, providing rapid feedback to the development team. This helps businesses iterate more quickly in response to market changes.
  • Improve Development Quality: Identifying and fixing SQL quality issues in the development phase can reduce the occurrence of issues online. By standardizing SQL statements, ensuring robust and efficient SQL releases, it enhances the overall quality of applications.
  • Reduce DBA Workload: Delegating mechanical and repetitive review work to SQL Code Review allows DBAs to focus more on handling more complex and advanced database management tasks, improving efficiency.

Prerequisites​

  • You have already created or joined an organization, and the organization has subscribed to . Please ensure that your annual or monthly subscription is still active. For more information, please refer to Manage Organizations.
  • Your current account has been switched to the target organization. For more information, please refer to Switching to an Organization.
  • You are using MySQL, PostgreSQL, Oracle, Oceanbase Oracle, OceanBase MySQL, DB2, TiDB, or TDSQL MySQL as your data source.
  • You have entered the data source that needs to be reviewed into the NineData console. For more information, see Create Data Source.
  • If you need to audit based on the database's full query logs, you have enabled the full logs for the data source. If not enabled, the page will prompt you to do so. Please follow the instructions on the page to enable it.
  • If you need to audit based on the database's slow query logs, you have enabled the slow query logs for the data source. If not enabled, the page will prompt you to do so. Please follow the instructions on the page to enable it.

Submitting SQL Code Review​

  1. Log in to the NineData Console.

  2. Click and then in the left navigation bar.

  3. On the page, click and configure according to the table below.

    Parameter
    		Description
    Enter the name of the SQL Code Review task. The name typically includes the purpose of the SQL statements. To reduce communication costs, use meaningful names. Limited to 64 characters.
    Select the data source on which the SQL to be reviewed will act.
    Select the database on which the SQL to be reviewed will act.
    (Optional)Notes for unfinished tasks in the current SQL Code Review.
    Choose the SQL source that needs to be reviewed.
    • : Enter the SQL statement to be reviewed directly in the text box.
    • : Click , select and upload the file containing the SQL statements to be reviewed. Multiple files can be uploaded simultaneously.
      Note: To delete uploaded SQL files, move the mouse over the file name after uploading, and click the delete icon on the right side of the file name to delete the file.
    • : Upload XML files or ZIP compressed packages containing XML files.
    • : Upload compressed packages containing code files (XML files, SQL files).
    • : Automatically review slow SQL based on the slow query logs in the database. You also need to select the time range of slow SQL generation in . The default is the previous 24 hours from the current time point, with a maximum selectable range of 24 hours, and data from the previous month can be selected.
    • : Automatically review online SQL based on the full query logs in the database. You also need to select the time range of SQL generation in . The default is the previous 12 hours from the current time point, with a maximum selectable range of 24 hours, and data from the previous month can be selected.
    • : View only the review results of the current SQL statement.
    • : Combine the latest historical SQL Code Review results that have been approved, and view the differential results of the current SQL statement.

  4. Click to enter the process. In this process, the system will automatically pre-review the SQL you submitted and provide optimization suggestions based on intelligent algorithms. According to the SQL Development Specifications associated with the current data source, there are several possible outcomes:

    • Pre-review passed: Depending on the configuration of the approval process, the task status will change to or . If it is the former, proceed to the next step. If it is the latter, the process ends.
    • Pre-review failed: The task status will change to . You can click at the top right of the page to pre-review again, or click to edit and resubmit.
    tip

    You can optimize your SQL statements and indexes based on the optimization suggestions and index recommendations provided by the system. For more information, see View Optimization Recommendations.

  5. Click at the top right of the page. In the popup window, select the approval personnel for the process and click .

    tip
    • The number of approval personnel to be selected here depends on the configuration of the approval process. Select according to the actual situation.

    • If the feature is enabled in the current approval process, this section will display . You do not need to manually specify an approver; all personnel authorized to approve the current work order will receive approval notifications and be able to approve it.

  6. The task status changes to . Before approval, you can perform the following operations:

    • : Withdraw the SQL Code Review. For more information, see Undo SQL Code Review.
    • : Change the approver for this SQL Code Review.

View Optimization Recommendations​

  1. Log in to the NineData Console.

  2. Click and then in the left navigation bar.

  3. On the page, click the task ID of the target SQL Code Review or click in the column on the right side of the task.

  4. On the page, you can view optimization recommendations generated for this SQL Code Review task, including and . On this page, you can perform the following operations.

    review_details

    OperationDescription
    View overall optimization recommendationsUnder the tab, display the total number of suggestions and suggestions for each SQL. Click on the right of and (only shown when there are optimization recommendations) to view overall optimization recommendations.
    Filter SQL statementsUnder the tab, click the dropdown menu in the upper right corner to filter SQL statements that match corresponding categories such as , , , , , etc.
    View details of and Under the tab, hover over the number on the right of the target SQL statement under column or . If there are optimization recommendations for this SQL statement, a tooltip will appear with specific content.
    View optimization recommendations for the target SQL statementUnder the tab, click in the column on the right of the target SQL statement. This will open the page, where you can view detailed and for this SQL statement, as well as the SQL statement itself. Additionally, you can click on the and tabs on this page to view the execution plan and metadata information for this SQL statement.
    View overall SQL detailsClick the tab to view all SQL statements in the current SQL Code Review task.
    tip

    Explanation of and :

    • : Audits SQL statements based on the SQL development specifications bound to the current data source and provides optimization suggestions.
    • : Provides index optimization suggestions based on artificial intelligence auditing of SQL statements.

Undo SQL Code Review​

When the status of SQL Code Review is , , or , you can withdraw the application for this SQL Code Review. After withdrawal, you can re-edit the task and submit it again.

Prerequisites

  • The status of SQL Code Review is , , or .
  • You are the or the of the target SQL Code Review.

Operational Steps

  1. Log in to the NineData Console.
  2. In the left navigation bar, click > .
  3. On the page, find the target SQL Code Review task, click on the task ID or click in the column on the right of the task. On the page, click in the upper right corner.
  4. In the pop-up confirmation window, click or .
    • : The task status changes to . If you need to re-edit the task, you can click on the SQL Code Review ID in the list to enter the page, and then click in the upper right corner to re-edit the task.
    • : The task status changes to and automatically jumps to the editing page of SQL Code Review.

Approval of SQL Code Review​

Prerequisites

  • The status of SQL Code Review is .
  • You are the or the of the target SQL Code Review.

Operational Steps

  1. Log in to the NineData Console.

  2. In the left navigation bar, click > .

  3. On the page, find the target task, click the task ID, or click in the column on the right of the task.

  4. On the page, approve the target SQL Code Review and choose , , or according to the actual situation.

    Operation
    		Description
    Transfer Task Transfer the task to another user for approval.
    Approve Application Approve the application. After clicking , you can enter the as needed and click .
    Reject Application Reject the application. After clicking , you also need to enter the and click .
Last updated on