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

Last change on this file since 188 was 185, checked in by Kris Deugau, 20 years ago

/branches/stable

Backport SQL/SQL-related bugfixes from /trunk r180 through 184

File size: 4.6 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
122COPY "alloctypes" FROM stdin;
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
127dc Dynamic cable block Dynamic cable block 103 CBL-RES
128dy Dynamic DSL block Dynamic DSL block 102 DSL-RES
129dn Dialup netblock Dialup netblock 101 DIAL-RES
130dw Dynamic WiFi block Dynamic WiFi block 104 WL-RES
131mm Master block Master block 999 6750400
132rr Routing Routed netblock 500 6750400
133in Internal netblock Internal netblock 990 6750400
134ee End-use netblock End-use netblock 100 6750400
135sd Static Pool - Servers Server pool 40 6750400
136cn Customer netblock Customer netblock 0
137ci Static IP - Cable Static cable IP 21
138di Static IP - DSL Static DSL IP 22
139mi Static IP - Dialup Static dialup IP 23
140wi Static IP - Wireless Static wireless IP 24
141si Static IP - Server pool Server pool IP 20 6750400
142\.
Note: See TracBrowser for help on using the repository browser.