Method and system for efficiently retrieving information from multiple databases

• US 6,366,915 B1
• Filed: 11/04/1998
• Issued: 04/02/2002
• Est. Priority Date: 11/04/1998
• Status: Expired due to Term

• Current Assignee: Mei California, Inc. (Micron Technology, Inc.)
• Original Assignee: Micron Technology, Inc.
• Inventors: Sastry, Saraswatula V., Dykes, Bren, Nair, Shreedev G., Hatch, Shane, Rubert, Amy
• Primary Examiner(s): Homere, Jean R.
• Assistant Examiner(s): Wassum, Luke S
• Application Number:
  US09/186,499
• Time in Patent Office: 1,245 Days
• Field of Search: 707/1-4, 707/9-10, 707/5
• US Class Current: 707/770
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• CPC Class Codes: G06F 21/6218 to a system of files or objects, e.g. local or distributed file system or database
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• CPC Class Codes: G06F 2221/2141 Access rights, e.g. capability lists, access control lists, access tables, access matrices
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99939 Privileged access

Abstract

A system for efficiently retrieving information from one of several databases. The system acts as an intermediary between users and databases, managing user access to the databases so that query specification, query execution, and query result retrieval can occur efficiently and securely. After verifying the identity of a user, the system determines the databases which the user is authorized to access and the database queries which the user is authorized to execute. The system then presents the user with available queries. If the user selects a predefined report form, the system presents the user with previously used options for that report form which the user can select. If the user selects a type of inquiry, the system presents the user with previously used inquiries of the selected type. After the user finishes specifying the query to be executed, the system then allows the user to schedule the query execution. When the scheduled execution time arrives, the system determines whether the selected query can be currently executed, and whether it is actually necessary to execute the query to return accurate query results. If execution is needed to obtain accurate query results and the selected query can be currently executed, the system automatically performs the query execution. After query execution is completed, the system notifies specified users of the query results, and ensures that the query results will be available to the requesting user and to other users.

Claims

1. A method for a user of a computer system to supply information to destination users from one of a plurality of databases each storing information, each database having a plurality of associated predefined queries that when executed each retrieve a specified subset of the information stored in the database, the computer-implemented method comprising:

verifying identity of the user;

for each of the plurality of databases,

after verifying the user identity, retrieving access privileges for the user which identify information stored in the database that the user is authorized to access; and

retrieving predefined queries associated with the database, the specified subset of each retrieved predefined query including information identified by the retrieved access privileges;

presenting only the retrieved predefined queries to the user that the user is authorized to access based on the user'"'"'s access privileges and making the other predefined queries associated with the database unavailable to the user based on the user'"'"'s access privileges;

receiving an indication of a retrieved predefined query selected by the user;

determining the database with which the selected query is associated;

determining whether executing the selected query will impose an excessive load on a database server for the determined database;

when the executing of the selected query will impose an excessive load, delaying the executing of the selected query until the executing will not impose an excessive load; and

when the executing of the selected query will not impose an excessive load, retrieving database information by

executing the selected query on the database server to retrieve from the determined database the subset of information specified by the selected query; and

sending to each of the destination users an indication of the retrieved subset of information.

2. The method of claim 1 wherein the selected query is a report form with modifiable options to vary the subset of information to be retrieved, each modifiable option to have an option value when the selected query is executed, and including:

before the determining of whether the executing will impose an excessive load,

presenting to the user at least one option value for each modifiable option of the selected query, the presented option values such that information has previously been retrieved from the determined database using the selected query with the presented option values for modifiable options;

receiving an indication from the user of an option value for each modifiable option of the selected query; and

modifying the selected query to include the indicated option values so that the information to be retrieved by the selected query has been varied.

3. The method of claim 1 including receiving from the user an indication of a scheduled time at which to execute the selected query, and wherein the determining of whether the executing will impose an excessive load is delayed until the indicated scheduled time.

4. The method of claim 1 including:

before the executing of the selected query,

determining whether the subset of information specified by the selected query was previously retrieved within a specified time period; and

when the subset was retrieved within the specified time period, supplying database information in lieu of the retrieving of the database information by

selecting the information previously retrieved within the specified time period; and

sending to each of the destination users an indication of the selected information.

5. The method of claim 1 including retrieving access information that enables access to the database server for the determined database, and wherein the executing of the selected query uses the retrieved access information.

6. A method for a user of a computer system to retrieve information from one of a plurality of databases using one of a plurality of predefined queries, each query for retrieving specified information from an associated one of the plurality of databases, the method comprising:

determining access privileges for the user that identify multiple predefined queries, each identified predefined query retrieving information which the user is authorized to access, the identified predefined queries including at least one predefined query associated with each of the plurality of databases;

after determining access privileges for the user, presenting to the user indications of only those identified predefined queries that the user is authorized to access for each of the plurality of databases and making the other predefined queries associated with each of the plurality of databases unavailable to the user based on the user'"'"'s access privileges;

receiving an indication of a selection by the user of an identified predefined query;

determining whether executing the selected query will impose an excessive load on a database server for the database associated with the selected query;

when the executing of the selected query will impose an excessive load, delaying the executing of the selected query until the executing will not impose an excessive load; and

when the executing of the selected query will not impose an excessive load, retrieving access information for accessing the database associated with the selected query; and

retrieving the specified information for the selected query by using the retrieved access information to access the database associated with the selected query.

7. The method of claim 6 wherein each predefined query has modifiable options to vary the specified information to be retrieved by the query, and including:

before the retrieving of the specified information for the selected query,

presenting to the user a value for a modifiable option of the selected query;

receiving an indication from the user of a value for the modifiable option; and

modifying the selected query to include the indicated value for the modifiable option.

8. The method of claim 7 wherein when the selected query was most recently used to retrieve information from the associated database, the selected query included the presented value for the modifiable option.

9. The method of claim 6 wherein the retrieved access information includes database access data needed to gain entry to a database server for the database.

10. The method of claim 6 wherein the retrieved access information includes database contact data needed to contact the database.

11. The method of claim 6 including receiving from the user an indication of a scheduled time at which to execute the selected query, and wherein the retrieving of the specified information for the selected query is delayed until the indicated scheduled time.

12. The method of claim 11 wherein the indication of the scheduled time includes an indication to execute the selected query periodically.

13. The method of claim 6 wherein the determining of the access privileges includes retrieving group privileges for the user and retrieving queries associated with the retrieved group privileges.

14. The method of claim 6 wherein the receiving of the indication of the selection by the user of the identified predefined query comprises:

receiving an indication of a specified query, the specified query suggested by the user and distinct from the identified predefined queries;

determining whether the specified query retrieves information which the user is authorized to access; and

when the specified query retrieves information which the user is authorized to access,

selecting the specified query as an identified predefined query; and

determining a database associated with the selected query.

15. The method of claim 14 including storing the specified query for future use as a predefined query.

16. A method for a computer system to efficiently retrieve information from a database using one of a plurality of predefined queries, each query for retrieving specified information from the database, the computer-implemented method comprising:

identifying multiple predefined queries that each retrieve information which the computer system is allowed to access;

presenting to a user indications of only those identified predefined queries that the user is authorized to access for the database and making the other of the multiple predefined queries associated with the database unavailable to the user based on the user'"'"'s access privileges;

receiving an indication of a selection of an identified predefined query;

determining whether retrieving the information specified by the selected query will produce a significant impact on performance of the database;

when the retrieving is determined not to produce a significant impact, retrieving from the database the information specified by the selected query; and

when the retrieving is determined to produce a significant impact,

determining whether information approximating the information specified by the selected query was previously retrieved; and

when the approximating information is determined to have been previously retrieved, selecting the approximating information in lieu of retrieving information from the database.

17. The method of claim 16 wherein each query has a specified time period, and wherein the approximating information is selected only when the approximating information was retrieved from the database within the specified time period for the selected query.

18. The method of claim 16 wherein the selected query is a form with a modifiable option to vary the specified information to be retrieved, wherein the selected query has a specified value for the modifiable option, and wherein the determining of whether previously retrieved information approximates the information specified by the selected query involves determining whether the previously retrieved information was retrieved using the selected form with the specified value for the modifiable option.

19. The method of claim 16 including:

receiving an indication of destination users; and

after the retrieving of the specified information from the database, notifying the destination users of the retrieved information.

20. The method of claim 16 including receiving an indication of a scheduled time at which to execute the selected query, and wherein the determining of whether retrieving the information will produce a significant impact is delayed until the indicated scheduled time.

21. The method of claim 16 wherein when the approximating information is determined to have not been recently retrieved, delaying retrieving from the database the information specified by the selected query until a later time.

22. The method of claim 16 wherein when the approximating information is determined to have not been previously retrieved, notifying a user of the computer system that the information specified by the selected query is not currently available.

23. A method for a server to retrieve information from a database using one of a plurality of predefined queries, each query when executed for retrieving specified information from the database, the method comprising:

receiving an indication of a user of a client computer;

verifying identity of the user;

supplying an indication of the verified user identity to a security computer distinct from the client computer in order to retrieve access privileges for the user;

identifying multiple predefined queries which the user is authorized to execute based on the retrieved access privileges;

presenting to the user only the identified predefined queries and making the other predefined queries associated with the database unavailable to the user based on the user'"'"'s retrieved access privileges;

receiving an indication of a selection by the user of an identified predefined query;

determining whether executing the selected query will impose an excessive load on a database server for the database associated with the selected query;

when the executing of the selected query will impose an excessive load, delaying the executing of the selected query until the executing will not impose an excessive load; and

when the executing of the selected query will not impose an excessive load, executing the selected query to retrieve the specified information for the selected query from the database.

24. The method of claim 23 wherein the receiving of the indication of the selection by the user of the identified predefined query comprises:

receiving an indication of a specified query, the specified query suggested by the user and distinct from the identified predefined queries;

determining whether the specified query retrieves information authorized by the retrieved access privileges; and

when the specified query retrieves information authorized by the retrieved access privileges,

selecting the specified query as an identified predefined query; and

determining a database associated with the selected query.

25. The method of claim 23 wherein each predefined query is associated with a group, and wherein the retrieved access privileges include group privileges for the user.

26. A method for a user to retrieve information from one of a plurality of databases using one of a plurality of predefined report forms, each report form for retrieving a specified type of information from an associated one of the plurality of databases and having options to vary information of the specified type to be retrieved, the computer-implemented method comprising:

identifying types of database information which the user is allowed to access;

identifying a plurality of predefined report forms that each retrieve an identified type of database information;

after determining access privileges for the user, notifying the user of only those identified predefined report forms that the user is authorized to access and making the other predefined report forms associated with the identified type of database information unavailable to the user based on the user'"'"'s access privileges;

determining whether a selection of an identified predefined report form is made by the user; and

when the user selects an identified predefined report form, retrieving database information by

determining the database associated with the selected report form;

determining the options for the selected report form;

notifying the user of at least one option value choice for each determined option of the selected report form, the option value choices such that information has previously been retrieved from the determined database using the selected report form with the option value choices for the determined options;

receiving an indication from the user of an option value choice for each determined option of the selected report form;

determining whether executing the selected option value choice will impose an excessive load on a database server for the database associated with the selected option value choice;

when the executing of the selected option value choice will impose an excessive load, delaying the executing of the selected option value choice until the executing will not impose an excessive load; and

when the executing of the selected option value choice will not impose an excessive load, retrieving from the determined database the information specified by the selected predefined report form with the indicated option value choices for the determined options.

27. The method of claim 26 wherein each user is a member of at least one group, and after the retrieving of the information from the determined database, notifying other members of the at least one group of the retrieved information.

28. The method of claim 26 including:

when the user specifies an inquiry distinct from the identified predefined report forms, the inquiry specifying information from a database, retrieving database information by

determining whether the information specified by the inquiry is of at least one of the identified types of database information; and

when the information is of at least one of the identified types, retrieving from the database the specified information for the inquiry.

29. A computer-readable medium containing instructions for a method of controlling a computer system to efficiently retrieve information for a user from one of a plurality of databases using one of a plurality of predefined queries, each query for retrieving specified information from an associated one of the plurality of databases, the method comprising:

determining access privileges for the user that identify multiple predefined queries, each identified predefined query retrieving information which the user is authorized to access, the identified predefined queries including at least one predefined query associated with each of the plurality of databases;

after determining access privileges for the user, presenting to the user indications of only those identified predefined queries that the user is authorized to access for each of the plurality of databases and making the other identified predefined queries associated with each of the plurality of databases unavailable to the user based on the user'"'"'s retrieved access privileges;

receiving an indication of a selection by the user of an identified predefined query;

retrieving access information for accessing the database associated with the selected query;

determining whether executing the selected query will impose an excessive load on a database server for the database associated with the selected query;

when the executing of the selected query will impose an excessive load, delaying the executing of the selected query until the executing will not impose an excessive load; and

when the executing of the selected query will not impose an excessive load, retrieving the specified information for the selected query by using the retrieved access information to access the database associated with the selected query.

30. The computer-readable medium of claim 29 wherein each predefined query has modifiable options to vary the specified information to be retrieved by the query, and wherein the computer system is further controlled to:

before the retrieving of the specified information for the selected query,

presenting to the user a value for a modifiable option of the selected query;

receiving an indication from the user of a value for the modifiable option; and

modifying the selected query to include the indicated value for the modifiable option.

31. The computer-readable medium of claim 29 wherein the retrieved access information includes database access data needed to gain entry to a database server for the database.

32. The computer-readable medium of claim 29 including receiving from the user an indication of a scheduled time at which to execute the selected query, and wherein the retrieving of the specified information for the selected query is delayed until the indicated scheduled time.

33. The computer-readable medium of claim 29 wherein the receiving of the indication of the selection by the user of the identified predefined query comprises:

receiving an indication of a specified query, the specified query suggested by the user and distinct from the identified predefined queries;

determining whether the specified query retrieves information which the user is authorized to access; and

when the specified query retrieves information which the user is authorized to access,

selecting the specified query as an identified predefined query; and

determining a database associated with the selected query.

34. A computer system for efficiently retrieving information from one of a plurality of databases using one of a plurality of predefined queries, each query for retrieving specified information from an associated one of the plurality of databases, comprising:

a query availability monitor that identifies multiple predefined queries, each identified predefined query retrieving information which a user of the computer system is authorized to access, the identified predefined queries including at least one predefined query associated with each of the plurality of databases;

a query selector that receives indications of the identified predefined queries from the query availability monitor, that presents only those indications of the identified predefined queries that the user is authorized to access to the user for each of the plurality of databases and makes the other identified predefined queries associated with each of the plurality of databases unavailable to the user based on the user'"'"'s retrieved access privileges, and that receives an indication of a selection by the user of an identified predefined query; and

a query executor that receives an indication of the selected query from the query selector, that retrieves access information for accessing the database associated with the selected query, and that retrieves the specified information for the selected query by using the retrieved access information to access the database associated with the selected query, wherein before the query executor retrieves the specified information by accessing the database associated with the selected query, the query executor determines whether retrieving the information specified by the selected query will produce a significant impact on performance of the associated database and if so, in lieu of retrieving information from the database selects information approximating the specified information if such approximating information was previously retrieved from the associated database.

35. The computer system of claim 34 including:

a result notifier that receives an indication of the retrieved specified information, that receives an indication of destination users, and that notifies the destination users of the retrieved information after the retrieving of the specified information from the database.

36. The computer system of claim 34 wherein each query has a specified time period, and wherein the approximating information is selected only when the approximating information was retrieved from the associated database within the specified time period for the selected query.

37. The computer system of claim 34 wherein each predefined query has modifiable options to vary the specified information to be retrieved by the query, and wherein the query selector additionally presents to the user a value for a modifiable option of the selected query, receives an indication from the user of a value for the modifiable option, and modifies the selected query to include the indicated value for the modifiable option.

38. The computer system of claim 34 wherein the receiving of the indication of the selection by the user of the identified predefined query includes:

receiving an indication of a specified query, the specified query suggested by the user and distinct from the identified predefined queries;

determining whether the specified query retrieves information which the user is authorized to access; and

when the specified query retrieves information which the user is authorized to access,

selecting the specified query as an identified predefined query; and

determining a database associated with the selected query.