source: branches/dns/cgi-bin/ipdb.psql@ 953

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

/branches/dns

Update branch base with trunk changes from r216:261

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