Context Navigation

source: trunk/dns.sql@ 377

Last change on this file since 377 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: 10.5 KB

Rev	Line
[50]	1	-- these lines could be run as a superuser. alter database name, username, password, group as appropriate.
	2	-- make sure to alter dnsdb.conf to match
	3	-- CREATE GROUP dnsdb;
	4	-- CREATE USER dnsdb WITH UNENCRYPTED PASSWORD 'secret' IN GROUP dnsdb;
	5	-- CREATE DATABASE dnsdb OWNED BY dnsdb;
	6	-- SET SESSION AUTHORIZATION 'dnsdb';
	7
[212]	8	-- need a handy place to put eg a DB version identifier - useful for auto-upgrading a DB
	9	CREATE TABLE misc (
	10	misc_id serial NOT NULL,
	11	key text DEFAULT '' NOT NULL,
[215]	12	value text DEFAULT '' NOT NULL
[212]	13	);
	14
	15	COPY misc (misc_id, key, value) FROM stdin;
[347]	16	1 dbversion 1.2
[212]	17	\.
	18
[370]	19	CREATE TABLE locations (
	20	location character varying (4) PRIMARY KEY,
[375]	21	loc_id serial UNIQUE,
[370]	22	group_id integer NOT NULL DEFAULT 1,
	23	iplist text NOT NULL DEFAULT '',
[375]	24	description character varying(40) NOT NULL DEFAULT '',
	25	comments text NOT NULL DEFAULT ''
[370]	26	);
	27
[50]	28	CREATE TABLE default_records (
	29	record_id serial NOT NULL,
	30	group_id integer DEFAULT 1 NOT NULL,
[130]	31	host text DEFAULT '' NOT NULL,
[50]	32	"type" integer DEFAULT 1 NOT NULL,
[130]	33	val text DEFAULT '' NOT NULL,
[50]	34	distance integer DEFAULT 0 NOT NULL,
	35	weight integer DEFAULT 0 NOT NULL,
	36	port integer DEFAULT 0 NOT NULL,
	37	ttl integer DEFAULT 86400 NOT NULL,
[130]	38	description text
[50]	39	);
	40
	41	-- default records for the default group
	42	COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
	43	1 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N
	44	2 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N
	45	3 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N
	46	4 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N
	47	5 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N
	48	6 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N
	49	7 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N
	50	\.
	51
[224]	52	CREATE TABLE default_rev_records (
	53	record_id serial NOT NULL,
	54	group_id integer DEFAULT 1 NOT NULL,
	55	host text DEFAULT '' NOT NULL,
	56	"type" integer DEFAULT 1 NOT NULL,
	57	val text DEFAULT '' NOT NULL,
	58	ttl integer DEFAULT 86400 NOT NULL,
	59	description text
	60	);
	61
[249]	62	COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
[224]	63	1 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
	64	2 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
[328]	65	3 1 ns2.example.com 2 ZONE 7200 \N
	66	4 1 ns1.example.com 2 ZONE 7200 \N
[224]	67	\.
	68
[85]	69	CREATE TABLE domains (
	70	domain_id serial NOT NULL,
[347]	71	"domain" character varying(80) NOT NULL PRIMARY KEY,
[85]	72	group_id integer DEFAULT 1 NOT NULL,
	73	description character varying(255) DEFAULT ''::character varying NOT NULL,
	74	status integer DEFAULT 1 NOT NULL,
	75	zserial integer,
[366]	76	sertype character(1) DEFAULT 'D'::bpchar,
[370]	77	changed boolean DEFAULT true NOT NULL,
	78	default_location character varying (4) DEFAULT '' NOT NULL
[85]	79	);
	80
[224]	81	CREATE TABLE revzones (
	82	rdns_id serial NOT NULL,
[347]	83	revnet cidr NOT NULL PRIMARY KEY,
[224]	84	group_id integer DEFAULT 1 NOT NULL,
	85	description character varying(255) DEFAULT ''::character varying NOT NULL,
	86	status integer DEFAULT 1 NOT NULL,
	87	zserial integer,
[366]	88	sertype character(1) DEFAULT 'D'::bpchar,
[370]	89	changed boolean DEFAULT true NOT NULL,
	90	default_location character varying (4) DEFAULT '' NOT NULL
[224]	91	);
	92
[85]	93	CREATE TABLE groups (
	94	group_id serial NOT NULL,
	95	parent_group_id integer DEFAULT 1 NOT NULL,
	96	group_name character varying(255) DEFAULT ''::character varying NOT NULL,
	97	permission_id integer DEFAULT 1 NOT NULL,
	98	inherit_perm boolean DEFAULT true NOT NULL
	99	);
	100
	101	-- Provide a basic default group
[86]	102	COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
[85]	103	1 1 1 default
	104	\.
	105
[91]	106	-- entry is text due to possible long entries from AXFR - a domain with "many"
	107	-- odd records will overflow varchar(200)
[85]	108	CREATE TABLE log (
[89]	109	log_id serial NOT NULL,
[85]	110	domain_id integer,
[224]	111	rdns_id integer,
[85]	112	user_id integer,
	113	group_id integer,
	114	email character varying(60),
	115	name character varying(60),
[91]	116	entry text,
[85]	117	stamp timestamp with time zone DEFAULT now()
	118	);
	119
	120	CREATE TABLE permissions (
	121	permission_id serial NOT NULL,
	122	"admin" boolean DEFAULT false NOT NULL,
	123	self_edit boolean DEFAULT false NOT NULL,
	124	group_create boolean DEFAULT false NOT NULL,
	125	group_edit boolean DEFAULT false NOT NULL,
	126	group_delete boolean DEFAULT false NOT NULL,
	127	user_create boolean DEFAULT false NOT NULL,
	128	user_edit boolean DEFAULT false NOT NULL,
	129	user_delete boolean DEFAULT false NOT NULL,
	130	domain_create boolean DEFAULT false NOT NULL,
	131	domain_edit boolean DEFAULT false NOT NULL,
	132	domain_delete boolean DEFAULT false NOT NULL,
	133	record_create boolean DEFAULT false NOT NULL,
	134	record_edit boolean DEFAULT false NOT NULL,
	135	record_delete boolean DEFAULT false NOT NULL,
[370]	136	location_create boolean DEFAULT false NOT NULL,
	137	location_edit boolean DEFAULT false NOT NULL,
	138	location_delete boolean DEFAULT false NOT NULL,
	139	location_view boolean DEFAULT false NOT NULL,
[86]	140	user_id integer UNIQUE,
	141	group_id integer UNIQUE
[85]	142	);
	143
	144	-- Need two basic permissions; one for the initial group, one for the default admin user
[86]	145	COPY permissions (permission_id, admin, self_edit, group_create, group_edit, group_delete, user_create, user_edit, user_delete, domain_create, domain_edit, domain_delete, record_create, record_edit, record_delete, user_id, group_id) FROM stdin;
[370]	146	1 f f f f f f f f t t t t t t f f f f \N 1
	147	2 t f f f f f f f f f f f f f f f f f 1 \N
[85]	148	\.
	149
[224]	150	-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
[50]	151	CREATE TABLE records (
[224]	152	domain_id integer NOT NULL DEFAULT 0,
	153	rdns_id integer NOT NULL DEFAULT 0,
[50]	154	record_id serial NOT NULL,
[130]	155	host text DEFAULT '' NOT NULL,
[50]	156	"type" integer DEFAULT 1 NOT NULL,
[130]	157	val text DEFAULT '' NOT NULL,
[50]	158	distance integer DEFAULT 0 NOT NULL,
	159	weight integer DEFAULT 0 NOT NULL,
	160	port integer DEFAULT 0 NOT NULL,
[85]	161	ttl integer DEFAULT 7200 NOT NULL,
[370]	162	description text,
	163	default_location character varying (4) DEFAULT '' NOT NULL
[50]	164	);
	165
	166	CREATE TABLE rectypes (
	167	val integer NOT NULL,
[224]	168	name character varying(20) NOT NULL,
[50]	169	stdflag integer DEFAULT 1 NOT NULL,
[102]	170	listorder integer DEFAULT 255 NOT NULL,
	171	alphaorder integer DEFAULT 32768 NOT NULL
[50]	172	);
	173
	174	-- Types are required. NB: these are vaguely read-only too
[85]	175	-- data from http://www.iana.org/assignments/dns-parameters
[102]	176	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
	177	1 A 1 1 1
[300]	178	2 NS 2 9 37
[224]	179	3 MD 5 255 29
	180	4 MF 5 255 30
[300]	181	5 CNAME 2 11 9
[224]	182	6 SOA 0 0 53
	183	7 MB 5 255 28
	184	8 MG 5 255 31
	185	9 MR 5 255 33
	186	10 NULL 5 255 43
	187	11 WKS 5 255 64
[300]	188	12 PTR 3 5 46
[224]	189	13 HINFO 5 255 18
	190	14 MINFO 5 255 32
[300]	191	15 MX 1 10 34
[351]	192	16 TXT 2 12 60
[224]	193	17 RP 4 255 48
	194	18 AFSDB 5 255 4
	195	19 X25 5 255 65
	196	20 ISDN 5 255 21
	197	21 RT 5 255 50
	198	22 NSAP 5 255 38
	199	23 NSAP-PTR 5 255 39
	200	24 SIG 5 255 51
	201	25 KEY 5 255 23
	202	26 PX 5 255 47
	203	27 GPOS 5 255 17
	204	28 AAAA 1 3 3
	205	29 LOC 5 255 25
	206	30 NXT 5 255 44
	207	31 EID 5 255 15
	208	32 NIMLOC 5 255 36
[300]	209	33 SRV 1 13 55
[224]	210	34 ATMA 5 255 6
	211	35 NAPTR 5 255 35
	212	36 KX 5 255 24
	213	37 CERT 5 255 8
	214	38 A6 5 3 2
	215	39 DNAME 5 255 12
	216	40 SINK 5 255 52
	217	41 OPT 5 255 45
	218	42 APL 5 255 5
	219	43 DS 5 255 14
	220	44 SSHFP 5 255 56
	221	45 IPSECKEY 5 255 20
	222	46 RRSIG 5 255 49
	223	47 NSEC 5 255 40
	224	48 DNSKEY 5 255 13
	225	49 DHCID 5 255 10
	226	50 NSEC3 5 255 41
	227	51 NSEC3PARAM 5 255 42
	228	55 HIP 5 255 19
	229	99 SPF 5 255 54
	230	100 UINFO 5 255 62
	231	101 UID 5 255 61
	232	102 GID 5 255 16
	233	103 UNSPEC 5 255 63
	234	249 TKEY 5 255 58
	235	250 TSIG 5 255 59
	236	251 IXFR 5 255 22
	237	252 AXFR 5 255 7
	238	253 MAILB 5 255 27
	239	254 MAILA 5 255 26
	240	32768 TA 5 255 57
	241	32769 DLV 5 255 11
[50]	242	\.
	243
[224]	244	-- Custom types (ab)using the "Private use" range from 65280 to 65534
	245	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
	246	65280 A+PTR 2 2 2
	247	65281 AAAA+PTR 2 4 4
[300]	248	65282 PTR template 3 6 2
[342]	249	65283 A+PTR template 2 7 2
[300]	250	65284 AAAA+PTR template 8 13 2
[342]	251	65285 Delegation 2 8 2
[224]	252	\.
	253
[50]	254	CREATE TABLE users (
	255	user_id serial NOT NULL,
	256	group_id integer DEFAULT 1 NOT NULL,
	257	username character varying(60) NOT NULL,
	258	"password" character varying(34) NOT NULL,
[85]	259	firstname character varying(60),
	260	lastname character varying(60),
[50]	261	phone character varying(15),
	262	"type" character(1) DEFAULT 'S'::bpchar NOT NULL,
	263	status integer DEFAULT 1 NOT NULL,
[85]	264	permission_id integer DEFAULT 1 NOT NULL,
	265	inherit_perm boolean DEFAULT true NOT NULL
[50]	266	);
	267
	268	-- create initial default user? may be better to create an "initialize" script or something
[86]	269	COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
[89]	270	1 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
[50]	271	\.
	272
	273	--
	274	-- contraints. add these here so initial data doesn't get added strangely.
	275	--
	276
	277	-- primary keys
[65]	278	ALTER TABLE ONLY permissions
	279	ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
	280
[50]	281	ALTER TABLE ONLY groups
	282	ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
	283
	284	ALTER TABLE ONLY domains
	285	ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain");
	286
	287	ALTER TABLE ONLY domains
	288	ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
	289
	290	ALTER TABLE ONLY default_records
	291	ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
	292
	293	ALTER TABLE ONLY records
	294	ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
	295
	296	ALTER TABLE ONLY rectypes
	297	ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
	298
	299	ALTER TABLE ONLY users
	300	ADD CONSTRAINT users_pkey PRIMARY KEY (username);
	301
	302	ALTER TABLE ONLY users
	303	ADD CONSTRAINT uidu UNIQUE (user_id);
	304
	305	-- foreign keys
[65]	306	-- fixme: permissions FK refs
[370]	307	ALTER TABLE ONLY locations
	308	ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	309
[50]	310	ALTER TABLE ONLY domains
	311	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	312
[369]	313	ALTER TABLE ONLY revzones
	314	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	315
[50]	316	ALTER TABLE ONLY default_records
	317	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	318
	319	ALTER TABLE ONLY users
	320	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	321
	322	ALTER TABLE ONLY groups
	323	ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
[86]	324
	325	-- set starting sequence numbers, since we've inserted data before they're active
[224]	326	SELECT pg_catalog.setval('misc_misc_id_seq', 2, false);
	327	SELECT pg_catalog.setval('default_records_record_id_seq', 8, false);
[300]	328	SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
[86]	329	SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
[224]	330	SELECT pg_catalog.setval('groups_group_id_seq', 2, false);
	331	SELECT pg_catalog.setval('permissions_permission_id_seq', 3, false);
[86]	332	SELECT pg_catalog.setval('records_record_id_seq', 1, false);
	333	SELECT pg_catalog.setval('users_user_id_seq', 2, false);

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

Context Navigation

source: trunk/dns.sql@ 377

Download in other formats: