Re: Need help with db script, and daily routines

Peter Neu wrote:
> Hello,
>
> I have 2 tables:
>
> In one I log the user name of a web site user like this:
>
> Name Access time
> "makost0001" " 2007-04-19 15:09:19"
> "makost0001" " 2007-04-19 15:09:19"
>
> In the other I have the user name his group and the expiry date of his
> account.
>
> Name Group Expiry date
> "makost0001" "book" "2013-04-05 09:41:25.357677"
>
> When the user accesses the web site for the first time I need to change the
> expiry date
> to < today > + 3 years.
>
> Problem is I need to do this on a regular basis once a day to avoid table
> locks. And also how do I tell the program to find the earliest access time
> of a user? Like shown above there a multiple
> entries for the same time & user because of the multiple requests. :o(
>
> Should I have a field in the first table like <ignore this entry forever>
> when an expiry date is already set?
>
> I'm pretty new to db programming and especially to PostgreSQL. Can somebody
> please help?

Once a day, just do something like this:

UPDATE user_expiry_dates
SET expiry_date = CURRENT_DATE + '3 years'::interval
WHERE user_name IN (
SELECT user_name
FROM access_logs
GROUP BY user_name
HAVING min(access_time) BETWEEN (CURRENT_DATE - '1 day'::interval)
AND CURRENT_DATE)
);

However, if your expiry date was null before the user has visited the
site you might find it more efficient to do:

UPDATE user_expiry_dates
SET expiry_date = CURRENT_DATE + '3 years'::interval
WHERE
expiry_date IS NULL
AND user_name IN (
SELECT user_name
FROM access_logs
HAVING access_time BETWEEN (CURRENT_DATE - '1 day'::interval) AND
CURRENT_DATE)
);

Here we don't care if this user has logged in 100 times before today, we
only update the expiry_date if it's NULL.

HTH
--
Richard Huxton
Archonet Ltd