-- Install database with:  psql template1 < tables.sql

CREATE DATABASE netmap;
\connect netmap



-- All rows in all tables are history-tracked.  They all have an
-- activation and deactivation date.  When the machine is in service,
-- the deactivation date is NULL.  When looking at the current layout,
-- always exclude objects: WHERE date_deactivated IS NOT NULL

CREATE TABLE objects (
	date_activated		TIMESTAMP NOT NULL,
	date_deactivated	TIMESTAMP DEFAULT NULL
);
CREATE INDEX obj_active_idx ON objects (date_deactivated);



-- Owners should be the primary point of contact for a network or
-- host.  Other contacts in the escalation path can be noted in in the
-- hosts's notes column.

CREATE TABLE owners (
	first_name		CHAR(32),
	last_name		CHAR(32),
	email			CHAR(64) PRIMARY KEY,
	pager			CHAR(64),
	phone			CHAR(20),
	wireless_phone		CHAR(20)
) INHERITS (objects);



-- The network table primarily validates and groups hosts.

CREATE TABLE networks (
	network			CIDR PRIMARY KEY,
	owner			CHAR(64) REFERENCES owners
					ON UPDATE CASCADE,
	purpose			TEXT
) INHERITS (objects);



-- The purpose of each host should be breifly described so that it can
-- be retired or updated as appropriate in the future.

CREATE TABLE hosts (
	network			CIDR REFERENCES networks,
	host			INET PRIMARY KEY,
	hostname		CHAR(64),
	owner			CHAR(64) REFERENCES owners
					ON UPDATE CASCADE,
	purpose			TEXT
) INHERITS (objects);



-- It is important to list *all* of the services on each host.  This
-- will allow the network admins to search for likely security
-- breeches when advisories are issued.

CREATE TABLE services (
	host			INET REFERENCES hosts
					ON UPDATE CASCADE,
	port			INTEGER,
	daemon			CHAR(32),
	version			CHAR(16),
	patchlevel		CHAR(16)
) INHERITS (objects);
CREATE INDEX services_host_idx ON services (host);