CN103440273A - Data cross-platform migration method and device - Google Patents

Abstract

The invention relates to a data cross-platform migration method and device, which are used for data migration between different database systems. The device includes a source data information module, a target data information module, a conversion module, SQLEngine, a data packet encapsulation, transmission and analysis module, and an SQL statement execution module. Describe the information of the source data platform and the information of the target data platform through the source data information module and the target data information module, obtain the table from the source database to generate a Table object, and according to the XML file describing the mapping relationship between the fields between the databases, the conversion module will The Table object performs format conversion, and SQLEngine constructs SQL statements with the converted information, encapsulates the SQL statements, and transmits them to the platform where the target database is located, and executes the SQL statements on the target platform to complete the data migration. The invention realizes the smooth and non-destructive migration of data between different databases of the heterogeneous platform.

Description

Method and device for data cross-platform migration

technical field

The invention belongs to the field of cross-platform migration of application software, in particular to a data cross-platform migration method and device.

Background technique

Data migration refers to migrating the database and all its table structures, data in tables, and stored procedures (primary key, foreign key, index, etc.) to another database server, and it can be fully run. Another database server can be Running on a different machine, either on a different operating system or another database management system. There are many reasons for data migration, either because the application system it supports is migrated to another platform, or because the existing database system needs to be upgraded to a higher version, or because the platform where the data resides It is necessary to upgrade a series of basic software such as hardware and operating system, and so on. The entire migration process involves many steps and factors. Data migration methods include extraction/transformation/loading, replication, and manual scripting, etc. However, these methods face some problems. When the amount of data is growing on the one hand, and the allowed downtime is decreasing on the other hand, this work will become Extraordinarily complex.

The following statistics are listed in the "Global Data Migration 2000" report released by the Bloor Institute in September 2007:

16% of data migration projects were partially successful

37% over budget

64% did not meet deadlines

It can be seen that data migration is not an easy task. This is because database system manufacturers adopt different technical strategies and technical routes. Due to market competition and other reasons, the development and application of different database systems are different in terms of resource utilization, table structure design, library structure, etc. There are big differences. Difficulties that may be faced during the data migration process include:

(1) Different database systems support different character sets, and the most common problem in migration is garbled characters;

(2) The unique functions and technologies of the database system cannot be recognized by other database systems;

(3) Different database systems have different definitions and processing methods for fields, events, operations, etc., resulting in conflicts in the identification and analysis of some content;

How to completely and accurately implement data migration is a difficult problem for people in the industry to solve.

Contents of the invention

Aiming at the problems currently faced in implementing data migration, the present invention proposes a data cross-platform migration method and device, which can realize almost lossless data migration without any intermediate storage process.

The data cross-platform migration method provided by the present invention comprises the following steps:

Step 1: Create the information of the source data platform as a SrcDatabase object;

Step 2: Create the information of the target data platform as a DesDatabase object;

Step 3: Call the execute method to complete the entire migration process;

The established SrcDatabase object and DesDatabase object both contain the following information: database type, IP address of the host where the database is located, database port number, user name, password and database name;

Described execute method comprises the steps:

Step 3.1: Obtain a data table from the source database through the SrcDatabase object, extract data from the data table to construct a corresponding Table object; the information contained in the Table object includes: the schema name of the table, the name of the table, the structural information of all fields of the table, and the table The primary key information contained in the table, the foreign key information contained in the table, and all the index information in the table;

Step 3.2: Convert the Table object into a table of the target database according to the field mapping relationship between the source database and the target database;

Step 3.3: Pass the converted information to the SQL statement construction engine SQLEngine, and construct the corresponding SQL statement according to the DesDatabase object that encapsulates the target database information;

Step 3.4: Encapsulate the SQL statement into a data packet and pass it to the platform where the target database is located;

Step 3.5: Execute the SQL statement on the target platform, insert the data of the source database into the target database, and complete the data migration.

The data cross-platform migration device provided by the present invention includes the following components: source data information module, target data information module, conversion module, SQLEngine, data packet encapsulation, transmission and analysis module, and SQL statement execution module.

The source data information module is used to create the information of the source data platform as a SrcDatabase object; the target data information module is used to create the information of the target data platform as a DesDatabase object. The SrcDatabase object and the DesDatabase object both include the following information: database type, IP address of the host where the database is located, database port number, user name, password and database name.

The conversion module is used to obtain a data table from the source database according to the SrcDatabase object, extract data from the data table to construct a corresponding Table object, and then convert the Table object into A table that conforms to the format of the target database.

SQLEngine is used to construct the corresponding SQL statement according to the DesDatabase object.

The data packet encapsulation, transmission and analysis module is used to encapsulate the constructed SQL statement into a data packet, and transmit it to the platform where the target database is located, and analyze the data packet on the platform where the target database is located, and deliver the parsed SQL statement to the SQL statement execution module.

The SQL statement execution module is used to execute the SQL statement, and insert the data of the source database into the target database.

The advantages and positive effects of the data cross-platform migration method and device of the present invention are:

(1) XML stores the mapping relationship between database system fields, fully utilizes the cross-platform characteristics of XML files, so that the data cross-platform migration device is not limited by the operating system and software platform;

(2) The storage of XML adopts hashmap, which is easy to expand;

(3) There is no need for any intermediate storage process, which saves system storage resources and reduces the difficulty of data migration;

(4) The difference between the source database system and the target database system is shielded upward, and one-click migration is realized;

(5) Adopt specially designed transmission data packets, which are simple and easy to parse, without redundant information, and further improve the efficiency of migration.

Description of drawings

Fig. 1 is a schematic flow chart of the data cross-platform migration method of the present invention;

Fig. 2 is the flowchart of the execute method used in the data cross-platform migration method of the present invention;

Fig. 3 is an example diagram of using HashMap to store XML files describing the mapping relationship between database fields;

Fig. 4 is a schematic diagram of a data packet encapsulated into an SQL statement;

Fig. 5 is a schematic structural diagram of a data cross-platform migration device of the present invention;

6 is an information description diagram of a source database and a target database in an embodiment of the present invention;

Fig. 7 is the schematic diagram of the table of source database in the embodiment of the present invention;

Fig. 8 is a schematic diagram of table equipment in the target database in an embodiment of the present invention;

9 is a schematic structural diagram of the table asset in the source database in the embodiment of the present invention;

Fig. 10 is a schematic diagram of index information of the table shown in Fig. 9;

Figure 11 is a schematic diagram of the structure of the table asset migrated to the target database MySQL;

Fig. 12 is a schematic structural diagram of the table users in the source database in the embodiment of the present invention;

Figure 13 is a schematic diagram of the structure of the table users after migrating to the target database MySQL;

Fig. 14 is a data schematic diagram of the table asset in the source database in the embodiment of the present invention;

Figure 15 is a schematic diagram of the data in the table asset after migrating to the target database MySQL;

Fig. 16 is a schematic diagram of the data of table users in the source database in the embodiment of the present invention;

Figure 17 is a schematic diagram of data in table users after migrating to the target database MySQL.

Detailed ways

The present invention will be further described in detail with reference to the accompanying drawings and embodiments.

The invention provides a data cross-platform migration method and device, which are used to migrate data located in a database system on a certain platform to a database system on another heterogeneous platform. The technology adopted in the present invention is mainly: creating the information of the source data platform as a SrcDatabase object, creating the information of the target data platform as a DesDatabase object, and then calling the execute method to complete the entire migration process. The specific execution process of the execute method is to first obtain a Table object from the source database through the obtained SrcDatabase object, pass it to the conversion (Transfer) module for corresponding format conversion, and then pass the converted information to SQL (Structured Query Language, Structured Query Language) statement construction engine SQLEngine, according to the DesDatabase object that encapsulates the information of the target database, to construct the corresponding SQL statement, and encapsulate the SQL statement into a data package, and pass it to the platform where the target database is located, and then in the target database Execute SQL statements on the platform to complete data migration. The entire process of data migration is performed in the order of basic table structure (including whether it is empty, default value), index, primary key, automatic growth, and data information. After the migration of all data tables is completed, the migration of foreign keys is performed. The invention is used to solve the technical problem of the integrity and accuracy of data migration in the cross-platform migration of application software, and realizes the smooth and lossless migration of data between different databases of heterogeneous platforms

As shown in Figure 1, the data cross-platform migration method provided by the present invention includes the following three steps:

Step 1: Create the information of the source data platform as a SrcDatabase object;

Step 2: Create the information of the target data platform as a DesDatabase object;

Step 3: Call the execute method to complete the entire migration process.

The information contained in the established SrcDatabase object and DesDatabase object includes: database type, IP address of the host where the database is located, database port number, user name, password, database name and other information.

As shown in Figure 2, it is the execution process of the execute method, including the following steps:

Step 3.1: Obtain a data table from the source database through the SrcDatabase object, and extract data from the data table to construct the corresponding Table object.

The information contained in the Table object includes: the schema name of the table, the name of the table, the structure information of all fields in the table, the primary key information contained in the table, the foreign key information contained in the table, and all the index information in the table.

The schema name of the table, schemaName, is a private (private) variable, and the data type is String (character type).

The table name tableName is a private variable of String data type.

The structural information columnList of all fields in the table is a private variable, and the array ArrayList<Column> is used to record each field. The structural information of the field is shown in Table 1:

Structural information of the fields in Table 1

name access permission type of data describe colName private String the name of the field dataType private int The value of Types in JDBC corresponding to the data type of the field typeName private String The name of the data type in the field database colSize private int Field size, the precision corresponds to decimals, and the length corresponds to characters decimalDigits private int When the field is a decimal, it indicates the number of decimal places, otherwise it is 0 nullable private boolean Whether the field is allowed to be empty colDef private String default value of the field auto increment private boolean Whether the field is auto-incremented

int is an integer type, and boolean is a Boolean type. JDBC (Java Data Base Connectivity, java database connection) is a Java API for executing SQL statements.

The primary key information pk contained in the table is a private variable, which is the primary key PrimaryKey. The primary key attribute description is shown in Table 2, and ArrayList<String> represents an additional table structure whose parameter is character type.

Table 2 primary key attribute information

The foreign key information fk contained in the table is a private variable, which is the PrimaryKey of other tables. The attribute description of the foreign key is shown in Table 3:

Table 3 foreign key attribute information

name access permission type of data describe pkTableSchema private String The schema name of the table where the primary key referenced by the foreign key resides pkTableName private String The table name of the table where the primary key referenced by the foreign key resides pkColNames private ArrayList<String> The names of the primary key constituent columns referenced by the foreign key pkName private String The primary key name of the foreign key reference fkTableSchema private String The schema name of the table where the foreign key resides fkTableName private String The table name of the table where the foreign key resides fkColNames private String The names of the columns that make up the foreign key fkName private String the name of the foreign key updataRule private short Actions when the primary key is updated deleteRule private short Operation when the primary key is deleted

short represents the data type of short integer.

All the index information indexList in the table is a private variable, and the array ArrayList<Index> is used to record the index information. The description of index attributes is shown in Table 4.

Table 4 Index attribute description

Step 3.2: Perform corresponding format conversion on the obtained Table object; the specific format conversion method is: convert the Table object into a table of the target database according to the field mapping relationship between the source database and the target database.

The mapping relationship between the fields between the source database and the target database is described by XML files, and the XML files are stored in the data structure Hashmap.

As shown in Figure 3, HashMap is used to store XML files describing the mapping relationship, and each XML file is only used to describe the mapping relationship between fields between two specific databases. HashMap has attribute values KEY and VALUE, and the KEY value stores a string, which is used to identify two databases for field mapping. For example, the value of KEY is DB2TOOracle, indicating that the two databases for field mapping are DB2 and Oracle. The VALUE value stores the save location of the XML file.

Among them, the common field mapping tables of common database systems IBM DB2, Oracle, Sybase, SQL Server, Informix and IBM ContentManager are as follows:

The mapping relationship between the fields of each two databases is described by an XML file. When converting the Table object, first, from the HashMap, find the corresponding XML file according to the string identifying the database, and then, according to the XML file, convert the Table The fields in are converted to the fields corresponding to the target database, and finally the table of the target database is obtained. When it is necessary to add the field correspondence of the new database, use the XML file to describe the field correspondence, and add the corresponding KEY and VALUE values in the HashMap.

Step 3.3: Pass the converted information to SQLEngine, and construct corresponding SQL statements according to the DesDatabase object that encapsulates the information of the target database; SQLEngine can generate corresponding SQL statements for a specific database. The generated SQL statement is used to insert the data of the source database into the target database, including insert statement, create statement, update statement and so on.

Step 3.4: Encapsulate the SQL statement into a data packet and deliver it to the platform where the target database is located.

As shown in Figure 4, it is the structure of the encapsulated data packet. The data packet used is composed of a data packet header and a data body, and the data packet tail is omitted. This simplification not only makes the structure of the entire data packet simpler, but also requires transmission. The traffic is smaller, the work of encapsulation/parsing is simpler, and the coding implementation is also simpler.

The data packet header is a description of the entire data packet, including a 4-byte packet start flag, a 4-byte protocol version number, a 2-byte packet size, and a 4-byte CRC (Cyclic Redundancy Check, cyclic redundancy check). I check) verification code. The data body part is an SQL statement that needs to be transmitted to the platform where the target database is located.

Step 3.5: Execute the SQL statement on the target platform, insert the data of the source database into the target database, and complete the data migration.

The entire process of data migration is performed in the order of basic table structure (including whether it is empty, default value), index, primary key, automatic growth, and data information. After the migration of all data tables is completed, the migration of foreign keys is performed.

As shown in FIG. 5 , it is a schematic structural diagram of a data cross-platform migration device provided by the present invention. The data cross-platform migration device includes the following modules: source data information module 1, target data information module 2, conversion module 3, SQLEngine4, data packet encapsulation, transmission and analysis module 5, and SQL statement execution module 6.

The source data information module 1, also called the SrcDatabase module, is used to create the relevant information of the source data platform as a SrcDatabase object.

The target data information module 2, also called the DesDatabase module, is used to create the relevant information of the target data platform as a DesDatabase object.

The information contained in the SrcDatabase object and DesDatabase object includes: database type, IP address of the host where the database is located, database port number, user name, password and database name and other information.

Conversion module 3, also known as Transfer module, is used to obtain a data table from the source database according to the SrcDatabase object, extract data from the data table to construct the corresponding Table object, and then use the field mapping between the source database and the target database according to the DesDatabase object Relationship, convert the Table object into a table that conforms to the format of the target database.

SQLEngine4 is an engine used to generate corresponding SQL statements according to the DesDatabase object.

The data packet encapsulation, transmission and analysis module 5 is used to encapsulate the constructed SQL statement into a data packet, and transmit it to the platform where the target database system is located, and analyze the data packet on the platform where the target database is located, and finally analyze the obtained The SQL statement is handed over to the SQL statement execution module 6 .

The SQL statement execution module 6 is used for executing the SQL statement, and inserting the data of the source database into the target database.

The effect of the data cross-platform migration method and device of the present invention will be described below through running examples.

As shown in Figure 6, it is a schematic diagram of the interface for data migration settings. The running example takes the migration of data from SQL server2008 (source database) on the Windows operating system to MySQL (the target database) on the Linux operating system as an example. The migration setting interface is used to set the source database and destination database information, as shown in the figure below. After clicking the "Start Migration" button, the migration of data, indexes, tables and table structures to the source database will start.

Next, let's verify the correctness and completeness of the data migration.

There are several tables in the equipment database of the original SQL server2008 database. Each table has attributes such as primary key and field automatic growth, data in the table, and index information.

(1) Table: as shown in Figure 7, it is the table of the source database. Migrate the data to MySQL, and the table of the database equipment is shown in Figure 8.

(2) Table structure:

The table structure of the asset in the source database is shown in Figure 9. The index information in the asset table is shown in Figure 10, and the index is assetname.

Migrate to the table structure of the asset table in MySQL, as shown in Figure 11. The MUL in the key attribute of assetname in Figure 11 is the index information of the table.

The table structure of users in the source database is shown in Figure 12.

After migrating to MySQL, the table structure of the users table is shown in Figure 13.

(3) Data in the table:

In the asset table in the source database, the data is shown in Figure 14.

The data in the asset table migrated to MySQL is shown in Figure 15.

Figure 16 shows the data in the Users table in the source database.

Figure 17 shows the data in the Users table after migrating to MySQL.

The results show:

(1) By adopting the technical solution of the present invention, the corresponding field conversion can be automatically performed according to the database table fields, thereby avoiding possible conflicts caused by different database fields;

(2) By comparing the contents of the same table in the source database and the target database, it is well verified that the data cross-platform migration method and device of the present invention have integrity and accuracy during the data migration process;

(3) The data cross-platform migration method and device of the present invention can automatically and effectively identify the number of tables in the database, and perform corresponding migration respectively;

(4) The cross-platform data migration method and device of the present invention can realize smooth and lossless migration of data between different databases on heterogeneous platforms in a network environment without an intermediate storage process.

Claims (7)

1. the cross-platform moving method of data, is characterized in that, comprises the steps:

Step 1: be a SrcDatabase object by the information creating of source data platform;

Step 2: be a DesDatabase object by the information creating of target data platform;

Step 3: call the execute method, complete whole transition process;

The SrcDatabase object of setting up and DesDatabase object all comprise following information: IP address, database side slogan, user name, password and the database-name of type of database, database place main frame;

Described execute method, comprise the steps:

Step 3.1: obtain a tables of data by the SrcDatabase object from the source database end, extract Table object corresponding to data construct from tables of data; The information that the Table object comprises has: all index informations in the foreign key information comprised in the major key information comprised in the structural information of the schema name of table, table name, all fields of table, table, table and table;

Step 3.2: according to the mapping relations of field between source database and target database, the Table object is converted to the table of target database;

Step 3.3: the information after conversion is passed to SQL statement and build engine SQLEngine, the DesDatabase object according to having encapsulated the target data library information, build corresponding SQL statement;

Step 3.4: SQL statement is packaged into to packet, passes to the platform that target database is positioned at;

Step 3.5: carry out SQL statement on target platform, the data of source database are inserted in target database, complete Data Migration.

2. the cross-platform moving method of data according to claim 1, is characterized in that, the Table object described in step 3.1, and the structural information of wherein showing all fields comprises following information:

Title Access rights Data type Describe colName private String The title of field dataType private int The value of Types in JDBC corresponding to the data type of field typeName private String The title of data type in the field data storehouse colSize private int Field size, corresponding precision during decimal, corresponding length during character decimalDigits private int Field means decimal digits while being decimal, otherwise is 0 nullable private boolean Whether field allows for sky colDef private String The default value of field autoincrement private boolean Whether automatic increase of field

Private means private variable, and String is character types, and int is integer, and boolean is Boolean type; JDBC means that the Java database connects;

The major key information comprised in table is the private variable, is primary key PrimaryKey; The major key attribute comprises following information:

ArrayList<String > mean the other list structure that a parameter is character types;

The foreign key information comprised in table is the private variable, and the attribute of external key comprises following information:

Title Access rights Data type Describe pkTableSchema private String The schema name of the major key place table of foreign key reference pkTableName private String The table name of the major key place table of foreign key reference claims pkColNames private ArrayList<String> The major key of foreign key reference forms the title of row pkName private String The major key title of foreign key reference fkTableSchema private String The schema name of external key place table fkTableName private String The table name of external key place table fkColNames private String External key forms the title of row fkName private String The title of external key updataRule private short Operation when major key upgrades deleteRule private short Operation when major key is deleted

Short means the data type of short;

In table, all index informations are the private variable, with array ArrayList<Index > carry out recording indexes information, index attributes comprises following information:

ASC is that ascending order is arranged, and DESC is descending sort.

3. the cross-platform moving method of data according to claim 1, it is characterized in that, the mapping relations of field between source database described in step 3.2 and target database, adopt the XML file to describe, the XML file is stored in data structure Hashmap, and HashMap has two property value: KEY and VALUE; The character string of KEY storage, for two databases of identification field mapping; What VALUE stored is the preservation position of XML file.

4. according to the cross-platform moving method of the described data of claim 1 or 3, it is characterized in that the packet that SQL statement is packaged into described in step 3.4 is comprised of data packet head and data volume; Data packet head comprises the bag beginning flag of 4 bytes, the protocol version of 4 bytes, the bag size of 2 bytes, and the cyclic redundancy identifying code of 4 bytes; Data volume is for being transferred to the SQL statement of target database place platform.

5. the cross-platform moving apparatus of data, is characterized in that, comprises as lower module: SDI module, target data information module, modular converter, SQLEngine, packet encapsulation, transmission and parsing module, and SQL statement execution module;

It is a SrcDatabase object by the information creating of source data platform that described SDI module is used for; It is a DesDatabase object by the information creating of target data platform that described target data information module is used for; Described SrcDatabase object and DesDatabase object all comprise following information: IP address, database side slogan, user name, password and the database-name of type of database, database place main frame;

Described modular converter is for obtaining a tables of data according to the SrcDatabase object from source database, extract Table object corresponding to data construct from tables of data, again according to the DesDatabase object, utilize the mapping relations of field between source database and target database, the Table object is converted to the table that meets the target data library format;

Described SQLEngine, for according to the DesDatabase object, builds corresponding SQL statement;

Described packet encapsulation, transmission and parsing module are for being packaged into packet by the SQL statement of structure, and transfer to the platform at target database place, and on the platform at target database place, packet is resolved, the SQL statement that parsing is obtained is given the SQL statement execution module;

Described SQL statement execution module, for carrying out SQL statement, inserts the data of source database in target database.

6. the cross-platform moving apparatus of data according to claim 5, it is characterized in that, the mapping relations of field between the source database that described modular converter utilizes and target database, adopt the XML file to describe, the XML file is stored in data structure Hashmap, and HashMap has two property value: KEY and VALUE; The character string of KEY storage, for two databases of identification field mapping; What VALUE stored is the preservation position of XML file.

7. according to the cross-platform moving apparatus of the described data of claim 5 or 6, it is characterized in that the packet that described packet encapsulation, transmission and parsing module are packaged into SQL statement is comprised of data packet head and data volume; Data packet head comprises the bag beginning flag of 4 bytes, the protocol version of 4 bytes, the bag size of 2 bytes, and the cyclic redundancy identifying code of 4 bytes; Data volume is for being transferred to the SQL statement of target database place platform.

Images