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

Last change on this file since 778 was 543, checked in by Kris Deugau, 11 years ago

/trunk

Implement most of the UI and back end for handling scheduled changes
to records. See #40.

This turned out to be most of what I had vaguely imagined; only SOA
records can't sanely be set for scheduled changes yet (can't think of
a scenario where this would even be useful) and there's only a small
dusting of UI chrome left for another time.

Bumped up from projected 1.4 to 1.2 per request from Reid Sutherland.

File size: 5.6 KB
Line 
1-- SQL table/record type upgrade file for dnsadmin 1.0 to 1.2 migration
2
3-- need this before we add any other bits
4CREATE TABLE locations (
5 location character varying (4) PRIMARY KEY,
6 loc_id serial UNIQUE,
7 group_id integer NOT NULL DEFAULT 1,
8 iplist text NOT NULL DEFAULT '',
9 description character varying(40) NOT NULL DEFAULT '',
10 comments text NOT NULL DEFAULT ''
11);
12
13ALTER TABLE ONLY locations
14 ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
15
16ALTER TABLE permissions ADD COLUMN record_locchg boolean DEFAULT false NOT NULL;
17ALTER TABLE permissions ADD COLUMN location_create boolean DEFAULT false NOT NULL;
18ALTER TABLE permissions ADD COLUMN location_edit boolean DEFAULT false NOT NULL;
19ALTER TABLE permissions ADD COLUMN location_delete boolean DEFAULT false NOT NULL;
20ALTER TABLE permissions ADD COLUMN location_view boolean DEFAULT false NOT NULL;
21
22-- Minor buglet; domains must be unique
23-- ALTER TABLE domains ADD PRIMARY KEY (domain);
24
25CREATE TABLE default_rev_records (
26 record_id serial NOT NULL,
27 group_id integer DEFAULT 1 NOT NULL,
28 host text DEFAULT '' NOT NULL,
29 "type" integer DEFAULT 1 NOT NULL,
30 val text DEFAULT '' NOT NULL,
31 ttl integer DEFAULT 86400 NOT NULL,
32 description text
33);
34
35COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
361 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
372 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
383 1 ns2.example.com 2 ZONE 7200 \N
394 1 ns1.example.com 2 ZONE 7200 \N
40\.
41
42SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);
43
44ALTER TABLE domains ADD COLUMN changed boolean DEFAULT true NOT NULL;
45ALTER TABLE domains ADD COLUMN default_location character varying (4) DEFAULT '' NOT NULL;
46-- ~2x performance boost iff most zones are fed to output from the cache
47CREATE INDEX dom_status_index ON domains (status);
48
49CREATE TABLE revzones (
50 rdns_id serial NOT NULL,
51 revnet cidr NOT NULL PRIMARY KEY,
52 group_id integer DEFAULT 1 NOT NULL,
53 description character varying(255) DEFAULT ''::character varying NOT NULL,
54 status integer DEFAULT 1 NOT NULL,
55 zserial integer,
56 sertype character(1) DEFAULT 'D'::bpchar,
57 changed boolean DEFAULT true NOT NULL,
58 default_location character varying (4) DEFAULT '' NOT NULL
59);
60CREATE INDEX rev_status_index ON revzones (status);
61
62ALTER TABLE ONLY revzones
63 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
64
65ALTER TABLE log ADD COLUMN rdns_id INTEGER;
66
67-- Since records are now parented by one or both of a forward or reverse zone,
68-- we can't enforce FK relations on domain_id (or the new rdns_id) since many
69-- records won't have one or the other.
70ALTER TABLE records DROP CONSTRAINT "$1";
71ALTER TABLE records ALTER COLUMN domain_id SET DEFAULT 0;
72ALTER TABLE records ADD COLUMN rdns_id INTEGER DEFAULT 0 NOT NULL;
73ALTER TABLE records ADD COLUMN location character varying (4) DEFAULT '' NOT NULL;
74-- Scheduled changes.
75ALTER TABLE records ADD COLUMN stamp TIMESTAMP WITH TIME ZONE DEFAULT 'epoch' NOT NULL;
76ALTER TABLE records ADD COLUMN expires boolean DEFAULT 'n' NOT NULL;
77ALTER TABLE records ADD COLUMN stampactive boolean DEFAULT 'n' NOT NULL;
78
79-- ~120s -> 75s performance boost on 100K records when always exporting all records
80CREATE INDEX rec_types_index ON records (type);
81-- Further ~1/3 performance gain, same dataset
82CREATE INDEX rec_domain_index ON records (domain_id);
83CREATE INDEX rec_revzone_index ON records (rdns_id);
84
85-- May as well drop and recreate; this is nominally static and loaded from the
86-- DB mainly for subset grouping and sorting convenience. Most of the entries
87-- have also been updated with new subset grouping and sorting data.
88DROP TABLE rectypes;
89CREATE TABLE rectypes (
90 val integer NOT NULL,
91 name character varying(20) NOT NULL,
92 stdflag integer DEFAULT 1 NOT NULL,
93 listorder integer DEFAULT 255 NOT NULL,
94 alphaorder integer DEFAULT 32768 NOT NULL
95);
96
97-- Types are required. NB: these are vaguely read-only too
98-- data from http://www.iana.org/assignments/dns-parameters
99COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
1001 A 1 1 1
1012 NS 2 10 37
1023 MD 5 255 29
1034 MF 5 255 30
1045 CNAME 2 12 9
1056 SOA 0 0 53
1067 MB 5 255 28
1078 MG 5 255 31
1089 MR 5 255 33
10910 NULL 5 255 43
11011 WKS 5 255 64
11112 PTR 3 5 46
11213 HINFO 5 255 18
11314 MINFO 5 255 32
11415 MX 1 11 34
11516 TXT 2 13 60
11617 RP 4 255 48
11718 AFSDB 5 255 4
11819 X25 5 255 65
11920 ISDN 5 255 21
12021 RT 5 255 50
12122 NSAP 5 255 38
12223 NSAP-PTR 5 255 39
12324 SIG 5 255 51
12425 KEY 5 255 23
12526 PX 5 255 47
12627 GPOS 5 255 17
12728 AAAA 1 3 3
12829 LOC 5 255 25
12930 NXT 5 255 44
13031 EID 5 255 15
13132 NIMLOC 5 255 36
13233 SRV 1 14 55
13334 ATMA 5 255 6
13435 NAPTR 5 255 35
13536 KX 5 255 24
13637 CERT 5 255 8
13738 A6 5 3 2
13839 DNAME 5 255 12
13940 SINK 5 255 52
14041 OPT 5 255 45
14142 APL 5 255 5
14243 DS 5 255 14
14344 SSHFP 5 255 56
14445 IPSECKEY 5 255 20
14546 RRSIG 5 255 49
14647 NSEC 5 255 40
14748 DNSKEY 5 255 13
14849 DHCID 5 255 10
14950 NSEC3 5 255 41
15051 NSEC3PARAM 5 255 42
15155 HIP 5 255 19
15299 SPF 5 255 54
153100 UINFO 5 255 62
154101 UID 5 255 61
155102 GID 5 255 16
156103 UNSPEC 5 255 63
157249 TKEY 5 255 58
158250 TSIG 5 255 59
159251 IXFR 5 255 22
160252 AXFR 5 255 7
161253 MAILB 5 255 27
162254 MAILA 5 255 26
16332768 TA 5 255 57
16432769 DLV 5 255 11
165\.
166
167-- Custom types (ab)using the "Private use" range from 65280 to 65534
168COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
16965280 A+PTR 2 2 2
17065281 AAAA+PTR 2 4 4
17165282 PTR template 3 6 2
17265283 A+PTR template 2 7 2
17365284 AAAA+PTR template 2 8 2
17465285 Delegation 2 9 2
175\.
176
177-- and readd the primary key
178ALTER TABLE ONLY rectypes
179 ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
180
181-- Update dbversion
182UPDATE misc SET value='1.2' WHERE key='dbversion';
Note: See TracBrowser for help on using the repository browser.