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