[365] | 1 | -- SQL table/record type upgrade file for dnsadmin 1.0 to 1.2 migration
|
---|
| 2 |
|
---|
| 3 | -- Minor buglet; domains must be unique
|
---|
| 4 | -- ALTER TABLE domains ADD PRIMARY KEY (domain);
|
---|
| 5 |
|
---|
| 6 | CREATE TABLE default_rev_records (
|
---|
| 7 | record_id serial NOT NULL,
|
---|
| 8 | group_id integer DEFAULT 1 NOT NULL,
|
---|
| 9 | host text DEFAULT '' NOT NULL,
|
---|
| 10 | "type" integer DEFAULT 1 NOT NULL,
|
---|
| 11 | val text DEFAULT '' NOT NULL,
|
---|
| 12 | ttl integer DEFAULT 86400 NOT NULL,
|
---|
| 13 | description text
|
---|
| 14 | );
|
---|
| 15 |
|
---|
| 16 | COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
|
---|
| 17 | 1 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
|
---|
| 18 | 2 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
|
---|
| 19 | 3 1 ns2.example.com 2 ZONE 7200 \N
|
---|
| 20 | 4 1 ns1.example.com 2 ZONE 7200 \N
|
---|
| 21 | \.
|
---|
| 22 |
|
---|
| 23 | SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
|
---|
| 24 |
|
---|
[366] | 25 | ALTER TABLE domains ADD COLUMN changed boolean;
|
---|
| 26 | UPDATE domains SET changed=false;
|
---|
| 27 | ALTER TABLE domains ALTER COLUMN changed SET DEFAULT true;
|
---|
| 28 | ALTER TABLE domains ALTER COLUMN changed SET NOT NULL;
|
---|
[368] | 29 | -- ~2x performance boost iff most zones are fed to output from the cache
|
---|
| 30 | CREATE INDEX dom_status_index ON domains (status);
|
---|
[366] | 31 |
|
---|
[365] | 32 | CREATE TABLE revzones (
|
---|
| 33 | rdns_id serial NOT NULL,
|
---|
| 34 | revnet cidr NOT NULL PRIMARY KEY,
|
---|
| 35 | group_id integer DEFAULT 1 NOT NULL,
|
---|
| 36 | description character varying(255) DEFAULT ''::character varying NOT NULL,
|
---|
| 37 | status integer DEFAULT 1 NOT NULL,
|
---|
| 38 | zserial integer,
|
---|
[366] | 39 | sertype character(1) DEFAULT 'D'::bpchar,
|
---|
| 40 | changed boolean DEFAULT true NOT NULL
|
---|
[365] | 41 | );
|
---|
[368] | 42 | CREATE INDEX rev_status_index ON revzones (status);
|
---|
[365] | 43 |
|
---|
| 44 | ALTER TABLE log ADD COLUMN rdns_id INTEGER;
|
---|
| 45 |
|
---|
| 46 | -- Since records are now parented by one or both of a forward or reverse zone,
|
---|
| 47 | -- we can't enforce FK relations on domain_id (or the new rdns_id) since many
|
---|
| 48 | -- records won't have one or the other.
|
---|
| 49 | ALTER TABLE records DROP CONSTRAINT "$1";
|
---|
| 50 | ALTER TABLE records ALTER COLUMN domain_id SET DEFAULT 0;
|
---|
| 51 | ALTER TABLE records ADD COLUMN rdns_id INTEGER DEFAULT 0;
|
---|
| 52 | UPDATE records SET rdns_id=0;
|
---|
| 53 | ALTER TABLE records ALTER COLUMN rdns_id SET NOT NULL;
|
---|
[368] | 54 | -- ~120s -> 75s performance boost on 100K records when always exporting all records
|
---|
| 55 | CREATE INDEX rec_types_index ON records (type);
|
---|
| 56 | -- Further ~1/3 performance gain, same dataset
|
---|
| 57 | CREATE INDEX rec_domain_index ON records (domain_id);
|
---|
| 58 | CREATE INDEX rec_revzone_index ON records (rdns_id);
|
---|
[365] | 59 |
|
---|
| 60 | -- May as well drop and recreate; this is nominally static and loaded from the
|
---|
| 61 | -- DB mainly for subset grouping and sorting convenience. Most of the entries
|
---|
| 62 | -- have also been updated with new subset grouping and sorting data.
|
---|
| 63 | DROP TABLE rectypes;
|
---|
| 64 | CREATE TABLE rectypes (
|
---|
| 65 | val integer NOT NULL,
|
---|
| 66 | name character varying(20) NOT NULL,
|
---|
| 67 | stdflag integer DEFAULT 1 NOT NULL,
|
---|
| 68 | listorder integer DEFAULT 255 NOT NULL,
|
---|
| 69 | alphaorder integer DEFAULT 32768 NOT NULL
|
---|
| 70 | );
|
---|
| 71 |
|
---|
| 72 | -- Types are required. NB: these are vaguely read-only too
|
---|
| 73 | -- data from http://www.iana.org/assignments/dns-parameters
|
---|
| 74 | COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
|
---|
| 75 | 1 A 1 1 1
|
---|
| 76 | 2 NS 2 9 37
|
---|
| 77 | 3 MD 5 255 29
|
---|
| 78 | 4 MF 5 255 30
|
---|
| 79 | 5 CNAME 2 11 9
|
---|
| 80 | 6 SOA 0 0 53
|
---|
| 81 | 7 MB 5 255 28
|
---|
| 82 | 8 MG 5 255 31
|
---|
| 83 | 9 MR 5 255 33
|
---|
| 84 | 10 NULL 5 255 43
|
---|
| 85 | 11 WKS 5 255 64
|
---|
| 86 | 12 PTR 3 5 46
|
---|
| 87 | 13 HINFO 5 255 18
|
---|
| 88 | 14 MINFO 5 255 32
|
---|
| 89 | 15 MX 1 10 34
|
---|
| 90 | 16 TXT 2 12 60
|
---|
| 91 | 17 RP 4 255 48
|
---|
| 92 | 18 AFSDB 5 255 4
|
---|
| 93 | 19 X25 5 255 65
|
---|
| 94 | 20 ISDN 5 255 21
|
---|
| 95 | 21 RT 5 255 50
|
---|
| 96 | 22 NSAP 5 255 38
|
---|
| 97 | 23 NSAP-PTR 5 255 39
|
---|
| 98 | 24 SIG 5 255 51
|
---|
| 99 | 25 KEY 5 255 23
|
---|
| 100 | 26 PX 5 255 47
|
---|
| 101 | 27 GPOS 5 255 17
|
---|
| 102 | 28 AAAA 1 3 3
|
---|
| 103 | 29 LOC 5 255 25
|
---|
| 104 | 30 NXT 5 255 44
|
---|
| 105 | 31 EID 5 255 15
|
---|
| 106 | 32 NIMLOC 5 255 36
|
---|
| 107 | 33 SRV 1 13 55
|
---|
| 108 | 34 ATMA 5 255 6
|
---|
| 109 | 35 NAPTR 5 255 35
|
---|
| 110 | 36 KX 5 255 24
|
---|
| 111 | 37 CERT 5 255 8
|
---|
| 112 | 38 A6 5 3 2
|
---|
| 113 | 39 DNAME 5 255 12
|
---|
| 114 | 40 SINK 5 255 52
|
---|
| 115 | 41 OPT 5 255 45
|
---|
| 116 | 42 APL 5 255 5
|
---|
| 117 | 43 DS 5 255 14
|
---|
| 118 | 44 SSHFP 5 255 56
|
---|
| 119 | 45 IPSECKEY 5 255 20
|
---|
| 120 | 46 RRSIG 5 255 49
|
---|
| 121 | 47 NSEC 5 255 40
|
---|
| 122 | 48 DNSKEY 5 255 13
|
---|
| 123 | 49 DHCID 5 255 10
|
---|
| 124 | 50 NSEC3 5 255 41
|
---|
| 125 | 51 NSEC3PARAM 5 255 42
|
---|
| 126 | 55 HIP 5 255 19
|
---|
| 127 | 99 SPF 5 255 54
|
---|
| 128 | 100 UINFO 5 255 62
|
---|
| 129 | 101 UID 5 255 61
|
---|
| 130 | 102 GID 5 255 16
|
---|
| 131 | 103 UNSPEC 5 255 63
|
---|
| 132 | 249 TKEY 5 255 58
|
---|
| 133 | 250 TSIG 5 255 59
|
---|
| 134 | 251 IXFR 5 255 22
|
---|
| 135 | 252 AXFR 5 255 7
|
---|
| 136 | 253 MAILB 5 255 27
|
---|
| 137 | 254 MAILA 5 255 26
|
---|
| 138 | 32768 TA 5 255 57
|
---|
| 139 | 32769 DLV 5 255 11
|
---|
| 140 | \.
|
---|
| 141 |
|
---|
| 142 | -- Custom types (ab)using the "Private use" range from 65280 to 65534
|
---|
| 143 | COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
|
---|
| 144 | 65280 A+PTR 2 2 2
|
---|
| 145 | 65281 AAAA+PTR 2 4 4
|
---|
| 146 | 65282 PTR template 3 6 2
|
---|
| 147 | 65283 A+PTR template 2 7 2
|
---|
| 148 | 65284 AAAA+PTR template 8 13 2
|
---|
| 149 | 65285 Delegation 2 8 2
|
---|
| 150 | \.
|
---|
| 151 |
|
---|
| 152 | -- Update dbversion
|
---|
| 153 | UPDATE misc SET value='1.2' WHERE key='dbversion';
|
---|