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