1# ************************************************************ 2# Sequel Pro SQL dump 3# Version 4096 4# 5# http://www.sequelpro.com/ 6# http://code.google.com/p/sequel-pro/ 7# 8# Host: 127.0.0.2 (MySQL 5.5.30) 9# Database: mail_dmarc 10# Generation Time: 2013-05-17 07:47:45 +0000 11# ************************************************************ 12 13 14/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 15/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 16/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 17/*!40101 SET NAMES utf8 */; 18/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 19/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 20/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 21 22 23# Dump of table author 24# ------------------------------------------------------------ 25 26DROP TABLE IF EXISTS `author`; 27 28CREATE TABLE `author` ( 29 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 30 `org_name` varchar(253) CHARACTER SET ascii NOT NULL DEFAULT '', 31 `email` varchar(255) CHARACTER SET ascii DEFAULT NULL, 32 `extra_contact` varchar(255) CHARACTER SET ascii DEFAULT NULL, 33 PRIMARY KEY (`id`) 34) ENGINE=InnoDB DEFAULT CHARSET=utf8; 35 36 37 38# Dump of table domain 39# ------------------------------------------------------------ 40 41DROP TABLE IF EXISTS `domain`; 42 43CREATE TABLE `domain` ( 44 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 45 `domain` varchar(253) CHARACTER SET ascii NOT NULL DEFAULT '', 46 PRIMARY KEY (`id`), 47 UNIQUE KEY `domain` (`domain`) 48) ENGINE=InnoDB DEFAULT CHARSET=utf8; 49 50 51DROP TABLE IF EXISTS `report_error`; 52 53CREATE TABLE `report_error` ( 54 `report_id` int(11) unsigned NOT NULL, 55 `error` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', 56 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 57 KEY `report_id` (`report_id`), 58 CONSTRAINT `report_error_ibfk_1` FOREIGN KEY (`report_id`) REFERENCES `report` (`id`) ON DELETE CASCADE ON UPDATE CASCADE 59) ENGINE=InnoDB DEFAULT CHARSET=utf8; 60 61 62# Dump of table fk_disposition 63# ------------------------------------------------------------ 64 65DROP TABLE IF EXISTS `fk_disposition`; 66 67CREATE TABLE `fk_disposition` ( 68 `disposition` varchar(10) NOT NULL DEFAULT '', 69 PRIMARY KEY (`disposition`) 70) ENGINE=InnoDB DEFAULT CHARSET=ascii; 71 72LOCK TABLES `fk_disposition` WRITE; 73/*!40000 ALTER TABLE `fk_disposition` DISABLE KEYS */; 74 75INSERT INTO `fk_disposition` (`disposition`) 76VALUES 77 ('none'), 78 ('quarantine'), 79 ('reject'); 80 81/*!40000 ALTER TABLE `fk_disposition` ENABLE KEYS */; 82UNLOCK TABLES; 83 84 85# Dump of table fk_disposition_reason 86# ------------------------------------------------------------ 87 88DROP TABLE IF EXISTS `fk_disposition_reason`; 89 90CREATE TABLE `fk_disposition_reason` ( 91 `type` varchar(24) NOT NULL DEFAULT '', 92 PRIMARY KEY (`type`) 93) ENGINE=InnoDB DEFAULT CHARSET=ascii; 94 95LOCK TABLES `fk_disposition_reason` WRITE; 96/*!40000 ALTER TABLE `fk_disposition_reason` DISABLE KEYS */; 97 98INSERT INTO `fk_disposition_reason` (`type`) 99VALUES 100 ('forwarded'), 101 ('local_policy'), 102 ('mailing_list'), 103 ('other'), 104 ('sampled_out'), 105 ('trusted_forwarder'); 106 107/*!40000 ALTER TABLE `fk_disposition_reason` ENABLE KEYS */; 108UNLOCK TABLES; 109 110 111# Dump of table fk_dkim_result 112# ------------------------------------------------------------ 113 114DROP TABLE IF EXISTS `fk_dkim_result`; 115 116CREATE TABLE `fk_dkim_result` ( 117 `result` varchar(9) NOT NULL DEFAULT '', 118 PRIMARY KEY (`result`) 119) ENGINE=InnoDB DEFAULT CHARSET=ascii; 120 121LOCK TABLES `fk_dkim_result` WRITE; 122/*!40000 ALTER TABLE `fk_dkim_result` DISABLE KEYS */; 123 124INSERT INTO `fk_dkim_result` (`result`) 125VALUES 126 ('fail'), 127 ('neutral'), 128 ('none'), 129 ('pass'), 130 ('permerror'), 131 ('policy'), 132 ('temperror'); 133 134/*!40000 ALTER TABLE `fk_dkim_result` ENABLE KEYS */; 135UNLOCK TABLES; 136 137 138# Dump of table fk_spf_result 139# ------------------------------------------------------------ 140 141DROP TABLE IF EXISTS `fk_spf_result`; 142 143CREATE TABLE `fk_spf_result` ( 144 `result` varchar(9) NOT NULL DEFAULT '', 145 PRIMARY KEY (`result`) 146) ENGINE=InnoDB DEFAULT CHARSET=ascii; 147 148LOCK TABLES `fk_spf_result` WRITE; 149/*!40000 ALTER TABLE `fk_spf_result` DISABLE KEYS */; 150 151INSERT INTO `fk_spf_result` (`result`) 152VALUES 153 ('fail'), 154 ('neutral'), 155 ('none'), 156 ('pass'), 157 ('permerror'), 158 ('softfail'), 159 ('temperror'); 160 161/*!40000 ALTER TABLE `fk_spf_result` ENABLE KEYS */; 162UNLOCK TABLES; 163 164 165# Dump of table fk_spf_scope 166# ------------------------------------------------------------ 167 168DROP TABLE IF EXISTS `fk_spf_scope`; 169 170CREATE TABLE `fk_spf_scope` ( 171 `scope` varchar(5) NOT NULL DEFAULT '', 172 PRIMARY KEY (`scope`) 173) ENGINE=InnoDB DEFAULT CHARSET=ascii; 174 175LOCK TABLES `fk_spf_scope` WRITE; 176/*!40000 ALTER TABLE `fk_spf_scope` DISABLE KEYS */; 177 178INSERT INTO `fk_spf_scope` (`scope`) 179VALUES 180 ('helo'), 181 ('mfrom'); 182 183/*!40000 ALTER TABLE `fk_spf_scope` ENABLE KEYS */; 184UNLOCK TABLES; 185 186 187# Dump of table report 188# ------------------------------------------------------------ 189 190DROP TABLE IF EXISTS `report`; 191 192CREATE TABLE `report` ( 193 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 194 `begin` int(11) unsigned NOT NULL, 195 `end` int(11) unsigned NOT NULL, 196 `author_id` int(11) unsigned NOT NULL, 197 `rcpt_domain_id` int(11) unsigned DEFAULT NULL, 198 `from_domain_id` int(11) unsigned NOT NULL, 199 `uuid` varchar(253) DEFAULT NULL, 200 PRIMARY KEY (`id`), 201 KEY `author_id` (`author_id`), 202 KEY `from_domain_id` (`from_domain_id`), 203 CONSTRAINT `report_ibfk_3` FOREIGN KEY (`from_domain_id`) REFERENCES `domain` (`id`) ON UPDATE CASCADE, 204 CONSTRAINT `report_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE 205) ENGINE=InnoDB DEFAULT CHARSET=ascii; 206 207 208# Dump of table report_policy_published 209# ------------------------------------------------------------ 210 211DROP TABLE IF EXISTS `report_policy_published`; 212 213CREATE TABLE `report_policy_published` ( 214 `report_id` int(11) unsigned NOT NULL, 215 `adkim` char(1) DEFAULT NULL, 216 `aspf` char(1) DEFAULT NULL, 217 `p` varchar(10) DEFAULT NULL, 218 `sp` varchar(10) DEFAULT NULL, 219 `pct` tinyint(1) unsigned DEFAULT NULL, 220 `rua` varchar(255) DEFAULT NULL, 221 KEY `report_id` (`report_id`), 222 CONSTRAINT `report_policy_published_ibfk_1` FOREIGN KEY (`report_id`) REFERENCES `report` (`id`) ON DELETE CASCADE ON UPDATE CASCADE 223) ENGINE=InnoDB DEFAULT CHARSET=ascii; 224 225 226 227# Dump of table report_record 228# ------------------------------------------------------------ 229 230DROP TABLE IF EXISTS `report_record`; 231 232CREATE TABLE `report_record` ( 233 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 234 `report_id` int(11) unsigned NOT NULL, 235 `source_ip` varbinary(16) NOT NULL DEFAULT '', 236 `count` int(11) unsigned DEFAULT NULL, 237 `disposition` varchar(10) NOT NULL DEFAULT '', 238 `dkim` char(4) NOT NULL DEFAULT '', 239 `spf` char(4) NOT NULL DEFAULT '', 240 `envelope_to_did` int(11) unsigned DEFAULT NULL, 241 `envelope_from_did` int(11) unsigned DEFAULT NULL, 242 `header_from_did` int(11) unsigned NOT NULL, 243 PRIMARY KEY (`id`), 244 KEY `report_id` (`report_id`), 245 KEY `disposition` (`disposition`), 246 CONSTRAINT `report_record_ibfk_2` FOREIGN KEY (`disposition`) REFERENCES `fk_disposition` (`disposition`) ON DELETE CASCADE ON UPDATE CASCADE, 247 CONSTRAINT `report_record_ibfk_1` FOREIGN KEY (`report_id`) REFERENCES `report` (`id`) ON DELETE CASCADE ON UPDATE CASCADE 248) ENGINE=InnoDB DEFAULT CHARSET=ascii; 249 250 251 252# Dump of table report_record_reason 253# ------------------------------------------------------------ 254 255DROP TABLE IF EXISTS `report_record_reason`; 256 257CREATE TABLE `report_record_reason` ( 258 `report_record_id` int(11) unsigned NOT NULL, 259 `type` varchar(24) NOT NULL DEFAULT '', 260 `comment` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 261 KEY `report_record_id` (`report_record_id`), 262 KEY `type` (`type`), 263 CONSTRAINT `report_record_reason_ibfk_3` FOREIGN KEY (`report_record_id`) REFERENCES `report_record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, 264 CONSTRAINT `report_record_reason_ibfk_4` FOREIGN KEY (`type`) REFERENCES `fk_disposition_reason` (`type`) ON DELETE NO ACTION ON UPDATE CASCADE 265) ENGINE=InnoDB DEFAULT CHARSET=ascii; 266 267 268 269# Dump of table report_record_dkim 270# ------------------------------------------------------------ 271 272DROP TABLE IF EXISTS `report_record_dkim`; 273 274CREATE TABLE `report_record_dkim` ( 275 `report_record_id` int(11) unsigned NOT NULL, 276 `domain_id` int(11) unsigned NOT NULL, 277 `selector` varchar(253) DEFAULT NULL, 278 `result` varchar(9) NOT NULL DEFAULT '', 279 `human_result` varchar(64) DEFAULT NULL, 280 KEY `report_record_id` (`report_record_id`), 281 KEY `result` (`result`), 282 CONSTRAINT `report_record_dkim_ibfk_2` FOREIGN KEY (`result`) REFERENCES `fk_dkim_result` (`result`), 283 CONSTRAINT `report_record_dkim_ibfk_1` FOREIGN KEY (`report_record_id`) REFERENCES `report_record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE 284) ENGINE=InnoDB DEFAULT CHARSET=ascii; 285 286 287 288# Dump of table report_record_spf 289# ------------------------------------------------------------ 290 291DROP TABLE IF EXISTS `report_record_spf`; 292 293CREATE TABLE `report_record_spf` ( 294 `report_record_id` int(11) unsigned NOT NULL, 295 `domain_id` int(11) unsigned NOT NULL, 296 `scope` varchar(5) DEFAULT NULL, 297 `result` varchar(9) NOT NULL DEFAULT '', 298 KEY `report_record_id` (`report_record_id`), 299 KEY `scope` (`scope`), 300 KEY `result` (`result`), 301 CONSTRAINT `report_record_spf_ibfk_1` FOREIGN KEY (`report_record_id`) REFERENCES `report_record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, 302 CONSTRAINT `report_record_spf_ibfk_2` FOREIGN KEY (`scope`) REFERENCES `fk_spf_scope` (`scope`) ON DELETE CASCADE ON UPDATE CASCADE, 303 CONSTRAINT `report_record_spf_ibfk_3` FOREIGN KEY (`result`) REFERENCES `fk_spf_result` (`result`) ON DELETE CASCADE ON UPDATE CASCADE 304) ENGINE=InnoDB DEFAULT CHARSET=ascii; 305 306 307 308 309/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 310/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; 311/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 312/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; 313/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; 314/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; 315