how to enable temporal database capability in mysql.?

Question

How to enable the temporal database compatibility in mysql database?

Is there any library or a plug-in to enable the temporal upward compatibility? I want to keep the past records whenever a current record changes.

• MDCCL: A future visitor may find of interest this Q & A. The method detailed there does not require additional libraries or plug-ins.

Answer 1

1. Add a from_date field, and a nullable to_date field

2. expand your primary key to include from_date

3. keep everything in UTC

4. consider using PostgreSQL as there is a temporal extension for preventing overlapping time periods.

Here is the book from "THE" temporal db guy (Snodgrass):

http://www.cs.arizona.edu/~rts/tdbbook.pdf

Also try Chapter 4 of Celko's Smarties book

Answer 2

Exactly what you are asking for is in the MariaDB 10.3.x and called `System Versioned Tables'

It is in a stable release as of version 10.3.16. But beware that the mysqldump utilty might not backup data correctly.

https://mariadb.com/kb/en/library/system-versioned-tables/

Info:

System-versioned tables is a feature first introduced in the SQL:2011 standard. System-versioned tables store the history of all changes, not only the data which is valid at the current moment in time. This allows data analysis for any point in time, auditing of changes and comparison of data from different points in time. Typical uses cases are:

• Forensic analysis & legal requirements to store data for N years.
• Data analytics (retrospective, trends etc.), e.g. to get your staff information as of one year ago.
• Point-in-time recovery - recover a table state as of particular point in time.

Answer 3

A temporal database is a database with built-in time aspects, for example a temporal data model and a temporal version of Structured Query Language.

More specifically the temporal aspects usually include valid-time and transaction-time. These attributes go together to form bitemporal data.

1.Valid time denotes the time period during which a fact is true with respect to the real world.

2.Transaction time is the time period during which a fact is stored in the database.

3.Bitemporal data combines both Valid and Transaction Time.

UPDATE

Firstly i am removing that part which i have added previously as MySQL5.1 and its above versions of MySQL does not support BDB.have a look at This Link.But in My Opinion what the BDB was doing the InnoDB is capable of Doing all. As BDB is not supported anymore By MySQL.But the InnoDB is able to do all which the BDB was doing.

For More Information About temporal database have a look at This.

With MySQL, The unavailability of CHECK CONSTRAINT,DEFERRED CONSTRAINT, and some limits on triggers, make it harder to build temporal relational database architecture in MySQL.

CHECK CONSTRAINT

A CHECK CONSTRAINT is often of the form CHECK( [NOT] EXISTS( select_expression ))

MySQL implements foreign key constraints in INNODB tables, but does not yet implement CHECK CONSTRAINT. Until it does, such constraints must be enforced by other means. That has onerous consequences for time-valid tables. Some time-valid constraints can be enforced in triggers, but most of the temporal constraints we will consider cannot. Until MySQL implements CHECK CONSTRAINT, they must be enforced in application code. That is a heavy penalty.

Deferred constraints

MySQL does not yet implement deferred constraints, either. Furthermore, constraints are applied row-wise rather than at COMMIT time. This raises a problem for many complex constraints, even for some simple ones. For example to delete a MySQL row which refers to itself via a foreign key, you must temporarily SET foreign_key_checks = 0. A transaction fulfilling a complex constraint must leave the database in a consistent state.But there is nothing in relational database theory to suggest that a database should be in a consistent state after each statement within a transaction.

Triggers

MySQL 5 triggers cannot issue UPDATE statements on the trigger table, and cannot raise errors. These limitations create difficulties for implementing transaction validity in MySQL, but the difficulties can be overcome.

• Abdul Manaf: I am answering this question in some less knowledge i will update it as soon as possible.
• Zeus: Thank you very much for the information @Abdul Munaf.. Can I have little more detail?.. When I install MySQL, to enable TUC what should I do?/ Should I install BereklyDB also??.. or just the MySQL in enough??.. I'm a little confused here
• Abdul Manaf: @ArunaSujith : I have updated My Answer.Your MySQL is enough you do not need to do any thing.
• Zeus: Thank you for your support. I think it solved all the doubts in my mind. Again thanks for the quick reply.
• Zeus: Sorry for troubling again.I've installed mysql mysql-5.0.95-win32 version. And when I enter command "SHOW ENGINES" i saw that support for berekelyDB is "NO". How can I enable the temporal compatibility. A good tutorial is highly appreciated because I found it very difficult to create and manipulate such tables. (this looks like a dumb question: can I query the old records which are labelled as deleted??.. I mean it's the whole idea of temporal."nothing to be deleted ever"..) It's ok if this can active in MySQL 5.5 also.