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;