A method and system for generating and testing custom functions of a database

Abstract

The present invention discloses a method and system for generating and testing user-defined functions in a database. The method comprises the following steps: building a retrieval corpus containing function semantic fragments based on official documents of a database system; adopting a hierarchical strategy to build multi-layer prompt information based on the function semantic fragments, structured requirement information and example guidance templates; a function creation model adopts a streaming generation and syntax verification mechanism based on the multi-layer prompt information to generate a user-defined function creation statement; injecting the statement into a database system to execute a creation operation; if the execution is successful, performing correctness verification on the generated user-defined function; when the verification result is passed, recording metadata and a boundary value sample set of the user-defined function, and generating a corresponding user-defined function entity; constructing a database test statement group based on the metadata, boundary value sample set and target database structure of the user-defined function entity, and performing automated testing on the user-defined function entity.

Description

Technical Field

[0001] The present invention relates to the field of databases, and in particular to a method and system for generating and testing user-defined functions of a database. Background Art

[0002] As database applications become increasingly complex, user-defined functions (UDFs) have become an important means of extending database functionality. UDFs allow users to write custom logic based on specific business needs and embed it into database systems, enabling more flexible data processing and analysis capabilities. However, with the widespread use of UDFs, the complexity of generating, testing, and verifying UDFs in database systems has increased significantly, especially when complex logic is involved, multiple data sources are integrated, or external services are relied upon. Traditional database UDF generation and testing relies primarily on manual code writing, test case development, and static code analysis. These manually generated UDFs are prone to syntax errors, non-standard parameter definitions, and logical flaws, resulting in low development efficiency and difficulty ensuring quality. Furthermore, manually written test cases fail to cover all possible input scenarios, resulting in low testing efficiency and difficulty meeting the high-quality and fast delivery requirements of modern database systems.

[0003] Recent advances in natural language processing technology, particularly the development of large language models (LLMs) and retrieval-augmented generation (RAGs), have provided new approaches for the automated generation and testing of database user-defined functions (UDFs). LLMs, with their powerful semantic understanding and code generation capabilities, can assist in generating high-quality database UDF creation statements. RAGs, by integrating structured semantic fragments from external knowledge bases, improve the accuracy and executability of database UDF creation statement generation. They also automatically construct database test statements, enhancing test case coverage and relevance. However, existing technologies lack a mature solution that effectively integrates LLM and RAG mechanisms and integrates them throughout the automated database UDF generation and testing process. As a result, the database UDF generation and testing process still requires significant manual effort, which is not only inefficient but also prone to human error.

[0004] Therefore, it is urgent to propose a database custom function generation and testing method and system to solve the above problems. Summary of the Invention

[0005] This invention addresses the problems and shortcomings of existing technologies and provides a method and system for generating and testing user-defined functions in a database. These methods automatically generate and test user-defined functions, thereby automating the entire process from generation to verification. This invention significantly improves development efficiency, reduces labor costs, and ensures the stability and security of user-defined functions in database systems.

[0006] The technical solution adopted by the present invention to solve the above technical problems is to provide a method for generating and testing a database user-defined function, comprising the following steps:

[0007] A search corpus is constructed, which includes function semantic fragments based on the official documentation of the database system. The function semantic fragments are semantic fragments related to the custom function creation statement.

[0008] Prompt information construction uses a hierarchical strategy to build multi-layer prompt information based on function semantic fragments, structured requirement information, and example guidance templates;

[0009] Generate custom function creation statements. The function creation model uses a streaming generation and syntax verification mechanism based on the multi-layer prompt information to generate custom function creation statements that meet the syntax specifications of the database system.

[0010] Generate a user-defined function entity by injecting the user-defined function creation statement into the database system to execute the creation operation. If the execution is successful, verify the correctness of the generated user-defined function. When the result of the correctness verification is that the verification passes, record the metadata of the user-defined function and the boundary value sample set of the input parameters, and generate the corresponding user-defined function entity;

[0011] Automated testing of user-defined function entities: Based on the metadata, boundary value sample set and target database structure of the user-defined function entity, a database test statement group with context dependencies and executable nature is constructed to perform automated testing on the user-defined function entity.

[0012] Preferably, the retrieval corpus is used to provide the function semantic fragments required for calling the retrieval enhancement generation mechanism.

[0013] Preferably, the function creation model is a large language model with natural language processing capabilities, which is used to generate a custom function creation statement that meets the grammatical specifications of the database system based on the multi-layer prompt information.

[0014] Preferably, the retrieval corpus construction includes:

[0015] A keyword mapping algorithm is used to extract text blocks related to the custom function creation statement from the official documents of the database system, and the text blocks are semantically segmented to generate multiple semantic segments;

[0016] A text retrieval algorithm based on relevance scoring is used to filter out noisy semantic fragments and retain function semantic fragments related to user-defined function creation statements;

[0017] Semantic vector indexing technology is used to generate high-dimensional semantic vectors corresponding to function semantic fragments;

[0018] Based on the index structure constructed by the high-dimensional semantic vector, a retrieval corpus is generated that can be used by the retrieval enhancement generation mechanism.

[0019] Preferably, the tiered strategy includes:

[0020] Bottom-level prompts, including function semantic fragments, structured requirement information, and example guidance templates related to the custom function creation statement;

[0021] Mid-level prompts, generate examples by injecting multiple custom functions to create statements through the few-shot learning mechanism;

[0022] Top-level prompt, set the rule conditions used to constrain the generation of user-defined function creation statements.

[0023] Preferably, the streaming generation and syntax verification mechanism includes:

[0024] The function creation model generates a custom function creation statement draft in a streaming generation manner based on the multi-layer prompt information;

[0025] Injecting the user-defined function creation statement draft into the database system to execute the user-defined function creation operation;

[0026] When the execution result is creation success, the user-defined function creation statement draft is determined as the user-defined function creation statement;

[0027] When the execution result is creation failure, the function creation model is triggered to regenerate the custom function creation statement draft, and the number of regenerations does not exceed the preset maximum retry count threshold.

[0028] Preferably, the correctness verification includes function name conflict detection and executable verification;

[0029] The function name conflict detection includes:

[0030] Importing metadata of the user-defined function entity and the database system function into a symbol table manager, wherein the metadata includes a function name, an input parameter list, and a return value type;

[0031] Compare the function name of the user-defined function entity with the function name of the database system function to determine whether the function names are repeated;

[0032] If it is determined that there is no duplication, the result of the function name conflict detection is successful;

[0033] The executability verification includes:

[0034] Generate a set of boundary value samples of input parameters based on parameter definitions corresponding to user-defined function entities in the structured requirement information;

[0035] Constructing a function call statement of a user-defined function entity based on the boundary value sample set, and injecting the function call statement into a database system for execution;

[0036] Determine whether the function call statement is executed successfully;

[0037] If the execution is successful, the result of the executability verification is success;

[0038] When the result of the function name conflict detection and the result of the executable verification are both successful, the result of the correctness verification is verification passed.

[0039] Preferably, the user-defined function entity automated testing includes:

[0040] Based on the metadata of the user-defined function entity and the target database structure, the expression template is spliced ​​to construct a test-use custom function call statement template, and multiple groups of custom function call statements are generated in combination with the boundary value sample set;

[0041] Build a dependency graph based on the dependency information in the target database structure, and use the dependency graph traversal algorithm to generate auxiliary operation statements with contextual dependencies;

[0042] Generate query conditions based on the field constraint information of the target database structure;

[0043] The user-defined function call statement, the auxiliary operation statement and the query condition are combined to generate an executable database test statement group with context dependency.

[0044] Preferably, the method further comprises database crash detection, which is triggered after executing the automated test of the user-defined function entity;

[0045] The database crash detection includes heartbeat mechanism crash detection and multi-script adaptation crash detection. When the target database system supports heartbeat statements, the heartbeat mechanism crash detection is used to detect the operating status of the target database system. When the target database system does not support heartbeat statements, the multi-script adaptation crash detection is used to detect the operating status of the target database system.

[0046] The database crash detection is a black box detection method;

[0047] The heartbeat mechanism crash detection includes:

[0048] Set the default connection timeout threshold and query timeout threshold;

[0049] Execute heartbeat statements in a separate connection pool;

[0050] Dynamically adjust the connection timeout threshold and the query timeout threshold based on the historical response time of the target database system;

[0051] When a connection rejection, timeout, or operation error is detected, a triple verification process is triggered, which includes re-establishing the connection, changing the port and retrying, and calling the database service status interface;

[0052] If the verification results of re-establishing the connection, retrying to change the port, and calling the database service status interface all fail, the target database system is determined to be in a crashed state;

[0053] The multi-script adaptation crash detection includes:

[0054] Calling a preset backup detection script library, the backup detection script library includes basic connectivity scripts, protocol layer detection scripts and database specific detection scripts;

[0055] Running each detection script in the backup detection script library in an isolated container and restricting file access permissions;

[0056] When any script in the backup detection script library returns a non-zero status code, the output contains an error keyword, or the execution time exceeds a preset execution time threshold, the target database system is determined to be in a crash state.

[0057] The present invention also provides a database custom function generation and testing system, the system comprising:

[0058] A retrieval corpus construction module constructs a retrieval corpus containing function semantic fragments based on the official documents of the database system, wherein the function semantic fragments are semantic fragments related to the custom function creation statement;

[0059] The prompt information construction module adopts a hierarchical strategy to construct multi-layer prompt information based on function semantic fragments, structured requirement information and example guidance templates;

[0060] A custom function creation statement generation module, wherein the function creation model adopts a stream generation and syntax verification mechanism based on the multi-layer prompt information to generate a custom function creation statement that meets the syntax specifications of the database system;

[0061] A user-defined function entity generation module injects the user-defined function creation statement into the database system to execute the creation operation. If the execution is successful, the correctness of the generated user-defined function is verified. When the correctness verification result is verification passed, the metadata of the user-defined function and the boundary value sample set of the input parameters are recorded, and the corresponding user-defined function entity is generated;

[0062] The user-defined function entity automated testing module constructs a context-dependent and executable database test statement group based on the metadata, boundary value sample set, and target database structure of the user-defined function entity to perform automated testing on the user-defined function entity;

[0063] The database crash detection module triggers database crash detection after executing the automated test of the user-defined function entity. The database crash detection includes heartbeat mechanism crash detection and multi-script adaptation crash detection. When the target database system supports heartbeat statements, the heartbeat mechanism crash detection is used to detect the operating status of the target database system. When the target database system does not support heartbeat statements, the multi-script adaptation crash detection is used to detect the operating status of the target database system.

[0064] Compared with the prior art, the technical solution of the embodiment of the present invention has the following beneficial effects:

[0065] The present invention provides a method and system for generating and testing a user-defined function in a database. The method comprises: constructing a retrieval corpus based on official documents of a database system to construct a retrieval corpus containing function semantic fragments, wherein the function semantic fragments are semantic fragments related to user-defined function creation statements; constructing prompt information using a hierarchical strategy to construct multi-layer prompt information based on the function semantic fragments, structured requirement information, and example guidance templates; generating user-defined function creation statements, wherein a function creation model uses a streaming generation and syntax verification mechanism based on the multi-layer prompt information to generate a user-defined function creation statement that meets the syntax specifications of the database system; generating a user-defined function entity, wherein the user-defined function creation statement is injected into the database system to execute a creation operation. If the execution is successful, the generated user-defined function is correct. When the correctness verification result is that the verification is passed, the metadata of the user-defined function and a boundary value sample set of the input parameters are recorded to generate a corresponding user-defined function entity; and automatically testing the user-defined function entity, wherein a database test statement group with context dependencies and executable is constructed based on the metadata, boundary value sample set, and target database structure of the user-defined function entity, and the user-defined function entity is automatically tested. The present invention constructs a retrieval corpus containing semantic fragments of user-defined functions, and combines it with a multi-layer prompt information-driven function creation model to generate custom function creation statements that conform to the grammatical specifications of the database system, thereby realizing the automation process of user-defined functions from semantic understanding, code generation to grammatical verification.

[0066] Furthermore, by recording the metadata of user-defined function entities and a sample set of boundary values ​​of input parameters, and constructing a database test statement group with context dependencies based on the target database structure, it is possible to comprehensively test user-defined function entities under a variety of typical input scenarios, significantly improving the accuracy and coverage of user-defined function test verification, reducing the workload of manually writing test cases, and ensuring the stability and availability of user-defined functions in the database system.

[0067] Furthermore, database crash detection can promptly identify abnormalities in the database system's operational status after executing automated tests of user-defined function entities. Through heartbeat-based crash detection and multi-script-based crash detection, the system improves adaptability and fault response efficiency for diverse database systems, enhancing the stability and security of the overall testing process. BRIEF DESCRIPTION OF THE DRAWINGS

[0068] To more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention, not all embodiments. Those skilled in the art can also derive other drawings based on these drawings without inventive effort.

[0069] Figure 1 This is a flowchart of a method for generating and testing a database custom function according to a preferred embodiment of the present invention.

[0070] Figure 2 Flowchart of the retrieval corpus construction of the preferred embodiment of the present invention.

[0071] Figure 3 Flowchart of the streaming generation and syntax checking mechanism of a preferred embodiment of the present invention.

[0072] Figure 4 This is a flowchart of automated testing of user-defined function entities in a preferred embodiment of the present invention.

[0073] Figure 5 Schematic diagram of the architecture of a database custom function generation and testing system according to a preferred embodiment of the present invention. DETAILED DESCRIPTION

[0074] To make the objectives, technical solutions, and advantages of the embodiments of the present invention more clear, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts shall fall within the scope of protection of the present invention.

[0075] The technical solution of the present invention is described in detail below with reference to specific embodiments.

[0076] To address the challenges of existing technologies, the present invention provides a method and system for generating and testing user-defined functions in a database. These methods automatically generate and test user-defined functions, thereby automating the entire process from generation to verification. This method significantly improves development efficiency, reduces labor costs, and ensures the stability and security of user-defined functions in database systems.

[0077] See now Figure 1 The embodiment of the present invention provides a method for generating and testing a database user-defined function, comprising the following steps:

[0078] Step S101: constructing a search corpus, which constructs a search corpus containing function semantic fragments based on official documents of a database system. The function semantic fragments are semantic fragments related to user-defined function creation statements.

[0079] In this example, we first extract text blocks related to custom function creation statements from official database system documentation (e.g., official manuals for MySQL and PostgreSQL database systems) using a keyword mapping algorithm. These blocks contain function semantics, parameter descriptions, and example usage examples. These extracted text blocks are then divided into structured paragraphs and further refined into multiple structured semantic segments, such as function definitions, parameter declarations, and return type descriptions.

[0080] Then, a text retrieval algorithm based on relevance scoring (Best Matching 25, BM25) is used to filter out noisy semantic fragments and retain the function semantic fragments containing the CREATE FUNCTION syntax structure, parameter type description, and return value definition.

[0081] Next, semantic vector indexing technology (such as the Sentence-BERT model) is used to encode each function semantic fragment and generate a corresponding high-dimensional semantic vector (for example, 768 dimensions).

[0082] Finally, based on the index structure constructed by the high-dimensional semantic vector, a retrieval corpus is generated for use by the retrieval enhancement generation mechanism, wherein the retrieval corpus is used to provide the function semantic fragments required for the retrieval enhancement generation mechanism to call.

[0083] Step S102: constructing prompt information, using a layered strategy to construct multi-layer prompt information based on function semantic fragments, structured requirement information and example guidance templates.

[0084] In this step, a layered strategy is employed to construct multiple layers of prompt information, improving the accuracy of user-defined function generation. By introducing collaborative prompts that combine function semantic fragments, structured requirement information, and example guidance templates, the prompt information's contextual integrity and directionality are enhanced, reducing the rate of syntax errors and semantic deviations in generated custom function creation statements, and improving the alignment of generated results with user-defined function requirements.

[0085] The tiering strategy includes:

[0086] The underlying prompt includes the function semantic fragment related to the custom function creation statement, structured requirement information, and an example guidance template. The function semantic fragment related to the custom function creation statement is obtained from the Retrieval Enhanced Generation (RAG) mechanism. The structured requirement information is the result of structuring the custom function requirement dataset. The example guidance template is a natural language prompt template used to guide the function creation model to generate custom function creation statements, for example, "Generate a user-defined function based on the following parameter requirements: Parameter 1 is of type INT, named age, and requires a verification range of 18-60."

[0087] Mid-level prompts use a few-shot learning mechanism to inject multiple custom function creation statements as examples. This mechanism provides a small number of representative custom function creation statements as mid-level prompts during the multi-layer prompt construction process, typically 3-5 creation statements. These prompts help the function creation model learn the function syntax structure and parameter definition rules, thereby improving the rationality and accuracy of generated results. For example, a correct database statement (SQL statement) such as CREATE FUNCTION compute_avg(...) is first displayed, followed by a prompt to generate a new user-defined function entity.

[0088] This approach can effectively reduce the requirement for function creation models to rely on large sample training and improve the quality of generated results.

[0089] Top-level prompts set the rules and conditions used to constrain the generation of custom function creation statements. For example, prompts such as "The generated function must be compatible with MySQL 8.0 syntax" and "Must include NULL value processing logic" are displayed.

[0090] Step S103: generating a custom function creation statement. The function creation model adopts a stream generation and syntax verification mechanism based on the multi-layer prompt information to generate a custom function creation statement that meets the syntax specifications of the database system.

[0091] In this embodiment, the function creation model is a large language model with natural language processing capabilities, which is used to generate a custom function creation statement that meets the grammatical specifications of the database system based on the multi-layer prompt information.

[0092] The function creation model incorporates a retrieval-augmented generation mechanism (RAG), which captures function semantic snippets in real time and combines them with structured requirement information as prompts, significantly improving the accuracy and contextual adaptability of generated custom function creation statements. The LLM+RAG combination leverages the semantic understanding and code generation capabilities of existing general language models without requiring specialized training or fine-tuning of the function creation model. This approach enables the generation of database user-defined functions through high-quality, multi-layered prompts, offering low implementation costs, strong adaptability, and high deployment efficiency.

[0093] Step S104: Generate a user-defined function entity, inject the user-defined function creation statement into the database system to execute the creation operation. If the execution is successful, the correctness of the generated user-defined function is verified. When the result of the correctness verification is that the verification is passed, the metadata of the user-defined function and the boundary value sample set of the input parameters are recorded to generate the corresponding user-defined function entity.

[0094] In this embodiment, the correctness verification includes function name conflict detection and executable verification;

[0095] The function name conflict detection includes:

[0096] Import metadata of the user-defined function entity and database system function (such as basic functions within the database system such as date processing and numerical calculation) into the symbol table manager, wherein the metadata includes function name, input parameter list and return value type.

[0097] Compare the function name of the user-defined function entity with the function name of the database system function to determine whether the function names are repeated;

[0098] If it is determined that there is no duplication, the result of the function name conflict detection is successful;

[0099] The executability verification includes:

[0100] Generate a set of boundary value samples of input parameters based on parameter definitions corresponding to user-defined function entities in the structured requirement information;

[0101] Constructing a function call statement of a user-defined function entity based on the boundary value sample set, and injecting the function call statement (i.e., a SELECT statement) into a database system for execution;

[0102] Determine whether the function call statement is executed successfully;

[0103] If the execution is successful, the result of the executability verification is success;

[0104] When the result of the function name conflict detection and the result of the executable verification are both successful, the result of the correctness verification is verification passed.

[0105] Step S105: Automated testing of user-defined function entities. Based on the metadata, boundary value sample set and target database structure of the user-defined function entity, a database test statement group with context dependencies and executable nature is constructed to perform automated testing on the user-defined function entity.

[0106] In this embodiment, based on the metadata of the user-defined function entity, a set of boundary value samples, and the target database structure, a set of directly executable database test statements with contextual dependencies is automatically constructed, thereby enabling comprehensive verification of the user-defined function under a variety of typical input value scenarios. This effectively improves test coverage and execution efficiency, reduces the risk of omissions in manual testing, and ensures the stability and correctness of the user-defined function entity.

[0107] Step S106: Database crash detection. After executing the automated test of the user-defined function entity, database crash detection is triggered. The database crash detection includes heartbeat mechanism crash detection and multi-script adaptation crash detection. When the target database system supports heartbeat statements, the heartbeat mechanism crash detection is used to detect the operating status of the target database system. When the target database system does not support heartbeat statements, the multi-script adaptation crash detection is used to detect the operating status of the target database system.

[0108] In this embodiment, by introducing database crash detection after automated testing of user-defined function entities, database operational anomalies caused by abnormal execution of user-defined functions can be promptly detected. The two detection mechanisms, heartbeat mechanism crash detection and multi-script adaptation crash detection, enable adaptive fault monitoring for different types of database systems, effectively improving the stability and robustness of the testing process.

[0109] The database crash detection is a black box detection method.

[0110] The heartbeat mechanism crash detection includes:

[0111] Set the default connection timeout threshold and query timeout threshold. The connection timeout threshold and query timeout threshold are controlled by the connect_timeout and read_timeout parameters respectively. The default connection timeout threshold and query timeout threshold are 3 seconds and 5 seconds respectively.

[0112] Execute heartbeat statements (such as SELECT 1;) in a separate connection pool to avoid heartbeat mechanism crash detection caused by user-defined function entity automation testing blocking.

[0113] Dynamically adjust the connection and query timeout thresholds based on the target database system's historical response time. For example, if the target database system's query response time exceeds 1.5 seconds three times in a row, the system automatically increases both the current connection and query timeout thresholds by 20% to accommodate performance fluctuations and reduce the possibility of falsely diagnosing a target database system crash.

[0114] When a connection rejection (ConnectionRefusedError), timeout (TimeoutError) or operation error (OperationalError) is detected, a triple verification process is triggered. The triple verification process includes re-establishing the connection, changing the port and retrying, and calling the database service status interface (such as MySQL's SHOW PROCESSLIST).

[0115] If the verification results of re-establishing the connection, retrying by changing the port, and calling the database service status interface all fail, the target database system is determined to be in a crashed state.

[0116] The multi-script adaptation crash detection includes:

[0117] Invoke a pre-defined backup detection script library, which includes basic connectivity scripts (such as attempting to establish a connection through an ODBC / JDBC driver), protocol layer detection scripts (such as sending a handshake packet to MySQL port 3306 and parsing the response message to determine the service status), and database-specific detection scripts (such as Oracle's tnsping tool and PostgreSQL's pg_isready command).

[0118] Running each detection script in the backup detection script library in an isolated container and restricting file access permissions;

[0119] If any script in the backup detection script library returns a non-zero status code, contains error keywords (such as "down" or "error") in its output, or takes longer than a preset execution time threshold (such as 10 seconds), the target database system is determined to be in a crashed state.

[0120] See now Figure 2 ,The retrieval corpus construction includes the following steps:

[0121] Step S1011: extracting text blocks related to user-defined function creation statements from official documents of the database system using a keyword mapping algorithm, and performing semantic segmentation on the text blocks to generate multiple semantic segments.

[0122] In this example, a keyword mapping algorithm is first used to extract text blocks related to custom function creation statements, including function semantics, parameter descriptions, and example usage, from the official documentation of the database system (e.g., the official manuals for the MySQL and PostgreSQL database systems). The keyword mapping algorithm performs a preliminary screening of natural language passages in the official documentation using a preset set of keywords.

[0123] Then, the extracted text blocks are divided into structured paragraphs using a rule-based parsing method based on format features and keyword identification, and further refined into multiple structured semantic fragments such as function definition, parameter declaration, return type description, etc.

[0124] Step S1012: Using a text retrieval algorithm based on relevance scoring to filter out noisy semantic segments, retaining function semantic segments related to the user-defined function creation statement.

[0125] In this embodiment, the text retrieval algorithm based on relevance scoring (BM25) is a type of probabilistic retrieval model that can score text relevance based on factors such as word frequency and document length.

[0126] The function semantic fragment related to the user-defined function creation statement includes the key paragraphs of the CREATE FUNCTION syntax structure, parameter type description, and return value definition.

[0127] Step S1013: Generate a high-dimensional semantic vector corresponding to the function semantic segment using semantic vector indexing technology.

[0128] In this embodiment, semantic vector indexing technology uses a pre-trained language model (such as the Sentence-BERT model) with contextual semantic encoding capabilities to encode each semantic segment and generate a corresponding fixed-length high-dimensional semantic vector (for example, 768 dimensions). The generated high-dimensional semantic vector is used to support semantic similarity matching and vector retrieval operations in the Retrieval-Augmented Generation (RAG) mechanism.

[0129] The pre-trained language model (Sentence-BERT model) used in this embodiment is an open source pre-trained model trained on general corpus. It is directly used for the embedding encoding task of semantic fragments without the need for local retraining.

[0130] Step S1014: Based on the index structure constructed by the high-dimensional semantic vector, a retrieval corpus that can be used by the retrieval enhancement generation mechanism is generated.

[0131] In this embodiment, the index structure constructs a semantic search index based on high-dimensional semantic vectors, supporting the search-enhanced generation mechanism to efficiently retrieve function semantic snippets related to user-defined function creation statements from official documents. When generating user-defined function creation statements, the function creation model can use this index structure to retrieve semantic snippets that match structured requirement information as underlying prompt content, improving the contextual relevance and grammatical accuracy of the generated results.

[0132] See now Figure 3 ,The flow generation and syntax verification mechanism includes the following steps:

[0133] Step S1031: The function creation model generates a custom function creation statement draft in a streaming generation manner based on the multi-layer prompt information.

[0134] Step S1032: injecting the user-defined function creation statement draft into the database system to execute the user-defined function creation operation.

[0135] Step S1033: When the execution result is creation success, the user-defined function creation statement draft is determined as the user-defined function creation statement.

[0136] Step S1034: When the execution result is creation failure, the function creation model is triggered to regenerate the custom function creation statement draft, and the number of regenerations does not exceed the preset maximum retry count threshold.

[0137] In this embodiment, the streaming generation method involves the function creation model gradually generating draft custom function creation statements based on multiple layers of prompt information. A syntax verification mechanism verifies the syntactical validity and creation effectiveness of the generated custom function creation statement draft by injecting it into the database system for actual execution. Based on the execution results, it controls whether to trigger the regeneration of legal statements. This mechanism effectively avoids syntactic errors or illegal function definitions, improves the stability and applicability of automatic generation, and reduces manual verification costs.

[0138] See now Figure 4 , the automated testing of user-defined function entities includes the following steps:

[0139] Step S1051: Based on the metadata of the user-defined function entity and the target database structure, the expression templates are spliced ​​to construct a test-use custom function call statement template, and multiple groups of custom function call statements are generated in combination with the boundary value sample set.

[0140] In this embodiment, expression templates are first randomly called, and different expression templates are spliced ​​into a complete custom function call statement template, which abstractly represents various queries, calculations, conditional judgments, functions, operators, etc. in the database. For example, for the user-defined function calculate_discount(age INT), its custom function call statement template can be expressed as SELECT calculate_discount({age});, where {age} is the input parameter placeholder to be filled, which will be dynamically replaced with the specific input value through the boundary value sample set.

[0141] Then, combining the boundary value sample set, we generate multiple sets of custom function call statements to test the execution behavior of the user-defined function under different typical input conditions. Input parameter values ​​include maximum, minimum, and out-of-bounds values. For example, if the input parameter is the age field and its value range is 18 to 60, we can generate input value combinations such as 18, 60, 17, and 61. When the input parameter placeholder in the custom function call statement template is replaced with one of the input values, the custom function call statement example is: SELECT calculate_discount(18).

[0142] Step S1052: construct a dependency graph based on the dependency information in the target database structure, and use a dependency graph traversal algorithm to generate auxiliary operation statements with context dependencies.

[0143] In this embodiment, a corresponding dependency graph is constructed by identifying dependency information such as foreign key relationships and inter-table reference relationships in the target database structure. Based on the dependency graph, a depth-first or breadth-first dependency graph traversal algorithm is then used to automatically generate auxiliary operation statements that conform to the dependency relationships. For example, if a user-defined function involves a query on Table A, and a field in Table A is a foreign key reference to Table B, then before generating the custom function call statement, a test database statement for inserting data into Table B is automatically generated to ensure the integrity of the foreign key constraint and ensure that the calling context of the user-defined function is correct.

[0144] Step S1053: Generate query conditions based on the field constraint information of the target database structure.

[0145] In this embodiment, the target database structure includes field constraint information, including field type, nullability, default value settings, uniqueness constraints, and whether the field is an indexed field. Based on this field constraint information, a matching query statement structure is automatically constructed. For example, if a field is set to non-null and has a unique index, the WHERE clause will be generated based on this field first, ensuring that the generated database test statement complies with the index field access specifications.

[0146] Step S1054: combining the user-defined function call statement, the auxiliary operation statement and the query condition to generate an executable database test statement group with context dependency.

[0147] In this embodiment, custom function call statements, auxiliary operation statements, and query conditions are combined to form a complete database test statement group. The database test statement group is executed sequentially according to contextual dependencies to ensure the integrity and accuracy of the test environment. To improve testing efficiency, query conditions are preferentially generated based on fields marked as indexes in the target database structure. This improves the efficiency of database test statement execution and assists in evaluating the performance of user-defined functions in typical index access scenarios.

[0148] See now Figure 5 The embodiment of the present invention further provides a database custom function generation and testing system 500, the system comprising:

[0149] A retrieval corpus construction module 501 constructs a retrieval corpus containing function semantic fragments based on official documents of the database system, wherein the function semantic fragments are semantic fragments related to user-defined function creation statements;

[0150] The prompt information construction module 502 adopts a layered strategy to construct multi-layer prompt information based on function semantic fragments, structured requirement information and example guidance templates;

[0151] A custom function creation statement generation module 503, in which a function creation model adopts a stream generation and syntax verification mechanism based on the multi-layer prompt information to generate a custom function creation statement that meets the syntax specifications of the database system;

[0152] The user-defined function entity generation module 504 injects the user-defined function creation statement into the database system to execute the creation operation. If the execution is successful, the correctness of the generated user-defined function is verified. If the correctness verification result is verified to be passed, the metadata of the user-defined function and the boundary value sample set of the input parameters are recorded, and the corresponding user-defined function entity is generated;

[0153] The user-defined function entity automated testing module 505 constructs a context-dependent and executable database test statement group based on the metadata of the user-defined function entity, the boundary value sample set, and the target database structure, and performs automated testing on the user-defined function entity;

[0154] The database crash detection module 506 triggers database crash detection after executing the automated test of the user-defined function entity. The database crash detection includes heartbeat mechanism crash detection and multi-script adaptation crash detection. When the target database system supports heartbeat statements, the heartbeat mechanism crash detection is used to detect the operating status of the target database system. When the target database system does not support heartbeat statements, the multi-script adaptation crash detection is used to detect the operating status of the target database system.

[0155] In summary, the present invention provides a method and system for generating and testing a custom function in a database, the method comprising: constructing a retrieval corpus, based on the official documents of the database system, a retrieval corpus containing function semantic fragments, wherein the function semantic fragments are semantic fragments related to custom function creation statements; constructing prompt information, using a hierarchical strategy to construct multi-layer prompt information based on function semantic fragments, structured requirement information, and example guidance templates; generating custom function creation statements, wherein the function creation model uses a streaming generation and syntax verification mechanism based on the multi-layer prompt information to generate a custom function creation statement that meets the syntax specifications of the database system; generating a user-defined function entity, injecting the custom function creation statement into the database system to perform a creation operation, and if the execution is successful, performing correctness verification on the generated user-defined function. When the result of the correctness verification is verification passed, recording the metadata of the user-defined function and a set of boundary value samples of the input parameters, and generating a corresponding user-defined function entity; and automatically testing the user-defined function entity, constructing an executable database test statement group with context dependencies based on the metadata, boundary value sample set, and target database structure of the user-defined function entity, and performing automated testing on the user-defined function entity. The present invention constructs a retrieval corpus containing semantic fragments of user-defined functions, and combines it with a multi-layer prompt information-driven function creation model to generate custom function creation statements that conform to the grammatical specifications of the database system, thereby realizing the automation process of user-defined functions from semantic understanding, code generation to grammatical verification.

[0156] Furthermore, by recording the metadata of user-defined function entities and a sample set of boundary values ​​of input parameters, and constructing a database test statement group with context dependencies based on the target database structure, it is possible to comprehensively test user-defined function entities under a variety of typical input scenarios, significantly improving the accuracy and coverage of user-defined function test verification, reducing the workload of manually writing test cases, and ensuring the stability and availability of user-defined functions in the database system.

[0157] Furthermore, database crash detection can promptly identify abnormalities in the database system's operational status after executing automated tests of user-defined function entities. Through heartbeat-based crash detection and multi-script-based crash detection, the system improves adaptability and fault response efficiency for diverse database systems, enhancing the stability and security of the overall testing process.

[0158] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the above embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the above embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements 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 method for generating and testing a database custom function, characterized in that: include: A search corpus is constructed, which includes function semantic fragments based on the official documentation of the database system. The function semantic fragments are semantic fragments related to the custom function creation statement. Prompt information construction uses a hierarchical strategy to build multi-layer prompt information based on function semantic fragments, structured requirement information, and example guidance templates; Generate custom function creation statements. The function creation model uses a streaming generation and syntax verification mechanism based on the multi-layer prompt information to generate custom function creation statements that meet the syntax specifications of the database system. Generate a user-defined function entity by injecting the user-defined function creation statement into the database system to execute the creation operation. If the execution is successful, verify the correctness of the generated user-defined function. When the result of the correctness verification is that the verification passes, record the metadata of the user-defined function and the boundary value sample set of the input parameters, and generate the corresponding user-defined function entity; Automated testing of user-defined function entities: Based on the metadata, boundary value sample set, and target database structure of the user-defined function entity, a database test statement group with context dependencies and executable content is constructed to perform automated testing on the user-defined function entity. The retrieval corpus is used to provide the function semantic fragments required for the retrieval enhancement generation mechanism to call; The function creation model is a large language model with natural language processing capabilities, which is used to generate a custom function creation statement that meets the database system grammar specification based on the multi-layer prompt information; The tiering strategy includes: Bottom-level prompts, including function semantic fragments, structured requirement information, and example guidance templates related to the custom function creation statement; Mid-level prompts, generate examples by injecting multiple custom functions to create statements through the few-shot learning mechanism; Top-level prompts, set the rule conditions used to constrain the generation of custom function creation statements; The streaming generation and syntax verification mechanism includes: The function creation model generates a custom function creation statement draft in a streaming generation manner based on the multi-layer prompt information; Injecting the user-defined function creation statement draft into the database system to execute the user-defined function creation operation; When the execution result is creation success, the user-defined function creation statement draft is determined as the user-defined function creation statement; When the execution result is creation failure, the function creation model is triggered to regenerate the custom function creation statement draft, and the number of regenerations does not exceed the preset maximum retry count threshold.

2. The method for generating and testing a database user-defined function according to claim 1, wherein: The retrieval corpus construction includes: A keyword mapping algorithm is used to extract text blocks related to the custom function creation statement from the official documents of the database system, and the text blocks are semantically segmented to generate multiple semantic segments; A text retrieval algorithm based on relevance scoring is used to filter out noisy semantic fragments and retain function semantic fragments related to user-defined function creation statements; Semantic vector indexing technology is used to generate high-dimensional semantic vectors corresponding to function semantic fragments; Based on the index structure constructed by the high-dimensional semantic vector, a retrieval corpus is generated that can be used by the retrieval enhancement generation mechanism.

3. The method for generating and testing a database user-defined function according to claim 1, wherein: The correctness verification includes function name conflict detection and executable verification; The function name conflict detection includes: Importing metadata of the user-defined function entity and the database system function into a symbol table manager, wherein the metadata includes a function name, an input parameter list, and a return value type; Compare the function name of the user-defined function entity with the function name of the database system function to determine whether the function names are repeated; If it is determined that there is no duplication, the result of the function name conflict detection is successful; The executability verification includes: Generate a set of boundary value samples of input parameters based on parameter definitions corresponding to user-defined function entities in the structured requirement information; Constructing a function call statement of a user-defined function entity based on the boundary value sample set, and injecting the function call statement into a database system for execution; Determine whether the function call statement is executed successfully; If the execution is successful, the result of the executability verification is success; When the result of the function name conflict detection and the result of the executable verification are both successful, the result of the correctness verification is verification passed.

4. The method for generating and testing a database user-defined function according to claim 1, wherein: The user-defined function entity automated testing includes: Based on the metadata of the user-defined function entity and the target database structure, the expression template is spliced ​​to construct a test-use custom function call statement template, and multiple groups of custom function call statements are generated in combination with the boundary value sample set; Build a dependency graph based on the dependency information in the target database structure, and use the dependency graph traversal algorithm to generate auxiliary operation statements with contextual dependencies; Generate query conditions based on the field constraint information of the target database structure; The user-defined function call statement, the auxiliary operation statement and the query condition are combined to generate an executable database test statement group with context dependency.

5. The method for generating and testing a database user-defined function according to claim 1, wherein: The method further includes database crash detection, which is triggered after executing the user-defined function entity automated test; The database crash detection includes heartbeat mechanism crash detection and multi-script adaptation crash detection. When the target database system supports heartbeat statements, the heartbeat mechanism crash detection is used to detect the operating status of the target database system. When the target database system does not support heartbeat statements, the multi-script adaptation crash detection is used to detect the operating status of the target database system. The database crash detection is a black box detection method; The heartbeat mechanism crash detection includes: Set the default connection timeout threshold and query timeout threshold; Execute heartbeat statements in a separate connection pool; Dynamically adjust the connection timeout threshold and the query timeout threshold based on the historical response time of the target database system; When a connection rejection, timeout, or operation error is detected, a triple verification process is triggered, which includes re-establishing the connection, changing the port and retrying, and calling the database service status interface; If the verification results of re-establishing the connection, retrying to change the port, and calling the database service status interface all fail, the target database system is determined to be in a crashed state; The multi-script adaptation crash detection includes: Calling a preset backup detection script library, the backup detection script library includes basic connectivity scripts, protocol layer detection scripts and database specific detection scripts; Running each detection script in the backup detection script library in an isolated container and restricting file access permissions; When any script in the backup detection script library returns a non-zero status code, the output contains an error keyword, or the execution time exceeds a preset execution time threshold, the target database system is determined to be in a crash state.

6. A database custom function generation and testing system, characterized in that: include: A retrieval corpus construction module constructs a retrieval corpus containing function semantic fragments based on the official documents of the database system, wherein the function semantic fragments are semantic fragments related to the custom function creation statement; The prompt information construction module adopts a hierarchical strategy to construct multi-layer prompt information based on function semantic fragments, structured requirement information and example guidance templates; A custom function creation statement generation module, wherein the function creation model adopts a stream generation and syntax verification mechanism based on the multi-layer prompt information to generate a custom function creation statement that meets the syntax specifications of the database system; A user-defined function entity generation module injects the user-defined function creation statement into the database system to execute the creation operation. If the execution is successful, the correctness of the generated user-defined function is verified. When the correctness verification result is verification passed, the metadata of the user-defined function and the boundary value sample set of the input parameters are recorded, and the corresponding user-defined function entity is generated; The user-defined function entity automated testing module constructs a context-dependent and executable database test statement group based on the metadata, boundary value sample set, and target database structure of the user-defined function entity to perform automated testing on the user-defined function entity; a database crash detection module, which triggers database crash detection after executing the automated test of the user-defined function entity, wherein the database crash detection includes heartbeat mechanism crash detection and multi-script adaptation crash detection. When the target database system supports heartbeat statements, the heartbeat mechanism crash detection is used to detect the operating status of the target database system; when the target database system does not support heartbeat statements, the multi-script adaptation crash detection is used to detect the operating status of the target database system; The retrieval corpus is used to provide the function semantic fragments required for the retrieval enhancement generation mechanism to call; The function creation model is a large language model with natural language processing capabilities, which is used to generate a custom function creation statement that meets the database system grammar specification based on the multi-layer prompt information; The tiering strategy includes: Bottom-level prompts, including function semantic fragments, structured requirement information, and example guidance templates related to the custom function creation statement; Mid-level prompts, generate examples by injecting multiple custom functions to create statements through the few-shot learning mechanism; Top-level prompts, set the rule conditions used to constrain the generation of custom function creation statements; The streaming generation and syntax verification mechanism includes: The function creation model generates a custom function creation statement draft in a streaming generation manner based on the multi-layer prompt information; Injecting the user-defined function creation statement draft into the database system to execute the user-defined function creation operation; When the execution result is creation success, the user-defined function creation statement draft is determined as the user-defined function creation statement; When the execution result is creation failure, the function creation model is triggered to regenerate the custom function creation statement draft, and the number of regenerations does not exceed the preset maximum retry count threshold.

Images