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

-- ~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';