Tom Collins wrote:
I think we'll get better domain alias support if you pull column
> `domain` out of table `Domains` and add it to the table `domain_alias`.
I agree. This makes it easy to make sure domain names are unique.
> I'm not sure we need to have a "master" and "alias" -- the names can
> all be equal with this setup.
I think we still need it. The 'real' domain is the one that has a file
system behind it, and we need to know which one it is. I suggest a
pointer field real_domain containing the domain_name_id of the real
domain. [Isn't there something in mailing lists that ties the list to
the real domain, that makes renaming domains hard?]
We can save a little bit of space by storing the path to the domain
directory in the domain table, and only the path from there down in the
users table. The dir field in the users table should be renamed to
user_dir.
I think domain_alias should be renamed to domain_names. Is there a
better name for domains now?
You might even want to have the limits fields go into a separate
> table, with one entry in that table declared "default". That way,
> domains with default entries can all point to that single row in the
> limits table.
How about we remove the NOT NULL constraint from each of the limit
values, and if they are null take the value from the 'domain'
_system.default. This way if you leave any value NULL, the default is
filled in from the database in a single query:
SELECT b.domain as domain,
c.domain as real_domain,
if( a.ip_address IS NULL, e.ip_address, a.ip_address ) as ip_address,
if( a.max... IS NULL, e.max..., a.max... ) as max...,
...
FROM domain_names a
LEFT JOIN domains b USING( domain_id )
LEFT JOIN domain_names c ON c.domain_id = b.real_domain
LEFT JOIN domain_names d ON d.domain = '_system.default'
LEFT JOIN domains e ON e.domain_id = d.domain_id
WHERE a.domain = '$TargetDomain';
Rick Macdougall wrote:
I'd add a key on timestamp. [in the relay table]
OK.
I also added a sequence field to the valias table.
Here is a pseudo-diff showing the changes:
CREATE TABLE Domains(
domain_id bigint not null auto_increment,
real_domain bigint,
- domain char(195), // TC
+ domain_dir char(100), // RW
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 ),
key( ip_address ),
);
- CREATE TABLE domain_alias( // RW
+ CREATE TABLE domain_names( // RW
- domain_alias_id bigint not null auto_increment, // RW
+ domain_name_id bigint not null auto_increment, // RW
domain_id bigint not null,
- alias char(195), // TC
+ domain char(195), // TC
- primary key( domain_alias_id ), // RW
+ primary key( domain_name_id ), // RW
foreign key( domain_id ) REFERENCES domains( domain_id ),
unique key( domain )
);
CREATE TABLE relay(
ip_addr char(18) not null,
timestamp datetime,
primary key( ip_addr ),
+ key( timestamp ) // RM
),
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), // RW
+ user_dir char(160), // RW
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,
+ sequence integer, // RW
name char(?),
alias char(195),
primary key( valias_id ),
foreign key( user_id ) REFERENCES users( user_id ),
unique key( name )
);
