Jorge Bastos wrote:
Hi Jon,
Jorge Bastos wrote:
John & Paul,
To recreate the complete cache, can i:
---
Delete from dbmail_header;
Delete from dbmail_headername;
Delete from dbmail_headervalue;
Delete from dbmail_envelope;
Dbmail-util -by
Yes - delete from all 4 tables. Do take your daemons down while doing
this to be from cache pollution and client pulling bogus data. I don't
see anything wrong with your db schema that we haven't corrected.
use dbmail-util -tuby twice to make sure all is well.
To continue this matter,
I was checking the table structure, and, are you sure I can delete from
dbmail_header?
It has some connections by foreign keys, data won't be deleted from the
other tables and consequently I'll lose data?
The same goes for dbmail_envelope.
Or should I do before this:
SET FOREIGN_KEY_CHECKS=0;
After:
SET FOREIGN_KEY_CHECKS=1; ?
Jorge,
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
The foreign key constraints are based upon the physmessage_id being
there. So if a physmessage is deleted, then the envelope and header
cache for the message is deleted. Subsequently, any unused header names
or values are removed when the pointers to them are deleted. You do not
need turn off the key check. It would be faster though to run the
attached script to drop the tables and recreate them. This script should
clean out all the caching. You would then need to run dbmail-util -by to
fill in the cache tables. If you have a bunch of messages that can be
purged, the do a purge run before rebuilding the cache to speed it up.
-Jon
--
Scanned for viruses and dangerous content by MailScanner
SET FOREIGN_KEY_CHECKS=0;
-- Delete old cached data for the references and envelopes
BEGIN;
DELETE FROM dbmail_referencesfield;
DELETE FROM dbmail_envelope;
COMMIT;
-- Drop old legacy cache tables and new views
DROP TABLE IF EXISTS dbmail_ccfield;
DROP TABLE IF EXISTS dbmail_datefield;
DROP TABLE IF EXISTS dbmail_fromfield;
DROP TABLE IF EXISTS dbmail_replytofield;
DROP TABLE IF EXISTS dbmail_subjectfield;
DROP TABLE IF EXISTS dbmail_tofield;
DROP VIEW IF EXISTS dbmail_fromfield;
DROP VIEW IF EXISTS dbmail_ccfield;
DROP VIEW IF EXISTS dbmail_tofield;
DROP VIEW IF EXISTS dbmail_subjectfield;
DROP VIEW IF EXISTS dbmail_datefield;
-- Drop new cache tables because of move from legacy tables
DROP TABLE IF EXISTS dbmail_header;
DROP TABLE IF EXISTS dbmail_headername;
DROP TABLE if EXISTS dbmail_headervalue;
-- Create new single instance header storage tables
CREATE TABLE `dbmail_headername` (
`id` bigint(20) NOT NULL auto_increment,
`headername` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `headername` (`headername`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `dbmail_headervalue` (
`id` bigint(20) NOT NULL auto_increment,
`hash` varchar(255) NOT NULL,
`headervalue` text NOT NULL,
`sortfield` varchar(255) default NULL,
`datefield` datetime default NULL,
PRIMARY KEY (`id`),
KEY `hash` (`hash`),
KEY `headervalue` (`headervalue`(255)),
KEY `sortfield` (`sortfield`),
KEY `datefield` (`datefield`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `dbmail_header` (
`physmessage_id` bigint(20) NOT NULL,
`headername_id` bigint(20) NOT NULL,
`headervalue_id` bigint(20) NOT NULL,
PRIMARY KEY (`physmessage_id`,`headername_id`,`headervalue_id`),
KEY `physmessage_id` (`physmessage_id`),
KEY `headername_id` (`headername_id`),
KEY `headervalue_id` (`headervalue_id`),
KEY `physmessage_id_headername_id` (`physmessage_id`,`headername_id`),
KEY `physmessage_id_headervalue_id` (`physmessage_id`,`headervalue_id`),
KEY `headername_id_headervalue_id` (`headername_id`,`headervalue_id`),
CONSTRAINT `dbmail_header_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES
`dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `dbmail_header_ibfk_2` FOREIGN KEY (`headername_id`) REFERENCES
`dbmail_headername` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `dbmail_header_ibfk_3` FOREIGN KEY (`headervalue_id`) REFERENCES
`dbmail_headervalue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- views for IMAP sort
CREATE VIEW dbmail_fromfield AS
SELECT physmessage_id,sortfield AS fromfield
FROM dbmail_messages m
JOIN dbmail_header h USING (physmessage_id)
JOIN dbmail_headername n ON h.headername_id = n.id
JOIN dbmail_headervalue v ON h.headervalue_id = v.id
WHERE n.headername='from';
CREATE VIEW dbmail_ccfield AS
SELECT physmessage_id,sortfield AS ccfield
FROM dbmail_messages m
JOIN dbmail_header h USING (physmessage_id)
JOIN dbmail_headername n ON h.headername_id = n.id
JOIN dbmail_headervalue v ON h.headervalue_id = v.id
WHERE n.headername='cc';
CREATE VIEW dbmail_tofield AS
SELECT physmessage_id,sortfield AS tofield
FROM dbmail_messages m
JOIN dbmail_header h USING (physmessage_id)
JOIN dbmail_headername n ON h.headername_id = n.id
JOIN dbmail_headervalue v ON h.headervalue_id = v.id
WHERE n.headername='to';
CREATE VIEW dbmail_subjectfield AS
SELECT physmessage_id,headervalue AS subjectfield
FROM dbmail_messages m
JOIN dbmail_header h USING (physmessage_id)
JOIN dbmail_headername n ON h.headername_id = n.id
JOIN dbmail_headervalue v ON h.headervalue_id = v.id
WHERE n.headername='subject';
CREATE VIEW dbmail_datefield AS
SELECT physmessage_id,datefield
FROM dbmail_messages m
JOIN dbmail_header h USING (physmessage_id)
JOIN dbmail_headername n ON h.headername_id = n.id
JOIN dbmail_headervalue v ON h.headervalue_id = v.id
WHERE n.headername='date';
SET FOREIGN_KEY_CHECKS=1;
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
