helpers/database_hsqldb_tmpl.sql
changeset 44 5d7d1adf387f
child 45 7e24949b87b0
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/helpers/database_hsqldb_tmpl.sql	Tue Jun 07 11:55:22 2011 +0200
     1.3 @@ -0,0 +1,142 @@
     1.4 +/* 
     1.5 +  flags:
     1.6 +  If bit 0 is set, groups is a mirrorred mailing list. 
     1.7 +  If not set default newsgroup.
     1.8 +
     1.9 +  Normalization: 1NF, 2NF, 3NF
    1.10 +*/
    1.11 +CREATE CACHED TABLE groups 
    1.12 +(
    1.13 +  group_id      INT,
    1.14 +  name          VARCHAR(80) NOT NULL,
    1.15 +  flags         TINYINT DEFAULT 0,
    1.16 +
    1.17 +  PRIMARY KEY(group_id),
    1.18 +  UNIQUE(name)
    1.19 +);
    1.20 +
    1.21 +CREATE CACHED TABLE articles 
    1.22 +(
    1.23 +  article_id    INT,
    1.24 +  body          VARBINARY,
    1.25 +
    1.26 +  PRIMARY KEY(article_id)
    1.27 +);
    1.28 +
    1.29 +CREATE CACHED TABLE article_ids
    1.30 +(
    1.31 +  article_id  INT,
    1.32 +  message_id  VARCHAR(255),
    1.33 +
    1.34 +  PRIMARY KEY(article_id),
    1.35 +  UNIQUE(message_id),
    1.36 +  FOREIGN KEY(article_id) REFERENCES articles(article_id) ON DELETE CASCADE
    1.37 +);
    1.38 +
    1.39 +CREATE CACHED TABLE headers
    1.40 +(
    1.41 +  article_id    INT,
    1.42 +  header_key    VARCHAR(255),
    1.43 +  header_value  LONGVARCHAR,
    1.44 +  header_index  INT,
    1.45 +
    1.46 +  PRIMARY KEY(article_id, header_key, header_index),
    1.47 +  FOREIGN KEY(article_id) REFERENCES articles(article_id) ON DELETE CASCADE
    1.48 +);
    1.49 +
    1.50 +/*
    1.51 +  Normalization: 1NF, 2NF
    1.52 +*/
    1.53 +CREATE CACHED TABLE postings 
    1.54 +(
    1.55 +  group_id      INTEGER,
    1.56 +  article_id    INTEGER,
    1.57 +  article_index INTEGER NOT NULL, 
    1.58 +
    1.59 +  PRIMARY KEY(group_id, article_id),
    1.60 +  FOREIGN KEY(article_id) REFERENCES articles(article_id) ON DELETE CASCADE
    1.61 +);
    1.62 +
    1.63 +/* 
    1.64 +  Table for association of newsgroups and mailing-lists 
    1.65 +
    1.66 +  Normalization: 1NF, 2NF, 3NF
    1.67 +*/
    1.68 +CREATE CACHED TABLE groups2list
    1.69 +(
    1.70 +  group_id    INTEGER,
    1.71 +  listaddress VARCHAR(255),
    1.72 +
    1.73 +  PRIMARY KEY(group_id, listaddress),
    1.74 +  UNIQUE(listaddress),
    1.75 +  FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE 
    1.76 +);
    1.77 +
    1.78 +/* 
    1.79 +  Configuration table, containing key/value pairs 
    1.80 +
    1.81 +  Normalization: 1NF, 2NF, 3NF
    1.82 +*/
    1.83 +CREATE CACHED TABLE config
    1.84 +(
    1.85 +  config_key     VARCHAR(255),
    1.86 +  config_value   LONGVARCHAR,
    1.87 +
    1.88 +  PRIMARY KEY(config_key)
    1.89 +);
    1.90 +
    1.91 +/* 
    1.92 +  Newsserver peers 
    1.93 +  feedtype: 0: pullfeed 1: pushfeed
    1.94 +  Normalization: 1NF (atomic values), 2NF
    1.95 +*/
    1.96 +CREATE CACHED TABLE peers
    1.97 +(
    1.98 +  peer_id     INT,
    1.99 +  host        VARCHAR(255),
   1.100 +  port        INT,
   1.101 +
   1.102 +  PRIMARY KEY(peer_id),
   1.103 +  UNIQUE(host, port)
   1.104 +);
   1.105 +
   1.106 +/* 
   1.107 +  List of newsgroups to feed into sonews 
   1.108 +
   1.109 +  Normalization: 1NF, 2NF, 3NF
   1.110 +*/
   1.111 +CREATE CACHED TABLE peer_subscriptions
   1.112 +(
   1.113 +  peer_id    INTEGER,
   1.114 +  group_id   INTEGER,
   1.115 +  feedtype   TINYINT DEFAULT 0,
   1.116 +
   1.117 +  PRIMARY KEY(peer_id, group_id, feedtype),
   1.118 +  FOREIGN KEY(peer_id) REFERENCES peers(peer_id) ON DELETE CASCADE,
   1.119 +  FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE
   1.120 +);
   1.121 +
   1.122 +/* 
   1.123 +   Tables for server event statistics
   1.124 +
   1.125 +   Possible statistic keys:
   1.126 +   1=CONNECTIONS     (active connections)
   1.127 +   2=POSTED_NEWS     (directly to the server posted unique messages)
   1.128 +   3=GATEWAYED_NEWS  (posted unique message gateways through the ML-gateway)
   1.129 +   4=FEEDED_NEWS     (unique messages feed via NNTP)
   1.130 +
   1.131 +   The server will create snapshots of the above data.
   1.132 +
   1.133 +   Normalization: 1NF, 2NF
   1.134 +*/
   1.135 +CREATE CACHED TABLE events
   1.136 +(
   1.137 +  event_time         BIGINT,   /* time of this snapshot */
   1.138 +  event_key          TINYINT,  /* which data */
   1.139 +  group_id           INT ,
   1.140 +
   1.141 +  PRIMARY KEY(event_time, event_key),
   1.142 +  FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE
   1.143 +);
   1.144 +
   1.145 +COMMIT;