Metadata version comparison method and device of relational database and computer equipment

Abstract

The invention discloses a metadata version comparison method and device for a relational database and computer equipment, and the method comprises the steps: storing data needing to be compared into a temporary table, and generating a batch number; under the condition that no difference exists in the table-level data, whether difference exists in the field-level data or not is judged according to the batch number and the table identification, and the step of judging whether difference exists in the field-level data or not comprises the substeps that a field-level temporary table and a field-level process table are associated and grouped, and 5 values of message digests of the groups are compared; and / or comparing an array of the field-level temporary table with an array of the field-level process table. According to the scheme, the metadata version comparison efficiency of the relational database is effectively improved.

Description

Technical Field

[0001] This invention relates to the field of database management technology, and more specifically, to a method, apparatus, and computer device for comparing metadata versions of a relational database. Background Technology

[0002] With the rapid development of information technology, relational databases have become an indispensable part of enterprise information systems. In the database management process, version comparison of metadata (data describing data) is a fundamental and crucial task, significant for database migration, upgrades, and maintenance.

[0003] Currently, metadata comparison of relational databases typically uses a full, row-by-row comparison method, which comprehensively compares the table names, table structures, column information (e.g., name, length, comments, etc.), and row data of the source and target databases one by one, directly identifying the differences. Summary of the Invention

[0004] The inventors discovered that traditional full-database, row-by-row comparison methods are not only time-consuming but also consume significant computational resources in both the source and target databases when dealing with large databases containing numerous tables and fields. Therefore, how to efficiently perform version comparison of relational database metadata has become a pressing issue.

[0005] To address at least one of the aforementioned technical problems, this invention proposes a method, apparatus, and computer device for comparing metadata versions in relational databases. In this method, the data to be compared is stored in a temporary table, and field-level data comparison is performed when there are no differences in the table-level data. This avoids the waste of resources from full-scale in-depth field comparison and improves the efficiency of metadata version comparison in relational databases.

[0006] One aspect of the present invention provides a method for comparing metadata versions of a relational database, the method comprising:

[0007] Store the data to be compared in a temporary table and generate batch numbers; and

[0008] If there are no differences in the table-level data, determine whether there are differences in the field-level data based on the batch number and table identifier.

[0009] Determining whether there are differences in field-level data includes:

[0010] Join and group field-level temporary tables with field-level procedure tables, and compare the message digest 5 values ​​of each group; and / or

[0011] Compare arrays of field-level temporary tables and field-level procedure tables.

[0012] Optionally, determining whether there are differences in field-level data includes:

[0013] Filter field-level temporary table data based on the batch number and the table identifier;

[0014] The field-level temporary table and the field-level procedure table are associated through the table identifier and then grouped according to the table identifier;

[0015] For each group of field-level temporary tables and field-level process tables, the fields to be matched are concatenated respectively;

[0016] Calculate the 5-value of the message digest after concatenation for each group; and

[0017] Compare the message digest 5 values ​​of each group.

[0018] Optionally, determining whether there are differences in field-level data includes:

[0019] Grouping according to the table identifier, serializing each group of fields to be matched into an array in a predetermined order, and comparing the array of the field-level temporary table with the array of the field-level process table.

[0020] Optionally, the field-level procedure table includes a field number, a data item number, a primary key identifier, and a table identifier field, wherein the field number is the primary key of the field-level procedure table.

[0021] The field-level temporary table includes the fields of the field-level process table, as well as the version number, table import result identifier, and batch number fields.

[0022] Optionally, determining whether there are differences in table-level data includes:

[0023] Filter table-level temporary table data according to the batch number;

[0024] Perform a joint query on the table-level temporary table and the table-level procedure table using the table identifier;

[0025] Concatenate the table-level fields that need to be matched in the query results;

[0026] Calculate the message digest 5 value of the concatenated table-level temporary table and the table-level procedure table respectively;

[0027] Compare the message digest 5 values ​​corresponding to the table-level temporary table and the table-level procedure table.

[0028] Optionally, determining whether there are differences in table-level data includes:

[0029] The fields to be matched in the table-level temporary table and the table-level procedure table are compared value by value.

[0030] Optionally, the table identifier is the primary key of the table-level procedure table.

[0031] The table-level temporary table includes the fields of the table-level process table, as well as the table import result identifier and batch number fields.

[0032] Optionally, the table-level fields and field-level fields to be matched can be selected according to the Structured Query Language configuration.

[0033] Optionally, the sources of the metadata include web page operations and / or online services and / or table imports.

[0034] The result identifier of the table import is used to filter metadata imported normally.

[0035] In another aspect, the present invention provides a comparison apparatus for metadata versions of a relational database, the comparison apparatus comprising:

[0036] The data storage unit stores the data to be compared into a temporary table and generates batch numbers; and

[0037] The field-level data comparison unit, in the absence of differences in table-level data, determines whether differences exist in field-level data based on the batch number and table identifier.

[0038] Determining whether there are differences in field-level data includes:

[0039] Join and group field-level temporary tables with field-level procedure tables, and compare the message digest 5 values ​​of each group; and / or

[0040] Compare arrays of field-level temporary tables and field-level procedure tables.

[0041] To achieve the above objectives, according to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the method for comparing metadata versions of the relational database described above.

[0042] To achieve the above objectives, according to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer program / instructions are stored, which, when executed by a processor, implement the steps of the method for comparing metadata versions of the relational database described above.

[0043] To achieve the above objectives, according to another aspect of the present invention, a computer program product is also provided, comprising a computer program / instruction that, when executed by a processor, implements the steps of the method for comparing metadata versions of the relational database described above.

[0044] The beneficial effects of this invention are as follows: This invention stores the data to be compared in a temporary table, and performs field-level data comparison when there are no differences in the table-level data, thus avoiding the waste of resources in full-scale in-depth field comparison and improving the efficiency of metadata version comparison in relational databases. Attached Figure Description

[0045] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings:

[0046] Figure 1 This is a flowchart of a method for comparing metadata versions of a relational database according to an embodiment of the present invention;

[0047] Figure 2 This is a flowchart illustrating the process of determining whether there are differences in table-level data according to an embodiment of the present invention;

[0048] Figure 3 This is a flowchart illustrating the process of determining whether there are differences in field-level data according to an embodiment of the present invention;

[0049] Figure 4 This is a flowchart illustrating the comparison of metadata versions in a relational database according to an embodiment of the present invention.

[0050] Figure 5 This is a structural block diagram of a relational database metadata version comparison device according to an embodiment of the present invention;

[0051] Figure 6 This is a schematic diagram of the structure of a computer device according to an embodiment of the present invention. Detailed Implementation

[0052] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0053] It should be noted that the terms "comprising" and "having" and any variations thereof in the specification, claims and accompanying drawings of this invention are intended to cover non-exclusive inclusion. For example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to such processes, methods, products or devices.

[0054] It should be noted that, unless otherwise specified, the embodiments and features described in the present invention can be combined with each other. The present invention will now be described in detail with reference to the accompanying drawings and embodiments.

[0055] It should be noted that the information collected in the technical solution of this application is information and data authorized by the user or fully authorized by all parties, and the collection, storage, use, processing, transmission, provision, disclosure and application of the relevant data all comply with the relevant laws, regulations and standards of the relevant countries and regions, necessary confidentiality measures have been taken, and they do not violate public order and good morals. Corresponding operation portals are provided for users to choose to authorize or refuse.

[0056] It should be noted that the technical solution of this application provides users with corresponding operation entry points for users to choose to agree to or reject the automated decision results; if the user chooses to reject, the process will proceed to the expert decision-making process.

[0057] It should be noted that the method and apparatus for comparing metadata versions of relational databases of the present invention can be used in the financial field, or in any field other than the financial field. The application field of the method and apparatus for comparing metadata versions of relational databases of the present invention is not limited.

[0058] First aspect of the embodiments

[0059] An embodiment of the first aspect of the present invention provides a method for comparing metadata versions of a relational database.

[0060] Figure 1 This is a flowchart of a method for comparing metadata versions of a relational database according to an embodiment of the present invention, such as... Figure 1 As shown, in one embodiment of the present invention, the method for comparing metadata versions of the relational database of the present invention includes steps S101 and S102.

[0061] Step S101: Store the data to be compared into a temporary table and generate batch numbers; and

[0062] Step S102: If there is no difference in the table-level data, determine whether there is a difference in the field-level data based on the batch number and table identifier. Determining whether there is a difference in the field-level data includes: associating and grouping the field-level temporary table and the field-level process table, and comparing the message digest 5 values ​​of each group; and / or comparing the arrays of the field-level temporary table and the field-level process table.

[0063] Therefore, it can be seen that the present invention, through the synergistic effect of the above steps S101 to S102, stores the data to be compared into a temporary table, and performs field-level data comparison when there is no difference in the table-level data, thus avoiding the waste of resources in full-scale in-depth field comparison and improving the efficiency of metadata version comparison of relational databases.

[0064] In step S101, the metadata to be compared is divided into table-level data and field-level data, and stored in corresponding temporary tables. Table-level data is stored in the table-level temporary table DEMO_FU_IMP, and field-level data is stored in the field-level temporary table DEMO_ZI_IMP. During the data storage process, the system automatically generates a unique batch number for each batch of imported data. This batch number is used to identify all the data imported in this batch, facilitating subsequent comparison operations.

[0065] Table 1 illustrates the data structure of the table-level process table DEMO_FU in this embodiment of the application. As shown in Table 1, the table-level process table DEMO_FU includes fields such as table identifier, table name (in Chinese), version number, and table modification flag. The table identifier is the primary key of the table-level process table, used to uniquely identify each table record.

[0066] Table 1

[0067]

[0068] Table 2 illustrates the data structure of the table-level temporary table DEMO_FU_IMP in this embodiment of the application. As shown in Table 2, the data structure of the table-level temporary table DEMO_FU_IMP includes all fields of the table-level process table DEMO_FU, namely, table identifier, Chinese table name, version number, and table modification flag, and also adds table import result identifier and batch number fields. The table import result identifier is used to mark the data import status, and the batch number is a unique identifier automatically generated by the system during data import, used to distinguish different batches of imported data. The table-level temporary table DEMO_FU_IMP may also contain additional status fields to record the status of each stage of data processing, facilitating the tracking of the entire comparison process.

[0069] Table 2

[0070]

[0071] Table 3 illustrates the data structure of the field-level procedure table DEMO_ZI according to an embodiment of the present invention. As shown in Table 3, the field-level procedure table DEMO_ZI includes fields such as field number, data item number, primary key flag, and table identifier. The field number is the primary key of the field-level procedure table, used to uniquely identify each field-level data record. The data item number is used to associate specific information about the data item, the primary key flag is used to identify whether the field is the primary key of the table, and the table identifier field is used to associate information about the table to which the field belongs.

[0072] Table 3

[0073]

[0074] Table 4 shows the data structure of the field-level temporary table DEMO_ZI_IMP in this embodiment of the invention. As shown in Table 4, the field-level temporary table DEMO_ZI_IMP includes all the fields of the field-level process table DEMO_ZI, namely the field number, data item number, primary key flag, and table identifier fields. It also adds version number, table import result flag, and batch number fields. The version number is used to identify the version information of the metadata, the table import result flag is used to mark the status and result of the data import, and the batch number is used to identify the batch of data import for easy comparison and analysis later. The batch number is consistent with the batch number in the table-level temporary table to ensure data correlation.

[0075] Table 4

[0076]

[0077] In some examples, the method for comparing metadata versions of relational databases according to the present invention further includes:

[0078] Step S103: Determine whether there are differences in the table-level data.

[0079] The following describes the methods for determining whether there are differences in table-level data in step S103, including method 1 and method 2.

[0080] Method 1:

[0081] Figure 2 This is a flowchart illustrating how to determine whether there are differences in table-level data according to an embodiment of the present invention. For example... Figure 2 As shown, the method for determining whether there are differences in table-level data includes steps S201 to S205.

[0082] Step S201: Filter the temporary table data at the table level according to the batch number.

[0083] Specifically, the system filters all data records corresponding to a specific batch number from the table-level temporary table DEMO_FU_IMP. As shown in Table 2, the data structure of the table-level temporary table DEMO_FU_IMP includes fields such as table identifier, Chinese table name, version number, table modification flag, table import result identifier, and batch number. By filtering by batch number, it can be ensured that differences are only judged for data imported from the same batch, improving the accuracy and efficiency of comparison.

[0084] Step S202: Perform a joint query on the table-level temporary table and the table-level procedure table using the table identifier.

[0085] Specifically, the system associates the filtered table-level temporary table DEMO_FU_IMP with the table-level procedure table DEMO_FU through the table identifier field. As shown in Table 1, the data structure of the table-level procedure table DEMO_FU includes fields such as table identifier, Chinese table name, version number, and table modification flag.

[0086] Table 5

[0087]

[0088] Table 5 shows the table-level single-row comparison SQL statements in an embodiment of the present invention. As shown in Table 5, the LEFT JOIN statement is used to perform a joint query between the table-level temporary table and the table-level procedure table, ensuring that all records in the temporary table are included in the comparison scope, even if there is no corresponding record in the procedure table.

[0089] Step S203: Concatenate the table-level fields that need to be matched in the query results.

[0090] Specifically, the system concatenates the table-level fields in the combined query results that require comparison in a predefined order. These table-level fields to be matched include, but are not limited to, the Chinese table name, version number, and table modification flag. The concatenation operation combines the values ​​of these fields into a complete string according to a fixed format, preparing for subsequent message digest calculation. During concatenation, the system performs necessary preprocessing on the field values, such as removing leading and trailing spaces and ensuring case consistency, to ensure the accuracy of the comparison.

[0091] Step S204: Calculate the message digest 5 value of the concatenated table-level temporary table and table-level procedure table respectively.

[0092] Specifically, the MD5 values ​​are calculated for the temporary table concatenation string and the process table concatenation string obtained in step S203. The MD5 algorithm can convert a string of arbitrary length into a fixed-length 128-bit string, which is usually represented by a 32-bit hexadecimal number. By calculating the MD5 value, complex field combinations can be simplified into a fixed-length string, facilitating rapid comparison.

[0093] Step S205: Compare the message digest 5 values ​​corresponding to the table-level temporary table and the table-level procedure table.

[0094] Specifically, the system compares whether the two MD5 values ​​calculated in step S4 are the same. If the two MD5 values ​​are different, it indicates that there is a difference in the table-level data; if the two MD5 values ​​are the same, it indicates that there is no difference in the table-level data. Figure 1 The process shown involves determining whether there are differences in field-level data when there are no differences in table-level data, based on the batch number and table identifier.

[0095] The above-described method for determining table-level data differences can quickly and accurately identify table-level differences in metadata of different versions in a relational database, providing database administrators with an effective data version management tool and improving the efficiency of database maintenance and upgrades.

[0096] Method 2:

[0097] In some embodiments, the method for determining whether there are differences in table-level data includes: performing a value-by-value comparison of the fields to be matched in the table-level temporary table and the table-level procedure table.

[0098] In some cases, when hash value comparisons reveal discrepancies, a further value-by-value comparison is performed on the fields to be matched in the table-level temporary table and the table-level procedure table to determine which specific fields have changed. Value-by-value comparison involves comparing each field to be matched in the temporary table and the procedure table one by one to identify the inconsistent fields.

[0099] During the value-by-value comparison process, the comparison results for each field are recorded, including the field name, the value in the temporary table, the value in the process table, and an indicator indicating whether a difference exists. These detailed comparison results help users accurately pinpoint the specific location of data changes, facilitating subsequent processing.

[0100] Based on the above table-level difference determination method, combined with Figure 1 As shown in the overall process, the system can efficiently compare metadata versions of relational databases, quickly identify data differences at the table and field levels, and provide strong support for database management and version control.

[0101] The following describes the methods for determining whether there are differences in field-level data in step S102, including method 1, method 2 and method 3.

[0102] Method 1:

[0103] Figure 3 This is a flowchart illustrating how to determine whether there are differences in field-level data according to an embodiment of the present invention. For example... Figure 3 As shown, the method for determining whether there are differences in field-level data includes steps S301 to S305.

[0104] Step S301: Filter field-level temporary table data based on batch number and table identifier.

[0105] In step S301, data records matching specific batch numbers and table identifiers are filtered from the field-level temporary table DEMO_ZI_IMP. As shown in Table 4, the field-level temporary table DEMO_ZI_IMP contains fields such as field number, data item number, primary key flag, table identifier, version number, table import result identifier, and batch number. By specifying the batch number and table identifier for filtering, field-level data of the specific table for which difference determination is required can be obtained.

[0106] Step S302: Associate the field-level temporary table with the field-level procedure table using table identifiers, and then group them according to the table identifiers.

[0107] In step S302, the filtered field-level temporary table data is associated with the field-level procedure table DEMO_ZI. As shown in Table 3, the field-level procedure table DEMO_ZI contains fields such as field number, data item number, primary key flag, and table identifier. The association operation is performed through the table identifier field to ensure that the data in the temporary table correctly matches the data in the corresponding table in the procedure table. After association, the system groups the data according to the table identifier field, so that field data from the same table are grouped together, facilitating subsequent comparison operations.

[0108] Step S303: Concatenate the fields to be matched in each group of field-level temporary tables and field-level procedure tables.

[0109] In step S303, for each group, the system concatenates the field values ​​to be compared in the field-level temporary table and the field-level procedure table. Fields to be matched include, but are not limited to, field numbers, data item numbers, and primary key identifiers. The concatenation operation joins these field values ​​into a string in a predetermined order, preparing for subsequent calculation of the message digest value.

[0110] Table 6 shows the field-level batch comparison SQL statements according to an embodiment of the present invention. As shown in Table 6, the field-level batch comparison SQL statements connect the field-level temporary table and the field-level procedure table through a JOIN operation, and use a string concatenation function to combine the values ​​of the fields to be matched.

[0111] Table 6

[0112]

[0113] Step S304: Calculate the message digest 5 value of each group after splicing.

[0114] In step S304, a message digest 5 (MD5) value is calculated for each concatenated string obtained in step S303. The MD5 algorithm can convert data of any length into a fixed-length 128-bit hash value, typically represented as a 32-bit hexadecimal number. This calculation method can efficiently represent the characteristics of field data, facilitating subsequent difference comparison. As shown in Table 6, the SQL statement uses the MD5 function to perform hash calculations on the concatenated field values.

[0115] Step S305: Compare the message digest 5 values ​​of each group.

[0116] In step S305, the MD5 values ​​of each group in the field-level temporary table and the field-level procedure table are compared. If the two MD5 values ​​are the same, it means that the field-level data in that table has not changed; if the MD5 values ​​are different, it means that the field-level data in that table has differed. In this way, the system can quickly determine which tables' field-level data has changed without having to compare each field individually, greatly improving comparison efficiency.

[0117] Method 2:

[0118] In some embodiments, the field-level difference determination method includes comparing arrays of field-level temporary tables and field-level procedure tables.

[0119] In some cases, the arrays serialized from the field-level temporary tables are compared one by one with the arrays serialized from the field-level procedure tables. During the comparison, it can be checked whether the array lengths are the same and whether the values ​​of each element in the arrays are consistent. If the two arrays are completely identical, it means there are no differences in the field-level data; if the two arrays differ in any way, it means there are differences in the field-level data.

[0120] The above-described field-level difference determination method can efficiently identify field-level differences in relational database metadata versions, providing strong support for database management and version control.

[0121] Method 3:

[0122] In some embodiments, the field-level difference determination method includes: associating and grouping the field-level temporary table and the field-level procedure table, comparing the message digest 5 values ​​of each group to obtain a first determination result; and comparing the arrays of the field-level temporary table and the field-level procedure table to obtain a second determination result; if both the first determination result and the second determination result are not different, the two arrays are considered to be completely identical; otherwise, the two arrays are considered to be different.

[0123] In some embodiments, the first determination result and the second determination result can be fused. For example, the first determination result and the second determination result can be the probability that the two arrays are not different. The first determination result and the second determination result can be assigned weights respectively, and the weighted sum of the two can be calculated. If the weighted sum exceeds a preset threshold, it is considered that the two arrays are not different; otherwise, it is considered that the two arrays are different.

[0124] By combining the above field-level difference determination methods, two determination strategies can be integrated to improve the accuracy of field-level difference identification in relational database metadata versions.

[0125] The following describes the methods for selecting table-level fields and field-level fields to be matched.

[0126] In a method for comparing metadata versions in a relational database, the methods for selecting the fields to be matched include:

[0127] The selection of table-level and field-level fields to be matched is based on the Structured Query Language (SQL) configuration.

[0128] Specifically, in the process of comparing table-level data, it is necessary to first filter the table-level temporary table data according to the batch number, and then perform a joint query on the table-level temporary table and the table-level procedure table through the table identifier, such as... Figure 2 As shown in Table 1 and Table 2, the table-level fields to be matched are configured using SQL statements. These fields can include table identifier, Chinese table name, version number, table modification flag, etc., as shown in Table 1 and Table 2, in the table-level procedure table DEMO_FU and the table-level temporary table DEMO_FU_IMP.

[0129] When configuring SQL to match table-level fields, you can explicitly specify the fields to be compared using the field list in the SELECT statement. For example, in the table-level single-row comparison SQL statement shown in Table 5, you can select common fields from the table-level temporary table and the table-level procedure table for comparison. After configuration, the table-level fields to be matched in the query results are concatenated, the message digest 5 value is calculated, and then compared.

[0130] Similarly, in the process of comparing field-level data, such as Figure 3 As shown, it is necessary to filter the field-level temporary table data based on the batch number and table identifier, and then associate and group the field-level temporary table with the field-level process table. During this process, the field-level fields to be matched are configured using SQL statements. These fields can include field numbers, data item numbers, primary key flags, etc., as shown in Tables 3 and 4 in the field-level process table DEMO_ZI and the field-level temporary table DEMO_ZI_IMP.

[0131] When configuring the fields to be matched in the SQL configuration, you can use the JOIN function to connect the field-level temporary table and the field-level procedure table in the batch comparison SQL statement shown in Table 6, and specify the fields to be compared in the SELECT clause. After configuration, concatenate the fields to be matched in each group, calculate the message digest value, and compare them.

[0132] When configuring the SQL statement to select fields to match, you can flexibly choose the fields to compare according to your actual needs. For example, you can select only key fields for comparison, or you can select all fields for comparison. This flexibility allows the metadata version comparison method to adapt to different application scenarios.

[0133] Configuring the fields to be matched in SQL is a crucial step in the metadata version comparison process of relational databases. It, along with other steps such as data import, table-level comparison, and field-level comparison, constitutes the complete comparison process. By properly configuring the fields to be matched, the accuracy and efficiency of the comparison can be improved, making metadata version comparison more precise and reliable.

[0134] The following describes the sources of metadata in relational databases.

[0135] In relational database metadata version comparison systems, metadata sources include web page operations and / or online services and / or table imports. For example... Figure 1 As shown, the method for comparing metadata versions first requires obtaining the metadata and importing it into the system for processing.

[0136] Web-based operation refers to users directly interacting with the database management system through a browser interface, manually entering or modifying metadata information. This method is suitable for maintaining and updating small amounts of metadata, allowing users to intuitively view and edit the metadata content.

[0137] Online services refer to retrieving metadata through API interfaces or web services. The system can periodically or on-demand call external service interfaces to obtain the latest metadata information. This approach is suitable for scenarios with a high degree of automation, enabling automatic synchronization and updates of metadata.

[0138] Table import is the most common method for obtaining batch metadata. As shown in Tables 2 and 4, the imported data is stored in the table-level temporary table DEMO_FU_IMP and the field-level temporary table DEMO_ZI_IMP. During the table import process, the system generates a unique batch number for each batch of imported data to identify different batches of import operations.

[0139] The table import result identifier is a key field in the table import process, used to filter metadata imported normally. As shown in Table 2, the table-level temporary table DEMO_FU_IMP contains a "Table Import Result Identifier" field, which is used to identify the import status of each metadata record. Only when the table import result identifier is "normal" or a specific success status value will the system include that metadata record in subsequent version comparison analysis.

[0140] During the metadata version comparison process, as shown in Table 5, SQL statements were used to filter out normally imported metadata based on batch number and table import result identifiers, excluding data records that failed to import or were abnormal. This ensures that only metadata of acceptable quality participates in the version comparison analysis, improving the accuracy and reliability of the comparison results.

[0141] As shown in Table 6, during the field-level data comparison process, the metadata records that were imported normally are filtered out based on the table import result identifier, and then MD5 values ​​are calculated and compared to detect whether there are differences in the field-level data.

[0142] In some cases, the table import result flag is automatically validated to ensure that only records flagged as "successful" or "1" are included in the comparison. If an error occurs during the import process, the system will set the table import result flag of the corresponding record to "failure" or "0" and record detailed error information in the log to facilitate troubleshooting by administrators.

[0143] In some cases, import quality reports can be generated based on the statistics of the table import results, helping users understand the overall quality of metadata and make targeted improvements to the data collection and processing process.

[0144] By defining the source of metadata and applying the identifiers for table import results, the system can effectively manage and filter metadata, ensuring the accuracy and reliability of metadata version comparisons and providing strong support for database management and maintenance.

[0145] The following describes the process of comparing metadata versions of a relational database according to an embodiment of the present invention.

[0146] Figure 4 This is a schematic diagram illustrating the process of comparing metadata versions in a relational database according to an embodiment of the present invention. Figure 4 As shown, in one embodiment of the present invention, the overall processing flow of the present invention includes steps S401 to S408.

[0147] S401: Insert data into a temporary table;

[0148] In step S401, the hierarchical data requiring version verification is extracted from the versions to be compared, including parent data and all corresponding child data. Parent data is the core, dominant data in the hierarchical relationship (such as the database master table or metadata schema), while child data is the related data dependent on the parent data (such as database slave tables or table structures under the schema). The extracted parent and child data are stored in pre-defined parent and child temporary tables, respectively. The temporary table structure corresponds one-to-one with the data field attributes, ensuring the integrity and standardization of data storage and providing a data foundation for subsequent comparisons.

[0149] S402: Compare the parent class with the parent class's temporary table;

[0150] In step S402, the baseline version of the parent data for this comparison is determined (such as the parent data of a historical stable version). This baseline version of the parent data is then compared with the temporary parent table storing the data to be compared in S401. The comparison dimensions include key information such as the core fields, attribute configurations, and relationships of the parent data. Field-by-field comparison or hash value comparison is used to ensure the accuracy of the comparison results.

[0151] S403: Determine if a difference exists;

[0152] In step S403, based on the comparison result of S402, it is determined whether there is a difference between the baseline version parent class data and the parent class data to be compared in the parent class temporary table. This determination is a binary choice logic, outputting only two results: "there is a difference" or "there is no difference", which serve as the triggering condition for subsequent process branches.

[0153] S404: If not, batch compare whether there are differences between subclasses;

[0154] In step S404, if the judgment result of S403 is "no" (i.e., there is no difference in the parent class data), the subclass data comparison process is triggered. All subclass data to be compared is extracted from the temporary subclass table in S401 and compared in batches with the corresponding associated subclass data in the baseline version. During the comparison, the parent class association identifier is used as the matching condition to ensure that the subclass data to be compared and the subclass data in the baseline version belong to the same parent class association. The comparison dimensions include the core attributes of the subclass data, such as field values, data types, and constraints.

[0155] S405: If so, upgrade the version number;

[0156] In step S405, if the judgment result of S403 is "yes" (i.e., there is a difference in the parent data), then there is no need to perform subsequent comparison of the sub-data, and the version number upgrade operation is performed directly. According to the preset version number upgrade rules (such as major version number + 1, minor version number + 1, or revision number + 1), the overall version number of the hierarchical data to be compared is upgraded. The upgraded version number serves as the latest version identifier for the data, and the reason for the version upgrade (difference in the parent data) and the upgrade time are recorded.

[0157] S406: Determine if a difference exists;

[0158] In step S406, based on the batch comparison results of S404, it is determined whether there is a difference between the baseline version subclass data and the subclass data to be compared in the subclass temporary table. This determination is also a binary choice, outputting only two results: "difference exists" or "no difference exists," which are used to trigger subsequent version number management operations.

[0159] S407: If not, there is no difference in the version comparison, so the version number will not be upgraded;

[0160] In step S407, if the judgment result of S406 is "no" (i.e., no difference in subclass data), it indicates that the parent and subclass data of the version to be compared are consistent with the baseline version, and there is no difference in version comparison. At this time, the version number upgrade operation is not performed, the original version number remains unchanged, and the comparison result (no difference) and comparison time are recorded to form a version comparison log.

[0161] S408: If so, upgrade the version numbers of the parent and child classes;

[0162] In step S408, if the judgment result of S406 is "yes" (i.e., there is a difference in subclass data), then the version number synchronization upgrade operation of the parent class and subclass is performed. According to the preset version number upgrade rules, the version numbers of the parent class data and subclass data to be compared are uniformly upgraded to ensure the consistency of the parent-child hierarchical data version numbers. At the same time, the reason for the version upgrade (difference in subclass data), the identification information of the differing subclasses, the upgrade time, and other key information are recorded in detail for easy version traceability.

[0163] Therefore, the system first compares the parent class data, then compares the child class data in conjunction with it. If there are differences in the parent class data, the comparison of the child class data is terminated directly, reducing unnecessary calculations. The child class data is compared in batches, significantly improving comparison efficiency. A logic of "upgrade separately for parent class differences, upgrade synchronously for child class differences, and no upgrade if there are no differences" is established to ensure that the version number matches the actual data state, improving version traceability.

[0164] Taking the version comparison of the main table (parent class) and the subordinate table (child class) in the database as an example, the above steps S401 to S408 are introduced.

[0165] S401: Extract all data from the main table "USER" (parent data) and the secondary table "USER_ORDER" (child data) in the version to be compared, and store them in the parent temporary table "TEMP_USER" and the child temporary table "TEMP_USER_ORDER" respectively;

[0166] S402: Compare the data in the base version of the main table "USER" with the parent temporary table "TEMP_USER" field by field, and verify the core attributes such as "USER_ID" and "USER_NAME".

[0167] S403: The judgment result is "No", meaning there is no difference in the main table data;

[0168] S404: Based on the association identifier "USER_ID", perform a batch comparison of the baseline version from the table "USER_ORDER" and the subclass temporary table "TEMP_USER_ORDER" for attributes such as "ORDER_ID" and "ORDER_AMOUNT".

[0169] S406: The judgment result is "yes", which means that there is a difference in the data from the table (such as a change in the precision of the "ORDER_AMOUNT" field).

[0170] S408: According to the version numbering rule "major version number, minor version number", upgrade the original version number "V2.1" to "V2.2", and synchronously update the version numbers of the main table "USER" and the minor table "USER_ORDER", and record the upgrade reason "USER_ORDER table, ORDER_AMOUNT field precision change".

[0171] If the comparison in step S402 reveals a change in the "USER_STATUS" field attribute of the "USER" table, and the result of S403 is "yes", then proceed directly to S405 to upgrade the version number from "V2.1" to "V3.0", without needing to perform subsequent subclass comparisons.

[0172] It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions, and although a logical order is shown in the flowchart, in some cases the steps shown or described may be executed in a different order than that shown here.

[0173] Second aspect of the embodiments

[0174] Based on the same inventive concept, embodiments of the present invention also provide a metadata version comparison device for relational databases, which can be used to implement the metadata version comparison method for relational databases described in the above embodiments, as described in the following embodiments. Since the principle of solving the problem by the metadata version comparison device for relational databases is similar to that of the metadata version comparison method for relational databases, embodiments of the metadata version comparison device for relational databases can refer to embodiments of the metadata version comparison method for relational databases, and repeated details will not be elaborated further. As used below, the terms "unit" or "module" can refer to a combination of software and / or hardware that performs a predetermined function. Although the device described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.

[0175] Figure 5 This is a structural block diagram of the metadata version comparison device for a relational database according to an embodiment of the present invention, such as... Figure 5 As shown, in one embodiment of the present invention, the metadata version comparison device 500 for relational databases of the present invention includes:

[0176] Data storage unit 501 is used to store the data to be compared into a temporary table and generate batch numbers; and

[0177] Field-level data comparison unit 502, in the absence of differences in table-level data, determines whether differences exist in field-level data based on the batch number and table identifier (TBL_ID).

[0178] Determining whether there are differences in field-level data includes:

[0179] Join and group field-level temporary tables with field-level procedure tables, and compare the message digest 5 values ​​of each group; and / or

[0180] Compare arrays of field-level temporary tables and field-level procedure tables.

[0181] In one embodiment of the present invention, the comparison device 500 further includes:

[0182] Table-level data comparison unit 503 determines whether there are differences in the table-level data.

[0183] Third aspect of the embodiments

[0184] To achieve the above objectives, according to another aspect of this application, a computer device is also provided. For example... Figure 6As shown, the computer device 600 includes a memory 601, a processor 602, a communication interface 603, and a communication bus 604. The memory 601 stores a computer program that can run on the processor 602. When the processor 602 executes the computer program, it implements the steps in the method of the above embodiments.

[0185] Processor 602 may be a central processing unit (CPU). Processor 602 may also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or combinations thereof.

[0186] The memory 601, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs, non-transitory computer-executable programs, and units, such as the program units corresponding to those in the above method embodiments of the present invention. The processor 602 executes various functional applications and data processing of the processor 602 by running the non-transitory software programs, instructions, and modules stored in the memory 601, thereby implementing the methods in the above method embodiments.

[0187] Memory 601 may include a program storage area and a data storage area. The program storage area may store the operating system and applications required for at least one function; the data storage area may store data created by the processor, etc. Furthermore, memory 601 may include high-speed random access memory and may also include non-transitory memory, such as at least one disk storage device, flash memory device, or other non-transitory solid-state storage device. In some embodiments, memory 601 may optionally include memory remotely located relative to processor 602, and these remote memories may be connected to the processor via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0188] The one or more units are stored in the memory 601, and when executed by the processor 602, the methods in the above embodiments are performed.

[0189] The specific details of the aforementioned computer device 600 can be understood by referring to the relevant descriptions and effects in the above embodiments, and will not be repeated here.

[0190] Fourth aspect of the embodiment

[0191] To achieve the above objectives, according to another aspect of this application, a computer-readable storage medium is also provided, which stores a computer program that, when executed in a computer processor, implements the steps in the method for comparing metadata versions of the relational database described above. Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The program can be stored in a computer-readable storage medium, and when executed, it can include the processes of the embodiments of the methods described above. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk drive (HDD), or solid-state drive (SSD), etc.; the storage medium may also include combinations of the above types of memory.

[0192] Fifth aspect of the embodiment

[0193] To achieve the above objectives, according to another aspect of this application, a computer program product is also provided, including a computer program / instructions that, when executed by a processor, implement the steps of the above-described method for comparing metadata versions of a relational database.

[0194] The computer program product can be a non-transitory computer-readable storage medium storing a computer program, such as ROM, RAM, CD-ROM, magnetic tape, floppy disk, optical data storage device, etc. When the computer program is executed by a processor, it can implement the various steps of the metadata version comparison method for a relational database described in Embodiment 1.

[0195] The computer program product can be installed in various electronic devices, such as personal computers, servers, smartphones, tablets, etc., enabling these devices to execute a metadata version comparison method for a relational database as described in the first aspect embodiment.

[0196] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0197] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0198] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0199] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0200] It is worth noting that the above description is merely illustrative, and the invention is not limited thereto; appropriate modifications can be made based on the above embodiments. Furthermore, the above description is merely illustrative of the various components, and the invention is not limited thereto; the specific details of each component can be found in related technologies. Additionally, components not shown in the figures can be added, or one or more components in the figures can be removed.

[0201] The present invention has been described above with reference to specific embodiments. However, those skilled in the art should understand that these descriptions are exemplary and not intended to limit the scope of protection of the present invention. Those skilled in the art can make various modifications and variations to the present invention based on the principles thereof, and these modifications and variations are also within the scope of the present invention.

[0202] Preferred embodiments of the invention have been described above with reference to the accompanying drawings. Many features and advantages of these embodiments are apparent from this detailed description, and therefore the appended claims are intended to cover all such features and advantages of these embodiments that fall within their true spirit and scope. Furthermore, since many modifications and alterations will readily occur to those skilled in the art, the embodiments of the invention are not intended to be limited to the precise structures and operations illustrated and described, but rather to encompass all suitable modifications and equivalents falling within their scope.

Claims

1. A method for comparing metadata versions of a relational database, characterized in that, The comparison method includes: Store the data to be compared in a temporary table and generate batch numbers; and If there are no differences in the table-level data, determine whether there are differences in the field-level data based on the batch number and table identifier (TBL_ID). Determining whether there are differences in field-level data includes: Join and group field-level temporary tables with field-level procedure tables, and compare the message digest 5 values ​​of each group; and / or Compare arrays of field-level temporary tables and field-level procedure tables.

2. The comparison method according to claim 1, characterized in that, Join and group the field-level temporary table with the field-level procedure table, and compare the message digest 5 values ​​of each group, including: Filter field-level temporary table data based on the batch number and the table identifier (TBL_ID); The field-level temporary table and the field-level procedure table (DEMO_ZI) are associated through the table identifier and then grouped according to the table identifier; For each group of field-level temporary tables and field-level process tables, the fields to be matched are concatenated respectively; Calculate the MD5 hash of the concatenated message digest for each group; and Compare the message digest 5 values ​​of each group.

3. The comparison method according to claim 1, characterized in that, The comparison involves arrays of field-level temporary tables and field-level procedure tables, including: Grouping according to the table identifier, serializing each group of fields to be matched into an array (JSON array) in a predetermined order, and comparing the array of the field-level temporary table with the array of the field-level process table.

4. The comparison method according to claim 1, characterized in that, The field-level procedure table (DEMO_ZI) includes the following fields: field number (FLD_ID), data item number (DITM_ID), primary key flag (PK_IND), and table identifier (TBL_ID). The field number is the primary key of the field-level procedure table. The field-level temporary table (DEMO_ZI_IMP) includes the fields of the field-level process table, as well as the version number (VNO), table import result identifier (EXCEL_IMP_RTN), and batch number (EXCEL_IMP_BTH) fields.

5. The comparison method according to claim 1, characterized in that, Determining whether there are discrepancies in table-level data includes: Filter table-level temporary table data according to the batch number; Perform a joint query on the table-level temporary table and the table-level procedure table (DEMO_FU) using the table identifier (TBL_ID); Concatenate the table-level fields that need to be matched in the query results; Calculate the message digest 5 value of the concatenated table-level temporary table and the table-level procedure table (DEMO_FU); Compare the message digest 5 values ​​corresponding to the table-level temporary table and the table-level procedure table.

6. The comparison method according to claim 1, characterized in that, Determining whether there are discrepancies in table-level data includes: The fields to be matched in the table-level temporary table and the table-level procedure table are compared value by value.

7. The comparison method according to claim 1, characterized in that, The table identifier is the primary key of the table-level procedure table. The table-level temporary table (DEMO_FU_IMP) includes the fields of the table-level procedure table, as well as the table import result identifier (EXCEL_IMP_RTN) and batch number (EXCEL_IMP_BTH) fields.

8. The comparison method according to claim 1, characterized in that, The table-level and field-level fields to be matched are selected based on the Structured Query Language (SQL) configuration.

9. The comparison method according to claim 1, characterized in that, The sources of the metadata include web page operations and / or online services and / or table imports. The result identifier of the table import is used to filter metadata imported normally.

10. A device for comparing metadata versions of a relational database, characterized in that, The comparison device includes: The data storage unit stores the data to be compared into a temporary table and generates batch numbers; and The field-level data comparison unit, in the absence of differences in table-level data, determines whether differences exist in field-level data based on the batch number and table identifier (TBL_ID). Determining whether there are differences in field-level data includes: Join and group field-level temporary tables with field-level procedure tables, and compare the message digest 5 values ​​of each group; and / or Compare arrays of field-level temporary tables and field-level procedure tables.

11. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 9.

12. A computer-readable storage medium having a computer program / instructions stored thereon, characterized in that, When the computer program / instructions are executed by the processor, they implement the steps of the method according to any one of claims 1 to 9.

13. A computer program product comprising a computer program / instructions, characterized in that, When the computer program / instructions are executed by the processor, they implement the steps of the method according to any one of claims 1 to 9.

Images