Natural language generation sql method based on bidirectional mapping and semantic analysis

Abstract

The application provides a natural language generation SQL method based on bidirectional mapping and semantic analysis, relates to the technical field of database query, and comprises the following steps: extracting natural language query elements and encapsulating the natural language query elements as structured data, adopting bidirectional hash index technology to establish a mapping relationship from a query field to a business attribute and a physical data table, automatically identifying a multi-table association key, performing semantic expansion and compliance verification, generating an abstract syntax tree and processing according to user permissions, and finally converting into an SQL statement conforming to the syntax specification of a target database. The application improves the accuracy and efficiency of natural language conversion into SQL, and enhances the flexibility and security of the system.

Description

Technical Field

[0001] This invention relates to the field of database query technology, and in particular to a natural language SQL generation method based on bidirectional mapping and semantic parsing. Background Technology

[0002] With the rapid development of big data and artificial intelligence technologies, natural language processing (NLP) is increasingly being applied in database querying. Traditional database queries require users to master SQL, which presents a high barrier to entry for non-technical users. Natural language-to-SQL (NLP) technology aims to solve this problem, allowing users to express their query needs in everyday language and automatically converting them into standard SQL statements to execute database operations, thereby significantly improving the convenience and universality of data acquisition. Currently, NLP-to-SQL technologies mainly rely on rule-based template matching, machine learning, and deep learning. In practical applications, these technologies typically require semantic parsing of the natural language query to extract key information and then generating corresponding SQL statements based on the database structure.

[0003] Existing technologies suffer from low mapping efficiency when handling complex queries. Traditional one-way mapping methods struggle to effectively handle the ambiguity and polysemy between natural language query terms and database table structures, especially when dealing with multi-table joins. They fail to accurately identify and construct the relationships between tables, resulting in incomplete SQL statements or logical errors. Existing technologies lack effective semantic expansion mechanisms. When user queries are incomplete or contain implicit conditions, they cannot automatically supplement necessary query conditions and table join information, leading to inaccurate or failed query results that fail to meet the user's true intent, severely impacting query efficiency and user experience. Furthermore, existing technologies generally neglect data access control. In enterprise-level applications, different user roles have different data access permissions. Existing technologies lack flexible access control mechanisms during the natural language to SQL conversion process, failing to automatically adjust the query scope and filtering conditions based on user roles, posing potential data security risks. Summary of the Invention

[0004] This invention provides a method for generating SQL from natural language based on bidirectional mapping and semantic parsing, which can solve the problems in the prior art.

[0005] A first aspect of this invention provides a method for generating SQL from natural language based on bidirectional mapping and semantic parsing, comprising:

[0006] Extract query elements from the natural language query request input by the user role and encapsulate them into structured data;

[0007] A bidirectional hash index technique is used to map query elements to the structured data. A mapping relationship between query fields and standard business attributes is established in the forward hash table, and a mapping relationship between standard business attributes and physical data tables is constructed in the reverse hash table. Through bidirectional joint retrieval, the target data table and its associated tables are located based on the query field information in the structured data, 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.

[0008] The complete query structure is subjected to compliance verification, entity linking and relation reasoning are performed, and the query conditions are replaced to obtain a standardized query expression.

[0009] The standardized query expression is parsed to generate an abstract syntax tree containing the query semantic structure. The user role's permission configuration information is read, and the abstract syntax tree is pruned based on the permission configuration information. Data permission filtering conditions are added. The permission-processed abstract syntax tree is then converted into an SQL statement that conforms to the target database syntax specification.

[0010] The structured data is mapped to query elements using bidirectional hash indexing technology. This involves establishing a mapping relationship between query fields and standard business attributes in the forward hash table, and constructing a mapping relationship between standard business attributes and physical data tables in the reverse hash table.

[0011] The query field information is extracted from the structured data and numerically converted to obtain the feature code. A forward mapping table is constructed for the query field information. The initial detection position is obtained by performing a modulo operation between the feature code 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 code. The probe is performed position by position along the linear detection sequence until a free storage position is found.

[0012] The query field information is converted into a query field feature vector. The dot product of the query field feature vector and the preset standard business attribute feature vector is calculated to obtain the inner product value. The magnitude of the two feature vectors is calculated. The inner product value is divided by the product of the two magnitude values ​​to obtain the similarity score. When the similarity score is greater than the preset similarity threshold, the mapping relationship between the query field information and the standard business attribute is established in the free storage location of the positive mapping table.

[0013] Physical data table information is extracted from the structured data. The standard business attributes are used as hash keys, and a reverse hash mapping table is constructed using a linked storage structure. For each standard business attribute, its correspondence with the physical data table is analyzed based on business rules, and the correspondence is stored as a linked list node. When a hash collision occurs, a new linked list node is added to the end of the corresponding linked list. The mapping relationship between the standard business attributes and the physical data table is established through the reverse hash mapping table.

[0014] Through bidirectional joint retrieval, based on the query field information in the structured data, the target data table and its related 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:

[0015] Based on the results of the query field information in the forward mapping, a retrieval is performed in the reverse mapping to obtain the target physical table. By analyzing the data relationship of the target physical table, the associated table of the target physical table is obtained. The target physical table and the associated table are constructed into a table association graph, where the vertices in the table association graph represent physical tables and the edges represent the association relationships between tables.

[0016] Scan the table structure definition of the target physical table to obtain primary and foreign key constraints, use the primary and foreign key constraints as explicit associations, extract word frequency features from the field names in the target physical table and the associated table, use field pairs with a similarity of word frequency features greater than a preset potential association threshold as potential association fields, and integrate the explicit associations and the potential association fields to obtain the association key;

[0017] Analyze the distribution of the association keys in the table association graph to determine the shortest join path between tables; perform association analysis on the fields of the tables in the shortest join path to identify fields with the same business meaning and extract their table identifiers, generate corresponding table join statements, sort the table join statements according to the order of the shortest join path, supplement the table join conditions based on the sorted table join statements, and integrate the query conditions in the structured data with the supplemented table join conditions to form a complete query structure.

[0018] The complete query structure undergoes compliance verification, entity linking and relational reasoning, and query conditions are replaced to obtain standardized query expressions including:

[0019] Check the validity of table names and field names in the complete query structure and generate a verification result; extract entity mention information from the verification result, calculate string edit distance and contextual semantic similarity based on the entity mention information, and filter out target entities based on the string edit distance and contextual semantic similarity;

[0020] The shortest path between the target entities is retrieved, and the semantic features of the shortest path are extracted. The semantic features are input into a predefined set of Horn clause rules for forward reasoning to obtain the relation derivation chain between the target entities. The confidence of each path in the relation derivation chain is calculated, and the paths with confidence greater than the relation confidence threshold are determined as valid semantic associations.

[0021] Extract the query condition features from 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 features, and combine it with other query condition features in the complete query structure to form a standardized query expression.

[0022] The standardized query expression is parsed to generate an abstract syntax tree containing the query semantic structure. User role permission configuration information is read, and the abstract syntax tree is pruned based on this permission configuration information. Data permission filtering conditions are then added, including:

[0023] An Antlr parser is constructed based on lexical rules for identifying identifiers, keywords, and operators, and grammatical rules for defining the grammatical structure of query statements. The Antlr parser is used to perform grammatical parsing on standardized query expressions to generate a set of nodes containing semantic types, attribute values, and hierarchical relationship information. The set of nodes is then connected to obtain an abstract syntax tree containing the semantic structure of the query.

[0024] Read the permission configuration information of the user role to obtain the permission inheritance relationship and row-level data filtering condition set of the user role;

[0025] Based on the permission inheritance relationship, the node set of the abstract syntax tree is traversed, the access permission of each node is checked, and nodes without access permission are deleted from the abstract syntax tree to obtain the pruned syntax tree; each filtering condition in the row-level data filtering condition set is parsed into a predicate expression, and a condition node is constructed based on the predicate expression, and the condition nodes are organized into a filtering condition subtree; at the WHERE clause position of the pruned syntax tree, the filtering condition subtree is connected to the query condition in the pruned syntax tree through a logical AND operation.

[0026] Converting the permission-processed abstract syntax tree into SQL statements that conform to the target database's syntax specifications includes:

[0027] Obtain the dialect type of the target database, construct a dialect feature vector by constructing the data type representation features, function naming features, pagination syntax features and date and time processing features corresponding to the dialect type, generate the corresponding dialect-specific grammar based on the dialect feature vector, extract the dependency relationship between the standard grammar and the dialect-specific grammar to form context constraints, and combine the dialect-specific grammar and the context constraints into a grammar rule mapping set.

[0028] Traverse the nodes of the abstract syntax tree after permission processing to obtain the matching degree between each node and 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 transformation rule;

[0029] The nodes of the abstract syntax tree that have undergone permission processing are transformed using the optimal syntax transformation rule. Based on the context constraints, the transformed syntax structure of adjacent nodes is guaranteed to meet the syntax specification requirements of the target database, and SQL statements that conform to the dialect specification of the target database are generated.

[0030] A second aspect of this invention provides a natural language SQL generation system based on bidirectional mapping and semantic parsing, comprising:

[0031] The first unit is used to extract query elements from the natural language query requests input by the user role and encapsulate them into structured data;

[0032] The second unit is used to map query elements to the structured data using bidirectional hash indexing technology. It establishes a mapping relationship between query fields and standard business attributes in the forward hash table, and constructs a mapping relationship between standard business attributes and physical data tables in the reverse hash table. Through bidirectional joint retrieval, based on the query field information in the structured data, it locates the target data table and its associated tables, automatically identifies the association keys between multiple tables, and uses these association keys to semantically expand the query conditions in the structured data, automatically supplementing necessary table join conditions to form a complete query structure.

[0033] The third unit is used to perform compliance verification on the complete query structure, while performing entity linking and relation reasoning, and replacing the query conditions to obtain a standardized query expression.

[0034] The fourth unit is used to perform syntax parsing on the standardized query expression, generate an abstract syntax tree containing the query semantic structure, read the user role's permission configuration information, prune the abstract syntax tree based on the permission configuration information, and add data permission filtering conditions; and convert the permission-processed abstract syntax tree into SQL statements that conform to the target database syntax specifications.

[0035] A third aspect of the present invention,

[0036] An electronic device is provided, comprising:

[0037] processor;

[0038] Memory used to store processor-executable instructions;

[0039] The processor is configured to invoke instructions stored in the memory to execute the aforementioned method.

[0040] Fourth aspect of the embodiments of the present invention,

[0041] A computer-readable storage medium is provided, having stored thereon computer program instructions that, when executed by a processor, implement the aforementioned method.

[0042] The beneficial effects of this application are as follows:

[0043] This method solves the semantic understanding problem in the conversion of natural language to SQL. By establishing a bidirectional hash index and a joint retrieval mechanism, it can accurately identify the user's query intent, automatically complete table association and condition mapping, improve the conversion accuracy of query requests, and reduce the requirements for users' professional knowledge.

[0044] This method implements intelligent processing and security control of query requests. Through permission configuration and abstract syntax tree pruning, it ensures that the generated SQL conforms to the user's permission scope. At the same time, it performs compliance verification and entity linking, which enhances the security and reliability of query results and avoids the risk of unauthorized access.

[0045] This technology employs semantic expansion and structured processing mechanisms, which can automatically supplement necessary table join conditions, identify the association keys between multiple tables, and form a complete query structure. This greatly improves the system's adaptability and intelligence, enabling non-technical personnel to easily perform complex data query operations. Attached Figure Description

[0046] Figure 1 This is a flowchart illustrating the natural language SQL generation method based on bidirectional mapping and semantic parsing according to an embodiment of the present invention.

[0047] Figure 2 This is a schematic diagram illustrating the process of constructing and querying the integrity of the table association graph. Detailed Implementation

[0048] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0049] The technical solution of the present invention will be described in detail below with reference to specific embodiments. These specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments.

[0050] Figure 1 This is a flowchart illustrating the natural language SQL generation method based on bidirectional mapping and semantic parsing according to an embodiment of the present invention. Figure 1 As shown, the method includes:

[0051] Extract query elements from the natural language query request input by the user role and encapsulate them into structured data;

[0052] A bidirectional hash index technique is used to map query elements to the structured data. A mapping relationship between query fields and standard business attributes is established in the forward hash table, and a mapping relationship between standard business attributes and physical data tables is constructed in the reverse hash table. Through bidirectional joint retrieval, the target data table and its associated tables are located based on the query field information in the structured data, 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.

[0053] The complete query structure is subjected to compliance verification, entity linking and relation reasoning are performed, and the query conditions are replaced to obtain a standardized query expression.

[0054] The standardized query expression is parsed to generate an abstract syntax tree containing the query semantic structure. The user role's permission configuration information is read, and the abstract syntax tree is pruned based on the permission configuration information. Data permission filtering conditions are added. The permission-processed abstract syntax tree is then converted into an SQL statement that conforms to the target database syntax specification.

[0055] In one optional implementation, a bidirectional hash index technique is used to map query elements to the structured data. This involves establishing a mapping relationship between query fields and standard business attributes in the forward hash table, and constructing a mapping relationship between standard business attributes and physical data tables in the reverse hash table.

[0056] The query field information is extracted from the structured data and numerically converted to obtain the feature code. A forward mapping table is constructed for the query field information. The initial detection position is obtained by performing a modulo operation between the feature code 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 code. The probe is performed position by position along the linear detection sequence until a free storage position is found.

[0057] The query field information is converted into a query field feature vector. The dot product of the query field feature vector and the preset standard business attribute feature vector is calculated to obtain the inner product value. The magnitude of the two feature vectors is calculated. The inner product value is divided by the product of the two magnitude values ​​to obtain the similarity score. When the similarity score is greater than the preset similarity threshold, the mapping relationship between the query field information and the standard business attribute is established in the free storage location of the positive mapping table.

[0058] Physical data table information is extracted from the structured data. The standard business attributes are used as hash keys, and a reverse hash mapping table is constructed using a linked storage structure. For each standard business attribute, its correspondence with the physical data table is analyzed based on business rules, and the correspondence is stored as a linked list node. When a hash collision occurs, a new linked list node is added to the end of the corresponding linked list. The mapping relationship between the standard business attributes and the physical data table is established through the reverse hash mapping table.

[0059] After a user inputs a query request such as "Query products with sales exceeding 10 million in the first quarter of 2024 and their responsible persons," the input text is segmented into words to identify the time expression "first quarter of 2024," the conditional expression "sales exceeding 10 million," and the entity nouns "product" and "responsible person." The segmented text is then analyzed by a deep semantic parsing model. This model employs a pre-trained language model architecture and has been trained using a large amount of general-purpose corpus and enterprise-specific query corpus, enabling it to accurately identify business terms and query intent. The semantic parsing engine converts the parsing results into structured data in JSON format, containing query fields, query conditions, sorting rules, and constraints. For the example above, 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}.

[0060] Extract query field information, such as business terms like "sales revenue," "product name," and "responsible person," from structured JSON objects. Perform numerical conversion on each query field and calculate a feature code using a string hash algorithm. Specifically, extract the ASCII code value for each character in the query field, assign a weight factor to the character position, and multiply and sum the character code values ​​at each position with their respective weights to generate a 32-bit integer feature code. For example, for the query field "sales revenue," the calculated feature code is 2749385621. After generating the feature code, perform a modulo operation on the feature code based on the capacity M of the forward mapping table (usually set to a prime number greater than twice the number of query fields, such as 509) to obtain the initial probe position. Taking the feature code 2749385621 as an example, if M is 509, the initial probe position is 2749385621 % 509 = 375.

[0061] The forward mapping table uses open addressing to handle hash collisions. The table structure includes an array of key-value pairs and an array of occupancy flags. When the calculated initial probe position 375 is already occupied by another query field, a linear probe sequence is generated based on the signature. Linear probing employs a quadratic probing strategy, with the probe position sequence being (H(key) + i...). 2 ) % M, where i increments from 1. For "sales amount", if position 375 is occupied, then try position (375+1) in sequence. 2 )%509=376、(375+2 2 )%509=379、(375+3 2 509 = 384, until a free storage location is found. Once a free location is found, store the "sales amount" and its feature code in that location, and set the corresponding occupancy flag to 1.

[0062] The query field information is converted into query field feature vectors, and word embedding technology is used 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 "sales 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. The cosine similarity between the query field feature vector and the feature vectors of each standard business attribute is calculated. Specifically, the dot product of the two vectors is calculated and then divided by the product of the magnitudes of the two vectors. For example, the dot product of the "sales amount" vector and the "SALE_AMOUNT" vector is 42.67, the magnitude of the "sales amount" vector is 8.54, the magnitude of the "SALE_AMOUNT" vector is 7.32, and the similarity score is 42.67 / (8.54*7.32)=0.683.

[0063] A preset similarity threshold of 0.6 is set. When the calculated similarity score is greater than the threshold, the query field is considered to match the standard business attribute. For example, the similarity score of "Sales Amount" and "SALE_AMOUNT" is 0.683, which is greater than the threshold of 0.6. A mapping relationship from "Sales Amount" to "SALE_AMOUNT" is established in a free storage location (e.g., position 384) of the forward mapping table. The mapping relationship is stored as key-value pairs of <"Sales Amount", "SALE_AMOUNT">. If a query field matches multiple standard business attributes, the attribute with the highest similarity is selected as the mapping target. When all similarity scores are below the threshold, the query field is marked as "unrecognized" and recorded in the exception handling queue.

[0064] The reverse hash table uses a linked storage structure to establish a mapping relationship between standard business attributes and physical data tables. First, it extracts physical data table information from the structured data, including table name, field names, and their data types. Standard business attributes such as "SALE_AMOUNT" are used as hash keys, and a hash function is used to calculate their index position in the reverse hash table. The hash function for the reverse hash table uses the FNV-1a algorithm, performing bitwise operations on the input string to obtain the hash value. For example, for "SALE_AMOUNT", the calculated hash value is 3721498536. If the reverse hash table size is 1024, then the index position is 3721498536 % 1024 = 2^32.

[0065] The analysis is based on business rules to determine the correspondence between standard business attributes and physical data tables. These business rules include table field annotation matching, historical query statistics, and table relationship analysis. For example, the analysis reveals that "SALE_AMOUNT" corresponds to multiple fields in the physical database: the `total_amount` field in the `order_summary` table, the `amount` field in the `order_detail` table, and the `sales_amount` field in the `sales_report` table. A linked list node is created for each correspondence, with the node structure including table name, field name, field type, and matching degree. Examples include `<"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>`.

[0066] When hash collisions occur, i.e., different standard business attributes map to the same index position, a linked list approach is used. For example, if "SALE_AMOUNT" and "SALE_QUANTITY" both map to index position 232, the linked lists for these two attributes are connected by pointers. In the specific implementation, each slot in the reverse mapping table stores a pointer to the head node of the linked list. When a collision occurs, the head node of the new attribute's linked list is inserted at the end of the linked list at the collision position. All physical table field information corresponding to a specific standard business attribute is found by traversing the linked lists.

[0067] When the database structure changes or business rules are adjusted, the mapping relationships are updated incrementally. Updates to the forward mapping table use a mark-and-sweep mechanism, marking obsolete mapping relationships and cleaning them up at appropriate times. Updates to the reverse mapping table are implemented through linked list operations, allowing for easy addition, modification, or deletion of nodes. A full synchronization is performed periodically (e.g., every morning) to ensure that the mapping relationships are consistent with the latest database structure and business rules.

[0068] In practical applications, when a user queries "products with sales exceeding 10 million in the first quarter of 2024 and their responsible persons," the query fields "sales revenue," "product," and "responsible person" are extracted from structured data. A forward mapping table maps these query fields to the standard business attributes "SALE_AMOUNT," "PRODUCT_NAME," and "MANAGER_NAME," respectively. A reverse mapping table then determines that "SALE_AMOUNT" corresponds to the amount field in the order_detail table, "PRODUCT_NAME" corresponds to the product_name field in the product table, and "MANAGER_NAME" corresponds to the manager_name field in the sales_manager table. This establishes that the query involves three physical tables: order_detail, product, and sales_manager, providing the necessary table and field information for subsequent SQL generation.

[0069] In enterprise data environments containing tens of thousands of standard business attributes and hundreds of physical tables, traditional sequential matching methods require several seconds to complete the mapping, while bidirectional hash index technology reduces this time to milliseconds. Simultaneously, this technology effectively solves the problem of complex and variable correspondences 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 approximately 10MB of memory, and the reverse mapping table occupies approximately 15MB of memory, making it suitable for resident in memory to provide high-speed access.

[0070] In one optional implementation, 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 these association keys, the query conditions in the structured data are semantically expanded, and necessary table join conditions are automatically added to form a complete query structure, including:

[0071] Based on the results of the query field information in the forward mapping, a retrieval is performed in the reverse mapping to obtain the target physical table. By analyzing the data relationship of the target physical table, the associated table of the target physical table is obtained. The target physical table and the associated table are constructed into a table association graph, where the vertices in the table association graph represent physical tables and the edges represent the association relationships between tables.

[0072] Scan the table structure definition of the target physical table to obtain primary and foreign key constraints, use the primary and foreign key constraints as explicit associations, extract word frequency features from the field names in the target physical table and the associated table, use field pairs with a similarity of word frequency features greater than a preset potential association threshold as potential association fields, and integrate the explicit associations and the potential association fields to obtain the association key;

[0073] Analyze the distribution of the association keys in the table association graph to determine the shortest join path between tables; perform association analysis on the fields of the tables in the shortest join path to identify fields with the same business meaning and extract their table identifiers, generate corresponding table join statements, sort the table join statements according to the order of the shortest join path, supplement the table join conditions based on the sorted table join statements, and integrate the query conditions in the structured data with the supplemented table join conditions to form a complete query structure.

[0074] like Figure 2 As shown, the method includes:

[0075] Based on the extracted query fields such as "sales amount," "product name," and "responsible person," the corresponding standard business attributes "SALE_AMOUNT," "PRODUCT_NAME," and "MANAGER_NAME" are obtained through a forward mapping table. Next, these standard business attributes are used to retrieve the corresponding physical tables and fields in a reverse mapping table to determine each attribute's physical table and field. For example, "SALE_AMOUNT" maps to the amount field of the order_detail table, "PRODUCT_NAME" maps to the product_name field of the product table, and "MANAGER_NAME" maps to the manager_name field of the sales_manager table. Through this mapping relationship, the target physical tables involved in the query are identified as order_detail, product, and sales_manager.

[0076] Retrieve relationship information between target physical tables from the database metadata repository, and analyze the data relationships between tables. This relationship information includes table structure definitions, primary and foreign key constraints, field name similarity, etc. Represent the relationships between tables using a graph structure, creating a table relationship graph where vertices represent physical tables and edges represent relationships between tables. For the example above, construct a relationship graph containing three vertices: order_detail, product, and sales_manager. The initial stage of the relationship graph only includes the identified target physical tables; subsequent analysis will expand to necessary related tables.

[0077] The table structure definition of the target physical tables is scanned to extract primary and foreign key constraint information, which directly reflects the explicit relationships between tables. For the `product` and `order_detail` tables, a foreign key constraint `product_id` is found in `order_detail`, pointing to the primary key `product_id` in the `product` table. Therefore, an edge from `order_detail` to `product` is added to the relationship graph, and the association key is labeled as `product_id`. For table relationships without explicitly defined primary and foreign key constraints, the table field naming patterns are analyzed to extract word frequency features. A field name segmenter is implemented to decompose field names such as "manager_id" into "manager" and "id", and the frequency of the same morphemes between different tables is calculated. Field names are converted into word frequency vectors, and the similarity between fields in different tables is calculated. When the word frequency feature similarity between two fields exceeds a preset potential association threshold (e.g., 0.75), they are identified as a potentially related field pair.

[0078] For the `order_detail` and `sales_manager` tables, the naming patterns of `order_detail.manager_id` and `sales_manager.manager_id` are found to be highly similar, with a word frequency similarity of 1.0, exceeding the preset threshold of 0.75. Therefore, this pair of fields is identified as potential related fields. The explicit relationships (primary and foreign key constraints) and potential related fields are integrated to generate a complete set of related keys. For the three tables in the example, the set of related keys includes: {(order_detail.product_id, product.product_id), (order_detail.manager_id, sales_manager.manager_id)}. These related keys form the edges in the table relationship graph, with each edge carrying information about the related field pair.

[0079] Analyze the distribution of join keys in the table association graph to determine the shortest join path between tables. A breadth-first search-based pathfinding algorithm is used to minimize the number of joined tables and avoid large table joins. The algorithm starts with the starting table and expands layer by layer to adjacent tables until all target tables are covered. For each path, a path weight is calculated, considering factors such as table size, index status, and cardinality of the join fields. Paths with lower weights are prioritized to improve query efficiency. For the example query, the determined shortest join path is: product → order_detail → sales_manager, meaning the order_detail table will serve as the intermediate table joining the product and sales_manager tables.

[0080] After determining the shortest join path, perform a correlation analysis on the table fields along the path to identify fields with the same business meaning. Examine field names, data types, comments, and historical query patterns to determine the semantic similarity of the fields. For `product.product_id` and `order_detail.product_id`, they are identified as having the same business meaning, representing product identifiers. Similarly, `order_detail.manager_id` and `sales_manager.manager_id` are identified as semantically similar fields representing sales manager identifiers. Extract the table identifiers containing these fields and generate table join statements for each pair of related fields. For the join between `product` and `order_detail`, the generated statement is "product INNER JOIN order_detail ON product.product_id = order_detail.product_id"; for the join 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".

[0081] The table join statements are sorted according to the shortest join path to ensure 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. The table join conditions are then supplemented based on the sorted table join statements. This process includes converting the identified inter-table relationships into canonical SQL join condition expressions. Specifically, equi-join conditions of the form "table1.related field = table2.related field" are generated for each pair of adjacent tables, such as "product.product_id = order_detail.product_id" and "order_detail.manager_id = sales_manager.manager_id". Simultaneously, appropriate join types (INNER JOIN, LEFT JOIN, etc.) are selected based on field characteristics and table relationships, and the correct join order is assigned to each join condition to ensure that the logical flow from the parent table to the child 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 filtering conditions and 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":10 000000},{"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}.

[0082] Suppose a user queries "Query the products purchased by the customer and their suppliers," involving five tables: customer, order_header, order_detail, product, and supplier. Analysis of the relationship graph reveals that these tables form a path: customer → order_header → order_detail → product → supplier. The system automatically identifies the relationships 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. It generates complete join paths, ensuring that the query accurately joins all relevant tables.

[0083] For scenarios with multiple join paths, an intelligent path selection algorithm is used. This algorithm considers factors such as table size, selectivity of join fields, index status, and historical query performance to calculate a comprehensive score for each path. For example, if there are two paths joining `product` and `sales_manager`: path 1 connects through the `order_detail` table, and path 2 connects through the `product_manager` table, the algorithm analyzes the data volume and index status of the two intermediate tables. If the `order_detail` table has 10 million rows and the join fields have no indexes, while the `product_manager` table has only 10,000 rows and the join fields have indexes, the latter will be selected as the preferred path to improve query efficiency.

[0084] For one-to-many relationships, such as one product corresponding to multiple orders, the system identifies this relationship and uses the appropriate join type when generating join conditions. For many-to-many relationships, such as a product and tag linked through an intermediate table `product_tag`, the system can automatically identify the intermediate table and generate the correct two join conditions. It can even handle self-join scenarios, such as `manager_id` in the employee table referencing `employee_id` in the same table, forming a hierarchical management relationship. Self-join issues are resolved through a table alias mechanism, assigning different aliases to different roles within the same table.

[0085] The join condition generation process considers field type compatibility. When the types of related fields are not completely consistent, appropriate type conversion functions are added. For example, if the `product_id` in the `product` table is of type VARCHAR, while the `product_id` in the `order_detail` table is of type INTEGER, the join condition "product.product_id = CAST(order_detail.product_id AS VARCHAR)" will be generated, ensuring the correct execution of the join operation. Through this series of sophisticated table association analysis and join condition generation mechanisms, multi-table relationships can be accurately identified from the user's natural language queries, and efficient SQL join structures can be automatically constructed, greatly reducing the difficulty for users to understand and write complex table joins.

[0086] In one optional implementation, the complete query structure undergoes compliance verification, entity linking and relational reasoning are performed, and query conditions are replaced to obtain a standardized query expression including:

[0087] Check the validity of table names and field names in the complete query structure and generate a verification result; extract entity mention information from the verification result, calculate string edit distance and contextual semantic similarity based on the entity mention information, and filter out target entities based on the string edit distance and contextual semantic similarity;

[0088] The shortest path between the target entities is retrieved, and the semantic features of the shortest path are extracted. The semantic features are input into a predefined set of Horn clause rules for forward reasoning to obtain the relation derivation chain between the target entities. The confidence of each path in the relation derivation chain is calculated, and the paths with confidence greater than the relation confidence threshold are determined as valid semantic associations.

[0089] Extract the query condition features from 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 features, and combine it with other query condition features in the complete query structure to form a standardized query expression.

[0090] The complete query structure compares table names and field names with the database metadata to verify their existence in the target database. For table names, the `information_schema.tables` view is queried; for field names, the `information_schema.columns` view is queried. For example, a user querying "Query products with sales exceeding 10 million in the first quarter of 2024 and their responsible persons" will generate a complete query structure containing the tables `product`, `order_detail`, and `sales_manager`. This will verify the existence of these three tables in the target database, and also verify the existence of the fields `product.product_name`, `sales_manager.manager_name`, `order_detail.amount`, and `order_detail.order_date` in their respective tables. For non-existent tables or fields, validation error messages will be generated; for tables or fields that exist but have slightly different names, potential matches will be recorded. The results generated during the validation process include the validation status of all tables and fields, as well as potential matching suggestions.

[0091] Entity mention information is extracted from the validation results, including table names, field names, and their semantic meanings involved in the query. The edit distance between the user-mentioned entities and the actual entities in the database is calculated using the Levenshtein distance algorithm to determine the minimum number of edit operations required to convert one string to another. For example, if a user mentions "produt" and the database has a table named "product", the edit distance is 1, meaning the character 'c' needs to be added; if a user mentions "sales_amt" and the database has a field named "sales_amount", the edit distance is 5, meaning "amt" needs to be replaced with "amount". Simultaneously, 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 the vectors. For "sales_amt" and "sales_amount", despite the large edit distance, their high semantic similarity results in a contextual semantic similarity of 0.92. Taking into account both edit distance and semantic similarity, entities in the database are identified as target entities when the edit distance is less than a preset threshold (e.g., 3) or the semantic similarity is greater than a preset threshold (e.g., 0.85).

[0092] Using a graph-based search algorithm, the tables and fields in the database are constructed into an entity-relationship graph, where nodes represent tables or fields, and edges represent foreign key relationships between tables or semantic associations between fields. For the three tables `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 and types of the associated fields, whether they are primary keys or foreign keys, the cardinality of the association (one-to-one, one-to-many, etc.), and the meaning of the association in the business context. For example, the `product_id` association represents the subordinate relationship between a product and an order, and the `manager_id` association represents the responsibility relationship between an order and a sales manager. These semantic features are encoded into feature vectors and used as input for relational reasoning.

[0093] Horn clauses are expressions in formal logic, such as "If A and B and C, then D", suitable for expressing reasoning rules between entities. Maintain a rule base for a business domain, containing multiple business rules in the form of Horn clauses. For example, rules like "If A is a product and B is an order and B.product_id = A.id, then B is A's sales record", "If B is an order and C is a 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 sales record, then C is indirectly responsible for A's sales". Substitute the target entities and their relationships into these rules, and derive the relationship chain between entities through forward reasoning. For the above example, derive the relationship chain "sales_manager is responsible for the sales of products in order_detail".

[0094] When calculating the confidence score of each path in the relation derivation chain, a comprehensive scoring method combining rule weights and entity matching scores is used. Each rule has a preset weight reflecting its reliability in business operations; each entity match also has a corresponding similarity score. The path confidence score is obtained by multiplying the rule weights by the entity matching scores. For example, for the derivation path "sales_manager is responsible for the sales of products in order_detail", if the rule weight involved is 0.9 and the entity matching score is 0.95, then the path confidence score is 0.9 × 0.95 = 0.855. A relation confidence threshold of 0.8 is set; when the path confidence score is greater than this threshold, the path is determined to be a valid semantic association. These valid semantic associations constitute a logical relationship network between entities, used for subsequent query condition processing.

[0095] Extract query condition features from the complete query structure, including field names, operators, values, and logical relationships between conditions. For the example query conditions "order_detail.amount>10000000" and "order_detail.order_date BETWEEN '2024-01-01' AND '2024-03-31'", extract the field names "amount" and "order_date", the operators ">" and "BETWEEN", and the values ​​"10000000" and "['2024-01-01', '2024-03-31']". Map these query condition features to the standard attribute constraints corresponding to valid semantic associations. Standard attribute constraints are stored in the system's knowledge base and are optimized query condition templates. For example, the standard constraint for "sales amount" is "amount>threshold", and the standard constraint for "time range" is "order_date BETWEEN start date AND end date". Calculate the matching degree between the query condition features and the standard attribute constraints, and select the standard constraint with the highest matching degree to replace it.

[0096] The query condition replacement process corrects ambiguity and non-standardization in condition expressions. It was found that in the user condition "order_detail.amount>10000000", "10000000" is a specific numerical value, but in actual business operations, the sales threshold is dynamically adjusted based on factors such as product category and sales region. A query of the business rule base revealed that the criterion for high-value orders is "sales exceeding 150% of the average sales of the product category". The fixed threshold "10000000" was replaced with the dynamically calculated 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 business logic and can adapt to the sales characteristics of different product categories.

[0097] For the date range condition, check whether "2024-01-01" to "2024-03-31" meets the "first quarter" defined by the business. In the time standard library, the standard definition of the first quarter is FROM January 1 to 3 1st of each year. Confirm that the date range entered by the user is consistent with the standard definition, so the original condition remains unchanged. However, the conditional 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')" to improve the query efficiency using a left-closed right-open interval.

[0098] When it is found that the query contains two conditions, "sales>0" and "sales>10000000", the redundant condition "sales>0" will be automatically deleted; when it is found that the conditions "product status='active'" and "product listing date>current date" are contradictory, a warning will be issued or one of them will be retained according to the 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 the correct execution of the conditions in SQL.

[0099] The processed query conditions are combined with other elements in the complete query structure (tables, fields, joins, grouping, sorting, etc.) to form a standardized query expression. The standardized expression uses a unified JSON format and includes standardized table names, field names, conditional expressions, join relationships, grouping fields, sorting rules, and constraints. For the example query, the normalized 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.ord er_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}.

[0100] Standardized query expressions have a unified structure and semantics, facilitating subsequent SQL generation and optimization. Each element in the expression is normalized to ensure compatibility with the target database's field names, data types, and syntax rules. Conditional expressions have been replaced with optimized forms to avoid potential performance issues and semantic ambiguities. Table join conditions have been sorted according to the optimal path to ensure the efficiency of the query execution plan. Through this series of compliance checks, entity links, relational reasoning, and conditional substitution processes, the user's natural language query is transformed into an accurate, efficient, and business-compliant standardized query expression, laying a solid foundation for subsequent SQL generation.

[0101] In one optional implementation, the standardized query expression is parsed to generate an abstract syntax tree containing the query semantic structure. User role permission configuration information is read, and the abstract syntax tree is pruned based on the permission configuration information. Data permission filtering conditions are then added, including:

[0102] An Antlr parser is constructed based on lexical rules for identifying identifiers, keywords, and operators, and grammatical rules for defining the grammatical structure of query statements. The Antlr parser is used to perform grammatical parsing on standardized query expressions to generate a set of nodes containing semantic types, attribute values, and hierarchical relationship information. The set of nodes is then connected to obtain an abstract syntax tree containing the semantic structure of the query.

[0103] Read the permission configuration information of the user role to obtain the permission inheritance relationship and row-level data filtering condition set of the user role;

[0104] Based on the permission inheritance relationship, the node set of the abstract syntax tree is traversed, the access permission of each node is checked, and nodes without access permission are deleted from the abstract syntax tree to obtain the pruned syntax tree; each filtering condition in the row-level data filtering condition set is parsed into a predicate expression, and a condition node is constructed based on the predicate expression, and the condition nodes are organized into a filtering condition subtree; at the WHERE clause position of the pruned syntax tree, the filtering condition subtree is connected to the query condition in the pruned syntax tree through a logical AND operation.

[0105] A dedicated parser is built based on SQL language lexical and syntactic rules. Lexical rules identify basic elements in SQL such as identifiers, keywords, and operators. The lexical rule set includes keywords such as SELECT, FROM, WHERE, and JOIN; various operators such as comparison operators (>, <, =, !=), logical operators (AND, OR, NOT), arithmetic operators (+, -, *, / ); and identifier rules, allowing table and field names to use combinations of letters, numbers, and underscores, and supporting dot-separated qualified names such as "table.column". Syntactic rules define the structure of query statements, including the composition rules of each part such as the SELECT clause, FROM clause, WHERE clause, GROUP BY clause, HAVING clause, and ORDER BY clause, as well as the relationships between them. It also supports complex query structures such as subqueries, union queries, and common table expressions (CTEs) and other advanced SQL features.

[0106] The standardized query expression is parsed using the built 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 WHERE product_id IN (SELECT product_id FROM product WHERE category_id = The code snippet `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}` first converts the string into an SQL string, then inputs it into the parser for processing. During parsing, the parser recursively analyzes the input string according to the syntax rules, identifies each syntax unit, and constructs the corresponding nodes. Each node contains semantic type (such as SELECT node, table reference node, field reference node, condition expression node, etc.), attribute values ​​(such as table name, field name, operator, constant value, etc.), and location information (row number, column number).

[0107] The parsed nodes are connected hierarchically to form a complete Abstract Syntax Tree (AST). In the AST, the root node represents the entire query statement, and the child nodes are organized according to the SQL syntax structure, forming a hierarchical tree structure. For example, the SELECT clause forms a subtree containing multiple field reference nodes; the FROM clause forms another subtree containing table reference nodes and table join nodes; and the WHERE clause forms a condition expression subtree containing various condition nodes and logical operation nodes. A unique identifier is assigned to each node in the AST, establishing parent-child and sibling relationships between nodes, and recording the node's depth and path information to facilitate subsequent tree traversal and operations. The root node of the example query's AST is QueryNode, with SelectListNode (containing two ColumnRefNodes: p.product_name and sm.manager_name), FromNode (containing TableNode:product, TableNode:order_detail, and TableNode:sales_manager, as well as the JoinNode connecting them), and WhereNode (containing multiple condition expression nodes connected by AND nodes).

[0108] The system reads the user role's permission configuration information, retrieving the complete permission definition for the current user's role from the permission management system. The permission configuration information includes two parts: permission inheritance relationships and a set of row-level data filtering conditions. Permission inheritance relationships define the hierarchical structure between roles; a role can inherit all permissions from its parent role. For example, the Sales Manager role inherits permissions from the Salesperson role, while the Sales Director role inherits permissions from the Sales Manager role. The system recursively parses the permission inheritance chain, summarizing all inherited permissions for the current user role. The set of row-level data filtering conditions defines access restriction rules for different data tables, typically described using expressions. For example, the Salesperson role has the filter condition "sales_manager.manager_id = ${current_user_id}", indicating that it can only access sales data it is responsible for; the Sales Manager role has the filter condition "sales_manager.department_id = ${current_user_dept_id}", indicating that it can only access sales data from its department.

[0109] Based on permission inheritance, the node set of the abstract syntax tree is traversed and permission checks are performed. The traversal uses a depth-first search algorithm, recursively visiting each child node starting from the root node. For each node, it checks whether its corresponding data object (table, field, function, etc.) is within the current user's permission scope. Permission checks are based on fine-grained object-level permission control, supporting multi-dimensional control such as table-level, column-level, row-level, and operation-level permissions. For example, it checks whether the user has permission to access the `product` table, the `product_name` field, and the `amount` field of the `order_detail` table. If a node's corresponding data object is found to be outside the user's permission scope, the node is marked as invalid, and the node and all its child nodes are deleted from the abstract syntax tree. This process is called syntax tree pruning. Pruning is achieved by modifying the parent-child relationship of nodes; the parent node of the deleted node directly points to another valid child node, or the parent node is marked as invalid when there are no valid child nodes. Through pruning, it is ensured that the final query only includes data objects that the user has the right to access.

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

[0111] Condition nodes are constructed based on the parsed predicate expressions. Each predicate expression corresponds to one condition node, and the type of the condition node is determined by the predicate type, such as equality condition nodes, comparison condition nodes, IN condition nodes, etc. Each condition node contains three parts: operands, operators, and values. Operands are usually field references, operators are comparison operators, and values ​​can be constants, variables, or subqueries. The constructed condition nodes are organized into filtering condition subtrees according to logical relationships. Multiple conditions are usually connected by AND nodes, indicating that all conditions must be met simultaneously. For example, for the filtering conditions "sales_manager.manager_id = 'U10086'" and "sales_manager.status = 'active'" for the salesperson role, a subtree containing two equality condition nodes is constructed, and the two nodes are connected by AND nodes.

[0112] In the pruned syntax tree, at the WHERE clause position, the filtering condition subtree is connected to the original query conditions using a logical AND operation. If the original query already has a WHERE clause, an AND node is added under the original WHERE node, connecting the original condition subtree and the new filtering condition subtree. If the original query does not have a WHERE clause, a new WHERE node is created, with the filtering condition subtree as its child node. This method ensures that row-level data filtering conditions are forcibly applied regardless of whether the user's query contains conditions, preventing unauthorized data access. For the example query, assuming the current user is a sales manager with permission to access the sales data of 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 an AND logical operation.

[0113] For syntax trees containing subqueries, the same permission checks and filtering condition appending processes are recursively applied to each subquery. This ensures that regardless of the complexity of the query structure, all data access is subject to uniform permission control. A view replacement mechanism is also implemented. For tables that users do not have direct access to but can access through views, the table references are automatically replaced with the corresponding authorized view references, thus providing flexible access methods while ensuring data security. For example, if a user does not have 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.

[0114] For sensitive fields, different de-identification rules are applied based on the user's permission level, such as masking, truncation, and encryption. These de-identification operations are implemented by inserting function call nodes into the syntax tree, replacing references to sensitive fields with de-identified function calls. For example, replacing "sm.phone_number" with "MASK(sm.phone_number, 3, 4)" means only the first 3 and last 4 digits of the phone number are displayed, with asterisks used to represent the middle digits. Through fine-grained manipulation of the syntax tree, flexible and powerful data access control is achieved, ensuring both correct query execution and data security and privacy protection. The final abstract syntax tree, processed by access control, serves as the basis for generating optimized SQL statements, ensuring that the generated SQL statements both match the user's query intent and strictly adhere to data access permission control rules.

[0115] In one alternative implementation, converting the permission-processed abstract syntax tree into SQL statements conforming to the target database syntax specification includes:

[0116] Obtain the dialect type of the target database, construct a dialect feature vector by constructing the data type representation features, function naming features, pagination syntax features and date and time processing features corresponding to the dialect type, generate the corresponding dialect-specific grammar based on the dialect feature vector, extract the dependency relationship between the standard grammar and the dialect-specific grammar to form context constraints, and combine the dialect-specific grammar and the context constraints into a grammar rule mapping set.

[0117] Traverse the nodes of the abstract syntax tree after permission processing to obtain the matching degree between each node and 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 transformation rule;

[0118] The nodes of the abstract syntax tree that have undergone permission processing are transformed using the optimal syntax transformation rule. Based on the context constraints, the transformed syntax structure of adjacent nodes is guaranteed to meet the syntax specification requirements of the target database, and SQL statements that conform to the dialect specification of the target database are generated.

[0119] The database connection information is read from the configuration center, and the database type (e.g., MySQL, PostgreSQL, Oracle, SQLite) is extracted. A feature information database is maintained for each database dialect, including data type representation features, function naming features, pagination syntax features, and date and time handling features. Data type representation features describe the declaration methods of basic data types, such as INT for integers in MySQL and NUMBER for Oracle; VARCHAR for strings in MySQL and TEXT for strings in PostgreSQL. Function naming features record the naming differences of commonly used functions, such as CONCAT for string concatenation in MySQL and the || operator in Oracle; CURDATE() for retrieving the current date in MySQL and DATE('now') in SQLite. Pagination syntax features define the implementation method of pagination queries, such as LIMIT offset, count syntax in MySQL, ROWNUM or ROW_NUMBER() OVER() syntax in Oracle, and LIMIT count OFFSET offset syntax in SQLite. The characteristics of date and time processing include differences in date formatting, date calculation, and time zone handling. For example, date addition and subtraction are performed using the DATE_ADD and DATE_SUB functions in MySQL, while in PostgreSQL, the + and - operators are used in conjunction with the INTERVAL keyword.

[0120] The aforementioned features are constructed into dialect feature vectors to generate corresponding dialect-specific grammar for the target database. Each dialect feature vector is a multi-dimensional vector, with each dimension corresponding to a type of grammatical feature. The vector value represents the implementation of that feature in the target dialect. For example, for the MySQL dialect, the feature vector includes a pagination syntax dimension value of "LIMIT", a string concatenation dimension value of "CONCAT", and a date formatting dimension value of "DATE_FORMAT". Based on the dialect feature vectors, SQL syntax fragments compatible with the target database are generated through template replacement and rule derivation. For example, standard pagination expressions are converted to MySQL's "LIMIT offset, count" form or Oracle's "WHERE ROWNUM BETWEEN start ANDend" form. These dialect-specific grammars form a grammar conversion rule base for use in subsequent node conversions.

[0121] The dependencies between standard syntax and dialect-specific syntax are extracted to form contextual constraints. These constraints describe the contextual elements that must be considered during syntax transformation, ensuring that the transformed SQL fragments are consistent with the context. These constraints include expression evaluation order, subquery position restrictions, function nesting rules, and alias reference domains. For example, in MySQL, derived tables (subqueries in the FROM clause) must have aliases, while this constraint is more lenient in SQLite; in Oracle, the ORDER BY clause cannot directly reference aliases in the SELECT list, while in PostgreSQL it can. The dialect-specific syntax and contextual constraints are combined to form a complete set of syntax rule mappings. This set is a multi-level rule base, including node type mappings, expression transformation rules, function mapping tables, and special syntax processing rules.

[0122] A depth-first search algorithm is used to visit each node of the abstract syntax tree after permission processing. For each node, its type, attributes, and child node information are extracted to form a node feature description. The node feature description includes node type (e.g., SELECT, FROM, WHERE, function call, etc.), operand type, return type, and context. The node feature description is matched against each rule in the syntax rule mapping set to calculate a matching score. The matching score calculation uses a weighted similarity algorithm, considering multiple factors such as node type matching, attribute compatibility, and context compliance. For example, for the function call node SUBSTRING(str, start, length), the string truncation function mapping rules in different dialects are checked, such as SUBSTRING in MySQL, SUBSTR in Oracle, and SUBSTR in SQLite, and the differences in parameter order and meaning are considered. The matching score ranges from 0 to 1, where 1 represents a complete match and 0 represents a complete mismatch. The rule with the highest matching score is selected as the optimal syntax transformation rule from the syntax rule mapping set. When multiple rules have the same matching score, the rule priority, execution efficiency, and maintainability are considered to select the most suitable rule.

[0123] The selected optimal syntax transformation rules are used to transform the nodes of the abstract syntax tree. The transformation process is a recursive operation, starting from the leaf nodes and transforming upwards layer by layer. For each node, the corresponding transformation rule is applied to generate SQL fragments in the target dialect. For example, for the MySQL dialect, the standard SUBSTRING function is converted to MySQL SUBSTRING syntax; standard date comparison expressions are converted to forms using MySQL date functions. During the transformation process, contextual constraints are strictly followed to ensure that the transformed syntax structure of adjacent nodes meets the syntax specifications of the target database. For example, it ensures that subquery positions are valid, aggregate functions are used in accordance with specifications, and ORDER BY and GROUP BY clauses are referenced correctly. For complex expressions, the components and dependencies of the expression are analyzed to ensure that the structure and evaluation order of the transformed expression remain unchanged. When a specific function does not have a direct corresponding implementation in the target dialect, an equivalent alternative is used, such as simulating a complex function by combining multiple simple functions, or using a subquery to replace an unsupported syntax structure.

[0124] Taking date processing as an example, different databases support date formatting very differently. When converting the date formatting expression FORMAT_DATE(date, 'yyyy-MM-dd') in a standard query to MySQL, it becomes DATE_FORMAT(date, '%Y-%m-%d'); when converting to Oracle, it becomes TO_CHAR(date, 'YYYY-MM-DD'); and when converting to SQLite, it uses strftime('%Y-%m-%d', date). When handling paginated queries, the appropriate pagination implementation is selected based on the target dialect. For standard OFFSET-LIMIT pagination, the syntax "LIMIT offset,count" is used when converting to MySQL; the syntax "LIMIT count OFFSET offset" is used when converting to PostgreSQL; and when converting to Oracle, ROWNUM or ROW_NUMBER() OVER() is used depending on the Oracle version. It also handles the difference in the order of NULL values. In MySQL, NULL values ​​are placed at the beginning by default, while in Oracle, they are placed at the end by default. Appropriate NULLS FIRST or NULLS LAST modifiers will be added to the ORDER BY clause.

[0125] In MySQL, the Boolean type is actually TINYINT(1), in PostgreSQL it is BOOLEAN, and in Oracle it is simulated using NUMBER(1). The representation of Boolean values ​​is automatically adjusted according to the target dialect to ensure correct execution of query conditions. For regular expressions, MySQL uses the REGEXP operator, PostgreSQL uses the ~ operator, and Oracle uses the REGEXP_LIKE function, selecting the appropriate regular expression syntax based on dialect differences. Cross-dialect conversions for common operations such as string concatenation, string truncation, and mathematical functions are handled to ensure consistency of functional semantics. Finally, a complete SQL statement conforming to the target database dialect specification is generated.

[0126] This invention provides a natural language SQL generation system based on bidirectional mapping and semantic parsing, the system comprising:

[0127] The first unit is used to extract query elements from the natural language query requests input by the user role and encapsulate them into structured data;

[0128] The second unit is used to map query elements to the structured data using bidirectional hash indexing technology. It establishes a mapping relationship between query fields and standard business attributes in the forward hash table, and constructs a mapping relationship between standard business attributes and physical data tables in the reverse hash table. Through bidirectional joint retrieval, based on the query field information in the structured data, it locates the target data table and its associated tables, automatically identifies the association keys between multiple tables, and uses these association keys to semantically expand the query conditions in the structured data, automatically supplementing necessary table join conditions to form a complete query structure.

[0129] The third unit is used to perform compliance verification on the complete query structure, while performing entity linking and relation reasoning, and replacing the query conditions to obtain a standardized query expression.

[0130] The fourth unit is used to perform syntax parsing on the standardized query expression, generate an abstract syntax tree containing the query semantic structure, read the user role's permission configuration information, prune the abstract syntax tree based on the permission configuration information, and add data permission filtering conditions; and convert the permission-processed abstract syntax tree into SQL statements that conform to the target database syntax specifications.

[0131] A third aspect of the present invention provides an electronic device, comprising:

[0132] processor;

[0133] Memory used to store processor-executable instructions;

[0134] The processor is configured to invoke instructions stored in the memory to execute the aforementioned method.

[0135] A fourth aspect of the present invention provides a computer-readable storage medium having stored thereon computer program instructions that, when executed by a processor, implement the aforementioned method.

[0136] This invention can be a method, apparatus, system, and / or computer program product. The computer program product may include a computer-readable storage medium having computer-readable program instructions loaded thereon for performing various aspects of the invention.

[0137] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features therein. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A natural language SQL generation method based on bidirectional mapping and semantic parsing, characterized in that, include: Extract query elements from the natural language query request input by the user role and encapsulate them into structured data; A bidirectional hash index technique is used to map query elements to the structured data. A mapping relationship between query fields and standard business attributes is established in the forward hash table, and a mapping relationship between standard business attributes and physical data tables is constructed in the reverse hash table. This includes: The query field information is extracted from the structured data and numerically converted to obtain the feature code. A forward mapping table is constructed for the query field information. The initial detection position is obtained by performing a modulo operation between the feature code 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 code. The probe is performed position by position along the linear detection sequence until a free storage position is found. The query field information is converted into a query field feature vector. The dot product of the query field feature vector and the preset standard business attribute feature vector is calculated to obtain the inner product value. The magnitude of the two feature vectors is calculated. The inner product value is divided by the product of the two magnitude values ​​to obtain the similarity score. When the similarity score is greater than the preset similarity threshold, the mapping relationship between the query field information and the standard business attribute is established in the free storage location of the positive mapping table. Extract the physical data table information from the structured data, use the standard business attributes as hash keys, and construct a reverse hash mapping table using a linked storage structure; for each standard business attribute, analyze its correspondence with the physical data table based on business rules, and store the correspondence as a linked list node; when a hash collision occurs, add a new linked list node to the end of the corresponding linked list, and establish the mapping relationship between the standard business attributes and the physical data table through the reverse hash mapping table; Through bidirectional joint retrieval, based on the query field information in the structured data, the target data table and its related 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. The complete query structure is subjected to compliance verification, entity linking and relation reasoning are performed, and the query conditions are replaced to obtain a standardized query expression. The standardized query expression is parsed to generate an abstract syntax tree containing the query semantic structure. The user role's permission configuration information is read, and the abstract syntax tree is pruned based on the permission configuration information. Data permission filtering conditions are added. The permission-processed abstract syntax tree is then converted into an SQL statement that conforms to the target database syntax specification.

2. The method according to claim 1, characterized in that, Through bidirectional joint retrieval, based on the query field information in the structured data, the target data table and its related 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 results of the query field information in the forward mapping, a retrieval is performed in the reverse mapping to obtain the target physical table. By analyzing the data relationship of the target physical table, the associated table of the target physical table is obtained. The target physical table and the associated table are constructed into a table association graph, where the vertices in the table association graph represent physical tables and the edges represent the association relationships between tables. Scan the table structure definition of the target physical table to obtain primary and foreign key constraints, use the primary and foreign key constraints as explicit associations, extract word frequency features from the field names in the target physical table and the associated table, use field pairs with a similarity of word frequency features greater than a preset potential association threshold as potential association fields, and integrate the explicit associations and the potential association fields to obtain the association key; Analyze the distribution of the association keys in the table association graph to determine the shortest join path between tables; perform association analysis on the fields of the tables in the shortest join path to identify fields with the same business meaning and extract their table identifiers, generate corresponding table join statements, sort the table join statements according to the order of the shortest join path, supplement the table join conditions based on the sorted table join statements, and integrate the query conditions in the structured data with the supplemented table join conditions to form a complete query structure.

3. The method according to claim 1, characterized in that, The complete query structure undergoes compliance verification, entity linking and relational reasoning, and query conditions are replaced to obtain standardized query expressions including: Check the validity of table names and field names in the complete query structure and generate a verification result; extract entity mention information from the verification result, calculate string edit distance and contextual semantic similarity based on the entity mention information, and filter out target entities based on the string edit distance and contextual semantic similarity; The shortest path between the target entities is retrieved, and the semantic features of the shortest path are extracted. The semantic features are input into a predefined set of Horn clause rules for forward reasoning to obtain the relation derivation chain between the target entities. The confidence of each path in the relation derivation chain is calculated, and the paths with confidence greater than the relation confidence threshold are determined as valid semantic associations. Extract the query condition features from 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 features, and combine it with other query condition features in the complete query structure to form a standardized query expression.

4. The method according to claim 1, characterized in that, The standardized query expression is parsed to generate an abstract syntax tree containing the query semantic structure. User role permission configuration information is read, and the abstract syntax tree is pruned based on this permission configuration information. Data permission filtering conditions are then added, including: An Antlr parser is constructed based on lexical rules for identifying identifiers, keywords, and operators, and grammatical rules for defining the grammatical structure of query statements. The Antlr parser is used to perform grammatical parsing on standardized query expressions to generate a set of nodes containing semantic types, attribute values, and hierarchical relationship information. The set of nodes is then connected to obtain an abstract syntax tree containing the semantic structure of the query. Read the permission configuration information of the user role to obtain the permission inheritance relationship and row-level data filtering condition set of the user role; Based on the permission inheritance relationship, the node set of the abstract syntax tree is traversed, the access permission of each node is checked, and nodes without access permission are deleted from the abstract syntax tree to obtain the pruned syntax tree; each filtering condition in the row-level data filtering condition set is parsed into a predicate expression, and a condition node is constructed based on the predicate expression, and the condition nodes are organized into a filtering condition subtree; at the WHERE clause position of the pruned syntax tree, the filtering condition subtree is connected to the query condition in the pruned syntax tree through a logical AND operation.

5. The method according to claim 1, characterized in that, Converting the permission-processed abstract syntax tree into SQL statements that conform to the target database's syntax specifications includes: Obtain the dialect type of the target database, construct a dialect feature vector by constructing the data type representation features, function naming features, pagination syntax features and date and time processing features corresponding to the dialect type, generate the corresponding dialect-specific grammar based on the dialect feature vector, extract the dependency relationship between the standard grammar and the dialect-specific grammar to form context constraints, and combine the dialect-specific grammar and the context constraints into a grammar rule mapping set. Traverse the nodes of the abstract syntax tree after permission processing to obtain the matching degree between each node and 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 transformation rule; The nodes of the abstract syntax tree that have undergone permission processing are transformed using the optimal syntax transformation rule. Based on the context constraints, the transformed syntax structure of adjacent nodes is guaranteed to meet the syntax specification requirements of the target database, and SQL statements that conform to the dialect specification of the target database are generated.

6. A natural language SQL generation system based on bidirectional mapping and semantic parsing, used to implement the method as described in any one of claims 1-5, characterized in that, include: The first unit is used to extract query elements from the natural language query requests input by the user role and encapsulate them into structured data; The second unit is used to map query elements to the structured data using bidirectional hash indexing technology. It establishes a mapping relationship between query fields and standard business attributes in the forward hash table, and constructs a mapping relationship between standard business attributes and physical data tables in the reverse hash table. Through bidirectional joint retrieval, based on the query field information in the structured data, it locates the target data table and its associated tables, automatically identifies the association keys between multiple tables, and uses these association keys to semantically expand the query conditions in the structured data, automatically supplementing necessary table join conditions to form a complete query structure. The third unit is used to perform compliance verification on the complete query structure, while performing entity linking and relation reasoning, and replacing the query conditions to obtain a standardized query expression. The fourth unit is used to perform syntax parsing on the standardized query expression, generate an abstract syntax tree containing the query semantic structure, read the user role's permission configuration information, prune the abstract syntax tree based on the permission configuration information, and add data permission filtering conditions. The abstract syntax tree with permissions processed is converted into SQL statements that conform to the syntax specifications of the target database.

7. An electronic device, characterized in that, include: processor; Memory used to store processor-executable instructions; The processor is configured to invoke instructions stored in the memory to execute the method according to any one of claims 1 to 5.

8. A computer-readable storage medium having computer program instructions stored thereon, characterized in that, When the computer program instructions are executed by the processor, they implement the method described in any one of claims 1 to 5.

Images