1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/helpers/database_mysql5_tmpl.sql Wed Aug 12 13:03:23 2009 +0200
1.3 @@ -0,0 +1,158 @@
1.4 +/*
1.5 + Create a database at first:
1.6 + CREATE DATABASE sonews CHARACTER SET 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 TINYINT UNSIGNED DEFAULT 0,
1.21 +
1.22 + PRIMARY KEY(group_id),
1.23 + UNIQUE(name)
1.24 +)
1.25 +ENGINE = INNODB
1.26 +CHARACTER SET utf8;
1.27 +
1.28 +CREATE TABLE articles
1.29 +(
1.30 + article_id INT,
1.31 + body LONGBLOB,
1.32 +
1.33 + PRIMARY KEY(article_id)
1.34 +)
1.35 +ENGINE = INNODB
1.36 +CHARACTER SET utf8;
1.37 +
1.38 +CREATE TABLE article_ids
1.39 +(
1.40 + article_id INT REFERENCES articles.article_id ON DELETE CASCADE,
1.41 + message_id VARCHAR(255),
1.42 +
1.43 + PRIMARY KEY(article_id),
1.44 + UNIQUE(message_id)
1.45 +)
1.46 +ENGINE = INNODB
1.47 +CHARACTER SET utf8;
1.48 +
1.49 +CREATE TABLE headers
1.50 +(
1.51 + article_id INT REFERENCES articles.article_id ON DELETE CASCADE,
1.52 + header_key VARCHAR(255),
1.53 + header_value TEXT, /* Max. 64k */
1.54 + header_index INT,
1.55 +
1.56 + PRIMARY KEY(article_id, header_key, header_index)
1.57 +)
1.58 +ENGINE = INNODB
1.59 +CHARACTER SET utf8;
1.60 +
1.61 +/*
1.62 + Normalization: 1NF, 2NF
1.63 +*/
1.64 +CREATE TABLE postings
1.65 +(
1.66 + group_id INTEGER,
1.67 + article_id INTEGER REFERENCES articles.article_id ON DELETE CASCADE,
1.68 + article_index INTEGER NOT NULL,
1.69 +
1.70 + PRIMARY KEY(group_id, article_id)
1.71 +)
1.72 +ENGINE = INNODB
1.73 +CHARACTER SET utf8;
1.74 +
1.75 +/*
1.76 + Table for association of newsgroups and mailing-lists
1.77 +
1.78 + Normalization: 1NF, 2NF, 3NF
1.79 +*/
1.80 +CREATE TABLE groups2list
1.81 +(
1.82 + group_id INTEGER REFERENCES groups.group_id ON DELETE CASCADE,
1.83 + listaddress VARCHAR(255),
1.84 +
1.85 + PRIMARY KEY(group_id, listaddress),
1.86 + UNIQUE(listaddress)
1.87 +)
1.88 +ENGINE = INNODB
1.89 +CHARACTER SET utf8;
1.90 +
1.91 +/*
1.92 + Configuration table, containing key/value pairs
1.93 +
1.94 + Normalization: 1NF, 2NF, 3NF
1.95 +*/
1.96 +CREATE TABLE config
1.97 +(
1.98 + config_key VARCHAR(255),
1.99 + config_value TEXT,
1.100 +
1.101 + PRIMARY KEY(config_key)
1.102 +)
1.103 +ENGINE = INNODB
1.104 +CHARACTER SET utf8;
1.105 +
1.106 +/*
1.107 + Newsserver peers
1.108 + feedtype: 0: pullfeed 1: pushfeed
1.109 + Normalization: 1NF (atomic values), 2NF
1.110 +*/
1.111 +CREATE TABLE peers
1.112 +(
1.113 + peer_id SERIAL,
1.114 + host VARCHAR(255),
1.115 + port SMALLINT UNSIGNED,
1.116 +
1.117 + PRIMARY KEY(peer_id),
1.118 + UNIQUE(host, port)
1.119 +)
1.120 +ENGINE = INNODB
1.121 +CHARACTER SET utf8;
1.122 +
1.123 +/*
1.124 + List of newsgroups to feed into sonews
1.125 +
1.126 + Normalization: 1NF, 2NF, 3NF
1.127 +*/
1.128 +CREATE TABLE peer_subscriptions
1.129 +(
1.130 + peer_id INTEGER REFERENCES peers.peer_id ON DELETE CASCADE,
1.131 + group_id INTEGER REFERENCES groups.group_id ON DELETE CASCADE,
1.132 + feedtype TINYINT UNSIGNED DEFAULT 0,
1.133 +
1.134 + PRIMARY KEY(peer_id, group_id, feedtype)
1.135 +)
1.136 +ENGINE = INNODB
1.137 +CHARACTER SET utf8;
1.138 +
1.139 +/*
1.140 + Tables for server event statistics
1.141 +
1.142 + Possible statistic keys:
1.143 + 1=CONNECTIONS (active connections)
1.144 + 2=POSTED_NEWS (directly to the server posted unique messages)
1.145 + 3=GATEWAYED_NEWS (posted unique message gateways through the ML-gateway)
1.146 + 4=FEEDED_NEWS (unique messages feed via NNTP)
1.147 +
1.148 + The server will create snapshots of the above data.
1.149 +
1.150 + Normalization: 1NF, 2NF
1.151 +*/
1.152 +CREATE TABLE events
1.153 +(
1.154 + event_time BIGINT UNSIGNED, /* time of this snapshot */
1.155 + event_key TINYINT UNSIGNED, /* which data */
1.156 + group_id INT REFERENCES groups.group_id ON DELETE CASCADE,
1.157 +
1.158 + PRIMARY KEY(event_time, event_key)
1.159 +)
1.160 +ENGINE = INNODB
1.161 +CHARACTER SET utf8;