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