Database migration method, migration model and readable storage medium

Abstract

The invention belongs to the technical field of databases, and discloses a database migration method, a migration model and a readable storage medium, system code level matching after data migration is carried out by replacing manual checking and reprogramming in data migration of two databases in an automatic adaptation mode, and the method comprises the following steps: firstly, determining a source end database and a target database; the method comprises the following steps: acquiring a difference file of two databases, determining incompatible items in a source end database according to the difference file, processing the incompatible items through a large model according to an adaptation requirement of a target database, and migrating data of the source end database into the target database. And finally, testing the interface of the target database to generate a report to complete migration. According to the efficient and accurate migration transformation method provided by the invention, the transformation cost is reduced, the working efficiency is improved, and stable transition and stable operation of an information system are ensured.

Description

Technical Field

[0001] The present invention belongs to the technical field of database migration, and particularly relates to a database migration method, a migration model, and a readable storage medium. Background Art

[0002] With the in-depth implementation of the national policy of "independent and controllable" for basic software, middleware, as the core supporting software of the information system, has received high attention from various industries. In this context, many industries have introduced relevant policies, clearly favoring domestic brands, demonstrating their firm confidence in and support for domestic middleware technology.

[0003] More and more government departments and enterprises have begun to transform and migrate their existing information systems to domestic databases, aiming to fully replace the original non-domestic databases with domestic databases to achieve the full independence and controllability of the information system. However, the current transformation and migration work of domestic databases mainly rely on manual inspection and code re-compilation. This method is not only inefficient, prone to errors, but also has high transformation costs, bringing certain pressure to enterprises and government departments.

[0004] For example, the patent with the publication number CN113111050A needs to analyze log data. It has certain defects because the executed code cannot ensure that all SQL statements in the code are executed, and the log coverage is incomplete.

[0005] For example, a security multi-compatible lightweight database adaptation development and operation device supporting domestic databases described in the patent with the publication number CN111241065A. However, this method achieves compatibility after switching databases by adopting a standardized method during the coding stage and is not applicable to the adaptation and migration of existing systems. Summary of the Invention

[0006] In order to solve the problems existing in the prior art, the present invention provides a database migration method, a migration model, and a readable storage medium, which can automatically identify and replace keywords in the original code that are incompatible with the target database, so as to ensure that the code can run smoothly on the target database without changing the original logic.

[0007] The technical solution adopted by the present invention is as follows: In a first aspect, the present invention discloses a database migration method, which replaces manual inspection and recompilation in the data migration between two databases through an automated adaptation method to perform system code-level matching after data migration. First, the source database and the target database are determined, the difference file between the two databases is obtained, the incompatible items in the source database are determined according to the difference file, and the large model processes the incompatible items according to the adaptation requirements of the target database, then migrates the data of the source database into the target database, and finally tests the interfaces of the target database to generate a report to complete the migration.

[0008] In combination with the first aspect, the present invention provides a first implementation manner of the first aspect. The large model first learns and trains through the difference comparison information between the source database and the target database sorted and labeled by humans, and then automatically adjusts the incompatible items in the source database.

[0009] In combination with the first aspect, the present invention provides a second implementation manner of the first aspect. The difference file lists the mapping relationships of the difference points between the source database and the target database in the form of a comparison table, where keywords are used as the elements of the mapping relationships.

[0010] In combination with the second implementation manner of the first aspect, the present invention provides a third implementation manner of the first aspect. After obtaining the difference file, the source database is scanned to obtain the files including the keywords existing in the difference file as the incompatible items.

[0011] In combination with the third implementation manner of the first aspect, the present invention provides a fourth implementation manner of the first aspect. The specific steps are as follows: Step 1: First, link the source database and the target database, and the configuration module detects and obtains the difference file between the two databases; Types of difference files: SQL script difference file: Records the differences between the SQL scripts of the two databases, such as different function names, syntax structures, etc.

[0012] Configuration file difference file: Records the differences between the configuration files of the two databases, such as connection information, parameter settings, etc.

[0013] Data file difference file: Records the differences between the data files of the two databases, such as data types, data structures, etc.

[0014] The two databases targeted in this application have a certain degree of compatibility, that is, the differences in the data files themselves are not large, and the configuration file is the corresponding adaptation file for the database client. When changing the SQL file, the difference points will be counted, and the adaptation file of the client will be adjusted separately. Therefore, the difference file in this application is mainly for the difference content corresponding to the SQL file and is only used for database migration.

[0015] It should be noted that the SQL file referred to in the present invention contains the structure of the database (tables, views, indexes, stored procedures, etc.) and data, and is used to configure and reflect the data storage method of the database.

[0016] Ways to obtain the difference file: Database comparison tool: Using a database comparison tool can automatically analyze the differences between two databases and generate a difference file. Use professional database comparison tools, such as DBComparer, ApexSQLDiff, etc., to compare the structure and data of the source database and the target database, and generate a difference file Script analysis: Manually analyze the scripts between two databases and record the differences.

[0017] Log analysis: Analyze the database log to identify SQL statements or error messages that failed to execute.

[0018] Application of the difference file: Code conversion: Use the difference file to guide the conversion and adaptation of SQL scripts, such as replacing incompatible functions, adjusting the syntax structure, etc.

[0019] Configuration adjustment: Use the difference file to adjust the database configuration, such as modifying connection information, parameter settings, etc.

[0020] Data migration: Use the difference file to guide data migration, such as converting data types, adjusting data structures, etc.

[0021] Step 2: Export the SQL file from the source database, scan the SQL file according to the mapping relationship in the obtained difference file, and process the file with keywords existing in the mapping relationship as incompatible items; Step 3: During the processing, use the trained conversion large model for the corresponding target database type to process the incompatible items, generate an adapted SQL file, import the SQL file into the target database to create the database structure, and migrate the data of the source database to the target database; Step 4: Verify and test the target database after migrating the data, and complete the migration process after manually intervening and modifying the content with differences found in the test of the source database.

[0022] Combined with the fourth implementation manner of the first aspect, the present invention provides a fifth implementation manner of the first aspect. In the step 1, the difference file between the two databases is obtained through a database comparison tool.

[0023] In the second aspect, the present invention provides a migration model, which is applied in the above database migration method, including: A link module is provided with a corresponding client for linking the source database and the target database to read and write data; A cache module for storing the SQL files obtained from the link module; and A processing module for processing the SQL files by loading a locally trained or externally linked large model.

[0024] Combined with the second aspect, the present invention provides a first implementation manner of the second aspect. The link module sends the SQL of the source database processed by the processing module to the target database to create a database structure, and then the link module links the source database and the target database, and the source database directly migrates the data to the target database.

[0025] Combined with the second aspect, the present invention provides a second implementation manner of the second aspect. The model is set in any one of the servers of the source database, the servers of the target database, the application server or the external cloud platform.

[0026] In a third aspect, the present invention provides a readable storage medium with a computer program stored thereon, and when the program is executed by a processor, it implements the migration model described in any one of the above.

[0027] The beneficial effects of the present invention are as follows: (1) The present invention designs a specific query keyword replacement scheme for databases. By using the exported SQL files and making separate modifications using the differential files and then importing them into the target database, this scheme can not only identify and replace the keywords in the original code that are incompatible with the target database, thus ensuring that the code can run smoothly on the target database without changing the original logic, so as to solve the compatibility problems existing in replacing different databases. At the same time, it is more flexible. Different from the way of migration script files, the SQL file is in text format and can be directly viewed and edited. This improves the transparency of the migration process, making each change visible and facilitating auditing and problem tracking; (2) The present invention uses a large model to deeply understand the code and generates comments that conform to the target database specifications in combination with the context information, so as to be able to automatically analyze the key information in the code and generate corresponding comments, improving the readability and maintainability of the code; (3) The present invention can also automatically test the migrated database to automatically execute test cases on whether the code after keyword replacement and comment generation is compatible with other modules or systems, and generate a detailed test report to provide accurate feedback to developers. Specific implementation manners

[0028] The following further explains the present invention in combination with specific embodiments.

[0029] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the described embodiments are part of the embodiments of this application, rather than all of them. The components of the embodiments of this application usually described and illustrated here can be arranged and designed in various different configurations.

[0030] Embodiment 1: This embodiment discloses a database migration method, aiming to save steps and improve efficiency during the process of replacing a database while ensuring data security requirements, and at the same time improve the conversion accuracy using a large model.

[0031] First, determine the source database and the target database for which the data needs to be replaced, and obtain the difference files of the two databases through migration tools or script analysis. The difference files record the contents where the two databases are different and form a mapping relationship in the form of a comparison table. The elements in this mapping relationship are keywords, and a mapping relationship of related keywords is formed in the comparison table.

[0032] Then extract the SQL file of the source database, comprehensively scan the SQL file of the source database, and list the files containing the keywords in the mapping relationship as incompatible items, thereby forming a set of several incompatible items.

[0033] Use a large model trained through the mapping relationship and capable of identifying the difference features between the two databases to perform mapping replacement on the incompatible items in the SQL file, thereby forming an SQL file that can match the target database and insert it into the target database to establish the database structure.

[0034] After setting up the database structure, migrate the data in the source database to the target database. Finally, verify and test the target database after the data migration. If there are any differences, manual intervention and modification will be carried out, and a migration report will be sorted out to complete the migration process.

[0035] Furthermore, the overall migration plan and preparation are as follows: First, evaluate the scale, structure, and complexity of the current database, and then determine the migration objectives and requirements, including the type and version of the new database.

[0036] Then formulate a detailed migration plan, including a schedule, resource allocation, risk assessment, and rollback strategy. Then prepare the target database environment, including hardware, software, and network configurations, to ensure that the target environment meets the performance and capacity requirements.

[0037] Before migration, perform a full backup of the source database to prevent data loss or damage during the migration process. Determine the data to be migrated and the data not to be migrated, and at the same time clean up invalid, outdated, or unnecessary data to reduce the migration burden.

[0038] Then, determine the migration order according to the dependency relationships between database objects. Usually, migrate the schema (table structures, views, stored procedures, etc.) first, and then the data. Migrate the basic objects that do not depend on other objects first, and then the dependent objects.

[0039] Then, use a database migration tool or write scripts for migration. The migration is carried out in batches. First, migrate a small part of the data for testing. After ensuring that there are no errors, perform a full-scale migration. After the migration is completed, verify the data to ensure data integrity and consistency. Check the data quality to ensure that there is no data loss or error.

[0040] If the migration is successful, switch the application corresponding to the source database to the new database. If problems are found, perform recovery according to the pre-prepared rollback plan, and record all steps and problems encountered during the migration process through logs.

[0041] During the entire migration process, the following requirements will be followed: Migrate the database objects that do not depend on other objects first, and then the dependent objects. Migrate the static data (such as table structures) first, and then the dynamic data (such as the data in the tables). For large databases, it may be necessary to migrate in batches to reduce the impact on the production environment. Conduct tests after each step of the migration to ensure the correctness of the migration.

[0042] In this embodiment, a migration model is also provided. This model is applied in the above migration method to achieve automated data migration between two databases through an automated program. This model is set in any one of the servers of the source database, the servers of the target database, the application server, or an external cloud platform. In this embodiment, the database migration of an application is used for illustration, and this migration model is set on the server or terminal device where the application is located.

[0043] This migration model includes: A link module, which is provided with a corresponding client for linking the source database and the target database to read and write data; A cache module, which stores the SQL files obtained from the link module; and A processing module, which processes the SQL files by loading a locally or externally linked pre-trained large model.

[0044] Among them, the link module sends the SQL of the source database processed by the processing module to the target database to create the database structure. Then, the link module links the source database and the target database, and the source database directly migrates the data to the target database.

[0045] This embodiment designs an efficient data verification and recovery mechanism, which can monitor data consistency in real time during the migration process and automatically repair or roll back when data loss or damage is found. The data snapshot function before and after migration is realized to ensure that the state before migration can be quickly restored in case of migration failure.

[0046] Additionally, encryption transmission and storage technologies (using AES encryption and DES decryption strategies) are introduced in this embodiment to ensure the confidentiality and integrity of data during the migration process. A role-based access control mechanism is designed to restrict unauthorized users from accessing the migration data and tools.

[0047] At the same time, the incremental migration function is realized, and only the data that has changed since the last migration is migrated, reducing the migration time and resource consumption. The real-time data synchronization function is supported to ensure that the data between the source database and the target database is always consistent during the migration process.

[0048] As an implementation method, it is an application description of the above migration method.

[0049] First of all, the source database is a MySQL database, and the target database is a DM database. It should be noted that only the above two common database migration situations are described in this embodiment, and this kind of database migration has been commonly used as an alternative solution for domestic databases in recent years. Domestic databases include but are not limited to DM, Nantong University General, and KingbaseES. Non-domestic middleware includes but is not limited to Oracle, SQLSever, INFORMIX, MySQL, and DB2. The difference files are mainly based on the differences between domestic databases and non-domestic databases in terms of character set, case sensitivity, keywords, reserved words, functions, statements, etc. This embodiment mainly targets keywords, and the brief steps are as follows: Export the MySQL database structure and data: Use tools such as MySQLWorkbench, mysqldump command-line tool or other database management tools to export the SQL file.

[0050] Convert the SQL syntax: Convert the MySQL-specific syntax in the exported SQL file into DM-compatible syntax.

[0051] Execute the converted SQL script: Execute the converted SQL script in the DM database to create the database structure.

[0052] Migrate the data: Import the data into the DM database according to the exported data content.

[0053] Verification data: Combine all replacement files, then automatically reverse-check the code interfaces in the controller layer for the rewritten relevant files, and simultaneously batch-test the coverage of all-file interfaces to output an accuracy report. For example, a total of 100 files were modified, covering 18 interfaces. After automatic replacement, the interface accuracy is 17 / 18. Then manual intervention can be carried out for modification.

[0054] In this embodiment, keywords are used as the objects of incompatible items for SQL file replacement. The following lists the syntax compatible with non-domestic databases and the syntax compatible with domestic databases that correspond one by one.

[0055] For the obtained differential files, the following is a comparison table containing keywords: Then perform a full-file scan on the extracted SQL files. If the files contain the above keywords, extract them and perform keyword replacement. After replacement, automatically add it in the next line of the replaced part in the form of a line comment. The following is a schematic of some code: DATE_FORMAT(u.EXT86, '%Y-%n-%d') as ext86, DATE_FORMAT(u.EXT87, '%Y-%n-%d') as ext87, DATE_FORMAT(u.EXT88, '%Y-%n-%d') as ext88, DATE_FORMAT(u.EXT89, '%Y-%m-%d') as ext89, DATE_FORMAT(u.EXT98, '%Y-%m-%d') as ext90, count(DISTINCT ifnull(module_id, part_id)) as total_nun, <!--GROUP_CONCAT(DISTINCT IENULL(CONCAT('[', q.MODULE_ID, ']'), CONCAT('[', part_id, ']'))) as relationId--> WM_CONCAT(DISTINCT '[' IIIFNULL(q.MODULE_ID, q.part_id) 1l ']') AS relationId FROM dbuser.tb_user_coreuc LEFT JOIN dbuser.tb_user_extend ue ON uc.USER_ID = ue.USER_ID The above SQL code snippet is used to query user information and includes operations such as date formatting, grouped statistics, and joining multiple fields. The meaning of each line is explained as follows: Date formatting: DATE_FORMAT(u.EXT86, '%Y-%n-%d') as ext86: Format the value of the u.EXT86 field in the "year-month-day" format and name the result ext86.

[0056] DATE_FORMAT(u.EXT87, '%Y-%n-%d') as ext87: Similarly, format the u.EXT87 field.

[0057] DATE_FORMAT(u.EXT88, '%Y-%n-%d') as ext88: Similarly, format the u.EXT88 field.

[0058] DATE_FORMAT(u.EXT89, '%Y-%m-%d') as ext89: Format the value of the u.EXT89 field in the "year-month-day" format and name the result ext89.

[0059] DATE_FORMAT(u.EXT98, '%Y-%m-%d') as ext90: Format the value of the u.EXT98 field in the "year-month-day" format and name the result ext90.

[0060] Grouped statistics: count(DISTINCT ifnull(module_id, part_id)) as total_nun: Count the number of distinct non-null values in the module_id and part_id fields and name the result total_nun.

[0061] Joining multiple fields: <!-GROUP_CONCAT(DISTINCT IENULL (CONCAT('[', q.MODULE_ID, ']'), CONCAT('[', part_id, ']'))) as relationId --> This line of code is commented out and will not be executed. Its function is to concatenate the q.MODULE_ID and part_id fields, enclose them in square brackets, then concatenate all the distinct concatenation results with commas, and name the result relationId.

[0062] WM_CONCAT(DISTINCT '[' IIIFNULL (q.MODULE_ID, q.part_id) 1l ']') AS relationId: This line of code concatenates the q.MODULE_ID and part_id fields, encloses them in square brackets, then concatenates all the distinct concatenation results with commas, and names the result relationId.

[0063] This SQL code snippet is used to query user information and includes operations such as date formatting, grouped statistics, and concatenating multiple fields. The DATE_FORMAT function is used for date formatting in the code, the GROUP_CONCAT or WM_CONCAT function is used to concatenate multiple fields, and the COUNT function is used for grouped statistics.

[0064] Among them, when processing and replacing the SQL file extracted from the source database, some code will be spliced. The automatic code splicing mentioned refers to the process of automatically combining multiple code snippets into a complete code segment according to specific rules and conditions.

[0065] During the database migration process, automatic code splicing is usually used in the following scenarios: Generating SQL statements: Automatically splicing multiple SQL statement snippets into a complete SQL statement.

[0066] Generating stored procedures: Automatically splicing multiple stored procedure snippets into a complete stored procedure.

[0067] Generating triggers: Automatically splicing multiple trigger snippets into a complete trigger.

[0068] Steps of automatic code splicing: Analyzing code snippets: Analyze the structure and syntax of code snippets to determine the relationships between code snippets.

[0069] Determining splicing rules: Based on the relationships between code snippets, determine splicing rules such as concatenation operators, formatting methods, etc.

[0070] Perform the splicing operation: Automatically splice the code snippets into a complete code segment according to the splicing rules.

[0071] Then, when training the large model, the learned syntax comparison table is as follows: For special functions containing Mysql, such as keywords like GROUP_CONCAT, IF, CONCAT, etc., replacing the sql syntax of the target domestic database with the large model can greatly improve the success rate of complex sql rewriting. The following is demonstrated by some code: <iftest="reportStatusList!=nullandreportStatusList.size()>0"> ANDute.REPORT_STATUSin <foreachcollection="reportStatusList"separator=","open="("close=")"item="item"index="index"> #{item} Among them, <if>Label: This label is used to determine whether to execute the SQL statement inside it based on conditions. The conditional expression is reportStatusList!= null and reportStatusList.size() > 0, which means that the SQL statement inside it will only be executed when reportStatusList is not null and contains at least one element.

[0072] AND clause: If the condition is met, AND ute.REPORT_STATUS in (...) condition will be added to the WHERE clause, indicating that the query result needs to meet the ute.REPORT_STATUS field is in the reportStatusList list.

[0073] <foreach>Label: This label is used to traverse the reportStatusList list and insert each element into the SQL statement.

[0074] collection="reportStatusList" specifies the list to be traversed.

[0075] separator="," specifies that the separator between elements is a comma.

[0076] open="(" and close=")" specify that the start and end symbols of the traversal result are parentheses.

[0077] When scanning the Controller layer interfaces, introduce an intelligent recognition mechanism that not only recognizes the basic information of the interfaces (such as URLs, request methods, parameters, etc.), but also classifies the functions of the interfaces (such as data query, data modification, business logic processing, etc.) through static code analysis or dynamic execution path analysis, providing a basis for formulating subsequent test strategies.

[0078] Optimization: Design differentiated test case templates for different categories of interfaces to improve the effectiveness and pertinence of testing. Achieve 100% interface coverage. Discover and fix incorrect SQL syntax in a timely manner.

[0079] It should also be noted that in addition to the keyword replacement scheme, rule-based automatic adaptation can also be performed. This scheme can automatically analyze and modify the code according to preset rules to adapt to the characteristics of different databases. These rules can include conversion rules for database operation statements, data type mapping rules, etc.

[0080] The model in this embodiment can also provide an automatic adaptation service based on the cloud platform, which can provide online code adaptation and testing functions. Users only need to upload the code to be migrated to the cloud platform, and the platform can automatically perform operations such as keyword replacement, comment generation, and interface testing, and return the adapted code and test report. This scheme can lower the usage threshold for users and improve the convenience of adaptation. This embodiment also provides a storage medium on which a computer program is stored, and when the program is executed by a processor, it implements the method described in any one of the above.

[0081] In addition, in each embodiment of the present invention, each functional unit can be all integrated in one processing unit, or each unit can be separately used as one unit, or two or more units can be integrated in one unit; the above integrated units can be implemented in the form of hardware, or in the form of hardware plus software functional units.

[0082] Those of ordinary skill in the art can understand that all or part of the steps to implement the above method embodiments can be completed by hardware related to program instructions. The foregoing program can be stored in a computer-readable storage medium. When the program is executed, it performs the steps including those of the above method embodiments; and the foregoing storage medium includes: various media such as removable storage devices, read-only memory (ROM, Read Only Memory), random access memory (RAM, Random Access Memory), magnetic disks, or optical discs that can store program codes.

[0083] Alternatively, if the above integrated units are implemented in the form of software function modules and sold or used as independent products, they can also be stored in a computer-readable storage medium. Based on such an understanding, the technical solutions of the embodiments of the present invention, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the methods described in the various embodiments of the present invention. And the foregoing storage medium includes: various media such as removable storage devices, ROM, RAM, magnetic disks, or optical discs that can store program codes.

[0084] The present invention is not limited to the above optional embodiments, and any person can obtain other various forms of products under the inspiration of the present invention. The above specific embodiments should not be construed as limiting the protection scope of the present invention. The protection scope of the present invention should be defined by the claims, and the specification can be used to interpret the claims.< / foreach> < / if>

Claims

1. A database migration method, which replaces manual inspection and recompilation in the data migration between two databases by automated adaptation to perform system code-level matching after data migration, characterized in that: First, determine the source database and the target database, obtain the difference files of the two databases, determine the incompatible items in the source database based on the difference files, process the incompatible items according to the adaptation requirements of the target database through the big model, and then migrate the data of the source database to the target database. Finally, test the interface of the target database and generate a report to complete the migration.

2. A database migration method according to claim 1, characterized in that: The large model is first trained by manually sorting and annotating the difference comparison information between the source database and the target database, and then automatically adjusts the incompatible items of the source database.

3. A database migration method according to claim 1, characterized in that: The difference file lists the mapping relationships between the source database and the target database at the differences in the form of a comparison table, wherein keywords are used as elements of the mapping relationships.

4. A database migration method according to claim 3, characterized in that: After the difference file is obtained, the source database is scanned to obtain files including the keywords in the difference file as incompatible items.

5. A database migration method according to claim 4, characterized in that: The specific steps are as follows: Step 1: First, connect the source database and the target database, and configure the module to detect and obtain the difference files between the two databases; Step 2: Export the SQL file from the source database, scan the SQL file according to the mapping relationship in the obtained difference file, and process the file with the keyword existing in the mapping relationship as an incompatible item, including replacing the keyword and adding a replacement comment to the replaced part of the code; Step 3: During the processing, the incompatible items are processed using the conversion model trained for the target database type, and an adapted SQL file is generated. The SQL file is imported into the target database to create a database structure, and the data of the source database is migrated to the target database. Step 4: Verify and test the target database after data migration, compare the differences found in the test with the source database, and manually intervene and modify the content to complete the migration process.

6. A database migration method according to claim 5, characterized in that: In the step 1, a difference file between two databases is obtained by using a database comparison tool.

7. A migration model, characterized in that: Applied in the database migration method described in claim 6, comprising: The link module is provided with a corresponding client for linking the source database and the target database to read and write data; A cache module that stores SQL files obtained from linked modules; and The processing module processes the SQL file by loading a large model trained locally or linking to an external one.

8. A migration model according to claim 7, characterized in that: The link module sends the source database SQL processed by the processing module to the target database to create a database structure, and then the link module links the source database and the target database, and the source database directly migrates the data to the target database.

9. A migration model according to claim 7, characterized in that: The model is set in any one of the source database server, the target database server, the application server or the external cloud platform.

10. A readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the migration model described in any one of claims 7 to 9 is implemented.

Images