RETRIEVING INFORMATION FROM A RELATIONAL DATABASE USING USER DEFINED FACETS IN A FACETED QUERY

• US 20120197849A1
• Filed: 01/31/2011
• Published: 08/02/2012
• Est. Priority Date: 01/31/2011
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Srinivasa, Deepak M., Ramamurthy, Adarsh, Kumar, Samanvitha
• Granted Patent: US 9,208,195 B2
• Time in Patent Office: Days
• US Class Current: 707/687
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 16/2457 with adaptation to user needs

Abstract

A method, system and computer program product for retrieving information from a relational database using user defined facets in a faceted query may include receiving a faceted query and receiving at least one user defined facet group query. The method may also include filtering out facets in the faceted query that relate to metadata in the relational database. The method may additionally include associating each remaining facet in the faceted query with a corresponding user defined facet group query of the at least one user defined facet group query to provide a set of user defined facet groups. An SQL query may be generated for the faceted query using the set of user defined facet groups Information from the relational database may be retrieved responsive to the SQL query.

Claims

1. A method for retrieving information from a relational database using user defined facets in a faceted query, the method comprising:

receiving, by a processing device, a faceted query;

receiving, by the processing device, at least one user defined facet group query;

filtering out, by the processing device, facets in the faceted query that relate to metadata in the relational database;

associating, by the processing device, each remaining facet in the faceted query with a corresponding user defined facet group query of the at least one user defined facet group query to provide a set of user defined facet groups;

generating, by the processing device, an SQL query for the faceted query using the set of user defined facet groups; and

retrieving, by the processing device, the information from the relational database responsive to the SQL query.

2. The method of claim 1, further comprising generating a pivot query based on the set of user defined facet groups including each remaining facet in the faceted query that is associated with the corresponding user defined facet group query of the at least one user defined facet group query.

3. The method of claim 2, further comprising:

generating at least one pivot table from the pivot query; and

generating the SQL query for the faceted query by considering the at least one pivot table as a virtual table for query composition.

4. The method of claim 1, further comprising:

verifying that each remaining facet corresponds to one of the at least one user defined facet group queries;

flagging an error in response to any remaining facet not corresponding to one of the at least one user defined facet group queries; and

exiting a query routine in response to any remaining facet not corresponding to one of the at least one user defined facet group queries.

5. The method of claim 1, further comprising creating the at least one user defined facet group query.

6. The method of claim 5, further comprising receiving a name of a facet group, wherein the name relates to a portion of data in the relational database.

7. The method of claim 6, further comprising receiving the facet group query associated with the facet group name.

8. The method of claim 7, wherein receiving the facet group query comprises:

receiving a definition of each facet in the facet group query;

receiving a value corresponding to each facet in the facet group query; and

receiving a link associated with each facet to other facets, wherein the link associated with each facet is useable to relate the facet and its value with other data in the relational database.

9. The method of claim 8, further comprising generating a link association by associating a table attribute belonging to a relational schema of the relational database with a link column of a user defined facet group query output, wherein the association is made by receiving a name of the table attribute for each user defined facet group created.

10. The method of claim 5, further comprising:

checking for any conflict between a newly created user defined facet group and any previously created user defined facet groups; and

generating an error condition flag or message in response to any conflict.

11. The method of claim 1, further comprising providing a mechanism to define facets to point to portions of data in the relational database that are metadata elements in a domain data model.

12. The method of claim 11, further comprising:

receiving information about which elements in the relational database map to a facet in the domain data model;

receiving information about which elements in the relational database provide a value for each facet to which a corresponding element maps; and

receiving information related to linking of each facet to which the corresponding element maps and provides the value to other facets in the at least one user defined facet group query.

13. A system for retrieving information from a relational database using user defined facets in a faceted query, the system comprising:

a processing device;

a faceted query engine operable on the processing device, wherein the faceted query engine comprises;

a module for receiving a faceted query;

a module for receiving at least one user defined facet group query;

a module for filtering out facets in the faceted query that relate to metadata in the relational database;

a module for associating each remaining facet in the faceted query with a corresponding user defined facet group query of the at least one user defined facet group query to provide a set of user defined facet groups;

a module for generating an SQL query for the faceted query using the set of user defined facet groups; and

a module for retrieving the information from the relational database responsive to the SQL query.

14. The system of claim 13, further comprising a module for generating a pivot query based on the set of user defined facet groups including each remaining facet in the faceted query that is associated with the corresponding user defined facet group query of the at least one user defined facet group query.

15. The system of claim 14, further comprising:

a module for generating at least one pivot table from the pivot query; and

a module for generating the SQL query for the faceted query by considering the at least one pivot table as a virtual table for query composition.

16. The system of claim 14, further comprising a user defined facet module for creating the at least one user defined facet group query.

17. The system of claim 16, wherein the user defined facet module comprises:

a module for receiving a name of a facet group, wherein the name relates to a portion of data in the relational database;

a module for receiving the facet group query associated with the facet group name, wherein the module for receiving the facet group query comprises;

a module for receiving a definition of each facet in the facet group query;

a module for receiving a value corresponding to each facet in the facet group query; and

a module for receiving a link associated with each facet to other facets, wherein the link associated with each facet is useable to relate the facet and its value with other data in the relational database.

18. A computer program product to retrieve information from a relational database using user defined facets in a faceted query, the computer program product comprising:

a computer readable storage medium having computer readable program code embodied therewith, the computer readable program code comprising;

computer readable program code configured to receive a faceted query;

computer readable program code configured to receive at least one user defined facet group query;

computer readable program code configured to filter out facets in the faceted query that relate to metadata in the relational database;

computer readable program code configured to associate each remaining facet in the faceted query with a corresponding user defined facet group query of the at least one user defined facet group query to provide a set of user defined facet groups;

computer readable program code configured to generate an SQL query for the faceted query using the set of user defined facet groups; and

computer readable program code configured to retrieve the information from the relational database responsive to the SQL query.

19. The computer program product of claim 18, wherein the computer readable program code further comprises computer readable program code configured to generate a pivot query based on the set of user defined facet groups including each remaining facet in the faceted query that is associated with the corresponding user defined facet group query of the at least one user defined facet group query.

20. The computer program product of claim 19, wherein the computer readable program code further comprises:

computer readable program code configured to generate at least one pivot table from the pivot query; and

computer readable program code configured to generate the SQL query for the faceted query by considering the at least one pivot table as a virtual table for query composition.