--- 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 */;
projects / opendmarc.git / blobdiff