source: branches/stable/cgi-bin/ipdb.psql @ 286

Last change on this file since 286 was 286, checked in by Kris Deugau, 17 years ago

/branches/stable

Merge changes from /trunk revisions:

234
237
254 (ipdb.css only)
261
279
284
285

This merges the new search system (234, 237, 254), cleans up
some display CSS (254, 279), cleans up some leftover code (r261),
and merges the "private data" code (284, 285 - note SWIP hacks conflict).

/trunk should now be almost identical to /branches/stable.

File size: 5.9 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        "privdata" text DEFAULT '' NOT NULL,
72        "newcustid" integer,
73        CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
74);
75
76REVOKE ALL on "poolips" from PUBLIC;
77GRANT ALL on "poolips" to "kdeugau";
78GRANT ALL on "poolips" to "ipdb";
79
80CREATE TABLE "allocations" (
81        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
82        "custid" character varying(16) DEFAULT '',
83        "type" character(2) DEFAULT '',
84        "city" character varying(30) DEFAULT '',
85        "description" character varying(64) DEFAULT '',
86        "notes" text DEFAULT '',
87        "maskbits" integer DEFAULT 128,
88        "circuitid" character varying(128) DEFAULT '',
89        "privdata" text DEFAULT '' NOT NULL,
90        "newcustid" integer
91);
92
93REVOKE ALL on "allocations" from PUBLIC;
94GRANT ALL on "allocations" to "kdeugau";
95GRANT ALL on "allocations" to "ipdb";
96
97CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes FROM poolips;
98
99REVOKE ALL on "searchme" from PUBLIC;
100GRANT ALL on "searchme" to "kdeugau";
101GRANT ALL on "searchme" to "ipdb";
102
103CREATE TABLE "alloctypes" (
104        "type" character(2) DEFAULT '' NOT NULL PRIMARY KEY,
105        "listname" character varying(40) DEFAULT '',
106        "dispname" character varying(40) DEFAULT '',
107        "listorder" integer DEFAULT 0,
108        "def_custid" character varying(16) DEFAULT ''
109);
110
111REVOKE ALL on "alloctypes" from PUBLIC;
112GRANT ALL on "alloctypes" to "kdeugau";
113GRANT ALL on "alloctypes" to "ipdb";
114
115CREATE TABLE "cities" (
116        "city" character varying(30) DEFAULT '' NOT NULL PRIMARY KEY,
117        "routing" character(1) DEFAULT 'n' NOT NULL
118);
119
120REVOKE ALL on "cities" from PUBLIC;
121GRANT ALL on "cities" to "kdeugau";
122GRANT ALL on "cities" to "ipdb";
123
124--
125-- Selected TOC Entries:
126--
127\connect - ipdb
128
129--
130-- TOC Entry ID 2 (OID 92809)
131--
132-- Name: alloctypes Type: TABLE Owner: ipdb
133--
134
135CREATE TABLE "alloctypes" (
136        "type" character(2) DEFAULT '' NOT NULL,
137        "listname" character varying(40) DEFAULT '',
138        "dispname" character varying(40) DEFAULT '',
139        "listorder" integer DEFAULT 0,
140        "def_custid" character varying(16) DEFAULT '',
141        Constraint "alloctypes_pkey" Primary Key ("type")
142);
143
144--
145-- TOC Entry ID 3 (OID 92809)
146--
147-- Name: alloctypes Type: ACL Owner:
148--
149
150REVOKE ALL on "alloctypes" from PUBLIC;
151GRANT ALL on "alloctypes" to "kdeugau";
152GRANT ALL on "alloctypes" to "ipdb";
153
154--
155-- Data for TOC Entry ID 4 (OID 92809)
156--
157-- Name: alloctypes Type: TABLE DATA Owner: ipdb
158--
159
160
161COPY "alloctypes" FROM stdin;
162cd      Static Pool - Cable     Cable pool      41      CBL-BUS
163dp      Static Pool - DSL       DSL pool        42      DSL-BUS
164mp      Static Pool - Dialup    Static dialup pool      43      DIAL-BUS
165wp      Static Pool - Wireless  Static wireless pool    44      WL-BUS
166mm      Master block    Master block    999     6750400
167in      Internal netblock       Internal netblock       990     6750400
168sd      Static Pool - Servers   Server pool     40      6750400
169cn      Customer netblock       Customer netblock       0       
170ci      Static IP - Cable       Static cable IP 21     
171di      Static IP - DSL Static DSL IP   22     
172mi      Static IP - Dialup      Static dialup IP        23     
173wi      Static IP - Wireless    Static wireless IP      24     
174si      Static IP - Server pool Server pool IP  20      6750400
175wc      Reserve for WAN blocks  WAN IP blocks   200     6750400
176wr      Internal WAN block      Internal WAN block      201     6750400
177pc      Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 202     6750400
178en      End-use netblock        End-use netblock        100     6750400
179me      Dialup netblock Dialup netblock 101     DIAL-RES
180de      Dynamic DSL block       Dynamic DSL block       102     DSL-RES
181ce      Dynamic cable block     Dynamic cable block     103     CBL-RES
182we      Dynamic WiFi block      Dynamic WiFi block      104     WL-RES
183rm      Routing Routed netblock 500     6750400
184pr      Dynamic-route DSL netblock      Dynamic-route DSL       203     
185li      Static IP - LAN/POP     Static LAN/POP IP       190     6750400
186ld      Static Pool - LAN/POP   LAN pool        191     6750400
187\.
188
189
190--
191-- User data table - required for proper ACLs
192--
193
194CREATE TABLE "users" (
195        "username" varchar(16) NOT NULL PRIMARY KEY,
196        "password" varchar(16) DEFAULT '',
197        "acl" varchar(16) DEFAULT 'b'
198);
Note: See TracBrowser for help on using the repository browser.