manage.sql

Network Administration Visualized 网络管理可视化源码

/*
=============================================
        manage
    SQL Initialization script for NAV's
    manage database.  Read the README file
    for more info.
    
    Run the command:
    psql manage -f manage.sql
    
	!! WARNING !!

	This SQL script is encoded as unicode (UTF-8), before you do make
	changes and commit, be 100% sure that your editor does not mess it up.
    
    Check 1 : These norwegian letters looks nice:
    ! æøåÆØÅ !
    Check 2 : This is the Euro currency sign: 
    ! € !
=============================================
*/

-- This table has possibly gone unused since NAV 2
CREATE TABLE status (
  statusid SERIAL PRIMARY KEY,
  trapsource VARCHAR NOT NULL,
  trap VARCHAR NOT NULL,
  trapdescr VARCHAR,
  tilstandsfull CHAR(1) CHECK (tilstandsfull='Y' OR tilstandsfull='N') NOT NULL,
  boksid INT2,
  fra TIMESTAMP NOT NULL,
  til TIMESTAMP
);

CREATE TABLE org (
  orgid VARCHAR(30) PRIMARY KEY,
  parent VARCHAR(30) REFERENCES org (orgid),
  descr VARCHAR,
  opt1 VARCHAR,
  opt2 VARCHAR,
  opt3 VARCHAR
);


CREATE TABLE usage (
  usageid VARCHAR(30) PRIMARY KEY,
  descr VARCHAR NOT NULL
);


CREATE TABLE location (
  locationid VARCHAR(30) PRIMARY KEY,
  descr VARCHAR NOT NULL
);

CREATE TABLE room (
  roomid VARCHAR(30) PRIMARY KEY,
  locationid VARCHAR(30) REFERENCES location,
  descr VARCHAR,
  opt1 VARCHAR,
  opt2 VARCHAR,
  opt3 VARCHAR,
  opt4 VARCHAR
);

CREATE TABLE nettype (
  nettypeid VARCHAR PRIMARY KEY,
  descr VARCHAR,
  edit BOOLEAN DEFAULT FALSE
);
INSERT INTO nettype (nettypeid,descr,edit) VALUES ('core','core',FALSE);
INSERT INTO nettype (nettypeid,descr,edit) VALUES ('dummy','dummy',FALSE);
INSERT INTO nettype (nettypeid,descr,edit) VALUES ('elink','elink',FALSE);
INSERT INTO nettype (nettypeid,descr,edit) VALUES ('lan','lan',FALSE);
INSERT INTO nettype (nettypeid,descr,edit) VALUES ('link','link',FALSE);
INSERT INTO nettype (nettypeid,descr,edit) VALUES ('loopback','loopbcak',FALSE);
INSERT INTO nettype (nettypeid,descr,edit) VALUES ('reserved','reserved',TRUE);
INSERT INTO nettype (nettypeid,descr,edit) VALUES ('private','private',FALSE);
INSERT INTO nettype (nettypeid,descr,edit) VALUES ('scope','scope',TRUE);
INSERT INTO nettype (nettypeid,descr,edit) VALUES ('static','static',FALSE);
INSERT INTO nettype (nettypeid,descr,edit) VALUES ('unknown','unknow',FALSE);

CREATE TABLE vlan (
  vlanid SERIAL PRIMARY KEY,
  vlan INT4,
  nettype VARCHAR NOT NULL REFERENCES nettype(nettypeid) ON UPDATE CASCADE ON DELETE CASCADE,
  orgid VARCHAR(30) REFERENCES org,
  usageid VARCHAR(30) REFERENCES usage,
  netident VARCHAR,
  description VARCHAR
);  
CREATE INDEX vlan_vlan_btree ON vlan USING btree (vlan);

CREATE TABLE prefix (
  prefixid SERIAL PRIMARY KEY,
  netaddr CIDR NOT NULL,
  vlanid INT4 REFERENCES vlan ON UPDATE CASCADE ON DELETE CASCADE,
  UNIQUE(netaddr)
);
CREATE INDEX prefix_vlanid_btree ON prefix USING btree (vlanid);

CREATE TABLE vendor (
  vendorid VARCHAR(15) PRIMARY KEY
);

CREATE TABLE cat (
  catid VARCHAR(8) PRIMARY KEY,
  descr VARCHAR,
  req_snmp BOOLEAN NOT NULL
);

INSERT INTO cat values ('GW','Routers (layer 3 device)','t');
INSERT INTO cat values ('GSW','A layer 2 and layer 3 device','t');
INSERT INTO cat values ('SW','Core switches (layer 2), typically with many vlans','t');
INSERT INTO cat values ('EDGE','Edge switch without vlans (layer 2)','t');
INSERT INTO cat values ('WLAN','Wireless equipment','t');
INSERT INTO cat values ('SRV','Server','f');
INSERT INTO cat values ('OTHER','Other equipment','f');

CREATE TABLE product (
  productid SERIAL PRIMARY KEY,
  vendorid VARCHAR(15) NOT NULL REFERENCES vendor ON UPDATE CASCADE ON DELETE CASCADE,
  productno VARCHAR NOT NULL,
  descr VARCHAR,
  UNIQUE (vendorid,productno)
);

CREATE TABLE deviceorder (
  deviceorderid SERIAL PRIMARY KEY,
  registered TIMESTAMP NOT NULL DEFAULT now(),
  ordered DATE,
  arrived TIMESTAMP DEFAULT 'infinity',
  ordernumber VARCHAR,
  comment VARCHAR,
  retailer VARCHAR,
  username VARCHAR,
  orgid VARCHAR(30) REFERENCES org (orgid) ON UPDATE CASCADE ON DELETE SET NULL,
  productid INTEGER REFERENCES product (productid) ON UPDATE CASCADE ON DELETE SET NULL,
  updatedby VARCHAR,
  lastupdated DATE);


CREATE TABLE device (
  deviceid SERIAL PRIMARY KEY,
  productid INT4 REFERENCES product ON UPDATE CASCADE ON DELETE SET NULL,
  serial VARCHAR,
  hw_ver VARCHAR,
  fw_ver VARCHAR,
  sw_ver VARCHAR,
	auto BOOLEAN NOT NULL DEFAULT false,
  active BOOLEAN NOT NULL DEFAULT false,
  deviceorderid INT4 REFERENCES deviceorder (deviceorderid) ON DELETE CASCADE,
  discovered TIMESTAMP NULL DEFAULT NOW(),
  UNIQUE(serial)
);

CREATE TABLE type (
  typeid SERIAL PRIMARY KEY,
  vendorid VARCHAR(15) NOT NULL REFERENCES vendor ON UPDATE CASCADE ON DELETE CASCADE,
  typename VARCHAR NOT NULL,
  sysObjectID VARCHAR NOT NULL,
  cdp BOOL DEFAULT false,
  tftp BOOL DEFAULT false,
  cs_at_vlan BOOL,
  chassis BOOL NOT NULL DEFAULT true,
  frequency INT4,
  descr VARCHAR,
  UNIQUE (vendorid, typename),
  UNIQUE (sysObjectID)
);

CREATE TABLE snmpoid (
  snmpoidid SERIAL PRIMARY KEY,
  oidkey VARCHAR NOT NULL,
  snmpoid VARCHAR NOT NULL,
  oidsource VARCHAR,
  getnext BOOLEAN NOT NULL DEFAULT true,
  decodehex BOOLEAN NOT NULL DEFAULT false,
  match_regex VARCHAR,
  defaultfreq INT4 NOT NULL DEFAULT 21600,
  uptodate BOOLEAN NOT NULL DEFAULT false,
  descr VARCHAR,
  oidname VARCHAR,
  mib VARCHAR,
  UNIQUE(oidkey)
);

CREATE TABLE netbox (
  netboxid SERIAL PRIMARY KEY,
  ip INET NOT NULL,
  roomid VARCHAR(30) NOT NULL CONSTRAINT netbox_room_fkey REFERENCES room ON UPDATE CASCADE,
  typeid INT4 CONSTRAINT netbox_type_fkey REFERENCES type ON UPDATE CASCADE ON DELETE CASCADE,
  deviceid INT4 NOT NULL CONSTRAINT netbox_device_fkey REFERENCES device ON UPDATE CASCADE ON DELETE CASCADE,
  sysname VARCHAR UNIQUE,
  catid VARCHAR(8) NOT NULL CONSTRAINT netbox_cat_fkey REFERENCES cat ON UPDATE CASCADE ON DELETE CASCADE,
  subcat VARCHAR,
  orgid VARCHAR(30) NOT NULL CONSTRAINT netbox_org_fkey REFERENCES org ON UPDATE CASCADE,
  ro VARCHAR,
  rw VARCHAR,
  prefixid INT4 CONSTRAINT netbox_prefix_fkey REFERENCES prefix ON UPDATE CASCADE ON DELETE SET null,
  up CHAR(1) NOT NULL DEFAULT 'y' CHECK (up='y' OR up='n' OR up='s'), -- y=up, n=down, s=shadow
  snmp_version INT4 NOT NULL DEFAULT 1,
  snmp_agent VARCHAR,
  upsince TIMESTAMP NOT NULL DEFAULT NOW(),
  uptodate BOOLEAN NOT NULL DEFAULT false, 
  discovered TIMESTAMP NULL DEFAULT NOW(),
  UNIQUE(ip),
  UNIQUE(deviceid)
);
CREATE INDEX netbox_prefixid_btree ON netbox USING btree (prefixid);

CREATE TABLE netboxsnmpoid (
  netboxid INT4 REFERENCES netbox ON UPDATE CASCADE ON DELETE CASCADE,
  snmpoidid INT4 REFERENCES snmpoid ON UPDATE CASCADE ON DELETE CASCADE,
  frequency INT4,
  UNIQUE(netboxid, snmpoidid)
);  
CREATE INDEX netboxsnmpoid_snmpoidid_btree ON netboxsnmpoid USING btree (snmpoidid);

CREATE TABLE netbox_vtpvlan (
  netboxid INT4 REFERENCES netbox ON UPDATE CASCADE ON DELETE CASCADE,
  vtpvlan INT4,
  UNIQUE(netboxid, vtpvlan)
);

CREATE TABLE subcat (
    subcatid VARCHAR,
    descr VARCHAR NOT NULL,
    catid VARCHAR(8) NOT NULL REFERENCES cat(catid),
    PRIMARY KEY (subcatid)
);
INSERT INTO subcat (subcatid,descr,catid) VALUES ('AD','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('ADC','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('BACKUP','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('DNS','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('FS','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('LDAP','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('MAIL','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('NOTES','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('STORE','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('TEST','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('UNIX','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('UNIX-STUD','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('WEB','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('WIN','Description','SRV');
INSERT INTO subcat (subcatid,descr,catid) VALUES ('WIN-STUD','Description','SRV'
);

CREATE TABLE netboxcategory (
  netboxid INT4 NOT NULL REFERENCES netbox ON UPDATE CASCADE ON DELETE CASCADE,
  category VARCHAR NOT NULL REFERENCES subcat ON UPDATE CASCADE ON DELETE CASCADE,
  PRIMARY KEY(netboxid, category)
);


CREATE TABLE netboxinfo (
  netboxinfoid SERIAL PRIMARY KEY,
  netboxid INT4 NOT NULL REFERENCES netbox ON UPDATE CASCADE ON DELETE CASCADE,
  key VARCHAR,
  var VARCHAR NOT NULL,
  val TEXT NOT NULL,
  UNIQUE(netboxid, key, var, val)
);

CREATE TABLE module (
  moduleid SERIAL PRIMARY KEY,
  deviceid INT4 NOT NULL REFERENCES device ON UPDATE CASCADE ON DELETE CASCADE,
  netboxid INT4 NOT NULL REFERENCES netbox ON UPDATE CASCADE ON DELETE CASCADE,
  module INT4 NOT NULL,
  model VARCHAR,
  descr VARCHAR,
  up CHAR(1) NOT NULL DEFAULT 'y' CHECK (up='y' OR up='n'), -- y=up, n=down
  downsince TIMESTAMP,
  UNIQUE (netboxid, module),
  UNIQUE(deviceid)
);

CREATE TABLE mem (
  memid SERIAL PRIMARY KEY,
  netboxid INT4 NOT NULL REFERENCES netbox ON UPDATE CASCADE ON DELETE CASCADE,
  memtype VARCHAR NOT NULL,
  device VARCHAR NOT NULL,
  size INT4 NOT NULL,
  used INT4,
  UNIQUE(netboxid, memtype, device)
);


CREATE TABLE swport (
  swportid SERIAL PRIMARY KEY,
  moduleid INT4 NOT NULL REFERENCES module ON UPDATE CASCADE ON DELETE CASCADE,
  ifindex INT4 NOT NULL,
  port INT4,
  interface VARCHAR,
  link CHAR(1) CHECK (link='y' OR link='n' OR link='d'), -- y=up, n=down (operDown), d=down (admDown)
  speed DOUBLE PRECISION,
  duplex CHAR(1) CHECK (duplex='f' OR duplex='h'), -- f=full, h=half
  media VARCHAR,
  vlan INT,
  trunk BOOL,
  portname VARCHAR,