JPH10260992A - Database processor and storage medium - Google Patents

Abstract

PROBLEM TO BE SOLVED: To easily cope with a database even when the structure of the database is changed by providing a model retrieval sentence storing part which stores a retrieval sentence of a database that includes variable and a means which replaces the variable of a retrieval sentence which belongs to a model retrieval sentence storing part and corresponds to a retrieval item with a value that is inputted by a person who retrieves and generates a retrieval sentence. SOLUTION: An input form storing part 40 stores an HTML(hypertext markup language) document. When clients 6 and 7 make a retrieval request, an HTML document in an input form into which a stored retrieval condition is inputted is sent to the clients 6 and 7. A concrete structure with a model retrieval sentence storing part 30 stores a model SQL sentence that corresponds to a retrieval item, the model SQL sentence has variable, and a retrieval sentence is generated by replacing the variable with the value with which a retrieval sentence generating means 13 is inputted by the clients 6 and 7. Thus, execution efficiency is improved.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

[0001] 1. Field of the Invention [0002] The present invention relates to an application system for retrieving a database and support for developing the application system.

[0002]

2. Description of the Related Art In a conventional application system for searching a database, a program for generating a search sentence for searching a database based on a search instruction input according to an input format is closely related to the structure of the database. Was. For example, as a search sentence for searching a database, SQL (STRUCTURE)
ED QUERY LANGUAGE), there is a search sentence as follows.

[0003] SELECT data item name C FROM
Table name X WHERE Data item name A = Item value a OR Data item name B = Item value b In the above example, among records in the table X,
Item A has value a, or item B has value b
Indicates that the item name C of the record is extracted.

In this example, when data item B of table X is transferred to another table Y (including item name C), SELECT data item name C FROM table name X WHERE data item name A = item Value a UNION SELECT DISTINCT data item name C FROM table name Y WHERE data item name B = item value b. Therefore, it is necessary to modify the program that generates the search sentence.

Also, in order to reduce program modifications,
It is conceivable to store the relationship between the table name and the data item name stored in the table name, and to generate a search statement every time based on the contents, but in that case, the program becomes complicated and Its execution efficiency is also reduced.

[0006]

As described above, in the prior art, when the configuration of the database was changed, the program for generating the search command had to be modified. In addition, in order to reduce the modification of the program, the relationship between the table and the data items is stored, and the relationship is checked every time a search statement is generated.
Execution efficiency decreases.

An object of the present invention is to construct an application system for searching a database which can easily cope with a change in the configuration of the database and suppresses a decrease in execution efficiency.

[0008]

SUMMARY OF THE INVENTION The above object is achieved by a template search statement storage unit that stores a database search sentence including a variable corresponding to a search condition of a database search condition, The problem is solved by providing a search sentence generating means for generating a search sentence by replacing a variable of the search sentence corresponding to the search item with a value input by the searcher.

[0009]

FIG. 1 is a diagram showing a system configuration according to an embodiment of the present invention. 1 is a search server (processing device), and 6 and 7 are clients (terminal devices) connected to the 1 search server. Reference numeral 20 denotes a database, reference numeral 30 denotes a template search unit storage unit for storing a template of a search sentence for searching the database, and reference numeral 40 denotes an input form storage unit for storing an input form for inputting search conditions of the database.

Reference numeral 11 denotes a template search sentence registration unit which is a processing unit for registering a template search sentence in the template search sentence storage unit, 12 denotes communication means for communicating with a client requesting a search, and 13 denotes a communication unit. This is a search sentence generating means which is a processing means for generating a search sentence for searching a database in accordance with a condition input from a client. 14
Is a database search means for searching the database 20 according to a search sentence. Reference numerals 61 and 71 denote processing means having a function of receiving and displaying information from another processing device via a communication line, and transmitting predetermined information to another processing device. Is a program having an Internet WWW (World Wide Web) browser function.

FIG. 2 is an overall processing flow in an embodiment of the present invention. The overall movement will be described with reference to FIG.
First, in the search server 1, the administrator of the search server 1 creates an input form for inputting search conditions in advance and registers it in the input form storage unit 40. The template search sentence corresponding to the condition (search item) input in the created input form is registered in the template search sentence storage unit 60 using the template search sentence registration unit 11 (100). The registration of the template search sentence may be performed using a general editor.

The input form storage unit 40 stores an HTML document (HyperText Mark) as shown in FIG.
Up Language) is stored, and when a search request is received from the clients 6 and 7, an HTM of an input form for inputting search conditions stored therein is input.
The L document is transmitted to the clients 6 and 7. The specific structure of the template search sentence storage unit 30 stores a template SQL sentence corresponding to a search item as shown in the data structure of the template SQL table in FIG. The statement has a variable (": KWS")
And “: KWE” are variables), which will be described in detail later. The search sentence generating unit 13 replaces the variables with values input from the clients 6 and 7 to generate a search sentence.

In the example of FIG. 4, the search item “1” is a search item “title / title”, and the search for “title / title” is performed using the model S stored in the right column.
This is performed using a QL statement. The search item “2” is a search item “author name”, and the search for “author name” is performed using the template SQL sentence stored in the corresponding right column.

Before explaining the registration of the template SQL statement,
With reference to FIG. 5, the relationship between the search items in the present embodiment and the data items on the database will be described. As search items to be specified by the operators of the clients 6 and 7, for example,
The user is allowed to select “Book title / Title” or “Author name”. However, the search item selected by the operator is associated with a plurality of data items in the actual database, and the plurality of data items are separated. It may be stored in a table.

For example, a search item "title / title" is stored in the index table-A on the database.
Are associated with the data item "Title", the data item "Title", and the data item "Title" in the index table-B. As mentioned above, for one search item,
A plurality of data items on the database 30 may be related, and an SQL statement for performing a search for all the related data items is registered. At this time, a search input from the clients 6 and 7 is performed. A keyword (for example, a search request for searching for a document containing the keyword “mountain” as “document / title”) for the item is registered as a variable. In FIG. 4, the variable is “: K
WS "and": KWE ".

In FIG. 2, the step 100 is a preparation stage for operation of the search system, and the next steps 200 to 700 are processing of the actual operation of the search system.

First, upon receiving a database search start request from the clients 6 and 7, the communication means 12 of the search server 1 transmits an input form for inputting search conditions to the corresponding clients 6 and 7 (200). ). In addition,
In this embodiment, since the search is performed on the WWW on the Internet, the input form is an HTML document as shown in FIG.

In the clients 6 and 7 which have received the HTML document as the input form, the browsers (processing means 61 and 71) display an input screen of the search condition as shown in FIG. 6 according to the received HTML document. In the screen of FIG.
Numerals 1 and 71 are displayed based on the HTML document 320 of FIG. At 320, selection items such as “title / title”, “magazine name”, “author name”, and “volume” are registered. When the operator of the client 6 or 7 designates the right end of the 321 column, , Browser 61, 71
Displays this selection item, and when the operator selects one of them, the browsers 61 and 71 display the selected item in a column 321. The example of FIG. 6 shows a case where “Document / Title” is selected. At the same time, the browsers 61 and 71 store the value of the selected item (the value “1” corresponding to “document / title”) as the value of “search item-1”.

A field 331 in FIG. 6 is an area for inputting a search term to be searched for in the search item previously selected from the selection item 321 by the operator.
Are displayed by the browsers 61 and 71 based on. When the operator inputs a search word in this area 331, the browsers 61 and 71 display the contents in this field, and store the input value (search word) as the value of "keyword-1". .

The column 341 in FIG. 6 is processed based on the HTML document 340 in FIG. 3, similarly to the column 321. The column 351 in FIG. 6 is processed based on the HTML document 350 in FIG. 3, similarly to the column 331.

The “search” button 361 in FIG.
The browsers 61 and 71 display the content based on the HTML document 360 of FIG. 3. After the operator specifies a search item and inputs a search word, the “search” button 3 is displayed.
When the user designates 61, the browsers 61 and 71 transmit the stored contents to the search server 1 (400).

The stored contents are transmitted to the search server 1 as parameters of the program “kensaku” 310 in FIG. That is, in the search server 1, “/ cgi-bin / kensaku? Search item-1 =“ selected item ”& keyword-1 =“ inputted search word ”& search item-2 =“ selected item & Keyword-1 = “input selected word”....

The communication means 12 of the search server 1 receives the program name and its parameters from the browsers 61, 71 of the clients 6, 7. Then, the received program is started, and the parameters received at the same time are input to the program. The started program generates a search sentence for searching the database based on the parameters. The search sentence is generated by the search sentence generating means 13 based on the parameters and the contents of the previously registered template SQL table 30 (at step 100) (500).

The details of the processing in step 500 will be described with reference to FIG. In this embodiment, as described above, a plurality of search items and search words can be specified. First, whether the first search word (ie, “keyword-1”) is blank or not is determined. (51
0). If it is blank, the process proceeds to the step of determining whether there is a next search word without determining the value of the corresponding search item (that is, “search item-1”).

If the search term is not blank, the contents of the corresponding search item ("search item-1") are extracted (520).
In the above example, “search item-1” stores a value “1” corresponding to “book title / title”. Next, the template SQL table 30 is searched based on the value of the retrieved search item, and the corresponding template SQL statement is retrieved (530). In this case, since the value is “1”, the template SQL statement on the first line is extracted.

The variables (“: KWS” and “: KWE”) of the extracted template SQL statement are replaced with the corresponding search term (the value of “keyword-1”) (540).
In this embodiment, since a search for a prefix match is performed,
A search term with “hexadecimal 00” added to the corresponding data item length and a search term with “hexadecimal FF” added are stored respectively. If you want to search for an exact match, use "(contents BETWEEN: KWS AND: KW
E) ”is registered in this table as“ content =: KWD ”, and when a search sentence is generated, the“: KWD ”portion is directly replaced with a search word.

Next, it is determined whether or not there is a previously generated SQL statement.
(New SQL sentence) is stored in the work area. Also, if there is a previously generated SQL sentence, the replacement of the variable with the search term this time is an additional condition.
It is added to the previously generated SQL statement (550).

FIG. 8 shows this. 552
Indicates that the value of the search item in the template SQL table is "1"
("Title / Title"), and 554
Means that the value of the search item in the template SQL table is "2"
("Author name"). 511 "SE
LECT * FROM bibliographic master WHERE bibliographic number IN () indicates that all data of bibliographic numbers matching the conditions corresponding to the search item values "1" and "2" are (*
Indicates all items) from a database called a bibliographic master. Also, 553 “I
NTERSECT ”is a condition for the value“ 1 ”of the search item,
This is a sentence indicating an AND condition of the condition of the value “2” of the search item.

Next, it is determined whether or not there are any search words (keywords) that have not yet been processed.
Move on to step 10. If all the search terms have been processed, the SQL sentence currently stored in the work area becomes the search sentence used for searching the database (560). The database search means 14 searches the database 20 using the generated SQL statement (600).

The search result is transmitted to the clients 6 and 7 which have issued the search request (700).

[0031]

As described above, according to the present invention, even if the configuration of the database is changed, the template SQL table registered in the template SQL table is changed based on the changed configuration of the database. You do not need to modify the program.

Further, even if the configuration of the database is not changed, and even if a new search item is to be added, an input form corresponding to the new search item is created, and the search item and the data item are added. It is only necessary to register a template SQL sentence indicating the relationship in the template SQL sentence, and it is not necessary to modify the program. Further, since a search sentence can be generated only by replacing variables, the execution efficiency is improved.

[Brief description of the drawings]

FIG. 1 is a system configuration diagram according to an embodiment of the present invention.

FIG. 2 is an overall processing flow in an embodiment of the present invention.

FIG. 3 shows an example of an HTML document of a search condition input form.

FIG. 4 is a data structure of a template SQL table.

FIG. 5 is a diagram showing a relationship between a search item and a data item.

FIG. 6 shows an example of a search condition input screen.

FIG. 7 is a processing flow for generating a search sentence;

FIG. 8 shows an example of a generated SQL statement.

[Explanation of symbols]

 DESCRIPTION OF SYMBOLS 1 Search server 6, 7 Client 11 Model search sentence registration means 12 Communication means 13 Search sentence generation means 14 Database search means 20 Database 30 Model search sentence storage section 40 Input form storage section 61, 71 Processing means

Claims (4)

[Claims] 1. A template search sentence storage unit for storing a search sentence of a database including a variable corresponding to a search item of a search condition of a database, and a variable of the search sentence corresponding to the search item in the template search sentence storage unit. And a search sentence generating means for generating a search sentence by replacing a search term with a value input by a searcher. 2. When a plurality of values corresponding to a plurality of search items are input, the search sentence generation means combines a plurality of search sentences corresponding to each search item to form one search The database processing device according to claim 1, wherein the database processing device generates a sentence. 3. The database processing apparatus according to claim 1, wherein one search item relates to a plurality of types of data items of the database. A computer is operated to store, in a template search sentence storage unit that stores a search sentence of a database including a variable, corresponding to a search item of a search condition of a database, a variable of the search sentence corresponding to the search item. And a storage medium storing a program for causing a search sentence generating means to generate a search sentence by replacing the search sentence with a value input by a searcher.