Database table generation method based on large model

Abstract

The invention relates to a database table generation method based on a large model, belongs to the technical field of database table generation, and solves the problems that a database table generation method in the prior art is tedious in creation, time-consuming and high in misoperation risk. Comprising the following steps: creating a dialogue based on a database table, and generating the database table by a large model to create an SQL statement; creating SQL statements for the database table, analyzing the SQL statements, updating a database logic table, and verifying and modifying the updated database logic table to obtain a modified database logic table; and based on the modified database logic table, updating the current database table to create an SQL statement, and further creating a database table entity. The automatic creation of the database table is realized.

Description

Technical Field

[0001] The present invention relates to the technical field of database table generation, and in particular to a database table generation method based on a large model. Background Art

[0002] As software development technology matures, low-code development platforms based on the WEB side have emerged, greatly improving development efficiency and business response speed. In these platforms, visual generation of database tables is an indispensable basic usage step. Traditional database design usually requires the participation of professional database administrators or developers, while low-code platforms simplify this process so that business personnel can also participate. In low-code development platforms, users first design the structure of database tables based on their understanding of the business. This is usually achieved through an intuitive graphical interface, where users can easily add tables, fields, and set relationships and constraints by dragging, clicking, and other operations. Such a design process not only lowers the technical threshold, but also enables business needs and database design to be better connected. Users physicalize the designed table structure into the actual database by executing the database generation program. This step is usually automated, and the platform will generate and execute the corresponding SQL script based on the user's configuration to create the required database table structure. In this way, users can quickly build a database that meets business needs without writing complex SQL code.

[0003] The generation of traditional database tables is usually based on the user's understanding of the business. However, due to the different depths of understanding of the business and software system by different personnel, this method often leads to the designed database structure not fully matching the actual usage scenario. This mismatch may bring a series of problems, such as data redundancy, poor query performance, and data consistency issues. These problems need to be solved through multiple modifications and adjustments, which increases the development cycle and workload. Although the current low-code development platform based on the WEB end allows users to independently design the structure of the database table based on their understanding of the business, which lowers the technical threshold, the platform will generate and execute the corresponding SQL script according to the user's configuration to create the required database table structure. Without writing complex SQL code, a database that meets business needs can be quickly built. However, it is still necessary to configure each field, relationship, and constraint one by one, which is not only time-consuming, but also prone to misoperation in the process. For example, problems such as incorrect field type selection, improper index setting, or incorrect relationship definition may lead to unstable system operation or data errors in the later stage. In addition, database users are sometimes not professional database administrators and lack a deep understanding and experience of database design.

[0004] Therefore, the existing database table generation method has the problems of being cumbersome and time-consuming to create and having a high risk of misoperation, which seriously slows down the development progress and may also affect the stability and performance of the final system, and urgently needs to be improved and optimized. Summary of the invention

[0005] In view of the above analysis, an embodiment of the present invention aims to provide a database table generation method based on a large model, so as to solve the problems that the existing database table generation method has the characteristics of cumbersome creation, time-consuming and high risk of misoperation.

[0006] On the one hand, an embodiment of the present invention provides a method for generating a database table based on a large model, comprising the following steps:

[0007] Based on the database table creation dialogue, the large model generates the database table creation SQL statement;

[0008] After parsing the SQL statement for creating the database table, updating the database logic table, verifying and modifying the updated database logic table, and obtaining a modified database logic table;

[0009] Based on the modified database logic table, the current database table creation SQL statement is updated to further create the database table entity.

[0010] Furthermore, based on the database table creation dialogue, the large model generates a database table creation SQL statement, including:

[0011] Sending preconditions to the big model to establish a big model dialogue scenario; wherein the preconditions are to set the big model to create SQL statements for the database in response to the content of the dialogue;

[0012] Conduct a database table creation dialogue with the large model to generate an initial database table creation SQL statement;

[0013] The initial database table creation SQL statement is verified. If the verification passes, it is used as the database table creation SQL statement; otherwise, the database table creation dialogue is re-conducted with the large model.

[0014] Furthermore, the large model verifies the initial database table creation SQL statement in the following manner:

[0015] Check the SQL statement for creating the initial database table to see if it contains the necessary SQL information. If so,

[0016] Performing a syntax check on the SQL statement for creating the initial database table to determine whether the SQL statement is legal, if so, the check passes;

[0017] The necessary SQL information includes the CREATE TABLE keyword, table name, table description information, field name, field type, field length, field precision, and field description information.

[0018] Furthermore, the database logic table includes metadata information of each database table; wherein the metadata information includes table name, comment, field name, field type, and field comment of each database table; the database logic table is updated in the following manner:

[0019] Build grammar rule files and lexical rule files based on the grammar rules and lexical rules of SQL table creation statements;

[0020] After the SQL statement for creating the current database table is segmented based on the lexical rule file, an abstract syntax tree is obtained based on the grammatical rule file;

[0021] The abstract syntax tree is traversed to obtain metadata information of the database table, and then the database logic table is updated based on the metadata information of the database table.

[0022] Furthermore, the database logic table also includes table setting information of each database table; wherein the table setting information includes the master-slave table status; the updated database logic table is verified and modified in the following manner:

[0023] Generate JSON data from the latest metadata information of the database table to be generated in the updated database logic table;

[0024] Generate a custom form page based on JSON data; wherein the custom form page displays metadata information and table setting information;

[0025] After verifying and modifying the metadata information and table setting information in the custom form page, regenerate the JSON data;

[0026] Based on the regenerated JSON data, the updated database logical table is modified.

[0027] Furthermore, the master-slave table status includes the master table status and the sub-table status; when modifying the table setting information on the custom form page, if it is set as a sub-table, the database table of its master table is specified.

[0028] Furthermore, the database logic table includes creation information of each database table; wherein the creation information includes creation time, creation user and creation status of the database table; the database table entity is created in the following manner:

[0029] Based on the latest metadata information of the database table to be generated in the modified database logic table, update the SQL statement for creating the current database table;

[0030] Determine the master-slave table status of the current database table to be generated based on the latest table setting information of the database table to be generated in the modified database logic table;

[0031] If it is in the master table state, create a database table entity based on the current database table creation SQL statement and update the creation information of the database table;

[0032] If it is in the sub-table state, determine whether the database table of its main table has been created. If so, create a database table entity based on the current database table creation SQL statement and update the creation information of the database table; if not, update the creation status in the creation information of the database table to not created, and store the current database table creation SQL statement.

[0033] Furthermore, if it is determined that the current database table to be generated is in the main table state, the creation status in the creation information of each database table of its sub-table is determined. If it is not created, the database entity table is created based on the database table creation SQL statement corresponding to the uncreated database table and the creation information of the database table is updated.

[0034] Furthermore, the large model is trained as follows before application:

[0035] Acquire business database table data, process the business database table data, and obtain table configuration information; wherein the table configuration information includes data fields and data field types;

[0036] Adding tags to the table configuration information to form a sample data set; wherein the tags are business scenarios;

[0037] The large model is trained based on the sample data set to obtain a trained large model.

[0038] Furthermore, processing the business database table data includes deduplicating the business database table data, and then acquiring metadata information of each database table in the deduplicated business database table data.

[0039] Compared with the prior art, the present invention can achieve at least one of the following beneficial effects:

[0040] The embodiment of the present invention provides a database table generation method based on a big model. Through a database table creation dialogue, the big model generates a database table creation SQL statement. After parsing the database table creation SQL statement, the database logic table is updated, the updated database logic table is verified and modified to obtain a modified database logic table. Based on the modified database logic table, the current database table creation SQL statement is updated to further create a database table entity. The database table structure is constructed through a dialogue between the user and the big model, which greatly simplifies the database design and management work and provides powerful data support for the application system. The natural semantic big model is used to automatically analyze data patterns and business requirements to generate an efficient and optimized database table structure. The creation statement of the big model is then parsed to display the page form for the user to adjust. This is more intelligent and not only reduces manual intervention, but also improves the efficiency and accuracy of database design while ensuring program flexibility.

[0041] In the present invention, the above-mentioned technical solutions can also be combined with each other to achieve more preferred combination solutions. Other features and advantages of the present invention will be described in the subsequent description, and some advantages can become obvious from the description, or can be understood by practicing the present invention. The purpose and other advantages of the present invention can be realized and obtained through the contents particularly pointed out in the description and the drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] The drawings are only for the purpose of illustrating particular embodiments and are not to be considered limiting of the present invention. Like reference symbols denote like components throughout the drawings.

[0043] Figure 1 A schematic diagram of a flow chart of a method for generating a database table based on a large model provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0044] The preferred embodiments of the present invention are described in detail below in conjunction with the accompanying drawings, wherein the accompanying drawings constitute a part of this application and are used together with the embodiments of the present invention to illustrate the principles of the present invention, but are not used to limit the scope of the present invention.

[0045] A specific embodiment of the present invention discloses a method for generating a database table based on a large model, such as Figure 1 As shown, the following steps are included:

[0046] S1. Based on the database table creation dialogue, the large model generates the database table creation SQL statement.

[0047] During implementation, the database table creation dialogue is based on which the large model generates a database table creation SQL statement, including:

[0048] S111. Send preconditions to the big model to establish a big model dialogue scenario; wherein the preconditions are to set the big model to create SQL statements for the database in response to the content of the dialogue.

[0049] Specifically, the database table creation dialogue is a business scenario description or database table information.

[0050] Specifically, after the big model receives the preconditions, the big model limits the reply content to include the table name, Chinese comment on the table name, field name, field type, field length, Chinese comment on the field, and limits the reply to only the SQL statement for creating the database table.

[0051] S112: Conduct a database table creation dialogue with the large model to generate an initial database table creation SQL statement.

[0052] Specifically, the content of the dialogue for creating a database table with the large model includes an introduction to a business scenario or structural design information of a specific database table.

[0053] For example, a user can define database table requirements through simple natural language descriptions. For example, a user can say: "I need a customer information table that includes name, contact information, address, and registration date." After receiving these natural language descriptions, the large model begins to process the user's request and semantically understands the natural language input by the user; then, it converts the input natural language description into a structured data definition. For example, it will recognize that "name" means it may be a string type field, and "registration date" may be a date type field; the large model uses the understood structured data definition to generate an SQL statement that conforms to the SQL syntax rules to create a database table.

[0054] S113, verifying the initial database table creation SQL statement, if the verification passes, using it as the database table creation SQL statement; otherwise, re-performing the database table creation dialogue.

[0055] Specifically, the large model verifies the initial database table creation SQL statement in the following manner:

[0056] Check the SQL statement for creating the initial database table to see if it contains the necessary SQL information. If so,

[0057] Performing a syntax check on the SQL statement for creating the initial database table to determine whether the SQL statement is legal, if so, the check passes;

[0058] The necessary SQL information includes the CREATE TABLE keyword, table name, table description information, field name, field type, field length, field precision, and field description information.

[0059] Specifically, by introducing the JSQLParser third-party SQL parsing library, creating a SQL parser instance, parsing SQL statements, if the SQL syntax is illegal, capturing the JSQLParserException during the parsing process and outputting an error message.

[0060] Preferably, if the verification fails, the large model gives a prompt to remind the user to change the expression or restate it.

[0061] Preferably, the large model is trained as follows before application:

[0062] S121. Acquire business database table data, process the business database table data, and obtain table configuration information; wherein the table configuration information includes data fields and data field types.

[0063] Specifically, processing the business database table data includes deduplicating the business database table data, and then acquiring metadata information of each database table in the deduplicated business database table data.

[0064] Exemplarily, 42 systems and 2,300 business data tables are obtained.

[0065] S122. Add tags to the metadata information to form a sample data set; wherein the tags are business scenarios.

[0066] Specifically, the business scenario refers to the actual application scenario of the database table, for example, human resources statistics, salary management, cargo statistics, etc.

[0067] S123. Train the large model based on the sample data set to obtain a trained large model.

[0068] Specifically, the large model is trained based on the sample data set in the following manner:

[0069] Generate a table creation statement corresponding to each sample based on the sample data set, and then convert the table creation statement and the corresponding label corresponding to each sample into a data set in JSON format;

[0070] Add the dataset in JSON format to the Spider dataset to obtain the fine-tuning dataset;

[0071] Based on the fine-tuning dataset, QLoRA training is used to fine-tune the large model to obtain a trained large model;

[0072] It should be noted that the Spider dataset is a standard dataset used to evaluate the performance of natural language processing models in text-to-SQL query generation tasks. It contains a large number of natural language questions and corresponding SQL queries, as well as database schema information that supports these queries.

[0073] Specifically, the basic format of the Spider dataset includes database schema (tables.json), query (train.json) and question (dev.json); the database schema contains database schema information, such as database ID, table name, column name, column type, primary key, foreign key, etc., and the query and question include natural language questions, corresponding database creation SQL, database ID, etc.

[0074] It can be understood that the dataset in JSON format is added to the Spider dataset to obtain the fine-tuning dataset. The dataset in JSON format needs to match the table.json file structure.

[0075] Specifically, when using QLoRA training, the configured parameters include: the text length of the input model is set to 2048, the SQL content length of the output model is set to 512, the lora part of the different model fine-tuning in the project settings is set to llama, the fine-tuning type is set to lora, the rank size in LoRA fine-tuning is set to 64, the scaling factor in LoRA fine-tuning is set to 32, the quantization parameter is set to 4, the batch size of training samples on each gpu is set to 2, the cumulative steps value of gradient update is set to 16, the learning rate type is set to cosine_with_restarts, the steps interval of log saving is set to 50, the steps size value of ckpt saved by the model is set to 2000, the number of epochs of training data is set to 8, and the learning rate is set to 2e-4.

[0076] S2. After parsing the SQL statement for creating the database table, the database logic table is updated, and the updated database logic table is verified and modified to obtain a modified database logic table.

[0077] During implementation, the database logic table includes metadata information of each database table; wherein the metadata information includes the table name, comment, field name, field type, and field comment of each database table; the database logic table is updated in the following manner:

[0078] Build grammar rule files and lexical rule files based on the grammar rules and lexical rules of SQL table creation statements;

[0079] After the SQL statement for creating the current database table is segmented based on the lexical rule file, an abstract syntax tree is obtained based on the grammatical rule file;

[0080] The abstract syntax tree is traversed to obtain metadata information of the database table, and then the database logic table is updated based on the metadata information of the database table.

[0081] Specifically, the database logical table is updated by adding the database table metadata information to the database logical table.

[0082] It should be noted that lexical rules are used to define how to segment the input text into a series of tokens or lexical units; grammatical rules are used to define how tokens are combined into valid grammatical structures. SQL table creation statements are segmented into a series of tokens according to lexical rules. Each token represents an atomic element in the input text, such as keywords, identifiers, literals, operators, etc. Then, the above tokens are combined into an abstract syntax tree using grammatical rules. Finally, the abstract syntax tree is traversed to remove database dialects and useless characters, and key information of table creation statements is extracted, such as table name, column name, data type and other database metadata information, which is stored in the database logic table.

[0083] It should be noted that the database logic table stores information about the database tables generated for all conversations.

[0084] Specifically, JAVA language is used for implementation.

[0085] It should be noted that metadata information refers to the metadata table information of the database table, which refers to the information of the database table itself, such as table name, table comment, column name, column comment, column length, column type, etc., rather than the data content stored in the database table.

[0086] During implementation, the database logic table also includes table setting information of each database table; wherein the table setting information includes the master-slave table status; the updated database logic table is verified and modified in the following manner:

[0087] Generate JSON data from the latest metadata information of the database table to be generated in the updated database logic table;

[0088] Generate a custom form page based on JSON data; wherein the custom form page displays metadata information and table setting information;

[0089] After verifying and modifying the metadata information and table setting information in the custom form page, regenerate the JSON data;

[0090] Based on the regenerated JSON data, the updated database logical table is modified.

[0091] Specifically, the master-slave table status includes the master table status and the sub-table status; when modifying the table setting information on the custom form page, if it is set as a sub-table, the database table of its master table is specified.

[0092] Specifically, the user verifies the metadata information presented in the custom form page according to actual needs and modifies it according to actual conditions.

[0093] Specifically, the custom form page uses HTML and JavaScript to display the parsed data table structure; in JavaScript, after receiving the JSON data, HTML form elements are dynamically generated through DOM operations. The form elements are used to display the table name, column name and data type, and allow users to modify them; wherein, the form contains an input box for displaying the table name, and multiple input boxes for displaying the name and type of each column.

[0094] S3. Based on the modified database logical table, create a database table entity.

[0095] During implementation, the database logic table includes creation information of each database table; wherein the creation information includes creation time, creation user and creation status of the database table; the database table entity is created in the following manner:

[0096] Based on the latest metadata information of the database table to be generated in the modified database logic table, update the SQL statement for creating the current database table;

[0097] Determine the master-slave table status of the current database table to be generated based on the latest table setting information of the database table to be generated in the modified database logic table;

[0098] If it is in the master table state, create a database table entity based on the current database table creation SQL statement and update the creation information of the database table;

[0099] If it is in the sub-table state, determine whether the database table of its main table has been created. If so, create a database table entity based on the current database table creation SQL statement and update the creation information of the database table; if not, update the creation status in the creation information of the database table to not created, and store the current database table creation SQL statement.

[0100] Specifically, if the database table to be generated is determined to be in the master table state, the creation status in the creation information of each database table of its child table is determined; if it is not created, a database entity table is created based on the database table creation SQL statement corresponding to the uncreated database table and the creation information of the database table is updated.

[0101] It should be noted that when the database table of the sub-table is established, the main table foreign key is automatically generated.

[0102] Specifically, updating the creation information of the database table includes: updating the creation user according to the object of the database table creation SQL statement dialogue, updating the creation time according to the database table creation time, and updating the creation status according to whether the database table is created; the creation status includes created and not created.

[0103] Specifically, based on the latest metadata information of the database table to be generated in the modified database logic table, the current database table creation SQL statement is updated, including:

[0104] Convert the latest metadata information of the database table to be generated in the modified database logic table into JSON data;

[0105] Create SQL statements to update the current database table based on JSON data.

[0106] Preferably, the database type is determined by identifying the current database driver type; when creating a database table entity, the implementation class of the corresponding type of database abstract factory is called to create each type of database entity table.

[0107] It should be noted that this embodiment can provide generation and execution of multiple database table creation statements according to different database types. Through the design pattern of abstract factory, it realizes the generation of multiple and various types of database tables including postgreSQL, mysql, oracle, sqlserver, db2, h2, hive2, and is specially adapted for domestic databases, including DAMO database, Shentong database, and Jincang database.

[0108] Compared with the prior art, the present embodiment provides a method for generating a database table based on a big model. Through a database table creation dialogue, the big model generates a database table creation SQL statement. After parsing the database table creation SQL statement, the database logic table is updated, the updated database logic table is verified and modified to obtain a modified database logic table. Based on the modified database logic table, the current database table creation SQL statement is updated to further create a database table entity. The database table structure is constructed through a dialogue between the user and the big model, which greatly simplifies database design and management work and provides powerful data support for the application system. By using the natural semantic big model, the data model and business requirements can be automatically analyzed to generate an efficient and optimized database table structure. Subsequently, the creation statement of the big model is parsed to display the page form for the user to adjust. This is more intelligent and not only reduces manual intervention, but also improves the efficiency and accuracy of database design while ensuring program flexibility.

[0109] Those skilled in the art will appreciate that all or part of the processes of the above-mentioned embodiments can be implemented by instructing related hardware through a computer program, and the program can be stored in a computer-readable storage medium, wherein the computer-readable storage medium is a disk, an optical disk, a read-only storage memory, or a random access memory, etc.

[0110] The above description is only a preferred specific implementation manner of the present invention, but the protection scope of the present invention is not limited thereto. Any changes or substitutions that can be easily conceived by any technician familiar with the technical field within the technical scope disclosed by the present invention should be covered within the protection scope of the present invention.

Claims

1. A method for generating a database table based on a large model, characterized in that: The following steps are involved: Based on the database table creation dialogue, the large model generates the database table creation SQL statement; After parsing the SQL statement for creating the database table, updating the database logic table, verifying and modifying the updated database logic table, and obtaining a modified database logic table; Based on the modified database logic table, the current database table creation SQL statement is updated to further create the database table entity.

2. The method for generating a database table based on a large model according to claim 1, characterized in that: Based on the database table creation dialogue, the large model generates a database table creation SQL statement, including: Sending preconditions to the big model to establish a big model dialogue scenario; wherein the preconditions are to set the big model to create SQL statements for the database in response to the content of the dialogue; Conduct a database table creation dialogue with the large model to generate an initial database table creation SQL statement; The initial database table creation SQL statement is verified. If the verification passes, it is used as the database table creation SQL statement; otherwise, the database table creation dialogue is re-conducted with the large model.

3. The method for generating a database table based on a large model according to claim 2, characterized in that: The large model verifies the initial database table creation SQL statement in the following way: Check the SQL statement for creating the initial database table to see if it contains the necessary SQL information. If so, Performing a syntax check on the SQL statement for creating the initial database table to determine whether the SQL statement is legal, if so, the check passes; The necessary SQL information includes the CREATE TABLE keyword, table name, table description information, field name, field type, field length, field precision, and field description information.

4. The method for generating a database table based on a large model according to claim 1, characterized in that: The database logic table includes metadata information of each database table; wherein the metadata information includes the table name, comment, field name, field type, and field comment of each database table; the database logic table is updated in the following manner: Build grammar rule files and lexical rule files based on the grammar rules and lexical rules of SQL table creation statements; After the SQL statement for creating the current database table is segmented based on the lexical rule file, an abstract syntax tree is obtained based on the grammatical rule file; The abstract syntax tree is traversed to obtain metadata information of the database table, and then the database logic table is updated based on the metadata information of the database table.

5. The method for generating a database table based on a large model according to claim 3, characterized in that: The database logic table also includes table setting information of each database table; wherein the table setting information includes the master-slave table status; the updated database logic table is verified and modified in the following manner: Generate JSON data from the latest metadata information of the database table to be generated in the updated database logic table; Generate a custom form page based on JSON data; wherein the custom form page displays metadata information and table setting information; After verifying and modifying the metadata information and table setting information in the custom form page, regenerate the JSON data; Based on the regenerated JSON data, the updated database logical table is modified.

6. The method for generating a database table based on a large model according to claim 5, characterized in that: The master-slave table status includes the master table status and the sub-table status; when modifying the table setting information on the custom form page, if it is set as a sub-table, the database table of its master table is specified.

7. The method for generating a database table based on a large model according to claim 6, characterized in that: The database logic table also includes the creation information of each database table; wherein the creation information includes the creation time, the creation user and the creation status of the database table; the database table entity is created in the following manner: Based on the latest metadata information of the database table to be generated in the modified database logic table, update the SQL statement for creating the current database table; Determine the master-slave table status of the current database table to be generated based on the latest table setting information of the database table to be generated in the modified database logic table; If it is in the master table state, create a database table entity based on the current database table creation SQL statement and update the creation information of the database table; If it is in the sub-table state, determine whether the database table of its main table has been created. If so, create a database table entity based on the current database table creation SQL statement and update the creation information of the database table; if not, update the creation status in the creation information of the database table to not created, and store the current database table creation SQL statement.

8. The method for generating a database table based on a large model according to claim 7, characterized in that: If it is determined that the current database table to be generated is in the master table state, the creation status in the creation information of each database table of its child table is determined. If it is not created, the database entity table is created based on the database table creation SQL statement corresponding to the uncreated database table and the creation information of the database table is updated.

9. The method for generating a database table based on a large model according to claim 2, characterized in that: The large model is trained as follows before application: Acquire business database table data, process the business database table data, and obtain table configuration information; wherein the table configuration information includes data fields and data field types; Adding tags to the table configuration information to form a sample data set; wherein the tags are business scenarios; The large model is trained based on the sample data set to obtain a trained large model.

10. The method for generating a database table based on a large model according to claim 9, characterized in that: Processing the business database table data includes deduplicating the business database table data, and then acquiring metadata information of each database table in the deduplicated business database table data.

Images