Method and apparatus for changing temporal database information

• US 6,442,543 B1
• Filed: 12/18/2000
• Issued: 08/27/2002
• Est. Priority Date: 07/25/1997
• 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): Corrielus, Jean M.
• Assistant Examiner(s): ALAM, SHAHID AL
• Application Number:
  US09/740,495
• Time in Patent Office: 617 Days
• Field of Search: 707/2-5, 707/102, 707/3, 707/4, 707/104.1, 707/203, 707/9, 707/1, 707/201, 707/10, 711/110
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• 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 in a computing system for processing a temporal row modification command against a set of rows each having an effective time interval, the method comprising:

receiving a row modification command, the command specifying a modification time interval and a modification instruction;

for each original row whose effective time interval intersects the modification time interval but is not a subset of the modification time interval, replacing the original row with a plurality of replacement rows, the plurality of replacement rows including a distinguished replacement row, the distinguished replacement row having an effective time interval that is coextensive with the intersection between the effective time interval of the original row and the modification time interval, the effective time intervals of the replacement rows collectively comprising the effective time interval of the original row, the replacement rows each having contents copied from the original row; and

after the replacing, for each row whose effective time interval is a subset of the modification time interval, applying the modification instruction to the row, wherein, for distinguished replacement rows, the modification instruction is applied to the contents of the replacement rows copied from their original rows.

2. The method of claim 1 wherein the plurality of replacement rows with which original rows are replaced include a plurality of distinguished replacement rows, each of the distinguished replacement rows having an effective time interval that is coextensive with the intersection between the effective time interval of the original row and the modification time interval.

3. The method of claim 1 wherein the row modification command specifies a plurality of modification time intervals, and wherein the plurality of replacement rows with which original rows are replaced include a plurality of distinguished replacement rows, each of the distinguished replacement rows having an effective time interval that is coextensive with the intersection between the effective time interval of the original row and the modification time interval, the effective time intervals of at least two of the distinguished replacement rows being wholly within different modification time intervals.

4. The method of claim 1 wherein the row modification command specifies a plurality of modification time intervals, and wherein the modification instruction specified by the row modification command applies differently with respect to the different modification time intervals.

5. A computer-readable medium whose contents cause a computing system to process a row modification command against a set of rows each having an effective time period by:

receiving a row modification command, the command specifying a modification time period and a modification instruction;

for each original row whose effective time period overlaps the modification time period but is not wholly within the modification time period, replacing the original row with a plurality of replacement rows, the plurality of replacement rows including a distinguished replacement row, the distinguished replacement row having as an effective time period the period for which the effective time period of the original row overlaps the modification time period, the effective time periods of the replacement rows collectively cover the effective time period of the original row, the replacement rows each having contents copied from the original row; and

after the replacing, for each row whose effective time period is wholly within the modification time period, applying the modification instruction to the row, wherein, for distinguished replacement rows, the modification instruction is applied to the contents of the replacement rows copied from their original rows.

6. A computing system for processing a temporal row modification command against a set of rows each having an effective time range, comprising:

a command receiving subsystem that receives a row modification command, the command specifying a modification time range and a modification instruction;

a row subdivision subsystem that, for each original row whose effective time range intersects the modification time range but is not a subset of the modification time range, replaces the original row with a plurality of replacement rows, the plurality of replacement rows including a distinguished replacement row whose effective time range is coextensive with the intersection between the effective time range of the original row and the modification time range, the effective time ranges of the replacement rows collectively comprising the effective time range of the original row, the replacement rows each having contents copied from the original row; and

a modification instruction application subsystem that, subsequent to the operation of the row subdivision subsystem, for each row whose effective time range is a subset of the modification time range, applies the modification instruction to the row, wherein, for distinguished replacement rows, the modification instruction is applied to the contents of the replacement rows copied from their original rows.

7. One or more computer memories collectively containing a temporal row modification command, the temporal row modification command comprising:

a first indication indicating a set of rows to modify;

a second indication indicating a time period for which to modify rows in the indicated set; and

a third indication indicating a manner in which to modify the rows of the indicated set,

such that the indications comprising the temporal row modification command may be used to modify the indicated set of rows for the indicated time period in the indicated manner by (1) for any rows in tile indicated set having effective time periods not wholly within or without the indicated time period, splitting the row into two or more resulting rows, the effective time period of each resulting row wholly within or without the indicated time period; and

(2) modifying only those rows in the indicated set whose effective time periods are wholly within the indicated time period.

8. One or more generated data signals collectively conveying a row modification command, the row modification command for execution by a computer system, the row modification command comprising:

an indication of a set of rows to modify;

an indication of a time period for which to modify rows in the indicated set; and

an indication of a manner in which to modify the rows of the indicated set, such that the indications comprising the row modification command may be used by a receiver of the data signals to modify the indicated set of rows for the indicated time period in the indicated manner by (1) for any rows in the indicated set having effective time periods not wholly within or without the indicated time period, splitting the row into two or more resulting rows, the effective time period of each resulting row wholly within or without the indicated time period; and

(2) modifying only those rows in the indicated set whose effective time periods are wholly within the indicated time period.