101 lines
4.8 KiB
SQL
101 lines
4.8 KiB
SQL
-- Adminer 4.8.1 MySQL 10.11.6-MariaDB-0+deb12u1 dump
|
|
|
|
SET NAMES utf8;
|
|
SET time_zone = '+00:00';
|
|
SET foreign_key_checks = 0;
|
|
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
|
|
|
|
SET NAMES utf8mb4;
|
|
|
|
CREATE TABLE `oauth` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`access_token` varchar(4096) DEFAULT NULL,
|
|
`refresh_token` varchar(1024) DEFAULT NULL,
|
|
`expire` timestamp NOT NULL,
|
|
`valid` int(10) unsigned NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
|
|
CREATE TABLE `report` (
|
|
`serial` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`mindate` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
`maxdate` timestamp NULL DEFAULT NULL,
|
|
`domain` varchar(255) NOT NULL,
|
|
`org` varchar(255) NOT NULL,
|
|
`reportid` varchar(255) NOT NULL,
|
|
`email` varchar(255) DEFAULT NULL,
|
|
`extra_contact_info` varchar(255) DEFAULT NULL,
|
|
`policy_adkim` varchar(20) DEFAULT NULL,
|
|
`policy_aspf` varchar(20) DEFAULT NULL,
|
|
`policy_p` varchar(20) DEFAULT NULL,
|
|
`policy_sp` varchar(20) DEFAULT NULL,
|
|
`policy_pct` tinyint(3) unsigned DEFAULT NULL,
|
|
`raw_xml` mediumtext DEFAULT NULL,
|
|
PRIMARY KEY (`serial`),
|
|
UNIQUE KEY `domain` (`domain`,`reportid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED;
|
|
|
|
|
|
CREATE TABLE `report_stats` (`serial` int(10) unsigned, `domain` varchar(255), `rcount` int(10) unsigned, `disposition` enum('none','quarantine','reject','unknown'), `reason` varchar(255), `policy_p` varchar(20), `policy_pct` tinyint(3) unsigned, `dkimdomain` varchar(255), `dkimresult` enum('none','pass','fail','neutral','policy','temperror','permerror','unknown'), `dkim_align` enum('fail','pass','unknown'), `spfdomain` varchar(255), `spfresult` enum('none','neutral','pass','fail','softfail','temperror','permerror','unknown'), `spf_align` enum('fail','pass','unknown'), `mindate` timestamp, `maxdate` timestamp);
|
|
|
|
|
|
CREATE TABLE `rptrecord` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`serial` int(10) unsigned NOT NULL,
|
|
`ip` int(10) unsigned DEFAULT NULL,
|
|
`ip6` binary(16) DEFAULT NULL,
|
|
`rcount` int(10) unsigned NOT NULL,
|
|
`disposition` enum('none','quarantine','reject','unknown') DEFAULT NULL,
|
|
`reason` varchar(255) DEFAULT NULL,
|
|
`dkimdomain` varchar(255) DEFAULT NULL,
|
|
`dkimresult` enum('none','pass','fail','neutral','policy','temperror','permerror','unknown') DEFAULT NULL,
|
|
`spfdomain` varchar(255) DEFAULT NULL,
|
|
`spfresult` enum('none','neutral','pass','fail','softfail','temperror','permerror','unknown') DEFAULT NULL,
|
|
`spf_align` enum('fail','pass','unknown') NOT NULL,
|
|
`dkim_align` enum('fail','pass','unknown') NOT NULL,
|
|
`identifier_hfrom` varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `serial` (`serial`,`ip`),
|
|
KEY `serial6` (`serial`,`ip6`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
|
|
CREATE TABLE `tls` (
|
|
`serial` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`mindate` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
`maxdate` timestamp NULL DEFAULT NULL,
|
|
`domain` varchar(255) NOT NULL,
|
|
`org` varchar(255) NOT NULL,
|
|
`reportid` varchar(255) NOT NULL,
|
|
`email` varchar(255) DEFAULT NULL,
|
|
`policy_mode` varchar(20) DEFAULT NULL,
|
|
`summary_success` int(11) DEFAULT NULL,
|
|
`summary_failure` int(11) DEFAULT NULL,
|
|
`raw_json` mediumtext DEFAULT NULL,
|
|
PRIMARY KEY (`serial`),
|
|
UNIQUE KEY `domain` (`domain`,`reportid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED;
|
|
|
|
|
|
CREATE TABLE `tlsrecord` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`serial` int(10) unsigned NOT NULL,
|
|
`send_ip` int(10) unsigned DEFAULT NULL,
|
|
`send_ip6` binary(16) DEFAULT NULL,
|
|
`recv_ip` int(10) unsigned DEFAULT NULL,
|
|
`recv_ip6` binary(16) DEFAULT NULL,
|
|
`recv_mx` varchar(255) DEFAULT NULL,
|
|
`type` varchar(255) DEFAULT NULL,
|
|
`count` int(10) unsigned NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `serial` (`serial`,`send_ip`),
|
|
KEY `serial6` (`serial`,`send_ip6`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
|
|
DROP TABLE IF EXISTS `report_stats`;
|
|
CREATE ALGORITHM=UNDEFINED DEFINER=`dmarc`@`%` SQL SECURITY DEFINER VIEW `report_stats` AS (select `report`.`serial` AS `serial`,`report`.`domain` AS `domain`,`rptrecord`.`rcount` AS `rcount`,`rptrecord`.`disposition` AS `disposition`,`rptrecord`.`reason` AS `reason`,`report`.`policy_p` AS `policy_p`,`report`.`policy_pct` AS `policy_pct`,`rptrecord`.`dkimdomain` AS `dkimdomain`,`rptrecord`.`dkimresult` AS `dkimresult`,`rptrecord`.`dkim_align` AS `dkim_align`,`rptrecord`.`spfdomain` AS `spfdomain`,`rptrecord`.`spfresult` AS `spfresult`,`rptrecord`.`spf_align` AS `spf_align`,`report`.`mindate` AS `mindate`,`report`.`maxdate` AS `maxdate` from (`rptrecord` left join `report` on(`report`.`serial` = `rptrecord`.`serial`)));
|
|
|
|
-- 2024-03-25 17:18:03
|