JPH11161665A - Data base processor and recording medium - Google Patents

Abstract

PROBLEM TO BE SOLVED: To allow even a beginner to speedily and efficiently retrieve data without erroneous setting by automatically connecting and condition-setting a table at the time of allowing a user to set the table name of a retrieving object and an input/output item, and retrieving/extracting a data base to output a result. SOLUTION: When an automatically connecting means 24 refers to a group table 15 corresponding to the designation of the table names of retrieving objects on a picture and the designated table names all belong to the same table, the means 24 refers to a connection table 16 in the pertinent group and fetches/ connects key items to automatically generate a retrieving condition. Then a retrieving means 25 retrieves to a data base based on the automatically generated retrieving condition to output its result. Namely, this device automatically connects and condition-sets tables and retrieve-extracts the data base to output the result by allowing the user only to set the table name of the retrieving object and an input/output item.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a database processing apparatus and a recording medium for generating an instruction for searching a database composed of a plurality of tables.

[0002]

2. Description of the Related Art In recent years, a search tool that builds a client-server system, searches a database managed by a server from a client (user terminal), and displays a result of the search has been widely used. .

[0003] At this time, each time the user searches and extracts desired data from the database, the user performs a setting operation for linking tables to be searched from the screen, a search condition setting, and an input / output item setting. The database was searched and the results (output items) were displayed.

[0004]

Therefore, the user must
Each time, it is necessary to set the connection of the table to be used on the screen, search condition setting, and input / output item setting,
For beginners without database knowledge, there is a problem in that the setting operation is burdensome, and a large number of search / extraction results are displayed or no result due to incorrect settings, resulting in poor usability.

[0005] The present invention solves these problems,
Just by setting the name of the table to be searched and the input / output items, the user can automatically connect and set the tables, search and extract the database, output the results, and even beginners with no knowledge of the database can make mistakes. It is an object of the present invention to realize a system capable of quickly and efficiently searching a database without setting and obtaining a desired result.

[0006]

Means for solving the problem will be described with reference to FIG. In FIG. 1, a usage table 14 is for registering key items in association with table names.

The group table 15 registers table names belonging to the group in association with the group number. The concatenation table 16 is for registering key items in association with group numbers.

The connection pattern table 17 registers a table name pattern connected between groups. The automatic linking unit 24 links and automatically generates a search condition in accordance with the designation of the table name to be searched.

The search means 25 is for searching. Next, the operation will be described. When the automatic connection unit 24 refers to the group table 15 and all the table names specified in the table correspond to the specification of the table name to be searched on the screen, the connection table 1 of the corresponding group
6, the key items are extracted and linked to automatically generate search conditions, and the search means 25 searches the database based on the automatically generated search conditions and outputs the result.

At this time, priorities are set for table names in the same group, key items are taken out and linked in order of priority, and search conditions are automatically generated. When the specified table name belongs to a plurality of groups, the automatic linking unit 24 refers to the linking pattern table 17 to extract the parent key item and the child key item from the use table 14 and link them to determine the search condition. Automatically generated, and the search means 25 searches the database based on the automatically generated search conditions and outputs the result.

[0011] Therefore, by simply setting the table name and input / output items of the search target, the user can automatically connect and set the conditions, search and extract the database, and output the result. Even a beginner who does not have knowledge of the above can realize a system that can quickly and efficiently search the database without erroneous settings and obtain a desired result.

[0012]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Next, embodiments and operations of the present invention will be sequentially described in detail with reference to FIGS.

FIG. 1 shows a system configuration diagram of the present invention.
In FIG. 1, a system management terminal 1 is used by a system administrator to perform various inputs, settings, and the like. In this embodiment, the system management terminal 1 includes a connection information registration unit 2 and the like.

When the user specifies a table to be searched on the screen, the connection information registration means 2 connects (joins) the specified tables to automatically generate search conditions. (Connection information such as the usage table 14, the group table 15, the connection table 16, the connection pattern table 17, and the connection table table 18).

The server 11 is composed of an RDBMS (Relational Database Management System) and various tables (tables such as 14 to 17) for performing registration / update / search / deletion of the database. It is.

The use table 14 is for registering key items in association with table names (to be described later with reference to FIG. 8). The group table 15 registers a table name belonging to the group in association with the group number (described later with reference to FIG. 9).

The link table 16 registers key items in association with group numbers (described later with reference to FIG. 10). The connection pattern table 17 registers a pattern of a table name to be connected between groups (described later with reference to FIG. 11).

The connection table 18 is a table in which the priorities of the tables in the group are registered. The user terminal 21 is operated by a user to search the database, and includes a table selecting means 22, an automatic connecting means 24, a searching means 25, and the like.

The table selecting means 22 selects a table to be searched from the screen. Automatic connection means 2
Reference numeral 4 is for automatically generating a search condition (for example, an SQL statement) by linking in accordance with the specification of the table name to be searched.

The search means 25 searches the database based on the search conditions (for example, SQL sentences) automatically generated by the automatic connection means 24 and displays the results. The details will be sequentially described below.

FIG. 3 shows a screen example (part 1) of the present invention. This screen is used to select one of the following processes shown in the figure. -Consolidation group registration (consolidation table 1 in FIG. 10 described later)
6) Registration of connection pattern (registration in connection pattern table 17 of FIG. 11 described later) Registration of connection table (registration in connection table table 18 of FIG. 12 described later) FIG. 3 is a screen example of the present invention. (Part 2) is shown. This is a screen that is displayed when the user selects “consolidated group registration” on the screen of FIG. 2. The following items shown in the figure are displayed on the screen. Group name: "household-related" of group number 1 in the group table 15 in FIG. 9-Key item:-Primary key item:-Secondary key item: Here, a consolidation group number and a consolidation group name are input.

FIG. 4 shows a screen example (part 3) of the present invention. This is a screen displayed when the consolidation group number and the consolidation group name are input on the screen of FIG. 3, and the following items shown in the figure are displayed on the screen. G1) ・ Consolidated group name: “household related” of group number 1 in group table 15 in FIG. 9 ・ Registration target table: ・ Selection of registered table: Here, one or a plurality of registration target tables are selected, and the lower row is selected. Select the "Register / Update" button and add it to the registered table.

FIG. 5 shows a screen example (part 4) of the present invention. This is a screen that is set following the operation of FIG. 4 and the like, in which the priorities in the group are set, and the following items shown in the drawing are displayed on the screen. Order: Priority of table name after change: Here, one of the table names before change is sequentially selected and arranged in a desired order in the table after change on the right side, and the priority of the tables in the group is set. I do.

3 to 5, the names of the tables belonging to each group and the priorities of the tables in the group are set, and the group tables 15 and 10 in FIG. Consolidation table 16
And the priorities of the tables in the group are set.

FIG. 6 shows a screen example (part 5) of the present invention. This is a screen displayed when the connection pattern registration is selected on the screen of FIG. 2, and displays the following items shown in the figure.

Consolidation pattern name: Parent group selection: Consolidation group Group name Consolidation key Key item confirmation Child group selection Consolidation group Group name Consolidation key Key item confirmation Consolidation key combination status Shows a screen example (6) of the present invention. This is a display of the connection pattern registration status on the screen of FIG. 6, and the following items shown in the figure are displayed.

Connection pattern registration status: Combination pattern: Connection pattern name: Combination status The connection pattern registration status in FIG. 7 corresponds to the connection pattern table 17 in FIG. In the row, there is a circle in the use groups 1 and 2, which is set to 1 in the position of the groups G1 and G2 in the second row in FIG. "Household + customer"). In detail, 1
G1 and G2 shown in FIG.
Referring to FIG. 9, the group names are household-related and customer-related, respectively, and are set as the connection pattern name "household-customer" (household-customer-related) in FIG.

Next, the combination status of FIG. The main = G1 sub is the group G2 (the main key items “store number. Customer number” of Nos. 3 to 5 in the use table 14 of FIG. 8 which is the target table of the group G2 of FIG. 9) and the group G1 of FIG.
N in the usage table 14 of FIG.
o. The first “store number.
Since the customer numbers are the same, G2.main = G1 sub is set.

FIG. 8 shows an example of a usage table according to the present invention.
As shown, the following items shown in the figure are registered in association with the table names. -Table name:-Primary key item:-Secondary key item: Assigned in order.

FIG. 9 shows an example of a group table according to the present invention. In this example, the target table of FIG. 8 is registered in the order of priority within the group, in association with the group number (group name) for the following items shown in the figure.

Group number Group name Object table priority order FIG. 10 shows an example of a concatenation table according to the present invention. This is the group number (group name) for the following items shown
The primary key and the secondary key are set in association with.

Group number: Group name: Primary key item: Secondary key item: FIG. 11 shows an example of the connection pattern table of the present invention. This is a setting of the following items shown in the figure, in which a connection pattern for connecting between groups is registered,
A group set to 1 indicates connection, and a group set to 0 indicates no connection.

No: connection pattern number G1 to Gn: group number Connection pattern name: A group name in which 1 is set and arranged with + added. Connection 1 to n: FIG. 4 shows an example of a table. this is,
The following items shown in the figure are set, in which table names, group numbers, and priorities within groups are set.

Table identification number: Table name: Group number: Priority within group: FIG. 13 shows a search example of the present invention.

Example 1 in FIG. 13A is a search example in a case where all the tables to be searched specified by the user are in the group. When selecting three tables of customer attribute, customer count, and customer base: From the contents of FIG. 12 (contents of the consolidation table 18), it can be seen that the group number is one of two (customer attributes,
Referring to the concatenation table table 18 in FIG. 12, both the customer count and customer base tables become the group number 2 and all belong to the same group.)

From the contents of FIG. 10 (contents of the concatenation table 16), the concatenation key in the group of the concatenation group number 2 and the priority in the group can be known. -When an SQL statement is created, it becomes the following shown in the figure.

WHERE Customer Attributes. Store number = customer count. Store number AND Customer attribute. Customer number = customer count. Customer number AND Customer attribute. Store number = customer base. Store number AND Customer attribute. Customer number = customer base. Customer Number (Sentence 1) To be more specific, referring to the concatenation table 18 in FIG. 12, the priorities of the tables in the group 2 are: customer attribute, customer count, and customer base. Here, the first table is the parent, the second is
If the third is child 1 and child 2, customer attribute (parent) customer count (child 1) customer base (child 2) According to (2) in the connection procedure described at the end of the description of FIG. 12, connection is performed using the primary key. That is, when the primary key items of each table are taken out from FIG. 8, the table name primary key item, customer attribute (parent) store number. Customer number ・ Customer count (child 1) Store number. Customer number ・ Customer base (child 2) Store number. Customer number. Therefore, when the primary key items of the parent and the child 1 are linked, the following is obtained.

Parent Primary Key Child 1 Primary Key Customer Attributes. Store number = customer count. Store number Customer attribute. Customer number = customer count. Similarly, when the primary key items of the parent and the child 2 are linked, the following is obtained.

Parent Primary Key Child 2 Primary Key Customer Attributes. Store number = customer base. Store number Customer attribute. Customer number = customer base. Customer number Therefore, when these are put together to create an SQL statement, WHERE customer attribute. Store number = customer count. Store number AND Customer attribute. Customer number = customer count. Customer number AND Customer attribute. Store number = customer base. Store number AND Customer attribute. Customer number = customer base. Customer number.

As described above, when all the specified tables belong to the group when the user specifies the table name, the tables are arranged in order of priority, the top is set as the parent, the second and the following are set as child 1, child in order. .., Parent and child 1, parent and child 2,..., And linked by their respective primary keys, it is possible to automatically generate sentence 1 (SQL sentence).

Example 2 in FIG. 13B is a search example in the case where the search target table specified by the user belongs to a plurality of groups. When selecting four tables of household attribute, customer attribute, customer count, and customer base, it is understood from the contents of FIG. 12 (contents of the consolidation table 18) that the two group numbers are 1 and 2 ( FIG. 12 shows a table of household attributes, customer attributes, customer count, and customer base.
Referring to the concatenation table table 18 of FIG. 6, it can be seen that the group numbers are 1 and 2 and that there are two.

From the contents of FIG. 10 (contents of the concatenation table 16), the concatenation keys in the consolidation group numbers 1 and 2 and the priority in the group can be known. From the contents of FIG. 11 (the contents of the connection pattern table 17), the connection pattern is the second one, and the connection key between groups can be known.

When an SQL statement is created, the following is shown in the figure. WHERE household attributes. Store number = customer count. Store number AND Household attribute. Customer number = customer count. Household number AND Customer attribute. Store number = customer count. Store number AND Customer attribute. Customer number = customer count. Customer number AND Customer attribute. Store number = customer base. Store number AND Customer attribute. Customer number = customer base. Customer number (sentence 2) To be more specific, the priorities of the tables in groups 1 and 2 can be found by referring to the concatenation table table 18 in FIG. 12, as follows: Group 1 Household attribute Group 2 Customer attribute Customer count Become a customer base. Here, assuming that the first table of the household attributes of group 2 in which a plurality of tables are specified in the group is the parent and the second and third are the children 1 and the children 2, group 1 household attribute group 2 Customer attributes (parent) ・ Customer count (child 1) ・ Customer base (child 2) According to (4) in the connection procedure described at the end of the description of FIG. 12, connection is performed using the primary key. That is, when the primary key items of each table are extracted from FIG. 8, the table name: primary key item, group 1, household attribute, store number. Customer number • Group 2 • Customer attribute (parent) Store number. Customer number ・ Customer count (child 1) Store number. Customer number ・ Customer base (child 2) Store number. Customer number. Therefore, when the key item of group 1 and the key item of group 2 (the key item set to be connected between groups in advance), and the main key items of parent and child 1 and parent and child 2 in group 2 are connected, Group 1 household attribute and Group 2 customer attribute (parent)
About household attributes. Store number = customer count. Store number Household attribute. Customer number = customer count. Household number ・ Parent and child 1 and parent and child 2 in group 2 Customer attribute. Store number = customer count. Store number Customer attribute. Customer number = customer count. Customer number Customer attribute. Store number = customer base. Store number Customer attribute. Customer number = customer base. Customer number. Therefore, if it is made into an SQL sentence, it becomes (Sentence 2).

As described above, when the user specifies a table name and the specified table belongs to a plurality of groups, the tables are arranged in order of priority, and when there are a plurality of tables in each group, the top is the parent. , And the second and subsequent ones are referred to as child 1, child 2,...
Combine the parent and child 1, parent and child 2 ... in the group, and connect them with their respective primary keys,
Sentence 2 (SQL sentence) can be automatically generated.

The procedure for automatic generation when a table is designated by the user is as follows. (1) If there is at least one table for which no group is defined, the search cannot be executed.

(2) When there is one group, the groups are linked by the primary key (primary key item) (the links are arranged in the order of priority within the group and linked as in Example 1 described above). (3) If the combination pattern of the group is not defined, the search cannot be executed.

(4) When a combination pattern of groups is defined, the following applies. (4-1) When there are a plurality of tables in the same group, the tables are linked by the primary key (linking is performed as in the example 1 described in (2)).

(4-2) One representative (parent) table is selected from each group (one high-priority table is selected), and a connection key in which a combination pattern is defined (set) is assigned.

FIG. 14 is an explanatory view of connection of the present invention. This shows the procedure of registration from the above-described connection registration screen. In FIG. 14, S1 registers a connection group. As a result, the connection table 1 described in the upper right
6 are registered (see FIG. 10).

In step S2, a connection pattern is registered. As a result, the previously illustrated items are registered in the connection pattern table 17 described on the right side (see FIG. 11). In step S3, a consolidation table is registered. As a result, the above-described illustrated items are registered in the concatenation table 18 described in the lower right (see FIG. 12).

FIG. 15 shows a search flowchart of the present invention. In FIG. 15, S11 determines whether there is one selected table. This is to determine whether there is one table selected by the user as a search target table on the screen. In the case of YES, normal processing (because it is one table, for example, retrieval is performed based on the set primary key item without linking (SQL statement is automatically created and retrieved)). On the other hand, in the case of NO, the connection according to the present invention is required, and the process proceeds to S12.

In S12, it is determined whether or not any of the selected tables is not registered in the connection table.
If yes, error MSQ (error message)
Is displayed and the process ends. In the case of NO, it is determined that all the selected tables are registered in the concatenation table 18, and the process proceeds to S 13.

In S13, it is determined whether or not the connection table includes a plurality of groups to which the selected table belongs. YE
In the case of S, the process proceeds to S15. In the case of NO, it is determined that all the tables belong to the group, and the process proceeds to S14. On the other hand, if NO in S13, the process proceeds to S15.

In S14, since it is determined that all the selected tables belong to the same group by NO in S13, the process (2) described at the end of FIG. 13 described above is performed (for example, FIG. 13). (A) performs the processing described in detail in Example 1). Thus, the SQL sentence of (Sentence 1) described in Example 1 of FIG. 13A is automatically created only by the user selecting a plurality of tables in the group.

In S15, since it is determined that the table selected in S13 in YES belongs to a plurality of groups, it is further determined whether or not the connection pattern is registered in the connection pattern table. This determines whether the group to which the selected table belongs matches the pattern registered in the connection pattern table 17 of FIG. 11 described above. In the case of YES, the process proceeds to S16. If NO, an error MSG (error message) is displayed and the process ends.

In step S16, the connected pattern table is read.
The processing of (4) is performed. In this case, the processing (4) described at the end of FIG. 13 described above is performed (for example, the processing described in detail in Example 2 of (b) of FIG. 13 is performed). Thus, the SQL statement of (statement 2) described in the example 2 of FIG. 13B is automatically created only by the user selecting a plurality of tables belonging to a plurality of groups.

FIG. 16 shows a search explanatory diagram of the present invention. This explains the above-described state at the time of retrieval in an easily understandable manner.・ Table with four users ・ Basic household attributes ・ Basic customer attributes ・ Customer transaction count ・ Customer transaction base is selected on the screen.

The search condition setting means (extraction condition items) are shown in the table on the left as shown in the table selection column and the item selection column, with four tables selected as shown and the selected items (store number, customer number). Numbers, contact numbers, etc.)
Is displayed.

The search condition setting means (output item) displays output items (for example, store number, customer number, person in charge, etc.) as shown in the selection item column on the screen on the right. The search execution operation means starts the search execution.

The automatic connection unit automatically generates an SQL statement based on the selected table according to the flowchart of FIG. The search result inquiry means transfers the automatically generated SQL sentence to the server and displays the search result as shown in the lower screen.

[0061]

As described above, according to the present invention,
The database is configured by automatically connecting tables and setting conditions only by the user setting the table name and input / output items of the search target, searching and extracting the database, and outputting the results. Even a beginner who does not have knowledge of the above can realize a system that quickly and efficiently searches the database without erroneous setting and obtains a desired result. As a result, (1) SQL statements are automatically created simply by selecting a table name to be searched, as compared to a case where a conventional expert searches the database by setting search conditions on the screen. Then, since the database is searched and the result is displayed, a desired result can be obtained using the database efficiently in a short time without erroneous setting.

(2) Erroneous settings are eliminated, and the reliability and stability of the system can be improved. (3) Even people who do not have knowledge of the database can use it, and it is possible to increase the number of users of the database.

[Brief description of the drawings]

FIG. 1 is a system configuration diagram of the present invention.

FIG. 2 is a screen example (No. 1) of the present invention.

FIG. 3 is a screen example (part 2) of the present invention.

FIG. 4 is a screen example (part 3) of the present invention.

FIG. 5 is a screen example (part 4) of the present invention.

FIG. 6 is a screen example (No. 5) of the present invention.

FIG. 7 is a screen example (part 6) of the present invention.

FIG. 8 is an example of a usage table according to the present invention.

FIG. 9 is an example of a group table according to the present invention.

FIG. 10 is an example of a connection table according to the present invention.

FIG. 11 is an example of a connection pattern table according to the present invention.

FIG. 12 is an example of a concatenation table according to the present invention.

FIG. 13 is a search example of the present invention.

FIG. 14 is an explanatory view of connection of the present invention.

FIG. 15 is a search flowchart of the present invention.

FIG. 16 is an explanatory diagram of retrieval according to the present invention.

[Explanation of symbols]

 1: System management terminal 2: Connection information registration means 11: Server 12: RDBMS 14: Usage table 15: Group table 16: Connection table 17: Connection pattern table 18: Connection table table 21: Usage terminal 22: Table selection means 24: Automatic connection means 25: Search means

Claims (4)

[Claims] 1. A database processing apparatus for generating an instruction for searching a database composed of a plurality of tables, comprising: a group information storage unit for registering a key item associated with a plurality of tables belonging to one group; A database processing apparatus comprising: a linking unit that generates a linking command by referring to the table and the key item information in the group information storage unit in accordance with the specification of the table. 2. The database processing apparatus according to claim 1, wherein priorities are set for table names in the same group, the corresponding key items are taken out, and a linking instruction is generated in a priority order. 3. A connection pattern storage unit for registering a pattern to be connected between a plurality of groups and a key item for associating each group with each other, wherein the connection unit is configured to store the specified table name when the specified table name belongs to a plurality of groups. 2. The database processing apparatus according to claim 1, wherein the connection instruction is generated with reference to the information on the group and the key item in the connection pattern storage unit. 4. A means for registering key items to be associated with a plurality of tables belonging to one group in a group information storage unit, and a table and a key in said group information storage unit corresponding to a specification of a plurality of tables to be searched. A computer-readable recording medium that records a program that functions as a linking unit that generates a linking command by referring to information of an item.