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