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

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

/trunk

Add VRF table to initial tabledef

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