Why can't you store the msg_block fingerprint in the msg_block table? Like (with mysql syntax)CREATE TABLE messageblks ( messageblk_idnr bigint(21) NOT NULL auto_increment, physmessage_id bigint(21) NOT NULL default '0', messageblk longtext NOT NULL, blocksize bigint(21) NOT NULL default '0', ===> fingerprint bigint(21), PRIMARY KEY (messageblk_idnr), KEY physmsg_index (physmessage_id), ===> KEY (fingerprint), FOREIGN KEY (`physmessage_id`) REFERENCES `physmessage` (`id`) ON DELETE CASCADE ===> UNIQUE (messageblock, fingerprint), ) TYPE=InnoDB;
You can. That's effectively what I was doing in my message, except that you're not seeing it in the messageblk view. You probably don't want a unique constraint on messageblk, since the idea of the fingerprint is that it's a 1:1 mapping of the messageblk down to 128 bits.
At this point you're going to need to change dbmail to calculate and add the fingerprint, then do the conditional insert.
I've actually gotten it working on pgsql without changing dbmail using the rules and triggers. Performance seems reasonable. (although the testing managed to crash my laptop. )
eric
