1BEGIN; 2 3-- Netdisco 4-- Database Schema Modifications 5-- UPGRADE from 0.94 to 0.95 6 7CREATE TABLE subnets ( 8 net cidr NOT NULL, 9 creation timestamp default now(), 10 last_discover timestamp default now(), 11 PRIMARY KEY(net) 12); 13 14-- 15-- node_nbt could already exist, if you upgraded to 0.94, but if 16-- you ran pg_all in 0.94, node_nbt wasn't created. This 17-- will report some harmless errors if it already exists. 18 19CREATE TABLE node_nbt ( 20 mac macaddr PRIMARY KEY, 21 ip inet, 22 nbname text, 23 domain text, 24 server boolean, 25 nbuser text, 26 active boolean, -- do we need this still? 27 time_first timestamp default now(), 28 time_last timestamp default now() 29); 30 31-- Indexing speed ups. 32CREATE INDEX idx_node_nbt_mac ON node_nbt(mac); 33CREATE INDEX idx_node_nbt_nbname ON node_nbt(nbname); 34CREATE INDEX idx_node_nbt_domain ON node_nbt(domain); 35CREATE INDEX idx_node_nbt_mac_active ON node_nbt(mac,active); 36 37-- 38-- Add time_recent to node table 39ALTER TABLE node ADD time_recent timestamp; 40ALTER TABLE node ALTER time_recent SET DEFAULT now(); 41UPDATE node SET time_recent = time_first WHERE time_recent IS NULL; 42 43-- 44-- Add table to contain wireless base station SSIDs 45CREATE TABLE device_port_ssid ( 46 ip inet, -- ip of device 47 port text, -- Unique identifier of Physical Port Name 48 ssid text, -- An SSID that is valid on this port. 49 broadcast boolean,-- Is it broadcast? 50 channel integer -- 802.11 channel number 51); 52 53CREATE INDEX idx_device_port_ssid_ip_port ON device_port_ssid(ip,port); 54 55-- 56-- The OUI field in the oui database is now lowercase. 57UPDATE oui SET oui=lower(oui); 58 59COMMIT; 60