1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/helpers/database_mysql5_tmpl.sql	Wed Jul 01 10:48:22 2009 +0200
     1.3 @@ -0,0 +1,158 @@
     1.4 +/* 
     1.5 +  Create a database at first:
     1.6 +    CREATE DATABASE sonews CHARACTER SET 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         TINYINT UNSIGNED DEFAULT 0,
    1.21 +
    1.22 +  PRIMARY KEY(group_id),
    1.23 +  UNIQUE(name)
    1.24 +)
    1.25 +ENGINE = INNODB
    1.26 +CHARACTER SET utf8;
    1.27 +
    1.28 +CREATE TABLE articles 
    1.29 +(
    1.30 +  article_id    INT,
    1.31 +  body          LONGBLOB,
    1.32 +
    1.33 +  PRIMARY KEY(article_id)
    1.34 +)
    1.35 +ENGINE = INNODB
    1.36 +CHARACTER SET utf8;
    1.37 +
    1.38 +CREATE TABLE article_ids
    1.39 +(
    1.40 +  article_id  INT REFERENCES articles.article_id ON DELETE CASCADE,
    1.41 +  message_id  VARCHAR(255),
    1.42 +
    1.43 +  PRIMARY KEY(article_id),
    1.44 +  UNIQUE(message_id)
    1.45 +)
    1.46 +ENGINE = INNODB
    1.47 +CHARACTER SET utf8;
    1.48 +
    1.49 +CREATE TABLE headers
    1.50 +(
    1.51 +  article_id    INT REFERENCES articles.article_id ON DELETE CASCADE,
    1.52 +  header_key    VARCHAR(255),
    1.53 +  header_value  TEXT, /* Max. 64k */
    1.54 +  header_index  INT,
    1.55 +
    1.56 +  PRIMARY KEY(article_id, header_key, header_index)
    1.57 +)
    1.58 +ENGINE = INNODB
    1.59 +CHARACTER SET utf8;
    1.60 +
    1.61 +/*
    1.62 +  Normalization: 1NF, 2NF
    1.63 +*/
    1.64 +CREATE TABLE postings 
    1.65 +(
    1.66 +  group_id      INTEGER,
    1.67 +  article_id    INTEGER REFERENCES articles.article_id ON DELETE CASCADE,
    1.68 +  article_index INTEGER NOT NULL, 
    1.69 +
    1.70 +  PRIMARY KEY(group_id, article_id)
    1.71 +)
    1.72 +ENGINE = INNODB
    1.73 +CHARACTER SET utf8;
    1.74 +
    1.75 +/* 
    1.76 +  Table for association of newsgroups and mailing-lists 
    1.77 +
    1.78 +  Normalization: 1NF, 2NF, 3NF
    1.79 +*/
    1.80 +CREATE TABLE groups2list
    1.81 +(
    1.82 +  group_id    INTEGER REFERENCES groups.group_id ON DELETE CASCADE,
    1.83 +  listaddress VARCHAR(255),
    1.84 +
    1.85 +  PRIMARY KEY(group_id, listaddress),
    1.86 +  UNIQUE(listaddress)
    1.87 +)
    1.88 +ENGINE = INNODB
    1.89 +CHARACTER SET utf8;
    1.90 +
    1.91 +/* 
    1.92 +  Configuration table, containing key/value pairs 
    1.93 +
    1.94 +  Normalization: 1NF, 2NF, 3NF
    1.95 +*/
    1.96 +CREATE TABLE config
    1.97 +(
    1.98 +  config_key     VARCHAR(255),
    1.99 +  config_value   TEXT,
   1.100 +
   1.101 +  PRIMARY KEY(config_key)
   1.102 +)
   1.103 +ENGINE = INNODB
   1.104 +CHARACTER SET utf8;
   1.105 +
   1.106 +/* 
   1.107 +  Newsserver peers 
   1.108 +  feedtype: 0: pullfeed 1: pushfeed
   1.109 +  Normalization: 1NF (atomic values), 2NF
   1.110 +*/
   1.111 +CREATE TABLE peers
   1.112 +(
   1.113 +  peer_id     SERIAL,
   1.114 +  host        VARCHAR(255),
   1.115 +  port        SMALLINT UNSIGNED,
   1.116 +
   1.117 +  PRIMARY KEY(peer_id),
   1.118 +  UNIQUE(host, port)
   1.119 +)
   1.120 +ENGINE = INNODB
   1.121 +CHARACTER SET utf8;
   1.122 +
   1.123 +/* 
   1.124 +  List of newsgroups to feed into sonews 
   1.125 +
   1.126 +  Normalization: 1NF, 2NF, 3NF
   1.127 +*/
   1.128 +CREATE TABLE peer_subscriptions
   1.129 +(
   1.130 +  peer_id    INTEGER REFERENCES peers.peer_id ON DELETE CASCADE,
   1.131 +  group_id   INTEGER REFERENCES groups.group_id ON DELETE CASCADE,
   1.132 +  feedtype   TINYINT UNSIGNED DEFAULT 0,
   1.133 +
   1.134 +  PRIMARY KEY(peer_id, group_id, feedtype)
   1.135 +)
   1.136 +ENGINE = INNODB
   1.137 +CHARACTER SET utf8;
   1.138 +
   1.139 +/* 
   1.140 +   Tables for server event statistics
   1.141 +
   1.142 +   Possible statistic keys:
   1.143 +   1=CONNECTIONS     (active connections)
   1.144 +   2=POSTED_NEWS     (directly to the server posted unique messages)
   1.145 +   3=GATEWAYED_NEWS  (posted unique message gateways through the ML-gateway)
   1.146 +   4=FEEDED_NEWS     (unique messages feed via NNTP)
   1.147 +
   1.148 +   The server will create snapshots of the above data.
   1.149 +
   1.150 +   Normalization: 1NF, 2NF
   1.151 +*/
   1.152 +CREATE TABLE events
   1.153 +(
   1.154 +  event_time         BIGINT UNSIGNED,   /* time of this snapshot */
   1.155 +  event_key          TINYINT UNSIGNED,  /* which data */
   1.156 +  group_id           INT REFERENCES groups.group_id ON DELETE CASCADE,
   1.157 +
   1.158 +  PRIMARY KEY(event_time, event_key)
   1.159 +)
   1.160 +ENGINE = INNODB
   1.161 +CHARACTER SET utf8;