Retrieving information from a relational database using user defined facets in a faceted query

• US 9,208,195 B2
• Filed: 01/31/2011
• Issued: 12/08/2015
• Est. Priority Date: 01/31/2011
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Srinivasa, Deepak M., Ramamurthy, Adarsh, Kumar, Samanvitha
• Primary Examiner(s): Trujillo, James
• Assistant Examiner(s): MUELLER, KURT A
• Application Number:
  US13/017,520
• Publication Number:
  US 20120197849A1
• Time in Patent Office: 1,772 Days
• Field of Search: 707/754
• US Class Current: 1/1
• 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, the method comprising:

receiving, by the processing device, a faceted query comprising a plurality of facets;

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

filtering out, by the processing device, to remove facets from the faceted query that are system-defined facets, the system-defined facets relating 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 group queries;

generating, by the processing device, a output from the set of user defined facet group queries applied to the relational database, the output comprising a results table consisting of three columns, a first column of the three columns being a facet column including a definition of a facet in each row of the results table, a second column of the three columns being a value associated with the facet in each row, and a third column of the three columns being a link column including a link associated with the facet in each row that relates the associated facet and its value to other data in the relational database;

generating, by the processing device, an SQL query for the faceted query using the set of user defined facet group queries; 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 group queries 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 of the faceted query after filtering corresponds to the at least one user defined facet group query;

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

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

5. The method of claim 1, 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.

6. The method of claim 1, further comprising:

checking for any conflict between each facet of the at least one user defined facet group query and any facet or another user defined facet group query; and

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

7. 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.

8. The method of claim 7, 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.

9. A system for retrieving information from a relational database, 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 comprising a plurality of facets;

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

a module for filtering out to remove facets from the faceted query that are system-defined facets, the system-defined facets relating 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 group queries;

a module for generating a output from the set of user defined facet group queries applied to the relational database, the output comprising a results table consisting of three columns, a first column of the three columns being a facet column including a definition of a facet in each row of the results table, a second column of the three columns being a value associated with the facet in each row, and a third column of the three columns being a link column including a link associated with the facet in each row that relates the associated facet and its value to other data in the relational database;

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

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

10. The system of claim 9, further comprising a module for generating a pivot query based on the set of user defined facet group queries 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.

11. The system of claim 10, 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.

12. A computer program product to retrieve information from a relational database, the computer program product comprising:

a non-transitory 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 comprising a plurality of facets;

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

computer readable program code configured to filter out to remove facets from the faceted query that are system-defined facets, the system-defined facets relating 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 group queries;

computer readable program code configured to generate a output from the set of user defined facet group queries applied to the relational database, the output comprising a results table consisting of three columns, a first column of the three columns being a facet column including a definition of a facet in each row of the results table, a second column of the three columns being a value associated with the facet in each row, and a third column of the three columns being a link column including a link associated with the facet in each row that relates the associated facet and its value to other data in the relational database;

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

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

13. The computer program product of claim 12, 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 group queries 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.

14. The computer program product of claim 13, 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.