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

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

/trunk

Add a new permission record_locchg to separate changing the
location of a record from the abaility to edit a record or
change the location definition. See #10.

File size: 5.3 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', 5, false);
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
75-- ~120s -> 75s performance boost on 100K records when always exporting all records
76CREATE INDEX rec_types_index ON records (type);
77-- Further ~1/3 performance gain, same dataset
78CREATE INDEX rec_domain_index ON records (domain_id);
79CREATE 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.
84DROP TABLE rectypes;
85CREATE 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
95COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
961 A 1 1 1
972 NS 2 9 37
983 MD 5 255 29
994 MF 5 255 30
1005 CNAME 2 11 9
1016 SOA 0 0 53
1027 MB 5 255 28
1038 MG 5 255 31
1049 MR 5 255 33
10510 NULL 5 255 43
10611 WKS 5 255 64
10712 PTR 3 5 46
10813 HINFO 5 255 18
10914 MINFO 5 255 32
11015 MX 1 10 34
11116 TXT 2 12 60
11217 RP 4 255 48
11318 AFSDB 5 255 4
11419 X25 5 255 65
11520 ISDN 5 255 21
11621 RT 5 255 50
11722 NSAP 5 255 38
11823 NSAP-PTR 5 255 39
11924 SIG 5 255 51
12025 KEY 5 255 23
12126 PX 5 255 47
12227 GPOS 5 255 17
12328 AAAA 1 3 3
12429 LOC 5 255 25
12530 NXT 5 255 44
12631 EID 5 255 15
12732 NIMLOC 5 255 36
12833 SRV 1 13 55
12934 ATMA 5 255 6
13035 NAPTR 5 255 35
13136 KX 5 255 24
13237 CERT 5 255 8
13338 A6 5 3 2
13439 DNAME 5 255 12
13540 SINK 5 255 52
13641 OPT 5 255 45
13742 APL 5 255 5
13843 DS 5 255 14
13944 SSHFP 5 255 56
14045 IPSECKEY 5 255 20
14146 RRSIG 5 255 49
14247 NSEC 5 255 40
14348 DNSKEY 5 255 13
14449 DHCID 5 255 10
14550 NSEC3 5 255 41
14651 NSEC3PARAM 5 255 42
14755 HIP 5 255 19
14899 SPF 5 255 54
149100 UINFO 5 255 62
150101 UID 5 255 61
151102 GID 5 255 16
152103 UNSPEC 5 255 63
153249 TKEY 5 255 58
154250 TSIG 5 255 59
155251 IXFR 5 255 22
156252 AXFR 5 255 7
157253 MAILB 5 255 27
158254 MAILA 5 255 26
15932768 TA 5 255 57
16032769 DLV 5 255 11
161\.
162
163-- Custom types (ab)using the "Private use" range from 65280 to 65534
164COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
16565280 A+PTR 2 2 2
16665281 AAAA+PTR 2 4 4
16765282 PTR template 3 6 2
16865283 A+PTR template 2 7 2
16965284 AAAA+PTR template 8 13 2
17065285 Delegation 2 8 2
171\.
172
173-- Update dbversion
174UPDATE misc SET value='1.2' WHERE key='dbversion';
Note: See TracBrowser for help on using the repository browser.