Method and system for analyzing database table association relationships

Abstract

The present invention provides a method and system for analyzing database table association relationships in the field of database management technology. The method includes: step S10, reading table information of a data table stored in a database; step S20, creating an association record table and filtering the stored table information; step S30, performing a primary match based on the column names in the filtered table information, obtaining a primary match result for the data table and storing it in the association record table; step S40, performing a secondary match based on the unmatched table information, obtaining a secondary match result for the data table and storing it in the association record table; step S50, inputting the unmatched table information into a pre-trained association analysis model, obtaining a tertiary match result for the data table and storing it in the association record table; and step S60, generating and displaying an ER diagram for the data table based on the association record table. The present invention has the advantages of greatly improving the accuracy, applicability, efficiency, and security of database table association relationship analysis.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a method and system for analyzing database table association relationships. Background Art

[0002] With the increasing development and popularization of information technology, a large number of databases (information systems) have been rapidly developed to manage project-related data. A company often has several projects, each of which contains several data tables. The relationships between these tables are complex and diverse. During project promotion and management, it is often necessary to analyze (extract) the relationships between data tables from the database to better utilize the data stored in the database. Traditionally, the following methods exist for analyzing the relationships between database data tables:

[0003] Method 1: Draw table relationships during database design. This means that teams that prioritize standards first design the database tables and draw the relationships between them. These relationships are then maintained as the project progresses, and the relationships are submitted at the final handover. However, this method is only suitable for native teams working on their own tables. It's not suitable for databases developed by others, as it's difficult to understand the database's design logic, leading to inaccurate relationship analysis.

[0004] Method 2: If relationships aren't drawn during the database design process or aren't transferred during project handover, manually analyze the code corresponding to the data tables stored in the database and summarize the relationships. However, this method requires access to the latest version of the code and places high demands on the analyst's technical proficiency and business understanding. Analysts spend a significant amount of time reading and understanding the code, extracting relationships piecemeal, which can lead to errors. If the code isn't up to date, the resulting time and effort can only yield a subset of confirmed relationships, making the investment prohibitively expensive.

[0005] Method 3: Foreign keys are used to link other tables and draw relationships through the foreign keys of the data tables. However, foreign keys require developers or database administrators to actively create them. Furthermore, due to the numerous restrictions on "addition, deletion, and modification" of foreign keys, industry standards do not recommend their use. As a result, many databases rarely create them, and consequently, many databases cannot use foreign keys to draw relationships.

[0006] Method 4: Draw associations by analyzing database operation logs. This involves recording every executed SQL statement as an operation log during database operation. The logs are then analyzed to identify SQL statements involved in the associations, which are then summarized and mapped. However, this method requires the database logging function to be enabled, requires the database to have been running for a period of time, and often involves association operations rather than step-by-step code development. This method also has certain limitations and is limited in its applicability.

[0007] In addition, traditionally, when performing relationship analysis on database tables, the database is accessed only based on a simple account name and password to obtain relevant data for analysis. No corresponding security measures are taken in other links, resulting in security risks in the relationship analysis process and the analysis results are easily stolen and tampered with.

[0008] Therefore, how to provide a database table association relationship analysis method and system to improve the accuracy, applicability, efficiency and security of database table association relationship analysis has become a technical problem that needs to be solved urgently. Summary of the Invention

[0009] The technical problem to be solved by the present invention is to provide a method and system for analyzing the association relationship between database tables, so as to improve the accuracy, applicability, efficiency and security of the analysis of the association relationship between database tables.

[0010] In a first aspect, the present invention provides a method for analyzing database table association relationships, comprising the following steps:

[0011] Step S10: After authentication with the server, access the database deployed by the server, read the table information of the data table stored in the database, and perform structured storage of each table information based on a tree structure;

[0012] Step S20: Create an association relationship record table, and filter the stored table information through a rule engine;

[0013] Step S30: performing a primary match on the column names in the filtered table information to obtain a primary match result of the data table, verifying the primary match result, and storing the primary match result in an association record table;

[0014] Step S40: Perform secondary matching on the unmatched table information to obtain a secondary matching result of the data table, verify the secondary matching result, and store the secondary matching result in the association relationship record table;

[0015] Step S50: inputting the unmatched table information into the pre-trained association relationship analysis model to obtain the three-level matching results of the data table, verifying the three-level matching results, and storing the three-level matching results in the association relationship record table;

[0016] Step S60: Generate an ER diagram of the data table based on the association relationship record table, and display the ER diagram through a visual interface;

[0017] Step S70: Recording an analysis log of the association relationship of the data table in real time, and encrypting and storing the analysis log;

[0018] Step S80: dynamically updating the association relationship record table based on the incremental data table;

[0019] The step S10 specifically includes:

[0020] Step S11: The server creates a pair of public and private keys based on the RSA algorithm, and presets the public key to the client;

[0021] Based on the database address, the client sends a database access request to the server, which carries ciphertext data, signature data, device fingerprint, timestamp, and random number. The ciphertext data is obtained by encrypting the account name and password using the 3DES algorithm. The signature data is obtained by signing the account name and password using the public key. The device fingerprint is obtained by hashing the client's device serial number. The random number is a natural number randomly generated by the client and stored locally on the client.

[0022] The server parses the received database access request to obtain ciphertext data, signature data, device fingerprint, timestamp, and random number, performs a first-level verification of timeliness using the timestamp, verifies the signature data using the private key for a second-level verification, matches the device fingerprint using a preset user management table for a third-level verification, decrypts the ciphertext data using a 3DES algorithm to obtain an account name and password, performs a fourth-level verification of the legitimacy of the account name and password using the user management table, and if the verification passes, performs an HMAC calculation on the random number to obtain a first MAC value, generates a request response based on the first MAC value, and feeds the request response back to the client;

[0023] The client parses the received request response to obtain a first MAC value, performs HMAC calculation on the locally stored random number to obtain a second MAC value, and verifies the first MAC value using the second MAC value to complete the authentication operation;

[0024] Step S12: The client accesses the database deployed by the server and reads the table information of the data table stored in the database in real time; the table information includes at least the table name, table ID, column name, column ID, column length, primary key identifier, foreign key identifier, data type, data length, and table data; the primary key identifier is used to identify whether the corresponding column is a primary key; the foreign key identifier is used to identify whether the corresponding column is a foreign key;

[0025] Step S13: Based on the tree structure, the table ID is used as the parent node to perform structured storage on the information of each table.

[0026] Furthermore, the step S20 is specifically as follows:

[0027] Create a relationship record table for storing the table name, table ID, column name and column ID of the associated data table, and call the preset filtering rules through the rule engine to filter the stored table information; the filtering rules are that the column name contains preset characters, the column length is a preset length, the column is a primary key, the column is a foreign key, the data type is a string or the data length is not 0.

[0028] Furthermore, the step S30 is specifically as follows:

[0029] Through multi-threading technology, regular expressions are used to perform parallel one-to-one first-level matching on the column names in the filtered table information to obtain the first-level matching results of the data table; when a primary key or foreign key exists, the first-level matching is first performed based on the column name of the primary key or foreign key during the first-level matching process;

[0030] Generate a first SQL query statement based on the first-level matching result, query first table data from the data tables with associated relationships respectively, verify the first-level matching result based on the queried first table data, and store the first-level matching result in an associated relationship record table;

[0031] Determine whether there is any unmatched data table, if so, proceed to step S40; if not, proceed to step S60;

[0032] The step S40 is specifically as follows:

[0033] By using multi-threading technology, regular expressions are used to perform parallel one-to-many secondary matching on the table data in the table information of each of the unmatched data tables to obtain the secondary matching results of the data tables;

[0034] generating a second SQL query statement based on the secondary matching result, querying the second table data from the data tables with associated relationships respectively, verifying the secondary matching result based on the queried second table data, and storing the secondary matching result in the associated relationship record table;

[0035] It is determined whether there is any unmatched data table. If so, the process proceeds to step S50; if not, the process proceeds to step S60.

[0036] Furthermore, in step S50, the association relationship analysis model is constructed based on a data input module, a feature extraction module, a relationship reasoning module, and an output module, and a loss function is constructed based on a classification cross entropy loss function and a contrast loss function;

[0037] The data input module is used to input table information; the feature extraction module is used to extract structural features, text features and statistical features from the table information; the structural features include at least the number of columns and data types; the text features include at least the column names; the statistical features include at least the mean, variance and unique value ratio; the relationship reasoning module is used to infer the association relationship of the data table based on the structural features, text features and statistical features; the output module is used to output the matching results based on the association relationship;

[0038] The training process of the association relationship analysis model is specifically as follows:

[0039] A large amount of historical table information is obtained, and after pre-processing the data cleaning of each historical table information, the association relationship is annotated, and a data set is constructed based on the annotated historical table information; the data set is divided into a training set, a validation set, and a test set based on a ratio of 8:1:1, and the association relationship analysis model is trained using the training set until the loss value of the loss function is less than a preset loss threshold. During the training process, the learning rate, random dropout rate, number of iterations, and optimizer of the association relationship analysis model are continuously optimized; the analysis accuracy is calculated using the validation set to verify the trained association relationship analysis model. If the verification fails, the data set is expanded to continue training; if the verification is successful, then:

[0040] The confidence level is calculated using the test set to test the verified association analysis model. If the test fails, the data set is expanded to continue training; if the test succeeds, the training is terminated.

[0041] Furthermore, the step S60 is specifically as follows:

[0042] Based on the association relationship record table, an ER diagram of the data table is automatically generated through a drawing tool, one-to-one relationships, one-to-many relationships, and many-to-many relationships in the ER diagram are displayed differently, and the ER diagram is rendered and displayed in real time through a visual interface;

[0043] The step S70 is specifically as follows:

[0044] An analysis log that records the association relationship of data tables in real time, wherein the analysis log includes at least the analysis time, account name, device serial number, association relationship record table, and ER diagram;

[0045] The analysis log is hashed using the SHA-256 algorithm to obtain a hash value, the analysis log and the hash value are encrypted using the AES algorithm to obtain first-level encrypted data, the first-level encrypted data is converted into ASCII code, and the ASCII code is inverted to obtain second-level encrypted data, the second-level encrypted data is encrypted using the RC6 algorithm to obtain third-level encrypted data, the third-level encrypted data is encrypted using a preset mapping rule to obtain fourth-level encrypted data, the number 1 and the letter I are swapped, the number 0 and the letter O are swapped, and the number 7 and the letter L are swapped in the fourth-level encrypted data to obtain an encrypted log, the encrypted log is stored in a specified path, and the encrypted log is uploaded to the blockchain for backup.

[0046] In a second aspect, the present invention provides a database table association relationship analysis system, comprising the following modules:

[0047] A table information reading module is used to access the database deployed by the server after authentication with the server, read the table information of the data table stored in the database, and perform structured storage of each table information based on a tree structure;

[0048] A table information screening module is used to create an association relationship record table and screen the stored table information through a rule engine;

[0049] A primary matching module, configured to perform a primary match on the column names in the filtered table information to obtain a primary matching result of the data table, verify the primary matching result, and store the primary matching result in an association record table;

[0050] a secondary matching module, configured to perform secondary matching on the unmatched table information to obtain a secondary matching result of the data table, verify the secondary matching result, and store the secondary matching result in an association record table;

[0051] A three-level matching module is used to input the unmatched table information into the pre-trained association relationship analysis model to obtain the three-level matching results of the data table, verify the three-level matching results, and store the three-level matching results in the association relationship record table;

[0052] An ER diagram generation module is used to generate an ER diagram of a data table based on the association relationship record table, and display the ER diagram through a visual interface;

[0053] An analysis log management module is used to record the analysis log of the association relationship of the data table in real time and encrypt and store the analysis log;

[0054] A data table increment management module, configured to dynamically update the association relationship record table based on the incremented data table;

[0055] The table information reading module specifically includes:

[0056] An authentication unit is used for the server to create a pair of public and private keys based on the RSA algorithm and to pre-set the public key to the client;

[0057] Based on the database address, the client sends a database access request to the server, which carries ciphertext data, signature data, device fingerprint, timestamp, and random number. The ciphertext data is obtained by encrypting the account name and password using the 3DES algorithm. The signature data is obtained by signing the account name and password using the public key. The device fingerprint is obtained by hashing the client's device serial number. The random number is a natural number randomly generated by the client and stored locally on the client.

[0058] The server parses the received database access request to obtain ciphertext data, signature data, device fingerprint, timestamp, and random number, performs a first-level verification of timeliness using the timestamp, verifies the signature data using the private key for a second-level verification, matches the device fingerprint using a preset user management table for a third-level verification, decrypts the ciphertext data using a 3DES algorithm to obtain an account name and password, performs a fourth-level verification of the legitimacy of the account name and password using the user management table, and if the verification passes, performs an HMAC calculation on the random number to obtain a first MAC value, generates a request response based on the first MAC value, and feeds the request response back to the client;

[0059] The client parses the received request response to obtain a first MAC value, performs HMAC calculation on the locally stored random number to obtain a second MAC value, and verifies the first MAC value using the second MAC value to complete the authentication operation;

[0060] A database access unit, configured for a client to access a database deployed by a server and read in real time table information of a data table stored in the database; the table information includes at least a table name, table ID, column name, column ID, column length, primary key identifier, foreign key identifier, data type, data length, and table data; the primary key identifier is used to identify whether the corresponding column is a primary key; the foreign key identifier is used to identify whether the corresponding column is a foreign key;

[0061] The table information storage unit is used to perform structured storage of each table information based on a tree structure with the table ID as the parent node.

[0062] Furthermore, the table information screening module is specifically used to:

[0063] Create a relationship record table for storing the table name, table ID, column name and column ID of the associated data table, and call the preset filtering rules through the rule engine to filter the stored table information; the filtering rules are that the column name contains preset characters, the column length is a preset length, the column is a primary key, the column is a foreign key, the data type is a string or the data length is not 0.

[0064] Furthermore, the first-level matching module is specifically used to:

[0065] Through multi-threading technology, regular expressions are used to perform parallel one-to-one first-level matching on the column names in the filtered table information to obtain the first-level matching results of the data table; when a primary key or foreign key exists, the first-level matching is first performed based on the column name of the primary key or foreign key during the first-level matching process;

[0066] Generate a first SQL query statement based on the first-level matching result, query first table data from the data tables with associated relationships respectively, verify the first-level matching result based on the queried first table data, and store the first-level matching result in an associated relationship record table;

[0067] Determine whether there are any unmatched data tables. If so, enter the secondary matching module; if not, enter the ER diagram generation module;

[0068] The secondary matching module is specifically used for:

[0069] By using multi-threading technology, regular expressions are used to perform parallel one-to-many secondary matching on the table data in the table information of each of the unmatched data tables to obtain the secondary matching results of the data tables;

[0070] generating a second SQL query statement based on the secondary matching result, querying the second table data from the data tables with associated relationships respectively, verifying the secondary matching result based on the queried second table data, and storing the secondary matching result in the associated relationship record table;

[0071] Determine whether there are any unmatched data tables. If so, enter the third-level matching module; if not, enter the ER diagram generation module.

[0072] Furthermore, in the three-level matching module, the association relationship analysis model is constructed based on the data input module, the feature extraction module, the relationship reasoning module and the output module, and the loss function is constructed based on the classification cross entropy loss function and the contrast loss function;

[0073] The data input module is used to input table information; the feature extraction module is used to extract structural features, text features and statistical features from the table information; the structural features include at least the number of columns and data types; the text features include at least the column names; the statistical features include at least the mean, variance and unique value ratio; the relationship reasoning module is used to infer the association relationship of the data table based on the structural features, text features and statistical features; the output module is used to output the matching results based on the association relationship;

[0074] The training process of the association relationship analysis model is specifically as follows:

[0075] A large amount of historical table information is obtained, and after pre-processing the data cleaning of each historical table information, the association relationship is annotated, and a data set is constructed based on the annotated historical table information; the data set is divided into a training set, a validation set, and a test set based on a ratio of 8:1:1, and the association relationship analysis model is trained using the training set until the loss value of the loss function is less than a preset loss threshold. During the training process, the learning rate, random dropout rate, number of iterations, and optimizer of the association relationship analysis model are continuously optimized; the analysis accuracy is calculated using the validation set to verify the trained association relationship analysis model. If the verification fails, the data set is expanded to continue training; if the verification is successful, then:

[0076] The confidence level is calculated using the test set to test the verified association analysis model. If the test fails, the data set is expanded to continue training; if the test succeeds, the training is terminated.

[0077] Furthermore, the ER diagram generation module is specifically used to:

[0078] Based on the association relationship record table, an ER diagram of the data table is automatically generated through a drawing tool, one-to-one relationships, one-to-many relationships, and many-to-many relationships in the ER diagram are displayed differently, and the ER diagram is rendered and displayed in real time through a visual interface;

[0079] The analysis log management module is specifically used to:

[0080] An analysis log that records the association relationship of data tables in real time, wherein the analysis log includes at least the analysis time, account name, device serial number, association relationship record table, and ER diagram;

[0081] The analysis log is hashed using the SHA-256 algorithm to obtain a hash value, the analysis log and the hash value are encrypted using the AES algorithm to obtain first-level encrypted data, the first-level encrypted data is converted into ASCII code, and the ASCII code is inverted to obtain second-level encrypted data, the second-level encrypted data is encrypted using the RC6 algorithm to obtain third-level encrypted data, the third-level encrypted data is encrypted using a preset mapping rule to obtain fourth-level encrypted data, the number 1 and the letter I are swapped, the number 0 and the letter O are swapped, and the number 7 and the letter L are swapped in the fourth-level encrypted data to obtain an encrypted log, the encrypted log is stored in a specified path, and the encrypted log is uploaded to the blockchain for backup.

[0082] The advantages of the present invention are:

[0083] 1. After authenticating with the server, access the database deployed by the server, read the table information of the data table stored in the database and perform structured storage based on the tree structure; then create an association record table, filter the stored table information through the rule engine, perform a first-level match on the column names in the filtered table information, obtain the first-level matching result of the data table, verify the first-level matching result and store it in the association record table; then perform a second-level match on the unmatched table information to obtain the second-level matching result of the data table, verify the second-level matching result and store it in the association record table; then input the unmatched table information into the pre-trained association analysis model to obtain the third-level matching result of the data table, verify the third-level matching result and store it in the association record table; finally, generate the ER diagram of the data table based on the association record table for analysis It displays, records the analysis log of the association relationship of the data table in real time, encrypts and stores the analysis log, and dynamically updates the association record table based on the incremental data table; that is, after reading the table information of the data table, it first filters the table information, and then performs three-stage matching (association analysis) based on the table information, gradually completing the relationship analysis of all data tables. The analysis process combines the association analysis model and matching result verification to ensure the accuracy of the matching; the entire analysis process is carried out automatically, without the need to analyze the code and operation logs in the traditional way, without clarifying the design logic of the database, and without relying on foreign keys, ensuring the applicability and efficiency of the analysis; and the analysis process combines server authentication and encrypted storage of analysis logs to prevent related data from being illegally obtained, ultimately greatly improving the accuracy, applicability, efficiency and security of the database table association analysis.

[0084] 2. The server creates a pair of public and private keys based on the RSA algorithm and presets the public key to the client. The client sends a database access request to the server, which carries ciphertext data, signature data, device fingerprint, timestamp, and random number. The server parses the database access request to obtain the ciphertext data, signature data, device fingerprint, timestamp, and random number. It performs a first-level verification of timeliness using the timestamp, verifies the signature data using the private key for a second-level verification, matches the device fingerprint with the preset user management table for a third-level verification, decrypts the ciphertext data using the 3DES algorithm to obtain the account name and password, and performs a fourth-level verification of the legitimacy of the account name and password using the user management table. If the verification passes, HMAC calculation is performed on the random number to obtain a first MAC value, and a request response is generated based on the first MAC value and fed back to the client; the client parses the request response to obtain the first MAC value, and performs HMAC calculation on the locally stored random number to obtain a second MAC value, and verifies the first MAC value with the second MAC value to complete the authentication operation; that is, if the client wants to access the server's database, two-way verification is required. The verification process combines six security measures (timestamp, signature verification, device fingerprint matching, ciphertext data, account name and password matching, and HMAC calculation) to prevent illegal access to the database, thereby greatly improving the security of database table association analysis.

[0085] 3. By reading table information including at least table name, table ID, column name, column ID, column length, primary key identifier, foreign key identifier, data type, data length and table data, multi-dimensional data screening and matching (association analysis) can be performed based on the table information to eliminate irrelevant data, effectively improving the accuracy and efficiency of database table association relationship analysis.

[0086] 4. Through the tree structure, the table ID is used as the parent node to store the information of each table in a structured manner, which facilitates the subsequent rapid reading of table information for association analysis and further improves the efficiency of database table association relationship analysis.

[0087] 5. The rule engine calls the preset filtering rules to filter the table information, that is, retain the most valuable columns for association analysis, avoid the interference of irrelevant columns, reduce useless calculations, and combine multi-threading technology for parallel matching, effectively improving the accuracy and efficiency of database table association relationship analysis.

[0088] 6. The unmatched table information is matched through the association relationship analysis model constructed by the data input module, feature extraction module, relationship reasoning module and output module. Since the feature extraction module is used to extract structural features, text features and statistical features from the table information, that is, to extract multidimensional features for subsequent association relationship reasoning, it effectively improves the reliability of the matching result output, and the association relationship analysis model is continuously optimized, verified and tested during the training process, thereby greatly improving the accuracy of the association relationship analysis model in performing database table association relationship analysis.

[0089] 7. By displaying one-to-one relationships, one-to-many relationships, and many-to-many relationships in the ER diagram in a differentiated manner, different types of relationships can be clearly seen, greatly improving the user experience.

[0090] 8. The analysis log is hashed using the SHA-256 algorithm to obtain a hash value. The analysis log and hash value are encrypted using the AES algorithm to obtain first-level encrypted data. The first-level encrypted data is converted to ASCII code and the ASCII code is inverted to obtain second-level encrypted data. The second-level encrypted data is encrypted using the RC6 algorithm to obtain third-level encrypted data. The third-level encrypted data is encrypted using the mapping rule to obtain fourth-level encrypted data. The number 1 and the letter I, the number 0 and the letter O, and the number 7 and the letter L in the fourth-level encrypted data are swapped to obtain an encrypted log. The encrypted log is stored in a designated path and uploaded to the blockchain for backup. The analysis log storage process combines multiple encryption algorithms and data transformation rules. Without knowing any of these algorithms or data transformation rules, the encrypted log cannot be decrypted. At least seven security measures are implemented (SHA-256 algorithm, AES algorithm, ASCII code conversion, RC6 algorithm, mapping rules, character swapping, and blockchain) to prevent the analysis log from being stolen and tampered with in plain text. This greatly improves the security of analysis log storage and facilitates later traceability. BRIEF DESCRIPTION OF THE DRAWINGS

[0091] The present invention will be further described below with reference to the accompanying drawings and embodiments.

[0092] Figure 1 The present invention is a flowchart of a method for analyzing database table association relationships.

[0093] Figure 2 It is a structural diagram of a database table association relationship analysis system of the present invention. DETAILED DESCRIPTION

[0094] The technical solution in the embodiment of the present application has the following overall idea: after reading the table information of the data table, the table information is first screened, and then three-stage matching is performed based on the table information to gradually complete the relationship analysis of all data tables. The analysis process combines the association analysis model and matching result verification to ensure the accuracy of the matching; the entire analysis process is carried out automatically, without the need to analyze the code and operation logs as traditionally, without clarifying the design logic of the database, and without relying on foreign keys, thereby ensuring the applicability and efficiency of the analysis; and the analysis process combines server authentication and encrypted storage of analysis logs to prevent related data from being illegally obtained, thereby improving the accuracy, applicability, efficiency and security of the database table association analysis.

[0095] Please refer to Figures 1 to 2 As shown, a preferred embodiment of a database table association relationship analysis method of the present invention includes the following steps:

[0096] Step S10: After authentication with the server, access the database deployed by the server, read the table information of the data table stored in the database, and perform structured storage of each table information based on a tree structure;

[0097] By using the RSA algorithm, 3DES encryption, HMAC verification, and four-layer verification (timeliness, signature, device fingerprint, and account legitimacy), we ensure the security of database access, prevent unauthorized access and data leakage, and combine client and server two-way verification (random number generation and MAC value comparison) to avoid man-in-the-middle attacks;

[0098] Step S20: Create an association relationship record table, and filter the stored table information through a rule engine;

[0099] Step S30: performing a primary match on the column names in the filtered table information to obtain a primary match result of the data table, verifying the primary match result, and storing the primary match result in an association record table;

[0100] Step S40: Perform secondary matching on the unmatched table information to obtain a secondary matching result of the data table, verify the secondary matching result, and store the secondary matching result in the association relationship record table;

[0101] Step S50: Input the unmatched table information into the pre-trained association analysis model to obtain a three-level matching result for the data table. After verifying the three-level matching result, the three-level matching result is stored in the association record table. Through the three-level matching, both explicit and implicit associations are covered, thereby improving the comprehensiveness of identification (analysis);

[0102] Step S60: Generate an ER diagram of the data table based on the association relationship record table, and display the ER diagram through a visual interface; in specific implementation, the association relationship record table can be output through Excel;

[0103] Step S70: Recording an analysis log of the association relationship of the data table in real time, and encrypting and storing the analysis log;

[0104] Step S80: dynamically updating the association relationship record table based on the incremental data table;

[0105] Through multi-level security authentication, hierarchical (layered) association matching, dynamic model training, incremental updates and visual design, efficient, secure and accurate database table association analysis is achieved. At the same time, it has good scalability and compliance, and is suitable for complex enterprise-level database management scenarios.

[0106] The step S10 specifically includes:

[0107] Step S11: The server creates a pair of public and private keys based on the RSA algorithm, and presets the public key to the client;

[0108] Based on the database address, the client sends a database access request to the server, which carries ciphertext data, signature data, device fingerprint, timestamp, and random number. The ciphertext data is obtained by encrypting the account name and password using the 3DES algorithm. The signature data is obtained by signing the account name and password using the public key. The device fingerprint is obtained by hashing the client's device serial number. The random number is a natural number randomly generated by the client and stored locally on the client.

[0109] The server parses the received database access request to obtain ciphertext data, signature data, device fingerprint, timestamp, and random number, performs a first-level verification of timeliness using the timestamp, verifies the signature data using the private key for a second-level verification, matches the device fingerprint using a preset user management table for a third-level verification, decrypts the ciphertext data using a 3DES algorithm to obtain an account name and password, performs a fourth-level verification of the legitimacy of the account name and password using the user management table, and if the verification passes, performs an HMAC calculation on the random number to obtain a first MAC value, generates a request response based on the first MAC value, and feeds the request response back to the client;

[0110] The client parses the received request response to obtain a first MAC value, performs HMAC calculation on the locally stored random number to obtain a second MAC value, and verifies the first MAC value using the second MAC value to complete the authentication operation;

[0111] The server creates a pair of public and private keys based on the RSA algorithm and presets the public key to the client; the client sends a database access request carrying ciphertext data, signature data, device fingerprint, timestamp and random number to the server; the server parses the database access request to obtain ciphertext data, signature data, device fingerprint, timestamp and random number, performs a first-level verification of timeliness through the timestamp, verifies the signature data through the private key for a second-level verification, matches the device fingerprint through the preset user management table for a third-level verification, decrypts the ciphertext data through the 3DES algorithm to obtain the account name and password, and performs a fourth-level verification of the legitimacy of the account name and password through the user management table. If the verification is successful, HMAC calculation is performed on the random number to obtain a first MAC value, and a request response is generated based on the first MAC value and fed back to the client; the client parses the request response to obtain the first MAC value, and performs HMAC calculation on the locally stored random number to obtain a second MAC value, and verifies the first MAC value with the second MAC value to complete the authentication operation; that is, if the client wants to access the server's database, two-way verification is required. The verification process combines 6 security measures (timestamp, signature verification, device fingerprint matching, ciphertext data, account name and password matching, HMAC calculation) to prevent illegal access to the database, thereby greatly improving the security of database table association analysis.

[0112] Step S12: The client accesses the database deployed by the server and reads the table information of the data table stored in the database in real time; the table information includes at least the table name, table ID, column name, column ID, column length, primary key identifier, foreign key identifier, data type, data length, and table data; the primary key identifier is used to identify whether the corresponding column is a primary key; the foreign key identifier is used to identify whether the corresponding column is a foreign key;

[0113] By reading table information including at least the table name, table ID, column name, column ID, column length, primary key identifier, foreign key identifier, data type, data length, and table data, multi-dimensional data screening and matching (association analysis) can be performed based on the table information to eliminate irrelevant data, effectively improving the accuracy and efficiency of database table association analysis.

[0114] Step S13: Based on the tree structure, the table ID is used as the parent node to perform structured storage on the information of each table.

[0115] Through the tree structure, the table ID is used as the parent node to store the information of each table in a structured manner, which facilitates the subsequent rapid reading of table information for association analysis, and facilitates rapid retrieval and hierarchical management, further improving the efficiency of database table association relationship analysis.

[0116] The step S20 is specifically as follows:

[0117] Create a relationship record table for storing the table name, table ID, column name and column ID of the associated data table, and call the preset filtering rules through the rule engine to filter the stored table information; the filtering rules are that the column name contains preset characters, the column length is a preset length, the column is a primary key, the column is a foreign key, the data type is a string or the data length is not 0.

[0118] The rule engine calls preset filtering rules to filter table information, that is, retaining the most valuable columns for association analysis, avoiding interference from irrelevant columns, reducing useless calculations, and combining multi-threading technology for parallel matching, effectively improving the accuracy and efficiency of database table association relationship analysis.

[0119] The step S30 is specifically as follows:

[0120] Through multi-threading technology, regular expressions are used to perform parallel one-to-one first-level matching on the column names in the filtered table information to obtain the first-level matching results of the data table; when a primary key or foreign key exists, the first-level matching is first performed based on the column name of the primary key or foreign key during the first-level matching process;

[0121] During specific implementation, if a data table has multiple columns identified as foreign keys of other data tables, we will further determine whether the corresponding table data in these columns are repeated. After excluding the columns with uniqueness, we will determine the number of foreign keys again. If there are multiple foreign keys, these columns will be combined to generate a group query statement to see whether it can uniquely lock a record of a data table. If so, the columns corresponding to this combination will be additionally identified as joint foreign keys.

[0122] Generate a first SQL query statement based on the first-level matching result, query first table data from the data tables with associated relationships respectively, verify the first-level matching result based on the queried first table data, and store the first-level matching result in an associated relationship record table;

[0123] Determine whether there is any unmatched data table, if so, proceed to step S40; if not, proceed to step S60;

[0124] Through multi-threaded parallel processing and regular expression matching, the efficiency of data screening and association analysis is significantly improved, making it suitable for large-scale database scenarios.

[0125] The step S40 is specifically as follows:

[0126] By using multi-threading technology, regular expressions are used to perform parallel one-to-many secondary matching on the table data in the table information of each of the unmatched data tables to obtain the secondary matching results of the data tables;

[0127] generating a second SQL query statement based on the secondary matching result, querying the second table data from the data tables with associated relationships respectively, verifying the secondary matching result based on the queried second table data, and storing the secondary matching result in the associated relationship record table;

[0128] It is determined whether there is any unmatched data table. If so, the process proceeds to step S50; if not, the process proceeds to step S60.

[0129] In step S50, the association relationship analysis model is constructed based on a data input module, a feature extraction module, a relationship reasoning module, and an output module, and the loss function is constructed based on a classification cross entropy loss function and a contrast loss function; the contrast loss function can shorten the distance between the association column vectors;

[0130] The data input module is used to input table information; the feature extraction module is used to extract structural features, text features and statistical features from the table information; the structural features include at least the number of columns and data types; the text features include at least the column names; the statistical features include at least the mean, variance and unique value ratio; the relationship reasoning module is used to infer the association relationship of the data table based on the structural features, text features and statistical features; the output module is used to output the matching results based on the association relationship;

[0131] The training process of the association relationship analysis model is specifically as follows:

[0132] A large amount of historical table information is obtained, and after pre-processing the data cleaning of each historical table information, the association relationship is annotated, and a data set is constructed based on the annotated historical table information; the data set is divided into a training set, a validation set, and a test set based on a ratio of 8:1:1, and the association relationship analysis model is trained using the training set until the loss value of the loss function is less than a preset loss threshold. During the training process, the learning rate, random dropout rate, number of iterations, and optimizer of the association relationship analysis model are continuously optimized; the analysis accuracy is calculated using the validation set to verify the trained association relationship analysis model. If the verification fails, the data set is expanded to continue training; if the verification is successful, then:

[0133] The confidence level is calculated using the test set to test the verified association analysis model. If the test fails, the data set is expanded to continue training; if the test succeeds, the training is terminated.

[0134] The unmatched table information is matched through the association relationship analysis model constructed by the data input module, feature extraction module, relationship reasoning module and output module. Since the feature extraction module is used to extract structural features, text features and statistical features from the table information, that is, to extract multidimensional features for subsequent association relationship reasoning, the reliability of the matching result output is effectively improved. In addition, the association relationship analysis model is continuously optimized, verified and tested during the training process, thereby greatly improving the accuracy of the association relationship analysis model in performing database table association relationship analysis.

[0135] The step S60 is specifically as follows:

[0136] Based on the association relationship record table, an ER diagram of the data table is automatically generated through a drawing tool, one-to-one relationships, one-to-many relationships, and many-to-many relationships in the ER diagram are displayed differently, and the ER diagram is rendered and displayed in real time through a visual interface;

[0137] By displaying one-to-one relationships, one-to-many relationships, and many-to-many relationships in ER diagrams in a differentiated manner, different types of relationships can be clearly seen, greatly improving the user experience.

[0138] The step S70 is specifically as follows:

[0139] An analysis log that records the association relationship of data tables in real time, wherein the analysis log includes at least the analysis time, account name, device serial number, association relationship record table, and ER diagram;

[0140] The analysis log is hashed using the SHA-256 algorithm to obtain a hash value, the analysis log and the hash value are encrypted using the AES algorithm to obtain first-level encrypted data, the first-level encrypted data is converted into ASCII code, and the ASCII code is inverted to obtain second-level encrypted data, the second-level encrypted data is encrypted using the RC6 algorithm to obtain third-level encrypted data, the third-level encrypted data is encrypted using a preset mapping rule to obtain fourth-level encrypted data, the number 1 and the letter I are swapped, the number 0 and the letter O are swapped, and the number 7 and the letter L are swapped in the fourth-level encrypted data to obtain an encrypted log, the encrypted log is stored in a specified path, and the encrypted log is uploaded to the blockchain for backup.

[0141] The analysis log is hashed using the SHA-256 algorithm to obtain a hash value. The analysis log and hash value are encrypted using the AES algorithm to obtain first-level encrypted data. The first-level encrypted data is converted to ASCII code and the ASCII code is inverted to obtain second-level encrypted data. The second-level encrypted data is encrypted using the RC6 algorithm to obtain third-level encrypted data. The third-level encrypted data is encrypted using the mapping rule to obtain fourth-level encrypted data. The number 1 and the letter I, the number 0 and the letter O, and the number 7 and the letter L in the fourth-level encrypted data are swapped to obtain an encrypted log. The encrypted log is stored in a designated path and uploaded to the blockchain for backup. In other words, the analysis log storage process combines multiple encryption algorithms and data transformation rules. Without knowing any of these algorithms or data transformation rules, the encrypted log cannot be decrypted. At least seven security measures are implemented (SHA-256 algorithm, AES algorithm, ASCII code conversion, RC6 algorithm, mapping rules, character swapping, and blockchain) to prevent the analysis log from being stolen and tampered with in plain text. This greatly improves the security of analysis log storage and facilitates later traceability.

[0142] A preferred embodiment of a database table association relationship analysis system of the present invention includes the following modules:

[0143] A table information reading module is used to access the database deployed by the server after authentication with the server, read the table information of the data table stored in the database, and perform structured storage of each table information based on a tree structure;

[0144] By using the RSA algorithm, 3DES encryption, HMAC verification, and four-layer verification (timeliness, signature, device fingerprint, and account legitimacy), we ensure the security of database access, prevent unauthorized access and data leakage, and combine client and server two-way verification (random number generation and MAC value comparison) to avoid man-in-the-middle attacks;

[0145] A table information screening module is used to create an association relationship record table and screen the stored table information through a rule engine;

[0146] A primary matching module, configured to perform a primary match on the column names in the filtered table information to obtain a primary matching result of the data table, verify the primary matching result, and store the primary matching result in an association record table;

[0147] a secondary matching module, configured to perform secondary matching on the unmatched table information to obtain a secondary matching result of the data table, verify the secondary matching result, and store the secondary matching result in an association record table;

[0148] A three-level matching module is used to input the unmatched table information into the pre-trained association analysis model to obtain the three-level matching results of the data table, verify the three-level matching results, and store the three-level matching results in the association record table; through the three-level matching, both explicit and implicit associations are covered, thereby improving the comprehensiveness of identification (analysis);

[0149] An ER diagram generation module is used to generate an ER diagram of a data table based on the association relationship record table, and display the ER diagram through a visual interface; in specific implementation, the association relationship record table can be output through Excel;

[0150] An analysis log management module is used to record the analysis log of the association relationship of the data table in real time and encrypt and store the analysis log;

[0151] A data table increment management module, configured to dynamically update the association relationship record table based on the incremented data table;

[0152] Through multi-level security authentication, hierarchical (layered) association matching, dynamic model training, incremental updates and visual design, efficient, secure and accurate database table association analysis is achieved. At the same time, it has good scalability and compliance, and is suitable for complex enterprise-level database management scenarios.

[0153] The table information reading module specifically includes:

[0154] An authentication unit is used for the server to create a pair of public and private keys based on the RSA algorithm and to pre-set the public key to the client;

[0155] Based on the database address, the client sends a database access request to the server, which carries ciphertext data, signature data, device fingerprint, timestamp, and random number. The ciphertext data is obtained by encrypting the account name and password using the 3DES algorithm. The signature data is obtained by signing the account name and password using the public key. The device fingerprint is obtained by hashing the client's device serial number. The random number is a natural number randomly generated by the client and stored locally on the client.

[0156] The server parses the received database access request to obtain ciphertext data, signature data, device fingerprint, timestamp, and random number, performs a first-level verification of timeliness using the timestamp, verifies the signature data using the private key for a second-level verification, matches the device fingerprint using a preset user management table for a third-level verification, decrypts the ciphertext data using a 3DES algorithm to obtain an account name and password, performs a fourth-level verification of the legitimacy of the account name and password using the user management table, and if the verification passes, performs an HMAC calculation on the random number to obtain a first MAC value, generates a request response based on the first MAC value, and feeds the request response back to the client;

[0157] The client parses the received request response to obtain a first MAC value, performs HMAC calculation on the locally stored random number to obtain a second MAC value, and verifies the first MAC value using the second MAC value to complete the authentication operation;

[0158] The server creates a pair of public and private keys based on the RSA algorithm and presets the public key to the client; the client sends a database access request carrying ciphertext data, signature data, device fingerprint, timestamp and random number to the server; the server parses the database access request to obtain ciphertext data, signature data, device fingerprint, timestamp and random number, performs a first-level verification of timeliness through the timestamp, verifies the signature data through the private key for a second-level verification, matches the device fingerprint through the preset user management table for a third-level verification, decrypts the ciphertext data through the 3DES algorithm to obtain the account name and password, and performs a fourth-level verification of the legitimacy of the account name and password through the user management table. If the verification is successful, HMAC calculation is performed on the random number to obtain a first MAC value, and a request response is generated based on the first MAC value and fed back to the client; the client parses the request response to obtain the first MAC value, and performs HMAC calculation on the locally stored random number to obtain a second MAC value, and verifies the first MAC value with the second MAC value to complete the authentication operation; that is, if the client wants to access the server's database, two-way verification is required. The verification process combines 6 security measures (timestamp, signature verification, device fingerprint matching, ciphertext data, account name and password matching, HMAC calculation) to prevent illegal access to the database, thereby greatly improving the security of database table association analysis.

[0159] A database access unit, configured for a client to access a database deployed by a server and read in real time table information of a data table stored in the database; the table information includes at least a table name, table ID, column name, column ID, column length, primary key identifier, foreign key identifier, data type, data length, and table data; the primary key identifier is used to identify whether the corresponding column is a primary key; the foreign key identifier is used to identify whether the corresponding column is a foreign key;

[0160] By reading table information including at least the table name, table ID, column name, column ID, column length, primary key identifier, foreign key identifier, data type, data length, and table data, multi-dimensional data screening and matching (association analysis) can be performed based on the table information to eliminate irrelevant data, effectively improving the accuracy and efficiency of database table association analysis.

[0161] The table information storage unit is used to perform structured storage of each table information based on a tree structure with the table ID as the parent node.

[0162] Through the tree structure, the table ID is used as the parent node to store the information of each table in a structured manner, which facilitates the subsequent rapid reading of table information for association analysis, and facilitates rapid retrieval and hierarchical management, further improving the efficiency of database table association relationship analysis.

[0163] The table information screening module is specifically used for:

[0164] Create a relationship record table for storing the table name, table ID, column name and column ID of the associated data table, and call the preset filtering rules through the rule engine to filter the stored table information; the filtering rules are that the column name contains preset characters, the column length is a preset length, the column is a primary key, the column is a foreign key, the data type is a string or the data length is not 0.

[0165] The rule engine calls preset filtering rules to filter table information, that is, retaining the most valuable columns for association analysis, avoiding interference from irrelevant columns, reducing useless calculations, and combining multi-threading technology for parallel matching, effectively improving the accuracy and efficiency of database table association relationship analysis.

[0166] The first-level matching module is specifically used for:

[0167] Through multi-threading technology, regular expressions are used to perform parallel one-to-one first-level matching on the column names in the filtered table information to obtain the first-level matching results of the data table; when a primary key or foreign key exists, the first-level matching is first performed based on the column name of the primary key or foreign key during the first-level matching process;

[0168] During specific implementation, if a data table has multiple columns identified as foreign keys of other data tables, we will further determine whether the corresponding table data in these columns are repeated. After excluding the columns with uniqueness, we will determine the number of foreign keys again. If there are multiple foreign keys, these columns will be combined to generate a group query statement to see whether it can uniquely lock a record of a data table. If so, the columns corresponding to this combination will be additionally identified as joint foreign keys.

[0169] Generate a first SQL query statement based on the first-level matching result, query first table data from the data tables with associated relationships respectively, verify the first-level matching result based on the queried first table data, and store the first-level matching result in an associated relationship record table;

[0170] Determine whether there are any unmatched data tables. If so, enter the secondary matching module; if not, enter the ER diagram generation module;

[0171] Through multi-threaded parallel processing and regular expression matching, the efficiency of data screening and association analysis is significantly improved, making it suitable for large-scale database scenarios.

[0172] The secondary matching module is specifically used for:

[0173] By using multi-threading technology, regular expressions are used to perform parallel one-to-many secondary matching on the table data in the table information of each of the unmatched data tables to obtain the secondary matching results of the data tables;

[0174] generating a second SQL query statement based on the secondary matching result, querying the second table data from the data tables with associated relationships respectively, verifying the secondary matching result based on the queried second table data, and storing the secondary matching result in the associated relationship record table;

[0175] Determine whether there are any unmatched data tables. If so, enter the third-level matching module; if not, enter the ER diagram generation module.

[0176] In the three-level matching module, the association relationship analysis model is constructed based on a data input module, a feature extraction module, a relationship reasoning module, and an output module, and the loss function is constructed based on a classification cross entropy loss function and a contrast loss function; the contrast loss function can shorten the distance between the association column vectors;

[0177] The data input module is used to input table information; the feature extraction module is used to extract structural features, text features and statistical features from the table information; the structural features include at least the number of columns and data types; the text features include at least the column names; the statistical features include at least the mean, variance and unique value ratio; the relationship reasoning module is used to infer the association relationship of the data table based on the structural features, text features and statistical features; the output module is used to output the matching results based on the association relationship;

[0178] The training process of the association relationship analysis model is specifically as follows:

[0179] A large amount of historical table information is obtained, and after pre-processing the data cleaning of each historical table information, the association relationship is annotated, and a data set is constructed based on the annotated historical table information; the data set is divided into a training set, a validation set, and a test set based on a ratio of 8:1:1, and the association relationship analysis model is trained using the training set until the loss value of the loss function is less than a preset loss threshold. During the training process, the learning rate, random dropout rate, number of iterations, and optimizer of the association relationship analysis model are continuously optimized; the analysis accuracy is calculated using the validation set to verify the trained association relationship analysis model. If the verification fails, the data set is expanded to continue training; if the verification is successful, then:

[0180] The confidence level is calculated using the test set to test the verified association analysis model. If the test fails, the data set is expanded to continue training; if the test succeeds, the training is terminated.

[0181] The unmatched table information is matched through the association relationship analysis model constructed by the data input module, feature extraction module, relationship reasoning module and output module. Since the feature extraction module is used to extract structural features, text features and statistical features from the table information, that is, to extract multidimensional features for subsequent association relationship reasoning, the reliability of the matching result output is effectively improved. In addition, the association relationship analysis model is continuously optimized, verified and tested during the training process, thereby greatly improving the accuracy of the association relationship analysis model in performing database table association relationship analysis.

[0182] The ER diagram generation module is specifically used for:

[0183] Based on the association relationship record table, an ER diagram of the data table is automatically generated through a drawing tool, one-to-one relationships, one-to-many relationships, and many-to-many relationships in the ER diagram are displayed differently, and the ER diagram is rendered and displayed in real time through a visual interface;

[0184] By displaying one-to-one relationships, one-to-many relationships, and many-to-many relationships in ER diagrams in a differentiated manner, different types of relationships can be clearly seen, greatly improving the user experience.

[0185] The analysis log management module is specifically used to:

[0186] An analysis log that records the association relationship of data tables in real time, wherein the analysis log includes at least the analysis time, account name, device serial number, association relationship record table, and ER diagram;

[0187] The analysis log is hashed using the SHA-256 algorithm to obtain a hash value, the analysis log and the hash value are encrypted using the AES algorithm to obtain first-level encrypted data, the first-level encrypted data is converted into ASCII code, and the ASCII code is inverted to obtain second-level encrypted data, the second-level encrypted data is encrypted using the RC6 algorithm to obtain third-level encrypted data, the third-level encrypted data is encrypted using a preset mapping rule to obtain fourth-level encrypted data, the number 1 and the letter I are swapped, the number 0 and the letter O are swapped, and the number 7 and the letter L are swapped in the fourth-level encrypted data to obtain an encrypted log, the encrypted log is stored in a specified path, and the encrypted log is uploaded to the blockchain for backup.

[0188] The analysis log is hashed using the SHA-256 algorithm to obtain a hash value. The analysis log and hash value are encrypted using the AES algorithm to obtain first-level encrypted data. The first-level encrypted data is converted to ASCII code and the ASCII code is inverted to obtain second-level encrypted data. The second-level encrypted data is encrypted using the RC6 algorithm to obtain third-level encrypted data. The third-level encrypted data is encrypted using the mapping rule to obtain fourth-level encrypted data. The number 1 and the letter I, the number 0 and the letter O, and the number 7 and the letter L in the fourth-level encrypted data are swapped to obtain an encrypted log. The encrypted log is stored in a designated path and uploaded to the blockchain for backup. In other words, the analysis log storage process combines multiple encryption algorithms and data transformation rules. Without knowing any of these algorithms or data transformation rules, the encrypted log cannot be decrypted. At least seven security measures are implemented (SHA-256 algorithm, AES algorithm, ASCII code conversion, RC6 algorithm, mapping rules, character swapping, and blockchain) to prevent the analysis log from being stolen and tampered with in plain text. This greatly improves the security of analysis log storage and facilitates later traceability.

[0189] In summary, the advantages of the present invention are:

[0190] 1. After authenticating with the server, access the database deployed by the server, read the table information of the data table stored in the database and perform structured storage based on the tree structure; then create an association record table, filter the stored table information through the rule engine, perform a first-level match on the column names in the filtered table information, obtain the first-level matching result of the data table, verify the first-level matching result and store it in the association record table; then perform a second-level match on the unmatched table information to obtain the second-level matching result of the data table, verify the second-level matching result and store it in the association record table; then input the unmatched table information into the pre-trained association analysis model to obtain the third-level matching result of the data table, verify the third-level matching result and store it in the association record table; finally, generate the ER diagram of the data table based on the association record table for analysis It displays, records the analysis log of the association relationship of the data table in real time, encrypts and stores the analysis log, and dynamically updates the association record table based on the incremental data table; that is, after reading the table information of the data table, it first filters the table information, and then performs three-stage matching (association analysis) based on the table information, gradually completing the relationship analysis of all data tables. The analysis process combines the association analysis model and matching result verification to ensure the accuracy of the matching; the entire analysis process is carried out automatically, without the need to analyze the code and operation logs in the traditional way, without clarifying the design logic of the database, and without relying on foreign keys, ensuring the applicability and efficiency of the analysis; and the analysis process combines server authentication and encrypted storage of analysis logs to prevent related data from being illegally obtained, ultimately greatly improving the accuracy, applicability, efficiency and security of the database table association analysis.

[0191] 2. The server creates a pair of public and private keys based on the RSA algorithm and presets the public key to the client. The client sends a database access request to the server, which carries ciphertext data, signature data, device fingerprint, timestamp, and random number. The server parses the database access request to obtain the ciphertext data, signature data, device fingerprint, timestamp, and random number. It performs a first-level verification of timeliness using the timestamp, verifies the signature data using the private key for a second-level verification, matches the device fingerprint with the preset user management table for a third-level verification, decrypts the ciphertext data using the 3DES algorithm to obtain the account name and password, and performs a fourth-level verification of the legitimacy of the account name and password using the user management table. If the verification passes, HMAC calculation is performed on the random number to obtain a first MAC value, and a request response is generated based on the first MAC value and fed back to the client; the client parses the request response to obtain the first MAC value, and performs HMAC calculation on the locally stored random number to obtain a second MAC value, and verifies the first MAC value with the second MAC value to complete the authentication operation; that is, if the client wants to access the server's database, two-way verification is required. The verification process combines six security measures (timestamp, signature verification, device fingerprint matching, ciphertext data, account name and password matching, and HMAC calculation) to prevent illegal access to the database, thereby greatly improving the security of database table association analysis.

[0192] 3. By reading table information including at least table name, table ID, column name, column ID, column length, primary key identifier, foreign key identifier, data type, data length and table data, multi-dimensional data screening and matching (association analysis) can be performed based on the table information to eliminate irrelevant data, effectively improving the accuracy and efficiency of database table association relationship analysis.

[0193] 4. Through the tree structure, the table ID is used as the parent node to store the information of each table in a structured manner, which facilitates the subsequent rapid reading of table information for association analysis and further improves the efficiency of database table association relationship analysis.

[0194] 5. The rule engine calls the preset filtering rules to filter the table information, that is, retain the most valuable columns for association analysis, avoid the interference of irrelevant columns, reduce useless calculations, and combine multi-threading technology for parallel matching, effectively improving the accuracy and efficiency of database table association relationship analysis.

[0195] 6. The unmatched table information is matched through the association relationship analysis model constructed by the data input module, feature extraction module, relationship reasoning module and output module. Since the feature extraction module is used to extract structural features, text features and statistical features from the table information, that is, to extract multidimensional features for subsequent association relationship reasoning, it effectively improves the reliability of the matching result output, and the association relationship analysis model is continuously optimized, verified and tested during the training process, thereby greatly improving the accuracy of the association relationship analysis model in performing database table association relationship analysis.

[0196] 7. By displaying one-to-one relationships, one-to-many relationships, and many-to-many relationships in the ER diagram in a differentiated manner, different types of relationships can be clearly seen, greatly improving the user experience.

[0197] 8. The analysis log is hashed using the SHA-256 algorithm to obtain a hash value. The analysis log and hash value are encrypted using the AES algorithm to obtain first-level encrypted data. The first-level encrypted data is converted to ASCII code and the ASCII code is inverted to obtain second-level encrypted data. The second-level encrypted data is encrypted using the RC6 algorithm to obtain third-level encrypted data. The third-level encrypted data is encrypted using the mapping rule to obtain fourth-level encrypted data. The number 1 and the letter I, the number 0 and the letter O, and the number 7 and the letter L in the fourth-level encrypted data are swapped to obtain an encrypted log. The encrypted log is stored in a designated path and uploaded to the blockchain for backup. The analysis log storage process combines multiple encryption algorithms and data transformation rules. Without knowing any of these algorithms or data transformation rules, the encrypted log cannot be decrypted. At least seven security measures are implemented (SHA-256 algorithm, AES algorithm, ASCII code conversion, RC6 algorithm, mapping rules, character swapping, and blockchain) to prevent the analysis log from being stolen and tampered with in plain text. This greatly improves the security of analysis log storage and facilitates later traceability.

[0198] Although the specific embodiments of the present invention are described above, those skilled in the art should understand that the specific embodiments described are merely illustrative and are not intended to limit the scope of the present invention. Equivalent modifications and changes made by those skilled in the art in accordance with the spirit of the present invention should be included within the scope of protection of the claims of the present invention.

Claims

1. A database table association relationship analysis method, characterized by: The steps include: Step S10: After authentication with the server, access the database deployed by the server, read the table information of the data table stored in the database, and perform structured storage of each table information based on a tree structure; Step S20: Create an association relationship record table, and filter the stored table information through a rule engine; Step S30: performing a primary match on the column names in the filtered table information to obtain a primary match result of the data table, verifying the primary match result, and storing the primary match result in an association record table; Step S40: Perform secondary matching on the unmatched table information to obtain a secondary matching result of the data table, verify the secondary matching result, and store the secondary matching result in the association relationship record table; Step S50: inputting the unmatched table information into the pre-trained association relationship analysis model to obtain the three-level matching results of the data table, verifying the three-level matching results, and storing the three-level matching results in the association relationship record table; Step S60: Generate an ER diagram of the data table based on the association relationship record table, and display the ER diagram through a visual interface; Step S70: Recording an analysis log of the association relationship of the data table in real time, and encrypting and storing the analysis log; Step S80: dynamically updating the association relationship record table based on the incremental data table; The step S30 is specifically as follows: Through multi-threading technology, regular expressions are used to perform parallel one-to-one first-level matching on the column names in the filtered table information to obtain the first-level matching results of the data table; when a primary key or foreign key exists, the first-level matching is first performed based on the column name of the primary key or foreign key during the first-level matching process; Generate a first SQL query statement based on the first-level matching result, query first table data from the data tables with associated relationships respectively, verify the first-level matching result based on the queried first table data, and store the first-level matching result in an associated relationship record table; Determine whether there is any unmatched data table, if so, proceed to step S40; If not, go to step S60.

2. A database table association relationship analysis method according to claim 1, characterized in that: The step S10 specifically includes: Step S11: The server creates a pair of public and private keys based on the RSA algorithm, and presets the public key to the client; Based on the database address, the client sends a database access request to the server, which carries ciphertext data, signature data, device fingerprint, timestamp, and random number. The ciphertext data is obtained by encrypting the account name and password using the 3DES algorithm. The signature data is obtained by signing the account name and password using the public key. The device fingerprint is obtained by hashing the client's device serial number. The random number is a natural number randomly generated by the client and stored locally on the client. The server parses the received database access request to obtain ciphertext data, signature data, device fingerprint, timestamp, and random number, performs a first-level verification of timeliness using the timestamp, verifies the signature data using the private key for a second-level verification, matches the device fingerprint using a preset user management table for a third-level verification, decrypts the ciphertext data using a 3DES algorithm to obtain an account name and password, performs a fourth-level verification of the legitimacy of the account name and password using the user management table, and if the verification passes, performs an HMAC calculation on the random number to obtain a first MAC value, generates a request response based on the first MAC value, and feeds the request response back to the client; The client parses the received request response to obtain a first MAC value, performs HMAC calculation on the locally stored random number to obtain a second MAC value, and verifies the first MAC value using the second MAC value to complete the authentication operation; Step S12: The client accesses the database deployed by the server and reads the table information of the data table stored in the database in real time; the table information includes at least the table name, table ID, column name, column ID, column length, primary key identifier, foreign key identifier, data type, data length, and table data; the primary key identifier is used to identify whether the corresponding column is a primary key; the foreign key identifier is used to identify whether the corresponding column is a foreign key; Step S13: Based on the tree structure, the table ID is used as the parent node to perform structured storage on the information of each table; The step S20 is specifically as follows: Create a relationship record table for storing the table name, table ID, column name and column ID of the associated data table, and call the preset filtering rules through the rule engine to filter the stored table information; the filtering rules are that the column name contains preset characters, the column length is a preset length, the column is a primary key, the column is a foreign key, the data type is a string or the data length is not 0.

3. The method for analyzing database table association relationships according to claim 1, wherein: The step S40 is specifically as follows: By using multi-threading technology, regular expressions are used to perform parallel one-to-many secondary matching on the table data in the table information of each of the unmatched data tables to obtain the secondary matching results of the data tables; generating a second SQL query statement based on the secondary matching result, querying the second table data from the data tables with associated relationships respectively, verifying the secondary matching result based on the queried second table data, and storing the secondary matching result in the associated relationship record table; It is determined whether there is any unmatched data table. If so, the process proceeds to step S50; if not, the process proceeds to step S60.

4. The method for analyzing database table association relationships according to claim 1, wherein: In step S50, the association relationship analysis model is constructed based on the data input module, the feature extraction module, the relationship reasoning module and the output module, and the loss function is constructed based on the classification cross entropy loss function and the contrast loss function; The data input module is used to input table information; the feature extraction module is used to extract structural features, text features and statistical features from the table information; The structural features include at least the number of columns and the data type; the text features include at least the column name; the statistical features include at least the mean, variance, and unique value ratio; the relationship reasoning module is used to infer the association relationship of the data table based on the structural features, text features, and statistical features; the output module is used to output the matching result based on the association relationship; The training process of the association relationship analysis model is specifically as follows: A large amount of historical table information is obtained, and after pre-processing the data cleaning of each historical table information, the association relationship is annotated, and a data set is constructed based on the annotated historical table information; the data set is divided into a training set, a validation set, and a test set based on a ratio of 8:1:1, and the association relationship analysis model is trained using the training set until the loss value of the loss function is less than a preset loss threshold. During the training process, the learning rate, random dropout rate, number of iterations, and optimizer of the association relationship analysis model are continuously optimized; the analysis accuracy is calculated using the validation set to verify the trained association relationship analysis model. If the verification fails, the data set is expanded to continue training; if the verification is successful, then: The confidence level is calculated using the test set to test the verified association analysis model. If the test fails, the data set is expanded to continue training; if the test succeeds, the training is terminated.

5. The method for analyzing database table association relationships according to claim 1, wherein: The step S60 is specifically as follows: Based on the association relationship record table, an ER diagram of the data table is automatically generated through a drawing tool, one-to-one relationships, one-to-many relationships, and many-to-many relationships in the ER diagram are displayed differently, and the ER diagram is rendered and displayed in real time through a visual interface; The step S70 is specifically as follows: An analysis log that records the association relationship of data tables in real time, wherein the analysis log includes at least the analysis time, account name, device serial number, association relationship record table, and ER diagram; The analysis log is hashed using the SHA-256 algorithm to obtain a hash value, the analysis log and the hash value are encrypted using the AES algorithm to obtain first-level encrypted data, the first-level encrypted data is converted into ASCII code, and the ASCII code is inverted to obtain second-level encrypted data, the second-level encrypted data is encrypted using the RC6 algorithm to obtain third-level encrypted data, the third-level encrypted data is encrypted using a preset mapping rule to obtain fourth-level encrypted data, the number 1 and the letter I are swapped, the number 0 and the letter O are swapped, and the number 7 and the letter L are swapped in the fourth-level encrypted data to obtain an encrypted log, the encrypted log is stored in a specified path, and the encrypted log is uploaded to the blockchain for backup.

6. A database table association relationship analysis system, characterized by: Includes the following modules: A table information reading module is used to access the database deployed by the server after authentication with the server, read the table information of the data table stored in the database, and perform structured storage of each table information based on a tree structure; A table information screening module is used to create an association relationship record table and screen the stored table information through a rule engine; A primary matching module, configured to perform a primary match on the column names in the filtered table information to obtain a primary matching result of the data table, verify the primary matching result, and store the primary matching result in an association record table; a secondary matching module, configured to perform secondary matching on the unmatched table information to obtain a secondary matching result of the data table, verify the secondary matching result, and store the secondary matching result in an association record table; A three-level matching module is used to input the unmatched table information into the pre-trained association relationship analysis model to obtain the three-level matching results of the data table, verify the three-level matching results, and store the three-level matching results in the association relationship record table; An ER diagram generation module is used to generate an ER diagram of a data table based on the association relationship record table, and display the ER diagram through a visual interface; An analysis log management module is used to record the analysis log of the association relationship of the data table in real time and encrypt and store the analysis log; A data table increment management module, configured to dynamically update the association relationship record table based on the incremented data table; The first-level matching module is specifically used for: Through multi-threading technology, regular expressions are used to perform parallel one-to-one first-level matching on the column names in the filtered table information to obtain the first-level matching results of the data table; when a primary key or foreign key exists, the first-level matching is first performed based on the column name of the primary key or foreign key during the first-level matching process; Generate a first SQL query statement based on the first-level matching result, query first table data from the data tables with associated relationships respectively, verify the first-level matching result based on the queried first table data, and store the first-level matching result in an associated relationship record table; Determine whether there are any unmatched data tables. If so, enter the secondary matching module; if not, enter the ER diagram generation module.

7. The database table association relationship analysis system according to claim 6, characterized in that: The table information reading module specifically includes: An authentication unit is used for the server to create a pair of public and private keys based on the RSA algorithm and to pre-set the public key to the client; Based on the database address, the client sends a database access request to the server, which carries ciphertext data, signature data, device fingerprint, timestamp, and random number. The ciphertext data is obtained by encrypting the account name and password using the 3DES algorithm. The signature data is obtained by signing the account name and password using the public key. The device fingerprint is obtained by hashing the client's device serial number. The random number is a natural number randomly generated by the client and stored locally on the client. The server parses the received database access request to obtain ciphertext data, signature data, device fingerprint, timestamp, and random number, performs a first-level verification of timeliness using the timestamp, verifies the signature data using the private key for a second-level verification, matches the device fingerprint using a preset user management table for a third-level verification, decrypts the ciphertext data using a 3DES algorithm to obtain an account name and password, performs a fourth-level verification of the legitimacy of the account name and password using the user management table, and if the verification passes, performs an HMAC calculation on the random number to obtain a first MAC value, generates a request response based on the first MAC value, and feeds the request response back to the client; The client parses the received request response to obtain a first MAC value, performs HMAC calculation on the locally stored random number to obtain a second MAC value, and verifies the first MAC value using the second MAC value to complete the authentication operation; A database access unit, configured for a client to access a database deployed by a server and read in real time table information of a data table stored in the database; the table information includes at least a table name, table ID, column name, column ID, column length, primary key identifier, foreign key identifier, data type, data length, and table data; the primary key identifier is used to identify whether the corresponding column is a primary key; the foreign key identifier is used to identify whether the corresponding column is a foreign key; A table information storage unit, configured to perform structured storage of each table information based on a tree structure with the table ID as the parent node; The table information screening module is specifically used for: Create a relationship record table for storing the table name, table ID, column name and column ID of the associated data table, and call the preset filtering rules through the rule engine to filter the stored table information; the filtering rules are that the column name contains preset characters, the column length is a preset length, the column is a primary key, the column is a foreign key, the data type is a string or the data length is not 0.

8. The database table association relationship analysis system according to claim 6, characterized in that: The secondary matching module is specifically used for: By using multi-threading technology, regular expressions are used to perform parallel one-to-many secondary matching on the table data in the table information of each of the unmatched data tables to obtain the secondary matching results of the data tables; generating a second SQL query statement based on the secondary matching result, querying the second table data from the data tables with associated relationships respectively, verifying the secondary matching result based on the queried second table data, and storing the secondary matching result in the associated relationship record table; Determine whether there are any unmatched data tables. If so, enter the third-level matching module; if not, enter the ER diagram generation module.

9. The database table association relationship analysis system according to claim 6, characterized in that: In the three-level matching module, the association relationship analysis model is constructed based on the data input module, the feature extraction module, the relationship reasoning module and the output module, and the loss function is constructed based on the classification cross entropy loss function and the contrast loss function; The data input module is used to input table information; the feature extraction module is used to extract structural features, text features and statistical features from the table information; The structural features include at least the number of columns and the data type; the text features include at least the column name; the statistical features include at least the mean, variance, and unique value ratio; the relationship reasoning module is used to infer the association relationship of the data table based on the structural features, text features, and statistical features; the output module is used to output the matching result based on the association relationship; The training process of the association relationship analysis model is specifically as follows: A large amount of historical table information is obtained, and after pre-processing the data cleaning of each historical table information, the association relationship is annotated, and a data set is constructed based on the annotated historical table information; the data set is divided into a training set, a validation set, and a test set based on a ratio of 8:1:1, and the association relationship analysis model is trained using the training set until the loss value of the loss function is less than a preset loss threshold. During the training process, the learning rate, random dropout rate, number of iterations, and optimizer of the association relationship analysis model are continuously optimized; the analysis accuracy is calculated using the validation set to verify the trained association relationship analysis model. If the verification fails, the data set is expanded to continue training; if the verification is successful, then: The confidence level is calculated using the test set to test the verified association analysis model. If the test fails, the data set is expanded to continue training; if the test succeeds, the training is terminated.

10. The database table association relationship analysis system according to claim 6, characterized in that: The ER diagram generation module is specifically used for: Based on the association relationship record table, an ER diagram of the data table is automatically generated through a drawing tool, one-to-one relationships, one-to-many relationships, and many-to-many relationships in the ER diagram are displayed differently, and the ER diagram is rendered and displayed in real time through a visual interface; The analysis log management module is specifically used to: An analysis log that records the association relationship of data tables in real time, wherein the analysis log includes at least the analysis time, account name, device serial number, association relationship record table, and ER diagram; The analysis log is hashed using the SHA-256 algorithm to obtain a hash value, the analysis log and the hash value are encrypted using the AES algorithm to obtain first-level encrypted data, the first-level encrypted data is converted into ASCII code, and the ASCII code is inverted to obtain second-level encrypted data, the second-level encrypted data is encrypted using the RC6 algorithm to obtain third-level encrypted data, the third-level encrypted data is encrypted using a preset mapping rule to obtain fourth-level encrypted data, the number 1 and the letter I are swapped, the number 0 and the letter O are swapped, and the number 7 and the letter L are swapped in the fourth-level encrypted data to obtain an encrypted log, the encrypted log is stored in a specified path, and the encrypted log is uploaded to the blockchain for backup.

Images