For several years now, I'm yearning for a feature that I think that all SQL based databases that I know are missing, and throw it back to the program side.
The feature I wish to have is a way to set a specific record to be valid for only a known period of time, and when the time is up, I'll be able to do something.
In this post I'll try to create some sort of general specification for such feature, and I hope that there will be many comments on this that will benefit everyone, and I hopeful, that they will make the idea better.
An SQL statement that allow me to create, update and cancel a set of timeout per record or a where statement. It can look like this.
A record that is pointing to such timeout, can not be entered to another timeout rule. Any attempt to add additional timeoutout rule to such record must raise and exception, even if it part of a "where" statement, so the statement will have to have condition to exclude it from the new rules.
A Create new timeout condition can look something like this:
CREATE TIMEOUT <timeout name> FOR <table name> SET <time in seconds | TIMESTAMP timest> <IDENTIFIED BY id | WHERE condition> [ON TIMEOUT TRIGGER <trigger name>]
An Update condtion for existed timeout can look something like this:
UPDATE TIMEOUT <timeout name> FOR <table name> SET <time in seconds | TIMESTAMP timest> <IDENTIFIED BY id | WHERE condition> [ON TIMEOUT TRIGGER <trigger name>]
And a removing of a timeout can look something like this:
DROP TIMEOUT <timeout name>
Looking up for the time left for a record:
SELECT <timeout name [,timeout name ...] | *> FROM <TTL> [where condition]
Where TTL is a place that the query can get this information.
The idea is to be able to raise a trigger when the time is out, but if no trigger was set, then the record or records are removed. If the database have audit-trail (Firebird SQL for example), such trigger-less timeouts will be marked there.
Beside the creation, editing and deletion of the rule, there should be a way to see how much time was left for a specific rule, or set of rules.
If something went wrong, then an exception should be raised.
That's the whole idea in general. I'll be more then happy to hear what you have to say about it.