usr/share/doc/opendmarc/mkdb.mysql

   1 -- Copyright (c) 2013, The Trusted Domain Project.  All rights reserved.
   2
   3 -- MySQL command sequence to create a database to accumulate OpenDMARC
   4 -- report data
   5
   6 -- table mapping domain names to id numbers
   7 CREATE TABLE domains (
   8         id INT(11) NOT NULL AUTO_INCREMENT,
   9         name VARCHAR(255) NOT NULL,
  10         firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  11         PRIMARY KEY(id),
  12         UNIQUE KEY(name)
  13 ) ENGINE=innodb DEFAULT CHARSET=latin1;
  14
  15 -- table mapping IP addresses to id numbers
  16 CREATE TABLE ipaddr (
  17         id INT(11) NOT NULL AUTO_INCREMENT,
  18         addr VARCHAR(64) DEFAULT NULL,
  19         firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  20         PRIMARY KEY(id),
  21         UNIQUE KEY(addr)
  22 ) ENGINE=innodb DEFAULT CHARSET=latin1;
  23
  24 -- table tracking message-specific data
  25 CREATE TABLE messages (
  26         id INT(11) NOT NULL AUTO_INCREMENT,
  27         date TIMESTAMP NOT NULL default CURRENT_TIMESTAMP,
  28         jobid VARCHAR(128) NOT NULL,
  29         reporter INT(10) UNSIGNED NOT NULL,
  30         ip INT(10) UNSIGNED NOT NULL,
  31         policy TINYINT(3) UNSIGNED NOT NULL,
  32         disp TINYINT(3) UNSIGNED NOT NULL,
  33         from_domain INT(10) UNSIGNED NOT NULL,
  34         env_domain INT(10) UNSIGNED NOT NULL,
  35         policy_domain INT(10) UNSIGNED NOT NULL,
  36         sigcount TINYINT(3) UNSIGNED NOT NULL,
  37         spf TINYINT(3) NOT NULL,
  38         align_spf TINYINT(3) UNSIGNED NOT NULL,
  39         align_dkim TINYINT(3) UNSIGNED NOT NULL,
  40         PRIMARY KEY(id),
  41         UNIQUE KEY(reporter,date,jobid)
  42 ) ENGINE=innodb DEFAULT CHARSET=latin1;
  43
  44 -- table mapping reporters to ids
  45 CREATE TABLE reporters (
  46         id INT(11) NOT NULL AUTO_INCREMENT,
  47         name VARCHAR(255) NOT NULL,
  48         firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  49         PRIMARY KEY(id),
  50         UNIQUE KEY(name)
  51 ) ENGINE=innodb DEFAULT CHARSET=latin1;
  52
  53 -- table tracking report requests
  54 CREATE TABLE requests (
  55         id INT(11) NOT NULL AUTO_INCREMENT,
  56         domain INT(11) NOT NULL,
  57         repuri VARCHAR(255) NOT NULL,
  58         pct TINYINT(4) NOT NULL,
  59         policy TINYINT(4) NOT NULL,
  60         spolicy TINYINT(4) NOT NULL,
  61         aspf TINYINT(4) NOT NULL,
  62         adkim TINYINT(4) NOT NULL,
  63         locked TINYINT(4) NOT NULL DEFAULT '0',
  64         firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  65         lastsent TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  66         PRIMARY KEY(id)
  67 ) ENGINE=innodb DEFAULT CHARSET=latin1;
  68
  69 -- table for tracking DKIM signature evaluation results
  70 CREATE TABLE signatures (
  71         id INT(11) NOT NULL AUTO_INCREMENT,
  72         message INT(11) NOT NULL,
  73         domain INT(11) NOT NULL,
  74         pass TINYINT(4) NOT NULL,
  75         error TINYINT(4) NOT NULL,
  76         PRIMARY KEY(id)
  77 ) ENGINE=innodb DEFAULT CHARSET=latin1;