source: trunk/cgi-bin/ipdb.psql@ 898

Last change on this file since 898 was 865, checked in by Kris Deugau, 9 years ago

/trunk

Further refine new install and 2.7-3.0 upgrade SQL to support multiple
copies of the same reverse zone in different VRFs that use different
DNS views. See #41.
File size: 9.5 KB
Line 
1-- Need to do this or our triggers don't work. Why do we need to do this?
2-- CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;
3-- CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";
4
5-- Table for VRF list
6CREATE TABLE vrfs (
7 vrf varchar(32) DEFAULT '' NOT NULL,
8 comment text DEFAULT '' NOT NULL,
9 location varchar(4) DEFAULT '' NOT NULL
10);
11
12COPY vrfs (vrf, comment, location) FROM stdin;
13DEFAULT Initial default VRF
14\.
15
16ALTER TABLE ONLY vrfs
17 ADD CONSTRAINT vrfs_pkey PRIMARY KEY (vrf);
18
19-- Table for tracking active netblock assignments and containers.
20-- Note that maskbits is obsolete but left in for a release cycle or two
21-- so that legacy dumps can still be loaded, in the unlikely event.
22CREATE TABLE allocations (
23 cidr cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
24 "type" character(2) DEFAULT ''::bpchar,
25 city character varying(30) DEFAULT ''::character varying,
26 description character varying(128) DEFAULT ''::character varying,
27 notes text DEFAULT ''::text,
28 maskbits integer DEFAULT 128,
29 circuitid character varying(128) DEFAULT ''::character varying,
30 createstamp timestamp with time zone DEFAULT now(),
31 modifystamp timestamp with time zone DEFAULT now(),
32 privdata text DEFAULT ''::text NOT NULL,
33 custid character varying(16) DEFAULT ''::character varying,
34 swip character(1) DEFAULT 'n'::bpchar,
35 vrf text DEFAULT ''::text NOT NULL,
36 vlan text DEFAULT ''::text NOT NULL,
37 rdns text DEFAULT ''::text NOT NULL,
38 parent_id integer DEFAULT 0 NOT NULL,
39 master_id integer DEFAULT 0 NOT NULL,
40 backup_id integer DEFAULT 0 NOT NULL,
41 id serial NOT NULL
42);
43
44ALTER TABLE ONLY allocations
45 ADD CONSTRAINT allocations_pkey PRIMARY KEY (id);
46CREATE UNIQUE INDEX allocations_skey ON allocations (cidr,vrf,type);
47
48-- Customer or POP site cities or locations.
49CREATE TABLE cities (
50 id serial NOT NULL,
51 city character varying(30) DEFAULT ''::character varying NOT NULL,
52 routing character(1) DEFAULT 'n'::bpchar NOT NULL
53);
54
55ALTER TABLE ONLY cities
56 ADD CONSTRAINT cities_pkey PRIMARY KEY (id);
57
58-- Local table for rWHOIS customer data
59CREATE TABLE customers (
60 custid character varying(16) DEFAULT ''::character varying NOT NULL,
61 name character varying(64),
62 street character varying(25),
63 street2 character varying(25),
64 city character varying(30),
65 province character(2),
66 country character(2),
67 pocode character varying(7),
68 phone character varying(15),
69 tech_handle character varying(50),
70 abuse_handle character varying(50),
71 admin_handle character varying(50),
72 def_rdns character varying(40),
73 special text
74);
75
76ALTER TABLE ONLY customers
77 ADD CONSTRAINT customers_pkey PRIMARY KEY (custid);
78
79-- Flag table for deciding if we can usefully do rDNS RPC calls.
80CREATE TABLE dnsavail (
81 "zone" cidr,
82 "location" varchar(4) DEFAULT '',
83 parent_alloc integer NOT NULL
84);
85
86ALTER TABLE ONLY dnsavail
87 ADD CONSTRAINT dnsavail_pkey PRIMARY KEY ("zone",parent_alloc);
88
89-- Store backup fields in a separate table
90CREATE TABLE backuplist (
91 backup_id serial NOT NULL,
92 bkbrand text,
93 bkmodel text,
94 bktype text,
95 bkport integer,
96 bksrc text,
97 bkuser text,
98 bkvpass text,
99 bkepass text,
100 bkip inet
101);
102
103-- Table for tracking netblocks available for assignment
104CREATE TABLE freeblocks (
105 cidr cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
106 maskbits integer DEFAULT 128,
107 city character varying(30) DEFAULT ''::character varying,
108 routed character(1) DEFAULT 'n'::bpchar,
109 vrf text DEFAULT ''::text NOT NULL,
110 parent_id integer DEFAULT 0 NOT NULL,
111 master_id integer DEFAULT 0 NOT NULL,
112 reserve_for integer DEFAULT 0 NOT NULL,
113 id serial NOT NULL
114);
115
116ALTER TABLE ONLY freeblocks
117 ADD CONSTRAINT freeblocks_pkey PRIMARY KEY (cidr, parent_id);
118
119-- Network nodes - allows finding customers affected by a broken <x> quickly
120CREATE TABLE noderef (
121 block inet NOT NULL PRIMARY KEY,
122 node_id integer
123);
124
125CREATE TABLE nodes (
126 node_id serial NOT NULL PRIMARY KEY,
127 node_type character varying(2),
128 node_name character varying(40),
129 node_ip inet
130);
131
132
133-- Email notifications on <action>
134CREATE TABLE notify (
135 action varchar(5) NOT NULL PRIMARY KEY,
136 reciplist varchar(500)
137);
138
139
140-- Table for tracking single IP assignments. Single static IP assignments
141-- need somewhat stronger clustering than provided by other types.
142CREATE TABLE poolips (
143 pool cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
144 ip cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
145 city character varying(30) DEFAULT ''::character varying NOT NULL,
146 "type" character(2) DEFAULT ''::bpchar NOT NULL,
147 available character(1) DEFAULT 'y'::bpchar NOT NULL,
148 notes text DEFAULT ''::text NOT NULL,
149 description character varying(128) DEFAULT ''::character varying NOT NULL,
150 circuitid character varying(128) DEFAULT ''::character varying NOT NULL,
151 privdata text DEFAULT ''::text NOT NULL,
152 custid character varying(16) DEFAULT ''::character varying,
153 createstamp timestamp with time zone DEFAULT now(),
154 modifystamp timestamp with time zone DEFAULT now(),
155 vrf text DEFAULT ''::text NOT NULL,
156 vlan text DEFAULT ''::text NOT NULL,
157 rdns text DEFAULT ''::text NOT NULL,
158 parent_id integer DEFAULT 0 NOT NULL,
159 master_id integer DEFAULT 0 NOT NULL,
160 backup_id integer DEFAULT 0 NOT NULL,
161 id serial NOT NULL,
162 CONSTRAINT poolips_available_check CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
163);
164
165ALTER TABLE ONLY poolips
166 ADD CONSTRAINT poolips_pkey PRIMARY KEY (ip, parent_id);
167
168
169-- Combined netblock+IP view for searches
170CREATE VIEW searchme AS
171 SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city,
172 allocations.description, allocations.notes, allocations.circuitid, allocations.vrf,
173 allocations.vlan, allocations.id, allocations.parent_id, allocations.master_id, 'n' AS available
174 FROM allocations
175 UNION
176 SELECT poolips.ip AS cidr, poolips.custid, poolips."type", poolips.city,
177 poolips.description, poolips.notes, poolips.circuitid, poolips.vrf,
178 poolips.vlan, poolips.id, poolips.parent_id, poolips.master_id, poolips.available
179 FROM poolips;
180
181
182-- Type list for assignments
183CREATE TABLE alloctypes (
184 "type" character(2) DEFAULT ''::bpchar NOT NULL,
185 listname character varying(40) DEFAULT ''::character varying,
186 dispname character varying(40) DEFAULT ''::character varying,
187 listorder integer DEFAULT 0,
188 def_custid character varying(16) DEFAULT ''::character varying,
189 arin_netname character varying(20) DEFAULT 'ISP'::character varying
190);
191
192ALTER TABLE ONLY alloctypes
193 ADD CONSTRAINT alloctypes_pkey PRIMARY KEY ("type");
194
195
196-- Initial/standard allocation types. Update def_custid and arin_netname as appropriate.
197COPY alloctypes ("type", listname, dispname, listorder, def_custid, arin_netname) FROM stdin;
198cn Customer netblock Customer netblock 0 ISPCUST
199si Static IP - Server pool Server pool IP 20 ISP
200ci Static IP - Cable Static cable IP 21 ISP
201di Static IP - DSL Static DSL IP 22 ISP
202mi Static IP - Dialup Static dialup IP 23 ISP
203wi Static IP - Wireless Static wireless IP 24 ISP
204sd Static Pool - Servers Server pool 40 5554242 ISP
205cd Static Pool - Cable Cable pool 41 CBL-BUS ISP-STATIC-CABLE
206dp Static Pool - DSL DSL pool 42 DSL-BUS ISP-STATIC-DSL
207mp Static Pool - Dialup Static dialup pool 43 DIAL-BUS ISP-STATIC-DIAL
208wp Static Pool - Wireless Static wireless pool 44 WL-BUS ISP-STATIC-WIFI
209en End-use netblock End-use netblock 100 5554242 ISP
210me Dialup netblock Dialup netblock 101 DIAL-RES ISP-DIAL
211de Dynamic DSL block Dynamic DSL block 102 DSL-RES ISP-DSL
212ce Dynamic cable block Dynamic cable block 103 CBL-RES ISP-CABLE
213we Dynamic WiFi block Dynamic WiFi block 104 WL-RES ISP-WIFI
214ve Dynamic VoIP block Dynamic VoIP block 105 DYN-VOIP ISP
215li Static IP - LAN/POP Static LAN/POP IP 190 NOC-VPN ISP
216ai Static IP - Management Static management IP 192 NOC-VPN ISP
217bi Static IP - Wifi CPE Wifi CPE IP 193 ISP
218ld Static Pool - LAN/POP LAN pool 195 NOC-VPN ISP
219ad Static Pool - Management Management pool 196 NOC-VPN ISP
220bd Static pool - Wifi CPE Wifi CPE pool 197 ISP
221in Internal netblock Internal netblock 199 5554242 ISP
222wc Reserve for CORE/WAN blocks CORE/WAN blocks 200 5554242 ISP
223pc Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201 5554242 ISP-STATIC-DSL
224ac Reserve for ATM ATM blocks 202 5554242 ISP
225fc Reserve for fibre Fibre blocks 203 5554242 ISP
226wr CORE/WAN block CORE/WAN block 220 5554242 ISP
227pr Dynamic-route DSL netblock (cust) Dynamic-route DSL (cust) 221 ISPCUST
228ar ATM block ATM block 222 ISP
229fr Fibre Fibre 223 ISP
230rm Routing aggregation Routing aggregation 500 5554242 ISP
231mm Master block Master block 999 5554242 ISP
232\.
233
234-- User data table - required for proper ACLs
235CREATE TABLE users (
236 username character varying(256) NOT NULL,
237 "password" character varying(256) DEFAULT ''::character varying,
238 acl character varying(256) DEFAULT 'b'::character varying
239);
240
241ALTER TABLE ONLY users
242 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
243
244-- Default password is admin
245INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsAm');
246
247
248-- Trigger and matching function to update modifystamp on allocations, poolips
249CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
250 BEGIN
251 NEW.modifystamp := ''now'';
252 RETURN NEW;
253 END;
254' LANGUAGE 'plpgsql';
255
256CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
257 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
258
259CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
260 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
Note: See TracBrowser for help on using the repository browser.