A method and system for generating SQL with large model semantic constraints and dynamic checking

Abstract

The application provides a large model semantic constraint and dynamic checking SQL generation method and system, specifically comprising the following steps: S1: deploying an SQL generation engine and collecting basic data information; S2: automatically extracting the semantic of the basic data information, and constructing a business semantic knowledge graph; S3: taking the basic table structure information and the key semantic constraint rules in the knowledge graph as an external knowledge base and a Prompt constraint of a large language model; S4: the large language model parses the user natural language query demand under the constraint, and outputs an initial JSON; S5: performing dynamic multi-level checking on the JSON request; S6: generating a target SQL script and storing after the checking passes; S7: collecting user feedback information to update the knowledge graph and the business rule base. Through the knowledge graph semantic constraint and multi-level dynamic checking, the large model table field illusion is effectively inhibited, and the accuracy and interpretability of SQL generation are improved.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to a method and system for generating SQL with large-scale semantic constraints and dynamic validation. Background Technology

[0002] With the rapid growth of enterprise data and the increasing complexity of business analysis needs, Structured Query Language (SQL), as the core operating language of relational databases, directly impacts the timeliness of business decisions and the effectiveness of data-driven approaches through its writing efficiency and accuracy. Thanks to the rapid development of natural language processing technology, especially the breakthrough progress in large language models, the NL2SQL solution based on large language models allows users to describe their data query needs in natural language, which is then automatically parsed and generates the corresponding SQL statements by the large language model. This significantly lowers the technical barrier to SQL writing and improves the convenience of data querying.

[0003] Although NL2SQL solutions based on large language models have made some progress in lowering the barrier to entry, existing technologies still have the following core technical shortcomings in practical applications, especially in complex enterprise-level business scenarios (such as insurance and finance): 1. Existing technologies lack effective mechanisms to deeply embed the actual database structure into the model inference process, making it difficult to fundamentally suppress such illusory outputs. Typically, large language models' understanding of database schemas relies on general patterns in the training data, rather than the actual structure of the target database. When faced with specific table names, field names, or extended fields in a private enterprise database, the model is prone to generating field or table names that do not actually exist. Such errors often appear normal at the syntactic level and cannot be recognized by regular SQL parsers, but will directly lead to query failures during execution.

[0004] 2. General-purpose large language models lack an inherent understanding of enterprise-specific business rules. During SQL generation, large language models struggle to identify mandatory business filtering conditions and are prone to overlooking key constraints. For combinations of filtering conditions with logically mutually exclusive relationships, the model lacks conflict detection capabilities and may generate self-contradictory query logic. Furthermore, the model cannot accurately understand the enterprise-defined calculation methods for indicators and the dependencies between fields, leading to inconsistencies between aggregation logic and business definitions.

[0005] 3. Existing technical solutions generally adopt an end-to-end generation architecture. After the user inputs natural language, the large language model directly outputs the final SQL text. However, the entire generation process is invisible to the user. The user cannot know how the model interprets their needs, nor can they intervene and correct any misunderstandings in a timely manner. This black-box model leads to low user trust in the generated results. In actual business applications, manual review of each line of SQL is often required, which increases the cost of use and defeats the purpose of automated generation. Summary of the Invention

[0006] The purpose of this invention is to address the shortcomings of existing technologies by proposing a method and system for generating SQL with large-scale semantic constraints and dynamic validation.

[0007] To achieve the above objectives, the present invention adopts the following technical solution: A method for generating SQL with semantic constraints and dynamic validation for large-scale models includes the following steps: S1: Environment Preparation and SQL Generation Engine Deployment Deploy an SQL script generation engine in the target computing environment, and simultaneously collect and continuously maintain basic data information from the data environment; The basic data information includes, but is not limited to, basic SQL scripts, standardized definitions of data metrics, database schema definition language, data dictionary, and business rule documents; S2: Based on the basic data information collected and maintained in step S1, perform automated semantic extraction to construct a structured business semantic knowledge graph; Preferably, the specific process of constructing a business semantic knowledge graph is as follows: S21: Semantic extraction; Extract table entities, field attributes, and the physical relationships reflected by primary keys and foreign keys from the database schema definition language; Extract the business meaning, key business enumeration values, and semantic labels of fields from the data dictionary and indicator definitions; Extract the indicator entity, indicator calculation formula, indicator caliber constraints, and dependencies between indicators from the indicator definition; High-frequency query patterns and inter-table join paths are obtained from basic SQL scripts through pattern mining. S22: Semantic fusion; The extraction results of step S21 are merged, and the tables, fields, enumeration values, indicators and business rules are used as nodes, and physical relationships, semantic mapping and rule constraints are used as edges to construct a knowledge graph. S23: Extract semantic constraints; Based on the knowledge graph obtained in step S22, key semantic constraint rules are extracted from it, and the extracted key semantic constraint rules are transformed into structured Prompt constraint fragments. At the same time, a key semantic constraint rule library is constructed. S3: Semantic Augmentation Large Model Configuration Based on Orchestration Framework; Using the large model orchestration framework, the basic table structure information and the key semantic constraint rules in the business semantic knowledge graph constructed in step S2 are used together as the external knowledge base and Prompt constraint of the large language model. The basic table structure information includes table definition information and field information, and the key semantic constraint rules are used to limit the business semantic boundaries that the large language model must follow during the generation process.

[0008] S4: Initial JSON generation; Users input their data query requirements into the big language model in natural language through the front-end interactive interface. After receiving the data query requirements, the big language model parses the business intent of the data query requirements under the constraints of step S3 and outputs the semantic understanding results. Based on the semantic understanding results, the large language model retrieves and determines the candidate data table set and candidate field set from the business semantic knowledge graph constructed in step S2, and encapsulates the semantic understanding results, candidate data table set and candidate field set into initial JSON. The initial JSON content includes, but is not limited to, target metrics, dimensions, filtering conditions, and aggregation logic, wherein the data tables and fields involved in the target metrics, dimensions, filtering conditions, and aggregation logic are presented in the form of a set of candidate data tables or a set of candidate fields; At the same time, the initial JSON is rendered to the front-end interactive interface in real time. The front-end interactive interface presents the candidate data table set, candidate field set and query elements in a visual way, allowing users to confirm through parameter fine-tuning operations. Preferably, the user can fine-tune the parameters of the initial JSON through the front-end interactive interface; the parameter fine-tuning includes at least: using a checkbox to directly locate the target data table and target field from the candidate table set and candidate field set, adjusting the filtering threshold, adding or removing query dimensions, and correcting the enumeration value; Preferably, during the process of the user fine-tuning the parameters, the large language model performs real-time pre-validation on the initial JSON that the user is currently adjusting, performs syntax structure legality checks and basic field type matching verifications, and feeds back the pre-validation results to the front-end interactive interface in real time. S5: Dynamic multi-level verification; After the user confirms the parameter fine-tuning, the system generates a JSON request and submits it to the SQL script generation engine interface. Upon receiving the JSON request, the SQL script generation engine initiates dynamic multi-level validation. Level 1: Quick syntax and structure validation; quickly scans the JSON request for document integrity, existence of required fields, and basic structural conformity. If the validation fails, an error report pointing to the specific syntax node is returned directly. Level 2: Semantic and rule-based deep validation; Match the entities, fields, enumeration values, and functions referenced in the JSON request with the knowledge graph and key semantic constraint rule base constructed in step S2. Use the knowledge graph and key semantic constraint rule base to perform deep validation on the existence of fields, table association feasibility, business rule conflicts, and function legality of the JSON request. If any validation fails, return and record the corresponding semantic violation information. Level 3: For complex queries, in a secure sandbox environment or by utilizing the database's pre-compilation function, attempt to parse the generated SQL skeleton and capture obvious syntax errors or unresolved object reference errors. If any level of validation fails, intelligent correction suggestions will be automatically generated based on the key semantic constraint rule base and knowledge graph and returned to the front-end interactive interface. Users can adjust the JSON again according to the intelligent correction suggestions and resubmit it, forming an iterative correction closed loop until all validations pass. The proposed corrections should include at least the type of violation, the location of the violation, and a legally recommended correction scheme from the intelligent system. Preferably, the specific content of the second-level verification includes: field existence verification to confirm whether the fields specified in the JSON request actually exist in the corresponding table; table association feasibility verification to check whether the fields used in the table association conditions specified in the JSON request have defined foreign key relationships or allowed association rules in the knowledge graph, and to exclude unexecutable connection paths; business rule conflict verification to detect whether the JSON request violates rules or has logical contradictions by loading the semantic constraint rules injected in step S2, wherein the rule violation includes at least the JSON request omitting the mandatory filtering conditions in the semantic constraint rules, and the logical contradiction includes at least the semantic mutual exclusion between the filtering conditions specified in the JSON request; and function validity verification to check whether the aggregate functions and calculation expressions used in the JSON request conform to the data type of the target field and the function range supported by the system. S6: Generate SQL statements; After the JSON request passes all the validations in step S5, the SQL script generation engine uses the validated JSON parameters, combined with the built-in SQL template and dynamic construction logic, to generate an optimized target SQL script, and stores the target SQL script, along with its corresponding request JSON, generation timestamp, and validation pass identifier, into the database. S7: Closed-loop feedback and adaptive optimization; The system continuously collects user data on parameter fine-tuning operations in step S4, errors found during each level of verification in step S5, generated correction suggestions, and user adoption of correction suggestions, forming related feedback data. Based on the accumulated related feedback data, analyze it periodically or according to preset rules to identify high-frequency error patterns and newly emerging business rule constraints, and use the identification results as a knowledge update candidate set to automatically or prompt the administrator to update the knowledge graph and key semantic constraint rules in step S2. The user's adoption status refers to the content manually modified based on the intelligent correction suggestions; Preferably, the modified samples adopted by the user are used to adjust the large language model, so that the large language model outputs more accurate semantic understanding results and candidate tables and field recommendations in subsequent generation.

[0009] A SQL generation system for semantic constraints and dynamic validation of large models includes a knowledge graph construction module, a constraint injection module, a large model interaction module, a multi-level validation engine, an intelligent correction module, an SQL generation engine, and a feedback learning module. The knowledge graph construction module is used to extract table structure relationships and business rules from basic data information, construct a business semantic knowledge graph and extract key semantic constraint rules. The constraint injection module is used to inject the basic table structure information and the key semantic constraint rules into the prompt constraints of the large language model; The large model interaction module is used to receive natural language query requests and generate structured JSON query requests under the prompt constraints. The multi-level validation engine is used to perform multi-level dynamic validation on the JSON query request, including at least semantic and rule-based deep validation based on the knowledge graph.

[0010] The intelligent correction module is used to generate intelligent correction suggestions and return them to the front-end interactive interface when the verification fails. The SQL generation engine is used to generate the target SQL script based on the JSON request query after the verification is passed; The feedback learning module is used to collect user feedback on the intelligent correction suggestions and update the knowledge graph and business rule base based on the feedback information.

[0011] Compared with the prior art, the beneficial effects of the present invention are as follows: 1. This invention constructs a business semantic knowledge graph and injects key semantic constraint rules into the Prompt constraint of a large language model, so that the large language model is subject to clear business semantic boundary constraints during the generation process. At the same time, it uses knowledge graph retrieval to determine the candidate data table set and candidate field set, and presents them in the form of candidate recommendations for users to select and confirm. This suppresses the table field illusion problem of the large language model and significantly improves the accuracy of data entity positioning.

[0012] 2. This invention employs dynamic multi-level verification and utilizes a key semantic constraint rule base and knowledge graph for deep matching to perform multi-dimensional verification of the existence of fields, feasibility of table associations, business rule conflicts, and function validity in JSON requests. This enables the system to have business rule awareness capabilities and effectively avoids problems such as missing business rules and logical conflicts.

[0013] 3. This invention encapsulates the semantic understanding results of a large language model with a set of candidate data tables and candidate fields into an initial JSON and renders it to the front-end interactive interface in real time. This allows users to view the system's understanding of their needs and recommended candidate options in a visual way, and to confirm and correct them by fine-tuning parameters such as checking boxes, adjusting thresholds, and adding or removing dimensions. This significantly improves the interpretability and controllability of the generation process.

[0014] 4. This invention constructs a three-level dynamic verification mechanism encompassing grammatical structure, semantic rules, and pre-verification, achieving progressive and comprehensive verification. When verification fails, structured correction suggestions are automatically generated and returned to the front end, forming an iterative correction loop. Through a feedback learning module, user operations and verification data are collected to automatically identify high-frequency errors and new business rules, updating the knowledge graph and rule base. Thus, this invention achieves full coverage of verification dimensions and a complete feedback loop, enabling the system to continuously learn and adaptively evolve. Attached Figure Description

[0015] Figure 1 This is a flowchart illustrating the steps of a SQL generation method for semantic constraints and dynamic validation in a large model.

[0016] Figure 2 This is a system architecture diagram of a SQL generation system with large-scale model semantic constraints and dynamic validation. Detailed Implementation

[0017] To provide a further understanding of the purpose, structure, features, and functions of the present invention, detailed descriptions are provided below with reference to specific embodiments.

[0018] like Figure 2As shown, the present invention provides an SQL generation system for large model semantic constraints and dynamic verification, including a knowledge graph construction module, a constraint injection module, a large model interaction module, a multi-level verification engine, an intelligent correction module, an SQL generation engine, and a feedback learning module; The knowledge graph construction module is used to extract table structure relationships and business rules from basic data information, construct a business semantic knowledge graph, and extract key semantic constraint rules, while also building a key semantic constraint rule library. This module specifically includes a semantic extraction unit, a semantic fusion unit, and a semantic constraint extraction unit, which are used to perform the operations described in steps S21, S22, and S23, respectively.

[0019] The constraint injection module is used to inject the basic table structure information and the key semantic constraint rules into the prompt constraints of the large language model. Specifically, this module converts the key semantic constraint rules into structured Prompt fragments, and concatenates them into the system prompt words using RAG retrieval enhancement generation. At the same time, it registers the basic table structure information into the model in the form of a Function Calling Schema.

[0020] The large model interaction module receives natural language query requests and generates structured JSON query requests under the given constraints. This module communicates with the front-end interface, rendering the initial JSON to the front-end and receiving user parameter fine-tuning operations.

[0021] A multi-level validation engine is used to perform multi-level dynamic validation on the JSON query request, including at least semantic and rule-based in-depth validation based on the knowledge graph. This engine consists of three concatenated validators: a first-level JSON Schema validator, a second-level semantic rule validator, and a third-level SQL skeleton parser.

[0022] The intelligent correction module generates intelligent correction suggestions and returns them to the front-end interface when verification fails. This module retrieves correction templates from the rule base based on the violation type and automatically populates the corrective solution by combining it with legitimate node information from the knowledge graph.

[0023] The SQL generation engine is used to generate the target SQL script based on the JSON request query after successful verification. This engine has a built-in SQL template library and dynamic builder, supporting the automatic assembly of common query patterns such as multi-table joins, aggregation operations, and conditional filtering.

[0024] The feedback learning module collects user feedback on the intelligent correction suggestions and updates the knowledge graph and business rule base based on this feedback. This module periodically analyzes accumulated related feedback data, identifies high-frequency error patterns and newly emerging business rule constraints, and triggers knowledge graph updates automatically or upon administrator confirmation.

[0025] Please refer to the reference. Figure 1 This invention provides a method for generating SQL with semantic constraints and dynamic validation for large-scale models. Specifically, it includes the following steps: S1: Environment preparation and SQL generation engine deployment; Deploy a predefined SQL script generation engine in the target computing environment. This engine serves as the core execution component and provides calling interfaces to the outside world.

[0026] Simultaneously, basic data information is collected and continuously maintained from the enterprise data environment. This basic data information serves as the raw data for constructing knowledge graphs and constraint rules, and specifically includes the following categories: Basic SQL scripts: Historical SQL query scripts that have been manually verified, reflecting the query patterns and data usage habits in real business scenarios of enterprises; Standardized definition of data metrics: A unified description of a company's core business metrics, including metric name, business scope, calculation formula, and statistical granularity; Database schema definition language: defines the field structure, data types, primary key constraints, and foreign key constraints of each table in the database; Data dictionary: An explanatory document that explains the business meaning, value range, and code mapping relationship of tables and fields; Business rules document: A set of rules that describe the specific business logic and data compliance requirements of an enterprise.

[0027] The continuous maintenance of the aforementioned basic data information ensures that the subsequent knowledge graph and constraint rules can be dynamically updated as business evolves, thereby maintaining the long-term stability of the system's generated quality.

[0028] In this embodiment, the target computing environment refers to a server, cloud host, or containerized runtime environment that deploys and runs an SQL script generation engine, including but not limited to an enterprise's internal data center or cloud virtualization platform.

[0029] S2: Construct a business semantic knowledge graph; Based on the basic data information collected and maintained in step S1, the system automatically extracts semantic information from the basic data information, and then constructs a structured business semantic knowledge graph. This knowledge graph is used to carry the physical relationships of the enterprise database table structure and the semantic constraints in the business rules.

[0030] The construction process of a business semantic knowledge graph includes three sub-stages: semantic extraction, semantic fusion, and semantic constraint extraction. S21: Semantic extraction; In the semantic extraction stage, the system systematically extracts key semantic elements from multi-source data.

[0031] First, extract table entities, field attributes, and the physical relationships reflected by primary keys and foreign keys from the database schema definition language to form the basic physical connection skeleton between data tables.

[0032] Secondly, the business meaning, key business enumeration values, and semantic labels of fields are extracted from the data dictionary and indicator definitions. This enables the large language model to accurately understand the business meaning of data fields, thereby eliminating mismatch problems caused by similar field names.

[0033] Next, the indicator entities, indicator calculation formulas, indicator caliber constraints, and dependencies between indicators are extracted from the indicator definitions to ensure the consistency between the aggregation logic and business caliber during the SQL generation process.

[0034] Finally, high-frequency query patterns and inter-table join paths are obtained from the basic SQL scripts through pattern mining. Join combinations and query skeletons that frequently appear in actual business are identified. These high-frequency query patterns reflect the company's actual data usage experience and supplement the knowledge graph with implicit practical connections.

[0035] Pattern mining is used to identify frequently occurring query structure patterns and table associations in historical SQL scripts, providing empirical data support for the construction of association paths in knowledge graphs.

[0036] S22: Semantic fusion; In the semantic fusion stage, the system integrates the semantic extraction results, using tables, fields, enumeration values, metrics, and business rules as nodes, and physical relationships, semantic mappings, and rule constraints as edges, to construct a structured business semantic knowledge graph. This knowledge graph integrates the structured information and business experience originally scattered in the database schema definition language, data dictionary, metric definitions, and SQL scripts into an interconnected structured semantic network.

[0037] S23: Extract semantic constraints; During the semantic constraint extraction stage, the system extracts key semantic constraint rules from the constructed knowledge graph. The system then transforms these key semantic constraint rules into structured Prompt constraint fragments that can be directly parsed by large language models, and stores the key semantic constraint rules in a key semantic constraint rule library.

[0038] S3: Semantic Augmentation Large Model Configuration Based on Orchestration Framework; In this embodiment, the Dify large model orchestration framework is used to combine the basic table structure information and the key semantic constraint rules in the constructed business semantic knowledge graph as the external knowledge base and Prompt constraints of the large language model. The basic table structure information includes table definition information and field information, used to inform the large language model of the existing data tables in the current database and their field composition; the key semantic constraint rules are used to explicitly define the business semantic boundaries that the large language model must follow during the generation process.

[0039] Specifically, the injection of Prompt constraints can be achieved in ways including but not limited to: converting key semantic constraint rules into structured text fragments and concatenating them into system prompts before generating the large language model using RAG; or defining callable table structures and field lists through FunctionCalling Schema so that the model can only reference legal objects declared in the Schema when generating JSON.

[0040] When receiving user queries, the large language model's generation behavior is simultaneously constrained by the factual constraints of the data table structure and the business rules of the knowledge graph. This effectively suppresses the table / field illusion phenomenon and the risk of business logic violations that may occur during the generation process of the large language model, ensuring that the model's generation results are strictly limited to a compliant business semantic environment.

[0041] S4: Initial JSON generation; After configuration, users input their data query requirements into the large language model via the front-end interface in natural language. Upon receiving the query, the large language model, under the dual constraints of step S3, parses the business intent of the query and outputs a semantic understanding result. Then, based on this semantic understanding result, the large language model retrieves and determines a set of candidate data tables and candidate fields related to the current query intent from the business semantic knowledge graph. Finally, the semantic understanding result, the set of candidate data tables, and the set of candidate fields are encapsulated into an initial JSON.

[0042] The initial JSON content includes, but is not limited to, target metrics, dimensions, filtering conditions, and aggregation logic. The data tables and fields involved in the target metrics, dimensions, filtering conditions, and aggregation logic are presented as a set of candidate data tables or candidate fields, rather than being specified directly by the model as a single table or field. This preserves the user's space for review and correction, thus overcoming the problem of user intervention being difficult in end-to-end black-box generation solutions.

[0043] While the initial JSON is being generated, the system renders it to the front-end interactive interface in real time. The front-end interactive interface presents the candidate data table set, candidate field set, and various query elements in a visual way, mapping the structured fields in the JSON to editable graphical controls such as drop-down boxes and checkboxes on the interface, so that users can intuitively view the parsing results of the large model for their needs and confirm and adjust them.

[0044] Preferably, users can fine-tune the parameters of the initial JSON through a front-end interactive interface. Parameter fine-tuning includes at least: using checkboxes to directly locate and select the target data table and target field from the candidate table set and candidate field set, thereby avoiding field confusion that may be caused by manual input; adjusting the filtering threshold; adding or removing query dimensions; and correcting enumeration values.

[0045] Furthermore, during the user's parameter fine-tuning process, the large language model performs real-time pre-validation on the initial JSON that the user is currently adjusting. This pre-validation mainly verifies the legality of the syntax structure and the matching of basic field types, such as checking whether the JSON format is complete and closed, whether required fields are missing, and whether numeric fields have been mistakenly filled with strings. The pre-validation results are fed back to the front-end interactive interface in real time, thus guiding the user to correct low-level parameter errors before submitting the formal JSON request, reducing unnecessary iterations in subsequent deep validation stages, and improving the overall efficiency of SQL script generation.

[0046] In this invention, the large language model does not output deterministic instructions that directly point to a single data table or field. Instead, it outputs a set of candidate data tables and candidate fields retrieved from the knowledge graph. This candidate set is presented on the front-end interface as a recommendation list, allowing users to select target tables and fields by checking boxes or other means, rather than having the model specify them automatically.

[0047] S5: Dynamic multi-level verification; After the user confirms the parameter fine-tuning, the system generates a formal JSON request based on the final confirmed JSON content and submits this JSON request to the external interface of the SQL script generation engine. Upon receiving the JSON request, the SQL script generation engine initiates a multi-level, dynamic, multi-stage validation process to perform a comprehensive compliance verification of the JSON request.

[0048] The multi-level verification process is as follows: The first level is a rapid syntax and structure check. This level quickly scans the JSON request document for completeness, the presence of required fields, and basic structural compliance. If the JSON request document is found to be non-compliant with specifications, missing required fields, or having structural deficiencies, the check fails, and the SQL script generation engine directly returns an error report pinpointing the specific syntax node, clearly indicating the location and reason for the violation. The purpose of this level of check is to quickly intercept format-level errors, preventing them from proceeding to the subsequent semantic check stage.

[0049] The second level is semantic and rule-based in-depth verification; the entities, fields, enumeration values, functions referenced in the JSON request that passed the first level of verification are matched and verified one by one with the business semantic knowledge graph and key semantic constraint rule base constructed in step S2.

[0050] Specifically, this involves verifying the existence of fields and table affiliation, confirming that all corresponding tables mentioned in the JSON request actually exist, and that the affiliation between fields and tables is consistent with the definitions in the knowledge graph. This ensures the authenticity of field references and prevents invalid field names generated by the illusion of a large language model from entering the subsequent SQL generation process.

[0051] Table association feasibility verification checks whether the fields used in the table association conditions specified in the JSON request have defined foreign key relationships in the knowledge graph or allowed association rules verified by historical SQL patterns, in order to exclude unassociative connection paths between tables.

[0052] Business rule conflict detection utilizes key semantic constraint rules to detect whether JSON requests violate rules or contain logical contradictions. Rule violations include, at a minimum, the JSON request omitting mandatory filtering conditions required by the semantic constraint rules. Logical contradictions include, at a minimum, semantically mutually exclusive filtering conditions specified in the JSON request, in order to prevent queries that violate common business sense from being submitted and executed.

[0053] Function and expression validity checks verify whether the aggregate functions and calculation expressions used in the JSON request conform to the data type definition of the corresponding target field and whether they fall within the scope of functions supported by the system, ensuring the syntactic validity of SQL and the executableness of functions.

[0054] If any check fails, the corresponding semantic violation information is returned and recorded. The third level is SQL skeleton parsing and feasibility verification. For queries involving multiple table joins or complex nesting, the system attempts to parse the SQL skeleton generated after passing the first two levels of verification in a secure sandbox environment or by utilizing the database's pre-compilation function, and captures obvious syntax errors or unresolved object reference errors.

[0055] The third level of validation differs fundamentally from the first two levels: the first level only checks the JSON format and field existence, while the second level performs semantic rule matching based on a knowledge graph; neither of these involves the actual executability of the SQL statement. The third level, however, simulates execution in a security sandbox or utilizes database pre-compilation capabilities to parse the generated SQL skeleton. This allows it to capture syntax and execution-level errors caused by unreasonable table join paths, incorrect object references, etc., thus filling the blind spots that the first two levels of validation cannot reach.

[0056] After all three levels of validation have been completed, if any level fails, the system will summarize all failed items based on the key semantic constraint rule base and knowledge graph, generate a unified error report and corresponding intelligent correction suggestions, and return it to the front-end interface. Users can then adjust the JSON according to the intelligent correction suggestions and resubmit, forming an iterative correction loop until all validations pass.

[0057] The intelligent correction suggestion includes at least the violation type, the violation location, and a legal correction scheme intelligently recommended by the system based on the knowledge graph and rule base. The generation process of the intelligent correction suggestion is as follows: When a certain level of validation fails, the system retrieves the corresponding correction template from the key semantic constraint rule base based on the violation type, including non-existent fields, invalid association paths, and function mismatches. It then automatically fills in the specific correction scheme by combining this template with the information of legal nodes adjacent to the violation location in the knowledge graph. For example, when a field does not exist, the system retrieves the actual field list of the table to which the field belongs from the knowledge graph and recommends the closest legal field as a replacement.

[0058] S6: Generate SQL statements; Once the JSON request passes all three levels of validation in step S5, the validated JSON parameters are sent to the SQL script generation engine. The SQL script generation engine uses the validated JSON parameters, combined with its built-in SQL templates and dynamic construction logic, to generate an optimized target SQL script. The generated SQL script, along with its corresponding request JSON, generation timestamp, and validation pass identifier, is then persistently stored in the database.

[0059] S7: Closed-loop feedback and adaptive optimization; To support the system's continuous adaptive evolution, this step constructs a complete feedback loop from user interaction behavior to knowledge base updates.

[0060] First, the system continuously collects feedback data from multiple dimensions, specifically including: The user's parameter fine-tuning records on the front-end interactive interface, the types of errors found in each level of verification, the content of the generated correction suggestions, and the user's adoption status of the correction suggestions are all linked together to form interconnected feedback data. The user's adoption status refers to the manual modifications made based on the intelligent correction suggestions.

[0061] Secondly, the system will analyze the accumulated related feedback data periodically or according to preset rules to identify high-frequency error patterns and newly emerging business rule constraints, and use the identification results as a knowledge update candidate set to automatically or prompt the administrator to update the knowledge graph and key semantic constraint rules in step S2.

[0062] Among them, the high-frequency error pattern is the repeated occurrence of the same type of verification failure. This type of pattern often indicates that the constraint rules of the knowledge graph have blind spots or insufficient expression granularity. The newly emerging business rule constraints are filtering conditions or logical restrictions that have been manually added by users multiple times and have not yet been included in the system rule base. This type of constraint reflects the dynamic evolution of enterprise business rules.

[0063] Finally, the system uses the above identification results as a candidate set for knowledge updates. For candidate updates with high confidence and sufficient data support, the system can automatically write them into the knowledge graph and key semantic constraint rule base; for candidate updates that require careful confirmation, the system prompts the administrator to conduct manual review before deciding whether to include them. Through this mechanism, the knowledge graph and rule base are no longer static assets that remain unchanged after initialization, but rather a self-growing knowledge foundation that dynamically evolves and continuously improves with the continuous injection of user feedback during the system's operation, enabling the system to have higher accuracy and rule coverage in subsequent generation.

[0064] Preferably, the system uses the corrected samples adopted by the user to adjust the large language model, so that the large language model can output more accurate semantic understanding results and candidate table and field recommendations in subsequent generation.

[0065] The present invention has been described in the above-described embodiments; however, these embodiments are merely examples for implementing the present invention. It must be noted that the disclosed embodiments do not limit the scope of the present invention. Conversely, any modifications and refinements made without departing from the spirit and scope of the present invention are within the scope of patent protection of the present invention.

Claims

1. A method for generating SQL with semantic constraints and dynamic validation for large-scale models, characterized in that, Includes the following steps: S1: Environment preparation and SQL generation engine deployment; Deploy an SQL script generation engine in the target computing environment, and simultaneously collect and continuously maintain basic data information from the data environment; The basic data information includes, but is not limited to, basic SQL scripts, standardized definitions of data metrics, database schema definition language, data dictionary, and business rule documents; S2: Construct a knowledge graph; Based on the basic data information collected and maintained in step S1, automated semantic extraction is performed to construct a structured business semantic knowledge graph. S3: Semantic Augmentation Large Model Configuration Based on Orchestration Framework; Using the large model orchestration framework, the basic table structure information and the key semantic constraint rules in the business semantic knowledge graph constructed in step S2 are used together as the external knowledge base and Prompt constraint of the large language model. The basic table structure information includes table definition information and field information, and the key semantic constraint rules are used to limit the business semantic boundaries that the large language model must follow during the generation process. S4: Initial JSON generation; Users input their data query requirements into the large language model in natural language through the front-end interactive interface. After receiving the data query requirements, the large language model outputs an initial JSON under the constraints of step S3. The initial JSON content includes target metrics, dimensions, filtering conditions, and aggregation logic, wherein the data tables and fields involved in the target metrics, dimensions, filtering conditions, and aggregation logic are presented in the form of a set of candidate data tables or a set of candidate fields; S5: Dynamic multi-level verification; Once the user confirms the fine-tuning is complete, the system generates a JSON request and submits it to the SQL script generation engine interface. After receiving the JSON request, the SQL script generation engine initiates dynamic multi-level validation. The dynamic multi-level verification includes: Level 1: Quick syntax and structure validation, quickly scanning the JSON request for document integrity, existence of required fields, and basic structural conformity. Level 2: Semantic and rule-based deep validation, which matches the entities, fields, enumeration values, and functions referenced in the JSON request with the knowledge graph constructed in step S2, and performs deep validation on field existence, table association feasibility, business rule conflicts, and function validity; Level 3: For complex queries, in a secure sandbox environment or by utilizing the database's pre-compilation function, attempt to parse the generated SQL skeleton and capture syntax errors or unresolved object reference errors. If any level of verification fails, intelligent correction suggestions will be automatically generated based on the business rule base and knowledge graph and returned to the front-end interactive interface. Users can adjust the JSON again according to the intelligent correction suggestions and resubmit it, forming an iterative correction closed loop until all verifications pass. S6: Generate SQL statements; After the JSON request passes all the validations in step S6, the SQL script generation engine uses the validated JSON parameters, combined with the built-in SQL template and dynamic construction logic, to generate an optimized target SQL script, and stores the target SQL script, along with its corresponding request JSON, generation timestamp, and validation pass identifier, into the database. S7: Closed-loop feedback and adaptive optimization; Collect user feedback on the intelligent correction suggestions, and update the knowledge graph and business rule base based on the feedback.

2. The SQL generation method for large model semantic constraints and dynamic validation as described in claim 1, characterized in that, In step S2, the specific process of constructing the business semantic knowledge graph is as follows: S21: Semantic extraction; Extract table entities, field attributes, and the physical relationships reflected by primary keys and foreign keys from the database schema definition language; Extract the business meaning, key business enumeration values, and semantic labels of fields from the data dictionary and indicator definitions; Extract the indicator entity, indicator calculation formula, indicator caliber constraints, and dependencies between indicators from the indicator definition; S22: Semantic fusion; The extraction results of step S21 are merged, and the tables, fields, enumeration values, indicators and business rules are used as nodes, and physical relationships, semantic mapping and rule constraints are used as edges to construct a knowledge graph. S23: Extract semantic constraints; Based on the knowledge graph obtained in step S22, key semantic constraint rules are extracted from it, and the extracted key semantic constraint rules are transformed into structured Prompt constraint fragments.

3. The SQL generation method for large-scale model semantic constraints and dynamic validation as described in claim 1, characterized in that, In step S4, the large language model parses the natural language query requirements input by the user and outputs semantic understanding results; combined with the knowledge graph, it retrieves a set of candidate data tables and a set of candidate fields, and encapsulates the semantic understanding results, the set of candidate data tables, and the set of candidate fields into initial JSON.

4. The SQL generation method for large model semantic constraints and dynamic validation as described in claim 3, characterized in that, In step S4, after the large language model encapsulates the initial JSON, it will render the initial JSON to the front-end interactive interface for the user to confirm through parameter fine-tuning; Users can fine-tune the parameters of the initial JSON document through the front-end interactive interface. During the editing process, the initial JSON currently adjusted by the user is pre-validated in real time, and the syntax structure legality check and basic field type matching verification are performed. The pre-validation results are then fed back to the front-end interactive interface. The parameter fine-tuning includes at least adjusting the filtering threshold, adding or removing query dimensions, and correcting enumeration values.

5. The SQL generation method for large-scale model semantic constraints and dynamic validation as described in claim 1, characterized in that, In step S5, the dynamic multi-level verification is as follows: Level 1: Quick syntax and structure validation; quickly scans the JSON request for document integrity, existence of required fields, and basic structural conformity. If the validation fails, an error report pointing to the specific syntax node is returned directly. Level 2: Semantic and rule-based deep validation; Match the entities, fields, enumeration values, and functions referenced in the JSON request with the knowledge graph constructed in step S2. Use the knowledge graph to perform deep validation of the existence of fields, the feasibility of table associations, business rule conflicts, and the legality of functions in the JSON request. If any validation fails, return and record the corresponding semantic violation information. Level 3: For complex queries, in a secure sandbox environment or by utilizing the database's pre-compilation capabilities, attempt to parse the generated SQL skeleton and capture obvious syntax errors or unresolved object reference errors.

6. The SQL generation method for large model semantic constraints and dynamic validation as described in claim 5, characterized in that, The second level of verification includes: field existence verification to confirm whether the fields specified in the JSON request actually exist in the corresponding tables; table association feasibility verification to check whether the fields used in the table association conditions specified in the JSON request have defined foreign key relationships or allowed association rules in the knowledge graph, excluding unexecutable connection paths; business rule conflict verification to detect whether the JSON request violates rules or has logical contradictions by loading the semantic constraint rules injected in step S2. The rule violation includes at least the JSON request omitting the mandatory filtering conditions in the semantic constraint rules, and the logical contradiction includes at least the semantic mutual exclusion between the filtering conditions specified in the JSON request; and function validity verification to check whether the aggregate functions and calculation expressions used in the JSON request conform to the data type of the target field and the function range supported by the system.

7. The SQL generation method for large model semantic constraints and dynamic validation as described in claim 1, characterized in that, In step S7, the closed-loop feedback is: collecting the user's fine-tuning operations, the errors found in each level of verification in step S5 and the generated correction suggestions, and the user's adoption of the correction suggestions to form related feedback data; Periodically or according to preset rules, analyze the accumulated related feedback data to identify high-frequency error patterns and newly emerging business rule constraints, and use the identification results as a knowledge update candidate set to automatically or prompt the administrator to update the knowledge graph and key semantic constraint rules in step S2.

8. The SQL generation method for large model semantic constraints and dynamic validation as described in claim 7, characterized in that, In step S7, the corrected samples adopted by the user are used to fine-tune the large language model, so that the model can output more accurate semantic understanding results and candidate table and field recommendations in subsequent generation.

9. A SQL generation system for implementing the method according to any one of claims 1 to 8, characterized in that, It includes a knowledge graph construction module, a constraint injection module, a large model interaction module, a multi-level validation engine, an intelligent correction module, an SQL generation engine, and a feedback learning module; The knowledge graph construction module is used to extract table structure relationships and business rules from basic data information, construct a business semantic knowledge graph and extract key semantic constraint rules. The constraint injection module is used to inject the basic table structure information and the key semantic constraint rules into the prompt constraints of the large language model; The large model interaction module is used to receive natural language query requests and generate structured JSON query requests under the prompt constraints. The multi-level verification engine is used to perform multi-level dynamic verification on the JSON query request, including at least semantic and rule-based deep verification based on the knowledge graph. The intelligent correction module is used to generate intelligent correction suggestions and return them to the front-end interactive interface when the verification fails. The SQL generation engine is used to generate the target SQL script based on the JSON request query after the verification is passed; The feedback learning module is used to collect user feedback on the intelligent correction suggestions and update the knowledge graph and business rule base based on the feedback information.

Images