-- Create database for DMARC data

-- Copyright 2012, 2016, Taughannock Networks. All rights reserved.

-- Redistribution and use in source and binary forms, with or without
-- modification, are permitted provided that the following conditions
-- are met:

-- Redistributions of source code must retain the above copyright
-- notice, this list of conditions and the following disclaimer.

-- Redistributions in binary form must reproduce the above copyright
-- notice, this list of conditions and the following disclaimer in the
-- documentation and/or other materials provided with the distribution.

-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
-- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
-- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
-- A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
-- HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
-- INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
-- BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
-- OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
-- AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
-- LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY
-- WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
-- POSSIBILITY OF SUCH DAMAGE.

USE dmarc

CREATE TABLE report (
  serial int(10) unsigned NOT NULL AUTO_INCREMENT,
  mindate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  maxdate timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  domain varchar(255) NOT NULL,
  org varchar(255) NOT NULL,
  reportid varchar(255) NOT NULL,
  email varchar(255),
  extra_contact_info varchar(255),
  policy_adkim varchar(20),
  policy_aspf varchar(20),
  policy_p varchar(20),
  policy_sp varchar(20),
  policy_pct tinyint unsigned,
  PRIMARY KEY (serial),
  UNIQUE KEY domain (domain,reportid)
);

-- Use these commands to change the old IPv4 only DMARC table to the new one
/***
alter table rptrecord modify ip int(10) unsigned;
alter table rptrecord add column ip6 binary(16) after ip;
alter table rptrecord add key serial6(serial,ip6);
***/

-- Use these commands to load in the optional IPv6 formatting functions
/***
CREATE FUNCTION inet_6top RETURNS STRING SONAME 'mysql_ip6.so';
CREATE FUNCTION inet_pto6 RETURNS STRING SONAME 'mysql_ip6.so';
***/

CREATE TABLE rptrecord (
  serial int(10) unsigned NOT NULL,
  ip int(10) unsigned,
  ip6 binary(16),
  rcount int(10) unsigned NOT NULL,
  disposition enum('none','quarantine','reject'),
  reason varchar(255),
  dkimdomain varchar(255),
  dkimresult enum('none','pass','fail','neutral','policy','temperror','permerror'),
  spfdomain varchar(255),
  spfresult enum('none','neutral','pass','fail','softfail','temperror','permerror'),
  spf_align enum('fail', 'pass'),
  dkim_align enum('fail', 'pass'),
  identifier_hfrom varchar(255),
  KEY serial (serial,ip),
  KEY serial6 (serial,ip6)
) ENGINE=MyISAM;

CREATE TABLE failure (
  serial int(10) unsigned NOT NULL AUTO_INCREMENT,
  org varchar(255) NOT NULL, -- reported-domain
  bouncedomain varchar(255), -- MAIL FROM bouncebox@bouncedomain
  bouncebox varchar(255),
  fromdomain varchar(255), -- From: frombox@fromdomain
  frombox varchar(255),
  arrival TIMESTAMP,
  sourceip int unsigned, -- inet_aton(source-ip)
  sourceip6 BINARY(16), -- inet_6top(source-ip)
  headers TEXT,
  authres TEXT,
  PRIMARY KEY(serial),
  KEY(sourceip),
  KEY(fromdomain),
  KEY(bouncedomain)
) charset=utf8;

GRANT all on dmarc.* to dmarc identified by 'xxx';
GRANT all on dmarc.* to dmarc@localhost identified by 'xxx';