JP2993530B2 - Sentence generator - Google Patents

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a sentence generating apparatus for automatically generating a sentence for retrieving data from a database. In a search system using a database, a user can perform a complicated search only by a simple designation. The present invention relates to a sentence generation device.

[0002]

2. Description of the Related Art A general relational database performs a search, a registration, and the like in a query language (SQL). To create an SQL sentence, you need to use SQL grammar,
In addition, it is necessary to know the table structure in the database and the join relationship between a plurality of tables. For this reason, users of the relational database are limited to those who have specialized knowledge, and ordinary users request this expert to create an SQL sentence and perform a search or the like.

Some database search systems that use a relational database automatically generate SQL statements (for example, Japanese Patent Application Laid-Open No. 63-31153).
No. 1), but the join relationship between a plurality of tables in a relational database that can generate SQL statements is limited to simple ones, and SQL statements are automatically generated and searched only between them. .

[0004]

Recently, SQ, which is a query language of the relational database described above, is used.
Although L has been standardized, there is a problem in that when searching is performed using this standardized SQL, the user is required to have specialized knowledge. In addition, the aforementioned S
A system that automatically generates a QL sentence has a problem in that the operability of the user is improved, but the structure of the target relational database is limited. In order to solve this, it is required to automatically generate an SQL statement and the like including a join relationship even for a relational database having a complicated structure.

The present invention automatically generates an SQL sentence or the like in a database search language including a join relationship between a plurality of tables, and automatically generates an SQL sentence or the like of a relational database having a complicated structure with only simple operations. It is intended to be searchable.

[0006]

Means for solving the problem will be described with reference to FIG. In FIG. 1, the database information 3 is registered in advance by assigning a unique number to a pair of a table name of the database 7 and an item name of the table.

The join stream 4 is provided for each join stream by extracting a join relationship between unique numbers registered in advance in the database information 3. The sentence generation unit 5 generates a sentence (for example, an SQL sentence) for searching the database 7 with reference to the database information 3 and the join stream 4 in accordance with the input of the search condition, the relational operator, and the retrieval order. .

[0008]

According to the present invention, as shown in FIG. 1, database information 3 and join stream 4 are generated in advance,
In response to the input of a search condition, a relational operator, and a retrieval order from a search screen or the like, the sentence generator 5
Further, a search sentence for searching the database 7 with reference to the join stream 4 is generated. At this time, as a sentence for search, the sentence generation unit 5 sends the select clause and the parameter, the from clause and the parameter, and
An SQL statement including a re clause and parameters is generated.

Therefore, a search sentence such as an SQL sentence of the database 7 having a complicated structure can be automatically generated only by a simple operation of designating a search condition, a relational operator, and a retrieval order from a search screen or the like. . Then, the database 7 is searched using the automatically generated search sentence such as the SQL sentence.

[0010]

Next, the configuration and operation of an embodiment of the present invention will be sequentially described in detail with reference to FIGS.

In FIG. 1, a storage device 1 stores data, programs, and the like. Here, it stores screen information 2, database information 3, a join stream 4, a sentence generation unit 5, and the like.

The screen information 2 is various information displayed on the screen of the display device 8. The database information 3 is information for searching the database 7, and is obtained by assigning a unique number to a pair of a table name and an item name in the table (to be described later with reference to FIG. Do).

The join stream 4 includes a database 7
And manages the join streams, for example, the join streams 1, 2, and 3 shown in (b) of FIG. 2 (described later using (b) of FIG. 2).

The sentence generator 5 automatically generates a search sentence, here an SQL sentence, based on a search condition, a relational operator, and a retrieval order specified by a user on a search screen.

The processor 6 executes various processes based on the programs stored in the storage device 1, here, the processes of the sentence generation unit 5 according to the programs stored at the positions of the sentence generation units 5. .

The database 7 is a relational database, and has a table name (for example, (a) in FIG. 2).
The storage location of the material, the keyword, and the employee) and the item names (for example, the material number and the name of the storage shelf) in the table are stored in a searchable form.

The display device 8 displays a search screen and the like. The keyboard 9 is used for key input of a search condition, a relation identifier, a takeout order, and the like from a search screen or the like.

First, data representation by a join stream will be described with reference to FIG. FIG. 2A shows an example of a table in the database (relational database) 7. Here, the table name is “data storage destination”,
“Keyword” and “Employee”.

The item names are "material number" stored in the table name "material storage destination", "storage shelf name" and the like. Here, the join represents a join relationship among three table names “material storage destination”, “keyword”, and “employee”.

A join stream is a name that groups joins between tables. For example, the join stream 1 is a group name of only the join A here, and has a join relationship with "material storage destination" -join A- "keyword" as shown in the figure.

The join stream 2 is a group name of the join A and the join B in this case. As shown in the figure, a join relationship between “material storage destination”, “join A”, “keyword”, “join B”, and “employee” is shown. Have.

Here, the join stream 3 is a group name of the join A and the join C, and as shown in the drawing, a join relationship with "material storage destination" -join A- "keyword" -join C- "employee" With

Here, a method of defining a join stream will be described below. (1) Find all join relationships between tables in the database 7 and express them as shown in FIG.

(2) The path of the join is examined, and the joins are concentrated as in the table name “data storage destination”, and one table name that starts the join path is found. The table that is the starting point (vertex) of the join route found here is defined as the main table.

(3) Check all join paths from the main table and group them. At this time, when two or more join paths branch from one table name “keyword” as in the case of Join B and Join C in FIG. 2A, the join paths to that table are grouped as one group. (Refer to the join stream 1 in (b) of FIG. 2 described later).

FIG. 2B shows an example of expressing data by a join stream. Here, database information 3
Is obtained by assigning a unique number to a pair of a table name and an item name in the table.

As the join streams 1, 2, and 3, one group of joins obtained as described in (1), (2), and (3) of FIG. The join relationship is represented by unique numbers in the database information 3 of the join source and the join destination. For example, the join stream 1 is “material storage destination” -join A- “keyword” as described in (a) of FIG. 2, so that the join source is numbered as related to the database information 3 by a dotted line. “1” and the join destination are number “5”.

Similarly, the join stream 2 is, as described in FIG. 2A, "material storage destination" -join A
-"Keyword" -Join B- "Employee", so the join source is number "1", the join destination is number "5", the join source number is "7", and the join destination is number "11". . Here, the table name "keyword"
If there is more than one join relationship such as the table name "Employee", an alias is given to the join destination table name, here "Create employee" so that the join destination table name can be identified. deep.

Similarly, the join stream 3 is as shown in the figure. Here, since there are two or more join relationships, an alias, "updated employee", is given to the join destination table name here so that the join destination table name can be identified.

With the above processing, the database information 3 and the join stream 4 according to the present invention are created in advance. Next, a procedure for automatically generating an SQL sentence in response to a user inputting a search condition, a relational operator, and a retrieval order from the search screen will be sequentially described in detail with reference to FIGS.

FIG. 3 is an explanatory diagram of input information from a search screen according to the present invention. Here, the search item name is displayed on the search screen, and is a number representing the item name of the search target in the database information 3 of FIG. Represents the item name of the search target corresponding to.

The search condition, relational operator, and retrieval order are as follows:
This is a field for the user to enter a key on the search screen. Here, key input is performed as shown. "1" in the order of taking out the "storage shelf""2" in the order of taking out the "storage drawer""3" in the order of taking out the "storage binder""X%" (where X% is X) Starting character string) and the relational operator "like""Keyword1" search condition "K" and the relational operator "=" (representing equality) "creating department name" search condition ""A", search condition "B" of relational operator "=", "creator section name", search condition "C" of relational operator "=", "updater section name", and relational operator " The number of the database information 3 and the number of the join stream 4 are information held internally in association with the search item name, and the database information of FIG. 3 unique number and joins dream Which is a number.

As described above, when the user makes a key entry as shown in the search condition, relational operator, and retrieval order fields in association with the search item name displayed on the search screen, the sentence generation shown in FIG. The part 5 is an SQL of FIG.
Generate statements automatically. Then, when the SQL statement of FIG. 4 is executed, the database 7 of FIG. 1 is searched, and FIG.
The search result of 0 is displayed. This will be described sequentially below.

First, a standardized SQL statement automatically generated will be described with reference to FIG. In FIG. 4, SQL
The statement is expressed by a select clause + parameter from clause + parameter where clause + parameter.

For the parameters of the select clause, "table name.item name" is automatically created in the order of retrieval by key input in FIG. Here, three sets are automatically created as shown (described later using the flowchart of FIG. 6).

As the parameters of the “where” clause, “table name.item name relational operator 'search condition'” is automatically created based on the search conditions and relational operators keyed in FIG. 3 (flow chart in FIG. 7). Will be described later). If there is an alias, this alias takes precedence as the table name.

For the parameters of the from clause, all table names to be searched are created based on the search conditions and relational operators entered in FIG. 3 (if an alias exists, the alias is also created together). (This will be described later with reference to the flowchart of FIG. 8).

"Table name alias" By the above procedure, when the search conditions, relational operators and retrieval order inputted from the search screen in FIG. 3 are performed, the SQL sentence in FIG. 4 is automatically created.

FIG. 5 is a flowchart illustrating the overall operation of the present invention. In FIG. 5, S1 displays a search screen (list of search items). This displays a search screen provided with, for example, fields of the search item name of FIG. 3, a search condition, a relational operator, and a retrieval order on the display device 8 of FIG.

In step S2, the user inputs a search condition, a relational operator, and a retrieval order. For example, the user performs a key input as shown in the search condition, relational operator, and retrieval order input fields shown in FIG. 3 in S1.

S3 is a select list (select)
Clause parameters). This select list specifies the order in which search results are retrieved from the database, and is created as a parameter of the select clause of the SQL sentence of FIG. 4 by the procedure of the flowchart of FIG. 6 described later.

In step S4, a bind list (where clause parameter) and a bind join list (where clause parameter) are created. The bind list specifies a search condition, and the bind join list specifies a join condition in the bind list. The “where” clause of the SQL statement in FIG. Create as a parameter of.

In step S5, a table list (from clause parameters) is created. This table list specifies a table to be searched, and a list of all tables to be searched (alias table together with an alias table, if any) is added to the list in the from clause by the procedure of the flowchart of FIG. Create as a parameter.

In step S6, the SQL sentence (select sentence) is completed. This is performed by the procedure of the flowchart of FIG.
SQL by arranging parameters, where clause + parameters
Create a statement.

In step S7, an SQL statement (select statement) is executed, and the corresponding data is retrieved from the database 7. In step S8, a search result is displayed. This displays a search result as shown in FIG. 10 described later.

FIG. 6 shows the select list (se
4 shows a flowchart for creating a parameter of a select clause. This shows the detailed procedure of S3 in FIG. 5, and creates a parameter that specifies the order in which search results are retrieved from the database 7. In order to simplify the description, DB number: database number JS number: join stream number DB information: database information 3 JS information: join stream 4

In step S31, the DB number and the JS number with the priority of the take-out order are taken out. This means that, for example, the priority of the retrieval order input on the search screen in FIG. 3, the first DB number “2” and the JS number “1” are retrieved first.

In step S32, the table name and the item name of the DB number are extracted from the DB information. At this time, from the JS information,
If there is a table alias of the JS number, it is preferentially taken out as the table name. For example, based on the DB number “2” extracted in S31, the table name “material storage destination” and the item name “storage shelf” are extracted from the (b) database information 3 of FIG. At this time, reference is made to the join stream 1 in (b) of FIG. 2 based on the JS number “1” extracted in S31, but here nothing is performed because the alias table is not set.

In step S33, "table name. Item name" is set as a parameter of the select clause (for example, "material storage destination. Storage shelf"). For example, “material storage destination. Storage shelf” shown in FIG. 4 is created, and designation is first made to retrieve from the database 7 as a search result.

A step S34 decides whether or not it is the end. YE
In the case of S, the process ends. In the case of NO, S31 to S33 are repeated for the next priority order, and as a result, the first time: “material storage destination. Storage shelf” in FIG. 4 is created for the takeout order “1” in FIG. I do.

Second time: For the take-out order “2” in FIG. 3, “material storage destination. Storage drawer” is similarly created. Third time: For the extraction order “3” in FIG. 3, “material storage destination. Storage binder” is similarly created.

As described above, when the user specifies the retrieval order of the retrieval results on the retrieval screen of FIG. 3, the retrieval of the retrieval results is specified as the parameter (select list) of the select clause in FIG. Following this, they are created in the order of extraction.

FIG. 7 shows the bind list (wh
4 shows a flowchart for creating a bind clause (a parameter of a where clause) and a bind join list (a parameter of a where clause). This shows the detailed procedure of S4 in FIG.

In S41, the following retrieval is performed although the search condition is set. -Search condition (for example, "X%")-Relational operator (for example, "like")-DB number (for example, DB number "6")-JB number (for example, JS number "1") Number table name. Retrieve the item name. At this time, if there is a table alias of the JS number from the JS information, it is preferentially extracted as a table name. For example, based on the DB number “6” extracted in S41, the table name “keyword” and the item name “material name” are extracted from the (b) database information 3 of FIG. At this time, based on the JS number “1” extracted in S41, FIG.
Referring to the join stream 1 of (b), no operation is performed here because no alias table is set.

In step S43, a bind list ("table name. Item name relational operator 'search condition'") is set as a parameter of the "where" clause (eg, "keyword. Material name like" X% "in FIG. 4). ”).

In step S44, a bind join list ("table name.item name = table name.item name") is set as a parameter of the "where" clause. ) Is found, and the list related to the join is described as “keyword. Document number = Data storage location. It is set as "material number" (FIG. 4).

In the step S45, it is determined whether or not the processing is over. YES
In the case of, the process ends. In the case of NO, S41 to S44 are repeated for the following, and as a result,
For the search item name “material name” in FIG. Similarly, as shown in FIG. 4, those having the same JS number are collectively created.

As described above, when the user specifies a search condition and a relational operator on the search screen of FIG.
The parameters of the re clause (the bind list and the bind join list) are created as shown for each JS number in FIG.

FIG. 8 is a table list (fr) according to the present invention.
5 shows a flowchart for creating an om phrase parameter). This shows the detailed procedure of S5 in FIG. S51 is sel
Parameter setting (creation) of ect clause and where clause
A table list (table name table alias) is set as a parameter of the from clause from the target table name and the table alias, if any. This is, for example, when the search condition “A” and the relational operator “=” are specified for the creator affiliation name in FIG. 3, and when the (B) join stream 2 in FIG. In this example, the table list "Worker Created Employee" is set as shown in FIG.

A step S52 decides whether or not it is the end. YES
In the case of, the process ends. In the case of NO, S51 is repeated for the following items. As described above, when the user specifies the search condition and the relational operator on the search screen in FIG. 3, all the table names targeted for the parameter setting of the select clause and the where clause and the table aliases, if any, are set. It will be created as a parameter of the phrase.

FIG. 9 shows an SQL statement (select) according to the present invention.
(Sentence) is shown below. This corresponds to S6 in FIG.
The detailed procedure will be described. In S61, an SQL sentence (select sentence) select clause + parameter from clause + parameter where clause + parameter is created. This is achieved by rearranging the parameters of the select clause created in FIG. 6, the parameters of the from clause created in FIG. 8, and the parameters of the where clause created in FIG. 7 in the order of the SQL sentence (select sentence). Create a statement.

FIG. 10 shows a display example of the search result of the present invention. This shows a specific display example of the search result in S8 of FIG. In step S71, a search result is displayed for the item of the table whose extraction order is specified. The display results displayed here are set from the search screen of FIG. 3 as shown, automatically create and execute the SQL sentence of FIG. 4, and display the results.

[0063]

As described above, according to the present invention,
A database information 3 for registering a pair of a table name of the database 7 and an item name in the table by assigning a unique number thereto, and a join stream 4 in which a join relationship is extracted are provided, and are specified by a user on a search screen. Since a configuration for automatically generating a sentence for searching the database 7 by referring to the database information 3 and the join stream 4 in response to the input of the search condition, the relational operator, and the retrieval order is employed, the search condition and the relation operation A search sentence such as an SQL sentence that can search the database 7 having a complicated structure can be automatically generated only by a simple operation of designating a child and a retrieval order. And this automatically generated S
By executing a search sentence such as a QL sentence, it becomes possible to search the database 7 and display the search result.

[Brief description of the drawings]

FIG. 1 is a configuration diagram of one embodiment of the present invention.

FIG. 2 is a data representation diagram using a join stream according to the present invention.

FIG. 3 is an explanatory diagram of input information from a search screen according to the present invention.

FIG. 4 is an example of an SQL sentence according to the present invention.

FIG. 5 is a flowchart for explaining the overall operation of the present invention.

FIG. 6 is a flowchart for creating a select list (select clause parameter) according to the present invention.

FIG. 7 shows a bind list (a parameter of a where clause) and a bind join list (a where clause) of the present invention.
9 is a flowchart for creating a parameter (re clause).

FIG. 8 is a flowchart for creating a table list (from clause parameters) according to the present invention.

FIG. 9 is a completed flowchart of an SQL statement (select statement) according to the present invention.

FIG. 10 is a display example of a search result of the present invention.

[Explanation of symbols]

 1: Storage device 2: Screen information 3: Database information 4: Join stream 5: Sentence generator 6: Processor 7: Database 8: Display device 9: Keyboard

──────────────────────────────────────────────────続 き Continuation of the front page (56) References JP-A-1-181125 (JP, A) JP-B-2-17815 (JP, B2) (58) Fields investigated (Int. Cl. ⁶ , DB name) G06F 17/30 G06F 12/00 512

Claims (2)

(57) [Claims] 1. A sentence generating apparatus for automatically generating a sentence for retrieving data from a database, wherein a unique number is assigned to a pair of a database table name and each item name in the table and registered.
A storage unit , extracting a join relationship between the unique numbers registered in the database information storage unit , a plurality of join streams provided for each stream of the join over a plurality of tables , a search condition, and a relationship. For the operator and the input of the extraction order,
A sentence generation device, comprising: the database information storage unit; and a sentence generation unit that generates a sentence for searching a database based on the plurality of join streams , and configured to output the generated sentence. 2. A sentence generation unit according to claim 1, wherein
2. The sentence generating apparatus according to claim 1, wherein an SQL sentence including an ect clause and parameters, a from clause and parameters, and a where clause and parameters including a plurality of join streams is generated.