2011-12-07 15:24:52 +01:00
|
|
|
|
2011-12-05 17:18:03 +01:00
|
|
|
create table if not exists `sph_counter` (
|
2014-03-04 13:42:34 +01:00
|
|
|
`counter_id` bigint not null,
|
|
|
|
`max_doc_id` bigint not null,
|
2011-11-14 15:57:52 +01:00
|
|
|
primary key (`counter_id`)
|
|
|
|
);
|
|
|
|
|
2011-12-07 15:24:52 +01:00
|
|
|
|
2011-12-05 17:18:03 +01:00
|
|
|
create table if not exists `sph_index` (
|
2011-12-03 23:05:00 +01:00
|
|
|
`id` bigint not null,
|
2011-11-14 15:57:52 +01:00
|
|
|
`from` char(255) default null,
|
2012-02-02 15:29:07 +01:00
|
|
|
`to` text(8192) default null,
|
2012-01-14 09:53:26 +01:00
|
|
|
`fromdomain` char(255) default null,
|
|
|
|
`todomain` text(512) default null,
|
2011-11-14 15:57:52 +01:00
|
|
|
`subject` text(512) default null,
|
2012-04-27 14:39:10 +02:00
|
|
|
`arrived` int unsigned not null,
|
|
|
|
`sent` int unsigned not null,
|
2011-11-14 15:57:52 +01:00
|
|
|
`body` text,
|
|
|
|
`size` int default '0',
|
2012-01-03 00:19:43 +01:00
|
|
|
`direction` int default 0,
|
2012-08-23 10:23:58 +02:00
|
|
|
`folder` int default 0,
|
2011-11-28 14:21:14 +01:00
|
|
|
`attachments` int default 0,
|
2011-12-30 15:52:59 +01:00
|
|
|
`attachment_types` text(512) default null,
|
2011-11-14 15:57:52 +01:00
|
|
|
primary key (`id`)
|
|
|
|
) Engine=InnoDB;
|
|
|
|
|
2011-11-28 14:21:14 +01:00
|
|
|
|
2011-12-05 17:18:03 +01:00
|
|
|
create table if not exists `metadata` (
|
2011-11-14 15:57:52 +01:00
|
|
|
`id` bigint unsigned not null auto_increment,
|
2013-02-07 20:47:57 +01:00
|
|
|
`from` varchar(128) not null,
|
|
|
|
`fromdomain` varchar(64) not null,
|
2011-11-14 15:57:52 +01:00
|
|
|
`subject` text(512) default null,
|
2012-01-26 14:35:51 +01:00
|
|
|
`spam` tinyint(1) default 0,
|
2012-04-27 14:39:10 +02:00
|
|
|
`arrived` int unsigned not null,
|
|
|
|
`sent` int unsigned not null,
|
|
|
|
`retained` int unsigned not null,
|
2011-12-13 17:05:22 +01:00
|
|
|
`deleted` tinyint(1) default 0,
|
2011-11-14 15:57:52 +01:00
|
|
|
`size` int default 0,
|
|
|
|
`hlen` int default 0,
|
2012-01-03 00:19:43 +01:00
|
|
|
`direction` int default 0,
|
2011-11-22 12:31:54 +01:00
|
|
|
`attachments` int default 0,
|
2011-11-14 15:57:52 +01:00
|
|
|
`piler_id` char(36) not null,
|
2013-02-07 20:47:57 +01:00
|
|
|
`message_id` varchar(128) not null,
|
2013-02-04 11:59:14 +01:00
|
|
|
`reference` char(64) not null,
|
2011-11-22 12:31:54 +01:00
|
|
|
`digest` char(64) not null,
|
2011-11-14 15:57:52 +01:00
|
|
|
`bodydigest` char(64) not null,
|
2012-01-09 23:15:39 +01:00
|
|
|
`vcode` char(64) default null,
|
2013-01-10 11:32:19 +01:00
|
|
|
primary key (`id`)
|
2011-11-14 15:57:52 +01:00
|
|
|
) Engine=InnoDB;
|
|
|
|
|
|
|
|
create index metadata_idx on metadata(`piler_id`);
|
|
|
|
create index metadata_idx2 on metadata(`message_id`);
|
2012-02-08 23:14:28 +01:00
|
|
|
create index metadata_idx3 on metadata(`reference`);
|
|
|
|
create index metadata_idx4 on metadata(`bodydigest`);
|
|
|
|
create index metadata_idx5 on metadata(`deleted`);
|
|
|
|
create index metadata_idx6 on metadata(`arrived`);
|
2012-02-19 22:59:47 +01:00
|
|
|
create index metadata_idx7 on metadata(`retained`);
|
2015-03-29 09:59:41 +02:00
|
|
|
create index metadata_idx8 on metadata(`fromdomain`);
|
|
|
|
create index metadata_idx9 on metadata(`from`);
|
|
|
|
create index metadata_idx10 on metadata(`sent`);
|
2011-11-14 15:57:52 +01:00
|
|
|
|
2011-12-03 23:05:00 +01:00
|
|
|
|
2011-12-05 17:18:03 +01:00
|
|
|
create table if not exists `rcpt` (
|
2011-12-03 23:05:00 +01:00
|
|
|
`id` bigint unsigned not null,
|
2013-02-07 20:47:57 +01:00
|
|
|
`to` varchar(128) not null,
|
2013-08-02 12:30:09 +02:00
|
|
|
`todomain` varchar(128) not null,
|
2011-12-03 23:05:00 +01:00
|
|
|
unique(`id`,`to`)
|
|
|
|
) Engine=InnoDB;
|
|
|
|
|
|
|
|
create index `rcpt_idx` on `rcpt`(`id`);
|
|
|
|
create index `rcpt_idx2` on `rcpt`(`to`);
|
2013-08-02 12:30:09 +02:00
|
|
|
create index `rcpt_idx3` on `rcpt`(`todomain`);
|
2011-12-03 23:05:00 +01:00
|
|
|
|
|
|
|
|
2012-04-27 14:39:10 +02:00
|
|
|
drop view if exists `v_messages`;
|
2016-04-06 21:32:24 +02:00
|
|
|
create view `v_messages` AS select `metadata`.`id` AS `id`,`metadata`.`piler_id` AS `piler_id`,`metadata`.`from` AS `from`,`metadata`.`fromdomain` AS `fromdomain`,`rcpt`.`to` AS `to`,`rcpt`.`todomain` AS `todomain`,`metadata`.`subject` AS `subject`, `metadata`.`size` AS `size`, `metadata`.`direction` AS `direction`, `metadata`.`sent` AS `sent`, `metadata`.`retained` AS `retained`, `metadata`.`arrived` AS `arrived`, `metadata`.`digest` AS `digest`, `metadata`.`bodydigest` AS `bodydigest`, `metadata`.`deleted` AS `deleted` from (`metadata` join `rcpt`) where (`metadata`.`id` = `rcpt`.`id`);
|
2012-01-07 23:26:47 +01:00
|
|
|
|
2011-12-05 17:18:03 +01:00
|
|
|
|
|
|
|
create table if not exists `attachment` (
|
2011-11-19 21:25:44 +01:00
|
|
|
`id` bigint unsigned not null auto_increment,
|
|
|
|
`piler_id` char(36) not null,
|
|
|
|
`attachment_id` int not null,
|
2013-02-07 20:47:57 +01:00
|
|
|
`name` varchar(128) default null,
|
|
|
|
`type` varchar(128) default null,
|
2011-11-19 21:25:44 +01:00
|
|
|
`sig` char(64) not null,
|
2011-11-23 12:24:21 +01:00
|
|
|
`size` int default 0,
|
2013-05-05 11:34:17 +02:00
|
|
|
`ptr` bigint unsigned default 0,
|
2011-12-19 23:11:40 +01:00
|
|
|
`deleted` tinyint(1) default 0,
|
2011-11-19 21:25:44 +01:00
|
|
|
primary key (`id`)
|
|
|
|
) Engine=InnoDB;
|
|
|
|
|
|
|
|
create index `attachment_idx` on `attachment`(`piler_id`);
|
2016-06-19 20:24:23 +02:00
|
|
|
create index `attachment_idx2` on `attachment`(`sig`, `size`, `ptr`);
|
2016-10-17 22:05:41 +02:00
|
|
|
create index `attachment_idx3` on `attachment`(`ptr`);
|
2012-03-10 14:52:50 +01:00
|
|
|
|
|
|
|
drop view if exists `v_attachment`;
|
|
|
|
create view `v_attachment` AS select `id` as `i`, `piler_id`, `attachment_id`, `ptr`, (select count(*) from `attachment` where `ptr`=`i`) as `refcount` from `attachment`;
|
2011-11-19 21:25:44 +01:00
|
|
|
|
2011-12-03 23:05:00 +01:00
|
|
|
|
2011-12-05 17:18:03 +01:00
|
|
|
create table if not exists `tag` (
|
2012-09-06 15:27:20 +02:00
|
|
|
`_id` bigint unsigned auto_increment not null,
|
2012-09-02 23:21:41 +02:00
|
|
|
`id` bigint not null,
|
2011-12-06 11:29:36 +01:00
|
|
|
`uid` int not null,
|
2013-02-07 20:47:57 +01:00
|
|
|
`tag` varchar(255) default null,
|
2012-09-06 15:27:20 +02:00
|
|
|
unique(`id`, `uid`),
|
|
|
|
key (`_id`)
|
2011-12-06 11:29:36 +01:00
|
|
|
) ENGINE=InnoDB;
|
2011-12-05 17:18:03 +01:00
|
|
|
|
|
|
|
|
|
|
|
create table if not exists `archiving_rule` (
|
2011-11-19 21:25:44 +01:00
|
|
|
`id` bigint unsigned not null auto_increment,
|
2013-07-31 10:57:45 +02:00
|
|
|
`domain` varchar(128) default null,
|
2013-11-11 11:34:00 +01:00
|
|
|
`from` varchar(128) default null,
|
|
|
|
`to` varchar(128) default null,
|
|
|
|
`subject` varchar(128) default null,
|
2015-03-11 12:54:31 +01:00
|
|
|
`body` varchar(128) default null,
|
2011-11-19 21:25:44 +01:00
|
|
|
`_size` char(2) default null,
|
|
|
|
`size` int default 0,
|
2013-11-11 11:34:00 +01:00
|
|
|
`attachment_name` varchar(128) default null,
|
|
|
|
`attachment_type` varchar(128) default null,
|
2011-11-23 12:24:21 +01:00
|
|
|
`_attachment_size` char(2) default null,
|
|
|
|
`attachment_size` int default 0,
|
2012-02-19 22:59:47 +01:00
|
|
|
`spam` tinyint(1) default -1,
|
|
|
|
`days` int default 0,
|
2015-08-28 22:50:28 +02:00
|
|
|
`folder_id` int default 0,
|
2012-01-28 20:52:13 +01:00
|
|
|
primary key (`id`),
|
2015-03-11 12:54:31 +01:00
|
|
|
unique(`from`,`to`,`subject`,`body`,`_size`,`size`,`attachment_name`,`attachment_type`,`_attachment_size`,`attachment_size`,`spam`)
|
2012-02-19 22:59:47 +01:00
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
|
|
|
create table if not exists `retention_rule` (
|
|
|
|
`id` bigint unsigned not null auto_increment,
|
2013-07-31 10:57:45 +02:00
|
|
|
`domain` varchar(128) default null,
|
2013-11-11 11:34:00 +01:00
|
|
|
`from` varchar(128) default null,
|
|
|
|
`to` varchar(128) default null,
|
|
|
|
`subject` varchar(128) default null,
|
2015-03-11 12:54:31 +01:00
|
|
|
`body` varchar(128) default null,
|
2012-02-19 22:59:47 +01:00
|
|
|
`_size` char(2) default null,
|
|
|
|
`size` int default 0,
|
2013-11-11 11:34:00 +01:00
|
|
|
`attachment_name` varchar(128) default null,
|
|
|
|
`attachment_type` varchar(128) default null,
|
2012-02-19 22:59:47 +01:00
|
|
|
`_attachment_size` char(2) default null,
|
|
|
|
`attachment_size` int default 0,
|
|
|
|
`spam` tinyint(1) default -1,
|
|
|
|
`days` int default 0,
|
2015-08-28 22:50:28 +02:00
|
|
|
`folder_id` int default 0,
|
|
|
|
primary key (`id`),
|
|
|
|
unique (`domain`,`from`,`to`,`subject`,`body`,`_size`,`size`,`attachment_name`,`attachment_type`,`_attachment_size`,`attachment_size`,`spam`)
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
|
|
|
create table if not exists `folder_rule` (
|
|
|
|
`id` bigint unsigned not null auto_increment,
|
|
|
|
`domain` varchar(128) default null,
|
|
|
|
`from` varchar(128) default null,
|
|
|
|
`to` varchar(128) default null,
|
|
|
|
`subject` varchar(128) default null,
|
|
|
|
`body` varchar(128) default null,
|
|
|
|
`_size` char(2) default null,
|
|
|
|
`size` int default 0,
|
|
|
|
`attachment_name` varchar(128) default null,
|
|
|
|
`attachment_type` varchar(128) default null,
|
|
|
|
`_attachment_size` char(2) default null,
|
|
|
|
`attachment_size` int default 0,
|
|
|
|
`spam` tinyint(1) default -1,
|
|
|
|
`days` int default 0,
|
|
|
|
`folder_id` int default 0,
|
2012-02-19 22:59:47 +01:00
|
|
|
primary key (`id`),
|
2015-03-11 12:54:31 +01:00
|
|
|
unique (`domain`,`from`,`to`,`subject`,`body`,`_size`,`size`,`attachment_name`,`attachment_type`,`_attachment_size`,`attachment_size`,`spam`)
|
2011-11-23 12:24:21 +01:00
|
|
|
) ENGINE=InnoDB;
|
2011-11-19 21:25:44 +01:00
|
|
|
|
2011-12-06 11:29:36 +01:00
|
|
|
|
2011-11-14 15:57:52 +01:00
|
|
|
create table if not exists `counter` (
|
|
|
|
`rcvd` bigint unsigned default 0,
|
|
|
|
`virus` bigint unsigned default 0,
|
2011-12-30 15:52:59 +01:00
|
|
|
`duplicate` bigint unsigned default 0,
|
2012-02-12 09:18:07 +01:00
|
|
|
`ignore` bigint unsigned default 0,
|
2014-05-05 15:26:42 +02:00
|
|
|
`size` bigint unsigned default 0,
|
|
|
|
`stored_size` bigint unsigned default 0
|
2011-11-14 15:57:52 +01:00
|
|
|
) Engine=InnoDB;
|
|
|
|
|
2014-05-05 15:26:42 +02:00
|
|
|
insert into `counter` values(0, 0, 0, 0, 0, 0);
|
2011-11-14 15:57:52 +01:00
|
|
|
|
2011-12-06 11:29:36 +01:00
|
|
|
|
2012-04-27 14:39:10 +02:00
|
|
|
create table if not exists `option` (
|
|
|
|
`key` char(64) not null,
|
|
|
|
`value` char(32) not null
|
|
|
|
) Engine=InnoDB;
|
|
|
|
|
|
|
|
insert into `option` (`key`, `value`) values('enable_purge', '1');
|
|
|
|
|
|
|
|
|
2011-12-05 17:18:03 +01:00
|
|
|
create table if not exists `search` (
|
2011-11-30 23:42:15 +01:00
|
|
|
`email` char(128) not null,
|
|
|
|
`ts` int default 0,
|
|
|
|
`term` text(512) not null
|
|
|
|
) Engine=InnoDB;
|
|
|
|
|
|
|
|
create index `search_idx` on `search`(`email`);
|
|
|
|
|
|
|
|
|
2011-12-19 23:11:40 +01:00
|
|
|
create table if not exists `user_settings` (
|
|
|
|
`username` char(64) not null unique,
|
|
|
|
`pagelen` int default 20,
|
|
|
|
`theme` char(8) default 'default',
|
2013-10-16 14:55:17 +02:00
|
|
|
`lang` char(2) default null,
|
|
|
|
`ga_enabled` int default 0,
|
|
|
|
`ga_secret` varchar(255) default null
|
2015-03-11 12:54:31 +01:00
|
|
|
) Engine=InnoDB;
|
2011-12-19 23:11:40 +01:00
|
|
|
|
|
|
|
create index `user_settings_idx` on `user_settings`(`username`);
|
|
|
|
|
2011-12-27 20:51:56 +01:00
|
|
|
|
|
|
|
create table if not exists `user` (
|
|
|
|
`uid` int unsigned not null primary key,
|
|
|
|
`username` char(64) not null unique,
|
|
|
|
`realname` char(64) default null,
|
2012-10-16 23:45:54 +02:00
|
|
|
`samaccountname` char(64) default null,
|
2012-08-01 16:03:55 +02:00
|
|
|
`password` char(128) default null,
|
2011-12-27 20:51:56 +01:00
|
|
|
`domain` char(64) default null,
|
|
|
|
`dn` char(255) default '*',
|
|
|
|
`isadmin` tinyint default 0
|
|
|
|
) Engine=InnoDB;
|
|
|
|
|
2012-10-16 23:45:54 +02:00
|
|
|
insert into `user` (`uid`, `username`, `realname`, `samaccountname`, `password`, `isadmin`, `domain`) values (0, 'admin', 'built-in piler admin', '', '$1$PItc7d$zsUgON3JRrbdGS11t9JQW1', 1, 'local');
|
2013-08-16 09:37:20 +02:00
|
|
|
insert into `user` (`uid`, `username`, `realname`, `samaccountname`, `password`, `isadmin`, `domain`) values (1, 'auditor', 'built-in piler auditor', '', '$1$SLIIIS$JMBwGqQg4lIir2P2YU1y.0', 2, 'local');
|
2011-12-27 20:51:56 +01:00
|
|
|
|
|
|
|
create table if not exists `email` (
|
|
|
|
`uid` int unsigned not null,
|
|
|
|
`email` char(128) not null primary key
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
insert into `email` (`uid`, `email`) values(0, 'admin@local');
|
2013-08-16 09:37:20 +02:00
|
|
|
insert into `email` (`uid`, `email`) values(1, 'auditor@local');
|
2011-12-27 20:51:56 +01:00
|
|
|
|
|
|
|
|
|
|
|
create table if not exists `email_groups` (
|
|
|
|
`uid` int unsigned not null,
|
|
|
|
`gid` int unsigned not null,
|
|
|
|
unique key `uid` (`uid`,`gid`),
|
|
|
|
key `email_groups_idx` (`uid`,`gid`)
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
2012-06-22 12:30:55 +02:00
|
|
|
create table if not exists `group` (
|
|
|
|
`id` bigint unsigned not null auto_increment primary key,
|
|
|
|
`groupname` char(255) not null unique
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
2012-06-25 22:14:30 +02:00
|
|
|
create table if not exists `group_user` (
|
|
|
|
`id` bigint unsigned not null,
|
2014-07-05 17:09:38 +02:00
|
|
|
`email` char(128) not null,
|
2012-06-25 22:14:30 +02:00
|
|
|
key `group_user_idx` (`id`),
|
2014-07-05 17:09:38 +02:00
|
|
|
key `group_user_idx2` (`email`)
|
2012-06-25 22:14:30 +02:00
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
2012-06-22 12:30:55 +02:00
|
|
|
create table if not exists `group_email` (
|
|
|
|
`id` bigint unsigned not null,
|
|
|
|
`email` char(128) not null,
|
|
|
|
key `group_email_idx` (`id`)
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
2013-01-05 16:42:36 +01:00
|
|
|
create table if not exists `domain_user` (
|
|
|
|
`domain` char(64) not null,
|
|
|
|
`uid` int unsigned not null,
|
|
|
|
key `domain_user_idx` (`domain`),
|
|
|
|
key `domain_user_idx2` (`uid`)
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
2012-08-28 22:00:45 +02:00
|
|
|
create table if not exists `folder` (
|
|
|
|
`id` int not null auto_increment,
|
|
|
|
`parent_id` int default 0,
|
2012-10-08 22:06:03 +02:00
|
|
|
`name` char(64) not null,
|
|
|
|
unique(`parent_id`, `name`),
|
2012-08-28 22:00:45 +02:00
|
|
|
primary key (`id`)
|
|
|
|
) Engine=InnoDB;
|
|
|
|
|
|
|
|
|
2012-09-06 15:27:20 +02:00
|
|
|
create table if not exists `folder_user` (
|
|
|
|
`id` bigint unsigned not null,
|
|
|
|
`uid` int unsigned not null,
|
|
|
|
key `folder_user_idx` (`id`),
|
|
|
|
key `folder_user_idx2` (`uid`)
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
2012-09-16 16:47:28 +02:00
|
|
|
create table if not exists `folder_extra` (
|
|
|
|
`id` int unsigned not null auto_increment,
|
|
|
|
`uid` int unsigned not null,
|
|
|
|
`name` char(64) not null,
|
2012-09-17 16:49:27 +02:00
|
|
|
unique(uid, name),
|
|
|
|
key (`id`)
|
2012-09-16 16:47:28 +02:00
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
|
|
|
create table if not exists `folder_message` (
|
|
|
|
folder_id bigint not null,
|
|
|
|
id bigint not null,
|
|
|
|
unique(folder_id, id)
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
2012-09-02 23:21:41 +02:00
|
|
|
create table if not exists `note` (
|
2012-09-06 15:27:20 +02:00
|
|
|
`_id` bigint unsigned auto_increment not null,
|
2012-08-28 22:00:45 +02:00
|
|
|
`id` bigint unsigned not null,
|
2012-09-02 23:21:41 +02:00
|
|
|
`uid` int not null,
|
2012-08-28 22:00:45 +02:00
|
|
|
`note` text default null,
|
2012-09-02 23:21:41 +02:00
|
|
|
unique(`id`, `uid`),
|
2012-09-06 15:27:20 +02:00
|
|
|
key (`_id`)
|
2012-08-28 22:00:45 +02:00
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
2016-02-10 14:57:30 +01:00
|
|
|
create table if not exists `private` (
|
|
|
|
`id` bigint unsigned not null unique,
|
|
|
|
key (`id`)
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
2011-12-27 20:51:56 +01:00
|
|
|
create table if not exists `remote` (
|
|
|
|
`remotedomain` char(64) not null primary key,
|
|
|
|
`remotehost` char(64) not null,
|
2012-08-06 14:01:56 +02:00
|
|
|
`basedn` char(255) not null,
|
|
|
|
`binddn` char(255) not null,
|
2011-12-27 20:51:56 +01:00
|
|
|
`sitedescription` char(64) default null
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
|
|
|
create table if not exists `domain` (
|
|
|
|
`domain` char(64) not null primary key,
|
2013-07-26 14:29:33 +02:00
|
|
|
`mapped` char(64) not null,
|
|
|
|
`ldap_id` int default 0
|
2011-12-27 20:51:56 +01:00
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
insert into `domain` (`domain`, `mapped`) values('local', 'local');
|
|
|
|
|
2012-01-14 09:53:26 +01:00
|
|
|
|
|
|
|
create table if not exists `audit` (
|
|
|
|
`id` bigint unsigned not null auto_increment,
|
|
|
|
`ts` int not null,
|
2013-02-07 20:47:57 +01:00
|
|
|
`email` varchar(128) not null,
|
2013-07-12 15:02:50 +02:00
|
|
|
`domain` varchar(128) not null,
|
2012-01-14 09:53:26 +01:00
|
|
|
`action` int not null,
|
|
|
|
`ipaddr` char(15) not null,
|
|
|
|
`meta_id` bigint unsigned not null,
|
2013-02-07 20:47:57 +01:00
|
|
|
`description` varchar(255) default null,
|
2012-01-14 09:53:26 +01:00
|
|
|
`vcode` char(64) default null,
|
|
|
|
primary key (`id`)
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
2012-01-26 14:35:51 +01:00
|
|
|
create index `audit_idx` on `audit`(`email`);
|
|
|
|
create index `audit_idx2` on `audit`(`action`);
|
|
|
|
create index `audit_idx3` on `audit`(`ipaddr`);
|
|
|
|
create index `audit_idx4` on `audit`(`ts`);
|
2013-07-12 15:02:50 +02:00
|
|
|
create index `audit_idx5` on `audit`(`domain`);
|
2012-01-26 14:35:51 +01:00
|
|
|
|
2012-09-28 10:34:04 +02:00
|
|
|
|
|
|
|
|
|
|
|
create table if not exists `google` (
|
2014-01-16 23:02:23 +01:00
|
|
|
`id` char(32) not null primary key,
|
2012-09-28 10:34:04 +02:00
|
|
|
`email` char(128) not null unique,
|
|
|
|
`access_token` char(255) default null,
|
|
|
|
`refresh_token` char(255) default null,
|
|
|
|
`created` int default 0
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
|
|
|
create table if not exists `google_imap` (
|
2014-01-16 23:02:23 +01:00
|
|
|
`id` char(32) not null primary key,
|
2012-09-28 10:34:04 +02:00
|
|
|
`email` char(128) not null,
|
|
|
|
`last_msg_id` bigint default 0,
|
|
|
|
key(`email`)
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
2013-02-16 12:33:25 +01:00
|
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS `counter_stats` (
|
|
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
|
|
`date` int(11) NOT NULL,
|
|
|
|
`email` varchar(255) NOT NULL,
|
|
|
|
`domain` varchar(255) NOT NULL,
|
|
|
|
`sent` int(11) NOT NULL,
|
|
|
|
`recd` int(11) NOT NULL,
|
|
|
|
`sentsize` int(11) NOT NULL,
|
|
|
|
`recdsize` int(11) NOT NULL,
|
|
|
|
PRIMARY KEY (`id`),
|
|
|
|
KEY `date` (`date`),
|
|
|
|
KEY `email` (`email`),
|
|
|
|
KEY `domain` (`domain`)
|
|
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
|
2013-07-12 15:02:50 +02:00
|
|
|
create table if not exists `ldap` (
|
|
|
|
`id` int not null auto_increment primary key,
|
|
|
|
`description` varchar(255) not null,
|
|
|
|
`ldap_type` varchar(255) not null,
|
|
|
|
`ldap_host` varchar(255) not null,
|
|
|
|
`ldap_base_dn` varchar(255) not null,
|
|
|
|
`ldap_bind_dn` varchar(255) not null,
|
2013-07-23 22:44:34 +02:00
|
|
|
`ldap_bind_pw` varchar(255) not null,
|
2014-02-18 10:46:23 +01:00
|
|
|
`ldap_auditor_member_dn` varchar(255) default null,
|
|
|
|
`ldap_mail_attr` varchar(128) default null,
|
|
|
|
`ldap_account_objectclass` varchar(128) default null,
|
|
|
|
`ldap_distributionlist_attr` varchar(128) default null,
|
|
|
|
`ldap_distributionlist_objectclass` varchar(128) default null
|
2013-07-12 15:02:50 +02:00
|
|
|
) Engine=InnoDB;
|
|
|
|
|
|
|
|
|
2013-07-16 07:36:48 +02:00
|
|
|
create table if not exists `customer_settings` (
|
2013-07-23 22:44:34 +02:00
|
|
|
`id` int not null auto_increment primary key,
|
2013-07-16 07:36:48 +02:00
|
|
|
`domain` varchar(255) not null unique,
|
|
|
|
`branding_text` varchar(255) default null,
|
|
|
|
`branding_url` varchar(255) default null,
|
2013-07-18 17:13:49 +02:00
|
|
|
`branding_logo` varchar(255) default null,
|
2013-07-16 07:36:48 +02:00
|
|
|
`support_link` varchar(255) default null,
|
2013-08-09 22:11:16 +02:00
|
|
|
`background_colour` varchar(255) default null,
|
|
|
|
`text_colour` varchar(255) default null
|
2013-07-16 07:36:48 +02:00
|
|
|
) Engine=InnoDB;
|
|
|
|
|
|
|
|
|
2013-07-24 22:14:05 +02:00
|
|
|
create table if not exists `online` (
|
2013-07-25 11:56:52 +02:00
|
|
|
`username` varchar(255) not null,
|
2013-07-24 22:14:05 +02:00
|
|
|
`ts` int default 0,
|
|
|
|
`last_activity` int default 0,
|
2013-07-25 11:56:52 +02:00
|
|
|
`ipaddr` varchar(255) default null,
|
|
|
|
unique(`username`,`ipaddr`)
|
2013-07-24 22:14:05 +02:00
|
|
|
) Engine=InnoDB;
|
|
|
|
|
2013-08-23 13:02:51 +02:00
|
|
|
|
|
|
|
create table if not exists `import` (
|
|
|
|
`id` int not null auto_increment primary key,
|
|
|
|
`type` varchar(255) not null,
|
|
|
|
`username` varchar(255) not null,
|
|
|
|
`password` varchar(255) not null,
|
|
|
|
`server` varchar(255) not null,
|
|
|
|
`created` int default 0,
|
|
|
|
`started` int default 0,
|
|
|
|
`finished` int default 0,
|
|
|
|
`updated` int default 0,
|
|
|
|
`status` int default 0,
|
|
|
|
`total` int default 0,
|
|
|
|
`imported` int default 0,
|
|
|
|
`duplicate` int default 0,
|
|
|
|
`error` int default 0,
|
|
|
|
`cleared` int default 0
|
|
|
|
) Engine=InnoDB;
|
|
|
|
|
|
|
|
|
2014-01-24 14:23:49 +01:00
|
|
|
create table if not exists `autosearch` (
|
|
|
|
`id` int not null auto_increment primary key,
|
|
|
|
`query` varchar(512) not null
|
|
|
|
) Engine=InnoDB;
|
|
|
|
|
2015-03-29 09:59:41 +02:00
|
|
|
|
|
|
|
create table if not exists `legal_hold` (
|
2015-04-05 13:18:35 +02:00
|
|
|
email varchar(128) unique not null
|
2015-03-29 09:59:41 +02:00
|
|
|
) Engine=InnoDB;
|
|
|
|
|
|
|
|
|
2015-04-22 12:26:04 +02:00
|
|
|
create table if not exists `timestamp` (
|
|
|
|
`id` bigint unsigned not null auto_increment,
|
|
|
|
`start_id` bigint default 0,
|
|
|
|
`stop_id` bigint default 0,
|
|
|
|
`hash_value` char(40),
|
|
|
|
`count` int default 0,
|
|
|
|
`response_time` bigint default 0,
|
|
|
|
`response_string` blob not null,
|
|
|
|
primary key (`id`)
|
|
|
|
) Engine=InnoDB;
|
|
|
|
|