Context Navigation

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

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

/trunk

Re-re-re-review and tweak the new-database initialization dns.sql script
and the dns-1.0-1.2.sql upgrade script to ensure the resulting structure
is as close as possible to identical to minimize any possible breakage.

Creating a database with the 1.0 SQL init, then applying the 1.0-1.2
upgrade script, will result in pg_dump output with only one difference;
the setval for the sequence on user_id in the users table. Sematically,
there is no difference in the database behaviour (the first new user
created will have ID 2), but the changes make it easier to copy-paste
pg_dump schema fragments if further updates to the default schema are
required.

File size: 5.4 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,
[375]	6	loc_id serial UNIQUE,
[370]	7	group_id integer NOT NULL DEFAULT 1,
	8	iplist text NOT NULL DEFAULT '',
[375]	9	description character varying(40) NOT NULL DEFAULT '',
	10	comments text NOT NULL DEFAULT ''
[370]	11	);
	12
	13	ALTER TABLE ONLY locations
	14	ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	15
[387]	16	ALTER TABLE permissions ADD COLUMN record_locchg boolean DEFAULT false NOT NULL;
[370]	17	ALTER TABLE permissions ADD COLUMN location_create boolean DEFAULT false NOT NULL;
	18	ALTER TABLE permissions ADD COLUMN location_edit boolean DEFAULT false NOT NULL;
	19	ALTER TABLE permissions ADD COLUMN location_delete boolean DEFAULT false NOT NULL;
	20	ALTER TABLE permissions ADD COLUMN location_view boolean DEFAULT false NOT NULL;
	21
[365]	22	-- Minor buglet; domains must be unique
	23	-- ALTER TABLE domains ADD PRIMARY KEY (domain);
	24
	25	CREATE 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
	35	COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
	36	1 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
	37	2 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
	38	3 1 ns2.example.com 2 ZONE 7200 \N
	39	4 1 ns1.example.com 2 ZONE 7200 \N
	40	\.
	41
[507]	42	SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);
[365]	43
[370]	44	ALTER TABLE domains ADD COLUMN changed boolean DEFAULT true NOT NULL;
	45	ALTER TABLE domains ADD COLUMN default_location character varying (4) DEFAULT '' NOT NULL;
[368]	46	-- ~2x performance boost iff most zones are fed to output from the cache
	47	CREATE INDEX dom_status_index ON domains (status);
[366]	48
[365]	49	CREATE 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,
[366]	56	sertype character(1) DEFAULT 'D'::bpchar,
[370]	57	changed boolean DEFAULT true NOT NULL,
	58	default_location character varying (4) DEFAULT '' NOT NULL
[365]	59	);
[368]	60	CREATE INDEX rev_status_index ON revzones (status);
[365]	61
[369]	62	ALTER TABLE ONLY revzones
	63	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	64
[365]	65	ALTER 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.
	70	ALTER TABLE records DROP CONSTRAINT "$1";
	71	ALTER TABLE records ALTER COLUMN domain_id SET DEFAULT 0;
[370]	72	ALTER TABLE records ADD COLUMN rdns_id INTEGER DEFAULT 0 NOT NULL;
	73	ALTER TABLE records ADD COLUMN location character varying (4) DEFAULT '' NOT NULL;
	74
[368]	75	-- ~120s -> 75s performance boost on 100K records when always exporting all records
	76	CREATE INDEX rec_types_index ON records (type);
	77	-- Further ~1/3 performance gain, same dataset
	78	CREATE INDEX rec_domain_index ON records (domain_id);
	79	CREATE INDEX rec_revzone_index ON records (rdns_id);
[365]	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.
	84	DROP TABLE rectypes;
	85	CREATE 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
	95	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
	96	1 A 1 1 1
[456]	97	2 NS 2 10 37
[365]	98	3 MD 5 255 29
	99	4 MF 5 255 30
[456]	100	5 CNAME 2 12 9
[365]	101	6 SOA 0 0 53
	102	7 MB 5 255 28
	103	8 MG 5 255 31
	104	9 MR 5 255 33
	105	10 NULL 5 255 43
	106	11 WKS 5 255 64
	107	12 PTR 3 5 46
	108	13 HINFO 5 255 18
	109	14 MINFO 5 255 32
[456]	110	15 MX 1 11 34
	111	16 TXT 2 13 60
[365]	112	17 RP 4 255 48
	113	18 AFSDB 5 255 4
	114	19 X25 5 255 65
	115	20 ISDN 5 255 21
	116	21 RT 5 255 50
	117	22 NSAP 5 255 38
	118	23 NSAP-PTR 5 255 39
	119	24 SIG 5 255 51
	120	25 KEY 5 255 23
	121	26 PX 5 255 47
	122	27 GPOS 5 255 17
	123	28 AAAA 1 3 3
	124	29 LOC 5 255 25
	125	30 NXT 5 255 44
	126	31 EID 5 255 15
	127	32 NIMLOC 5 255 36
[456]	128	33 SRV 1 14 55
[365]	129	34 ATMA 5 255 6
	130	35 NAPTR 5 255 35
	131	36 KX 5 255 24
	132	37 CERT 5 255 8
	133	38 A6 5 3 2
	134	39 DNAME 5 255 12
	135	40 SINK 5 255 52
	136	41 OPT 5 255 45
	137	42 APL 5 255 5
	138	43 DS 5 255 14
	139	44 SSHFP 5 255 56
	140	45 IPSECKEY 5 255 20
	141	46 RRSIG 5 255 49
	142	47 NSEC 5 255 40
	143	48 DNSKEY 5 255 13
	144	49 DHCID 5 255 10
	145	50 NSEC3 5 255 41
	146	51 NSEC3PARAM 5 255 42
	147	55 HIP 5 255 19
	148	99 SPF 5 255 54
	149	100 UINFO 5 255 62
	150	101 UID 5 255 61
	151	102 GID 5 255 16
	152	103 UNSPEC 5 255 63
	153	249 TKEY 5 255 58
	154	250 TSIG 5 255 59
	155	251 IXFR 5 255 22
	156	252 AXFR 5 255 7
	157	253 MAILB 5 255 27
	158	254 MAILA 5 255 26
	159	32768 TA 5 255 57
	160	32769 DLV 5 255 11
	161	\.
	162
	163	-- Custom types (ab)using the "Private use" range from 65280 to 65534
	164	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
	165	65280 A+PTR 2 2 2
	166	65281 AAAA+PTR 2 4 4
	167	65282 PTR template 3 6 2
	168	65283 A+PTR template 2 7 2
[456]	169	65284 AAAA+PTR template 2 8 2
	170	65285 Delegation 2 9 2
[365]	171	\.
	172
[507]	173	-- and readd the primary key
	174	ALTER TABLE ONLY rectypes
	175	ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
	176
[365]	177	-- Update dbversion
	178	UPDATE misc SET value='1.2' WHERE key='dbversion';

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

Download in other formats: