Automatic generation of database queries

• US 6,148,296 A
• Filed: 02/04/1998
• Issued: 11/14/2000
• Est. Priority Date: 02/04/1998
• Status: Expired due to Term

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Tabbara, Bassam
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): Corrielus, Jean M
• Application Number:
  US09/018,287
• Time in Patent Office: 1,014 Days
• Field of Search: 707/2, 707/3, 707/4, 707/103, 707/100, 707/104
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 16/2433 Query languages
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 16/289 Object oriented databases
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A computer program for automatically generating queries to a database management system (DBMS) is disclosed. The computer program receives a high level specification of the data sought to be retrieved from an application program. The high level specification includes the columns from which data is sought, and any constraints on the data to be retrieved from those columns such as filter constraints. The computer program also receives a context for the columns to be queried, so that it can be determined which tables of the DBMS the columns are associated with. The computer program further receives a specification of the schema of the DBMS, with the schema specifying the relationship between the various data storage entities of the DBMS. Using the high level specification and the schema, the computer program automatically generates queries to the DBMS seeking to reduce the complexity of the queries to speed execution, and to reduce the number of round trips between the computer program and the DBMS, also to enhance speed. For this purpose, queries may be separated into primary and secondary queries, and batched together, with secondary queries using data retrieved in a primary query in order to retrieve further data.

Claims

1. A computerized method for automatically generating queries for and retrieving data from a database management system (DBMS) wherein the data held in the DBMS is organized and maintained in related data structures and wherein the relationship and organization of the data structures is specified by a schema, and wherein the DBMS receives and executes queries written in a code of a query language, the method comprising:

a) receiving a specification of a schema of a DBMS from which data is to be retrieved;

b) receiving a specification of a structure for holding data retrieved from the DBMS;

c) receiving a specification of the data sought to be retrieved from the DBMS;

d) using the specification of the schema and the specification of data sought to be retrieved automatically generating query language code to carry out two or more queries of the DBMS which will retrieve the sought after data, a first one of the queries being a primary query for retrieving a first set of data, and a second one of the queries being a secondary query for retrieving a second set of data, the secondary query using data retrieved in the primary query to obtain the second set of data;

e) automatically sending at least the first and second queries to the DBMS at different successive times and retrieving the associated data; and

f) organizing at least some of the retrieved data into the specified structure for holding data retrieved from the DBMS.

2. A method according to claim 1 further wherein the specification of the data sought to be retrieved includes:

column information specifying a column in the database from which data is to be retrieved;

filter information specifying a subset of the data in a column; and

context information specifying the relationship of the column to other data entities in the DBMS.

3. A method according to claim 1 further wherein at least some of the queries are organized in batches and sent to the DBMS in batches.

4. A method according to claim 1 further wherein the queries are generated substantially so as to reduce the complexity of individual queries to improve the speed of execution of the query by the DBMS.

5. A method according to claim 4 further wherein the queries are generated so that they reduce the number of round trips between the entity issuing the queries and the DBMS.

6. A method according to claim 1 further wherein the primary and secondary queries are organized according to the context of the data to be retrieved.

7. A system for automatically generating queries for and retrieving data from a database, comprising:

a database management system (DBMS) operating on a computer wherein data held in the DBMS is organized and maintained in related data structures and wherein the relationship and organization of the data structures is specified by a schema, and wherein the DBMS receives and executes queries written in a code of a query language;

an application program executing on a computer;

query generating program executing on a computer to perform the tasks of;

a) receiving from the application program a specification of a schema of a DBMS from which data is to be retrieved,

b) receiving from the application program a specification of a structure for holding data retrieved from the DBMS,

c) receiving from the application program a specification of the data sought to be retrieved from the DBMS,

d) using the specification of the schema and the specification of data sought to be retrieved automatically generating query language code to carry out two or more queries of the DBMS which will retrieve the sought after data, a first one of the queries being a primary query for retrieving a first set of data, and a second one of the queries being a secondary query for retrieving a second set of data, the secondary query using data retrieved in the primary query to obtain the second set of data,

e) automatically sending at least the first and second queries to the DBMS at different successive times and retrieving the associated data;

f) organizing at least some of the retrieved data into the specified structure for holding data retrieved from the DBMS; and

g) sending the data held in the specified structure to the application program.

8. A system according to claim 7 further wherein the specification of the data sought to be retrieved includes:

column information specifying a column in the database from which data is to be retrieved;

filter information specifying a subset of the data in a column; and

context information specifying the relationship of the column to other data entities in the DBMS.

9. A system according to claim 7 further wherein at least some of the queries are organized in batches and sent to the DBMS in batches.

10. A system according to claim 7 further wherein the queries are generated substantially so as to reduce the complexity of individual queries to improve the speed of execution of the query by the DBMS.

11. A system according to claim 10 further wherein the queries are generated so that they reduce the number of round trips between the entity issuing the queries and the DBMS.

12. A system according to claim 7 further wherein the primary and secondary queries are organized according to the context of the data to be retrieved.

13. An article of manufacture comprising computer program code recorded on a computer-readable media, the computer program code executable on a computer for automatically generating queries for and retrieving data from a database management system (DBMS) wherein the data held in the DBMS is organized and maintained in related data structures and wherein the relationship and organization of the data structures is specified by a schema, and wherein the DBMS receives and executes queries written in a code of a query language, the computer program code operating a computer to:

g) receive a specification of a schema of a DBMS from which data is to be retrieved;

h) receive a specification of a structure for holding data retrieved from the DBMS;

i) receive a specification of the data sought to be retrieved from the DBMS;

j) use the specification of the schema and the specification of data sought to be retrieved to automatically generate query language code to carry out two or more queries of the DBMS which will retrieve the sought after data, a first one of the queries being a primary query for retrieving a first set of data, and a second one of the queries being a secondary query for retrieving a second set of data, the secondary query using data retrieved in the primary query to obtain the second set of data;

k) automatically send at least the first and second queries to the DBMS at different successive times and retrieving the associated data; and

l) organize at least some of the retrieved data into the specified structure for holding data retrieved from the DBMS.

14. An article of manufacture according to claim 13 further wherein the specification of the data sought to be retrieved includes:

column information specifying a column in the database from which data is to be retrieved;

filter information specifying a subset of the data in a column; and

context information specifying the relationship of the column to other data entities in the DBMS.

15. An article of manufacture according to claim 13 further wherein at least some of the queries are organized in batches and sent to the DBMS in batches.

16. An article of manufacture according to claim 13 further wherein the queries are generated substantially so as to reduce the complexity of individual queries to improve the speed of execution of the query by the DBMS.

17. An article of manufacture according to claim 16 further wherein the queries are generated so that they reduce the number of round trips between the entity issuing the queries and the DBMS.

18. An article of manufacture according to claim 13 further wherein the primary and secondary queries are organized according to the context of the data to be retrieved.