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

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

/trunk

Update SQL tabledefs and "default" alloctypes with current alloctype list

File size: 6.1 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 "ipdb";
22
23CREATE TABLE "masterblocks" (
24 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
25 "ctime" timestamp DEFAULT now()
26);
27
28REVOKE ALL on "masterblocks" from PUBLIC;
29GRANT ALL on "masterblocks" to "ipdb";
30
31CREATE TABLE "routed" (
32 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
33 "maskbits" integer DEFAULT 128,
34 "city" character varying(30) DEFAULT '',
35 "ctime" timestamp DEFAULT now()
36);
37
38REVOKE ALL on "routed" from PUBLIC;
39GRANT ALL on "routed" to "ipdb";
40GRANT SELECT on "routed" to "ipdb";
41
42CREATE TABLE "temp" (
43 "ofs" integer
44);
45
46REVOKE ALL on "temp" from PUBLIC;
47GRANT ALL on "temp" to "ipdb";
48
49CREATE TABLE "freeblocks" (
50 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
51 "maskbits" integer DEFAULT 128,
52 "city" character varying(30) DEFAULT '',
53 "routed" character(1) DEFAULT 'n'
54);
55
56REVOKE ALL on "freeblocks" from PUBLIC;
57GRANT ALL on "freeblocks" to "ipdb";
58
59CREATE TABLE "poolips" (
60 "pool" cidr DEFAULT '255.255.255.255/32' NOT NULL,
61 "ip" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
62 "custid" character varying(16) DEFAULT '' NOT NULL,
63 "city" character varying(30) DEFAULT '' NOT NULL,
64 "type" character(2) DEFAULT '' NOT NULL,
65 "available" character(1) DEFAULT 'y' NOT NULL,
66 "notes" text DEFAULT '' NOT NULL,
67 "description" character varying(64) DEFAULT '' NOT NULL,
68 "circuitid" character varying(128) DEFAULT '' NOT NULL,
69 "privdata" text DEFAULT '' NOT NULL,
70 "newcustid" character varying(16) DEFAULT '',
71 "createstamp" timestamp DEFAULT now(),
72 "modifystamp" timestamp DEFAULT now(),
73 CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
74);
75
76REVOKE ALL on "poolips" from PUBLIC;
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 "createstamp" timestamp DEFAULT now(),
89 "modifystamp" timestamp DEFAULT now(),
90 "privdata" text DEFAULT '' NOT NULL,
91 "newcustid" character varying(16) DEFAULT '',
92 swip character(1) DEFAULT 'n'
93);
94
95REVOKE ALL on "allocations" from PUBLIC;
96GRANT ALL on "allocations" to "ipdb";
97
98CREATE 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;
99
100REVOKE ALL on "searchme" from PUBLIC;
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
111--
112-- Name: alloctypes; Type: TABLE DATA; Schema: public; Owner: ipdb
113--
114
115COPY alloctypes ("type", listname, dispname, listorder, def_custid) FROM stdin;
116cn Customer netblock Customer netblock 0
117si Static IP - Server pool Server pool IP 20
118ci Static IP - Cable Static cable IP 21
119di Static IP - DSL Static DSL IP 22
120mi Static IP - Dialup Static dialup IP 23
121wi Static IP - Wireless Static wireless IP 24
122sd Static Pool - Servers Server pool 40 6750400
123cd Static Pool - Cable Cable pool 41 CBL-BUS
124dp Static Pool - DSL DSL pool 42 DSL-BUS
125mp Static Pool - Dialup Static dialup pool 43 DIAL-BUS
126wp Static Pool - Wireless Static wireless pool 44 WL-BUS
127en End-use netblock End-use netblock 100 6750400
128me Dialup netblock Dialup netblock 101 DIAL-RES
129de Dynamic DSL block Dynamic DSL block 102 DSL-RES
130ce Dynamic cable block Dynamic cable block 103 CBL-RES
131we Dynamic WiFi block Dynamic WiFi block 104 WL-RES
132ve Dynamic VoIP block Dynamic VoIP block 105 DYN-VOIP
133li Static IP - LAN/POP Static LAN/POP IP 190 6750400
134ai Static IP - Managment Static management IP 192 6750400
135ad Static Pool - Managment Management pool 196 6750400
136ld Static Pool - LAN/POP LAN pool 195 6750400
137wc Reserve for CORE/WAN blocks CORE/WAN blocks 200 6750400
138pc Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201 6750400
139ac Reserve for ATM ATM blocks 202 6750400
140wr CORE/WAN block CORE/WAN block 220 6750400
141pr Dynamic-route DSL netblock Dynamic-route DSL 221
142ar ATM block ATM block 222
143rm Routing Routed netblock 500 6750400
144in Internal netblock Internal netblock 990 6750400
145mm Master block Master block 999 6750400
146\.
147
148REVOKE ALL on "alloctypes" from PUBLIC;
149GRANT ALL on "alloctypes" to "ipdb";
150
151CREATE TABLE "cities" (
152 "id" serial NOT NULL PRIMARY KEY,
153 "city" character varying(30) DEFAULT '' NOT NULL,
154 "routing" character(1) DEFAULT 'n' NOT NULL
155);
156
157REVOKE ALL on "cities" from PUBLIC;
158GRANT ALL on "cities" to "ipdb";
159
160--
161-- Trigger and matching function to update modifystamp on allocations, poolips
162--
163CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
164 BEGIN
165 NEW.modifystamp := ''now'';
166 RETURN NEW;
167 END;
168' LANGUAGE 'plpgsql';
169
170CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
171 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
172
173CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
174 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
175
176--
177-- User data table - required for proper ACLs
178--
179
180CREATE TABLE "users" (
181 "username" varchar(16) NOT NULL PRIMARY KEY,
182 "password" varchar(16) DEFAULT '',
183 "acl" varchar(16) DEFAULT 'b'
184);
185
186CREATE TABLE "dns" (
187 "ip" inet NOT NULL PRIMARY KEY,
188 "hostname" character varying(128),
189 "auto" character(1) DEFAULT 'y'
190);
Note: See TracBrowser for help on using the repository browser.