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