Record Versioning

Contents

Record Versioning
Overview

Overview

The goal is to have the ability to track any changes in the system at the database level. This goal may be achieved by log tables and database triggers. The log tables are copies of the tables to be tracked with some additional fields, while the database triggers save the records before all changes.

The sample below shows the solution on the FileTypeRef table.

The log table

The log table is the copy of the original table with the following additional fields:

rvId - simply a serial field to have correct sequence also within a second
rvUserId - DrawMGT userId of the user who did the change
rvTimestamp - time of the operation
rvOperation - 'i', 'u', 'd' for the operations insert, update, delete respectively

It is recommended to set the primary key of the original table to 'not null' and to set an index on it in the log table:

CREATE TABLE FileTypeRef_log
(
    rvId                      SERIAL                        ,
    rvUserId                  INTEGER               not null,
    rvTimestamp               DATETIME              not null,
    rvOperation               CHAR(1)                       ,
    fileTypeId                INTEGER               not null,
    code                      CHAR(32)                      ,
    name                      VARCHAR(128)                  ,
    description               VARCHAR(255)                  ,
    mimeType                  VARCHAR(255)                  ,
    fileExtension             CHAR(32)                      ,
    define                    CHAR(32)                      ,
    PRIMARY KEY( rvId )                                     ,
    INDEX iFileTypeRef_log (fileTypeId)

);

The Database Triggers

Note that the SQL command delimiter must be changed at issuing the 'CREATE TRIGGER' commands because the usual delimiter ';' is part of the command itself:

DELIMITER |

CREATE TRIGGER ins_FileTypeRef AFTER INSERT ON FileTypeRef
FOR EACH ROW
BEGIN
  IF @userId is null THEN
    SET @userId = 0;
  END IF;
  INSERT INTO FileTypeRef_log VALUES (
    0, @userId, now(), 'i',
    NEW.fileTypeId,
    NEW.code,
    NEW.name,
    NEW.description,
    NEW.mimeType,
    NEW.fileExtension,
    NEW.define
  );
END;
|

CREATE TRIGGER upd_FileTypeRef AFTER UPDATE ON FileTypeRef
FOR EACH ROW
BEGIN
  IF @userId is null THEN
    SET @userId = 0;
  END IF;
  INSERT INTO FileTypeRef_log VALUES (
    0, @userId, now(), 'u',
    OLD.fileTypeId,
    OLD.code,
    OLD.name,
    OLD.description,
    OLD.mimeType,
    OLD.fileExtension,
    OLD.define
  );
END;
|

CREATE TRIGGER del_FileTypeRef AFTER DELETE ON FileTypeRef
FOR EACH ROW
BEGIN
  IF @userId is null THEN
    SET @userId = 0;
  END IF;
  INSERT INTO FileTypeRef_log VALUES (
    0, @userId, now(), 'd',
    OLD.fileTypeId,
    OLD.code,
    OLD.name,
    OLD.description,
    OLD.mimeType,
    OLD.fileExtension,
    OLD.define
  );
END;
|

DELIMITER ;

Notes

The session variable '@userId' must be set every time on new database connection:

$userId = $session->getGlobal( 'userId' );
$cmd = "set @userId=$userId";
$result = $db->Execute( $cmd );

The triggers set @userId to 0 if it is empty (if the change is initiated from mysql command line interface or by a daemon, etc.):
The record, saved by the insert trigger, will be the same as of the record saved by the update trigger on the first update operation, however the userId and the timestamp contain important information.
Because the log tables are hidden may be that the locking mechanism used in DrawMGT must be revised.