SQL script-based database deployment method and device, equipment and medium

Abstract

The invention relates to a database deployment method and device based on an SQL script, equipment and a medium. The method comprises the following steps: deploying a test environment database through an SQL script; metadata information of SQL change is extracted based on the SQL script; generating a pre-deployment database structure based on baseline information of the production environment database through the metadata information; performing structural difference comparison on the pre-deployed database structure and the test environment database; modifying the SQL script according to the comparison difference; and deploying the production environment database based on the modified SQL script. The metadata of the SQL script and the metadata of the production environment database are obtained through analysis and combined to achieve pre-simulation of the structure of the production environment database, the production environment database is compared with a test environment under the condition that production environment deployment is not carried out, inconsistent changes among different environments are reduced, and the test efficiency is improved. And the accuracy and the reliability of the change are improved.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a database deployment method, device, equipment and medium based on SQL scripts. Background Art

[0002] With the development of software development and operation and maintenance, rapid iteration and continuous deployment of applications have become the norm. In this process, the database, as the core of data storage, may change its structure frequently. However, if these changes are not handled properly, it is easy to cause inconsistent database structure in the production environment, resulting in performance degradation and even service crashes.

[0003] In traditional database management practices, database changes usually involve the gradual deployment and execution of SQL scripts in different environments (such as development, testing, and production). This process is highly dependent on manual operations, which is not only inefficient but also prone to errors. Especially when deploying across environments, it is easy to cause problems due to environmental differences, script omissions, or misoperation. Among them, the most likely problem is the difference between the same database corresponding to different environments.

[0004] Currently, no effective solution has been proposed to the above problems. Summary of the invention

[0005] Based on this, it is necessary to provide a database deployment method, device, equipment and medium based on SQL scripts to address the above technical problems. The present invention simulates these changes in advance before the actual SQL deployment, locates and identifies potential structural differences, and makes adjustments before SQL deployment to reduce the risk of production accidents caused by structural inconsistencies.

[0006] According to a first aspect of the present invention, there is provided a database deployment method based on an SQL script, comprising:

[0007] Test deployment steps, deploy the test environment database through SQL scripts;

[0008] Parsing step, extracting metadata information of SQL changes based on SQL scripts;

[0009] A simulation step, generating a pre-deployment database structure obtained after pre-deployment based on the baseline information of the production environment database through the metadata information of the SQL change;

[0010] A comparison step is to compare the structure differences between the pre-deployment database structure and the test environment database;

[0011] A judging step, judging whether the SQL script needs to be modified according to the result of the structural difference comparison, and if so, modifying the SQL script and performing the structural difference comparison based on the modified SQL script until the SQL script does not need to be modified;

[0012] Execute the steps to deploy the production environment database based on the SQL script after the judgment step.

[0013] In some embodiments, the metadata information changed by the SQL is based on the baseline information of the production environment database to generate a pre-deployment database structure obtained after pre-deployment, including: obtaining the metadata information of the production environment database through a parsing tool, merging the metadata information of the SQL change with the metadata information of the production environment database, and obtaining the pre-deployment database structure. The parsing tool may be an open source SQL parsing tool. The merging method may be: modifying the metadata information of the production environment database accordingly according to the metadata information of the SQL change.

[0014] In some embodiments, the structural difference comparison between the pre-deployed database structure and the test environment database includes: obtaining metadata information of the test environment database after SQL script deployment through a parsing tool, and comparing the metadata information of the test environment database after SQL script deployment with the pre-deployed database structure.

[0015] In some embodiments, it also includes: a verification step to verify whether the SQL changes in the test environment database are accurate and whether they comply with the functional implementation.

[0016] In some embodiments, the metadata information includes: table attributes, field attributes, and index attributes.

[0017] In some embodiments, the table attributes include: table name, auto-increment value, character set, sorting rule, and comment.

[0018] In some embodiments, the field attributes include: field name, field type, type length, whether it is non-empty, default value, comment, character set, sorting rule, additional attributes, and expression.

[0019] In some embodiments, the index attributes include: index name, index field, index type, and whether it is unique.

[0020] According to a second aspect of the present invention, there is provided a database deployment device based on an SQL script, comprising:

[0021] Test deployment module, used to deploy the test environment database through SQL scripts;

[0022] Parsing module, used to extract metadata information of SQL changes based on SQL scripts;

[0023] A simulation module, used to generate a pre-deployment database structure obtained after pre-deployment based on the baseline information of the production environment database through the metadata information of the SQL change;

[0024] The comparison module is used to compare the structure differences between the pre-deployed database structure and the test environment database;

[0025] A judgment module, used for judging whether the SQL script needs to be modified according to the result of the structural difference comparison, and if so, modifying the SQL script and performing the structural difference comparison based on the modified SQL script until the SQL script does not need to be modified;

[0026] The execution module is used to deploy the production environment database based on the SQL script after the judgment step.

[0027] According to a third aspect of the present invention, there is provided a computer device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of any of the above-mentioned method embodiments when executing the computer program.

[0028] According to a fourth aspect of the present invention, there is provided a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the steps of any of the above-mentioned embodiments of the method are implemented.

[0029] The present invention obtains metadata of SQL scripts and metadata of production environment database by parsing, and merges the two to achieve pre-simulation of production environment database structure without production environment deployment, and by comparing with metadata information of test environment, the differences between different environments can be known in advance, thus improving the accuracy and reliability of changes. Compared with other existing technologies, the present invention provides a simple and convenient difference comparison scheme for different environment data deployment, avoiding problems caused by differences in database structure and ensuring the security of database. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] Figure 1 is a flow chart of some embodiments of the SQL script-based database deployment method of the present invention;

[0031] Figure 2 It is a result diagram of the structural difference comparison in the present invention;

[0032] Figure 3 It is a structural schematic diagram of some embodiments of the SQL script-based database deployment device of the present invention;

[0033] Figure 4 It is a diagram of the internal structure of a computer device used to implement some embodiments of the present invention. DETAILED DESCRIPTION

[0034] Embodiments of the present invention will be described more fully below with reference to the accompanying drawings, in which embodiments of the present invention are shown. However, the present invention can be embodied in many different forms and should not be construed as limited to the embodiments set forth herein.

[0035] The terms used herein are only used for the purpose of describing specific embodiments and are not intended to limit the present disclosure. As used herein, the singular forms "a", "an", "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise. It should also be understood that when used herein, the term "comprising" specifies the presence of the claimed features, wholes, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, wholes, steps, operations, elements, components and / or groups thereof.

[0036] Unless otherwise defined, the terms (including technical and scientific terms) used herein have the same meanings as commonly understood by those of ordinary skill in the art to which the present disclosure belongs. The terms used herein should be interpreted as having the meanings consistent with their meanings in the context of this specification and in the relevant field, and should not be interpreted in an idealized or overly formal sense, unless specifically defined as such herein.

[0037] Figure 1 The flowcharts of some embodiments of the SQL script-based database deployment method of the present invention are shown.

[0038] like Figure 1 As shown, the method includes:

[0039] Test deployment step S102, deploying the test environment database through SQL script;

[0040] The program developer submits SQL scripts to deploy the test environment database and change the test environment database; and verifies whether the SQL changes are accurate and meet the functional implementation requirements.

[0041] Parsing step S104, extracting metadata information of SQL changes based on the SQL script;

[0042] The program developer submits SQL to deploy the production environment. Based on the deployed SQL scripts, including but not limited to CREATE_TABLE, ALTER_TABLE, DROP_TABLE, RENAME_TABLE, CREATE_INDEX, DROP_INDEX and other types, the metadata information of the SQL changes is extracted, mainly including table attributes, field attributes, and index attributes. Table attributes: table name, auto-increment, character set, sorting rules, comments and other information. Field attributes: field name, field type, type length, whether it is non-empty, default value, comments, character set, sorting rules, additional attributes, expressions and other information. Index attributes: index name, index field, index type, whether it is unique and other information.

[0043] Simulating step S106, generating a pre-deployment database structure obtained after pre-deployment based on the baseline information of the production environment database through the metadata information of the SQL change; the pre-deployment database structure is obtained by merging the metadata information of the SQL change with the metadata information of the production environment database.

[0044] This step can obtain the structural status that the SQL script should have after actual deployment and execution, but there is no actual deployment and execution of the SQL.

[0045] Comparison step S108, comparing the pre-deployment database structure with the test environment database for structural differences;

[0046] Determining step S110, determining whether the SQL script needs to be modified according to the result of the structural difference comparison, and if so, modifying the SQL script and performing the structural difference comparison based on the modified SQL script until the SQL script does not need to be modified;

[0047] Execute step S112 to deploy the production environment database based on the SQL script after the determination step.

[0048] The prior art lacks metadata analysis before SQL script deployment, and cannot simulate these structural changes in advance. The present invention parses the SQL script before SQL deployment, extracts all details of the database table structure that will be changed (such as table attributes, field attributes, index attributes, etc.), and simulates these changes in advance before the actual SQL deployment to obtain the library table structure after SQL deployment, compares it with the database structure of the test environment, locates and identifies potential structural differences, helps to make adjustments before SQL deployment, and enhances the accuracy and security of SQL script deployment.

[0049] The present invention also provides other embodiments of the database deployment method based on SQL scripts.

[0050] There is a user table (users):

[0051] CREATE TABLE users(

[0052] id INT(11)AUTO_INCREMENT NOT NULL COMMENT'User ID',

[0053] name VARCHAR(50)NOT NULL COMMENT'name',

[0054] email VARCHAR(255)NOT NULL COMMENT'email address',

[0055] mobile VARCHAR(20)COMMENT'Phone number',

[0056] birth_date DATE COMMENT'date of birth',

[0057] created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',

[0058] updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT'Record update time',

[0059] PRIMARY KEY(id)

[0060] )COMMENT = 'User information table';

[0061] Deploy the test environment database through SQL scripts:

[0062] Through SQL statements (SQL-1)

[0063] ALTER TABLE users

[0064] ADD COLUMN age INT(11)NOT NULL COMMENT'age'AFTE R name,

[0065] DROP COLUMN birth_date,

[0066] MODIFY COLUMN name VARCHAR(100)COMMENT'name',

[0067] ADDINDEX idx_email(email)

[0068] That is, add a new "age" field, delete the "birth_date" field, modify the "name" field to modify its length and field comment; and add a new index. After the development self-test passes, deploy it to the test environment and wait for the tester to test it. After the tester verifies it, deploy the above SQL changes to the production environment.

[0069] In many cases, the SQL changes deployed to the production environment are inconsistent with the changes tested. For example, when changing the production database table, execute the SQL statement (SQL-2):

[0070] ALTER TABLE users

[0071] ADD COLUMN age INT NULL COMMENT 'age' AFTER name,

[0072] DROP COLUMN birth_date,

[0073] MODIFY COLUMN name VARCHAR(255)COMMENT'User name'

[0074] It is not completely consistent with the SQL-1 executed in the test environment database table. You need to use the comparison function before going online to check whether there are any differences to avoid errors.

[0075] In the simulation step, the metadata information (prod-tb-metadata) of the users table structure in the production environment is parsed. At this time, the baseline information has not been changed. The metadata information of the online SQL-2 is parsed and merged with prod-tb-metadata (that is, prod-tb-metadata is modified accordingly according to the metadata information of SQL-2), thereby simulating the metadata information (prod-new-tb-metadata) of the users table in the production environment after SQL-2 is online.

[0076] Comparison step: parse the metadata information (sit-tb-metadata) of the users table structure in the test environment (the test environment where SQL-1 has been executed), compare sit-tb-metadata with prod-new-tb-metadata, and check whether there are any differences in the table structures. Figure 2 The result diagram of the structural difference comparison in the present invention is shown. Figure 2It can be seen that if SQL-2 is deployed to the production environment, there are differences in the table structure including: name, age, idx_email, and the SQL script needs to be modified accordingly.

[0077] Figure 3 A schematic structural diagram of some embodiments of the SQL script-based database deployment device of the present invention is shown.

[0078] like Figure 3 As shown, the SQL script-based database deployment device in the embodiment includes:

[0079] The test deployment module 100 is used to deploy the test environment database through SQL scripts;

[0080] The parsing module 200 is used to extract metadata information of SQL changes based on the SQL script;

[0081] The simulation module 300 is used to generate a pre-deployment database structure obtained after pre-deployment based on the baseline information of the production environment database through the metadata information of the SQL change;

[0082] A comparison module 400 is used to compare the structure differences between the pre-deployment database structure and the test environment database;

[0083] A judgment module 500 is used to judge whether the SQL script needs to be modified according to the result of the structural difference comparison. If it needs to be modified, the SQL script is modified and the structural difference comparison is performed based on the modified SQL script until the SQL script does not need to be modified;

[0084] The execution module 600 is used to deploy the production environment database based on the SQL script after the determination step.

[0085] For the specific definition of a database deployment device based on SQL scripts, please refer to the definition of a database deployment method based on SQL scripts above, which will not be repeated here. Each module in the above-mentioned database deployment device based on SQL scripts can be implemented in whole or in part by software, hardware and a combination thereof. The above-mentioned modules can be embedded in or independent of the processor in the computer device in the form of hardware, or can be stored in the memory of the computer device in the form of software, so that the processor can call and execute the operations corresponding to the above modules.

[0086] The present invention also provides a computer device, which may be a terminal, and its internal structure diagram may be as follows: Figure 4As shown. The computer device includes a processor, a memory, a network interface, a display screen and an input device connected via a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, the above-mentioned SQL script-based database deployment method is implemented. The display screen of the computer device can be a liquid crystal display or an electronic ink display screen, and the input device of the computer device can be a touch layer covering the display screen, or a button, trackball or touchpad provided on the housing of the computer device, or an external keyboard, touchpad or mouse, etc. Those skilled in the art can understand that Figure 4 The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present invention, and does not constitute a limitation on the computer device to which the solution of the present invention is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0087] The present invention also provides a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the above-mentioned database deployment method based on SQL script is implemented.

[0088] Those skilled in the art can understand that all or part of the processes in the above method embodiments can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided by the present invention can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus), direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

[0089] So far, the embodiments of the present invention have been described in detail. In order to avoid obscuring the concept of the present invention, some details known in the art are not described. Based on the above description, those skilled in the art can fully understand how to implement the technical solutions disclosed herein.

[0090] Although some specific embodiments of the present invention have been described in detail by way of examples, it should be understood by those skilled in the art that the above examples are for illustration only and are not intended to limit the scope of the present invention. It should be understood by those skilled in the art that the above embodiments may be modified or some technical features may be replaced by equivalents without departing from the scope and spirit of the present invention. The scope of the present invention is defined by the appended claims.

Claims

1. A database deployment method based on SQL script, characterized in that: include: Test deployment steps, deploy the test environment database through SQL scripts; Parsing step, extracting metadata information of SQL changes based on SQL scripts; A simulation step, generating a pre-deployment database structure obtained after pre-deployment based on the baseline information of the production environment database through the metadata information of the SQL change; A comparison step is to compare the structure differences between the pre-deployment database structure and the test environment database; A judging step, judging whether the SQL script needs to be modified according to the result of the structural difference comparison, and if so, modifying the SQL script and performing the structural difference comparison based on the modified SQL script until the SQL script does not need to be modified; Execute the steps to deploy the production environment database based on the SQL script after the judgment step.

2. The SQL script-based database deployment method according to claim 1, characterized in that: The metadata information changed by the SQL generates a pre-deployment database structure obtained after pre-deployment based on the baseline information of the production environment database, including: obtaining the metadata information of the production environment database through a parsing tool, merging the metadata information of the SQL change with the metadata information of the production environment database, and obtaining the pre-deployment database structure.

3. The SQL script-based database deployment method according to claim 1, characterized in that: The structural difference comparison between the pre-deployment database structure and the test environment database includes: obtaining metadata information of the test environment database after SQL script deployment through a parsing tool, and comparing the metadata information of the test environment database after SQL script deployment with the pre-deployment database structure.

4. The SQL script-based database deployment method according to claim 1, characterized in that: Also includes: Verification step: Verify whether the SQL changes in the test environment database are accurate and meet the functional implementation requirements.

5. The SQL script-based database deployment method according to claim 1, characterized in that: The metadata information includes: table attributes, field attributes, and index attributes.

6. The SQL script-based database deployment method according to claim 3, characterized in that: The table attributes include: table name, auto-increment value, character set, sorting rule, and comment; The field attributes include: field name, field type, type length, whether it is not empty, default value, comment, character set, sorting rule, additional attributes, and expression; The index attributes include: index name, index field, index type, and whether it is unique.

7. A database deployment device based on SQL script, characterized in that: include: Test deployment module, used to deploy the test environment database through SQL scripts; Parsing module, used to extract metadata information of SQL changes based on SQL scripts; A simulation module, used to generate a pre-deployment database structure obtained after pre-deployment based on the baseline information of the production environment database through the metadata information of the SQL change; The comparison module is used to compare the structure differences between the pre-deployed database structure and the test environment database; A judgment module, used for judging whether the SQL script needs to be modified according to the result of the structural difference comparison, and if so, modifying the SQL script and performing the structural difference comparison based on the modified SQL script until the SQL script does not need to be modified; The execution module is used to deploy the production environment database based on the SQL script after the judgment step.

8. A computer device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 6 are implemented.

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

Images