A database structure comparison method

Abstract

This invention discloses a database structure comparison method, comprising the following steps: querying and assembling system table structures according to the corresponding database type; ultimately assembling the queried tables, fields, indexes, views, triggers, and other information in a data exchange format; encrypting the queried database structure data of various types to generate a tdhdb file in a specific format; exporting this tdhdb file in text form; decrypting the tdhdb file; and then performing a database comparison. This invention helps on-site database maintenance personnel evaluate the database and facilitates database structure optimization. This invention not only provides modification suggestions but also generates DDL and incremental statements based on the results, saving maintenance personnel a significant amount of time writing these statements. It also facilitates developers in strengthening control over the on-site environment, effectively preventing online incidents, and improving user experience.

Description

Technical Field

[0001] This invention relates to the field of computer database applications, and in particular to a method for comparing database structures. Background Technology

[0002] Software development and maintenance inevitably involve changes to the database structure. As software systems grow larger and deployment environments become more complex, the accuracy of the database structure becomes increasingly critical. In actual project deployments, missing or incorrect database structures frequently occur in production environments, impacting user experience. Furthermore, network conditions make structure comparisons difficult in remote environments. In addition, the diverse database types across different production environments further complicate database structure comparisons. Summary of the Invention

[0003] To address the problems existing in the prior art, this invention provides a database structure comparison method that can compare domestic databases DM (DaMeng) and Kingbase with common databases such as MySQL, Oracle, and Sybase, and supports cross-database type structure, view, index, trigger comparison, and structure mapping relationship settings.

[0004] The objective of this invention is achieved through the following technical solutions.

[0005] A database structure comparison method, comprising the following steps:

[0006] 1) Based on the system table structure query of the corresponding database type, the underlying data tables of the database structure are stored. The table fields are defined by the database system. Finally, the queried table, field, index, view, trigger and other information are assembled in a data exchange format.

[0007] 2) Encrypt the queried database structure data of various types to generate a tdhdb file in a specific format, and export the tdhdb file in text form. The tdhdb file includes the production environment tdhdb file and the R&D version environment tdhdb file.

[0008] 3) Decrypt the production environment tdhdb file and the R&D version environment tdhdb file, and then perform a database comparison, which includes table structure comparison, table field comparison, index comparison, view comparison, and trigger comparison.

[0009] The encryption process in step 2) involves first encrypting using the Aes algorithm, and then encoding using the Base64 method, as shown in the following formula:

[0010] encrypted = Base64(aes(json, key)), where encrypted: the encrypted characters, aes: a standard symmetric encryption algorithm, json: the characters to be encrypted, and key: the key agreed upon for encryption.

[0011] The decryption method in step 2) involves first performing Base64 decoding and then using the AES algorithm for decryption, as shown in the following formula. After decryption, the original JSON format of the database structure is obtained. The JSON format includes table information and view information, where the table information includes fields, indexes, and trigger information.

[0012] decrypted = Aes(Base64(encrypted), key), where decrypted: the decrypted character, aes: a standard symmetric encryption algorithm, encrypted: the decrypted character, and key: the key agreed upon for encryption.

[0013] The table structure comparison is as follows: First, obtain the union of the table structures of the benchmark database and the target database. Then, using the benchmark database table set as the standard, if the target database does not contain the table, it is a missing table in the target database; if the target database contains the table, perform field comparison or index comparison. Tables not accessed in the table union are newly added tables in the target database. Let 't' represent a database table. In this step, the following set is used: Benchmark database table set T. base {t1, t2, ..., t m , t m+1 …, t n}, Target database table set T target {t m , t m+1 …t n , t n+1 …t r The union T of the benchmark and target database tables union {t1, t2…t m , t m+1 …t n , t n+1 …t r The target database is missing a set of tables T compared to the benchmark database. dec For the benchmark database set T base Perform the traversal:

[0014] For table t i (i = 1, 2, ..., m-1): Then t i For tables missing in the target database, t i Add the set T with missing tables dec In the middle, at the same time t i From T union Remove from;

[0015] For table t i (i = m, m+1…n): t i ∈T target , then t i It exists in both the benchmark and target libraries, for table t. i Compare internal fields, or compare table t. i Compare the indexes, and also compare t. i From T union Remove from;

[0016] At this point, the union T union ={t n+1 , t n+2 …t r}, for t i ∈T union , t i Tables added to the target database.

[0017] The comparison of fields within the tables includes field name comparison and field detail information comparison. The field name comparison involves: first, obtaining the union of fields from the base table and the target table. For a field in the base table, if the field does not exist in the target table, it is a missing field in the target table and is removed from the union. If the field exists in the target table, it is also removed from the union. The remaining fields in the union are the newly added fields in the target table. Hereinafter, 'c' represents a database field, and the following set is used in this step: Base table field set C. base {c1, c2, ..., c m c m+1 …, c n}, Target table field set C target {c m c m+1 …c n c n+1 …c r The union of fields in the baseline table and the target table, C. union {c1, c2…c m c m+1 …c n c n+1 …c r The target table is missing the following set of fields compared to the baseline table: C dec ; Traverse the base table field set C base :

[0018] For field c i (i = 1, 2, ..., m-1): Then c i For the missing fields in the target table, add c i Add the missing field set C decIn the middle, at the same time, c i From C union Remove from;

[0019] For field c i (i = m, m+1…n): c i ∈C target Then c i It exists in both the baseline table and the target table, for field c i Compare detailed information, and also include c i From C union Remove from;

[0020] At this point, the union C union ={c n+1 c n+2 …c r}, for c i ∈C union c i Add fields to the target table;

[0021] The detailed field information comparison is as follows: For fields that exist in both the base table and the target table, the field types are first compared. Leveraging the ability of hash mapping to store key-value pairs and retrieve corresponding values ​​via keys, hash mappings F are constructed for different database architectures, using the original field type as the key and a generic type as the value. db Then the original type of fields in different database architectures can be changed through F. ab The data is converted to a generic type, and then compared between these generic types to enable comparison of field types in different databases.

[0022] When the conversion from the original field type to the general type is not unique, based on the large field type and binary type, the standard requirements of CLOB and BLOB are uniformly used according to the principle of convention being greater than configuration; based on the numeric type, if it is an integer and its precision is less than or equal to 2, the corresponding general type is TINYINT; if the precision is greater than 2 and less than or equal to 11, the corresponding general type is SMALLINT; if the precision is greater than 11, the corresponding general type is BIGINT; if it is a decimal, DECIMAL is uniformly used as the general field.

[0023] If the general types are the same, the two fields are considered to be of the same type. On this basis, the precision, decimal places, default value, whether it is a primary key, and whether it is not null are compared. If there are field types with no mapping relationship, no conversion is performed for the time being, and they are directly exported as differences.

[0024] The index comparison process uses the fields involved in the indexes as the standard for determining whether two indexes are the same. During the comparison, the union of the indexes of the base table and the target table is first obtained. For an index in the base table, if the target table does not have that index, it is considered a missing index in the target table; if the target table has the index, its uniqueness is compared. Unaccessed indexes in the remaining union are considered newly added indexes in the target table. Hereinafter, 'd' represents a database index, and the following set is used in this step: Base table index set D. base {d1, d2, ..., d m d m+1 …,d n}、Target table index set D target {d m d m+1 …d n d n+1 …d r The union D of the indexes of the base table and the target table. union {d1, d2…d m d m+1 …d n d n+1 …d r The target table lacks the following set of indexes, D, compared to the baseline table. dec Traverse the base library index set D base :

[0025] For index d i (i = 1, 2, ..., m-1): Then d i For the missing fields in the target table, d i Add the missing field set D dec In the middle, at the same time d i From D union Removed from the middle.

[0026] For index d i (i = m, m+1…n): d i ∈D target , then d i If the index exists in both the base table and the target table, continue comparing whether the index is unique, and also set d i From D union Removed from the middle.

[0027] At this point, the union D union ={d n+1 d n+2 …d r}, for d i ∈D union d i An index added to the target table.

[0028] The view comparison process involves: first, obtaining the union of views from the baseline database and the target database; then, using the baseline database view set as the benchmark, if a view is missing from the target database, it is removed from the union; if it exists in the target database, it is also removed. The remaining views in the final view union are the newly added views from the target database. Hereinafter, 'v' represents a database view, and the following set is used in this step: Baseline database view set V. base {v1, v2, ..., v m v m+1 …, v n}, Target library view collection V target {v m v m+1 …v n v n+1 …v r The union of the baseline library and the target library views, V union {v1, v2…v m v m+1 …v n v n+1 …v r The target library lacks the set of views V compared to the baseline library. dec Traverse the base library view set V base :

[0029] For view v i (i = 1, 2, ..., m-1): Then v i For views missing from the target library, v i Add missing view collection V dec In the middle, at the same time v i From V union Removed from the middle.

[0030] For view v i (i = m, m+1…n): v i ∈V target Then v i It exists in both the benchmark and target libraries, and v i From V union Removed from the middle.

[0031] At this point, the union V union ={v n+1 v n+2 …v r}, for v i ∈V union v i A view added to the target library.

[0032] The trigger comparison process is as follows: First, obtain the triggers from the baseline and target database views. Then, using the baseline database trigger set as the benchmark, if the trigger does not exist in the target database, it is a missing trigger in the target database and is removed from the union set. If the trigger exists in the target database, it is also removed from the union set. The remaining triggers in the final trigger union set are the newly added triggers in the target database. Hereinafter, 'tr' represents a database table. In this step, the following set is used: Baseline Database Trigger Set TR base {tr1, tr2, ..., tr m tr m+1 …, tr n}, Target Library Trigger Set TR arget {tr m tr m+1 …tr n tr n+1 …tr r The union of the base library and the target library triggers (TR) union {tr1, tr2…tr m tr m+1 …tr n tr n+1 …tr r The target library lacks the set of triggers TRs compared to the benchmark library. dec Traverse the base library trigger set TR base :

[0033] For trigger tr i (i = 1, 2, ..., m-1): Then tr i For the missing triggers in the target library, set tr i Add missing trigger set TR dec In the middle, at the same time, tr i From TR union Removed from the middle.

[0034] For trigger tr i (i = m, m+1…n): tr i ∈TR target , then tr i It exists in both the benchmark and target libraries, and will be displayed in the tr library. i From TR union Removed from the middle.

[0035] At this time, the union TR union ={tr n+1 tr n+2 …tr r}, for tr i ∈TR union tr i A trigger added to the target library.

[0036] Compared to existing technologies, the advantages of this invention are as follows: This invention clearly and comprehensively explains the differences between the baseline and target databases, listing redundant, missing, or non-compliant database structures, helping on-site database maintenance personnel evaluate the database and facilitating database structure optimization. This invention not only provides modification suggestions but also generates DDL and incremental statements based on the results, saving maintenance personnel a significant amount of time writing these statements. It also allows R&D personnel to better control the on-site environment, effectively preventing online incidents and improving user experience.

[0037] This method reads the database system table structure of the R&D, testing, or production environment, exports it using a fixed encrypted format, transforms and compares it according to certain rules, and finally generates a database structure comparison report. This allows us to understand the differences between the production environment and the R&D version environment, laying the foundation for establishing a more accurate software runtime library structure and strengthening the control over the structure of production environments in various regions. Attached Figure Description

[0038] Figure 1 This is a schematic diagram of the process of the present invention.

[0039] Figure 2 This is a schematic diagram of the JSON data structure of this invention.

[0040] Figure 3 This is a basic setting table for the field type mapping relationship of this invention. Detailed Implementation

[0041] The present invention will now be described in detail with reference to the accompanying drawings and specific embodiments.

[0042] like Figure 1 As shown, a database structure comparison method includes the following steps:

[0043] 1) Based on the system table structure query of the corresponding database type, the underlying data tables of the database structure are stored. The table fields are defined by the database system. Finally, the queried table, field, index, view, trigger and other information are assembled in a data exchange format.

[0044] 2) Encrypt the queried database structure data of various types to generate a tdhdb file in a specific format, and export the tdhdb file in text form. The tdhdb file includes the production environment tdhdb file and the R&D version environment tdhdb file.

[0045] 3) Decrypt the production environment tdhdb file and the R&D version environment tdhdb file, and then perform a database comparison, which includes table structure comparison, table field comparison, index comparison, view comparison, and trigger comparison.

[0046] (I) Data Structure Export

[0047] 1. Database system table query and data assembly

[0048] This invention supports the export of data tables, fields, indexes, views, and triggers from MySQL, Oracle, Sybase, Kingbase, and DM databases. Since the underlying structures of different databases are different, this method queries and assembles data based on the system table structure of the corresponding database type (the underlying data tables that store the database structure, and the table fields are defined by the database system).

[0049] -MySQL system tables:

[0050] information_schema.tables, statistics, views, triggers, key_column_usage, table_constraints

[0051] -Oracle system tables:

[0052] user_tables, user_ind_columns, user_indexes, user_views, all_triggers, user_constraints, user_tab_columns, user_tab_comments

[0053] -Sybase system tables:

[0054] sysindexes, sysobjects, syscomments, syscolumns, systypes

[0055] -DaMeng System Table:

[0056] dba_tables, dba_ind_columns, dba_views, dba_triggers, all_constraints, all_cons_columns, user_tab_columns, user_col_comments, all_cons_columns, user_tab_comments

[0057] -Golden Storage System Table:

[0058] all_tab_columns, sys_trigger, sys_class, user_tables, user_views, sys_index

[0059] Finally, the retrieved information, including tables, fields, indexes, views, and triggers, is assembled in JSON format (a data exchange format).

[0060] 2. Encryption and Export

[0061] The queried database structure data of various types is encrypted to generate a file in a specific format, tdhdb (the file extension of the encrypted file in this invention). The encryption method involves first encrypting using the Aes algorithm, and then encoding using Base64 (a method for representing binary data based on 64 printable characters), as shown below:

[0062] encrypted = Base64(aes(jcon, key))(encrypted: the encrypted string, aes: a standard symmetric encryption algorithm, json: the string to be encrypted, key: the key used for encryption)

[0063] Export this tdhdb file as text after encryption.

[0064] (II) Database Comparison

[0065] The tdhdb files exported from the target library and the benchmark library are decrypted separately. The decryption method is to first perform Base64 decoding and then use the AES algorithm for decryption, as shown in the following formula. After decryption, the original JSON format of the database structure is obtained. The JSON format includes information such as... Figure 2 As shown. It mainly includes table and view information. Table information includes fields, indexes, and trigger information.

[0066] decvypted = Aes(Base64(encvypted), key)(decrypted: the decrypted string, aes: a standard symmetric encryption algorithm, encrypted: the decrypted string, key: the key used for encryption)

[0067] 1. Table Structure Comparison

[0068] First, obtain the union of the table structures of the benchmark database and the target database. Then, using the benchmark database table set as the benchmark, if the target database does not contain the table, it is a missing table in the target database; if the target database contains the table, field comparison and index comparison can be performed. Tables not accessed in the table union are newly added tables in the target database. Let 't' represent a database table. In this step, the following set is used: Benchmark database table set T. base{t1, t2, ..., t m , t m+1 …, t n}, Target database table set T target {t m , t m+1 …t n , t n+1 …t r The union T of the benchmark and target database tables union {t1, t2…t m , t m+1 …t n , t n+1 …t r The target database is missing a set of tables T compared to the benchmark database. dec For the baseline database set T base Perform the traversal:

[0069] a) For table t i (i = 1, 2, ..., m-1): Then t i For tables missing in the target database, t i Add the set T with missing tables dec In the middle, at the same time t i From T union Removed from the middle.

[0070] b) For table t i (i = m, m+1…n): t i ∈T target , then t i It exists in both the benchmark and target libraries; proceed to step 3 for table t. i The internal fields are compared, and step 5 is performed on table t. i Compare the indexes, and also compare t. i From T union Removed from the middle.

[0071] c) At this point, the union T union ={t n+1 , t n+2 …t r}, for t i ∈T union , t i Tables added to the target database.

[0072] 2. Comparison of fields within the table

[0073] a) Field name comparison

[0074] First, obtain the union of the fields from the base table and the target table. For a field in the base table, if the field does not exist in the target table, it is a missing field in the target table, and this field is removed from the union. If the field exists in the target table, it is also removed from the union. The remaining fields in the field union are the newly added fields in the target table. Let 'c' represent a database field. In this step, the following set is used: Base table field set C base {c1, c2, ..., c m c m+1 …, c n}, Target table field set C target {c m c m+1 …c n c n+1 …c r The union of fields in the baseline table and the target table, C. union {c1, c2…c m c m+1 …c n c n+1 …c r The target table is missing the following set of fields compared to the baseline table: C dec Traverse the base table field set C base :

[0075] ① For field c i (i = 1, 2, ..., m-1): Then c i For the missing fields in the target table, add c i Add the missing field set C dec In the middle, at the same time, c i From C union Removed from the middle.

[0076] ② For field c i (i = m, m+1…n): c i ∈C target Then c i It exists in both the baseline table and the target table, for field c i Compare detailed information, and also include c i From C union Removed from the middle.

[0077] ③ At this time, the union of C union ={c n+1 c n+2 …c r}, for c i ∈C union c i Add fields to the target table.

[0078] b) Comparison of detailed field information

[0079] For fields that exist in both the baseline and target tables, the first step is to compare their types. Existing technologies mostly only allow comparison of fields within the same database architecture and do not support some domestically developed databases. The innovation of this invention lies in introducing the concept of a "universal type" as a bridge for comparing field types across different database architectures. This comparison is achieved by converting the universal types of databases such as MySQL, Oracle, Sybase, Kingbase, and DM. This invention utilizes the characteristic of HashMap (hash mapping) to store key-value pairs and retrieve corresponding values ​​by keys. Using the original field type as the key and the universal type as the value, corresponding hash maps F are constructed for different database architectures. db Then the original type of fields in different database architectures can be changed through F. ab The data is converted into a generic type, and then compared to achieve the comparison of field types in different types of databases.

[0080] However, the conversion from the original field type to the general type is not unique; multiple types may correspond to the same type. This occurs with numeric types (NUMBER), large field types (CLOB), and binary types (BLOB). This invention solves this problem by determining a unique general type based on constraints such as field precision, decimal places, and the data characteristics of the field itself. The rules are as follows: For large field types and binary types, CLOB and BLOB are uniformly used according to the convention greater than configuration principle; for numeric types, if it is an integer and its precision is less than or equal to 2, the corresponding general type is TINYINT; if the precision is greater than 2 and less than or equal to 11, the corresponding general type is SMALLINT; if the precision is greater than 11, the corresponding general type is BIGINT; if it is a decimal, DECIMAL is uniformly used as the general field.

[0081] Since the DM database has time-type data that requires specifying precision and decimal places when creating or modifying, a HashMap is used to establish a mapping between general types and whether precision or decimal places are required. When generating, it is determined whether the current type needs to be concatenated with precision or decimal places. Special types are as follows: DATE(13), TIMESTAMP(36,6).

[0082] Basic settings for field type mapping relationships, such as Figure 3 The diagram shows mappings for mainstream databases such as MySQL, Oracle, Sybase, Kingbase, and DM, which can be customized for specific scenarios.

[0083] If the general types are the same, then this invention considers the two fields to be of the same type. Based on this, the precision, decimal places, default values, whether it is a primary key, and whether it is NOT null are compared. If there are field types with no mapping relationship, this invention does not perform conversion processing and directly exports them as differences.

[0084] 3. Index Comparison

[0085] In this method, the fields involved in the index are used as the standard for determining whether two indexes are the same index. When comparing indexes, the union of the indexes of the base table and the target table is first obtained. For an index in the base table, if the target table does not have that index, it is a missing index in the target table; if the target table has that index, its uniqueness can be compared. Unaccessed indexes in the remaining union are considered newly added indexes in the target table. Let 'd' represent the database indexes. In this step, the following set is used: Base table index set D. base {d1, d2, ..., d m d m+1 …,d n}、Target table index set D target {d m d m+1 …d n d n+1 …d r The union D of the indexes of the base table and the target table. union {d1, d2…d m d m+1 …d n d n+1 …d r The target table lacks the following set of indexes, D, compared to the baseline table. dec Traverse the base library index set D base :

[0086] a) For index d i (i = 1, 2, ..., m-1): Then d i For the missing fields in the target table, d i Add the missing field set D dec In the middle, at the same time d i From D union Removed from the middle.

[0087] b) For index d i (i = m, m+1…n): d i ∈D target , then d i If the index exists in both the base table and the target table, continue comparing whether the index is unique, and also set d i From D union Removed from the middle.

[0088] c) At this point, the union D union ={d n+1 d n+2 …d r}, for d i ∈D union d i An index added to the target table.

[0089] 4. View Comparison

[0090] First, obtain the union of the base database and target database views. Then, using the base database view set as the benchmark, if the view does not exist in the target database, it is a missing view in the target database, and the view is removed from the union. If the view exists in the target database, it is also removed from the union. The remaining views in the final view union are the newly added views in the target database. Let 'v' represent a database view. In this step, the following set is used: Base database view set V base {v1, v2, ..., v m v m+1 …, v n}, Target library view collection V target {v m v m+1 …v n v n+1 …v r The union of the baseline library and the target library views, V union {v1, v2…v m v m+1 …v n v n+1 …v r The target library lacks the set of views V compared to the baseline library. dec Traverse the base library view collection V base :

[0091] a) For view v i (i = 1, 2, ..., m-1): Then v i For views missing from the target library, v i Add missing view collection V dec In the middle, at the same time v i From V union Removed from the middle.

[0092] b) For view v i (i = m, m+1…n): v i ∈V target Then v i It exists in both the benchmark and target libraries, and v i From V union Removed from the middle.

[0093] c) At this point, the union V union ={v n+1 v n+2 …v r}, for v i ∈V union v i A view added to the target library.

[0094] 5. Trigger Comparison

[0095] First, obtain the triggers from the baseline and target database views. Then, using the baseline database trigger set as a reference, if the trigger does not exist in the target database, it is a missing trigger in the target database, and the trigger is removed from the union set. If the trigger exists in the target database, it is also removed from the union set. The remaining triggers in the final trigger union set are the newly added triggers from the target database. Below, 'tr' represents the database table, and the following set is used in this step: Baseline Database Trigger Set TR base {tr1, tr2, ..., tr m tr m+1 …, tr n}, Target Library Trigger Set TR arget {tr m tr m+1 …tr n tr n+1 …tr r The union of the base library and the target library triggers (TR) union {tr1, tr2…tr m tr m+1 …tr n tr n+1 …tr r The target library lacks the set of triggers TRs compared to the benchmark library. dec Traverse the base library trigger set TR base :

[0096] a) For trigger tr i (i = 1, 2, ..., m-1): Then tr i For the missing triggers in the target library, set tr i Add missing trigger set TR dec In the middle, at the same time, tr i From TR union Removed from the middle.

[0097] b) For trigger tr i (i = m, m+1…n): tr i ∈TR target , then tr iIt exists in both the benchmark and target libraries, and will be displayed in the tr library. i From TR union Removed from the middle.

[0098] c) At this time, the union TR union ={tr n+1 tr n+2 …tr r}, for tr i ∈TR union tr i A trigger added to the target library.

[0099] 6. Generate a comparison report

[0100] The report includes the following comparison results: multiple tables, fewer tables; multiple fields, fewer fields, inconsistent fields; multiple indexes, fewer indexes, inconsistent indexes; multiple views, fewer views, inconsistent views; multiple triggers, fewer triggers, inconsistent triggers.

[0101] Based on the severity of these comparison results, they are divided into the following four levels:

[0102] a) Issues: Missing tables, missing fields, inconsistent fields, missing indexes, inconsistent indexed fields. These issues are highly likely to affect the normal operation of the project after deployment and need to be addressed promptly.

[0103] b) Verification Items: Multiple tables, multiple fields, multiple indexes. Results at this level may be due to modifications made to the table structure during development that have not yet been released; therefore, verification is needed to determine if this is a problematic item.

[0104] c) Warning: Inconsistent Index Names. While this won't affect the project's normal operation, it increases maintenance costs. Some databases randomly assign index names when creating indexes if no name is specified. These index names are meaningless, requiring you to check their associated fields to understand their purpose during maintenance. Furthermore, duplicate indexes are prone to occur, significantly increasing maintenance costs when there are many. Therefore, this method specifies the following for indexes: the index naming format is "IDX_table_name<_field_name>", where at least one element is enclosed in angle brackets; if the index name length exceeds 30 characters, the index naming format is "IDX_table_name<_first letter of_field_name>", where at least one element is enclosed in angle brackets; all letters in the index name must be uppercase.

[0105] d) Reference items: Multiple views, few views, inconsistent views, multiple triggers, few triggers, inconsistent triggers. The results of this level are not included in the problem items, but are provided for maintenance personnel's reference.

[0106] To facilitate database repair by maintenance personnel, this invention also automatically generates DDL (SQL statements for defining table structures) and incremental statements (SQL statements for modifying existing table structures) for the comparison results other than the reference items, based on the benchmark database. The implementation method is as follows: commonly used SQL statements for different database architectures are written into Velocity (a Java-based template engine) templates, and corresponding SQL statements are generated according to the type of comparison results. During the statement generation process, [the following is considered]: Figure 3 The system dynamically determines whether different data types include precision and decimal place declarations, thereby ensuring that the generated DDL statements conform to the execution specifications of various databases.

[0107] The comparison report generated through the above steps clearly and in detail illustrates the differences between the benchmark and target databases, listing redundant, missing, or non-compliant database structures. This helps on-site database maintenance personnel evaluate the database and facilitates database structure optimization. This invention not only provides modification suggestions but also generates DDL and incremental statements based on the results, saving maintenance personnel significant time in writing these statements. It also allows R&D personnel to better control the on-site environment, effectively preventing online incidents and improving user experience.

Claims

1. A database structure comparison method, characterized in that... The steps include: 1) Based on the system table structure query of the corresponding database type, the underlying data tables of the database structure are stored. The table fields are defined by the database system. Finally, the queried table, field, index, view, and trigger information are assembled in a data exchange format. 2) Encrypt the queried database structure data of various types to generate a tdhdb file in a specific format, and export the tdhdb file in text form. The tdhdb file includes the production environment tdhdb file and the R&D version environment tdhdb file. 3) Decrypt the production environment tdhdb file and the R&D version environment tdhdb file, and then perform a database comparison, which includes table structure comparison, table field comparison, index comparison, view comparison, and trigger comparison. The comparison of fields within the tables includes field name comparison and field detail information comparison. The field name comparison involves: first, obtaining the union of fields from the base table and the target table. For a field in the base table, if the field does not exist in the target table, it is a missing field in the target table and is removed from the union. If the field exists in the target table, it is also removed from the union. The remaining fields in the union are the newly added fields in the target table. Hereinafter, 'c' represents a database field, and the following set is used in this step: Base table field set C. base {c1, c2, ..., c m c m+1 c n }, Target table field set C target {c m c m+1 c n c n+1 c r The union of fields in the baseline table and the target table, C. union {c1, c2, ..., c m c m+1 c n c n+1 c r The target table is missing the following set of fields compared to the baseline table: C dec ; Traverse the base table field set C base : For field c i (i = 1, 2, ..., m-1): Then c i For the missing fields in the target table, add c i Add the missing field set C dec In the middle, at the same time, c i From C union Remove from; For field c i (i = m, m+1, ..., n): c i ∈C target Then ci exists in both the baseline table and the target table, for field c i Compare detailed information, and also include c i From C union Remove from; At this point, the union C union ={c n+1 c n+2 c r }, for c i ∈C union c i Add fields to the target table; The detailed field information comparison is as follows: For fields that exist in both the baseline and target tables, the field types are first compared. Leveraging the ability of hash mapping to store key-value pairs and retrieve corresponding values ​​via keys, hash mappings (Fdb) are constructed for different database architectures, using the original field type as the key and a generic type as the value. The original field types in different database architectures are then converted to generic types using Fab, and the generic types are compared to achieve the comparison of field types across different database types. When the conversion from the original field type to the general type is not unique, based on the large field type and binary type, the standard requirements of CLOB and BLOB are uniformly used according to the principle of convention being greater than configuration; based on the numeric type, if it is an integer and its precision is less than or equal to 2, the corresponding general type is TINYINT; if the precision is greater than 2 and less than or equal to 11, the corresponding general type is SMALLINT; if the precision is greater than 11, the corresponding general type is BIGINT; if it is a decimal, DECIMAL is uniformly used as the general field. If the general types are the same, the two fields are considered to be of the same type. On this basis, the precision, decimal places, default value, whether it is a primary key, and whether it is not null are compared. If there are field types with no mapping relationship, no conversion is performed for the time being, and they are directly exported as differences.

2. The database structure comparison method according to claim 1, characterized in that... The encryption process in step 2) involves first encrypting using the Aes algorithm, and then encoding using the Base64 method, as shown in the following formula: encrypted = Base64(aes(json, key)), where encrypted: the encrypted characters, aes: a standard symmetric encryption algorithm, json: the characters to be encrypted, and key: the key agreed upon for encryption.

3. The database structure comparison method according to claim 2, characterized in that... The decryption method in step 2) involves first performing Base64 decoding and then using the AES algorithm for decryption, as shown in the following formula. After decryption, the original JSON format of the database structure is obtained. The JSON format includes table information and view information, where the table information includes fields, indexes, and trigger information. decrypted = Aes(Base64(encrypted), key), where decrypted: the decrypted character, aes: a standard symmetric encryption algorithm, encrypted: the decrypted character, and key: the key agreed upon for encryption.

4. The database structure comparison method according to claim 1, characterized in that... The table structure comparison is as follows: First, obtain the union of the table structures of the benchmark library and the target library. Then, take the table set of the benchmark library as the standard. If there is no table in the union of the target library, it is a table that is missing from the target library. If the target database has tables in the union set, perform field comparison or index comparison; tables not accessed in the table union set are newly added tables in the target database; let t represent a database table, and in this step, use the following set: the set of base database tables T. base {t1, t2, ..., t m , t m+1 , ..., t n }, Target database table set T target {t m , t m+1 , ..., t n , t n+1 , ..., t r The union T of the benchmark and target database tables union {t1, t2, ..., t m , t m+1 , ..., t n , t n+1 , ..., t r The target database is missing a set of tables T compared to the benchmark database. dec For the benchmark database set T base Perform the traversal: For table t i (i = 1, 2, ..., m-1): Then t i For tables missing in the target database, t i Add the set T with missing tables dec In the middle, at the same time t i From T union Remove from; For table t i (i = m, m+1, ..., n): t i ∈T target , then t i It exists in both the benchmark and target libraries, for table t. i Compare internal fields, or compare table t. i Compare the indexes, and also compare t. i From T union Remove from; At this point, the union T union ={t n+1 , t n+2 , ..., t r }, for t i ∈T union , t i Tables added to the target database.

5. The database structure comparison method according to claim 1, characterized in that... The index comparison process uses the fields involved in the indexes as the standard for determining whether two indexes are the same. During the comparison, the union of the indexes of the base table and the target table is first obtained. For an index in the base table, if the target table does not have that index, it is considered a missing index in the target table; if the target table has the index, its uniqueness is compared. Unaccessed indexes in the remaining union are considered newly added indexes in the target table. Hereinafter, 'd' represents a database index, and the following set is used in this step: Base table index set D. base {d1, d2, ..., d m d m+1 , ..., d n }、Target table index set D target {d m d m+1 , ..., d n d n+1 , ..., d r The union D of the indexes of the base table and the target table. union {d1, d2, ..., d m d m+1 , ..., d n d n+1 , ..., d r The target table lacks the following set of indexes, D, compared to the baseline table. dec Traverse the base library index set D base : For index d i (i = 1, 2, ..., m-1): Then d i For the missing fields in the target table, d i Add the missing field set D dec In the middle, at the same time d i From D union Removed from the middle For index d i (i = m, m+1, ..., n): d i ∈D target , then d i If the index exists in both the base table and the target table, continue comparing whether the index is unique, and also set d i From D union Removed from the middle At this point, the union D union ={d n+1 d n+2 , ..., d r }, for d i ∈D union d i An index added to the target table.

6. The database structure comparison method according to claim 1, characterized in that... The view comparison is as follows: First, the union of the views in the benchmark library and the target library is obtained. Then, based on the set of views in the benchmark library, if the view does not exist in the target library, it is a missing view in the target library, and the view is removed from the union. If the view exists in the target database, remove it from the union set; the remaining views in the final view union set are the views newly added to the target database; below, v represents the database view, and the following set is used in this step: Baseline database view set V base {v1, v2, ..., v m v m+1 , ..., v n }, Target library view collection V target {v m v m+1 , ..., v n v n+1 , ..., v r The union of the baseline library and the target library views, V union {v1, v2, ..., v m v m+1 , ..., v n v n+1 , ..., v r The target library lacks the set of views V compared to the baseline library. dec Traverse the base library view set V base : For view v i (i = 1, 2, ..., m-1): Then v i For views missing from the target library, v i Add missing view collection V dec In the middle, at the same time v i From V union Removed from the middle For view v i (i = m, m+1, ..., n): v i ∈V target Then v i It exists in both the benchmark and target libraries, and v i From V union Removed from the middle At this point, the union V union ={v n+1 v n+2 , ..., v r }, for v i ∈V union v i A view added to the target library.

7. A database structure comparison method according to claim 1, characterized in that... The trigger comparison is as follows: First, the triggers in the baseline library and the target library view are obtained. Then, based on the baseline library trigger set, if the trigger does not exist in the target library, it is a missing trigger in the target library, and the trigger is removed from the union set. If the trigger exists in the target database, remove it from the union set. The remaining triggers in the final trigger union set are the newly added triggers from the target database. Hereinafter, 'tr' represents the database table. In this step, the following set is used: the base database trigger set TR. base {tr1, tr2, ..., tr m tr m+1 , ...,tr n }, Target Library Trigger Set TR arget {tr m tr m+1 , ...,tr n tr n+1 , ...,tr r The union of the base library and target library triggers TR union {tr1, tr2, ..., tr m tr m+1 , ...,tr n tr n+1 , ...,tr r The target library lacks the set of triggers TRs compared to the benchmark library. dec Traverse the base library trigger set TR base : For trigger tr i (i = 1, 2, ..., m-1): Then tr i For the missing triggers in the target library, set tr i Add missing trigger set TR dec In the middle, at the same time, tr i From TR union Removed from the middle For trigger tr i (i = m, m+1, ..., n): tr i ∈TR target , then tr i It exists in both the benchmark and target libraries, and will be displayed in the tr library. i From TR union Removed from the middle At this time, the union TR union ={tr n+1 tr n+2 , ...,tr r }, for tr i ∈TR union tr i A trigger added to the target library.

Images