source: trunk/dns.sql@ 50

-- 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';

-- tabledefs and preloaded data bits
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
);

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

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
);

CREATE TABLE default_records (
    record_id serial NOT NULL,
    group_id integer DEFAULT 1 NOT NULL,
    host character varying(100) DEFAULT ''::character varying NOT NULL,
    "type" integer DEFAULT 1 NOT NULL,
    val character varying(100) DEFAULT ''::character varying 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 character varying(255)
);

-- 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 records (
    domain_id integer NOT NULL,
    record_id serial NOT NULL,
    host character varying(100) DEFAULT ''::character varying NOT NULL,
    "type" integer DEFAULT 1 NOT NULL,
    val character varying(100) DEFAULT ''::character varying 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 character varying(255)
);

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

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

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(30),
    lastname character varying(30),
    phone character varying(15),
    "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
    status integer DEFAULT 1 NOT NULL,
    acl character varying(40) DEFAULT 'b'::character varying 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, acl) FROM stdin;
1       1       test@test       $1$BByge8u2$48AaGX3YeHplfErX5Tlqa1      \N      \N      \N      S       1       A
\.

CREATE TABLE log (
    domain_id integer,
    user_id integer,
    group_id integer,
    email character varying(60),
    name character varying(60),
    entry character varying(200),
    stamp timestamp with time zone
);

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

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

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

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

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 users
    ADD CONSTRAINT users_pkey PRIMARY KEY (username);

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

-- foreign keys
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 records
    ADD CONSTRAINT "$1" FOREIGN KEY (domain_id) REFERENCES domains(domain_id);

ALTER TABLE ONLY users
    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 sequence start values - make sure we don't screw up adding
-- records to tables that already have a few entries

SELECT pg_catalog.setval('groups_group_id_seq', 52, true);

SELECT pg_catalog.setval('domains_domain_id_seq', 953, true);

SELECT pg_catalog.setval('default_records_record_id_seq', 320, true);

SELECT pg_catalog.setval('records_record_id_seq', 660, true);

SELECT pg_catalog.setval('users_user_id_seq', 37, true);