A data insertion method, system and computer storage medium

Abstract

This invention discloses a data insertion method, system, and computer storage medium. The method involves acquiring a general information database and an environmental information database; processing data from these databases using single-table fields and inter-field relationships to obtain several single-table data sets; acquiring multi-table data generation scenario names and mapping these single-table data sets based on the mapping relationship between tables and field models to obtain multi-table associated data; parsing the multi-table associated data to obtain a main table, secondary tables, and inter-table association fields; and matching the main table and secondary tables with the single-table data sets according to the inter-table association fields, converting the matched data into an insertion table data program to classify and insert the real-time acquired data. The beneficial effects of this invention include flexible data configuration and automated data acquisition and insertion.

Description

Technical Field

[0001] This invention relates to the field of data insertion technology, and more specifically, to a data insertion method, system, and computer storage medium. Background Technology

[0002] During development or testing, especially data research or testing, it is often necessary to test and verify the correctness of code based on existing data. In current technologies, when testing and analyzing data, data is usually generated in the test environment using tools. However, when generating data in such a test environment, either all the previously generated data can only be translated into an equivalent number of SQL statements or files for saving, or if you want to add or modify data, you have to modify the SQL statements or files accordingly, resulting in low automation; moreover, the amount of code or data maintenance is relatively large, and maintainability is poor; and the relationships and connections between code or data are not explicitly defined, which is not conducive to verifying the logical relationships of data.

[0003] In view of the above, this application is hereby submitted. Summary of the Invention

[0004] The technical problem to be solved by the present invention is that in the prior art, when data testing is carried out by using tools to generate data in a test environment, the entire file needs to be changed when data needs to be added or modified, which cannot be automatically adjusted and has poor flexibility. The purpose is to provide a data insertion method, system and computer storage medium that can realize real-time automatic updates of data during testing and verification, and improve the flexibility of data processing.

[0005] This invention is achieved through the following technical solution:

[0006] A data insertion method, the method steps include:

[0007] Access general information databases and environmental information databases;

[0008] By processing the general information database and the environmental information database through single-table fields and the interrelationships between fields, several single-table data are obtained.

[0009] Obtain the name of the multi-table data generation scenario, and based on the mapping relationship between the table and field model, perform mapping processing on several single-table data to obtain multi-table related data;

[0010] The multi-table related data is parsed to obtain the main table, secondary table, and inter-table related fields;

[0011] Based on the inter-table association fields, the main table and the secondary table are respectively associated and matched with several single-table data, and the matched data is converted into table data insertion program to classify and insert the real-time acquired data.

[0012] Traditionally, data research or testing typically involves creating data in a test environment using tools to obtain relevant data sources and verify existing data. However, when processing data using this method, adding or modifying data requires changing the entire file, lacking automation and flexibility. This invention provides a data insertion method that uses multi-table joins to create data based on single-table combinations. It first maintains the configuration relationships of single tables and then assembles these relationships, achieving flexibility in data configuration and automated data acquisition and insertion.

[0013] Preferably, the method for obtaining the general information database includes:

[0014] Obtain information from various software systems;

[0015] Select any software system and obtain the basic information data corresponding to that software system, as well as the rules for generating related data. The obtained data is the general information data of that software system.

[0016] By traversing all software systems, a number of common information data are obtained, which constitutes the common information database.

[0017] Preferably, the environmental information database includes several environmental name information data and corresponding database name dimension information data.

[0018] Preferably, the sub-step for obtaining single-table data includes:

[0019] In the environmental information database, select any environmental information data, connect the built-in database to the environmental information data through the data connection module, and configure the corresponding structure information by showing create table xxx table_name.

[0020] In the general information database, table fields corresponding to the structural information are matched to obtain single-table data;

[0021] Traverse the environment information database to obtain several single-table data.

[0022] Preferably, the sub-step for obtaining the multi-table join data includes:

[0023] Constructing multi-table data generation scenarios;

[0024] Based on the mapping relationship between tables and field models, several single-table data are mapped to obtain sub-multi-table related data.

[0025] Configure the association data mode and the null value rate ratio of the association relationship for the sub-multi-table association data to obtain the multi-table association data.

[0026] Preferably, the associated data patterns include one-to-one, one-to-many, and many-to-many patterns.

[0027] Preferably, the sub-step of parsing the multi-table related data includes:

[0028] Obtain text data information with fields named main_table_id and correlation_tables from the multi-table related data;

[0029] By segmenting the data using relationList, we can obtain the main table, secondary table, and fields related to the relationships between the tables.

[0030] Preferably, based on the inter-table association fields, the main table and the secondary table are respectively associated and matched with several single-table data, and the matched data is converted into table data insertion program. The sub-steps of classifying and inserting the real-time acquired data include:

[0031] In the aforementioned single-table data, the data corresponding to the main table is matched to obtain the main table data;

[0032] In the aforementioned single-table data, the data corresponding to the sub-table is matched to obtain the sub-table data;

[0033] By using the inter-table association fields and combining them with the association pattern, the data in the main table and the data in the secondary table are associated, and the associated data is converted into an insert table data program to classify and insert the real-time acquired data.

[0034] The present invention provides a multi-table data insertion system, comprising a database acquisition module, a first processing module, a second processing module, a parsing module, and a data insertion module;

[0035] The database acquisition module is used to acquire a general information database and an environmental information database;

[0036] The first processing module is used to process data from the general information database and the environmental information database through single table fields and the interrelationships between fields to obtain several single table data.

[0037] The second processing module is used to obtain the name of the multi-table data generation scenario, and based on the mapping relationship between the table and the field model, to perform mapping processing on several single-table data to obtain multi-table related data;

[0038] The parsing module is used to parse the multi-table related data to obtain the main table, secondary table, and inter-table related fields;

[0039] The data insertion module is used to associate and match the main table and the secondary table with several single table data according to the inter-table association fields, and convert the matched data into an insertion table data program to classify and insert the real-time acquired data.

[0040] The present invention provides a computer storage medium storing a computing program, which, when executed by a processor, implements the method described above.

[0041] Compared with the prior art, the present invention has the following advantages and beneficial effects:

[0042] The present invention provides a data insertion method, system, and computer storage medium that uses multi-table association to create data based on single-table combinations. It first maintains the configuration relationships of single tables, and then assembles the configuration relationships of single tables, thereby achieving flexibility in data configuration and automated data acquisition and insertion. Attached Figure Description

[0043] To more clearly illustrate the technical solutions of the exemplary embodiments of the present invention, the accompanying drawings used in the embodiments will be briefly described below. It should be understood that the following drawings only show some embodiments of the present invention and should not be regarded as a limitation of the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0044] Figure 1 Diagram of the insertion method Detailed Implementation

[0045] To make the objectives, technical solutions, and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the embodiments and accompanying drawings. The illustrative embodiments and descriptions of the present invention are only used to explain the present invention and are not intended to limit the present invention.

[0046] In the following description, numerous specific details are set forth in order to provide a thorough understanding of the invention. However, it will be apparent to those skilled in the art that these specific details are not necessary to practice the invention. In other embodiments, well-known structures, circuits, materials, or methods have not been specifically described in order to avoid obscuring the invention.

[0047] Throughout this specification, references to "an embodiment," "an example," or "an example" mean that a particular feature, structure, or characteristic described in connection with that embodiment or example is included in at least one embodiment of the present invention. Therefore, the phrases "an embodiment," "an example," "an example," or "an example" appearing in various places throughout the specification do not necessarily refer to the same embodiment or example. Furthermore, specific features, structures, or characteristics can be combined in one or more embodiments or examples in any suitable combination and / or sub-combination. Moreover, those skilled in the art will understand that the illustrations provided herein are for illustrative purposes and are not necessarily drawn to scale. The term "and / or" as used herein includes any and all combinations of one or more of the associated listed items.

[0048] In the description of this invention, the terms "front", "rear", "left", "right", "up", "down", "vertical", "horizontal", "high", "low", "inner", and "outer" indicate the orientation or positional relationship based on the orientation or positional relationship shown in the accompanying drawings. They are only for the convenience of describing this invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation. Therefore, they should not be construed as limiting the scope of protection of this invention.

[0049] Example 1

[0050] Traditionally, data research or testing involves using tools to generate data in a test environment to obtain relevant data sources and verify existing data. However, when processing data using this method, any additions or modifications require changing the entire file, making automated adjustments impossible and resulting in poor flexibility.

[0051] This embodiment provides a data insertion method that uses multi-table joins to create data based on single-table combinations. It first maintains the configuration relationships of the single tables, then assembles these relationships, achieving flexibility in data configuration and automated data retrieval and insertion. The specific insertion method is as follows: Figure 1 As shown, the method steps include:

[0052] S1: Obtain general information database and environmental information database;

[0053] The method for obtaining the general information database includes:

[0054] Obtain information from various software systems;

[0055] Select any software system and obtain the basic information data corresponding to that software system, as well as the rules for generating related data. The obtained data is the general information data of that software system.

[0056] By traversing all software systems, a number of general information data are obtained, which constitutes the general information database. The environmental information database includes several environmental name information data and corresponding database name dimension information data.

[0057] Step S1 mainly involves configuring the general information database and the environmental information database. The configuration of the general information database mainly includes:

[0058] S11: Determine the contents of the general configuration module, which mainly involves the common data definitions of all software systems.

[0059] S12: Determine the data name, type, and scope of use based on the software system level; for example, system-level configuration: system number, system name, string type, and affiliated Quanke Technology; and other specific domain data content definitions, such as user number, string type, and affiliated user center system...

[0060] S13: Determine the data generation rules, such as the rule for user IDs being a string of length 10, with the string content being pure numbers, and the first number starting with 9. There are several generation rules here, but it is not limited to this one generation rule.

[0061] S14: Generate data according to the configured rules, and check whether the generated data matches the actual data performance. If it does not match, adjust the data generation rules in S13 until the actual data requirements are met.

[0062] Specific configuration details for the environmental information database:

[0063] Environment configuration name information, such as uata, uatb, uatc, etc.; configuration environment specific database information, such as the connection information of the user database in the uata environment, including database type, database name, database IP, port, connection username, password; environment information will be flattened into environment name_database name dimension, such as uata_user, uatb_user, uatc_user.

[0064] S2: By processing the data of the general information database and the environmental information database through single table fields and the interrelationships between fields, several single table data are obtained;

[0065] The sub-steps for obtaining single-table data include:

[0066] In the environmental information database, select any environmental information data and connect the built-in database to the environmental information data through the data connection module, and configure the corresponding structure information by showing create table xxx table_name; in the general information database, match the table fields corresponding to the structure information to obtain single table data; traverse the environmental information database to obtain several single table data.

[0067] The specific process for creating single-table data is as follows:

[0068] Configure single-table information. Here, you need to select the environment name and database name information from step S1. You can connect to the database using the database connection module in Java and obtain the structure information of the configured table by using `show create table xxx table_name`. Obtain the names and types of all table fields. Define the set of rules that can be supported according to different data types. For example, for integers, it supports generating data at equal intervals within a numerical range, random generation, and auto-incrementing order generation. For floating-point numbers, it supports range + precision selection. For enumeration types, it supports defining an enumeration value set. For string types, it supports length + content definition (pure numbers, pure letters, numbers + letters + special characters, etc.). In addition, it also includes the common data generation rules for the corresponding types maintained in step S1. All selectable rules can be selected through drop-down boxes.

[0069] Configure the generation rules for the corresponding fields on the page. If the field is a primary field, provide a dropdown list for selecting the rules. If the field is a derived field, define the interrelationships between fields using expressions, such as field c = field a + field b. Preview the generated data to check if it meets the actual requirements. If not, continue adjusting the generation rules until they do. By configuring the field generation logic within the table, the interrelationships between fields, and the relationships between tables, data can be automatically and randomly generated. This not only enables rapid batch data generation and improves data creation efficiency, but also ensures data authenticity by conforming to the predefined rules in the configuration.

[0070] S3: Obtain the name of the multi-table data generation scenario, and based on the mapping relationship between the table and field model, perform mapping processing on several single-table data to obtain multi-table related data;

[0071] The sub-steps for obtaining multi-table related data include: constructing a multi-table data generation scenario; mapping several single-table data based on the mapping relationship between tables and field models to obtain sub-multi-table related data; configuring the related data mode and the null value ratio of the related relationships for the sub-multi-table related data to obtain multi-table related data. The related data mode includes one-to-one mode, one-to-many mode, and many-to-many mode.

[0072] Create a multi-table data creation scenario name, such as user loan scenario data creation.

[0073] Select the single table defined in step S2 and configure the fields for association between the tables. Map them using the table.field model. For example, if the cust_number field of the customer_info table is associated with the cust_no field of the loan_info table, then configure it as customer_info.cust_number = loan_info.cust_no.

[0074] Configure the data relationship mode between the tables, choosing one-to-one, one-to-many, or many-to-many; configure the null value rate of the relationship, such as configuring 20%, which means that if 100 data records are created in the secondary table, 20 of them will have null values ​​in the related fields.

[0075] S4: Parse the multi-table related data to obtain the main table, secondary table, and inter-table related fields;

[0076] The sub-steps for parsing the multi-table related data include:

[0077] Obtain text data information with fields named main_table_id and correlation_tables from the multi-table related data;

[0078] By segmenting the data using relationList, we can obtain the main table, secondary table, and fields related to the relationships between the tables.

[0079] For example, in a scenario of "data creation for financial information," the information configured on the front-end page is stored in a database table. The configuration information is retrieved from the configuration table based on `data_name = 'data creation for financial information'`, and then parsed. The value of the field named `main_table_id` in the configuration information is the main table number. Since there may be multiple secondary tables, they are all designed in `correlation_tables`. `correlation_tables` includes not only the secondary tables but also the relationships between tables; `leftField` represents the left table field of the relationship, `rightField` represents the right table field of the relationship, and `relation` indicates the relationship mode, which can be one-to-one (enumeration value 1), one-to-many (enumeration value 2), or many-to-many (enumeration value 3).

[0080] After parsing the configuration information to extract the primary and derived fields of the main and secondary tables, the tool generates the primary, derived, primary, and derived fields of the main and secondary tables in the order they were generated. This allows the tool to be used in any data creation scenario involving configuring relationships between tables, making the tool truly platform-based and reusable.

[0081] S5: Based on the inter-table association fields, the main table and the secondary table are respectively associated and matched with several single-table data, and the matched data is converted into table data insertion program. The real-time acquired data is classified and inserted. The specific sub-steps include:

[0082] In several single-table data sets, data corresponding to the main table is matched to obtain the main table data. If the main table involves primary fields and derived fields, a primary field is one whose value does not depend on the values ​​of other fields and is generated independently. For example, a customer ID field `cust_no`, assuming its generation rule is a random 10-character string, has no other fields participating in logical calculations; this type is called a primary field. A derived field is one whose value depends on the values ​​of other fields. For example, the total amount field `total_amt` equals the principal field `prin_amt` plus the interest field `inst_amt`; this type is a derived field. Since derived fields depend on the values ​​of primary fields, the values ​​of primary fields should be generated first, followed by the values ​​of derived fields, according to the dependency order. Therefore, primary field data is generated first, and derived data is generated from expressions defined in the main table.

[0083] In the aforementioned single-table data, the data corresponding to the sub-table is matched to obtain the sub-table data. If the sub-table involves a primary field and a derived field, the primary field data is generated first, and the derived data is generated by the expression defined in the sub-table.

[0084] By using the inter-table association fields and combining them with the association pattern, the data in the main table and the data in the secondary table are associated, and the associated data is converted into an insert table data program to classify and insert the real-time acquired data.

[0085] A program that converts data generated from all tables into data to be inserted into the table is typically a batch SQL insert statement.

[0086] Then execute the data insertion procedure in step S5 to monitor whether the data was successfully inserted.

[0087] This embodiment provides a data insertion method that uses multi-table data generation based on single-table combinations. By first maintaining the configuration relationships of single tables and then assembling these relationships, the implementation and configuration maintenance are decoupled, thus improving the flexibility of the data generation tool. Through configurable data vacancy rates and other functions, it can generate not only real data but also some abnormal data, which can be used to check whether the code covers abnormal branches, thereby improving code robustness. Through common data definitions, data rules common to various systems or the entire department can be consolidated, enabling both realistic data generation and direct reuse via dropdown options, thus improving the efficiency of data rule definition.

[0088] Example 2

[0089] This embodiment discloses a multi-table data insertion system. This embodiment is designed to implement the insertion method as described in Embodiment 1, and includes a database acquisition module, a first processing module, a second processing module, a parsing module, and a data insertion module.

[0090] The database acquisition module is used to acquire a general information database and an environmental information database;

[0091] The first processing module is used to process data from the general information database and the environmental information database through single table fields and the interrelationships between fields to obtain several single table data.

[0092] The second processing module is used to obtain the name of the multi-table data generation scenario, and based on the mapping relationship between the table and the field model, to perform mapping processing on several single-table data to obtain multi-table related data;

[0093] The parsing module is used to parse the multi-table related data to obtain the main table, secondary table, and inter-table related fields;

[0094] The data insertion module is used to associate and match the main table and the secondary table with several single table data according to the inter-table association fields, and convert the matched data into an insertion table data program to classify and insert the real-time acquired data.

[0095] Example 3

[0096] This embodiment discloses a computer storage medium storing a computing program, which, when executed by a processor, implements the method described in Embodiment 1.

[0097] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0098] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program issuing instructions. These computer program issuing instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the issuing instructions, which execute via the processor of the computer or other programmable data processing apparatus, produce implementations of the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0099] These computer program issuing instructions may also be stored in a computer-readable storage medium capable of directing a computer or other programmable data processing device to function in a particular manner, such that the issuing instructions stored in the computer-readable storage medium produce an article of manufacture including an issuing instruction means, the issuing instruction means being implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0100] These computer program instructions can also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing the instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0101] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of the present invention. It should be understood that the above description is only a specific embodiment of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

Claims

1. A data insertion method characterized by, The method steps comprise: Obtaining a general information database and an environment information database; Processing data of the general information database and the environment information database through single table fields and inter-field cross-checking relations to obtain a plurality of single table data; Obtaining a multi-table data scene name and performing mapping processing on the plurality of single table data based on a mapping relation between tables and field models to obtain multi-table associated data; Analyzing the multi-table associated data to obtain a primary table, a secondary table and inter-table associated fields; According to the inter-table associated fields, associating and matching the primary table and the secondary table with the plurality of single table data respectively, and converting the matched data into an insertion table data program to classify and insert real-time obtained data.

2. The method of claim 1, wherein, The method for obtaining the general information database comprises: Obtaining each software system; Selecting an arbitrary software system and obtaining basic information data corresponding to the software system and rules for generating related data, and the obtained data is general information data of the software system; Traversing all software systems to obtain a plurality of general information data, which is a general information database.

3. The method of claim 2, wherein, The environment information database comprises a plurality of environment name information data and corresponding database library name dimension information data.

4. The method of claim 1, wherein, The sub-steps for obtaining the single table data comprise: In the environment information database, selecting an arbitrary environment information data and connecting an embedded database with the environment information data through a data connection module, and configuring corresponding structure information through show create table xxx table name; In the general information database, matching table fields corresponding to the structure information to obtain single table data; Traversing the environment information database to obtain a plurality of single table data.

5. The method of claim 4, wherein, The sub-steps for obtaining the multi-table associated data comprise: Constructing a multi-table data scene; Based on a mapping relation between tables and field models, performing mapping processing on the plurality of single table data to obtain sub-multi-table associated data; Configuring an associated data mode and a null rate proportion of an associated relation for the sub-multi-table associated data to obtain multi-table associated data.

6. The method of claim 5, wherein, The associated data mode comprises one-to-one mode, one-to-many mode and many-to-many mode.

7. The method of claim 1, wherein, The sub-steps for analyzing the multi-table associated data comprise: Obtaining text data information with a name of main_table_id and correlation_tables fields in the multi-table associated data; Obtaining a primary table, a secondary table and inter-table associated fields through segmentation by relationList.

8. The method of claim 7, wherein, The sub-steps for associating and matching the primary table and the secondary table with the plurality of single table data respectively based on the inter-table associated fields, and converting the matched data into an insertion table data program to classify and insert real-time obtained data comprise: In the plurality of single table data, matching data corresponding to the primary table to obtain primary table data; In the plurality of single table data, matching data corresponding to the secondary table to obtain secondary table data; The main table data and the secondary table data are associated through the inter-table association field and in combination with the association mode, and the associated data is converted into an insert table data program to classify and insert the real-time acquired data.

9. A data insertion system characterized by The method comprises a database acquisition module, a first processing module, a second processing module, an analysis module and a data insertion module. The database acquisition module is configured to acquire a general information database and an environmental information database. The first processing module is configured to perform data processing on the general information database and the environmental information database through single table fields and inter-field cross-checking relationships to obtain single table data. The second processing module is configured to acquire a multi-table data scenario name and perform mapping processing on the single table data based on a mapping relationship between a table and a field model to obtain multi-table association data. The analysis module is configured to analyze the multi-table association data to obtain a main table, a secondary table and inter-table association fields. The data insertion module is configured to associate and match the main table and the secondary table with the single table data respectively according to the inter-table association fields, convert the matched data into an insert table data program, and classify and insert real-time acquired data.

10. A computer storage medium having stored thereon a computer program, characterized in that The computer program is executed by a processor to implement the method of any one of claims 1-8.

Images