]>
Commit | Line | Data |
---|---|---|
94828012 MS |
1 | PRAGMA foreign_keys = 1; |
2 | ||
fe8e6b44 KM |
3 | CREATE TABLE domains ( |
4 | id INTEGER PRIMARY KEY, | |
5 | name VARCHAR(255) NOT NULL COLLATE NOCASE, | |
6 | master VARCHAR(128) DEFAULT NULL, | |
7 | last_check INTEGER DEFAULT NULL, | |
8 | type VARCHAR(6) NOT NULL, | |
9 | notified_serial INTEGER DEFAULT NULL, | |
10 | account VARCHAR(40) DEFAULT NULL | |
11 | ); | |
12 | ||
13 | CREATE UNIQUE INDEX name_index ON domains(name); | |
14 | ||
15 | ||
fe8e6b44 KM |
16 | CREATE TABLE records ( |
17 | id INTEGER PRIMARY KEY, | |
18 | domain_id INTEGER DEFAULT NULL, | |
19 | name VARCHAR(255) DEFAULT NULL, | |
20 | type VARCHAR(10) DEFAULT NULL, | |
21 | content VARCHAR(65535) DEFAULT NULL, | |
22 | ttl INTEGER DEFAULT NULL, | |
23 | prio INTEGER DEFAULT NULL, | |
fe8e6b44 KM |
24 | disabled BOOLEAN DEFAULT 0, |
25 | ordername VARCHAR(255), | |
94828012 MS |
26 | auth BOOL DEFAULT 1, |
27 | FOREIGN KEY(domain_id) REFERENCES domains(id) ON DELETE CASCADE ON UPDATE CASCADE | |
fe8e6b44 KM |
28 | ); |
29 | ||
30 | CREATE INDEX rec_name_index ON records(name); | |
31 | CREATE INDEX nametype_index ON records(name,type); | |
32 | CREATE INDEX domain_id ON records(domain_id); | |
33 | CREATE INDEX orderindex ON records(ordername); | |
34 | ||
35 | ||
fe8e6b44 KM |
36 | CREATE TABLE supermasters ( |
37 | ip VARCHAR(64) NOT NULL, | |
38 | nameserver VARCHAR(255) NOT NULL COLLATE NOCASE, | |
dd930ede | 39 | account VARCHAR(40) NOT NULL |
fe8e6b44 KM |
40 | ); |
41 | ||
42 | CREATE UNIQUE INDEX ip_nameserver_pk ON supermasters(ip, nameserver); | |
43 | ||
44 | ||
fe8e6b44 KM |
45 | CREATE TABLE comments ( |
46 | id INTEGER PRIMARY KEY, | |
47 | domain_id INTEGER NOT NULL, | |
48 | name VARCHAR(255) NOT NULL, | |
49 | type VARCHAR(10) NOT NULL, | |
50 | modified_at INT NOT NULL, | |
51 | account VARCHAR(40) DEFAULT NULL, | |
94828012 MS |
52 | comment VARCHAR(65535) NOT NULL, |
53 | FOREIGN KEY(domain_id) REFERENCES domains(id) ON DELETE CASCADE ON UPDATE CASCADE | |
fe8e6b44 KM |
54 | ); |
55 | ||
56 | CREATE INDEX comments_domain_id_index ON comments (domain_id); | |
57 | CREATE INDEX comments_nametype_index ON comments (name, type); | |
58 | CREATE INDEX comments_order_idx ON comments (domain_id, modified_at); | |
59 | ||
60 | ||
fe8e6b44 KM |
61 | CREATE TABLE domainmetadata ( |
62 | id INTEGER PRIMARY KEY, | |
63 | domain_id INT NOT NULL, | |
b0e315d9 | 64 | kind VARCHAR(32) COLLATE NOCASE, |
94828012 MS |
65 | content TEXT, |
66 | FOREIGN KEY(domain_id) REFERENCES domains(id) ON DELETE CASCADE ON UPDATE CASCADE | |
fe8e6b44 KM |
67 | ); |
68 | ||
69 | CREATE INDEX domainmetaidindex ON domainmetadata(domain_id); | |
70 | ||
71 | ||
fe8e6b44 KM |
72 | CREATE TABLE cryptokeys ( |
73 | id INTEGER PRIMARY KEY, | |
74 | domain_id INT NOT NULL, | |
75 | flags INT NOT NULL, | |
76 | active BOOL, | |
94828012 MS |
77 | content TEXT, |
78 | FOREIGN KEY(domain_id) REFERENCES domains(id) ON DELETE CASCADE ON UPDATE CASCADE | |
fe8e6b44 KM |
79 | ); |
80 | ||
81 | CREATE INDEX domainidindex ON cryptokeys(domain_id); | |
82 | ||
83 | ||
fe8e6b44 KM |
84 | CREATE TABLE tsigkeys ( |
85 | id INTEGER PRIMARY KEY, | |
86 | name VARCHAR(255) COLLATE NOCASE, | |
87 | algorithm VARCHAR(50) COLLATE NOCASE, | |
88 | secret VARCHAR(255) | |
89 | ); | |
90 | ||
91 | CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm); |