Change management method and system for relational database

Abstract

The invention discloses a change management method and system for a relational database, and the method comprises the steps: 100, responding to a change application request, and receiving change information of tables and fields; step 200, performing data standard and SQL standard check on the change information of the tables and the fields, judging whether the check is passed or not, if so, turning to step 300, and if not, re-receiving the change information of the tables and the fields after the data standard is used; 300, the change application request information is transmitted to a DBA node for change rationality auditing; and step 400, after the verification is passed, executing database change. According to the method, multiple functions of checking function, manual auditing, database automatic deployment, model change and the like are integrated, and data standard, SQL standard checking, DBA auditing, database automatic deployment and data model change are realized in the database change process.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a change management method and system for a relational database. Background Art

[0002] Currently, DBAs focus more on database operation and maintenance, and do not intervene too much in the database application development process and business data architecture. However, whether the database design complies with SQL specifications and how to manage database version changes are tasks that require DBAs to participate in the database architecture design process.

[0003] In database architecture design, the preparatory work of data governance is becoming more and more important. Data standards are an important part of data governance. The process of data management is the process of data standardization. In the process of database architecture design, the verification of the standards of changed data items becomes inevitable. At the same time, the data model is an important asset of the database architecture. Usually, we generate database change scripts through the data model, but after the database is changed, the data model also needs to be changed, otherwise the data model and the database will be two different things.

[0004] In today's world where database instances are numerous and complex, how DBAs, as managers, connect with developers to participate in database architecture design has become a question that needs to be considered. Recently, there are some database CI / CD tools in the industry that DBAs can use to participate in database structure design, but these tools are limited to a single function and application, and cannot integrate multiple functions such as SQL verification, data standard verification, manual review, automatic database deployment, and model changes. Summary of the invention

[0005] The main purpose of the present invention is to provide a relational database change management method and system to address the shortcomings of the prior art database change methods and systems that are limited to a single function and application and cannot integrate multiple functions such as verification functions, manual review, automatic database deployment, and model changes.

[0006] To achieve the above object, a change management method for a relational database of the present invention comprises the following steps: Step 100: responding to a change application request and receiving change information of tables and fields; Step 200: performing data standard and SQL specification check on the change information of tables and fields, and determining whether the check is passed, if so, going to step 300, if not, re-receiving the change information of tables and fields after using the data standard;

[0007] Step 300: Transmit the change application request information to the DBA node for change rationality review; Step 400: After the review is passed, execute the database change.

[0008] Preferably, the verification method of "performing data standard and SQL specification verification on change information of tables and fields" in step 200 is: calling the data standard verification service to perform data standard verification on data standard items of added, modified, and deleted change information of tables and fields, the data standard items include data domain, table field name, field encoding, data type, length, and value range; calling the SQL specification verification service to perform SQL specification verification on DDL statements of added, modified, and deleted change information of tables and fields.

[0009] Preferably, the "if not, re-receive the change information of the table and field after using the data standard" in step 200 includes: if the check fails, the list of data items that do not meet the data standard and the error description that does not meet the SQL specification are fed back to the change request initiating node, and the change request initiating node modifies and changes the data items according to the existing data standard, or modifies and changes the data items according to the updated data standard after the update standard is passed.

[0010] Preferably, the "change application request information" in step 300 includes: application information and change information, the application information includes the work order name, type, initiator, work order status, initiation time, instance, database, verification results, and audit log, and the change information includes the change execution script.

[0011] Preferably, the "database change" in step 400 includes database synchronization deployment and data model change. The database synchronization deployment is to select the instance and database to be synchronized, execute the change script, and synchronize the database metadata. The data model change is to execute the change model generation script in sequence to change the PDM data model.

[0012] The present invention also provides a change management system for a relational database, comprising: a response module, used to detect whether there is a change application request, and if so, activate a receiving module, a checking module, a transmission module and an execution module; a receiving module, used to receive change information of tables and fields; a checking module, used to perform data standard and SQL specification checking on the change information of tables and fields, and output the checking result; a transmission module, used to transmit the change application request information to a DBA node for a change rationality review; and an execution module, used to execute the database change after the DBA node has passed the review.

[0013] Preferably, the verification module verifies tables and fields by calling a data standard verification service to perform a data standard verification on data standard items of added, modified, and deleted change information of tables and fields, where the data standard items include data domain, table field name, field encoding, data type, length, and value range; and calling an SQL specification verification service to perform an SQL specification verification on DDL statements of added, modified, and deleted change information of tables and fields.

[0014] Preferably, when the verification module outputs a verification result indicating that the verification has failed, the list of data items that do not meet the data standards and the error descriptions that do not meet the SQL specifications are fed back to the change request initiating node, and the change request initiating node modifies the data items according to the existing data standards, or modifies the data items according to the updated data standards after the update standards are passed.

[0015] Preferably, the "change application request information" transmitted by the transmission module includes: application information and change information, the application information includes the work order name, type, initiator, work order status, initiation time, instance, database, verification results, and audit log, and the change information includes the change execution script.

[0016] Preferably, the database changes executed by the execution module include database synchronization deployment and data model changes. The database synchronization deployment is to select the instance and database to be synchronized, execute the change script, and synchronize the database metadata. The data model change is to execute the change model generation script in sequence to change the PDM data model.

[0017] A change management method for a relational database provided by the present invention has the following beneficial effects: by checking the data standards and SQL specifications of the tables and fields of the database changes, the standardization of the data management of the database is improved, and the appearance of non-standardized tables and fields in the database during the change process is avoided, which brings more stability and reliability to the database architecture; by setting the DBA node to review the change application request information, the DBA as a database manager can participate more deeply in the design process of the database and no longer completely rely on the developer's individual design; by converting the approved change support into an execution script, the automatic deployment of the configuration database and the linkage change of the data model are realized. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art are briefly introduced below. Obviously, the drawings in the following description are only embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on the provided drawings without creative work:

[0019] Figure 1 The figure is a flow chart of a relational database change management method provided by an embodiment of the present invention.

[0020] Figure 2 The figure is a schematic diagram of the module structure of a relational database change management system provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0021] In order to facilitate understanding of the present invention, the present invention will be described more fully below with reference to the relevant drawings. Typical embodiments of the present invention are given in the drawings. However, the present invention can be implemented in many different forms and is not limited to the embodiments described herein. On the contrary, the purpose of providing these embodiments is to make the disclosure of the present invention more thorough and comprehensive.

[0022] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by those skilled in the art of the present invention. The terms used in the specification of the present invention herein are only for the purpose of describing specific embodiments and are not intended to limit the present invention.

[0023] The general idea of ​​the present invention is: in view of the shortcomings of the prior art that the database change method and system in the prior art are limited to a single function and application, and cannot integrate multiple functions such as verification function, manual review, automatic database deployment, model change, etc., the present invention improves the standardization of database data management by performing data standard and SQL specification verification on the tables and fields of database changes, avoids the appearance of non-standardized tables and fields in the database during the change process, and brings more stability and reliability to the database architecture; by setting the DBA node to review the change application request information, the DBA, as the database manager, can participate more deeply in the database design process and no longer rely entirely on the developer's individual design; by converting the approved change support into an execution script, the automatic deployment of the configuration database and the linkage change of the data model are realized.

[0024] In order to better understand the above-mentioned technical scheme, the above-mentioned technical scheme will be described in detail below in conjunction with the accompanying drawings and specific implementation methods. It should be understood that the embodiments of the present invention and the specific features in the embodiments are detailed descriptions of the technical scheme of the present application, rather than limitations on the technical scheme of the present application. In the absence of conflict, the embodiments of the present invention and the technical features in the embodiments can be combined with each other.

[0025] Reference Figure 1 , Figure 1 FIG. 1 is a flow chart of a method for managing changes to a relational database provided by an embodiment of the present invention. In this embodiment, a method for managing changes to a relational database includes:

[0026] Step 100: Respond to the change application request and receive the change information of the table and field;

[0027] The database architecture developer needs to make changes to the database and initiate a database change application request. After receiving the application request, the system verifies the initiator's account password. If the verification is successful, it responds to the change application request and activates related functions such as verification, transmission, review, and execution of changes.

[0028] After the database change application request is approved, you need to enter the content information of the database that needs to be changed. The initiator can perform the change operation through the table editing interface of the system. The change operation includes adding new tables and fields, modifying the original tables and fields, and deleting the original tables and fields. After the change operation is completed, the system receives the change information of the tables and fields and transmits it to the verification node for verification.

[0029] Step 200: Check the table and field change information according to the data standard and SQL specification to determine whether the check is passed. If so, go to step 300. If not, re-receive the table and field change information after using the data standard.

[0030] When it reaches the verification node, the system's verification service component verifies the tables and fields changed by the initiator. If the verification passes, it proceeds to the next node. If not, it returns to the initiating node and re-enters the changed tables and fields.

[0031] As for how to check, specifically, the checking method of "performing data standard and SQL specification check on the change information of tables and fields" is: call the data standard check service, perform data standard check on the data items of the added, modified, and deleted change information of tables and fields. The data items include data domain, table field name, field code, data type, length, and value range; call the SQL specification check service, and perform SQL specification check on the DDL statements of the added, modified, and deleted change information of tables and fields.

[0032] After receiving the change information of tables and fields, the system calls the data standard verification service component and the SQL specification verification service component to compare and verify the change information of tables and fields with the standard template of the verification service component. During the development and construction of the database, all tables and fields have standard data items, and the changed data items need to meet the standards of the existing standard data items. Data items specifically include data domains, table field names, field codes, data types, lengths, and value ranges. Table field names are names that distinguish various table fields, which can be formed by a combination of letters and special symbols, such as LAST_UPDATED_DATE. The field description is what the object is about, described in Chinese characters, such as modification time. The data type is the type of the field, such as date data type, string, etc. The length is the length of the field data, such as 36 bits, etc. Each data item to be changed is checked and compared with each item in the existing standard data item template. For example, if the changed data type is a date data type, and the standard data type is a string, etc., the output verification result is that the data type does not match. By converting the change information of tables and fields into SQL statements, the SQL standard verification service component is called to perform SQL standard verification on the DDL statements of the change information, mainly for database objects such as tables, fields, primary and foreign keys, and indexes. If the primary key is not set for the first field of the table, the output verification result is that it does not meet the SQL standard.

[0033] As for what to do after the check fails, specifically, "If not, re-receive the change information of the table and field after using the data standard" includes: if the check fails, the list of data items that do not meet the data standard and the error description that does not meet the SQL specification are fed back to the change request initiating node, and the change request initiating node modifies the data items according to the existing data standard, or modifies the data items according to the updated data standard after the update standard is passed.

[0034] If the check fails, a list of data items that do not meet the data standard and error descriptions that do not meet the SQL specification will also be output. For example, if the data type does not meet the standard, it means that the data type in the change information does not meet the usage standard, and the change requester will know that the data type needs to be modified. For example, if the error description of the SQL specification check is that the first field of the table does not have a primary key set, the change requester will know that the first field of the table needs to have a primary key set. If the check fails, the change requester can select the existing data item standard to modify the change information, or update the database standard. After the update, use the latest data item standard to modify the change.

[0035] Step 300: Send the change application request information to the DBA node for change rationality review;

[0036] After the review is passed, the system will transmit the change application request information to the DBA node for a review of the rationality of the change. As a database manager, the DBA has been operating and maintaining the database and is familiar with all aspects of database applications. He or she has an intuitive judgment on whether the database changes can lead to further use or cause database bugs. He or she can judge the rationality of the database changes based on various conditions in the actual application of the database.

[0037] Regarding the contents of the DBA's review of database changes, specifically, "change application request information" includes: application information and change information. The application information includes the work order name, type, initiator, work order status, initiation time, instance, database, verification results, and audit log. The change information includes the change execution script. First, you need to review the information of the change request itself, such as the work order name, work order name, type, initiator, work order status, initiation time, instance, database, verification results, and audit log, etc. For example, whether the change is a new addition or modification, whether the database allows the addition of new tables and fields in the near future, such as whether the initiator is a member of the data architecture group, whether he has the qualifications to initiate the change, such as the initiation time, whether it affects the allowed maintenance of the database in the near future, etc., and then conduct a specific review of the change information. You need to check the specific execution script of the change, whether the script corresponds to the content of the change request, whether the change script includes changes to all change data items, etc. After confirmation, you can approve this change request.

[0038] Step 400: After the review is passed, the database change is executed.

[0039] After the DBA node passes the review, it goes to the change execution node to execute the change request. Specifically, "database changes" include database synchronization deployment and data model changes. Database synchronization deployment is to select the instance and database that need to be synchronized, execute the change script, and synchronize the database metadata. The data model change is to execute the change model generation script in sequence to change the PDM data model. For database synchronization deployment, the SQL script that has passed the review will be merged into a branch after several iterations. After merging, it will be saved to the code base through version control. The instance and database that need to execute the change script of this version will be selected, and the change script will be executed. The metadata in the database will be changed synchronously, realizing data synchronization deployment. Find the instance and database that need to be changed by name, confirm whether the database type is consistent, obtain other information about the database, connect to the data through the database IP address and database port, and verify the legitimacy of the change script by verifying the database username and database password, that is, run the change script on the database environment, execute the change script, and change the physical table of the database. For data model changes, when the database metadata is changed, a script for changing the model is reversely generated and saved in the data model library. In the data model library, the script for changing the model is executed in the order of version numbers to change the data model.

[0040] Based on the above method, by checking the data standards and SQL specifications of the tables and fields of the database changes, the standardization of database data management is improved, and non-standardized tables and fields are avoided during the database change process, which brings more stability and reliability to the database architecture; by setting up the DBA node to review the change application request information, the DBA, as a database manager, can participate more deeply in the database design process and no longer rely entirely on the developer's individual design; by converting the approved change support into an execution script, the automatic deployment of the configuration database and the linkage change of the data model are realized.

[0041] Accordingly, the present invention also provides a relational database change management system, referring to Figure 2 , Figure 2The figure shows a schematic diagram of the system module structure of a relational database change management system provided by an embodiment of the present invention. The system implements database verification, review, automatic database deployment, and data model linkage change by performing change management on the database through the method described above. The system includes: a response module, which is used to detect whether there is a change application request, and if so, activates a receiving module, a checking module, a transmitting module, and an executing module; a receiving module, which is used to receive change information of tables and fields; a checking module, which is used to perform data standard and SQL specification verification on the change information of tables and fields, and output the verification result; a transmitting module, which is used to transmit the change application request information to the DBA node for change rationality review; and an executing module, which is used to execute database changes after the DBA node has passed the review.

[0042] Preferably, the verification module verifies tables and fields by calling a data standard verification service to perform a data standard verification on data standard items of added, modified, and deleted change information of tables and fields, where the data standard items include data domain, table field name, field encoding, data type, length, and value range; and calling an SQL specification verification service to perform an SQL specification verification on DDL statements of added, modified, and deleted change information of tables and fields.

[0043] Preferably, when the verification module outputs a verification result indicating that the verification has failed, the list of data items that do not meet the data standards and the error descriptions that do not meet the SQL specifications are fed back to the change request initiating node, and the change request initiating node modifies the data items according to the existing data standards, or modifies the data items according to the updated data standards after the update standards are passed.

[0044] Preferably, the "change application request information" transmitted by the transmission module includes: application information and change information, the application information includes the work order name, type, initiator, work order status, initiation time, instance, database, verification results, and audit log, and the change information includes the change execution script.

[0045] Preferably, the database changes executed by the execution module include database synchronization deployment and data model changes. Database synchronization deployment is to select the instance and database to be synchronized, execute the change script, and synchronize the metadata of the database. The data model change is to execute the change model generation script in sequence to change the PDM data model. PDM is the physical model of the data model.

[0046] In the description provided herein, a large number of specific details are described. However, it is understood that embodiments of the present invention can be practiced without these specific details. In some instances, well-known methods, structures and techniques are not shown in detail so as not to obscure the understanding of this description.

[0047] Similarly, it should be understood that in order to streamline the present disclosure and aid in understanding one or more of the various inventive aspects, in the above description of exemplary embodiments of the present invention, various features of the present invention are sometimes grouped together into a single embodiment, figure, or description thereof. However, this disclosed method should not be interpreted as reflecting the intention that the claimed invention requires more features than those explicitly recited in each claim. More specifically, as reflected in the claims below, inventive aspects lie in less than all the features of the individual embodiments disclosed above. Therefore, the claims that follow the specific embodiment are hereby expressly incorporated into the specific embodiment, with each claim itself serving as a separate embodiment of the present invention.

[0048] Those skilled in the art will appreciate that the modules in the devices in the embodiments may be adaptively changed and arranged in one or more devices different from the embodiments. The modules or units or components in the embodiments may be combined into one module or unit or component, and in addition they may be divided into a plurality of submodules or subunits or subcomponents. Except that at least some of such features and / or processes or units are mutually exclusive, all features disclosed in this specification (including the accompanying claims, abstracts and drawings) and all processes or units of any method or device disclosed in this manner may be combined in any combination. Unless otherwise expressly stated, each feature disclosed in this specification (including the accompanying claims, abstracts and drawings) may be replaced by an alternative feature providing the same, equivalent or similar purpose.

[0049] In addition, those skilled in the art will appreciate that, although some embodiments herein include certain features included in other embodiments but not other features, the combination of features of different embodiments is meant to be within the scope of the present invention and form different embodiments. For example, in the claims below, any one of the claimed embodiments may be used in any combination.

[0050] The various component embodiments of the present invention can be implemented in hardware, or in software modules running on one or more processors, or in a combination thereof. It should be understood by those skilled in the art that a microprocessor or digital signal processor (DSP) can be used in practice to implement some or all of the functions of some or all of the components in the embodiments of the present invention. The present invention can also be implemented as a device or system program (e.g., computer program and computer program product) for executing part or all of the methods described herein. Such a program implementing the present invention can be stored on a computer-readable medium, or can have the form of one or more signals. Such a signal can be downloaded from an Internet website, or provided on a carrier signal, or provided in any other form.

[0051] It should be noted that the above embodiments illustrate the present invention rather than limit it, and that those skilled in the art may devise alternative embodiments without departing from the scope of the appended claims. In the claims, any reference symbol between brackets should not be constructed as a limitation to the claims. The word "comprising" does not exclude the presence of elements or steps not listed in the claims. The word "one" or "an" preceding an element does not exclude the presence of a plurality of such elements. The present invention may be implemented by means of hardware comprising a number of different elements and by means of a suitably programmed computer. In a unit claim listing a number of systems, several of these systems may be embodied by the same hardware item. The use of the words first, second, and third, etc., does not indicate any order. These words may be interpreted as names.

Claims

1. A change management method for a relational database, characterized in that: The following steps are involved: Step 100: Respond to the change application request and receive the change information of the table and field; Step 200: Check the table and field change information according to the data standard and SQL specification to determine whether the check is passed. If so, go to step 300. If not, re-receive the table and field change information after using the data standard. Step 300: Send the change application request information to the DBA node for change rationality review; Step 400: After the review is passed, the database change is executed.

2. A method for managing changes to a relational database as claimed in claim 1, characterized in that: The verification method of "performing data standard and SQL specification verification on the change information of tables and fields" in step 200 is: calling the data standard verification service to perform data standard verification on the data standard items of the added, modified, and deleted change information of tables and fields, the data standard items include data domain, table field name, field code, data type, length, and value range; calling the SQL specification verification service to perform SQL specification verification on the DDL statements of the added, modified, and deleted change information of tables and fields.

3. A method for managing changes to a relational database as claimed in claim 2, characterized in that: "If not, re-receive the change information of tables and fields after using the data standard" in step 200 includes: if the check fails, the list of data items that do not meet the data standard and the error description that does not meet the SQL specification are fed back to the change request initiating node, and the change request initiating node modifies the data items according to the existing data standard, or modifies the data items according to the updated data standard after the update standard is passed.

4. A method for managing changes to a relational database as claimed in claim 3, characterized in that: The "change application request information" in step 300 includes: application information and change information. The application information includes the work order name, type, initiator, work order status, initiation time, instance, database, verification result, and audit log. The change information includes the change execution script.

5. A relational database change management method as claimed in claim 4, characterized in that: The "database change" in step 400 includes database synchronization deployment and data model change. The database synchronization deployment is to select the instance and database to be synchronized, execute the change script, and synchronize the database metadata. The data model change is to execute the change model generation script in sequence to change the PDM data model.

6. A change management system for a relational database, characterized in that: include: The response module is used to detect whether there is a change application request, and if so, activate the receiving module, the checking module, the transmitting module and the executing module; The receiving module is used to receive the change information of tables and fields; The verification module is used to verify the data standards and SQL specifications of the table and field change information and output the verification results; The transmission module is used to transmit the change application request information to the DBA node for change rationality review; The execution module is used to execute database changes after the DBA node has approved them.

7. A relational database change management system as claimed in claim 6, characterized in that: The verification module verifies tables and fields by calling the data standard verification service to perform data standard verification on data standard items of added, modified, and deleted change information of tables and fields. The data standard items include data domain, table field name, field code, data type, length, and value range. The SQL specification verification service is called to perform SQL specification verification on DDL statements of added, modified, and deleted change information of tables and fields.

8. A relational database change management system as claimed in claim 7, characterized in that: When the verification module outputs a verification result indicating that the verification has failed, the list of data items that do not meet the data standards and the error descriptions that do not meet the SQL specifications are fed back to the change request initiating node. The change request initiating node modifies the data items according to the existing data standards, or modifies the data items according to the updated data standards after the update standards are passed.

9. A relational database change management system as claimed in claim 8, characterized in that: The "change application request information" transmitted by the transmission module includes: application information and change information. The application information includes the work order name, type, initiator, work order status, initiation time, instance, database, verification result, and audit log. The change information includes the change execution script.

10. A relational database change management system as claimed in claim 9, characterized in that: The database changes executed by the execution module include database synchronization deployment and data model changes. Database synchronization deployment is to select the instance and database that need to be synchronized, execute the change script, and synchronize the database metadata. The data model change is to execute the change model generation script in sequence to change the PDM data model.

Images