source: trunk/dns-1.0-1.2.sql@ 701

-- SQL table/record type upgrade file for dnsadmin 1.0 to 1.2 migration

-- need this before we add any other bits
CREATE TABLE locations (
    location character varying (4) PRIMARY KEY,
    loc_id serial UNIQUE,
    group_id integer NOT NULL DEFAULT 1,
    iplist text NOT NULL DEFAULT '',
    description character varying(40) NOT NULL DEFAULT '',
    comments text NOT NULL DEFAULT ''
);

ALTER TABLE ONLY locations
    ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);

ALTER TABLE permissions ADD COLUMN record_locchg boolean DEFAULT false NOT NULL;
ALTER TABLE permissions ADD COLUMN location_create boolean DEFAULT false NOT NULL;
ALTER TABLE permissions ADD COLUMN location_edit boolean DEFAULT false NOT NULL;
ALTER TABLE permissions ADD COLUMN location_delete boolean DEFAULT false NOT NULL;
ALTER TABLE permissions ADD COLUMN location_view boolean DEFAULT false NOT NULL;

-- Minor buglet;  domains must be unique
-- ALTER TABLE domains ADD PRIMARY KEY (domain);
 
CREATE TABLE default_rev_records (
    record_id serial NOT NULL,
    group_id integer DEFAULT 1 NOT NULL,
    host text DEFAULT '' NOT NULL,
    "type" integer DEFAULT 1 NOT NULL,
    val text DEFAULT '' NOT NULL,
    ttl integer DEFAULT 86400 NOT NULL,
    description text
);

COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
1       1       hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN  6       3600:900:1048576:2560   3600    
2       1       unused-%r.ADMINDOMAIN   65283   ZONE    3600    
3       1       ns2.example.com 2       ZONE    7200    \N
4       1       ns1.example.com 2       ZONE    7200    \N
\.
 
SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);

ALTER TABLE domains ADD COLUMN changed boolean DEFAULT true NOT NULL;
ALTER TABLE domains ADD COLUMN default_location character varying (4) DEFAULT '' NOT NULL;
-- ~2x performance boost iff most zones are fed to output from the cache
CREATE INDEX dom_status_index ON domains (status);

CREATE TABLE revzones (
    rdns_id serial NOT NULL,
    revnet cidr NOT NULL PRIMARY KEY,
    group_id integer DEFAULT 1 NOT NULL,
    description character varying(255) DEFAULT ''::character varying NOT NULL,
    status integer DEFAULT 1 NOT NULL,
    zserial integer,
    sertype character(1) DEFAULT 'D'::bpchar,
    changed boolean DEFAULT true NOT NULL,
    default_location character varying (4) DEFAULT '' NOT NULL
);
CREATE INDEX rev_status_index ON revzones (status);

ALTER TABLE ONLY revzones
    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);

ALTER TABLE log ADD COLUMN rdns_id INTEGER;

-- Since records are now parented by one or both of a forward or reverse zone,
-- we can't enforce FK relations on domain_id (or the new rdns_id) since many
-- records won't have one or the other.
ALTER TABLE records DROP CONSTRAINT "$1";
ALTER TABLE records ALTER COLUMN domain_id SET DEFAULT 0;
ALTER TABLE records ADD COLUMN rdns_id INTEGER DEFAULT 0 NOT NULL;
ALTER TABLE records ADD COLUMN location character varying (4) DEFAULT '' NOT NULL;
-- Scheduled changes.
ALTER TABLE records ADD COLUMN stamp TIMESTAMP WITH TIME ZONE DEFAULT 'epoch' NOT NULL;
ALTER TABLE records ADD COLUMN expires boolean DEFAULT 'n' NOT NULL;
ALTER TABLE records ADD COLUMN stampactive boolean DEFAULT 'n' NOT NULL;

-- ~120s -> 75s performance boost on 100K records when always exporting all records
CREATE INDEX rec_types_index ON records (type);
-- Further ~1/3 performance gain, same dataset
CREATE INDEX rec_domain_index ON records (domain_id);
CREATE INDEX rec_revzone_index ON records (rdns_id);

-- May as well drop and recreate;  this is nominally static and loaded from the
-- DB mainly for subset grouping and sorting convenience.  Most of the entries
-- have also been updated with new subset grouping and sorting data.
DROP TABLE rectypes;
CREATE TABLE rectypes (
    val integer NOT NULL,
    name character varying(20) NOT NULL,
    stdflag integer DEFAULT 1 NOT NULL,
    listorder integer DEFAULT 255 NOT NULL,
    alphaorder integer DEFAULT 32768 NOT NULL
);

-- Types are required.  NB:  these are vaguely read-only too
-- data from http://www.iana.org/assignments/dns-parameters
COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
1       A       1       1       1
2       NS      2       10      37
3       MD      5       255     29
4       MF      5       255     30
5       CNAME   2       12      9
6       SOA     0       0       53
7       MB      5       255     28
8       MG      5       255     31
9       MR      5       255     33
10      NULL    5       255     43
11      WKS     5       255     64
12      PTR     3       5       46
13      HINFO   5       255     18
14      MINFO   5       255     32
15      MX      1       11      34
16      TXT     2       13      60
17      RP      4       255     48
18      AFSDB   5       255     4
19      X25     5       255     65
20      ISDN    5       255     21
21      RT      5       255     50
22      NSAP    5       255     38
23      NSAP-PTR        5       255     39
24      SIG     5       255     51
25      KEY     5       255     23
26      PX      5       255     47
27      GPOS    5       255     17
28      AAAA    1       3       3
29      LOC     5       255     25
30      NXT     5       255     44
31      EID     5       255     15
32      NIMLOC  5       255     36
33      SRV     1       14      55
34      ATMA    5       255     6
35      NAPTR   5       255     35
36      KX      5       255     24
37      CERT    5       255     8
38      A6      5       3       2
39      DNAME   5       255     12
40      SINK    5       255     52
41      OPT     5       255     45
42      APL     5       255     5
43      DS      5       255     14
44      SSHFP   5       255     56
45      IPSECKEY        5       255     20
46      RRSIG   5       255     49
47      NSEC    5       255     40
48      DNSKEY  5       255     13
49      DHCID   5       255     10
50      NSEC3   5       255     41
51      NSEC3PARAM      5       255     42
55      HIP     5       255     19
99      SPF     5       255     54
100     UINFO   5       255     62
101     UID     5       255     61
102     GID     5       255     16
103     UNSPEC  5       255     63
249     TKEY    5       255     58
250     TSIG    5       255     59
251     IXFR    5       255     22
252     AXFR    5       255     7
253     MAILB   5       255     27
254     MAILA   5       255     26
32768   TA      5       255     57
32769   DLV     5       255     11
\.
 
-- Custom types (ab)using the "Private use" range from 65280 to 65534
COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
65280   A+PTR   2       2       2
65281   AAAA+PTR        2       4       4
65282   PTR template    3       6       2
65283   A+PTR template  2       7       2
65284   AAAA+PTR template       2       8       2
65285   Delegation      2       9       2
\.

-- and readd the primary key
ALTER TABLE ONLY rectypes
     ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);

-- Update dbversion
UPDATE misc SET value='1.2' WHERE key='dbversion';