chris@1
|
1 |
/*
|
chris@1
|
2 |
Create a database at first:
|
chris@1
|
3 |
CREATE DATABASE sonews CHARACTER SET utf8
|
chris@1
|
4 |
*/
|
chris@1
|
5 |
|
chris@1
|
6 |
/*
|
chris@1
|
7 |
flags:
|
chris@1
|
8 |
If bit 0 is set, groups is a mirrorred mailing list.
|
chris@1
|
9 |
If not set default newsgroup.
|
chris@1
|
10 |
|
chris@1
|
11 |
Normalization: 1NF, 2NF, 3NF
|
chris@1
|
12 |
*/
|
chris@1
|
13 |
CREATE TABLE groups
|
chris@1
|
14 |
(
|
chris@1
|
15 |
group_id SERIAL,
|
chris@1
|
16 |
name VARCHAR(80) NOT NULL,
|
chris@1
|
17 |
flags TINYINT UNSIGNED DEFAULT 0,
|
chris@1
|
18 |
|
chris@1
|
19 |
PRIMARY KEY(group_id),
|
chris@1
|
20 |
UNIQUE(name)
|
chris@1
|
21 |
)
|
chris@1
|
22 |
ENGINE = INNODB
|
chris@1
|
23 |
CHARACTER SET utf8;
|
chris@1
|
24 |
|
chris@1
|
25 |
CREATE TABLE articles
|
chris@1
|
26 |
(
|
chris@1
|
27 |
article_id INT,
|
chris@1
|
28 |
body LONGBLOB,
|
chris@1
|
29 |
|
chris@1
|
30 |
PRIMARY KEY(article_id)
|
chris@1
|
31 |
)
|
chris@1
|
32 |
ENGINE = INNODB
|
chris@1
|
33 |
CHARACTER SET utf8;
|
chris@1
|
34 |
|
chris@1
|
35 |
CREATE TABLE article_ids
|
chris@1
|
36 |
(
|
chris@1
|
37 |
article_id INT REFERENCES articles.article_id ON DELETE CASCADE,
|
chris@1
|
38 |
message_id VARCHAR(255),
|
chris@1
|
39 |
|
chris@1
|
40 |
PRIMARY KEY(article_id),
|
chris@1
|
41 |
UNIQUE(message_id)
|
chris@1
|
42 |
)
|
chris@1
|
43 |
ENGINE = INNODB
|
chris@1
|
44 |
CHARACTER SET utf8;
|
chris@1
|
45 |
|
chris@1
|
46 |
CREATE TABLE headers
|
chris@1
|
47 |
(
|
chris@1
|
48 |
article_id INT REFERENCES articles.article_id ON DELETE CASCADE,
|
chris@1
|
49 |
header_key VARCHAR(255),
|
chris@1
|
50 |
header_value TEXT, /* Max. 64k */
|
chris@1
|
51 |
header_index INT,
|
chris@1
|
52 |
|
chris@1
|
53 |
PRIMARY KEY(article_id, header_key, header_index)
|
chris@1
|
54 |
)
|
chris@1
|
55 |
ENGINE = INNODB
|
chris@1
|
56 |
CHARACTER SET utf8;
|
chris@1
|
57 |
|
chris@1
|
58 |
/*
|
chris@1
|
59 |
Normalization: 1NF, 2NF
|
chris@1
|
60 |
*/
|
chris@1
|
61 |
CREATE TABLE postings
|
chris@1
|
62 |
(
|
chris@1
|
63 |
group_id INTEGER,
|
chris@1
|
64 |
article_id INTEGER REFERENCES articles.article_id ON DELETE CASCADE,
|
chris@1
|
65 |
article_index INTEGER NOT NULL,
|
chris@1
|
66 |
|
chris@1
|
67 |
PRIMARY KEY(group_id, article_id)
|
chris@1
|
68 |
)
|
chris@1
|
69 |
ENGINE = INNODB
|
chris@1
|
70 |
CHARACTER SET utf8;
|
chris@1
|
71 |
|
chris@1
|
72 |
/*
|
chris@1
|
73 |
Table for association of newsgroups and mailing-lists
|
chris@1
|
74 |
|
chris@1
|
75 |
Normalization: 1NF, 2NF, 3NF
|
chris@1
|
76 |
*/
|
chris@1
|
77 |
CREATE TABLE groups2list
|
chris@1
|
78 |
(
|
chris@1
|
79 |
group_id INTEGER REFERENCES groups.group_id ON DELETE CASCADE,
|
chris@1
|
80 |
listaddress VARCHAR(255),
|
chris@1
|
81 |
|
chris@1
|
82 |
PRIMARY KEY(group_id, listaddress),
|
chris@1
|
83 |
UNIQUE(listaddress)
|
chris@1
|
84 |
)
|
chris@1
|
85 |
ENGINE = INNODB
|
chris@1
|
86 |
CHARACTER SET utf8;
|
chris@1
|
87 |
|
chris@1
|
88 |
/*
|
chris@1
|
89 |
Configuration table, containing key/value pairs
|
chris@1
|
90 |
|
chris@1
|
91 |
Normalization: 1NF, 2NF, 3NF
|
chris@1
|
92 |
*/
|
chris@1
|
93 |
CREATE TABLE config
|
chris@1
|
94 |
(
|
chris@1
|
95 |
config_key VARCHAR(255),
|
chris@1
|
96 |
config_value TEXT,
|
chris@1
|
97 |
|
chris@1
|
98 |
PRIMARY KEY(config_key)
|
chris@1
|
99 |
)
|
chris@1
|
100 |
ENGINE = INNODB
|
chris@1
|
101 |
CHARACTER SET utf8;
|
chris@1
|
102 |
|
chris@1
|
103 |
/*
|
chris@1
|
104 |
Newsserver peers
|
chris@1
|
105 |
feedtype: 0: pullfeed 1: pushfeed
|
chris@1
|
106 |
Normalization: 1NF (atomic values), 2NF
|
chris@1
|
107 |
*/
|
chris@1
|
108 |
CREATE TABLE peers
|
chris@1
|
109 |
(
|
chris@1
|
110 |
peer_id SERIAL,
|
chris@1
|
111 |
host VARCHAR(255),
|
chris@1
|
112 |
port SMALLINT UNSIGNED,
|
chris@1
|
113 |
|
chris@1
|
114 |
PRIMARY KEY(peer_id),
|
chris@1
|
115 |
UNIQUE(host, port)
|
chris@1
|
116 |
)
|
chris@1
|
117 |
ENGINE = INNODB
|
chris@1
|
118 |
CHARACTER SET utf8;
|
chris@1
|
119 |
|
chris@1
|
120 |
/*
|
chris@1
|
121 |
List of newsgroups to feed into sonews
|
chris@1
|
122 |
|
chris@1
|
123 |
Normalization: 1NF, 2NF, 3NF
|
chris@1
|
124 |
*/
|
chris@1
|
125 |
CREATE TABLE peer_subscriptions
|
chris@1
|
126 |
(
|
chris@1
|
127 |
peer_id INTEGER REFERENCES peers.peer_id ON DELETE CASCADE,
|
chris@1
|
128 |
group_id INTEGER REFERENCES groups.group_id ON DELETE CASCADE,
|
chris@1
|
129 |
feedtype TINYINT UNSIGNED DEFAULT 0,
|
chris@1
|
130 |
|
chris@1
|
131 |
PRIMARY KEY(peer_id, group_id, feedtype)
|
chris@1
|
132 |
)
|
chris@1
|
133 |
ENGINE = INNODB
|
chris@1
|
134 |
CHARACTER SET utf8;
|
chris@1
|
135 |
|
chris@1
|
136 |
/*
|
chris@1
|
137 |
Tables for server event statistics
|
chris@1
|
138 |
|
chris@1
|
139 |
Possible statistic keys:
|
chris@1
|
140 |
1=CONNECTIONS (active connections)
|
chris@1
|
141 |
2=POSTED_NEWS (directly to the server posted unique messages)
|
chris@1
|
142 |
3=GATEWAYED_NEWS (posted unique message gateways through the ML-gateway)
|
chris@1
|
143 |
4=FEEDED_NEWS (unique messages feed via NNTP)
|
chris@1
|
144 |
|
chris@1
|
145 |
The server will create snapshots of the above data.
|
chris@1
|
146 |
|
chris@1
|
147 |
Normalization: 1NF, 2NF
|
chris@1
|
148 |
*/
|
chris@1
|
149 |
CREATE TABLE events
|
chris@1
|
150 |
(
|
chris@1
|
151 |
event_time BIGINT UNSIGNED, /* time of this snapshot */
|
chris@1
|
152 |
event_key TINYINT UNSIGNED, /* which data */
|
chris@1
|
153 |
group_id INT REFERENCES groups.group_id ON DELETE CASCADE,
|
chris@1
|
154 |
|
chris@1
|
155 |
PRIMARY KEY(event_time, event_key)
|
chris@1
|
156 |
)
|
chris@1
|
157 |
ENGINE = INNODB
|
chris@1
|
158 |
CHARACTER SET utf8;
|