
CREATE DATABASE IF NOT EXISTS `emailserver`;

FLUSH PRIVILEGES;
USE emailserver;

-- ----------------------------
-- Table: access
-- this table lists anything that we flat out deny.
-- examples:
-- (source,access,type,info) values ('186','533 rejected','client','the entire 186.0.0.0/8 ip range')
-- (source,access,type,info) values ('186.17.0','533 rejected','client','the entire 186.17.0.0/16 ip range')
-- (source,access,type,info) values ('spamhost.com','533 rejected','client','any host that has spamhost.com')
-- (source,access,type,info) values ('spammer@somedomain.com','533 rejected','sender','any email sent from this address (= from header)')
-- ----------------------------
CREATE TABLE `access` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `source` varchar(128) NOT NULL DEFAULT '',
  `access` varchar(128) NOT NULL DEFAULT '',
  `type` enum('recipient','sender','client','disabled') NOT NULL DEFAULT 'disabled',
  `info` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `source` (`source`)
) ENGINE=InnoDB AUTO_INCREMENT=428 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;


-- ----------------------------
-- Table: aliases
-- the usual alias table in /etc/aliases with some extra fields for the web app
-- examples:
-- (alias,aliasdest,validtarget,public) VALUES ('root','/dev/null','N','N')
-- (alias,aliasdest,validtarget,public) VALUES ('bouncer,'|/usr/local/etc/postfix/bouncer.pl','Y','Y')
-- ----------------------------
CREATE TABLE `aliases` (
  `aliasid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `alias` varchar(128) NOT NULL DEFAULT '',
  `aliasdest` varchar(128) NOT NULL DEFAULT '',
  `validtarget` enum('Y','N') NOT NULL DEFAULT 'N',
  `public` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`aliasid`),
  UNIQUE KEY `alias` (`alias`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Table: calendardata
-- Used for holding the CALDAV data items. 
-- ----------------------------
CREATE TABLE `calendardata` (
  `listid` bigint(20) unsigned NOT NULL,
  `uid` varchar(50) NOT NULL,
  `etag` varchar(50) DEFAULT '',
  `data` blob NOT NULL,
  PRIMARY KEY (`listid`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: calendarlist
-- Used to store the calendars of any pop3 account. The pop3id is linked to the pop3id in the pop3 table.
-- ----------------------------
CREATE TABLE `calendarlist` (
  `pop3id` bigint(20) unsigned NOT NULL,
  `listid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `type` enum('E','T') NOT NULL DEFAULT 'E',
  `color` varchar(10) DEFAULT '',
  `sort` int(10) unsigned NOT NULL DEFAULT 0,
  `ctag` varchar(20) NOT NULL DEFAULT '1',
  PRIMARY KEY (`listid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: calendarshares
-- used for caldav calendar sharing. pop3id is the pop3id in the pop3 table, defining to whom this calendar is shared.
-- listid is the calendar listid in the calendarlist table.
-- it also defines the rights on this shared calendar to that pop3 account.
-- ----------------------------
CREATE TABLE `calendarshares` (
  `pop3id` bigint(20) unsigned NOT NULL,
  `listid` bigint(20) unsigned NOT NULL,
  `canadd` tinyint(3) unsigned NOT NULL DEFAULT 1,
  `canupdate` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `candelete` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `canchange` tinyint(3) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`pop3id`,`listid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: clients
-- mainly for use when you have several clients using this mailserver and be able to assign domains to clients
-- it is not used in any way in the mail delivery
-- ----------------------------
CREATE TABLE `clients` (
  `clientid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `clientname` varchar(50) NOT NULL,
  `editdomain` enum('Y','N','') NOT NULL DEFAULT 'N',
  `editpop3` enum('Y','N') NOT NULL DEFAULT 'N',
  `editemail` enum('Y','N') NOT NULL DEFAULT 'Y',
  `editweb` enum('Y','N') NOT NULL DEFAULT 'N',
  `editftp` enum('Y','N') NOT NULL DEFAULT 'N',
  `editdatabase` enum('Y','N') NOT NULL DEFAULT 'N',
  `editalias` enum('Y','N') NOT NULL DEFAULT 'N',
  `edituser` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`clientid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: clienttodomain
-- to link domains to the clients table, mainly for the web frontend app
-- ----------------------------
CREATE TABLE `clienttodomain` (
  `recordid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `clientid` int(10) unsigned NOT NULL,
  `domainid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`recordid`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for clienttopop3
-- links pop3 accounts to clients in the client table. Mainly for administration
-- ----------------------------
CREATE TABLE `clienttopop3` (
  `recordid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `clientid` int(10) unsigned NOT NULL,
  `pop3id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`recordid`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: clienttouser
-- Link user accounts (that can login on the web frontend) to clients
-- ----------------------------
CREATE TABLE `clienttouser` (
  `recordid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `clientid` int(10) unsigned NOT NULL,
  `man_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`recordid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COMMENT='links clients to user login accounts\r\n';

-- ----------------------------
-- Table: contactdata
-- contact items for the addressbooks
-- ----------------------------
CREATE TABLE `contactdata` (
  `listid` bigint(20) unsigned NOT NULL,
  `uid` varchar(50) NOT NULL,
  `etag` varchar(50) DEFAULT '',
  `data` blob NOT NULL,
  PRIMARY KEY (`listid`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for contactlist
-- the contact lists for any pop3. The pop3id is the pop3id in the pop3 table
-- ----------------------------
CREATE TABLE `contactlist` (
  `pop3id` bigint(20) unsigned NOT NULL,
  `listid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `color` varchar(10) DEFAULT '',
  `sort` int(10) unsigned NOT NULL DEFAULT 0,
  `ctag` varchar(20) NOT NULL DEFAULT '1',
  PRIMARY KEY (`listid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: contactshares
-- the contact lists shared with the pop3 accounts. The pop3id is the pop3id in the pop3 table, to which the contactlist is shared.
-- the listid is the listid in the contactlist table
-- ----------------------------
CREATE TABLE `contactshares` (
  `pop3id` bigint(20) unsigned NOT NULL,
  `listid` bigint(20) unsigned NOT NULL,
  `canadd` tinyint(3) unsigned NOT NULL DEFAULT 1,
  `canupdate` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `candelete` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `canchange` tinyint(3) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`pop3id`,`listid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: currentquotas
-- this is used by dovecot to store the current size of the mailboxes, in bytes
-- ----------------------------
CREATE TABLE `currentquotas` (
  `recordid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `bytes` bigint(20) unsigned NOT NULL,
  `messages` int(11) unsigned NOT NULL,
  PRIMARY KEY (`recordid`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: domains
-- the domains for which this server will accept email. This table is also used to store groups of domains so they can be sorted in groups in the web frontend
-- it is possible to set up a non-email domain to keep as a placeholder. If has_mail is 'N', it will not receive any email and just bounce it all, but you won't see an error
-- in the logfile along the lines of: domain ..... loops back to me.
-- ----------------------------
CREATE TABLE `domains` (
  `domainid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `dom_name` varchar(128) NOT NULL DEFAULT '',
  `domaingroup` enum('Y','N') DEFAULT 'N',
  `groupid` int(10) unsigned zerofill DEFAULT NULL,
  `has_mail` enum('Y','N') DEFAULT 'N',
  `domaintransport` varchar(31) DEFAULT NULL,
  PRIMARY KEY (`domainid`),
  UNIQUE KEY `domain` (`dom_name`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Table: imapshares
-- shared mailboxes for the pop3 users. From-user is the login name of the owner, to-user is the login (in pop3 table) of the person allowed to use it.
-- it is NOT possible to share a subfolder (yet), only the entire mailfolder can be shared.
-- ----------------------------
CREATE TABLE `imapshares` (
  `shareid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `from_user` varchar(100) NOT NULL,
  `to_user` varchar(100) NOT NULL,
  `dummy` char(1) NOT NULL,
  PRIMARY KEY (`shareid`),
  UNIQUE KEY `usercombo` (`from_user`,`to_user`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: imapshares_anyone
-- the same thing as imapshares, only these shares will get shared with everybody 
-- ----------------------------
CREATE TABLE `imapshares_anyone` (
  `shareid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `from_user` varchar(100) NOT NULL,
  `dummy` char(1) NOT NULL,
  PRIMARY KEY (`shareid`),
  UNIQUE KEY `usercombo` (`from_user`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: pop3
-- the pop3 accounts. Yes, we store the password in plain text. We need it for several things.
-- ----------------------------
CREATE TABLE `pop3` (
  `pop3id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `login` varchar(100) NOT NULL,
  `clear` varchar(100) NOT NULL,
  `name` varchar(128) NOT NULL,
  `pop3` enum('Y','N') NOT NULL DEFAULT 'Y',
  `imap` enum('Y','N') NOT NULL DEFAULT 'Y',
  `smtp` enum('Y','N') NOT NULL DEFAULT 'Y',
  `quota` int(10) unsigned NOT NULL DEFAULT 0,
  `notice` enum('Y','N') NOT NULL DEFAULT 'N',
  `message` text DEFAULT NULL,
  `caldav_ctag` char(32) DEFAULT NULL,
  `caldav_enable` enum('Y','N') NOT NULL DEFAULT 'N',
  `carddav_ctag` char(32) DEFAULT NULL,
  `carddav_enable` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`pop3id`),
  UNIQUE KEY `login` (`login`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: users
-- This table holds the user accounts that can login on the web frontend. It also holds the permissions for the users.
-- ----------------------------
CREATE TABLE `users` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(25) NOT NULL,
  `clearpass` varchar(25) NOT NULL,
  `name` varchar(64) NOT NULL,
  `isadmin` enum('Y','N') NOT NULL DEFAULT 'N',
  `editdomain` enum('Y','N','') NOT NULL DEFAULT 'N',
  `editpop3` enum('Y','N') NOT NULL DEFAULT 'N',
  `editemail` enum('Y','N') NOT NULL DEFAULT 'N',
  `editalias` enum('Y','N') NOT NULL DEFAULT 'N',
  `edituser` enum('Y','N') NOT NULL DEFAULT 'N',
  `editclients` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: usertargets
-- only used in the web frontend to remember any entered email addresses
-- ----------------------------
CREATE TABLE `usertargets` (
  `targetid` bigint(20) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `targetadres` varchar(255) NOT NULL,
  PRIMARY KEY (`targetid`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=170 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: vacation
-- Used for remembering what email addresses the vacation notice is sent to, so each recipient will get the vacation notice only once!
-- ----------------------------
CREATE TABLE `vacation` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `login` varchar(128) DEFAULT NULL,
  `toadres` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `login` (`login`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: virtual
-- this table holds all the lookups for domains and email addresses for postfix
-- ----------------------------
CREATE TABLE `virtual` (
  `virtualid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domainid` int(10) unsigned NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`virtualid`,`email`),
  UNIQUE KEY `email` (`email`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=457 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table: virtualdest
-- this table holds the link to email adresses in the virtual table and the recipient. The recipient is either an alias or pop3 entry
-- ----------------------------
CREATE TABLE `virtualdest` (
  `vdestid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `virtualid` int(10) unsigned NOT NULL,
  `virtualdest` varchar(128) NOT NULL,
  PRIMARY KEY (`vdestid`,`virtualid`),
  KEY `virtualid` (`virtualid`)
) ENGINE=InnoDB AUTO_INCREMENT=684 DEFAULT CHARSET=latin1;



