HSQLDB backend support completed, but untested.
3 If bit 0 is set, groups is a mirrorred mailing list.
4 If not set default newsgroup.
6 Normalization: 1NF, 2NF, 3NF
8 CREATE CACHED TABLE groups
11 name VARCHAR(80) NOT NULL,
12 flags TINYINT DEFAULT 0,
14 PRIMARY KEY(group_id),
18 CREATE CACHED TABLE articles
23 PRIMARY KEY(article_id)
26 CREATE CACHED TABLE article_ids
29 message_id VARCHAR(255),
31 PRIMARY KEY(article_id),
33 FOREIGN KEY(article_id) REFERENCES articles(article_id) ON DELETE CASCADE
36 CREATE CACHED TABLE headers
39 header_key VARCHAR(255),
40 header_value LONGVARCHAR,
43 PRIMARY KEY(article_id, header_key, header_index),
44 FOREIGN KEY(article_id) REFERENCES articles(article_id) ON DELETE CASCADE
48 Normalization: 1NF, 2NF
50 CREATE CACHED TABLE postings
54 article_index INTEGER NOT NULL,
56 PRIMARY KEY(group_id, article_id),
57 FOREIGN KEY(article_id) REFERENCES articles(article_id) ON DELETE CASCADE
61 Table for association of newsgroups and mailing-lists
63 Normalization: 1NF, 2NF, 3NF
65 CREATE CACHED TABLE groups2list
68 listaddress VARCHAR(255),
70 PRIMARY KEY(group_id, listaddress),
72 FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE
76 Configuration table, containing key/value pairs
78 Normalization: 1NF, 2NF, 3NF
80 CREATE CACHED TABLE config
82 config_key VARCHAR(255),
83 config_value LONGVARCHAR,
85 PRIMARY KEY(config_key)
90 feedtype: 0: pullfeed 1: pushfeed
91 Normalization: 1NF (atomic values), 2NF
93 CREATE CACHED TABLE peers
104 List of newsgroups to feed into sonews
106 Normalization: 1NF, 2NF, 3NF
108 CREATE CACHED TABLE peer_subscriptions
112 feedtype TINYINT DEFAULT 0,
114 PRIMARY KEY(peer_id, group_id, feedtype),
115 FOREIGN KEY(peer_id) REFERENCES peers(peer_id) ON DELETE CASCADE,
116 FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE
120 Tables for server event statistics
122 Possible statistic keys:
123 1=CONNECTIONS (active connections)
124 2=POSTED_NEWS (directly to the server posted unique messages)
125 3=GATEWAYED_NEWS (posted unique message gateways through the ML-gateway)
126 4=FEEDED_NEWS (unique messages feed via NNTP)
128 The server will create snapshots of the above data.
130 Normalization: 1NF, 2NF
132 CREATE CACHED TABLE events
134 event_time BIGINT, /* time of this snapshot */
135 event_key TINYINT, /* which data */
138 PRIMARY KEY(event_time, event_key),
139 FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE