X-Git-Url: https://git.njae.me.uk/?a=blobdiff_plain;f=usr%2Fshare%2Fdoc%2Fopendmarc%2Fmkdb.mysql;h=05962b9ef925eb50763271b75d99aa7cb3feff49;hb=da92e3b4e53441f5c960812a29c68126af733459;hp=83f5de5dd0ea848f899885d9ba811226d2c60afe;hpb=256870664833badf02ba69cb474a5efecb5c5fbb;p=opendmarc.git diff --git a/usr/share/doc/opendmarc/mkdb.mysql b/usr/share/doc/opendmarc/mkdb.mysql index 83f5de5..05962b9 100644 --- a/usr/share/doc/opendmarc/mkdb.mysql +++ b/usr/share/doc/opendmarc/mkdb.mysql @@ -1,77 +1,114 @@ --- Copyright (c) 2013, The Trusted Domain Project. All rights reserved. +/* +SQLyog Ultimate v11.0 (64 bit) +MySQL - 5.7.15-0ubuntu0.16.04.1 : Database - opendmarc +********************************************************************* +*/ -- MySQL command sequence to create a database to accumulate OpenDMARC -- report data +/*!40101 SET NAMES utf8 */; + +/*!40101 SET SQL_MODE=''*/; + +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP DATABASE IF EXISTS opendmarc; +CREATE DATABASE /*!32312 IF NOT EXISTS*/`opendmarc` /*!40100 DEFAULT CHARACTER SET latin1 */; + +USE `opendmarc`; + -- table mapping domain names to id numbers -CREATE TABLE domains ( - id INT(11) NOT NULL AUTO_INCREMENT, - name VARCHAR(255) NOT NULL, - firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY(id), - UNIQUE KEY(name) -) ENGINE=innodb DEFAULT CHARSET=latin1; +/*Table structure for table `domains` */ +CREATE TABLE `domains` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + `firstseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) +) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; -- table mapping IP addresses to id numbers -CREATE TABLE ipaddr ( - id INT(11) NOT NULL AUTO_INCREMENT, - addr VARCHAR(64) DEFAULT NULL, - firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY(id), - UNIQUE KEY(addr) -) ENGINE=innodb DEFAULT CHARSET=latin1; +/*Table structure for table `ipaddr` */ + +CREATE TABLE `ipaddr` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `addr` varchar(64) NOT NULL, + `firstseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `addr` (`addr`) +) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1; -- table tracking message-specific data -CREATE TABLE messages ( - id INT(11) NOT NULL AUTO_INCREMENT, - date TIMESTAMP NOT NULL default CURRENT_TIMESTAMP, - jobid VARCHAR(128) NOT NULL, - reporter INT(10) UNSIGNED NOT NULL, - ip INT(10) UNSIGNED NOT NULL, - policy TINYINT(3) UNSIGNED NOT NULL, - disp TINYINT(3) UNSIGNED NOT NULL, - from_domain INT(10) UNSIGNED NOT NULL, - env_domain INT(10) UNSIGNED NOT NULL, - policy_domain INT(10) UNSIGNED NOT NULL, - sigcount TINYINT(3) UNSIGNED NOT NULL, - spf TINYINT(3) NOT NULL, - align_spf TINYINT(3) UNSIGNED NOT NULL, - align_dkim TINYINT(3) UNSIGNED NOT NULL, - PRIMARY KEY(id), - UNIQUE KEY(reporter,date,jobid) -) ENGINE=innodb DEFAULT CHARSET=latin1; +/*Table structure for table `messages` */ + +CREATE TABLE `messages` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `jobid` varchar(128) NOT NULL, + `reporter` int(10) unsigned NOT NULL, + `policy` tinyint(3) unsigned NOT NULL, + `disp` tinyint(3) unsigned NOT NULL, + `ip` int(10) unsigned NOT NULL, + `env_domain` int(10) unsigned NOT NULL, + `from_domain` int(10) unsigned NOT NULL, + `policy_domain` int(10) unsigned DEFAULT NULL, + `spf` tinyint(3) unsigned NOT NULL, + `align_dkim` tinyint(3) unsigned NOT NULL, + `align_spf` tinyint(3) unsigned NOT NULL, + `sigcount` tinyint(3) unsigned NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `reporter` (`reporter`,`date`,`jobid`), + KEY `date` (`date`) +) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1; -- table mapping reporters to ids -CREATE TABLE reporters ( - id INT(11) NOT NULL AUTO_INCREMENT, - name VARCHAR(255) NOT NULL, - firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY(id), - UNIQUE KEY(name) -) ENGINE=innodb DEFAULT CHARSET=latin1; +/*Table structure for table `reporters` */ + +CREATE TABLE `reporters` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + `firstseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) +) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- table tracking report requests -CREATE TABLE requests ( - id INT(11) NOT NULL AUTO_INCREMENT, - domain INT(11) NOT NULL, - repuri VARCHAR(255) NOT NULL, - pct TINYINT(4) NOT NULL, - policy TINYINT(4) NOT NULL, - spolicy TINYINT(4) NOT NULL, - aspf TINYINT(4) NOT NULL, - adkim TINYINT(4) NOT NULL, - locked TINYINT(4) NOT NULL DEFAULT '0', - firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - lastsent TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', - PRIMARY KEY(id) -) ENGINE=innodb DEFAULT CHARSET=latin1; - --- table for tracking DKIM signature evaluation results -CREATE TABLE signatures ( - id INT(11) NOT NULL AUTO_INCREMENT, - message INT(11) NOT NULL, - domain INT(11) NOT NULL, - pass TINYINT(4) NOT NULL, - error TINYINT(4) NOT NULL, - PRIMARY KEY(id) -) ENGINE=innodb DEFAULT CHARSET=latin1; +/*Table structure for table `requests` */ + +CREATE TABLE `requests` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `domain` int(11) NOT NULL, + `repuri` varchar(255) DEFAULT NULL, + `adkim` tinyint(4) DEFAULT NULL, + `aspf` tinyint(4) DEFAULT NULL, + `policy` tinyint(4) DEFAULT NULL, + `spolicy` tinyint(4) DEFAULT NULL, + `pct` tinyint(4) DEFAULT NULL, + `locked` tinyint(4) NOT NULL DEFAULT '0', + `firstseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `lastsent` timestamp NOT NULL DEFAULT '1971-01-01 00:00:01', + PRIMARY KEY (`id`), + UNIQUE KEY `domain` (`domain`), + KEY `lastsent` (`lastsent`) +) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; + +-- table for tracking DKIM signature +/*Table structure for table `signatures` */ + +CREATE TABLE `signatures` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `message` int(11) NOT NULL, + `domain` int(11) NOT NULL, + `pass` tinyint(4) NOT NULL, + `error` tinyint(4) NOT NULL, + PRIMARY KEY (`id`), + KEY `message` (`message`) +) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;