[horde] IMP IMAP DB Cache backend lifetime

[Joao S Veiga]

----- 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...

If you look at the horde_imap_client_data, you probably have a lot of  
data with NULL in the 'modified' field because of what I mentioned  
before (the _createUid() does not set the 'modified', then the script  
does not clear these rows).

I have changed my  
Imap_Client/lib/Horde/Imap/Client/Cache/Backend/Db.php to insert the  
'modified' when creating the entries (it's under the "protected  
function _createUid($mailbox)"):

--- Db.php.orig 2017-08-30 07:57:02.299562533 -0300
+++ Db.php      2017-08-30 07:58:37.094862818 -0300
@@ -389,15 +389,16 @@
      {
          return $this->_db->insert(
              sprintf(
-                'INSERT INTO %s (hostspec, mailbox, port, username) ' .
-                    'VALUES (?, ?, ?, ?)',
+                'INSERT INTO %s (hostspec, mailbox, port, username,  
modified) ' .
+                    'VALUES (?, ?, ?, ?, ?)',
                  self::BASE_TABLE
              ),
              array(
                  $this->_params['hostspec'],
                  $mailbox,
                  $this->_params['port'],
-                $this->_params['username']
+                $this->_params['username'],
+                $time()
              )
          );
      }

After this you'll want to clear the entries that are already with  
'modified' = NULL:

delete from horde_imap_client_metadata where messageid in (select  
messageid from horde_imap_client_data where modified is NULL);
delete from horde_imap_client_message where messageid in (select  
messageid from horde_imap_client_data where modified is NULL);
delete from horde_imap_client_data where modified is NULL;

and also messages/metadata that have no corresponding entries in  
horde_imap_client_data (should not exist, but at least in my server  
they do):

delete FROM horde.horde_imap_client_message where messageid not in  
(select messageid from horde_imap_client_data);
delete FROM horde.horde_imap_client_metadata where messageid not in  
(select messageid from horde_imap_client_data);

Then what I did was to create a script to clean unmatched and old  
entries (older than 120 days) every day, from crontab. (I'm not a php  
guy, so I did it on perl, which is my native tongue):

(yes, running  imp-query-imap-cache periodically could do it, but that  
script does not clear the unmatched messages/metadata too, only the  
old ones)

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $db = DBI->connect("DBI:mysql:horde:localhost", "horde",  
"XXXXXXXX", {mysql_enable_utf8=>1});
my $old = time() - 120 * 24 * 3600;
my $q = $db->prepare("delete from horde_imap_client_metadata where  
messageid in (select messageid from horde_imap_client_data where  
modified < ?);");
$q->execute($old);
$q = $db->prepare("delete from horde_imap_client_message where  
messageid in (select messageid from horde_imap_client_data where  
modified < ?);");
$q->execute($old);
$q = $db->prepare("delete from horde_imap_client_data where modified < ?;");
$q->execute($old);
$db->do("delete FROM horde.horde_imap_client_message where messageid  
not in (select messageid from horde_imap_client_data);");
$db->do("delete FROM horde.horde_imap_client_metadata where messageid  
not in (select messageid from horde_imap_client_data);");
$db->disconnect();

Note that this will only work after you have cleared the NULL entries  
and changed Db.php so that doesn't happen again.

Best regards,

Joao S Veiga