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