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




