A change management method for database automation build and deployment

Abstract

The application relates to a database automatic construction and deployment change management method, which comprises the following steps: S1, a user designs a database table structure through a data modeling tool, and each system always maintains a table structure baseline of the latest production online in the data modeling tool; S2, a code warehouse carries out format checking on database change information submitted by the user, and submits the database change information to the code warehouse after the format checking; S3, according to a code warehouse directory structure and pipeline parameters configured by the user, database scripts are constructed and packaged, and the database DDL scripts are checked in the packaging process to verify whether the DDL scripts are all submitted through the data modeling tool; S4, review of the change SQL, scanning of a constructed release package, identification of high-risk sentence operations, manual confirmation and subsequent operation; and S5, dispatching of executed SQL sentences. Compared with the prior art, the application can realize automatic management and control of SQL scripts and online product packages and function requirement tracing.

Description

Technical Field

[0001] This invention relates to the field of database change management technology, and in particular to a change management method for automated database construction and deployment. Background Technology

[0002] The rise of digitalization has demanded shorter software delivery cycles to meet rapid needs. Databases, as containers holding the most valuable and critically protected business data, are deployed differently than other software components or compiled code. A database is not a collection of files and cannot be directly copied from the development environment to the testing environment and then to the production environment.

[0003] Existing database management statement change technologies mostly suffer from problems such as delayed review timelines, inability to detect issues in advance, and risks associated with production deployment. Specifically, these include:

[0004] 1. Creating database object scripts and storing them in a traditional version control system, with the script being selected from the version control system for execution, involves manual step-by-step execution. Because the version control system and the database are two separate systems, there is no connection between the scripts in the version control system and the database objects. Database code writing and testing are all done on the database side, which easily leads to the following problems: 1) Code overwriting in the database is common; before running code in the database, it needs to be retrieved from the version control system, which raises concerns about incorrect version selection; updates outside the workflow are often overlooked; 2) Script writing is prone to human error and syntax errors; 3) Scripts are difficult to test as a whole. One person updates one script, another updates another, and if the scripts need to run in a specific order, running them in any order often results in errors due to incorrect dependencies; 4) If a script represents the entire update, it can handle dependencies, but handling project-wide changes is much more difficult because the file is long, and how to continue execution if an error occurs in the middle; 5) Unless highly experienced, these scripts may lack information about changes that occur in the target environment during the time from writing to execution; they may overwrite hot patches in the production environment or parallel operations by another team.

[0005] 2. Use comparison tools to handle different environments and generate migration code. This approach is called the "compare and synchronize" method. Its principle is to examine database objects in two different database environments, compare them, and if differences are found, automatically generate a target script that mimics the changes made to the source object. However, this method has the following problems: 1) The "compare and synchronize" tool is unaware of changes that occurred before it, or any changes that occurred in the target environment; there is no version control, no change information, only differences at a specific point in time; 2) Database scripts can be saved in traditional version control solutions, while database script deployment is suitable for the "compare and synchronize" tool, making coordination between the two difficult; 3) Manually checking and confirming changes must be part of the process, requiring a large amount of manual confirmation; 4) Code merging between teams cannot clarify the source.

[0006] Therefore, there is an urgent need to design a change management method for automated database construction and deployment to achieve effective control over SQL script management and online artifact packages, as well as traceability of functional requirements. Summary of the Invention

[0007] The purpose of this invention is to overcome the shortcomings of the existing technology by providing a change management method for automated database construction and deployment, which can realize automated control of SQL script management and online artifact packages, as well as traceability of functional requirements.

[0008] The objective of this invention can be achieved through the following technical solutions:

[0009] This invention provides a change management method for automated database construction and deployment, which includes the following steps:

[0010] Step S1: Users design the database table structure using data modeling tools. Each system always maintains a baseline of the table structure from the most recent production deployment in the data modeling tool.

[0011] Step S2: Submit the database changes to the code repository. The code repository will validate the user's submission information in the correct format. Once the validation is successful, the changes will be submitted to the code repository.

[0012] Step S3: Based on the directory structure of the code repository and the parameters of the pipeline configured by the user, build and package the database scripts. During the packaging process, validate the database DDL scripts to verify whether they are all submitted through the data modeling tool. If the validation fails, the build and packaging will also fail.

[0013] Step S4: Review the SQL changes, scan the built release package, identify high-risk statement operations, and proceed with subsequent operations only after manual confirmation;

[0014] Step S5: Schedule the execution of the SQL statement.

[0015] Preferably, in step S1, if the user updates the table structure, the relevant SQL change statement is generated by comparing it with the most recent production baseline; if the user's database change involves data update, modification, or deletion operations, the user needs to write the SQL statement locally.

[0016] Preferably, step S1 includes the following sub-steps:

[0017] Step S1.1: The data modeling system needs to maintain a data dictionary. This dictionary defines the meaning of each field in the table structure and the range of field values, so that users can select them when setting database table attributes.

[0018] Step S1.2: The process of writing database change scripts by users is divided into two parts: one part is the DDL script related to the table structure, which the user completes directly in the modeling tool. When the user selects database attributes during the modeling process, they must select them from the data dictionary; the other part is the DML script for database changes, which the user completes in the local editor.

[0019] Step S1.3: After the user completes the writing, a new baseline is created for the corresponding table in the modeling tool; the writing content includes data table structure information, table type information, stored procedure information, basic data table information, and update data information; the update data includes data table structure data, table type data, and stored procedure data;

[0020] Step S1.4: The user compares the most recent production baseline with the current newly created baseline to generate the SQL file for production database changes.

[0021] Preferably, step S2 specifically includes the following steps:

[0022] Step S2.1: Obtain code repository information: If the code repository information is not obtained, the authorized user can create a new code repository, plan the directory structure, and then bind it to the corresponding code project on the data modeling platform; the repository information mentioned here includes the address of the code repository and the directory information that can be submitted.

[0023] Step S2.2: Obtain Submission Information: Based on the corresponding task of modifying the table structure, the user writes the submission information according to the format requirements and generates the corresponding submission information through the modeling tool; the generated information includes the database identifier and the name of the table structure to be modified.

[0024] Step S2.3: The code repository performs relevant permission verification, compliance verification of the submission information, and compliance verification of the submission content for each user's submission;

[0025] Step S2.4: The user writes the SQL script related to database data operations locally, selects the corresponding directory, obtains the commit information from the modeling tool, and commits it to the code repository.

[0026] Preferably, the verification method in step S2.3 is as follows:

[0027] Based on the format of the commit messages generated by the modeling tool, add hooks to the corresponding code repository to verify whether the user's commit messages conform to the commit message format agreed upon with the modeling tool.

[0028] Preferably, the parameters of the pipeline configured by the user in step S3 include:

[0029] Step S3.1: User configures build pipeline parameters; wherein, the pipeline parameters include: pipeline execution mode, build and package directory, and whether to enable quality gate;

[0030] Step S3.2: Check the compliance of user scripts according to the configured code repository directory; wherein, the script compliance includes: the automatically generated scripts cannot contain scripts uploaded by the user, and the password in the configuration file cannot be in plaintext;

[0031] Step S3.3: Generate a file list for the finished product package and place it into the finished product package; the file list includes the MD5 hash of the file and the file name;

[0032] Step S3.4: Check if there is a database configuration file in the file list, and generate a database information file based on the configuration file.

[0033] Preferably, step S4, reviewing the modified SQL, specifically includes the following steps:

[0034] Step S4.1: Review the database release rules, scan all SQL statements in the release package, and determine whether they comply with the release rules. If they do not comply, the subsequent steps cannot be executed.

[0035] Step S4.2: Database deployment file orchestration: Obtain the SQL files in the deployment package, select the SQL files to be executed, then reorder the SQL files to be executed, and set whether to continue execution after the SQL file fails;

[0036] Step S4.3: Identify high-risk statements in the database based on the generated file list;

[0037] Step S4.4: Write the identified statements into a new file. The file name is the original file name plus the suffix "Risk". High-risk statements are written into the scheduling platform and associated with the packet information for easy viewing by users.

[0038] Preferably, step S4.3 specifically involves: first, reading the contents of the SQL file, then reading the configured high-risk statement rules, and performing rule matching on the SQL statements in the file contents; then, generating a new database file execution release package; wherein, the high-risk statement rules are SQL statements containing DELETE, DROP, and TRUNCATE.

[0039] Preferably, step S5 specifically involves: the deployment of the scheduled SQL statements requires passing through three environments, including a development environment, a verification environment, and a production environment; wherein, the development environment supports the execution of a single SQL file as well as the execution of SQL files in the entire deployment package; the verification environment performs full execution of the SQL statements in the deployment package; before execution in the production environment, a gate is set to determine whether all scripts in the current deployment package have been executed in the verification environment, and if they have been executed, then production deployment is performed.

[0040] Preferably, step S5 specifically includes the following sub-steps:

[0041] Step S5.1: Based on the production line information or the product information selected by the user, download the product package and decompress it;

[0042] Step S5.2: Obtain the orchestration information corresponding to the artifact package; wherein, the orchestration information includes a list of files to be executed in the release package and the execution order of the files;

[0043] Step S5.3: The test environment database auditing platform receives the SQL file package from step S5.1, calculates the MD5 value of the SQL file package, and compares the MD5 value with the MD5 value in the release package. If the two MD5 values ​​are the same, it means that the SQL file package is consistent.

[0044] Step S5.4: Determine whether the current release environment is a development and verification environment. If it is a development and verification environment, execute the SQL in the release package according to the orchestration information of the release package, and record the execution result in the file.

[0045] Step S5.5: Determine whether the current execution environment is a test and verification environment. If it is a test and verification environment, execute the release package in its entirety and record the execution results.

[0046] Step S5.6: Determine whether the current environment is a production environment. If it is a production environment, determine whether all the files in the release package have been executed in the test and verification environment. If they have all been executed, then schedule the production environment.

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

[0048] 1) The database release package is verified in the test environment and contains information related to development. This solves the problem that existing technologies may generate database scripts without verification or may only have artifact packages without artifact package association information. It realizes automated lifecycle management of database scripts.

[0049] 2) The database script of this invention is developed, tested, and released in a single package, ensuring consistency between the test release package and the production release package, thus guaranteeing data consistency;

[0050] 3) Through the code review mechanism, database change risks are identified in advance and optimized. Attached Figure Description

[0051] Figure 1 This is a flowchart of the method of the present invention;

[0052] Figure 2 This is a diagram illustrating the process of generating database table modification statements.

[0053] Figure 3 This is a diagram illustrating the SQL file review process.

[0054] Figure 4 A diagram illustrating the process of scheduling and executing SQL statements;

[0055] Figure 5 This is a flowchart of the database scheduling engine. Detailed Implementation

[0056] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0057] Example

[0058] like Figure 1 As shown in the figure, this embodiment provides a change management method for automated database construction and deployment, which includes the following steps:

[0059] Step S1: Users design the database table structure using data modeling tools. Each system maintains a baseline of the table structure from the most recent production deployment in the data modeling tool. If a user updates the table structure, relevant SQL change statements are generated by comparing it with the most recent production baseline. If the user's database changes involve updating, modifying, or deleting data, the user needs to write the SQL statements locally.

[0060] like Figure 2 As shown, the specific steps include the following:

[0061] Step S1.1: The data modeling system needs to maintain a data dictionary. This dictionary defines the meaning of each field in the table structure and the range of field values, so that users can select them when setting database table attributes.

[0062] Step S1.2: The process of writing database change scripts by users is divided into two parts: one part is the DDL script related to the table structure, which the user completes directly in the modeling tool. When the user selects database attributes during the modeling process, they must select them from the data dictionary; the other part is the DML script for database changes, which the user completes in the local editor.

[0063] Step S1.3: After the user completes the writing, a new baseline is created for the corresponding table in the modeling tool; the writing content includes data table structure information, table type information, stored procedure information, basic data table information, and update data information; the update data includes data table structure data, table type data, and stored procedure data;

[0064] Step S1.4: The user compares the most recent production baseline with the current newly created baseline to generate the SQL file for production database changes.

[0065] Step S2: Submit the database changes to the code repository. The code repository validates the user's submission information in a specific format. Once the validation is successful, the changes are submitted to the code repository. This includes the following sub-steps:

[0066] Step S2.1: Obtain code repository information: If the code repository information is not obtained, the authorized user can create a new code repository, plan the directory structure, and then bind it to the corresponding code project on the data modeling platform; the repository information mentioned here includes the address of the code repository and the directory information that can be submitted.

[0067] Step S2.2: Obtain Submission Information: Based on the corresponding task of modifying the table structure, the user writes the submission information according to the format requirements and generates the corresponding submission information through the modeling tool; the generated information includes the database identifier and the name of the table structure to be modified.

[0068] Step S2.3: The code repository performs relevant permission verification, compliance verification of the submission information, and compliance verification of the submission content for each user's submission; the specific verification methods are as follows:

[0069] Based on the format of the commit information generated by the modeling tool, add hooks to the corresponding code repository to verify whether the user's commit information conforms to the commit information format agreed upon with the modeling tool;

[0070] Step S2.4: The user writes the SQL script related to database data operations locally, selects the corresponding directory, obtains the commit information from the modeling tool, and commits it to the code repository.

[0071] Step S3: Based on the directory structure of the code repository and the parameters of the pipeline configured by the user, build and package the database scripts. During the packaging process, validate the database DDL scripts to verify whether they are all submitted through the data modeling tool. If the validation fails, the build and packaging will also fail.

[0072] The parameters of the pipeline configured by the user include:

[0073] Step S3.1: User configures build pipeline parameters; wherein, the pipeline parameters include: pipeline execution mode, build and package directory, and whether to enable quality gate;

[0074] Step S3.2: Check the compliance of user scripts according to the configured code repository directory; wherein, the script compliance includes: the automatically generated scripts cannot contain scripts uploaded by the user, and the password in the configuration file cannot be in plaintext;

[0075] Step S3.3: Generate a file list for the finished product package and place it into the finished product package; the file list includes the MD5 hash of the file and the file name;

[0076] Step S3.4: Check if there is a database configuration file in the file list, and generate a database information file based on the configuration file.

[0077] Step S4: Review the modified SQL statements, scan the built release package, identify high-risk statements, and require manual confirmation before proceeding with further operations; for example, Figure 3 As shown, the review of SQL changes specifically includes the following steps:

[0078] Step S4.1: Review the database release rules, scan all SQL statements in the release package, and determine whether they comply with the release rules. If they do not comply, the subsequent steps cannot be executed.

[0079] Step S4.2: Database deployment file orchestration: Obtain the SQL files in the deployment package, select the SQL files to be executed, then reorder the SQL files to be executed, and set whether to continue execution after the SQL file fails;

[0080] Step S4.3: Based on the generated file list, identify high-risk statements in the database; first, read the SQL file content, then read the configured high-risk statement rules, and match the SQL statements in the file content according to the rules; then, generate a new database file execution release package; wherein, the high-risk statement rule is that the SQL statement contains DELETE, DROP, TRUNCAT;

[0081] Step S4.4: Write the identified statements into a new file. The file name is the original file name plus the suffix "Risk". High-risk statements are written into the scheduling platform and associated with the packet information for easy viewing by users.

[0082] Step S5: Schedule the execution of SQL statements. The deployment of scheduled SQL statements requires passing through three environments: development, verification, and production. The development environment supports the execution of individual SQL files as well as the entire SQL file in the deployment package. The verification environment performs a full execution of all SQL statements in the deployment package. Before execution in the production environment, a gatekeeper is set to determine whether all scripts in the current deployment package have been executed in the verification environment. If they have, production deployment proceeds. Specifically, this includes the following sub-steps:

[0083] Step S5.1: Based on the production line information or the product information selected by the user, download the product package and decompress it;

[0084] Step S5.2: Obtain the orchestration information corresponding to the artifact package; wherein, the orchestration information includes a list of files to be executed in the release package and the execution order of the files;

[0085] Step S5.3: The test environment database auditing platform receives the SQL file package from step S5.1, calculates the MD5 value of the SQL file package, and compares the MD5 value with the MD5 value in the release package. If the two MD5 values ​​are the same, it means that the SQL file package is consistent.

[0086] Step S5.4: Determine whether the current release environment is a development and verification environment. If it is a development and verification environment, execute the SQL in the release package according to the orchestration information of the release package, and record the execution result in the file.

[0087] Step S5.5: Determine whether the current execution environment is a test and verification environment. If it is a test and verification environment, execute the release package in its entirety and record the execution results.

[0088] Step S5.6: Determine whether the current environment is a production environment. If it is a production environment, determine whether all the files in the release package have been executed in the test and verification environment. If they have all been executed, then schedule the production environment.

[0089] In summary, this invention manages database script DDL statements from the source using a specific database modeling client; then, it combines this with a code repository for version management of database change scripts; script management within the code repository follows a specific directory structure; and finally, the code repository serves as the source of the CI / CD pipeline, automatically triggering the CI / CD pipeline during the SQL file submission phase to package and deploy the changed scripts. During deployment, high-risk statements are first scanned, and a high-risk statement scan report is provided for user review. After user confirmation of the high-risk statements, the user selects the executable file for automated deployment.

[0090] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any person skilled in the art can easily conceive of various equivalent modifications or substitutions within the technical scope disclosed in the present invention, and these modifications or substitutions should all be covered within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. A change management method for automated database construction and deployment, characterized in that, The method includes the following steps: Step S1: Users design the database table structure using data modeling tools. Each system always maintains a baseline of the table structure from the most recent production deployment in the data modeling tool. If a user updates the table structure, the relevant SQL change statements are generated by comparing it with the table structure baseline of the most recent production deployment; if the user's database changes involve data updates, modifications, or deletions, the user needs to write the SQL statements locally. Includes the following sub-steps: Step S1.1: The data modeling system needs to maintain a data dictionary. This dictionary defines the meaning of each field in the table structure and the range of field values, so that users can select them when setting database table attributes. Step S1.2: The process of writing database change scripts by users is divided into two parts: one part is the DDL script related to the table structure, which the user completes directly in the modeling tool. When the user selects database attributes during the modeling process, they must select them from the data dictionary; the other part is the DML script for database changes, which the user completes in the local editor. Step S1.3: After the user completes the writing, a new baseline is created for the corresponding table in the modeling tool; the writing content includes data table structure information, table type information, stored procedure information, basic data table information, and update data information; the update data includes data table structure data, table type data, and stored procedure data; Step S1.4: The user compares the table structure baseline of the most recent production deployment with the newly created baseline to generate the SQL file for production database changes; Step S2: Submit the database changes to the code repository. The code repository will validate the user's submission information in the correct format. Once the validation is successful, the changes will be submitted to the code repository. Step S3: Based on the directory structure of the code repository and the parameters of the pipeline configured by the user, build and package the database scripts. During the packaging process, validate the database DDL scripts to verify whether they are all submitted through the data modeling tool. If the validation fails, the build and packaging will also fail. Step S4: Review the SQL changes, scan the built release package, identify high-risk statement operations, and proceed with subsequent operations only after manual confirmation; Step S5: Schedule the execution of the SQL statement.

2. The change management method for automated database construction and deployment according to claim 1, characterized in that, Step S2 specifically includes the following steps: Step S2.1: Obtain code repository information: If the code repository information is not obtained, the authorized user can create a new code repository, plan the directory structure, and then bind it to the corresponding code project on the data modeling platform; the repository information mentioned here includes the address of the code repository and the directory information that can be submitted; Step S2.2: Obtain Submission Information: Based on the corresponding task of modifying the table structure, the user writes the submission information according to the format requirements and generates the corresponding submission information through the modeling tool; the generated information includes the database identifier and the name of the table structure to be modified. Step S2.3: The code repository performs relevant permission verification, compliance verification of the submission information, and compliance verification of the submission content for each user's submission; Step S2.4: The user writes the SQL script related to database data operations locally, selects the corresponding directory, obtains the commit information from the modeling tool, and commits it to the code repository.

3. The change management method for automated database construction and deployment according to claim 2, characterized in that, Step S2.3 specifically includes: Based on the format of the commit messages generated by the modeling tool, add hooks to the corresponding code repository to verify whether the user's commit messages conform to the commit message format agreed upon with the modeling tool.

4. The change management method for automated database construction and deployment according to claim 1, characterized in that, Step S3 specifically includes: Step S3.1: User configures build pipeline parameters; wherein, the pipeline parameters include: pipeline execution mode, build and package directory, and whether to enable quality gate; Step S3.2: Check the compliance of user scripts according to the configured code repository directory; wherein, the script compliance includes: the automatically generated scripts cannot contain scripts uploaded by the user, and the password in the configuration file cannot be in plaintext; Step S3.3: Generate a list of files for the release package and include it in the release package; the list of files includes the MD5 value and file name of the SQL file, as well as the database configuration file; Step S3.4: Check if there is a database configuration file in the file list, and generate a database information file based on the database configuration file.

5. The change management method for automated database construction and deployment according to claim 1, characterized in that, Step S4 specifically includes the following steps: Step S4.1: Review the database release rules, scan all SQL statements in the release package, and determine whether they comply with the release rules. If they do not comply, the subsequent steps cannot be executed. Step S4.2: Database deployment file orchestration: Obtain the SQL files in the deployment package, select the SQL files to be executed, then reorder the SQL files to be executed, and set whether to continue execution after the SQL file fails; Step S4.3: Identify high-risk statements in the database based on the generated file list; Step S4.4: Write the identified statements into a new file. The file name is the original file name plus the suffix "Risk". High-risk statements are written into the scheduling platform and associated with the packet information for easy viewing by users.

6. The change management method for automated database construction and deployment according to claim 5, characterized in that, Step S4.3 specifically involves: first, reading the contents of the SQL file, then reading the configured high-risk statement rules, and performing rule matching on the SQL statements in the file contents; then, generating a new database file to execute the deployment package; wherein, the high-risk statement rules are SQL statements containing DELETE, DROP, and TRUNCATE.

7. The change management method for automated database construction and deployment according to claim 1, characterized in that, Step S5 specifically involves: the deployment of SQL statements to be executed requires passing through three environments, including a development environment, a verification environment, and a production environment; in the development environment, the deployment supports the execution of a single SQL file as well as the execution of SQL files in the entire deployment package; in the verification environment, the SQL statements in the deployment package are executed in full; before execution in the production environment, a gate is set to determine whether all scripts in the current deployment package have been executed in the verification environment. If they have been executed, then production deployment is performed.

8. The change management method for automated database construction and deployment according to claim 7, characterized in that, Step S5 specifically includes the following sub-steps: Step S5.1: Based on the production line information or the product information selected by the user, download the release package and decompress it; Step S5.2: Obtain the orchestration information corresponding to the release package; wherein, the orchestration information includes a list of files to be executed in the release package and the execution order of the files; Step S5.3: The test environment database auditing platform receives the SQL file package from step S5.1, calculates the MD5 value of the SQL file, and compares the MD5 value with the MD5 value in the release package. If the two MD5 values ​​are the same, it means that the SQL file package is consistent. Step S5.4: Determine whether the current execution environment is a development environment. If it is a development environment, execute the SQL in the release package according to the release package orchestration information and record the execution result in the file. Step S5.5: Determine whether the current execution environment is a verification environment. If it is a verification environment, execute the entire release package and record the execution results. Step S5.6: Determine whether the current execution environment is a production environment. If it is a production environment, determine whether all files in the release package have been executed in the verification environment. If all files have been executed, then schedule the production environment.

Images