1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/helpers/database_postgresql8_tmpl.sql Fri Jun 26 16:48:50 2009 +0200
1.3 @@ -0,0 +1,138 @@
1.4 +/*
1.5 + Create a database at first:
1.6 + CREATE DATABASE sonews ENCODING 'UTF8';
1.7 +*/
1.8 +
1.9 +/*
1.10 + flags:
1.11 + If bit 0 is set, groups is a mirrorred mailing list.
1.12 + If not set default newsgroup.
1.13 +
1.14 + Normalization: 1NF, 2NF, 3NF
1.15 +*/
1.16 +CREATE TABLE groups
1.17 +(
1.18 + group_id SERIAL,
1.19 + name VARCHAR(80) NOT NULL,
1.20 + flags SMALLINT DEFAULT 0,
1.21 +
1.22 + PRIMARY KEY(group_id),
1.23 + UNIQUE(name)
1.24 +);
1.25 +
1.26 +CREATE TABLE articles
1.27 +(
1.28 + article_id INT,
1.29 + body BYTEA,
1.30 +
1.31 + PRIMARY KEY(article_id)
1.32 +);
1.33 +
1.34 +CREATE TABLE article_ids
1.35 +(
1.36 + article_id INT REFERENCES articles(article_id) ON DELETE CASCADE,
1.37 + message_id VARCHAR(255),
1.38 +
1.39 + PRIMARY KEY(article_id),
1.40 + UNIQUE(message_id)
1.41 +);
1.42 +
1.43 +CREATE TABLE headers
1.44 +(
1.45 + article_id INT REFERENCES articles(article_id) ON DELETE CASCADE,
1.46 + header_key VARCHAR(255),
1.47 + header_value TEXT,
1.48 + header_index INT,
1.49 +
1.50 + PRIMARY KEY(article_id, header_key, header_index)
1.51 +);
1.52 +
1.53 +/*
1.54 + Normalization: 1NF, 2NF
1.55 +*/
1.56 +CREATE TABLE postings
1.57 +(
1.58 + group_id INTEGER,
1.59 + article_id INTEGER REFERENCES articles (article_id) ON DELETE CASCADE,
1.60 + article_index INTEGER NOT NULL,
1.61 +
1.62 + PRIMARY KEY(group_id, article_id)
1.63 +);
1.64 +
1.65 +/*
1.66 + Table for association of newsgroups and mailing-lists
1.67 +
1.68 + Normalization: 1NF, 2NF, 3NF
1.69 +*/
1.70 +CREATE TABLE groups2list
1.71 +(
1.72 + group_id INTEGER REFERENCES groups(group_id) ON DELETE CASCADE,
1.73 + listaddress VARCHAR(255),
1.74 +
1.75 + PRIMARY KEY(group_id, listaddress),
1.76 + UNIQUE(listaddress)
1.77 +);
1.78 +
1.79 +/*
1.80 + Configuration table, containing key/value pairs
1.81 +
1.82 + Normalization: 1NF, 2NF, 3NF
1.83 +*/
1.84 +CREATE TABLE config
1.85 +(
1.86 + config_key VARCHAR(255),
1.87 + config_value TEXT,
1.88 +
1.89 + PRIMARY KEY(config_key)
1.90 +);
1.91 +
1.92 +/*
1.93 + Newsserver peers
1.94 +
1.95 + Normalization: 1NF (atomic values), 2NF
1.96 +*/
1.97 +CREATE TABLE peers
1.98 +(
1.99 + peer_id SERIAL,
1.100 + host VARCHAR(255),
1.101 + port SMALLINT,
1.102 +
1.103 + PRIMARY KEY(peer_id),
1.104 + UNIQUE(host, port)
1.105 +);
1.106 +
1.107 +/*
1.108 + List of newsgroups to feed into sonews
1.109 +
1.110 + Normalization: 1NF, 2NF, 3NF
1.111 +*/
1.112 +CREATE TABLE peer_subscriptions
1.113 +(
1.114 + peer_id INTEGER REFERENCES peers (peer_id) ON DELETE CASCADE,
1.115 + group_id INTEGER REFERENCES groups (group_id) ON DELETE CASCADE,
1.116 + feedtype SMALLINT DEFAULT 0, /* 0: pullfeed; 1: pushfeed */
1.117 +
1.118 + PRIMARY KEY(peer_id, group_id, feedtype)
1.119 +);
1.120 +
1.121 +/*
1.122 + Tables for server event statistics
1.123 +
1.124 + Possible statistic keys:
1.125 + 1=CONNECTIONS (active connections)
1.126 + 2=POSTED_NEWS (directly to the server posted unique messages)
1.127 + 3=GATEWAYED_NEWS (posted unique message gateways through the ML-gateway)
1.128 + 4=FEEDED_NEWS (unique messages feed via NNTP)
1.129 +
1.130 + The server will create snapshots of the above data.
1.131 +
1.132 + Normalization: 1NF, 2NF
1.133 +*/
1.134 +CREATE TABLE events
1.135 +(
1.136 + event_time BIGINT, /* time of this snapshot */
1.137 + event_key SMALLINT, /* which data */
1.138 + group_id INT REFERENCES groups(group_id) ON DELETE CASCADE,
1.139 +
1.140 + PRIMARY KEY(event_time, event_key)
1.141 +);