Context Navigation

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

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

/trunk

Checkpoint adding location/view support. See #10.

minor location list template tweak
extend importer to deal with locations on records, and location definitions
extend exporter to handle locations

Also:

minor SQL error-log cleanup (boolean types use 'y'/'n' or 'true'/'false'). See #25, sort of
Fix handling of CNAME exports for reverse zones; the records look ugly but there's no simple way to autoconvert them to the template types on import
Fix lurking buglet in tabledef update; we're using a 4-char field for locations to allow for legacy uses.

File size: 5.1 KB

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

Note: See TracBrowser for help on using the repository browser.

Download in other formats: