Jesse,
You're right. I have no idea where that one came from. So I've rebuild
the tables file.
As it is, indexes probably could use some further improvements.
Christopher had some ideas about that I think. I'll look into this
myself sometime later this week.
Also, there appears to be an issue with innodb tables that I hadn't
encountered before: the order in which tables are created is paramount
when they contain foreign key references. However, mysqldump doesn't
dump them in the correct order. This makes it impossible to simply
reload the data from a 'mysqldump --opt dbmail' dump. So beware. This is
probably a bug in mysqldump, and it could be specific to my installation
(4.0.12 debian/sid).
Jesse Norell wrote:
Hello,
CREATE TABLE users (
user_idnr bigint(21) NOT NULL auto_increment,
userid varchar(100) NOT NULL default '',
passwd varchar(32) NOT NULL default '',
client_idnr bigint(21) NOT NULL default '0',
maxmail_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 KEY userid (userid),
UNIQUE KEY useridnr_2 (user_idnr,userid)
) TYPE=InnoDB;
Just a heads up, you need passwd to be varchar(34) if you're
going to use md5 hash passwords.
--
Jesse Norell
jesse (at) kci.net
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
--
________________________________________________________________
Paul Stevens mailto:[EMAIL PROTECTED]
NET FACILITIES GROUP PGP: finger [EMAIL PROTECTED]
The Netherlands________________________________http://www.nfg.nl
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',
encryption_type varchar(20) NOT NULL default '',
last_login datetime NOT NULL default '1979-11-03 22:05:58',
PRIMARY KEY (user_idnr),
UNIQUE KEY (userid),
UNIQUE KEY useridnr_2 (user_idnr,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',
is_subscribed tinyint(1) NOT NULL default '0',
PRIMARY KEY (mailbox_idnr),
KEY name (name),
KEY owner_idnr (owner_idnr),
KEY is_subscribed (is_subscribed),
FOREIGN KEY (`owner_idnr`) REFERENCES `users` (`user_idnr`) ON DELETE CASCADE
) 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',
messagesize 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 '',
internal_date datetime NOT NULL default '0000-00-00 00:00:00',
status tinyint(3) unsigned zerofill NOT NULL default '000',
rfcsize bigint(21) NOT NULL default '0',
PRIMARY KEY (message_idnr),
KEY mailbox_idnr (mailbox_idnr),
KEY seen_flag (seen_flag),
KEY unique_id (unique_id),
KEY status (status),
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,
message_idnr bigint(21) NOT NULL default '0',
messageblk longtext NOT NULL,
blocksize bigint(21) NOT NULL default '0',
PRIMARY KEY (messageblk_idnr),
KEY msg_index (message_idnr),
FOREIGN KEY (`message_idnr`) REFERENCES `messages` (`message_idnr`) ON DELETE
CASCADE
) TYPE=InnoDB;
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),
KEY alias (alias)
) TYPE=InnoDB;
DROP TABLE IF EXISTS tmpmessage;
CREATE TABLE tmpmessage (
message_idnr bigint(21) NOT NULL auto_increment,
mailbox_idnr int(21) NOT NULL default '0',
messagesize 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 '',
internal_date datetime NOT NULL default '0000-00-00 00:00:00',
status tinyint(3) unsigned zerofill NOT NULL default '000',
PRIMARY KEY (message_idnr)
) TYPE=InnoDB;
DROP TABLE IF EXISTS tmpmessageblk;
CREATE TABLE tmpmessageblk (
messageblk_idnr bigint(21) NOT NULL auto_increment,
message_idnr bigint(21) NOT NULL default '0',
messageblk longtext NOT NULL,
blocksize bigint(21) NOT NULL default '0',
PRIMARY KEY (messageblk_idnr),
KEY msg_index (message_idnr)
) TYPE=InnoDB;
