]>
Commit | Line | Data |
---|---|---|
40ef9665 KM |
1 | CREATE TABLE domains ( |
2 | id SERIAL PRIMARY KEY, | |
3 | name VARCHAR(255) NOT NULL, | |
4 | master VARCHAR(128) DEFAULT NULL, | |
5 | last_check INT DEFAULT NULL, | |
70005f1e | 6 | type VARCHAR(6) NOT NULL, |
1953ba21 | 7 | notified_serial BIGINT DEFAULT NULL, |
40ef9665 KM |
8 | account VARCHAR(40) DEFAULT NULL, |
9 | CONSTRAINT c_lowercase_name CHECK (((name)::TEXT = LOWER((name)::TEXT))) | |
10 | ); | |
11 | ||
12 | CREATE UNIQUE INDEX name_index ON domains(name); | |
13 | ||
14 | ||
15 | CREATE TABLE records ( | |
92585308 | 16 | id BIGSERIAL PRIMARY KEY, |
40ef9665 KM |
17 | domain_id INT DEFAULT NULL, |
18 | name VARCHAR(255) DEFAULT NULL, | |
19 | type VARCHAR(10) DEFAULT NULL, | |
20 | content VARCHAR(65535) DEFAULT NULL, | |
21 | ttl INT DEFAULT NULL, | |
22 | prio INT DEFAULT NULL, | |
40ef9665 KM |
23 | disabled BOOL DEFAULT 'f', |
24 | ordername VARCHAR(255), | |
25 | auth BOOL DEFAULT 't', | |
26 | CONSTRAINT domain_exists | |
27 | FOREIGN KEY(domain_id) REFERENCES domains(id) | |
28 | ON DELETE CASCADE, | |
29 | CONSTRAINT c_lowercase_name CHECK (((name)::TEXT = LOWER((name)::TEXT))) | |
30 | ); | |
31 | ||
32 | CREATE INDEX rec_name_index ON records(name); | |
33 | CREATE INDEX nametype_index ON records(name,type); | |
34 | CREATE INDEX domain_id ON records(domain_id); | |
35 | CREATE INDEX recordorder ON records (domain_id, ordername text_pattern_ops); | |
36 | ||
37 | ||
40ef9665 KM |
38 | CREATE TABLE supermasters ( |
39 | ip INET NOT NULL, | |
40 | nameserver VARCHAR(255) NOT NULL, | |
dd930ede | 41 | account VARCHAR(40) NOT NULL, |
40ef9665 KM |
42 | PRIMARY KEY(ip, nameserver) |
43 | ); | |
44 | ||
45 | ||
40ef9665 KM |
46 | CREATE TABLE comments ( |
47 | id SERIAL PRIMARY KEY, | |
48 | domain_id INT NOT NULL, | |
49 | name VARCHAR(255) NOT NULL, | |
50 | type VARCHAR(10) NOT NULL, | |
51 | modified_at INT NOT NULL, | |
52 | account VARCHAR(40) DEFAULT NULL, | |
53 | comment VARCHAR(65535) NOT NULL, | |
54 | CONSTRAINT domain_exists | |
55 | FOREIGN KEY(domain_id) REFERENCES domains(id) | |
56 | ON DELETE CASCADE, | |
57 | CONSTRAINT c_lowercase_name CHECK (((name)::TEXT = LOWER((name)::TEXT))) | |
58 | ); | |
59 | ||
60 | CREATE INDEX comments_domain_id_idx ON comments (domain_id); | |
61 | CREATE INDEX comments_name_type_idx ON comments (name, type); | |
62 | CREATE INDEX comments_order_idx ON comments (domain_id, modified_at); | |
63 | ||
64 | ||
40ef9665 KM |
65 | CREATE TABLE domainmetadata ( |
66 | id SERIAL PRIMARY KEY, | |
67 | domain_id INT REFERENCES domains(id) ON DELETE CASCADE, | |
b0e315d9 | 68 | kind VARCHAR(32), |
40ef9665 KM |
69 | content TEXT |
70 | ); | |
71 | ||
72 | CREATE INDEX domainidmetaindex ON domainmetadata(domain_id); | |
73 | ||
74 | ||
40ef9665 KM |
75 | CREATE TABLE cryptokeys ( |
76 | id SERIAL PRIMARY KEY, | |
77 | domain_id INT REFERENCES domains(id) ON DELETE CASCADE, | |
78 | flags INT NOT NULL, | |
79 | active BOOL, | |
80 | content TEXT | |
81 | ); | |
82 | ||
83 | CREATE INDEX domainidindex ON cryptokeys(domain_id); | |
84 | ||
85 | ||
40ef9665 KM |
86 | CREATE TABLE tsigkeys ( |
87 | id SERIAL PRIMARY KEY, | |
88 | name VARCHAR(255), | |
89 | algorithm VARCHAR(50), | |
90 | secret VARCHAR(255), | |
91 | CONSTRAINT c_lowercase_name CHECK (((name)::TEXT = LOWER((name)::TEXT))) | |
92 | ); | |
93 | ||
94 | CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm); |