/* 

   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;