[horde] IMP IMAP DB Cache backend lifetime

[Federico Giannici]

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.