JP4090226B2 - Display method of stored procedure call relation diagram and database management system - Google Patents

Description

[0001]
[Industrial application fields]
The present invention relates to a display method of a stored procedure call relation diagram stored in a database and a database management system for executing the display method, and in particular, automatically executed by data change operations such as update, deletion, and insertion. The present invention relates to a method for displaying a call relation diagram of a stored procedure in a database, including a trigger which is a special stored procedure, and a database management system.
[0002]
[Prior art]
In general, a stored procedure is one of database objects in which a series of processing procedures for a database are combined into one program and stored in a database management system. This stored procedure has a function of passing an argument from the client and executing a process based on the argument or returning a process deterioration to the client.
[0003]
This created stored procedure is converted into a ready-to-execute format and stored in the database, reducing network traffic compared to sending SQL (Structured Query Language) statements one at a time as usual. At the same time, since parsing and conversion to machine language are terminated in advance on the server, the processing time can be shortened. Therefore, the stored procedure can be executed simply by sending a call command from the client.
[0004]
On the other hand, a trigger is a special stored procedure that is not called directly by the user, and is executed when changing data in a specific table by using one or more change operations such as data update, deletion, and insertion. It is defined, and whenever the user supports change such as update, deletion, insertion, etc., the change is automatically executed according to the definition content of the trigger. This trigger is used when a change process to related data is automatically propagated by one data operation, or a change content of the data is checked.
[0005]
In this way, stored procedures including triggers are provided as standard in many database systems as functions for reducing processing time and maintaining data integrity.
[0006]
In the development of a database application using the stored procedure, the number of stored procedures may be several tens to several hundreds, and in some cases, more than a thousand. In this case, improvements are repeatedly added to improve the function expansion, but in order to perform the expansion and improvement efficiently and definitely, the structure of a complicated program is expressed (displayed) in an easy-to-understand manner for the operator. It is important to.
[0007]
As a conventional technique for displaying the program structure (calling procedure of stored procedure on the database) in an easy-to-understand and accurate manner, a dependency table, which is one of the management tables of the database management system shown in FIG. 3, is used. It has been known.
[0008]
FIG. 3 is a diagram for explaining a program structure display method according to the prior art, and shows stored procedures (Proc), triggers (Triggers), and various tables (Tables) in the server computer 1.
[0009]
The server computer 1 includes a database management system program (DBMS) 11, a DBMS (database management system) management area 13, a stored procedure 14, a user table 15, a management table 16, etc. The program 11 is configured to access the hard disk 12, access the user table 15 and the like according to the contents defined in the stored procedure 14, and execute a predetermined command, via the database management system program 11 performed by the user. The update information, deletion information, and DBMS (database management system software) for the records in the data file stored in the hard disk 12 are recorded. Updates to records in the Airu, deletion, it is possible to perform a variety of record operation of the search and the like.
[0010]
The DMS management area 13 stores user-created tables, tables used by the DBMS for management, and database objects such as stored jars. Registration of stored procedure DBMS management is performed by requesting the DBMS to execute a command (CREATE procedure statement). An object that references another object is considered to be subordinate to that object, and when a database object is created, it is a table that manages the dependency with another object that references it. Are stored in the dependency table.
[0011]
More specifically, the procedure shown in FIG. 3 executes (1) acquires all (*) rows of TableA by executing Proc1, and then (2) the contents of TableA. Update (UPDATE) so as to set the variable 10 in the line 1 and (3) delete the contents of Table A (DELETE) by Proc3, and then execute a series of processing to acquire Table B (SELECT) Is defined. Note that TriggerA_UPD in the figure is a trigger that is activated by an update operation (UPDATE) to Table A, and indicates a process of adding a new row to Table B. TriggerA_DEL is activated by a delete operation (DELETE) to Table A. This is a trigger for deleting a row from TableB.
[0012]
The stored procedure call relationship in this example can be obtained by expanding the contents of the dependency table into a tree structure.
[0013]
[Problems to be solved by the invention]
By analyzing the contents of the dependency table as described above, a stored procedure call relationship diagram can be obtained, and the call relationship with the database object Proc1 in FIG. It becomes like this. This relationship diagram shows Proc1 as a vertex, a branch branching from Table A to TriggerA_UPD and TriggerA_DEL, a branch branching from Proc2 to TriggerA_UPD and TriggerA_DEL, and a branch branching from Proc2 to TriggerTigAG_A , Branching from Proc3 to TriggerA_UPD and TriggerA_DEL via TableA, and branching from Proc3 to TableB.
[0014]
This stored procedure call relationship diagram according to the prior art is displayed in association with a table for a trigger that is a stored procedure that is not a direct call from a user. For this reason, the conventional relationship diagram display method is that there is no explicit description of the direct call in the stored procedure definition from which the table is called, so the stored procedure that executes the conversion of the table that triggers the trigger and the trigger is executed. There is no direct dependency relationship between and, and all indirect dependency relationships via the triggered table are displayed.
[0015]
In addition, since the conventional relationship diagram display method simply expands the dependency between two database objects, the TriggerA_DEL trigger associated with the table that is never executed from Proc1 is displayed on the call relationship diagram. Will be expanded.
[0016]
Therefore, the stored method call relationship diagram display method according to the prior art displays complicated dependencies directly to the user, and it is difficult for the user to grasp the relationship of each procedure. was there.
[0017]
An object of the present invention is to eliminate the above-mentioned problems caused by the prior art, and display the dependency relationship between the trigger and the caller stored procedure as a direct relationship, and change the user table. It is intended to provide a display method and a database management system for a stored procedure call relation diagram that can categorize triggers that are actually called and can accurately represent a call relation of a procedure.
[0018]
[Means for Solving the Problems]
In order to achieve the above object, a method for displaying a stored procedure call relationship diagram of a database management system according to the present invention includes a dependency relationship with other procedures or tables or triggers dependent on a plurality of procedures, and a plurality of tables. Dependency relationship between a trigger stored in the dependency relationship table and a trigger is stored using a dependency relationship table that stores dependency relationships between triggers dependent on each other and dependency relationships between tables dependent on a plurality of triggers. And the relationship with other procedures and triggers executed depending on a specific procedure is displayed in a tree structure.
[0019]
Furthermore, the database management system according to the present invention provides a dependency relationship with other procedures or tables or triggers subordinate to a plurality of procedures, a dependency relationship with a trigger subordinate to a plurality of tables, and a plurality of triggers. Referring to the dependency table storing the dependency relationship with the dependent table, and the dependency relationship between the procedure, the table and the trigger stored in the dependency table, and executing the other depending on the specific procedure Stored procedure analysis means for outputting the relationship between a procedure and a trigger, and a call relationship for displaying in a tree structure the relationship between other procedures and triggers executed depending on a specific procedure output from the stored procedure analysis means And a figure display means.
[0020]
DETAILED DESCRIPTION OF THE INVENTION
Hereinafter, a stored procedure call relation diagram display method and a database management system for executing the method according to the present invention will be described in detail with reference to the drawings.
[0021]
FIG. 1 is a diagram for explaining the overall configuration of a database management system to which a stored procedure call relationship diagram display method according to the present invention is applied, and FIG. 2 is a stored procedure call relationship diagram displayed by the display method according to the present embodiment. FIGS. 4 to 7 are diagrams illustrating an example of a call relationship table. FIGS. 4 to 7 are diagrams for explaining a processing flow according to the present embodiment.
[0022]
As shown in FIG. 1, the database management system to which the stored procedure call relation diagram display method according to the present embodiment is applied comprises a server computer 1 and a client computer 2, and the server computer 1 is connected via a database management system 11. The user can access the hard disk 12 having the DBMS management area 13 for storing database objects such as the stored procedure 14, the user table 15, and the management table 16, and manages the entire database and is connected to the client computer 2. A database management system program 11 having a function to perform the stored procedure analysis means 21 of the server computer 1 and the stored procedure solution. The stored procedure call related information output by the means 21 comprises a stored procedure call relationship diagram displaying means 22 for displaying a tree structure. An actual system includes a CPU, a memory, an input / output device, and other circuit mechanisms. However, in this specification, since it is a well-known technique, it will be omitted.
[0023]
Next, a processing procedure in which the stored procedure analysis means 21 by the database management system analyzes the stored procedure call relationship will be described with reference to FIGS. The case where the contents of each procedure are the same as those shown in FIG. 3 will be described.
<Proc1 first processing procedure>
As shown in FIG. 4, when a procedure name (procedure = Proc1) to be checked is given, this processing procedure depends from the dependency table 160 in order to check whether there is an object on which the procedure depends. A record (Proc1: Proc2 in row (1) in FIG. 3) which is a procedure in which an object is designated is acquired (step 201). If the record does not exist, the process ends. If it exists, the process proceeds to the next step 202 for examining the object as shown in the flow A1, and it is determined whether or not the subordinate object is a procedure (step 203). Since the procedure (Proc2) exists, the acquired dependency record is added to the call relationship table 32 (step 204: Proc1: Proc2 is added to the row a of the call relationship 32 in FIG. 2), and this time the dependency is added. The procedure (Proc2), which is the object to be processed, enters a recursive call that executes this algorithm (step 205).
[0024]
The above is the first process in Proc1, and in this first process, the definition contents of Proc1 (Proc1: Proc2) are acquired in step 201, the procedure (Proc2) is detected in step 203, and acquired in step 204 The operation is performed to add the dependency record (Proc1: Proc2 in row a) to the call relationship table 32. This call relationship table 32 stores a specific procedure as a parent node in association with other procedures or triggers executed for the procedure.
<First Proc2 Processing Procedure>
Now, as shown in FIG. 5, the recursive call in the step 205, given the procedure name (procedure = Proc2) to be checked, in order to check whether there is an object on which the procedure is dependent, A record that is a procedure in which a subordinate object is designated is acquired from the table 160 (step 201: Proc2: TableA in row (2) in FIG. 3). If the record does not exist, the process ends. However, since the record exists, the process proceeds to the next step 202 where the object is examined as shown in the flow C1, and it is determined whether or not the subordinate object is a procedure (step 203). ) Since the procedure does not exist, the process proceeds to the next step 206.
[0025]
In this step 206, it is determined whether or not it is a table, and since there is a table (TableA), the definition content of the subordinate object (procedure currently being examined) (the SELECT * FROM which is the content of Proc2 in FIG. 3). Table A) is acquired (step 207), and it is determined whether all tables have been checked (step 208). Then, it is determined whether this is a SELECT statement (step 209). Returning to step 201, the process returns to the step following step 205 of FIG.
<Second Proc1 Processing Procedure>
This processing procedure returns to FIG. 4. When a procedure name to be checked (procedure = Proc1) is given, in order to check whether or not there is an object on which the procedure is dependent, an object dependent from the dependency table is determined. A record that is a designated procedure (Proc1: TableA in row (2) in FIG. 3) is acquired (step 201). If the record does not exist, the process ends. If it exists, the process proceeds to the next step 202 for examining the object as shown in the flow B1, and it is determined whether or not the subordinate object is a procedure (step 203). Since this procedure does not exist, the process proceeds to the next step 206.
[0026]
In this step 206, it is determined whether or not it is a table, and since it is a table (TableA), the definition content of the subordinate object (procedure currently being examined) (: EXECUTE Proc2 / which is the content of Proc1 in FIG. 3) UPDATE TableA SET col1 = 10 / EXECUTE Proc3) is acquired (step 207), whether all the operations on the table are checked (step 208), further determined that it is not a SELECT statement (step 209), and to the table If the operation is SELECT, the trigger is not activated, so it is determined whether there is a trigger for the operation (step 210), the subordinate object is replaced with the trigger and added to the relation table (step 211) I do. This flow is like flows B1-B-B2. The specific processing of step 211 is the addition of row b (Proc2: TriggerA_UPD) of the call relation table 32 in FIG.
<Proc1 Third Processing Procedure>
In this processing procedure, as shown in FIG. 6, when a procedure name (procedure = Proc1) to be checked is given, in order to check whether there is an object on which the procedure is dependent, an object subordinate to the dependency table is checked. Is acquired (Proc1: Proc3 in row (3) in FIG. 3) (step 201).
[0027]
If the record does not exist, the process ends, and since the object exists, the process proceeds to the next step 202 for examining the object as shown in the flow E1, and it is determined whether or not the subordinate object is a procedure (step 203). Since the procedure (Proc3) exists, the acquired dependency record is added to the call relationship table (step 204: Proc1: Proc3, which is line c of the call relationship table 32 in FIG. 3), and this time the dependency is added. For the procedure (Proc3), which is an object to be executed, a recursive call for executing this algorithm is entered (step 205).
<Proc3 first processing procedure>
As shown in FIG. 7, when the procedure name to be checked (procedure = Proc3) is given, the recursive operation in step 205 is performed from the dependency table to check whether there is an object on which the procedure is dependent. A record (Proc2: TableA in row (4) in FIG. 3) which is a procedure in which the subordinate object is designated is acquired (step 201).
[0028]
If the record does not exist, the process ends. Since the record exists, the process proceeds to the next step 202 for examining the object as shown in the flow G, and determines whether or not the subordinate object is a procedure (step 203). Since this procedure does not exist, the process proceeds to the next step 206.
[0029]
In this step 206, it is determined whether or not it is a table, and since there is a table (TableA), the definition contents of the subordinate object (procedure currently being examined) (EDLETE FROM TableA which is the contents of Proc3 in FIG. 3) / WHREE col1 = 30 / SELECT * FROM TableB) (step 207), whether all the operations on the table have been checked (step 208), further determined that it is not a SELECT statement (step 209), and the table If the operation to SELECT is SELECT, the trigger is not activated, so it is determined whether there is a trigger for the operation (step 210), and the subordinate object is replaced with the trigger and added to the relation table (step 211). And do. This flow is like flows G1-G-G2. Further, the specific processing of the above 211 is the addition of the row d (Proc3: TableA_DEL) of the call relation table 32 in FIG.
[0030]
In this embodiment, as the second and third processing procedures of Proc3, the same operation as described above is performed according to the process flow H1 to H2 and I1 to I2 in FIG. However, since it is the same as that of the above-mentioned operation | movement here, description is abbreviate | omitted.
<Display processing>
In the database management system to which the stored procedure call relationship diagram display method according to the present embodiment is applied, the stored procedure analysis unit 21 executes the processing flow shown in FIGS. And a stored procedure call relation diagram as shown in the left side of FIG. 2 or FIG. 1 is displayed based on the call relation table 32.
[0031]
That is, in the present embodiment, when the call relationship having the Proc1 procedure of the database in which the database object of FIG. 3 exists as a vertex is analyzed by the procedure shown in FIGS. When a call relation table is output and this call relation table 32 is expanded into a tree structure, it is possible to express a call relation between stored procedures including a trigger indicated by reference numeral 31.
[0032]
【The invention's effect】
As described above, according to the present invention, in the method for displaying a stored procedure call relationship in a database application using a stored procedure including a trigger, in particular, the dependency relationship between the trigger and the trigger call source is stored directly. It is possible to express the relationship of the stored procedure and accurately display the stored procedure call relation by identifying the actual trigger that is called by re-operation on the user's table. It becomes possible.
[Brief description of the drawings]
FIG. 1 is a diagram for explaining an overall configuration of a database management system to which a stored procedure call relation diagram display method according to the present invention is applied;
FIG. 2 is a diagram illustrating an example of a stored procedure call relation diagram and a call relation table displayed by the display method according to the present embodiment.
FIG. 3 is a diagram for explaining a data database management system for displaying a program structure according to the prior art.
FIG. 4 is a diagram showing a processing flow for analyzing a dependency relationship between a procedure, a table, and a trigger stored in a dependency relationship table according to the embodiment;
FIG. 5 is a view showing a processing flow for analyzing the dependency relationship according to the embodiment;
FIG. 6 is a diagram showing a processing flow for analyzing the dependency relationship according to the embodiment.
FIG. 7 is a view showing a processing flow for analyzing the dependency relationship according to the embodiment;
FIG. 8 is a view for explaining a program structure display method according to the prior art.

Claims (2)

Stored procedure storage means for storing definitions of a plurality of procedures, tables and triggers;
Stores dependencies with other procedures, tables, or triggers that are dependent on each procedure, dependencies with triggers that are dependent on each table, and dependencies on tables that are dependent on each trigger Dependency storage means;
Analyzing means for analyzing a dependency relationship between the procedure, table and trigger based on the stored contents of the dependency relationship storing means for any procedure stored in the stored procedure storage means;
Call relation storage means for storing the call relation between the procedure, table and trigger analyzed by the analysis means;
In a stored procedure database management system comprising display means for displaying a call relation between the procedure, table and trigger stored in the call relation storage means in a tree structure,
The analysis means executes a first step of storing, in the call relation storage means, a call relationship in which the procedure is a parent node and the subordinate procedure is a child node for the procedure subordinate to the procedure. Recursively performing the first step on the procedure;
For a table subordinate to the procedure, refer to the definition contents of the table stored in the stored procedure storage means, and if there is a trigger corresponding to the operation on the table, the procedure is set as a parent node and the trigger is set. A database management system for stored procedures, wherein a second step of storing a calling relationship as a child node in a calling relationship storage means is executed. A method for displaying stored procedure call relationships,
Stored procedure storage means for storing definitions of a plurality of procedures, tables and triggers;
Stores dependencies with other procedures, tables, or triggers that are dependent on each procedure, dependencies with triggers that are dependent on each table, and dependencies on tables that are dependent on each trigger Dependency storage means;
Analysis means for analyzing a dependency relationship between a procedure, a table and a trigger based on the stored contents of the dependency relationship storage means;
Call relation storage means for storing the call relation between the procedure, table and trigger analyzed by the analysis means;
In a stored procedure database management system comprising display means for displaying a call relationship among procedures, tables and triggers in a tree structure,
For any procedure stored in the stored procedure storage means,
The analysis means executes a first step of storing, in the call relation storage means, a call relationship having the procedure as a parent node and the dependent procedure as a child node, for the procedure subordinate to the procedure. Recursively performing the first step on the procedure;
For the table subordinate to the procedure, the analysis means refers to the definition content of the table stored in the stored procedure storage means, and if there is a trigger corresponding to the operation on the table, the procedure is regarded as a parent. Executing a second step of storing the call relationship in the call relationship storage means with the node as a child node,
A method of executing a third step of displaying, by the display means, a call relation between the procedure, table and trigger stored in the call relation storage means in a tree structure.

Images