I wasn't quite ready for this, but it has been on my list for a long
time... since it has come up, let me throw up this database structure
for comment. I have reduced the number of tables (in a full
installation) and made it relational.-
CREATE TABLE Domains(
domain_id bigint not null auto_increment,
domain char(195),
ip_address char(18)NOT NULL DEFAULT '0.0.0.0',
maxpopaccounts INT(10) NOT NULL DEFAULT -1,
maxaliases INT(10) NOT NULL DEFAULT -1,
maxforwards INT(10) NOT NULL DEFAULT -1,
maxautoresponders INT(10) NOT NULL DEFAULT -1,
maxmailinglists INT(10) NOT NULL DEFAULT -1,
diskquota INT(12) NOT NULL DEFAULT 0,
maxmsgcount INT(12) NOT NULL DEFAULT 0,
defaultquota INT(12) NOT NULL DEFAULT 0,
defaultmaxmsgcount INT(12) NOT NULL DEFAULT 0,
disable_pop TINYINT(1) NOT NULL DEFAULT 0,
disable_imap TINYINT(1) NOT NULL DEFAULT 0,
disable_dialup TINYINT(1) NOT NULL DEFAULT 0,
disable_passwordchanging TINYINT(1) NOT NULL DEFAULT 0,
disable_webmail TINYINT(1) NOT NULL DEFAULT 0,
disable_relay TINYINT(1) NOT NULL DEFAULT 0,
disable_smtp TINYINT(1) NOT NULL DEFAULT 0,
disable_spamassassin TINYINT(1) NOT NULL DEFAULT 0,
delete_spam TINYINT(1) NOT NULL DEFAULT 0,
perm_account TINYINT(2) NOT NULL DEFAULT 0,
perm_alias TINYINT(2) NOT NULL DEFAULT 0,
perm_forward TINYINT(2) NOT NULL DEFAULT 0,
perm_autoresponder TINYINT(2) NOT NULL DEFAULT 0,
perm_maillist TINYINT(4) NOT NULL DEFAULT 0,
perm_quota TINYINT(2) NOT NULL DEFAULT 0,
perm_defaultquota TINYINT(2) NOT NULL DEFAULT 0,
cur_users int,
level_cur int,
level_max int,
level_start0 int,
level_start1 int,
level_start2 int,
level_end0 int,
level_end1 int,
level_end2 int,
level_mod0 int,
level_mod1 int,
level_mod2 int,
level_index0 int,
level_index1 int,
level_index2 int,
the_dir char(160),
primary key( domain_id ),
unique key( domain )
key( ip_address ),
);
CREATE TABLE domain_alias(
domain_alias_id bigint not null auto_increment,
domain_id bigint not null,
alias char(195),
primary key( domain_alias_id ),
foreign key( domain_id ) REFERENCES domains( domain_id ),
unique key( alias )
);
CREATE TABLE relay(
ip_addr char(18) not null,
timestamp datetime,
primary key( ip_addr )
),
CREATE TABLE users(
user_id bigint not null auto_increment,
domain_id bigint not null,
name
passwd char(40),
uid int,
flags int,
comment char(48),
dir char(160),
shell char(20),
passwd char(16),
remote_ip char(18),
rate_limit int,
active char(1),
created datetime,
timestamp bigint default 0 NOT NULL,
primary key( valias_id ),
foreign key( domain_id ) REFERENCES domains( domain_id ),
unique key ( name, domain_id ) "
);
CREATE TABLE valias(
valias_id bigint not null auto_increment,
user_id bigint not null,
name char(?),
alias char(195),
primary key( valias_id ),
foreign key( user_id ) REFERENCES users( user_id ),
unique key( name )
);
I use bigint keys because the last time I looked (admittedly it was a
long time ago) if you had 2 bigints at the beginning of a table, and the
value in the second one was small, those two fields were bigger than the
data overwritten when a record is deleted from the table. This
increases the amount of data you can recover until the record gets
overwritten. (I have had to recover deleted records the by searching
through the database files before...)
If this happens, MANY_DOMAINS, CLEAR_PASS and IP_ALIAS_DOMAINS will all
go away. If we need to enable/disable clear pass it will be an option
in the file where we set the database open info.
Also, I want to change query construction from creating defines to
building them into strings.
I would also like to remove all data base structure changes from the
code, and provide a sql script to create the database. The vopomail
database user should be able to operate with no more than INSERT,
DELETE, UPDATE, and SELECT rights to the database.
I was planning on making vpopmaild the distinguishing factor for 5.5,
but it looks like this will be it. As soon as we go two weeks with no
one finding a problem with 5.4, I'll start a new 5.5 branch for this.
Comments?
This should probably move to vpopmail-devel on SourceForge...
