[horde] IMP IMAP DB Cache backend lifetime

[Joao S Veiga]

----- Message from Federico Giannici <giannici at neomedia.it> ---------
> On 08/31/17 13:53, Joao S Veiga wrote:
>>
>> ----- 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
>
>
> Yes, this is exactly what I suggested, but only for the  
> horde_imap_client_message table. Are you sure that  
> horde_imap_client_metadata table can contain more than an element  
> per messageid? I looked at the data in my DB and found only ONE row  
> per messageid. But I must admit that I don't know what this metadata  
> is... :-)
>
> Bye.
>

Yes, in my environment there are more than one metadata entry per  
messageid, with different "field" values. The metadata as far as I  
could gather from the scripts include search results caching and  
modification sequences (if supported by the IMAP server).

Joao