Method and apparatus for changing temporal database

• US 6,185,556 B1
• Filed: 05/04/1999
• Issued: 02/06/2001
• Est. Priority Date: 05/04/1999
• Status: Expired due to Term

• Current Assignee: Amazon Technologies, Inc. (Amazon.com, Inc.)
• Original Assignee: Amazon.com, Inc.
• Inventors: Bair, John, Snodgrass, Richard T
• Primary Examiner(s): Choules, Jack M.
• Assistant Examiner(s): ALAM, SHAHID AL
• Application Number:
  US09/304,697
• Time in Patent Office: 644 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/9, 707/102, 707/203, 707/5, 711/110
• US Class Current: 707/687
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 16/2477 Temporal data queries
• 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/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99939 Privileged access
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A method and apparatus accepts a query to update or delete rows in a temporal table of a database. The query may describe which rows are to be updated, a calculation for the value to be used to update each row of the table, and a period of validity, during which the changes are to take place. The method and apparatus creates a sequence of queries and SQL program elements that together can make up an SQL program that will execute the query received. The SQL program can split any row of the table to be updated into multiple rows so that each of the multiple rows of the table to be changed have an effective period during which no changes occur in the table to be changed or any other tables referenced in the query received and no effective periods span the beginning or the end of the period of validity. The specified change may then be made to the resulting rows, and rows that are adjacent in time with identical values may be collapsed into a single row. The result is inserted into the table to be changed and the corresponding original rows in the table to be changed are deleted. The method and apparatus operates for queries received to update or delete a table.

Claims

1. A method of updating a database table comprising a first plurality of rows, each row having a start identifier, an end identifier and a first value, the method comprising:

receiving a command to update a database, the command comprising a criteria and a period of validity, the period of validity having a beginning and an end;

generating at least two rows for each of a second plurality of the first plurality of rows of the table meeting the criteria and having at least one selected from a start date not after the end of the period of validity and a stop date not prior to the beginning of the period of validity; and

changing the value of at least one row corresponding to at least one of the rows in the second plurality, responsive to the command received.

2. The method of claim 1, wherein the criteria comprises a first criteria and a second criteria and the generating step comprises:

identifying a third plurality of rows meeting the second criteria and not the first criteria and having at least one selected from;

a start identifier after the beginning of the period of validity and before the end of the period of validity; and

an end identifier after the beginning of the period of validity and before the end of the period of validity; and

providing for each row in the third plurality a first row and a second row;

the first row having the first value and having a start identifier and an end identifier, said start identifier and end identifier of the first row responsive to a period when the first criteria is false; and

the second row having a second value responsive to the command and having a start identifier and an end identifier responsive to a period when the first criteria and the second criteria are true.

3. The method of claim 1, wherein the generating step comprises:

identifying a third plurality of rows meeting the criteria and having a start identifier prior to the beginning of the period of validity and an end identifier after the beginning of the period of validity; and

providing for each row in the third plurality a first row and a second row, the first row having a start identifier comprising the start identifier of the respective row in the third plurality and an end identifier corresponding to the beginning of the period of validity and the second row having a start identifier corresponding to the beginning of the period of validity.

4. The method of claim 3 wherein the changing step comprises assigning a second value to the second row and the first value to the first row.

5. The method of claim 3 wherein the changing step comprises assigning a second value to the second row and the first value to the first row.

6. The method of claim 3 wherein the changing step comprises assigning a second value to the second row, a third value to the third row and the first value to the first row.

7. The method of claim 1, wherein the generating step comprises:

identifying a third plurality of rows meeting the criteria and having a end identifier after the end of the period of validity and an start identifier before the end of the period of validity; and

providing for each row in the third plurality a first row and a second row, the first row having an end identifier comprising the end identifier of the respective row in the third plurality and a start identifier corresponding to the end of the period of validity and the second row having an end identifier corresponding to the end of the period of validity.

8. The method of claim 1, wherein the generating step comprises:

identifying a third plurality of rows meeting the criteria and having a end identifier after the end of the period of validity and an start identifier before the beginning of the period of validity; and

providing for each row in the third plurality a first row, a second row and a third row, the first row having an end identifier comprising the end identifier of the respective row in the third plurality and a start identifier corresponding to the end of the period of validity, the second row having an end identifier corresponding to the end of the period of validity and a start identifier corresponding to the beginning of the period of validity, the third row having a start identifier comprising the start identifier of the respective row in the third plurality and an end identifier corresponding to the beginning of the period of validity.

9. The method of claim 1 comprising the additional step of storing at least some of the first rows and the second rows into the table.

10. The method of claim 1 comprising the additional step of identifying at least one constant set of startdates and stopdates from at least one selected from the table and a table described in the command, and wherein the changing step is responsive to at least one of the at least one constant set.

11. The method of claim 10 wherein the constant sets are responsive to the period of validity.

12. The method of claim 1 wherein the changing step comprises setting up a database cursor.

13. The method of claim 1 comprising the additional step of combining at least two rows having a same value.

14. The method of claim 1 wherein the changing step comprises inserting at least one row into the table.

15. A computer program product comprising a computer useable medium having computer readable program code embodied therein for updating a database table comprising a first plurality of rows, each row having a start identifier, an end identifier and a first value, the computer program product comprising:

computer readable program code devices configured to cause a computer to receive a command to update a database, the command comprising a criteria and a period of validity, the period of validity having a beginning and an end;

computer readable program code devices configured to cause a computer to generate at least two rows for each of a second plurality of the first plurality of rows of the table meeting the criteria and having at least one selected from a start date not after the end of the period of validity and a stop date not prior to the beginning of the period of validity; and

computer readable program code devices configured to cause a computer to change the value of at least one row corresponding to at least one of the rows in the second plurality, responsive to the command received.

16. The computer program product of claim 15, wherein the criteria comprises a first criteria and a second criteria and the computer readable program code devices configured to cause a computer to generate comprise:

computer readable program code devices configured to cause a computer to identify a third plurality of rows meeting the second criteria and not the first criteria and having at least one selected from;

a start identifier after the beginning of the period of validity and before the end of the period of validity; and

an end identifier after the beginning of the period of validity and before the end of the period of validity; and

computer readable program code devices configured to cause a computer to provide for each row in the third plurality a first row and a second row;

the first row having the first value and having a start identifier and an end identifier, said start identifier and end identifier of the first row responsive to a period when the first criteria is false; and

the second row having a second value responsive to the command and having a start identifier and an end identifier responsive to a period when the first criteria and the second criteria are true.

17. The computer program product of claim 15, wherein the computer readable program code devices configured to cause a computer to generate comprise:

computer readable program code devices configured to cause a computer to identify a third plurality of rows meeting the criteria and having a start identifier prior to the beginning of the period of validity and an end identifier after the beginning of the period of validity; and

computer readable program code devices configured to cause a computer to provide for each row in the third plurality a first row and a second row, the first row having a start identifier comprising the start identifier of the respective row in the third plurality and an end identifier corresponding to the beginning of the period of validity and the second row having a start identifier corresponding to the beginning of the period of validity.

18. The computer program product of claim 17 wherein the computer readable program code devices configured to cause a computer to change comprise computer readable program code devices configured to cause a computer to assign a second value to the second row and the first value to the first row.

19. The computer program product of claim 17 wherein the computer readable program code devices configured to cause a computer to change comprise assigning a second value to the second row and the first value to the first row.

20. The computer program product of claim 17 wherein the computer readable program code devices configured to cause a computer to change comprise computer readable program code devices configured to cause a computer to assign a second value to the second row, a third value to the third row and the first value to the first row.

21. The computer program product of claim 15, wherein the computer readable program code devices configured to cause a computer to generate comprise:

computer readable program code devices configured to cause a computer to identify a third plurality of rows meeting the criteria and having a end identifier after the end of the period of validity and an start identifier before the end of the period of validity; and

computer readable program code devices configured to cause a computer to provide for each row in the third plurality a first row and a second row, the first row having an end identifier comprising the end identifier of the respective row in the third plurality and a start identifier corresponding to the end of the period of validity and the second row having an end identifier corresponding to the end of the period of validity.

22. The computer program product of claim 15, wherein the computer readable program code devices configured to cause a computer to generate comprise:

computer readable program code devices configured to cause a computer to identify a third plurality of rows meeting the criteria and having a end identifier after the end of the period of validity and an start identifier before the beginning of the period of validity; and

computer readable program code devices configured to cause a computer to provide for each row in the third plurality a first row, a second row and a third row, the first row having an end identifier comprising the end identifier of the respective row in the third plurality and a start identifier corresponding to the end of the period of validity, the second row having an end identifier corresponding to the end of the period of validity and a start identifier corresponding to the beginning of the period of validity, the third row having a start identifier comprising the start identifier of the respective row in the third plurality and an end identifier corresponding to the beginning of the period of validity.

23. The computer program product of claim 15 additionally comprising computer readable program code devices configured to cause a computer to store at least some of the first rows and the second rows into the table.

24. The computer program product of claim 15 additionally comprising computer readable program code devices configured to cause a computer to identify at least one constant set of startdates and stopdates from at least one selected from the table and a table described in the command, and wherein the computer readable program code devices configured to cause a computer to change are responsive to at least one of the at least one constant set.

25. The computer program product of claim 24 wherein the constant sets are responsive to the period of validity.

26. The computer program product of claim 15 wherein the computer readable program code devices configured to cause a computer to change comprise computer readable program code devices configured to cause a computer to set up a database cursor.

27. The computer program product of claim 15 additionally comprising computer readable program code devices configured to cause a computer to combine at least two rows having a same value.

28. The computer program product of claim 15 wherein the computer readable program code devices configured to cause a computer to change comprise computer readable program code devices configured to cause a computer to insert at least one row into the table.

29. A system for updating a database table comprising a plurality of rows, each row having a start identifier, and end identifier and a first value, the system comprising:

a query router having an input operatively coupled for receiving a command to update a database, the command comprising a criteria and a period of validity, the period of validity having a beginning and an end, the query router for providing at an output an indicator of at least a portion of the query received at the input;

at least one values retainer having an input coupled to the query router output for generating at least one row comprising the first value for each of a second plurality of the first plurality of rows of the table meeting the criteria and having at least one selected from a start date not after the end of the period of validity and a stop date not prior to the beginning of the period of validity; and

a values setter for generating at least one row comprising a second value responsive to the command for each of a second plurality of the first plurality of rows of the table meeting the criteria and having at least one selected from a start date not after the end of the period of validity and a stop date not prior to the beginning of the period of validity.