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

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

/trunk

Begin adding configuration backup fields with the tabledef and link to
the allocations/poolips tables. See #52.

Also note an index for composite key that may not be useful

File size: 3.6 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-- Store backup fields in a separate table
16CREATE TABLE backuplist (
17 backup_id serial NOT NULL,
18 bkbrand text,
19 bkmodel text,
20 bktype text,
21 bkport integer,
22 bksrc text,
23 bkuser text,
24 bkvpass text,
25 bkepass text,
26 ip inet
27);
28
29-- Master and routed blocks now live in the allocations table.
30
31ALTER TABLE allocations ADD COLUMN vrf text NOT NULL DEFAULT '';
32ALTER TABLE allocations ADD COLUMN vlan text NOT NULL DEFAULT '';
33ALTER TABLE allocations ADD COLUMN rdns text NOT NULL DEFAULT '';
34ALTER TABLE allocations ADD COLUMN parent_id integer NOT NULL DEFAULT 0;
35ALTER TABLE allocations ADD COLUMN master_id integer NOT NULL DEFAULT 0;
36ALTER TABLE allocations ADD COLUMN backup_id integer NOT NULL DEFAULT 0;
37ALTER TABLE allocations DROP CONSTRAINT allocations_pkey;
38ALTER TABLE allocations ADD COLUMN id serial PRIMARY KEY;
39-- not certain this is right
40CREATE UNIQUE INDEX allocations_skey ON allocations (cidr,vrf,type);
41
42ALTER TABLE freeblocks ADD COLUMN vrf text NOT NULL DEFAULT '';
43ALTER TABLE freeblocks ADD COLUMN parent_id integer NOT NULL DEFAULT 0;
44ALTER TABLE freeblocks ADD COLUMN master_id integer NOT NULL DEFAULT 0;
45ALTER TABLE freeblocks ADD COLUMN reserve_for integer NOT NULL DEFAULT 0;
46ALTER TABLE freeblocks DROP CONSTRAINT freeblocks_pkey;
47ALTER TABLE freeblocks ADD CONSTRAINT freeblocks_pkey PRIMARY KEY ("cidr","parent_id");
48ALTER TABLE freeblocks ADD COLUMN id serial;
49
50ALTER TABLE poolips ADD COLUMN vrf text NOT NULL DEFAULT '';
51ALTER TABLE poolips ADD COLUMN vlan text NOT NULL DEFAULT '';
52ALTER TABLE poolips ADD COLUMN rdns text NOT NULL DEFAULT '';
53ALTER TABLE poolips ADD COLUMN parent_id integer NOT NULL DEFAULT 0;
54ALTER TABLE poolips ADD COLUMN master_id integer NOT NULL DEFAULT 0;
55ALTER TABLE poolips ADD COLUMN backup_id integer NOT NULL DEFAULT 0;
56ALTER TABLE poolips DROP CONSTRAINT poolips_pkey;
57ALTER TABLE poolips ADD COLUMN id serial;
58ALTER TABLE poolips ADD CONSTRAINT poolips_pkey PRIMARY KEY ("ip", "parent_id");
59
60-- probably need some more indexes
61
62-- Need some additional fields to support search output. Might arguably
63-- move pool IPs into the allocations table; we'll see.
64DROP VIEW searchme;
65CREATE VIEW "searchme" AS
66 SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city,
67 allocations.description, allocations.notes, allocations.circuitid, allocations.vrf,
68 allocations.vlan, allocations.id, allocations.parent_id, 'n' AS available
69 FROM allocations
70 UNION
71 SELECT poolips.ip, poolips.custid, poolips.type, poolips.city,
72 poolips.description, poolips.notes, poolips.circuitid, poolips.vrf,
73 poolips.vlan, poolips.id, poolips.parent_id, poolips.available
74 FROM poolips;
75
76-- Relabel a few types to match the new structure
77UPDATE alloctypes SET listname='Routing aggregation', dispname='Routing aggregation' WHERE type='rm';
78
79-- Not critical but the previous definitions were too restrictive (eg, can't
80-- handle MD5, or SHA password hashes). Plans for ACLs also require larger fields.
81ALTER TABLE users ALTER COLUMN username TYPE varchar(256);
82ALTER TABLE users ALTER COLUMN password TYPE varchar(256);
83ALTER TABLE users ALTER COLUMN acl TYPE varchar(256);
Note: See TracBrowser for help on using the repository browser.