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...

Reply via email to