OpenCabinet provides a simple solution for maintaining a revision history and audit trail of database updates.
This is implemented using history tables: For every database table that is included in the versioning support (this is a per-table decision) there will be a history table, that contains all the original columns plus a revision number. When updating the data table, the updated row is also inserted into the history table.
Only tables that have a primary key are supported.
The versioning system uses several database tables to store its meta-data (the mapping from data tables to history tables, and the changelog). You can use the OpenCabinet command-line tool to create them.
For every table whose update history you want to track, you have to create a history table. This is also done with the command-line tool.
VersionedTableManager vMan = new VersionedTableManager(dataSource);
Updates to versioned tables are done through the VersionedTableManager.
Changeset c = vMan.createChangeSet();
c.insert("tbl_bugs",
cabMan.persistAll(
bugId, severity, status, product, assignedTo, reporter, ts, description ));
c.commit();All updates (including deletes and inserts) are done through changesets, and they are always single-row operations (identified by the primary key columns of the underlying table).
A single changeset can contain more than one update, and can concern more than one table.
c.insert("tbl_bugs",
cabMan.persistAll(
bugId, severity, status, product, assignedTo, reporter, ts, description ));The insert() method takes the table name and a variable number of arguments that are used as column values. The number, types and order of arguments must match the column definition of the database table.
If you are using OpenCabinet data types you still have to use a CabinetManager to persist them. This can be done manually (using cabMan.persistAll) as in the example above, or you can use OpenCabinet data types (such as IdString directly, and pass the CabinetManager to the commit() method.
c.insert("tbl_bugs",
bugId, severity, status, product, assignedTo, reporter, ts, description );
c.commit(cabMan);c.update("tbl_bugs", cabMan.persistAll(
bugId, severity, status, product, assignedTo, reporter, ts, description ));The parameters for update() are the same as for insert().
You have to specify all columns, for columns that you do not want to update, you can pass the constant SingleRowUpdate.KEEP.
The row that you want to update is identified by the value of the primary key columns among the arguments.
c.delete("tbl_bugs", bugId);The delete() method takes the table name and the primary key values as arguments. If it is a composite primary key, the order of arguments is the order of columns in the primary key definition. This could be different from the overall column order of the table.
c.setApplicationName("BugDB Servlet");
c.setModuleName("Team Vaporware");
c.setUserId(12345);
c.setType("Routine data purge");
c.setComment("delete misguided input");The commit() will fail if there has been another update to the rows of the changeset after the changeset has been created.
Updates to other rows are not considered to be conflicting.
Until you commit() nothing is sent to the database at all. If you want to abort the transaction, you can just discard the changeset.