source: trunk/new-dns.sql@ 157

-- fixme:  include template-ish ACL?
CREATE TABLE groups (
        group_id SERIAL UNIQUE,
        parent_group_id int NOT NULL DEFAULT '1',
        name varchar(255) NOT NULL DEFAULT ''
);
-- gotta have a default group to attach some further default data to...
INSERT INTO groups (name) VALUES ('default');

-- use ON DELETE (CASCADE|RESTRICT|NO ACTION) on FKs

CREATE TABLE domains (
        domain_id SERIAL UNIQUE,
        domain varchar(80) NOT NULL PRIMARY KEY,
        group_id int NOT NULL DEFAULT 1 REFERENCES groups (group_id),
        description varchar(255) NOT NULL DEFAULT '',
        status int NOT NULL DEFAULT 1
);

CREATE TABLE default_records (
        record_id SERIAL PRIMARY KEY,
        group_id int NOT NULL DEFAULT 1 REFERENCES groups (group_id),
        host varchar(100) NOT NULL DEFAULT '',
        type int NOT NULL DEFAULT 1,
        val varchar(100) NOT NULL DEFAULT '',
        distance int NOT NULL DEFAULT 0,
        weight int NOT NULL DEFAULT 0,
        port int NOT NULL DEFAULT 0,
        ttl int NOT NULL DEFAULT 86400,
        description varchar(255)
);

-- let's take pity on the poor sod installing things and include some default data that's halfway sane
INSERT INTO default_records (group_id,host,type,val,ttl) VALUES (1,'ns1.example.com:hostmaster.DOMAIN',6,'10800:3600:604800:10800',86400);

CREATE TABLE records (
        domain_id int NOT NULL REFERENCES domains (domain_id),
        record_id SERIAL PRIMARY KEY,
        host varchar(100) NOT NULL DEFAULT '',
        type int NOT NULL DEFAULT 1,
        val varchar(100) NOT NULL DEFAULT '',
        distance int NOT NULL DEFAULT 0,
        weight int NOT NULL DEFAULT 0,
        port int NOT NULL DEFAULT 0,
        ttl int NOT NULL DEFAULT 86400,
        description varchar(255)
);

-- oooo! oooo!  allow staff/unwashed-masses parallel users?
--  - multitable, or just another column in the database...?
--  - go with column for now (could even overload groups...)
CREATE TABLE users (
        user_id SERIAL PRIMARY KEY,
        group_id int NOT NULL DEFAULT 1 REFERENCES groups (group_id),
        email varchar(60) NOT NULL,
        password varchar(34) NOT NULL,
        firstname varchar(30),
        lastname varchar(30),
        phone varchar(15),
        type char NOT NULL DEFAULT 'S',
        status int NOT NULL DEFAULT 1,
        acl varchar(40) NOT NULL DEFAULT 'b'
);

-- include a default user, so we don't break on install.  not happy with this, but...
-- default password is 'test'
INSERT INTO users (group_id,email,password,type,status,acl) VALUES (1,'test@test','$1$BByge8u2$48AaGX3YeHplfErX5Tlqa1','S',1,'A');

-- these could be stuffed in a hash, but that gets tedious
CREATE TABLE rectypes (
        val int NOT NULL,
        name varchar(12) NOT NULL,
        stdflag int NOT NULL DEFAULT 1,
        listorder int NOT NULL DEFAULT 255,
        PRIMARY KEY (val,name)
);
-- data from http://www.iana.org/assignments/dns-parameters
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
\.