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

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

/trunk

Update initial SQL definition file with current structure

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