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

-- 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!

-- Flag table for deciding if we can usefully do rDNS RPC calls.
CREATE TABLE dnsavail (
    "zone" cidr,
    "location" varchar(4) DEFAULT '',
    parent_alloc integer NOT NULL
);

ALTER TABLE ONLY dnsavail
    ADD CONSTRAINT dnsavail_pkey PRIMARY KEY ("zone",parent_alloc);

-- Store backup fields in a separate table
CREATE TABLE backuplist (
    backup_id serial NOT NULL,
    bkbrand text,
    bkmodel text,
    bktype text,
    bkport integer,
    bksrc text,
    bkuser text,
    bkvpass text,
    bkepass text,
    bkip inet
);

-- VRFs are now the apex entity
CREATE TABLE vrfs (
    vrf varchar(32) DEFAULT '' NOT NULL,
    comment text DEFAULT '' NOT NULL,
    location varchar(4) DEFAULT '' NOT NULL
);

ALTER TABLE ONLY vrfs
    ADD CONSTRAINT vrfs_pkey PRIMARY KEY (vrf);

-- need a default VRF to stuff everything into to start with
INSERT INTO vrfs (vrf,comment) VALUES ('DEFAULT','Initial default VRF');

-- 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 vlan 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 ADD COLUMN backup_id integer NOT NULL DEFAULT 0;
ALTER TABLE allocations DROP CONSTRAINT allocations_pkey;
ALTER TABLE allocations ADD COLUMN id serial PRIMARY KEY;
-- not certain this is right
CREATE UNIQUE INDEX allocations_skey ON allocations (cidr,vrf,type);

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 ADD COLUMN reserve_for 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 vlan 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 ADD COLUMN backup_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

-- Need some additional fields to support search output.  Might arguably
-- move pool IPs into the allocations table;  we'll see.
DROP VIEW searchme;
-- do these here, because we can't do them while the view is defined
ALTER TABLE allocations ALTER COLUMN description TYPE varchar(128);
ALTER TABLE poolips ALTER COLUMN description TYPE varchar(128);
CREATE VIEW "searchme" AS
    SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, 
      allocations.description, allocations.notes, allocations.circuitid, allocations.vrf,
      allocations.vlan, allocations.id, allocations.parent_id, allocations.master_id, 'n' AS available
    FROM allocations
  UNION
    SELECT poolips.ip, poolips.custid, poolips.type, poolips.city,
      poolips.description, poolips.notes, poolips.circuitid, poolips.vrf,
      poolips.vlan, poolips.id, poolips.parent_id, poolips.master_id, poolips.available
    FROM poolips;

-- 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);