Reputation: 1021
I want to implement a temporal database using mysql. I have found out that BerekelyDB Engine is not supporting for current MySQL versions. I want to store data and query the history. (Don't want to delete any data). For example let say table stundet.
Student_ID Student_Name Class Added_Date Deleted_Date
1004 ABC 19 2011-02-03:18-24 2011-04-03:20-24
1004 ABC_D 19 2011-04-03:20-24 null
here is an example table I want. when I change some detail there will be a deleted_datetime stamp added and a whole new record will be added. When deleting a record it will only add a Deleted_Date
So do I have to write a whole library, or is there any particular library or database implementation to support my problem?..
By the way I found this http://www.cs.arizona.edu/projects/tau/tbdb/ but seems like it works only for older versions.
Upvotes: 2
Views: 1078
Reputation: 23824
When you upgrade your non temporal table to a temporal table you can achieve compatibility to older applications be creating a non temporal view for the temporal table. The view reduces the temporal data to the currently valid data. For such a view you can create so called instead of triggers which perform the correct action on the temporal table.
Unfortunately this does not work with MySQL, because the functionality of MySQL is way to limited. This means that you have two possible solutions:
The following example shows a possible solution for the first alternative. I have tested the example with Oracle 10gR2.
Create a table for the temporal data. The current value is the value, for which the ending is NULL. The unique constraint prevents that the table contains more than one current value. The table does not have a primary key because the id is not unique in the temporal table but only in the legacy view.
CREATE TABLE temporal (
id NUMBER NOT NULL,
starting TIMESTAMP NOT NULL,
ending TIMESTAMP,
attribute CHAR(20),
UNIQUE (id, ending));
Create a view for the legacy application.
CREATE VIEW legacy (id, attribute) AS
SELECT id, attribute FROM temporal WHERE ending IS NULL;
Create an "instead of" trigger for the legacy table which performs the correct insert into the temporal table. An insert into the legacy table is also an insert into the temporal table.
CREATE TRIGGER legacy_insert
INSTEAD OF INSERT ON legacy
FOR EACH ROW
BEGIN
INSERT INTO temporal (id, starting, ending, attribute)
VALUES (:NEW.id, CURRENT_TIMESTAMP, NULL, :NEW.attribute);
END;
Create an "instead of" trigger for the update of the legacy table. An update of the legacy table is an update and an insert of the temporal table. The current value gets updated and for the new value a new row gets inserted.
CREATE TRIGGER legacy_update
INSTEAD OF UPDATE ON legacy
FOR EACH ROW
BEGIN
UPDATE temporal SET ending = CURRENT_TIMESTAMP WHERE id = :NEW.id;
INSERT INTO temporal (id, starting, ending, attribute)
VALUES (:NEW.id, CURRENT_TIMESTAMP, NULL, :NEW.attribute);
END;
Create an "instead of" trigger for the deletion of an attribute in the legacy table. A delete of the legacy table is an update of the temporal table.
CREATE TRIGGER legacy_delete
INSTEAD OF DELETE ON legacy
FOR EACH ROW
BEGIN
UPDATE temporal SET ending = CURRENT_TIMESTAMP
WHERE id = :OLD.id AND ending IS NULL;
END;
Try to insert.
INSERT INTO legacy (id, attribute) VALUES (1, 'a');
SELECT * FROM legacy;
SELECT * FROM temporal;
Try to update.
UPDATE legacy SET attribute = 'A' WHERE id = 1;
SELECT * FROM legacy;
SELECT * FROM temporal;
Try to delete.
DELETE FROM legacy WHERE id = 1;
SELECT * FROM legacy;
SELECT * FROM temporal;
Upvotes: 2