Hi, I needed to take over an piler archive from my provider. The Version there was 1.2.0 build 952.
On my site I set up docker with your docker image 1.4.4. Out of the box everything was fine. I then restored the database I received from my old provder and migrated this to what I thought is the newest version: * as described here [ https://www.mailpiler.org/wiki/current:upgrade | https://www.mailpiler.org/wiki/current:upgrade ] and after that * as described here [ https://bitbucket.org/jsuto/piler/src/master/util/ | https://bitbucket.org/jsuto/piler/src/master/util/ ] [ https://bitbucket.org/jsuto/piler/src/master/util/db-upgrade.sql | db-upgrade.sql ] All statements were processed without errors When I compare the structure there are small differences. See attached files: * piler_apconsulting.sql is the migrated database * piler.sql is the database that was installed with the docker Do I need to run additional sql statements? If yes: Where can I find them? In the following i restored all files in /var/piler/store and the keys and adpoted the config files. While reindexing i got several identical error messages "Failed to inflate invalid or incomplete deflate data". I am not sure if the db differences are the root cause for this. If not: what do I need to do? When i check the ui with the auditor user, i see e-mails and they are validated (good!). I can filter them on date, but I can not filter them using string nor using the advanced search (except the dates). If I use the simple search I get the error message " Empty search result. Try adding the wildcard character(*) after a word snippet (min. 5 characters), eg. duplic* to find "duplicate", "duplicated", etc." Unsing wildcards there is no change, still the error message. With advanced search I receive the same error message. I am not sure if the above mentioned error messages during reindexing are the root cause for this. If not: what do I need to do? I would appretiate any help. Please keep in mind I am an advanced end user, no developer or sys admin. And please let me know if you need additional information. Thank you in advance Andreas
-- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/ -- -- Host: localhost -- Erstellungszeit: 04. Okt 2023 um 18:00 -- Server-Version: 10.3.37-MariaDB -- PHP-Version: 8.0.28 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Datenbank: `piler` -- -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `archiving_rule` -- CREATE TABLE `archiving_rule` ( `id` bigint(20) UNSIGNED NOT NULL, `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(11) DEFAULT 0, `attachment_name` varchar(128) DEFAULT NULL, `attachment_type` varchar(64) DEFAULT NULL, `_attachment_size` char(2) DEFAULT NULL, `attachment_size` int(11) DEFAULT 0, `spam` tinyint(1) DEFAULT -1, `days` int(11) DEFAULT 0, `folder_id` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `attachment` -- CREATE TABLE `attachment` ( `id` bigint(20) UNSIGNED NOT NULL, `piler_id` char(36) NOT NULL, `attachment_id` int(11) NOT NULL, `name` tinyblob DEFAULT NULL, `type` varchar(128) DEFAULT NULL, `sig` char(64) NOT NULL, `size` int(11) DEFAULT 0, `ptr` bigint(20) UNSIGNED DEFAULT 0, `deleted` tinyint(1) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `audit` -- CREATE TABLE `audit` ( `id` bigint(20) UNSIGNED NOT NULL, `ts` int(11) NOT NULL, `email` varchar(128) NOT NULL, `domain` varchar(128) NOT NULL, `action` int(11) NOT NULL, `ipaddr` varchar(39) NOT NULL, `meta_id` bigint(20) UNSIGNED NOT NULL, `description` varchar(255) DEFAULT NULL, `vcode` char(64) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `autosearch` -- CREATE TABLE `autosearch` ( `id` int(11) NOT NULL, `query` varchar(512) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `counter` -- CREATE TABLE `counter` ( `rcvd` bigint(20) UNSIGNED DEFAULT 0, `virus` bigint(20) UNSIGNED DEFAULT 0, `duplicate` bigint(20) UNSIGNED DEFAULT 0, `ignore` bigint(20) UNSIGNED DEFAULT 0, `size` bigint(20) UNSIGNED DEFAULT 0, `stored_size` bigint(20) UNSIGNED DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `counter_stats` -- CREATE TABLE `counter_stats` ( `id` int(11) NOT NULL, `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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `customer_settings` -- CREATE TABLE `customer_settings` ( `id` int(11) NOT NULL, `domain` varchar(128) NOT NULL, `branding_text` varchar(255) DEFAULT NULL, `branding_url` varchar(255) DEFAULT NULL, `branding_logo` varchar(255) DEFAULT NULL, `support_link` varchar(255) DEFAULT NULL, `background_colour` varchar(255) DEFAULT NULL, `text_colour` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `deleted` -- CREATE TABLE `deleted` ( `id` bigint(20) UNSIGNED NOT NULL, `requestor` varchar(128) NOT NULL, `reason1` varchar(128) NOT NULL, `date1` int(10) UNSIGNED DEFAULT 0, `approver` varchar(128) DEFAULT NULL, `reason2` varchar(128) DEFAULT NULL, `date2` int(10) UNSIGNED DEFAULT 0, `deleted` tinyint(1) DEFAULT -1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `domain` -- CREATE TABLE `domain` ( `domain` char(64) NOT NULL, `mapped` char(64) NOT NULL, `ldap_id` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `domain_user` -- CREATE TABLE `domain_user` ( `domain` char(64) NOT NULL, `uid` int(10) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `email` -- CREATE TABLE `email` ( `uid` int(10) UNSIGNED NOT NULL, `email` char(128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `email_groups` -- CREATE TABLE `email_groups` ( `uid` int(10) UNSIGNED NOT NULL, `gid` int(10) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `folder` -- CREATE TABLE `folder` ( `id` int(11) NOT NULL, `parent_id` int(11) DEFAULT 0, `name` char(64) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `folder_extra` -- CREATE TABLE `folder_extra` ( `id` int(10) UNSIGNED NOT NULL, `uid` int(10) UNSIGNED NOT NULL, `name` char(64) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `folder_message` -- CREATE TABLE `folder_message` ( `folder_id` bigint(20) NOT NULL, `id` bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `folder_rule` -- CREATE TABLE `folder_rule` ( `id` bigint(20) UNSIGNED NOT NULL, `domain` varchar(100) DEFAULT NULL, `from` varchar(100) DEFAULT NULL, `to` varchar(100) DEFAULT NULL, `subject` varchar(128) DEFAULT NULL, `body` varchar(128) DEFAULT NULL, `_size` char(2) DEFAULT NULL, `size` int(11) DEFAULT 0, `attachment_name` varchar(128) DEFAULT NULL, `attachment_type` varchar(64) DEFAULT NULL, `_attachment_size` char(2) DEFAULT NULL, `attachment_size` int(11) DEFAULT 0, `spam` tinyint(1) DEFAULT -1, `days` int(11) DEFAULT 0, `folder_id` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `folder_user` -- CREATE TABLE `folder_user` ( `id` bigint(20) UNSIGNED NOT NULL, `uid` int(10) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `google` -- CREATE TABLE `google` ( `id` char(32) NOT NULL, `email` char(128) NOT NULL, `access_token` char(255) DEFAULT NULL, `refresh_token` char(255) DEFAULT NULL, `created` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `google_imap` -- CREATE TABLE `google_imap` ( `id` char(32) NOT NULL, `email` char(128) NOT NULL, `last_msg_id` bigint(20) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `group_email` -- CREATE TABLE `group_email` ( `id` bigint(20) UNSIGNED NOT NULL, `email` char(128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `group_user` -- CREATE TABLE `group_user` ( `id` bigint(20) UNSIGNED NOT NULL, `email` char(128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `import` -- CREATE TABLE `import` ( `id` int(11) NOT NULL, `type` varchar(255) NOT NULL, `username` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `server` varchar(255) NOT NULL, `created` int(11) DEFAULT 0, `started` int(11) DEFAULT 0, `finished` int(11) DEFAULT 0, `updated` int(11) DEFAULT 0, `status` int(11) DEFAULT 0, `total` int(11) DEFAULT 0, `imported` int(11) DEFAULT 0, `duplicate` int(11) DEFAULT 0, `error` int(11) DEFAULT 0, `cleared` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `ldap` -- CREATE TABLE `ldap` ( `id` int(11) NOT NULL, `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, `ldap_bind_pw` varchar(255) NOT NULL, `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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `legal_hold` -- CREATE TABLE `legal_hold` ( `email` varchar(128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `metadata` -- CREATE TABLE `metadata` ( `id` bigint(20) UNSIGNED NOT NULL, `from` varchar(255) NOT NULL, `fromdomain` varchar(255) NOT NULL, `subject` blob DEFAULT NULL, `spam` tinyint(1) DEFAULT 0, `arrived` int(10) UNSIGNED NOT NULL, `sent` int(10) UNSIGNED NOT NULL, `retained` int(10) UNSIGNED NOT NULL, `deleted` tinyint(1) DEFAULT 0, `size` int(11) DEFAULT 0, `hlen` int(11) DEFAULT 0, `direction` int(11) DEFAULT 0, `attachments` int(11) DEFAULT 0, `piler_id` char(36) NOT NULL, `message_id` varchar(255) NOT NULL, `reference` char(64) NOT NULL, `digest` char(64) NOT NULL, `bodydigest` char(64) NOT NULL, `vcode` char(64) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `note` -- CREATE TABLE `note` ( `_id` bigint(20) UNSIGNED NOT NULL, `id` bigint(20) UNSIGNED NOT NULL, `uid` int(11) NOT NULL, `note` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `online` -- CREATE TABLE `online` ( `username` varchar(128) NOT NULL, `ts` int(11) DEFAULT 0, `last_activity` int(11) DEFAULT 0, `ipaddr` varchar(64) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `option` -- CREATE TABLE `option` ( `key` char(64) NOT NULL, `value` char(32) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `private` -- CREATE TABLE `private` ( `id` bigint(20) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `rcpt` -- CREATE TABLE `rcpt` ( `id` bigint(20) UNSIGNED NOT NULL, `to` varchar(128) NOT NULL, `todomain` varchar(128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `remote` -- CREATE TABLE `remote` ( `remotedomain` char(64) NOT NULL, `remotehost` char(64) NOT NULL, `basedn` char(255) NOT NULL, `binddn` char(255) NOT NULL, `sitedescription` char(64) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `retention_rule` -- CREATE TABLE `retention_rule` ( `id` bigint(20) UNSIGNED NOT NULL, `domain` varchar(100) DEFAULT NULL, `from` varchar(100) DEFAULT NULL, `to` varchar(100) DEFAULT NULL, `subject` varchar(128) DEFAULT NULL, `body` varchar(128) DEFAULT NULL, `_size` char(2) DEFAULT NULL, `size` int(11) DEFAULT 0, `attachment_name` varchar(100) DEFAULT NULL, `attachment_type` varchar(64) DEFAULT NULL, `_attachment_size` char(2) DEFAULT NULL, `attachment_size` int(11) DEFAULT 0, `spam` tinyint(1) DEFAULT -1, `days` int(11) DEFAULT 0, `folder_id` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `search` -- CREATE TABLE `search` ( `email` char(128) NOT NULL, `ts` int(11) DEFAULT 0, `term` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `sph_counter` -- CREATE TABLE `sph_counter` ( `counter_id` bigint(20) NOT NULL, `max_doc_id` bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `sph_index` -- CREATE TABLE `sph_index` ( `id` bigint(20) NOT NULL, `from` tinyblob DEFAULT NULL, `to` blob DEFAULT NULL, `fromdomain` tinyblob DEFAULT NULL, `todomain` blob DEFAULT NULL, `subject` blob DEFAULT NULL, `arrived` int(10) UNSIGNED NOT NULL, `sent` int(10) UNSIGNED NOT NULL, `body` mediumblob DEFAULT NULL, `size` int(11) DEFAULT 0, `direction` int(11) DEFAULT 0, `folder` int(11) DEFAULT 0, `attachments` int(11) DEFAULT 0, `attachment_types` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `tag` -- CREATE TABLE `tag` ( `_id` bigint(20) UNSIGNED NOT NULL, `id` bigint(20) NOT NULL, `uid` int(11) NOT NULL, `tag` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `timestamp` -- CREATE TABLE `timestamp` ( `id` bigint(20) UNSIGNED NOT NULL, `start_id` bigint(20) DEFAULT 0, `stop_id` bigint(20) DEFAULT 0, `hash_value` varchar(128) DEFAULT NULL, `count` int(11) DEFAULT 0, `response_time` bigint(20) DEFAULT 0, `response_string` blob NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `user` -- CREATE TABLE `user` ( `uid` int(10) UNSIGNED NOT NULL, `username` char(64) NOT NULL, `realname` char(64) DEFAULT NULL, `samaccountname` char(64) DEFAULT NULL, `password` char(128) DEFAULT NULL, `domain` char(64) DEFAULT NULL, `dn` char(255) DEFAULT '*', `isadmin` tinyint(4) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `usergroup` -- CREATE TABLE `usergroup` ( `id` bigint(20) UNSIGNED NOT NULL, `groupname` char(128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `user_settings` -- CREATE TABLE `user_settings` ( `username` char(64) NOT NULL, `pagelen` int(11) DEFAULT 20, `theme` char(8) DEFAULT 'default', `lang` char(2) DEFAULT NULL, `ga_enabled` int(11) DEFAULT 0, `ga_secret` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Stellvertreter-Struktur des Views `v_attachment` -- (Siehe unten für die tatsächliche Ansicht) -- CREATE TABLE `v_attachment` ( `i` bigint(20) unsigned ,`piler_id` char(36) ,`attachment_id` int(11) ,`ptr` bigint(20) unsigned ,`refcount` bigint(21) ); -- -------------------------------------------------------- -- -- Stellvertreter-Struktur des Views `v_messages` -- (Siehe unten für die tatsächliche Ansicht) -- CREATE TABLE `v_messages` ( `id` bigint(20) unsigned ,`piler_id` char(36) ,`from` varchar(255) ,`fromdomain` varchar(255) ,`to` varchar(128) ,`todomain` varchar(128) ,`subject` blob ,`size` int(11) ,`direction` int(11) ,`sent` int(10) unsigned ,`retained` int(10) unsigned ,`arrived` int(10) unsigned ,`digest` char(64) ,`bodydigest` char(64) ,`deleted` tinyint(1) ,`attachments` int(11) ); -- -------------------------------------------------------- -- -- Struktur des Views `v_attachment` -- DROP TABLE IF EXISTS `v_attachment`; CREATE ALGORITHM=UNDEFINED DEFINER=`piler`@`%` SQL SECURITY DEFINER VIEW `v_attachment` AS SELECT `attachment`.`id` AS `i`, `attachment`.`piler_id` AS `piler_id`, `attachment`.`attachment_id` AS `attachment_id`, `attachment`.`ptr` AS `ptr`, (select count(0) from `attachment` where `attachment`.`ptr` = `i`) AS `refcount` FROM `attachment` ; -- -------------------------------------------------------- -- -- Struktur des Views `v_messages` -- DROP TABLE IF EXISTS `v_messages`; CREATE ALGORITHM=UNDEFINED DEFINER=`piler`@`%` SQL SECURITY DEFINER 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`, `metadata`.`attachments` AS `attachments` FROM (`metadata` join `rcpt`) WHERE `metadata`.`id` = `rcpt`.`id` ; -- -- Indizes der exportierten Tabellen -- -- -- Indizes für die Tabelle `archiving_rule` -- ALTER TABLE `archiving_rule` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `from` (`from`,`to`,`subject`,`body`,`_size`,`size`,`attachment_name`,`attachment_type`,`_attachment_size`,`attachment_size`,`spam`); -- -- Indizes für die Tabelle `attachment` -- ALTER TABLE `attachment` ADD PRIMARY KEY (`id`), ADD KEY `attachment_idx` (`piler_id`), ADD KEY `attachment_idx2` (`sig`,`size`,`ptr`), ADD KEY `attachment_idx3` (`ptr`); -- -- Indizes für die Tabelle `audit` -- ALTER TABLE `audit` ADD PRIMARY KEY (`id`), ADD KEY `audit_idx` (`email`), ADD KEY `audit_idx2` (`action`), ADD KEY `audit_idx3` (`ipaddr`), ADD KEY `audit_idx4` (`ts`), ADD KEY `audit_idx5` (`domain`); -- -- Indizes für die Tabelle `autosearch` -- ALTER TABLE `autosearch` ADD PRIMARY KEY (`id`); -- -- Indizes für die Tabelle `counter_stats` -- ALTER TABLE `counter_stats` ADD PRIMARY KEY (`id`), ADD KEY `date` (`date`), ADD KEY `email` (`email`), ADD KEY `domain` (`domain`); -- -- Indizes für die Tabelle `customer_settings` -- ALTER TABLE `customer_settings` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `domain` (`domain`); -- -- Indizes für die Tabelle `deleted` -- ALTER TABLE `deleted` ADD UNIQUE KEY `id` (`id`), ADD KEY `id_2` (`id`), ADD KEY `deleted` (`deleted`); -- -- Indizes für die Tabelle `domain` -- ALTER TABLE `domain` ADD PRIMARY KEY (`domain`); -- -- Indizes für die Tabelle `domain_user` -- ALTER TABLE `domain_user` ADD KEY `domain_user_idx` (`domain`), ADD KEY `domain_user_idx2` (`uid`); -- -- Indizes für die Tabelle `email` -- ALTER TABLE `email` ADD PRIMARY KEY (`email`); -- -- Indizes für die Tabelle `email_groups` -- ALTER TABLE `email_groups` ADD UNIQUE KEY `uid` (`uid`,`gid`), ADD KEY `email_groups_idx` (`uid`,`gid`); -- -- Indizes für die Tabelle `folder` -- ALTER TABLE `folder` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `parent_id` (`parent_id`,`name`); -- -- Indizes für die Tabelle `folder_extra` -- ALTER TABLE `folder_extra` ADD UNIQUE KEY `uid` (`uid`,`name`), ADD KEY `id` (`id`); -- -- Indizes für die Tabelle `folder_message` -- ALTER TABLE `folder_message` ADD UNIQUE KEY `folder_id` (`folder_id`,`id`); -- -- Indizes für die Tabelle `folder_rule` -- ALTER TABLE `folder_rule` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `domain` (`domain`,`from`,`to`,`subject`,`body`,`_size`,`size`,`attachment_name`,`attachment_type`,`_attachment_size`,`attachment_size`,`spam`); -- -- Indizes für die Tabelle `folder_user` -- ALTER TABLE `folder_user` ADD KEY `folder_user_idx` (`id`), ADD KEY `folder_user_idx2` (`uid`); -- -- Indizes für die Tabelle `google` -- ALTER TABLE `google` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `email` (`email`); -- -- Indizes für die Tabelle `google_imap` -- ALTER TABLE `google_imap` ADD PRIMARY KEY (`id`), ADD KEY `email` (`email`); -- -- Indizes für die Tabelle `group_email` -- ALTER TABLE `group_email` ADD KEY `group_email_idx` (`id`); -- -- Indizes für die Tabelle `group_user` -- ALTER TABLE `group_user` ADD KEY `group_user_idx` (`id`), ADD KEY `group_user_idx2` (`email`); -- -- Indizes für die Tabelle `import` -- ALTER TABLE `import` ADD PRIMARY KEY (`id`); -- -- Indizes für die Tabelle `ldap` -- ALTER TABLE `ldap` ADD PRIMARY KEY (`id`); -- -- Indizes für die Tabelle `legal_hold` -- ALTER TABLE `legal_hold` ADD UNIQUE KEY `email` (`email`); -- -- Indizes für die Tabelle `metadata` -- ALTER TABLE `metadata` ADD PRIMARY KEY (`id`), ADD KEY `metadata_idx` (`piler_id`), ADD KEY `metadata_idx2` (`message_id`), ADD KEY `metadata_idx3` (`reference`), ADD KEY `metadata_idx4` (`bodydigest`), ADD KEY `metadata_idx5` (`deleted`), ADD KEY `metadata_idx6` (`arrived`), ADD KEY `metadata_idx7` (`retained`), ADD KEY `metadata_idx8` (`fromdomain`), ADD KEY `metadata_idx9` (`from`), ADD KEY `metadata_idx10` (`sent`); -- -- Indizes für die Tabelle `note` -- ALTER TABLE `note` ADD UNIQUE KEY `id` (`id`,`uid`), ADD KEY `_id` (`_id`); -- -- Indizes für die Tabelle `online` -- ALTER TABLE `online` ADD UNIQUE KEY `username` (`username`,`ipaddr`); -- -- Indizes für die Tabelle `private` -- ALTER TABLE `private` ADD UNIQUE KEY `id` (`id`), ADD KEY `id_2` (`id`); -- -- Indizes für die Tabelle `rcpt` -- ALTER TABLE `rcpt` ADD UNIQUE KEY `id` (`id`,`to`), ADD KEY `rcpt_idx` (`id`), ADD KEY `rcpt_idx2` (`to`), ADD KEY `rcpt_idx3` (`todomain`); -- -- Indizes für die Tabelle `remote` -- ALTER TABLE `remote` ADD PRIMARY KEY (`remotedomain`); -- -- Indizes für die Tabelle `retention_rule` -- ALTER TABLE `retention_rule` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `domain` (`domain`,`from`,`to`,`subject`,`body`,`_size`,`size`,`attachment_name`,`attachment_type`,`_attachment_size`,`attachment_size`,`spam`); -- -- Indizes für die Tabelle `search` -- ALTER TABLE `search` ADD KEY `search_idx` (`email`); -- -- Indizes für die Tabelle `sph_counter` -- ALTER TABLE `sph_counter` ADD PRIMARY KEY (`counter_id`); -- -- Indizes für die Tabelle `sph_index` -- ALTER TABLE `sph_index` ADD PRIMARY KEY (`id`); -- -- Indizes für die Tabelle `tag` -- ALTER TABLE `tag` ADD UNIQUE KEY `id` (`id`,`uid`), ADD KEY `_id` (`_id`); -- -- Indizes für die Tabelle `timestamp` -- ALTER TABLE `timestamp` ADD PRIMARY KEY (`id`); -- -- Indizes für die Tabelle `user` -- ALTER TABLE `user` ADD PRIMARY KEY (`uid`), ADD UNIQUE KEY `username` (`username`); -- -- Indizes für die Tabelle `usergroup` -- ALTER TABLE `usergroup` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `groupname` (`groupname`); -- -- Indizes für die Tabelle `user_settings` -- ALTER TABLE `user_settings` ADD UNIQUE KEY `username` (`username`), ADD KEY `user_settings_idx` (`username`); -- -- AUTO_INCREMENT für exportierte Tabellen -- -- -- AUTO_INCREMENT für Tabelle `archiving_rule` -- ALTER TABLE `archiving_rule` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `attachment` -- ALTER TABLE `attachment` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `audit` -- ALTER TABLE `audit` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `autosearch` -- ALTER TABLE `autosearch` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `counter_stats` -- ALTER TABLE `counter_stats` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `customer_settings` -- ALTER TABLE `customer_settings` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `folder` -- ALTER TABLE `folder` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `folder_extra` -- ALTER TABLE `folder_extra` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `folder_rule` -- ALTER TABLE `folder_rule` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `import` -- ALTER TABLE `import` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `ldap` -- ALTER TABLE `ldap` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `metadata` -- ALTER TABLE `metadata` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `note` -- ALTER TABLE `note` MODIFY `_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `retention_rule` -- ALTER TABLE `retention_rule` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `tag` -- ALTER TABLE `tag` MODIFY `_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `timestamp` -- ALTER TABLE `timestamp` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `usergroup` -- ALTER TABLE `usergroup` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
-- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/ -- -- Host: localhost -- Erstellungszeit: 04. Okt 2023 um 18:26 -- Server-Version: 10.3.37-MariaDB -- PHP-Version: 8.0.28 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Datenbank: `piler_apconsulting` -- -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `archiving_rule` -- CREATE TABLE `archiving_rule` ( `id` bigint(20) UNSIGNED NOT NULL, `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(11) DEFAULT 0, `attachment_name` varchar(128) DEFAULT NULL, `attachment_type` varchar(128) DEFAULT NULL, `_attachment_size` char(2) DEFAULT NULL, `attachment_size` int(11) DEFAULT 0, `spam` tinyint(1) DEFAULT -1, `days` int(11) DEFAULT 0, `folder_id` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `attachment` -- CREATE TABLE `attachment` ( `id` bigint(20) UNSIGNED NOT NULL, `piler_id` char(36) NOT NULL, `attachment_id` int(11) NOT NULL, `name` tinyblob DEFAULT NULL, `type` varchar(128) DEFAULT NULL, `sig` char(64) NOT NULL, `size` int(11) DEFAULT 0, `ptr` bigint(20) UNSIGNED DEFAULT 0, `deleted` tinyint(1) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `audit` -- CREATE TABLE `audit` ( `id` bigint(20) UNSIGNED NOT NULL, `ts` int(11) NOT NULL, `email` varchar(128) NOT NULL, `domain` varchar(128) NOT NULL, `action` int(11) NOT NULL, `ipaddr` char(15) NOT NULL, `meta_id` bigint(20) UNSIGNED NOT NULL, `description` varchar(255) DEFAULT NULL, `vcode` char(64) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `autosearch` -- CREATE TABLE `autosearch` ( `id` int(11) NOT NULL, `query` varchar(512) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `counter` -- CREATE TABLE `counter` ( `rcvd` bigint(20) UNSIGNED DEFAULT 0, `virus` bigint(20) UNSIGNED DEFAULT 0, `duplicate` bigint(20) UNSIGNED DEFAULT 0, `ignore` bigint(20) UNSIGNED DEFAULT 0, `size` bigint(20) UNSIGNED DEFAULT 0, `stored_size` bigint(20) UNSIGNED DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `counter_stats` -- CREATE TABLE `counter_stats` ( `id` int(11) NOT NULL, `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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `customer_settings` -- CREATE TABLE `customer_settings` ( `id` int(11) NOT NULL, `domain` varchar(255) NOT NULL, `branding_text` varchar(255) DEFAULT NULL, `branding_url` varchar(255) DEFAULT NULL, `branding_logo` varchar(255) DEFAULT NULL, `support_link` varchar(255) DEFAULT NULL, `background_colour` varchar(255) DEFAULT NULL, `text_colour` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `domain` -- CREATE TABLE `domain` ( `domain` char(64) NOT NULL, `mapped` char(64) NOT NULL, `ldap_id` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `domain_user` -- CREATE TABLE `domain_user` ( `domain` char(64) NOT NULL, `uid` int(10) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `email` -- CREATE TABLE `email` ( `uid` int(10) UNSIGNED NOT NULL, `email` char(128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `email_groups` -- CREATE TABLE `email_groups` ( `uid` int(10) UNSIGNED NOT NULL, `gid` int(10) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `folder` -- CREATE TABLE `folder` ( `id` int(11) NOT NULL, `parent_id` int(11) DEFAULT 0, `name` char(64) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `folder_extra` -- CREATE TABLE `folder_extra` ( `id` int(10) UNSIGNED NOT NULL, `uid` int(10) UNSIGNED NOT NULL, `name` char(64) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `folder_message` -- CREATE TABLE `folder_message` ( `folder_id` bigint(20) NOT NULL, `id` bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `folder_rule` -- CREATE TABLE `folder_rule` ( `id` bigint(20) UNSIGNED NOT NULL, `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(11) DEFAULT 0, `attachment_name` varchar(128) DEFAULT NULL, `attachment_type` varchar(128) DEFAULT NULL, `_attachment_size` char(2) DEFAULT NULL, `attachment_size` int(11) DEFAULT 0, `spam` tinyint(1) DEFAULT -1, `days` int(11) DEFAULT 0, `folder_id` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `folder_user` -- CREATE TABLE `folder_user` ( `id` bigint(20) UNSIGNED NOT NULL, `uid` int(10) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `google` -- CREATE TABLE `google` ( `id` char(32) NOT NULL, `email` char(128) NOT NULL, `access_token` char(255) DEFAULT NULL, `refresh_token` char(255) DEFAULT NULL, `created` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `google_imap` -- CREATE TABLE `google_imap` ( `id` char(32) NOT NULL, `email` char(128) NOT NULL, `last_msg_id` bigint(20) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `group` -- CREATE TABLE `group` ( `id` bigint(20) UNSIGNED NOT NULL, `groupname` char(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `group_email` -- CREATE TABLE `group_email` ( `id` bigint(20) UNSIGNED NOT NULL, `email` char(128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `group_user` -- CREATE TABLE `group_user` ( `id` bigint(20) UNSIGNED NOT NULL, `email` char(128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `import` -- CREATE TABLE `import` ( `id` int(11) NOT NULL, `type` varchar(255) NOT NULL, `username` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `server` varchar(255) NOT NULL, `created` int(11) DEFAULT 0, `started` int(11) DEFAULT 0, `finished` int(11) DEFAULT 0, `updated` int(11) DEFAULT 0, `status` int(11) DEFAULT 0, `total` int(11) DEFAULT 0, `imported` int(11) DEFAULT 0, `duplicate` int(11) DEFAULT 0, `error` int(11) DEFAULT 0, `cleared` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `ldap` -- CREATE TABLE `ldap` ( `id` int(11) NOT NULL, `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, `ldap_bind_pw` varchar(255) NOT NULL, `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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `legal_hold` -- CREATE TABLE `legal_hold` ( `email` varchar(128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `metadata` -- CREATE TABLE `metadata` ( `id` bigint(20) UNSIGNED NOT NULL, `from` varchar(255) NOT NULL, `fromdomain` varchar(255) NOT NULL, `subject` blob DEFAULT NULL, `spam` tinyint(1) DEFAULT 0, `arrived` int(10) UNSIGNED NOT NULL, `sent` int(10) UNSIGNED NOT NULL, `retained` int(10) UNSIGNED NOT NULL, `deleted` tinyint(1) DEFAULT 0, `size` int(11) DEFAULT 0, `hlen` int(11) DEFAULT 0, `direction` int(11) DEFAULT 0, `attachments` int(11) DEFAULT 0, `piler_id` char(36) NOT NULL, `message_id` varchar(255) NOT NULL, `reference` char(64) NOT NULL, `digest` char(64) NOT NULL, `bodydigest` char(64) NOT NULL, `vcode` char(64) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `note` -- CREATE TABLE `note` ( `_id` bigint(20) UNSIGNED NOT NULL, `id` bigint(20) UNSIGNED NOT NULL, `uid` int(11) NOT NULL, `note` mediumtext DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `online` -- CREATE TABLE `online` ( `username` varchar(255) NOT NULL, `ts` int(11) DEFAULT 0, `last_activity` int(11) DEFAULT 0, `ipaddr` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `option` -- CREATE TABLE `option` ( `key` char(64) NOT NULL, `value` char(32) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `private` -- CREATE TABLE `private` ( `id` bigint(20) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `rcpt` -- CREATE TABLE `rcpt` ( `id` bigint(20) UNSIGNED NOT NULL, `to` varchar(128) NOT NULL, `todomain` varchar(128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `remote` -- CREATE TABLE `remote` ( `remotedomain` char(64) NOT NULL, `remotehost` char(64) NOT NULL, `basedn` char(255) NOT NULL, `binddn` char(255) NOT NULL, `sitedescription` char(64) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `retention_rule` -- CREATE TABLE `retention_rule` ( `id` bigint(20) UNSIGNED NOT NULL, `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(11) DEFAULT 0, `attachment_name` varchar(128) DEFAULT NULL, `attachment_type` varchar(128) DEFAULT NULL, `_attachment_size` char(2) DEFAULT NULL, `attachment_size` int(11) DEFAULT 0, `spam` tinyint(1) DEFAULT -1, `days` int(11) DEFAULT 0, `folder_id` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `search` -- CREATE TABLE `search` ( `email` char(128) NOT NULL, `ts` int(11) DEFAULT 0, `term` mediumtext NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `sph_counter` -- CREATE TABLE `sph_counter` ( `counter_id` bigint(20) NOT NULL, `max_doc_id` bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `sph_index` -- CREATE TABLE `sph_index` ( `id` bigint(20) NOT NULL, `from` tinyblob DEFAULT NULL, `to` blob DEFAULT NULL, `fromdomain` tinyblob DEFAULT NULL, `todomain` blob DEFAULT NULL, `subject` blob DEFAULT NULL, `arrived` int(10) UNSIGNED NOT NULL, `sent` int(10) UNSIGNED NOT NULL, `body` mediumblob DEFAULT NULL, `size` int(11) DEFAULT 0, `direction` int(11) DEFAULT 0, `attachments` int(11) DEFAULT 0, `attachment_types` mediumtext DEFAULT NULL, `folder` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `tag` -- CREATE TABLE `tag` ( `_id` bigint(20) UNSIGNED NOT NULL, `id` bigint(20) NOT NULL, `uid` int(11) NOT NULL, `tag` char(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `timestamp` -- CREATE TABLE `timestamp` ( `id` bigint(20) UNSIGNED NOT NULL, `start_id` bigint(20) DEFAULT 0, `stop_id` bigint(20) DEFAULT 0, `hash_value` varchar(128) DEFAULT NULL, `count` int(11) DEFAULT 0, `response_time` bigint(20) DEFAULT 0, `response_string` blob NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `user` -- CREATE TABLE `user` ( `uid` int(10) UNSIGNED NOT NULL, `username` char(64) NOT NULL, `realname` char(64) DEFAULT NULL, `samaccountname` char(64) DEFAULT NULL, `password` char(128) DEFAULT NULL, `domain` char(64) DEFAULT NULL, `dn` char(255) DEFAULT '*', `isadmin` tinyint(4) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `user_settings` -- CREATE TABLE `user_settings` ( `username` char(64) NOT NULL, `pagelen` int(11) DEFAULT 20, `theme` char(8) DEFAULT 'default', `lang` char(2) DEFAULT NULL, `ga_enabled` int(11) DEFAULT 0, `ga_secret` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- -------------------------------------------------------- -- -- Stellvertreter-Struktur des Views `v_attachment` -- (Siehe unten für die tatsächliche Ansicht) -- CREATE TABLE `v_attachment` ( `i` bigint(20) unsigned ,`piler_id` char(36) ,`attachment_id` int(11) ,`ptr` bigint(20) unsigned ,`refcount` bigint(21) ); -- -------------------------------------------------------- -- -- Stellvertreter-Struktur des Views `v_messages` -- (Siehe unten für die tatsächliche Ansicht) -- CREATE TABLE `v_messages` ( `id` bigint(20) unsigned ,`piler_id` char(36) ,`from` varchar(255) ,`fromdomain` varchar(255) ,`to` varchar(128) ,`todomain` varchar(128) ,`subject` blob ,`size` int(11) ,`direction` int(11) ,`sent` int(10) unsigned ,`retained` int(10) unsigned ,`arrived` int(10) unsigned ,`digest` char(64) ,`bodydigest` char(64) ,`deleted` tinyint(1) ); -- -------------------------------------------------------- -- -- Struktur des Views `v_attachment` -- DROP TABLE IF EXISTS `v_attachment`; CREATE ALGORITHM=UNDEFINED DEFINER=`piler`@`%` SQL SECURITY DEFINER VIEW `v_attachment` AS SELECT `attachment`.`id` AS `i`, `attachment`.`piler_id` AS `piler_id`, `attachment`.`attachment_id` AS `attachment_id`, `attachment`.`ptr` AS `ptr`, (select count(0) from `attachment` where `attachment`.`ptr` = `i`) AS `refcount` FROM `attachment` ; -- -------------------------------------------------------- -- -- Struktur des Views `v_messages` -- DROP TABLE IF EXISTS `v_messages`; CREATE ALGORITHM=UNDEFINED DEFINER=`piler`@`%` SQL SECURITY DEFINER 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` ; -- -- Indizes der exportierten Tabellen -- -- -- Indizes für die Tabelle `archiving_rule` -- ALTER TABLE `archiving_rule` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `from` (`from`,`to`,`subject`,`body`,`_size`,`size`,`attachment_name`,`attachment_type`,`_attachment_size`,`attachment_size`,`spam`); -- -- Indizes für die Tabelle `attachment` -- ALTER TABLE `attachment` ADD PRIMARY KEY (`id`), ADD KEY `attachment_idx` (`piler_id`), ADD KEY `attachment_idx2` (`sig`,`size`,`ptr`), ADD KEY `attachment_idx3` (`ptr`); -- -- Indizes für die Tabelle `audit` -- ALTER TABLE `audit` ADD PRIMARY KEY (`id`), ADD KEY `audit_idx` (`email`), ADD KEY `audit_idx2` (`action`), ADD KEY `audit_idx3` (`ipaddr`), ADD KEY `audit_idx4` (`ts`), ADD KEY `audit_idx5` (`domain`); -- -- Indizes für die Tabelle `autosearch` -- ALTER TABLE `autosearch` ADD PRIMARY KEY (`id`); -- -- Indizes für die Tabelle `counter_stats` -- ALTER TABLE `counter_stats` ADD PRIMARY KEY (`id`), ADD KEY `date` (`date`), ADD KEY `email` (`email`), ADD KEY `domain` (`domain`); -- -- Indizes für die Tabelle `customer_settings` -- ALTER TABLE `customer_settings` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `domain` (`domain`); -- -- Indizes für die Tabelle `domain` -- ALTER TABLE `domain` ADD PRIMARY KEY (`domain`); -- -- Indizes für die Tabelle `domain_user` -- ALTER TABLE `domain_user` ADD KEY `domain_user_idx` (`domain`), ADD KEY `domain_user_idx2` (`uid`); -- -- Indizes für die Tabelle `email` -- ALTER TABLE `email` ADD PRIMARY KEY (`email`); -- -- Indizes für die Tabelle `email_groups` -- ALTER TABLE `email_groups` ADD UNIQUE KEY `uid` (`uid`,`gid`), ADD KEY `email_groups_idx` (`uid`,`gid`); -- -- Indizes für die Tabelle `folder` -- ALTER TABLE `folder` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `parent_id` (`parent_id`,`name`); -- -- Indizes für die Tabelle `folder_extra` -- ALTER TABLE `folder_extra` ADD UNIQUE KEY `uid` (`uid`,`name`), ADD KEY `id` (`id`); -- -- Indizes für die Tabelle `folder_message` -- ALTER TABLE `folder_message` ADD UNIQUE KEY `folder_id` (`folder_id`,`id`); -- -- Indizes für die Tabelle `folder_rule` -- ALTER TABLE `folder_rule` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `domain` (`domain`,`from`,`to`,`subject`,`body`,`_size`,`size`,`attachment_name`,`attachment_type`,`_attachment_size`,`attachment_size`,`spam`); -- -- Indizes für die Tabelle `folder_user` -- ALTER TABLE `folder_user` ADD KEY `folder_user_idx` (`id`), ADD KEY `folder_user_idx2` (`uid`); -- -- Indizes für die Tabelle `google` -- ALTER TABLE `google` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `email` (`email`); -- -- Indizes für die Tabelle `google_imap` -- ALTER TABLE `google_imap` ADD PRIMARY KEY (`id`), ADD KEY `email` (`email`); -- -- Indizes für die Tabelle `group` -- ALTER TABLE `group` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `groupname` (`groupname`); -- -- Indizes für die Tabelle `group_email` -- ALTER TABLE `group_email` ADD KEY `group_email_idx` (`id`); -- -- Indizes für die Tabelle `group_user` -- ALTER TABLE `group_user` ADD KEY `group_user_idx` (`id`), ADD KEY `group_user_idx2` (`email`); -- -- Indizes für die Tabelle `import` -- ALTER TABLE `import` ADD PRIMARY KEY (`id`); -- -- Indizes für die Tabelle `ldap` -- ALTER TABLE `ldap` ADD PRIMARY KEY (`id`); -- -- Indizes für die Tabelle `legal_hold` -- ALTER TABLE `legal_hold` ADD UNIQUE KEY `email` (`email`); -- -- Indizes für die Tabelle `metadata` -- ALTER TABLE `metadata` ADD PRIMARY KEY (`id`), ADD KEY `metadata_idx` (`piler_id`), ADD KEY `metadata_idx2` (`message_id`), ADD KEY `metadata_idx3` (`reference`), ADD KEY `metadata_idx4` (`bodydigest`), ADD KEY `metadata_idx5` (`deleted`), ADD KEY `metadata_idx6` (`arrived`), ADD KEY `metadata_idx7` (`retained`), ADD KEY `metadata_idx8` (`fromdomain`), ADD KEY `metadata_idx9` (`from`), ADD KEY `metadata_idx10` (`sent`); -- -- Indizes für die Tabelle `note` -- ALTER TABLE `note` ADD UNIQUE KEY `id` (`id`,`uid`), ADD KEY `_id` (`_id`); -- -- Indizes für die Tabelle `online` -- ALTER TABLE `online` ADD UNIQUE KEY `username` (`username`,`ipaddr`); -- -- Indizes für die Tabelle `private` -- ALTER TABLE `private` ADD UNIQUE KEY `id` (`id`), ADD KEY `id_2` (`id`); -- -- Indizes für die Tabelle `rcpt` -- ALTER TABLE `rcpt` ADD UNIQUE KEY `id` (`id`,`to`), ADD KEY `rcpt_idx` (`id`), ADD KEY `rcpt_idx2` (`to`), ADD KEY `rcpt_idx3` (`todomain`); -- -- Indizes für die Tabelle `remote` -- ALTER TABLE `remote` ADD PRIMARY KEY (`remotedomain`); -- -- Indizes für die Tabelle `retention_rule` -- ALTER TABLE `retention_rule` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `domain` (`domain`,`from`,`to`,`subject`,`body`,`_size`,`size`,`attachment_name`,`attachment_type`,`_attachment_size`,`attachment_size`,`spam`); -- -- Indizes für die Tabelle `search` -- ALTER TABLE `search` ADD KEY `search_idx` (`email`); -- -- Indizes für die Tabelle `sph_counter` -- ALTER TABLE `sph_counter` ADD PRIMARY KEY (`counter_id`); -- -- Indizes für die Tabelle `sph_index` -- ALTER TABLE `sph_index` ADD PRIMARY KEY (`id`); -- -- Indizes für die Tabelle `tag` -- ALTER TABLE `tag` ADD UNIQUE KEY `id` (`id`,`uid`), ADD KEY `_id` (`_id`); -- -- Indizes für die Tabelle `timestamp` -- ALTER TABLE `timestamp` ADD PRIMARY KEY (`id`); -- -- Indizes für die Tabelle `user` -- ALTER TABLE `user` ADD PRIMARY KEY (`uid`), ADD UNIQUE KEY `username` (`username`); -- -- Indizes für die Tabelle `user_settings` -- ALTER TABLE `user_settings` ADD UNIQUE KEY `username` (`username`), ADD KEY `user_settings_idx` (`username`); -- -- AUTO_INCREMENT für exportierte Tabellen -- -- -- AUTO_INCREMENT für Tabelle `archiving_rule` -- ALTER TABLE `archiving_rule` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `attachment` -- ALTER TABLE `attachment` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `audit` -- ALTER TABLE `audit` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `autosearch` -- ALTER TABLE `autosearch` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `counter_stats` -- ALTER TABLE `counter_stats` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `customer_settings` -- ALTER TABLE `customer_settings` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `folder` -- ALTER TABLE `folder` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `folder_extra` -- ALTER TABLE `folder_extra` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `folder_rule` -- ALTER TABLE `folder_rule` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `group` -- ALTER TABLE `group` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `import` -- ALTER TABLE `import` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `ldap` -- ALTER TABLE `ldap` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `metadata` -- ALTER TABLE `metadata` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `note` -- ALTER TABLE `note` MODIFY `_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `retention_rule` -- ALTER TABLE `retention_rule` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `tag` -- ALTER TABLE `tag` MODIFY `_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT für Tabelle `timestamp` -- ALTER TABLE `timestamp` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
