helpers/database_mysql5_tmpl.sql
author František Kučera <franta-hg@frantovo.cz>
Wed Dec 31 12:07:40 2014 +0100 (2014-12-31)
changeset 120 bb1c8a7b774c
permissions -rwxr-xr-x
XSLT: licence – GNU GPLv3
chris@1
     1
/* 
chris@1
     2
  Create a database at first:
chris@1
     3
    CREATE DATABASE sonews CHARACTER SET 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         TINYINT UNSIGNED DEFAULT 0,
chris@1
    18
chris@1
    19
  PRIMARY KEY(group_id),
chris@1
    20
  UNIQUE(name)
chris@1
    21
)
chris@1
    22
ENGINE = INNODB
chris@1
    23
CHARACTER SET utf8;
chris@1
    24
chris@1
    25
CREATE TABLE articles 
chris@1
    26
(
chris@1
    27
  article_id    INT,
chris@1
    28
  body          LONGBLOB,
chris@1
    29
chris@1
    30
  PRIMARY KEY(article_id)
chris@1
    31
)
chris@1
    32
ENGINE = INNODB
chris@1
    33
CHARACTER SET utf8;
chris@1
    34
chris@1
    35
CREATE TABLE article_ids
chris@1
    36
(
chris@1
    37
  article_id  INT REFERENCES articles.article_id ON DELETE CASCADE,
chris@1
    38
  message_id  VARCHAR(255),
chris@1
    39
chris@1
    40
  PRIMARY KEY(article_id),
chris@1
    41
  UNIQUE(message_id)
chris@1
    42
)
chris@1
    43
ENGINE = INNODB
chris@1
    44
CHARACTER SET utf8;
chris@1
    45
chris@1
    46
CREATE TABLE headers
chris@1
    47
(
chris@1
    48
  article_id    INT REFERENCES articles.article_id ON DELETE CASCADE,
chris@1
    49
  header_key    VARCHAR(255),
chris@1
    50
  header_value  TEXT, /* Max. 64k */
chris@1
    51
  header_index  INT,
chris@1
    52
chris@1
    53
  PRIMARY KEY(article_id, header_key, header_index)
chris@1
    54
)
chris@1
    55
ENGINE = INNODB
chris@1
    56
CHARACTER SET utf8;
chris@1
    57
chris@1
    58
/*
chris@1
    59
  Normalization: 1NF, 2NF
chris@1
    60
*/
chris@1
    61
CREATE TABLE postings 
chris@1
    62
(
chris@1
    63
  group_id      INTEGER,
chris@1
    64
  article_id    INTEGER REFERENCES articles.article_id ON DELETE CASCADE,
chris@1
    65
  article_index INTEGER NOT NULL, 
chris@1
    66
chris@1
    67
  PRIMARY KEY(group_id, article_id)
chris@1
    68
)
chris@1
    69
ENGINE = INNODB
chris@1
    70
CHARACTER SET utf8;
chris@1
    71
chris@1
    72
/* 
chris@1
    73
  Table for association of newsgroups and mailing-lists 
chris@1
    74
chris@1
    75
  Normalization: 1NF, 2NF, 3NF
chris@1
    76
*/
chris@1
    77
CREATE TABLE groups2list
chris@1
    78
(
chris@1
    79
  group_id    INTEGER REFERENCES groups.group_id ON DELETE CASCADE,
chris@1
    80
  listaddress VARCHAR(255),
chris@1
    81
chris@1
    82
  PRIMARY KEY(group_id, listaddress),
chris@1
    83
  UNIQUE(listaddress)
chris@1
    84
)
chris@1
    85
ENGINE = INNODB
chris@1
    86
CHARACTER SET utf8;
chris@1
    87
chris@1
    88
/* 
chris@1
    89
  Configuration table, containing key/value pairs 
chris@1
    90
chris@1
    91
  Normalization: 1NF, 2NF, 3NF
chris@1
    92
*/
chris@1
    93
CREATE TABLE config
chris@1
    94
(
chris@1
    95
  config_key     VARCHAR(255),
chris@1
    96
  config_value   TEXT,
chris@1
    97
chris@1
    98
  PRIMARY KEY(config_key)
chris@1
    99
)
chris@1
   100
ENGINE = INNODB
chris@1
   101
CHARACTER SET utf8;
chris@1
   102
chris@1
   103
/* 
chris@1
   104
  Newsserver peers 
chris@1
   105
  feedtype: 0: pullfeed 1: pushfeed
chris@1
   106
  Normalization: 1NF (atomic values), 2NF
chris@1
   107
*/
chris@1
   108
CREATE TABLE peers
chris@1
   109
(
chris@1
   110
  peer_id     SERIAL,
chris@1
   111
  host        VARCHAR(255),
chris@1
   112
  port        SMALLINT UNSIGNED,
chris@1
   113
chris@1
   114
  PRIMARY KEY(peer_id),
chris@1
   115
  UNIQUE(host, port)
chris@1
   116
)
chris@1
   117
ENGINE = INNODB
chris@1
   118
CHARACTER SET utf8;
chris@1
   119
chris@1
   120
/* 
chris@1
   121
  List of newsgroups to feed into sonews 
chris@1
   122
chris@1
   123
  Normalization: 1NF, 2NF, 3NF
chris@1
   124
*/
chris@1
   125
CREATE TABLE peer_subscriptions
chris@1
   126
(
chris@1
   127
  peer_id    INTEGER REFERENCES peers.peer_id ON DELETE CASCADE,
chris@1
   128
  group_id   INTEGER REFERENCES groups.group_id ON DELETE CASCADE,
chris@1
   129
  feedtype   TINYINT UNSIGNED DEFAULT 0,
chris@1
   130
chris@1
   131
  PRIMARY KEY(peer_id, group_id, feedtype)
chris@1
   132
)
chris@1
   133
ENGINE = INNODB
chris@1
   134
CHARACTER SET utf8;
chris@1
   135
chris@1
   136
/* 
chris@1
   137
   Tables for server event statistics
chris@1
   138
chris@1
   139
   Possible statistic keys:
chris@1
   140
   1=CONNECTIONS     (active connections)
chris@1
   141
   2=POSTED_NEWS     (directly to the server posted unique messages)
chris@1
   142
   3=GATEWAYED_NEWS  (posted unique message gateways through the ML-gateway)
chris@1
   143
   4=FEEDED_NEWS     (unique messages feed via NNTP)
chris@1
   144
chris@1
   145
   The server will create snapshots of the above data.
chris@1
   146
chris@1
   147
   Normalization: 1NF, 2NF
chris@1
   148
*/
chris@1
   149
CREATE TABLE events
chris@1
   150
(
chris@1
   151
  event_time         BIGINT UNSIGNED,   /* time of this snapshot */
chris@1
   152
  event_key          TINYINT UNSIGNED,  /* which data */
chris@1
   153
  group_id           INT REFERENCES groups.group_id ON DELETE CASCADE,
chris@1
   154
chris@1
   155
  PRIMARY KEY(event_time, event_key)
chris@1
   156
)
chris@1
   157
ENGINE = INNODB
chris@1
   158
CHARACTER SET utf8;