Private database logging with minimal storage requirements

• US 8,745,000 B2
• Filed: 10/29/2010
• Issued: 06/03/2014
• Est. Priority Date: 10/29/2010
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bryson, Donald L.
• Primary Examiner(s): LE, HUNG D
• Application Number:
  US12/916,360
• Publication Number:
  US 20120109893A1
• Time in Patent Office: 1,313 Days
• Field of Search: None
• US Class Current: 707/634
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27

Abstract

An apparatus, system, and method are disclosed for logging operations for a database. An operation event module detects an operation on a record in a database table. A translation module creates a changed field bit field, one or more operation time bit fields, and a user bit field. The translation module creates the changed field bit field by encoding indicators of one or more fields of the record that the operation is configured to change, creates the one or more operation time bit fields by encoding time information for the operation, and creates the user bit field by encoding information of a user associated with the operation. A consolidation module packs at least the changed field bit field, the one or more operation time bit fields, and the user bit field into a single bit array. A log module stores the bit array in a change log.

Claims

1. An apparatus to log operations for a database, the apparatus comprising:

an operation event module that detects an operation on a record in a database table;

a translation module that creates a changed field bit field, one or more operation time bit fields, and a user bit field, the translation module creating the changed field bit field by encoding indicators of one or more fields of the record that the operation is configured to change, creating the one or more operation time bit fields by encoding time information for the operation, and creating the user bit field by encoding information of a user associated with the operation;

a consolidation module that packs at least the changed field bit field, the one or more operation time bit fields, and the user bit field into a single bit array; and

a log module that stores the bit array in a change log such that the bit array is associated with the record.

2. The apparatus of claim 1, further comprising a purge module that removes a stored bit array from the change log in response to the one or more operation time bit fields of the stored bit array indicating that the stored bit array is older than a predefined retention period.

3. The apparatus of claim 2, wherein the purge module further removes a stored record from the database table in response to time indicators of each stored bit array associated with the stored record indicating that each stored bit array is older than the predefined retention period.

4. The apparatus of claim 2, wherein the purge module further re-encodes one or more operation time bit fields for one or more additional stored bit arrays in response to removing the stored bit array, the purge module re-encoding the one or more operation time bit fields from offsets based on an original start date to offsets based on a new start date associated with the predefined retention period.

5. The apparatus of claim 1, further comprising a control data module that determines control data for the translation module, the control data comprising a selection of tracked fields of the database table for the translation module to use to encode the indicators of one or more fields of the record, a log start date for the translation module to use to encode the time information, and a number of users with access to the database table for the translation module to use to encode the information of the user.

6. The apparatus of claim 1, further comprising a read operation module that detects a second operation on the record in the database table, the second operation not configured to change a field of the record, wherein the translation module creates one or more second operation time bit fields and a second user bit field, the consolidation module packs at least the one or more second operation time bit fields and the second user bit field into a second bit array and the log module stores the second bit array in the change log such that the second bit array is associated with the record.

7. The apparatus of claim 6, wherein the translation module further creates a second changed field bit field, the second changed field bit field indicating that no fields of the record were changed by the second operation.

8. The apparatus of claim 1, wherein the translation module creates an operation type bit field by encoding an operation type for the operation, the consolidation module packing the operation type bit field into the bit array.

9. The apparatus of claim 1, wherein the change log comprises a change log database table.

10. The apparatus of claim 9, further comprising a data privacy module that prevents one or more users with access to the change log database table from accessing the database table of the record such that the one or more users have access to the changed field bit field, the one or more operation time bit fields, and the user bit field of the bit array and do not have access to data of the record.

11. The apparatus of claim 9, wherein the log module stores a record indicator and a log indicator with the bit array in the change log database table, the record indicator, the log indicator, and the bit array each comprising separate fields in the change log database table, the record indicator identifying the record that the operation is on, and the log indicator uniquely identifying the bit array and the record indicator in the change log database table.

12. The apparatus of claim 1, wherein the change log comprises a field in the database table of the record and the log module stores the bit array as a field of the record.

13. The apparatus of claim 1, wherein the change field bit field comprises a bitwise representation of one or more preselected tracked fields for the database table, each bit of the bitwise representation indicating whether the detected operation is configured to change an associated preselected tracked field of the record, the field indicator excluding bits for untracked fields of the database table.

14. The apparatus of claim 1, wherein a date operation time bit field from the one or more operation time bit fields comprise a binary representation of an offset number of days between a log start date and a date of the detected operation, the date operation time bit field having a number of bits selected based on a predefined retention period.

15. The apparatus of claim 14, wherein a time operation time bit field from the one or more operation time bit fields comprises a binary representation of a time of day for the detected operation.

16. The apparatus of claim 1, wherein the user bit field comprises a binary representation of a user identifier of the user associated with the operation, the user bit field having a number of bits selected based on a predefined maximum number of users.

17. The apparatus of claim 1, further comprising a database server computer hosting a database system, the database system storing the database table.

18. A method for logging operations for a database, the method comprising:

detecting an operation on a record in a database table;

creating a changed field bit field by encoding indicators of one or more fields of the record that the operation is configured to change;

creating one or more operation time bit fields by encoding time information for the operation;

creating a user bit field by encoding information of a user associated with the operation;

packing at least the changed field bit field, the one or more operation time bit fields, and the user bit field into a single bit array; and

storing the bit array in a change log such that the bit array is associated with the record.

19. The method of claim 18, further comprising,

removing a stored bit array from the change log in response to the one or more operation time bit fields of the stored bit array indicating that the stored bit array is older than a predefined retention period;

and

re-encoding one or more operation time bit fields for one or more additional stored bit arrays in response to removing the stored bit array, the one or more operation time bit fields re-encoded from offsets based on an original start date to offsets based on a new start date associated with the predefined retention period.

20. The method of claim 18, further comprising preventing one or more users with access to the change log from accessing the database table of the record such that the one or more users have access to the changed field bit field, the one or more operation time bit fields, and the user bit field of the bit array and do not have access to data of the record.

21. The method of claim 18, further comprising,

detecting a second operation on the record in the database table, the second operation not configured to change a field of the record;

creating one or more second operation time bit fields;

creating a second user bit field;

packing at least the one or more second operation time bit fields and the second user bit field into a second bit array; and

storing the second bit array in the change log such that the second bit array is associated with the record.

22. A computer program product comprising a non-transitory computer readable medium having computer readable program code executing to perform operations for logging operations for a database, the operations of the computer program product comprising:

detecting an operation on a record in a database table;

creating a changed field bit field by encoding indicators of one or more fields of the record that the operation is configured to change;

creating one or more operation time bit fields by encoding time information for the operation;

creating a user bit field by encoding information of a user associated with the operation;

packing at least the changed field bit field, the one or more operation time bit fields, and the user bit field into a single bit array; and

storing the bit array in a change log such that the bit array is associated with the record.

23. The computer program product of claim 22, further comprising,

removing a stored bit array from the change log in response to the one or more operation time bit fields of the stored bit array indicating that the stored bit array is older than a predefined retention period;

and

re-encoding one or more operation time bit fields for one or more additional stored bit arrays in response to removing the stored bit array, the one or more operation time bit fields re-encoded from offsets based on an original start date to offsets based on a new start date associated with the predefined retention period.

24. The computer program product of claim 22, further comprising preventing one or more users with access to the change log from accessing the database table of the record such that the one or more users have access to the changed field bit field, the one or more operation time bit fields, and the user bit field of the bit array and do not have access to data of the record.

25. A method for deploying computing infrastructure, the method comprising:

integrating computer readable code into a computing system, wherein the computer readable code in combination with the computing system is capable of,

detecting an operation on a record in a database table;

creating a changed field bit field by encoding indicators of one or more fields of the record that the operation is configured to change;

creating one or more operation time bit fields by encoding time information for the operation;

creating a user bit field by encoding information of a user associated with the operation;

packing at least the changed field bit field, the one or more operation time bit fields, and the user bit field into a single bit array; and

storing the bit array in a change log such that the bit array is associated with the record.