A method for changing redundant tables in a database based on feature recognition

Abstract

The present invention discloses a method for changing redundant tables in a database based on feature recognition, belonging to the technical field of database application management. It includes: 1) data cleaning; 2) field classification to obtain an enumerated field set and a field set to be confirmed; 3) field set classification; 4) generating a structure tree, sequentially taking out each enumerated field or enumerated field set and the field to be confirmed as tree nodes, and each tree node stores a key-value pair; 5) converting each node of the structure tree into a key-value pair and outputting a yaml configuration file; 6) manually making technical modifications to the yaml configuration file; 7) reading the yaml configuration file, restoring the data structure to a tree structure, and finally outputting a changed redundant database script. The present invention reduces the number of times of manually configuring redundant tables without reconstructing the database, greatly reduces the risk of project configuration going online and the interference of redundant fields, and improves the change efficiency of redundant data tables.

Description

Technical Field

[0001] The present invention belongs to the technical field of database application management, and particularly relates to a method for changing redundant tables in a database based on feature recognition. Background Art

[0002] Database redundancy is a long-term and inevitable structural problem. In traditional database model design, database designers will avoid the problems brought by redundancy by following the three normal form design patterns. However, in many flexible scenarios, the structure design of the database is often not a strictly normal form table. The main reasons include the requirements for business response speed, the anti-normal form of storage design such as vertical table splitting, and the security requirements that cannot be split into tables. In addition, even for normal form tables, in a system with a large number of granularities, the difficulty of configuring and publishing data is still equivalent to changing redundant tables.

[0003] The existing technical means for changing redundant tables can be roughly divided into two categories:

[0004] (1) Manual method: Using a combination of manual and chart tools to batch-configure redundant databases. The principle of this solution is to use batch operation tools to configure data entries with a redundant multiple to complete the configuration and go live.

[0005] (2) Reconstruction method: Reconstructing the existing database tables to reduce the configuration complexity brought by table redundancy. The principle of this solution is to extract normal form tables from data tables, refine redundant fields separately, associate them through foreign key fields, and then gradually eliminate redundant fields. However, the above solutions have the following defects:

[0006] 1. Manual configuration solution: Due to low redundancy efficiency and the error rate rising linearly with the redundancy multiple, the manual configuration error rate is positively correlated with the number of tables, configuration entries, and self-increment values.

[0007] 2. Reconstructing the database solution: The cycle is relatively long. It is necessary to create new tables and foreign keys, and gradually eliminate redundant fields using foreign key fields. Some key services involve a large number of associated services and have high coupling. In the short term, the database has no conditions for reconstruction. On the one hand, reconstruction often requires adding new data granularities, and databases with a large number of granularities will further magnify the difficulty of manual configuration. On the other hand, the vertical table splitting design of business requirements does not allow the reconstruction solution. Summary of the Invention

[0008] In view of the problems existing in the prior art, the present invention provides a method for changing redundant database tables based on feature recognition, and its purpose is to improve the efficiency of redundant design without database reconstruction, use dynamic feature extraction to refine a database model with the lowest redundancy for data configuration online, so as to avoid the change risks brought by service upgrades and database upgrades, and overcome the problems of high complexity of database redundant configuration and high risk of reconstructing the system existing in the prior art.

[0009] The technical solution adopted by the present invention is as follows:

[0010] A method for changing redundant database tables based on feature recognition includes the following steps:

[0011] Step 1: Filter the data in the redundant table that is not related to going online.

[0012] Step 2: Field classification: Classify the fields of the redundant table according to the existing data in the database to obtain an enumerated field set and a field set to be confirmed.

[0013] Step 3: Combine the fields in the field set to be confirmed, traverse all combinations of the field set to be confirmed, and determine whether the field set obtained after combining the fields is an enumerated field set. If so, remove the fields in the field set from the field set to be confirmed until the field set to be confirmed is empty or only one field remains.

[0014] Step 4: Generate a structure tree, take out each enumerated field or enumerated field set and the field to be confirmed in turn as tree nodes, and each tree node stores a key-value pair, the key name of which is the field name and the value is the value set of the field or field set.

[0015] Step 5: Convert each node of the structure tree into a key-value pair, and finally output it as a yaml configuration file, and this configuration file is the minimum non-redundant data structure.

[0016] Step 6: In the manual use link, make normal business and technical modifications to the yaml configuration file.

[0017] Step 7: Read the yaml configuration file, restore the data structure in Step 5 to a tree structure, finally count the data entries, complete the assignment of the self-increment value, and finally output the changed redundant database script.

[0018] Further, the specific steps of Step 1 are as follows:

[0019] Step 1.1: Data and structure reading: Read data through the open-source tool library tableSaw, and load the existing structure and data of the database into an organized two-dimensional array structure by establishing a connection method.

[0020] Step 1.2: Data cleaning: Clean the data, filter the fields, and remove the fields that are irrelevant to statistical significance and configuration.

[0021] Further, Step 2 classifies the fields of the redundant table according to the existing data in the database as follows: For field A, if field A and all other field sets μ outside field A are used as key-value pairs, and if for different keys of field A, the corresponding field sets μ are always the same, then field A is regarded as an enumerated field; when the classification operation has been performed on all fields, or if the field set to be confirmed has only a single field, the classification step ends and Step 3 is skipped.

[0022] Further, Step 3 is specifically as follows: Pair the fields in the field set to be confirmed according to the Cartesian product combination, traverse each combination, and determine whether the resulting field set after combining the fields is an enumerated field set. If so, remove the fields in this field set from the field set to be confirmed, and the field set to be confirmed continues with Step 3. When all field sets have been classified, specifically, when the field set to be confirmed has only a single field, regard this field as a combination, remove the field set to be confirmed, and end Step 3; specifically, when the field set to be confirmed does not meet the requirements of the enumerated field set after the above traversal, regard the entire field set to be confirmed as a combination, remove the field set to be confirmed, and end Step 3.

[0023] Further, the structure tree in Step 4 adopts the data structure of a tree or a linked list. The node order of the tree is arranged in sequence as enumerated fields, enumerated field sets, and fields to be confirmed. Each node has a key-value pair, where the key name is the name of the field or field set, and the value is the corresponding data combination of the key name field of the field or field set.

[0024] Further, Step 5 is specifically as follows: Convert the tree structure into key-value pairs, where the key name of the root node is the table name, and the value is an array that includes the key-value pairs of all nodes of the tree. Use the snakeyaml function library for persistence and output it as a yaml template file.

[0025] Further, the normal business and technical modifications to the yaml configuration file in Step 6 specifically include: specifying the primary key field, primary key value, batch modification fields, batch combination fields, batch adding data, and generating a rollback script.

[0026] Further, the output script in step 7 is specifically as follows: By recursively traversing the key-value pairs of each node, the key-value pairs are concatenated; traverse to the first key-value pair of the first tree node, and then recursively traverse the first key-value pair of the second node... traverse the first key-value pair of the last node to generate a script. Next, traverse the second key-value pair of the last node to output the second script. After traversing the key-value pairs of the last field, return to the second field of the penultimate node, and then traverse the last node; output all combinations of nodes in this order.

[0027] In summary, due to the adoption of the above technical solution, the beneficial effects of the present invention are as follows:

[0028] 1. Without reconstructing the database, the present invention maximally reduces the number of times of manually configuring redundant tables, uses the template file with the lowest redundancy extracted to replace the database script for changes, and then reversely generates the change script, greatly reducing the risk of project configuration going online and the interference of redundant fields, and improving the change efficiency of redundant data tables.

[0029] 2. In terms of configuration complexity, the present invention rewards the redundancy complexity of each field, reduces the number of configurations to o(n), meets the DRY usage and modification principle, solves the repetitive and inefficient problem of manual configuration facing redundant design, and can avoid the change risk problems brought by service upgrade and database upgrade.

[0030] 3. The present invention can still have the efficiency at the normal form table level when manually operating the database without updating and releasing the original service, without upgrading the application, and without reconstructing the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] The present invention will be described by way of examples with reference to the accompanying drawings, wherein:

[0032] Figure 1 is the flow chart of the present invention;

[0033] Figure 2 is the data flow diagram of the present invention. DETAILED DESCRIPTION OF THE INVENTION

[0034] To make the objectives, technical solutions and advantages of the embodiments of this application clearer, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings in the embodiments of this application. Apparently, the described embodiments are only some, but not all, of the embodiments of this application. Components of the embodiments of this application usually described and illustrated in the accompanying drawings here can be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of this application provided in the drawings is not intended to limit the scope of the claimed application, but is merely representative of selected embodiments of this application. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative efforts fall within the scope of protection of this application.

[0035] In the description of the embodiments of this application, it should be noted that the orientation or positional relationships indicated by the terms "upper", "lower", "left", "right", "vertical", "horizontal", "inner", "outer", etc. are based on the orientation or positional relationships shown in the accompanying drawings, or the orientation or positional relationships in which the inventive product is customarily placed during use. These are only for the convenience of describing this application and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and thus should not be construed as a limitation of this application. In addition, the terms "first", "second", "third", etc. are only used for descriptive distinction and should not be construed as indicating or implying relative importance.

[0036] This solution mainly solves the problem of redundant database changes and is commonly used in the scenario of application configuration release. An independent de-duplication algorithm is used to template redundant data and structures, and the template obtains a minimal set of non-redundant structures. In the future, new and modified data for the database can indirectly operate on the redundant database by operating on this template, and the changes to the redundant database are published in the form of database scripts.

[0037] Refer to Figure 1 and Figure 2 , Figure 1 is the flowchart of the present invention; Figure 2 is the data flow diagram of a complete process of the present invention, showing the interaction between the database and the program.

[0038] The specific solution of this scheme is as follows:

[0039] A method for changing redundant database tables based on feature recognition includes the following steps:

[0040] Step 1: Filter the data in the redundant table that is not related to going online. Common fields that need to be filtered include, for example, creation time, update time, etc. Step 1 includes data reading and filtering:

[0041] Step 1.1 Reading of Data and Structure: Without manual data input, it is read through the commonly used open-source tool library tableSaw for database tables, and the existing structure and data of the database are loaded into an organized two-dimensional array structure by establishing a connection method.

[0042] Step 1.2 Data Cleaning: Generally, fields that have nothing to do with statistical significance and configuration are removed, and only the data and fields that need to be retained in the data table are kept. This can minimize the impact of fields that are irrelevant to data going live.

[0043] Step 2: Field Classification: Classify the fields of the redundant table according to the existing data in the database to obtain an enumerated field set and a field set to be confirmed. For field A, if field A and all field sets μ other than field A are used as key-value pairs. If for different keys of A, the corresponding sub-field sets μ are always the same, then field A is regarded as an enumerated field (for example, if there are 2 pieces of data in this table, with only fields A, B, and C, and the two pieces of data are xbc and ybc respectively, then field A can be considered an enumerated field). For example, the table has a field set: {a, b, c, d}. If a is an enumerated field and b, c, and d are not, put b, c, and d into the set to be confirmed. At this time, the enumerated field set {a} and the field set to be confirmed {b, c, d} are obtained, and step 2 ends.

[0044] Step 3: Combine the fields in the field set to be confirmed, traverse all combinations of the field set to be confirmed, and determine whether the field set obtained after field combination is an enumerated field set. If it is, remove the fields in this field set from the field set to be confirmed until the field set to be confirmed is empty or only one field remains. For field set a, if field set a and all field sets μ other than field set a are used as key-value pairs. If for different keys of a, the corresponding values μ are always the same, then field set a is regarded as an enumerated field set until the field set to be confirmed is empty. Pair the field set to be confirmed according to the Cartesian product [that is, pair two by two, three by three... until N / 2 (if the number of fields is odd, then at most (N - 1) / 2 pairs)] and traverse each combination. Termination condition: All field sets are classified, or the field set to be confirmed has only a single field, which is regarded as a combination, and step 3 ends. For example, if the field set to be confirmed is {b, c, d}, pair it two by two and traverse the three possibilities of field sets {b, c}, {b, d}, and {c, d}. It is found that {b, c} is determined to be an enumerated field set. Since only one field d remains, all values of d are regarded as combinations, and step 3 ends.

[0045] Step 4: Generate a structure tree. Take out each enumerated field or set of enumerated fields and the fields to be confirmed in turn as tree nodes. Each tree node stores a key-value pair, where the key name is the field name and the value is the set of values of the field or field set. The structure tree can be implemented using the data structure of a tree or a linked list. The order of the nodes in the tree is arranged in sequence as enumerated fields, sets of enumerated fields, and fields to be confirmed, and each node has a key-value pair. For example, tree A has three nodes, which are

[0046] node1: {a: 1},

[0047] node2: {[b, c]: [[x, y], [m, n]]},

[0048] node3: {d: [α, γ]}.

[0049] Step 5: Convert each node of the structure tree into a key-value pair and finally output it as a yaml configuration file. This configuration file is the smallest non-redundant data structure. Step 5 converts the tree structure into key-value pairs, where the key name of the root node is regarded as the table name and the value is an array that includes the key-value pairs of all nodes in the tree. Use the snakeyaml function library for persistence and output it as a yaml template file. Normalize the data structures of different abstractions and use this method to output a unified resolvable data template. For example, tree A finally becomes

[0050] table:

[0051] {a: 1, [b, c]: [[x, y], [m, n]], d: [α, γ]}

[0052] Such key-value pairs are finally persisted into a yaml file.

[0053] Step 6: In the manual usage session, make normal business and technical modifications to the yaml configuration file. In the manual configuration stage, generally, the primary key field and primary key value can be specified, fields can be modified in batches, fields can be combined in batches, data can be added in batches, and a rollback script can be generated. Modify the original data by modifying the template file with the lowest redundancy. For example: only modify the value of the node with the key name a to 2. Make tree A finally become

[0054] table:

[0055] {a: 2, [b, c]: [[x, y], [m, n]], d: [α, γ]}

[0056] Step 7: Read the yaml configuration file, restore the data structure in Step 5 to a tree structure, finally count the number of data entries, complete the assignment of the auto-increment value, and finally output the redundant database script for the change. Finally, generate a release script. Taking the mysql insert script as an example:

[0057] insert into `table` (`a`, `b`, `c`, `d`) values (2, 'x', 'y', 'γ');

[0058] insert into `table` (`a`, `b`, `c`, `d`) values (2,'m', 'n', 'γ');

[0059] insert into `table` (`a`, `b`, `c`, `d`) values (2, 'x', 'y', 'α');

[0060] insert into `table` (`a`, `b`, `c`, `d`) values (2,'m', 'n', 'α');

[0061] The ways of outputting scripts: The entire tree structure can be output completely. This is generally used for insert statements and rollback statements for adding service configurations. Different nodes of the tree structure can also be output in a loop for publishing change configurations.

[0062] By recursively traversing the key-value pairs of each node, the key-value pairs are concatenated. Traverse the first key-value pair of the first tree node, then recursively traverse the first key-value pair of the second node... Traverse the first key-value pair of the last node to generate a script. Next, traverse the second key-value pair of the last node to output the second script. After traversing all the key-value pairs of the last field, go back to the second field of the penultimate node and then traverse the last node. Output all combinations of nodes in this order. This step mainly spreads the redundancy in such a way that the changes to the original template file are spread to each piece of data.

[0063] However, for manual operations, the redundant operations of log(n!) have been streamlined to the lowest level.

[0064] Using this solution, it can be achieved that without reconstructing the database of the original service, that is, without the need to update the version of the service, data configuration updates at the normal form table level can be achieved.

[0065] The above-described embodiments only represent the specific implementation manners of the present application. The description is relatively specific and detailed, but it should not be construed as a limitation on the protection scope of the present application. It should be noted that for those of ordinary skill in the art, without departing from the concept of the technical solution of the present application, several deformations and improvements can still be made, and these all belong to the protection scope of the present application.

Claims

1. A method for changing redundant tables in a database based on feature recognition, characterized in that, It includes the following steps: Step 1: Filter the data in the redundant table that is not related to going online; Step 2: Field classification: Classify the fields of the redundant table according to the existing data in the database to obtain an enumerated field set and a field set to be confirmed; Step 3: Combine the fields in the field set to be confirmed, traverse all combinations of the field set to be confirmed, and determine whether the obtained field set after combining the fields is an enumerated field set. If so, remove the fields in this field set from the field set to be confirmed until the field set to be confirmed is empty or only one field remains; Step 4: Generate a structure tree, and successively take out each enumerated field or enumerated field set and field to be confirmed as tree nodes. Each tree node stores a key-value pair, where the key name is the field name and the value is the value set of this field or field set; Step 5: Convert each node of the structure tree into a key-value pair, and finally output it as a yaml configuration file. This configuration file is the smallest non-redundant data structure; Step 6: In the manual usage link, make normal business and technical modifications to the yaml configuration file; Step 7: Read the yaml configuration file, restore the data structure in Step 5 to a tree structure, finally count the data entries, complete the assignment of the self-increment value, and finally output the changed redundant database script.

2. The method for changing redundant tables in a database based on feature recognition according to claim 1, wherein The specific steps of Step 1 are as follows: Step 1.1: Data and structure reading: Read the data through the open-source tool library tableSaw, and load the existing structure and data of the database into an organized two-dimensional array structure by establishing a connection method; Step 1.2: Data cleaning: Filter the fields and remove the fields that are not related to statistical significance and configuration; 3. A method for changing redundant tables in a database based on feature recognition according to claim 1, characterized in that, The specific field classification of the redundant table according to the existing data in the database in Step 2 is as follows: For field A, if field A and all other field sets μ outside field A are used as key-value pairs, and if for different keys of field A, the corresponding field sets μ are always the same, then field A is regarded as an enumerated field; When all fields have completed the classification operation, or the field set to be confirmed has only a single field, the classification step ends and Step 3 is skipped.

4. A method for changing redundant tables in a database based on feature recognition according to claim 1, characterized in that, The specific content of Step 3 is as follows: Pair the fields in the field set to be confirmed according to the Cartesian product combination, traverse each combination, and determine whether the obtained field set after combining the fields is an enumerated field set. If so, remove the fields in this field set from the field set to be confirmed. When all field sets have completed the classification, or the field set to be confirmed has only a single field, it is regarded as a combination, and Step 3 ends.

5. A method for changing redundant tables in a database based on feature recognition according to claim 1, characterized in that, The structure tree in Step 4 adopts a tree or linked list data structure. The order of the nodes of the tree is arranged in sequence of enumerated fields, enumerated field sets, and fields to be confirmed. Each node has a key-value pair, where the key name is the name of this field or field set, and the value is the corresponding data combination of the key name field of this field or field set.

6. The method for changing a redundant table of a database based on feature recognition according to claim 1, wherein The specific content of Step 5 is as follows: Convert the tree structure into a key-value pair, where the key name of the root node is the table name, and the value is an array that includes the key-value pairs of all nodes of the tree. Use the snakeyaml function library for persistence and output it as a yaml template file.

7. A method for changing a redundant table in a database based on feature recognition according to claim 1, characterized in that The normal business and technical modifications to the yaml configuration file in step 6 specifically include: specifying the primary key field, primary key value, batch modification fields, batch combination fields, batch adding data and generating a rollback script.

8. A method for changing redundant tables in a database based on feature recognition according to claim 1, characterized in that, The output script in step 7 is specifically as follows: by recursively traversing the key-value pairs of each node, splicing the key-value pairs; traversing to the first key-value pair of the first tree node, and then recursively traversing the first key-value pair of the second node... traversing the first key-value pair of the last node to generate a script. Next, traverse the second key-value pair of the last node to output the second script; after traversing all the key-value pairs of the last field, return to the second field of the penultimate node, and then traverse the last node; output the combinations of all nodes in this order.

Images