usr/share/doc/opendmarc/mkdb.mysql

   1 /*
   2 SQLyog Ultimate v11.0 (64 bit)
   3 MySQL - 5.7.15-0ubuntu0.16.04.1 : Database - opendmarc
   4 *********************************************************************
   5 */
   6
   7 -- MySQL command sequence to create a database to accumulate OpenDMARC
   8 -- report data
   9
  10 /*!40101 SET NAMES utf8 */;
  11
  12 /*!40101 SET SQL_MODE=''*/;
  13
  14 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  15 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  16 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  17 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  18 DROP DATABASE IF EXISTS opendmarc;
  19 CREATE DATABASE /*!32312 IF NOT EXISTS*/`opendmarc` /*!40100 DEFAULT CHARACTER SET latin1 */;
  20
  21 USE `opendmarc`;
  22
  23 -- table mapping domain names to id numbers
  24 /*Table structure for table `domains` */
  25 CREATE TABLE `domains` (
  26   `id` int(11) NOT NULL AUTO_INCREMENT,
  27   `name` varchar(255) NOT NULL,
  28   `firstseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  29   PRIMARY KEY (`id`),
  30   UNIQUE KEY `name` (`name`)
  31 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
  32
  33 -- table mapping IP addresses to id numbers
  34 /*Table structure for table `ipaddr` */
  35
  36 CREATE TABLE `ipaddr` (
  37   `id` int(11) NOT NULL AUTO_INCREMENT,
  38   `addr` varchar(64) NOT NULL,
  39   `firstseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  40   PRIMARY KEY (`id`),
  41   UNIQUE KEY `addr` (`addr`)
  42 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
  43
  44 -- table tracking message-specific data
  45 /*Table structure for table `messages` */
  46
  47 CREATE TABLE `messages` (
  48   `id` int(11) NOT NULL AUTO_INCREMENT,
  49   `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  50   `jobid` varchar(128) NOT NULL,
  51   `reporter` int(10) unsigned NOT NULL,
  52   `policy` tinyint(3) unsigned NOT NULL,
  53   `disp` tinyint(3) unsigned NOT NULL,
  54   `ip` int(10) unsigned NOT NULL,
  55   `env_domain` int(10) unsigned NOT NULL,
  56   `from_domain` int(10) unsigned NOT NULL,
  57   `policy_domain` int(10) unsigned DEFAULT NULL,
  58   `spf` tinyint(3) unsigned NOT NULL,
  59   `align_dkim` tinyint(3) unsigned NOT NULL,
  60   `align_spf` tinyint(3) unsigned NOT NULL,
  61   `sigcount` tinyint(3) unsigned NOT NULL,
  62   PRIMARY KEY (`id`),
  63   UNIQUE KEY `reporter` (`reporter`,`date`,`jobid`),
  64   KEY `date` (`date`)
  65 ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
  66
  67 -- table mapping reporters to ids
  68 /*Table structure for table `reporters` */
  69
  70 CREATE TABLE `reporters` (
  71   `id` int(11) NOT NULL AUTO_INCREMENT,
  72   `name` varchar(255) NOT NULL,
  73   `firstseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  74   PRIMARY KEY (`id`),
  75   UNIQUE KEY `name` (`name`)
  76 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
  77
  78 -- table tracking report requests
  79 /*Table structure for table `requests` */
  80
  81 CREATE TABLE `requests` (
  82   `id` int(11) NOT NULL AUTO_INCREMENT,
  83   `domain` int(11) NOT NULL,
  84   `repuri` varchar(255) DEFAULT NULL,
  85   `adkim` tinyint(4) DEFAULT NULL,
  86   `aspf` tinyint(4) DEFAULT NULL,
  87   `policy` tinyint(4) DEFAULT NULL,
  88   `spolicy` tinyint(4) DEFAULT NULL,
  89   `pct` tinyint(4) DEFAULT NULL,
  90   `locked` tinyint(4) NOT NULL DEFAULT '0',
  91   `firstseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  92   `lastsent` timestamp NOT NULL DEFAULT '1971-01-01 00:00:01',
  93   PRIMARY KEY (`id`),
  94   UNIQUE KEY `domain` (`domain`),
  95   KEY `lastsent` (`lastsent`)
  96 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
  97
  98 -- table for tracking DKIM signature
  99 /*Table structure for table `signatures` */
 100
 101 CREATE TABLE `signatures` (
 102   `id` int(11) NOT NULL AUTO_INCREMENT,
 103   `message` int(11) NOT NULL,
 104   `domain` int(11) NOT NULL,
 105   `pass` tinyint(4) NOT NULL,
 106   `error` tinyint(4) NOT NULL,
 107   PRIMARY KEY (`id`),
 108   KEY `message` (`message`)
 109 ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
 110
 111 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 112 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 113 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 114 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;