Query using device, method for producing view information, and program

Abstract

It was necessary to master complex queries such as SQL to obtain the necessary information. [Solution] For each of two or more queries, an object definition having two or more object information having an object name and a table name is obtained from the query, an integrated object definition is obtained from the two or more object definitions in which the object information is made unique, the integrated object definitions are stored, and for each of two or more queries, a joined table definition is obtained from the query in which a base table name and one or more joined table information having a table name of a join operation is obtained, and an integrated table definition is obtained from the two or more joined table definitions in which the one or more joined table information that pairs with the same base table name is made unique.By using a query utilization device 1, required information can be easily obtained without having to master complex queries.

Description

[Technical field]

[0001] The present invention relates to a query utilization device or the like that utilizes a set of queries such as SQL to search for information. [Background technology]

[0002] 2. Description of the Related Art Conventionally, there has been a device that provides information in a database in a differentiated manner according to the level of access rights of a user who wishes to search for information (see Patent Document 1). [Prior art documents] [Patent documents]

[0003] [Patent Document 1] Japanese Patent Application Publication No. 10-27124 Summary of the Invention [Problem to be solved by the invention]

[0004] However, in the conventional technology, a user may need to master complex queries such as SQL to obtain necessary information, but it is not easy to master complex queries.

[0005] More specifically, many types of databases, including relational databases, use SQL as their standard operation language. SQL was initially developed as a high-level language to meet operational requirements for databases. However, operational requirements for databases (especially search requirements) have continued to grow more sophisticated and complex, and today there are SQL commands that contain thousands or tens of thousands of steps, making it increasingly difficult to understand, develop, and maintain SQL.

[0006] For example, various tools have been developed, such as SQL modeling tools to support database development, and BI (Business Intelligence) tools that have a GUI to visualize database structures and support database searches. However, while these tools are effective for databases with relatively simple structures and straightforward business requirements, as databases become larger and more complex, the tools reach their limits, and the current situation is that large amounts of SQL are being produced and managed at system development sites.

[0007] In addition, data users typically spend a significant amount of time performing additional data processing using methods such as end-user computing (EUC) to deal with data complexity that cannot be handled by the systems and tools, to handle exceptions, and to meet new business requirements that were not initially anticipated.

[0008] The above problem can be solved by having users not directly operate the database manipulation language SQL, but by preparing a "metamodel" in advance that patterns and standardizes search requests for databases, allowing users to make search requests to this "metamodel" and receive search results that satisfy the search request based on the metamodel.

[0009] On the other hand, tools that aim to improve the efficiency of database searches by developing and introducing new "metamodels" are already in practical use under names such as "virtual databases" and "data integration search tools."

[0010] This invention relates to a technology that automatically generates a "metamodel" by collecting existing SQL used in system development or system usage, and patterning or standardizing it. This technology makes it possible to easily migrate an existing database search system to a search system that uses a "metamodel."

[0011] The introduction of a "metamodel" allows SQL components to be modularized by standardizing or patterning them, and automatically generates SQL each time it is executed, which has the following advantages: First, the amount of work required to develop new SQL can be reduced by reusing similar existing components and developing only the new or changed parts. Second, the number of steps that need to be managed in total is reduced when maintaining and modifying SQL, and the scope of changes can be limited, reducing the maintenance burden.

[0012] Furthermore, the introduction of the "metamodel" makes it possible to handle advanced functions that could not be realized with conventional SQL alone. First, it makes it easy to handle searches that span multiple databases or searches that mix different patterns of algorithms, which are difficult to do with a single SQL. Second, it makes it possible to manage information that is not supported by SQL, such as information on user operation support, navigation, and security constraints.

[0013] Furthermore, by automatically generating the "metamodel," it becomes possible to easily and quickly introduce the "metamodel." As a result, firstly, even users who do not have sufficient knowledge and skills in design and development will be able to utilize the "metamodel." Secondly, even users who already have a large amount of SQL assets and require a great deal of man-hours for development and maintenance will be able to easily migrate to the "metamodel." Thirdly, automatic generation makes it possible to standardize and stabilize the quality of the "metamodel," making future system operations easier. [Means for solving the problem]

[0014] a post-integrated object definition acquisition unit that acquires, from the two or more object definitions acquired by the object definition acquisition unit, a post-integrated object definition having one or more object information that makes the object information held by the two or more object definitions unique; a post-integrated object definition storage unit that accumulates the post-integrated object definition; a joined table definition acquisition unit that acquires, for each of the two or more queries, a joined table definition having a reference table name that identifies a reference table from the query and one or more joined table information having table names of two tables to be subjected to a join operation; a post-integrated table definition acquisition unit that acquires, from the two or more joined table definitions acquired by the joined table definition acquisition unit, a post-integrated table definition having one or more joined table information that makes the two or more joined table information paired with the same reference table name unique; and a post-integrated table definition storage unit that accumulates the post-integrated table definition.

[0015] With this configuration, it is possible to acquire view information having integrated object definitions and integrated table definitions for easily obtaining required information without having to use complex queries.

[0016] Furthermore, the query utilization device of the second invention is a query utilization device in which, compared to the first invention, the object information includes a calculation definition formula, which is information for acquiring an object, and a category indicating whether the object is a key or not, and the object definition acquisition unit acquires, for each of two or more queries, the calculation definition formula and the category from the query, and acquires an object definition having one or more object information having the calculation definition formula and the category.

[0017] With this configuration, it is possible to obtain an integrated object definition and an integrated table definition for easily obtaining required information without having to master complex queries. The integrated object definition and the integrated table definition may be considered to constitute view information.

[0018] In addition, the query utilization device of the third invention is a query utilization device according to the first or second invention, further comprising a query reception unit that receives a query for an integrated object definition and an integrated table definition, a query execution unit that obtains the integrated object definition and the integrated table definition for the query received by the query reception unit and obtains an execution result that is satisfied by the query using the query, the integrated object definition, and the integrated table definition, and an execution result output unit that outputs the execution result.

[0019] This configuration allows users to easily obtain the information they need without having to master complex queries.

[0020] Furthermore, the query utilization device of the fourth invention is a query utilization device in which, compared to the first invention, the object information includes a calculation definition formula, which is information for acquiring an object, and a category indicating whether the object is a key or not, and further includes a post-integrated scope acquisition unit that acquires two or more integrated object definitions acquired by a post-integrated object definition acquisition unit, and acquires integrated scope information having one or more scope information having object names and one or more calculation definition formulas that pair with the object names, which object information includes two or more integrated object definitions that have one or more key objects in common among the two or more integrated object definitions, and a post-integrated scope storage unit that stores the post-integrated scope information acquired by the post-integrated scope acquisition unit.

[0021] With this configuration, the required information can be easily obtained.

[0022] Furthermore, the query utilization device of the fifth invention is a query utilization device according to the fourth invention, further comprising a query reception unit that receives a query for a scope specified by integrated scope information, a query execution unit that obtains integrated scope information and an integrated table definition for the query received by the query reception unit, and obtains an execution result satisfied by the query using the query, the integrated scope information, and the integrated table definition, and an execution result output unit that outputs the execution result.

[0023] With this configuration, the required information can be easily obtained.

[0024] Furthermore, a query use device according to a sixth aspect of the present invention is a query use device according to any one of the first to fifth aspects of the present invention, in which the two or more queries in the query storage unit are queries directed to at least two or more different databases.

[0025] With this configuration, required information based on two or more databases can be easily obtained. Effect of the Invention

[0026] According to the query utilization device of the present invention, required information can be easily obtained without having to master complex queries. [Brief description of the drawings]

[0027] [Figure 1] Conceptual diagram of information system A in embodiment 1. [Diagram 2] Block diagram of Information System A [Diagram 3] Block diagram of the query utilization device 1 [Figure 4] A diagram showing an example of the query [Diagram 5] A diagram showing an example of the object definition. [Figure 6] A diagram showing an example of the join table definition [Figure 7] Figure showing an example of object definition after integration [Figure 8] Figure 2 shows an example of table definition after integration [Figure 9] A diagram showing an example of scope information after integration [Figure 10] A flowchart illustrating an example of the operation of the query use device 1. [Figure 11] A flowchart illustrating an example of the analysis process [Figure 12] A flowchart illustrating an example of the post-integration table acquisition process. [Figure 13] A flowchart illustrating an example of the integrated object acquisition process. [Figure 14] A flowchart illustrating an example of the post-integration scope acquisition process. [Figure 15] A flowchart illustrating an example of the query execution process. [Figure 16] A flowchart illustrating an example of the query configuration process. [Figure 17] A flowchart for explaining an example of the selection list configuration process. [Figure 18] A flowchart for explaining an example of the FROM clause construction process. [Figure 19] A flowchart for explaining an example of the WHERE clause construction process. [Figure 20] A flowchart for explaining an example of the GROUP BY clause construction process. [Figure 21] Flowchart for explaining the result combination process [Figure 22] FIG. 13 is a diagram showing an example of the view information. [Diagram 23] FIG. 13 shows an example of the generated query. [Figure 24] FIG. 13 is a diagram showing an example of the view information. [Diagram 25] FIG. 13 shows an example of the generated query. [Figure 26] Overview of the computer system [Figure 27] Block diagram of the computer system DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

[0028] Hereinafter, embodiments of a query using device and the like will be described with reference to the drawings. Note that components with the same reference numerals in the embodiments perform similar operations, and therefore repeated description may be omitted.

[0029] (Embodiment 1) In this embodiment, a query use device that generates a view based on two or more tables from a set of queries such as SQL will be described.

[0030] In this embodiment, a query use device capable of making a query to a generated view will be described.

[0031] In this embodiment, a query use device that generates a scope from two or more views will be described.

[0032] Furthermore, in this embodiment, a query use device capable of making a query to the generated scope will be described.

[0033] In this specification, information X being associated with information Y means that information Y can be obtained from information X, or information X can be obtained from information Y, and the method of association is not important. Information X and information Y may be linked, may exist in the same buffer, information X may be included in information Y, or information Y may be included in information X, etc.

[0034] Furthermore, in this specification, selecting or determining information Z means obtaining information Z, obtaining a pointer to information Z, obtaining the ID of information Z, setting a flag for information Z, etc., and is sufficient as long as information Z can be accessed.

[0035] 1 is a conceptual diagram of an information system A according to the present embodiment. The information system A includes a query use device 1, one or more terminal devices 2, and one or more database devices 3.

[0036] The query usage device 1 is a device that generates a view or a scope from a set of queries and is capable of making queries regarding the view or the scope. It is preferable that the query usage device 1 is a server. The query usage device 1 is, for example, a cloud server or an ASP server, but the type is not important. The query usage device 1 may also be a terminal. If the query usage device 1 is a terminal, the information system A does not require a terminal device 2. If the query usage device 1 is a terminal, it can be considered that the query usage device 1 also serves as the terminal device 2. If the query usage device 1 is a terminal, the query usage device 1 is, for example, a so-called personal computer, smartphone, or tablet terminal, but the type is not important.

[0037] The terminal device 2 is a terminal used by a user. The user is a person who uses the query use device 1 or a person who searches the database device 3. The terminal device 2 is, for example, a so-called personal computer, a smartphone, or a tablet terminal, but the type is not important.

[0038] The database device 3 is a device having a database function. The database device 3 is a device that receives a query such as SQL, executes the query, and transmits the execution result to the terminal device 2. The database device 3 is usually a server. However, the database device 3 may be provided in the query use device 1.

[0039] Fig. 2 is a block diagram of an information system A in this embodiment. Fig. 3 is a block diagram of a query use device 1.

[0040] The query use device 1 includes a storage unit 11, a reception unit 12, a processing unit 13, and an output unit 15. The storage unit 11 includes a query storage unit 111. The reception unit 12 includes a query reception unit 121. The processing unit 13 includes a query acquisition unit 131, an object definition acquisition unit 132, a joined table definition acquisition unit 133, a post-integrated object definition acquisition unit 134, a post-integrated object definition storage unit 135, a post-integrated table definition acquisition unit 136, a post-integrated table definition storage unit 137, a post-integrated scope acquisition unit 138, a post-integrated scope storage unit 139, and a query execution unit 140. The output unit 15 includes an execution result output unit 151.

[0041] The terminal device 2 includes a terminal storage unit 21, a terminal reception unit 22, a terminal processing unit 23, a terminal transmission unit 24, a terminal reception unit 25, and a terminal output unit .

[0042] Various types of information are stored in a storage unit 11 constituting the query use device 1. The various types of information are, for example, queries, which will be described later.

[0043] The query storage unit 111 stores two or more queries. The query here is usually an SQL statement, but may be other types of information. The query is a query statement for a table in a database. The query here is, for example, a query statement for one or more database devices 3. The query is usually a character string for searching a database. The query is, for example, a Select statement (search statement). It is preferable that the query has information indicating a join operation. The information indicating a join operation is, for example, the character strings "JOIN" and "INNER JOIN". It is preferable that the query in the query storage unit 111 is a query actually used by a user.

[0044] The query in the query storage unit 111 is, for example, information acquired from the database device 3.

[0045] The receiving unit 12 receives various instructions and information, such as a query, a query acquisition instruction, and a scope acquisition instruction.

[0046] The query acquisition instruction is an instruction to acquire a query from one or more database devices 3 .

[0047] The scope acquisition instruction is an instruction to use one or more queries in the query storage unit 111 to acquire view information (to be described later) and integrated scope information (to be described later).

[0048] Here, acceptance typically refers to the reception of information transmitted via a wired or wireless communication line, but may also be a concept including the acceptance of information input from an input device such as a keyboard, mouse, or touch panel, and the acceptance of information read from a recording medium such as an optical disk, magnetic disk, or semiconductor memory.

[0049] The query reception unit 121 receives a query. The query is, for example, SQL, but may be a query other than SQL. The query reception unit 121 receives, for example, a query for a view. The query reception unit 121 receives, for example, a query for a scope.

[0050] A view is associated with, for example, a view identifier that identifies the view. A view is specified, for example, by an integrated table definition, which will be described later. A view is specified, for example, by an integrated table definition, which will be described later, and an object definition, which will be described later. A view may also be called a search view. Information that specifies the contents of a view is called view information. The view information has an object definition, which will be described later, and a join table definition, which will be described later.

[0051] A scope is information composed of two or more views. For example, a scope corresponds to a scope identifier that identifies the scope. A scope is specified, for example, by integrated scope information described below. A scope usually corresponds to two or more views. Note that a scope may also be considered to be a view. Information that specifies the contents of a scope is called integrated scope information. The integrated scope information has one or more scope information. The scope information has an object name and one or more calculation definition expressions. Such calculation definition expressions correspond to view information.

[0052] The processing unit 13 performs various types of processing. The various types of processing are, for example, processing performed by a query acquisition unit 131, an object definition acquisition unit 132, and the like.

[0053] The query acquisition unit 131 acquires two or more queries from the query storage unit 111. Such queries are used to acquire view information and integrated scope information.

[0054] The object definition acquisition unit 132 acquires an object definition for each of the two or more queries.

[0055] An object definition is a collection of information about one or more objects. An object can also be said to be an attribute value. An object definition has one or more pieces of object information.

[0056] The object information is information about an object. The object information includes an object name and a table name. The object information preferably includes a calculation definition formula and a category.

[0057] An object name is the name of an object. An object name is usually an attribute name. An object name can also be the ID of an object.

[0058] The table name is the name of the table in which the object identified by the object name is stored. The table name may be the table ID.

[0059] A calculation definition formula is information for acquiring an object. A calculation definition formula is information for acquiring an attribute value. A calculation definition formula corresponds to an object name. A calculation definition formula is, for example, a "table name and object name", or an arithmetic formula. The arguments of an arithmetic formula are, for example, a "table name and object name". An arithmetic formula is an arithmetic formula for an object, for example, "SUM", "AVERAGE", or an arithmetic operation. A calculation definition formula may be said to be object acquisition source information.

[0060] The category is information indicating whether an object is a key or not. The category is, for example, "key" or "data". "Key" indicates that the object is a key. "Data" indicates that the object is not a key.

[0061] The object definition acquisition unit 132 acquires an object definition having one or more pieces of object information from each of two or more queries acquired by the query acquisition unit 131. The object definition has an object name and a table name. For example, the object definition acquisition unit 132 acquires an object name and a table name from a selection list (which may be called a "projection list") of an SQL statement. The selection list is, for example, a character string following the character string "SELECT" and preceding the character string "GROUP BY" or "WHERE". For example, the object definition acquisition unit 132 acquires two or more pairs of an object name and a table name from a selection list of an SQL statement. For example, the object definition acquisition unit 132 acquires a pair of an object name and a table name from a character string included in an arithmetic expression (for example, "SUM" or "AVERAGE") from the selection list of an SQL statement. For example, the object definition acquisition unit 132 acquires an object name following the character string "AS" corresponding to an arithmetic expression (for example, "SUM" or "AVERAGE") from the selection list of an SQL statement.

[0062] It is preferable that the object definition acquisition unit 132 acquires a calculation definition formula from the query for each of two or more queries, and acquires an object definition having one or more object information items that also have the calculation definition formula. The object definition acquisition unit 132 acquires the calculation definition formula from, for example, a selection list of an SQL statement.

[0063] It is preferable that the object definition acquisition unit 132 acquires a category of each object from each of two or more queries, and acquires an object definition having one or more object information items that also have the category. The object definition acquisition unit 132 acquires the object name of the object that is a key from, for example, a GROUP BY clause of a SELECT statement that is an SQL statement. Then, the object definition acquisition unit 132 acquires a category indicating "whether it is a key or not" in association with each object name. Note that the object name that appears in the GROUP BY clause is the name of the object that is a key. The object name that does not appear in the GROUP BY clause is the name of the object that is not a key.

[0064] For example, the object definition acquisition unit 132 acquires the object names "Customer Code", "Customer Name", "Order Quantity", and "Order Amount" from the selection list of the SQL command (Search 1) which is the query in Fig. 4. Also, from the selection list of the SQL command (Search 1) in Fig. 4, the object definition acquisition unit 132 acquires the table names "Order", "Customer", "Order Details", and "Order Details" which correspond to each of the four object names ("Customer Code", "Customer Name", "Order Quantity", and "Order Amount"). Also, from the selection list, the object definition acquisition unit 132 acquires the calculation definition formulas "Order.Customer Code", "Customer.Customer Name", "SUM(Order Details.Quantity)", and "SUM(Order Details.Unit Price*Order Details.Quantity)" which correspond to each of the four object names.

[0065] Furthermore, the object definition acquisition unit 132 acquires the object names "Customer Code" and "Customer Name" of the objects that are keys from, for example, the GROUP BY clause of the query in Fig. 4. Next, the object definition acquisition unit 132 acquires the category "Key" in association with the object names "Customer Code" and "Customer Name", and acquires the category "Data" in association with other object names "Order Quantity" and "Order Amount". The category "Data" indicates that the object in question is not a key.

[0066] Through the above processing, object definition acquisition unit 132 can acquire the object definition in Fig. 5. The object definition in Fig. 5 has four pieces of object information.

[0067] The joined table definition acquisition unit 133 acquires a joined table definition for each of the two or more queries. The joined table definition acquisition unit 133 acquires the joined table definition from, for example, the FROM clause of a SELECT statement. The joined table definition acquisition unit 133 acquires the joined table definition by using, for example, a clue clause that is a specific character string (for example, "JOIN", "INNER JOIN", "INNER JOIN...ON") in the FROM clause of the SELECT statement.

[0068] A join table definition is information about a join operation. The join table definition includes a reference table name and information about one or more join tables.

[0069] The joined table information includes the table names of the two tables to be joined, and includes, for example, a join type and a join condition expression.

[0070] The reference table name is the name of the reference table. The reference table is the table that is at the top of the join operation between the tables to be searched and has no further join sources. The reference table name is, for example, the first table identifier in the FROM clause of the SELECT statement.

[0071] The joined table definition acquisition unit 133 acquires the joined table definition of FIG. 6 from the query of FIG. 4, for example. The joined table definition of FIG. 6 is information having two records having a "super table", a "joined table", a "join type", and a "join condition expression". The "super table" and the "joined table" are the names of the two tables that are the targets of the join operation. The name of the joined table that pairs the "super table" with (BASE), "Order Details", is the name of the base table.

[0072] The join type is information that indicates the type of join operation. The join type is a command that indicates the join operation. Examples of join types are "INNER JOIN", "LEFT JOIN", "RIGHT JOIN", and "FULL OUTER JOIN".

[0073] A join condition expression is information that indicates the condition of a join operation. For example, a join condition expression is "Order Detail.Order Code = Order.Order Code" or "Order.Customer Code = Customer.Customer Code."

[0074] The integrated object definition acquisition unit 134 acquires an integrated object definition from the two or more object definitions acquired by the object definition acquisition unit 132 .

[0075] An integrated object definition is information obtained by integrating two or more object definitions. An integrated object definition is information that has one or more pieces of object information that make the object information held by each of two or more object definitions unique. An integrated object definition is also an example of an object definition. Like an object definition, an integrated object definition also has one or more pieces of object information.

[0076] The integrated object definition acquisition unit 134 acquires, from the two or more object definitions acquired by the object definition acquisition unit 132, an integrated object definition having one or more pieces of object information that make the object information held by the two or more object definitions unique.

[0077] For example, when there are three object definitions (FIGS. 7(a)(b)(c)) acquired by the object definition acquisition unit 132, the integrated object definition acquisition unit 134 acquires the integrated object definition of FIG. 7(d) from the three object definitions. In FIG. 7(d), duplicated object information among the object information in FIG. 7(a)(b)(c) is consolidated into one.

[0078] The integrated object definition storage unit 135 stores the integrated object definition acquired by the integrated object definition acquisition unit 134. Note that there is no restriction on where the integrated object definition is stored.

[0079] The integrated table definition acquisition unit 136 acquires an integrated table definition having one or more pieces of joined table information that have been made unique from two or more pieces of joined table information that are paired with the same reference table name, from the two or more joined table definitions acquired by the joined table definition acquisition unit 133.

[0080] An integrated table definition is information obtained by integrating two or more joined table definitions. An integrated table definition is also an example of a joined table definition. Like a joined table definition, an integrated table definition also has a reference table and one or more pieces of joined table information. An integrated table definition also has, for example, a join type and a join condition expression.

[0081] When there are three joined table definitions in Fig. 8(a), (b), and (c), the integrated table definition acquisition unit 136 acquires the integrated table definition in Fig. 8(d) from the three joined table definitions that have the same reference table name. In the integrated table definition (Fig. 8(d)), the joined table information that is the duplicated records in Fig. 8(a), (b), and (c) is processed to be unique.

[0082] Post-integration table definition storage unit 137 stores the post-integration table definition acquired by post-integration table definition acquisition unit 136. Note that there is no restriction on where the post-integration table definition is stored.

[0083] The integrated scope acquisition unit 138 acquires integrated scope information from two or more integrated object definitions having one or more key objects in common, among the two or more integrated object definitions acquired by the integrated object definition acquisition unit 134.

[0084] The integrated scope acquisition unit 138 acquires two or more integrated object definitions acquired by the integrated object definition acquisition unit 134. Next, the integrated scope acquisition unit 138 determines two or more integrated object definitions that have one or more key objects in common among the two or more integrated object definitions. Next, the integrated scope acquisition unit 138 acquires one or more pieces of scope information that have object names of the two or more determined integrated object definitions and one or more calculation definition expressions that are paired with the object names. Next, the integrated scope acquisition unit 138 configures integrated scope information that has the one or more pieces of scope information. Note that in the integrated scope information, when the object names of two or more integrated object definitions are the same, they are integrated into one.

[0085] The integrated scope information can be said to be information that defines a view based on two or more views. The integrated scope information corresponds to two or more pieces of view information. The scope information has an object name and a calculation definition formula corresponding to one or more views for obtaining an object (attribute value) corresponding to the object name.

[0086] When there are two integrated object definitions ("search view 1" and "search view 2") in Fig. 9(a) and (b), the integrated scope acquisition unit 138 acquires the integrated scope information in Fig. 9(c) from the two integrated object definitions. The integrated scope information in Fig. 9(c) corresponds to "search view 1" and "search view 2". In the integrated scope information, duplicate object names in Fig. 9(a) and (b) are processed to be unique. Note that the underlined object names in Fig. 9(a), (b), and (c) are the names of objects that serve as "keys".

[0087] The integrated scope storage unit 139 stores the integrated scope information acquired by the integrated scope acquisition unit 138. There is no restriction on where the integrated scope information is stored.

[0088] The integrated scope storage unit 139 stores the integrated scope information in association with two or more integrated object definitions (view information) that are the basis of the integrated scope information.

[0089] Query execution unit 140 obtains an integrated object definition and an integrated table definition for the query received by query reception unit 121, and obtains an execution result that is satisfied by the query using the query, the integrated object definition, and the integrated table definition.

[0090] The query execution unit 140, for example, acquires an integrated object definition and an integrated table definition for a query accepted by the query acceptance unit 121. For example, the query execution unit 140 acquires, from the integrated object definition, a calculation definition expression that pairs with one or more object names included in a search item (for example, a selection list of an SQL statement) included in the query. The query execution unit 140 also acquires table names included in the acquired one or more calculation definition expressions. Next, the query execution unit 140 acquires a reference table name among the acquired one or more table names from the integrated table definition. The query execution unit 140 also acquires a join condition expression that is one or more acquired table names and pairs with a join table from the integrated table definition.

[0091] Query execution unit 140 acquires post-integration scope information and a post-integration table definition for a query accepted by query acceptance unit 121, and acquires an execution result that is satisfied by the query using the query, the post-integration scope information, and the post-integration table definition.

[0092] A specific example of the process of query execution unit 140 will be described later with reference to the flowcharts of FIG. 15 to FIG.

[0093] The output unit 15 outputs various information such as execution results and accepted queries.

[0094] Here, output means, for example, transmission to terminal device 2, but it may also be a concept including display on a display, projection using a projector, printing on a printer, sound output, transmission to an external device, storage on a recording medium, and handing over the processing results to other processing devices or other programs.

[0095] The execution result output unit 151 outputs the execution result acquired by the query execution unit 140 .

[0096] Various types of information are stored in the terminal storage unit 21 constituting the terminal device 2. The various types of information are, for example, queries.

[0097] The terminal reception unit 22 receives various information, instructions, and the like. The various information, instructions, and the like are, for example, a query, a query acquisition instruction, or a scope acquisition instruction. The input means for inputting the input information may be anything, such as a touch panel, a keyboard, a mouse, or a menu screen. It is preferable that the input means for inputting a query, etc. is a microphone.

[0098] The terminal processing unit 23 performs various types of processing. For example, the various types of processing are processing for converting received information, instructions, etc. into information or instructions with a structure to be transmitted. For example, the various types of processing are processing for converting received information into information with a structure to be output.

[0099] The terminal transmitting unit 24 transmits various information, instructions, etc. to the query use device 1. The various information, instructions, etc. are, for example, a query, a query acquisition instruction, or a scope acquisition instruction.

[0100] The terminal receiving unit 25 receives various types of information, such as execution results.

[0101] The terminal output unit 26 outputs various information, such as execution results.

[0102] The storage unit 11, the query storage unit 111, and the terminal storage unit 21 are preferably non-volatile recording media, but may also be realized as volatile recording media.

[0103] There is no restriction on the process by which information is stored in the storage unit 11, etc. For example, information may be stored in the storage unit 11, etc. via a recording medium, information transmitted via a communication line, etc. may be stored in the storage unit 11, etc., or information inputted via an input device may be stored in the storage unit 11, etc.

[0104] The reception unit 12 and the query reception unit 121 are preferably realized by wireless or wired communication means, but may also be realized by a means for receiving broadcasts, a device driver for an input means such as a touch panel or a keyboard, or control software for a menu screen.

[0105] The processing unit 13, the query acquisition unit 131, the object definition acquisition unit 132, the join table definition acquisition unit 133, the integrated object definition acquisition unit 134, the integrated object definition storage unit 135, the integrated table definition acquisition unit 136, the integrated table definition storage unit 137, the integrated scope acquisition unit 138, the integrated scope storage unit 139, the query execution unit 140, and the terminal processing unit 23 can be usually realized by a processor, a memory, or the like. The processing procedure of the processing unit 13, etc. is usually realized by software, and the software is recorded in a recording medium such as a ROM. However, it may be realized by hardware (dedicated circuit). The processor is a CPU, an MPU, a GPU, etc., and the type does not matter.

[0106] The output unit 15 and the execution result output unit 151 are realized, for example, by wireless or wired communication means, but may also be realized by driver software for a display or speaker, or driver software for an output device and the output device, or the like.

[0107] The terminal reception unit 22 can be realized by a device driver for an input means such as a touch panel or a keyboard, or control software for a menu screen.

[0108] The terminal transmitting unit 24 is usually realized by a wireless or wired communication means, but may also be realized by a broadcasting means.

[0109] The terminal receiving unit 25 is usually realized by a wireless or wired communication means, but may be realized by a means for receiving a broadcast.

[0110] The terminal output unit 26 may be considered to include, or may not include, an output device such as a display, a speaker, etc. The terminal output unit 26 may be realized by driver software for an output device, or a combination of driver software for an output device and an output device, etc.

[0111] Next, an example of the operation of the query use device 1 will be described with reference to the flowchart of FIG.

[0112] (Step S1001) The reception unit 12 judges whether or not a query acquisition instruction has been received. If a query acquisition instruction has been received, the process proceeds to step S1002, and if not, the process proceeds to step S1012.

[0113] (Step S1002) The processing unit 13 assigns 1 to a counter i.

[0114] (Step S1003) The processing unit 13 judges whether or not the i-th database device 3 from which the query is to be acquired exists. If the i-th database device 3 exists, the process proceeds to step S1004, and if not, the process returns to step S1001.

[0115] (Step S1004) The processing unit 13 acquires from the i-th database device 3 one or more queries that have been executed in the past.

[0116] (Step S1005) The processing unit 13 assigns 1 to a counter j.

[0117] (Step S1006) The processing unit 13 judges whether or not the j-th query is present among the queries acquired in step S1004. If the j-th query is present, the process proceeds to step S1007, and if not, the process proceeds to step S1011.

[0118] (Step S1007) The processing unit 13 acquires the j-th query from the queries acquired in step S1004.

[0119] (Step S1008) The processing unit 13 judges whether or not the query acquired in step S1007 matches the storage condition. If the query matches the storage condition, the process proceeds to step S1009, and if the query does not match the storage condition, the process proceeds to step S1010.

[0120] The storage condition is a condition for storing a query. For example, the storage condition is that the query is a SELECT statement, or that the query is a SELECT statement and performs a JOIN operation. The storage condition is usually a condition related to a specific character string (cue phrase) such as "SELECT" or "JOIN".

[0121] (Step S1009) The processing unit 13 accumulates the query acquired in step S1007 in the query storage unit 111.

[0122] (Step S1010) The processing unit 13 increments the counter j by 1. The process returns to step S1006.

[0123] (Step S1011) The processing unit 13 increments the counter i by 1. The process returns to step S1003.

[0124] (Step S1012) The reception unit 12 judges whether or not a scope acquisition instruction has been received. If a scope acquisition instruction has been received, the process proceeds to step S1013, and if not, the process proceeds to step S1020.

[0125] (Step S1013) The processing unit 13 assigns 1 to the counter i.

[0126] (Step S1014) The query acquisition unit 131 judges whether or not the i-th query exists in the query storage unit 111. If the i-th query exists, the process proceeds to step S1015, and if not, the process proceeds to step S1017.

[0127] (Step S1015) The processing unit 13 analyzes the i-th query in the query storage unit 111. An example of such analysis processing will be described with reference to the flowchart of FIG.

[0128] (Step S1016) The processing unit 13 increments the counter i by 1. The process returns to step S1014.

[0129] (Step S1017) The integrated table definition acquisition unit 136 acquires the integrated table definition. An example of such integrated table acquisition processing will be described with reference to the flowchart of FIG.

[0130] (Step S1018) The integrated object definition acquisition unit 134 acquires an integrated object definition. An example of such integrated object acquisition processing will be described with reference to the flowchart of FIG.

[0131] (Step S1019) The integrated scope acquisition unit 138 acquires integrated scope information. Return to step S1001. An example of such integrated scope acquisition processing will be described with reference to the flowchart of FIG.

[0132] (Step S1020) Query receiving unit 121 determines whether or not a query has been received from a user. If a query has been received, the process proceeds to step S1021, and if not, the process returns to step S1001.

[0133] (Step S1021) Query execution unit 140 executes the query accepted in step S1020 and acquires the execution result. An example of such a query execution process will be described with reference to the flowchart of FIG.

[0134] (Step S1022) The execution result output unit 151 outputs the execution result acquired in step S1021. Then, the process returns to step S1001.

[0135] In this step, the execution result output unit 151 transmits the execution result to the terminal device 2, for example.

[0136] In the flowchart of FIG. 10, the process ends when the power is turned off or an interrupt is issued to end the process.

[0137] Next, an example of the analysis process in step S1015 will be described with reference to the flowchart in Fig. 11. It is assumed that the query to be analyzed here is a SELECT statement in SQL.

[0138] (Step S1101) The joined table definition acquiring unit 133 acquires a FROM clause from a query.

[0139] (Step S1102) The joined table definition acquiring unit 133 acquires a reference table name from the FROM clause acquired in step S1101.

[0140] (Step S1103) The join table definition obtaining unit 133 assigns 1 to a counter i.

[0141] (Step S1104) The join table definition acquisition unit 133 judges whether or not a character string indicating the i-th join operation exists in the FROM clause acquired in step S1101. If a character string indicating the i-th join operation exists, the process proceeds to step S1105, and if not, the process proceeds to step S1111.

[0142] (Step S1105) The joined table definition acquisition unit 133 acquires the upper table name in the character string indicating the i-th join operation in the FROM clause. Note that if the character string indicating the i-th join operation in the FROM clause is "INNER JOIN Order ON (Order Details.Order Code = Order.Order Code)", the upper table name is "Order Details".

[0143] (Step S1106) The joined table definition acquisition unit 133 acquires the joined table name in the i-th join operation in the FROM clause. Note that if the character string indicating the i-th join operation in the FROM clause is "INNER JOIN Order ON (Order Details.Order Code = Order.Order Code)", the joined table name is "Order".

[0144] (Step S1107) The join table definition acquisition unit 133 acquires a join type from a character string indicating the i-th join operation in the FROM clause. If the character string indicating the i-th join operation in the FROM clause is "INNER JOIN Order ON (Order Details.Order Code = Order.Order Code)", the join type is "INNER JOIN".

[0145] (Step S1108) The join table definition acquisition unit 133 acquires a join condition expression from a character string indicating the ith join operation in the FROM clause. If the character string indicating the ith join operation in the FROM clause is "INNER JOIN Order ON (Order Details.Order Code = Order.Order Code)", the join condition expression is "Order Details.Order Code = Order.Order Code".

[0146] (Step S1109) The joined table definition acquiring unit 133 configures joined table information having a higher-level table name, a joined table name, a join type, and a join condition expression.

[0147] (Step S1110) The joined table definition obtaining unit 133 increments the counter i by 1. The process returns to step S1104.

[0148] (Step S1111) The joined table definition acquiring unit 133 configures and accumulates a joined table definition having a reference table name and one or more pieces of joined table information.

[0149] (Step S1112) The object definition acquiring unit 132 acquires a selection list from the query.

[0150] (Step S1113) The object definition acquiring unit 132 acquires a GROUP BY clause from the query.

[0151] (Step S1114) The object definition acquiring unit 132 acquires a WHERE clause from the query.

[0152] (Step S1115) The object definition obtaining unit 132 assigns 1 to the counter i.

[0153] (Step S1116) The object definition obtaining unit 132 judges whether or not the i-th object name exists in the selection list. If the i-th object name exists, the process proceeds to step S1117, and if not, the process proceeds to step S1125.

[0154] (Step S1117) The object definition acquiring unit 132 acquires the i-th object name in the selection list.

[0155] (Step S1118) The object definition acquiring unit 132 acquires a table name that is paired with the i-th object name from the selection list. Note that this table name may be called a reference table name.

[0156] (Step S1119) The object definition acquisition unit 132 judges whether or not the i-th object name exists in the GROUP BY clause. If the i-th object name exists, the process proceeds to step S1120, and if not, the process proceeds to step S1121.

[0157] (Step S1120) The object definition obtaining unit 132 sets the category paired with the i-th object name as a "key." Then, the process proceeds to step S1122.

[0158] (Step S1121) The object definition obtaining unit 132 sets the category paired with the i-th object name to "data".

[0159] (Step S1122) The object definition acquisition unit 132 acquires a calculation definition formula including the i-th object name in the selection list.

[0160] (Step S1123) The object definition acquiring unit 132 configures object information including the object name, the reference table, the category, and the calculation definition formula.

[0161] (Step S1124) The object definition obtaining unit 132 increments the counter i by 1. The process returns to step S1117.

[0162] (Step S1125) The object definition acquisition unit 132

[0163] Next, an example of the integrated table acquisition process in step S1017 will be described with reference to the flowchart in FIG.

[0164] (Step S1201) The integrated table definition obtaining unit 136 assigns 1 to a counter i.

[0165] (Step S1202) The integrated table definition acquisition unit 136 judges whether the i-th reference table name exists in the two or more joined table definitions to be processed. If the i-th reference table name exists, the process proceeds to step S1203, and if it does not exist, the process returns to the upper process. Note that, here, the same reference table name is considered to be one reference table name.

[0166] (Step S1203) The integrated table definition acquiring unit 136 acquires a joined table definition having the i-th reference table name.

[0167] (Step S1204) Integrated table definition acquisition unit 136 determines whether or not two or more joined table definitions have been acquired in step S1203. If two or more joined table definitions have been acquired, the process proceeds to step S1205. If one joined table definition has been acquired, the process proceeds to step S1210.

[0168] (Step S1205) Integrated table definition acquiring unit 136 outputs two or more joined table definitions.

[0169] (Step S1206) Integrated table definition acquisition unit 136 judges whether or not a selection of two or more joined table definitions to be integrated has been accepted. If the selection has been accepted, the process proceeds to step S1207, and if not, the process returns to step S1206.

[0170] (Step S1207) The integrated table definition acquiring unit 136 acquires all joined table information contained in each of the two or more joined table definitions whose selection has been accepted.

[0171] (Step S1208) Integrated table definition acquiring unit 136 performs unique processing on the two or more pieces of joined table information acquired in step S1207, and acquires one or more pieces of joined table information.

[0172] (Step S1209) Integrated table definition storage unit 137 stores an integrated table definition having one or more pieces of joined table information and a reference table name acquired in step S1208.

[0173] (Step S1210) The integrated table definition obtaining unit 136 increments the counter i by 1. The process returns to step S1202.

[0174] Next, an example of the integrated object acquisition process in step S1018 will be described with reference to the flowchart in FIG.

[0175] (Step S1301) The integrated object definition obtaining unit 134 assigns 1 to a counter i.

[0176] (Step S1302) The integrated object definition obtaining unit 134 judges whether or not the i-th integrated table definition exists. If the i-th integrated table definition exists, the process proceeds to step S1303, and if not, the process returns to the upper process.

[0177] (Step S1303) The integrated object definition obtaining unit 134 assigns 1 to a counter j.

[0178] (Step S1304) The integrated object definition acquisition unit 134 judges whether or not the jth joined table that is the basis of the i-th integrated table definition exists. If the jth joined table exists, the process proceeds to step S1305, and if not, the process proceeds to step S1307.

[0179] (Step S1305) The integrated object definition obtaining unit 134 obtains an object definition that is paired with the j-th joined table that is the basis of the i-th integrated table definition.

[0180] (Step S1306) The integrated object definition obtaining unit 134 increments the counter j by 1. The process returns to step S1304.

[0181] (Step S1307) The integrated object definition acquisition unit 134 acquires all the object information contained in each of the two or more object definitions acquired in step S1305, performs unique processing on the two or more pieces of object information, and acquires an integrated object definition containing one or more pieces of object information.

[0182] (Step S1308) The integrated object definition storage unit 135 stores the integrated object definition acquired in step S1307 in association with the i-th integrated table definition.

[0183] (Step S1309) The integrated object definition obtaining unit 134 increments the counter i by 1. The process returns to step S1302.

[0184] Next, an example of the integrated scope acquisition process in step S1019 will be described with reference to the flowchart in FIG.

[0185] (Step S1401) The integrated scope acquisition unit 138 assigns 1 to a counter i.

[0186] (Step S1402) The integrated scope acquisition unit 138 determines whether or not the i-th combination, which is a combination of two or more key objects, exists. If the i-th combination exists, the process proceeds to step S1403, and if not, the process returns to the upper process.

[0187] (Step S1403) The integrated scope acquisition unit 138 acquires one or more integrated object definitions having two or more key objects corresponding to the i-th combination.

[0188] (Step S1404) The integrated scope acquisition unit 138 judges whether the integrated object definition acquired in step S1403 includes two or more integrated object definitions. If two or more integrated object definitions are included, the process proceeds to step S1405, and if not, the process proceeds to step S1410.

[0189] (Step S1405) The integrated scope acquisition unit 138 outputs two or more integrated object definitions.

[0190] (Step S1406) The integrated scope acquisition unit 138 judges whether or not a selection by the user from among the two or more integrated object definitions output in step S1405 has been accepted. If a selection has been accepted, the process proceeds to step S1407, and if a selection has not been accepted, the process returns to step S1406. Note that the selection here is a selection of two or more integrated object definitions.

[0191] (Step S1407) The integrated scope acquisition unit 138 combines two or more integrated object definitions corresponding to the selection in step S1406. Note that combining means forming an integrated object definition including all object information that each of the two or more integrated object definitions has.

[0192] (Step S1408) The integrated scope acquisition unit 138 performs unique processing on the object information in the integrated object definition acquired in step S1407 to configure an integrated object definition consisting of unique object information only. Such integrated object definition configures integrated scope information.

[0193] (Step S1409) The integrated scope storage unit 139 stores the integrated scope information acquired in step S1408 in association with the two or more integrated object definitions that are the basis of the integrated scope information.

[0194] (Step S1410) The integrated scope acquisition unit 138 increments the counter i by 1. The process returns to step S1402.

[0195] Next, an example of the query execution process in step S1021 will be described with reference to the flowchart in FIG.

[0196] (Step S1501) Query execution unit 140 acquires a scope identifier or a view identifier to be searched from the received query.

[0197] (Step S1502) Query execution unit 140 acquires one or more pairs corresponding to the scope identifier or view identifier acquired in step S1501. The pair is a combination of a join table definition and an object definition.

[0198] (Step S1503) Query execution unit 140 assigns 1 to counter i.

[0199] (Step S1504) Query execution unit 140 judges whether or not the i-th pair exists among the pairs acquired in step S1502. If the i-th pair exists, the process proceeds to step S1505, and if not, the process proceeds to step S1509.

[0200] (Step S1505) Query execution unit 140 acquires the i-th pair of a joined table definition and an object definition.

[0201] (Step S1506) Query execution unit 140 constructs a query using the joined table definition and object definition acquired in step S1505. An example of such a query construction process will be described with reference to the flowchart in FIG.

[0202] (Step S1507) Query execution unit 140 executes the query constructed in step S1506, and obtains the execution result.

[0203] (Step S1508) Query execution unit 140 increments counter i by 1. The process returns to step S1504.

[0204] (Step S1509) Query execution unit 140 judges whether the sets acquired in step S1502 are two or more sets or one set. If there are two or more sets, the process proceeds to step S1510, and if there is one set, the process returns to the upper process.

[0205] (Step S1510) Query execution unit 140 combines two or more execution results to obtain one execution result. Such result combination processing will be described with reference to the flowchart in FIG.

[0206] Next, an example of the query construction process in step S1506 will be described with reference to the flowchart in FIG.

[0207] (Step S1601) Query execution unit 140 acquires one or more search items included in the query. Note that a search item is information that identifies an object included in the execution result. It can be said that a search item includes an object name included in a selection list.

[0208] (Step S1602) Query execution unit 140 acquires a search condition included in the query. Note that the search condition can be said to be, for example, a WHERE clause in a SELECT statement of SQL.

[0209] (Step S1603) Query execution unit 140 constructs a selection list in the query. An example of such selection list construction processing will be described with reference to the flowchart in FIG.

[0210] (Step S1604) Query execution unit 140 constructs a FROM clause in the query. An example of such FROM clause construction processing will be described with reference to the flowchart in FIG.

[0211] (Step S1605) Query execution unit 140 constructs a WHERE clause in the query. An example of such WHERE clause construction processing will be described with reference to the flowchart in FIG.

[0212] (Step S1606) Query execution unit 140 constructs a GROUP BY clause in the query. Then, the process returns to the upper level process. An example of the GROUP BY clause construction process will be described with reference to the flowchart in FIG.

[0213] Next, an example of the selection list construction process in step S1603 will be described with reference to the flowchart in FIG.

[0214] (Step S1701) Query execution unit 140 assigns 1 to counter i.

[0215] (Step S1702) Query execution unit 140 determines whether or not the i-th search item exists in the query. If the i-th search item exists, the process proceeds to step S1703, and if not, the process proceeds to step S1710.

[0216] (Step S1703) Query execution unit 140 acquires the i-th search item from the query.

[0217] (Step S1704) Query execution unit 140 judges whether or not an object name matching the i-th search item exists in the target object definition. If an object name matching the i-th search item exists, the process proceeds to step S1705; if not, the process proceeds to step S1709.

[0218] (Step S1705) Query execution unit 140 acquires, from the object definition, a calculation definition formula that is paired with an object name that matches the i-th search item.

[0219] (Step S1706) Query execution unit 140 determines whether or not the calculation definition formula acquired in step S1705 includes an arithmetic expression. If it includes an arithmetic expression, the process proceeds to step S1707, and if it does not include an arithmetic expression, the process proceeds to step S1708.

[0220] (Step S1707) Query execution unit 140 modifies the calculation definition formula using the object name that matches the i-th search item. Query execution unit 140 acquires, for example, a calculation definition formula in which "AS object name" is added to the calculation definition formula acquired in step S1705.

[0221] (Step S1708) Query execution unit 140 adds the calculation definition formula acquired in step S1705 or step S1707 to the selection list.

[0222] (Step S1709) Query execution unit 140 increments counter i by 1. The process returns to step S1702.

[0223] Next, an example of the FROM clause construction process in step S1604 will be described with reference to the flowchart in FIG.

[0224] (Step S1801) Query execution unit 140 assigns 1 to counter i.

[0225] (Step S1802) The query execution unit 140 judges whether or not the i-th calculation definition formula exists among the calculation definition formulas acquired in step S1705 of the selection list configuration process (FIG. 17). If the i-th calculation definition formula exists, the process proceeds to step S1803, and if not, the process proceeds to step S1805.

[0226] (Step S1803) The query execution unit 140 acquires from the joined table definition the table name that pairs with the i-th calculation definition formula among the calculation definition formulas acquired in step S1705 of the selection list construction process (FIG. 17).

[0227] (Step S1804) Query execution unit 140 increments counter i by 1. The process returns to step S1802.

[0228] (Step S1805) Query execution unit 140 performs unique processing on the one or more table names acquired in step S1803, and acquires one or more table names.

[0229] (Step S1806) Query execution unit 140 obtains the name of the higher-level table that is the reference source of the table obtained in step S1805 based on the joined table information, and recursively repeats this process to obtain the reference table name, which is the top-level table, and places it in the FROM clause.

[0230] (Step S1807) Query execution unit 140 assigns 1 to counter j.

[0231] (Step S1808) Query execution unit 140 judges whether or not the j-th table name exists from among one or more table names obtained by excluding the reference table name from the table names acquired in step S1805. If the j-th table name exists, the process proceeds to step S1809, and if it does not exist, the process returns to the upper process. Note that at this time, if it is an intermediate table used recursively in step S1806, the process also proceeds to step S1809.

[0232] (Step S1809) Query execution unit 140 acquires the join type and join condition paired with the j-th table name from the joined table definition.

[0233] (Step S1810) Query execution unit 140 constructs a character string for a join operation using the j-th table name and the join type and join condition acquired in step S1809, and places the character string in the FROM clause.

[0234] The character string of the join operation is, for example, "join type j-th table name and ON (join condition)". and join conditions

[0235] (Step S1811) Query execution unit 140 increments counter j by 1. The process returns to step S1808.

[0236] Next, an example of the WHERE clause construction process in step S1605 will be described with reference to the flowchart in FIG.

[0237] (Step S1901) Query execution unit 140 assigns 1 to counter i.

[0238] (Step S1902) Query execution unit 140 determines whether or not the i-th item name is present in the search conditions of the accepted query. If the i-th item name is present, the process proceeds to step S1903; if not, the process proceeds to step S1907.

[0239] (Step S1903) Query execution unit 140 acquires the i-th item name from the query.

[0240] (Step S1904) Query execution unit 140 acquires, from the object definition, a calculation definition formula that is paired with an object name that matches the i-th item name.

[0241] (Step S1905) Query execution unit 140 replaces the i-th item name in the search conditions with the calculation definition formula acquired in step S1904.

[0242] (Step S1906) Query execution unit 140 increments counter i by 1. The process returns to step S1902.

[0243] (Step S1907) Query execution unit 140 prepends the character string "WHERE" to the search condition replaced in step S1905 to form a WHERE clause, and then returns to the upper-level process.

[0244] Next, an example of the GROUP BY clause construction process in step S1606 will be described with reference to the flowchart in FIG.

[0245] (Step S2001) Query execution unit 140 assigns 1 to counter i.

[0246] (Step S2002) Query execution unit 140 determines whether or not the i-th search item exists. If the i-th search item exists, the process proceeds to step S2003, and if not, the process proceeds to step S2007.

[0247] (Step S2003) Query execution unit 140 acquires a category paired with an object name that matches the i-th search item from the object definition.

[0248] (Step S2004) Query execution unit 140 determines whether or not the category acquired in step S2003 is "key." If it is "key," the process proceeds to step S2005, and if it is not "key," the process proceeds to step S2006.

[0249] (Step S2005) Query execution unit 140 acquires a calculation definition expression that is paired with an object name that matches the i-th search item from the object definition, and adds the calculation definition expression to the GROUP BY clause.

[0250] (Step S2006) Query execution unit 140 increments counter i by 1. The process returns to step S2002.

[0251] (Step S2007) Query execution unit 140 adds the character string "GROUP BY" before the one or more calculation definition expressions added in step S2005 to form a GROUP BY clause, and returns to the upper level process.

[0252] Next, the result combining process in step S1510 will be described with reference to the flowchart in FIG.

[0253] (Step S2101) Query execution unit 140 acquires a common key among two or more views.

[0254] (Step S2102) Query execution unit 140 configures a query for a join operation that includes one or more search items in the selection list, under the condition that the attribute values ​​(object values) of the keys acquired in step S2101 match.

[0255] (Step S2103) Query execution unit 140 performs a join operation on the query in step S2102 for two or more views, obtains the execution result, and returns to the upper process.

[0256] A specific example of the operation of the query use device 1 in this embodiment when performing an information search will be described below. Two specific examples will be described below.

[0257] (Example 1) Now, assume that the view information shown in Fig. 22, for example, has been accumulated in the storage unit 11 by the processing of the above-mentioned processing unit 13 of the query use device 1. The view identifier of the view information here is "search view 1." Moreover, the view information is a set of an object definition (Fig. 22(a)) and a join table definition (Fig. 22(b)).

[0258] Then, for the "Search View 1", the user inputs the query "<Target> Search View 1 <Search Item> Customer Code, Customer Name, Product Code, Product Name, Sales Quantity, Sales Amount <Search Condition> Customer Code = 134" into the terminal device 2. Note that this query may be an SQL statement "SELECT Customer Code, Customer Name, Product Code, Product Name, Sales Quantity, Sales Amount FROM Search View 1 WHERE Customer Code = 134".

[0259] Next, the terminal device 2 accepts the query and transmits the query to the query use device 1.

[0260] Next, the query receiving unit 121 of the query use device 1 receives the query.

[0261] Then, query execution unit 140 acquires the execution result as follows, through the processes described using the flowcharts of FIGS.

[0262] That is, the query execution unit 140 acquires the search items "customer code, customer name, product code, product name, sales quantity, sales price" from the query. The query execution unit 140 also acquires the search condition "customer code = 134" from the query.

[0263] Next, the query execution unit 140, through the processing described using the flowchart in Figure 17, refers to the object definition (Figure 22(a)) and constructs a selection list in the query (Figure 23(a)) based on the search items "customer code, customer name, product code, product name, sales quantity, sales price."

[0264] Furthermore, query execution unit 140 constructs a FROM clause (FIG. 23(b)) in the query through the FROM clause construction process described using the flowchart in FIG.

[0265] Furthermore, query execution unit 140 constructs a WHERE clause (FIG. 23(c)) in the query by the WHERE clause construction process described using the flowchart in FIG.

[0266] Furthermore, query execution unit 140 constructs a GROUP BY clause (FIG. 23(d)) in the query through the GROUP BY clause construction process described using the flowchart in FIG.

[0267] Through the above processing, query execution unit 140 can construct the query (SELECT statement) in Fig. 22. Next, query execution unit 140 executes the SELECT statement in Fig. 22, and obtains the execution results based on the records in the tables "Order", "Customer", "Product", and "Order Details".

[0268] Next, the execution result output unit 151 transmits the execution result to the terminal device 2.

[0269] Next, the terminal device 2 receives and outputs the execution result.

[0270] (Example 2) Now, suppose that, through the processing of the processing unit 13 described above, for example, a pair of object definition and joined table definition shown in Figure 22 ("search view 1") and a pair of object definition and joined table definition shown in Figure 24 ("search view 2") are stored in the storage unit 11.

[0271] Also, assume that the post-integration scope acquisition unit 138 integrates the object definition of search view 1 and the object definition of search view 2, and the post-integration scope information is acquired and stored by the process described using the flowchart in Fig. 14. Such post-integration scope information is "scope 1". Moreover, the post-integration scope information is as shown in Fig. 9(c). Note that Fig. 9(c) corresponds to "search view 1" (Fig. 22) and "search view 2" (Fig. 24).

[0272] Then, for the "scope 1", the user inputs the query "<target> scope 1 <search item> product code, product name, sales quantity, purchase amount, gross profit amount = sales amount - purchase amount <search condition> product code = 1020" into the terminal device 2. Note that this query may be an SQL statement "SELECT product code, product name, sales quantity, purchase amount, gross profit amount = sales amount - purchase amount FROM scope 1 WHERE product code = 1020".

[0273] Next, the terminal device 2 accepts the query and transmits the query to the query use device 1.

[0274] Next, the query receiving unit 121 of the query use device 1 receives the query.

[0275] Then, query execution unit 140 acquires the execution result as follows, through the processes described using the flowcharts of FIGS.

[0276] That is, the query execution unit 140 acquires "product code, product name, sales quantity, purchase amount, sales amount" from the search item "product code, product name, sales quantity, purchase amount, gross profit amount = sales amount - purchase amount" in the query. The query execution unit 140 also acquires the search condition "product code = 1020" in the query. The query execution unit 140 also acquires "search view 1" and "search view 2" associated with "scope 1" from the search target "scope 1" in the query.

[0277] Next, the query execution unit 140, through the processing described using the flowchart in Figure 17, refers to the object definition of search view 1 (Figure 22(a)) based on the search items "product code, product name, sales quantity, purchase price, sales price" and constructs a selection list in the query (Figure 25(A)(a)).

[0278] In addition, the query execution unit 140, through the processing described using the flowchart in Figure 17, refers to the object definition of search view 2 (Figure 24(a)) based on the search items "product code, product name, sales quantity, purchase price, sales price" and constructs a selection list in the query (Figure 25(B)(a)).

[0279] Furthermore, the query execution unit 140 constructs a FROM clause (FIGS. 25(A)(b)) in the query for "search view 1" through the FROM clause construction process described using the flowchart in FIG.

[0280] Furthermore, the query execution unit 140 constructs a FROM clause (FIG. 25(B)(b)) in the query for "query view 2" through the FROM clause construction process described using the flowchart in FIG.

[0281] Furthermore, the query execution unit 140 constructs a WHERE clause (FIG. 25(A)(c)) in the query for "query view 1" through the WHERE clause construction process described using the flowchart in FIG.

[0282] Furthermore, the query execution unit 140 constructs a WHERE clause (FIG. 25(B)(c)) in the query for "query view 2" through the WHERE clause construction process described using the flowchart in FIG.

[0283] Furthermore, the query execution unit 140 constructs a GROUP BY clause (FIG. 25(A)(d)) in the query for "search view 1" through the GROUP BY clause construction process described using the flowchart in FIG.

[0284] Furthermore, the query execution unit 140 constructs a GROUP BY clause (FIG. 25(B)(d)) in the query for "search view 2" through the GROUP BY clause construction process described using the flowchart in FIG.

[0285] Through the above processing, the query execution unit 140 constructs a query for "search view 1" (FIG. 25(A)) and a query for "search view 2" (FIG. 25(B)).

[0286] Next, query execution unit 140 executes the query (FIG. 25(A)) and obtains execution result 1. Also, query execution unit 140 executes the query (FIG. 25(B)) and obtains execution result 2.

[0287] Next, the query execution unit 140 performs a join operation on execution result 1 and execution result 2 using the “product code” as a key, in accordance with the process described using the flowchart in FIG. 21, and obtains an execution result that combines execution result 1 and execution result 2.

[0288] Next, the execution result output unit 151 transmits the execution result to the terminal device 2.

[0289] Next, the terminal device 2 receives and outputs the execution result.

[0290] As described above, according to this embodiment, it is possible to acquire view information for easily obtaining required information without having to master complex queries.

[0291] Furthermore, according to this embodiment, required information can be easily obtained without having to master complex queries.

[0292] The processing in this embodiment may be realized by software. This software may be distributed by software download or the like. This software may also be recorded on a recording medium such as a CD-ROM and distributed. This also applies to the other embodiments in this specification. The software that realizes the query utilization device 1 in this embodiment is a program such as the following. In other words, this program causes a computer to function as: a query acquisition unit that acquires two or more queries for a table in a database from a query storage unit in which the two or more queries are stored; an object definition acquisition unit that acquires, for each of the two or more queries, an object definition having two or more object information from the query, which has an object name and a table name of a table in which an object identified by the object name is stored; a post-integrated object definition acquisition unit that acquires, from the two or more object definitions acquired by the object definition acquisition unit, a post-integrated object definition having one or more object information that makes the object information held by the two or more object definitions unique; a post-integrated object definition storage unit that accumulates the post-integrated object definition; a joined table definition acquisition unit that acquires, for each of the two or more queries, a joined table definition having a reference table name that identifies a reference table from the query and one or more joined table information having table names of two tables to be subjected to a join operation; a post-integrated table definition acquisition unit that acquires, from the two or more joined table definitions acquired by the joined table definition acquisition unit, a post-integrated table definition having one or more joined table information that makes two or more joined table information paired with the same reference table name unique; and a post-integrated table definition storage unit that accumulates the post-integrated table definition.

[0293] 26 shows the appearance of a computer that executes the programs described in this specification to realize the query utilization device 1 and the like of the various embodiments described above. The above-described embodiments can be realized by computer hardware and a computer program executed thereon. FIG. 26 is an overview of this computer system 300, and FIG. 27 is a block diagram of the system 300.

[0294] In FIG. 26, a computer system 300 includes a computer 301 including a CD-ROM drive, a keyboard 302, a mouse 303, and a monitor 304.

[0295] 27, computer 301 includes, in addition to CD-ROM drive 3012, MPU 3013, bus 3014 connected to CD-ROM drive 3012 etc., ROM 3015 for storing programs such as a boot-up program, RAM 3016 connected to MPU 3013 for temporarily storing instructions of application programs and providing temporary storage space, and hard disk 3017 for storing application programs, system programs, and data. Although not shown here, computer 301 may further include a network card for providing connection to a LAN.

[0296] A program that causes computer system 300 to execute functions of query utilization device 1 and the like of the above-described embodiment may be stored on CD-ROM 3101, inserted into CD-ROM drive 3012, and further transferred to hard disk 3017. Alternatively, the program may be transmitted to computer 301 via a network (not shown) and stored on hard disk 3017. The program is loaded into RAM 3016 at the time of execution. The program may also be loaded directly from CD-ROM 3101 or the network.

[0297] The program does not necessarily include an operating system (OS) or a third party program that causes the computer 301 to execute the functions of the query utilization device 1 of the above-mentioned embodiment. The program only needs to include an instruction portion that calls appropriate functions (modules) in a controlled manner to obtain a desired result. How the computer system 300 operates is well known, and a detailed description will be omitted.

[0298] The program may be executed by a single computer or a plurality of computers. That is, the program may be executed by a centralized processing or a distributed processing.

[0299] Furthermore, in each of the above embodiments, it goes without saying that two or more communication means present in one device may be physically realized by one medium.

[0300] In each of the above embodiments, each process may be realized by centralized processing in a single device, or may be realized by distributed processing in a plurality of devices.

[0301] The present invention is not limited to the above-described embodiment, and various modifications are possible, and it goes without saying that these modifications are also included within the scope of the present invention. [Industrial Applicability]

[0302] As described above, the query use device 1 according to the present invention has an effect of allowing required information to be easily obtained without the need to master complex queries, and is useful as a server or the like that performs information searches.

Claims

1. a query acquisition unit that acquires two or more queries for a table in a database from a query storage unit in which the two or more queries are stored; an object definition acquisition unit that acquires, for each of the two or more queries, an object definition having two or more pieces of object information each having an object name and a table name of a table in which an object identified by the object name is stored from the query; an integrated object definition acquisition unit that acquires, from the two or more object definitions acquired by the object definition acquisition unit, an integrated object definition having one or more pieces of object information that make unique the object information held by the two or more object definitions; an integrated object definition storage unit that stores the integrated object definition; a join table definition acquisition unit that acquires, for each of the two or more queries, a reference table name that identifies a reference table from the query and a join table definition having one or more pieces of join table information that have table names of two tables to be joined; an integrated table definition acquisition unit that acquires, from the two or more joined table definitions acquired by the joined table definition acquisition unit, an integrated table definition having one or more joined table information that is made unique by making two or more joined table information that are paired with the same reference table name; a post-integration table definition storage unit for storing the post-integration table definition,

2. The object information includes a calculation definition formula, which is information for acquiring an object, and a category indicating whether the object is a key or not; The object definition acquisition unit 2 . The query utilization device according to claim 1 , wherein a calculation definition formula and a category are obtained from the query for each of the two or more queries, and the object definition having one or more pieces of object information having the calculation definition formula and the category is obtained.

3. a query receiving unit that receives a query for the integrated object definition and the integrated table definition; a query execution unit that acquires the integrated object definition and the integrated table definition for the query accepted by the query acceptance unit, and acquires an execution result that is satisfied by the query, using the query, the integrated object definition, and the integrated table definition; 3. The query utilization device according to claim 1, further comprising an execution result output unit that outputs the execution result.

4. The object information includes a calculation definition formula, which is information for acquiring an object, and a category indicating whether the object is a key or not; a post-integration scope acquisition unit that acquires the two or more post-integration object definitions acquired by the post-integration object definition acquisition unit, and acquires post-integration scope information having one or more pieces of scope information each having an object name owned by two or more post-integration object definitions having one or more key objects in common among the two or more post-integration object definitions, and one or more calculation definition expressions paired with the object name; The query utilization device according to claim 1 , further comprising: an integrated scope storage unit that stores the integrated scope information acquired by the integrated scope acquisition unit.

5. a query receiving unit that receives a query for a scope specified by the integrated scope information; a query execution unit that acquires the post-integration scope information and the post-integration table definition for the query accepted by the query acceptance unit, and acquires an execution result that is satisfied by the query, using the query, the post-integration scope information, and the post-integration table definition; The query utilization device according to claim 4 , further comprising an execution result output unit that outputs the execution result.

6. The query utilization device according to claim 1 , wherein the two or more queries in the query storage unit are queries to at least two or more different databases.

7. A method for producing view information, which is realized by a query acquisition unit, an object definition acquisition unit, an integrated object definition acquisition unit, an integrated object definition storage unit, a join table definition acquisition unit, an integrated table definition acquisition unit, and an integrated table definition storage unit, comprising: a query acquisition step in which the query acquisition unit acquires two or more queries for a table in a database from a query storage unit in which the two or more queries are stored; an object definition acquisition step in which the object definition acquisition unit acquires, for each of the two or more queries, an object definition having two or more pieces of object information from the query, the object definition having an object name and a table name of a table in which an object identified by the object name is stored; an integrated object definition acquisition step in which the integrated object definition acquisition unit acquires, from the two or more object definitions acquired by the object definition acquisition unit, an integrated object definition having one or more pieces of object information that make unique the object information held by the two or more object definitions; an integrated object definition storage step in which the integrated object definition storage unit stores the integrated object definition; a join table definition acquisition step in which the join table definition acquisition unit acquires, for each of the two or more queries, a join table definition having a reference table name for identifying a reference table from the query and one or more pieces of join table information having table names of two tables to be joined; an integrated table definition acquisition step in which the integrated table definition acquisition unit acquires an integrated table definition having one or more pieces of joined table information that are unique from two or more pieces of joined table information paired with the same reference table name, from the two or more joined table definitions acquired by the joined table definition acquisition unit; a post-integration table definition storage step in which the post-integration table definition storage unit stores the post-integration table definition.

8. Computer, a query acquisition unit that acquires two or more queries for a table in a database from a query storage unit in which the two or more queries are stored; an object definition acquisition unit that acquires, for each of the two or more queries, an object definition having two or more pieces of object information each having an object name and a table name of a table in which an object identified by the object name is stored from the query; an integrated object definition acquisition unit that acquires, from the two or more object definitions acquired by the object definition acquisition unit, an integrated object definition having one or more pieces of object information that make unique the object information held by the two or more object definitions; an integrated object definition storage unit that stores the integrated object definition; a join table definition acquisition unit that acquires, for each of the two or more queries, a join table definition having a reference table name that identifies a reference table from the query and one or more pieces of join table information having table names of two tables to be joined; an integrated table definition acquisition unit that acquires, from the two or more joined table definitions acquired by the joined table definition acquisition unit, an integrated table definition having one or more joined table information that is made unique by making two or more joined table information that are paired with the same reference table name; A program for causing the integrated table definition storage unit to function as an integrated table definition storage unit for storing the integrated table definition.

Images