-- these lines could be run as a superuser.  alter database name, username, password, group as appropriate.
-- make sure to alter dnsdb.conf to match
-- CREATE GROUP dnsdb;
-- CREATE USER dnsdb WITH UNENCRYPTED PASSWORD 'secret' IN GROUP dnsdb;
-- CREATE DATABASE dnsdb OWNED BY dnsdb;
-- SET SESSION AUTHORIZATION 'dnsdb';

-- pre-pg8.3, this must be run as a superuser
CREATE LANGUAGE plpgsql;
-- it's required for:

-- Return proper conversion of string to inet, or 0.0.0.0/0 if the string is
-- not a valid inet value.  We need to do this to support "funky" records that
-- may not actually have valid IP address values.  Used for ORDER BY
CREATE OR REPLACE FUNCTION inetlazy (rdata text) RETURNS inet AS $$
BEGIN
	RETURN CAST(rdata AS inet);
EXCEPTION
	WHEN OTHERS THEN
		RETURN CAST('0.0.0.0/0' AS inet);
END;
$$ LANGUAGE plpgsql;


-- need a handy place to put eg a DB version identifier - useful for auto-upgrading a DB
CREATE TABLE misc (
	misc_id	serial NOT NULL,
	key text DEFAULT '' NOT NULL,
	value text DEFAULT '' NOT NULL
);

COPY misc (misc_id, key, value) FROM stdin;
1	dbversion	1.4.0
\.

CREATE TABLE locations (
    location character varying (4) PRIMARY KEY,
    loc_id serial UNIQUE,
    group_id integer NOT NULL DEFAULT 1,
    iplist text NOT NULL DEFAULT '',
    description character varying(40) NOT NULL DEFAULT '',
    comments text NOT NULL DEFAULT ''
);

CREATE TABLE default_records (
    record_id serial NOT NULL,
    group_id integer DEFAULT 1 NOT NULL,
    host text DEFAULT '' NOT NULL,
    "type" integer DEFAULT 1 NOT NULL,
    val text DEFAULT '' NOT NULL,
    distance integer DEFAULT 0 NOT NULL,
    weight integer DEFAULT 0 NOT NULL,
    port integer DEFAULT 0 NOT NULL,
    ttl integer DEFAULT 86400 NOT NULL,
    description text
);

-- default records for the default group
COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
1	1	ns1.example.com:hostmaster.DOMAIN	6	10800:3600:604800:5400	0	0	0	86400	\N
2	1	DOMAIN	2	ns2.example.com	0	0	0	7200	\N
3	1	DOMAIN	2	ns1.example.com	0	0	0	7200	\N
4	1	DOMAIN	1	10.0.0.4	0	0	0	7200	\N
5	1	DOMAIN	15	mx1.example.com	10	0	0	7200	\N
6	1	www.DOMAIN	5	DOMAIN	0	0	0	10800	\N
7	1	DOMAIN	16	"v=spf1 a mx -all"	0	0	0	10800	\N
\.

CREATE TABLE default_rev_records (
    record_id serial NOT NULL,
    group_id integer DEFAULT 1 NOT NULL,
    host text DEFAULT '' NOT NULL,
    "type" integer DEFAULT 1 NOT NULL,
    val text DEFAULT '' NOT NULL,
    ttl integer DEFAULT 86400 NOT NULL,
    description text
);

COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
1	1	hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN	6	3600:900:1048576:2560	3600	
2	1	unused-%r.ADMINDOMAIN	65283	ZONE	3600	
3	1	ns2.example.com	2	ZONE	7200	\N
4	1	ns1.example.com	2	ZONE	7200	\N
\.

CREATE TABLE domains (
    domain_id serial NOT NULL,
    "domain" character varying(80) NOT NULL,
    group_id integer DEFAULT 1 NOT NULL,
    description character varying(255) DEFAULT ''::character varying NOT NULL,
    status integer DEFAULT 1 NOT NULL,
    zserial integer,
    sertype character(1) DEFAULT 'D'::bpchar,
    changed boolean DEFAULT true NOT NULL,
    default_location character varying (4) DEFAULT '' NOT NULL
);
-- ~2x performance boost iff most zones are fed to output from the cache
CREATE INDEX dom_status_index ON domains (status);


CREATE TABLE revzones (
    rdns_id serial NOT NULL,
    revnet cidr NOT NULL,
    group_id integer DEFAULT 1 NOT NULL,
    description character varying(255) DEFAULT ''::character varying NOT NULL,
    status integer DEFAULT 1 NOT NULL,
    zserial integer,
    sertype character(1) DEFAULT 'D'::bpchar,
    changed boolean DEFAULT true NOT NULL,
    default_location character varying(4) DEFAULT ''::character varying NOT NULL
);
CREATE INDEX rev_status_index ON revzones USING btree (status);

CREATE TABLE groups (
    group_id serial NOT NULL,
    parent_group_id integer DEFAULT 1 NOT NULL,
    group_name character varying(255) DEFAULT ''::character varying NOT NULL,
    permission_id integer DEFAULT 1 NOT NULL,
    inherit_perm boolean DEFAULT true NOT NULL
);

-- Provide a basic default group
COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
1	1	1	default
\.

-- entry is text due to possible long entries from AXFR - a domain with "many"
-- odd records will overflow varchar(200)
CREATE TABLE log (
    log_id serial NOT NULL,
    domain_id integer,
    user_id integer,
    group_id integer,
    email character varying(60),
    name character varying(60),
    entry text,
    stamp timestamp with time zone DEFAULT now(),
    rdns_id integer
);
CREATE INDEX log_domain_id_index ON log(domain_id);
CREATE INDEX log_user_id_index ON log(user_id);
CREATE INDEX log_group_id_index ON log(group_id);
CREATE INDEX log_rdns_id_index ON log(rdns_id);

CREATE TABLE permissions (
    permission_id serial NOT NULL,
    "admin" boolean DEFAULT false NOT NULL,
    self_edit boolean DEFAULT false NOT NULL,
    group_create boolean DEFAULT false NOT NULL,
    group_edit boolean DEFAULT false NOT NULL,
    group_delete boolean DEFAULT false NOT NULL,
    user_create boolean DEFAULT false NOT NULL,
    user_edit boolean DEFAULT false NOT NULL,
    user_delete boolean DEFAULT false NOT NULL,
    domain_create boolean DEFAULT false NOT NULL,
    domain_edit boolean DEFAULT false NOT NULL,
    domain_delete boolean DEFAULT false NOT NULL,
    record_create boolean DEFAULT false NOT NULL,
    record_edit boolean DEFAULT false NOT NULL,
    record_delete boolean DEFAULT false NOT NULL,
    user_id integer UNIQUE,
    group_id integer UNIQUE,
    record_locchg boolean DEFAULT false NOT NULL,
    location_create boolean DEFAULT false NOT NULL,
    location_edit boolean DEFAULT false NOT NULL,
    location_delete boolean DEFAULT false NOT NULL,
    location_view boolean DEFAULT false NOT NULL
);

-- Need *two* basic permissions;  one for the initial group, one for the default admin user
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, record_locchg, location_create, location_edit, location_delete, location_view) FROM stdin;
1	f	f	f	f	f	f	f	f	t	t	t	t	t	t	\N	1	f	f	f	f	f
2	t	f	f	f	f	f	f	f	f	f	f	f	f	f	1	\N	f	f	f	f	f
\.

-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
CREATE TABLE records (
    domain_id integer NOT NULL DEFAULT 0,
    record_id serial NOT NULL,
    host text DEFAULT '' NOT NULL,
    "type" integer DEFAULT 1 NOT NULL,
    val text DEFAULT '' NOT NULL,
    distance integer DEFAULT 0 NOT NULL,
    weight integer DEFAULT 0 NOT NULL,
    port integer DEFAULT 0 NOT NULL,
    ttl integer DEFAULT 7200 NOT NULL,
    description text,
    rdns_id integer NOT NULL DEFAULT 0,
    location character varying (4) DEFAULT '' NOT NULL,
    stamp TIMESTAMP WITH TIME ZONE DEFAULT 'epoch' NOT NULL,
    expires boolean DEFAULT 'n' NOT NULL,
    stampactive boolean DEFAULT 'n' NOT NULL
);
CREATE INDEX rec_domain_index ON records USING btree (domain_id);
CREATE INDEX rec_revzone_index ON records USING btree (rdns_id);
CREATE INDEX rec_types_index ON records USING btree ("type");

CREATE TABLE rectypes (
    val integer NOT NULL,
    name character varying(20) NOT NULL,
    stdflag integer DEFAULT 1 NOT NULL,
    listorder integer DEFAULT 255 NOT NULL,
    alphaorder integer DEFAULT 32768 NOT NULL
);

-- Types are required.  NB:  these are vaguely read-only too
-- data from http://www.iana.org/assignments/dns-parameters
COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
1	A	1	1	1
2	NS	2	10	37
3	MD	5	255	29
4	MF	5	255	30
5	CNAME	2	12	9
6	SOA	0	0	53
7	MB	5	255	28
8	MG	5	255	31
9	MR	5	255	33
10	NULL	5	255	43
11	WKS	5	255	64
12	PTR	3	5	46
13	HINFO	5	255	18
14	MINFO	5	255	32
15	MX	1	11	34
16	TXT	2	13	60
17	RP	4	255	48
18	AFSDB	5	255	4
19	X25	5	255	65
20	ISDN	5	255	21
21	RT	5	255	50
22	NSAP	5	255	38
23	NSAP-PTR	5	255	39
24	SIG	5	255	51
25	KEY	5	255	23
26	PX	5	255	47
27	GPOS	5	255	17
28	AAAA	1	3	3
29	LOC	5	255	25
30	NXT	5	255	44
31	EID	5	255	15
32	NIMLOC	5	255	36
33	SRV	1	14	55
34	ATMA	5	255	6
35	NAPTR	5	255	35
36	KX	5	255	24
37	CERT	5	255	8
38	A6	5	3	2
39	DNAME	5	255	12
40	SINK	5	255	52
41	OPT	5	255	45
42	APL	5	255	5
43	DS	5	255	14
44	SSHFP	5	255	56
45	IPSECKEY	5	255	20
46	RRSIG	5	255	49
47	NSEC	5	255	40
48	DNSKEY	5	255	13
49	DHCID	5	255	10
50	NSEC3	5	255	41
51	NSEC3PARAM	5	255	42
55	HIP	5	255	19
99	SPF	5	255	54
100	UINFO	5	255	62
101	UID	5	255	61
102	GID	5	255	16
103	UNSPEC	5	255	63
249	TKEY	5	255	58
250	TSIG	5	255	59
251	IXFR	5	255	22
252	AXFR	5	255	7
253	MAILB	5	255	27
254	MAILA	5	255	26
32768	TA	5	255	57
32769	DLV	5	255	11
\.

-- Custom types (ab)using the "Private use" range from 65280 to 65534
COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
65280	A+PTR	2	2	2
65281	AAAA+PTR	2	4	4
65282	PTR template	3	6	2
65283	A+PTR template	2	7	2
65284	AAAA+PTR template	2	8	2
65285	Delegation	2	9	2
\.

CREATE TABLE users (
    user_id serial NOT NULL,
    group_id integer DEFAULT 1 NOT NULL,
    username character varying(60) NOT NULL,
    "password" character varying(34) NOT NULL,
    firstname character varying(60),
    lastname character varying(60),
    phone character varying(15),
    "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
    status integer DEFAULT 1 NOT NULL,
    permission_id integer DEFAULT 1 NOT NULL,
    inherit_perm boolean DEFAULT true NOT NULL
);

-- create initial default user?  may be better to create an "initialize" script or something
COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
1	1	admin	$1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d.	Initial	User	\N	S	1	2	f
\.

--
-- contraints.  add these here so initial data doesn't get added strangely.
--

-- primary keys
ALTER TABLE ONLY permissions
    ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);

ALTER TABLE ONLY groups
    ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);

ALTER TABLE ONLY domains
    ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);

ALTER TABLE ONLY domains
    ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain", default_location);

ALTER TABLE ONLY default_records
    ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);

ALTER TABLE ONLY records
    ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);

ALTER TABLE ONLY rectypes
    ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);

ALTER TABLE ONLY revzones
    ADD CONSTRAINT revzones_rdns_id_key UNIQUE (rdns_id);

ALTER TABLE ONLY revzones
    ADD CONSTRAINT revzones_pkey PRIMARY KEY (revnet, default_location);

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (username);

ALTER TABLE ONLY users
    ADD CONSTRAINT uidu UNIQUE (user_id);

-- foreign keys
-- fixme: permissions FK refs
ALTER TABLE ONLY locations
    ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);

ALTER TABLE ONLY domains
    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);

ALTER TABLE ONLY default_records
    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);

ALTER TABLE ONLY users
    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);

ALTER TABLE ONLY revzones
    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);

ALTER TABLE ONLY groups
    ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);

-- set starting sequence numbers, since we've inserted data before they're active
-- only set the ones that have data loaded with \copy, and obey the convention
-- that comes out of pg_dump
SELECT pg_catalog.setval('misc_misc_id_seq', 1, true);
SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);
SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
SELECT pg_catalog.setval('users_user_id_seq', 1, true);