1-- $Id: schema.sql 589 2008-12-12 14:13:45Z calle $ 2 3-- DNSCheck Primary Data 4 5CREATE TABLE IF NOT EXISTS `messages` ( 6 `id` int(10) unsigned NOT NULL auto_increment, 7 `tag` varchar(255) NOT NULL default '', 8 `arguments` tinyint(3) unsigned NOT NULL default 0, 9 `language` varchar(16) NOT NULL default 'en-US', 10 `formatstring` varchar(255) default NULL, 11 `description` text default NULL, 12 PRIMARY KEY (`id`), 13 UNIQUE KEY `langtag` (`tag`,`language`) 14) ENGINE=InnoDB DEFAULT CHARSET=utf8; 15 16-- source_id should reference an entry in the source table. 17-- source_data is some piece of data private to a particular source. 18-- It will be copied to the tests table by the dispatcher. 19-- fake_parent_glue gives necessary data to run tests on undelegated 20-- domains. The content of the field must be nameserver specifikations 21-- separated by spaces. Each nameserver is either simply a name, which 22-- will be looked up in DNS as usual, or a name, a slash and an IP 23-- address. Example: "ns.example.com ns2.example.com/127.0.0.2" 24 25CREATE TABLE IF NOT EXISTS `queue` ( 26 `id` int(10) unsigned NOT NULL auto_increment, 27 `domain` varchar(255) default NULL, 28 `priority` tinyint(3) unsigned NOT NULL default '0', 29 `inprogress` datetime default NULL, 30 `tester_pid` int(10) unsigned NULL, 31 `source_id` int(10) unsigned NULL, 32 `source_data` varchar(255) NULL, 33 `fake_parent_glue` text NULL, 34 PRIMARY KEY (`id`) 35) ENGINE=InnoDB DEFAULT CHARSET=ascii; 36 37CREATE TABLE IF NOT EXISTS `tests` ( 38 `id` int(10) unsigned NOT NULL auto_increment, 39 `domain` varchar(255) NOT NULL default '', 40 `begin` datetime default NULL, 41 `end` datetime default NULL, 42 `count_critical` int(10) unsigned default '0', 43 `count_error` int(10) unsigned default '0', 44 `count_warning` int(10) unsigned default '0', 45 `count_notice` int(10) unsigned default '0', 46 `count_info` int(10) unsigned default '0', 47 `source_id` int(10) unsigned NULL, 48 `source_data` varchar(255) NULL, 49 PRIMARY KEY (`id`) 50) ENGINE=InnoDB DEFAULT CHARSET=ascii; 51 52CREATE TABLE IF NOT EXISTS `results` ( 53 `id` int(10) unsigned NOT NULL auto_increment, 54 `test_id` int(10) unsigned NOT NULL, 55 `line` int(10) unsigned NOT NULL, 56 `module_id` int(10) unsigned NOT NULL, 57 `parent_module_id` int(10) unsigned NOT NULL, 58 `timestamp` datetime default NULL, 59 `level` varchar(16) default NULL, 60 `message` varchar(255) NOT NULL default '', 61 `arg0` varchar(255) default NULL, 62 `arg1` varchar(255) default NULL, 63 `arg2` varchar(255) default NULL, 64 `arg3` varchar(255) default NULL, 65 `arg4` varchar(255) default NULL, 66 `arg5` varchar(255) default NULL, 67 `arg6` varchar(255) default NULL, 68 `arg7` varchar(255) default NULL, 69 `arg8` varchar(255) default NULL, 70 `arg9` varchar(255) default NULL, 71 PRIMARY KEY (`id`), 72 CONSTRAINT `tests` FOREIGN KEY (`test_id`) REFERENCES `tests` (`id`) ON DELETE CASCADE 73) ENGINE=InnoDB DEFAULT CHARSET=ascii; 74 75 76-- Name Service Providers 77 78CREATE TABLE IF NOT EXISTS `nameservers` ( 79 `id` int(10) unsigned NOT NULL auto_increment, 80 `nsp_id` int(10) unsigned NULL, 81 `nameserver` varchar(255) UNIQUE NOT NULL default '', 82 PRIMARY KEY (`id`) 83) ENGINE=InnoDB DEFAULT CHARSET=ascii; 84 85CREATE TABLE IF NOT EXISTS `nsp` ( 86 `id` int(10) unsigned NOT NULL auto_increment, 87 `name` varchar(255) default '', 88 `email` varchar(255) default '', 89 PRIMARY KEY (`id`) 90) ENGINE=InnoDB DEFAULT CHARSET=utf8; 91 92 93-- Domains and History 94 95CREATE TABLE IF NOT EXISTS `domains` ( 96 `id` int(10) unsigned NOT NULL auto_increment, 97 `domain` varchar(255) NOT NULL default '', 98 `last_test` datetime default NULL, 99 PRIMARY KEY (`id`), 100 UNIQUE KEY (`domain`) 101) ENGINE=InnoDB DEFAULT CHARSET=ascii; 102 103CREATE TABLE IF NOT EXISTS `delegation_history` ( 104 `id` int(10) unsigned NOT NULL auto_increment, 105 `domain` varchar(255) NOT NULL default '', 106 `nameserver` varchar(255) NOT NULL default '', 107 PRIMARY KEY (`id`), 108 UNIQUE KEY (`domain`,`nameserver`) 109) ENGINE=InnoDB DEFAULT CHARSET=ascii; 110 111-- Source is supposed to be a list of all sources requesting tests. 112-- The recommended procedure is that a program that wants to add 113-- tests adds its name and possible some contact information to this table, 114-- checks what id number it got and then uses that number when inserting 115-- into the queue table and selecting from the tests table. 116-- 117-- The easiest way for a source to use this, is to do an INSERT IGNORE of a 118-- string unique for that source and then SELECT the id for that string. 119-- For most sources, this need only be done once on startup and then the 120-- numeric id can be used to insert into the queue or select from tests. 121 122CREATE TABLE IF NOT EXISTS `source` ( 123 `id` int(10) unsigned NOT NULL auto_increment, 124 `name` varchar(255) NOT NULL, 125 `contact` varchar(255), 126 PRIMARY KEY (`id`), 127 UNIQUE KEY (`name`) 128 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;