Auto expire records

[fgetce]

I am using Gryphons wonderful vendor system which I love and the players really love.

However they are beginning to clog up the stock of the vendors with their loot and I am looking for a way to auto purge items that are older than a specific time frame.

My approach was to add a column called "expires" and make it a time stamp. At certain intervals I will execute a scrip that will "delete from vendor_Stock where expires < UNIX_TIMESTAMP(now)"

Has anyone tackled this problem of over stocked vendors?

By the way thanks for all the help guys,my world lacks most of the planned content but already the players are very happy. I am about to do a massive update to complete the crafting stations and resources and the first of several quests are going into the game.

If Gryphon still lurks could you assist and devising a good approach to removing out of date items from vendors, BUT not remove the basic gear they get?

Thanks everyone. NWN FOREVER! BIOWARE RULES!

[fgetce]

I had looked around a bit before posting and never did find this post about iExpiration explained. So I can use the columns "expire" and "last" from the PWData table with the following altered statement for cleanup.


sql='delete from vendor_stock where expire <> 0 AND
UNIX_TIMESTAMP(last)+(expire*24*60*60) < UNIX_TIMESTAMP(now())';

Question now is what exactly does (expire*24*60*60) equal to?

I would like for vendors to remove items form their stock that are more than a week to 10 days old, to keep them relatively clean of junk loot.

Also I want them to restock the vendors with their base gear which would be only those items that have expire=0. So at some time they must first remove items with expire=0 than reinsert the same items (to make sure they have correct amount).

Should that be a script run from within the game or should I set it as a auto job from outside the game, like a cron job?

[Grinning Fool]

The timestamp datatype stores time in seconds -- so 24*60*60 = 1 day (24 hours * 60 minutes * 60 seconds)
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.

[Gryphyn]

[Grumalg]

I've never looked at Gryphon's system to be sure this would apply, but you might want to look at what triggers can do. You can set them up to fire before or after such events as INSERT, UPDATE, etc. Triggers can do conditional execution of SQL statements. Since they exist and run in the database they wouldn't represent any load on NWN as they did their action(s).

--- Grumalg ---