Architecture of database application with robust online recoverability

Abstract

An architecture for efficiently identifying the scope and timeframe of database errors, providing online history images, creating online point-in-time views of application tables, reconstructing equivalent SQL statements of a committed transaction or of a user session, providing a selective audit trail report on demand, and permitting selective online rollbacks in an application database. The rollback can be done on a single row, a single transaction, a group transactions, a user session, or all user tables. The system includes after images tables and before images tables and tracks all before images of user application tables when a user performs an INSERT, UPDATE, or DELETE operation with respect to a row in a table. Also, the system is provided to generate history images of application tables at a point-in-time, and to selectively rollback, or undo whole or a portion of application tables, to generate a selective audit trail report on demand, and to manage the before images.

1 . A computer implemented database application system for managing a transaction in a database, the system comprising: 
 a database manager, an image manager, a rollback manager, a redo and undo log, a system tablespace, and a user tablespace, the user tablespace comprising:    a rollback timestamp tracking table that stores a table owner, a table name, a retention time, and an export timestamp;    a transaction identifier (id) tracking table that stores a transaction id, a user id, a session id, a starting timestamp, and an ending timestamp;    a user session tracking table that stores a user id, an operating system user id, a session id, a process id, a terminal id, a login timestamp, and a logout timestamp; and    application data, the application data comprising an after image table, a before image table, and an index, an after image view, a time-based after image view, and a history image view,    wherein when a user performs a database operation on a row, the image manager inserts an original row, as the row existed prior to the database operation, into the before image table, updates parameters associated with the original row inserted into the before image table, and sets parameters associated with a modified row, as it existed after the database operation, in the after image table.    
   
   
       2 . The system according to  claim 1 , the image manager further comprising: 
 an inserting trigger that sets parameters for an inserted row in the after image table;    an updating trigger that inserts the original row into the before image table and sets parameters associated with the modified row in the after image table and the before image table; and    a deleting trigger that inserts the original row into the before image table and sets parameters associated with the original row in the before image table.    
   
   
       3 . The system according to  claim 1 , further comprising a transaction trigger that records each transaction id.  
   
   
       4 . The system according to  claim 1 , further comprising a login trigger that records a time when a user enters the database and a logout trigger that records a time when a user exits the database.  
   
   
       5 . The system according to  claim 1 , wherein the database operation comprises one of an insert, an update, and a delete.  
   
   
       6 . The system according to  claim 1 , the after image table further including at least one of a timestamp field, a user id field, and a transaction id field; and the before image table further including at least one of a changing timestamp field, a changing user id field, a changing transaction id field, and an operation code.  
   
   
       7 . The system according to  claim 1 , wherein the rollback manager is configured to perform at least one of rollbacking a committed transaction and reconstructing an equivalent SQL statement of a committed transaction.  
   
   
       8 . The system according to  claim 2 , wherein the inserting trigger, the updating trigger, and the deleting trigger are implemented in an SQL statement execution engine.  
   
   
       9 . A computer readable medium containing instructions that when executed operates as part of a database management system, the medium comprising: 
 an after image storing code segment that stores an after image of a row, the after image comprising an image of the row after a database operation is performed on the row;    a before image storing code segment that stores a before image of a row, the before image comprising an image of the row prior to a database operation being performed on the row;    an after image table viewing code segment that displays the after image of the row; and    a before image table viewing code segment that displays the before image of the row.    
   
   
       10 . The computer readable medium according to  claim 9 , further comprising: 
 an inserting trigger code segment that sets parameters for the after image of an inserted row in the after image table;    an updating trigger code segment that inserts the before image of the row into the before image table and sets parameters associated with the after image of the row in the after image table and sets parameters associated with the before image of the row in the before image table; and    a deleting trigger code segment that inserts the before image of the row into the before image table sets parameters associated with the before image of the row in the before image table.    
   
   
       11 . The computer readable medium according to  claim 9 , wherein the after image table comprises at least one of a timestamp field, a user identifier (id) field, and a transaction id field.  
   
   
       12 . The computer readable medium according to  claim 9 , wherein the before image table further comprises at least one of a changing timestamp field, a changing user identifier (id) field, a changing transaction id field, and an operation code.  
   
   
       13 . The computer readable medium according to  claim 9 , wherein the operation code comprises one of insert, update, and delete.  
   
   
       14 . The computer readable medium according to  claim 9 , further comprising a rollback code segment that performs a rollback of a committed transaction.  
   
   
       15 . The computer readable medium according to  claim 9 , further comprising a reconstructing code segment that reconstructs an equivalent SQL statement of a committed transaction.  
   
   
       16 . A method of creating an online point-in-time view of an application table of an active database having a before image table and an after image table, the method comprising: 
 retrieving a retention time of the application table;    determining that a point-in-time is greater than or equal to the retention time; and    creating the online point-in-time view of the application table, the online point-in-time view comprising a union of a set of rows from the after image table as the set of rows existed in the after image table at the point-in-time, and a set of rows from the before image table as the set of rows existed in the after image table at the point-in-time.    
   
   
       17 . The method according to  claim 16 , further comprising: 
 retrieving a transaction identifier (id) set associated with a transaction id that begins before or at the point-in-time and ends after the point-in-time;    retrieving the set of rows from the after image table as the set of rows existed in the after image table at the point-in-time, having a timestamp that is less than or equal to the point-in-time, and having a transaction id that is not a proper subset of the transaction id set; and    retrieving the set of rows from the before image table as the set of rows existed in the after image table at the point-in-time, having a timestamp less than or equal to the point-in-time, and having a transaction id that is not a proper subset of the transaction id set, and having a changing transaction id that is a proper subset of the transaction id set or having a changing timestamp that is greater than the point-in-time.    
   
   
       18 . The method according to  claim 16 , further comprising: 
 retrieving the set of rows from the after image table as the set of rows existed in the after image table at the point-in-time, having a timestamp less than or equal to the point-in-time, and    retrieving the set of rows from the before image table as the set of rows existed in the after image table at the point-in-time, having a timestamp less than or equal to the point-in-time, and having a changing timestamp greater than the point-in-time.    
   
   
       19 . The method according to  claim 16 , wherein the after image table comprises at least one of a timestamp field, a user identifier (id) field, and a transaction id field.  
   
   
       20 . The method according to  claim 16 , wherein the before image table comprises at least one of a changing timestamp field, a changing user identifier (id) field, a changing transaction id field, and an operation code.