mkdb.mysql

-- MySQL command sequence to create a database to accumulate OpenDKIM
-- statistics reports

-- table of reporters
create table if not exists reporters (
	id int unsigned not null auto_increment,
	name varchar(255) not null,
	firstseen timestamp not null default CURRENT_TIMESTAMP,
        enabled tinyint not null default 1,

primary key(id),
	unique key(name)
) engine=innodb default charset=latin1;

-- table of domain names we've seen
create table if not exists domains (
	id int unsigned not null auto_increment,
	name varchar(255) not null,
	firstseen timestamp not null default CURRENT_TIMESTAMP,

primary key(id),
	unique key(name)
) engine=innodb default charset=latin1;

-- table of IP addresses we've seen
create table if not exists ipaddrs (
	id int unsigned not null auto_increment,
	addr varchar(64) not null,
	firstseen timestamp not null default CURRENT_TIMESTAMP,

primary key(id),
	unique key(addr)
) engine=innodb default charset=latin1;

-- table of messages names we've seen
create table if not exists messages (
	id int unsigned not null auto_increment,
	jobid varchar(64) not null,
	reporter int unsigned not null,
	from_domain int unsigned not null,
	ip int unsigned not null,
	msgtime timestamp not null default CURRENT_TIMESTAMP,
	size int unsigned not null,
	sigcount int unsigned not null,
	spam tinyint not null default -1,
	atps tinyint not null default -1,

primary key(id),
	key(reporter),
	key(from_domain),
	key(ip),
	key(msgtime),
	unique key(reporter, jobid, msgtime),
	foreign key(from_domain) references domains(id) on delete cascade,
	foreign key(ip) references ipaddrs(id) on delete cascade,
	foreign key(reporter) references reporters(id) on delete cascade
) engine=innodb default charset=latin1;

-- table of signatures we've seen
create table if not exists signatures (
	id int unsigned not null auto_increment,
	message int unsigned not null,
	domain int unsigned not null,
	pass tinyint unsigned not null,
	fail_body tinyint unsigned not null,
	siglength int not null,
	sigerror tinyint unsigned not null,
	dnssec tinyint not null,

primary key(id),
	key(domain),
	foreign key(message) references messages(id) on delete cascade,
	foreign key(domain) references domains(id) on delete cascade
) engine=innodb default charset=latin1;

-- table mapping signature error codes to descriptions
create table if not exists sigerrorcodes (
	id tinyint not null,
	name varchar(255) not null,

primary key (id)
) engine=innodb default charset=latin1;

insert ignore into sigerrorcodes (id, name) values
	(-1,	"unknown error"),
	(0,	"no error"),
	(1,	"unsupported version"),
	(2,	"invalid domain (d=/i=)"),
	(3,	"signature expired"),
	(4,	"signature in the future"),
	(5,	"x= < t="),
	(6,	"c= missing"),
	(7,	"c= invalid (header)"),
	(8,	"c= invalid (body)"),
	(9,	"a= missing"),
	(10,	"a= invalid"),
	(11,	"h= missing"),
	(12,	"l= invalid"),
	(13,	"q= invalid"),
	(14,	"q= option invalid"),
	(15,	"d= missing"),
	(16,	"d= empty"),
	(17,	"s= missing"),
	(18,	"s= empty"),
	(19,	"b= missing"),
	(20,	"b= empty"),
	(21,	"b= corrupt"),
	(22,	"no key found in DNS"),
	(23,	"DNS reply corrupt"),
	(24,	"DNS query failed"),
	(25,	"bh= missing"),
	(26,	"bh= empty"),
	(27,	"bh= corrupt"),
	(28,	"signature mismatch"),
	(29,	"unauthorized subdomain"),
	(30,	"multiple records returned"),
	(31,	"h= empty"),
	(32,	"h= missing required entries"),
	(33,	"l= value exceeds body size"),
	(34,	"field-must-be-signed failure"),
	(35,	"unknown key version"),
	(36,	"unknown key hash"),
	(37,	"signature-key hash mismatch"),
	(38,	"not an email key"),
	(39,	"key granularity mismatch"),
	(40,	"key type missing"),
	(41,	"key type unknown"),
	(42,	"key revoked"),
	(43,	"key could not be decoded"),
	(44,	"v= missing"),
	(45,	"v= empty")
;

docroot: /var/www/469ed2f6-4d4b-4a8a-9f5e-ee4ef04d3bc8/public_html

curdir: /usr/share/doc/opendkim-tools

	.	4096
	..	36864
X	README.stats.gz	4406
X	changelog.Debian.gz	3149
X	copyright	9948
X	mkdb.mysql	3840