Database system that provides for history-enabled tables
First Claim
1. A history-enabled table in a relational database system, the history-enabled table comprising:
- a set of other columns;
a start time column;
a period column;
a set of current rows, a current row containing current values of the fields that belong to the set of other columns and a start time field belonging to the start time column, the value of the start time field indicating the time at which the most recent change in the current values occurred; and
a set of history rows, the history rows having copied fields belonging to the set of other columns or a subset thereof and a row period field belonging to the period column, the values of the copied fields being copied from a current row prior to deletion or updating of the current row and the row period field'"'"'s value being set to indicate the period between the start time value of the copied current row and the time of the update or deletion of the copied current row.
1 Assignment
0 Petitions
Accused Products
Abstract
Techniques for history enabling a table in a database system so that past versions of rows of the history-enabled table are available for temporal querying. The table is history enabled by adding a start time column to the table and creating a history table for the history-enabled table. The start time field'"'"'s value in a row of the history-enabled table indicates when the contents of the row last changed. The rows of the history table are copies of rows of the history-enabled table that have been deleted or updated. The rows include end time fields whose values indicate when the row was updated or deleted. A history table'"'"'s row thus indicates a period in which the history table'"'"'s row was in the history-enabled table. Temporal queries are performed on a view which is the union of the history-enabled table and the history table.
65 Citations
44 Claims
-
1. A history-enabled table in a relational database system, the history-enabled table comprising:
-
a set of other columns;
a start time column;
a period column;
a set of current rows, a current row containing current values of the fields that belong to the set of other columns and a start time field belonging to the start time column, the value of the start time field indicating the time at which the most recent change in the current values occurred; and
a set of history rows, the history rows having copied fields belonging to the set of other columns or a subset thereof and a row period field belonging to the period column, the values of the copied fields being copied from a current row prior to deletion or updating of the current row and the row period field'"'"'s value being set to indicate the period between the start time value of the copied current row and the time of the update or deletion of the copied current row. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13)
-
-
14. Apparatus for creating and maintaining a history-enabled table in a relational database management system, the apparatus comprising:
-
a table in the relational database system that is to be history-enabled;
a history-enabled table creator that makes a current row table by adding a start time column to a set of current row table other columns of the table to be history-enabled and creates a row history table that has a set of row history columns corresponding to the current row table other columns or a subset thereof and a period column; and
a history-enabled table maintainer that responds to a change in a field in a row of the current row table, the field having a current row table other column that corresponds to a row history table other column, by making a row history table row in which the fields belonging to the row history table'"'"'s other columns are copied prior to the change from the current row table row'"'"'s fields that belong to the corresponding other columns in the current row table and whose period column contains a row period value that indicates the period between the time indicated by the copied current row table row'"'"'s start time value and the time at which the change occurred and setting the start time value in the changed current table row to the time at which the change occurred. - View Dependent Claims (15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27)
-
-
28. A method of creating and maintaining a history-enabled table in a relational database system, the method comprising the steps of:
-
making a current row table by adding a start time column to a set of current row table other columns of a table that is to be history enabled;
making a row history table that has a set of row history table other columns corresponding to the current row table other columns or a subset thereof and a period column; and
responding to a change in a field in a current row table row, the field having a current row table other column that corresponds to a row history table other column, by making a row history table row in which the fields belonging to the row history table'"'"'s other columns are copied prior to the change from the current row table row'"'"'s fields that belong to the corresponding other columns in the current row table, the row history table row'"'"'s period column containing a row period value that indicates the period between the time indicated by the copied current table row'"'"'s start time value and the time at which the change occurred; and
setting the start time value in the changed current table row to the time at which the change occurred. - View Dependent Claims (29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41)
-
-
42. A method employed in a relational database management system of setting a time field in a row in the database management system where the value of the time field indicates the time of an event that is part of a transaction that is performed in the database system, the method comprising the steps of:
-
setting a transaction identifier field that is related to the time field in the relational database management system to a transaction identifier for the transaction;
setting the time field in the row to a time that is during the transaction but prior to the transaction being committed;
after the transaction has been committed, using the transaction identifier in the related field to determine the commit time of the transaction; and
setting the time field in the row to the commit time. - View Dependent Claims (43, 44)
-
Specification