2 Create a database at first:
3 CREATE DATABASE sonews CHARACTER SET utf8
8 If bit 0 is set, groups is a mirrorred mailing list.
9 If not set default newsgroup.
11 Normalization: 1NF, 2NF, 3NF
16 name VARCHAR(80) NOT NULL,
17 flags TINYINT UNSIGNED DEFAULT 0,
19 PRIMARY KEY(group_id),
30 PRIMARY KEY(article_id)
35 CREATE TABLE article_ids
37 article_id INT REFERENCES articles.article_id ON DELETE CASCADE,
38 message_id VARCHAR(255),
40 PRIMARY KEY(article_id),
48 article_id INT REFERENCES articles.article_id ON DELETE CASCADE,
49 header_key VARCHAR(255),
50 header_value TEXT, /* Max. 64k */
53 PRIMARY KEY(article_id, header_key, header_index)
59 Normalization: 1NF, 2NF
64 article_id INTEGER REFERENCES articles.article_id ON DELETE CASCADE,
65 article_index INTEGER NOT NULL,
67 PRIMARY KEY(group_id, article_id)
73 Table for association of newsgroups and mailing-lists
75 Normalization: 1NF, 2NF, 3NF
77 CREATE TABLE groups2list
79 group_id INTEGER REFERENCES groups.group_id ON DELETE CASCADE,
80 listaddress VARCHAR(255),
82 PRIMARY KEY(group_id, listaddress),
89 Configuration table, containing key/value pairs
91 Normalization: 1NF, 2NF, 3NF
95 config_key VARCHAR(255),
98 PRIMARY KEY(config_key)
105 feedtype: 0: pullfeed 1: pushfeed
106 Normalization: 1NF (atomic values), 2NF
112 port SMALLINT UNSIGNED,
114 PRIMARY KEY(peer_id),
121 List of newsgroups to feed into sonews
123 Normalization: 1NF, 2NF, 3NF
125 CREATE TABLE peer_subscriptions
127 peer_id INTEGER REFERENCES peers.peer_id ON DELETE CASCADE,
128 group_id INTEGER REFERENCES groups.group_id ON DELETE CASCADE,
129 feedtype TINYINT UNSIGNED DEFAULT 0,
131 PRIMARY KEY(peer_id, group_id, feedtype)
137 Tables for server event statistics
139 Possible statistic keys:
140 1=CONNECTIONS (active connections)
141 2=POSTED_NEWS (directly to the server posted unique messages)
142 3=GATEWAYED_NEWS (posted unique message gateways through the ML-gateway)
143 4=FEEDED_NEWS (unique messages feed via NNTP)
145 The server will create snapshots of the above data.
147 Normalization: 1NF, 2NF
151 event_time BIGINT UNSIGNED, /* time of this snapshot */
152 event_key TINYINT UNSIGNED, /* which data */
153 group_id INT REFERENCES groups.group_id ON DELETE CASCADE,
155 PRIMARY KEY(event_time, event_key)