Quick find for data fields

• US 8,849,840 B2
• Filed: 10/19/2012
• Issued: 09/30/2014
• Est. Priority Date: 08/12/2009
• Status: Active Grant

• Current Assignee: Apple Inc.
• Original Assignee: Apple Inc.
• Inventors: Zaydman, Oleg, Crim, Christopher, Maeckel, Clay, Johnson, Galt, Iremonger, Stephen
• Primary Examiner(s): Obisesan, Augustine K
• Application Number:
  US13/655,711
• Publication Number:
  US 20130138627A1
• Time in Patent Office: 711 Days
• Field of Search: None
• US Class Current: 707/755
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 715/969 Network layout and operation interface

Abstract

Methods, program products, and systems for automatically searching data in multiple data fields are described. A user can view data retrieved from a database and formatted based on a layout. While viewing the data, the user can perform a search using one or more search terms. A system can automatically examine the database and identify on which tables and on which data fields of the tables to perform the search. The system can identify searchable tables and data fields by analyzing the database schema as well as characteristics of the tables and data fields.

Claims

1. A method comprising:

obtaining a database query statement, the database query statement specifying a join of a plurality of database tables in a database, a condition for selecting one or more data records from the database tables, and one or more data fields to retrieve from the one or more data records;

determining a layout, the layout specifying that at least one of the database tables is a base table and that one or more other database tables of the plurality of database tables are related tables in a database, wherein the layout comprises an arrangement of the one or more data fields;

identifying, from the one or more data fields, a searchable data field, including;

identifying, from the one or more related tables, a related table as a searchable table based on a degree of separation between the related table and the base table, including identifying the searchable table when the degree of separation satisfies a threshold value, the degree of separation corresponding to a distance measured by a number of joins between the related table and the base table through one or more other related tables; and

automatically identifying a data field of the related table that is specified in the database query statement as the searchable data field from the related table;

receiving a search request separately from the database query statement, the search request including at least one search term;

constructing a quick search query statement using the search term and the database query statement, including replacing the condition of the database query statement for selecting the one or more data records with a new condition that includes the search term and specifying the related table in the quick query statement;

performing a search in the database by executing the quick search query statement; and

providing a result of the search as a response to the search request, wherein the method is performed by one or more computers.

2. The method of claim 1, wherein the one or more data fields are displayed in a data view of the base table, the identified searchable data field being hidden from display in the data view.

3. The method of claim 1, wherein the degree of separation is determined based on a number of equality-based joins between the base table and the related table, wherein a higher number of equality-based joins corresponds to a higher degree of separation.

4. The method of claim 1, wherein the degree of separation is further determined based on whether the base table contains a foreign key of the related table or whether the related table contains a foreign key of the base table.

5. The method of claim 1, wherein identifying the related table is based on an attribute of the related table that changes in response to an insertion of a data record into, a modification of a data record in, or a removal of a data record from the related table.

6. The method of claim 1, wherein the quick search query specifies one or more target search fields, the one or more target search fields being selected from the database query statement and one or more user-specified data fields based on a pre-determined limit.

7. A non-transitory storage device storing instructions operable to cause one or more processors to perform operations comprising:

obtaining a database query statement, the database query statement specifying a join of a plurality of database tables in a database, a condition for selecting one or more data records from the database tables, and one or more data fields to retrieve from the one or more data records;

determining a layout, the layout specifying that at least one of the database tables is a base table and that one or more other database tables of the plurality of database tables are related tables in a database, wherein the layout comprises an arrangement of the one or more of data fields;

identifying, from the one or more data fields, a searchable data field, including;

identifying, from the one or more related tables, a related table as a searchable table based on a degree of separation between the related table and the base table, including identifying the searchable table when the degree of separation satisfies a threshold value, the degree of separation corresponding to a distance measured by a number of joins between the related table and the base table through one or more other related tables; and

automatically identifying the searchable data field from the related table;

receiving a search request separately from the database query statement, the search request including at least one search term;

constructing a quick search query statement using the search term and the database query statement, including replacing the condition of the database query statement for selecting the one or more data records with a new condition that includes the search term and specifying the related table in the quick query statement;

performing a search in the database by executing the quick search query statement; and

providing a result of the search as a response to the search request.

8. The non-transitory storage device of claim 7, wherein the one or more data fields are displayed in a data view of the base table, the identified searchable data field being hidden from display in the data view.

9. The non-transitory storage device of claim 7, wherein the degree of separation is determined based on a number of equality-based joins between the base table and the related table, wherein a higher number of equality-based joins corresponds to a higher degree of separation.

10. The non-transitory storage device of claim 7, wherein the degree of separation is further determined based on whether the base table contains a foreign key of the related table or whether the related table contains a foreign key of the base table.

11. The non-transitory storage device of claim 7, wherein identifying the related table is based on an attribute of the related table that changes in response to an insertion of a data record into, a modification of a data record in, or a removal of a data record from the related table.

12. The non-transitory storage device of claim 7, wherein the quick search query specifies one or more target search fields, the one or more target search fields being selected from the database query statement and one or more user-specified data fields based on a pre-determined limit.

13. A system comprising:

one or more processors; and

a non-transitory storage device storing instructions operable to cause the one or more processors to perform operations comprising;

obtaining a database query statement, the database query statement specifying a join of a plurality of database tables in a database, a condition for selecting one or more data records from the database tables, and one or more data fields to retrieve from the one or more data records;

determining a layout, the layout specifying that at least one of the database tables is a base table and that one or more other database tables of the plurality of database tables are related tables in a database, wherein the layout comprises an arrangement of the one or more data fields;

identifying, from the one or more data fields, a searchable data field, including;

identifying, from the one or more related tables, a related table as a searchable table based on a degree of separation between the related table and the base table, including identifying the searchable table when the degree of separation satisfies a threshold value, the degree of separation corresponding to a distance measured by a number of joins between the related table and the base table through one or more other related tables; and

automatically identifying the searchable data field from the related table;

receiving a search request separately from the database query statement, the search request including at least one search term;

constructing a quick search query statement using the search term and the database query statement, including replacing the condition of the database query statement for selecting the one or more data records with a new condition that includes the search term and specifying the related table in the quick query statement;

performing a search in the database by executing the quick search query; and

providing a result of the search as a response to the search request.

14. The system of claim 13, wherein the one or more data fields are displayed in a data view of the base table, the identified searchable data field being hidden from display in the data view.

15. The system of claim 13, wherein the degree of separation is determined based on a number of equality-based joins between the base table and the related table, wherein a higher number of equality-based joins corresponds to a higher degree of separation.

16. The system of claim 13, wherein the degree of separation is further determined based on whether the base table contains a foreign key of the related table or whether the related table contains a foreign key of the base table.

17. The system of claim 13, wherein identifying the related table is based on an attribute of the related table that changes in response to an insertion of a data record into, a modification of a data record in, or a removal of a data record from the related table.

18. The system of claim 13, wherein the query search query specifies one or more target search fields, the one or more target search fields being selected from the database query statement and one or more user-specified data fields based on a pre-determined limit.