WO2012034440A1 - Method and device for generating database upgrading script - Google Patents

Abstract

A method and device for generating database upgrading script are disclosed. The method includes: setting and storing the upgrading rule description file corresponding to the data change in a database table, obtaining the table structures before and after database upgrading by analyzing the Structured Query Language (SQL) statement for creating the table, and comparing the table structures before database upgrading with the table structures after database upgrading, parsing the upgrading rule description file, and generating the database upgrading script according to the parsing result and the comparing result of the table structures change. The method generates the corresponding upgrading statement for creating a new table or deleting an original table according to the comparing result of the table structures before and after upgrading when the database upgrading script is generated, and the SQL statement for importing data into the new table is also generated, and the statement includes the value of a new added field in the new table or the algorithm on which the original data in the original table depends to upgrade to the new data in the new table.

Description

 Method and device for generating database upgrade script

 The present invention relates to the field of database upgrade technologies, and in particular, to a method and an apparatus for generating a database upgrade script. Background technique

 At present, in the development of telecom management projects, multiple code branches need to be developed and maintained according to the needs of the application, and corresponding to the upgrade between different versions, the upgrade of the version includes: program upgrade, configuration file upgrade and database Upgrade. Among them, the database upgrade is the most cumbersome, the database upgrade is achieved by executing the database script written in advance. Due to the complexity of the telecommunication service, the change of the network element model is relatively large, and the changes of factors such as the network element model cause the database to change accordingly. Since the amount of data in the database is large and the fields are large, the database script is manually written. The workload is large and error prone.

 In order to solve the problem of writing a large amount of database scripts, a method for automatically generating a database upgrade script has been proposed in the prior art, and the method obtains a change of the database table structure by comparing the source version script file and the target version script file of the database, according to The database table structure changes to generate an upgrade script. However, it is not enough to just compare the changes in the database table structure, because there are corresponding records in the database table, that is, data, and the data in the database table after the upgrade will also change. Therefore, the database upgrade script generated by the existing method fails to fully reflect the change of the database, and the readability is poor. In addition, since the generated database upgrade script does not include the change of the data, and the backup of the original data is not implemented, Unable to implement rollback of the version. Summary of the invention

In view of this, the main object of the present invention is to provide a generator for generating a database upgrade script. The method and device enable the generated upgrade script to describe changes in the structure of the database table, as well as describe changes in the data in the table, and the upgrade script can implement rollback.

 In order to solve the above technical problem, the technical solution of the present invention is implemented as follows:

 The present invention provides a method for generating a database upgrade script, and sets and stores an upgrade rule description file corresponding to the data change in the database table. The method further includes:

 By analyzing the table structure structured query language SQL statement, the table structure before and after the database upgrade is obtained, and the table structure before and after the database upgrade is compared; the upgrade rule description file is parsed, and the database upgrade is generated according to the analysis result and the comparison result of the table structure change. script.

 The upgrade rule description file includes: an algorithm for upgrading the original data in the original table to new data in the new table, and a value of a new field in the upgraded new table.

 The upgrade rule description file is an extensible markup language XML file format or a text file format.

 The parsing upgrade rule description file is: a table name, a field name, and a default value. The three parts of the upgrade rule description file are correspondingly classified, and the contents of each part are correspondingly stored in the table entry as a table name, a field name, and a default. The value in the table.

 The table name and the field name are the table name and the field name in the new table obtained after the upgrade; the default value is: an algorithm according to the upgrade of the original data in the upgrade rule description file to the new data, or a new table after the upgrade The value of the new field.

 The generated database upgrade script includes an upgrade statement and a fallback statement.

In the above solution, in the process of generating a database upgrade script, the method further includes: generating a SQL statement for creating a table space in addition to the built-in SQL statement in the database script file; creating a user, a user-authorized SQL statement ; create a sequence number of SQL statements; create a view of the SQL statement; create a stored procedure SQL statement; create a trigger SQL statement and static configuration data insert statement changes script; also generate control for the upgrade operation Script.

 The present invention also provides a device for generating a database upgrade script, which includes a lexical analysis module and a syntax analysis module. The device further includes: an upgrade rule storage module, an upgrade rule analysis module, and an upgrade script generation module;

 The upgrade rule storage module is configured to store an upgrade rule description file corresponding to the data change in the set database table;

 The upgrade rule parsing module is configured to parse the upgrade rule description file stored in the upgrade rule storage module, and send the parsing result to the upgrade script generation module;

 The upgrade script generating module is configured to compare a table structure before and after the database upgrade sent by the parsing module, and generate a database upgrade according to the parsing result sent by the upgrade rule parsing module and the comparison result of the database structure before and after the database upgrade. script.

 The parsing operation performed by the upgrade rule parsing module is specifically:

 Calling and analyzing the upgrade rule description file stored in the upgrade rule storage module, classifying the content in the upgrade rule description file according to the table name, the field name, and the default value, and storing the contents of each part in the table entry as the table name. , field names, and default values in the table.

 The upgrade script generating module is further configured to generate a SQL statement for creating a table space in addition to the built-in SQL statement in the database script file; creating a SQL statement authorized by the user and the user; and creating a SQL statement of the serial number ; create a view of the SQL statement; create a stored procedure SQL statement; create a trigger SQL statement and static configuration data insert statement changes script, and generate a script to control the upgrade operation.

The method and device for generating a database upgrade script provided by the present invention focus on setting an upgrade rule description file, which describes an upgrade rule of data in a database table, and specifically includes upgrading original data in the original table to new data in the new table. Based on the algorithm, and the value of the new field in the new table after the upgrade. When generating the database upgrade script, generate the corresponding upgrade statement for creating a new table or deleting the original table according to the comparison result of the table structure before and after the upgrade; and also generating data for importing into the new table. The SQL statement, which includes the value of the new field in the new table or the algorithm by which the original data in the original table is upgraded to the new data in the new table. Therefore, the present invention can describe not only the change of the database table structure, but also the change of the data in the table, and the readability of the upgrade script is enhanced.

 In addition, the database upgrade script generated by the present invention further includes an upgrade statement for backing up the original table and a rollback statement, which can be rolled back to the original version of the database if the user needs or upgrade fails. DRAWINGS

 1 is a schematic flowchart of a method for generating a database upgrade script according to the present invention;

 FIG. 2 is a schematic structural diagram of a database upgrade script generating apparatus according to the present invention. detailed description

 As can be seen from the prior art, the database script file is roughly divided into the following eight parts according to the function: a) a structured query language (SQL) statement for creating a table space; b) creating a SQL statement authorized by the user and the user; c) Create SQL statements for tables and indexes; d) create SQL statements with serial numbers; e) create SQL statements for views; f) create SQL statements for stored procedures; g) create SQL statements for triggers; and h) statically configure data Insert the statement. In the database upgrade process, the data in the table structure and the table are often changed.

 The c) the table and the index of the SQL statement mainly include: a table name, a field name, a field type, an index name, an index field, etc., by analyzing the statement, the table structure of the database can be obtained, in the prior art, For the c) the table and the indexed SQL statement are successively subjected to lexical analysis and parsing to obtain the table structure of the database. In the subsequent description of the present invention, c) the SQL statement for creating the table and the index is simply referred to as the table-building SQL statement; The other seven parts of the database script file are usually unchanged during the upgrade process. The present invention mainly describes the changes in the table structure and the data in the table.

The basic idea of the present invention is: setting and storing an upgrade rule description file corresponding to the data change in the database table; obtaining a table structure before and after the database upgrade by analyzing the built-in SQL statement, And compare the table structure before and after the database upgrade; parse the upgrade rule description file, and generate a database upgrade script according to the analysis result and the comparison result of the table structure change.

 The solution of the invention is not only applicable to database upgrade in the field of telecommunication management, but also applicable to database upgrade in other fields, for example: database upgrade of enterprise management system.

 The present invention will be further described in detail below with reference to the accompanying drawings and specific embodiments.

 FIG. 1 is a schematic flowchart of a method for generating a database upgrade script according to the present invention. As shown in FIG. 1, the implementation steps of the method are as follows:

 Step 101: Set and store an upgrade rule description file corresponding to the data change in the table. Here, for different domains, the data in the database table is used to describe different content, such as the field of telecommunication management, and the data in the table. The wireless resource is mainly described. Correspondingly, the upgrade rule description file describes the change of the wireless resource, that is, the upgrade rule of the data in the table is described, for example: the original data in the original table is upgraded to a new one in the new table. The algorithm on which the data is based, and the value of the new field in the new table after the upgrade. The upgrade rule description file can be an Extensible Markup Language (XML) file format or a text file format.

 For the field of telecommunication management, the upgrade rule description file may be set by the relevant personnel of the radio resource module according to the needs of the maintenance work, and may also be set by the database upgrade management personnel. The workload of setting the upgrade rule description file is much smaller than the existing one. The workload of writing a database script.

 Step 102: Obtain a table structure before and after the database upgrade by analyzing the SQL statement of the table; specifically: performing lexical analysis and grammar for each of the built-in SQL statements in the source version script file and the upgraded target version script file respectively after the upgrade. Analysis, the table structure corresponding to the source version script file before the database upgrade, and the table structure corresponding to the target version script file after the database upgrade.

Here, the lexical analysis and grammar analysis process is prior art, and its implementation is briefly described below. This file is scanned to produce a single SQL statement. SQL statements generally end with a semicolon or "/". When you scan to such an end symbol, you get a complete SQL statement and pass the SQL statement to the corresponding module that performs the parsing.

 The grammatical analysis is: obtaining, for each SQL statement obtained by the lexical analysis, the table name, the field name, and the field type information in the SQL statement according to the syntax of the SQL statement, and storing the information in the table attribute list, and the obtained table attribute list. The table structure of the database can be embodied. As shown in Table 1, Table 1 is an embodiment of a list of table attributes obtained by parsing.

 Table 1

 Step 103: Compare the table structure before and after the database upgrade;

The table structure corresponding to the target version script file is compared, that is, the table attribute list corresponding to the source version script file and the table attribute list corresponding to the target version script file are compared, and the table name, the field name, and the field type are sequentially compared.

 For example: Determine whether the table name POLICYPROPERTY in the table attribute list corresponding to the source version script file is also included in the table attribute list corresponding to the target version script file. If it does not exist, it indicates that the table corresponding to the table name has been deleted after the upgrade. On the contrary, if the table name POLICYPROPERTY in the table attribute list corresponding to the target version script file is not included in the table attribute list corresponding to the source version script file, the table corresponding to the table name is the table added after the upgrade. If the table names before and after the upgrade are the same, compare the field name and field type.

Step 104: Parse the upgrade rule description file. Specifically, the upgrade rule description file of the set XML file format or the text file format is analyzed, and the content in the upgrade rule description file is correspondingly classified according to the table name, the field name and the default value, wherein the table is The name and field name are the table name in the new table obtained after the upgrade and the field name in the new table; the default value is the algorithm by which the original data in the original table is upgraded to the new data in the new table, or is in the new table. Add the value of the field, etc. Here, a TABCHANGE table can be created, and the entry is: a table name, a field name, and a default value, and is used to store the parsing result of the upgrade rule description file.

 In the present invention, the sequence of the steps 103 and 104 can be reversed.

 Step 105: Generate a database upgrade script according to the analysis result and the comparison result of the table structure change;

 Specifically: According to the analysis result of the upgrade rule description file, such as the specific content in the TABCHANGE table and the comparison result obtained in step 103, a database upgrade script is generated, which includes an upgrade statement and a fallback statement, all of which are SQL statements. E.g:

 1) If it is determined in step 103 that a table exists in the source version script file, and the table does not exist in the target version script file, the generated upgrade script is as follows:

 Upgrade statement: Modify the SQL statement of the original table named suffix BAK, and delete the original SQL statement;

 Rollback statement: Modify the table with the suffix BAK to the SQL statement of the original table name.

 The original table name is changed to a table with a suffix of BAK, that is, the suffix BAK is added after the suffix of the original table name, and the purpose of modifying the table name is to implement backup of the original table. If you want to restore the suffix BAK, you can directly delete the suffix BAK and get the original table name, which corresponds to the back-off statement described above. This backup method is prior art and will not be described in detail.

 In the process of the backup table, the backup of the original table structure is also included, and the backup of the data in the original table is also included to implement the rollback of the version.

2) If the comparison in step 103 determines that a table exists in the target version script file, and the source version The table does not exist in the script file. The generated upgrade script is as follows:

 Upgrade statement: Create a new table SQL statement and SQL statement to import data into the new table; Rollback statement: Delete the SQL statement of the new table.

 Wherein, since the SQL statement for creating a new table is relatively simple, the corresponding SQL statement can be directly called from the target version script file; the importing the data into the new table is: According to the analysis result of the upgrade rule description file, the corresponding new The data is filled in the new table; the SQL statement that imports the data into the new table includes the default value in the parsing result, that is, the value of the new field in the new table.

 3) If it is determined in step 103 that a table exists in both the source version script file and the target version script file, all field attributes of the table need to be compared, such as: field name and field type, if all field attributes in the table are the same, NOTE: The table and the data in it are unchanged before and after the upgrade. That is, the table does not need to be upgraded. You do not need to generate an upgrade script corresponding to the table. If there are differences, the generated upgrade script is as follows:

 Upgrade statement: Modify the SQL statement of the original table named suffix BAK, the SQL statement to create a new table, and the SQL statement to import data into the new table;

 Rollback statement: Modify the table with the suffix BAK to the SQL statement of the original table name.

 The importing data into the new table is: filling the new table with data corresponding to the table name and the field name according to the parsing result of the upgrade rule description file; and the SQL statement for importing data into the new table This includes the default values in the parsing results, ie the algorithm by which the original data in the original table is upgraded to the new data in the new table.

Further, in the step 105, the table structure and other information except the data change in the table may be described, that is, the seven parts of the database script file except the built-in SQL statement, due to the seven parts. It usually does not change during the database upgrade process, so you can directly call the corresponding content in the target version script file during the upgrade. Therefore, the generated upgrade script can be: Call to the target version script file directory e) Create the view SQL Statement, or call f) create a stored procedure SQL statement, or call g) create a trigger SQL statement, or call h) static State configuration data insertion statements and so on.

 The invention may also generate a script for controlling the upgrade operation, which may include: upgrading the pre-processed SQL statement, querying the version number of the current running environment, and calling the SQL statement of the corresponding version upgrade script and the SQL statement after the upgrade, the script The generation process is prior art and will not be described in detail herein.

 In order to implement the above method, the present invention further provides a device for generating a database upgrade script. As shown in FIG. 2, the device includes: an existing lexical analysis module and a syntax analysis module; and further includes: an upgrade rule storage module and an upgrade rule a parsing module and an upgrade script generating module; wherein, the version script file is scanned, and a SQL statement is generated and transmitted to the parsing module; the parsing module is configured to perform parsing on each SQL statement sent by the lexical module And sending the analyzed table structure before and after the database upgrade to the upgrade script generation module;

 Specifically, the table name, the field name, and the field type information in each SQL statement are obtained according to the syntax of the SQL statement, and are stored in the table attribute list, and the obtained table attribute list is transmitted to the upgrade script generation module.

 The upgrade rule storage module is configured to store an upgrade rule description file corresponding to the data change in the set database table;

 The upgrade rule parsing module is configured to parse the upgrade rule description file stored in the upgrade rule storage module, and send the parsing result to the upgrade script generation module;

 The upgrade script generating module is configured to compare a table structure before and after the database upgrade sent by the parsing module, and generate a database according to the parsing result sent by the upgrade rule parsing module and the comparison result of the table structure before and after the database upgrade. Upgrade the script.

The upgrade script generating module is further configured to generate a script for indicating a change of seven parts other than the built-in SQL statement in the database script file, and generate a script for controlling the upgrade operation. The parsing operation performed by the upgrade rule parsing module is specifically:

 Calling and analyzing the upgrade rule description file stored in the upgrade rule storage module, classifying the content in the upgrade rule description file according to the table name, the field name, and the default value, and storing the contents of each part in the table entry as the table name. , field names, and default values in the table.

 The above description is only for the preferred embodiment of the present invention, and is not intended to limit the scope of the present invention. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present invention should be included. Within the scope of protection of the present invention.

Claims

Claim  A method for generating a database upgrade script, which is characterized in that: an upgrade rule description file corresponding to a data change in a database table is set and stored; the method further includes:  By analyzing the table structure structured query language SQL statement, the table structure before and after the database upgrade is obtained, and the table structure before and after the database upgrade is compared; the upgrade rule description file is parsed, and the database upgrade is generated according to the analysis result and the comparison result of the table structure change. script.  The method according to claim 1, wherein the upgrade rule description file includes: an algorithm according to the original data in the original table being upgraded to new data in the new table, and a new in the upgraded new table. Increase the value of the field.  The method according to claim 1 or 2, wherein the upgrade rule description file is an extensible markup language XML file format or a text file format.  The method according to claim 3, wherein the parsing upgrade rule description file is: a table name, a field name, and a default value, and the content in the upgrade rule description file is correspondingly classified, and each part is contented. The corresponding table is stored in the table whose table entry is the table name, field name, and default value.  The method according to claim 4, wherein the table name and the field name are the table name and the field name in the new table obtained after the upgrade; the default value is: the original data upgrade in the upgrade rule description file. The algorithm on which the new data is based, or the value of the new field in the new table after the upgrade.  The method according to claim 1 or 2, wherein the generated database upgrade script includes an upgrade statement and a fallback statement.  The method according to claim 1 or 2, wherein, in the process of generating a database upgrade script, the method further comprises: Generating a SQL statement for creating a tablespace in addition to the built-in SQL statement in the database script file; creating a user, user-authorized SQL statement; creating a serial number SQL statement; Create a view of the SQL statement; create a stored procedure SQL statement; create a trigger SQL statement and static configuration data insert statement changes script; also generate a script to control the upgrade operation.  8. A device for generating a database upgrade script, comprising: a lexical analysis module and a grammar analysis module; wherein the device further comprises: an upgrade rule storage module, an upgrade rule analysis module, and an upgrade script generation module;  The upgrade rule storage module is configured to store an upgrade rule description file corresponding to the data change in the set database table;  The upgrade rule parsing module is configured to parse the upgrade rule description file stored in the upgrade rule storage module, and send the parsing result to the upgrade script generation module;  The upgrade script generating module is configured to compare a table structure before and after the database upgrade sent by the parsing module, and generate a database upgrade according to the parsing result sent by the upgrade rule parsing module and the comparison result of the database structure before and after the database upgrade. script.  The device according to claim 8, wherein the parsing operation performed by the upgrade rule parsing module is specifically:  Calling and analyzing the upgrade rule description file stored in the upgrade rule storage module, classifying the content in the upgrade rule description file according to the table name, the field name, and the default value, and storing the contents of each part in the table entry as the table name. , field names, and default values in the table.  The device according to claim 8 or 9, wherein the upgrade script generating module is further configured to generate a SQL statement for creating a table space in addition to the table SQL statement in the database script file; Create a user, user-authorized SQL statement; create a serial number SQL statement; create a view of the SQL statement; create a stored procedure SQL statement; create a trigger SQL statement and static configuration data insert statement change script, and generate A script that controls the upgrade operation.