chef.sqlite.schema

drop table if exists `sightings`;
drop table if exists `servers`;
drop table if exists `combinations`;
drop table if exists `ips`;
drop table if exists `names`;

create table `names` (
	`id` integer primary key,
	`name` text not null unique
);

create table `ips` (
	`ip` integer not null primary key
);

create table `combinations` (
	`id` integer primary key,
	`ip` integer references `ips`(`ip`),
	`name` integer not null references `names`(`id`),
	unique (`ip`, `name`)
);

create index idx_comb_name on `combinations`(`name`);
create index idx_comb_ip on `combinations`(`ip`);

create table `servers` (
	`id` integer primary key,
	`ip` text not null,
	`port` integer not null,
	`description` text not null,
	`mod` text,
	`last_seen` integer not null default (strftime('%s', 'now')),
	unique (`ip`, `port`)
);

create index idx_srv_descr on `servers`(`description`);

create table `sightings` (
	`combination` integer not null references `combinations`(`id`),
	`server` integer not null references `servers`(`id`),
	`timestamp` integer not null default (strftime('%s', 'now')),
	unique (`combination`, `timestamp`)
);