A data migration method between different types of relational databases

Abstract

The application discloses a data migration method between different types of relational databases, and the method comprises the following steps: a, acquiring table structure and data dependency involved in a data set to be migrated by accessing a source database data dictionary; b, using ORM to project the data table structure to be migrated in the source database into class object definition in a computer high-level language by using the acquired table structure and data dependency information, and using meta-object programming technology to add the data table structure definition information into the corresponding class object as an attribute, and no configuration file is generated additionally for the data table structure. Compared with the prior art, the application can acquire the dependency relationship between data tables by reading the data dictionary, and can establish the database data table and import the data in sequence according to the dependency relationship (the dependency is less in the front, and the dependency is more in the rear), so that the integrity and consistency of the imported data in the target database can be ensured.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of database technology, in particular to a data migration method between Oracle and a homebred database such as RDBMS, Dream database, etc. BACKGROUND

[0002] There are three ways for the current main database data migration method: 1) through ODBC interface, the data access efficiency is low by using this way, and in the practice process, without the related specific driver provided by the manufacturer, it is only theoretically feasible; 2) through the data migration tool provided by the database manufacturer, for example, the migration tool from Oracle database to Dream database is provided after the installation of Dream database, but there are some deficiencies in the use process of the tool, for example: the foreign key association between the data tables belonging to two schemes in Oracle cannot be effectively migrated to Dream database; 3) the source database exports SQL script and imports to the target database, this data import and export method is mainly used for data migration in the scene with small data volume, and the dependency relationship between the data is not considered when generating SQL statement, and the generated SQL statement has compatibility risk.

[0003] With the progress of the product of the homebred database manufacturer, more and more projects use homebred database to provide data access service for the system in the project design process due to the cost, after-sales, customer requirements and other factors. Due to the differences between the database in the table structure design and the library structure, a method is needed to quickly, correctly and efficiently realize the smooth migration of the database data structure, content and system. SUMMARY

[0004] The technical problem to be solved by the present application is to make up for the defects of the above-mentioned prior art, in order to solve the above-mentioned problems, a simple and efficient data migration method is provided, which has low requirement on the user's database professional skill in the use process; the operation steps are simple; the migration method is a process method, if the migration fails, the failure position and the failure reason can be directly obtained; the migrated data is stored and transmitted by using xml file after encryption, which meets the user's demand for data security.

[0005] The purpose of the present application is realized in the following way:

[0006] A data migration method between different types of relational databases, the method comprises:

[0007] a) obtaining the table structure and data dependency relationship involved in the data set to be migrated by accessing the source database data dictionary;

[0008] b) By the acquired table structure and data dependency information, using ORM to project the data table structure in the source database which needs to be migrated into the class object definition in the computer high-level language, and using meta-object programming technology to add the data table structure definition information as the attribute to the corresponding class object, without generating the configuration file for the data table structure;

[0009] c) For each class object mentioned in step b), providing the import and export interface of the xml data stream, and implementing the encryption and decryption function of the data at the interface, and providing the read and write access interface of the xml file for the class object instance set corresponding to each data table;

[0010] d) In the ORM data access layer, respectively fitting the source database and the destination database which need to be data migrated, so that they can normally read and write data of the related different types of databases through the modification of the running parameters;

[0011] e) By accessing the source database which needs to be data migrated through the ORM data access layer, according to the data dependency relationship in step a), in the order of dependency, with less dependent tables in front, sequentially calling the xml data stream export interface and the xml file writing interface in step c), exporting the data in the form of xml file in units of data table, and saving it on the specified external storage;

[0012] f) Reading the data table structure definition information stored in the class object in step b), according to the type of the destination database for data migration, generating the corresponding DDL, and submitting the destination database to execute to generate the database definition structure which is completely consistent with the source database structure definition;

[0013] g) By accessing the destination database which needs to be data migrated through the ORM data access layer, according to the data dependency relationship in step a), in the order of dependency, with less dependent tables in front, in units of data table, sequentially calling the xml file reading and xml data stream import interface in step c) to create the data set of the data table in memory, and inserting the data set into the destination database through the ORM data access layer, finally completing the migration of the data and the data storage scheme between different types of databases.

[0014] The related table information in step b) includes table structure definition, data column annotation, primary key definition, foreign key definition, data constraint and trigger definition.

[0015] The present application has the following advantages: compared with the prior art, the present application can obtain the dependency relationship between data tables by reading a data dictionary, and can ensure the integrity and consistency of imported data in a target database by establishing the database data table and importing data according to the dependency relationship (the data table with less dependency is established first, and the data table with other dependency is established later);

[0016] In the implementation of the present ORM data access layer, the table structure definition, data column annotation, primary key definition, foreign key definition, data constraint and trigger are defined in a class object as a part of program code by using meta-object programming technology, so that the number of configuration files involved by the tool can be effectively reduced, and the risk of tampering of the database mechanism can be reduced;

[0017] The ORM technology can effectively utilize the current code assets of a project, and separate the database data representation from the database access, so that the tool can be flexibly tried and matched with various types of databases;

[0018] In the data migration process, the table is taken as a basic unit, and when the data import fails, the data table involved in the fault can be indicated, so that the error can be accurately analyzed;

[0019] The database data is saved and transmitted in the form of an xml file, which meets the needs of offline upgrading of the system;

[0020] The data encryption and decryption function is added in the xml stream import and export of the class object representing the data, so that the data security requirement of the user is met. BRIEF DESCRIPTION OF DRAWINGS

[0021] Figure 1 is a schematic diagram of a data migration tool in the present application.

[0022] Figure 2 is a schematic diagram of a data migration process. DETAILED DESCRIPTION

[0023] The present application will be further described in detail below in combination with the drawings and specific embodiments.

[0024] It should be pointed out that the following detailed description is exemplary and is intended to provide further description of the present application. Unless otherwise specified, all technical and scientific terms used herein have the same technical meaning as understood by ordinary skilled persons in the technical field to which the present application belongs.

[0025] A data migration method between different types of relational databases, the method comprising:

[0026] a) obtaining the table structure and data dependency relationship involved in the data set to be migrated by accessing the data dictionary of a source database;

[0027] b) By obtaining the table structure and data dependency information, following the "convention over configuration" principle, using ORM technology to project the data table structure in the source database that needs to be migrated into the class object definition in the computer high-level language, and using meta-object programming technology to add the data table structure definition information as an attribute to the corresponding class object, without generating a configuration file for the data table structure;

[0028] c) For each class object mentioned in step b), provide an import and export interface for the xml data stream, and implement the decryption and encryption functions of the data at the interface, while providing read and write access interfaces for the xml file for each set of class object instances corresponding to the data table;

[0029] d) In the ORM data access layer, respectively fit the source database and the destination database that need to be migrated, so that they can perform normal data read and write operations on related databases of different types through the modification of running parameters;

[0030] e) Access the source database that needs to be migrated through the ORM data access layer, and according to the data dependency relationship in step a), in the order of dependency, with fewer tables in front, call the xml data stream export interface and xml file write interface in step c) in turn, export the data in the form of xml file in units of data tables, and save it on the designated external storage;

[0031] f) Read the data table structure definition information stored in the class object in step b), and according to the type of the destination database for data migration, generate the corresponding DDL (data definition statement), and submit the destination database to execute to generate a database definition structure consistent with the source database structure definition;

[0032] g) Access the destination database that needs to be migrated through the ORM data access layer, and according to the data dependency relationship in step a), in the order of dependency, with fewer tables in front, in units of data tables, call the xml file read and xml data stream import interface in step c) in turn to create a data set for storing the data table in memory, and insert the data set into the destination database through the ORM data access layer in turn, finally complete the migration of data and data storage scheme between different types of databases.

[0033] The related table information in step b) includes table structure definition, data column annotation, primary key definition, foreign key definition, data constraint, and trigger definition.

[0034] During the implementation of a certain project, the relevant data needs to be completely migrated from the Oracle database to the Dream database and the RDBMS database.

[0035] Since the original project uses a self-developed ORM framework based on the.NET framework for data description and data access, in the ORM framework: the scheme name, table name, etc. are modified with custom attributes to the corresponding data mapping class; the column name, data type, data length, whether it is a primary key, column annotation, etc. are modified with custom attributes to the corresponding data mapping class properties. Obviously, by reading the parameter values in the above-mentioned custom attributes, the ORM data access layer can generate the corresponding DDL (Data Definition Language) and DML (Data Manipulation Language) as needed, and by submitting the generated related statements to a specific database, it can achieve the functions of establishing a specific data table, defining foreign key constraints, and reading and inserting data.

[0036] The implementation of the dependency sequence between data tables is not expressed through a configuration file or by defining properties in the data mapping class, but by calling the file read-write interface and xml import / export interface mentioned in step c) above in the order of dependency sequence in the automatically generated code after reading the primary-foreign key relationship of the data table.

[0037] The functional framework of the tool is shown in Figure 1 , wherein:

[0038] "Data database reading and local storage code automatic generation tool ①" is a code automatic generation tool developed for the migration tool, which automatically generates code for reading data from the source database and saving it in xml file format locally according to the data dependency relationship;

[0039] "Data local reading and data database writing code automatic generation tool ②" is a code automatic generation tool developed for the migration tool, which automatically generates code for reading data from the xml file saved locally according to the data dependency relationship and inserting it into the destination database according to the dependency relationship;

[0040] "Database data reading ③" is the function of reading data from the source database in order implemented by "Data database reading and local storage code automatic generation tool ①";

[0041] "Data local storage ④" is the function of saving the data read out by "Database data reading ③" to a local xml file implemented by "Data database reading and local storage code automatic generation tool ①";

[0042] "Database data writing ⑤" is the function of saving the data read by "Data local reading ⑥" to the destination database implemented by "Data local reading and data database writing code automatic generation tool ②";

[0043] "Data local read ⑥" is the function of reading data from the xml file generated by "Data local storage ④" realized by "Data local read and data database write code automatic generation tool ②";

[0044] "Data dictionary adaptation layer ⑦" is a function of providing different types of database data dictionary definitions to "Data database read and local storage code automatic generation tool ①" and "Data local read and data database write code automatic generation tool ②" according to the pre-defined interface, and generating SQL statements such as foreign key definition, trigger definition and constraint check of the target database according to the foreign key definition in the source database;

[0045] "ORM data representation layer ⑧", through the way of customizing attributes, provides data table structure definition function, and at the same time, maps the data in the database into a collection of data model class instances;

[0046] "Database read-write access layer ⑨", the operation interface of database read-write access;

[0047] "Database attribute configuration ⑩", the configuration information of source database and target database, mainly including the URL of the database itself, the communication port used, the database type, etc.

[0048] The data migration process is divided into two parts, which are: the generation and effectiveness of the backup and data recovery function code of the data table structure to be migrated, the dependent relationship and related data, including the following a), b), c) and other steps; data migration implementation, including the following d), e), f), g), h) and other steps.

[0049] The process of data migration is as follows:

[0050] a) Automatic generation of database data backup code: "Data database read and local storage code automatic generation tool ①" reads the dependent relationship of the database related data table according to "Data dictionary adaptation layer ⑦", generates data backup code according to the dependency degree, and copies the related code to a specific location of the data migration tool software, realizing the functions of "Database data read ③" and "Data local storage ④";

[0051] b) Automatic generation of database data recovery code: "Data local read and data database write code automatic generation tool ②" reads the dependent relationship of the database related data table according to "Data dictionary adaptation layer ⑦", generates data recovery code according to the dependency degree, and copies the related code to a specific location of the data migration tool software, realizing the functions of "Database data write ⑤" and "Data local read ⑥";

[0052] c) compiling the data migration tool, so that the code automatically generated in steps a) and b) takes effect;

[0053] d) export and local storage of data in the source database: execute the code generated in step a), read data from the source database through "database data reading ③", encrypt the data through "data local storage ④", and save it locally in the form of an xml file;

[0054] e) generation and submission of data definition language of the target database: the "data dictionary adaptation layer ⑦" reads the relationships between the data tables to be synchronized in the source database, and according to the dependency relationship, the "ORM data representation layer ⑧" generates DDL table creation statements through the database table structure information included in the custom properties in the data model class, and submits them to the target database connection through the "database read-write access layer ⑨", and completes the table creation in the target database;

[0055] f) data dependency relationship definition: the "data dictionary adaptation layer ⑦" reads the relationships between the data tables to be synchronized in the source database, and according to the dependency relationship (primary-foreign key relationship), generates DDL to establish foreign keys, and submits them to the target database connection through the "database read-write access layer ⑨", and completes the creation of foreign key constraints in the target database;

[0056] g) data reading: execute the code generated in step a), decrypt the data saved in the xml file in step d) through "data local reading ⑥", and read the data into memory, at this time the table data is saved in memory in the form of a collection of data model class instances;

[0057] h) data migration: execute the code generated in step b), insert the table data read into memory into the target database through "database data writing ⑤" to complete the migration of related data.

[0058] The above steps are schematically illustrated as shown in Figure 2 .

[0059] The above only describes the preferred embodiments of the present application, and it should be noted that for those skilled in the art, without departing from the overall concept of the present application, a number of changes and improvements can be made, which should also be considered as the protection scope of the present application.

Claims

1. A method for data migration between different types of relational databases, characterized in that: The method comprises: a) obtaining the table structure and data dependency involved in the dataset to be migrated by accessing the source database data dictionary; b) using the ORM to project the data table structure in the source database that needs to be migrated into class object definition in computer high-level language by using the obtained table structure and data dependency information, and using the meta-object programming technology to add the data table structure definition information to the corresponding class object as an attribute, without generating a configuration file for the data table structure; c) providing an import and export interface for the xml data stream for each class object mentioned in step b), and implementing the decryption and encryption functions of the data at the interface, and providing read and write access interfaces for the xml file for the class object instance set corresponding to each data table; d) fitting the source database and the destination database that need to be data migrated respectively in the ORM data access layer, so that they can normally read and write data of the related different types of databases through the modification of the running parameters; e) accessing the source database that needs to be data migrated through the ORM data access layer, and sequentially calling the xml data stream export interface and the xml file writing interface in step c) according to the order of dependency through the data dependency relationship in step a), with the tables having less dependency in front, to export the data in the form of xml file in units of data table and save it on the specified external storage; f) reading the data table structure definition information stored in the class object in step b), generating the corresponding DDL according to the type of the destination database for data migration, and submitting the destination database to execute to generate a database definition structure consistent with the source database structure definition; g) accessing the destination database that needs to be data migrated through the ORM data access layer, and sequentially calling the xml file reading and xml data stream import interface in step c) to create a data set of the data table in memory according to the order of dependency through the data dependency relationship in step a), with the tables having less dependency in front, in units of data table, and inserting the data set into the destination database through the ORM data access layer, to finally complete the migration of the data and the data storage scheme between different types of databases.

2. The method of claim 1, wherein: The related table information in step b) includes table structure definition, data column annotation, primary key definition, foreign key definition, data constraint, and trigger definition.

Images