~liberachat/beryllia

ref: 2b7ebbed79a307a32d5134f39421aa47a88b9a8c beryllia/make-database.sql -rw-r--r-- 6.5 KiB
2b7ebbed — jesopo add index on cliexit(ts) 3 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
-- 16  is nickname length
-- 10  is username length
-- 32  is kline tag length
-- 50  is realname length
-- 64  is hostname length
-- 92  is mask length
-- 260 is reason length

BEGIN;

CREATE TABLE kline (
    id           SERIAL PRIMARY KEY,
    mask         VARCHAR(92)  NOT NULL,
    search_mask  VARCHAR(92)  NOT NULL,
    source       VARCHAR(92)  NOT NULL,
    oper         VARCHAR(16)  NOT NULL,
    duration     INT          NOT NULL,
    reason       VARCHAR(260) NOT NULL,
    ts           TIMESTAMP    NOT NULL,
    expire       TIMESTAMP    NOT NULL,
    last_reject  TIMESTAMP
);
-- for retention period bulk deletion
CREATE INDEX kline_expire ON kline(expire);
-- for database.kline.find()
CREATE INDEX kline_mask   ON kline(mask);

CREATE TABLE kline_remove (
    kline_id INTEGER     NOT NULL  PRIMARY KEY  REFERENCES kline (id)  ON DELETE CASCADE,
    source   VARCHAR(92),
    oper     VARCHAR(16),
    ts       TIMESTAMP   NOT NULL
);
-- for joining with kline(id)
CREATE INDEX kline_remove_kline_id ON kline_remove(kline_id);

CREATE TABLE kline_kill (
    id          SERIAL PRIMARY KEY,
    kline_id    INTEGER     NOT NULL  REFERENCES kline (id)  ON DELETE CASCADE,
    nickname    VARCHAR(16) NOT NULL,
    search_nick VARCHAR(16) NOT NULL,
    username    VARCHAR(10) NOT NULL,
    search_user VARCHAR(10) NOT NULL,
    hostname    VARCHAR(64) NOT NULL,
    search_host VARCHAR(64) NOT NULL,
    ip          INET,
    ts          TIMESTAMP   NOT NULL
);
-- for joining with kline(id)
CREATE INDEX kline_kill_kline_id    ON kline_kill(kline_id);
-- for `!kcheck` searches
CREATE INDEX kline_kill_search_nick ON kline_kill(search_nick);
CREATE INDEX kline_kill_search_user ON kline_kill(search_user);
CREATE INDEX kline_kill_search_host ON kline_kill(search_host);
CREATE INDEX kline_kill_ip          ON kline_kill(ip);

CREATE TABLE kline_reject (
    id          SERIAL PRIMARY KEY,
    kline_id    INTEGER     NOT NULL  REFERENCES kline (id)  ON DELETE CASCADE,
    nickname    VARCHAR(16) NOT NULL,
    search_nick VARCHAR(16) NOT NULL,
    username    VARCHAR(10) NOT NULL,
    search_user VARCHAR(10) NOT NULL,
    hostname    VARCHAR(64) NOT NULL,
    search_host VARCHAR(64) NOT NULL,
    ip          INET,
    ts          TIMESTAMP   NOT NULL,
    UNIQUE (kline_id, search_nick, search_user, search_host)
);
-- for joining with kline(id)
CREATE INDEX kline_reject_kline_id    ON kline_reject(kline_id);
-- for `!kcheck` searches
CREATE INDEX kline_reject_search_nick ON kline_reject(search_nick);
CREATE INDEX kline_reject_search_user ON kline_reject(search_user);
CREATE INDEX kline_reject_search_host ON kline_reject(search_host);
CREATE INDEX kline_reject_ip          ON kline_reject(ip);

CREATE TABLE kline_tag (
    kline_id    INTEGER      NOT NULL  REFERENCES kline (id)  ON DELETE CASCADE,
    tag         VARCHAR(32)  NOT NULL,
    search_tag  VARCHAR(32)  NOT NULL,
    source      VARCHAR(92)  NOT NULL,
    oper        VARCHAR(16)  NOT NULL,
    ts          TIMESTAMP    NOT NULL,
    PRIMARY KEY (kline_id, search_tag)
);
-- for `!kcheck` searches
CREATE INDEX kline_tag_search_tag ON kline_tag (search_tag);

CREATE TABLE cliconn (
    id          SERIAL PRIMARY KEY,
    nickname    VARCHAR(16) NOT NULL,
    search_nick VARCHAR(16) NOT NULL,
    username    VARCHAR(10) NOT NULL,
    search_user VARCHAR(10) NOT NULL,
    realname    VARCHAR(50) NOT NULL,
    search_real VARCHAR(50) NOT NULL,
    hostname    VARCHAR(64) NOT NULL,
    search_host VARCHAR(64) NOT NULL,
    account     VARCHAR(16),
    search_acc  VARCHAR(16),
    ip          INET,
    server      VARCHAR(92) NOT NULL,
    ts          TIMESTAMP   NOT NULL
);
-- for retention period bulk deletion
CREATE INDEX cliconn_ts          ON cliconn(ts);
-- for `!cliconn` searches
CREATE INDEX cliconn_search_nick ON cliconn(search_nick);
CREATE INDEX cliconn_search_user ON cliconn(search_user);
CREATE INDEX cliconn_search_host ON cliconn(search_host);
CREATE INDEX cliconn_ip          ON cliconn(ip);

CREATE TABLE cliexit (
    id           SERIAL       PRIMARY KEY,
    cliconn_id   INTEGER      REFERENCES cliconn (id),
    nickname     VARCHAR(16)  NOT NULL,
    search_nick  VARCHAR(16)  NOT NULL,
    username     VARCHAR(10)  NOT NULL,
    search_user  VARCHAR(10)  NOT NULL,
    hostname     VARCHAR(64)  NOT NULL,
    search_host  VARCHAR(64)  NOT NULL,
    ip           INET,
    reason       VARCHAR(260) NOT NULL,
    ts           TIMESTAMP    NOT NULL
);
-- for retention period bulk deletion
CREATE INDEX cliexit_ts ON cliexit(ts);

CREATE TABLE nick_change (
    id           SERIAL       PRIMARY KEY,
    cliconn_id   INTEGER      NOT NULL  REFERENCES cliconn (id)  ON DELETE CASCADE,
    nickname     VARCHAR(16)  NOT NULL,
    search_nick  VARCHAR(16)  NOT NULL,
    ts           TIMESTAMP    NOT NULL
);
-- for `!cliconn` searches
CREATE INDEX nick_change_cliconn_id ON nick_change(cliconn_id);
CREATE INDEX nick_change_nickname   ON nick_change(nickname);

CREATE TABLE registration (
    id            SERIAL        PRIMARY KEY,
    nickname      VARCHAR(16)   NOT NULL,
    search_nick   VARCHAR(16)   NOT NULL,
    account       VARCHAR(16)   NOT NULL,
    search_acc    VARCHAR(16)   NOT NULL,
    email         VARCHAR(256)  NOT NULL,
    search_email  VARCHAR(256)  NOT NULL,
    verified_at   TIMESTAMP,
    ts            TIMESTAMP     NOT NULL
);

CREATE TABLE email_resolve (
    id               SERIAL        PRIMARY KEY,
    registration_id  INTEGER       NOT NULL     REFERENCES registration (id)   ON DELETE CASCADE,
    record_parent    INTEGER                    REFERENCES email_resolve (id)  ON DELETE CASCADE,
    record_type      VARCHAR(16)   NOT NULL,
    record           VARCHAR(256)  NOT NULL
);

CREATE TABLE account_freeze (
    id       SERIAL        PRIMARY KEY,
    account  VARCHAR(16)   NOT NULL,
    soper    VARCHAR(16)   NOT NULL,
    reason   VARCHAR(256)  NOT NULL,
    ts       TIMESTAMP     NOT NULL
);

CREATE TABLE freeze_tag (
    freeze_id   INTEGER      NOT NULL  REFERENCES account_freeze (id)  ON DELETE CASCADE,
    tag         VARCHAR(32)  NOT NULL,
    search_tag  VARCHAR(32)  NOT NULL,
    soper       VARCHAR(16)  NOT NULL,
    ts          TIMESTAMP    NOT NULL,
    PRIMARY KEY (freeze_id, search_tag)
);

CREATE TABLE statsp (
    oper VARCHAR(16) NOT NULL,
    mask VARCHAR(92) NOT NULL,
    ts   TIMESTAMP   NOT NULL,
    PRIMARY KEY (mask, ts)
);
CREATE INDEX statsp_ts ON statsp(ts);

CREATE TABLE preference (
    oper   VARCHAR(16)   NOT NULL,
    key    VARCHAR(32)   NOT NULL,
    value  VARCHAR(260)  NOT NULL
);

COMMIT;