diff -r 000000000000 -r 1090e2141798 helpers/database_mysql5_tmpl.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/helpers/database_mysql5_tmpl.sql	Wed Jul 01 10:48:22 2009 +0200
@@ -0,0 +1,158 @@
+/* 
+  Create a database at first:
+    CREATE DATABASE sonews CHARACTER SET utf8
+*/
+
+/* 
+  flags:
+  If bit 0 is set, groups is a mirrorred mailing list. 
+  If not set default newsgroup.
+
+  Normalization: 1NF, 2NF, 3NF
+*/
+CREATE TABLE groups 
+(
+  group_id      SERIAL,
+  name          VARCHAR(80) NOT NULL,
+  flags         TINYINT UNSIGNED DEFAULT 0,
+
+  PRIMARY KEY(group_id),
+  UNIQUE(name)
+)
+ENGINE = INNODB
+CHARACTER SET utf8;
+
+CREATE TABLE articles 
+(
+  article_id    INT,
+  body          LONGBLOB,
+
+  PRIMARY KEY(article_id)
+)
+ENGINE = INNODB
+CHARACTER SET utf8;
+
+CREATE TABLE article_ids
+(
+  article_id  INT REFERENCES articles.article_id ON DELETE CASCADE,
+  message_id  VARCHAR(255),
+
+  PRIMARY KEY(article_id),
+  UNIQUE(message_id)
+)
+ENGINE = INNODB
+CHARACTER SET utf8;
+
+CREATE TABLE headers
+(
+  article_id    INT REFERENCES articles.article_id ON DELETE CASCADE,
+  header_key    VARCHAR(255),
+  header_value  TEXT, /* Max. 64k */
+  header_index  INT,
+
+  PRIMARY KEY(article_id, header_key, header_index)
+)
+ENGINE = INNODB
+CHARACTER SET utf8;
+
+/*
+  Normalization: 1NF, 2NF
+*/
+CREATE TABLE postings 
+(
+  group_id      INTEGER,
+  article_id    INTEGER REFERENCES articles.article_id ON DELETE CASCADE,
+  article_index INTEGER NOT NULL, 
+
+  PRIMARY KEY(group_id, article_id)
+)
+ENGINE = INNODB
+CHARACTER SET utf8;
+
+/* 
+  Table for association of newsgroups and mailing-lists 
+
+  Normalization: 1NF, 2NF, 3NF
+*/
+CREATE TABLE groups2list
+(
+  group_id    INTEGER REFERENCES groups.group_id ON DELETE CASCADE,
+  listaddress VARCHAR(255),
+
+  PRIMARY KEY(group_id, listaddress),
+  UNIQUE(listaddress)
+)
+ENGINE = INNODB
+CHARACTER SET utf8;
+
+/* 
+  Configuration table, containing key/value pairs 
+
+  Normalization: 1NF, 2NF, 3NF
+*/
+CREATE TABLE config
+(
+  config_key     VARCHAR(255),
+  config_value   TEXT,
+
+  PRIMARY KEY(config_key)
+)
+ENGINE = INNODB
+CHARACTER SET utf8;
+
+/* 
+  Newsserver peers 
+  feedtype: 0: pullfeed 1: pushfeed
+  Normalization: 1NF (atomic values), 2NF
+*/
+CREATE TABLE peers
+(
+  peer_id     SERIAL,
+  host        VARCHAR(255),
+  port        SMALLINT UNSIGNED,
+
+  PRIMARY KEY(peer_id),
+  UNIQUE(host, port)
+)
+ENGINE = INNODB
+CHARACTER SET utf8;
+
+/* 
+  List of newsgroups to feed into sonews 
+
+  Normalization: 1NF, 2NF, 3NF
+*/
+CREATE TABLE peer_subscriptions
+(
+  peer_id    INTEGER REFERENCES peers.peer_id ON DELETE CASCADE,
+  group_id   INTEGER REFERENCES groups.group_id ON DELETE CASCADE,
+  feedtype   TINYINT UNSIGNED DEFAULT 0,
+
+  PRIMARY KEY(peer_id, group_id, feedtype)
+)
+ENGINE = INNODB
+CHARACTER SET utf8;
+
+/* 
+   Tables for server event statistics
+
+   Possible statistic keys:
+   1=CONNECTIONS     (active connections)
+   2=POSTED_NEWS     (directly to the server posted unique messages)
+   3=GATEWAYED_NEWS  (posted unique message gateways through the ML-gateway)
+   4=FEEDED_NEWS     (unique messages feed via NNTP)
+
+   The server will create snapshots of the above data.
+
+   Normalization: 1NF, 2NF
+*/
+CREATE TABLE events
+(
+  event_time         BIGINT UNSIGNED,   /* time of this snapshot */
+  event_key          TINYINT UNSIGNED,  /* which data */
+  group_id           INT REFERENCES groups.group_id ON DELETE CASCADE,
+
+  PRIMARY KEY(event_time, event_key)
+)
+ENGINE = INNODB
+CHARACTER SET utf8;