Method and system for supporting scrollable, updatable database queries

• US 5,666,526 A
• Filed: 05/16/1996
• Issued: 09/09/1997
• Est. Priority Date: 09/02/1993
• Status: Expired due to Term

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Bosworth, Adam, Reiter, Allen, Bellew, Matthew, Brandli, Stephen Albert, Hecht, Stephen Charles
• Primary Examiner(s): AMSBURY, WAYNE P
• Application Number:
  US08/649,910
• Time in Patent Office: 481 Days
• Field of Search: 395/600, 395/602
• US Class Current: 1/1
• CPC Class Codes: G06F 16/235 Update request formulation
• CPC Class Codes: G06F 16/2438 Embedded query languages
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

A method and system for supporting scrollable, updatable database queries is provided. In a preferred embodiment, a database engine receives a query request specifying the retrieval of data from a source table stored by the engine. The engine then generates a query table that contains references to rows of a source table that satisfy the query request. The engine then receives one or more retrieve requests requesting that data specified by the query be provided by the engine. For each retrieval request, the engine retrieves data from the source table using one Or more references from the query table.

Claims

1. A computer-readable medium containing instructions for causing a computer system to retrieve data from a database having a source table, the source table having rows with data, by:

receiving a query request that identifies a subset of rows of the source table;

generating a query result table containing references to rows of the source table that are identified by the received query request;

receiving a retrieve request to retrieve data from a specified source table row identified by the received query request;

retrieving from the query result table the reference to the specified source table row; and

retrieving the data from the specified source table row using the retrieved reference to locate the specified source table row.

2. The computer-readable medium of claim 1, further including:

after receiving the retrieve request, receiving a second retrieve request to retrieve data from a second specified source table row identified by the received query request;

retrieving from the query result table the reference to the second specified source table row; and

retrieving the data from the second specified source table row using the retrieved reference to locate the second specified source table row.

3. The computer-readable medium of claim 1 further including:

receiving an instruction to store update data in a second specified source table row;

retrieving from the query result table the reference to the second specified source table row; and

storing the update data into the second specified source table row using the retrieved reference to locate the second specified source table row.

4. A method in a computer system for displaying data retrieved from a source table in a database, the source table having rows with data, the computer system having a front-end program and a database engine, the method comprising:

under control of the front-end program,

receiving from a user a query request to retrieve rows of data from the database, the query request indicating a criterion that the data of the retrieved rows is to satisfy; and

sending the received query request to the database engine;

under control of the database engine,

receiving the sent query request;

identifying those rows of the source table with data that satisfies the criterion of the received query request; and

for each identified row, storing in a row of a query result table a reference to the identified row; and

for each of a plurality of retrieve requests to retrieve data that satisfies the criterion of the query request,

under control of the front-end program,

receiving the retrieve request from a user; and

sending the received retrieve request to the database engine;

under control of the database engine,

receiving the sent retrieve request;

in response to receiving the sent retrieve request, retrieving, from a row of the query result table, the stored reference to an identified row of the source table;

using the retrieved reference to retrieve from the source table the data of the identified row; and

sending the retrieved data to the front-end program; and

under control of the front-end program,

receiving the sent data; and

displaying the received data.

42. The database engine of claim 4 including

means for updating data of a selected record wherein the sending of the retrieved record sends the updated data after storing the record identifiers.

43. The database engine of claim 4 wherein the records are stored as rows of a source table of the database.

44. The database engine of claim 4 wherein the means for storing the record identifiers stores the record identifiers in rows of a query result table.

45. The database engine of claim 4 wherein the computer program is a front-end database program that displays the sent records.

5. A method in a database engine of a computer system for supplying to a computer program records from a database, each record having data and a record identifier, the method comprising:

receiving from the computer program a query request with a selection criterion for selecting records that satisfy the selection criterion;

selecting the records of the database that satisfy the selection criterion;

storing the record identifiers of each selected record;

receiving from the computer program a plurality of retrieve requests to retrieve data from the records that satisfy the selection criterion; and

for each received retrieve request,

selecting a stored record identifier;

retrieving the record from the database that is identified by the selected record identifier; and

sending to the computer program the retrieved record.

6. The method of claim 5 including

after storing the record identifiers, updating data of a selected record wherein the sending of the retrieved record sends the updated data.

7. The method of claim 5 wherein the records are stored as rows of a source table of the database.

8. The method of claim 5 wherein the storing of the record identifiers stores the record identifiers in rows of a query result table.

9. The method of claim 5 wherein the computer program is a front-end database program that displays the sent records.

10. A method in a computer system for propagating changes to data contained in records of a database from the database to a display of the data, the display generated by a front-end display program, the method comprising:

receiving from the front-end display program a query request that identifies records of the database;

generating a query result table containing references to records of the database that are identified by the received query request;

receiving a retrieve request from the front-end display program to retrieve data from a specified record identified by the received query request;

retrieving from the query result table the reference to the specified record;

retrieving the specified record from the database using the retrieved reference to locate the specified record in the database;

determining whether the data of the specified record has been changed since the specified record was last accessed by the front-end display program; and

when it is determined that the data of the specified record has changed, notifying the front-end display program that the data in the specified record has been changed so that the front-end display program can update the display.

11. The method of claim 10 wherein the determining of whether the specified record has been changed includes:

fetching a current record version indicator indicating a current state of the specified record; and

comparing the fetched current record version indicator to a record version indicator indicating the state of the specified record at the time it was last accessed.

12. The method of claim 11 wherein the fetching of a current record version indicator includes the fetching of a current record timestamp indicating a time at which the specified record was last changed, and wherein the comparing of the fetched current record version indicator to a last-fetched record version indicator includes comparing the fetched current record timestamp to a last-fetched record timestamp indicating the time at which the specified record was last accessed.

13. The method of claim 11 wherein the fetching of a current record version indicator includes the fetching of a current record checksum that encodes the data currently stored in the specified record, and wherein the comparing of the fetched current record version indicator to a last-fetched record version indicator includes comparing the fetched current record checksum to a last-fetched record checksum that encodes the data retrieved from the specified record when retrieving.

14. The method of claim 11 wherein the fetching of a current record version indicator includes the fetching of a current record version number indicating the number of times the specified record has been modified since a counter initialization time, and wherein the comparing of the fetched current record version indicator to a last-fetched record version indicator includes the step of comparing the fetched current record version number to a last-fetched record version number indicating the number of times the specified record had been modified between the counter initialization time and the time at which the retrieving was performed.

15. The method of claim 11 wherein upon receiving the notification, the front-end display program displays to a user a warning indicating that data associated with the specified record has changed.

16. The method of claim 11 wherein the changed data from the specified record is provided to the front-end display program at the same time that the front-end display program is notified that the data in the specified record has changed.

17. The method of claim 16 wherein the step of providing the changed data from the specified record includes re-retrieving the specified record using a reference from the query result table.

18. The method of claim 17 wherein the query specifies an expression for deriving data to be returned to the requesting program from data stored in the specified record, further including using the expression specified by the query to derive the data to be returned to the requesting program from the data in the specified record retrieved by the re-retrieving.

19. The method of claim 10 wherein the changed data from the specified record is provided to the front-end display program at the same time that the front-end display program is notified that the data in the specified record has changed.

20. The method of claim 10 wherein upon receiving the notification, the front-end display program displays to a user a warning indicating that data associated with the specified record has changed.

21. The method of claim 10 further comprising:

receiving a request from the front-end display program to retrieve the changed data of the specified record; and

in response to receiving the request, retrieving the changed data from the specified record and sending the retrieved data to the front-end display program so that changes to the data can be propagated to the display.

22. An apparatus for providing a current version of a subset of the data contained in a database having a source table, the source table having rows with data, the subset of the data specified by execution of a database query, execution of the database query identifying source table rows and generating a query result table, the query result table comprised of rows containing references to the identified source table rows, the apparatus comprising:

a source table memory for storing the contents of the source table;

a query memory for storing the query result table; and

a query contents retrieval engine for, in response to a request to retrieve data from the subset of data contained in the identified source table rows, specifying a row of the query result table, reading the reference from the row of the query result table, and using the read reference to retrieve and provide at least a portion of the referenced source table row.

23. The apparatus of claim 22 wherein the database is comprised of a plurality of source tables.

24. The apparatus of claim 23 wherein each row of the query result table contains a plurality of references to source table rows identified from among the plurality of source tables and the query retrieval engine retrieves at least a portion of each referenced source table row contained in the specified query result table row.

25. A method in a computer system for retrieving data from a database having a plurality of source tables, the source tables having rows with data, the method comprising:

receiving a query request that identifies a subset of source table rows from among the plurality of source tables;

generating a query result table containing references to source table rows that are identified by the received query request;

receiving a retrieve request to retrieve data from specified source table rows identified by the received query request;

retrieving from the query result table the references to the specified source table rows from among the plurality of source tables; and

retrieving the data from the specified source table rows using the retrieved references to locate the specified source table rows from among the plurality of source tables.

26. The method of claim 25, further including:

after receiving the retrieve request, receiving a second retrieve request to retrieve data from a second specified source table row identified by the received query request;

retrieving from the query result table the reference to the second specified source table row; and

retrieving the data from the second specified source table row using the retrieved reference to locate the second specified source table row from among the plurality of source tables.

27. The method of claim 26 further including the steps of:

receiving an instruction to store update data in a second specified source table row;

retrieving from the query result table the reference to the specified source table row; and

storing the update data into the second specified source table row using the retrieved reference to locate the second specified source table rows from among the plurality of source tables.

28. A computer-readable medium containing instructions for causing a computer system to propagate changes to data contained in database source table rows from the source table rows to a display of the data, the display generated by a front-end display program, by:

receiving a query request that identifies a subset of rows of the source table;

generating a query result table containing references to rows of the source table that are identified by the received query request;

receiving a retrieve request from the front-end display program to retrieve data from a specified source table row identified by the received query request;

retrieving from the query result table the reference to the specified source table row;

retrieving the specified source table row using the retrieved reference to locate the specified source table row;

determining whether the data of the specified source table row has been changed since the specified source table row was last accessed by the front-end display program; and

when it is determined that the data of the specified source row table has changed, notifying the front-end display program that the data in the specified source table row has been changed so that the front-end display program can update the display.

29. The computer-readable medium of claim 28 wherein the determining is performed in response to receiving an instruction to specify a source table row for retrieval.

30. The computer-readable medium of claim 28 wherein determining whether the specified source table row has been changed includes:

fetching a current row version indicator indicating a current state of the specified source table row; and

comparing the fetched current row version indicator to a row version indicator indicating the state of the specified source table row at the time it was last accessed.

31. The computer-readable medium of claim 30 wherein the fetching of a current row version indicator includes fetching a current row timestamp indicating a time at which the specified source table row was last changed, and wherein the comparing of the fetched current row version indicator to a last-fetched row version indicator includes comparing the fetched current row timestamp to a last-fetched row timestamp indicating the time at which the specified source table row was last accessed.

32. The computer-readable medium of claim 30 wherein the fetching of a current row version indicator includes fetching a current row checksum that encodes the data currently stored in the specified source table row, and wherein the comparing of the fetched current row version indicator to a last-fetched row version indicator includes comparing the fetched current row checksum to a last-fetched row checksum that encodes the data retrieved from the specified source table row in the retrieving step.

33. The computer-readable medium of claim 30 wherein the fetching of a current row version indicator includes fetching a current row version number indicating the number of times the specified source table row has been modified since a counter initialization time, and wherein the comparing of the fetched current row version indicator to a last-fetched row version indicator includes comparing the fetched current row version number to a last-fetched row version number indicating the number of times the specified source table row had been modified between the counter initialization time and the time at which the retrieving step was performed.

34. The computer-readable medium of claim 28 wherein upon receiving the notification, the front-end display program displays to a user a warning indicating that data associated with the specified source table row has changed.

35. The computer-readable medium of claim 28 wherein the changed data from the specified source table row is provided to the front-end display program at the same time that the front-end display program is notified that the data in the specified source table row has changed.

36. The computer-readable medium of claim 35 wherein the providing of the changed data from the specified source table row includes re-retrieving the specified source table row using a reference from the query result table.

37. The computer-readable medium of claim 36 wherein the query specifies an expression for deriving data to be returned to the requesting program from data stored in the specified source table row, further including using the expression specified by the query to derive the data to be returned to the requesting program from the data in the specified source table row retrieved by the re-retrieving.

38. The computer-readable medium of claim 36 wherein the previously executed query specified that a relationship exists between data stored in a source table row referred to by a first reference contained in the query result table and data stored in a second source table row referred to by a second reference contained the query result table and wherein the relationship between the data referred to by the first reference and the data referred to by the second reference no longer exists because of changes to the data stored in at least one of the referenced source table rows, the computer-readable medium further including:

locating a different source table row containing data with which data from the source table row referred to by the first reference currently bears the specified relationship;

replacing the second reference with a reference to the located source table row; and

returning to the program requesting retrieval the data contained in the located source table row.

39. The computer-readable medium of claim 38, further including retrieving the located row using the reference with which the second reference was replaced in the replacing.

40. The computer-readable medium of claim 38 wherein the query further query further specifies that a second relationship exists between data stored in the source table row referred to by the second reference contained in the query result table and data stored in a third source table row referred to by a third reference contained in the query result table and wherein the relationship between the data referred to by the second reference and the data referred to by the third reference no longer exists at the time of the retrieving step because of changes to the data stored in at least one of the referenced source table rows referred to by the second reference and the third reference, the computer-readable medium further including:

locating a second different source table row containing data with which data from the source table row referred to by the second reference currently bears the second specified relationship;

replacing the third reference in the query result table with a reference to the second located source table row; and

returning to the program requesting retrieval the data contained in the second located source table row.

41. A database engine of a computer system for supplying to a computer program records from a database, each record having data and a record identifier, comprising:

means for receiving from the computer program a query request with a selection criterion for selecting records that satisfy the selection criterion;

means for selecting the records of the database that satisfy the selection criterion;

means for storing the record identifiers of each selected record;

means for receiving from the computer program a plurality of retrieve requests to retrieve data from the records that satisfy the selection criterion; and

means for each received retrieve request,

selecting a stored record identifier;

retrieving the record from the database that is identified by the selected record identifier; and

sending to the computer program the retrieved record.