A method and system for automated testing of data migration between multiple databases

Abstract

This invention provides an automated testing method and system for data migration between multiple databases, comprising the following steps: establishing and maintaining a list of field mappings between the old and new databases, database connection information for the test environment, configuring the query statement for the baseline table, and the migration identifier field and primary key of the test database; automatically generating test SQL based on the configuration and querying the test data, querying the baseline data according to the primary key of the test data, comparing the two, and outputting the comparison results, with inconsistent fields highlighted in red. This invention adapts to heterogeneous data migration scenarios through a configurable approach, achieving automated comparison and difference highlighting, significantly improving testing efficiency and accuracy, and the configuration is reusable.

Description

Technical Field

[0001] This invention belongs to the field of automation technology, specifically relating to an automated testing method and system for data migration between multiple databases. Background Technology

[0002] Currently, during functional testing of data migration, testers typically perform data verification manually. This involves manually querying data across different databases and then comparing the results to verify the correctness of the migration. When data migration involves large amounts of data and tables, this manual verification method is extremely costly in terms of manpower and time, and it cannot be reused. A complete regression test can take hours or even days to complete, severely slowing down project delivery.

[0003] As system design philosophies evolve, database table structures often undergo fundamental changes. In this context, the field names, field types, and even table relationships between the test database and the benchmark database can differ significantly. Traditional comparison tools often require the source and target tables to have completely identical structures. Faced with such complex heterogeneous scenarios, existing solutions are difficult to reuse directly, and testers still need a deep understanding of the business logic to manually construct complex field mapping relationships.

[0004] In summary, given the large volume of data, heterogeneous structure, and frequent regressions, traditional testing solutions are no longer sufficient to meet the demands for fast, accurate, and reusable testing. Therefore, there is an urgent need for a configurable, automated, and intelligent data migration and comparison method that highlights differences in red, in order to reduce manual costs and improve test coverage and accuracy. Summary of the Invention

[0005] The purpose of this invention is to overcome the shortcomings of existing technologies in data migration testing, such as reliance on manual comparison, low efficiency, poor test script reusability, and difficulty in handling differences in heterogeneous database table structures. This invention provides an automated data migration comparison method. This method automatically generates test SQL and retrieves data by pre-configuring the correspondence between fields in the old and new databases, database connection information, baseline query statements, and the primary key and migration identifier of the test database. It then uses the primary key to accurately query the baseline data, achieving automated comparison and highlighting inconsistent fields in red. This results in efficient, accurate, reusable testing that is adaptable to heterogeneous data migration scenarios.

[0006] To address the aforementioned technical problems, the present invention adopts the following technical solution: An automated testing method for data migration between multiple databases includes the following steps: Establish and maintain a list of field mappings between the old and new databases, database connection information for the test environment, configuration of baseline table query statements, and migration identifier fields and primary keys for the test database; Based on the configuration, test SQL is automatically generated and test data is queried. The benchmark data is queried according to the primary key of the test data, the two are compared and the comparison results are output, with inconsistent fields highlighted in red.

[0007] Furthermore, maintaining the mapping list of fields between the old and new databases includes: maintaining a table-like list of the correspondence between test database field names and benchmark database field names, where column A represents the test database field names and column B represents the benchmark database field names.

[0008] Furthermore, maintaining the database connection information for the test environment includes maintaining the database IP, username, and password for different test environments in the configuration file.

[0009] Furthermore, the configuration of the baseline table query statement includes: configuring the query statement of the baseline table in the configuration file according to the field correspondence.

[0010] Furthermore, the configuration of the test library migration identifier field and primary key includes: specifying the test library migration identifier field and primary key field in the configuration file.

[0011] Furthermore, the automatic generation of test SQL includes: automatically reading the database name and table name of the test database, saving them to an array, and automatically reading all fields that need to be tested, concatenating them into the SELECT part of the SQL, while reading the migration identifier field and the primary key as the WHERE part of the SQL, forming a complete test database test SQL.

[0012] Furthermore, the query test data includes: executing a query based on the generated test SQL connection to the test database to obtain all migration data.

[0013] Furthermore, the query benchmark data includes: for each piece of migration data, using its primary key field value as the query condition, executing the configured benchmark table query statement to obtain the corresponding benchmark data.

[0014] Furthermore, the comparison and output of results includes: comparing the test library field values ​​of each migration data with the corresponding benchmark library field values ​​one by one, saving the comparison results to a file, and highlighting the inconsistent fields in red in the file.

[0015] On the other hand, the present invention provides an automated testing system for data migration between multiple databases, comprising: The configuration module is used to establish and maintain a list of field mappings between the old and new databases, database connection information for the test environment, configuration of base table query statements, and migration identifier fields and primary keys for the test database. The execution module is used to automatically generate test SQL based on the configuration and query test data. It queries the benchmark data according to the primary key of the test data, compares the two and outputs the comparison results, with inconsistent fields highlighted in red.

[0016] Compared with the prior art, the present invention has the following beneficial effects: 1. This invention can perform data comparison regardless of whether it crosses databases; 2. This invention provides data for each field from two databases, making the data viewing process clear and indirect. 3. The comparison data of this invention is saved in real time, and previous comparison results can be traced. 4. When the data logic of the benchmark library of this invention is relatively complex, the data retrieval logic can be configured by oneself, and the code can be converted according to the test library standard for different code values; 5. This invention allows for the reuse of tests between any benchmark library and test library. Attached Figure Description

[0017] Figure 1 This is a flowchart of a method according to an embodiment of the present invention; Figure 2 This is a schematic diagram showing the comparison results of an embodiment of the present invention. Detailed Implementation

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

[0019] Example 1 like Figure 1 As shown in the figure, the automated testing method for data migration between multiple databases provided in this embodiment includes the following steps: Step 1: Establish and maintain a list of field mappings between the old and new databases, database connection information for the test environment, configure the query statements for the baseline table, and the migration identifier field and primary key for the test database; Step 2: Automatically generate test SQL based on the configuration and query test data. Query the baseline data according to the primary key of the test data, compare the two and output the comparison results, highlighting inconsistent fields in red.

[0020] Step 1 of this embodiment includes the following sub-steps: Step 1.1: Maintain the correspondence between the old and new database tables and fields in an Excel spreadsheet.

[0021] To achieve automated comparison, this embodiment requires maintaining the mapping relationship between the test table fields of the test library and the benchmark library. In practice, testers need to maintain the test document according to the template so that the test fields can be automatically read during subsequent automated testing.

[0022] Step 1.2: Maintain the database IP, username, and password for different test environments in the configuration file.

[0023] Configure the database information for the test library and benchmark library in the corresponding configuration file. During implementation, the automated testing tool will automatically read the configuration file, establish a database connection, and automatically query test data.

[0024] Step 1.3: Configure the query statement for the baseline table data in the configuration file.

[0025] The benchmark database may involve multi-table joins. In practice, the query statements for the benchmark tables need to be prepared in advance, with the field values ​​in the conditional queries being variables.

[0026] Step 1.4: Configure the migration identifier field and primary key of the test database in the configuration file.

[0027] The migration fields may differ for each table. After configuring the migration flag field, you also need to configure the migration flag to confirm the range of the migration test data. Additionally, set a primary key to verify whether there is duplicate data migration.

[0028] Once the above preparations are complete, you can start the automated test by clicking "Execute". In this embodiment, step 2 includes the following sub-steps: Step 2.1: Automatically read the database names and table names of the test database maintained in the Excel spreadsheet and store them in an array. Automatically read all the fields that need to be tested from the details page and automatically concatenate them into SQL. Maintain the database names and field names of all test tables, and use a Python program to automatically read and concatenate them into SQL queries for test data.

[0029] Step 2.2: Based on the test SQL extracted from the test database in Step 2.1, connect to the test database configured in Step 1.2 and query all migration data in the test database.

[0030] Step 2.3: Based on the migration data retrieved in Step 2.2, using the primary key field value as the query condition, query the benchmark database according to the configuration in Step 1.3. Compare the retrieved data with the test database data, and save the comparison results to a comparison result file. Fields that are inconsistent are highlighted in red. Steps 2.1 and 2.2 retrieve test data. This step retrieves the benchmark data in the benchmark database through the mapping relationship between the test database and the benchmark database. The test data is compared with the benchmark data. The field names of the benchmark database and the test database, as well as the corresponding values ​​of each field, are saved to the comparison result file. Data that is inconsistent is highlighted in red; consistent data is not processed and is simply saved.

[0031] Step 2.4: As Figure 2 As shown, view the comparison results generated in step 2.3, focusing on the fields highlighted in red. This completes the automated comparison for data migration. In migration requirements, especially those involving business data migration, where the test database and migration database have different design schemes, different table field names and definitions, and often do not share the same data structure, this solution greatly improves testing efficiency when dealing with large amounts of test data and complex logic. It can also be executed repeatedly, and the test results are automatically saved.

[0032] Example 2 This embodiment provides an automated testing system for data migration between multiple databases, including: The configuration module is used to establish and maintain a list of field mappings between the old and new databases, database connection information for the test environment, configuration of base table query statements, and migration identifier fields and primary keys for the test database. The execution module is used to automatically generate test SQL based on the configuration and query test data. It queries the benchmark data according to the primary key of the test data, compares the two and outputs the comparison results, with inconsistent fields highlighted in red.

[0033] Although preferred embodiments of the invention have been described, those skilled in the art, upon learning the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, the appended claims are intended to be interpreted as including both the preferred embodiments and all changes and modifications falling within the scope of the invention.

[0034] Obviously, those skilled in the art can make various modifications and variations to the embodiments of the present invention without departing from the spirit and scope of the embodiments of the present invention. Thus, if these modifications and variations to the embodiments of the present invention fall within the scope of the claims of the present invention and their equivalents, the present invention also intends to include these modifications and variations.

[0035] All other parts not described in detail are existing technologies.

Claims

1. An automated testing method for data migration between multiple databases, characterized in that, Includes the following steps: Establish and maintain a list of field mappings between the old and new databases, database connection information for the test environment, configuration of baseline table query statements, and migration identifier fields and primary keys for the test database; Based on the configuration, test SQL is automatically generated and test data is queried. The benchmark data is queried according to the primary key of the test data, the two are compared and the comparison results are output, with inconsistent fields highlighted in red.

2. The automated testing method for data migration between multiple databases according to claim 1, characterized in that, Maintaining the mapping list between the old and new database fields includes: maintaining a table-like list of the correspondence between the field names in the test database and the field names in the benchmark database, where column A is the field name in the test database and column B is the field name in the benchmark database.

3. The automated testing method for data migration between multiple databases according to claim 1, characterized in that, The maintenance of the test environment database connection information includes maintaining the database IP, username, and password for different test environments in the configuration file.

4. The automated testing method for data migration between multiple databases according to claim 1, characterized in that, The configuration baseline table query statement includes: configuring the baseline table query statement in the configuration file according to the field correspondence.

5. The automated testing method for data migration between multiple databases according to claim 1, characterized in that, The configuration test library migration identifier field and primary key include: specifying the test library migration identifier field and primary key field in the configuration file.

6. The automated testing method for data migration between multiple databases according to claim 1, characterized in that, The automatic generation of test SQL includes: automatically reading the database name and table name of the test database, saving them to an array, automatically reading all fields that need to be tested, concatenating them into the SELECT part of the SQL, and simultaneously reading the migration identifier field and the primary key as the WHERE part of the SQL to form a complete test database test SQL.

7. The automated testing method for data migration between multiple databases according to claim 1, characterized in that, The query test data includes: executing queries based on the generated test SQL connection to the test database to obtain all migration data.

8. The automated testing method for data migration between multiple databases according to claim 1, characterized in that, The query benchmark data includes: for each piece of migration data, using its primary key field value as the query condition, executing the configured benchmark table query statement to obtain the corresponding benchmark data.

9. The automated testing method for data migration between multiple databases according to claim 1, characterized in that, The comparison and output of results includes: comparing the test library field values ​​of each migration data with the corresponding benchmark library field values ​​one by one, saving the comparison results to a file, and highlighting the inconsistent fields in red in the file.

10. An automated testing system for data migration between multiple databases, characterized in that, include: The configuration module is used to establish and maintain a list of field mappings between the old and new databases, database connection information for the test environment, configuration of base table query statements, and migration identifier fields and primary keys for the test database. The execution module is used to automatically generate test SQL based on the configuration and query test data. It queries the benchmark data according to the primary key of the test data, compares the two and outputs the comparison results, with inconsistent fields highlighted in red. The automated testing system for data migration between multiple databases is used to perform the steps in the automated testing method for data migration between multiple databases as described in any one of claims 1-9.

Images