A certificate data migration method applied to heterogeneous databases

Abstract

The present invention particularly relates to a certificate data migration method applied to heterogeneous databases. The certificate data migration method applied to heterogeneous databases first establishes a data source connection between heterogeneous databases, and performs field analysis on the data sources between the two heterogeneous databases, extracts the field structure, performs comparative analysis, and identifies potential corresponding relationships, thereby realizing the migration of certificate type data, directory data, and template data; then extracts all migration data index information from the original database and stores it in a data distribution center, and inserts, updates, or deletes the certificate data of the new certificate database according to the migration status of the data to be processed, thereby realizing the migration of detailed certificate data. The certificate data migration method applied to heterogeneous databases makes the data entering the data distribution center accurate and controllable by inputting custom filtering conditions, ensuring that the latest generated certificate data can also be migrated into the database at the end of the migration, thereby ensuring the integrity of the certificate database migration.

Description

Technical Field

[0001] The present invention relates to the technical field of heterogeneous databases and data migration, and in particular to a license data migration method applied to a heterogeneous database. Background Art

[0002] Electronic certificate manufacturers use different certificate management systems, and the ways of storing certificate data vary. Even the systems of the same manufacturer may have different database structures due to the upgrades between versions. At the same time, different types of certificate systems may use proprietary management systems due to different industry standards. There are systems for managing business licenses, systems for managing construction permits, etc. There are great differences in the database table structures for storing certificate data in different electronic certificate systems. In addition, the types of databases used are also different, such as Oracle databases and MySQL databases. In particular, the databases used by various manufacturers in the process of localization transformation are different. When it is necessary to migrate or aggregate certificate data in heterogeneous databases, many problems will be encountered due to the differences in heterogeneous databases. For the migration of multiple types of electronic certificate data, separate docking is required, and the docking process is complicated and time-consuming.

[0003] At present, the migration of certificate data using different database types and table structures mostly adopts the intermediate database mode. That is, an intermediate database is established between two heterogeneous systems. First, one party uses program scheduling or ETL tools to migrate the original data to the intermediate database, and then the other party collects the data into the local database. Another mode is the interface call mode, which pushes the certificate data to the target system through interface docking and the background program enters the data into the database.

[0004] For the intermediate library mode, each type of certificate needs to be connected separately, and both parties need to connect separately according to the corresponding relationship of the database fields. It is only for the migration of a single type of certificate. In addition, this mode only migrates the certificate index and face data. The certificate type data and directory data need to be configured in advance by both databases. It is impossible to complete the full-process one-time migration of certificate types and detailed information for multiple types and multiple certificate directories. For the interface push mode, due to the limitation of server interface load pressure and the influence of network stability, it is not suitable for the migration of large amounts of data, and it also has the limitations of the intermediate library mode.

[0005] Based on the above situation, the present invention proposes a certificate data migration method applied to a heterogeneous database. Summary of the invention

[0006] In order to make up for the defects of the prior art, the present invention provides a simple and efficient license data migration method applied to heterogeneous databases.

[0007] The present invention is achieved through the following technical solutions:

[0008] A method for migrating license data applied to a heterogeneous database, characterized in that it includes the following steps:

[0009] The first step is to migrate the certificate type data, directory data and template data

[0010] Establish data source connection between heterogeneous databases, perform field analysis on the data sources between the two heterogeneous databases through field converters, extract the field structure of the certificate type table and certificate template table of the heterogeneous databases, compare and analyze the extracted fields and identify potential corresponding relationships, and combine manual comparison to input the compared mapping relationships into the field converter again to extract and migrate specific field data;

[0011] Step 2: Migrate detailed data of certificates and licenses

[0012] Enter custom filtering conditions according to the certificate type, and extract all migration data index information from the original database at one time based on the data filtering conditions and store it in the data distribution center. Loop through the identification of the data to be processed from the data distribution center, and insert, update or delete the certificate data in the new certificate database according to the migration status of the data to be processed.

[0013] In the second step, the data of the original database is filtered according to the custom filtering conditions and the system time limit;

[0014] The filtering according to the custom filtering condition refers to filtering the original database index table data by adding keywords (key=value) as filtering conditions for various types of license data;

[0015] The screening based on the system time limit refers to filtering the license data according to the generation time of the license during the migration process based on the extraction intervals obtained at different migration periods, so as to ensure that no data is missed after the entire migration is completed, and at the same time ensure that the latest generated data can also be migrated into the database synchronously, thereby ensuring the timeliness of data migration.

[0016] In the second step, the data distribution center only records the unique ID, migration status and data source of the data to be migrated, the migration status includes I (insert), U (update) and D (delete), and the data source includes physical aggregation and logical aggregation.

[0017] In the second step, the identifier of the data to be processed is taken from the data distribution center, N pieces of data are read first, and the data source to which the data belongs is determined:

[0018] If the data source is physical aggregation, migrate the index data of the certificate from the original certificate database, and query its face and file information according to the certificate identifier, and convert it into the data format required by the certificate entity table of the new certificate database;

[0019] If the data source is logical aggregation, the data is adjusted directly according to the structure of the certificate index of the new certificate database;

[0020] Then, the certificate data in the new certificate database is inserted, updated or deleted according to the migration status of the data to be processed.

[0021] In the second step, the specific implementation steps are as follows:

[0022] 1) Clarify the source of the license index data. If the data source is logical aggregation, first sort out the code of the logical aggregation source in the new license database; if the data source is physical aggregation, first create a license entity table in the new license database and then import the data;

[0023] 2) Record the current system time (SYS_TIME);

[0024] 3) All data information with the license data generation time (Lic_Data.CERATE_TIME) before the current system time (SYS_TIME), including the unique ID, migration status and data source, are stored in the data distribution center;

[0025] 4) Read the first N pieces of data from the data distribution center and determine the data source to which the data belongs;

[0026] 5) Convert the data sent out from the data distribution center, and process the index data, face data and file data according to the migration status;

[0027] 6) Store the processed data into the new license database;

[0028] 7) Delete the imported license data from the data distribution center;

[0029] 8) Repeat steps 4) to 7) until the data distribution center is empty.

[0030] Since it takes a considerable amount of time to process large quantities of data in the data distribution center, the system time at the start of data migration is the current system time (SYS_TIME). In the step 4), if the query result is empty when reading data from the data distribution center, record that SYS_TIME_OLD = SYS_TIME at this time, obtain the latest current system time (SYS_TIME) again as the new system time, query the data between the system time (SYS_TIME_OLD) at the start of the previous migration and the current system time (SYS_TIME), and export it to a temporary table.

[0031] The beneficial effect of the present invention is that the certificate data migration method applied to heterogeneous databases makes the data entering the data distribution center accurate and controllable by inputting custom filtering conditions, ensuring that the latest certificate data generated at the end of migration can also be migrated into the database, thereby ensuring the integrity of the certificate database migration. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0033] Attached Figure 1 It is a schematic diagram of the entire process of data migration of a heterogeneous certificate database of the present invention.

[0034] Attached Figure 2 It is a schematic diagram of the method for migrating certificate type data, directory data and template data of the present invention.

[0035] Attached Figure 3 It is a schematic diagram of the detailed data migration method of the certificate of the present invention.

[0036] Attached Figure 4 This is a schematic diagram of information recording in the data distribution center of the present invention. DETAILED DESCRIPTION

[0037] In order to enable those skilled in the art to better understand the technical solutions in the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.

[0038] Certificate type data is the basis of all certificate data. A certificate database usually has hundreds of certificate types, and different certificate types have different certificate templates. In the certificate database, certificate type data and template data constitute the skeleton of detailed certificate data. The first step in migrating heterogeneous certificate data is to migrate certificate type data, directory data, and template data, and then migrate detailed certificate data.

[0039] The certificate data migration method applied to a heterogeneous database includes the following steps:

[0040] The first step is to migrate the certificate type data, directory data and template data

[0041] Establish data source connection between heterogeneous databases, perform field analysis on the data sources between the two heterogeneous databases through field converters, extract the field structure of the certificate type table and certificate template table of the heterogeneous databases, compare and analyze the extracted fields and identify potential corresponding relationships, and combine manual comparison to input the compared mapping relationships into the field converter again to extract and migrate specific field data;

[0042] The field conversion execution example is as follows:

[0043] A. Conversion of certificate type data:

[0044] Input: O_CERTIFICATE_CODE (original database directory code)

[0045] Output: N_CERTYPE_DTL_ID (new database detail ID)

[0046] B. Conversion of template data:

[0047] Input: O_LICENSE_TYPE_CODE (original database template code)

[0048] Output: N_LICENSE_TYPE_ID (new database template ID)

[0049] C. Conversion of certificate template to physical surface

[0050] Input: O_NAME (the original database field name)

[0051] Output: N_NAME (new database field name)

[0052] Step 2: Migrate detailed data of certificates and licenses

[0053] Enter custom filtering conditions according to the certificate type, and extract all migration data index information from the original database at one time based on the data filtering conditions and store it in the data distribution center. Loop through the identification of the data to be processed from the data distribution center, and insert, update or delete the certificate data in the new certificate database according to the migration status of the data to be processed.

[0054] In the second step, the data of the original database is filtered according to the custom filtering conditions and the system time limit;

[0055] The filtering according to the custom filtering condition refers to filtering the original database index table data by adding keywords (key=value) as filtering conditions for various types of license data;

[0056] The screening based on the system time limit refers to filtering the license data according to the generation time of the license during the migration process based on the extraction intervals obtained at different migration periods, so as to ensure that no data is missed after the entire migration is completed, and at the same time ensure that the latest generated data can also be migrated into the database synchronously, thereby ensuring the timeliness of data migration.

[0057] CERATE_TIME is the time when the certificate data was generated;

[0058] SYS_TIME is the current system time;

[0059] SYS_TIME_OLD is the system time when the pre-migration starts;

[0060]

[0061]

[0062] In the second step, the data distribution center only records the unique ID, migration status and data source of the data to be migrated, the migration status includes I (insert), U (update) and D (delete), and the data source includes physical aggregation and logical aggregation.

[0063] In the second step, the identifier of the data to be processed is taken from the data distribution center, N pieces of data are read first, and the data source to which the data belongs is determined:

[0064] If the data source is physical aggregation, migrate the index data of the certificate from the original certificate database, and query its face and file information according to the certificate identifier, and convert it into the data format required by the certificate entity table of the new certificate database;

[0065] If the data source is logical aggregation, the data is adjusted directly according to the structure of the certificate index of the new certificate database;

[0066] Then, the certificate data in the new certificate database is inserted, updated or deleted according to the migration status of the data to be processed.

[0067] In the second step, the specific implementation steps are as follows:

[0068] 1) Clarify the source of the license index data. If the data source is logical aggregation, first sort out the code of the logical aggregation source in the new license database; if the data source is physical aggregation, first create a license entity table in the new license database and then import the data;

[0069] 2) Record the current system time (SYS_TIME);

[0070] 3) All data information with the license data generation time (Lic_Data.CERATE_TIME) before the current system time (SYS_TIME), including the unique ID, migration status and data source, are stored in the data distribution center;

[0071] 4) Read the first N pieces of data from the data distribution center and determine the data source to which the data belongs;

[0072] 5) Convert the data sent out from the data distribution center, and process the index data, face data and file data according to the migration status;

[0073] 6) Store the processed data into the new license database;

[0074] 7) Delete the imported license data from the data distribution center;

[0075] 8) Repeat steps 4) to 7) until the data distribution center is empty.

[0076] Since it takes a considerable amount of time to process large quantities of data in the data distribution center, the system time at the start of data migration is the current system time (SYS_TIME). In the step 4), if the query result is empty when reading data from the data distribution center, record that SYS_TIME_OLD = SYS_TIME at this time, obtain the latest current system time (SYS_TIME) again as the new system time, query the data between the system time (SYS_TIME_OLD) at the start of the previous migration and the current system time (SYS_TIME), and export it to a temporary table.

[0077] Example 1

[0078] To migrate the data of certificate types A, B, and C in two heterogeneous databases, first migrate the certificate type information. The generated field mapping conversion relationship is as follows

[0079]

[0080]

[0081] After the mapping relationship is generated, the data of the three types A, B, and C can be migrated. The same operation can be used to migrate template data, directory data, and template face data. Then migrate the detailed data of the certificate. First, you need to limit the filtering conditions. For example, if you only want to migrate the certificate data of a certain region under the certificate type, enter REGION_CODE=110000000000. Through data filtering, the certificate identification, data source, and migration status information of the data to be migrated are input into the data distribution center.

[0082] Data distribution center records information, such as Figure 4 As shown. By starting a thread to continuously extract data from the data distribution center, the index information and physical face information of the license are queried from the original license database based on the information obtained from the extraction, and the queried information is used to perform I (insert), U (update), and D (delete) operations on the new database until the data in the data distribution center is empty.

[0083] In summary, the model realizes the full-process migration of three types of certificate data: A, B, and C, directory data, certificate template data, certificate index, and certificate detailed data.

[0084] The embodiment described above is only one specific implementation of the present invention. Common changes and substitutions made by those skilled in the art within the scope of the technical solution of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for migrating license data applied to a heterogeneous database, characterized by: The following steps are involved: The first step is to migrate the certificate type data, directory data and template data Establish data source connection between heterogeneous databases, perform field analysis on the data sources between the two heterogeneous databases through field converters, extract the field structure of the certificate type table and certificate template table of the heterogeneous databases, compare and analyze the extracted fields and identify potential corresponding relationships, and combine manual comparison to input the compared mapping relationships into the field converter again to extract and migrate specific field data; Step 2: Migrate detailed data of certificates and licenses Enter custom filtering conditions according to the certificate type, and extract all migration data index information from the original database at one time according to the data filtering conditions and store it in the data distribution center. Loop through the identification of the data to be processed from the data distribution center, and insert, update or delete the certificate data in the new certificate database according to the migration status of the data to be processed. The data distribution center only records the unique ID, migration status and data source of the data to be migrated. The migration status includes insert, update and delete, and the data source includes physical aggregation and logical aggregation. In the second step, the specific implementation steps are as follows: 1) Clarify the source of the license index data. If the data source is logical aggregation, first sort out the code of the logical aggregation source in the new license database; if the data source is physical aggregation, first create a license entity table in the new license database and then import the data; 2) Record the current system time SYS_TIME; 3) All data information with the license data generation time Lic_Data.CERATE_TIME before the current system time SYS_TIME, including the unique ID, migration status and data source, are stored in the data distribution center; 4) Take out the identifier of the data to be processed from the data distribution center, read N pieces of data first, and determine the data source to which the data belongs; 5) If the data source is physical aggregation, migrate the index data of the certificate from the original database, query its face and file information according to the certificate identifier, and convert it into the data format required by the certificate entity table of the new certificate database; If the data source is logical aggregation, the data is adjusted directly according to the structure of the license index of the new license database; 6) Then, according to the migration status of the data to be processed, the license data in the new license database is inserted, updated or deleted; 7) Delete the imported license data from the data distribution center; 8) Repeat steps 4) to 7) until the data distribution center is empty.

2. The method for migrating license data applied to a heterogeneous database according to claim 1 is characterized in that: In the second step, the data in the original database is filtered according to the custom filtering conditions and the system time limit.

3. The method for migrating license data applied to a heterogeneous database according to claim 2 is characterized in that: The filtering according to the custom filtering conditions refers to filtering the original database index table data by adding keywords as filtering conditions for various types of license data; The screening based on the system time limit refers to filtering the license data according to the generation time of the license during the migration process based on the extraction intervals obtained at different migration periods, so as to ensure that no data is missed after the entire migration is completed, and at the same time ensure that the latest generated data can also be migrated into the database synchronously, thereby ensuring the timeliness of data migration.

4. The method for migrating license data applied to a heterogeneous database according to claim 1 is characterized in that: The system time at the start of data migration is the current system time SYS_TIME. In step 4), if the query result is empty when reading data from the data distribution center, record that SYS_TIME_OLD=SYS_TIME at this time, obtain the latest current system time SYS_TIME again as the new system time, query the license data generation time Lic_Data.CERATE_TIME, and the data between the system time SYS_TIME_OLD at the start of the previous migration and the current system time SYS_TIME, and export it to a temporary table.

Images