Decimail Server - Upgrading

Upgrading

This page contains notes for users who are upgrading from one version of Decimail to another.

The basic procedure is:

Build new daemons from scratch.
Re-run most of the SQL, if necessary because the database format has changed.
But DON'T re-run any SQL that would recreate the tables containing the messages, as this would DELETE ALL OF YOUR EMAIL (unless you don't mind that happening). Instead, follow the notes on this page.

1. Version 0.3

In version 0.3 the view that finds subscribed IMAP mailboxes has been fixed. This is generated by the create_imap.sql script. This script also creates the table that stores IMAP message flags (seen, answered, deleted). If you re-run create_imap.sql all of these flags will be lost and everything will appear as seen, unanswered and undeleted. If this matters, you should instead regenerate the imap_subscribed_mailboxes view manually. Simply start psql, "drop view imap_subscribed_mailboxes;" and enter the "create view imap_subscribed_mailboxes" command from create_imap.sql manually.

2. Version 0.4

Version 0.4 adds a daemon that deletes the backup files for messages that have been deleted from the database. If you want to keep these files permanently you should simply not install dmdeleted. If you do install it, note that it is triggered by notifications sent via that database when messages are deleted. Adding this has required a one-line addition to the function log_deleted_message in create_deletelog.sql. However, if you simply re-run create_deletelog.sql your log of already-deleted messages will be cleared. Instead you should manually replace the log_deleted_message function with the new version. Do this interactivly using psql and copy/paste the new code out of create_deletelog.sql, as follows:

$ psql -d decimail -U decimail => create or replace function log_deleted_message() returns trigger as ' '> begin '> insert into delete_log values (old.msg_id); '> notify delete_log; '> return old; '> end; '> ' language plpgsql;

3. Version 0.5

Version 0.5 adds the actions framework and an example application of it to create an automatic People/ folder hierarchy. To add the actions framework to your system, run create_actions.sql. To add the new People/ folder hierarchy, replacing the old People/ folder based on address_info.sql, drop table address_info and run create_people.sql, then re-run mailboxes.sql.

4. Version 0.6

The SQL files related to the Actions framework have moved to a new subdirectory of the sql directory, and include new hierarchies for per-domain, per-subject and per-label grouping. You can run these files to add those mailboxes.

The per-person hierarchy now shows To: and Cc: messages as well as From: messages. If you want to add this, do NOT run actions/people.sql unless you want it to delete your existing per-person mailbox definitions. To get the new functionality while retaining your existing mailboxes, replace the view people_mailboxes with the new version using copy-and-paste from people.sql. You'll then need to re-run mailboxes.sql.

5. Version 0.7

No SQL has changed in this version, but you need to do the following as the PostgreSQL superuser:

sql# CREATE CAST (bytea TO text) WITHOUT FUNCTION;

You'll also need to add a new configuration setting to specify where the message backup files should go. (This was previously hardcoded.)

decimail> INSERT INTO CONFIGURATION VALUES('message_backup_dir','pathname','/var/local/decimail/messages/');

It's important that the value that you give ends with a /.

6. Version 0.8

Not much SQL has changed in this version. If you were using the per-domain mailboxes and would like to make them more efficient, look at the new index messages_by_from_addr_domain in indexes.sql and the changes in sql/actions/domains.sql.

7. Version 0.9

No SQL has changed in this version, so you don't need to touch your database at all. Just recompile the IMAP daemon.

8. Version 0.10

The change in this version is the addition of text searching on message subjects using PostgreSQL's tsearch2 contrib module.

To add this functionality:

Make sure that you have tsearch2. On Debian you should just be able to "apt-get install postgresql-contrib-8.1" or similar.
Load tsearch2 into your decimail database. See the installation page for the description. It's simple, but there is a permissions issue.
Use psql to add the new subject index stuff to the database:

alter table messages add column subject_tsidx tsvector; update messages set subject_tsidx = to_tsvector('default',subject); vacuum full analyze; create index messages_by_subject_tsidx on messages using gist(subject_tsidx); vacuum full analyze;

Some of those steps may take a while.

To index the subjects of new messages, add this trigger:

create trigger update_subject_tsidx before update or insert on messages for each row execute procedure tsearch2(subject_tsidx, subject);

If you have existing Subject/ mailboxes, note that they will now be reinterpretted as tsearch queries rather than exact matches.
Use psql and create_or_replace_view to replace your existing definition of subject_mailboxes with the new one from sql/actions/subjects.sql, which uses tsearch2.
From your client, create a new mailbox under Subjects/. Until you do this the queries won't have been updated.
You should now be able to create, for example, Subjects/party and see all messages with the word "party" in the subject, or "Subjects/Beer|Pub" to see all messages containing either of those words. You can also use & for logical AND of terms, though these expression characters may cause issues for some IMAP clients.

9. Version 0.11

If you want to use the new actions framework code for mailing lists and/or spam mailboxes, first remove the old mailing list tables stuff and then run actions/spam.sql and actions/lists.sql. Then re-run mailboxes.sql to build a new mailboxes view that includes these new mailboxes.

You need to run realnames.sql and create_random_addrs.sql to create new tables for logging randomly-allocated email addresses and real names for completions. These shouldn't have any interactions with old database contents.

Then rebuild libpbe and the daemons.

10. Version 0.12

In this version the database has been extended to include the tsearch2 index table used for the Keywords/ actions framework extension. To add this, run part_tsearch.sql to add the table. If you want you can just index new messages - this will happen automatically once you have compiled and installed the new smtpd. If you want to index your existing messages, compile the utility in the partindex directory. When you run this it will index up to 10 existing messages. This rather small limit is because the query to find un-indexed messages is inexplicably slow; I'll fix it when I know how. You can try increasing it by changing the value passed from main() to the do_index() function.

To add the Keywords/ mailbox hierarchy, run actions/keywords.sql and then re-run mailboxes.sql.

11. Version 0.13

In this version, the mailbox queries have been changed so that they don't need to explicitly check for correct ownership of the messages that they select (e.g. using $U in the actions framework); instead they look for the messages in a set of views whose names begin "u_" that narrow the main database tables to the current users' messages.

You need to change your database to reflect this change. I suggest something like this:

A new username column has been added to the actions table, so actions are now per-user rather than global. You should add this:

alter table actions add column username text; update actions set username='your-username-here'; alter table actions alter column username set not null;

If there are any actions framework files where you don't mind loosing any existing state, re-run the corresponding sql file in the actions/ directory.
For actions framework files where you want to retain the state (e.g. lists of correspondents, mailing lists etc.) you should manually "create or replace view" based on the new definitions from the files, i.e.:

create or replace view subject_mailboxes as... create or replace view people_mailboxes as... create or replace view keyword_mailboxes as... create or replace view domain_mailboxes as... create or replace view list_mailboxes as...

Run mailboxes.sql.

12. Version 0.14

In this version the separate to_addrs, cc_addrs and bcc_addrs tables have been replaced by a single table, recipients. You should create this table and populate it from your existing tables, and create its indexes, as follows:

create table recipients ( recip_type text not null, addr text, msg_id integer not null references messages on delete cascade on update cascade ); insert into recipients select 'to' as recip_type, * from to_addrs; insert into recipients select 'cc' as recip_type, * from cc_addrs; insert into recipients select 'bcc' as recip_type, * from bcc_addrs; create index recipients_by_msg_id on recipients ( msg_id ); create index recipients_by_addr on recipients ( addr );

You then need to update the actions framework wherever it uses to_addrs, cc_addrs and bcc_addrs. This means you need to redefine the views lists_mailboxes and people_mailboxes by copying their new definitions from the sql files (use "create or replace view"). You'll also need to update the entries in the actions table for the Lists and Spam mailbox hierarchies using the new definitions.

Then compile and install the new daemons.

When you're happy that it is working you can delete your old to_addrs, cc_addrs and bcc_addrs tables.

13. Version 0.14

No SQL has changed in this version, so you don't need to touch your database at all. Just recompile the daemons.