Timeout records – Feature request

8 תשובות

Foreword

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.

The Idea

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.

More details

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.

8 מחשבות על “Timeout records – Feature request”

  1. ארתיום

    What is wrong with having a timeout column for the table?

    I mean you can create almost everything you noted with "where now() < timeout" or something like that.

    The only problem is to check for timeouts. So you need to have some daemon running
    and for example running a simple script every X seconds.

    I can see that this case it something similar to handling stuff like web sessions with a timeout

    Generally it can be nice feature but it would enforce the SQL engine to be a client-server solution
    and this isn't something that can't be implemented – yet another index and yet another column,
    just you need to add some schedule task that checks timeouts and removes dead records

    להגיב ↓
    1. ik_5 מאת

      When you talk about SQLite, or Firebird Embedded, sure it is a problem. But most SQL databases are server based.

      The thing is, that repeated tasks such as creatin daemons that constantly check stuff around, and adding a lot to the database, it usually starting in a very simple way, and ends up very complicated, and you usually will use some sort of IPC or RPC to tell other clients that something had happened if needed.

      Databases are a tool to save *and* manipulate data in the most effective way. Another index, another trigger etc, are not the issue here. A database can have thousands of that stuff, the problem is that you divide your logic and work to several locations. Adding extra work for every project/feature you require to do, and it repeated every time.

      להגיב ↓

  3. פינגבק: Firebird News » Timeout records – Feature request

כתיבת תגובה

אתר זו עושה שימוש ב-Akismet כדי לסנן תגובות זבל. פרטים נוספים אודות איך המידע מהתגובה שלך יעובד.