Full-automatic comparison method and device for evaluating database transformation effect and medium

Abstract

The invention relates to a full-automatic comparison method, equipment and medium for evaluating a database transformation effect, which are used for evaluating the transformation effect of transforming a first database into a second database, and the method comprises the following steps: S1, scanning Mapper files corresponding to the first database and the second database, and generating an executable SQL statement according to a set analysis rule; s2, scanning from the first database and the second database to obtain a table structure and table content, and replacing corresponding content of the executable SQL statement according to the table structure and the table content; and S3, packaging the executable SQL statement after content replacement, sending a request through an execution interface, returning an execution result, and comparing the execution result. Compared with the prior art, the method has the advantages of high automation degree and high accuracy.

Description

Technical Field

[0001] The present invention relates to the technical field of database transformation, and in particular to a fully automatic comparison method, device and medium for evaluating database transformation effects. Background Art

[0002] With the deepening of digital transformation and information innovation in banks, localization of databases has become one of the key steps in the development of banking systems. In the process of digital transformation of banks, databases, as the core data storage and processing systems of banks, need to have efficient, secure and reliable data storage and management capabilities to meet the ever-changing business needs and user needs of banks.

[0003] The transformation of the database mainly includes two aspects: one is to change the database of the original system to a domestic database, and the other is to transform the SQL statements in the program into executable statements of the domestic database, and optimize the original statements in accordance with relevant syntax specifications. The transformed database and SQL need to maintain content consistency with the original system to ensure the normal and orderly progress of the business, and on this basis, improve the effectiveness of the system, increase the fluency of business functions, and make the customer experience more friendly and convenient. Verification of the transformation effect is a very important part of database transformation, which can test the recent work results of developers and ensure the accurate and orderly progress of the transformation work.

[0004] How to effectively and accurately verify the transformation effect is an urgent problem to be solved. The verification of the transformation effect includes two aspects: one is the result returned by the transformed SQL, and the other is the execution time of the transformed SQL. The existing tool Postman relies on manual SQL writing and request sending, which is time-consuming and labor-intensive, and cannot fully meet the project team's requirements for database effect comparison. Summary of the invention

[0005] The purpose of the present invention is to overcome the defects of the above-mentioned prior art and to provide a fully automatic comparison method, device and medium with high automation and accuracy for evaluating the effect of database transformation.

[0006] The purpose of the present invention can be achieved by the following technical solutions:

[0007] According to a first aspect of the present invention, there is provided a fully automatic comparison method for evaluating the effect of database transformation, which is used to evaluate the effect of transforming a first database into a second database. The method comprises:

[0008] S1. Scan the Mapper files corresponding to the first database and the second database, and generate executable SQL statements according to the set parsing rules;

[0009] S2, scanning and obtaining table structures and table contents from the first database and the second database, and replacing corresponding contents of executable SQL statements according to the table structures and contents;

[0010] S3. Encapsulate the executable SQL statement after replacing the content, send a request through the execution interface, return the execution result, and compare the execution result.

[0011] Preferably, in S1, scanning the Mapper files corresponding to the first database and the second database, and generating executable SQL statements according to the set parsing rules, specifically includes:

[0012] The set tag pairs in the Mapper files corresponding to the first database and the second database are scanned through the ElementTree library, the corresponding Mapper statements are found, the tag pairs are removed, the matched "#{field}" is replaced with "1", and an executable SQL statement is generated.

[0013] Preferably, the setting tag pair includes <select>< / select> , <update>< / update> , <insert>< / insert> ,as well as <delete>< / delete> .

[0014] Preferably, S1 also includes scanning special tags in the Mapper files corresponding to the first database and the second database, and generating executable SQL statements according to set parsing rules, specifically:

[0015] for <insert>The tag's primary key attributes "keyProperty" and "useGeneratedKeys". If the "useGeneratedKeys" primary key attribute value is true, and there is no "keyProperty" attribute value as a field in the SQL statement, then the corresponding field plus the field prefix is ​​added to the executable SQL statement, and the field value "1" is added at the corresponding position;

[0016] for <if>< / if> Tag pair, remove the entire if condition, and add the corresponding field and field value "1" to the executable SQL statement;

[0017] for <where>< / where> Tag pair, add "where" to <where>The original position, and then remove the original label pair;

[0018] When updating multiple fields in an update statement, if the set part ends with a comma, remove the comma;

[0019] for <set>< / set> Tag pair, replaced by set;

[0020] For the <!--Comment Content--> tag, delete the processing.

[0021] Preferably, in S2, the corresponding content of the executable SQL statement is replaced according to the table structure and content, specifically:

[0022] Compare the table structures of the first database and the second database, check the consistency of the field names and field attributes in each table of the database, and record the inconsistency information in the result record;

[0023] Scan the executable SQL statements, search the table names involved in the executable SQL statements in the result records, compare and match the fields, replace "1" in the executable SQL statements with the field content in the result records, and remove the fields that are not in the result records of the executable SQL statements.

[0024] Preferably, the table structures of the first database and the second database are compared, the field names and field attributes in each table of the database are checked for consistency, and the inconsistency information is recorded in the result record, specifically:

[0025] 1) Connect the first database and the second database, obtain all table names, and cyclically scan the table names in the second database;

[0026] 2) Determine whether the table name in the second database matches the table name in the first database. If so, go to 3). Otherwise, determine that "the table name is inconsistent or does not exist" and record it in the result record;

[0027] 3) Obtaining field information of tables in the first database and the second database, and cyclically scanning the fields in the tables in the second database;

[0028] 4) Determine whether the field in the table in the second database matches the field in the table in the first database. If so, go to 5). Otherwise, determine that "the field name is inconsistent or does not exist" and record it in the result record;

[0029] 5) Get field attributes;

[0030] 6) Determine whether the field attributes in the table in the second database match the field attributes in the table in the first database. If they match, go to 3) loop; otherwise, determine that "field attributes are inconsistent" and record it in the result record.

[0031] Preferably, in S3, the executable SQL statement after the content is replaced is encapsulated, and a SQL request is sent to the execution interface through the requests library to simulate the real environment, and the execution time and execution result are returned;

[0032] The interface parameters corresponding to the SQL request include the execution database type, execution operation and executable SQL statements, and the corresponding return results include the SQL execution time and execution result.

[0033] Preferably, encapsulating the executable SQL statement after replacing the content includes:

[0034] For an insert statement, a delete message is generated first. Before sending each insert request message, a delete request message is sent first.

[0035] For SQL statements containing insert, a delete type SQL statement is generated based on the table name, the first field and the field value, and assembled into the corresponding delete message.

[0036] According to a second aspect of the present invention, there is provided an electronic device, comprising a memory and a processor, wherein a computer program is stored in the memory, and when the processor executes the program, any one of the methods described above is implemented.

[0037] According to a third aspect of the present invention, there is provided a computer-readable storage medium having a computer program stored thereon, wherein when the program is executed by a processor, any one of the methods described above is implemented.

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

[0039] 1) The entire process of generating SQL and comparing messages in the present invention is fully automated, which realizes the comparison before and after the database transformation, reduces a lot of manpower costs, and increases the efficiency and accuracy of the comparison process; developers can intuitively and conveniently judge the consistency of the effects before and after the database transformation, and at the same time easily discover problems in the database transformation process, reduce the risks that may exist in the database transformation, and ensure the accuracy of the transformation effect.

[0040] 2) The present invention can parse the tag pair information more quickly and accurately by scanning the Mapper file corresponding to the database through the ElementTree library.

[0041] 3) The present invention is carried out by <insert>The tag's primary key attributes "keyProperty" and "useGeneratedKeys" are parsed. When "useGeneratedKeys" is true and there is no "keyProperty" attribute value as a field in the SQL statement, the corresponding field plus the field prefix is ​​added to the executable SQL statement to solve the problem. <insert>Tags have their own primary keys, which causes SQL fields to be missing.

[0042] 4) Compare the table structures of the first database and the second database, check the consistency of the field names and field attributes in each table of the database, and record the inconsistency information in the result record, which can be used for the accuracy of subsequent SQL statement content replacement. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] Figure 1 Flow chart of the method of the present invention

[0044] Figure 2 This is a flowchart for comparing database table structures. DETAILED DESCRIPTION

[0045] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.

[0046] Example

[0047] like Figure 1 As shown, this embodiment provides a fully automatic comparison method for evaluating the effect of database transformation, which is used to evaluate the effect of transforming a first database into a second database. The method includes:

[0048] S1. Scan the Mapper files corresponding to the first database and the second database, and generate executable SQL statements according to the set parsing rules;

[0049] S2, scanning and obtaining table structures and table contents from the first database and the second database, and replacing corresponding contents of executable SQL statements according to the table structures and contents;

[0050] S3. Encapsulate the executable SQL statement after replacing the content, send a request through the execution interface and return the execution result, and compare the execution result.

[0051] Next, taking the transformation of a DB2 database to an OceanBase database as an example, the method of this embodiment is described in detail, specifically including:

[0052] 1. This embodiment uses the ElementTree library to scan the Mapper files corresponding to the DB2 database and the OceanBase database. The ElementTree library is a lightweight, high-performance XML document parsing library based on Python, which can quickly parse and operate XML documents.

[0053] Specifically, the root node is generated through the ElementTree library, the child nodes under the root node are scanned, the information about each tag is obtained, and the "namespace" and "id" are recorded to distinguish and finally generate an executable SQL statement.

[0054] 2. Generate corresponding executable SQL statements according to the set parsing rules.

[0055] By scanning the file <select>< / select> , <update>< / update> , <insert>< / insert> , <delete>< / delete> Tag pair, find the corresponding Mapper statement, remove the tag pair, match the "#{field}" in it, and replace it with "1" to make it an executable SQL statement.

[0056] At the same time, some special tags and rules are processed to meet the SQL syntax rules required by the database execution. The main processing methods are as follows:

[0057] ① Scan <insert>The primary key attributes "keyProperty" and "useGeneratedKeys" of the tag. If the corresponding value of "useGeneratedKeys" is true, and there is no "keyProperty" attribute value as a field in the SQL statement, then the corresponding field plus the field prefix is ​​added to the executable SQL, and the field value "1" is added at the corresponding position;

[0058] ② Scan <if>< / if> If there is a tag pair, remove the entire if condition and add the corresponding field and field value "1" to the executable SQL;

[0059] ③Scan <where>< / where> Tag pair, if any, add "where" to <where>The original position, and then remove the original label pair;

[0060] ④When updating multiple fields in an update statement, check whether the end of the set part ends with ","; if so, remove the comma;

[0061] ⑤Scan <set> 、< / set> Tag pair, if any, is replaced by set;

[0062] ⑥ Scan <! --Comment content--> and delete the relevant comments.

[0063] The above rules can adapt to most SQL statements, but there are some special and complex statements that cannot be fully applied. Currently, they rely on subsequent manual modification and testing, and can be optimized in subsequent work.

[0064] Scan the content in the tag according to the set rules, generate a relatively complete SQL statement, replace the carriage return, extra spaces and other characters in the SQL, and then replace each field value with "1" through regular matching. The specific implementation code is as follows:

[0065]

[0066] words = str.split("")

[0067] words=[word for word in words if word]

[0068] str = ".join(words)

[0069] str=re.sub("\#.*?\}","'1'",str)

[0070] 3. Solution <insert>The tag has its own primary key, which leads to the problem of missing SQL fields

[0071] scanning <insert>The primary key attributes "keyProperty" and "useGeneratedKeys" of the tag, if the corresponding value of "useGeneratedKeys" is true, and there is no "keyProperty" attribute value as a field in the SQL statement, then the corresponding field plus the field prefix is ​​added to the executable SQL, and the field value "1" is added to the corresponding position.

[0072] 4. Connect to the database, compare the database table structure, and record the comparison results

[0073] The SQLalchemy object-relational mapping tool is used to scan the DB2 database and the OceanBase database respectively. The ibm_db driver is used for the DB2 database, and the pymySQL driver is used for the OceanBase database. Metadata is used to obtain the table structure of the database, so as to achieve the comparison of the library table structure. The whole comparison process is as follows: Figure 2 As shown, including:

[0074] 1) Connect to the DB2 database and the OceanBase database, obtain all table names, and cyclically scan the OceanBase table names;

[0075] 2) Determine whether the OceanBase table name matches the DB2 table name. If so, go to 3). Otherwise, determine that "the table name is inconsistent or does not exist" and record it.

[0076] 3) Obtain the field information of the DB2 table and the OceanBase table, and cyclically scan the fields in the OceanBase table;

[0077] 4) Determine whether the fields in the OceanBase table match the fields in the DB2 table. If they do, go to 5). Otherwise, it is determined that "the field names are inconsistent or do not exist" and recorded.

[0078] 5) Get field attributes;

[0079] 6) Determine whether the field attributes in the OceanBase table match the field attributes in the DB2 table. If they match, go to 3) loop; otherwise, it is determined that "field attributes are inconsistent" and recorded.

[0080] 5. Query the common data of the database

[0081] In order to make the comparison more reasonable, the specific content of the SQL executed in DB2 and OceanBase needs to be consistent to ensure that the execution results are as consistent as possible.

[0082] Query the data of a table in the DB2 database through the session, scan row by row, parse the query results, assemble them into executable SELECT statements of the OceanBase database, and then encapsulate them into execution messages and call the interface for executing SQL statements. If the returned result is not empty, it means that the row of data is shared data. The table and public data are recorded in the form of a dictionary, and the scanning of the table data is stopped. Query the public data of other tables.

[0083] 6. Replace the content in SQL

[0084] According to the common data of each table, the relevant field values ​​in the generated SQL statement are replaced to make it a valid SQL statement. The specific implementation code is as follows:

[0085]

[0086] 7. Encapsulate the message and simulate the real environment to send the request

[0087] The generated SQL statement is encapsulated according to the message requirements, and a request is sent to the execution interface in a simulated real environment. It should be noted that when facing an insert statement, a delete message will be generated first, and a delete request message will be sent before each insert request message is sent.

[0088] In this embodiment, the Java execution interface is used, and the interface parameters include the execution database type, execution operation and executable SQL, and the returned result includes the execution time and execution result of SQL. The interface calls the corresponding Dao layer according to different databases, executes the SQL statement passed through the framework, records the time before and after the execution, and uses the difference as the execution time.

[0089] In the Python program, assemble the message according to the format requirements, and then send the request call interface through Python's requests library to simulate the execution of SQL in a real environment. In addition, for the SQL statement related to insert, the program will generate a delete type SQL statement based on the table name, the first field and the field value, and assemble it into the corresponding delete message. In order to ensure that each insert operation is successful, the program will delete the original data before each insert. This operation has certain risks, and it is necessary to ensure in advance that the data with a field value of "1" can be deleted.

[0090] 8. Analyze the results returned by the interface and compare the messages

[0091] The execution results of the two databases are returned, and by comparing the return messages of the two, a conclusion is drawn as to whether the messages are consistent, and the relevant results are recorded.

[0092] The Python program calls the Java interface multiple times for the same SQL statement, and records the average execution time as the SQL execution time. According to the established comparison rules, the results are automatically compared and analyzed, and the comparison results are divided into consistent and inconsistent. The execution and comparison results are recorded to facilitate subsequent developers to check and compare. If the execution results are exactly the same, it is the first case. In the execution results of the OceanBase database, the field return results of the count() function are different from those of DB2, but the content is the same. This is judged in the program. There are also some cases where the field capitalization is inconsistent, which are all classified as consistent.

[0093] The entire process of generating SQL statements and comparing messages is fully automated, and automatic comparison before and after database transformation is achieved, which reduces a lot of labor costs and increases the efficiency and accuracy of the comparison process. Through this comparison tool, developers can intuitively and conveniently determine the consistency of the effects before and after the system transformation, and at the same time easily discover problems in the database transformation process, reduce possible risks in the system transformation, and ensure the accuracy of the system transformation effect.

[0094] The electronic device of the present invention includes a central processing unit (CPU), which can perform various appropriate actions and processes according to computer program instructions stored in a read-only memory (ROM) or loaded from a storage unit into a random access memory (RAM). In the RAM, various programs and data required for device operation can also be stored. The CPU, ROM and RAM are connected to each other via a bus. An input / output (I / O) interface is also connected to the bus.

[0095] Multiple components in the device are connected to the I / O interface, including: input units, such as keyboards, mice, etc.; output units, such as various types of displays, speakers, etc.; storage units, such as disks, optical disks, etc.; and communication units, such as network cards, modems, wireless communication transceivers, etc. The communication unit allows the device to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunication networks.

[0096] The processing unit performs the various methods and processes described above. For example, in some embodiments, the method can be implemented as a computer software program, which is tangibly contained in a machine-readable medium, such as a storage unit. In some embodiments, part or all of the computer program can be loaded and / or installed on the device via a ROM and / or a communication unit. When the computer program is loaded into RAM and executed by the CPU, one or more steps of the method described above can be performed. Alternatively, in other embodiments, the CPU can be configured to perform the method in any other appropriate manner (e.g., by means of firmware).

[0097] The functions described above herein may be performed at least in part by one or more hardware logic components. For example, without limitation, exemplary types of hardware logic components that may be used include: field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chip systems (SOCs), load programmable logic devices (CPLDs), and the like.

[0098] The program code for implementing the method of the present invention can be written in any combination of one or more programming languages. These program codes can be provided to a processor or controller of a general-purpose computer, a special-purpose computer or other programmable data processing device, so that the program code, when executed by the processor or controller, enables the functions / operations specified in the flow chart and / or block diagram to be implemented. The program code can be executed entirely on the machine, partially on the machine, partially on the machine as a stand-alone software package and partially on a remote machine, or entirely on a remote machine or server.

[0099] In the context of the present invention, a machine-readable medium may be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, device, or equipment. A machine-readable medium may be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices, or equipment, or any suitable combination of the foregoing. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0100] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any technician familiar with the technical field can easily think of various equivalent modifications or replacements within the technical scope disclosed by the present invention, and these modifications or replacements should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention shall be based on the protection scope of the claims.< / insert> < / insert> < / where> < / insert> < / insert> < / insert> < / where> < / insert>

Claims

1. A fully automatic comparison method for evaluating the effect of database transformation, used to evaluate the effect of transforming a first database into a second database, characterized in that: The method includes: S1. Scan the Mapper files corresponding to the first database and the second database, and generate executable SQL statements according to the set parsing rules; S2, scanning and obtaining table structures and table contents from the first database and the second database, and replacing corresponding contents of executable SQL statements according to the table structures and contents; S3. Encapsulate the executable SQL statement after replacing the content, send a request through the execution interface, return the execution result, and compare the execution result.

2. A fully automatic comparison method for evaluating the effect of database transformation according to claim 1, characterized in that: In S1, the Mapper files corresponding to the first database and the second database are scanned, and executable SQL statements are generated according to the set parsing rules, which specifically include: The set tag pairs in the Mapper files corresponding to the first database and the second database are scanned through the ElementTree library, the corresponding Mapper statements are found, the tag pairs are removed, and the matched "#{field}" is replaced with "1" to generate executable SQL statements.

3. A fully automatic comparison method for evaluating the effect of database transformation according to claim 2, characterized in that: The setting tag pair includes <select>< / select> , <update>< / update> , <insert>< / insert> ,as well as <delete>< / delete> .

4. A fully automatic comparison method for evaluating the effect of database transformation according to claim 2, characterized in that: The S1 also includes scanning the special tags in the Mapper files corresponding to the first database and the second database, and generating executable SQL statements according to the set parsing rules, specifically: for <insert> The tag's primary key attributes "keyProperty" and "useGeneratedKeys". If the "useGeneratedKeys" primary key attribute value is true, and there is no "keyProperty" attribute value as a field in the SQL statement, then the corresponding field plus the field prefix is ​​added to the executable SQL statement, and the field value "1" is added at the corresponding position;< / insert> for <if>< / if> Tag pair, remove the entire if condition, and add the corresponding field and field value "1" to the executable SQL statement; for <where>< / where> Tag pair, add "where" to <where> The original position, and then remove the original label pair;< / where> When updating multiple fields in an update statement, if the set part ends with a comma, remove the comma; for <set>< / set> Tag pair, replaced by set; For the <!--Comment Content--> tag, delete the processing.

5. A fully automatic comparison method for evaluating the effect of database transformation according to claim 2, characterized in that: In S2, the corresponding content of the executable SQL statement is replaced according to the table structure and content, specifically: Compare the table structures of the first database and the second database, check the consistency of the field names and field attributes in each table of the database, and record the inconsistency information in the result record; Scan the executable SQL statements, search the table names involved in the executable SQL statements in the result records, compare and match the fields, replace "1" in the executable SQL statements with the field content in the result records, and remove the fields that are not in the result records of the executable SQL statements.

6. A fully automatic comparison method for evaluating the effect of database transformation according to claim 5, characterized in that: The table structures of the first database and the second database are compared, the field names and field attributes in each table of the database are checked for consistency, and the inconsistency information is recorded in the result record, specifically: 1) Connect the first database and the second database, obtain all table names, and cyclically scan the table names in the second database; 2) Determine whether the table name in the second database matches the table name in the first database. If so, go to 3). Otherwise, determine that "the table name is inconsistent or does not exist" and record it in the result record; 3) Obtaining field information of tables in the first database and the second database, and cyclically scanning the fields in the tables in the second database; 4) Determine whether the field in the table in the second database matches the field in the table in the first database. If so, go to 5). Otherwise, determine that "the field name is inconsistent or does not exist" and record it in the result record; 5) Get field attributes; 6) Determine whether the field attributes in the table in the second database match the field attributes in the table in the first database. If they match, go to 3) loop; otherwise, determine that "field attributes are inconsistent" and record it in the result record.

7. A fully automatic comparison method for evaluating the effect of database transformation according to claim 1, characterized in that: In S3, the executable SQL statement after the content is replaced is encapsulated, and the SQL request is sent to the execution interface through the requests library to simulate the real environment, and the execution time and execution result are returned; The interface parameters corresponding to the SQL request include the execution database type, execution operation and executable SQL statements, and the corresponding return results include the SQL execution time and execution result.

8. A fully automatic comparison method for evaluating the effect of database transformation according to claim 7, characterized in that: The step of encapsulating the executable SQL statement after replacing the content includes: For an insert statement, a delete message is generated first. Before sending each insert request message, a delete request message is sent first. For SQL statements containing insert, a delete type SQL statement is generated based on the table name, the first field and the field value, and assembled into the corresponding delete message.

9. An electronic device comprising a memory and a processor, wherein a computer program is stored in the memory, wherein: When the processor executes the program, the method according to any one of claims 1 to 8 is implemented.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 8 is implemented.

Images