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
|
---|
4 | CREATE 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 |
|
---|
13 | ALTER TABLE ONLY locations
|
---|
14 | ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
---|
15 |
|
---|
16 | ALTER TABLE permissions ADD COLUMN record_locchg boolean DEFAULT false NOT NULL;
|
---|
17 | ALTER TABLE permissions ADD COLUMN location_create boolean DEFAULT false NOT NULL;
|
---|
18 | ALTER TABLE permissions ADD COLUMN location_edit boolean DEFAULT false NOT NULL;
|
---|
19 | ALTER TABLE permissions ADD COLUMN location_delete boolean DEFAULT false NOT NULL;
|
---|
20 | ALTER 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 |
|
---|
25 | CREATE 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 |
|
---|
35 | COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
|
---|
36 | 1 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
|
---|
37 | 2 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
|
---|
38 | 3 1 ns2.example.com 2 ZONE 7200 \N
|
---|
39 | 4 1 ns1.example.com 2 ZONE 7200 \N
|
---|
40 | \.
|
---|
41 |
|
---|
42 | SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
|
---|
43 |
|
---|
44 | ALTER TABLE domains ADD COLUMN changed boolean DEFAULT true NOT NULL;
|
---|
45 | ALTER 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
|
---|
47 | CREATE INDEX dom_status_index ON domains (status);
|
---|
48 |
|
---|
49 | CREATE 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 | );
|
---|
60 | CREATE INDEX rev_status_index ON revzones (status);
|
---|
61 |
|
---|
62 | ALTER TABLE ONLY revzones
|
---|
63 | ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
---|
64 |
|
---|
65 | ALTER 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.
|
---|
70 | ALTER TABLE records DROP CONSTRAINT "$1";
|
---|
71 | ALTER TABLE records ALTER COLUMN domain_id SET DEFAULT 0;
|
---|
72 | ALTER TABLE records ADD COLUMN rdns_id INTEGER DEFAULT 0 NOT NULL;
|
---|
73 | ALTER TABLE records ADD COLUMN location character varying (4) DEFAULT '' NOT NULL;
|
---|
74 |
|
---|
75 | -- ~120s -> 75s performance boost on 100K records when always exporting all records
|
---|
76 | CREATE INDEX rec_types_index ON records (type);
|
---|
77 | -- Further ~1/3 performance gain, same dataset
|
---|
78 | CREATE INDEX rec_domain_index ON records (domain_id);
|
---|
79 | CREATE INDEX rec_revzone_index ON records (rdns_id);
|
---|
80 |
|
---|
81 | -- May as well drop and recreate; this is nominally static and loaded from the
|
---|
82 | -- DB mainly for subset grouping and sorting convenience. Most of the entries
|
---|
83 | -- have also been updated with new subset grouping and sorting data.
|
---|
84 | DROP TABLE rectypes;
|
---|
85 | CREATE TABLE rectypes (
|
---|
86 | val integer NOT NULL,
|
---|
87 | name character varying(20) NOT NULL,
|
---|
88 | stdflag integer DEFAULT 1 NOT NULL,
|
---|
89 | listorder integer DEFAULT 255 NOT NULL,
|
---|
90 | alphaorder integer DEFAULT 32768 NOT NULL
|
---|
91 | );
|
---|
92 |
|
---|
93 | -- Types are required. NB: these are vaguely read-only too
|
---|
94 | -- data from http://www.iana.org/assignments/dns-parameters
|
---|
95 | COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
|
---|
96 | 1 A 1 1 1
|
---|
97 | 2 NS 2 10 37
|
---|
98 | 3 MD 5 255 29
|
---|
99 | 4 MF 5 255 30
|
---|
100 | 5 CNAME 2 12 9
|
---|
101 | 6 SOA 0 0 53
|
---|
102 | 7 MB 5 255 28
|
---|
103 | 8 MG 5 255 31
|
---|
104 | 9 MR 5 255 33
|
---|
105 | 10 NULL 5 255 43
|
---|
106 | 11 WKS 5 255 64
|
---|
107 | 12 PTR 3 5 46
|
---|
108 | 13 HINFO 5 255 18
|
---|
109 | 14 MINFO 5 255 32
|
---|
110 | 15 MX 1 11 34
|
---|
111 | 16 TXT 2 13 60
|
---|
112 | 17 RP 4 255 48
|
---|
113 | 18 AFSDB 5 255 4
|
---|
114 | 19 X25 5 255 65
|
---|
115 | 20 ISDN 5 255 21
|
---|
116 | 21 RT 5 255 50
|
---|
117 | 22 NSAP 5 255 38
|
---|
118 | 23 NSAP-PTR 5 255 39
|
---|
119 | 24 SIG 5 255 51
|
---|
120 | 25 KEY 5 255 23
|
---|
121 | 26 PX 5 255 47
|
---|
122 | 27 GPOS 5 255 17
|
---|
123 | 28 AAAA 1 3 3
|
---|
124 | 29 LOC 5 255 25
|
---|
125 | 30 NXT 5 255 44
|
---|
126 | 31 EID 5 255 15
|
---|
127 | 32 NIMLOC 5 255 36
|
---|
128 | 33 SRV 1 14 55
|
---|
129 | 34 ATMA 5 255 6
|
---|
130 | 35 NAPTR 5 255 35
|
---|
131 | 36 KX 5 255 24
|
---|
132 | 37 CERT 5 255 8
|
---|
133 | 38 A6 5 3 2
|
---|
134 | 39 DNAME 5 255 12
|
---|
135 | 40 SINK 5 255 52
|
---|
136 | 41 OPT 5 255 45
|
---|
137 | 42 APL 5 255 5
|
---|
138 | 43 DS 5 255 14
|
---|
139 | 44 SSHFP 5 255 56
|
---|
140 | 45 IPSECKEY 5 255 20
|
---|
141 | 46 RRSIG 5 255 49
|
---|
142 | 47 NSEC 5 255 40
|
---|
143 | 48 DNSKEY 5 255 13
|
---|
144 | 49 DHCID 5 255 10
|
---|
145 | 50 NSEC3 5 255 41
|
---|
146 | 51 NSEC3PARAM 5 255 42
|
---|
147 | 55 HIP 5 255 19
|
---|
148 | 99 SPF 5 255 54
|
---|
149 | 100 UINFO 5 255 62
|
---|
150 | 101 UID 5 255 61
|
---|
151 | 102 GID 5 255 16
|
---|
152 | 103 UNSPEC 5 255 63
|
---|
153 | 249 TKEY 5 255 58
|
---|
154 | 250 TSIG 5 255 59
|
---|
155 | 251 IXFR 5 255 22
|
---|
156 | 252 AXFR 5 255 7
|
---|
157 | 253 MAILB 5 255 27
|
---|
158 | 254 MAILA 5 255 26
|
---|
159 | 32768 TA 5 255 57
|
---|
160 | 32769 DLV 5 255 11
|
---|
161 | \.
|
---|
162 |
|
---|
163 | -- Custom types (ab)using the "Private use" range from 65280 to 65534
|
---|
164 | COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
|
---|
165 | 65280 A+PTR 2 2 2
|
---|
166 | 65281 AAAA+PTR 2 4 4
|
---|
167 | 65282 PTR template 3 6 2
|
---|
168 | 65283 A+PTR template 2 7 2
|
---|
169 | 65284 AAAA+PTR template 2 8 2
|
---|
170 | 65285 Delegation 2 9 2
|
---|
171 | \.
|
---|
172 |
|
---|
173 | -- Update dbversion
|
---|
174 | UPDATE misc SET value='1.2' WHERE key='dbversion';
|
---|