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