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

Last change on this file since 770 was 762, checked in by Kris Deugau, 9 years ago

/trunk

Update 2.7 -> 3.0 SQL upgrade script

File size: 3.2 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-- Flag table for deciding if we can usefully do rDNS RPC calls.
9CREATE TABLE dnsavail (
10 "zone" cidr PRIMARY KEY,
11 "location" varchar(2) DEFAULT '',
12 parent_alloc integer NOT NULL
13);
14
15-- Master and routed blocks now live in the allocations table.
16
17ALTER TABLE allocations ADD COLUMN vrf text NOT NULL DEFAULT '';
18ALTER TABLE allocations ADD COLUMN vlan text NOT NULL DEFAULT '';
19ALTER TABLE allocations ADD COLUMN rdns text NOT NULL DEFAULT '';
20ALTER TABLE allocations ADD COLUMN parent_id integer NOT NULL DEFAULT 0;
21ALTER TABLE allocations ADD COLUMN master_id integer NOT NULL DEFAULT 0;
22ALTER TABLE allocations DROP CONSTRAINT allocations_pkey;
23ALTER TABLE allocations ADD COLUMN id serial PRIMARY KEY;
24CREATE UNIQUE INDEX allocations_skey ON allocations (cidr,vrf,type);
25
26ALTER TABLE freeblocks ADD COLUMN vrf text NOT NULL DEFAULT '';
27ALTER TABLE freeblocks ADD COLUMN parent_id integer NOT NULL DEFAULT 0;
28ALTER TABLE freeblocks ADD COLUMN master_id integer NOT NULL DEFAULT 0;
29ALTER TABLE freeblocks ADD COLUMN reserve_for integer NOT NULL DEFAULT 0;
30ALTER TABLE freeblocks DROP CONSTRAINT freeblocks_pkey;
31ALTER TABLE freeblocks ADD CONSTRAINT freeblocks_pkey PRIMARY KEY ("cidr","parent_id");
32ALTER TABLE freeblocks ADD COLUMN id serial;
33
34ALTER TABLE poolips ADD COLUMN vrf text NOT NULL DEFAULT '';
35ALTER TABLE poolips ADD COLUMN vlan text NOT NULL DEFAULT '';
36ALTER TABLE poolips ADD COLUMN rdns text NOT NULL DEFAULT '';
37ALTER TABLE poolips ADD COLUMN parent_id integer NOT NULL DEFAULT 0;
38ALTER TABLE poolips ADD COLUMN master_id integer NOT NULL DEFAULT 0;
39ALTER TABLE poolips DROP CONSTRAINT poolips_pkey;
40ALTER TABLE poolips ADD COLUMN id serial;
41ALTER TABLE poolips ADD CONSTRAINT poolips_pkey PRIMARY KEY ("ip", "parent_id");
42
43-- probably need some more indexes
44
45-- Need some additional fields to support search output. Might arguably
46-- move pool IPs into the allocations table; we'll see.
47DROP VIEW searchme;
48CREATE VIEW "searchme" AS
49 SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city,
50 allocations.description, allocations.notes, allocations.circuitid, allocations.vrf,
51 allocations.vlan, allocations.id, allocations.parent_id, 'n' AS available
52 FROM allocations
53 UNION
54 SELECT poolips.ip, poolips.custid, poolips.type, poolips.city,
55 poolips.description, poolips.notes, poolips.circuitid, poolips.vrf,
56 poolips.vlan, poolips.id, poolips.parent_id, poolips.available
57 FROM poolips;
58
59-- Relabel a few types to match the new structure
60UPDATE alloctypes SET listname='Routing aggregation', dispname='Routing aggregation' WHERE type='rm';
61
62-- Not critical but the previous definitions were too restrictive (eg, can't
63-- handle MD5, or SHA password hashes). Plans for ACLs also require larger fields.
64ALTER TABLE users ALTER COLUMN username TYPE varchar(256);
65ALTER TABLE users ALTER COLUMN password TYPE varchar(256);
66ALTER TABLE users ALTER COLUMN acl TYPE varchar(256);
Note: See TracBrowser for help on using the repository browser.