Method and system for completing SQL (Structured Query Language) statements of database security management and control platform based on syntax tree

Abstract

According to the syntax tree-based database security management and control platform SQL statement completion method and system provided by the invention, accurate completion suggestions are provided according to the structures and logics of the SQL statements, relatively good processing capability is also provided for complex SQL statements such as nested sub-query and joint query, syntax errors in the SQL statements can be accurately identified, and the SQL statements can be completely completed according to the syntax tree-based database security management and control platform SQL statement completion method and system. And the functions of SQL statement formatting, code highlighting and the like are also easy to realize. According to the method, it is ensured that for each table name in historical data, the sorting score can be calculated based on time decay and specific periodic disturbance, on the whole, the score of long-term data is made to be low, the score of recent data is made to be high, and meanwhile the repeatability of the data is considered.

Description

Technical Field

[0001] The present invention relates to a syntax tree-based SQL statement completion method and system for a database security management and control platform, belonging to the technical field of database security management. Background Art

[0002] In recent years, information technology has developed rapidly, and the amount of data generated and stored in various industries has increased exponentially. As the core of data storage, databases have attracted much attention. The use and management of databases is a very complex task. The larger the amount of database data, the more complex its use and management. In this case, in order to ensure the security of database assets, database security management has become an important task. The database security management platform contains many functions, such as data protection, identity authentication and access control, data masking and desensitization, etc. In the process of using the database security management platform, providing a convenient SQL (Structured Query Language) editor to facilitate developers to design accurate SQL statements is one of the necessary functions.

[0003] SQL is the standard language for accessing relational databases. The emergence of SQL provides great flexibility and convenience for data operations. However, it is not easy to master SQL overnight. Even if you have a certain programming foundation and have learned database theory before, it is not easy for SQL beginners with no experience to design SQL statements. The grammatical structure of SQL itself is relatively complex. If you want to write complex queries, you not only need to be familiar with SQL syntax, but also need to combine it with the specific structure of the database. Therefore, even experienced SQL developers are often very inefficient when faced with complex query logic.

[0004] In order to improve the efficiency of SQL statement design, many auxiliary tools have emerged, such as SQL statement completion tools. When users write SQL statements, SQL statement completion tools can automatically provide code suggestions based on the content that users have entered and the known database structure, thereby helping users to build SQL statements faster. This function can bring several benefits: First, SQL statement completion replaces the manual method of writing SQL statements to a certain extent, improves work efficiency, and reduces the large number of input errors that may occur in the manual method; secondly, SQL statement completion provides prompts such as table names and field names, which reduces the difficulty of writing SQL statements for users who are not familiar with the database structure.

[0005] Currently, the SQL statement completion function is mainly implemented through the following methods:

[0006] (1) Keyword matching-based method: This method provides completion suggestions based on the user input. For example, when the user enters part of the beginning of a table name or column name, the tool will list all matching complete table names or column names for the user to choose from. The keyword matching-based method is the most basic implementation method with high efficiency, but it is difficult to provide sufficient help for complex queries such as multi-table join queries.

[0007] (2) Template-based method: Create templates based on common SQL query patterns and generate completion suggestions based on these templates. This method has obvious advantages and disadvantages. The completion effect is good for SQL statements covered by the template; conversely, the completion effect is poor for SQL statements not covered by the template. Therefore, the cost of template maintenance is high. For less conventional or more complex queries, it is difficult to have an adapted template, so the SQL completion effect is not good.

[0008] (3) Methods based on statistics or machine learning: By analyzing the user's historical query records, learning the user's query habits, and giving completion suggestions based on this; or training deep learning models to predict the user's query intent and giving completion suggestions based on the intent. This type of method is extremely dependent on the quality of the historical data used for training. Low-quality historical data or noisy data has a significant negative impact on the quality of SQL completion. In addition, the training and maintenance costs of the model are also high. BRIEF DESCRIPTION OF THE DRAWINGS

[0009] Figure 1 A SQL statement fragment.

[0010] Figure 2 It is a system module and component diagram and data flow process of the present invention.

[0011] Figure 3 The user inputs SQL and the SQL code used to generate the syntax tree.

[0012] Figure 4 Use record for table name.

[0013] Figure 5 Flowchart of the table name sorting algorithm. Summary of the invention

[0014] In order to solve the problems existing in the prior art, the present invention proposes a SQL statement completion method for a database security management platform based on a syntax tree. The method uses a parser to convert part of the SQL statement input by the user into a syntax tree, and then predicts the next possible content based on the syntax tree structure and context information. Specifically, the method includes the following steps:

[0015] (1) Syntax tree construction: The user enters the SQL statement in the editor to generate the original syntax tree;

[0016] (2) Syntax tree enhancement: This module receives the original syntax tree generated by the syntax tree construction module, traverses the original syntax tree, adds an ID and a parent node ID to each node, then marks the table name and column name, and finally adds type information to the clause node, and outputs the enhanced syntax tree;

[0017] (3) Completion suggestion generation: Completion suggestions are given based on the enhanced syntax tree and the current cursor position;

[0018] (4) Database object management: Return database object information based on database object information request;

[0019] (5) Editor integration: Display completion suggestions in the editor UI;

[0020] When displaying the completion, the SQL statement completion results are displayed to the user in order of keywords, table names, and column names. Newer records are given higher ranking scores, older records are given lower ranking scores, and disturbances are added to the attenuation function.

[0021] Furthermore, in the editor integration module, sorting is performed according to keywords, table names, and column names, specifically including the following steps:

[0022] 3.1 Prepare the table name sequence x n ,x n-1 ,…,x2,x1, and initialize an empty set S to store the processed table names and their corresponding ranking scores. n is the number of table names in the historical records, x i Indicates the name of the table with serial number i, 1≤i≤n.

[0023] 3.2 Calculation parameters Among them, α max and α min are the maximum and minimum values ​​of the preset parameter α, log() represents the logarithm with base 10, and n max is the preset maximum value of n;

[0024] 3.3 Calculate the ranking score for the table name with serial number i, i starts from 1 and loops to n. The calculation process is:

[0025] ① Calculate the ranking score f(i) = e -αi (1+b×sin 2 ((1-α)i+c)), where sin() is the sine function, b∈[0,1), and c is the phase shift;

[0026] ②Query whether the table name x already exists in the set S i If the table name is x i If the information already exists in set S, then find the table name x from set S. iThe corresponding ranking score is recorded as At this time, the calculation table name x i New ranking score and will Update to set S; if table name x i The information does not exist in the set S before, so let the table name x i Ranking score and will Add to the set S.

[0027] ③ Check the table name x in the table name sequence i Check whether there are other table names after the search. If there are other table names, jump to step ①; if there are no other table names, it means that the search has been completed, and go to step 3.4.

[0028] 3.4 Sort the table names in set S according to the sorting scores from large to small.

[0029] Based on the above method, the present invention further proposes a SQL statement completion system for a database security management and control platform based on a syntax tree, the system comprising:

[0030] (1) Syntax tree construction module: This module receives the SQL statement input by the user and generates the original syntax tree;

[0031] (2) Syntax tree enhancement module: This module receives the original syntax tree generated by the syntax tree construction module, traverses the original syntax tree, adds an ID and a parent node ID to each node, then marks the table name and column name, and finally adds type information to the clause node and outputs the enhanced syntax tree;

[0032] (3) Completion suggestion generation module: This module gives completion suggestions based on the enhanced syntax tree and the current cursor position;

[0033] (4) Database object management module: This module returns database object information according to the database object information request;

[0034] (5) Editor integration module: This module displays completion suggestions in the editor UI;

[0035] When displaying the completion, the SQL statement completion results are displayed to the user in order of keywords, table names, and column names. Newer records are given higher ranking scores, older records are given lower ranking scores, and disturbances are added to the attenuation function.

[0036] Furthermore, in the editor integration module, sorting is performed according to keywords, table names, and column names, specifically including the following steps:

[0037] 3.1 Prepare the table name sequence x n ,x n-1,…,x2,x1, and initialize an empty set S to store the processed table names and their corresponding ranking scores. n is the number of table names in the historical records, x i Indicates the name of the table with serial number i, 1≤i≤n.

[0038] 3.2 Calculation parameters Among them, α max and α min are the maximum and minimum values ​​of the preset parameter α, log() represents the logarithm with base 10, and n max is the preset maximum value of n;

[0039] 3.3 Calculate the ranking score for the table name with serial number i, i starts from 1 and loops to n. The calculation process is:

[0040] ① Calculate the ranking score f(i) = e -αi (1+b×sin 2 ((1-α)i+c)), where sin() is the sine function, b∈[0,1), and c is the phase shift;

[0041] ②Query whether the table name x already exists in the set S i If the table name is x i If the information already exists in set S, then find the table name x from set S. i The corresponding ranking score is recorded as At this time, the calculation table name x i New ranking score and will Update to set S; if table name x i The information does not exist in the set S before, so let the table name x i Ranking score and will Add to the set S.

[0042] ③ Check the table name x in the table name sequence i Check whether there are other table names after the search. If there are other table names, jump to step ①; if there are no other table names, it means that the search has been completed, and go to step 3.4.

[0043] 3.4 Sort the table names in set S according to the sorting scores from large to small.

[0044] The present invention can provide accurate completion suggestions based on the structure and logic of SQL statements, and has good processing capabilities for complex SQL statements such as nested subqueries and joint queries. It can accurately identify syntax errors in SQL statements, and functions such as SQL statement formatting and code highlighting are also easy to implement. The present invention ensures that for each table name in historical data, the ranking score can be calculated based on time decay and specific periodic disturbances, so that older data scores are lower and more recent data scores are higher, while taking into account the repeatability of the data. DETAILED DESCRIPTION

[0045] Example 1

[0046] Based on the above analysis, the present invention designs a SQL statement completion method based on the Abstract Syntax Tree (AST), named AST-SQL. This method uses a parser to convert part of the SQL statement input by the user into a syntax tree, and then predicts the next possible content based on the syntax tree structure and context information as a completion suggestion.

[0047] A syntax tree is a tree-like representation of the syntax structure of the source code. That is, the structure of the program is represented in the form of a tree, and each node on the tree represents a syntax structure, such as an expression, statement, or type declaration. Specifically in SQL, the syntax tree can clearly represent the various components of an SQL statement in a hierarchical manner. Because the AST-SQL method can obtain the structure and logic of SQL statements through the syntax tree, it can provide accurate completion suggestions based on the current context; even for complex SQL statements such as nested subqueries and union queries, it has good processing capabilities. At the same time, the implementation basis of this method is a syntax tree, so it has an innate advantage in the ability to identify syntax errors in SQL statements. In addition, functions such as SQL statement formatting and code highlighting can be easily implemented as auxiliary functions based on the syntax tree.

[0048] In the AST-SQL method, each node of the syntax tree has some fixed properties, including:

[0049] (1)id: Each node has a unique ID.

[0050] (2) type: Each node has a type attribute, which indicates the type of the node, including statement, clause, keyword, property_access, identifier, comma, function_call, parenthesis, operator, and literal.

[0051] (3)index: All elements of the children array have an index attribute, which facilitates forward and backward searching.

[0052] According to the definition of nodes in the AST-SQL method, taking a simple SQL statement as an example, it can be roughly divided into the following Figure 1 Middle fragment.

[0053] Based on the AST-SQL method, the present invention designs a syntax tree-based SQL statement completion system, the key components of which include:

[0054] (1) SQLFormatterAst: This component is used to generate a syntax tree for SQL statements. In the database field, this component is relatively mature. Usually, the core of tools used to format SQL statements is to first build a syntax tree around the SQL statement.

[0055] (2) SqlAst: used to parse the syntax tree generated by SQLFormatterAst. The meta-information contained in the syntax tree is relatively conventional. In order to be suitable for use in SQL statement completion, additional meta-information is added to the conventional syntax tree, such as node ID (id field), parent node ID (parentId field), etc. This additional meta-information helps to establish the hierarchical relationship between different nodes, providing a prerequisite for improving the efficiency of the method. The component ultimately outputs an enhanced syntax tree that contains structural information about the SQL statement.

[0056] (3) SqlAdvisor: Determines appropriate completion suggestions based on the cursor position and syntax tree. This component locates the node where the cursor is located, analyzes the node context (such as clause type, node type, etc.), and generates completion suggestions based on the analysis results. Finally, the completion suggestions are output, including the completion type (table name, column name, keyword, etc.) and related context information.

[0057] (4) DatabaseObjectStore: stores information about database objects (such as tables and columns) and is used to provide completion options. This component loads the corresponding database object information according to the user's permissions and provides completion options based on the request.

[0058] (5) SqlCompletionProvider: implements the completion logic in the editor, including displaying completion suggestions. In terms of process, this component receives the completion suggestions generated by SqlAdvisor, generates completion items in the editor based on the completion suggestions, and then displays the completion items to the user.

[0059] The key modules of the system are:

[0060] (1) Syntax tree construction: This module receives the SQL statement input by the user and then uses the SQLFormatterAst component to generate the original syntax tree.

[0061] (2) Syntax tree enhancement: This module receives the original syntax tree generated by the syntax tree construction module, and then uses the SqlAst component to perform the following operations:

[0062] 1) Traverse the original syntax tree and add information such as ID and parent node ID to each node.

[0063] 2) Mark specific nodes, such as table names, column names, etc.

[0064] 3) Add type information to the clause node.

[0065] Finally, the enhanced syntax tree is output.

[0066] (3) Completion suggestion generation: Completion suggestions are given based on the enhanced syntax tree and the current cursor position. This module mainly uses the SqlAdvisor component, which first locates the node where the cursor is located, then analyzes the context of the cursor node, determines the completion type, and finally generates completion suggestions, including the completion type and context information.

[0067] (4) Database object management: Based on the DatabaseObjectStore component, this module returns database object information according to the database object information request.

[0068] (5) Editor integration: This module uses the SqlCompletionProvider component to provide completion suggestions and displays the completion suggestions in the editor UI.

[0069] The complete module and component diagram of the SQL statement completion system based on AST-SQL and the data flow process are as follows Figure 2 The main implementation steps are:

[0070] (1) Syntax tree construction

[0071] The user enters SQL statements in the editor. During the input process, the editor records the cursor position. In the AST-SQL method, the cursor position is marked with a special string __cursor__. The SQLFormatterAst component is used to parse the SQL statement with the __cursor__ mark and generate the original syntax tree.

[0072] It should be noted that the user may enter many SQL statements in an SQL editor, and when completing SQL statements, only the SQL statement where the cursor is located needs to be analyzed, rather than all SQL statements in the entire SQL editor. SQL statements are separated by semicolons. The method to obtain the SQL statement where the cursor is located is to find the semicolon position closest to the cursor and the semicolon position closest to the cursor. The content between these two semicolons is the SQL statement where the cursor is located. There are two situations in which the semicolons used in this process are not included: one is the semicolon in the SQL comment, such as --Hello; world, and the other is the semicolon in the string, such as 'Hello; world'.

[0073] Figure 3 A specific comparison is given between the SQL input by the user and the SQL used to generate the syntax tree (where | represents a cursor).

[0074] (2) Syntax tree enhancement

[0075] In this step, the SqlAst component enhances the generated original syntax tree and assigns each node information such as ID, type, parent node, and child node. It traverses the clauses in the syntax tree and performs different marking operations according to the clause type, such as marking table names, column names, etc.

[0076] (3) Cursor position positioning

[0077] The SqlAdvisor component searches for nodes marked with __cursor__ to determine the node where the cursor is located. It searches for the parent node of the cursor node through a recursive method until the nearest clause node is found. It determines the completion type (table name, column name, keyword, etc.) based on the type and position of the cursor node.

[0078] (4) Completion suggestion generation

[0079] SqlAdvisor generates completion suggestions based on the position and context of the cursor node. In simple terms, the completion suggestions are actually to get what type of data should be prompted to enter at the current cursor position, such as table name, column name, common keyword. Therefore, the content generated by this part of the completion suggestions is relatively trivial and includes more clues, mainly including the following situations:

[0080] 1) Complete the table name:

[0081] The completed table names in the system include view names. The situations in which the table names are completed mainly include:

[0082] In the FROM clause: Traverse forward in the syntax tree. If ON is encountered, complete the column name; if JOIN is encountered, complete the table name; if there is a comma or the first element in front, complete the table name.

[0083] INSERT INTO clause: Completes the table name and may need to automatically add an alias when completing the table name.

[0084] INSERT IGNORE INTO clause: Complete the table name.

[0085] REPLACE INTO clause: Completes the table name.

[0086] DELETE FROM clause: Complete the table name.

[0087] · UPDATE clause: Complete the table name.

[0088] DROP TABLE clause: Complete the table name.

[0089] CREATE VIEW clause: The view name does not need to be completed.

[0090] VALUES clause: The clause contains the values ​​entered by the user and does not need to be completed.

[0091] DROP VIEW clause: Complete the view name.

[0092] TRUNCATE TABLE clause: Complete the table name.

[0093] ALTER TABLE clause: Complete the table name.

[0094] 2) Complete the column name:

[0095] SELECT clause: Complete the column names based on the table in the FROM clause.

[0096] WHERE clause: Complete the column name.

[0097] GROUP BY clause: Complete the column name.

[0098] ORDER BY clause: Complete the column name.

[0099] HAVING clause: Complete the column name.

[0100] 3) Complete keywords:

[0101] By default, in other cases, complete the keyword.

[0102] (5) Database object retrieval

[0103] According to the type of completion suggestion, the corresponding database object is retrieved from the DatabaseObjectStore component. If some tables or columns have not been loaded, they are loaded asynchronously and the completion list is updated.

[0104] For table name completion, if the user enters a schema in SQL, then the table under the schema will be completed; if no schema is entered, then all tables will be completed.

[0105] For column name completion, if the user enters a table name, the columns of the table are completed; if no table name is entered, the columns are completed based on the table in the FROM clause.

[0106] (6) Completion display in the editor

[0107] The SqlCompletionProvider component displays the completion suggestions generated by the SqlAdvisor component to the user. The display of completion suggestions takes into account the differences between different types of prompt items, such as keywords, table names, column names, etc., and supports different types of icons.

[0108] In the completion display, a sorting algorithm is designed, which can sort by keyword, table name, and column name respectively. When the SQL statement completion results are displayed to the user, they are sorted according to the results of the sorting algorithm and displayed. When calculating, the main idea of ​​the algorithm is to give higher sorting scores to newer records and lower sorting scores to older records, which includes a decay function. At the same time, in order to avoid the decay process being too ideal, a slight disturbance is added to the decay function.

[0109] The system stores the user's usage records of keywords, table names, and column names, such as the table name usage records. Figure 4 shown.

[0110] The usage record of table names includes the table names and the order of usage. In the above example, users visited Table4, Table3, Table2, Table1, and Table2 in sequence. Table4 at the beginning was used a long time ago, while Table2 at the end was used recently. By numbering the table names in reverse order of actual usage, we can get the serial numbers corresponding to each table name. The serial number of Table2 at the end is 1, the serial number of Table4 at the beginning is 10, and the others are numbered in sequence. The method and processing process of keywords and column names are the same as those of table names, so the algorithm is introduced here only using table names as an example.

[0111] According to the above rules, let the sequence of table names be x n ,x n-1 ,…,x2,x1, where n is the number of table names in the historical records. It should be noted that there may be duplications among these n table names. i Indicates the name of the table with serial number i, 1≤i≤n.

[0112] The specific steps of the algorithm are as follows Figure 5 The detailed information of each step is as follows:

[0113] 1) Initialization

[0114] Prepare table name sequence x n ,x n-1 ,…,x2,x1, and initialize the empty set S to store the processed table names and their corresponding sorting scores.

[0115] 2) Parameter calculation

[0116] Calculate the parameter α, which will be used in the following steps. The calculation method is:

[0117]

[0118] Among them, α max and α min are the maximum and minimum values ​​of the preset parameter α, log() represents the logarithm with base 10, and n max It is the preset maximum value of n.

[0119] 3) Historical data traversal

[0120] Calculate the sorting score for the table name with serial number i. Note that i starts from 1 and is calculated cyclically to n.

[0121] The calculation process is:

[0122] ① Calculate the ranking score: The calculation method is:

[0123] f(i)=e -αi (1+b×sin 2 ((1-α)i+c))

[0124] Among them, sin() is the sine function, b∈[0,1) controls the amplitude of the periodic disturbance, and c is the phase shift, which controls the starting position of the disturbance.

[0125] ②Duplicate table name check:

[0126] Check whether table name x already exists in set S i information.

[0127] If the check result shows table name x i The information of already exists in the set S, then find the table name x from the set S i The corresponding ranking score is recorded as At this time, the calculation table name x i The new ranking score is calculated as follows: And the calculated table name xi The new ranking score Update to the set S.

[0128] If the check result shows table name x i The information does not exist in the set S before, so let the table name x i Ranking score And name the table x i and the calculated ranking score Add to the set S.

[0129] ③Traversal:

[0130] View the table name sequence, table name x i Check whether there are other table names after the traversal. If there are other table names, jump to step a to continue processing; otherwise, if there are no other table names, it means that the traversal has been completed and continue with the next step.

[0131] 4) Result output

[0132] The set S stores the processed table names and their corresponding sorting scores. The table names are sorted from large to small according to the sorting scores to obtain the table name order for display output.

[0133] The algorithm ensures that for each table name in the historical data, a ranking score can be calculated based on time decay and specific periodic disturbances, which overall makes older data have lower scores and more recent data have higher scores, while taking into account the repeatability of the data.

[0134] Example 2

[0135] The following is a specific example to illustrate the use of the SQL statement completion system.

[0136] Assume there are three SQL statements as follows:

[0137]

[0138] These three SQL statements are not complete, and each SQL statement is actually being edited. In the SQL editor, there should be a SQL statement completion suggestion for the possible input content at the current position of each SQL statement (the "|" in the above three SQL statements represents the current input position, that is, the cursor position). The specific process is as follows:

[0139] (1) Syntax tree construction

[0140] The user enters SQL statements in the editor. During the input process, the editor records the cursor position. In the AST-SQL method, the cursor position is marked with a special string __cursor__. According to this method, the above three SQL statements are converted into:

[0141]

[0142] Then, use the SQLFormatterAst component to parse the SQL statement with the __cursor__ tag and generate the original syntax tree.

[0143] (2) Syntax tree enhancement

[0144] In this step, the SqlAst component enhances the generated original syntax tree and assigns information such as ID, type, parent node, and child node to each node. It traverses the clauses in the syntax tree and performs different marking operations according to the clause type, such as marking table names, column names, etc. According to this method, the enhanced syntax trees corresponding to the above three SQL statements are:

[0145]

[0146]

[0147]

[0148] (3) Cursor position positioning

[0149] The SqlAdvisor component searches for nodes marked with __cursor__ to determine the node where the cursor is located. It searches for the parent node of the cursor node through a recursive method until the nearest clause node is found. It determines the completion type (table name, column name, keyword, etc.) based on the type and position of the cursor node.

[0150] From the generated syntax tree, we can see that the above three SQL statements should complete the column name, table name and table name respectively.

[0151] (4) Completion suggestion generation

[0152] SqlAdvisor generates completion suggestions based on the position and context of the cursor node. In simple terms, completion suggestions are actually to get what type of data should be prompted for the current cursor position, such as table name, column name, and common keywords. From the generated syntax tree, it can be seen that the above three SQL statements should prompt column name, table name, and table name respectively.

[0153] (5) Database object retrieval

[0154] According to the type of completion suggestion, the corresponding database object is retrieved from the DatabaseObjectStore component. If some tables or columns have not been loaded, they are loaded asynchronously and the completion list is updated.

[0155] For table name completion, if the user enters a schema in SQL, then the table under the schema will be completed; if no schema is entered, then all tables will be completed.

[0156] For column name completion, if the user enters a table name, the columns of the table are completed; if no table name is entered, the columns are completed based on the table in the FROM clause.

[0157] For the above three SQL statements, the first SQL statement needs to add column names, but since no table name is provided, no prompt can be given; for the second and third SQL statements, table names need to be added. At this time, the corresponding database tables are retrieved from the DatabaseObjectStore component to obtain a table name list.

[0158] (6) Completion display in the editor

[0159] The SqlCompletionProvider component displays the completion suggestions generated by the SqlAdvisor component to the user. The display of completion suggestions takes into account the differences between different types of prompt items, such as keywords, table names, column names, etc., and supports different types of icons.

[0160] For the above three SQL statements, the first SQL statement needs to add column names, but since no table name is provided, no prompt can be given; for the second and third SQL statements, the table name needs to be added. At this time, the corresponding database table is retrieved from the DatabaseObjectStore component to obtain a table name list and provide it to the SqlAdvisor component. This is to check whether some tables in the obtained table name list have been used by the user before. If some tables can be found in the usage records, the ranking scores of these tables need to be calculated and displayed in front of the prompt area; for tables that are not used in the usage records, they are arranged in alphabetical order and displayed behind the prompt area.

[0161] The calculation of the ranking score uses a sorting algorithm that can sort by keyword, table name, and column name. When the SQL statement completion results are displayed to the user, they are sorted according to the results of the sorting algorithm. When calculating, the main idea of ​​the algorithm is to give higher ranking scores to newer records and lower ranking scores to older records. It contains a decay function. At the same time, in order to avoid the decay process being too ideal, a slight disturbance is added to the decay function.

[0162] Assume a set of ordered table name historical data: X = {x 10,x9,…,x1}={"student","scores","courses","student","departments","scores","student","scores","courses","student"}, the specific steps of the algorithm are:

[0163] 1) Initialization

[0164] Initialize an empty set S to store processed table names and their corresponding ranking scores, n=10.

[0165] 2) Parameter calculation

[0166] Assume a max =0.5,a min =0.1,n max =15, b = 0.2, c = 0.5, according to the given formula

[0167]

[0168] The parameter α can be calculated, α≈0.366.

[0169] 3) Historical data traversal

[0170] Calculate the sorting score for the table name with serial number i. Note that i starts from 1 and is calculated cyclically to n.

[0171] The calculation process is:

[0172] ① Calculate the ranking score: The calculation method is:

[0173] f(i)=e -αi (1+b×sin 2 ((1-α)i+c))

[0174] Among them, sin() is the sine function, b∈[0,1) controls the amplitude of the periodic disturbance, and c is the phase shift, which controls the starting position of the disturbance.

[0175] ②Duplicate table name check:

[0176] Check whether table name x already exists in set S i information.

[0177] If the check result shows table name x i The information of already exists in the set S, then find the table name x from the set S i The corresponding ranking score is recorded as At this time, the calculation table name x i The new ranking score is calculated as follows: And the calculated table name x i The new ranking score Update to the set S.

[0178] If the check result shows table name x i The information does not exist in the set S before, so let the table name x i Ranking score And name the table x i and the calculated ranking score Add to the set S.

[0179] ③Traversal:

[0180] View the table name sequence, table name x i Check whether there are other table names after the traversal. If there are other table names, jump to step a to continue processing; otherwise, if there are no other table names, it means that the traversal has been completed and continue with the next step.

[0181] The specific calculation process is:

[0182] When i=1, the table name x1 is processed, that is, the table name "student".

[0183] f(1)=e -0.366×1 (1+0.2×sin 2 ((1-0.366)×1+0.5))≈0.697

[0184] Add the table name "student" and its ranking score 0.697 to set S.

[0185] When i=2, process table name x2, that is, table name "courses",

[0186] f(2)=e -0.366×2 (1+0.2×sin 2 ((1-0.366)×2+0.5))≈0.483

[0187] Add the table name "courses" and its ranking score 0.483 to the set S.

[0188] When i=3, the table name x3 is processed, that is, the table name "scores".

[0189] f(3)=e -0.366×3 (1+0.2×sin 2 ((1-0.366)×3+0.5))≈0.332

[0190] Add the table name "scores" and its ranking score 0.332 to the set S.

[0191] When i=4, the table name x4 is processed, that is, the table name "student".

[0192] f(4)=e -0.366×4 (1+0.2×sin 2 ((1-0.366)×4+0.5))≈0.228

[0193] Because the table name "student" already exists in the set S, find the ranking score corresponding to the table name x4 from the set S, recorded as Update its weight based on this:

[0194]

[0195] Update the ranking score of the table name "student" in set S to 0.732.

[0196] When i=5, the table name x5 is processed, that is, the table name "scores".

[0197] f(5)=e -0.366×5 (1+0.2×sin 2 ((1-0.366)×5+0.5))≈0.157

[0198] Since the table name "scores" already exists in the set S, find the ranking score corresponding to the table name x5 from the set S, recorded as Update its weight based on this:

[0199]

[0200] Update the ranking score of the table name "scores" in set S to 0.415.

[0201] By analogy, the subsequent steps are:

[0202] When i=6, add the table name "departments" and its ranking score 0.105 to the set S.

[0203] When i=7, the ranking score of the table name "student" in the set S is updated to 0.753.

[0204] When i=8, update the ranking score of the table name "courses" in the set S to 0.502.

[0205] When i=9, update the ranking score of the table name "scores" in the set S to 0.432.

[0206] When i=10, update the ranking score of the table name "student" in the set S to 0.767.

[0207] 4) Result output

[0208] The set S stores the processed table names and their corresponding ranking scores. By sorting the table names from large to small according to the ranking scores, the table name order for display output can be obtained. The results of the above example are as follows:

[0209]

[0210] Finally, the four table names, students, courses, scores, and departments, can be displayed in order in the completion suggestions and presented to the user.

[0211] From the algorithm sorting process, it can be seen that the sorting algorithm has the following advantages: ① Simple and easy to implement. The algorithm does not require complex mathematical models or deep learning frameworks, and is easy to understand and implement; it does not require a large amount of computing resources, and is less difficult to implement. ② Time sensitivity. The algorithm takes into account the time decay factor, which means that more recent data has a higher score, which helps to capture the latest trends with higher accuracy. ③ Duplicate data processing. The algorithm effectively handles the problem of duplicate data. The score calculation method accurately reflects the repeatability of these data points and adapts to the time decay characteristics at the same time. ④ Fast response. The algorithm can quickly calculate results, which is very useful for application scenarios that require real-time feedback.

Claims

1. A SQL statement completion method for a database security management platform based on a syntax tree. The method uses a parser to convert part of the SQL statement input by the user into a syntax tree, and then predicts the next possible content based on the syntax tree structure and context information. The method specifically includes the following steps: (1) Syntax tree construction: The user enters the SQL statement in the editor to generate the original syntax tree; (2) Syntax tree enhancement: This module receives the original syntax tree generated by the syntax tree construction module, traverses the original syntax tree, adds an ID and a parent node ID to each node, then marks the table name and column name, and finally adds type information to the clause node, and outputs the enhanced syntax tree; (3) Completion suggestion generation: Completion suggestions are given based on the enhanced syntax tree and the current cursor position; (4) Database object management: Return database object information based on database object information request; (5) Editor integration: Display completion suggestions in the editor UI; Its characteristics are as follows: when displaying completion, the SQL statement completion results are displayed to the user in order of keywords, table names, and column names, higher ranking scores are given to newer records, lower ranking scores are given to older records, and disturbances are added to the attenuation function.

2. A method for completing SQL statements of a database security management and control platform based on a syntax tree as claimed in claim 1, characterized in that: In the editor integration module, sorting is performed based on keywords, table names, and column names. The specific steps include: 3.1 Prepare the table name sequence x n ,x n-1 ,…,x2,x1, and initialize an empty set S to store the processed table names and their corresponding ranking scores. n is the number of table names in the historical records, x i Indicates the name of the table with serial number i, 1≤i≤n. 3.2 Calculation parameters Among them, α max and α min are the maximum and minimum values ​​of the preset parameter α, log() represents the logarithm with base 10, and n max is the preset maximum value of n; 3.3 Calculate the ranking score for the table name with serial number i, i starts from 1 and loops to n. The calculation process is: ① Calculate the ranking score f(i) = e -αi (1+b×sin 2 ((1-α)i+c)), where sin() is the sine function, b∈[0,1), and c is the phase shift; ②Query whether the table name x already exists in the set S i If the table name is x i If the information already exists in set S, then find the table name x from set S. i The corresponding ranking score is recorded as At this time, the calculation table name x i New ranking score and will Update to set S; if table name x i The information does not exist in the set S before, so let the table name x i Ranking score and will Add to the set S. ③ Check the table name x in the table name sequence i Check whether there are other table names after the search. If there are other table names, jump to step ①; if there are no other table names, it means that the search has been completed, and go to step 3.

4. 3.4 Sort the table names in set S according to the sorting scores from large to small.

3. A SQL statement completion system for a database security management and control platform based on a syntax tree, the system comprising: (1) Syntax tree construction module: This module receives the SQL statement input by the user and generates the original syntax tree; (2) Syntax tree enhancement module: This module receives the original syntax tree generated by the syntax tree construction module, traverses the original syntax tree, adds an ID and a parent node ID to each node, then marks the table name and column name, and finally adds type information to the clause node and outputs the enhanced syntax tree; (3) Completion suggestion generation module: This module gives completion suggestions based on the enhanced syntax tree and the current cursor position; (4) Database object management module: This module returns database object information according to the database object information request; (5) Editor integration module: This module displays completion suggestions in the editor UI; Its characteristics are as follows: when displaying completion, the SQL statement completion results are displayed to the user in order of keywords, table names, and column names, higher ranking scores are given to newer records, lower ranking scores are given to older records, and disturbances are added to the attenuation function.

4. A syntax tree-based database security management and control platform SQL statement completion system as claimed in claim 3, characterized in that: In the editor integration module, sorting is performed based on keywords, table names, and column names. The specific steps include: 3.1 Prepare the table name sequence x n ,x n-1 ,…,x2,x1, and initialize an empty set S to store the processed table names and their corresponding ranking scores. n is the number of table names in the historical records, x i Indicates the name of the table with serial number i, 1≤i≤n. 3.2 Calculation parameters in, α max and α min are the maximum and minimum values ​​of the preset parameter α, log() represents the logarithm with base 10, and n max is the preset maximum value of n; 3.3 Calculate the ranking score for the table name with serial number i, i starts from 1 and loops to n. The calculation process is: ① Calculate the ranking score f(i) = e -αi (1+b×sin 2 ((1-α)i+c)), where sin() is the sine function, b∈[0,1), and c is the phase shift; ②Query whether the table name x already exists in the set S i If the table name is x i If the information already exists in set S, then find the table name x from set S. i The corresponding ranking score is recorded as At this time, the calculation table name x i New ranking score and will Update to set S; if table name x i The information does not exist in the set S before, so let the table name x i Ranking score and will Add to the set S. ③ Check the table name x in the table name sequence i Check whether there are other table names after the search. If there are other table names, jump to step ①; if there are no other table names, it means that the search has been completed, and go to step 3.

4. 3.4 Sort the table names in set S according to the sorting scores from large to small.

Images