CN108984408B

Movatterモバイル変換

Info

Publication number: CN108984408B
Application number: CN201810769897.2A
Authority: CN
Inventors: 万小妹; 易金华; 李纤纤
Original assignee: Bank of China Ltd
Current assignee: Bank of China Ltd
Priority date: 2018-07-13
Filing date: 2018-07-13
Publication date: 2021-11-30
Anticipated expiration: 2038-07-13
Also published as: CN108984408A

Abstract

The invention provides a method and a device for detecting SQL codes in an application system. In each stage of software engineering, when SQL codes are detected to be changed, acquiring the content item states of all current SQL codes in a working environment corresponding to the working progress according to the working progress of the software engineering; creating a main base line based on the content item states of all the collected SQL codes, and setting a base line identifier for the main base line; comparing the content items of the main base line with the content items of the historical main base line in the working environment to determine a first SQL change range; and prompting each SQL code in the first SQL change range so that a worker can find each changed SQL code in time and confirm the risk of each changed SQL code. The invention realizes scientific and important management of the change risk of the SQL code at different stages of software engineering, and ensures the sustainable, accurate and efficient operation of the SQL code.

Description

Method and device for detecting SQL (structured query language) codes in application system

Technical Field

The invention relates to the technical field of computers, in particular to a method and a device for detecting SQL codes in an application system.

Background

At present, most data of an application system are stored depending on a database, and for a system using the database, the performance of the system is directly influenced by the quality of SQL statements. Research shows that many application system performance bottlenecks are caused by the poor quality of SQL statements. In practical applications, a poor quality SQL statement may drag down the entire application system, and in extreme cases, may even cause a database server crash. Therefore, it is very important to ensure a stable and efficient SQL statement for the application system, which directly affects the user experience of the application system.

The SQL code refers to an SQL statement, and improper use of the SQL code can bring about serious waste of application system resources and seriously affect the customer experience. At present, the management of the SQL codes in most application programs only stays at the primary level of the release management of the code version, and a complete and reliable management method is lacked to ensure the continuous, accurate and efficient operation of the SQL codes.

Disclosure of Invention

In view of this, the present invention provides a method and an apparatus for detecting an SQL code in an application system, so as to scientifically and intensively manage the change risk of the SQL code at different stages of a software project, identify the risk of the SQL code in time, and ensure the SQL code to continuously, accurately and efficiently run. The technical scheme is as follows:

based on one aspect of the present invention, the present invention provides a method for detecting an SQL code in an application system, where the method includes:

in each stage of software engineering, when SQL codes are detected to be changed, acquiring the content item states of all current SQL codes in a working environment corresponding to the working progress according to the working progress of the software engineering;

creating a main baseline based on the content item states of all the collected SQL codes, and setting a baseline identifier for the main baseline;

comparing the content items of the main baseline with the content items of the historical main baseline in the working environment to determine a first SQL change range, wherein the first SQL change range comprises changed SQL codes;

and prompting each SQL code in the first SQL change range so that a worker can find each changed SQL code in time and confirm the risk of each changed SQL code.

Optionally, when the risk value of at least one SQL code in the first SQL alteration scope is greater than a first preset threshold, the method further includes:

after the SQL codes with the risk values larger than a first preset threshold value in the first SQL change range are processed, acquiring the content item states of all current SQL codes again;

creating a temporary baseline based on the content item states of all the collected SQL codes, and setting a baseline identifier for the temporary baseline;

comparing the content items of the temporary baseline with the content items of the historical main baseline in the working environment to determine a second SQL change range, wherein the second SQL change range comprises changed SQL codes;

prompting each SQL code in the second SQL change range so that a worker can find each changed SQL code in time and confirm the risk of each changed SQL code;

when the risk value of at least one SQL code in the second SQL change range is determined to be larger than a first preset threshold value, processing the SQL codes with the risk value larger than the first preset threshold value in the second SQL change range, and returning to the step of collecting the content item states of all the current SQL codes again after the processing is finished;

and when determining that the risk values of all SQL codes in the second SQL variation range are not greater than a first preset threshold value, determining the temporary baseline as a main baseline.

Optionally, the historical main baseline in the work environment comprises: a most recent historical primary baseline in the work environment.

Optionally, the method further comprises:

acquiring a plurality of baselines with the same baseline identification in different working environments;

and comparing the content items of the baselines pairwise to determine the difference between the content items of the baselines with the same baseline identification in different working environments.

Optionally, when the work progress of the software engineering is in a running stage, after determining the first SQL change scope, or after determining the second SQL change scope, the method further includes:

collecting the operation performance parameters of each SQL code in the current main baseline/temporary baseline;

comparing the operating performance parameters of the SQL codes with a performance baseline;

and outputting alarm information when the operation performance of the SQL code is smaller than a second preset threshold value.

Optionally, when it is determined that the running performance of all current SQL codes is smaller than a second preset threshold, the method further includes:

and outputting an optimization suggestion according to the operation performance parameters, the physical structure, the execution plan and the preset tuning rule of the SQL codes in the current main baseline/temporary baseline.

Based on another aspect of the present invention, the present invention provides an apparatus for detecting SQL codes in an application system, the apparatus comprising:

the system comprises a first acquisition unit, a second acquisition unit and a third acquisition unit, wherein the first acquisition unit is used for acquiring the content item states of all current SQL codes in a working environment corresponding to the working progress according to the working progress of the software engineering when the SQL codes are detected to be changed in each stage of the software engineering;

the system comprises a creating unit, a setting unit and a processing unit, wherein the creating unit is used for creating a main baseline based on the states of all the acquired content items of the SQL codes and setting a baseline identifier for the main baseline;

the first comparison unit is used for comparing the content items of the main baseline with the content items of the historical main baseline in the working environment to determine a first SQL change range, and the first SQL change range comprises changed SQL codes;

and the prompting unit is used for prompting each SQL code in the first SQL change range so that a worker can find each changed SQL code in time and confirm the risk of each changed SQL code.

Optionally, when the risk value of at least one SQL code in the first SQL modification range is greater than a first preset threshold, after the SQL code in the first SQL modification range whose risk value is greater than the first preset threshold is processed, the first acquisition unit is further configured to acquire the content item states of all current SQL codes again;

the creating unit is further used for creating a temporary baseline based on the content item states of all the collected SQL codes and setting a baseline identifier for the temporary baseline;

the first comparison unit is further configured to compare the content items of the temporary baseline with the content items of the historical main baseline in the working environment, and determine a second SQL change range, where the second SQL change range includes changed SQL codes;

the prompting unit is further used for prompting each SQL code in the second SQL change range so that a worker can find each changed SQL code in time and confirm the risk of each changed SQL code;

the device further comprises:

and the determining unit is used for determining the temporary baseline as a main baseline when the risk values of all SQL codes in the second SQL variation range are determined not to be larger than a first preset threshold.

Optionally, when the work progress of the software engineering is in a running stage, the apparatus further includes:

the second acquisition unit is used for acquiring the operation performance parameters of all SQL codes in the current main baseline/temporary baseline;

the second comparison unit is used for comparing the operation performance parameters of the SQL codes with the performance baseline;

and the early warning unit is used for outputting warning information when the operation performance of the SQL code is determined to be smaller than a second preset threshold value.

In the method and the device for detecting the SQL codes in the application system, in each stage of software engineering, when the SQL codes are detected to be changed, the content item states of all the current SQL codes are collected in the working environment corresponding to the working progress according to the working progress of the software engineering; creating a main baseline based on the content item states of all the collected SQL codes, and setting a baseline identifier for the main baseline; comparing the content items of the main baseline with the content items of the historical main baseline in the working environment to determine a first SQL change range, wherein the first SQL change range comprises changed SQL codes; and prompting each SQL code in the first SQL change range so that a worker can find each changed SQL code in time and confirm the risk of each changed SQL code.

The invention realizes the detection of the SQL code in each stage of the software engineering and establishes a set of full life cycle management mechanism of the SQL code. During specific detection, a baseline management technology is used for tracking and managing logic change and performance change of the SQL code, the changed SQL code is prompted in time, and the possibility of introducing risks into the SQL code is reduced to the greatest extent. The method and the system realize scientific and key management of the change risk of the SQL code at different stages of software engineering, identify the risk of the SQL code in time and ensure the sustainable, accurate and efficient operation of the SQL code, thereby controlling the development quality of an application system and ensuring the accuracy and stability of production operation.

Drawings

In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to the provided drawings without creative efforts.

FIG. 1 is a full lifecycle diagram of the SQL code of the present invention;

FIG. 2 is a flowchart of a method for detecting SQL codes in an application system according to the present invention;

FIG. 3 is a flow chart of another method for detecting SQL codes in an application system according to the present invention;

FIG. 4 is a schematic diagram of SQL code detection throughout the entire software project;

fig. 5 is a schematic structural diagram of a detection apparatus for SQL codes in an application system according to the present invention;

fig. 6 is a schematic structural diagram of another detection apparatus for SQL codes in an application system according to the present invention.

Detailed Description

The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

Before specifically describing the detection method of the SQL code in the application system provided by the present invention, the applicant first explains the related technical terms related to the present invention.

SQL statement: a Structured query language (Structured query language) that operates on a database.

SQL code: typically, the SQL statements refer to the same SQL statement, and may be different SQL statements if the operation functions are equivalent. Such as: and optimizing the operation function into a merge statement by an update statement under the condition of unchanging the operation function.

SQL change: the SQL code changes are called SQL changes. Because of the close correlation of SQL statements and their referencing objects, the SQL changes described in this invention include changes to the SQL itself and to the physical structures such as tables, indexes, etc. to which it refers.

And (3) SQL code identification: for uniquely identifying a segment of SQL code. The SQL code identifier is embodied in the SQL code in an annotated form, for example: v xxx/, thereby facilitating automatic collection of SQL code.

The content item: refers to what the baseline contains. In the invention, each SQL code contains 6 kinds of information: current environmental parameters, SQL source codes, SQL related physical structure information, an SQL execution plan and SQL performance indexes.

Main baseline: refers to the collection of states of an SQL content item at a certain time. In practical applications, the main base line is approved, and an SQL has only 1 main base line in a working environment.

Temporary baseline: similar to the main baseline, the provisional baseline is not deemed identifiable. The temporary baseline is acquired temporarily in order to form or optimize the main baseline. In practice, when the tentative baseline is approved, it may be marked as the primary baseline (where the original primary baseline may fail). An SQL may have multiple temporary baselines in a working environment.

Performance criteria: the reference value of each performance index in the SQL baseline generally takes the past optimal performance index as the reference value.

The identification related in the invention mainly comprises a baseline identification used for uniquely identifying the baseline, an environment identification used for uniquely identifying the working environment, an SQL code identification used for uniquely identifying the SQL code and a baseline instance used for uniquely identifying the SQL code baseline instance.

Specifically, in the present invention, the definition rule of the baseline identifier may include: lot + task number containing time + serial number, where optionally the serial number starts at 001, up to 999.

The definition rule of the environment identification may include: product identification + environment type + environment ID. Wherein the environment type may be a development environment, an internal test environment, an integrated test environment, a production environment, and the like.

The definition rules identified by the SQL code may include: product identification + module name + function descriptor. The SQL code of each section of functional logic has a unique SQL code identification.

The rules for defining the baseline instance may include: environment identification + baseline identification. The baseline instance can uniquely identify a SQL code baseline instance.

One of the main ideas of the present invention includes, but is not limited to, using baseline management technology to track and manage the logic change and performance change of the SQL code, and detecting and controlling the SQL code in each stage of the software engineering, thereby reducing the possibility of introducing risks into the SQL code to the greatest extent.

In the invention, each stage of software engineering comprises: the SQL identification definition phase, the SQL code creation phase, the SQL code modification phase, the SQL code review phase, the SQL code debugging phase, the SQL code operation phase and the SQL identification extinction phase are 7 phases in total. The 7 phases constitute the full life cycle of SQL. As shown in fig. 1, a logic problem or a performance problem found in the SQL code review stage, the SQL code debugging stage, and the SQL code operation stage may cause SQL code modification, and an external service requirement change may also cause SQL code modification. Every time when the SQL code changes, the SQL code review stage, the SQL code debugging stage and the SQL code running stage are triggered.

Based on the data operation logic centralization, performance stability and 3 major characteristics of optimization of SQL codes, the invention can make the full life cycle management of the SQL codes from the following aspects:

(1) in the application system, the SQL code is marked by the unique SQL code identification, so that the development process of the SQL code and the quick positioning of the fault of the SQL code are conveniently tracked.

(2) And (3) tracking and managing the physical structure of the object (such as a table, a partition, a view, an index and the like) corresponding to the SQL code while tracking the SQL code, so as to prevent the occurrence of performance problems and cause analysis.

(3) The performance risk which possibly occurs is identified in advance in the development stage of the application software, and the following software engineering activities are prompted to be tracked:

1) ensuring functional logic equivalence before and after SQL code modification in the encoding stage (including walk-through);

2) the performance risk which possibly occurs is ensured to be identified in advance in the walk-through stage;

3) ensuring that performance risks are eliminated during the internal testing phase;

4) ensuring the stability of the operation of the SQL code in an integration test stage (including a performance test), and carrying out performance optimization on the SQL code at the right time;

5) and when the production is put on line, the SQL code performance is confirmed through performance standard comparison, so that production accidents are prevented.

(4) And a continuous optimization mechanism is provided, and SQL sentences with low efficiency in operation in the production environment are identified regularly and optimization measure suggestions are provided.

In the following, the applicant will describe in detail the detection method of the SQL code in the application system provided by the present invention, as shown in fig. 2, the detection method of the SQL code in the application system provided by the present invention may include:

step 101, in each stage of the software engineering, when detecting that the SQL code is changed, according to the work progress of the software engineering, in the work environment corresponding to the work progress, acquiring the content item states of all current SQL codes.

In the present invention, the work progress of the software engineering may include entering an SQL identifier definition phase (hereinafter referred to as definition), an SQL code creation phase (hereinafter referred to as creation), an SQL code modification phase (hereinafter referred to as modification), an SQL code review phase (hereinafter referred to as review), an SQL code debugging phase (hereinafter referred to as debugging), an SQL code running phase (hereinafter referred to as running), and an SQL identifier extinction phase (hereinafter referred to as extinction). The working environment corresponding to the definition, the creation, the change and the review is a development testing environment, the working environment corresponding to the debugging comprises a unit testing environment, an assembly testing environment and an integration testing environment, and the working environment corresponding to the operation and the extinction is a production environment.

Specifically, the content item state of the SQL code collected by the present invention includes the operating environment parameter of the SQL code, the SQL source code, the SQL related physical structure information, the SQL execution plan, and the SQL performance index.

Step 102, creating a main baseline based on the collected content item states of all SQL codes, and setting a baseline identifier for the main baseline.

Step 103, comparing the content items of the main baseline with the content items of the historical main baseline in the working environment, and determining a first SQL code change range, wherein the first SQL change range comprises changed SQL codes.

Specifically, the present invention compares each item in the content items of the main baseline with each item in the content items of the historical main baseline in sequence, wherein the comparison mainly includes logical equivalence of SQL source codes, execution plan change analysis, physical structure change analysis, performance index analysis, and the like, so as to determine all changed SQL codes, and all changed SQL codes form the first SQL change range. That is, in the present invention, the SQL code described in the first SQL modification range is a modified SQL code, that is, an SQL code that requires attention by a worker to be at risk.

Preferably, in order to avoid repeated confirmation of the content items of the newly created main baseline, the present invention preferentially compares the content items of the newly created main baseline with the content items of the historical main baseline of the latest (i.e. most recent) confirmed content items.

When the SQL code which is not changed at present is determined by comparing the content item of the newly created main baseline with the content item of the latest historical main baseline, the method automatically confirms that the main baseline is valid and continues to enter the next stage.

When the currently changed SQL codes are determined by comparing the content items of the newly created main base line with the content items of the latest historical main base line, all the changed SQL codes are found and positioned in a first SQL change range mode, so that workers (such as software developers) only need to pay attention to and manually confirm the risks of the changed SQL codes.

And 104, prompting each SQL code in the first SQL change range so that a worker can find each changed SQL code in time and confirm the risk of each changed SQL code.

After the first SQL change range is determined, each changed SQL code in the determined first SQL change range is prompted in a prompting mode such as highlighting, newly creating a window and displaying in the newly created window, and the like, so that a worker can clearly and quickly view and position each changed SQL code.

Specifically, in the invention, the staff checks and judges the risk value of each SQL code in the first SQL change range in sequence. When the risk value of one of the SQL codes (taking the first SQL code as an example) is greater than a first preset threshold, it indicates that the first SQL code has a potential risk, and corresponding processing needs to be performed on the first SQL code, such as recoding, creating an index, and the like. When the risk value of one SQL code (taking the second SQL code as an example) is not greater than the first preset threshold, it indicates that the risk of the second SQL code is very low or no risk, and at this time, the worker continues to obtain the next SQL code and determines the risk of the next SQL code.

In the detection method of the SQL code in the application system, the detection of the SQL code in each stage of software engineering is realized, and a set of full life cycle management mechanism of the SQL code is established. During specific detection, a baseline management technology is used for tracking and managing logic change and performance change of the SQL code, the changed SQL code is prompted in time, and the possibility of introducing risks into the SQL code is reduced to the greatest extent. The method and the system realize scientific and key management of the change risk of the SQL code at different stages of software engineering, identify the risk of the SQL code in time and ensure the sustainable, accurate and efficient operation of the SQL code, thereby controlling the development quality of an application system and ensuring the accuracy and stability of production operation.

In addition, when logic and performance problems occur in production, the method can provide the tracing of SQL change history, help to quickly locate the problem source and implement the corresponding measures.

On the basis of the foregoing embodiment, as shown in fig. 3, afterstep 104, the method of the present invention determines that the risk value of at least one SQL code in the first SQL modification range is greater than a first preset threshold, and after completing processing on the SQL code in the first SQL modification range whose risk value is greater than the first preset threshold, the method may further include:

and step 105, collecting the content item states of all the current SQL codes again.

And 106, creating a temporary baseline based on the content item states of all the collected SQL codes, and setting a baseline identifier for the temporary baseline.

And 107, comparing the content items of the temporary baseline with the content items of the historical main baseline in the working environment, and determining a second SQL change range, wherein the second SQL change range comprises changed SQL codes.

Preferably, in the embodiment of the present invention, the historical main baseline in the working environment is the latest historical main baseline in the working environment.

And step 108, prompting each SQL code in the second SQL change range so that a worker can find each changed SQL code in time and confirm the risk of each changed SQL code.

In the present invention, when it is determined that the risk value of at least one SQL code in the second SQL modification range is greater than the first preset threshold, the SQL code in the second SQL modification range whose risk value is greater than the first preset threshold is processed, and after the processing is completed, theexecution step 105 is returned.

And step 109, when it is determined that the risk values of all the SQL codes in the second SQL variation range are not greater than a first preset threshold, determining the temporary baseline as a main baseline.

In the invention, after SQL codes with risk values larger than a first preset threshold value in the first SQL change range are processed by a worker, extra SQL risks may be introduced into the processing operation, so that the content items of the temporary baseline are compared with the content items of the latest historical main baseline in the working environment through the temporary baseline created based on the content item states of all the current SQL codes acquired again to further judge whether the SQL codes in the temporary baseline processed by the worker still have risks.

If the risk still exists, the SQL codes with the risk continue to be processed by the staff, and the procedure returns to thestep 105 after the processing is finished until the risk of all the SQL codes is confirmed to be low or no. Further, the present invention marks the newly created temporary baseline as the primary baseline after confirming that all SQL codes are at low or no risk, while the previously created primary baseline fails.

The baseline content item is progressively validated as complete at different stages of the software project. In the same working environment, generally, after the content item of a certain temporary baseline is confirmed to the maximum, the content item will be marked as a main baseline, which means that the working phase of the working environment is stable or optimal.

The invention adopts the realization mode of the temporary baseline auxiliary main baseline, accurately tracks the logic change and the performance change of the SQL code in the detection process of the SQL code, prompts the changed SQL code in time and reduces the possibility of introducing risks into the SQL code to the greatest extent.

On the basis of the foregoing embodiment, further optionally, the present invention may further include:

acquiring a plurality of baselines with the same baseline identification in different working environments; and comparing the content items of the baselines pairwise to determine the difference between the content items of the baselines with the same baseline identification in different working environments.

In the invention, for different working environments, a plurality of SQL code baselines with the same baseline identification can be compared with each other, so as to find the difference between content items in different working environments.

Particularly, in the present invention, when the work progress of the software engineering is in the running stage, after determining the first SQL change scope, or after determining the second SQL change scope, the method may further include:

step S1, collecting the operation performance parameters of each SQL code in the current main baseline/temporary baseline;

step S2, comparing the operation performance parameters of the SQL codes with performance baselines;

and step S3, when the operation performance of the SQL code is determined to be smaller than a second preset threshold value, outputting alarm information.

And further preferably, the present invention may further include, after step S3:

and step S4, outputting an optimization suggestion according to the operation performance parameters, the physical structure, the execution plan and the preset tuning rules of the SQL codes in the current main baseline/temporary baseline.

After the work progress of software engineering enters the running stage, although performance tuning is already performed on the SQL code in the previous integrated test environment, the data volume and concurrent access in the production environment are far larger than those in the integrated test environment, and the stability of the SQL performance is more worthy of attention. Therefore, in order to monitor the stable running of the SQL code in the production environment, the invention further provides a performance early warning function and an optimization suggestion function.

Specifically, the present invention presets a tuning rule base for storing various tuning rules, where the tuning rules stored in the tuning rule base may include: (1) in the indexing aspect: implicit conversion cannot occur to index columns in Where conditions, and the like, wherein functions or case Where expressions are not used for the index columns in Where conditions; (2) and (3) partitioning: not using functions or case Where expressions for partition columns in the Where condition, restricting as much as possible the equal or range for partition columns of range partitions (between … … and etc.); (3) and (3) performing planning: small tables of row sources are used as driving tables, non-equal value connections are preferably not to occur, etc.

In the practical application process of the invention, a background process acquires the operation performance parameters of the SQL codes in the current main baseline/temporary baseline in real time, compares the operation performance parameters with the performance baseline, and when the operation performance of a certain SQL code is determined to be smaller than a second preset threshold, the operation performance of the SQL code is reduced, and alarm information is output.

The invention realizes the monitoring and early warning of the operation performance of the SQL code and ensures the sustainable, accurate and efficient operation of the SQL code.

FIG. 4 is a schematic diagram of SQL code detection throughout the entire software project, visually depicting the division of labor at various stages of the software project. Wherein, the development phase ensures the functional logic equivalence of the SQL codes before and after modification; the walkthrough phase ensures that logical risks are eliminated while identifying potential performance risks; the internal testing phase ensures that performance risks are eliminated; the SQL code is properly adjusted and optimized in the integrated test stage, so that the stability and proper optimization of the operation of the SQL code are ensured; monitoring the SQL code running condition in the production stage, providing an early warning function, and enabling a user to check a performance analysis report; and deleting the base line corresponding to the SQL code by using logic in the death phase for history tracing. When a change is initiated, there is also a special change flow to handle. Each stage has clear division of labor and well controls SQL change risks.

In the invention, different life cycles have different emphasis on SQL code management, and the next stage can automatically inherit the confirmed result of the previous stage, thereby efficiently controlling the development quality of an application system and ensuring the accuracy and stability of production and operation.

Based on the foregoing text, the present invention provides a method and an apparatus for detecting SQL codes in an application system, where as shown in fig. 5, the apparatus includes:

afirst collecting unit 100, configured to, in each stage of a software project, when detecting that an SQL code is changed, collect, according to a work progress of the software project, content item states of all current SQL codes in a work environment corresponding to the work progress;

the creatingunit 200 is configured to create a main baseline based on the states of all the acquired content items of the SQL codes, and set a baseline identifier for the main baseline;

afirst comparison unit 300, configured to compare the content item of the main baseline with the content item of the historical main baseline in the working environment, and determine a first SQL change range, where the first SQL change range includes an SQL code that has been changed;

the promptingunit 400 is configured to prompt each SQL code in the first SQL change range, so that a worker finds each changed SQL code in time and confirms a risk of each changed SQL code.

When the risk value of at least one SQL code in the first SQL modification range is greater than a first preset threshold, after the SQL code with the risk value greater than the first preset threshold in the first SQL modification range is processed, thefirst acquisition unit 100 is further configured to acquire the content item states of all current SQL codes again;

the creatingunit 200 is further configured to create a temporary baseline based on the states of all the acquired content items of the SQL codes, and set a baseline identifier for the temporary baseline;

the first comparingunit 300 is further configured to compare the content items of the temporary baseline with the content items of the historical main baseline in the working environment, and determine a second SQL change range, where the second SQL change range includes changed SQL codes;

the promptingunit 400 is further configured to prompt each SQL code in the second SQL change range, so that a worker finds each changed SQL code in time and confirms a risk of each changed SQL code;

the device further comprises:

In the present invention, the historical main baselines in the working environment include: the most recent historical primary baseline in the work environment.

Further, as shown in fig. 6, when the work progress of the software engineering is in the running stage, the apparatus for detecting an SQL code in an application system provided by the present invention may further include:

thesecond acquisition unit 500 is configured to acquire the operation performance parameters of each SQL code in the current main baseline/temporary baseline;

a second comparingunit 600, configured to compare the running performance parameter of the SQL code with a performance baseline;

and theearly warning unit 700 is configured to output warning information when it is determined that the operation performance of the SQL code is smaller than a second preset threshold.

And anoutput unit 800, configured to output an optimization suggestion according to the operation performance parameter, the physical structure, the execution plan, and the preset tuning rule of the SQL code in the current main baseline/temporary baseline.

The detection device of the SQL code in the application system can be a universal SQL full life cycle management component which can be conveniently embedded into the application system, and the component has independence on the management of the SQL code and is completely isolated from other functions of the application system. The component is an embedded device, is optional for an application system and is not coupled with other functions of the system. The component has safety on the management of the SQL codes and can isolate the production environment from other environments. A set of 'full life cycle management system' is deployed in the production environment and other environments, and data can be shared among the production environment and other environments.

It should be noted that, in the present specification, the embodiments are all described in a progressive manner, each embodiment focuses on differences from other embodiments, and the same and similar parts among the embodiments may be referred to each other. For the device-like embodiment, since it is basically similar to the method embodiment, the description is simple, and for the relevant points, reference may be made to the partial description of the method embodiment.

Finally, it should also be noted that, herein, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Also, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.

The method and the device for detecting the SQL code in the application system provided by the application are introduced in detail, specific examples are applied in the description to explain the principle and the implementation of the application, and the description of the above embodiments is only used to help understanding the method and the core idea of the application; meanwhile, for a person skilled in the art, according to the idea of the present application, there may be variations in the specific embodiments and the application scope, and in summary, the content of the present specification should not be construed as a limitation to the present application.

Claims

1. A method for detecting SQL codes in an application system is characterized by comprising the following steps:

in each stage of software engineering, when SQL codes are detected to be changed, acquiring the content item states of all current SQL codes in a working environment corresponding to the working progress according to the working progress of the software engineering; the content item state comprises: the SQL execution plan comprises SQL performance indexes, SQL source codes, SQL related physical structure information, SQL execution plans and SQL operation environment parameters of SQL codes;

prompting each SQL code in the first SQL change range so that a worker can find each changed SQL code in time and confirm the risk of each changed SQL code;

when the risk value of at least one SQL code in the first SQL alteration scope is greater than a first preset threshold, the method further comprises:

2. The method of claim 1, wherein the historical master baseline in the work environment comprises: a most recent historical primary baseline in the work environment.

3. The method of claim 1, further comprising:

4. The method of claim 1, wherein when the work progress of the software project is in a runtime phase, after determining a first SQL change scope, or after determining a second SQL change scope, the method further comprises:

5. The method according to claim 4, wherein when the running performance of all current SQL codes is determined to be less than the second preset threshold, the method further comprises:

6. An apparatus for detecting SQL codes in an application system, the apparatus comprising:

the system comprises a first acquisition unit, a second acquisition unit and a third acquisition unit, wherein the first acquisition unit is used for acquiring the content item states of all current SQL codes in a working environment corresponding to the working progress according to the working progress of the software engineering when the SQL codes are detected to be changed in each stage of the software engineering; the content item state comprises: the SQL execution plan comprises SQL performance indexes, SQL source codes, SQL related physical structure information, SQL execution plans and SQL operation environment parameters of SQL codes;

the prompting unit is used for prompting each SQL code in the first SQL change range so that a worker can find each changed SQL code in time and confirm the risk of each changed SQL code;

when the risk value of at least one SQL code in the first SQL modification range is greater than a first preset threshold, after the SQL code with the risk value greater than the first preset threshold in the first SQL modification range is processed, the first acquisition unit is further configured to acquire the content item states of all current SQL codes again;

the device further comprises:

7. The apparatus of claim 6, wherein the historical master baseline in the work environment comprises: a most recent historical primary baseline in the work environment.

8. The apparatus of claim 6, wherein when the work progress of the software engineering is in a run phase, the apparatus further comprises: