drop table nodes;
create table nodes (
mac_addr varchar(17) PRIMARY KEY,
switch varchar,
vlan varchar,
module int,
port int,
ip_addr varchar,
last_updated timestamp
);
grant select on nodes to "www-data";
create index ip_addr_idx on nodes (ip_addr);
create index swport_idx on nodes (switch, module, port);



drop table switch_ports;
create table switch_ports (
switch varchar,
module int,
port int,
description varchar,
inOctets int8,
adminStatus int,
adminSpeed int,
vlan int,
last_input_increment timestamp,
CONSTRAINT uniq_port PRIMARY KEY (switch,module,port)
);
grant select on switch_ports to "www-data";

drop table devices;

CREATE TABLE devices (
name varchar primary key,
address varchar,
snmp_ro_string varchar,
snmp_rw_string varchar,
cached_snmp_string varchar,
last_contacted timestamp,
object_id varchar,
cdp_id varchar,
status int,
ignore_ports varchar,
error varchar
);
grant all on devices to "www-data";


drop table device_states;
CREATE TABLE device_states (
id int primary key,
name varchar unique
);
grant select on device_states to "www-data";
INSERT INTO device_states VALUES(1,'active');
INSERT INTO device_states VALUES(2,'discovered');
INSERT INTO device_states VALUES(3,'ignored');
INSERT INTO device_states VALUES(4,'error');

drop table cisco_products;
CREATE TABLE cisco_products(
snmp_oid varchar unique,
description varchar
);
grant select on cisco_products to "www-data";


drop table config;
CREATE TABLE config (
name varchar primary key,
value varchar
);
grant all on config to "www-data";
INSERT INTO "config" VALUES ('snmp_string1','');
INSERT INTO "config" VALUES ('node_collector_threads','');
INSERT INTO "config" VALUES ('swport_collector_threads','');
INSERT INTO "config" VALUES ('cdp_initial_state', 'dns');
INSERT INTO "config" VALUES ('cdp_ignore_ports', '');

drop table device_page;
CREATE TABLE device_page (
name varchar primary key,
query varchar
);
INSERT INTO "device_page" VALUES ('All Devices','');
INSERT INTO "device_page" VALUES ('Devices with errors', 'status=error,discovered' );