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

Last change on this file since 214 was 189, checked in by Kris Deugau, 19 years ago

/trunk

Caught some buglets in the HairyPerl(TM) ('$1' vs "$1")
Corrected a potentially annoying SQL buglet relating to the

selection of which netblock an allocation is to be taken from;
under certain conditions it would pick a larger block to slice
up while there was still a perfectly usable block just the
right size waiting to be allocated.

Removed display of back-end alloctypes from success/failure notes
Corrected comments referring to alloctypes that have been altered
Updated IPDB "default" schema and alloctypes list with current

base information

File size: 5.5 KB
Line 
1CREATE DATABASE ipdb;
2
3\connect ipdb ipdb
4
5CREATE TABLE "customers" (
6 "custid" character varying(16) DEFAULT '' NOT NULL,
7 "name" character varying(64),
8 "street" character varying(25),
9 "street2" character varying(25),
10 "city" character varying(30),
11 "province" character(2),
12 "pocode" character varying(7),
13 "phone" character varying(15),
14 "abuse" character varying(50),
15 "def_rdns" character varying(40),
16 "description" text,
17 Constraint "customers_pkey" Primary Key ("custid")
18);
19
20REVOKE ALL on "customers" from PUBLIC;
21GRANT ALL on "customers" to "kdeugau";
22GRANT ALL on "customers" to "ipdb";
23
24CREATE TABLE "masterblocks" (
25 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY
26);
27
28REVOKE ALL on "masterblocks" from PUBLIC;
29GRANT ALL on "masterblocks" to "kdeugau";
30GRANT ALL on "masterblocks" to "ipdb";
31
32CREATE TABLE "routed" (
33 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
34 "maskbits" integer DEFAULT 128,
35 "city" character varying(30) DEFAULT ''
36);
37
38REVOKE ALL on "routed" from PUBLIC;
39GRANT ALL on "routed" to "kdeugau";
40GRANT ALL on "routed" to "ipdb";
41
42CREATE TABLE "temp" (
43 "ofs" integer
44);
45
46REVOKE ALL on "temp" from PUBLIC;
47GRANT ALL on "temp" to "kdeugau";
48GRANT ALL on "temp" to "ipdb";
49
50CREATE TABLE "freeblocks" (
51 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
52 "maskbits" integer DEFAULT 128,
53 "city" character varying(30) DEFAULT '',
54 "routed" character(1) DEFAULT 'n'
55);
56
57REVOKE ALL on "freeblocks" from PUBLIC;
58GRANT ALL on "freeblocks" to "kdeugau";
59GRANT ALL on "freeblocks" to "ipdb";
60
61CREATE TABLE "poolips" (
62 "pool" cidr DEFAULT '255.255.255.255/32' NOT NULL,
63 "ip" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
64 "custid" character varying(16) DEFAULT '' NOT NULL,
65 "city" character varying(30) DEFAULT '' NOT NULL,
66 "type" character(2) DEFAULT '' NOT NULL,
67 "available" character(1) DEFAULT 'y' NOT NULL,
68 "notes" text DEFAULT '' NOT NULL,
69 "description" character varying(64) DEFAULT '' NOT NULL,
70 "circuitid" character varying(128) DEFAULT '' NOT NULL,
71 "newcustid" integer,
72 CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
73);
74
75REVOKE ALL on "poolips" from PUBLIC;
76GRANT ALL on "poolips" to "kdeugau";
77GRANT ALL on "poolips" to "ipdb";
78
79CREATE TABLE "allocations" (
80 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
81 "custid" character varying(16) DEFAULT '',
82 "type" character(2) DEFAULT '',
83 "city" character varying(30) DEFAULT '',
84 "description" character varying(64) DEFAULT '',
85 "notes" text DEFAULT '',
86 "maskbits" integer DEFAULT 128,
87 "circuitid" character varying(128) DEFAULT '',
88 "newcustid" integer
89);
90
91REVOKE ALL on "allocations" from PUBLIC;
92GRANT ALL on "allocations" to "kdeugau";
93GRANT ALL on "allocations" to "ipdb";
94
95CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description FROM poolips;
96
97REVOKE ALL on "searchme" from PUBLIC;
98GRANT ALL on "searchme" to "kdeugau";
99GRANT ALL on "searchme" to "ipdb";
100
101CREATE TABLE "alloctypes" (
102 "type" character(2) DEFAULT '' NOT NULL PRIMARY KEY,
103 "listname" character varying(40) DEFAULT '',
104 "dispname" character varying(40) DEFAULT '',
105 "listorder" integer DEFAULT 0,
106 "def_custid" character varying(16) DEFAULT ''
107);
108
109REVOKE ALL on "alloctypes" from PUBLIC;
110GRANT ALL on "alloctypes" to "kdeugau";
111GRANT ALL on "alloctypes" to "ipdb";
112
113CREATE TABLE "cities" (
114 "city" character varying(30) DEFAULT '' NOT NULL PRIMARY KEY,
115 "routing" character(1) DEFAULT 'n' NOT NULL
116);
117
118REVOKE ALL on "cities" from PUBLIC;
119GRANT ALL on "cities" to "kdeugau";
120GRANT ALL on "cities" to "ipdb";
121
122--
123-- Selected TOC Entries:
124--
125\connect - ipdb
126
127--
128-- TOC Entry ID 2 (OID 92809)
129--
130-- Name: alloctypes Type: TABLE Owner: ipdb
131--
132
133CREATE TABLE "alloctypes" (
134 "type" character(2) DEFAULT '' NOT NULL,
135 "listname" character varying(40) DEFAULT '',
136 "dispname" character varying(40) DEFAULT '',
137 "listorder" integer DEFAULT 0,
138 "def_custid" character varying(16) DEFAULT '',
139 Constraint "alloctypes_pkey" Primary Key ("type")
140);
141
142--
143-- TOC Entry ID 3 (OID 92809)
144--
145-- Name: alloctypes Type: ACL Owner:
146--
147
148REVOKE ALL on "alloctypes" from PUBLIC;
149GRANT ALL on "alloctypes" to "kdeugau";
150GRANT ALL on "alloctypes" to "ipdb";
151
152--
153-- Data for TOC Entry ID 4 (OID 92809)
154--
155-- Name: alloctypes Type: TABLE DATA Owner: ipdb
156--
157
158
159COPY "alloctypes" FROM stdin;
160cd Static Pool - Cable Cable pool 41 CBL-BUS
161dp Static Pool - DSL DSL pool 42 DSL-BUS
162mp Static Pool - Dialup Static dialup pool 43 DIAL-BUS
163wp Static Pool - Wireless Static wireless pool 44 WL-BUS
164mm Master block Master block 999 6750400
165in Internal netblock Internal netblock 990 6750400
166sd Static Pool - Servers Server pool 40 6750400
167cn Customer netblock Customer netblock 0
168ci Static IP - Cable Static cable IP 21
169di Static IP - DSL Static DSL IP 22
170mi Static IP - Dialup Static dialup IP 23
171wi Static IP - Wireless Static wireless IP 24
172si Static IP - Server pool Server pool IP 20 6750400
173wc Reserve for WAN blocks WAN IP blocks 200 6750400
174wr Internal WAN block Internal WAN block 201 6750400
175pc Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 202 6750400
176en End-use netblock End-use netblock 100 6750400
177me Dialup netblock Dialup netblock 101 DIAL-RES
178de Dynamic DSL block Dynamic DSL block 102 DSL-RES
179ce Dynamic cable block Dynamic cable block 103 CBL-RES
180we Dynamic WiFi block Dynamic WiFi block 104 WL-RES
181rm Routing Routed netblock 500 6750400
182pr Dynamic-route DSL netblock Dynamic-route DSL 203
183\.
Note: See TracBrowser for help on using the repository browser.