Extended database search

• US 9,009,201 B2
• Filed: 05/13/2013
• Issued: 04/14/2015
• Est. Priority Date: 11/05/2010
• Status: Active Grant

• Current Assignee: Apple Inc.
• Original Assignee: Apple Inc.
• Inventors: Jacobson, Eric, Thomas, Les, Zhang, Gang
• Primary Examiner(s): Truong, Dennis
• Application Number:
  US13/893,122
• Publication Number:
  US 20130246437A1
• Time in Patent Office: 701 Days
• Field of Search: 707/706, 707/707, 707/711, 707/732, 707/741, 707/755, 707/756, 707/999.003, 707/E17.044, 707/E17.707, 707/E17.058, 707/811
• US Class Current: 707/811
• CPC Class Codes: G06F 16/2272 Management thereof
• CPC Class Codes: G06F 16/2458 Special types of queries, e.g. statistical queries, fuzzy queries or distributed queries
• CPC Class Codes: G06F 16/256 in federated or virtual databases
• CPC Class Codes: G06F 16/3331 Query processing

Abstract

Methods, program products, and systems implementing integrated repository of structured and unstructured data are disclosed. An integrated environment is provided for accessing structured data (e.g., data of a relational database) and unstructured data (e.g., data stored in a text or binary file), including creating, managing, modifying, and searching the structured data and unstructured data. The integrated environment can include an integrated user interface, a set of commands and application programming interface (API), and storage for a relational database and a document repository. The integrated environment can include a database abstraction layer that allows database operations on both the structured data and the unstructured data.

Claims

1. A method comprising:

receiving, for inserting into a database, structured data and unstructured data, the structured data including data for storing in the database as one or more data records, the unstructured data including an electronic document;

recording an insertion event, including inserting a first data record corresponding to the structured data into an event data structure and inserting a second data record corresponding to the unstructured data into the event data structure;

determining, using an indexing agent that monitors the event data structure for the insertion event, a combined index for the structured data and unstructured data, wherein determining the combined index comprises;

determining, by the indexing agent and storing in a collection data structure, a record for the structured data upon detecting an insertion of the first data record into the event data structure;

determining, by the indexing agent and storing in a document data structure, a record for the unstructured data upon detecting an insertion of the second data record into the event data structure, the document data structure comprising a document table and a file table, the document table comprising a file identifier data field for storing a foreign key to the file table, the file table comprising a tenant specific data table for storing information for unstructured data uploaded by the tenant, the tenant being a work group including one or more user computers; and

determining the combined index based on the record in the collection data structure and the record in the document data structure; and

providing the combined index to a search module for performing a search in the structured data and unstructured data,

wherein the method is performed by one or more computers.

2. The method of claim 1, wherein receiving the unstructured data includes:

receiving an input to insert the unstructured data into a table of the database; and

in response to the input;

storing the unstructured data separate from the database;

extracting metadata of the unstructured data; and

inserting the metadata and a document reference that refers to the stored unstructured data into the table of the database.

3. The method of claim 1, wherein determining the combined index based on the record in the collection data structure and the record in the document data structure comprises:

identifying, by the indexing agent, the unstructured data based on the record in the document data structure;

determining an index of the unstructured data based on content of the unstructured data; and

determining the combined index based on the index of the unstructured data.

4. The method of claim 3, wherein determining the combined index based on the index of the unstructured data comprises:

determining a record identifier from the first data record, the record identifier identifying a record of data in a table of the database, the record of data including a document reference to the unstructured data; and

associating the index of the unstructured data with a database index using the record identifier.

5. The method of claim 1, wherein the file table comprises a metadata data field for storing metadata generated based on the unstructured data and an internal file identifier field for storing an automatically generated name of the unstructured data.

6. The method of claim 1, wherein the file table comprises a reserved, read-only, and non-sortable data field to store a result of the search temporarily.

7. A system comprising:

one or more computer processors; and

a non-transitory storage medium coupled to the one more computer processors and storing instructions operable to cause the one more computer processors to perform operations comprising;

receiving, for inserting into a database, structured data and unstructured data, the structured data including data for storing in the database as one or more data records, the unstructured data including an electronic document;

recording an insertion event, including inserting a first data record corresponding to the structured data into an event data structure and inserting a second data record corresponding to the unstructured data into the event data structure;

determining, using an indexing agent that monitors the event data structure for the insertion event, a combined index for the structured data and unstructured data, wherein determining the combined index comprises;

determining, by the indexing agent and storing in a collection data structure, a record for the structured data upon detecting an insertion of the first data record into the event data structure;

determining, by the indexing agent and storing in a document data structure, a record for the unstructured data upon detecting an insertion of the second data record into the event data structure;

the document data structure comprising a document table and a file table, the document table comprising a file identifier data field for storing a foreign key to the file table, the file table comprising a tenant specific data table for storing information for unstructured data uploaded by the tenant, the tenant being a work group including one or more user computers; and

determining the combined index based on the record in the collection data structure and the record in the document data structure; and

providing the combined index to a search module for performing a search in the structured data and unstructured data.

8. The system of claim 7, wherein receiving the unstructured data includes:

receiving an input to insert the unstructured data into a table of the database; and

in response to the input;

storing the unstructured data separate from the database;

extracting metadata of the unstructured data; and

inserting the metadata and a document reference that refers to the stored unstructured data into the table of the database.

9. The system of claim 7, wherein determining the combined index based on the record in the collection data structure and the record in the document data structure comprises:

identifying, by the indexing agent, the unstructured data based on the record in the document data structure;

determining an index of the unstructured data based on content of the unstructured data; and

determining the combined index based on the index of the unstructured data.

10. The system of claim 9, wherein determining the combined index based on the index of the unstructured data comprises:

determining a record identifier from the first data record, the record identifier identifying a record of data in a table of the database, the record of data including a document reference to the unstructured data; and

associating the index of the unstructured data with a database index using the record identifier.

11. The system of claim 9, wherein the file table comprises a metadata data field for storing metadata generated based on the unstructured data and an internal file identifier field for storing an automatically generated name of the unstructured data.

12. The system of claim 9, wherein the file table comprises a reserved, read-only, and non-sortable data field to store a result of the search temporarily.

13. A non-transitory storage medium coupled to one more computer processors and storing instructions operable to cause the one more computer processors to perform operations comprising:

receiving, for inserting into a database, structured data and unstructured data, the structured data including data for storing in the database as one or more data records, the unstructured data including an electronic document;

recording an insertion event, including inserting a first data record corresponding to the structured data into an event data structure and inserting a second data record corresponding to the unstructured data into the event data structure;

determining, using an indexing agent that monitors the event data structure for the insertion event, a combined index for the structured data and unstructured data, wherein determining the combined index comprises;

determining, by the indexing agent and storing in a collection data structure, a record for the structured data upon detecting an insertion of the first data record into the event data structure;

determining, by the indexing agent and storing in a document data structure, a record for the unstructured data upon detecting an insertion of the second data record into the event data structure; and

the document data structure comprising a document table and a file table, the document table comprising a file identifier data field for storing a foreign key to the file table, the file table comprising a tenant specific data table for storing information for unstructured data uploaded by the tenant, the tenant being a work group including one or more user computers; and

determining the combined index based on the record in the collection data structure and the record in the document data structure; and

providing the combined index to a search module for performing a search in the structured data and unstructured data.

14. The non-transitory storage medium of claim 13, wherein receiving the unstructured data includes:

receiving an input to insert the unstructured data into a table of the database; and

in response to the input;

storing the unstructured data separate from the database;

extracting metadata of the unstructured data; and

inserting the metadata and a document reference that refers to the stored unstructured data into the table of the database.

15. The non-transitory storage medium of claim 13, wherein determining the combined index based on the record in the collection data structure and the record in the document data structure comprises:

identifying, by the indexing agent, the unstructured data based on the record in the document data structure;

determining an index of the unstructured data based on content of the unstructured data; and

determining the combined index based on the index of the unstructured data.

16. The non-transitory storage medium of claim 15, wherein determining the combined index based on the index of the unstructured data comprises:

determining a record identifier from the first data record, the record identifier identifying a record of data in a table of the database, the record of data including a document reference to the unstructured data; and

associating the index of the unstructured data with a database index using the record identifier.

17. The non-transitory storage medium of claim 13, wherein the file table comprises a metadata data field for storing metadata generated based on the unstructured data and an internal file identifier field for storing an automatically generated name of the unstructured data.

18. The non-transitory storage medium of claim 13, wherein the file table comprises a reserved, read-only, and non-sortable data field to store a result of the search temporarily.