Method for generating SQL (structured query language) from natural language based on bidirectional mapping and semantic analysis

Abstract

The invention provides a method for generating an SQL (Structured Query Language) by a natural language based on bidirectional mapping and semantic parsing, which relates to the technical field of database query and comprises the following steps of: extracting natural language query elements and packaging the natural language query elements into structured data, and establishing a mapping relationship from a query field to a service attribute and a physical data table by adopting a bidirectional Hash index technology; and automatically identifying multi-table association keys, performing semantic extension and compliance verification, generating an abstract syntax tree, performing processing according to user permission, and finally converting the abstract syntax tree into an SQL statement conforming to a target database syntax specification. According to the method, the accuracy and the efficiency of converting the natural language into the SQL are improved, and the flexibility and the safety of the system are enhanced.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database query, and in particular to a natural language generated SQL method based on bidirectional mapping and semantic analysis. BACKGROUND

[0002] With the rapid development of big data and artificial intelligence technology, natural language processing is increasingly widely used in the field of database query. Traditional database query requires users to master SQL language, which has a high threshold for non-professional technical personnel. Natural language generated SQL technology aims to solve this problem, allowing users to express query requirements in daily language, automatically converting them into standard SQL statements to perform database operations, thereby greatly improving the convenience and universality of data acquisition. Currently, natural language to SQL technology mainly relies on rule template matching, machine learning and deep learning methods. In practical applications, these technologies usually need to perform semantic analysis on natural language queries, extract key information, and then generate corresponding SQL statements according to the database structure.

[0003] The prior art has low mapping efficiency when processing complex queries. Traditional one-way mapping methods are difficult to effectively handle the ambiguity and ambiguity between natural language query words and database table structures, especially when facing multi-table association queries, they cannot accurately identify and construct the association relationship between tables, resulting in incomplete or logically incorrect SQL statement structures. The prior art lacks an effective semantic expansion mechanism. When the user's query expression is incomplete or contains implicit conditions, it cannot automatically supplement the necessary query conditions and table connection information, resulting in inaccurate query results or query failure, which cannot meet the user's real intention and seriously affects the query efficiency and user experience. The prior art generally ignores data permission control. In enterprise-level application scenarios, different user roles have different access permissions to data, but the prior art lacks a flexible permission control mechanism when converting natural language to SQL, and cannot automatically adjust the query range and filtering conditions according to the user role, which poses a potential data security risk. SUMMARY

[0004] The embodiment of the present application provides a natural language generated SQL method based on bidirectional mapping and semantic analysis, which can solve the problems in the prior art.

[0005] In a first aspect, the embodiment of the present application provides a natural language generated SQL method based on bidirectional mapping and semantic analysis, comprising: extracting query elements in a natural language query request input by a user role, and encapsulating them as structured data; The structured data is mapped by using a bidirectional hash index technology, a mapping relationship between a query field and a standard business attribute is established in a forward hash table, and a mapping relationship between the standard business attribute and a physical data table is constructed in a reverse hash table; through bidirectional joint retrieval, based on query field information in the structured data, a target data table and its associated table are located, and an associated key between multiple tables is automatically identified, the associated key is used to perform semantic expansion on a query condition in the structured data, necessary table connection conditions are automatically supplemented, and a complete query structure is formed; The complete query structure is subjected to compliance verification, entity linking and relationship reasoning are performed, and the query condition is replaced to obtain a standardized query expression; The standardized query expression is subjected to syntax analysis to generate an abstract syntax tree containing a query semantic structure, permission configuration information of a user role is read, the abstract syntax tree is pruned based on the permission configuration information, and a data permission filtering condition is appended; the abstract syntax tree subjected to the permission processing is converted into an SQL statement conforming to a syntax specification of a target database.

[0006] The structured data is mapped by using a bidirectional hash index technology, a mapping relationship between a query field and a standard business attribute is established in a forward hash table, and a mapping relationship between the standard business attribute and a physical data table is constructed in a reverse hash table includes: Feature codes are obtained by extracting query field information from the structured data and performing numerical conversion, a forward mapping table is constructed for the query field information, and an initial detection position is obtained by performing a remainder operation on the feature codes and a capacity value of the forward mapping table; when the initial detection position is occupied, a linear detection sequence is generated based on the feature codes, and the linear detection sequence is detected position by position until a free storage position is found; The query field information is converted into a query field feature vector, an inner product value is obtained by calculating a dot product of the query field feature vector and a preset standard business attribute feature vector, a module length value of the two feature vectors is calculated, and a similarity score is obtained by dividing the inner product value by a product of the two module length values; when the similarity score is greater than a preset similarity threshold, a mapping relationship between the query field information and the standard business attribute is established in the free storage position of the forward mapping table; Physical data table information in the structured data is extracted, the standard business attribute is used as a hash key value, and a reverse hash mapping table is constructed by using a linked list storage structure; for each standard business attribute, a corresponding relationship between the standard business attribute and a physical data table is analyzed based on a business rule, and the corresponding relationship is stored as a linked list node; when a hash conflict occurs, a new linked list node is added to the end of the corresponding linked list, and a mapping relationship between the standard business attribute and the physical data table is established through the reverse hash mapping table.

[0007] By bidirectional joint retrieval, based on the query field information in the structured data, the target data table and its associated tables are located, and the association keys between multiple tables are automatically identified. The association keys are used to semantically expand the query conditions in the structured data, automatically supplement necessary table connection conditions, and form a complete query structure including: Based on the results of the query field information in the forward mapping, the target physical table is retrieved in the reverse mapping, the associated tables of the target physical table are obtained by analyzing the data relationship of the target physical table, and the target physical table and the associated tables are constructed into a table association graph. The vertices in the table association graph represent physical tables, and the edges represent the association relationship between tables. Scan the table structure definition of the target physical table to obtain the primary foreign key constraint, use the primary foreign key constraint as an explicit association relationship, extract word frequency features from the field naming in the target physical table and the associated tables, and use the word frequency features with a similarity greater than a preset potential association threshold as potential association fields. The explicit association relationship and the potential association fields are integrated to obtain association keys. Analyze the distribution position of the association keys in the table association graph to determine the shortest connection path between tables; analyze the fields of the tables in the shortest connection path for association, identify fields with the same business meaning and extract the table identifiers, generate corresponding table connection statements, sort the table connection statements according to the order of the shortest connection path, supplement table connection conditions based on the sorted table connection statements, and integrate the query conditions in the structured data and the supplemented table connection conditions to form a complete query structure.

[0008] The complete query structure is checked for compliance, entity linking and relationship reasoning are performed, and the query conditions are replaced to obtain a standardized query expression including: Check the legality of the table name and field name in the complete query structure to generate a verification result; extract entity mention information from the verification result, calculate the string edit distance and context semantic similarity based on the entity mention information, and select target entities based on the string edit distance and context semantic similarity. Retrieve the shortest path between the target entities, extract the semantic features of the shortest path; input the semantic features into a predefined Horn clause rule set for forward reasoning to obtain a relationship derivation chain between the target entities; calculate the confidence of each path in the relationship derivation chain, and determine the path with a confidence greater than a relationship confidence threshold as an effective semantic association. Extract the query condition features in the complete query structure, map and match the query condition features with the corresponding standard attribute constraints of the effective semantic association, select the highest matching degree of the standard attribute constraint to replace the query condition features, combine with other query condition features in the complete query structure, and form a standardized query expression.

[0009] The syntax analysis of the standardized query expression generates an abstract syntax tree containing a query semantic structure, reads the permission configuration information of the user role, prunes the abstract syntax tree based on the permission configuration information, and appends a data permission filtering condition, comprising: An Antlr parser is constructed based on the lexical rules for identifying identifiers, keywords and operators and the syntax rules for defining the syntax structure of the query statement; the syntax analysis of the standardized query expression is performed using the Antlr parser to generate a node set containing semantic types, attribute values and hierarchical relationship information, and the node set is connected to obtain an abstract syntax tree containing a query semantic structure; Read the permission configuration information of the user role to obtain the permission inheritance relationship of the user role and the row-level data filtering condition set; Based on the permission inheritance relationship, traverse the node set of the abstract syntax tree, check the access permission of each node, delete the nodes without access permission from the abstract syntax tree, and obtain the pruned syntax tree; parse each filtering condition in the row-level data filtering condition set into a predicate expression, construct a condition node based on the predicate expression, and organize the condition node into a filtering condition sub-tree; in the where clause position of the pruned syntax tree, connect the filtering condition sub-tree with the query condition in the pruned syntax tree through logical and operation.

[0010] Converting the abstract syntax tree after permission processing into a SQL statement conforming to the syntax specification of the target database comprises: Obtain the dialect type of the target database, construct the data type representation features, function naming features, paging syntax features and time date processing features corresponding to the dialect type into a dialect feature vector, generate a corresponding dialect specific syntax based on the dialect feature vector, extract the dependency relationship of the standard syntax and the dialect specific syntax, form a context constraint condition, and combine the dialect specific syntax and the context constraint condition into a syntax rule mapping set; Traverse the nodes of the abstract syntax tree after permission processing to obtain the matching degree of each node with each rule in the syntax rule mapping set; select the rule with the highest matching degree from the syntax rule mapping set as the optimal syntax conversion rule; The node of the abstract syntax tree after the permission processing is converted by using the optimal syntax conversion rule, the syntax structure of the converted adjacent nodes is ensured to meet the syntax specification requirement of the target database based on the context constraint condition, and a SQL statement meeting the dialect specification of the target database is generated.

[0011] In a second aspect of the embodiment of the application, a natural language SQL generation system based on bidirectional mapping and semantic analysis is provided, comprising: A first unit is configured to extract query elements in a natural language query request input by a user role and encapsulate the query elements as structured data. A second unit is configured to map the structured data by using a bidirectional hash index technology, establish a mapping relationship between query fields and standard business attributes in a forward hash table, and construct a mapping relationship between the standard business attributes and physical data tables in a reverse hash table, locate a target data table and its associated tables based on query field information in the structured data by joint retrieval in the two directions, automatically identify associated keys between the tables, perform semantic expansion on query conditions in the structured data by using the associated keys, automatically supplement necessary table connection conditions, and form a complete query structure. A third unit is configured to perform compliance verification on the complete query structure, perform entity linking and relationship reasoning, and replace query conditions to obtain a standardized query expression. A fourth unit is configured to perform syntax analysis on the standardized query expression, generate an abstract syntax tree containing a query semantic structure, read permission configuration information of a user role, prune the abstract syntax tree based on the permission configuration information, and append a data permission filtering condition, and convert the abstract syntax tree after the permission processing into a SQL statement meeting the syntax specification of a target database.

[0012] In a third aspect of the embodiment of the application, An electronic device is provided, comprising: a processor; a memory for storing processor-executable instructions; The processor is configured to invoke the instructions stored in the memory to execute the method described above.

[0013] In a fourth aspect of the embodiment of the application, A computer-readable storage medium is provided, which stores computer program instructions, and the computer program instructions are executed by a processor to implement the method described above.

[0014] The application has the following beneficial effects: The method solves the semantic understanding problem of natural language to SQL conversion, can accurately identify user query intention through the establishment of bidirectional hash index and joint retrieval mechanism, automatically completes table association and condition mapping, improves the conversion accuracy of query request, and reduces the requirement for user professional knowledge.

[0015] The method realizes intelligent processing and security control of query request, ensures that the generated SQL conforms to the user permission range through permission configuration and abstract syntax tree pruning, and enhances the security and reliability of the query result through compliance verification and entity linking, avoiding the risk of unauthorized access.

[0016] The technology adopts semantic expansion and structured processing mechanism, can automatically supplement necessary table connection conditions, identify the association key between multiple tables, form a complete query structure, greatly improves the adaptability and intelligent level of the system, and enables non-technical personnel to conveniently perform complex data query operations. BRIEF DESCRIPTION OF DRAWINGS

[0017] Figure 1 The flowchart of the natural language SQL generation method based on bidirectional mapping and semantic analysis of the embodiment of the present application is shown in the figure. Figure 2 The flowchart of the table association graph construction and query structure integrity processing is shown in the figure. DETAILED DESCRIPTION

[0018] To make the purpose, technical scheme and advantages of the embodiments of the present application clearer, the technical scheme in the embodiments of the present application will be described clearly and completely below in combination with the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative labor fall within the scope of protection of the present application.

[0019] The technical scheme of the present application will be described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described in detail in some embodiments.

[0020] Figure 1 The flowchart of the natural language SQL generation method based on bidirectional mapping and semantic analysis of the embodiment of the present application is shown in the figure. Figure 1 As shown in the figure, the method comprises: Extracting the query elements in the natural language query request input by the user role, and encapsulating them into structured data; The structured data is mapped by using a bidirectional hash index technology, a mapping relationship between a query field and a standard business attribute is established in a forward hash table, and a mapping relationship between the standard business attribute and a physical data table is constructed in a reverse hash table; by bidirectional joint retrieval, based on query field information in the structured data, a target data table and its associated table are located, and an associated key between multiple tables is automatically identified, the associated key is used to perform semantic expansion on a query condition in the structured data, necessary table connection conditions are automatically supplemented, and a complete query structure is formed; The complete query structure is subjected to compliance verification, entity linking and relationship reasoning are performed, and the query condition is replaced to obtain a standardized query expression; The standardized query expression is subjected to syntax analysis to generate an abstract syntax tree containing a query semantic structure, permission configuration information of a user role is read, the abstract syntax tree is pruned based on the permission configuration information, and a data permission filtering condition is appended; the abstract syntax tree subjected to the permission processing is converted into an SQL statement conforming to a syntax specification of a target database.

[0021] In an optional implementation, the mapping of the query elements of the structured data by using the bidirectional hash index technology includes: Feature codes are obtained by extracting query field information from the structured data and performing numerical conversion, a forward mapping table is constructed based on the query field information, and an initial detection position is obtained by performing a remainder operation on the feature codes and the capacity value of the forward mapping table; when the initial detection position is occupied, a linear detection sequence is generated based on the feature codes, and the linear detection sequence is detected position by position until an idle storage position is found; The query field information is converted into a query field feature vector, an inner product value is obtained by calculating the dot product of the query field feature vector and a preset standard business attribute feature vector, the module length values of the two feature vectors are calculated, and the similarity score is obtained by dividing the inner product value by the product of the two module length values; when the similarity score is greater than a preset similarity threshold, the mapping relationship between the query field information and the standard business attribute is established in the idle storage position of the forward mapping table; The physical data table information in the structured data is extracted, the standard business attribute is used as a hash key value, and a reverse hash mapping table is constructed by using a linked list storage structure; for each standard business attribute, a corresponding relationship between the standard business attribute and a physical data table is analyzed based on a business rule, and the corresponding relationship is stored as a linked list node; when a hash conflict occurs, a new linked list node is added to the end of the corresponding linked list, and the mapping relationship between the standard business attribute and the physical data table is established through the reverse hash mapping table.

[0022] After the user inputs the query request such as "Query the products and their managers whose sales in the first quarter of 2024 exceed 10 million", the input text is processed for word segmentation, and the time expression "the first quarter of 2024", the condition expression "sales exceeding 10 million", and the entity nouns "product" and "manager" are identified. The segmented text is analyzed by a deep semantic parsing model, which uses a pre-trained language model architecture and has been trained using a large amount of general corpus and enterprise-specific query corpus, and can accurately identify business terminology and query intent. The semantic parsing engine converts the parsing result into JSON format structured data containing query fields, query conditions, sorting rules and limit conditions. For the above example, the generated structured data is: {"fields":["product_name","manager_name"],"conditions":[{"field":"sale_amount","operator":">","value":10000000},{"field":"sale_date","operator":"between","value":["2024-01-01","2024-03-31"]}],"order_by":[],"limit":null}.

[0023] The query field information such as "sales", "product name", "manager" and other business terms is extracted from the structured data JSON object. Each query field information is converted to a numerical value, and a string hash algorithm is used to calculate a feature code. Specifically, the ASCII code value of each character in the query field is taken, and a weight factor is assigned to the character position. The product of the character code value and its weight is accumulated, and finally a 32-bit integer feature code is generated. For example, for the query field "sales", the feature code is calculated as 2749385621. After the feature code is generated, according to the capacity value M of the forward mapping table (usually set to a prime number greater than twice the number of query fields, such as 509), the feature code is taken modulo operation to get the initial probe position. For example, if the feature code is 2749385621 and M is 509, the initial probe position is 2749385621 % 509 = 375.

[0024] The forward mapping table uses open addressing method to handle hash collision, and the table structure contains key-value pair array and occupation flag bit array. When the initial probe position 375 calculated is occupied by other query fields, a linear probe sequence is generated based on the feature code. The linear probe uses a quadratic probing strategy, and the probe position sequence is (H(key) + i 2 ) % M, where i increases from 1. For "sales", if position 375 is occupied, then positions (375+12 )509 = 376, (375 + 2 2 )509 = 379, (375 + 3 2 )509 = 384, until a free storage location is found. After a free location is found, the "SALE AMOUNT" and its feature code are stored in the location, and the corresponding occupancy flag is set to 1.

[0025] Convert the query field information into a query field feature vector, using word embedding technology to map the field text to a high-dimensional semantic space. A 300-dimensional word vector model is pre-trained, which can represent business terms as dense vectors. For "SALE AMOUNT", its word vector [0.123, 0.456,..., 0.789] is extracted. At the same time, a standard business attribute library is maintained, containing standardized business attributes such as "SALE AMOUNT", "PRODUCT_NAME", "MANAGER_NAME", etc., and each attribute also has a pre-computed feature vector. Calculate the cosine similarity between the query field feature vector and each standard business attribute feature vector. The specific method is to calculate the dot product of the two vectors, and then divide by the product of the two vector lengths. For example, the dot product of the "SALE AMOUNT" vector and the "SALE AMOUNT" vector is 42.67, the "SALE AMOUNT" vector length is 8.54, and the "SALE AMOUNT" vector length is 7.32, the similarity score is 42.67 / (8.54*7.32)=0.683.

[0026] Set the preset similarity threshold to 0.6. When the calculated similarity score is greater than the threshold, it is considered that the query field matches the standard business attribute. The similarity of "SALE AMOUNT" and "SALE AMOUNT" is 0.683, which is greater than the threshold 0.6, and the mapping relationship of "SALE AMOUNT" to "SALE AMOUNT" is established in the free storage location (such as location 384) of the positive mapping table. The mapping relationship is stored as a key-value pair of <"SALE AMOUNT", "SALE AMOUNT">. For the case where a query field matches multiple standard business attributes, the attribute with the highest similarity is selected as the mapping target. When the similarity score is below the threshold, the query field is marked as "unrecognized" and recorded in the exception handling queue.

[0027] The reverse hash mapping table adopts a linked list storage structure, and is used to establish a mapping relationship between the standard business attribute and the physical data table. First, the physical data table information in the structured data is extracted, including the table name, field name and data type thereof. The standard business attribute such as "SALE_AMOUNT" is taken as a hash key value, and a hash function is used to calculate the index position thereof in the reverse mapping table. The hash function of the reverse mapping table adopts the FNV-1a algorithm, and a bit operation is performed on the input string to obtain a hash value. For example, for "SALE_AMOUNT", the hash value calculated is 3721498536, and if the size of the reverse mapping table is 1024, the index position is 3721498536 % 1024 = 232.

[0028] The corresponding relationship between the standard business attribute and the physical data table is analyzed based on the business rule, and the business rule includes table field annotation matching, historical query statistics, table correlation degree analysis and the like. For example, it is found through analysis that "SALE_AMOUNT" corresponds to the fields of multiple tables in the physical library: the total_amount field of the order_summary table, the amount field of the order_detail table and the sales_amount field of the sales_report table. A linked list node is created for each corresponding relationship, and the node structure includes the table name, field name, field type, matching degree and the like. For example, <"order_summary", "total_amount", "DECIMAL(18, 2)", 0.95>, <"order_detail", "amount", "DECIMAL(18, 2)", 0.92> and <"sales_report", "sales_amount", "DECIMAL(18, 2)", 0.88>.

[0029] When a hash conflict occurs, that is, different standard business attributes are mapped to the same index position, the linked list method is used for processing. For example, if "SALE_AMOUNT" and "SALE_QUANTITY" are both mapped to the index position 232, the linked lists of the two attributes are connected through a pointer. In the specific implementation, each slot of the reverse mapping table stores a pointer to the head node of the linked list, and when a conflict occurs, the head node of the linked list of the new attribute is inserted into the end of the linked list at the conflict position. All physical table field information corresponding to a specific standard business attribute is found through the traversal of the linked list.

[0030] When the database structure changes or business rules adjust, the mapping relationship is updated incrementally. The forward mapping table is updated using a mark-clear mechanism, marking obsolete mapping relationships and cleaning them up at the appropriate time. The reverse mapping table is updated through linked list operations, which can easily add, modify, or delete nodes. Full synchronization is performed periodically (e.g., every morning) to ensure that the mapping relationship is consistent with the latest database structure and business rules.

[0031] In practical application scenarios, when a user queries "products with sales exceeding 10 million in the first quarter of 2024 and their responsible persons," the query fields "sales," "product," and "responsible person" are extracted from the structured data. Through the forward mapping table, these query fields are mapped to the standard business attributes "SALE_AMOUNT," "PRODUCT_NAME," and "MANAGER_NAME," respectively. Then, through the reverse mapping table, it is determined that "SALE_AMOUNT" corresponds to the amount field of the order_detail table, "PRODUCT_NAME" corresponds to the product_name field of the product table, and "MANAGER_NAME" corresponds to the manager_name field of the sales_manager table. Thus, it is determined that the query involves three physical tables: order_detail, product, and sales_manager, providing necessary table and field information for subsequent SQL generation.

[0032] In an enterprise data environment containing tens of thousands of standard business attributes and hundreds of physical tables, the traditional sequential matching method requires several seconds to complete mapping, while the bidirectional hash index technology shortens the time to milliseconds. At the same time, this technology effectively solves the problem of complex and variable correspondence between business terms and physical table fields, improving the accuracy and adaptability of natural language queries. The memory space occupied by the bidirectional hash index structure is also relatively limited. For a system with 5000 standard business attributes and 300 physical tables, the forward mapping table occupies about 10MB of memory, and the reverse mapping table occupies about 15MB of memory, which is suitable for residing in memory to provide high-speed access.

[0033] In an alternative embodiment, through bidirectional joint retrieval, based on the query field information in the structured data, the target data table and its associated tables are located, and the association keys between multiple tables are automatically identified. Using the association keys, the query conditions in the structured data are semantically expanded, and necessary table join conditions are automatically supplemented to form a complete query structure including: Based on the result of the query field information in the forward mapping, retrieval is performed in the reverse mapping to obtain a target physical table, the associated table of the target physical table is obtained by analyzing the data relationship of the target physical table, the target physical table and the associated table are constructed into a table association graph, and the vertex in the table association graph represents a physical table and the edge represents an inter-table association relationship. The table structure definition of the target physical table is scanned to obtain a primary-foreign key constraint, the primary-foreign key constraint is taken as an explicit association relationship, word frequency features are extracted from the field naming in the target physical table and the associated table, a field pair with a similarity of the word frequency features greater than a preset potential association threshold is taken as a potential association field, and an association key is obtained by integrating the explicit association relationship and the potential association field. The distribution position of the association key in the table association graph is analyzed to determine a shortest connection path between tables, the fields of the tables in the shortest connection path are subjected to association analysis, fields with the same business meaning are identified and the table identifiers thereof are extracted, a corresponding table connection statement is generated, the table connection statement is sorted according to the order of the shortest connection path, a table connection condition is supplemented based on the sorted table connection statement, and the query condition in the structured data and the supplemented table connection condition are integrated to form a complete query structure.

[0034] As shown in Figure 2 The method includes: According to the extracted query fields such as "sales amount", "product name" and "person in charge", the corresponding standard business attributes "SALE_AMOUNT", "PRODUCT_NAME" and "MANAGER_NAME" are obtained through forward mapping. Then, these standard business attributes are used for retrieval in the reverse mapping table to determine the corresponding physical table and field of each attribute. For example, "SALE_AMOUNT" is mapped to the amount field of the order_detail table, "PRODUCT_NAME" is mapped to the product_name field of the product table, and "MANAGER_NAME" is mapped to the manager_name field of the sales_manager table. Through this mapping relationship, it is identified that the target physical tables involved in the query include order_detail, product and sales_manager.

[0035] The association information between the target physical tables is obtained from the database metadata warehouse, and the data relationship between the tables is analyzed. The relationship information includes table structure definition, primary-foreign key constraint, field name similarity, etc. The table relationship is represented in a graph structure, and a table association graph is created, in which the vertices represent physical tables and the edges represent the association relationship between the tables. For the above example, an association graph containing three vertices of order_detail, product, and sales_manager is constructed. The initialization stage of the association graph only contains the determined target physical tables, and the necessary associated tables will be expanded through analysis in the later stage.

[0036] The table structure definition of the target physical table is scanned, and the primary-foreign key constraint information is extracted, which directly reflects the explicit association relationship between the tables. For the product table and the order_detail table, it is found that there is a foreign key constraint product_id in the order_detail table, which points to the primary key product_id of the product table, so an edge from order_detail to product is added in the association graph, and the association key is marked as product_id. For the table relationship that is not explicitly defined as a primary-foreign key constraint, the table field naming pattern is analyzed, and the word frequency feature is extracted. A field name tokenizer is implemented to decompose field names such as "manager_id" into "manager" and "id", and the frequency of the same morphemes appearing between different tables is calculated. The field name is converted into a word frequency vector, and the similarity between different table fields is calculated. When the similarity of the word frequency features of two fields exceeds the preset potential association threshold (such as 0.75), they are identified as a pair of potential associated fields.

[0037] For the order_detail table and the sales_manager table, it is found that the naming patterns of order_detail.manager_id and sales_manager.manager_id are highly similar, and the word frequency feature similarity is 1.0, which exceeds the preset threshold of 0.75, so this pair of fields is identified as a potential associated field. The explicit association relationship (primary-foreign key constraint) and the potential associated field are integrated to generate a complete set of association keys. For the three tables in the example, the association key set includes: {(order_detail.product_id, product.product_id), (order_detail.manager_id, sales_manager.manager_id)}. These association keys constitute the edges in the table association graph, and each edge carries the information of the associated field pair.

[0038] The distribution of join keys in the table join graph is analyzed to determine the shortest connection path between tables. A breadth-first search-based path finding algorithm is used to minimize the number of connected tables and avoid large table joins. The algorithm starts from the starting table and expands to adjacent tables layer by layer until all target tables are covered. For each path, the path weight is calculated considering factors such as table size, index status, and join field cardinality. Paths with lower weights are prioritized to improve query efficiency. For the example query, the determined shortest connection path is: product → order_detail → sales_manager, which means that the order_detail table will serve as the intermediate table connecting the product and sales_manager tables.

[0039] After determining the shortest connection path, the fields in the tables along the path are analyzed for relevance, identifying fields with the same business meaning. The field names, data types, annotations, and historical query patterns are examined to determine the business semantic similarity of the fields. For product.product_id and order_detail.product_id, it is determined that they have the same business meaning, representing product identifiers. Similarly, order_detail.manager_id and sales_manager.manager_id are identified as the same semantic fields representing sales manager identifiers. The table identifiers where these fields reside are extracted, and table connection statements are generated for each pair of join fields. For the connection between product and order_detail, the generated statement is "product INNER JOIN order_detail ON product.product_id = order_detail.product_id"; for the connection between order_detail and sales_manager, the generated statement is "order_detail INNER JOIN sales_manager ON order_detail.manager_id = sales_manager.manager_id".

[0040] The table join statements are ordered according to the shortest connection path, ensuring the correctness of the join order. In the example, the join order is: first join product and order_detail, then join order_detail and sales_manager. Based on the ordered table join statements, the table join conditions are supplemented, which involves converting the identified inter-table association relationships into standard SQL join condition expressions. Specifically, for each pair of adjacent tables, an equality join condition in the form of "Table1.AssociationField = Table2.AssociationField" is generated, such as "product.product_id = order_detail.product_id" and "order_detail.manager_id = sales_manager.manager_id". At the same time, appropriate join types (INNER JOIN, LEFT JOIN, etc.) are selected based on field characteristics and table relationships, and each join condition is assigned the correct join order, ensuring that the logical flow from the main table to the slave table conforms to business semantics and database optimization principles. These generated table join conditions are added to the original structured data, together with user-specified query conditions (such as filter conditions, sorting rules), to form a complete query structure definition.The JSON representation of the complete query structure is: {"tables":["product","order_detail","sales_manager"],"fields":["product.product_name","sales_manager.manager_name"],"conditions":[{"field":"order_detail.amount","operator":">","value":10000000},{"field":"order_detail.order_date","operator":"between","value":["2024-01-01","2024-03-31"]}],"joins":[{"type":"INNER","table1":"product","table2":"order_detail","condition":"product.product_id = order_detail.product_id"},{"type":"INNER","table2":"sales_manager","condition":"order_detail.manager_id = sales_manager.manager_id"}],"group_by":[],"order_by":[],"limit":null}..

[0041] Suppose the user query is "Find the products purchased by customers and their suppliers", involving five tables: customer, order_header, order_detail, product, and supplier. Through the analysis of the association graph, it is found that these tables form a path: customer → order_header → order_detail → product → supplier. The associations between customer.customer_id and order_header.customer_id, order_header.order_id and order_detail.order_id, order_detail.product_id and product.product_id, and product.supplier_id and supplier.supplier_id are automatically identified. The complete join path is generated to ensure that the query can accurately associate all relevant tables.

[0042] For cases where multiple connection paths exist, a smart path selection algorithm is used, which takes into account factors such as table size, selectivity of connection fields, index status, and historical query performance to calculate a comprehensive score for each path. For example, if there are two paths connecting product and sales_manager: path 1 through the order_detail table and path 2 through the product_manager table, the data volume and index status of the two intermediate tables are analyzed. If the order_detail table has a data volume of 10 million rows and the connection field is not indexed, while the product_manager table has a data volume of only 10,000 rows and the connection field is indexed, the latter is chosen as the preferred path to improve query efficiency.

[0043] For one-to-many relationships, such as one product corresponding to multiple orders, the relationship is identified and the appropriate connection type is used when generating the connection condition. For many-to-many relationships, such as the association between products and tags through the intermediate table product_tag, the intermediate table can be automatically identified and the correct two-segment connection condition can be generated. Even self-connection scenarios can be handled, such as the manager_id in the employee table referencing the employee_id in the same table, forming a management relationship hierarchy. The self-connection problem is solved through the table alias mechanism, and different aliases are assigned to different roles of the same table.

[0044] Field type compatibility is considered during the connection condition generation process. When the associated field types are not completely consistent, appropriate type conversion functions are added. For example, if the product_id in the product table is of VARCHAR type and the product_id in the order_detail table is of INTEGER type, the connection condition "product.product_id = CAST(order_detail.product_id AS VARCHAR)" is generated to ensure the correct execution of the connection operation. Through this series of fine table association analysis and connection condition generation mechanism, the multi-table relationship can be accurately identified from the user's natural language query, and an efficient SQL connection structure can be automatically constructed, greatly reducing the user's difficulty in understanding and writing complex table connections.

[0045] In an optional implementation, the complete query structure is subjected to compliance verification, entity linking and relationship reasoning are performed simultaneously, and the query condition is replaced to obtain a standardized query expression, which includes: checking the legality of table names and field names in the complete query structure to generate a verification result; extracting entity mention information from the verification result, calculating string edit distance and contextual semantic similarity based on the entity mention information, and selecting target entities based on the string edit distance and contextual semantic similarity; retrieve the shortest path between the target entities, extract the semantic features of the shortest path; input the semantic features into the predefined Horn clause rule set for forward reasoning to obtain the relationship derivation chain between the target entities; calculate the confidence of each path in the relationship derivation chain, and determine the path with confidence greater than the relationship confidence threshold as the effective semantic association; extract the query condition features in the complete query structure, map and match the query condition features with the standard attribute constraints corresponding to the effective semantic association, select the standard attribute constraint with the highest matching degree to replace the query condition feature, combine with other query condition features in the complete query structure to form a standardized query expression.

[0046] Compare the table name and field name in the complete query structure with the database metadata to verify whether they exist in the target database. For table name, query the information_schema.tables view of the database; for field name, query the information_schema.columns view. For example, the complete query structure generated by the user query "query the products with sales exceeding 10 million in the first quarter of 2024 and their responsible persons" contains three tables: product, order_detail and sales_manager, which will be verified for existence in the target database, and the fields product.product_name, sales_manager.manager_name, order_detail.amount and order_detail.order_date will be verified for existence in the respective tables. For non-existent tables or fields, error information will be generated; for existing but slightly different named tables or fields, potential matching items will be recorded. The results generated by the verification process include the verification status of all tables and fields, as well as potential matching suggestions.

[0047] The entity mention information is extracted from the check results, including the table name, field name and their semantic meanings involved in the query. The string edit distance between the user-mentioned entity and the actual entity in the database is calculated, using the Levenshtein distance algorithm, which calculates the minimum number of editing operations required to convert one string to another. For example, the user mentions "produt" and there is a "product" table in the database, the edit distance is 1, indicating that one character 'c' needs to be added; the user mentions "sales_amt" and there is a "sales_amount" field in the database, the edit distance is 5, indicating that "amt" needs to be replaced with "amount". At the same time, the contextual semantic similarity is calculated, using a pre-trained word embedding model to convert entity names into vector representations, and calculating the cosine similarity between vectors. For "sales_amt" and "sales_amount", although the edit distance is large, they are highly similar in semantics, and the contextual semantic similarity reaches 0.92. Considering the edit distance and semantic similarity, when the edit distance is less than a preset threshold (such as 3) or the semantic similarity is greater than a preset threshold (such as 0.85), the entity in the database is identified as the target entity.

[0048] A graph-based search algorithm is used to construct the entity relationship graph in the database, where the nodes represent tables or fields, and the edges represent the foreign key relationship between tables or the semantic association between fields. For the three tables of product, order_detail and sales_manager, the shortest path between them is found in the entity relationship graph: product is associated with order_detail through product_id, and order_detail is associated with sales_manager through manager_id. The semantic features of this shortest path are extracted, including the names, types, whether they are primary keys or foreign keys, association cardinality (one-to-one, one-to-many, etc.) and the meaning of association in business. For example, the product_id association represents the subordinate relationship between product and order, and the manager_id association represents the responsible relationship between order and sales manager. These semantic features are encoded into feature vectors as input for relationship reasoning.

[0049] Horn clauses are expressions in formal logic of the form "if A and B and C, then D" and are suitable for expressing reasoning rules between entities. A rule base for a business domain is maintained, containing a number of business rules in the form of Horn clauses. For example, the rules "if A is product and B is order and B.product_id = A.id, then B is A's sale record", "if B is order and C is sales manager and B.manager_id = C.id, then C is responsible for B", and "if C is responsible for B and B is A's sale record, then C is indirectly responsible for A's sale". Substituting target entities and their associated relationships into these rules, a chain of relationships between entities is derived through forward chaining. For the above example, the chain of relationships "sales_manager is responsible for the sale of product in order_detail" is derived.

[0050] In calculating the confidence of each path in the chain of relationship derivation, a comprehensive scoring method of rule weight and entity matching degree is used. Each rule has a pre-set weight, reflecting its reliability in the business; each entity matching also has a corresponding similarity score. The rule weight and entity matching degree are multiplied to obtain the path confidence. For example, for the derivation path "sales_manager is responsible for the sale of product in order_detail", if the rule weight involved is 0.9 and the entity matching degree is 0.95, then the path confidence is 0.9 x 0.95 = 0.855. A relationship confidence threshold is set to 0.8, and when the path confidence is greater than this threshold, the path is determined to be an effective semantic association. These effective semantic associations constitute a logical relationship network between entities, which is used for subsequent query condition processing.

[0051] The query condition features in the complete query structure are extracted, including field names, operators, values, and logical relationships between conditions. For the conditions "order_detail.amount>10000000" and "order_detail.order_date BETWEEN '2024-01-01' AND '2024-03-31'" in the example query, the field names "amount" and "order_date" are extracted, the operators ">" and "BETWEEN" are extracted, the value "10000000" is extracted, and the value "['2024-01-01', '2024-03-31']" is extracted. These query condition features are associated with valid semantics and mapped to corresponding standard attribute constraints. Standard attribute constraints are stored in the system's knowledge base and are optimized query condition templates, such as the standard constraint for "sales amount" being "amount>threshold value" and the standard constraint for "time range" being "order_date BETWEEN start date AND end date". The matching degree of the query condition features and the standard attribute constraints is calculated, and the standard constraint with the highest matching degree is selected for replacement.

[0052] The query condition replacement process corrects the ambiguity and non-standardization of the condition expression. It is found that "10000000" in the user condition "order_detail.amount>10000000" is a specific numerical value, but in actual business, the sales threshold value is dynamically adjusted according to product categories, sales regions, and other factors. The query business rule base is found to determine the criteria for high-value orders as "sales amount exceeding 150% of the average sales amount of the product category". The fixed threshold value "10000000" is replaced by the dynamic calculation expression "(SELECT AVG(amount)*1.5 FROM order_detail WHERE product_id IN(SELECT product_id FROM product WHERE category_id = p.category_id))", where p represents the product table in the outer query. This replacement makes the query more consistent with the business logic and can adapt to the sales characteristics of different product categories.

[0053] For date range conditions, check if "2024-01-01" to "2024-03-31" matches the business definition of "first quarter". Query the time standard library to find that the standard definition of first quarter is January 1st to March 31st of each year, confirming that the user-input date range is consistent with the standard definition, so the original condition is kept unchanged. But the condition expression will be converted into a form optimized for the database, such as converting "BETWEEN '2024-01-01' AND '2024-03-31'" to "> = DATE('2024-01-01') AND < DATE('2024-04-01')", using left-closed and right-open intervals to improve query efficiency.

[0054] When it is found that the query contains both "sales > 0" and "sales > 10000000" conditions, the redundant "sales > 0" condition will be automatically deleted; when it is found that the conditions "product status = 'active'" and "product on-shelf date > current date" contradict each other, a warning will be issued or one of them will be retained according to business priority. It can also identify and optimize conditions related to null values, such as replacing "field <> null" with "field IS NOT NULL" to ensure correct execution of the condition in SQL.

[0055] The processed query conditions are combined with other elements (tables, fields, joins, groupings, orderings, etc.) in the full query structure to form a standardized query expression. The standardized expression is in a uniform JSON format and includes normalized table names, field names, condition expressions, join relationships, grouping fields, ordering rules, and limit conditions. For the example query, the standardized query expression is: {"tables":["product p","order_detail od","sales_manager sm"],"fields":["p.product_name","sm.manager_name"],"conditions":[{"field":"od.amount","operator":">","value":"(SELECT AVG(amount) * 1.5 FROM order_detail WHERE product_id IN (SELECT product_id FROM product WHERE category_id= p.category_id))"},{"field":"od.order_date","operator":">=","value":"DATE('2024-01-01')"},{"field":"od.order_date","operator":"<","value":"DATE('2024-04-01')"}],"joins":[{"type":"INNER","condition":"p.product_id = od.product_id"},{"type":"INNER","condition":"od.manager_id = sm.manager_id"}],"group_by":[],"order_by":[],"limit":null}。

[0056] The standardized query expression has a uniform structure and semantics, facilitating subsequent SQL generation and optimization processing. Each element in the expression is normalized to ensure compatibility with the target database's field names, data types, and syntax rules. The condition expressions have been replaced with optimized forms to avoid potential performance issues and semantic ambiguities. The table join conditions have been sorted according to the optimal path to ensure the efficiency of the query execution plan. Through a series of compliance checks, entity linking, relationship reasoning, and condition replacement processes, the user's natural language query is converted into an accurate, efficient, and business rule-compliant standardized query expression, laying a solid foundation for subsequent SQL generation.

[0057] In an alternative embodiment, the standardized query expression is parsed in syntax to generate an abstract syntax tree containing query semantic structure, the permission configuration information of the user role is read, the abstract syntax tree is pruned based on the permission configuration information, and a data permission filtering condition is appended, comprising: An Antlr parser is constructed based on lexical rules for identifying identifiers, keywords and operators and syntax rules for defining the syntax structure of the query statement; the standardized query expression is parsed in syntax using the Antlr parser to generate a node set containing semantic types, attribute values and hierarchical relationship information, and the node set is connected to obtain an abstract syntax tree containing query semantic structure; The permission configuration information of the user role is read to obtain the permission inheritance relationship of the user role and a set of row-level data filtering conditions; Based on the permission inheritance relationship, the node set of the abstract syntax tree is traversed, the access permission of each node is checked, nodes without access permission are deleted from the abstract syntax tree to obtain a pruned syntax tree; each filtering condition in the set of row-level data filtering conditions is parsed into a predicate expression, a condition node is constructed based on the predicate expression, and the condition node is organized into a filtering condition sub-tree; the filtering condition sub-tree is connected with the query condition in the pruned syntax tree through logical AND operation at the where clause position of the pruned syntax tree.

[0058] A special parser is constructed based on the lexical rules and syntax rules of the SQL language. The lexical rules are used to identify basic elements in the SQL, such as identifiers, keywords and operators. The lexical rules include keywords such as SELECT, FROM, WHERE, JOIN, etc., various operators such as comparison operators (>, <, =,!= ), logical operators (AND, OR, NOT), arithmetic operators (+, -, *, / ), and identifier rules allowing table names and field names to use combinations of letters, numbers and underscores, and supporting qualified names separated by dots in the "table.column" format. The syntax rules define the structure of the query statement, including the composition rules of the SELECT clause, FROM clause, WHERE clause, GROUP BY clause, HAVING clause, ORDER BY clause, etc., and the combination relationship between them. It also supports complex query structures such as subqueries, joint queries, common table expressions (CTE) and other advanced SQL features.

[0059] The standardized query expression is parsed using the constructed Antlr parser. For the example query expression {"tables":["product p","order_detail od","sales_manager sm"],"fields":["p.product_name","sm.manager_name"],"conditions":[{"field":"od.amount","operator":">","value":"(SELECT AVG(amount) * 1.5 FROM order_detail WHEREproduct_id IN (SELECT product_id FROM product WHERE category_id = p.category_id))"},{"field":"od.order_date","operator":">=","value":"DATE('2024-01-01')"},{"field":"od.order_date","operator":"<","value":"DATE('2024-04-01')"}],"joins":[{"type":"INNER","condition":"p.product_id = od.product_id"},{"type":"INNER","condition":"od.manager_id = sm.manager_id"}],"group_by":[],"order_by":[],"limit":null}, it is first converted into a SQL string, and then input into the parser for processing. During parsing, the parser recursively analyzes the input string according to the grammar rules, identifies each syntax unit, and constructs the corresponding nodes. Each node contains semantic types (such as SELECT nodes, table reference nodes, field reference nodes, condition expression nodes, etc.), attribute values (such as table names, field names, operators, constant values, etc.), and position information (line number, column number).

[0060] The parsed nodes are connected according to the hierarchical relationship to form a complete abstract syntax tree (AST), in which the root node represents the entire query statement, the child nodes are organized according to the SQL syntax structure to form a hierarchical tree structure. For example, the SELECT clause forms a sub-tree, including multiple field reference nodes; the FROM clause forms another sub-tree, including table reference nodes and table join nodes; the WHERE clause forms a conditional expression sub-tree, including various condition nodes and logical operation nodes. Each node of the abstract syntax tree is assigned a unique identifier, the parent-child relationship and sibling relationship between nodes are established, and the depth and path information of the nodes are recorded, facilitating subsequent tree traversal and operation. The root node of the abstract syntax tree of the example query is QueryNode, which has SelectListNode (including two ColumnRefNode: p.product_name and sm.manager_name), FromNode (including TableNode: product and TableNode: order_detail and TableNode: sales_manager, and JoinNode connecting them), WhereNode (including multiple condition expression nodes connected by AND node) under it.

[0061] Read the permission configuration information of the user role, get the complete permission definition of the role to which the current user belongs from the permission management system, and the permission configuration information includes two parts: permission inheritance relationship and row-level data filtering condition set. The permission inheritance relationship defines the hierarchical structure between roles, and a role can inherit all the permissions of the superior role. For example, the sales manager role inherits the permissions of the salesperson role, and the sales director role inherits the permissions of the sales manager role. Recursively analyze the permission inheritance chain to summarize all the inherited permissions of the current user role. The row-level data filtering condition set defines the access restriction rules for different data tables, usually described in the form of expressions. For example, the salesperson role has the filtering condition "sales_manager.manager_id = ${current_user_id}", indicating that only the sales data under his / her responsibility can be accessed; the sales manager role has the filtering condition "sales_manager.department_id = ${current_user_dept_id}", indicating that only the sales data of his / her department can be accessed.

[0062] Based on the permission inheritance relationship, the node set of the abstract syntax tree is traversed and permission checked. The traversal uses a depth-first search algorithm, starting from the root node and recursively accessing each child node. For each node, check whether its corresponding data object (table, field, function, etc.) is within the permission range of the current user role. The permission check is based on fine-grained object-level permission control, supporting multi-dimensional control such as table-level permissions, column-level permissions, row-level permissions, and operation-level permissions. For example, check whether the user has permission to access the product table, the product_name field, the amount field of the order_detail table, etc. If it is found that the data object corresponding to a certain node is beyond the user's permission range, mark the node as invalid and delete the node and all its child nodes from the abstract syntax tree. This process is called syntax tree pruning. Pruning is achieved by modifying the parent-child relationship of the node, pointing the parent node directly to other valid child nodes, or marking the parent node as invalid when there are no valid child nodes. Through pruning, it is ensured that the final query only contains data objects that the user has access to.

[0063] Each filter condition in the row-level data filter condition set is parsed and converted into a standard predicate expression. A predicate expression is a special type of condition expression used to describe the conditions that a data row must satisfy. Multiple types of predicate expressions are supported, including equality judgment (=), inequality judgment (!=), range judgment (>, <, >=, <= ), set judgment (IN, NOTIN), pattern matching (LIKE), null value judgment (IS NULL, IS NOT NULL), etc. For condition expressions containing user context variables, variable substitution will be performed, replacing variables in the form of ${variable} with actual values. For example, replace ${current_user_id} in "sales_manager.manager_id = ${current_user_id}" with the ID value "U10086" of the currently logged-in user to get "sales_manager.manager_id = 'U10086'". For complex row-level filter conditions, multiple predicate expressions can be combined using AND, OR, NOT, etc. to form a composite condition.

[0064] Based on the parsed predicate expressions, condition nodes are constructed, each predicate expression corresponds to a condition node, and the type of the condition node is determined according to the predicate type, such as an equality condition node, a comparison condition node, an IN condition node, etc. Each condition node contains three parts: an operand, an operator, and a value, where the operand is usually a field reference, the operator is a comparison operator, and the value can be a constant, a variable, or a subquery. The constructed condition nodes are organized into a filter condition sub-tree according to the logical relationship, and multiple conditions are usually connected by an AND node, indicating that all conditions need to be met at the same time. For example, for the filter conditions "sales_manager.manager_id = 'U10086'" and "sales_manager.status = 'active'" of the sales manager role, a sub-tree containing two equality condition nodes is constructed, and the two nodes are connected by an AND node.

[0065] In the WHERE clause position of the pruned syntax tree, the filter condition sub-tree is connected to the original query condition through logical AND operation. If the original query already has a WHERE clause, an AND node is added under the original WHERE node to connect the original condition sub-tree and the new filter condition sub-tree; if the original query does not have a WHERE clause, a new WHERE node is created, and the filter condition sub-tree is used as its child node. This approach ensures that row-level data filtering conditions are applied regardless of whether the user query contains conditions, preventing unauthorized access to data. For the example query, assuming the current user is a sales manager role with permission to access sales data of the department "D5001", the condition "sales_manager.department_id = 'D5001'" will be appended to the WHERE clause. The final WHERE clause will contain the original three conditions (amount condition and two date conditions) and the newly added department filtering condition, connected by AND logic.

[0066] For syntax trees containing subqueries, the same permission check and filter condition appending process is recursively performed on each subquery. This ensures that all data access involved is subject to uniform permission control regardless of the complexity of the query structure. A view replacement mechanism is also implemented, for tables that the user does not have direct access to but can access through a view, the table reference is automatically replaced with the corresponding authorized view reference, ensuring data security while providing flexible access methods. For example, if the user has no direct access to the manager_salary field of the sales_manager table but has access to the v_sales_manager_public view, the sales_manager table in the query will be replaced with the v_sales_manager_public view.

[0067] For sensitive fields, different de-sensitization rules such as masking, truncation, encryption, etc. are applied according to the user's permission level. These de-sensitization operations are implemented by inserting function call nodes in the syntax tree, replacing the reference of the sensitive field with a de-sensitization function call. For example, "sm.phone_number" is replaced with "MASK(sm.phone_number, 3, 4)", which means only the first 3 digits and the last 4 digits of the phone number are displayed, and the middle is replaced with an asterisk. Through fine operation of the syntax tree, flexible and powerful data access control is realized, which not only ensures the correct execution of the query, but also ensures data security and privacy protection. The abstract syntax tree after permission control processing will serve as the basis for generating optimized SQL statements, ensuring that the generated SQL statements not only meet the user's query intent, but also strictly comply with the data access permission control rules.

[0068] In an optional implementation, converting the abstract syntax tree after permission processing into an SQL statement conforming to the syntax specification of the target database includes: Obtaining the dialect type of the target database, constructing the data type representation features, function naming features, paging syntax features and time and date processing features corresponding to the dialect type into a dialect feature vector, generating a corresponding dialect-specific syntax based on the dialect feature vector, extracting the dependency relationship of the standard syntax and the dialect-specific syntax to form a context constraint condition, and combining the dialect-specific syntax and the context constraint condition into a syntax rule mapping set; Traversing the nodes of the abstract syntax tree after permission processing to obtain the matching degree of each node with each rule in the syntax rule mapping set; selecting the rule with the highest matching degree from the syntax rule mapping set as the optimal syntax conversion rule; Converting the nodes of the abstract syntax tree after permission processing using the optimal syntax conversion rule, and ensuring that the syntax structure of the converted adjacent nodes meets the syntax specification requirements of the target database based on the context constraint condition, to generate an SQL statement conforming to the dialect specification of the target database.

[0069] The database connection information is read from the configuration center, and the database types such as MySQL, PostgreSQL, Oracle, SQLite, etc. are extracted, and a feature information base is maintained for each database dialect, including data type representation features, function naming features, paging syntax features, and time and date processing features. The data type representation feature describes the declaration method of the basic data type, such as integer in MySQL is INT, and in Oracle is NUMBER; the string type in MySQL is VARCHAR, and in PostgreSQL is TEXT. The function naming feature records the naming differences of common functions, such as string connection in MySQL uses the CONCAT function, and in Oracle uses the || operator; the current date in MySQL uses CURDATE(), and in SQLite uses DATE('now'). The paging syntax feature defines the implementation method of the paging query, such as MySQL uses the LIMIT offset, count syntax, Oracle uses ROWNUM or ROW_NUMBER() OVER() syntax, and SQLite uses the LIMIT count OFFSET offset syntax. The time and date processing features include differences in date formatting, date calculation, and time zone processing, such as date addition and subtraction in MySQL uses DATE_ADD and DATE_SUB functions, and in PostgreSQL uses + and - operators with the INTERVAL keyword.

[0070] The above features are constructed as dialect feature vectors, and the corresponding dialect-specific syntax is generated for the target database. The dialect feature vector is a multi-dimensional vector, each dimension corresponds to a type of syntax feature, and the vector value represents the implementation method of the feature in the target dialect. For example, for the MySQL dialect, the feature vector contains the paging syntax dimension value "LIMIT", the string connection dimension value "CONCAT", the date formatting dimension value "DATE_FORMAT", etc. Based on the dialect feature vector, template replacement and rule derivation are used to generate SQL syntax fragments compatible with the target database. For example, the standard paging expression is converted to the "LIMIT offset, count" form of MySQL or the "WHERE ROWNUM BETWEEN start AND end" form of Oracle. These dialect-specific syntaxes form a syntax conversion rule library for subsequent node conversion.

[0071] The dependencies between the standard syntax and the dialect-specific syntax are extracted to form context constraints, which describe contextual elements that must be considered in the syntax conversion to ensure that the converted SQL fragment is consistent with the context. These constraints include expression evaluation order, subquery position restrictions, function nesting rules, alias reference domains, and the like. For example, in MySQL, a derived table (a subquery in the FROM clause) must have an alias, while this constraint is more relaxed in SQLite; in Oracle, the ORDER BY clause cannot directly reference aliases in the SELECT list, while this is possible in PostgreSQL. The dialect-specific syntax and the context constraints are combined to form a complete set of syntax rule mappings. This set is a multi-level rule library that includes node type mappings, expression conversion rules, function mapping tables, and special syntax processing rules, among other aspects.

[0072] A depth-first traversal algorithm is used to visit each node of the abstract syntax tree that has been processed for permissions. For each node, its type, attributes, and child node information are extracted to form a node feature description. The node feature description includes information such as the node type (e.g., SELECT, FROM, WHERE, function call, etc.), operand types, return type, context environment, and the like. The node feature description is matched against the rules in the syntax rule mapping set, and a matching score is calculated. The matching score is calculated using a weighted similarity algorithm that considers factors such as node type matching, attribute compatibility, context compliance, and the like. For example, for a function call node SUBSTRING(str, start, length), the mapping rules for string truncation functions in different dialects are checked, such as MySQL's SUBSTRING, Oracle's SUBSTR, SQLite's SUBSTR, and the like, taking into account parameter order and meaning differences. The matching score ranges from 0 to 1, with 1 indicating a perfect match and 0 indicating a perfect mismatch. From the syntax rule mapping set, the rule with the highest matching score is selected as the optimal syntax conversion rule. When multiple rules have the same matching score, the rule with the highest priority, execution efficiency, and maintainability is selected.

[0073] The selected optimal syntax conversion rules are used to convert the nodes of the abstract syntax tree. The conversion process is a recursive operation, starting from the leaf nodes and converting layer by layer upwards. For each node, the corresponding conversion rule is applied to generate the SQL fragment of the target dialect. For example, for the MySQL dialect, the standard SUBSTRING function is converted to the MySQL SUBSTRING syntax; the standard date comparison expression is converted to the form using the MySQL date function. During the conversion process, the context constraints are strictly followed to ensure that the syntax structure of the converted adjacent nodes meets the syntax specification requirements of the target database. For example, it is ensured that the subquery position is legal, the aggregate function is used in accordance with the specification, the references of the ORDER BY and GROUP BY clauses are correct, etc. For complex expressions, the components of the expression and their dependency relationships are analyzed to ensure that the structure and evaluation order of the converted expression are unchanged. When a specific function has no direct corresponding implementation in the target dialect, an equivalent alternative is used, such as using multiple simple functions to simulate a complex function, or using a subquery to replace a syntax structure that is not supported.

[0074] Taking date processing as an example, different databases have great differences in support for date formatting. When converting the date formatting expression FORMAT_DATE(date, 'yyyy-MM-dd') in the standard query to MySQL, it is converted to DATE_FORMAT(date, '%Y-%m-%d'); when converted to Oracle, it becomes TO_CHAR(date, 'YYYY-MM-DD'); and when converted to SQLite, it uses strftime('%Y-%m-%d', date). When processing a paging query, the appropriate paging implementation is selected according to the target dialect. For the standard OFFSET-LIMIT paging, the "LIMIT offset,count" syntax is used when converting to MySQL; the "LIMIT count OFFSET offset" syntax is used when converting to PostgreSQL; and according to the Oracle version, ROWNUM or ROW_NUMBER() OVER() is used for implementation when converting to Oracle. The difference in the position of NULL values in sorting is also handled. In MySQL, NULL values are by default placed at the front, while in Oracle, NULL values are by default placed at the end, and appropriate NULLS FIRST or NULLS LAST modifiers are added to the ORDER BY clause.

[0075] The boolean type is actually TINYINT(1) in MySQL, BOOLEAN in PostgreSQL, and simulated using NUMBER(1) in Oracle. Automatically adjust the representation of boolean values according to the target dialect to ensure that query conditions are executed correctly. For regular expressions, MySQL uses the REGEXP operator, PostgreSQL uses the ~ operator, and Oracle uses the REGEXP_LIKE function. Choose the appropriate regular expression syntax according to dialect differences. Handle cross-dialect conversion of common operations such as string concatenation, string truncation, mathematical functions, etc. to ensure consistency in functional semantics. Finally, generate a complete SQL statement that conforms to the specifications of the target database dialect.

[0076] The embodiment of the application is based on a natural language generation SQL system based on bidirectional mapping and semantic analysis, which comprises: A first unit for extracting query elements in a natural language query request input by a user role and encapsulating them as structured data; A second unit for using bidirectional hash index technology to map query elements of the structured data, establishing a mapping relationship between query fields and standard business attributes in a forward hash table, and constructing a mapping relationship between standard business attributes and physical data tables in a reverse hash table; through bidirectional joint retrieval, based on query field information in the structured data, locating target data tables and their associated tables, and automatically identifying associated keys between multiple tables, using the associated keys to perform semantic expansion on query conditions in the structured data, automatically supplementing necessary table connection conditions to form a complete query structure; A third unit for performing compliance verification on the complete query structure, simultaneously performing entity linking and relationship reasoning, and replacing query conditions to obtain a standardized query expression; A fourth unit for performing syntax analysis on the standardized query expression, generating an abstract syntax tree containing query semantic structure, reading permission configuration information of a user role, pruning the abstract syntax tree based on the permission configuration information, and appending data permission filtering conditions; converting the abstract syntax tree that has undergone permission processing into an SQL statement that conforms to the syntax specifications of a target database.

[0077] In a third aspect of the embodiment of the application, an electronic device is provided, comprising: A processor; A memory for storing processor-executable instructions; The processor is configured to invoke instructions stored in the memory to execute the method described above.

[0078] In a fourth aspect, the present application provides a computer readable storage medium having stored thereon computer program instructions, which when executed by a processor, implement the method described above.

[0079] The present application can be a method, an apparatus, a system, and / or a computer program product. The computer program product can include a computer readable storage medium (or media) having computer readable program instructions thereon for performing various aspects of the present application.

[0080] Finally, it should be noted that: the above embodiments are only used to illustrate the technical solutions of the present application, and not to limit them; although the present application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand: it can still modify the technical solutions recorded in the foregoing embodiments, or make equivalent replacement for part or all of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the scope of the technical solutions of the embodiments of the present application.

Claims

1. A natural language to SQL method based on bidirectional mapping and semantic parsing, characterized in that, The method comprises the following steps: extracting query elements in a natural language query request input by a user role and encapsulating them as structured data; using a bidirectional hash index technology to map the structured data, establishing a mapping relationship between query fields and standard business attributes in a forward hash table, and constructing a mapping relationship between standard business attributes and physical data tables in a reverse hash table; through bidirectional joint retrieval, locating target data tables and their associated tables based on query field information in the structured data, automatically identifying the association keys between the tables, using the association keys to semantically extend the query conditions in the structured data, automatically supplementing necessary table connection conditions, and forming a complete query structure; performing compliance verification on the complete query structure, simultaneously performing entity linking and relationship reasoning, and replacing query conditions to obtain a standardized query expression; performing syntax analysis on the standardized query expression to generate an abstract syntax tree containing query semantic structure, reading permission configuration information of the user role, pruning the abstract syntax tree based on the permission configuration information, and appending data permission filtering conditions; converting the abstract syntax tree after permission processing into a SQL statement conforming to the syntax specification of a target database.

2. The method of claim 1, wherein, Using a bidirectional hash index technology to map the structured data, establishing a mapping relationship between query fields and standard business attributes in a forward hash table, and constructing a mapping relationship between standard business attributes and physical data tables in a reverse hash table comprises: extracting query field information from the structured data and performing numerical conversion to obtain a feature code, constructing a forward mapping table for the query field information, and performing a modulo operation on the feature code and the capacity value of the forward mapping table to obtain an initial probe position; when the initial probe position is occupied, generating a linear probe sequence based on the feature code and probing along the linear probe sequence position by position until an empty storage position is found; converting the query field information into a query field feature vector, calculating the dot product of the query field feature vector and a preset standard business attribute feature vector to obtain an inner product value, calculating the module length values of the two feature vectors, and dividing the inner product value by the product of the two module length values to obtain a similarity score; when the similarity score is greater than a preset similarity threshold, establishing a mapping relationship between the query field information and the standard business attribute in the empty storage position of the forward mapping table; extracting physical data table information in the structured data, using the standard business attributes as hash key values, and constructing a reverse hash mapping table using a linked list storage structure; for each standard business attribute, analyzing its corresponding relationship with a physical data table based on business rules, and storing the corresponding relationship as a linked list node; when a hash collision occurs, adding a new linked list node to the end of the corresponding linked list, and establishing a mapping relationship between the standard business attributes and the physical data tables through the reverse hash mapping table.

3. The method of claim 1, wherein, By bidirectional joint retrieval, based on the query field information in the structured data, the target data table and its associated tables are located, and the association keys between multiple tables are automatically identified. The association keys are used to semantically expand the query conditions in the structured data, automatically supplement necessary table connection conditions, and form a complete query structure including: Based on the results of the query field information in the forward mapping, the target physical table is retrieved in the reverse mapping, the associated tables of the target physical table are obtained by analyzing the data relationship of the target physical table, and the target physical table and the associated tables are constructed into a table association graph. The vertices in the table association graph represent physical tables, and the edges represent the association relationship between tables. Scan the table structure definition of the target physical table to obtain the primary-foreign key constraint, use the primary-foreign key constraint as an explicit association relationship, extract the word frequency features in the field naming of the target physical table and the associated tables, and use the word frequency features with a similarity greater than a preset potential association threshold as potential association fields. The explicit association relationship and the potential association fields are integrated to obtain the association keys. Analyze the distribution position of the association keys in the table association graph to determine the shortest connection path between tables; perform association analysis on the fields of the tables in the shortest connection path, identify fields with the same business meaning and extract the table identifiers, generate corresponding table connection statements, sort the table connection statements according to the order of the shortest connection path, supplement table connection conditions based on the sorted table connection statements, and integrate the query conditions in the structured data and the supplemented table connection conditions to form a complete query structure.

4. The method of claim 1, wherein, Perform compliance verification on the complete query structure, perform entity linking and relationship reasoning, and replace the query conditions to obtain a standardized query expression including: Check the legality of the table name and field name in the complete query structure to generate a verification result; extract entity mention information from the verification result, calculate the string edit distance and context semantic similarity based on the entity mention information, and select target entities based on the string edit distance and context semantic similarity; Retrieve the shortest path between the target entities, extract the semantic features of the shortest path, input the semantic features into a predefined Horn clause rule set for forward reasoning to obtain a relationship derivation chain between the target entities, and calculate the confidence of each path in the relationship derivation chain. Paths with a confidence greater than a relationship confidence threshold are determined as valid semantic associations. Extract the query condition features in the complete query structure, map the query condition features to the corresponding standard attribute constraints of the valid semantic associations, select the standard attribute constraint with the highest matching degree to replace the query condition features, combine with other query condition features in the complete query structure, and form a standardized query expression.

5. The method of claim 1, wherein, Perform syntax analysis on the standardized query expression to generate an abstract syntax tree containing query semantic structure, read the permission configuration information of the user role, prune the abstract syntax tree based on the permission configuration information, and append data permission filtering conditions including: An Antlr parser is constructed based on lexical rules for identifying identifiers, keywords and operators and syntax rules for defining syntax structure of a query statement; the Antlr parser is used to perform syntax analysis on a standardized query expression, to generate a node set containing semantic types, attribute values and hierarchical relationship information, and to connect the node set to obtain an abstract syntax tree containing query semantic structure; Permission configuration information of a user role is read to obtain a permission inheritance relationship of the user role and a set of row-level data filtering conditions; Based on the permission inheritance relationship, the node set of the abstract syntax tree is traversed, access permissions of each node are checked, nodes without access permissions are deleted from the abstract syntax tree, and a pruned syntax tree is obtained; each filtering condition in the set of row-level data filtering conditions is parsed into a predicate expression, a condition node is constructed based on the predicate expression, and the condition node is organized into a filtering condition sub-tree; the filtering condition sub-tree and query conditions in the pruned syntax tree are connected through logical AND operation at a where clause position of the pruned syntax tree.

6. The method of claim 1, wherein, The abstract syntax tree after permission processing is converted into an SQL statement conforming to a syntax specification of a target database, including: A dialect type of the target database is obtained, data type representation features, function naming features, paging syntax features and time and date processing features corresponding to the dialect type are constructed into a dialect feature vector, a dialect-specific syntax is generated based on the dialect feature vector, a dependency relationship of the standard syntax and the dialect-specific syntax is extracted, a context constraint condition is formed, and the dialect-specific syntax and the context constraint condition are combined into a syntax rule mapping set; Nodes of the abstract syntax tree after permission processing are traversed to obtain a matching degree of each node with each rule in the syntax rule mapping set; a rule with the highest matching degree is selected from the syntax rule mapping set as an optimal syntax conversion rule; The optimal syntax conversion rule is used to convert nodes of the abstract syntax tree after permission processing, and the context constraint condition is used to ensure that syntax structures of adjacent nodes after conversion meet syntax specification requirements of the target database, to generate an SQL statement conforming to dialect specifications of the target database.

7. A natural language to SQL system based on bidirectional mapping and semantic parsing for implementing the method of any of claims 1-6, characterized in that, It includes: A first unit configured to extract query elements in a natural language query request input by a user role and encapsulate the query elements into structured data; A second unit configured to use a bidirectional hash index technology to perform query element mapping on the structured data, to establish a mapping relationship between query fields and standard business attributes in a forward hash table and to construct a mapping relationship between the standard business attributes and physical data tables in a reverse hash table, to locate a target data table and associated tables based on query field information in the structured data by bidirectional joint retrieval, to automatically identify association keys between the tables, to perform semantic expansion on query conditions in the structured data by using the association keys, to automatically supplement necessary table connection conditions, and to form a complete query structure. The third unit is configured to perform compliance verification on the complete query structure, perform entity linking and relationship reasoning, and replace the query condition to obtain a standardized query expression. The fourth unit is configured to perform syntax analysis on the standardized query expression to generate an abstract syntax tree containing a query semantic structure, read permission configuration information of a user role, prune the abstract syntax tree based on the permission configuration information, and append a data permission filtering condition. The abstract syntax tree after the permission processing is converted into an SQL statement conforming to a target database syntax specification.

8. An electronic device, comprising: The computer program instructions are executed by the processor to implement the method in any one of claims 1 to 6. The computer program instructions are executed by the processor to implement the method in any one of claims 1 to 6. The computer program instructions are executed by the processor to implement the method in any one of claims 1 to 6. ​ 9. A computer-readable storage medium having stored thereon computer program instructions, wherein, ​

Images