Re: [Dbmail] debian update

[Paul J Stevens]

I've tried very hard to avoid this kind of hassle. Ilja, please try the 
modified migrate.psql I'm attaching.



Feargal Reilly wrote:
Using the messages table from dbmail-1.2.3 as an example:
dbmail=# \d messages
<snip>
Foreign Key constraints: $1 FOREIGN KEY (mailbox_idnr) REFERENCES 
mailboxes(mailbox_idnr) ON UPDATE NO ACTION ON DELETE NO ACTION

To drop that constraint:
dbmail=# ALTER TABLE messages DROP CONSTRAINT "$1";

if you want to be able to turn it on or off easily, here's a plpgsql function 
to do it:

CREATE OR REPLACE FUNCTION toggle_messages_constraint(INTEGER)
RETURNS VARCHAR AS '
DECLARE cmd VARCHAR;
BEGIN
        IF $1 = 0
THEN
        RAISE NOTICE ''Dropping constraint on messages'';
        EXECUTE ''ALTER TABLE messages DROP CONSTRAINT "$1"'';
        RETURN ''OFF'';
ELSE
        RAISE NOTICE ''Adding constraint to messages'';
        ALTER TABLE messages ADD FOREIGN KEY(mailbox_idnr) REFERENCES 
mailboxes(mailbox_idnr);
        RETURN ''ON'';
END IF;
RETURN ''NO CHANGE'';
END;
'
LANGUAGE plpgsql;


You may first have to do the following to enable plpgsql:
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS       
'/usr/local/lib/postgresql/plpgsql.so' LANGUAGE C;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;

Obviously, replace the path to your plpgsql.so file.

Now you can drop/add the constraint easily with:

dbmail=# select toggle_messages_constraint(0);
NOTICE:  Dropping constraint on messages
 toggle_messages_constraint 
----------------------------
 OFF
(1 row)

dbmail=# select toggle_messages_constraint(1);
NOTICE:  Adding constraint to messages
 toggle_messages_constraint 
----------------------------
 ON
(1 row)

That good enough for you?

-Feargal.

On Tue, 03 Feb 2004 17:33:27 +0100
Ilja Booij <[EMAIL PROTECTED]> wrote:


Hi,

I was trying the PostgreSQL script, but it failed on some foreign key
checks. I'd like to switch off foreign keys temporarily to enable the
tables to be filled.

Does anybody know the postgres commands for this? (if there are any)

Ilja

Paul J Stevens wrote:


I've just finished uploading the updated experimental packages for 
dbmail-2.0

They are CVS snapshots, not rc1

They contain migration scripts for mysql and postgresql. Pure SQL. More 
tests are required for these. I'll add them here as well.

There are still some rough edges to these packages: lmtpd documentation 
is missing, installation or migration of the database is not even 
attempted, ldap functionality has to be tested and documented.

Just add:

deb http://debian.nfgd.net/debian experimental/

to your sources list.



------------------------------------------------------------------------

# SQL for upgrading from dbmail-1.2 to dbmail-2.0

SET FOREIGN_KEY_CHECKS=0;
SET SQL_LOG_OFF=1;
SET SQL_LOG_UPDATE=0;

# required tables:
RENAME table aliases to aliases_1, users to users_1, mailboxes to mailboxes_1, 
messages to messages_1, messageblks to messageblks_1;

# optional tables:
RENAME table auto_notifications to auto_notifications_1;
RENAME table auto_replies to auto_replies_1;


DROP TABLE IF EXISTS aliases;
CREATE TABLE aliases (
        alias_idnr bigint(21) NOT NULL auto_increment,
        alias varchar(100) NOT NULL default '',
        deliver_to varchar(250) NOT NULL default '',
        client_idnr bigint(21) NOT NULL default '0',
        PRIMARY KEY  (alias_idnr),
        INDEX (alias),
        INDEX (client_idnr)     
) TYPE=InnoDB;

DROP TABLE IF EXISTS users;
CREATE TABLE users (
        user_idnr bigint(21) NOT NULL auto_increment,
        userid varchar(100) NOT NULL default '',
        passwd varchar(34) NOT NULL default '',
        client_idnr bigint(21) NOT NULL default '0',
        maxmail_size bigint(21) NOT NULL default '0',
        curmail_size bigint(21) NOT NULL default '0',
        encryption_type varchar(20) NOT NULL default '',
        last_login datetime NOT NULL default '1979-11-03 22:05:58',
        PRIMARY KEY  (user_idnr),
        UNIQUE INDEX (userid)
) TYPE=InnoDB;

DROP TABLE IF EXISTS mailboxes;
CREATE TABLE mailboxes (
        mailbox_idnr bigint(21) NOT NULL auto_increment,
        owner_idnr bigint(21) NOT NULL default '0',
        name varchar(100) NOT NULL default '',
        seen_flag tinyint(1) NOT NULL default '0',
        answered_flag tinyint(1) NOT NULL default '0',
        deleted_flag tinyint(1) NOT NULL default '0',
        flagged_flag tinyint(1) NOT NULL default '0',
        recent_flag tinyint(1) NOT NULL default '0',
        draft_flag tinyint(1) NOT NULL default '0',
        no_inferiors tinyint(1) NOT NULL default '0',
        no_select tinyint(1) NOT NULL default '0',
        permission tinyint(1) default '2',
        PRIMARY KEY  (mailbox_idnr),
        INDEX (name),
        INDEX (owner_idnr),
	FOREIGN KEY (`owner_idnr`) 
		REFERENCES `users` (`user_idnr`) ON DELETE CASCADE
) TYPE=InnoDB;

DROP TABLE IF EXISTS subscription;
CREATE TABLE subscription (
        user_id bigint(21) not null default '0',
        mailbox_id bigint(21) not null,
        primary key (user_id, mailbox_id),
        index (user_id),
        index (mailbox_id),
	FOREIGN KEY (`user_id`) 
		REFERENCES `users` (`user_idnr`) ON DELETE CASCADE,
	FOREIGN KEY (`mailbox_id`) 
		REFERENCES `mailboxes` (`mailbox_idnr`) ON DELETE CASCADE,
        
) TYPE=InnoDB;


DROP TABLE IF EXISTS acl;
CREATE TABLE acl (
        user_id bigint(21) NOT NULL,
        mailbox_id bigint(21) NOT NULL,
        lookup_flag tinyint(1) default '0' not null,
        read_flag tinyint(1) default '0' not null,
        seen_flag tinyint(1) default '0' not null,
        write_flag tinyint(1) default '0' not null,
        insert_flag tinyint(1) default '0' not null,    
        post_flag tinyint(1) default '0' not null,
        create_flag tinyint(1) default '0' not null,    
        delete_flag tinyint(1) default '0' not null,    
        administer_flag tinyint(1) default '0' not null,        
        PRIMARY KEY(user_id, mailbox_id),
        index (user_id),
        index (mailbox_id),
	FOREIGN KEY (`user_id`) 
		REFERENCES `users` (`user_idnr`) ON DELETE CASCADE,	
	FOREIGN KEY (`mailbox_id`) 
		REFERENCES `mailboxes` (`mailbox_idnr`) ON DELETE CASCADE
) TYPE=InnoDB;

        
DROP TABLE IF EXISTS physmessage;
CREATE TABLE physmessage (
        id bigint(21) NOT NULL auto_increment,
        messagesize bigint(21) NOT NULL default '0',
        rfcsize bigint(21) NOT NULL default '0',
        internal_date datetime NOT NULL default '0000-00-00 00:00:00',
        PRIMARY KEY (id)
) TYPE=InnoDB;

DROP TABLE IF EXISTS messages;
CREATE TABLE messages (
        message_idnr bigint(21) NOT NULL auto_increment,
        mailbox_idnr bigint(21) NOT NULL default '0',
        physmessage_id bigint(21) NOT NULL default '0',
        seen_flag tinyint(1) NOT NULL default '0',
        answered_flag tinyint(1) NOT NULL default '0',
        deleted_flag tinyint(1) NOT NULL default '0',
        flagged_flag tinyint(1) NOT NULL default '0',
        recent_flag tinyint(1) NOT NULL default '0',
        draft_flag tinyint(1) NOT NULL default '0',
        unique_id varchar(70) NOT NULL default '',
        status tinyint(3) unsigned zerofill NOT NULL default '000',
        PRIMARY KEY  (message_idnr),
        INDEX physmessage_id (physmessage_id),          
        INDEX mailbox_idnr (mailbox_idnr),
        INDEX seen_flag (seen_flag),
        INDEX unique_id (unique_id),
        INDEX status (status),
	FOREIGN KEY (`physmessage_id`) 
		REFERENCES `physmessage` (`id`) ON DELETE CASCADE,
	FOREIGN KEY (`mailbox_idnr`) 
		REFERENCES `mailboxes` (`mailbox_idnr`) ON DELETE CASCADE
) TYPE=InnoDB;

DROP TABLE IF EXISTS messageblks;
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',
        PRIMARY KEY  (messageblk_idnr),
        INDEX physmsg_index (physmessage_id),
	FOREIGN KEY (`physmessage_id`) 
		REFERENCES `physmessage` (`id`) ON DELETE CASCADE
) TYPE=InnoDB;

DROP TABLE IF EXISTS auto_notifications;
CREATE TABLE auto_notifications (
        auto_notify_idnr bigint(21) NOT NULL AUTO_INCREMENT,
        user_idnr bigint(21) NOT NULL,
        notify_address VARCHAR(100) NOT NULL,
        PRIMARY KEY (auto_notify_idnr),
        INDEX (user_idnr),
	FOREIGN KEY (`user_idnr`) 
		REFERENCES `users`(`user_idnr`) ON DELETE CASCADE
) TYPE=InnoDB;

DROP TABLE IF EXISTS auto_replies;
CREATE TABLE auto_replies (
        auto_reply_idnr bigint(21) DEFAULT '0' NOT NULL auto_increment,
        user_idnr bigint(21) DEFAULT '0' NOT NULL,
        reply_body mediumtext,
        PRIMARY KEY (auto_reply_idnr),
        INDEX (user_idnr),
	FOREIGN KEY (`user_idnr`) 
		REFERENCES `users`(`user_idnr`) ON DELETE CASCADE
) TYPE=InnoDB;

SET FOREIGN_KEY_CHECKS=0;
INSERT INTO aliases SELECT * from aliases_1;

INSERT INTO subscription ( user_id, mailbox_id ) SELECT owner_idnr, mailbox_idnr 
FROM mailboxes_1 where is_subscribed > 0;

INSERT INTO mailboxes ( mailbox_idnr, owner_idnr, name, seen_flag, 
answered_flag, deleted_flag, flagged_flag, recent_flag, draft_flag, 
no_inferiors, no_select, permission )
SELECT mailbox_idnr, owner_idnr, name, seen_flag, answered_flag, deleted_flag, 
flagged_flag, recent_flag, draft_flag, no_inferiors, no_select, permission FROM 
mailboxes_1;

INSERT INTO messages ( message_idnr, mailbox_idnr, seen_flag, answered_flag, 
deleted_flag, flagged_flag, recent_flag, draft_flag, unique_id )
SELECT message_idnr, mailbox_idnr, seen_flag, answered_flag, deleted_flag, 
flagged_flag, recent_flag, draft_flag, unique_id FROM messages_1;

INSERT INTO physmessage ( id, messagesize, rfcsize, internal_date) 
SELECT message_idnr, messagesize, rfcsize, internal_date FROM messages_1; 

UPDATE messages SET physmessage_id = message_idnr;

INSERT INTO messageblks ( messageblk_idnr, physmessage_id, messageblk, 
blocksize )
SELECT messageblk_idnr, message_idnr, messageblk, blocksize FROM messageblks_1;


INSERT INTO users ( user_idnr, userid, passwd, client_idnr, maxmail_size, 
encryption_type, last_login, curmail_size )
SELECT u.*, sum(p.messagesize) AS curmail_size
FROM users_1 u LEFT JOIN mailboxes b ON b.owner_idnr = u.user_idnr LEFT JOIN 
messages m ON m.mailbox_idnr = b.mailbox_idnr LEFT JOIN physmessage p ON 
m.physmessage_id = p.id
GROUP BY u.user_idnr;

INSERT INTO auto_replies SELECT * FROM auto_replies_1;
INSERT INTO auto_notifications SELECT * FROM auto_notifications_1;

DROP TABLE auto_replies_1, auto_notifications_1, aliases_1, users_1, 
mailboxes_1, messages_1, messageblks_1;

SET FOREIGN_KEY_CHECKS=1;


------------------------------------------------------------------------

-- SQL for upgrading from dbmail-1.2 to dbmail-2.0

-- required tables:
CREATE TABLE aliases_1 AS SELECT * FROM aliases;
CREATE TABLE users_1 AS SELECT * FROM users;
CREATE TABLE mailboxes_1 AS SELECT * FROM mailboxes;
CREATE TABLE messages_1 AS SELECT * FROM messages;
CREATE TABLE messageblks_1 AS SELECT * FROM messageblks;

DROP TABLE aliases;
DROP TABLE messageblks;
DROP TABLE messages;
DROP TABLE mailboxes;
DROP TABLE users;
-- create dbmail-2 tables

CREATE TABLE aliases (
   alias_idnr INT8 DEFAULT nextval('alias_idnr_seq'),
   alias VARCHAR(100) NOT NULL, 
   deliver_to VARCHAR(250) NOT NULL,
   client_idnr INT8 DEFAULT '0' NOT NULL,
   PRIMARY KEY (alias_idnr)
);

CREATE UNIQUE INDEX aliases_alias_idx ON aliases(alias);
CREATE UNIQUE INDEX aliases_alias_low_idx ON aliases(lower(alias));

CREATE TABLE users (
  user_idnr INT8 DEFAULT nextval('user_idnr_seq'),
  userid VARCHAR(100) NOT NULL,
  passwd VARCHAR(34) NOT NULL,
  client_idnr INT8 DEFAULT '0' NOT NULL,
  maxmail_size INT8 DEFAULT '0' NOT NULL,
  curmail_size INT8 DEFAULT '0' NOT NULL,
  encryption_type VARCHAR(20) DEFAULT '' NOT NULL,
  last_login TIMESTAMP DEFAULT '1979-11-03 22:05:58' NOT NULL,
  PRIMARY KEY (user_idnr)
);
CREATE INDEX users_name_idx ON users(userid);

CREATE TABLE mailboxes (
  mailbox_idnr INT8 DEFAULT nextval('mailbox_idnr_seq'),
  owner_idnr INT8 NOT NULL,
  name VARCHAR(100) NOT NULL,
  seen_flag INT2 DEFAULT '0' NOT NULL,
  answered_flag INT2 DEFAULT '0' NOT NULL,
  deleted_flag INT2 DEFAULT '0' NOT NULL,
  flagged_flag INT2 DEFAULT '0' NOT NULL,
  recent_flag INT2 DEFAULT '0' NOT NULL,
  draft_flag INT2 DEFAULT '0' NOT NULL,
  no_inferiors INT2 DEFAULT '0' NOT NULL,
  no_select INT2 DEFAULT '0' NOT NULL,
  permission INT2 DEFAULT '2',
  PRIMARY KEY (mailbox_idnr),
  FOREIGN KEY (owner_idnr) REFERENCES users(user_idnr) ON DELETE CASCADE
);
CREATE INDEX mailboxes_owner_idx ON mailboxes(owner_idnr);
CREATE INDEX mailboxes_name_idx ON mailboxes(name);

CREATE TABLE subscription (
        user_id INT8 NOT NULL,
        mailbox_id INT8 NOT NULL,
        PRIMARY KEY (user_id, mailbox_id),
        FOREIGN KEY (user_id) REFERENCES users(user_idnr) ON DELETE CASCADE,
	FOREIGN KEY (mailbox_id) 
		REFERENCES mailboxes(mailbox_idnr) ON DELETE CASCADE
);

CREATE TABLE acl (
   user_id INT8 NOT NULL,
   mailbox_id INT8 NOT NULL,
   lookup_flag INT2 DEFAULT '0' NOT NULL,
   read_flag INT2 DEFAULT '0' NOT NULL,
   seen_flag INT2 DEFAULT '0' NOT NULL,
   write_flag INT2 DEFAULT '0' NOT NULL,
   insert_flag INT2 DEFAULT '0' NOT NULL,
   post_flag INT2 DEFAULT '0' NOT NULL,
   create_flag INT2 DEFAULT '0' NOT NULL,
   delete_flag INT2 DEFAULT '0' NOT NULL,
   administer_flag INT2 DEFAULT '0' NOT NULL,
   PRIMARY KEY (user_id, mailbox_id),
   FOREIGN KEY (user_id) REFERENCES users(user_idnr) ON DELETE CASCADE,
   FOREIGN KEY (mailbox_id) REFERENCES mailboxes(mailbox_idnr) ON DELETE CASCADE
);

CREATE SEQUENCE physmessage_id_seq;
CREATE TABLE physmessage (
  id INT8 DEFAULT nextval('physmessage_id_seq'),
  messagesize INT8 DEFAULT '0' NOT NULL,   
  rfcsize INT8 DEFAULT '0' NOT NULL,
  internal_date TIMESTAMP,
  PRIMARY KEY(id)
);

CREATE TABLE messages (
  message_idnr INT8 DEFAULT nextval('message_idnr_seq'),
  mailbox_idnr INT8 DEFAULT '0' NOT NULL,
  physmessage_id INT8 DEFAULT '0' NOT NULL,
  seen_flag INT2 DEFAULT '0' NOT NULL,
  answered_flag INT2 DEFAULT '0' NOT NULL,
  deleted_flag INT2 DEFAULT '0' NOT NULL,
  flagged_flag INT2 DEFAULT '0' NOT NULL,
  recent_flag INT2 DEFAULT '0' NOT NULL,
  draft_flag INT2 DEFAULT '0' NOT NULL,
  unique_id varchar(70) NOT NULL,
  status INT2 DEFAULT '000' NOT NULL,
  PRIMARY KEY (message_idnr),
  FOREIGN KEY (physmessage_id) REFERENCES physmessage(id) ON DELETE CASCADE,
  FOREIGN KEY (mailbox_idnr) REFERENCES mailboxes(mailbox_idnr) ON DELETE 
CASCADE
);
CREATE INDEX messages_mailbox_idx ON messages(mailbox_idnr);
CREATE INDEX messages_physmessage_idx ON messages(physmessage_id);
CREATE INDEX messages_seen_flag_idx ON messages(seen_flag);
CREATE INDEX messages_unique_id_idx ON messages(unique_id);
CREATE INDEX messages_status_idx ON messages(status);

CREATE TABLE messageblks (
  messageblk_idnr INT8 DEFAULT nextval('messageblk_idnr_seq'),
  physmessage_id INT8 DEFAULT '0' NOT NULL,
  messageblk TEXT NOT NULL,
  blocksize INT8 DEFAULT '0' NOT NULL,
  PRIMARY KEY (messageblk_idnr),
  FOREIGN KEY (physmessage_id) REFERENCES physmessage (id) ON DELETE CASCADE
);
CREATE INDEX messageblks_physmessage_idx ON messageblks(physmessage_id);


-- fillerup

INSERT INTO aliases SELECT * from aliases_1;

INSERT INTO subscription ( user_id, mailbox_id ) SELECT owner_idnr, mailbox_idnr 
FROM mailboxes_1 where is_subscribed > 0;

INSERT INTO mailboxes ( mailbox_idnr, owner_idnr, name, seen_flag, 
answered_flag, deleted_flag, flagged_flag, recent_flag, draft_flag, 
no_inferiors, no_select, permission )
SELECT mailbox_idnr, owner_idnr, name, seen_flag, answered_flag, deleted_flag, 
flagged_flag, recent_flag, draft_flag, no_inferiors, no_select, permission FROM 
mailboxes_1;

INSERT INTO messages ( message_idnr, mailbox_idnr, seen_flag, answered_flag, 
deleted_flag, flagged_flag, recent_flag, draft_flag, unique_id )
SELECT message_idnr, mailbox_idnr, seen_flag, answered_flag, deleted_flag, 
flagged_flag, recent_flag, draft_flag, unique_id FROM messages_1;

INSERT INTO physmessage ( id, messagesize, rfcsize, internal_date) 
SELECT message_idnr, messagesize, rfcsize, internal_date FROM messages_1; 

UPDATE messages SET physmessage_id = message_idnr;

INSERT INTO messageblks ( messageblk_idnr, physmessage_id, messageblk, 
blocksize )
SELECT messageblk_idnr, message_idnr, messageblk, blocksize FROM messageblks_1;


INSERT INTO users ( user_idnr, userid, passwd, client_idnr, maxmail_size, 
encryption_type, last_login, curmail_size )
SELECT u.*, sum(p.messagesize) AS curmail_size 
FROM users_1 u 
LEFT JOIN mailboxes b ON b.owner_idnr = u.user_idnr 
LEFT JOIN messages m ON m.mailbox_idnr = b.mailbox_idnr 
LEFT JOIN physmessage p ON m.physmessage_id = p.id 
GROUP BY u.user_idnr, u.userid, u.passwd, u.client_idnr, u.maxmail_size, u.encryption_type,u.last_login;

DROP TABLE aliases_1, users_1, mailboxes_1, messages_1, messageblks_1;


--
IC&S
Stadhouderslaan 57
3583 JD Utrecht

PGP-key:
http://www.ic-s.nl/keys/ilja.txt

_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail


--
  ________________________________________________________________
  Paul Stevens                                         [EMAIL PROTECTED]
  NET FACILITIES GROUP                     GPG/PGP: 1024D/11F8CD31
  The Netherlands_______________________________________www.nfg.nl
-- SQL for upgrading from dbmail-1.2 to dbmail-2.0

-- required tables:
CREATE TABLE aliases_1 AS SELECT * FROM aliases;
CREATE TABLE users_1 AS SELECT * FROM users;
CREATE TABLE mailboxes_1 AS SELECT * FROM mailboxes;
CREATE TABLE messages_1 AS SELECT * FROM messages;
CREATE TABLE messageblks_1 AS SELECT * FROM messageblks;

DROP TABLE aliases;
DROP TABLE messageblks;
DROP TABLE messages;
DROP TABLE mailboxes;
DROP TABLE users;
-- create dbmail-2 tables

CREATE TABLE aliases (
    alias_idnr INT8 DEFAULT nextval('alias_idnr_seq'),
    alias VARCHAR(100) NOT NULL, 
    deliver_to VARCHAR(250) NOT NULL,
    client_idnr INT8 DEFAULT '0' NOT NULL,
    PRIMARY KEY (alias_idnr)
);

CREATE UNIQUE INDEX aliases_alias_idx ON aliases(alias);
CREATE UNIQUE INDEX aliases_alias_low_idx ON aliases(lower(alias));

CREATE TABLE users (
   user_idnr INT8 DEFAULT nextval('user_idnr_seq'),
   userid VARCHAR(100) NOT NULL,
   passwd VARCHAR(34) NOT NULL,
   client_idnr INT8 DEFAULT '0' NOT NULL,
   maxmail_size INT8 DEFAULT '0' NOT NULL,
   curmail_size INT8 DEFAULT '0' NOT NULL,
   encryption_type VARCHAR(20) DEFAULT '' NOT NULL,
   last_login TIMESTAMP DEFAULT '1979-11-03 22:05:58' NOT NULL,
   PRIMARY KEY (user_idnr)
);
CREATE INDEX users_name_idx ON users(userid);

CREATE TABLE mailboxes (
   mailbox_idnr INT8 DEFAULT nextval('mailbox_idnr_seq'),
   owner_idnr INT8 NOT NULL,
   name VARCHAR(100) NOT NULL,
   seen_flag INT2 DEFAULT '0' NOT NULL,
   answered_flag INT2 DEFAULT '0' NOT NULL,
   deleted_flag INT2 DEFAULT '0' NOT NULL,
   flagged_flag INT2 DEFAULT '0' NOT NULL,
   recent_flag INT2 DEFAULT '0' NOT NULL,
   draft_flag INT2 DEFAULT '0' NOT NULL,
   no_inferiors INT2 DEFAULT '0' NOT NULL,
   no_select INT2 DEFAULT '0' NOT NULL,
   permission INT2 DEFAULT '2',
   PRIMARY KEY (mailbox_idnr)
);
CREATE INDEX mailboxes_owner_idx ON mailboxes(owner_idnr);
CREATE INDEX mailboxes_name_idx ON mailboxes(name);

CREATE TABLE subscription (
        user_id INT8 NOT NULL,
        mailbox_id INT8 NOT NULL,
        PRIMARY KEY (user_id, mailbox_id)
);

CREATE TABLE acl (
    user_id INT8 NOT NULL,
    mailbox_id INT8 NOT NULL,
    lookup_flag INT2 DEFAULT '0' NOT NULL,
    read_flag INT2 DEFAULT '0' NOT NULL,
    seen_flag INT2 DEFAULT '0' NOT NULL,
    write_flag INT2 DEFAULT '0' NOT NULL,
    insert_flag INT2 DEFAULT '0' NOT NULL,
    post_flag INT2 DEFAULT '0' NOT NULL,
    create_flag INT2 DEFAULT '0' NOT NULL,
    delete_flag INT2 DEFAULT '0' NOT NULL,
    administer_flag INT2 DEFAULT '0' NOT NULL,
    PRIMARY KEY (user_id, mailbox_id)
);

CREATE SEQUENCE physmessage_id_seq;
CREATE TABLE physmessage (
   id INT8 DEFAULT nextval('physmessage_id_seq'),
   messagesize INT8 DEFAULT '0' NOT NULL,   
   rfcsize INT8 DEFAULT '0' NOT NULL,
   internal_date TIMESTAMP,
   PRIMARY KEY(id)
);

CREATE TABLE messages (
   message_idnr INT8 DEFAULT nextval('message_idnr_seq'),
   mailbox_idnr INT8 DEFAULT '0' NOT NULL,
   physmessage_id INT8 DEFAULT '0' NOT NULL,
   seen_flag INT2 DEFAULT '0' NOT NULL,
   answered_flag INT2 DEFAULT '0' NOT NULL,
   deleted_flag INT2 DEFAULT '0' NOT NULL,
   flagged_flag INT2 DEFAULT '0' NOT NULL,
   recent_flag INT2 DEFAULT '0' NOT NULL,
   draft_flag INT2 DEFAULT '0' NOT NULL,
   unique_id varchar(70) NOT NULL,
   status INT2 DEFAULT '000' NOT NULL,
   PRIMARY KEY (message_idnr)
);

CREATE INDEX messages_mailbox_idx ON messages(mailbox_idnr);
CREATE INDEX messages_physmessage_idx ON messages(physmessage_id);
CREATE INDEX messages_seen_flag_idx ON messages(seen_flag);
CREATE INDEX messages_unique_id_idx ON messages(unique_id);
CREATE INDEX messages_status_idx ON messages(status);

CREATE TABLE messageblks (
   messageblk_idnr INT8 DEFAULT nextval('messageblk_idnr_seq'),
   physmessage_id INT8 DEFAULT '0' NOT NULL,
   messageblk TEXT NOT NULL,
   blocksize INT8 DEFAULT '0' NOT NULL,
   PRIMARY KEY (messageblk_idnr)
);
CREATE INDEX messageblks_physmessage_idx ON messageblks(physmessage_id);


-- fillerup

INSERT INTO aliases SELECT * from aliases_1;

INSERT INTO subscription ( user_id, mailbox_id ) SELECT owner_idnr, 
mailbox_idnr FROM mailboxes_1 where is_subscribed > 0;

INSERT INTO mailboxes ( mailbox_idnr, owner_idnr, name, seen_flag, 
answered_flag, deleted_flag, flagged_flag, recent_flag, draft_flag, 
no_inferiors, no_select, permission )
SELECT mailbox_idnr, owner_idnr, name, seen_flag, answered_flag, deleted_flag, 
flagged_flag, recent_flag, draft_flag, no_inferiors, no_select, permission FROM 
mailboxes_1;

INSERT INTO messages ( message_idnr, mailbox_idnr, seen_flag, answered_flag, 
deleted_flag, flagged_flag, recent_flag, draft_flag, unique_id )
SELECT message_idnr, mailbox_idnr, seen_flag, answered_flag, deleted_flag, 
flagged_flag, recent_flag, draft_flag, unique_id FROM messages_1;

INSERT INTO physmessage ( id, messagesize, rfcsize, internal_date) 
SELECT message_idnr, messagesize, rfcsize, internal_date FROM messages_1; 

UPDATE messages SET physmessage_id = message_idnr;

INSERT INTO messageblks ( messageblk_idnr, physmessage_id, messageblk, 
blocksize )
SELECT messageblk_idnr, message_idnr, messageblk, blocksize FROM messageblks_1;


INSERT INTO users ( user_idnr, userid, passwd, client_idnr, maxmail_size, 
encryption_type, last_login, curmail_size )
SELECT u.*, sum(p.messagesize) AS curmail_size 
FROM users_1 u 
LEFT JOIN mailboxes b ON b.owner_idnr = u.user_idnr 
LEFT JOIN messages m ON m.mailbox_idnr = b.mailbox_idnr 
LEFT JOIN physmessage p ON m.physmessage_id = p.id 
GROUP BY u.user_idnr, u.userid, u.passwd, u.client_idnr, u.maxmail_size, 
u.encryption_type,u.last_login;

DROP TABLE aliases_1, users_1, mailboxes_1, messages_1, messageblks_1;

ALTER TABLE mailboxes ADD   FOREIGN KEY (owner_idnr) REFERENCES 
users(user_idnr) ON DELETE CASCADE;
ALTER TABLE subscription ADD    FOREIGN KEY (user_id) REFERENCES 
users(user_idnr) ON DELETE CASCADE;
ALTER TABLE subscription ADD    FOREIGN KEY (mailbox_id) REFERENCES 
mailboxes(mailbox_idnr) ON DELETE CASCADE;
ALTER TABLE acl ADD FOREIGN KEY (user_id) REFERENCES users(user_idnr) ON DELETE 
CASCADE;
ALTER TABLE acl ADD FOREIGN KEY (mailbox_id) REFERENCES mailboxes(mailbox_idnr) 
ON DELETE CASCADE;
ALTER TABLE messages ADD FOREIGN KEY (physmessage_id) REFERENCES 
physmessage(id) ON DELETE CASCADE;
ALTER TABLE messages ADD FOREIGN KEY (mailbox_idnr) REFERENCES 
mailboxes(mailbox_idnr) ON DELETE CASCADE;
ALTER TABLE messageblks ADD FOREIGN KEY (physmessage_id) REFERENCES physmessage 
(id) ON DELETE CASCADE;