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

Last change on this file since 940 was 915, checked in by Kris Deugau, 7 years ago

/trunk

Start adding a generalized in-your-face infonotice space to attach warnings
to a netblock. See #17 and #23, sort of.

  • table to hold the notices, since they're likely to be far fewer than the overall allocation count
  • show any notices for the parent chain for new allocations in the admin tools
File size: 9.8 KB
RevLine 
[347]1-- Need to do this or our triggers don't work. Why do we need to do this?
[763]2-- CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;
3-- CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";
[347]4
[804]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
[864]12COPY vrfs (vrf, comment, location) FROM stdin;
13DEFAULT Initial default VRF
14\.
15
[804]16ALTER TABLE ONLY vrfs
17 ADD CONSTRAINT vrfs_pkey PRIMARY KEY (vrf);
18
[763]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,
[807]26 description character varying(128) DEFAULT ''::character varying,
[763]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,
[864]40 backup_id integer DEFAULT 0 NOT NULL,
[763]41 id serial NOT NULL
42);
[347]43
[763]44ALTER TABLE ONLY allocations
45 ADD CONSTRAINT allocations_pkey PRIMARY KEY (id);
46CREATE UNIQUE INDEX allocations_skey ON allocations (cidr,vrf,type);
[4]47
[763]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
[4]53);
54
[763]55ALTER TABLE ONLY cities
56 ADD CONSTRAINT cities_pkey PRIMARY KEY (id);
[34]57
[763]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
[4]74);
75
[763]76ALTER TABLE ONLY customers
77 ADD CONSTRAINT customers_pkey PRIMARY KEY (custid);
[34]78
[763]79-- Flag table for deciding if we can usefully do rDNS RPC calls.
80CREATE TABLE dnsavail (
[865]81 "zone" cidr,
[864]82 "location" varchar(4) DEFAULT '',
[763]83 parent_alloc integer NOT NULL
[4]84);
85
[865]86ALTER TABLE ONLY dnsavail
87 ADD CONSTRAINT dnsavail_pkey PRIMARY KEY ("zone",parent_alloc);
88
[864]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
[763]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);
[34]115
[763]116ALTER TABLE ONLY freeblocks
117 ADD CONSTRAINT freeblocks_pkey PRIMARY KEY (cidr, parent_id);
118
[915]119-- Table for arbitrary infonotices tagged to an allocation
120CREATE TABLE blocknotices (
121 alloc_id integer NOT NULL,
122 ipflag boolean DEFAULT false NOT NULL,
123 notice text DEFAULT ''::text NOT NULL
124);
125
126ALTER TABLE ONLY blocknotices
127 ADD CONSTRAINT blocknotices_pkey PRIMARY KEY (alloc_id, ipflag);
128
[763]129-- Network nodes - allows finding customers affected by a broken <x> quickly
130CREATE TABLE noderef (
131 block inet NOT NULL PRIMARY KEY,
132 node_id integer
[34]133);
134
[763]135CREATE TABLE nodes (
136 node_id serial NOT NULL PRIMARY KEY,
137 node_type character varying(2),
138 node_name character varying(40),
139 node_ip inet
140);
[34]141
[763]142
143-- Email notifications on <action>
144CREATE TABLE notify (
145 action varchar(5) NOT NULL PRIMARY KEY,
146 reciplist varchar(500)
[74]147);
148
149
[763]150-- Table for tracking single IP assignments. Single static IP assignments
151-- need somewhat stronger clustering than provided by other types.
152CREATE TABLE poolips (
153 pool cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
154 ip cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
155 city character varying(30) DEFAULT ''::character varying NOT NULL,
156 "type" character(2) DEFAULT ''::bpchar NOT NULL,
157 available character(1) DEFAULT 'y'::bpchar NOT NULL,
158 notes text DEFAULT ''::text NOT NULL,
[807]159 description character varying(128) DEFAULT ''::character varying NOT NULL,
[763]160 circuitid character varying(128) DEFAULT ''::character varying NOT NULL,
161 privdata text DEFAULT ''::text NOT NULL,
162 custid character varying(16) DEFAULT ''::character varying,
163 createstamp timestamp with time zone DEFAULT now(),
164 modifystamp timestamp with time zone DEFAULT now(),
165 vrf text DEFAULT ''::text NOT NULL,
166 vlan text DEFAULT ''::text NOT NULL,
167 rdns text DEFAULT ''::text NOT NULL,
168 parent_id integer DEFAULT 0 NOT NULL,
169 master_id integer DEFAULT 0 NOT NULL,
[864]170 backup_id integer DEFAULT 0 NOT NULL,
[763]171 id serial NOT NULL,
172 CONSTRAINT poolips_available_check CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
[74]173);
174
[763]175ALTER TABLE ONLY poolips
176 ADD CONSTRAINT poolips_pkey PRIMARY KEY (ip, parent_id);
[74]177
[92]178
[763]179-- Combined netblock+IP view for searches
180CREATE VIEW searchme AS
181 SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city,
182 allocations.description, allocations.notes, allocations.circuitid, allocations.vrf,
[864]183 allocations.vlan, allocations.id, allocations.parent_id, allocations.master_id, 'n' AS available
[763]184 FROM allocations
185 UNION
186 SELECT poolips.ip AS cidr, poolips.custid, poolips."type", poolips.city,
187 poolips.description, poolips.notes, poolips.circuitid, poolips.vrf,
[864]188 poolips.vlan, poolips.id, poolips.parent_id, poolips.master_id, poolips.available
[763]189 FROM poolips;
[176]190
[763]191
192-- Type list for assignments
193CREATE TABLE alloctypes (
194 "type" character(2) DEFAULT ''::bpchar NOT NULL,
195 listname character varying(40) DEFAULT ''::character varying,
196 dispname character varying(40) DEFAULT ''::character varying,
197 listorder integer DEFAULT 0,
198 def_custid character varying(16) DEFAULT ''::character varying,
199 arin_netname character varying(20) DEFAULT 'ISP'::character varying
[92]200);
201
[763]202ALTER TABLE ONLY alloctypes
203 ADD CONSTRAINT alloctypes_pkey PRIMARY KEY ("type");
[321]204
[763]205
206-- Initial/standard allocation types. Update def_custid and arin_netname as appropriate.
[388]207COPY alloctypes ("type", listname, dispname, listorder, def_custid, arin_netname) FROM stdin;
[324]208cn Customer netblock Customer netblock 0 ISPCUST
[403]209si Static IP - Server pool Server pool IP 20 ISP
210ci Static IP - Cable Static cable IP 21 ISP
211di Static IP - DSL Static DSL IP 22 ISP
212mi Static IP - Dialup Static dialup IP 23 ISP
213wi Static IP - Wireless Static wireless IP 24 ISP
[417]214sd Static Pool - Servers Server pool 40 5554242 ISP
[324]215cd Static Pool - Cable Cable pool 41 CBL-BUS ISP-STATIC-CABLE
216dp Static Pool - DSL DSL pool 42 DSL-BUS ISP-STATIC-DSL
217mp Static Pool - Dialup Static dialup pool 43 DIAL-BUS ISP-STATIC-DIAL
218wp Static Pool - Wireless Static wireless pool 44 WL-BUS ISP-STATIC-WIFI
[417]219en End-use netblock End-use netblock 100 5554242 ISP
[324]220me Dialup netblock Dialup netblock 101 DIAL-RES ISP-DIAL
221de Dynamic DSL block Dynamic DSL block 102 DSL-RES ISP-DSL
222ce Dynamic cable block Dynamic cable block 103 CBL-RES ISP-CABLE
223we Dynamic WiFi block Dynamic WiFi block 104 WL-RES ISP-WIFI
224ve Dynamic VoIP block Dynamic VoIP block 105 DYN-VOIP ISP
[403]225li Static IP - LAN/POP Static LAN/POP IP 190 NOC-VPN ISP
[409]226ai Static IP - Management Static management IP 192 NOC-VPN ISP
[403]227bi Static IP - Wifi CPE Wifi CPE IP 193 ISP
228ld Static Pool - LAN/POP LAN pool 195 NOC-VPN ISP
[409]229ad Static Pool - Management Management pool 196 NOC-VPN ISP
[403]230bd Static pool - Wifi CPE Wifi CPE pool 197 ISP
[417]231in Internal netblock Internal netblock 199 5554242 ISP
232wc Reserve for CORE/WAN blocks CORE/WAN blocks 200 5554242 ISP
233pc Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201 5554242 ISP-STATIC-DSL
234ac Reserve for ATM ATM blocks 202 5554242 ISP
235fc Reserve for fibre Fibre blocks 203 5554242 ISP
236wr CORE/WAN block CORE/WAN block 220 5554242 ISP
[403]237pr Dynamic-route DSL netblock (cust) Dynamic-route DSL (cust) 221 ISPCUST
[324]238ar ATM block ATM block 222 ISP
[403]239fr Fibre Fibre 223 ISP
[763]240rm Routing aggregation Routing aggregation 500 5554242 ISP
[417]241mm Master block Master block 999 5554242 ISP
[182]242\.
[218]243
[763]244-- User data table - required for proper ACLs
245CREATE TABLE users (
246 username character varying(256) NOT NULL,
247 "password" character varying(256) DEFAULT ''::character varying,
248 acl character varying(256) DEFAULT 'b'::character varying
[316]249);
250
[763]251ALTER TABLE ONLY users
252 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
[316]253
[763]254-- Default password is admin
[803]255INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsAm');
[763]256
257
[218]258-- Trigger and matching function to update modifystamp on allocations, poolips
259CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
260 BEGIN
261 NEW.modifystamp := ''now'';
262 RETURN NEW;
263 END;
264' LANGUAGE 'plpgsql';
265
266CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
267 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
268
269CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
270 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
Note: See TracBrowser for help on using the repository browser.