helpers/database_postgresql8_tmpl.sql
author chris <chris@marvin>
Wed, 22 Jul 2009 14:04:05 +0200
changeset 3 2fdc9cc89502
parent 1 6fceb66e1ad7
permissions -rw-r--r--
sonews/1.0.0
     1 /*
     2   Create a database at first:
     3     CREATE DATABASE sonews ENCODING 'UTF8';
     4 */
     5 
     6 /* 
     7   flags:
     8   If bit 0 is set, groups is a mirrorred mailing list. 
     9   If not set default newsgroup.
    10 
    11   Normalization: 1NF, 2NF, 3NF
    12 */
    13 CREATE TABLE groups 
    14 (
    15   group_id      SERIAL,
    16   name          VARCHAR(80) NOT NULL,
    17   flags         SMALLINT DEFAULT 0,
    18 
    19   PRIMARY KEY(group_id),
    20   UNIQUE(name)
    21 );
    22 
    23 CREATE TABLE articles 
    24 (
    25   article_id    INT,
    26   body          BYTEA,
    27 
    28   PRIMARY KEY(article_id)
    29 );
    30 
    31 CREATE TABLE article_ids
    32 (
    33   article_id  INT REFERENCES articles(article_id) ON DELETE CASCADE,
    34   message_id  VARCHAR(255),
    35 
    36   PRIMARY KEY(article_id),
    37   UNIQUE(message_id)
    38 );
    39 
    40 CREATE TABLE headers
    41 (
    42   article_id    INT REFERENCES articles(article_id) ON DELETE CASCADE,
    43   header_key    VARCHAR(255),
    44   header_value  TEXT,
    45   header_index  INT,
    46 
    47   PRIMARY KEY(article_id, header_key, header_index)
    48 );
    49 
    50 /*
    51   Normalization: 1NF, 2NF
    52 */
    53 CREATE TABLE postings 
    54 (
    55   group_id      INTEGER,
    56   article_id    INTEGER REFERENCES articles (article_id) ON DELETE CASCADE,
    57   article_index INTEGER NOT NULL, 
    58 
    59   PRIMARY KEY(group_id, article_id)
    60 );
    61 
    62 /* 
    63   Table for association of newsgroups and mailing-lists 
    64 
    65   Normalization: 1NF, 2NF, 3NF
    66 */
    67 CREATE TABLE groups2list
    68 (
    69   group_id   INTEGER REFERENCES groups(group_id) ON DELETE CASCADE,
    70   listaddress VARCHAR(255),
    71 
    72   PRIMARY KEY(group_id, listaddress)
    73 );
    74 
    75 CREATE INDEX listaddress_key ON groups2list USING btree(listaddress);
    76 
    77 /* 
    78   Configuration table, containing key/value pairs 
    79 
    80   Normalization: 1NF, 2NF, 3NF
    81 */
    82 CREATE TABLE config
    83 (
    84   config_key     VARCHAR(255),
    85   config_value   TEXT,
    86 
    87   PRIMARY KEY(config_key)
    88 );
    89 
    90 /* 
    91   Newsserver peers 
    92 
    93   Normalization: 1NF (atomic values), 2NF
    94 */
    95 CREATE TABLE peers
    96 (
    97   peer_id     SERIAL,
    98   host        VARCHAR(255),
    99   port        SMALLINT,
   100 
   101   PRIMARY KEY(peer_id),
   102   UNIQUE(host, port)
   103 );
   104 
   105 /* 
   106   List of newsgroups to feed into sonews 
   107 
   108   Normalization: 1NF, 2NF, 3NF
   109 */
   110 CREATE TABLE peer_subscriptions
   111 (
   112   peer_id    INTEGER REFERENCES peers (peer_id) ON DELETE CASCADE, 
   113   group_id   INTEGER REFERENCES groups (group_id) ON DELETE CASCADE,
   114   feedtype   SMALLINT DEFAULT 0, /* 0: pullfeed; 1: pushfeed */
   115 
   116   PRIMARY KEY(peer_id, group_id, feedtype)
   117 );
   118 
   119 /* 
   120    Tables for server event statistics
   121 
   122    Possible statistic keys:
   123    1=CONNECTIONS     (active connections)
   124    2=POSTED_NEWS     (directly to the server posted unique messages)
   125    3=GATEWAYED_NEWS  (posted unique message gateways through the ML-gateway)
   126    4=FEEDED_NEWS     (unique messages feed via NNTP)
   127 
   128    The server will create snapshots of the above data.
   129 
   130    Normalization: 1NF, 2NF
   131 */
   132 CREATE TABLE events
   133 (
   134   event_time         BIGINT,   /* time of this snapshot */
   135   event_key          SMALLINT,  /* which data */
   136   group_id           INT REFERENCES groups(group_id) ON DELETE CASCADE,
   137 
   138   PRIMARY KEY(event_time, event_key)
   139 );