Expire flag for nwnx odbc

[Zarx]

Hi!

I have a question regarding the expire flag that can be set in the ODBC plugin when setting database values. Is this feature actually implemented? I tested it using a MySQL database, and nothing really seems to happen (I wrote a value to the database with the expire flag set to one, set my PC clock two days forward and tried to read and overwrite the value, nothing happens (I just got the value returned and could update the vale in the DB, which by the way does not reset the 'latest' value)).

I had a look in the c++ source and couldn't find a hint on how the database entry should be removed after the expiration time. I assume MySql won't do this by itself, since the expire and latest columns are just normal database columns. I could imagine that the expiration would be checked by the plugin when retrieving a value from the database, discarding it when it has expired, but this does not seem to happen.

Could you clarify whether or not the expiration value should work and if so, how to get it working? Thanks!

[Baaleos]

Yes - you need to handle the cleanup yourself.

When you store the pw var, you can add an expire - which will be an int value relating to whatever interval you wish to use as the denomination of time to wait before removing the variable.

The expire column could relate to Seconds, Minutes, Hours, Days etc
its up to you to decide that.

Once you do, you can then write a script/query that clears out variables that fall within that time frame

Select * FROM x WHERE ts BETWEEN timestamp(DATE_SUB(NOW(), INTERVAL 30 MINUTE)) AND timestamp(NOW())

This sample query shows how to select a record between two timestamps.
What we need to do, is delete records where last (timestamp) + interval is less than timestamp(now)
To put it in terms relating to pwdata.

I came up with this quickly.
Please test it before you hook it in to delete anything.


SELECT *
FROM `pwdata`
WHERE last + INTERVAL expire DAY < TIMESTAMP(NOW()) AND expire !=0
This will return anything that has last has a (timestamp+expire) adding together to give a date that is in the past.

You could in theory hook up a delete job, and have your server run it every hour?

You just need to work out what interval you are going to work with.
Do you want the expire to relate to seconds, minutes, hours, days, weeks, months, years, decades, thousands of years, ages?

[Zarx]

Hmm, I think one or two Millennia will do (have to be careful of int overflows though)

Thanks for the reply! This clarifies a lot Considering the nwnx_odbc documentation and the fact that it explicitly mentions the expiration value is set in days, I assumed this was implemented already, but no problem!
For the moment, I fixed it by making wrapper functions "PurgeExpiredGetPersistentX()" for all getter methods in the nss include file, doing a database purge before retrieving values.

I use the following query for the purge:

DELETE FROM table WHERE TIMESTAMPDIFF( SECOND, last, current_timestamp ) >= expire AND expire > 0;

(so I use seconds for the expiration) This seems to work fine!

I was thinking it might even be faster to add this purge query in the c++ library, before executing any other query, but that might be over-doing things. At lesst now I can call the wrapper function when I know the data I store has an expiration while calling the normal getters when there is no expiration.