Tracking modifications to values of various fields in a database server

Storing historical/previous values of a field of a relational database in the same table. The previous values can be conveniently used to revert back to any of such previously stored values. Thus, for each update/modify operation, the present value is stored as a previous value. In one embodiment, the previous values are stored in a VARRAY associated with each field. The indexed entries of the VARRAY store the present as well as previous values of the field.

RELATED APPLICATIONS

The present application is related to and claims priority from the co-pending India Patent Application entitled, “Tracking Modifications to Values of Various Fields in a Database Server”, Serial Number: 1570/CHE/2005, Filed: 28 Oct. 2005, naming the same inventors as in the subject patent application.

BACKGROUND OF THE INVENTION

1. Field of the Invention

The present invention relates to database technologies, and more specifically to a method and apparatus for tracking modifications to values of various fields in a database server.

2. Related Art

A database server generally refers to a system which permits organization of related data and management of the organized data using structured queries. For example, in case of relational databases, SQL queries facilitate definition of schemas for tables contained in each database instance, and then to add, delete or change the data in various rows of the tables. The description is continued with respect to relational databases for illustration.

In general, the data of interest may be viewed as containing various values which are stored in fields organized in a database instance. The intersection of a column and row specifies the corresponding field in case of relational database. As may be appreciated, the values of fields can change, typically in response to queries which set the field to a new value.

There is often a need to track the modification (change) of values of several fields of interest. For example, the tracked values serve as an audit record for the corresponding field.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

Overview

According to an aspect of the present invention, the sequence of values a field of interest are set to, are stored/tracked in the same table associated with the field of interest. Due to such tracking, the prior values for any field may be quickly available for any later use (e.g., to revert back to a prior value).

According to one more aspect of the present invention, the user merely needs to specify the fields of interest and the database server automatically (i.e., without requiring further instructions from users) tracks the prior values.

2. Example Environment

FIG. 1is a block diagram illustrating an example environment in which various aspects of the present invention can be implemented. The environment is shown containing database server130, network120, and client systems100A through100N. Each system/device is described below in further detail.

Network120provides the connectivity to facilitate forwarding of queries (e.g., in the form of SQL) from client systems to database server130on path123, and the responses from database server130back to client systems on the corresponding paths. Network120may be implemented using protocols such as Internet Protocol.

Client systems110-A through110-N send queries to and receive the corresponding responses from network120, on paths112A through112N respectively. Client systems110-A through110-N may have several applications executing (such as automated teller applications, ticket reservation and enquiry applications, etc), which cause SQL queries to be sent to database server130on network120.

Database server130processes the queries received from client systems110-A through110-N, and sends the corresponding responses on path123. In general, the queries first specify database schemas to be defined for each table contained in a database instance, and then various operations for the management of data in the framework of the tables.

As noted above in the background section, there may be several situations in which the historical values of fields of interest need to be tracked. Various aspects of the present invention track such historical values, as described below in further detail.

3. Tracking Historical Values of Fields

FIG. 2is a flowchart illustrating the manner in which a database server tracks the historical values of fields of interest according to various aspects of the present invention. The flowchart is described with respect toFIG. 1merely for illustration. However, various features can be implemented in other environments without departing from several aspects of the present invention, as will be apparent to one skilled in the relevant arts by reading the disclosure provided herein. The flow chart begins in step201, in which control immediately passes to step220.

In step220, database server130receives an update request indicating a new value for a field of a table. The update request can be in the form of an SQL query or according to any convention.

In step230, database server130stores the present (i.e., at the time of reception of the update request) value in the same table associated with the field. In general, the (management software of) database server needs to be designed to permit storing of multiple values associated with the field. Various approaches will be apparent to one skilled in the relevant arts based on the disclosure provided herein. Such approaches are contemplated to be covered by various aspects of the present invention. An example approach is described in sections below in further detail.

In step250, database server130sets the field to equal the new value (as required to process the update request) received in step220. The flowchart then ends in step299. Due to the storing of the present value in step230, historical values for the field are maintained.

The description is continued with respect to the manner in which database server130may store the historical values for a field in one embodiment.

4. Storing Historical Values

According to one approach, pre-existing database servers supporting SQL queries and VARRAY structures (for columns of interest) are modified to support the storing of historical values for fields of interest. In such an approach, client systems110A-110N can send queries as if no historical data is maintained, and database server130automatically translates the queries into a form consistent with the storage in the VARRAY structures.

For simplicity, the implementation concepts are described in a scenario in which historical data related to all fields of a table are maintained, and also a change of some columns is reflected as a change to the same corresponding values in other columns. However, conventions can be employed to specify only the fields of interest and VARRAYs can be specified only for such specified fields.

Broadly, a VARRAY structure is created for each field for which historical information needs to be maintained. The depth of the structure can be set to the number of historical data elements sought to be maintained for the corresponding field. For further description of VARRAY structures, the reader is referred to a document entitled, “Oracle9i: JDBC Developer's Guide and Reference: Release 2 (9.2)”, Part No.: A96654—01, dated March 2002.

Thus,FIG. 3Arepresents a view of the data stored in database server130, ignoring the storing of the historical data. Thus, view300is shown containing columns identifier310, price320and name330, storing data in three rows311,312and313.

FIG. 3Brepresents the actual data stored in tables by database server130permitting storing of historical values. For simplicity, it is assumed that only (up to) 3 prior values need to be stored. Thus, table350is shown containing three rows391-393organized using columns identifier360, price370, name380and count390. Each column is described below in further detail. Columns360,370, and380are shown containing respective sub-columns361-364,371-374, and381-384, are described in sections below.

Column390contains a value representing the number of prior values presently available for the corresponding row under the assumption that a change of value to even one field would be reflected as a change (to the same prior value) in the remaining columns (as illustrated with respect to row392, where only value of price is sought to be changed but the prior value of the remaining columns are also copied.

Each column360,370and380is shown containing multiple sub-columns (only 4 assuming 3 prior values need to be stored indicated by361-364,371-374,381-384) representing the entries of the corresponding VARRAY, but respectively correspond to identifier, price and name, also shown inFIG. 3A. According to one convention, the left most entry (361,371,381) has an index equal to 1+number of prior values presently available (as indicated by the value of column390) and contains the earliest value for the column and the right most entry (364,374,384) has an index value of 1 and contains the present value.

Now assuming that a query is received to change the price of row393to 110.00 the changes to view300and table350are respectively shown in view400and table450ofFIGS. 4A and 4Brespectively.

It may thus be appreciated that the user queries may continue to be on the view ofFIGS. 3A and 4A, while database server130contains the internal logic to store the tables ofFIGS. 3B and 4Band to process the user queries appropriately. The manner in which database server130can be implemented is described below in further detail.

5. Database Server

FIG. 5is a block diagram illustrating the details of database server130in one embodiment. Database server130is shown containing interface block510, transaction block520, wrapper550, database manager560, and secondary storage580. Each block is described below in further detail.

For illustration of some features, it is assumed that database server130receives multiple queries forming a single ‘atomic’ transaction (i.e., requiring reversal of any completed queries if any of the later queries are not executed successfully to completion). In case such reversal is required, the historical values are conveniently used to revert back to the values prior to receiving the transaction, as described below in further detail.

Secondary storage580stores the data underlying the database instances containing the tables of FIG.3B/4B. Database manager560receives queries directed to the tables of FIGS.4B/3B in the form of SQL (structured query language), and manipulates the data in secondary storage580to support the database instances. As can be appreciated, the queries would be directed to VARRAYS (in the example scenario being described).

Interface block510provides the physical, electrical and protocol interfaces to receive queries (either as a part of transactions, or as individual queries) and passes the received queries to transaction block520. Similarly, interface block510sends back any responses generated by transaction block520.

Transaction block520forwards queries received from user systems110A-110N to wrapper block550in case the queries are directed to the view of FIGS.3A/4A. On the other hand, if the queries are directed to the tables of FIGS.3B/4B, transaction block520forwards the queries directly to database manager560. Irrespective, the responses to queries sent on both paths, may be received directly from database manager560.

Transaction block520may further issue queries itself to the extent needed for reversing prior changes to maintain atomicity of transactions. In such a situation, the count of column390/490may need to be decremented by 1.

Wrapper block550performs the necessary translations between the queries directed to the view of FIGS.3A/4A to generate the queries suitable for tables3B/4B. The nature of the queries to be generated will be clearer from the examples described below.

Broadly, queries are first received to define the schema, then to insert rows, and then to update the previously stored values. Accordingly, the description is continued with respect to corresponding three queries.

To define the schema corresponding to FIGS.3A/3B, user system110A may send the SQL query shown in lines601-607ofFIG. 6A. Given that VARRAYS are to be maintained for all the columns, wrapper block550may generate the SQL query of lines611-619ofFIG. 6B. Information indicating mapping of column labels Identifier310, Price320and Name330to column labels Identifier360, Price370and Name380respectively, is stored in secondary storage580according to any pre-specified convention.

To insert a row with values corresponding to row311, the query of lines701-703ofFIG. 7Aare received from one of user systems110A-110N. Wrapper program550may generate the SQL query of lines710-712ofFIG. 7B, and pass the query to database manager560. In response to the query, the values “item 1”, 1000 and “CPU” would be written in sub-columns364,374and384, which are modified again (resulting in the final values shown inFIG. 3B), as described below.

As noted above, sub-column364indicates a present value for identifier360, sub-column374the present value for column price370and sub-column384indicates the present value for name380.

During processing of an update request for any of the columns (ofFIG. 3B) identifier360, Price370and Name380, new values for each of the columns are respectively stored in sub-columns364,374and384. In addition, values of sub-columns (364,374,384) are copied into corresponding sub-columns (363,373,383), values of sub-columns (363,373,383) into corresponding sub-columns (362,372,382) and values of sub-columns (363,373,383) into (362,372,382) and (362,372,382) into sub-columns (361,371,381), thus enables tracking of modifications to any of the columns in a row in the same table.

Continuing with the illustration of tracking of changes, to update the value of price320(in row393) with a new value or any prior value, the query of lines801-802ofFIG. 8Aare received from user systems110A-110N. Wrapper program550may generate the SQL query of lines811-835ofFIG. 8B.

As may be appreciated, the query of lines824-826set the value of the first element (corresponding to sub-columns364,374and384) to contain the new value. Also, query lines within the loop indicated by lines828-832set the value for each sub-column (i) representing a prior value (361-363,371-373,381-383) to the value in another sub-column i+1 as indicated inFIG. 4B.

Accordingly, row493is shown containing new values for the sub-columns. Row493contains a new value 110.00 in column474and prior values (as indicated by sub-columns374,373) stored in sub-columns473and472respectively after the processing of the update request is complete. Also, row493indicates that sub-columns462-464and482-484also contain values such that a new value in stored in sub-columns464and484. Values in sub-columns363and364are shown respectively stored in sub-columns462and463and Values in sub-columns383and384are respectively stored in sub-columns482and483. Additionally, it may be appreciated tat column490is shown updated to a value ‘2’ to indicate the count of prior values for the row (as in query line834).

It should be appreciated that the wrapper approach illustrates an example technique to store historical values of (desired) fields. However, alternative approaches (such as making changes to database manager software itself) can be implemented without departing from the scope and spirit of several aspects of the present invention.

It should be appreciated that database server130may be implemented in a combination of one or more of hardware, software and firmware. The description is continued with respect to an embodiment which various features are operative by execution of corresponding software instructions.

7. Digital Processing System

FIG. 9is a block diagram illustrating the details of database server130in another embodiment. Server130may contain one or more processors such as central processing unit (CPU)910, random access memory (RAM)920, secondary storage unit580, graphics controller960, display unit950, network interface410, and operator interface470. All the components except display unit950may communicate with each other over communication path940, which may contain several buses as is well known in the relevant arts. The components ofFIG. 9are described below in further detail.

CPU910may execute instructions stored in RAM920to provide several features of the present invention. CPU910may contain multiple processing units, with each processing unit potentially being designed for a specific task. Alternatively, CPU910may contain only a single general purpose processing unit. RAM920may receive instructions from secondary storage unit580using communication path940. RAM920provides the memory space required for processing of various queries.

Graphics controller960generates display signals (e.g., in RGB format) to display unit950based on data/instructions received from CPU910. Display unit950contains a display screen to display the images defined by the display signals. Network interface410provides connectivity to a network (e.g., using Internet Protocol), and may be used to receive various transaction requests and provide the corresponding responses.

Secondary storage unit580may contain hard drive956, flash memory957, and removable storage drive958. Secondary storage unit580stores mapping of columns in user view of each of the table to columns in DBMS view of the corresponding table and may also store other data and software instructions, which enable database server130to provide several features in accordance with the present invention. Some or all of the data and instructions may be provided on removable storage unit959, and the data and instructions may be read and provided by removable storage drive958to CPU910. Floppy drive, magnetic tape drive, CD_ROM drive, DVD Drive, Flash memory, removable memory chip (PCMCIA Card, EPROM) are examples of such removable storage drive958.

Removable storage unit959may be implemented using medium and storage format compatible with removable storage drive958such that removable storage drive958can read the data and instructions. Thus, removable storage unit959includes a computer readable storage medium having stored therein computer software and/or data.

In this document, the term “computer program product” is used to generally refer to removable storage unit959or hard disk installed in hard drive955. These computer program products are means for providing software to server130. CPU910may retrieve the software instructions, and execute the instructions to provide various features of the present invention described above.

While various embodiments of the present invention have been described above, it should be understood that they have been presented by way of example only, and not limitation. Thus, the breadth and scope of the present invention should not be limited by any of the above described exemplary embodiments, but should be defined only in accordance with the following claims and their equivalents.