Not necessarily a database question , but as I'm trying to implement it on the database side here goes...
I'm using an Amazon API which throttles requests using a 'leaky bucket' model, such that (for example) each time your make a request it goes in to the bucket; the bucket can only hold 6 request (the
max_quota); the requests leak away at a rate of 1 per 60 seconds the
restore_rate). You can't make requests with a full bucket.
What I'd like to do is calculate whether I have room in my bucket. But rather than maintaining a stack counter and continually emptying it in real time, I'm trying to calculate it, based on the requests I've made. I'm not sure if it can be done.
I got as far is this solution:
SELECT act.action_id, act.action, rule.rule_id, rule.max_quota, rule.restore_rate,
COUNT(DISTINCT req.request_id) AS unexpired_requests
FROM aws_actions act
JOIN aws_throttling_rule_action rule_act ON act.action_id=rule_act.action_id
JOIN aws_throttling_rule rule ON rule_act.rule_id=rule.rule_id
LEFT JOIN aws_request req ON req.action_id=act.action_id=req.action_id AND req.datetime>CURDATE() - INTERVAL 1 DAY AND req.datetime + INTERVAL rule.restore_rate SECOND >=NOW()
But then realised that this assumes that ALL requests expired after the restore_rate as passed, but they won't, they'll leak out one at a time, and I can't think how to work it out.
Apologies for rambling, but if anyone has any suggestions, I'm all ears!