1 | -- Initial table setup for IP Database
|
---|
2 |
|
---|
3 | CREATE TABLE "customers" (
|
---|
4 | "custid" character varying(16) DEFAULT '' NOT NULL,
|
---|
5 | "name" character varying(64),
|
---|
6 | "street" character varying(25),
|
---|
7 | "street2" character varying(25),
|
---|
8 | "city" character varying(30),
|
---|
9 | "province" character(2),
|
---|
10 | "country" character(2),
|
---|
11 | "pocode" character varying(7),
|
---|
12 | "phone" character varying(15),
|
---|
13 | "tech_handle" character varying(50),
|
---|
14 | "abuse_handle" character varying(50),
|
---|
15 | "admin_handle" character varying(50),
|
---|
16 | "def_rdns" character varying(40),
|
---|
17 | "special" text,
|
---|
18 | Constraint "customers_pkey" Primary Key ("custid")
|
---|
19 | );
|
---|
20 |
|
---|
21 | CREATE TABLE "masterblocks" (
|
---|
22 | "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
|
---|
23 | "ctime" timestamp DEFAULT now(),
|
---|
24 | "mtime" timestamp DEFAULT now(),
|
---|
25 | "rwhois" character(1) DEFAULT 'n' NOT NULL
|
---|
26 | );
|
---|
27 |
|
---|
28 | CREATE TABLE "routed" (
|
---|
29 | "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
|
---|
30 | "maskbits" integer DEFAULT 128,
|
---|
31 | "city" character varying(30) DEFAULT '',
|
---|
32 | "ctime" timestamp DEFAULT now()
|
---|
33 | );
|
---|
34 |
|
---|
35 | CREATE TABLE "freeblocks" (
|
---|
36 | "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
|
---|
37 | "maskbits" integer DEFAULT 128,
|
---|
38 | "city" character varying(30) DEFAULT '',
|
---|
39 | "routed" character(1) DEFAULT 'n'
|
---|
40 | );
|
---|
41 |
|
---|
42 | CREATE TABLE "poolips" (
|
---|
43 | "pool" cidr DEFAULT '255.255.255.255/32' NOT NULL,
|
---|
44 | "ip" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
|
---|
45 | "city" character varying(30) DEFAULT '' NOT NULL,
|
---|
46 | "type" character(2) DEFAULT '' NOT NULL,
|
---|
47 | "available" character(1) DEFAULT 'y' NOT NULL,
|
---|
48 | "notes" text DEFAULT '' NOT NULL,
|
---|
49 | "description" character varying(64) DEFAULT '' NOT NULL,
|
---|
50 | "circuitid" character varying(128) DEFAULT '' NOT NULL,
|
---|
51 | "privdata" text DEFAULT '' NOT NULL,
|
---|
52 | "custid" character varying(16) DEFAULT '',
|
---|
53 | "createstamp" timestamp DEFAULT now(),
|
---|
54 | "modifystamp" timestamp DEFAULT now(),
|
---|
55 | "vrf" character varying(128) DEFAULT '' NOT NULL,
|
---|
56 | CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
|
---|
57 | );
|
---|
58 |
|
---|
59 | CREATE TABLE "allocations" (
|
---|
60 | "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
|
---|
61 | "type" character(2) DEFAULT '',
|
---|
62 | "city" character varying(30) DEFAULT '',
|
---|
63 | "description" character varying(64) DEFAULT '',
|
---|
64 | "notes" text DEFAULT '',
|
---|
65 | "maskbits" integer DEFAULT 128,
|
---|
66 | "circuitid" character varying(128) DEFAULT '',
|
---|
67 | "createstamp" timestamp DEFAULT now(),
|
---|
68 | "modifystamp" timestamp DEFAULT now(),
|
---|
69 | "privdata" text DEFAULT '' NOT NULL,
|
---|
70 | "custid" character varying(16) DEFAULT '',
|
---|
71 | swip character(1) DEFAULT 'n'
|
---|
72 | "vrf" character varying(128) DEFAULT '' NOT NULL,
|
---|
73 | );
|
---|
74 |
|
---|
75 | CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes, allocations.circuitid, allocations.vrf, 'n' AS available FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes, poolips.circuitid, poolips.vrf, poolips.available FROM poolips;
|
---|
76 |
|
---|
77 | CREATE TABLE "alloctypes" (
|
---|
78 | "type" character(2) DEFAULT '' NOT NULL PRIMARY KEY,
|
---|
79 | "listname" character varying(40) DEFAULT '',
|
---|
80 | "dispname" character varying(40) DEFAULT '',
|
---|
81 | "listorder" integer DEFAULT 0,
|
---|
82 | "def_custid" character varying(16) DEFAULT '',
|
---|
83 | "arin_netname" character varying(20) DEFAULT 'ISP'
|
---|
84 | );
|
---|
85 |
|
---|
86 | --
|
---|
87 | -- Name: alloctypes; Type: TABLE DATA; Schema: public; Owner: ipdb
|
---|
88 | --
|
---|
89 |
|
---|
90 | COPY alloctypes ("type", listname, dispname, listorder, def_custid, arin_netname) FROM stdin;
|
---|
91 | cn Customer netblock Customer netblock 0 ISPCUST
|
---|
92 | si Static IP - Server pool Server pool IP 20 ISP
|
---|
93 | ci Static IP - Cable Static cable IP 21 ISP
|
---|
94 | di Static IP - DSL Static DSL IP 22 ISP
|
---|
95 | mi Static IP - Dialup Static dialup IP 23 ISP
|
---|
96 | wi Static IP - Wireless Static wireless IP 24 ISP
|
---|
97 | sd Static Pool - Servers Server pool 40 5554242 ISP
|
---|
98 | cd Static Pool - Cable Cable pool 41 CBL-BUS ISP-STATIC-CABLE
|
---|
99 | dp Static Pool - DSL DSL pool 42 DSL-BUS ISP-STATIC-DSL
|
---|
100 | mp Static Pool - Dialup Static dialup pool 43 DIAL-BUS ISP-STATIC-DIAL
|
---|
101 | wp Static Pool - Wireless Static wireless pool 44 WL-BUS ISP-STATIC-WIFI
|
---|
102 | en End-use netblock End-use netblock 100 5554242 ISP
|
---|
103 | me Dialup netblock Dialup netblock 101 DIAL-RES ISP-DIAL
|
---|
104 | de Dynamic DSL block Dynamic DSL block 102 DSL-RES ISP-DSL
|
---|
105 | ce Dynamic cable block Dynamic cable block 103 CBL-RES ISP-CABLE
|
---|
106 | we Dynamic WiFi block Dynamic WiFi block 104 WL-RES ISP-WIFI
|
---|
107 | ve Dynamic VoIP block Dynamic VoIP block 105 DYN-VOIP ISP
|
---|
108 | li Static IP - LAN/POP Static LAN/POP IP 190 NOC-VPN ISP
|
---|
109 | ai Static IP - Management Static management IP 192 NOC-VPN ISP
|
---|
110 | bi Static IP - Wifi CPE Wifi CPE IP 193 ISP
|
---|
111 | ld Static Pool - LAN/POP LAN pool 195 NOC-VPN ISP
|
---|
112 | ad Static Pool - Management Management pool 196 NOC-VPN ISP
|
---|
113 | bd Static pool - Wifi CPE Wifi CPE pool 197 ISP
|
---|
114 | in Internal netblock Internal netblock 199 5554242 ISP
|
---|
115 | wc Reserve for CORE/WAN blocks CORE/WAN blocks 200 5554242 ISP
|
---|
116 | pc Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201 5554242 ISP-STATIC-DSL
|
---|
117 | ac Reserve for ATM ATM blocks 202 5554242 ISP
|
---|
118 | fc Reserve for fibre Fibre blocks 203 5554242 ISP
|
---|
119 | wr CORE/WAN block CORE/WAN block 220 5554242 ISP
|
---|
120 | pr Dynamic-route DSL netblock (cust) Dynamic-route DSL (cust) 221 ISPCUST
|
---|
121 | ar ATM block ATM block 222 ISP
|
---|
122 | fr Fibre Fibre 223 ISP
|
---|
123 | rm Routing Routed netblock 500 5554242 ISP
|
---|
124 | mm Master block Master block 999 5554242 ISP
|
---|
125 | \.
|
---|
126 |
|
---|
127 | CREATE TABLE "cities" (
|
---|
128 | "id" serial NOT NULL PRIMARY KEY,
|
---|
129 | "city" character varying(30) DEFAULT '' NOT NULL,
|
---|
130 | "routing" character(1) DEFAULT 'n' NOT NULL
|
---|
131 | );
|
---|
132 |
|
---|
133 | --
|
---|
134 | -- Trigger and matching function to update modifystamp on allocations, poolips
|
---|
135 | --
|
---|
136 | CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
|
---|
137 | BEGIN
|
---|
138 | NEW.modifystamp := ''now'';
|
---|
139 | RETURN NEW;
|
---|
140 | END;
|
---|
141 | ' LANGUAGE 'plpgsql';
|
---|
142 |
|
---|
143 | CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
|
---|
144 | FOR EACH ROW EXECUTE PROCEDURE up_modtime();
|
---|
145 |
|
---|
146 | CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
|
---|
147 | FOR EACH ROW EXECUTE PROCEDURE up_modtime();
|
---|
148 |
|
---|
149 | --
|
---|
150 | -- User data table - required for proper ACLs
|
---|
151 | --
|
---|
152 |
|
---|
153 | CREATE TABLE "users" (
|
---|
154 | "username" varchar(40) NOT NULL PRIMARY KEY,
|
---|
155 | "password" varchar(60) DEFAULT '',
|
---|
156 | "acl" varchar(30) DEFAULT 'b'
|
---|
157 | );
|
---|
158 |
|
---|
159 | -- Default password is admin
|
---|
160 | INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsA');
|
---|
161 |
|
---|
162 | -- Network nodes - allows finding customers affected by a broken <x> quickly
|
---|
163 | CREATE TABLE noderef (
|
---|
164 | block inet NOT NULL PRIMARY KEY,
|
---|
165 | node_id integer
|
---|
166 | );
|
---|
167 |
|
---|
168 | CREATE TABLE nodes (
|
---|
169 | node_id serial NOT NULL PRIMARY KEY,
|
---|
170 | node_type character varying(2),
|
---|
171 | node_name character varying(40),
|
---|
172 | node_ip inet
|
---|
173 | );
|
---|
174 |
|
---|
175 | -- Email notifications on <action>
|
---|
176 | CREATE TABLE notify (
|
---|
177 | action varchar(5) NOT NULL PRIMARY KEY,
|
---|
178 | reciplist varchar(500)
|
---|
179 | );
|
---|