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

Last change on this file since 365 was 365, checked in by Kris Deugau, 12 years ago

/trunk

Add SQL upgrade script for 1.0 -> 1.2

File size: 3.6 KB
Line 
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
6CREATE 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
16COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
171 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
182 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
193 1 ns2.example.com 2 ZONE 7200 \N
204 1 ns1.example.com 2 ZONE 7200 \N
21\.
22
23SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
24
25CREATE TABLE revzones (
26 rdns_id serial NOT NULL,
27 revnet cidr NOT NULL PRIMARY KEY,
28 group_id integer DEFAULT 1 NOT NULL,
29 description character varying(255) DEFAULT ''::character varying NOT NULL,
30 status integer DEFAULT 1 NOT NULL,
31 zserial integer,
32 sertype character(1) DEFAULT 'D'::bpchar
33);
34
35ALTER TABLE log ADD COLUMN rdns_id INTEGER;
36
37-- Since records are now parented by one or both of a forward or reverse zone,
38-- we can't enforce FK relations on domain_id (or the new rdns_id) since many
39-- records won't have one or the other.
40ALTER TABLE records DROP CONSTRAINT "$1";
41ALTER TABLE records ALTER COLUMN domain_id SET DEFAULT 0;
42ALTER TABLE records ADD COLUMN rdns_id INTEGER DEFAULT 0;
43UPDATE records SET rdns_id=0;
44ALTER TABLE records ALTER COLUMN rdns_id SET NOT NULL;
45
46-- May as well drop and recreate; this is nominally static and loaded from the
47-- DB mainly for subset grouping and sorting convenience. Most of the entries
48-- have also been updated with new subset grouping and sorting data.
49DROP TABLE rectypes;
50CREATE TABLE rectypes (
51 val integer NOT NULL,
52 name character varying(20) NOT NULL,
53 stdflag integer DEFAULT 1 NOT NULL,
54 listorder integer DEFAULT 255 NOT NULL,
55 alphaorder integer DEFAULT 32768 NOT NULL
56);
57
58-- Types are required. NB: these are vaguely read-only too
59-- data from http://www.iana.org/assignments/dns-parameters
60COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
611 A 1 1 1
622 NS 2 9 37
633 MD 5 255 29
644 MF 5 255 30
655 CNAME 2 11 9
666 SOA 0 0 53
677 MB 5 255 28
688 MG 5 255 31
699 MR 5 255 33
7010 NULL 5 255 43
7111 WKS 5 255 64
7212 PTR 3 5 46
7313 HINFO 5 255 18
7414 MINFO 5 255 32
7515 MX 1 10 34
7616 TXT 2 12 60
7717 RP 4 255 48
7818 AFSDB 5 255 4
7919 X25 5 255 65
8020 ISDN 5 255 21
8121 RT 5 255 50
8222 NSAP 5 255 38
8323 NSAP-PTR 5 255 39
8424 SIG 5 255 51
8525 KEY 5 255 23
8626 PX 5 255 47
8727 GPOS 5 255 17
8828 AAAA 1 3 3
8929 LOC 5 255 25
9030 NXT 5 255 44
9131 EID 5 255 15
9232 NIMLOC 5 255 36
9333 SRV 1 13 55
9434 ATMA 5 255 6
9535 NAPTR 5 255 35
9636 KX 5 255 24
9737 CERT 5 255 8
9838 A6 5 3 2
9939 DNAME 5 255 12
10040 SINK 5 255 52
10141 OPT 5 255 45
10242 APL 5 255 5
10343 DS 5 255 14
10444 SSHFP 5 255 56
10545 IPSECKEY 5 255 20
10646 RRSIG 5 255 49
10747 NSEC 5 255 40
10848 DNSKEY 5 255 13
10949 DHCID 5 255 10
11050 NSEC3 5 255 41
11151 NSEC3PARAM 5 255 42
11255 HIP 5 255 19
11399 SPF 5 255 54
114100 UINFO 5 255 62
115101 UID 5 255 61
116102 GID 5 255 16
117103 UNSPEC 5 255 63
118249 TKEY 5 255 58
119250 TSIG 5 255 59
120251 IXFR 5 255 22
121252 AXFR 5 255 7
122253 MAILB 5 255 27
123254 MAILA 5 255 26
12432768 TA 5 255 57
12532769 DLV 5 255 11
126\.
127
128-- Custom types (ab)using the "Private use" range from 65280 to 65534
129COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
13065280 A+PTR 2 2 2
13165281 AAAA+PTR 2 4 4
13265282 PTR template 3 6 2
13365283 A+PTR template 2 7 2
13465284 AAAA+PTR template 8 13 2
13565285 Delegation 2 8 2
136\.
137
138-- Update dbversion
139UPDATE misc SET value='1.2' WHERE key='dbversion';
Note: See TracBrowser for help on using the repository browser.