mdmaug-installation.sql 2.67 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

CREATE DATABASE `mdmaug` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;
USE `mdmaug`;

DROP TABLE IF EXISTS `export`;
CREATE TABLE `export` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
14
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Dates of realized Encounter export';
15 16 17 18 19 20 21 22 23 24 25 26 27 28 29


DROP TABLE IF EXISTS `status`;
CREATE TABLE `status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='IP status';

INSERT INTO `status` (`id`, `name`) VALUES
(0,	'n/a'),
(1,	'allow'),
(2,	'log'),
(3,	'block');

30 31
DROP TABLE IF EXISTS `encounter`;
CREATE TABLE `encounter` (
32 33 34
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When we encountered the IP on a page.',
  `status` int(11) NOT NULL DEFAULT '0' COMMENT 'n/a, allow, log, block',
35
  `host` varchar(255) COLLATE utf8_bin NOT NULL,
36 37
  `ip` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `port` int(11) NOT NULL,
38
  `origin` varchar(255) COLLATE utf8_bin NOT NULL,
39 40
  PRIMARY KEY (`id`),
  KEY `status` (`status`),
41
  CONSTRAINT `encounter_ibfk_1` FOREIGN KEY (`status`) REFERENCES `status` (`id`) ON UPDATE CASCADE
42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


DROP TABLE IF EXISTS `whitelist`;
CREATE TABLE `whitelist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain` varchar(255) COLLATE utf8_bin NOT NULL COMMENT '2nd level domain',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'when whitelisted',
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='2nd domains that are whitelisted automatically.';

INSERT INTO `whitelist` (`id`, `domain`, `timestamp`) VALUES
(26,	'mozilla.org',	'2015-02-05 19:21:15'),
(27,	'mozilla.net',	'2015-02-05 19:21:22'),
(28,	'digicert.com',	'2015-02-05 19:21:30'),
(29,	'google.com',	'2015-02-05 19:21:36'),
(30,	'google-analytics.com',	'2015-02-05 19:23:16'),
(31,	'googlesyndication.com',	'2015-02-05 19:24:00'),
(32,	'gstatic.com',	'2015-02-05 19:25:45'),
(33,	'google.cz',	'2015-04-27 11:11:10'),
(36,	'w3.org',	'2015-05-07 14:38:05'),
(37,	'googleapis.com',	'2015-05-07 14:48:41'),
(38,	'mozilla.com',	'2015-05-07 14:55:38'),
(39,	'doubleclick.net',	'2015-05-07 17:48:20'),
(41,	'cloudfront.net',	'2015-05-10 18:57:10'),
(42,	'googleadservices.com',	'2015-06-11 15:23:12');

-- 2017-04-07 05:06:19