A method and device for reverse analysis of relational database table structure

Abstract

The present invention discloses a method for reverse analysis of a table structure of a relational database, comprising the following steps: initializing and configuring a target database, generating a table structure file from multiple table structures of the target database, and generating a table data file from table data corresponding to the table structure; obtaining a search keyword, searching in the table data file according to the keyword, and generating a search result; constructing a table structure connection model diagram of the target database based on the search result, and outputting the search result and the table structure connection model diagram. The present invention provides a method for reverse analysis of a table structure of a relational database, a method for quickly analyzing the table association relationship of a relational database. Compared with the existing general global search tools, the present invention is more targeted, and has a more concentrated analysis scale, a faster calculation speed, and a more intuitive result display. Among them, the files are divided into two categories: table structure files and table data files.

Description

Technical Field

[0001] The present invention belongs to the technical field of database information processing, and in particular relates to a method and device for reverse analysis of a relational database table structure. Background Art

[0002] The database table structure description is a document used to describe the detailed design of the program database. It is an instruction manual for the data table itself and the relationship between data tables. It is also the basis for understanding software behavior and functions. Whether it is development assistance, later iterative maintenance, or software reconstruction, the database table structure description is inseparable.

[0003] However, the applicant has discovered through research that in actual scenarios, the database table structure description is frequently missing due to various reasons, resulting in the software system being incomprehensible, which in turn leads to difficulties in the use, maintenance, modification, and reconstruction of the software, and may even directly lead to project failure.

[0004] The significance of database table structure description to software system is similar to the instruction manual of complex machine. Without the instruction manual, it will be difficult to use and maintain. Therefore, it is urgent to develop technology to retrieve the database table structure description under the current situation of running software. Summary of the invention

[0005] The purpose of the present invention is to solve the above-mentioned technical problems and to provide a method and device for reverse analysis of a relational database table structure.

[0006] In order to solve the above problems, the present invention is implemented according to the following technical solutions:

[0007] In a first aspect, the present invention provides a method for reverse analysis of a relational database table structure, comprising the following steps:

[0008] Initialize the configuration of the target database, generate table structure files from multiple table structures of the target database, and generate table data files from table data corresponding to the table structures;

[0009] Obtain search keywords, search in table data files according to the keywords, and generate search results;

[0010] A table structure connection model diagram of the target database is constructed based on the search results, and the search results and the table structure connection model diagram are output.

[0011] In combination with the first aspect, the present invention further provides a first preferred implementation manner of the first aspect, specifically, searching in the table data file according to the keyword specifically comprises the following steps:

[0012] Perform traversal search in table data files based on keywords;

[0013] When a hit occurs, extracting first information from the table data file;

[0014] Identify a table structure corresponding to the first information from the table structure file, and extract the second information from the table structure;

[0015] Adding the first information and the second information to the result cache;

[0016] Repeat the above steps until the traversal search of the table data file is completed, and generate search results based on the result cache.

[0017] In combination with the first aspect, the present invention further provides a second preferred implementation of the first aspect, specifically, initializing and configuring the target database, specifically including:

[0018] According to the preset storage path, export all the database data of the target database to the storage path;

[0019] According to the first preset configuration condition, all table structures of the target database are generated into a table structure file, wherein the table structure file includes multiple rows of table structure information, a single table structure occupies one row of table structure information, and the first element of each row of table structure information is a table name;

[0020] According to the second preset configuration condition, the table data corresponding to the table structure is used to generate a table data file, wherein the table data file includes multiple rows of data information, a single row of data of the table data occupies one row of data information, and the first element of each row of data information is the table name.

[0021] In combination with the first aspect, the present invention further provides a third preferred implementation of the first aspect, specifically, according to the second preset configuration condition, generating a table data file from the table data corresponding to the table structure, specifically including:

[0022] The data of different tables can be integrated into the same table data file until the data volume reaches the maximum row number threshold of the table data file;

[0023] Alternatively, when the data volume of a single table data exceeds the maximum row number threshold of the table data file, the table data is split into several table data files for storage.

[0024] In combination with the first aspect, the present invention further provides a fourth preferred implementation of the first aspect, specifically, performing a traversal search in the table data file according to the keyword, specifically comprising:

[0025] Row data traversal search, specifically, read the table data file in row reading mode, search the keyword for the read string, if it is a hit, extract the table name pointed to by the row data, extract the table header from the corresponding table structure file, add the table name, table header, and hit row data to the result cache, if there is no hit, skip to the next row or the next file until the traversal is completed;

[0026] Or;

[0027] File traversal search, specifically, read the table data file in text format, search for keywords in the read text, and if it is a name, add the table name of the table data file to the result cache; after traversing all table data files, traverse the hit table data files in the result cache to determine the hit row data.

[0028] In combination with the first aspect, the present invention further provides a fifth preferred implementation of the first aspect, specifically, before constructing the table structure connection model diagram of the target database based on the search results, the following steps are also included:

[0029] displaying the search results;

[0030] Determine whether to perform a recursive search;

[0031] If a recursive search is performed, the characteristic data value of any table structure corresponding to the search result is used as a recursive keyword, and the above search steps are repeated to generate a recursive search result;

[0032] If recursive search is not performed, go directly to the next step.

[0033] In combination with the first aspect, the present invention further provides a sixth preferred implementation of the first aspect, specifically, constructing a table structure connection model diagram of a target database based on search results, specifically including:

[0034] The search results are used to indicate the existence of virtual foreign keys between data tables; based on the search structure, several hit table structures are associated to draw a table structure connection model diagram.

[0035] In a second aspect, the present invention provides a reverse analysis device for a relational database table structure, comprising:

[0036] A configuration module is used to initialize and configure a target database, generate table structure files from multiple table structures of the target database, and generate table data files from table data corresponding to the table structures;

[0037] A search module, which is used to obtain a search keyword, search in the table data file according to the keyword, and generate a search result;

[0038] A construction module is used to construct a table structure connection model diagram of a target database based on search results, and output search results and the table structure connection model diagram.

[0039] In a third aspect, the present invention provides an electronic device comprising a memory and a processor, wherein the memory stores a computer program, and when the computer program is executed by the processor, the processor executes the steps of the reverse analysis method of the relational database table structure as described in the first aspect.

[0040] The fourth invention provides a computer-readable storage medium having a computer program stored thereon, and when the computer program is executed by a processor, the steps of the reverse analysis method of the relational database table structure as described in the first aspect are implemented.

[0041] Compared with the prior art, the present invention has the following beneficial effects:

[0042] The present invention provides a reverse analysis method for a relational database table structure, which is a method for quickly analyzing the table association relationship of a relational database. Compared with the existing general global search tools, the present invention is more targeted, has a more concentrated analysis scale, a faster calculation speed, and a more intuitive result display. Among them, the files are divided into two types of table structure files and table data files. The data table structure information generates a table structure text file; the database table data generates multiple data files according to the system performance, and the data files are searched for keywords line by line. The search results are displayed in text and charts, all focusing on the table structure and the association relationship between tables. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] The specific embodiments of the present invention are further described in detail below in conjunction with the accompanying drawings, wherein:

[0044] Figure 1 It is a schematic flow chart of the reverse analysis method of the relational database table structure of the present invention;

[0045] Figure 2 is a schematic diagram of a reverse analysis device for a relational database table structure of the present invention;

[0046] Figure 3 is a schematic diagram showing the search results of the present invention;

[0047] Figure 4 It is a database table structure connection model diagram constructed by the present invention. DETAILED DESCRIPTION

[0048] The preferred embodiments of the present invention are described below in conjunction with the accompanying drawings. It should be understood that the preferred embodiments described herein are only used to illustrate and explain the present invention, and are not used to limit the present invention.

[0049] Software is a collection of computer data and algorithm instructions organized in a specific order. Under the two mainstream architecture branches of CS (client-server) and BS (browser-server), data is mainly stored in relational databases, such as Mysql, Oracle, etc. The applicant's research found that as the scale of software system data becomes larger and larger, the use of physical foreign keys to display database table relationships becomes more and more cumbersome and is gradually abandoned. Instead, virtual foreign keys are used. In this way, the table structure description often exists in the following forms: the name of the data table field is self-explanatory, data table field comments, database model diagram, independent database table structure description document, software source code, etc.

[0050] The database table structure description is a description of the data table itself and the relationship between data tables. It is the basis for understanding software behavior and functions. Whether it is development assistance, later iteration maintenance, or software reconstruction, the database table structure description is indispensable. The significance of the database table structure description to the software system is similar to the instruction manual of a complex machine. Without the guidance of the manual, it will be difficult to use and maintain. However, in actual scenarios, this important description is often missing due to various reasons, resulting in the software system being incomprehensible, which in turn leads to difficulties in the use, maintenance, transformation, and reconstruction of the software, and can even directly lead to project failure.

[0051] To this end, the present invention provides a reverse analysis method for a relational database table structure to achieve a global search based on one or more keywords, and then analyze the association relationship between the business tables to which the keywords belong. The tool can be used in scenarios such as software reverse engineering and reconstruction of old systems.

[0052] Specifically, the reverse analysis method of the relational database table structure of the present invention is through initializing and configuring the target database; generating a table structure file from multiple table structures of the target database, and generating a table data file from table data corresponding to the table structure; obtaining search keywords, searching in the table data file according to the keywords, and generating search results; constructing a table structure connection model diagram of the target database based on the search results, and outputting the search results and the table structure connection model diagram.

[0053] The present invention can be implemented using Python language. By focusing on the analysis of database table structure, it is more targeted, has a more concentrated analysis scale, faster calculation speed, and more intuitive result display compared to general global search tools. In order to avoid interference caused by the system using the analyzed database and reduce the difficulty of data cleaning, the space and time complexity of the analysis target is greatly reduced.

[0054] The following is combined with the above Figure 1 The reverse analysis method of the relational database table structure provided in the embodiment of the present application is described in detail.

[0055] Reference Figure 1 As shown, Figure 1 A flowchart of a method for reverse analysis of a relational database table structure provided in an embodiment of the present application is provided. The method for reverse analysis of a relational database table structure comprises the following steps:

[0056] S100: Initialize and configure the target database, generate table structure files from multiple table structures of the target database, and generate table data files from table data corresponding to the table structures.

[0057] The present invention proposes a new solution to the shortcomings of existing global search tools, such as complicated deployment, lack of pertinence, and interference with normal software operation. The solution uses specific text files of unified specifications as intermediate data carriers to load all data with as few intermediate carriers as possible, which can reduce the time and space complexity of reverse analysis. Multiple keywords can be used in one search to improve the accuracy of the results.

[0058] In the present invention, both the table structure file and the table data file are in lines. Each line can contain several elements, which are enclosed in single quotes ('') and separated by spaces (). By initializing and configuring the target database, a text file format with a line structure is used as the data carrier. The files are divided into two categories: table structure file and table data file. The data table structure information generates a table structure text file; the database table data generates multiple table data files according to the system performance, and the table data files are traversed line by line to search for keywords.

[0059] Specifically, the initialization configuration of the target database includes:

[0060] S110: According to a preset storage path, export all the database data of the target database to the storage path.

[0061] Exemplarily, when analyzing a database for the first time, initial configuration is required, including configuring a file path large enough to store text file data carriers and configuring database connection information to generate text files. After the configuration is completed, the system will automatically export all database information to the configured storage path.

[0062] S120: Generate a table structure file for all table structures of the target database according to the first preset configuration condition, wherein the table structure file includes multiple rows of table structure information, a single table structure occupies one row of table structure information, and the first element of each row of table structure information is the table name.

[0063] In a specific implementation, all table structures of the target database are generated into a table structure file, where a single table structure information occupies one line, and the first element of each line is the table name. The table structure file format example is as follows:

[0064] 'table_name''file_name1''file_name2''file_name3''file_name4'......'file_nameN'

[0065] S130: Generate a table data file from table data corresponding to the table structure according to the second preset configuration condition, wherein the table data file includes multiple rows of data information, a single row of table data occupies one row of data information, and the first element of each row of data information is the table name.

[0066] For example, table data generates a table data file, where a single row of data occupies one row, and the first element of each row is the table name. When configuring the table data file, the maximum row number threshold can be determined based on the current operating environment performance. The data file format example is as follows:

[0067] 'table_name''file_value1''file_value2''file_value3''file_value4'...'file_valueN'

[0068] In a preferred implementation, data from different tables can be integrated into the same table data file until the data volume reaches the maximum row number threshold of the table data file; alternatively, when the data volume of a single table data exceeds the maximum row number threshold of the table data file, the table data is split into several table data files for storage.

[0069] S200: Obtain a search keyword, search in a table data file according to the keyword, and generate a search result.

[0070] In one implementation, the setting requirements of the keywords used for search in the present invention are derived from actual business scenarios. The stronger the characteristic of the keyword, the higher the search efficiency. On the contrary, if the characteristic is weak and the keyword exists in multiple scenarios, there will be more interference.

[0071] For example, taking the database of a workflow system as an example, the workflow process name, the ID attached to the URL when viewing the workflow, and other characteristic information are all good keywords. By performing a global search with these keywords, you can quickly outline the database tables involved in the creation and flow of a process.

[0072] It should be noted that the relationship between the keyword and the database table field value can be a complete match or a partial match. The keyword can be one or more. If multiple keywords are selected, each keyword must appear in different fields to be considered a hit. For example: keywords key1, key2, field 1 of a row in table a matches key1, and field 2 matches key2, then it is a hit.

[0073] Preferably, in the present invention, when searching, the search range and filter range can be customized; based on experience or previous search results, only one or more data tables can be specified to be searched, and one or more data tables can be specified not to participate in the search.

[0074] For example, during the initial configuration process, the search range and filter range are customized in the selected target database, specifically, one or more target data tables are specified for search, one or more data tables are specified not to participate in the search, and the filtered table structure file and table data file are generated. Through this setting, many known invalid interferences can be eliminated and the analysis speed can be improved.

[0075] In a specific implementation, searching in the table data file according to the keyword specifically includes the following steps:

[0076] S210: Perform a traversal search in the table data file according to the keyword.

[0077] Exemplarily, the present invention performs traversal based on the table data file generated when the target database is initialized and configured. The traversal unit can be by row or by file (after hitting, the file is traversed by row to continue locating the row data), and the row traversal algorithm is used by default. To this end, the present invention provides two search methods, including row data traversal search and file traversal search.

[0078] Among them, the row data traversal search includes: reading the table data file in a row reading mode, searching the read string for keywords, if a hit is found, extracting the table name pointed to by the row data, and extracting the table header from the corresponding table structure file, adding the table name, table header, and hit row data to the result cache, and if there is no hit, skipping to the next row or the next file until the traversal is completed.

[0079] It can be directly understood that row-by-row traversal, as the name implies, is to read the table data file in a row-by-row manner, search for keywords in the read string, and if a hit is found, extract the table name (the first element) pointed to by the row of data, extract the table header from the table structure file, and add the table name, table header, and hit row data to the result. If there is no hit, skip to the next row or the next file until the traversal is completed.

[0080] The file traversal search includes: reading the table data file in text form, searching for keywords in the read text, and if it is a name, adding the table name of the table data file to the result cache; after traversing all table data files, traversing the hit table data files in the result cache to determine the hit row data.

[0081] It can be directly understood that file traversal is completed in two steps: determining the hit file; determining the hit row data in the hit file. First, the entire data file is read into the system in text format, and the keyword is searched in the text. If a hit is found, the table name is added to the result cache. After the first step of file traversal is completed, the hits in the result cache are traversed row by row.

[0082] S220: when a hit occurs, extracting first information from the table data file;

[0083] S230: identifying a table structure corresponding to the first information from the table structure file, and extracting second information from the table structure;

[0084] S240: Add the first information and the second information to the result cache;

[0085] S250: Repeat the above steps S210-S240 until the traversal search of the table data file is completed, and generate search results based on the result cache.

[0086] The search results include three elements: table name, table header (table field), hit row data, and highlighting of hit keywords. Figure 3 As displayed.

[0087] After outputting the search structure, the present invention displays the search results, and highlights the hit keywords to facilitate the staff to view. By displaying the search results, the staff is provided with the search results search conditions, so as to judge whether a recursive search is needed.

[0088] Specifically, this step S200 also includes:

[0089] S260: Display the search results.

[0090] S270: Determine whether to perform a recursive search.

[0091] S271: If a recursive search is performed, the characteristic data value of any table structure corresponding to the search result is used as a recursive keyword, and the above search steps are repeated to generate a recursive search result;

[0092] S272: If recursive search is not performed, go directly to the next step.

[0093] For example, the staff can choose whether to perform a recursive search, which can be triggered by inputting a command, clicking an option, etc. If the selection is no, the process directly jumps to step S300. If a recursive search is performed, the staff is required to select a characteristic data value of any data table in the search results as a keyword, and then repeat steps S210-S250. If the search is already a recursive search, the process directly skips this step.

[0094] S300: constructing a table structure connection model diagram of a target database based on the search results, and outputting the search results and the table structure connection model diagram.

[0095] Exemplarily, the search results are used to indicate the existence of virtual foreign keys between data tables; based on the search structure, several hit table structures are associated to draw a table structure connection model diagram.

[0096] Based on the virtual foreign key relationship between database tables, it can be understood that if a keyword appears in two data tables (field value), it is considered that there is a virtual foreign key between the two tables. For example, if the keyword is key, the value of field A in table A is key, and the value of field B in table B is also key, then it can be temporarily determined that there is a virtual foreign key AB between table A and table B. Based on this, a database table structure connection model diagram can be automatically drawn based on the search results, such as Figure 4 shown.

[0097] The embodiment of the present application also provides a reverse analysis device for a relational database table structure, comprising:

[0098] A configuration module is used to initialize and configure a target database, generate table structure files from multiple table structures of the target database, and generate table data files from table data corresponding to the table structures;

[0099] A search module, which is used to obtain a search keyword, search in the table data file according to the keyword, and generate a search result;

[0100] A construction module is used to construct a table structure connection model diagram of a target database based on search results, and output search results and the table structure connection model diagram.

[0101] The configuration module, search module and construction module are also used to execute part or all of the steps of the above-mentioned reverse analysis method of the relational database table structure.

[0102] An embodiment of the present application also provides an electronic device, including a memory and a processor, wherein a computer program is stored in the memory, and when the computer program is executed by the processor, the processor executes part or all of the steps of the reverse analysis method of the relational database table structure described in the previous embodiment.

[0103] An embodiment of the present application discloses a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, some or all steps of the reverse analysis method of the relational database table structure described in the previous embodiment are implemented.

[0104] An embodiment of the present application provides a computer program product, which includes a computer-readable storage medium storing a computer program, and the computer program enables a computer to execute some or all steps of the reverse analysis method of the relational database table structure described in the previous embodiment.

[0105] The above-mentioned specific method embodiments and the explanations, descriptions, and expansions of various implementation forms of the technical features in the embodiments are also applicable to the execution of the method in the device, and will not be repeated in the device embodiments.

[0106] It should be understood that the division of the modules in the above device is only a division of logical functions. In actual implementation, they can be fully or partially integrated into one physical entity, or they can be physically separated. For example, the above modules can be separately established processing elements, or they can be integrated into a certain chip of the terminal for implementation. In addition, they can also be stored in the storage element of the controller in the form of program code, and called and executed by a certain processing element of the processor. The functions of the above modules. In addition, the modules can be integrated together or implemented independently. The processing element described here can be an integrated circuit chip with signal processing capabilities. In the implementation process, the steps of the above method or the above modules can be completed by hardware integrated logic circuits in the processor element or software instructions. The processing element can be a general processor, such as a processor (central processing unit, CPU), or it can be one or more integrated circuits configured to implement the above method, such as: one or more specific integrated circuits (application-specific integrated circuit, ASIC), or, one or more microprocessors (digital signal processor, DSP), or, one or more field programmable gate arrays (field-programmable gate array, FPGA), etc.

[0107] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Therefore, any modification, equivalent change and modification made to the above embodiment according to the technical essence of the present invention without departing from the technical solution of the present invention shall still fall within the scope of the technical solution of the present invention.

Claims

1. A method for reverse analysis of a relational database table structure, characterized in that: The following steps are involved: Initialize the configuration of the target database, generate table structure files from multiple table structures of the target database, and generate table data files from table data corresponding to the table structures; Obtain search keywords, search in table data files according to the keywords, and generate search results; Construct a table structure connection model diagram of the target database based on the search results, and output the search results and the table structure connection model diagram; Wherein, searching in the table data file according to the keyword specifically includes the following steps: Traverse and search the table data files based on keywords; When a hit occurs, extracting first information from the table data file; Identify a table structure corresponding to the first information from the table structure file, and extract the second information from the table structure; Adding the first information and the second information to the result cache; Repeat the above steps until the traversal search of the table data file is completed, and generate search results based on the result cache.

2. The reverse analysis method of a relational database table structure according to claim 1, characterized in that: Initialize and configure the target database, including: According to the preset storage path, export all the database data of the target database to the storage path; According to the first preset configuration condition, all table structures of the target database are generated into a table structure file, wherein the table structure file includes multiple rows of table structure information, a single table structure occupies one row of table structure information, and the first element of each row of table structure information is a table name; According to the second preset configuration condition, the table data corresponding to the table structure is used to generate a table data file, wherein the table data file includes multiple rows of data information, a single row of data of the table data occupies one row of data information, and the first element of each row of data information is the table name.

3. The reverse analysis method of the relational database table structure according to claim 2 is characterized in that: According to the second preset configuration condition, the table data corresponding to the table structure is used to generate a table data file, specifically including: The data of different tables can be integrated into the same table data file until the data volume reaches the maximum row number threshold of the table data file; Alternatively, when the data volume of a single table data exceeds the maximum row number threshold of the table data file, the table data is split into several table data files for storage.

4. The reverse analysis method of a relational database table structure according to claim 2, characterized in that: Traverse and search the table data files based on keywords, including: Row data traversal search, specifically, read the table data file in row reading mode, search the keyword for the read string, if it is a hit, extract the table name pointed to by the row data, extract the table header from the corresponding table structure file, add the table name, table header, and hit row data to the result cache, if there is no hit, skip to the next row or the next file until the traversal is completed; Or; File traversal search, specifically, reads the table data file in text format, searches for keywords in the read text, and if a hit is found, adds the table name of the table data file to the result cache; after traversing all table data files, traverses the hit table data files in the result cache to determine the hit row data.

5. The reverse analysis method of a relational database table structure according to claim 1, characterized in that: Before constructing the table structure connection model diagram of the target database based on the search results, the following steps are also included: displaying the search results; Determine whether to perform a recursive search; If a recursive search is performed, the characteristic data value of any table structure corresponding to the search result is used as a recursive keyword, and the above search steps are repeated to generate a recursive search result; If recursive search is not performed, go directly to the next step.

6. The reverse analysis method of a relational database table structure according to claim 1, characterized in that: Construct a table structure connection model diagram of the target database based on the search results, including: The search results are used to indicate the existence of virtual foreign keys between data tables; based on the search results, several hit table structures are associated to draw a table structure connection model diagram.

7. A reverse analysis device for a relational database table structure, characterized in that: include: A configuration module is used to initialize and configure a target database, generate table structure files from multiple table structures of the target database, and generate table data files from table data corresponding to the table structures; A search module, which is used to obtain a search keyword, search in the table data file according to the keyword, and generate a search result; A construction module is used to construct a table structure connection model diagram of a target database based on the search results, and output the search results and the table structure connection model diagram; Wherein, searching in the table data file according to the keyword specifically includes the following steps: Traverse and search the table data files based on keywords; When a hit occurs, extracting first information from the table data file; Identify a table structure corresponding to the first information from the table structure file, and extract the second information from the table structure; Adding the first information and the second information to the result cache; Repeat the above steps until the traversal search of the table data file is completed, and generate search results based on the result cache.

8. An electronic device, characterized in that: It comprises a memory and a processor, wherein a computer program is stored in the memory, and when the computer program is executed by the processor, the processor executes the steps of the reverse analysis method of the relational database table structure as described in any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method for reverse analysis of a relational database table structure according to any one of claims 1 to 6 are implemented.

Images