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

-- Baseline SQL to add necessary fields for IPDB 2.7 -> IPDB 3.0
-- Note some data changes must be made via script.

-- WARNING:  This SQL is backwards-compatible, but once db-update.pl
-- has run, IPDB v2.7 and older will NOT be able to properly manipulate
-- the data!

-- Master and routed blocks now live in the allocations table.

ALTER TABLE allocations ADD COLUMN vrf text NOT NULL DEFAULT '';
ALTER TABLE allocations ADD COLUMN rdns text NOT NULL DEFAULT '';
ALTER TABLE allocations ADD COLUMN parent_id integer NOT NULL DEFAULT 0;
ALTER TABLE allocations ADD COLUMN master_id integer NOT NULL DEFAULT 0;
ALTER TABLE allocations DROP CONSTRAINT allocations_pkey;
ALTER TABLE allocations ADD COLUMN id serial PRIMARY KEY;

ALTER TABLE freeblocks ADD COLUMN vrf text NOT NULL DEFAULT '';
ALTER TABLE freeblocks ADD COLUMN parent_id integer NOT NULL DEFAULT 0;
ALTER TABLE freeblocks ADD COLUMN master_id integer NOT NULL DEFAULT 0;
ALTER TABLE freeblocks DROP CONSTRAINT freeblocks_pkey;
ALTER TABLE freeblocks ADD CONSTRAINT freeblocks_pkey PRIMARY KEY ("cidr","parent_id");
ALTER TABLE freeblocks ADD COLUMN id serial;

ALTER TABLE poolips ADD COLUMN vrf text NOT NULL DEFAULT '';
ALTER TABLE poolips ADD COLUMN rdns text NOT NULL DEFAULT '';
ALTER TABLE poolips ADD COLUMN parent_id integer NOT NULL DEFAULT 0;
ALTER TABLE poolips ADD COLUMN master_id integer NOT NULL DEFAULT 0;
ALTER TABLE poolips DROP CONSTRAINT poolips_pkey;
ALTER TABLE poolips ADD COLUMN id serial;
ALTER TABLE poolips ADD CONSTRAINT poolips_pkey PRIMARY KEY ("ip", "parent_id");

-- probably need some more indexes

-- Relabel a few types to match the new structure
UPDATE alloctypes SET listname='Routing aggregation', dispname='Routing aggregation' WHERE type='rm';

-- Not critical but the previous definitions were too restrictive (eg, can't
-- handle MD5, or SHA password hashes).  Plans for ACLs also require larger fields.
ALTER TABLE users ALTER COLUMN username TYPE varchar(256);
ALTER TABLE users ALTER COLUMN password TYPE varchar(256);
ALTER TABLE users ALTER COLUMN acl TYPE varchar(256);