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

Last change on this file since 497 was 455, checked in by Kris Deugau, 14 years ago

/trunk

Remove reference to legacy oldcustid field - leftovers from a
billing system transition. See #26.

File size: 7.6 KB
RevLine 
[347]1DROP DATABASE ipdb;
2
3CREATE USER ipdb WITH PASSWORD 'ipdbpwd';
4
[181]5CREATE DATABASE ipdb;
[4]6
[347]7-- Need to do this or our triggers don't work. Why do we need to do this?
8CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;
9CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";
10
11UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE usename='ipdb')
12 WHERE datname='ipdb';
13
[176]14\connect ipdb ipdb
[4]15
16CREATE TABLE "customers" (
[347]17 "custid" character varying(16) DEFAULT '' NOT NULL,
18 "name" character varying(64),
19 "street" character varying(25),
20 "street2" character varying(25),
21 "city" character varying(30),
22 "province" character(2),
23 "country" character(2),
24 "pocode" character varying(7),
25 "phone" character varying(15),
26 "tech_handle" character varying(50),
27 "abuse_handle" character varying(50),
28 "admin_handle" character varying(50),
29 "def_rdns" character varying(40),
30 "special" text,
31 Constraint "customers_pkey" Primary Key ("custid")
[4]32);
33
[34]34REVOKE ALL on "customers" from PUBLIC;
35GRANT ALL on "customers" to "ipdb";
36
[4]37CREATE TABLE "masterblocks" (
[219]38 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
[324]39 "ctime" timestamp DEFAULT now(),
[371]40 "mtime" timestamp DEFAULT now(),
41 "rwhois" character(1) DEFAULT 'n' NOT NULL
[4]42);
43
[34]44REVOKE ALL on "masterblocks" from PUBLIC;
45GRANT ALL on "masterblocks" to "ipdb";
46
[4]47CREATE TABLE "routed" (
[176]48 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
[4]49 "maskbits" integer DEFAULT 128,
[219]50 "city" character varying(30) DEFAULT '',
51 "ctime" timestamp DEFAULT now()
[4]52);
53
[34]54REVOKE ALL on "routed" from PUBLIC;
55GRANT ALL on "routed" to "ipdb";
[316]56GRANT SELECT on "routed" to "ipdb";
[34]57
[4]58CREATE TABLE "temp" (
59 "ofs" integer
60);
61
[34]62REVOKE ALL on "temp" from PUBLIC;
63GRANT ALL on "temp" to "ipdb";
64
[57]65CREATE TABLE "freeblocks" (
[180]66 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
[57]67 "maskbits" integer DEFAULT 128,
68 "city" character varying(30) DEFAULT '',
[176]69 "routed" character(1) DEFAULT 'n'
[34]70);
71
[57]72REVOKE ALL on "freeblocks" from PUBLIC;
73GRANT ALL on "freeblocks" to "ipdb";
[34]74
[74]75CREATE TABLE "poolips" (
76 "pool" cidr DEFAULT '255.255.255.255/32' NOT NULL,
[176]77 "ip" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
[175]78 "city" character varying(30) DEFAULT '' NOT NULL,
79 "type" character(2) DEFAULT '' NOT NULL,
80 "available" character(1) DEFAULT 'y' NOT NULL,
81 "notes" text DEFAULT '' NOT NULL,
82 "description" character varying(64) DEFAULT '' NOT NULL,
83 "circuitid" character varying(128) DEFAULT '' NOT NULL,
[284]84 "privdata" text DEFAULT '' NOT NULL,
[347]85 "custid" character varying(16) DEFAULT '',
[218]86 "createstamp" timestamp DEFAULT now(),
87 "modifystamp" timestamp DEFAULT now(),
[176]88 CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
[74]89);
90
91REVOKE ALL on "poolips" from PUBLIC;
92GRANT ALL on "poolips" to "ipdb";
93
94CREATE TABLE "allocations" (
[176]95 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
[74]96 "type" character(2) DEFAULT '',
97 "city" character varying(30) DEFAULT '',
98 "description" character varying(64) DEFAULT '',
99 "notes" text DEFAULT '',
100 "maskbits" integer DEFAULT 128,
101 "circuitid" character varying(128) DEFAULT '',
[218]102 "createstamp" timestamp DEFAULT now(),
103 "modifystamp" timestamp DEFAULT now(),
[284]104 "privdata" text DEFAULT '' NOT NULL,
[347]105 "custid" character varying(16) DEFAULT '',
[316]106 swip character(1) DEFAULT 'n'
[74]107);
108
109REVOKE ALL on "allocations" from PUBLIC;
110GRANT ALL on "allocations" to "ipdb";
111
[455]112CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes, allocations.circuitid FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes, poolips.circuitid FROM poolips;
[92]113
[176]114REVOKE ALL on "searchme" from PUBLIC;
115GRANT ALL on "searchme" to "ipdb";
116
[92]117CREATE TABLE "alloctypes" (
[176]118 "type" character(2) DEFAULT '' NOT NULL PRIMARY KEY,
[92]119 "listname" character varying(40) DEFAULT '',
120 "dispname" character varying(40) DEFAULT '',
121 "listorder" integer DEFAULT 0,
[324]122 "def_custid" character varying(16) DEFAULT '',
[347]123 "arin_netname" character varying(20) DEFAULT 'ISP'
[92]124);
125
[321]126--
127-- Name: alloctypes; Type: TABLE DATA; Schema: public; Owner: ipdb
128--
129
[388]130COPY alloctypes ("type", listname, dispname, listorder, def_custid, arin_netname) FROM stdin;
[324]131cn Customer netblock Customer netblock 0 ISPCUST
[403]132si Static IP - Server pool Server pool IP 20 ISP
133ci Static IP - Cable Static cable IP 21 ISP
134di Static IP - DSL Static DSL IP 22 ISP
135mi Static IP - Dialup Static dialup IP 23 ISP
136wi Static IP - Wireless Static wireless IP 24 ISP
[417]137sd Static Pool - Servers Server pool 40 5554242 ISP
[324]138cd Static Pool - Cable Cable pool 41 CBL-BUS ISP-STATIC-CABLE
139dp Static Pool - DSL DSL pool 42 DSL-BUS ISP-STATIC-DSL
140mp Static Pool - Dialup Static dialup pool 43 DIAL-BUS ISP-STATIC-DIAL
141wp Static Pool - Wireless Static wireless pool 44 WL-BUS ISP-STATIC-WIFI
[417]142en End-use netblock End-use netblock 100 5554242 ISP
[324]143me Dialup netblock Dialup netblock 101 DIAL-RES ISP-DIAL
144de Dynamic DSL block Dynamic DSL block 102 DSL-RES ISP-DSL
145ce Dynamic cable block Dynamic cable block 103 CBL-RES ISP-CABLE
146we Dynamic WiFi block Dynamic WiFi block 104 WL-RES ISP-WIFI
147ve Dynamic VoIP block Dynamic VoIP block 105 DYN-VOIP ISP
[403]148li Static IP - LAN/POP Static LAN/POP IP 190 NOC-VPN ISP
[409]149ai Static IP - Management Static management IP 192 NOC-VPN ISP
[403]150bi Static IP - Wifi CPE Wifi CPE IP 193 ISP
151ld Static Pool - LAN/POP LAN pool 195 NOC-VPN ISP
[409]152ad Static Pool - Management Management pool 196 NOC-VPN ISP
[403]153bd Static pool - Wifi CPE Wifi CPE pool 197 ISP
[417]154in Internal netblock Internal netblock 199 5554242 ISP
155wc Reserve for CORE/WAN blocks CORE/WAN blocks 200 5554242 ISP
156pc Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201 5554242 ISP-STATIC-DSL
157ac Reserve for ATM ATM blocks 202 5554242 ISP
158fc Reserve for fibre Fibre blocks 203 5554242 ISP
159wr CORE/WAN block CORE/WAN block 220 5554242 ISP
[403]160pr Dynamic-route DSL netblock (cust) Dynamic-route DSL (cust) 221 ISPCUST
[324]161ar ATM block ATM block 222 ISP
[403]162fr Fibre Fibre 223 ISP
[417]163rm Routing Routed netblock 500 5554242 ISP
164mm Master block Master block 999 5554242 ISP
[182]165\.
[218]166
[316]167REVOKE ALL on "alloctypes" from PUBLIC;
168GRANT ALL on "alloctypes" to "ipdb";
169
170CREATE TABLE "cities" (
171 "id" serial NOT NULL PRIMARY KEY,
172 "city" character varying(30) DEFAULT '' NOT NULL,
173 "routing" character(1) DEFAULT 'n' NOT NULL
174);
175
176REVOKE ALL on "cities" from PUBLIC;
177GRANT ALL on "cities" to "ipdb";
178
[218]179--
180-- Trigger and matching function to update modifystamp on allocations, poolips
181--
182CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
183 BEGIN
184 NEW.modifystamp := ''now'';
185 RETURN NEW;
186 END;
187' LANGUAGE 'plpgsql';
188
189CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
190 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
191
192CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
193 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
[233]194
195--
196-- User data table - required for proper ACLs
197--
198
199CREATE TABLE "users" (
[237]200 "username" varchar(16) NOT NULL PRIMARY KEY,
[233]201 "password" varchar(16) DEFAULT '',
202 "acl" varchar(16) DEFAULT 'b'
203);
[257]204
[347]205-- Default password is admin
206INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsA');
207
[257]208CREATE TABLE "dns" (
209 "ip" inet NOT NULL PRIMARY KEY,
210 "hostname" character varying(128),
211 "auto" character(1) DEFAULT 'y'
212);
[397]213
214-- Network nodes - allows finding customers affected by a broken <x> quickly
215CREATE TABLE noderef (
216 block inet NOT NULL PRIMARY KEY,
217 node_id integer
218);
219
220CREATE TABLE nodes (
221 node_id serial NOT NULL PRIMARY KEY,
222 node_type character varying(2),
223 node_name character varying(40),
224 node_ip inet
225);
[416]226
227-- Email notifications on <action>
228CREATE TABLE notify (
229 action varchar(5) NOT NULL PRIMARY KEY,
230 reciplist varchar(500)
231);
Note: See TracBrowser for help on using the repository browser.