1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/helpers/database_postgresql8_tmpl.sql	Wed Jul 01 10:48:22 2009 +0200
     1.3 @@ -0,0 +1,138 @@
     1.4 +/*
     1.5 +  Create a database at first:
     1.6 +    CREATE DATABASE sonews ENCODING 'UTF8';
     1.7 +*/
     1.8 +
     1.9 +/* 
    1.10 +  flags:
    1.11 +  If bit 0 is set, groups is a mirrorred mailing list. 
    1.12 +  If not set default newsgroup.
    1.13 +
    1.14 +  Normalization: 1NF, 2NF, 3NF
    1.15 +*/
    1.16 +CREATE TABLE groups 
    1.17 +(
    1.18 +  group_id      SERIAL,
    1.19 +  name          VARCHAR(80) NOT NULL,
    1.20 +  flags         SMALLINT DEFAULT 0,
    1.21 +
    1.22 +  PRIMARY KEY(group_id),
    1.23 +  UNIQUE(name)
    1.24 +);
    1.25 +
    1.26 +CREATE TABLE articles 
    1.27 +(
    1.28 +  article_id    INT,
    1.29 +  body          BYTEA,
    1.30 +
    1.31 +  PRIMARY KEY(article_id)
    1.32 +);
    1.33 +
    1.34 +CREATE TABLE article_ids
    1.35 +(
    1.36 +  article_id  INT REFERENCES articles(article_id) ON DELETE CASCADE,
    1.37 +  message_id  VARCHAR(255),
    1.38 +
    1.39 +  PRIMARY KEY(article_id),
    1.40 +  UNIQUE(message_id)
    1.41 +);
    1.42 +
    1.43 +CREATE TABLE headers
    1.44 +(
    1.45 +  article_id    INT REFERENCES articles(article_id) ON DELETE CASCADE,
    1.46 +  header_key    VARCHAR(255),
    1.47 +  header_value  TEXT,
    1.48 +  header_index  INT,
    1.49 +
    1.50 +  PRIMARY KEY(article_id, header_key, header_index)
    1.51 +);
    1.52 +
    1.53 +/*
    1.54 +  Normalization: 1NF, 2NF
    1.55 +*/
    1.56 +CREATE TABLE postings 
    1.57 +(
    1.58 +  group_id      INTEGER,
    1.59 +  article_id    INTEGER REFERENCES articles (article_id) ON DELETE CASCADE,
    1.60 +  article_index INTEGER NOT NULL, 
    1.61 +
    1.62 +  PRIMARY KEY(group_id, article_id)
    1.63 +);
    1.64 +
    1.65 +/* 
    1.66 +  Table for association of newsgroups and mailing-lists 
    1.67 +
    1.68 +  Normalization: 1NF, 2NF, 3NF
    1.69 +*/
    1.70 +CREATE TABLE groups2list
    1.71 +(
    1.72 +  group_id   INTEGER REFERENCES groups(group_id) ON DELETE CASCADE,
    1.73 +  listaddress VARCHAR(255),
    1.74 +
    1.75 +  PRIMARY KEY(group_id, listaddress),
    1.76 +  UNIQUE(listaddress)
    1.77 +);
    1.78 +
    1.79 +/* 
    1.80 +  Configuration table, containing key/value pairs 
    1.81 +
    1.82 +  Normalization: 1NF, 2NF, 3NF
    1.83 +*/
    1.84 +CREATE TABLE config
    1.85 +(
    1.86 +  config_key     VARCHAR(255),
    1.87 +  config_value   TEXT,
    1.88 +
    1.89 +  PRIMARY KEY(config_key)
    1.90 +);
    1.91 +
    1.92 +/* 
    1.93 +  Newsserver peers 
    1.94 +
    1.95 +  Normalization: 1NF (atomic values), 2NF
    1.96 +*/
    1.97 +CREATE TABLE peers
    1.98 +(
    1.99 +  peer_id     SERIAL,
   1.100 +  host        VARCHAR(255),
   1.101 +  port        SMALLINT,
   1.102 +
   1.103 +  PRIMARY KEY(peer_id),
   1.104 +  UNIQUE(host, port)
   1.105 +);
   1.106 +
   1.107 +/* 
   1.108 +  List of newsgroups to feed into sonews 
   1.109 +
   1.110 +  Normalization: 1NF, 2NF, 3NF
   1.111 +*/
   1.112 +CREATE TABLE peer_subscriptions
   1.113 +(
   1.114 +  peer_id    INTEGER REFERENCES peers (peer_id) ON DELETE CASCADE, 
   1.115 +  group_id   INTEGER REFERENCES groups (group_id) ON DELETE CASCADE,
   1.116 +  feedtype   SMALLINT DEFAULT 0, /* 0: pullfeed; 1: pushfeed */
   1.117 +
   1.118 +  PRIMARY KEY(peer_id, group_id, feedtype)
   1.119 +);
   1.120 +
   1.121 +/* 
   1.122 +   Tables for server event statistics
   1.123 +
   1.124 +   Possible statistic keys:
   1.125 +   1=CONNECTIONS     (active connections)
   1.126 +   2=POSTED_NEWS     (directly to the server posted unique messages)
   1.127 +   3=GATEWAYED_NEWS  (posted unique message gateways through the ML-gateway)
   1.128 +   4=FEEDED_NEWS     (unique messages feed via NNTP)
   1.129 +
   1.130 +   The server will create snapshots of the above data.
   1.131 +
   1.132 +   Normalization: 1NF, 2NF
   1.133 +*/
   1.134 +CREATE TABLE events
   1.135 +(
   1.136 +  event_time         BIGINT,   /* time of this snapshot */
   1.137 +  event_key          SMALLINT,  /* which data */
   1.138 +  group_id           INT REFERENCES groups(group_id) ON DELETE CASCADE,
   1.139 +
   1.140 +  PRIMARY KEY(event_time, event_key)
   1.141 +);