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