--
-- $Id: simple-schema-v1final.sql,v 1.6 2005/03/31 19:12:21 sharding Exp $

CREATE TABLE switches ( switchid SERIAL PRIMARY KEY,
                        switch_name VARCHAR UNIQUE NOT NULL );
-- Index implicitly created on UNIQUE column switch_name

CREATE TABLE port_status ( statusid SERIAL PRIMARY KEY,
			   descr VARCHAR UNIQUE NOT NULL );

CREATE TABLE switch_ports ( portid SERIAL PRIMARY KEY,
                            switch INTEGER REFERENCES switches,
                            slot INTEGER NOT NULL,
                            port INTEGER NOT NULL,
                            descr VARCHAR,
                            wall_jack VARCHAR(50),
			    status INTEGER REFERENCES port_status );
CREATE INDEX i_swports_switch ON switch_ports(switch);
CREATE INDEX i_swports_jack ON switch_ports(wall_jack);
CREATE INDEX i_swports_status ON switch_ports(status);

-- external_entity_id is intended to give us some flexibility in
-- connecting with other databases (like Peoplesoft). It may not 
-- help in the end, but it's worth a shot.
CREATE TABLE contacts ( contactid SERIAL PRIMARY KEY,
                        contact_name VARCHAR,
                        email_addr VARCHAR,
                        phone_num VARCHAR,
                        external_entity_id VARCHAR );

CREATE TABLE organizations ( orgid SERIAL PRIMARY KEY,
                             org_name VARCHAR UNIQUE NOT NULL,
                             contact INTEGER REFERENCES contacts );
CREATE INDEX i_orgs_contact ON organizations(contact);
-- Index implicitly created on UNIQUE column org_name

CREATE TABLE operating_systems ( osid SERIAL PRIMARY KEY,
                                 vendor INTEGER REFERENCES organizations,
                                 os_name VARCHAR UNIQUE NOT NULL );
CREATE INDEX i_os_vendor ON operating_systems(vendor);
-- Index implicitly created on UNIQUE column os_name

CREATE TABLE os_versions ( versionid SERIAL PRIMARY KEY,
                           os INTEGER REFERENCES operating_systems,
                           version VARCHAR NOT NULL,
                           revision VARCHAR,
                           version_name VARCHAR UNIQUE NOT NULL);
CREATE INDEX i_osv_os on os_versions(os);
-- Index implicitly created on UNIQUE column version_name

-- os_hostname is for OS-specific names like NetBIOS names for Windows boxes
-- and Appleshare or Rendezvous names in MacOS. DNS hostname is the DNS name of
-- that box *at the time the data was collected*. We could just do a reverse 
-- lookup of the IP whenever we want that info, but I'd prefer to have the 
-- database reflect the info as it was when the data was collected, rather
-- than as it was when the data is queried.
CREATE TABLE hosts ( hostid SERIAL PRIMARY KEY,
		     mac MACADDR UNIQUE NOT NULL,
		     ip_addr INET,
		     vlan INTEGER,
		     os_hostname VARCHAR,
                     dns_hostname VARCHAR,
		     current_os_user VARCHAR,
                     switch_port INTEGER REFERENCES switch_ports,
		     wireless_ap VARCHAR,
		     wireless_network VARCHAR,
                     osver INTEGER REFERENCES os_versions,
                     descr VARCHAR,
		     snmp_sys_descr VARCHAR,
		     snmp_sys_objectid VARCHAR,
                     owning_org INTEGER REFERENCES organizations,
	             ad_ou VARCHAR,
                     first_seen TIMESTAMP,
                     last_seen TIMESTAMP,
                     last_updated TIMESTAMP );
CREATE INDEX i_hosts_ipaddr ON hosts(ip_addr);
CREATE INDEX i_hosts_swport ON hosts(switch_port);
CREATE INDEX i_hosts_osver ON hosts(osver);
CREATE INDEX i_hosts_ownorg ON hosts(owning_org);
-- Index implicitly created on UNIQUE column mac

CREATE TABLE vulnerabilities ( vulnid SERIAL PRIMARY KEY,
                               vuln_name VARCHAR UNIQUE NOT NULL,
                               descr VARCHAR,
                               severity INTEGER,
                               more_info_ref VARCHAR,
                               osver INTEGER REFERENCES os_versions,
                               discovered_time TIMESTAMP );
CREATE INDEX i_vuln_osver ON vulnerabilities(osver);
-- Indices implicitly created on UNIQUE columns vuln_name and ext_vuln_id

CREATE TABLE host_vulnerabilities ( host INTEGER REFERENCES hosts,
                                    vuln INTEGER REFERENCES vulnerabilities,
                                    discovered_time TIMESTAMP,
                                    corrected_time TIMESTAMP,
                                    last_checked_time TIMESTAMP,
                                    PRIMARY KEY (host, vuln) );