/* Main address table */
/* Need varchar. Domain = 3 chars => fixed length, as opposed to varchar */
/* Always select on domain and hash, so that one index should do         */
/* primary key(address) is very inefficient for MySQL. */
/* MySQL tables do not need a primary key. Other RDBMS require one. For  */
/* the log tables, just add an INT AUTO_INCREMENT. For the address table,*/
/* do that or use address as a primary key. */

create TABLE [префикс] (
        hash            TINYINT UNSIGNED NOT NULL,
        address         VARCHAR(255) NOT NULL,
        INDEX h (hash),
        INDEX a (address(12)));

/* Subscription log table. No addr idx to make insertion fast, since that is */
/* almost the only thing we do with this table */
create TABLE [префикс]_slog (
        tai             TIMESTAMP,
        address         VARCHAR(255) NOT NULL,
        fromline        VARCHAR(255) NOT NULL,
        edir            CHAR(1) NOT NULL,
        etype           CHAR(1) NOT NULL,
        INDEX (tai));

/* digest list table */
create TABLE [префикс]_digest (
        hash            TINYINT UNSIGNED NOT NULL,
        address         VARCHAR(255) NOT NULL,
        INDEX h (hash),
        INDEX a (address(12)));

/* digest list subscription log */
create TABLE [префикс]_digest_slog (
        tai             TIMESTAMP,
        address         VARCHAR(255) NOT NULL,
        fromline        VARCHAR(255) NOT NULL,
        edir            CHAR(1) NOT NULL,
        etype           CHAR(1) NOT NULL,
        INDEX (tai));

/* moderator addresses */
create TABLE [префикс]_mod (
        hash            TINYINT UNSIGNED NOT NULL,
        address         VARCHAR(255) NOT NULL,
        INDEX h(hash),
        INDEX a(address(12)));

/* moderator subscription log */
create TABLE [префикс]_mod_slog (
        tai             TIMESTAMP,
        address         VARCHAR(255) NOT NULL,
        fromline        VARCHAR(255) NOT NULL,
        edir            CHAR(1) NOT NULL,
        etype           CHAR(1) NOT NULL,
        INDEX (tai));

/* "allow" address table */
create TABLE [префикс]_allow (
        hash            TINYINT UNSIGNED NOT NULL,
        address         VARCHAR(255) NOT NULL,
        INDEX h(hash),
        INDEX a(address(12)));

/* extra address table log */
create TABLE [префикс]_allow_slog (
        tai             TIMESTAMP,
        address         VARCHAR(255) NOT NULL,
        fromline        VARCHAR(255) NOT NULL,
        edir            CHAR(1) NOT NULL,
        etype           CHAR(1) NOT NULL,
        INDEX (tai));

/* blacklist address table */
create TABLE [префикс]_deny (
        hash            TINYINT UNSIGNED NOT NULL,
        address         VARCHAR(255) NOT NULL,
        INDEX h(hash),
        INDEX a(address(12)));

/* blacklist subscription log */
create TABLE [префикс]_deny_slog (
        tai             TIMESTAMP,
        address         VARCHAR(255) NOT NULL,
        fromline        VARCHAR(255) NOT NULL,
        edir            CHAR(1) NOT NULL,
        etype           CHAR(1) NOT NULL,
        INDEX (tai));

/* main list inserts a cookie here. Sublists check it */
CREATE TABLE [префикс]_cookie (
        msgnum          INTEGER UNSIGNED NOT NULL,
        tai             TIMESTAMP NOT NULL,
        cookie          CHAR(20) NOT NULL,
        chunk           TINYINT UNSIGNED NOT NULL DEFAULT 0,
        bodysize        INTEGER UNSIGNED NOT NULL DEFAULT 0,
        PRIMARY KEY (msgnum));

/* main and sublist log here when the message is done */
/* done=0 for arrived, done=4 for sent, 5 for receit. */
/* tai reflects last change */
CREATE TABLE [префикс]_mlog (
        msgnum          INTEGER UNSIGNED NOT NULL,
        listno          INTEGER UNSIGNED NOT NULL,
        tai             TIMESTAMP,
        subs            INTEGER UNSIGNED NOT NULL DEFAULT 0,
        done            TINYINT NOT NULL DEFAULT 0,
        PRIMARY KEY listmsg (listno,msgnum,done));

/* ezmlm-get when creating a digests inserts a cookie here. Sublists check it */
CREATE TABLE [префикс]_digest_cookie (
        msgnum          INTEGER UNSIGNED NOT NULL,
        tai             TIMESTAMP NOT NULL,
        cookie          CHAR(20) NOT NULL,
        chunk           TINYINT UNSIGNED NOT NULL DEFAULT 0,
        bodysize        INTEGER UNSIGNED NOT NULL DEFAULT 0,
        PRIMARY KEY (msgnum));

/* ezmlm-get and digest sublists log here when the message is done */
/* done=0 for arrived, done=4 for sent, 5 for receit. */
/* tai reflects last change */
CREATE TABLE [префикс]_digest_mlog (
        msgnum          INTEGER UNSIGNED NOT NULL,
        listno          INTEGER UNSIGNED NOT NULL,
        tai             TIMESTAMP,
        subs            INT UNSIGNED NOT NULL DEFAULT 0,
        done            TINYINT NOT NULL DEFAULT 0,
        PRIMARY KEY listmsg (listno,msgnum,done));