#!/bin/sh
# Simple script to generate input to mysql to generate tables for a list
# All tables are created, even though it is not advisable to put e.g. the
# moderator table in the SQL database nor is it very useful to put the
# blacklist/deny table there. The subscriber lists for the main and digest
# lists should be there, and it's reasonable to put the "extra" list there
# if used.
ECHO='echo'
CAT='cat'

CREATE='y';
DROP='n'

if [ "$1" = "-C" ]; then	# Don't create
  CREATE='n'; shift;
fi
if [ "$1" = "-d" ]; then	# drop
  DROP='y'; shift;
fi
if [ "$1" = "-C" ]; then	# Don't create
  CREATE='n'; shift;
fi
if [ "$1" = "-dC" ]; then	# drop; don't create
  DROP='y'; CREATE='n'; shift;
fi
if [ -z "$1" ]; then
  { ${ECHO} "usage: ezmlm-mktab [-C] [-d] troot | mysql -hhost" \
	"-uuserid -ppw -f datab"; } >&2
  exit 100
fi
TROOT="$1";


if [ "$DROP" = "y" ]; then
  cat <<EOF

/* drop old tables. This may fail unless you use mysql -f */
/* Usage: */
/* ezmlm-mktab [-d] troot | mysql -hhost -uuserid -ppw datab -f */

DROP TABLE ${TROOT};
DROP TABLE ${TROOT}_slog;
DROP TABLE ${TROOT}_digest;
DROP TABLE ${TROOT}_digest_slog;
DROP TABLE ${TROOT}_mod;
DROP TABLE ${TROOT}_mod_slog;
DROP TABLE ${TROOT}_allow;
DROP TABLE ${TROOT}_allow_slog;
DROP TABLE ${TROOT}_deny;
DROP TABLE ${TROOT}_deny_slog;
DROP TABLE ${TROOT}_name;
DROP TABLE ${TROOT}_cookie;
DROP TABLE ${TROOT}_mlog;
DROP TABLE ${TROOT}_digest_name;
DROP TABLE ${TROOT}_digest_cookie;
DROP TABLE ${TROOT}_digest_mlog;

EOF

fi

if [ $CREATE = 'y' ]; then
  cat << EOF

/* 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 ${TROOT} (
	hash		TINYINT UNSIGNED NOT NULL,
	domain		CHAR(3) NOT NULL,
	address		VARCHAR(255) NOT NULL,
	INDEX h (hash),
	INDEX d (domain),
	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 ${TROOT}_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 ${TROOT}_digest (
	hash		TINYINT UNSIGNED NOT NULL,
	domain		CHAR(3) NOT NULL,
	address		VARCHAR(255) NOT NULL,
	INDEX h (hash),
	INDEX d (domain),
	INDEX a (address(12)));

/* digest list subscription log */
create TABLE ${TROOT}_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 ${TROOT}_mod (
	hash		TINYINT UNSIGNED NOT NULL,
	domain		CHAR(3) NOT NULL,
	address		VARCHAR(255) NOT NULL,
	INDEX h(hash),
	INDEX d(domain),
	INDEX a(address(12)));

/* moderator subscription log */
create TABLE ${TROOT}_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 ${TROOT}_allow (
	hash		TINYINT UNSIGNED NOT NULL,
	domain		CHAR(3) NOT NULL,
	address		VARCHAR(255) NOT NULL,
	INDEX h(hash),
	INDEX d(domain),
	INDEX a(address(12)));

/* extra address table log */
create TABLE ${TROOT}_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 ${TROOT}_deny (
	hash		TINYINT UNSIGNED NOT NULL,
	domain		CHAR(3) NOT NULL,
	address		VARCHAR(255) NOT NULL,
	INDEX h(hash),
	INDEX d(domain),
	INDEX a(address(12)));

/* blacklist subscription log */
create TABLE ${TROOT}_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));

/* sublist restriction table */
/* notuse != 0 => defer message. = 0 => process message */
/* no reason for index - will always be small */
create TABLE ${TROOT}_name (
	listno		INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
	name		VARCHAR(255) NOT NULL,
	notuse		TINYINT UNSIGNED NOT NULL DEFAULT 0,
	msgnum_lo	INTEGER UNSIGNED NOT NULL DEFAULT 0,
	msgnum_hi	INTEGER UNSIGNED NOT NULL DEFAULT 4294967295,
	hash_lo		TINYINT UNSIGNED NOT NULL DEFAULT 0,
	hash_hi		TINYINT UNSIGNED NOT NULL DEFAULT 52,
	domain		CHAR(3) NOT NULL DEFAULT '',
	PRIMARY KEY (listno));

/* main list inserts a cookie here. Sublists check it */
CREATE TABLE ${TROOT}_cookie (
	msgnum		INTEGER UNSIGNED NOT NULL,
	tai		TIMESTAMP NOT NULL,
	cookie		CHAR(20) NOT NULL,
	PRIMARY KEY (msgnum));

/* main and sublist log here when the message is done */
/* done=0 for arrived, done=1 for sent. tai reflects last change, as e.g. */
/* done=0 may be overwritten in case first delivery to the list fails.    */
CREATE TABLE ${TROOT}_mlog (
	msgnum		INTEGER UNSIGNED NOT NULL,
	listno		INTEGER UNSIGNED NOT NULL,
	tai		TIMESTAMP,
	done		TINYINT NOT NULL DEFAULT 0,
	PRIMARY KEY listmsg (listno,msgnum,done));

/* digest sublist restriction table */
/* notuse != 0 => defer message. = 0 => process message */
/* no index, since table unlikely to have >30 or so rows */
create TABLE ${TROOT}_digest_name (
	listno		INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name		VARCHAR(255) NOT NULL,
	notuse		TINYINT UNSIGNED NOT NULL DEFAULT 0,
	msgnum_lo	INT UNSIGNED NOT NULL DEFAULT 0,
	msgnum_hi	INT UNSIGNED NOT NULL DEFAULT 4294967295,
	hash_lo		TINYINT UNSIGNED NOT NULL DEFAULT 0,
	hash_hi		TINYINT UNSIGNED NOT NULL DEFAULT 52,
	domain		CHAR(3) NOT NULL DEFAULT '',
	PRIMARY KEY (listno));

/* ezmlm-get when creating a digests inserts a cookie here. Sublists check it */
CREATE TABLE ${TROOT}_digest_cookie (
	msgnum		INTEGER UNSIGNED NOT NULL,
	tai		TIMESTAMP NOT NULL,
	cookie		CHAR(20) NOT NULL,
	PRIMARY KEY (msgnum));

/* ezmlm-get and digest sublists log here when the message is done */
/* done=0 for arrived, done=1 for sent. tai reflects last change */
CREATE TABLE ${TROOT}_digest_mlog (
	msgnum		INTEGER UNSIGNED NOT NULL,
	listno		INTEGER UNSIGNED NOT NULL,
	tai		TIMESTAMP,
	done		TINYINT NOT NULL DEFAULT 0,
	PRIMARY KEY listmsg (listno,msgnum,done));

EOF

fi
exit 0

