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

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

/trunk

Checkpoint adding locations/views. See #10.

  • Tweak new locations table again with an internal serial for sorting and finding the "highest" previous location
  • Complete "Add location" stub
  • Fill in initial display page for "Edit location"
  • Tweak location add/edit/update template a little, add new comments field to match new field in table
  • Tweak location import so we don't end up with great long space-free fields similar to what can't be avoided with TXT (usually SPF/DomainKeys) records
  • Fill in addLoc() stub
  • Add getLoc()
  • Tweak location export to strip commas and whitespace properly
File size: 5.2 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 location_create boolean DEFAULT false NOT NULL;
17ALTER TABLE permissions ADD COLUMN location_edit boolean DEFAULT false NOT NULL;
18ALTER TABLE permissions ADD COLUMN location_delete boolean DEFAULT false NOT NULL;
19ALTER TABLE permissions ADD COLUMN location_view boolean DEFAULT false NOT NULL;
20
21-- Minor buglet; domains must be unique
22-- ALTER TABLE domains ADD PRIMARY KEY (domain);
23
24CREATE TABLE default_rev_records (
25 record_id serial NOT NULL,
26 group_id integer DEFAULT 1 NOT NULL,
27 host text DEFAULT '' NOT NULL,
28 "type" integer DEFAULT 1 NOT NULL,
29 val text DEFAULT '' NOT NULL,
30 ttl integer DEFAULT 86400 NOT NULL,
31 description text
32);
33
34COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
351 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
362 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
373 1 ns2.example.com 2 ZONE 7200 \N
384 1 ns1.example.com 2 ZONE 7200 \N
39\.
40
41SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
42
43ALTER TABLE domains ADD COLUMN changed boolean DEFAULT true NOT NULL;
44ALTER TABLE domains ADD COLUMN default_location character varying (4) DEFAULT '' NOT NULL;
45-- ~2x performance boost iff most zones are fed to output from the cache
46CREATE INDEX dom_status_index ON domains (status);
47
48CREATE TABLE revzones (
49 rdns_id serial NOT NULL,
50 revnet cidr NOT NULL PRIMARY KEY,
51 group_id integer DEFAULT 1 NOT NULL,
52 description character varying(255) DEFAULT ''::character varying NOT NULL,
53 status integer DEFAULT 1 NOT NULL,
54 zserial integer,
55 sertype character(1) DEFAULT 'D'::bpchar,
56 changed boolean DEFAULT true NOT NULL,
57 default_location character varying (4) DEFAULT '' NOT NULL
58);
59CREATE INDEX rev_status_index ON revzones (status);
60
61ALTER TABLE ONLY revzones
62 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
63
64ALTER TABLE log ADD COLUMN rdns_id INTEGER;
65
66-- Since records are now parented by one or both of a forward or reverse zone,
67-- we can't enforce FK relations on domain_id (or the new rdns_id) since many
68-- records won't have one or the other.
69ALTER TABLE records DROP CONSTRAINT "$1";
70ALTER TABLE records ALTER COLUMN domain_id SET DEFAULT 0;
71ALTER TABLE records ADD COLUMN rdns_id INTEGER DEFAULT 0 NOT NULL;
72ALTER TABLE records ADD COLUMN location character varying (4) DEFAULT '' NOT NULL;
73
74-- ~120s -> 75s performance boost on 100K records when always exporting all records
75CREATE INDEX rec_types_index ON records (type);
76-- Further ~1/3 performance gain, same dataset
77CREATE INDEX rec_domain_index ON records (domain_id);
78CREATE INDEX rec_revzone_index ON records (rdns_id);
79
80-- May as well drop and recreate; this is nominally static and loaded from the
81-- DB mainly for subset grouping and sorting convenience. Most of the entries
82-- have also been updated with new subset grouping and sorting data.
83DROP TABLE rectypes;
84CREATE TABLE rectypes (
85 val integer NOT NULL,
86 name character varying(20) NOT NULL,
87 stdflag integer DEFAULT 1 NOT NULL,
88 listorder integer DEFAULT 255 NOT NULL,
89 alphaorder integer DEFAULT 32768 NOT NULL
90);
91
92-- Types are required. NB: these are vaguely read-only too
93-- data from http://www.iana.org/assignments/dns-parameters
94COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
951 A 1 1 1
962 NS 2 9 37
973 MD 5 255 29
984 MF 5 255 30
995 CNAME 2 11 9
1006 SOA 0 0 53
1017 MB 5 255 28
1028 MG 5 255 31
1039 MR 5 255 33
10410 NULL 5 255 43
10511 WKS 5 255 64
10612 PTR 3 5 46
10713 HINFO 5 255 18
10814 MINFO 5 255 32
10915 MX 1 10 34
11016 TXT 2 12 60
11117 RP 4 255 48
11218 AFSDB 5 255 4
11319 X25 5 255 65
11420 ISDN 5 255 21
11521 RT 5 255 50
11622 NSAP 5 255 38
11723 NSAP-PTR 5 255 39
11824 SIG 5 255 51
11925 KEY 5 255 23
12026 PX 5 255 47
12127 GPOS 5 255 17
12228 AAAA 1 3 3
12329 LOC 5 255 25
12430 NXT 5 255 44
12531 EID 5 255 15
12632 NIMLOC 5 255 36
12733 SRV 1 13 55
12834 ATMA 5 255 6
12935 NAPTR 5 255 35
13036 KX 5 255 24
13137 CERT 5 255 8
13238 A6 5 3 2
13339 DNAME 5 255 12
13440 SINK 5 255 52
13541 OPT 5 255 45
13642 APL 5 255 5
13743 DS 5 255 14
13844 SSHFP 5 255 56
13945 IPSECKEY 5 255 20
14046 RRSIG 5 255 49
14147 NSEC 5 255 40
14248 DNSKEY 5 255 13
14349 DHCID 5 255 10
14450 NSEC3 5 255 41
14551 NSEC3PARAM 5 255 42
14655 HIP 5 255 19
14799 SPF 5 255 54
148100 UINFO 5 255 62
149101 UID 5 255 61
150102 GID 5 255 16
151103 UNSPEC 5 255 63
152249 TKEY 5 255 58
153250 TSIG 5 255 59
154251 IXFR 5 255 22
155252 AXFR 5 255 7
156253 MAILB 5 255 27
157254 MAILA 5 255 26
15832768 TA 5 255 57
15932769 DLV 5 255 11
160\.
161
162-- Custom types (ab)using the "Private use" range from 65280 to 65534
163COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
16465280 A+PTR 2 2 2
16565281 AAAA+PTR 2 4 4
16665282 PTR template 3 6 2
16765283 A+PTR template 2 7 2
16865284 AAAA+PTR template 8 13 2
16965285 Delegation 2 8 2
170\.
171
172-- Update dbversion
173UPDATE misc SET value='1.2' WHERE key='dbversion';
Note: See TracBrowser for help on using the repository browser.