source: trunk/cgi-bin/ipdb-2.7-3.0.sql@ 707

Last change on this file since 707 was 691, checked in by Kris Deugau, 10 years ago

/trunk

Expose VRF field, and add a similar VLAN field (see #10).

File size: 2.8 KB
Line 
1-- Baseline SQL to add necessary fields for IPDB 2.7 -> IPDB 3.0
2-- Note some data changes must be made via script.
3
4-- WARNING: This SQL is backwards-compatible, but once db-update.pl
5-- has run, IPDB v2.7 and older will NOT be able to properly manipulate
6-- the data!
7
8-- Master and routed blocks now live in the allocations table.
9
10ALTER TABLE allocations ADD COLUMN vrf text NOT NULL DEFAULT '';
11ALTER TABLE allocations ADD COLUMN vlan text NOT NULL DEFAULT '';
12ALTER TABLE allocations ADD COLUMN rdns text NOT NULL DEFAULT '';
13ALTER TABLE allocations ADD COLUMN parent_id integer NOT NULL DEFAULT 0;
14ALTER TABLE allocations ADD COLUMN master_id integer NOT NULL DEFAULT 0;
15ALTER TABLE allocations DROP CONSTRAINT allocations_pkey;
16ALTER TABLE allocations ADD COLUMN id serial PRIMARY KEY;
17
18ALTER TABLE freeblocks ADD COLUMN vrf text NOT NULL DEFAULT '';
19ALTER TABLE freeblocks ADD COLUMN parent_id integer NOT NULL DEFAULT 0;
20ALTER TABLE freeblocks ADD COLUMN master_id integer NOT NULL DEFAULT 0;
21ALTER TABLE freeblocks DROP CONSTRAINT freeblocks_pkey;
22ALTER TABLE freeblocks ADD CONSTRAINT freeblocks_pkey PRIMARY KEY ("cidr","parent_id");
23ALTER TABLE freeblocks ADD COLUMN id serial;
24
25ALTER TABLE poolips ADD COLUMN vrf text NOT NULL DEFAULT '';
26ALTER TABLE poolips ADD COLUMN vlan text NOT NULL DEFAULT '';
27ALTER TABLE poolips ADD COLUMN rdns text NOT NULL DEFAULT '';
28ALTER TABLE poolips ADD COLUMN parent_id integer NOT NULL DEFAULT 0;
29ALTER TABLE poolips ADD COLUMN master_id integer NOT NULL DEFAULT 0;
30ALTER TABLE poolips DROP CONSTRAINT poolips_pkey;
31ALTER TABLE poolips ADD COLUMN id serial;
32ALTER TABLE poolips ADD CONSTRAINT poolips_pkey PRIMARY KEY ("ip", "parent_id");
33
34-- probably need some more indexes
35
36-- Need some additional fields to support search output. Might arguably
37-- move pool IPs into the allocations table; we'll see.
38DROP VIEW searchme;
39CREATE VIEW "searchme" AS
40 SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city,
41 allocations.description, allocations.notes, allocations.circuitid, allocations.vrf,
42 allocations.vlan, allocations.id, allocations.parent_id, 'n' AS available
43 FROM allocations
44 UNION
45 SELECT poolips.ip, poolips.custid, poolips.type, poolips.city,
46 poolips.description, poolips.notes, poolips.circuitid, poolips.vrf,
47 poolips.vlan, poolips.id, poolips.parent_id, poolips.available
48 FROM poolips;
49
50-- Relabel a few types to match the new structure
51UPDATE alloctypes SET listname='Routing aggregation', dispname='Routing aggregation' WHERE type='rm';
52
53-- Not critical but the previous definitions were too restrictive (eg, can't
54-- handle MD5, or SHA password hashes). Plans for ACLs also require larger fields.
55ALTER TABLE users ALTER COLUMN username TYPE varchar(256);
56ALTER TABLE users ALTER COLUMN password TYPE varchar(256);
57ALTER TABLE users ALTER COLUMN acl TYPE varchar(256);
Note: See TracBrowser for help on using the repository browser.