JPH077350B2 - Relational database search processing method - Google Patents

Description

DETAILED DESCRIPTION OF THE INVENTION [Outline] The present invention performs an indefinite level search that depends on data content by making a view function recursive in query language processing such as SQL language for a relational database. It is possible.

[Industrial application field]

The present invention relates to a relational database search processing method,
In particular, the present invention relates to a relational database search processing method capable of easily performing so-called child part expansion and parent search expansion in a relational database.

In recent years, relational databases (relational databases) have been widely adopted as database systems that are easy for users to use.
In database systems, it is desirable to be able to extract the data required by the user from various types of managed data, not limited to relational databases, with the simplest possible specifications.

[Conventional technology]

Relational databases manage data in tabular form. For example, SQL language is known as a query language for this relational database. The SQL language allows you to specify a search in the following format.

SELECT (target column name) FROM (table name) WHERE (condition) In addition, the SQL language dynamically constructs a virtual table by extracting only the data that satisfies a predetermined condition from one or more tables. It has a view function.

However, in the view definition, the table that can be specified as the search target must be a table that has already been defined, and therefore the view definition name is not allowed to be used in the view itself.

[Problems to be solved by the invention]

 FIG. 4 is a diagram for explaining the problem of the present invention.

For example, consider data having a parent-child relationship as shown in FIG. P1 to P7 are data of parts, respectively, and P1 indicates that P2, P3, and P4 parts are used. P2 uses the parts of P3 and P5. For parts below P3, use other parts as well. If such part data is managed in a table format by a relational database, the table T2 (table name) shown in FIG.
PP). That is, A # represents a parent component and C # represents a child component. Column QTY is the quantity of child parts. Table T
1 (table name PARTS) is a table that stores attribute data for each part.

In such a table T2, when expanding child parts such as "what are all the child parts necessary for the configuration of P1?", P1 to P2, P3, and P4 are obtained, and P2 to P
3, P5 is obtained, P5 is obtained from P6, and so on. It is necessary to search the table over many levels. Since this level depends on the data contents and is indefinite, there is a problem that the search results cannot be easily obtained with the conventional query language system. For example, the same applies to the case of performing parent search such as "What are all the parent parts using P7?"

That is, in the case of a conventional network type database, the child part expansion and parent search expansion can be processed relatively easily, but in the case of a relational database, a special data structure is used. If special processing was not assembled individually, complete expansion of child parts could not be performed.

An object of the present invention is to solve the above problems and to provide a means for easily performing an indefinite level search without significantly changing the existing language system for a relational database.

[Means for solving problems]

 FIG. 1 shows a basic configuration example of the present invention.

In FIG. 1, 10 is an input unit for inputting search commands and view definition information, 11 is a query language processing unit, 12 is a syntactic analysis unit for analyzing the syntax of commands and the like, and 13 is the meaning of the syntactic analysis result. A semantic analysis unit, 14 is an optimization processing unit that performs scheduling to access the actual table, 15 is a code generation unit that generates a series of intermediate code or object code for accessing the table, 16 is a code execution unit, 17 Is a generated code generated by the code generation unit 15, 17A is an initial data generation unit, 17B is a synchronous buffering control unit, 17C is a search data generation unit, 18 is a view definition unit for registering view definition information, 19 is a database,
20 is a view definition information section for storing view definition information, 21A, 2
1B is a table for storing table format data, and 22 is a database access unit for inputting / outputting data to / from the database 19.

In the case of the present invention, the view definition unit 18 accepts a view having a recursive definition including a target view definition name as a search target on the view definition in the same manner as a normal view definition, and then the view definition information unit 20. Register above.

When a search command for a recursively defined view is input from the input unit 10, the query language processing unit 11
After the syntax is analyzed by the syntax analysis unit 12, the semantic analysis unit 13 refers to the definition information on the view definition information unit 20, the optimization processing unit 14 performs optimization, and the code generation unit 15 , Generate generated code 17. The generated code 17 may be an intermediate code that is executed in an interpreter format, or a compiler output that can be directly executed. In particular, the generated code 17 related to the present invention includes a recursive call related to search data generation, and the query result can be obtained substantially from the set sum of the search results obtained according to each nesting level. ing.

The code execution unit 16 interprets and executes the generated code 17, and accesses the database 19 via the database access unit 22.

[Operation] In the present invention, a recursive definition including a target view definition name as a search target is allowed on the view definition. Therefore, an indefinite level search that depends on the data content can be specified in advance by the recursive view definition, and the search command for that view can obtain the set sum of the search results according to each nesting level. it can.

〔Example〕

FIG. 2 is a conceptual diagram of processing by generated code according to an embodiment of the present invention, and FIG. 3 is an explanatory diagram of execution equivalent commands for explaining the operation of the embodiment of the present invention.

In the following embodiments, the SQL language, which is a well-known query language for relational databases, will be described as an example. The same can be applied to other query languages having the same view function.

For example, the table T2 (table name P shown in FIG. 4B)
For P), when obtaining a list of component parts by expanding child parts, the following view definition is performed in advance.

VIEW V (A #, C #, QTY) AS SELECT PP.A #, PP.C #, PP.
QTY FROM PP, V WHERE PP.C # = VA # This view definition is for table PP and view V, PP
It is shown that the view V is constituted by the columns A #, C #, and QTY of the PP selected by selecting the column C # of which is the same as the column A # of the V. In this view definition, the view definition name "V" is used in the FROM clause in the definition, so it is a recursive definition. Note that this definition is
S User does.

Here, suppose that you want to extract all the components that make up the component P1. In this case, enter the following search command.

SELEKCT * FROMV V WHERE PP.C # = VA # The query language processing unit 11 shown in FIG. 1 analyzes this search command and generates generated code 17 as shown in FIG. 2, for example.
To generate. The generated code 17 includes an initial data generation unit 17A, a synchronous buffering control unit 17B, and a search data generation unit 17C.

In the initial data generation unit 17A, the level is first set to "0" by processing, "P1" is specified as the search target item, and the synchronous buffering control unit 17B is called. The level indicates the depth of the recursive call.

In the synchronous buffering control unit 17B, the level and the search target item information are respectively set in the variable SYSLEVE by processing.
When received in L, P #, buffering control for synchronization is performed by processing, and variable SYSLEV is processed by processing.
Specify EL and P # and call the search data generator 17C.

Upon receiving the request from the synchronous buffering control unit 17B by the processing, the search data generation unit 17C declares the search target C1 by the processing. This C1 may be considered as a host interface used by the database access unit 22 shown in FIG. 1 to access the database 19. The processing specifies this C1 search.

The process instructs the process or the process to be repeated until there are no search results. By the processing, the actual database 19 is accessed and one P # is acquired. The obtained value of P # is output as one of the search results by the processing. For example, if there is a restriction on the depth of the level, SYSLEVEL is checked by the process, and the next process is skipped if necessary.

In the processing, the value obtained by adding 1 to the value of SYSLEVEL is set as a level, and the synchronous buffering control unit 17B is called with P # as a new search target item. Recursive will be realized by returning this data.

The process corresponds to the process and indicates the end of repetition. The process indicates the end of the search by C1.

By interpreting or executing the generated code 17 interpretively or directly into something like object code, the search command is substantially interpreted and executed as shown in FIG. Become. That is, the search command that specifies the view according to the recursive definition shown in FIG. 3 (a) is substantially executed with the search command UNION (at the beginning of the set) corresponding to each nesting level shown in FIG. 3 (b). The behavior and the result are equivalent.

In FIG. 3B, PP1, PP2, ... Are names designated for the level-corresponding search for the table PP, respectively. You can think that the number of UNIONs is infinite, and that a certain level or higher will always be the union of empty sets, or you can think that there are UNIONs up to a predetermined nesting level.

At level "0", P2, P3, and P4 are obtained as child parts of P1. At level "1", P2, P3, P4 which are child parts of P1 and their child parts P3, P5, P6, P7, etc. are obtained. The same applies to level “2” and level “3”, but the result is an empty set at level “4” because there are no child parts of the part P6. Therefore, the merger by UNION will be canceled. The set of search results obtained according to each nesting level obtained above is the child component expansion of the desired P1.

For example, in the case of parent search expansion such as "What are all the parts that use part P7?", Since the directions of start and search are different from child part expansion, the following view definition Should be used.

VIEW V (~) AS SELECT * FROM PP, V WHERE PP.A # = VC # The above parent search can be expanded by using the following search command for this view.

SELECT * FROM V WHERE VC # = 'P7' Note that this processing operation is the same as in the case of child part expansion, except for the search direction.

By the way, users may want to know the nesting level. In this case, the built-in function (SY
SLEVEL) can notify you.

"SELECT SYSLEVEL, A #, C #, ..." In addition, since the user mistakenly made a view definition, a view that is not originally recursive may be recursively interpreted and unexpected behavior may occur. . Since it is considered rare to use the recursive definition as in the present invention, the following keywords may be provided to distinguish it from the case of erroneous definition. "DEFINE RECURSIVE VIEW V (...)" In addition, in order to allow the user to control the depth of the search level, it is desirable to be able to set the following conditions, for example.

"WHERE SYSLEVEL <constant" If this condition is specified, the recursive call will be aborted when the level reaches the specified constant.

〔The invention's effect〕

As described above, according to the present invention, it is possible to perform an indefinite level of data-dependent retrieval in a query language for a relational database, and to easily perform child component expansion, parent search expansion, and the like. become able to.

[Brief description of drawings]

FIG. 1 is a basic configuration example of the present invention, FIG. 2 is a conceptual diagram of processing by generated code according to an embodiment of the present invention, and FIG. 3 is an explanation of an execution equivalent command for explaining the operation of the embodiment of the present invention. FIG. 4 and FIG. 4 show diagrams for explaining the problems of the present invention. In the figure, 10 is an input unit, 11 is a query language processing unit, 12 is a syntax analysis unit, 13 is a semantic analysis unit, 14 is an optimization processing unit, 15 is a code generation unit, 16 is a code execution unit, and 17 is a generated code. , 17A is an initial data generation unit, 17B is a synchronous buffering control unit, 17C is a search data generation unit, 18 is a view definition unit, 19 is a database, 20
Is a view definition information part, 21A and 21B are tables, and 22 is a database access part.

Claims (1)

[Claims] 1. A processing method in a database search processing system for processing a query to a relational database by inputting a query language having a function of extracting a data group meeting a certain condition, targeting a predefined view, The process of inputting and storing a recursive definition that further designates its own view as a search target in the view definition, and performing syntactic analysis and semantic analysis on the input of the search request in the query language, and the search request becomes the recursive When it is for a defined view, at least the code that generates the initial value of the level indicating the depth of recursive call and the search target item specified in the query language, and the level A buffer that stores search results in the middle And a code including a recursive call so that the query result can be obtained from the set sum of the search results obtained according to each level, and the database is accessed by executing the generated code. A relational database search processing method, which comprises a step of outputting a requested search result.