[horde] IMP IMAP DB Cache backend lifetime

[Joao S Veiga]

----- Message from Joao S Veiga <jsveiga at rf.com.br> ---------

>> ----- Message from Federico Giannici <giannici at neomedia.it> ---------
>>>
>>> I think that a possible simple solution could be to add a  
>>> "created" field the horde_imap_client_message table automatically  
>>> set to the current timestamp at the INSERT. Then we could use this  
>>> field to purge the messages that were cached since a lot (maybe a  
>>> month?). In this way no messages would remain in cache forever,  
>>> but eventually the reload of the message would happen only after a  
>>> lot of time (and in a "time distributed" way).
>>>
>>> What do you think?
>>>
>>
>> Hi Federico,
>>
>> (Did you fix the Db.php script first (the missing ")")? That would  
>> prevent the lifetime limit to work.)
>>
>> The horde_imap_client_message (and the horde_imap_client_metadata)  
>> are related to the horde_imap_client_data by the messageid field,  
>> and horde_imap_client_data already (should) have timestamps, so  
>> when clearing the tables by timestamp, the old _message/_metadata  
>> entries should go away too. The script uses the  
>> horde_imap_client_data 'modified' timestamp to do that, but...
>
> Oops, sorry; I see what you mean; the update timestamp for  
> horde_imap_client_data will be renewed, but there will still be very  
> old entries in horde_imap_client_message that will stay there unless  
> the actual mailbox gets "old" enough to expire.
>


I'll experiment with this in my system:

ALTER TABLE `horde`.`horde_imap_client_metadata`
ADD COLUMN `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP AFTER  
`messageid`;

ALTER TABLE `horde`.`horde_imap_client_message`
ADD COLUMN `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP AFTER  
`messageid`;

(you might want to set all 'created' values to the current timestamp  
or purge the cache completely, or previous entries will have NULL  
timestamps)

And I added this to the cleaning perl script:

$db->prepare("delete from horde_imap_client_message where created <  
from_unixtime(?);");
$q->execute($old);
$q = $db->prepare("delete from horde_imap_client_metadata where  
created < from_unixtime(?);");
$q->execute($old);

These require no changes in the horde scripts - but might be a problem  
if upgrade scripts alter the table.

best regards,

Joao