1BEGIN; 2 3-- admin table - Queue for admin tasks sent from front-end for back-end processing. 4 5CREATE TABLE admin ( 6 job serial, 7 entered TIMESTAMP DEFAULT now(), 8 started TIMESTAMP, 9 finished TIMESTAMP, 10 device inet, 11 port text, 12 action text, 13 subaction text, 14 status text, 15 username text, 16 userip inet, 17 log text, 18 debug boolean 19 ); 20 21CREATE INDEX idx_admin_entered ON admin(entered); 22CREATE INDEX idx_admin_status ON admin(status); 23CREATE INDEX idx_admin_action ON admin(action); 24 25CREATE TABLE device ( 26 ip inet PRIMARY KEY, 27 creation TIMESTAMP DEFAULT now(), 28 dns text, 29 description text, 30 uptime bigint, 31 contact text, 32 name text, 33 location text, 34 layers varchar(8), 35 ports integer, 36 mac macaddr, 37 serial text, 38 model text, 39 ps1_type text, 40 ps2_type text, 41 ps1_status text, 42 ps2_status text, 43 fan text, 44 slots integer, 45 vendor text, 46 os text, 47 os_ver text, 48 log text, 49 snmp_ver integer, 50 snmp_comm text, 51 snmp_class text, 52 vtp_domain text, 53 last_discover TIMESTAMP, 54 last_macsuck TIMESTAMP, 55 last_arpnip TIMESTAMP 56); 57 58-- Indexing for speed-ups 59CREATE INDEX idx_device_dns ON device(dns); 60CREATE INDEX idx_device_layers ON device(layers); 61CREATE INDEX idx_device_vendor ON device(vendor); 62CREATE INDEX idx_device_model ON device(model); 63 64CREATE TABLE device_ip ( 65 ip inet, 66 alias inet, 67 subnet cidr, 68 port text, 69 dns text, 70 creation TIMESTAMP DEFAULT now(), 71 PRIMARY KEY(ip,alias) 72); 73 74-- Indexing for speed ups 75CREATE INDEX idx_device_ip_ip ON device_ip(ip); 76CREATE INDEX idx_device_ip_alias ON device_ip(alias); 77CREATE INDEX idx_device_ip_ip_port ON device_ip(ip,port); 78 79CREATE TABLE device_module ( 80 ip inet not null, 81 index integer, 82 description text, 83 type text, 84 parent integer, 85 name text, 86 class text, 87 pos integer, 88 hw_ver text, 89 fw_ver text, 90 sw_ver text, 91 serial text, 92 model text, 93 fru boolean, 94 creation TIMESTAMP DEFAULT now(), 95 last_discover TIMESTAMP, 96 PRIMARY KEY(ip,index) 97 ); 98 99CREATE TABLE device_port ( 100 ip inet, 101 port text, 102 creation TIMESTAMP DEFAULT now(), 103 descr text, 104 up text, 105 up_admin text, 106 type text, 107 duplex text, 108 duplex_admin text, 109 speed text, 110 name text, 111 mac macaddr, 112 mtu integer, 113 stp text, 114 remote_ip inet, 115 remote_port text, 116 remote_type text, 117 remote_id text, 118 vlan text, 119 pvid integer, 120 lastchange bigint, 121 PRIMARY KEY(port,ip) 122); 123 124CREATE INDEX idx_device_port_ip ON device_port(ip); 125CREATE INDEX idx_device_port_remote_ip ON device_port(remote_ip); 126-- For the duplex mismatch finder : 127CREATE INDEX idx_device_port_ip_port_duplex ON device_port(ip,port,duplex); 128CREATE INDEX idx_device_port_ip_up_admin ON device_port(ip,up_admin); 129CREATE INDEX idx_device_port_mac ON device_port(mac); 130 131CREATE TABLE device_port_log ( 132 id serial, 133 ip inet, 134 port text, 135 reason text, 136 log text, 137 username text, 138 userip inet, 139 action text, 140 creation TIMESTAMP DEFAULT now() 141 ); 142 143CREATE INDEX idx_device_port_log_1 ON device_port_log(ip,port); 144CREATE INDEX idx_device_port_log_user ON device_port_log(username); 145 146CREATE TABLE device_port_power ( 147 ip inet, 148 port text, 149 module integer, 150 admin text, 151 status text, 152 class text, 153 power integer, 154 PRIMARY KEY(port,ip) 155); 156 157CREATE TABLE device_port_ssid ( 158 ip inet, 159 port text, 160 ssid text, 161 broadcast boolean, 162 bssid macaddr 163); 164 165CREATE INDEX idx_device_port_ssid_ip_port ON device_port_ssid(ip,port); 166 167CREATE TABLE device_port_vlan ( 168 ip inet, 169 port text, 170 vlan integer, 171 native boolean not null default false, 172 creation TIMESTAMP DEFAULT now(), 173 last_discover TIMESTAMP DEFAULT now(), 174 vlantype text, 175 PRIMARY KEY(ip,port,vlan) 176); 177 178CREATE TABLE device_port_wireless ( 179 ip inet, 180 port text, 181 channel integer, 182 power integer 183); 184 185CREATE INDEX idx_device_port_wireless_ip_port ON device_port_wireless(ip,port); 186 187CREATE TABLE device_power ( 188 ip inet, 189 module integer, 190 power integer, 191 status text, 192 PRIMARY KEY(ip,module) 193); 194 195CREATE TABLE device_vlan ( 196 ip inet, 197 vlan integer, 198 description text, 199 creation TIMESTAMP DEFAULT now(), 200 last_discover TIMESTAMP DEFAULT now(), 201 PRIMARY KEY(ip,vlan) 202); 203 204 205CREATE TABLE log ( 206 id serial, 207 creation TIMESTAMP DEFAULT now(), 208 class text, 209 entry text, 210 logfile text 211); 212 213CREATE TABLE node ( 214 mac macaddr, 215 switch inet, 216 port text, 217 vlan text default '0', 218 active boolean, 219 oui varchar(8), 220 time_first timestamp default now(), 221 time_recent timestamp default now(), 222 time_last timestamp default now(), 223 PRIMARY KEY(mac,switch,port,vlan) 224); 225 226-- Indexes speed things up a LOT 227CREATE INDEX idx_node_switch_port_active ON node(switch,port,active); 228CREATE INDEX idx_node_switch_port ON node(switch,port); 229CREATE INDEX idx_node_switch ON node(switch); 230CREATE INDEX idx_node_mac ON node(mac); 231CREATE INDEX idx_node_mac_active ON node(mac,active); 232-- CREATE INDEX idx_node_oui ON node(oui); 233 234CREATE TABLE node_ip ( 235 mac macaddr, 236 ip inet, 237 active boolean, 238 time_first timestamp default now(), 239 time_last timestamp default now(), 240 PRIMARY KEY(mac,ip) 241); 242 243-- Indexing speed ups. 244CREATE INDEX idx_node_ip_ip ON node_ip(ip); 245CREATE INDEX idx_node_ip_ip_active ON node_ip(ip,active); 246CREATE INDEX idx_node_ip_mac ON node_ip(mac); 247CREATE INDEX idx_node_ip_mac_active ON node_ip(mac,active); 248 249CREATE TABLE node_monitor ( 250 mac macaddr, 251 active boolean, 252 why text, 253 cc text, 254 date TIMESTAMP DEFAULT now(), 255 PRIMARY KEY(mac) 256); 257 258-- node_nbt - Hold Netbios information for each node. 259 260CREATE TABLE node_nbt ( 261 mac macaddr PRIMARY KEY, 262 ip inet, 263 nbname text, 264 domain text, 265 server boolean, 266 nbuser text, 267 active boolean, 268 time_first timestamp default now(), 269 time_last timestamp default now() 270); 271 272-- Indexing speed ups. 273CREATE INDEX idx_node_nbt_mac ON node_nbt(mac); 274CREATE INDEX idx_node_nbt_nbname ON node_nbt(nbname); 275CREATE INDEX idx_node_nbt_domain ON node_nbt(domain); 276CREATE INDEX idx_node_nbt_mac_active ON node_nbt(mac,active); 277 278-- Add "vlan" column to node table 279-- ALTER TABLE node ADD COLUMN vlan text default '0'; 280 281alter table node drop constraint node_pkey; 282alter table node add primary key (mac, switch, port, vlan); 283 284CREATE TABLE node_wireless ( 285 mac macaddr, 286 ssid text default '', 287 uptime integer, 288 maxrate integer, 289 txrate integer, 290 sigstrength integer, 291 sigqual integer, 292 rxpkt integer, 293 txpkt integer, 294 rxbyte bigint, 295 txbyte bigint, 296 time_last timestamp default now(), 297 PRIMARY KEY(mac,ssid) 298); 299 300 301-- Add "ssid" column to node_wireless table 302-- ALTER TABLE node_wireless ADD ssid text default ''; 303 304alter table node_wireless drop constraint node_wireless_pkey; 305alter table node_wireless add primary key (mac, ssid); 306 307 308 309CREATE TABLE oui ( 310 oui varchar(8) PRIMARY KEY, 311 company text 312); 313 314 315-- process table - Queue to coordinate between processes in multi-process mode. 316 317CREATE TABLE process ( 318 controller integer not null, 319 device inet not null, 320 action text not null, 321 status text, 322 count integer, 323 creation TIMESTAMP DEFAULT now() 324 ); 325 326CREATE TABLE sessions ( 327 id char(32) NOT NULL PRIMARY KEY, 328 creation TIMESTAMP DEFAULT now(), 329 a_session text 330 ); 331 332CREATE TABLE subnets ( 333 net cidr NOT NULL, 334 creation timestamp default now(), 335 last_discover timestamp default now(), 336 PRIMARY KEY(net) 337); 338 339-- Add "topology" table to augment manual topo file 340CREATE TABLE topology ( 341 dev1 inet not null, 342 port1 text not null, 343 dev2 inet not null, 344 port2 text not null 345); 346 347 348 349-- This table logs login and logout / change requests for users 350 351CREATE TABLE user_log ( 352 entry serial, 353 username varchar(50), 354 userip inet, 355 event text, 356 details text, 357 creation TIMESTAMP DEFAULT now() 358 ); 359 360CREATE TABLE users ( 361 username varchar(50) PRIMARY KEY, 362 password text, 363 creation TIMESTAMP DEFAULT now(), 364 last_on TIMESTAMP, 365 port_control boolean DEFAULT false, 366 ldap boolean DEFAULT false, 367 admin boolean DEFAULT false, 368 fullname text, 369 note text 370 ); 371 372COMMIT; 373