1BEGIN; 2 3-- Netdisco 4-- Database Schema Modifications 5-- UPGRADE from 0.95 to 0.96 6 7-- 8-- Add snmp_class to device table 9ALTER TABLE device ADD snmp_class text; 10 11-- 12-- Add subnet to device_ip table 13ALTER TABLE device_ip ADD subnet cidr; 14 15-- 16-- Add indexes on admin table 17CREATE INDEX idx_admin_entered ON admin(entered); 18CREATE INDEX idx_admin_status ON admin(status); 19CREATE INDEX idx_admin_action ON admin(action); 20 21-- 22-- Create device_module table 23CREATE TABLE device_module ( 24 ip inet not null, 25 index integer, 26 description text, 27 type text, 28 parent integer, 29 name text, 30 class text, 31 pos integer, 32 hw_ver text, 33 fw_ver text, 34 sw_ver text, 35 serial text, 36 model text, 37 fru boolean, 38 creation TIMESTAMP DEFAULT now(), 39 last_discover TIMESTAMP 40 ); 41 42-- 43-- Earlier versions of device_module didn't have the index 44ALTER TABLE device_module ADD PRIMARY KEY(ip,index); 45 46-- Create process table - Queue to coordinate between processes in multi-process mode. 47CREATE TABLE process ( 48 controller integer not null, -- pid of controlling process 49 device inet not null, 50 action text not null, -- arpnip, macsuck, nbtstat, discover 51 status text, -- queued, running, skipped, done, error, timeout, nocdp, nosnmp 52 count integer, 53 creation TIMESTAMP DEFAULT now() 54 ); 55 56-- Earlier versions of the process table didn't have the creation timestamp 57ALTER TABLE process ADD creation TIMESTAMP DEFAULT now(); 58 59-- 60-- Add ldap to users table 61ALTER TABLE users ADD ldap boolean; 62ALTER TABLE users ALTER ldap SET DEFAULT false; 63 64-- 65-- Add pvid to device_port table 66ALTER TABLE device_port ADD pvid integer; 67 68-- 69-- Create device_port_vlan table 70CREATE TABLE device_port_vlan ( 71 ip inet, -- ip of device 72 port text, -- Unique identifier of Physical Port Name 73 vlan integer, -- VLAN ID 74 native boolean not null default false, -- native or trunked 75 creation TIMESTAMP DEFAULT now(), 76 last_discover TIMESTAMP DEFAULT now(), 77 PRIMARY KEY(ip,port,vlan) 78); 79 80-- 81-- Create device_vlan table 82CREATE TABLE device_vlan ( 83 ip inet, -- ip of device 84 vlan integer, -- VLAN ID 85 description text, -- VLAN description 86 creation TIMESTAMP DEFAULT now(), 87 last_discover TIMESTAMP DEFAULT now(), 88 PRIMARY KEY(ip,vlan) 89); 90 91-- 92-- Create device_power table 93CREATE TABLE device_power ( 94 ip inet, -- ip of device 95 module integer,-- Module from PowerEthernet index 96 power integer,-- nominal power of the PSE expressed in Watts 97 status text, -- The operational status 98 PRIMARY KEY(ip,module) 99); 100 101-- 102-- Create device_port_power table 103CREATE TABLE device_port_power ( 104 ip inet, -- ip of device 105 port text, -- Unique identifier of Physical Port Name 106 module integer,-- Module from PowerEthernet index 107 admin text, -- Admin power status 108 status text, -- Detected power status 109 class text, -- Detected class 110 PRIMARY KEY(port,ip) 111); 112 113CREATE TABLE device_port_wireless ( 114 ip inet, -- ip of device 115 port text, -- Unique identifier of Physical Port Name 116 channel integer,-- 802.11 channel number 117 power integer -- transmit power in mw 118); 119 120CREATE INDEX idx_device_port_wireless_ip_port ON device_port_wireless(ip,port); 121 122-- 123-- device_port_ssid lost its channel column, it moved to device_port_wireless 124-- 125-- Migrate any existing data 126INSERT INTO device_port_wireless ( ip,port,channel ) ( SELECT ip,port,channel FROM device_port_ssid WHERE channel IS NOT NULL ); 127 128ALTER TABLE device_port_ssid DROP channel; 129 130 131-- 132-- node_wireless, for client association information 133CREATE TABLE node_wireless ( 134 mac macaddr, 135 uptime integer, 136 maxrate integer, -- can be 0.5 but we ignore that for now 137 txrate integer, -- can be 0.5 but we ignore that for now 138 sigstrength integer, -- signal strength (-db) 139 sigqual integer, -- signal quality 140 rxpkt integer, -- received packets 141 txpkt integer, -- transmitted packets 142 rxbyte bigint, -- received bytes 143 txbyte bigint, -- transmitted bytes 144 time_last timestamp default now(), 145 PRIMARY KEY(mac) 146); 147 148-- 149-- node_monitor, for lost/stolen device monitoring 150CREATE TABLE node_monitor ( 151 mac macaddr, 152 active boolean, 153 why text, 154 cc text, 155 date TIMESTAMP DEFAULT now(), 156 PRIMARY KEY(mac) 157); 158 159COMMIT; 160