DROP INDEX dbmail_sievescripts_3;
CREATE UNIQUE INDEX dbmail_sievescripts_3 ON dbmail_sievescripts(owner_idnr,name);

DROP TABLE IF EXISTS dbmail_mimeparts;
CREATE TABLE dbmail_mimeparts (
	id	INTEGER NOT NULL PRIMARY KEY,
	hash	TEXT NOT NULL,
	data	BLOB NOT NULL,
	size	INTEGER NOT NULL
);

CREATE INDEX dbmail_mimeparts_1 ON dbmail_mimeparts (hash);

DROP TABLE IF EXISTS dbmail_partlists;
CREATE TABLE dbmail_partlists (
	physmessage_id	INTEGER NOT NULL,
   	is_header 	BOOLEAN DEFAULT '0' NOT NULL,
	part_key	INTEGER DEFAULT '0' NOT NULL,
	part_depth	INTEGER DEFAULT '0' NOT NULL,
	part_order	INTEGER DEFAULT '0' NOT NULL,
	part_id		INTEGER NOT NULL
);

CREATE INDEX dbmail_partlists_1 ON dbmail_partlists(physmessage_id);
CREATE INDEX dbmail_partlists_2 ON dbmail_partlists(part_id);


-- ALTER TABLE ONLY dbmail_partlists
--    ADD CONSTRAINT dbmail_partlists_part_id_fkey FOREIGN KEY (part_id) REFERENCES dbmail_mimeparts(id) ON UPDATE CASCADE ON DELETE CASCADE;

DROP TRIGGER IF EXISTS fk_insert_partlists_mimeparts_id;
CREATE TRIGGER fk_insert_partlists_mimeparts_id
	BEFORE INSERT ON dbmail_partlists
	FOR EACH ROW BEGIN
		SELECT CASE 
			WHEN (new.part_id IS NOT NULL)
				AND ((SELECT id FROM dbmail_mimeparts WHERE id = new.part_id) IS NULL)
			THEN RAISE (ABORT, 'insert on table "dbmail_partlists" violates foreign key constraint "fk_insert_partlists_mimeparts_id"')
		END;
	END;
DROP TRIGGER IF EXISTS fk_update_partlists_mimeparts_id;
CREATE TRIGGER fk_update_partlists_mimeparts_id
	BEFORE UPDATE ON dbmail_partlists
	FOR EACH ROW BEGIN
		SELECT CASE 
			WHEN (new.part_id IS NOT NULL)
				AND ((SELECT id FROM dbmail_mimeparts WHERE id = new.part_id) IS NULL)
			THEN RAISE (ABORT, 'update on table "dbmail_partlists" violates foreign key constraint "fk_update_partlists_mimeparts_id"')
		END;
	END;
DROP TRIGGER IF EXISTS fk_update2_partlists_mimeparts_id;
CREATE TRIGGER fk_update2_partlists_mimeparts_id
	AFTER UPDATE ON dbmail_mimeparts
	FOR EACH ROW BEGIN
		UPDATE dbmail_partlists SET part_id = new.id WHERE part_id = OLD.id;
	END;
DROP TRIGGER IF EXISTS fk_delete_partlists_mimeparts_id;
CREATE TRIGGER fk_delete_partlists_mimeparts_id
	BEFORE DELETE ON dbmail_mimeparts
	FOR EACH ROW BEGIN
		DELETE FROM dbmail_partlists WHERE part_id = OLD.id;
	END;

-- ALTER TABLE ONLY dbmail_partlists
--    ADD CONSTRAINT dbmail_partlists_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE;

DROP TRIGGER IF EXISTS fk_insert_partlists_physmessage_id;
CREATE TRIGGER fk_insert_partlists_physmessage_id
	BEFORE INSERT ON dbmail_partlists
	FOR EACH ROW BEGIN
		SELECT CASE 
			WHEN (new.physmessage_id IS NOT NULL)
				AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL)
			THEN RAISE (ABORT, 'insert on table "dbmail_partlists" violates foreign key constraint "fk_insert_partlists_physmessage_id"')
		END;
	END;
DROP TRIGGER IF EXISTS fk_update_partlists_physmessage_id;
CREATE TRIGGER fk_update_partlists_physmessage_id
	BEFORE UPDATE ON dbmail_partlists
	FOR EACH ROW BEGIN
		SELECT CASE 
			WHEN (new.physmessage_id IS NOT NULL)
				AND ((SELECT id FROM dbmail_physmessage WHERE id = new.physmessage_id) IS NULL)
			THEN RAISE (ABORT, 'update on table "dbmail_partlists" violates foreign key constraint "fk_update_partlists_physmessage_id"')
		END;
	END;
DROP TRIGGER IF EXISTS fk_update2_partlists_physmessage_id;
CREATE TRIGGER fk_update2_partlists_physmessage_id
	AFTER UPDATE ON dbmail_physmessage
	FOR EACH ROW BEGIN
		UPDATE dbmail_partlists SET physmessage_id = new.id WHERE physmessage_id = OLD.id;
	END;
DROP TRIGGER IF EXISTS fk_delete_partlists_physmessage_id;
CREATE TRIGGER fk_delete_partlists_physmessage_id
	BEFORE DELETE ON dbmail_physmessage
	FOR EACH ROW BEGIN
		DELETE FROM dbmail_partlists WHERE physmessage_id = OLD.id;
	END;

ALTER TABLE dbmail_mailboxes ADD mtime DATETIME DEFAULT '0' NOT NULL;
CREATE INDEX dbmail_mailbox_4 ON dbmail_mailboxes(mtime);

CREATE TABLE dbmail_keywords (
	keyword		TEXT NOT NULL,
	message_idnr	INT NOT NULL
);
CREATE UNIQUE INDEX dbmail_keywords_1 ON dbmail_keywords(keyword,message_idnr);

DROP TRIGGER IF EXISTS fk_insert_dbmail_keywords_dbmail_messages_message_idnr;
CREATE TRIGGER fk_insert_dbmail_keywords_dbmail_messages_message_idnr
	BEFORE INSERT ON dbmail_keywords
	FOR EACH ROW BEGIN
		SELECT CASE 
			WHEN (new.message_idnr IS NOT NULL)
				AND ((SELECT message_idnr FROM dbmail_messages WHERE message_idnr = new.message_idnr) IS NULL)
			THEN RAISE (ABORT, 'insert on table "dbmail_keywords" violates foreign key constraint "fk_insert_dbmail_keywords_dbmail_messages_message_idnr"')
		END;
	END;

DROP TRIGGER IF EXISTS fk_update_dbmail_keywords_dbmail_messages_message_idnr;
CREATE TRIGGER fk_update_dbmail_keywords_dbmail_messages_message_idnr
	BEFORE UPDATE ON dbmail_keywords
	FOR EACH ROW BEGIN
		SELECT CASE 
			WHEN (new.message_idnr IS NOT NULL)
				AND ((SELECT message_idnr FROM dbmail_messages WHERE message_idnr = new.message_idnr) IS NULL)
			THEN RAISE (ABORT, 'update on table "dbmail_keywords" violates foreign key constraint "fk_update_dbmail_keywords_dbmail_messages_message_idnr"')
		END;
	END;

DROP TRIGGER IF EXISTS fk_update2_dbmail_keywords_dbmail_messages_message_idnr;
CREATE TRIGGER fk_update2_dbmail_keywords_dbmail_messages_message_idnr
	AFTER UPDATE ON dbmail_messages
	FOR EACH ROW BEGIN
		UPDATE dbmail_keywords SET message_idnr = new.message_idnr WHERE message_idnr = OLD.message_idnr;
	END;

DROP TRIGGER IF EXISTS fk_delete_dbmail_keywords_dbmail_messages_message_idnr;
CREATE TRIGGER fk_delete_dbmail_keywords_dbmail_messages_message_idnr
	BEFORE DELETE ON dbmail_messages
	FOR EACH ROW BEGIN
		DELETE FROM dbmail_keywords WHERE message_idnr = OLD.message_idnr;
	END;