A method and system for analyzing application system adaptation in database migration

Abstract

The present invention discloses a method for analyzing the adaptation of an application system in database migration, relates to the field of database technology, and solves the technical problem of low efficiency in manually analyzing and evaluating database migration. The method includes: obtaining application database information; determining data collection points according to application database information; collecting corresponding data through each collection point, forming structured collection data in JSON format and storing it; performing SQL syntax rule analysis on the structured collection data according to set rules to obtain an adaptation route analysis tree; after collecting enough data and analyzing it, integrating all analysis data to obtain an adaptation migration report and an adaptation route tree; generating an analysis decision report according to the adaptation report. The present invention also discloses a system for applying the above method. The present invention dynamically analyzes the database operations performed during the application operation process to form an analysis decision report for the system for multiple heterogeneous database adaptation solutions, which can improve work efficiency.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and more specifically, to a method and system for analyzing application system adaptation in database migration. Background Art

[0002] With the development of database technology and the requirements of industry policies, more and more companies have the need to replace or upgrade databases and migrate heterogeneous databases. In this context, most companies are not familiar with heterogeneous database migration and do not have a standardized process to evaluate and decide on heterogeneous database migration and replacement work.

[0003] If you want to replace the database for the above requirements, you need to migrate the heterogeneous database and adapt the application system code. The analysis and evaluation of the upper-level application adaptation and transformation during heterogeneous database migration is often done manually. The general process is as follows: the application system developers roughly compare the code involved in the migration adaptation and transformation based on the system code or development documents and the database documents to be adapted, and then analyze, evaluate and make decisions on the heterogeneous database migration. Finally, adapt the application code according to the analysis results. This method is not only slow, but also cannot fully and accurately analyze and evaluate the application system, and is inefficient. Summary of the invention

[0004] The technical problem to be solved by the present invention is to address the above-mentioned deficiencies in the prior art. One purpose of the present invention is to provide a method for analyzing application system adaptation in database migration that can improve efficiency.

[0005] The second objective of the present invention is to provide a system for analyzing application system adaptation in database migration that can improve efficiency.

[0006] To achieve the above-mentioned first objective, the present invention provides a method for analyzing application system adaptation in database migration, comprising:

[0007] For the application system to be adapted, obtain application database information;

[0008] Determine the data collection source, application interface log collection point, SQL execution log collection point, and database log execution collection point according to the application database information;

[0009] Start the application system to be adapted, collect corresponding data through various collection points, form structured collection data in JSON format and store it;

[0010] Perform SQL grammar rule analysis on the structured collected data according to the set rules, generate a corresponding grammar analysis tree, map and transform SQL according to the grammar mapping rules of each database, adapt and transform the original SQL statement into each heterogeneous database SQL statement, and construct an adaptation route analysis tree corresponding to SQL;

[0011] After collecting and analyzing enough data, integrate all the analysis data, output the adaptation route plan of the SQL execution statement of the application under the corresponding interface, and form the adaptation migration report and adaptation route tree of the whole application;

[0012] Based on the adaptation report, a feasibility decision analysis is conducted on the subsequent adaptation, an evaluation of the resources required for route selection is provided, and an analysis decision report is generated. The content of the decision report includes: the number of SQL statements that need to be modified for adaptation of each heterogeneous database, the recommended adaptation route, and the approximate time cost, labor cost, and resource information required for adaptation.

[0013] As a further improvement, the application database information includes deployment information, module information, and interface document address.

[0014] Furthermore, the collection process runs through the application operation cycle, and the collected data is obtained through log analysis.

[0015] Furthermore, the set rules include analysis rules of business interfaces, matching rules, syntax tree rules of various databases, and syntax mapping rules of heterogeneous databases.

[0016] Furthermore, the matching rules include regular expression rules, functions in various databases, and keywords.

[0017] Furthermore, the database syntax tree rules and heterogeneous database syntax mapping rules include database syntax tree rules and database syntax mapping rules of Oracle database, Sql Server database, MySQL database, DAMO database, Renmin University of China Jincang database, and Shenzhou general database.

[0018] Furthermore, the adaptation route analysis tree includes a trunk and multiple branches, the trunk represents the statements of the original database of the application system, and the branches represent the statements obtained according to the grammatical rules of each database and the grammatical mapping rules of each database.

[0019] Furthermore, the statements of each branch are compared, and the cost required to adapt to each database is obtained.

[0020] Furthermore, the content of the adaptation migration report includes: business module name, business interface name, SQL statement to be adapted corresponding to the business interface, SQL keyword to be modified, convertible and adapted target SQL, and SQL adaptation route tree information.

[0021] In order to achieve the above-mentioned second objective, the present invention provides a system for analyzing application system adaptation in database migration, comprising:

[0022] The collection module is used to obtain application database information for the application system to be adapted; determine the data collection source, application interface log collection point, SQL execution log collection point, and database log execution collection point according to the application database information; collect corresponding data through each collection point, form structured collection data in JSON format and store it;

[0023] The rule base module is used to store database adaptation knowledge, including unified rule analysis under the same function of various databases, SQL syntax mapping rules for heterogeneous databases, and syntax tree rules for each database;

[0024] An analysis module is used to perform SQL grammar rule analysis on the structured collected data according to the rules of the rule base module, generate a corresponding grammar analysis tree, map and transform SQL according to the grammar mapping rules of each database, adapt and transform the original SQL statement into each heterogeneous database SQL statement, and construct an adaptation route analysis tree corresponding to SQL;

[0025] The adaptation report generation module is used to integrate all the analysis data after collecting and analyzing enough data, output the adaptation route plan of the SQL execution statement of the application under the corresponding interface, and form the adaptation migration report and adaptation route tree of the whole application;

[0026] The adaptation decision module is used to perform feasibility decision analysis on subsequent adaptation according to the adaptation report, provide an evaluation of the resources required for route selection, and generate an analysis decision report.

[0027] Beneficial Effects

[0028] Compared with the prior art, the present invention has the following advantages:

[0029] This application has a great advantage in the application adaptation in heterogeneous database migration under the context of existing related technical application solutions. Compared with manual investigation and analysis of SQL statements that need to be adapted and static scanning of application codes, the data analysis of interfaces in the dynamic collection and operation system is more in line with the business of the application system. It can clearly know which business in a certain module involves adaptation changes, and provide directional adaptation decision guidance for subsequent adaptation and transformation. It also greatly reduces the technical difficulty of adaptation and transformation, and reduces the dependence on the original application system developers. Any developer can quickly perform adaptation evaluation and transformation on heterogeneous databases of business systems through this adaptation analysis and decision system, which greatly reduces the time cost and adaptation difficulty and improves the adaptation efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] Figure 1 is a flow chart of the method of the present invention;

[0031] Figure 2 It is a grammar analysis tree diagram;

[0032] Figure 3 It is the grammar analysis rule graph;

[0033] Figure 4 is the grammar mapping rule graph;

[0034] Figure 5 Adapt route tree diagram for SQL. DETAILED DESCRIPTION

[0035] The present invention will be further described below with reference to the specific embodiments in the accompanying drawings.

[0036] The embodiment of the present invention solves the problem of analyzing and making decisions on the rapid adaptation and transformation of the upper-level application system code in the migration of heterogeneous databases. For example, an application system is developed using Java, and the original database is Oracle. It wants to replace the database, but does not understand the databases on the market and the cost of replacing them with these heterogeneous databases. To meet this demand, it is necessary to know how many adaptation routes there are for the original database based on the mainstream databases on the market, and how high the adaptation cost is. The embodiment of the present invention uses an external component to obtain the executed database SQL by collecting data from the running application system, and outputs the SQL code and adaptation route analysis tree corresponding to the mainstream database according to the mapping rules in the rule library, and finally forms an adaptation and transformation analysis and evaluation report for development engineers to analyze and evaluate decisions, and finally select a suitable adaptation route and perform adaptation and transformation.

[0037] See also Figures 1 to 5 , a method for analyzing application system adaptation in database migration, comprising:

[0038] For the application system to be adapted, such as the OA office WEB system, obtain the application database information, which includes deployment information, module information, and interface document address;

[0039] Determine the data collection source, application interface log collection point, SQL execution log collection point, and database log execution collection point based on the application database information;

[0040] Start the OA office WEB system, collect corresponding data through various collection points, form structured collection data in JSON format and store it. The collection process runs through the application operation cycle, and the collection data is obtained through log analysis;

[0041] According to the set rules, the structured collected data is analyzed by SQL syntax rules to generate the corresponding syntax analysis tree, such as Figure 2 As shown, according to the syntax mapping rules of each database, the SQL is mapped and converted, the original SQL statement is adapted and transformed into the SQL statement of each heterogeneous database, and the adaptation route analysis tree corresponding to the SQL is constructed, as shown in Figure 5 As shown; the adaptation route analysis tree includes a trunk and multiple branches. The trunk represents the statements of the original database of the application system, and the branches represent the statements obtained according to the grammatical rules of each database and the grammatical mapping rules of each database. The statements of each branch are compared, and the cost required for adapting each database is obtained;

[0042] After collecting and analyzing enough data, integrate all the analysis data, output the adaptation route plan of the SQL execution statement of the application under the corresponding interface, and form the adaptation migration report and adaptation route tree of the whole application. The content of the adaptation migration report includes: business module name, business interface name, SQL statement to be adapted corresponding to the business interface, SQL keywords to be modified, target SQL that can be converted and adapted, and SQL adaptation route tree information;

[0043] Based on the adaptation report, a feasibility decision analysis is conducted on the subsequent adaptation, an evaluation of the resources required for route selection is provided, and an analysis decision report is generated. The content of the decision report includes: the number of SQL statements that need to be modified for adaptation of each heterogeneous database, the recommended adaptation route, and the approximate time cost, labor cost, and resource information required for adaptation.

[0044] The set rules include business interface analysis rules, matching rules, database syntax tree rules, and heterogeneous database syntax mapping rules.

[0045] Matching rules include regular expression rules, functions in various databases, and keywords.

[0046] The database syntax tree rules and heterogeneous database syntax mapping rules include the database syntax tree rules and database syntax mapping rules of Oracle database, SqlServer database, Mysql database, DAMO database, Renmin University of China Jincang database, and Shenzhou general database. For example, the keyword to query the current date and time is the sysdate keyword in Oracle, while Mysql generally uses the now() function, and in Sql Server it is the GETDATE() function, and DAMO uses the SYSDATE() function. When using the rule base rules for analysis, the original SQL statement can be parsed into the corresponding syntax tree, and the generated syntax tree mapping is converted into the SQL statement of other databases through the syntax mapping rules.

[0047] like Figure 3 As shown, the left outer link query of the original database Oracle of the application system is as follows:

[0048] select a.*,b.*from a,b where a.id=b.id(+)

[0049] Parsed into a syntax tree through grammar rules.

[0050] The rule detects the key syntax characters a.id=b.id(+) and determines that the statement is a left outer join query. It is then converted into SQL statements suitable for each database through the syntax mapping rules of each database. For example, the left join...on... syntax is mapped to Sql Server, Mysql and DAMO databases, and the Jincang database can use the same syntax rules and combine the syntax tree rules to generate SQL statements.

[0051] like Figure 4 As shown, the final left outer join query statement using the left join...on... syntax is formed:

[0052] select a.*,b.*from a left join b on a.id=b.id

[0053] Then the modified SQL statement is output and combined with other database adaptation statements to form the adaptation route analysis tree corresponding to this SQL statement, such as Figure 5 As shown in the figure, the trunk on the left represents the statements of the original database of the application system, and the branches on the right represent the statements obtained according to the grammatical rules of each database and the grammatical mapping rules of each database. Comparing the statements of each branch, from the adaptation route analysis tree of this SQL statement, the cost required to adapt to the Renda Jincang database is the lowest, and no code changes are required.

[0054] A system for analyzing application system adaptation in database migration, comprising:

[0055] The collection module is used to obtain application database information for the application system to be adapted; determine the data collection source, application interface log collection point, SQL execution log collection point, and database log execution collection point according to the application database information; collect corresponding data through each collection point, form structured collection data in JSON format, and store it;

[0056] The rule base module is used to store database adaptation knowledge, including unified rule analysis under the same function of various databases, SQL syntax mapping rules for heterogeneous databases, and syntax tree rules for each database;

[0057] The analysis module is used to perform SQL syntax rule analysis on the structured collected data according to the rules of the rule base module, generate the corresponding syntax analysis tree, map and transform the SQL according to the syntax mapping rules of each database, adapt and transform the original SQL statement into the SQL statement of each heterogeneous database, and construct the adaptation route analysis tree corresponding to the SQL;

[0058] The adaptation report generation module is used to integrate all the analysis data after collecting and analyzing enough data, output the adaptation route plan of the SQL execution statement of the application under the corresponding interface, and form the adaptation migration report and adaptation route tree of the whole application;

[0059] The adaptation decision module is used to conduct feasibility decision analysis on subsequent adaptations based on the adaptation report, provide an evaluation of the resources required for route selection, and generate an analysis decision report.

[0060] The above are only preferred embodiments of the present invention. It should be pointed out that for those skilled in the art, several modifications and improvements can be made without departing from the structure of the present invention, which will not affect the effect of the implementation of the present invention and the practicality of the patent.

Claims

1. A method for analyzing application system adaptation in database migration, characterized in that: include: For the application system to be adapted, obtain application database information; Determine the data collection source, application interface log collection point, SQL execution log collection point, and database log execution collection point according to the application database information; Start the application system to be adapted, collect corresponding data through various collection points, form structured collection data in JSON format and store it; Perform SQL grammar rule analysis on the structured collected data according to the set rules, generate a corresponding grammar analysis tree, map and transform the SQL statement according to the grammar mapping rules of each heterogeneous database, adapt and transform the original SQL statement into the SQL statement of each heterogeneous database, and construct an adaptation route analysis tree corresponding to the SQL statement; Integrate all analysis data, output the adaptation route plan of the SQL execution statement of the application under the corresponding interface, and form the adaptation migration report and adaptation route tree of the whole application; Conduct a feasibility decision analysis on subsequent adaptation based on the adaptation migration report, provide an evaluation of the resources required for route selection, and generate an analysis decision report. The content of the analysis decision report includes: the number of SQL statements that need to be modified for adaptation of each heterogeneous database, the recommended adaptation route, and the time cost, labor cost, and resource information required for adaptation; The adaptation route analysis tree includes a trunk and multiple branches, the trunk represents the SQL statements of the original database of the application system, and the branches represent the SQL statements obtained according to the grammatical rules of each heterogeneous database and the grammatical mapping rules of each heterogeneous database.

2. A method for analyzing application system adaptation in database migration according to claim 1, characterized in that: The application database information includes deployment information, module information, and interface document address.

3. A method for analyzing application system adaptation in database migration according to claim 1, characterized in that: The collection process runs through the application operation cycle, and the collected data is obtained through log analysis.

4. A method for analyzing application system adaptation in database migration according to claim 1, characterized in that: The set rules include analysis rules, matching rules, syntax tree rules of various heterogeneous databases, and syntax mapping rules of various heterogeneous databases of the business interface.

5. A method for analyzing application system adaptation in database migration according to claim 4, characterized in that: The matching rules include regular expression rules, functions in various heterogeneous databases, and keywords.

6. A method for analyzing application system adaptation in database migration according to claim 4, characterized in that: The syntax tree rules of each heterogeneous database and the syntax mapping rules of each heterogeneous database include database syntax tree rules and database syntax mapping rules of Oracle database, SqlServer database, MySQL database, DAMO database, Renmin University of China Jincang database, and Shenzhou general database.

7. A method for analyzing application system adaptation in database migration according to claim 1, characterized in that: Compare the SQL statements of each branch and obtain the cost required to adapt to each heterogeneous database.

8. A method for analyzing application system adaptation in database migration according to claim 1, characterized in that: The content of the adaptation migration report includes: business module name, business interface name, SQL statement to be adapted corresponding to the business interface, SQL keyword to be modified, target SQL statement that can be converted and adapted, and SQL statement adaptation route tree information.

9. A system for analyzing application system adaptation in database migration, characterized in that: include: The acquisition module is used to obtain application database information for the application system to be adapted; Determine the data collection source, application interface log collection point, SQL execution log collection point, and database log execution collection point according to the application database information; collect corresponding data through each collection point, form structured collection data in JSON format and store it; The rule base module is used to store database adaptation knowledge, including unified analysis rules for the same function of various heterogeneous databases, syntax mapping rules for heterogeneous databases, and syntax tree rules for various heterogeneous databases; An analysis module is used to perform SQL grammar rule analysis on the structured collected data according to the rules of the rule base module, generate a corresponding grammar analysis tree, map and transform the SQL statement according to the grammar mapping rules of each heterogeneous database, adapt and transform the original SQL statement into the SQL statement of each heterogeneous database, and construct an adaptation route analysis tree corresponding to the SQL statement; The adaptation report generation module is used to integrate all analysis data, output the adaptation route plan of the SQL execution statement of the application under the corresponding interface, and form the adaptation migration report and adaptation route tree of the whole application; The adaptation decision module is used to perform feasibility decision analysis on subsequent adaptation according to the adaptation migration report, provide an evaluation of resources required for route selection, and generate an analysis decision report.

Images