source: branches/stable/cgi-bin/ipdb.psql@ 621

Last change on this file since 621 was 621, checked in by Kris Deugau, 10 years ago

/branches/stable

Brown-paper-bag fix for patch in r620; forgot to include the SQL update,
forgot to include the new field in the column selection, and got the logic
sense for whether to show the "edit block" or "allocate free IP" link
inverted. *sigh*
File size: 6.4 KB
Line 
1-- Initial table setup for IP Database
2
3CREATE TABLE "customers" (
4 "custid" character varying(16) DEFAULT '' NOT NULL,
5 "name" character varying(64),
6 "street" character varying(25),
7 "street2" character varying(25),
8 "city" character varying(30),
9 "province" character(2),
10 "country" character(2),
11 "pocode" character varying(7),
12 "phone" character varying(15),
13 "tech_handle" character varying(50),
14 "abuse_handle" character varying(50),
15 "admin_handle" character varying(50),
16 "def_rdns" character varying(40),
17 "special" text,
18 Constraint "customers_pkey" Primary Key ("custid")
19);
20
21CREATE TABLE "masterblocks" (
22 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
23 "ctime" timestamp DEFAULT now(),
24 "mtime" timestamp DEFAULT now(),
25 "rwhois" character(1) DEFAULT 'n' NOT NULL
26);
27
28CREATE TABLE "routed" (
29 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
30 "maskbits" integer DEFAULT 128,
31 "city" character varying(30) DEFAULT '',
32 "ctime" timestamp DEFAULT now()
33);
34
35CREATE TABLE "freeblocks" (
36 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
37 "maskbits" integer DEFAULT 128,
38 "city" character varying(30) DEFAULT '',
39 "routed" character(1) DEFAULT 'n'
40);
41
42CREATE TABLE "poolips" (
43 "pool" cidr DEFAULT '255.255.255.255/32' NOT NULL,
44 "ip" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
45 "city" character varying(30) DEFAULT '' NOT NULL,
46 "type" character(2) DEFAULT '' NOT NULL,
47 "available" character(1) DEFAULT 'y' NOT NULL,
48 "notes" text DEFAULT '' NOT NULL,
49 "description" character varying(64) DEFAULT '' NOT NULL,
50 "circuitid" character varying(128) DEFAULT '' NOT NULL,
51 "privdata" text DEFAULT '' NOT NULL,
52 "custid" character varying(16) DEFAULT '',
53 "createstamp" timestamp DEFAULT now(),
54 "modifystamp" timestamp DEFAULT now(),
55 "vrf" character varying(128) DEFAULT '' NOT NULL,
56 CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
57);
58
59CREATE TABLE "allocations" (
60 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
61 "type" character(2) DEFAULT '',
62 "city" character varying(30) DEFAULT '',
63 "description" character varying(64) DEFAULT '',
64 "notes" text DEFAULT '',
65 "maskbits" integer DEFAULT 128,
66 "circuitid" character varying(128) DEFAULT '',
67 "createstamp" timestamp DEFAULT now(),
68 "modifystamp" timestamp DEFAULT now(),
69 "privdata" text DEFAULT '' NOT NULL,
70 "custid" character varying(16) DEFAULT '',
71 swip character(1) DEFAULT 'n'
72 "vrf" character varying(128) DEFAULT '' NOT NULL,
73);
74
75CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes, allocations.circuitid, allocations.vrf, 'n' AS available FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes, poolips.circuitid, poolips.vrf, poolips.available FROM poolips;
76
77CREATE TABLE "alloctypes" (
78 "type" character(2) DEFAULT '' NOT NULL PRIMARY KEY,
79 "listname" character varying(40) DEFAULT '',
80 "dispname" character varying(40) DEFAULT '',
81 "listorder" integer DEFAULT 0,
82 "def_custid" character varying(16) DEFAULT '',
83 "arin_netname" character varying(20) DEFAULT 'ISP'
84);
85
86--
87-- Name: alloctypes; Type: TABLE DATA; Schema: public; Owner: ipdb
88--
89
90COPY alloctypes ("type", listname, dispname, listorder, def_custid, arin_netname) FROM stdin;
91cn Customer netblock Customer netblock 0 ISPCUST
92si Static IP - Server pool Server pool IP 20 ISP
93ci Static IP - Cable Static cable IP 21 ISP
94di Static IP - DSL Static DSL IP 22 ISP
95mi Static IP - Dialup Static dialup IP 23 ISP
96wi Static IP - Wireless Static wireless IP 24 ISP
97sd Static Pool - Servers Server pool 40 5554242 ISP
98cd Static Pool - Cable Cable pool 41 CBL-BUS ISP-STATIC-CABLE
99dp Static Pool - DSL DSL pool 42 DSL-BUS ISP-STATIC-DSL
100mp Static Pool - Dialup Static dialup pool 43 DIAL-BUS ISP-STATIC-DIAL
101wp Static Pool - Wireless Static wireless pool 44 WL-BUS ISP-STATIC-WIFI
102en End-use netblock End-use netblock 100 5554242 ISP
103me Dialup netblock Dialup netblock 101 DIAL-RES ISP-DIAL
104de Dynamic DSL block Dynamic DSL block 102 DSL-RES ISP-DSL
105ce Dynamic cable block Dynamic cable block 103 CBL-RES ISP-CABLE
106we Dynamic WiFi block Dynamic WiFi block 104 WL-RES ISP-WIFI
107ve Dynamic VoIP block Dynamic VoIP block 105 DYN-VOIP ISP
108li Static IP - LAN/POP Static LAN/POP IP 190 NOC-VPN ISP
109ai Static IP - Management Static management IP 192 NOC-VPN ISP
110bi Static IP - Wifi CPE Wifi CPE IP 193 ISP
111ld Static Pool - LAN/POP LAN pool 195 NOC-VPN ISP
112ad Static Pool - Management Management pool 196 NOC-VPN ISP
113bd Static pool - Wifi CPE Wifi CPE pool 197 ISP
114in Internal netblock Internal netblock 199 5554242 ISP
115wc Reserve for CORE/WAN blocks CORE/WAN blocks 200 5554242 ISP
116pc Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201 5554242 ISP-STATIC-DSL
117ac Reserve for ATM ATM blocks 202 5554242 ISP
118fc Reserve for fibre Fibre blocks 203 5554242 ISP
119wr CORE/WAN block CORE/WAN block 220 5554242 ISP
120pr Dynamic-route DSL netblock (cust) Dynamic-route DSL (cust) 221 ISPCUST
121ar ATM block ATM block 222 ISP
122fr Fibre Fibre 223 ISP
123rm Routing Routed netblock 500 5554242 ISP
124mm Master block Master block 999 5554242 ISP
125\.
126
127CREATE TABLE "cities" (
128 "id" serial NOT NULL PRIMARY KEY,
129 "city" character varying(30) DEFAULT '' NOT NULL,
130 "routing" character(1) DEFAULT 'n' NOT NULL
131);
132
133--
134-- Trigger and matching function to update modifystamp on allocations, poolips
135--
136CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
137 BEGIN
138 NEW.modifystamp := ''now'';
139 RETURN NEW;
140 END;
141' LANGUAGE 'plpgsql';
142
143CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
144 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
145
146CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
147 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
148
149--
150-- User data table - required for proper ACLs
151--
152
153CREATE TABLE "users" (
154 "username" varchar(40) NOT NULL PRIMARY KEY,
155 "password" varchar(60) DEFAULT '',
156 "acl" varchar(30) DEFAULT 'b'
157);
158
159-- Default password is admin
160INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsA');
161
162-- Network nodes - allows finding customers affected by a broken <x> quickly
163CREATE TABLE noderef (
164 block inet NOT NULL PRIMARY KEY,
165 node_id integer
166);
167
168CREATE TABLE nodes (
169 node_id serial NOT NULL PRIMARY KEY,
170 node_type character varying(2),
171 node_name character varying(40),
172 node_ip inet
173);
174
175-- Email notifications on <action>
176CREATE TABLE notify (
177 action varchar(5) NOT NULL PRIMARY KEY,
178 reciplist varchar(500)
179);
Note: See TracBrowser for help on using the repository browser.