CN111381860A

CN111381860A - Quick maintenance framework and method for database SQL script

Info

Publication number: CN111381860A
Application number: CN202010253823.0A
Authority: CN
Inventors: 方鑫
Original assignee: Joint Digital Technology Co ltd
Current assignee: Joint Digital Technology Co ltd
Priority date: 2020-04-02
Filing date: 2020-04-02
Publication date: 2020-07-07

Abstract

The invention discloses a quick maintenance framework of a database SQL script and an implementation method thereof. The method comprises the following steps: acquiring a plurality of SQL files to the local; placing the SQL file and the db.bat file in the same directory; bat script is executed to upload SQL files to the configuration library in batch; logging in a Jenkins environment to construct a corresponding database environment, downloading db.sh and SQL files, executing the db.sh files in the corresponding environment, and triggering and executing SQL files to store all SQL files in a directory; and when the SQL file is completely executed successfully, triggering and informing other service line test engineers. The invention can execute a plurality of SQL files to a plurality of database environments, and only when the SQL files are completely executed successfully, the SQL files are informed to other people to be executed, so that the SQL can be ensured to be executed successfully in each environment.

Description

Quick maintenance framework and method for database SQL script

Technical Field

The invention belongs to the technical field of computer software, and relates to a database SQL script rapid maintenance framework and an implementation method thereof.

Background

When the software releases a new version, if the database is changed, a plurality of database environments need to be maintained simultaneously. The conventional maintenance method is that a development engineer provides an SQL script of a database, the SQL script is uploaded manually, then test engineers of all service lines maintain own database environments respectively, and the SQL script is executed manually respectively.

This conventional method has the following disadvantages:

(1) one SQL script is executed manually for at least 1 minute, then ten SQL scripts take at least 10 minutes;

(2) one database environment is manually executed for 10 minutes, then ten database environments are 100 minutes;

(3) if one SQL script fails to be executed, other database environments also inevitably fail, and people of other service lines also need to modify the SQL script, so that more time cost and labor cost are lost;

(4) business scenarios for fast iteration of companies are not satisfied;

(5) in the case that someone else on another service line misses the SQL, the administrator cannot visually check which database environment has executed the SQL script.

Disclosure of Invention

In order to solve the problems, the invention discloses a database SQL script rapid maintenance framework and an implementation method thereof, which realize rapid batch execution of a plurality of database environments.

In order to achieve the purpose, the invention provides the following technical scheme:

the database SQL script rapid maintenance framework comprises an SQL automatic uploading module, a configuration library and a batch construction module; the SQL automatic uploading module is used for automatically uploading a local SQL file to the configuration library; the configuration library is used for maintaining SQL files and storing db.sh files; the batch construction module is used for downloading the SQL file and the db.sh file on the configuration library by utilizing Jenkins, uploading the SQL file and the db.sh file to a database environment needing construction, and then triggering the db.sh file to execute the SQL statement in batch;

sh file is used to initialize system environment variable, connect database address to be operated, read sql storage directory and execute sql file; and if the plurality of execution files exist in the sql directory, sequentially reading and then executing the operation, and if the execution of the sql fails, skipping and continuing to execute downwards until all the sql files are successfully executed and then triggering message notification.

Furthermore, the SQL automatic uploading module is realized by adopting batch processing scripts.

And further, when a plurality of execution files are read from the db.sh file, reading is carried out according to the file name sequence, and one sql is read to execute one operation.

Further, the message notification is implemented by means of nailing.

The method for quickly maintaining the SQL script of the database comprises the following steps:

step 1, acquiring a plurality of SQL files to be executed to the local;

placing the SQL file and the db.bat file into the same directory;

b.bat scripts are executed, and SQL files are uploaded to a configuration library in batches;

step 4, logging in a Jenkins environment, constructing a corresponding database environment, downloading db.sh and SQL files, executing the db.sh files in the corresponding environment, reading SQL storage directories by the db.sh files and triggering all SQL files under the execution directories;

and 5, when the SQL file is completely executed successfully, triggering and informing other service line test engineers.

Further, in step 4, when the execution of sql fails, the execution is skipped to continue.

Further, the step 5 is followed by a step of writing the result of the execution into a log file.

Compared with the prior art, the invention has the following advantages and beneficial effects:

sh is directly used for connecting the database and executing the SQL file without installing a database driver, and a plurality of SQL files can be executed to a plurality of database environments. And only when the SQL file is completely executed successfully, other people are informed to execute the SQL file, so that the SQL can be ensured to be executed successfully in each environment. The invention can construct a plurality of different database environments, does not need to build an operation environment, realizes the rapid maintenance of the database environments, can ensure the consistency of the environments, has simple and easy scheme, obviously improves the efficiency and reduces the cost.

Drawings

FIG. 1 is a schematic diagram of a database SQL script rapid maintenance framework structure provided by the present invention.

Fig. 2 is a schematic diagram of a directory storing SQL files and db.

Bat, upload sql file to git library page.

FIG. 4 is a Jenkins page.

Detailed Description

The technical solutions provided by the present invention will be described in detail below with reference to specific examples, and it should be understood that the following specific embodiments are only illustrative of the present invention and are not intended to limit the scope of the present invention. Additionally, the steps illustrated in the flow charts of the figures may be performed in a computer system such as a set of computer-executable instructions and, although a logical order is illustrated in the flow charts, in some cases, the steps illustrated or described may be performed in an order different than here.

The structure of the rapid maintenance framework for the database SQL script is shown in figure 1, and the rapid maintenance framework comprises an SQL automatic uploading module, a configuration library and a batch construction module, wherein the SQL automatic uploading module is used for automatically uploading a local SQL file to the configuration library, and db.bat batch processing scripts are adopted in the rapid maintenance framework. The configuration library is used for maintaining SQL files and storing db.sh files, the batch construction module downloads the SQL files and the db.sh files on the configuration library by utilizing Jenkins, uploads the SQL files and the db.sh files to a database environment needing construction, and then triggers the db.sh files to execute.

Sh file is used for realizing batch execution of sql statements, and concretely, the realized functions include: initializing Linux system environment variables, connecting database addresses to be operated, reading an sql storage directory, if a plurality of execution files exist in the sql directory, reading according to the file name sequence, and reading one sql to execute one operation. If the sql execution fails, the execution is skipped and continued. Until all sql files are executed successfully, a nail message is triggered, informing others that the sql can be built on jenkins.

And the executed result is written into a log file, so that the follow-up log tracking is facilitated.

The invention also provides a method for quickly maintaining the SQL script of the database, which comprises the following steps:

step 1, acquiring a plurality of SQL files to be executed to a local computer.

And 2, placing the SQL file and the db. As shown in fig. 2, the 3 SQL files and db.

And 3, executing db.bat scripts (which can be realized by double clicking of a mouse), and uploading the SQL file to a configuration library. Uploading post git library presentation page as shown in fig. 3, 3 SQL files have been uploaded.

And 4, logging in the Jenkins environment, and clicking the corresponding database environment (clicking the right control in the figure 4), so that the corresponding database environment is constructed. The specific construction method comprises the following steps: and downloading the db.sh files and the SQL files, and executing the db.sh files in a corresponding environment, wherein the db.sh files can trigger all the SQL files to be executed. When the sql execution fails, the execution is skipped.

And 5, when the SQL file is completely executed successfully, triggering the nail to inform test engineers of other service lines to log in Jenkins to execute the corresponding database environment. That is, the test engineers of other service lines need not pay attention to the service logic of SQL, and only need to construct on Jenkins.

Corresponding to the foregoing framework, the method further includes the step of writing the result of the execution into a log file.

Based on the scheme of the invention, the person of the service line can execute each sql file by directly removing jenkins and clicking once without paying attention to the sql. When one person uploads the sql and performs successfully, the nail can be triggered to inform others to construct, which can ensure that everyone performs the sql and that the sql is certainly successful in performing.

The technical means disclosed in the invention scheme are not limited to the technical means disclosed in the above embodiments, but also include the technical scheme formed by any combination of the above technical features. It should be noted that those skilled in the art can make various improvements and modifications without departing from the principle of the present invention, and such improvements and modifications are also considered to be within the scope of the present invention.

Claims

1. The quick maintenance framework of the database SQL script is characterized in that: the system comprises an SQL automatic uploading module, a configuration library and a batch construction module; the SQL automatic uploading module is used for automatically uploading a local SQL file to the configuration library; the configuration library is used for maintaining SQL files and storing db.sh files; the batch construction module is used for downloading the SQL file and the db.sh file on the configuration library by utilizing Jenkins, uploading the SQL file and the db.sh file to a database environment needing construction, and then triggering the db.sh file to execute the SQL statement in batch;

2. The database SQL script fast maintenance framework of claim 1, wherein: the SQL automatic uploading module is realized by adopting batch processing scripts.

3. The database SQL script fast maintenance framework of claim 1, wherein: and when the db.sh file reads a plurality of execution files, reading according to the file name sequence, and reading one sql to execute one operation.

4. The database SQL script fast maintenance framework of claim 1, wherein: the message notification is implemented using staples.

5. The method for quickly maintaining the SQL script of the database is characterized by comprising the following steps of:

step 1, acquiring a plurality of SQL files to be executed to the local;

placing the SQL file and the db.bat file into the same directory;

6. The method for rapidly maintaining the database SQL script according to the claim 5, wherein in the step 4, when the SQL fails to be executed, the execution is skipped.

7. The method for rapidly maintaining the SQL script of the database according to claim 5, wherein the step 5 is followed by a step of writing the executed result into a log file.